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

namespace HorseRacingPredictor.Football.Database
{
    internal class Score : HorseRacingPredictor.Football.Manager.Database
    {
        public void Upsert(SqlConnection connection, JsonNode score, int fixtureId)
        {
            try
            {
                var query = @"
                        IF EXISTS (SELECT 1 FROM Score WHERE fixture_id = @fixture_id)
                        BEGIN
                            UPDATE Score SET home_halftime = @home_halftime, away_halftime = @away_halftime, 
                                         home_fulltime = @home_fulltime, away_fulltime = @away_fulltime, 
                                         home_extratime = @home_extratime, away_extratime = @away_extratime, 
                                         home_penalty = @home_penalty, away_penalty = @away_penalty 
                                         WHERE fixture_id = @fixture_id
                        END
                        ELSE
                        BEGIN
                            INSERT INTO Score (home_halftime, away_halftime, home_fulltime, away_fulltime, home_extratime, away_extratime, home_penalty, away_penalty, fixture_id) 
                            VALUES (@home_halftime, @away_halftime, @home_fulltime, @away_fulltime, @home_extratime, @away_extratime, @home_penalty, @away_penalty, @fixture_id)
                        END";
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@home_halftime", score["halftime"]["home"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@away_halftime", score["halftime"]["away"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@home_fulltime", score["fulltime"]["home"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@away_fulltime", score["fulltime"]["away"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@home_extratime", score["extratime"]["home"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@away_extratime", score["extratime"]["away"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@home_penalty", score["penalty"]["home"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@away_penalty", score["penalty"]["away"]?.GetValue<int?>() ?? 0);
                    command.Parameters.AddWithValue("@fixture_id", fixtureId);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert del punteggio", ex);
            }
        }
    }
}
