using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.Json.Nodes;

namespace HorseRacingPredictor.Football.Database
{
    internal class Fixture : HorseRacingPredictor.Football.Manager.Database
    {
        public void Upsert(SqlConnection connection, SqlTransaction transaction, JsonNode fixture)
        {
            try
            {
                var id = fixture["id"]?.GetValue<int>();
                if (id == null) return; // Salta il record se l'id è null

                var query = @"
                                    IF EXISTS (SELECT 1 FROM Fixture WHERE fixture_id = @fixture_id)
                                    BEGIN
                                        UPDATE Fixture SET 
                                            referee = @referee,
                                            timezone = @timezone, 
                                            date = @date, 
                                            timestamp = @timestamp,
                                            venue_id = @venue_id,
                                            status_long = @status_long,
                                            status_short = @status_short,
                                            status_elapsed = @status_elapsed,
                                            status_extra = @status_extra
                                        WHERE fixture_id = @fixture_id
                                    END
                                    ELSE
                                    BEGIN
                                        INSERT INTO Fixture (
                                            fixture_id, 
                                            referee, 
                                            timezone, 
                                            date, 
                                            timestamp, 
                                            venue_id, 
                                            status_long, 
                                            status_short, 
                                            status_elapsed, 
                                            status_extra
                                        ) VALUES (
                                            @fixture_id, 
                                            @referee, 
                                            @timezone, 
                                            @date, 
                                            @timestamp, 
                                            @venue_id, 
                                            @status_long, 
                                            @status_short, 
                                            @status_elapsed, 
                                            @status_extra
                                        )
                                    END";
                using (var command = new SqlCommand(query, connection, transaction))
                {
                    // Estrai tutti i valori dal JSON con gestione dei valori null
                    var status = fixture["status"];
                    var venue = fixture["venue"];

                    // Parametri principali
                    command.Parameters.AddWithValue("@fixture_id", id);
                    command.Parameters.AddWithValue("@timezone", fixture["timezone"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@date", fixture["date"]?.GetValue<DateTime>() ?? DateTime.MinValue);
                    command.Parameters.AddWithValue("@timestamp", fixture["timestamp"]?.GetValue<long>() ?? 0L);
                    command.Parameters.AddWithValue("@referee", fixture["referee"]?.GetValue<string>() ?? (object)DBNull.Value);

                    // Parametri venue
                    int? venueId = venue?["id"]?.GetValue<int?>();
                    command.Parameters.AddWithValue("@venue_id", venueId.HasValue ? (object)venueId.Value : DBNull.Value);

                    // Parametri status
                    command.Parameters.AddWithValue("@status_long", status?["long"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@status_short", status?["short"]?.GetValue<string>() ?? "");

                    int? elapsed = status?["elapsed"]?.GetValue<int?>();
                    int? extra = null; // Assumo che l'API restituisca questo valore, altrimenti va gestito come gli altri

                    command.Parameters.AddWithValue("@status_elapsed", elapsed.HasValue ? (object)elapsed.Value : DBNull.Value);
                    command.Parameters.AddWithValue("@status_extra", extra.HasValue ? (object)extra.Value : DBNull.Value);

                    command.ExecuteNonQuery();
                }

                // Gestione della venue, se necessario
                if (fixture["venue"] != null && fixture["venue"]["id"] != null)
                {
                    UpsertVenue(connection, transaction, fixture["venue"]);
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert del fixture", ex);
                throw; // Rilancia l'eccezione per il rollback della transazione
            }
        }

        /// <summary>
        /// Metodo di supporto per inserire/aggiornare le informazioni sulla venue
        /// </summary>
        private void UpsertVenue(SqlConnection connection, SqlTransaction transaction, JsonNode venue)
        {
            try
            {
                var id = venue["id"]?.GetValue<int>();
                if (id == null) return;

                var query = @"
                            IF EXISTS (SELECT 1 FROM Venue WHERE venue_id = @venue_id)
                            BEGIN
                                UPDATE Venue SET name = @name, city = @city WHERE venue_id = @venue_id
                            END
                            ELSE
                            BEGIN
                                INSERT INTO Venue (venue_id, name, city) VALUES (@venue_id, @name, @city)
                            END";

                using (var command = new SqlCommand(query, connection, transaction))
                {
                    command.Parameters.AddWithValue("@venue_id", id);
                    command.Parameters.AddWithValue("@name", venue["name"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@city", venue["city"]?.GetValue<string>() ?? "");
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert della venue", ex);
                throw; // Rilancia l'eccezione per il rollback della transazione
            }
        }

        /// <summary>
        /// Recupera i fixture elaborati dal database con tutte le informazioni correlate
        /// </summary>
        /// <returns>DataTable contenente tutte le partite con le relative quote e previsioni</returns>
        public DataTable GetProcessedFixtures()
        {
            var result = new DataTable();

            try
            {
                using (var connection = new System.Data.SqlClient.SqlConnection(_connectionString))
                {
                    connection.Open();

                    string query = @"
                            SELECT 
                                f.fixture_id AS ID,
                                l.country AS Paese,
                                l.name AS Campionato,
                                f.date AS [Data / Ora],
                                f.status AS Stato,
                                th.name AS Casa,
                                ta.name AS Trasferta,
                                g.home AS [Goals Casa],
                                g.away AS [Goals Trasferta],
                                o1.value AS [Quota Casa],
                                o2.value AS [Quota Pareggio],
                                o3.value AS [Quota Trasferta],
                                p.winner_name AS Previsione
                            FROM 
                                Fixture f
                                JOIN FixtureLeague fl ON f.fixture_id = fl.fixture_id
                                JOIN League l ON fl.league_id = l.league_id
                                JOIN Team th ON th.team_id = f.home_team_id
                                JOIN Team ta ON ta.team_id = f.away_team_id
                                LEFT JOIN Goals g ON f.fixture_id = g.fixture_id
                                LEFT JOIN (
                                    SELECT fixture_id, value FROM Odds 
                                    WHERE bet_id = 1 AND value_name = 'Home' AND bookmaker_id = 8
                                ) o1 ON f.fixture_id = o1.fixture_id
                                LEFT JOIN (
                                    SELECT fixture_id, value FROM Odds 
                                    WHERE bet_id = 1 AND value_name = 'Draw' AND bookmaker_id = 8
                                ) o2 ON f.fixture_id = o2.fixture_id
                                LEFT JOIN (
                                    SELECT fixture_id, value FROM Odds 
                                    WHERE bet_id = 1 AND value_name = 'Away' AND bookmaker_id = 8
                                ) o3 ON f.fixture_id = o3.fixture_id
                                LEFT JOIN Prediction p ON f.fixture_id = p.fixture_id
                            ORDER BY
                                f.date ASC";

                    using (var adapter = new System.Data.SqlClient.SqlDataAdapter(query, connection))
                    {
                        adapter.Fill(result);
                    }
                }
            }
            catch (Exception ex)
            {
                LogError("recupero partite elaborate dal database", ex);
                // Return empty table in case of error
            }

            return result;
        }
    }
}
