using System.Data;
using System.Data.OleDb;
using TarifasANEEL.Models;
using System.Text.Json.Nodes;
using System.Globalization;

namespace TarifasANEEL.Services
{
    public static class DatabaseService
    {
        public static DataTable DistribuidorasTable { get; private set; } = new();

        public static OleDbConnection CreateConnection(string connectionString)
        {
            return new OleDbConnection(connectionString);
        }

        public static bool TableExists(OleDbConnection connection, string tableName)
        {
            try
            {
                using var command = new OleDbCommand($"SELECT COUNT(*) FROM {tableName}", connection);
                command.ExecuteScalar();
                return true;
            }
            catch (OleDbException)
            {
                return false;
            }
        }

        public static void LoadDistribuidoras(OleDbConnection connection)
        {
            if (!TableExists(connection, "Distribuidoras_geral"))
            {
                throw new Exception("Tabela [Distribuidoras_geral] não encontrada.");
            }

            using var command = new OleDbCommand(
                $"SELECT *\r\nFROM Distribuidoras_geral\r\nWHERE ((Not (Distribuidoras_geral.CNPJ)=\"\"));",
                connection);

            using OleDbDataReader reader = command.ExecuteReader();
            DistribuidorasTable = new DataTable();
            DistribuidorasTable.Load(reader);
        }

        public static int GetOffsetForTable(string tableName, bool isAll, string idColumn, OleDbConnection connection)
        {
            if (TableExists(connection, tableName))
            {
                string query = isAll
                    ? $"SELECT MAX([{idColumn}]) FROM {tableName}"
                    : $"SELECT COUNT([{idColumn}]) FROM {tableName}";

                using var command = new OleDbCommand(query, connection);
                object result = command.ExecuteScalar() ?? 0;
                return Convert.ToInt32(result);
            }
            return 0;
        }
        public static int GetDistribuidoraId(string cnpj)
        {
            return int.Parse(DistribuidorasTable?.AsEnumerable().FirstOrDefault(row => row["CNPJ"].ToString() == cnpj)?["ID_dist"].ToString() ?? "0");
        }

        public static void AddDataToTable(OleDbConnection connection,
            Dictionary<int, Dictionary<string, Dictionary<string, Dictionary<string, Tarifa>>>> data,
            string tableName, string year)
        {
            if (!TableExists(connection, tableName))
            {
                // Define columns based on Tarifa model properties
                var createColumns = @"
                    ID_tar COUNTER PRIMARY KEY,
                    ID_dist NUMBER,
                    Grupo TEXT, 
                    Perfil TEXT, 
                    Ciclo TEXT,
                    DatInicioVigencia DATETIME,
                    DatFimVigencia DATETIME,
                    Energia_P NUMBER,
                    Energia_FP NUMBER,
                    Enc_P NUMBER,
                    Enc_FP NUMBER,
                    Dem_P NUMBER,
                    Dem_FP NUMBER,
                    ER_P NUMBER,
                    ER_FP NUMBER,
                    Megaflex NUMBER,
                    Covid_P NUMBER,
                    Covid_FP NUMBER,
                    EH_P NUMBER,
                    EH_FP NUMBER";
                CreateTable(connection, tableName, createColumns);
            }

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            using var transaction = connection.BeginTransaction();

            try
            {
                foreach (var (idDist, grupos) in data)
                foreach (var (grupo, perfis) in grupos)
                foreach (var (perfil, ciclos) in perfis)
                foreach (var (ciclo, tarifa) in ciclos)
                {
                    var selectCommand = new OleDbCommand(
                        $"SELECT ID_tar FROM {tableName} " +
                        "WHERE ID_dist = @ID_dist AND Grupo = @Grupo AND Perfil = @Perfil AND Ciclo = @Ciclo",
                        connection, transaction);

                    selectCommand.Parameters.AddWithValue("@ID_dist", idDist);
                    selectCommand.Parameters.AddWithValue("@Grupo", grupo);
                    selectCommand.Parameters.AddWithValue("@Perfil", perfil);
                    selectCommand.Parameters.AddWithValue("@Ciclo", ciclo);

                    object? result = null;
                    try
                    {
                        result = selectCommand.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Error checking for existing record: {ex.Message}");
                    }

                    OleDbCommand command;
                    if (result != null && result != DBNull.Value)
                    {
                        var updateSql = $@"UPDATE {tableName} 
                            SET DatInicioVigencia = @DatInicioVigencia,
                                DatFimVigencia = @DatFimVigencia,
                                Energia_P = @Energia_P,
                                Energia_FP = @Energia_FP,
                                Enc_P = @Enc_P,
                                Enc_FP = @Enc_FP,
                                Dem_P = @Dem_P,
                                Dem_FP = @Dem_FP,
                                ER_P = @ER_P,
                                ER_FP = @ER_FP,
                                Covid_P = @Covid_P,
                                Covid_FP = @Covid_FP,
                                EH_P = @EH_P,
                                EH_FP = @EH_FP
                            WHERE ID_tar = @ID_tar";

                        command = new OleDbCommand(updateSql, connection, transaction);
                        AddTarifaParameters(command, tarifa);
                        command.Parameters.AddWithValue("@ID_tar", result);
                    }
                    else
                    {
                        // Insert new record
                        var insertSql = $@"INSERT INTO {tableName} 
                            (ID_dist, Grupo, Perfil, Ciclo, DatInicioVigencia,
                             DatFimVigencia, Energia_P, Energia_FP, Enc_P, Enc_FP,
                             Dem_P, Dem_FP, ER_P, ER_FP, Covid_P,
                             Covid_FP, EH_P, EH_FP)
                            VALUES 
                            (@ID_dist, @Grupo, @Perfil, @Ciclo, @DatInicioVigencia,
                             @DatFimVigencia, @Energia_P, @Energia_FP, @Enc_P, @Enc_FP,
                             @Dem_P, @Dem_FP, @ER_P, @ER_FP, @Covid_P,
                             @Covid_FP, @EH_P, @EH_FP)";

                        command = new OleDbCommand(insertSql, connection, transaction);
                        command.Parameters.AddWithValue("@ID_dist", tarifa.ID_dist);
                        command.Parameters.AddWithValue("@Grupo", tarifa.Grupo);
                        command.Parameters.AddWithValue("@Perfil", tarifa.Perfil);
                        command.Parameters.AddWithValue("@Ciclo", tarifa.Ciclo);
                        AddTarifaParameters(command, tarifa);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Error executing command: {ex.Message}");
                        throw;
                    }
                }

                transaction.Commit();
                Console.WriteLine($"Dados de {year} atualizados com sucesso em [{tableName}]");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Erro ao atualizar dados: {ex.Message}");
                transaction.Rollback();
                throw;
            }
        }

        private static void AddTarifaParameters(OleDbCommand command, Tarifa tarifa)
        {
            command.Parameters.AddWithValue("@DatInicioVigencia", tarifa.DatInicioVigencia);
            command.Parameters.AddWithValue("@DatFimVigencia", tarifa.DatFimVigencia);
            command.Parameters.AddWithValue("@Energia_P", tarifa.Energia_P);
            command.Parameters.AddWithValue("@Energia_FP", tarifa.Energia_FP);
            command.Parameters.AddWithValue("@Enc_P", tarifa.Enc_P);
            command.Parameters.AddWithValue("@Enc_FP", tarifa.Enc_FP);
            command.Parameters.AddWithValue("@Dem_P", tarifa.Dem_P);
            command.Parameters.AddWithValue("@Dem_FP", tarifa.Dem_FP);
            command.Parameters.AddWithValue("@ER_P", tarifa.ER_P);
            command.Parameters.AddWithValue("@ER_FP", tarifa.ER_FP);
            command.Parameters.AddWithValue("@Covid_P", tarifa.Covid_P);
            command.Parameters.AddWithValue("@Covid_FP", tarifa.Covid_FP);
            command.Parameters.AddWithValue("@EH_P", tarifa.EH_P);
            command.Parameters.AddWithValue("@EH_FP", tarifa.EH_FP);
        }

        private static void CreateTable(OleDbConnection connection, string tableName, string columnNames)
        {
            using var command = new OleDbCommand(
                @$"CREATE TABLE {tableName} ({columnNames})",
                connection);

            command.ExecuteNonQuery();
        }
    }
}