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

namespace HorseRacingPredictor.Football.Database
{
    internal class Prediction : HorseRacingPredictor.Football.Manager.Database
    {
        public void Upsert(SqlConnection connection, JsonNode predictions, int fixtureId)
        {
            try
            {
                // Estrazione dei dati dalla risposta JSON
                string advice = predictions["advice"]?.GetValue<string>();
                if (string.IsNullOrEmpty(advice)) return; // Nessuna previsione se non c'è consiglio

                // Estrazione degli altri valori, con gestione null
                int? winnerId = predictions["winner"]?["id"]?.GetValue<int?>();
                string winnerName = predictions["winner"]?["name"]?.GetValue<string>();
                string winnerComment = predictions["winner"]?["comment"]?.GetValue<string>();
                bool? winOrDraw = predictions["win_or_draw"]?.GetValue<bool?>();
                string underOver = predictions["under_over"]?.GetValue<string>();
                string goalsHome = predictions["goals"]?["home"]?.GetValue<string>();
                string goalsAway = predictions["goals"]?["away"]?.GetValue<string>();
                string percentHome = predictions["percent"]?["home"]?.GetValue<string>();
                string percentDraw = predictions["percent"]?["draw"]?.GetValue<string>();
                string percentAway = predictions["percent"]?["away"]?.GetValue<string>();

                // Query per l'upsert con tutti i campi disponibili
                var query = @"
                        IF EXISTS (SELECT 1 FROM Prediction WHERE fixture_id = @fixture_id)
                        BEGIN
                            UPDATE Prediction 
                            SET winner_id = @winner_id, 
                                winner_name = @winner_name, 
                                winner_comment = @winner_comment,
                                win_or_draw = @win_or_draw, 
                                under_over = @under_over, 
                                goals_home = @goals_home, 
                                goals_away = @goals_away, 
                                advice = @advice,
                                percent_home = @percent_home, 
                                percent_draw = @percent_draw, 
                                percent_away = @percent_away
                            WHERE fixture_id = @fixture_id
                        END
                        ELSE
                        BEGIN
                            INSERT INTO Prediction (
                                prediction_id, 
                                fixture_id, 
                                winner_id, 
                                winner_name, 
                                winner_comment, 
                                win_or_draw, 
                                under_over, 
                                goals_home, 
                                goals_away, 
                                advice, 
                                percent_home, 
                                percent_draw, 
                                percent_away
                            ) 
                            VALUES (
                                NEXT VALUE FOR prediction_seq, 
                                @fixture_id, 
                                @winner_id, 
                                @winner_name, 
                                @winner_comment, 
                                @win_or_draw, 
                                @under_over, 
                                @goals_home, 
                                @goals_away, 
                                @advice, 
                                @percent_home, 
                                @percent_draw, 
                                @percent_away
                            )
                        END";

                using (var command = new SqlCommand(query, connection))
                {
                    // Fixture ID
                    command.Parameters.AddWithValue("@fixture_id", fixtureId);

                    // Dati vincitore
                    command.Parameters.AddWithValue("@winner_id", winnerId.HasValue ? (object)winnerId.Value : DBNull.Value);
                    command.Parameters.AddWithValue("@winner_name", string.IsNullOrEmpty(winnerName) ? DBNull.Value : (object)winnerName);
                    command.Parameters.AddWithValue("@winner_comment", string.IsNullOrEmpty(winnerComment) ? DBNull.Value : (object)winnerComment);

                    // Altri dati di previsione
                    command.Parameters.AddWithValue("@win_or_draw", winOrDraw.HasValue ? (object)winOrDraw.Value : DBNull.Value);
                    command.Parameters.AddWithValue("@under_over", string.IsNullOrEmpty(underOver) ? DBNull.Value : (object)underOver);
                    command.Parameters.AddWithValue("@goals_home", string.IsNullOrEmpty(goalsHome) ? DBNull.Value : (object)goalsHome);
                    command.Parameters.AddWithValue("@goals_away", string.IsNullOrEmpty(goalsAway) ? DBNull.Value : (object)goalsAway);
                    command.Parameters.AddWithValue("@advice", advice); // Sappiamo che non è null grazie al controllo iniziale

                    // Percentuali
                    command.Parameters.AddWithValue("@percent_home", string.IsNullOrEmpty(percentHome) ? DBNull.Value : (object)percentHome);
                    command.Parameters.AddWithValue("@percent_draw", string.IsNullOrEmpty(percentDraw) ? DBNull.Value : (object)percentDraw);
                    command.Parameters.AddWithValue("@percent_away", string.IsNullOrEmpty(percentAway) ? DBNull.Value : (object)percentAway);

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