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

namespace HorseRacingPredictor.Football.Database
{
    internal class LeagueStats : HorseRacingPredictor.Football.Manager.Database
    {
        public int Insert(SqlConnection connection, int? teamId, int? predictionId, bool isHome, JsonNode stats)
        {
            try
            {
                // Ottieni il prossimo ID di statistiche
                int statsId = GetNextStatsId(connection);
                if (statsId <= 0) return 0;

                var query = @"
                    INSERT INTO LeagueStats (
                        stats_id, team_id, prediction_id, is_home, form,
                        fixtures_played_home, fixtures_played_away, fixtures_played_total,
                        wins_home, wins_away, wins_total,
                        draws_home, draws_away, draws_total,
                        loses_home, loses_away, loses_total
                    ) VALUES (
                        @stats_id, @team_id, @prediction_id, @is_home, @form,
                        @fixtures_played_home, @fixtures_played_away, @fixtures_played_total,
                        @wins_home, @wins_away, @wins_total,
                        @draws_home, @draws_away, @draws_total,
                        @loses_home, @loses_away, @loses_total
                    )";

                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@stats_id", statsId);
                    command.Parameters.AddWithValue("@team_id", teamId.HasValue ? (object)teamId.Value : DBNull.Value);
                    command.Parameters.AddWithValue("@prediction_id", predictionId.HasValue ? (object)predictionId.Value : DBNull.Value);
                    command.Parameters.AddWithValue("@is_home", isHome);
                    command.Parameters.AddWithValue("@form", stats["form"]?.GetValue<string>() ?? (object)DBNull.Value);

                    var fixtures = stats["fixtures"];
                    command.Parameters.AddWithValue("@fixtures_played_home", fixtures?["played"]?["home"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@fixtures_played_away", fixtures?["played"]?["away"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@fixtures_played_total", fixtures?["played"]?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);

                    var wins = stats["fixtures"]?["wins"];
                    command.Parameters.AddWithValue("@wins_home", wins?["home"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@wins_away", wins?["away"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@wins_total", wins?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);

                    var draws = stats["fixtures"]?["draws"];
                    command.Parameters.AddWithValue("@draws_home", draws?["home"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@draws_away", draws?["away"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@draws_total", draws?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);

                    var loses = stats["fixtures"]?["loses"];
                    command.Parameters.AddWithValue("@loses_home", loses?["home"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@loses_away", loses?["away"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@loses_total", loses?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);

                    command.ExecuteNonQuery();
                }

                return statsId;
            }
            catch (Exception ex)
            {
                LogError("l'inserimento delle statistiche di lega", ex);
                return 0;
            }
        }

        private int GetNextStatsId(SqlConnection connection)
        {
            try
            {
                var query = "SELECT ISNULL(MAX(stats_id), 0) + 1 FROM LeagueStats";
                using (var command = new SqlCommand(query, connection))
                {
                    return Convert.ToInt32(command.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                LogError("il recupero del prossimo ID statistiche", ex);
                return 0;
            }
        }
    }
}
