using System.Data.OleDb;
using Faturas;
using System.Text.Json;
using Download_Faturas;

namespace Download_Faturas.Tests
{
    public class FaturaIntegrationTests
    {
        private const string Token = "UFY4VWzqcHYcGNd0gkBOMFL9G5ZThV6gXBQIJ79F5HSqITzavz4Fe7iXvAbJLvZJ";
        private static readonly HttpClient httpClient = new ();

        [Fact]
        public async Task CompareFaturaWithFaturaOld_ShouldReportDifferences()
        {
            // Sample fatura IDs to test (replace with real IDs or fetch dynamically)
            var faturaIds = LoadCsvColumn("../../../../Faturas.Tests/4Docs_2025_07.csv", 0);
            var differences = new List<string>
            {
                $"FaturaId\tDistribuidora\toldConsumo_P\tnewConsumo_P\toldConsumo_FP\tnewConsumo_FP\toldDem_Reg_P\tnewDem_Reg_P\toldDem_Reg_FP\tnewDem_Reg_FP\toldEn_Reativa_Mvarh\tnewEn_Reativa_Mvarh"
            };

            using (OleDbConnection conn = new (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=X:/Middle/Informativo Setorial/Modelo Word/BD1_dados cadastrais e faturas.accdb;Jet OLEDB:Database Password=gds21"))
            {
                // Open the connection to the test database
                conn.Open();

                foreach (var faturaId in faturaIds)
                {
                    if (faturaId == "2640189")
                    {
                        int i = 0;
                        if (i == 0) continue;
                    }
                    // Retrieve fatura JSON from 4Docs API
                    var request = new HttpRequestMessage(HttpMethod.Get, $"https://api.4docs.cloud/v2/request/status?id={faturaId}");
                    request.Headers.TryAddWithoutValidation("Authorization", $"Bearer {Token}");
                    var response = await httpClient.SendAsync(request);
                    if (!response.IsSuccessStatusCode) continue;
                    var json = await response.Content.ReadAsStringAsync();
                    var faturaParsed = JsonDocument.Parse(json).RootElement;

                    // Process with old logic (FaturaOld)
                    var faturaOld = new FaturaOld(faturaId, faturaParsed);
                    // Process with new logic (Fatura)
                    var fatura = new Fatura(faturaId, faturaParsed);

                    faturaOld.Processar(conn); // This will write to DB, but we want to compare without writing
                    var oldVal = GetDadosTusd(conn, faturaOld.CodTusd);
                    DeleteTusdRecords(conn, faturaOld.CodTusd); // Delete old TUSD records before processing

                    fatura.Processar(conn); // This will also write to DB, but we want to compare without writing
                    var newVal = GetDadosTusd(conn, fatura.CodTusd);
                    //DeleteTusdRecords(conn, fatura.CodTusd); // Delete old TUSD records before processing

                    if (!Equals(newVal, oldVal))
                    {
                        differences.Add($"{faturaId}\t{oldVal.Distribuidora}\t{oldVal.Consumo_P}\t{newVal.Consumo_P}\t{oldVal.Consumo_FP}\t{newVal.Consumo_FP}\t{oldVal.Dem_Reg_P}\t{newVal.Dem_Reg_P}\t{oldVal.Dem_Reg_FP}\t{newVal.Dem_Reg_FP}\t{oldVal.En_Reativa_Mvarh}\t{newVal.En_Reativa_Mvarh}");
                    }
                }
            }
            if (differences.Count > 1)
            {
                WriteCsv("../../../../Download Faturas.Tests/divergencias.csv", differences);
            }
            Assert.True(differences.Count == 0, $"Differences found between Fatura and FaturaOld:\n{string.Join("\n", differences)}");
        }

        static void DeleteTusdRecords(OleDbConnection conn, double codTusd)
        {
            // Deletes TUSD records based on cod_tusd
            string sqlQuery = "DELETE FROM Dados_TUSD WHERE Cod_TUSD = ?";
            using (OleDbCommand cmd = new (sqlQuery, conn))
            {
                cmd.Parameters.AddWithValue("?", codTusd);
                cmd.ExecuteNonQuery();
            }
        }

        static RecordSet GetDadosTusd(OleDbConnection conn, double codTusd)
        {
            RecordSet dadosTusd = new ();
            // Retrieves TUSD records based on cod_tusd
            string sqlQuery = "SELECT * FROM Dados_TUSD WHERE Cod_TUSD = ?";
            using (OleDbCommand cmd = new (sqlQuery, conn))
            {
                cmd.Parameters.AddWithValue("?", codTusd);
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Dados Tusd
                        ;
                        dadosTusd.Cod_TUSD = double.Parse(reader["Cod_TUSD"].ToString()!);
                        dadosTusd.Mes = int.Parse(reader["Mes"].ToString()!);
                        dadosTusd.Distribuidora = reader["Distribuidora"].ToString()!;
                        dadosTusd.Grupo = reader["Grupo"].ToString()!;
                        dadosTusd.Perfil = reader["Perfil"].ToString()!;
                        dadosTusd.Inicio_Leitura = DateTime.Parse(reader["Inicio_Leitura"].ToString()!);
                        dadosTusd.Fim_leitura = DateTime.Parse(reader["Fim_leitura"].ToString()!);
                        dadosTusd.Valor = float.Parse(reader["Valor"].ToString()!);
                        dadosTusd.Consumo_P = float.Parse(reader["Consumo_P"].ToString()!);
                        dadosTusd.Consumo_FP = float.Parse(reader["Consumo_FP"].ToString()!);
                        dadosTusd.Dem_Reg_P = float.Parse(reader["Dem_Reg_P"].ToString()!);
                        dadosTusd.Dem_Reg_FP = float.Parse(reader["Dem_Reg_FP"].ToString()!);
                        dadosTusd.Dem_Cont_P = float.Parse(reader["Dem_Cont_P"].ToString()!);
                        dadosTusd.Dem_Cont_FP = float.Parse(reader["Dem_Cont_FP"].ToString()!);
                        dadosTusd.En_Reativa_Mvarh = float.Parse(reader["En_Reativa_Mvarh"].ToString()!);
                        dadosTusd.Dem_Reativa_kvar = float.Parse(reader["Dem_Reativa_kvar"].ToString()!);
                    }
                }
            }
            return dadosTusd;
        }
        //load csv column
        static List<string> LoadCsvColumn(string filePath, int columnIndex)
        {
            var columnData = new List<string>();
            using (var reader = new StreamReader(filePath))
            {
                while (!reader.EndOfStream)
                {
                    // skip first line (header)
                    reader.ReadLine();
                    var line = reader.ReadLine();
                    if (line != null)
                    {
                        var values = line.Split('\t');
                        if (values.Length > columnIndex && (values[4] == "\"\"API\"\"" || values[4] == "\"\"BOT\"\""))
                        {
                            columnData.Add(values[columnIndex].Replace("\"", "").Trim());
                        }
                    }
                }
            }
            return columnData;
        }

        //write data to csv
        static void WriteCsv(string filePath, List<string> data)
        {
            using (var writer = new StreamWriter(filePath))
            {
                foreach (var item in data)
                {
                    writer.WriteLine(item);
                }
            }
        }
    }
}