using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.Json.Nodes;
using System.Threading.Tasks;

namespace HorseRacingPredictor.Football.Database
{
    internal class Comparison : HorseRacingPredictor.Football.Manager.Database
    {
        public void Upsert(SqlConnection connection, int predictionId, JsonNode comparison)
        {
            try
            {
                if (comparison == null) return;

                var query = @"
                                IF EXISTS (SELECT 1 FROM Comparison WHERE prediction_id = @prediction_id)
                                BEGIN
                                    UPDATE Comparison 
                                    SET form_home = @form_home, 
                                        form_away = @form_away,
                                        att_home = @att_home,
                                        att_away = @att_away,
                                        def_home = @def_home,
                                        def_away = @def_away,
                                        poisson_home = @poisson_home,
                                        poisson_away = @poisson_away,
                                        h2h_home = @h2h_home,
                                        h2h_away = @h2h_away,
                                        goals_home = @goals_home,
                                        goals_away = @goals_away,
                                        total_home = @total_home,
                                        total_away = @total_away
                                    WHERE prediction_id = @prediction_id
                                END
                                ELSE
                                BEGIN
                                    INSERT INTO Comparison (
                                        prediction_id, form_home, form_away, att_home, att_away,
                                        def_home, def_away, poisson_home, poisson_away,
                                        h2h_home, h2h_away, goals_home, goals_away,
                                        total_home, total_away
                                    ) VALUES (
                                        @prediction_id, @form_home, @form_away, @att_home, @att_away,
                                        @def_home, @def_away, @poisson_home, @poisson_away,
                                        @h2h_home, @h2h_away, @goals_home, @goals_away,
                                        @total_home, @total_away
                                    )
                                END";

                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@prediction_id", predictionId);

                    // Aggiungi tutti i parametri del confronto
                    command.Parameters.AddWithValue("@form_home", comparison["form"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@form_away", comparison["form"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@att_home", comparison["att"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@att_away", comparison["att"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@def_home", comparison["def"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@def_away", comparison["def"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@poisson_home", comparison["poisson_distribution"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@poisson_away", comparison["poisson_distribution"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@h2h_home", comparison["h2h"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@h2h_away", comparison["h2h"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@goals_home", comparison["goals"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@goals_away", comparison["goals"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@total_home", comparison["total"]?["home"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@total_away", comparison["total"]?["away"]?.GetValue<string>() ?? (object)DBNull.Value);

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert del confronto di previsione", ex);
            }
        }
    }
}
