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

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

                var query = @"
                                IF EXISTS (SELECT 1 FROM League WHERE league_id = @league_id)
                                BEGIN
                                    UPDATE League SET name = @name, country = @country, logo = @logo, flag = @flag, season = @season WHERE league_id = @league_id
                                END
                                ELSE
                                BEGIN
                                    INSERT INTO League (league_id, name, country, logo, flag, season) VALUES (@league_id, @name, @country, @logo, @flag, @season)
                                END";
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@league_id", id);
                    command.Parameters.AddWithValue("@name", league["name"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@country", league["country"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@logo", league["logo"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@flag", league["flag"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@season", league["season"]?.GetValue<int>() ?? 0);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert della lega", ex);
            }
        }

        public void Upsert(SqlConnection connection, SqlTransaction transaction, JsonNode league)
        {
            try
            {
                var id = league["id"]?.GetValue<int>();
                if (id == null) return; // Salta il record se l'id è null

                var query = @"
                                IF EXISTS (SELECT 1 FROM League WHERE league_id = @league_id)
                                BEGIN
                                    UPDATE League SET name = @name, country = @country, logo = @logo, flag = @flag, season = @season WHERE league_id = @league_id
                                END
                                ELSE
                                BEGIN
                                    INSERT INTO League (league_id, name, country, logo, flag, season) VALUES (@league_id, @name, @country, @logo, @flag, @season)
                                END";
                using (var command = new SqlCommand(query, connection, transaction))
                {
                    command.Parameters.AddWithValue("@league_id", id);
                    command.Parameters.AddWithValue("@name", league["name"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@country", league["country"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@logo", league["logo"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@flag", league["flag"]?.GetValue<string>() ?? "");
                    command.Parameters.AddWithValue("@season", league["season"]?.GetValue<int>() ?? 0);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError("l'upsert della lega", ex);
                throw; // Rilancia l'eccezione per il rollback della transazione
            }
        }
    }
}
