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 TeamStats : 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 TeamStats (
                            stats_id, team_id, prediction_id, is_home, played, form,
                            att, def, goals_for_total, goals_for_average,
                            goals_against_total, goals_against_average
                        ) VALUES (
                            @stats_id, @team_id, @prediction_id, @is_home, @played, @form,
                            @att, @def, @goals_for_total, @goals_for_average,
                            @goals_against_total, @goals_against_average
                        )";

                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("@played", stats["played"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@form", stats["form"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@att", stats["att"]?.GetValue<string>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@def", stats["def"]?.GetValue<string>() ?? (object)DBNull.Value);

                    var goalsFor = stats["goals"]?["for"];
                    command.Parameters.AddWithValue("@goals_for_total", goalsFor?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@goals_for_average", goalsFor?["average"]?.GetValue<string>() ?? (object)DBNull.Value);

                    var goalsAgainst = stats["goals"]?["against"];
                    command.Parameters.AddWithValue("@goals_against_total", goalsAgainst?["total"]?.GetValue<int?>() ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@goals_against_average", goalsAgainst?["average"]?.GetValue<string>() ?? (object)DBNull.Value);

                    command.ExecuteNonQuery();
                }

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

        private int GetNextStatsId(SqlConnection connection)
        {
            try
            {
                var query = "SELECT ISNULL(MAX(stats_id), 0) + 1 FROM TeamStats";
                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;
            }
        }
    }
}
