using System.Globalization;
using System.Text;
using ClosedXML.Excel;
using line_gestao_api.Data;
using line_gestao_api.Dtos;
using line_gestao_api.Models;
using Microsoft.EntityFrameworkCore;

namespace line_gestao_api.Services;

public sealed class ParcelamentosImportService
{
    private readonly AppDbContext _db;

    public ParcelamentosImportService(AppDbContext db)
    {
        _db = db;
    }

    public async Task<ParcelamentosImportSummaryDto> ImportFromWorkbookAsync(XLWorkbook wb, bool replaceAll, CancellationToken cancellationToken = default)
    {
        var ws = FindWorksheet(wb);
        if (ws == null)
        {
            return new ParcelamentosImportSummaryDto
            {
                Erros =
                {
                    new ParcelamentosImportErrorDto
                    {
                        LinhaExcel = 0,
                        Motivo = "Aba 'PARCELAMENTOS DE APARELHOS' ou 'PARCELAMENTOS' não encontrada."
                    }
                }
            };
        }

        if (replaceAll)
        {
            await _db.ParcelamentoMonthValues.ExecuteDeleteAsync(cancellationToken);
            await _db.ParcelamentoLines.ExecuteDeleteAsync(cancellationToken);
        }

        if (!TryFindParcelamentosHeader(ws, out var headerRow, out var headerMap, out var lastCol))
        {
            return new ParcelamentosImportSummaryDto
            {
                Erros =
                {
                    new ParcelamentosImportErrorDto
                    {
                        LinhaExcel = 0,
                        Motivo = "Cabeçalho da aba PARCELAMENTOS não encontrado (LINHA/CLIENTE/QT PARCELAS/VALOR CHEIO/DESCONTO/VALOR C/ DESCONTO + meses)."
                    }
                }
            };
        }

        var colLinha = GetColAny(headerMap, "LINHA");
        var colCliente = GetColAny(headerMap, "CLIENTE");
        var colQtParcelas = GetColAny(headerMap, "QT PARCELAS", "QT. PARCELAS", "QTPARCELAS");
        var colValorCheio = GetColAny(headerMap, "VALOR CHEIO", "VALORCHEIO");
        var colDesconto = GetColAny(headerMap, "DESCONTO");
        var colValorComDesconto = GetColAny(headerMap, "VALOR C/ DESCONTO", "VALOR C\\ DESCONTO", "VALORCOMDESCONTO", "VALOR C DESCONTO");

        var colItem = GetColAny(headerMap, "ITEM");
        var colAnoRef = GetColAny(headerMap, "ANO REF", "ANOREF", "ANO");

        if (colItem == 0 && colLinha > 1) colItem = colLinha - 1;
        if (colAnoRef == 0 && colLinha > 2) colAnoRef = colLinha - 2;

        var monthMap = BuildDynamicMonthMap(ws, headerRow, colValorComDesconto, lastCol);
        if (monthMap.Count == 0)
        {
            return new ParcelamentosImportSummaryDto
            {
                Erros =
                {
                    new ParcelamentosImportErrorDto
                    {
                        LinhaExcel = headerRow,
                        Motivo = "Meses (JAN..DEZ) não identificados no cabeçalho da aba PARCELAMENTOS."
                    }
                }
            };
        }

        var existing = await _db.ParcelamentoLines
            .AsNoTracking()
            .ToListAsync(cancellationToken);
        var existingByKey = existing
            .Where(x => x.AnoRef.HasValue && x.Item.HasValue)
            .ToDictionary(x => (x.AnoRef!.Value, x.Item!.Value), x => x.Id);

        var summary = new ParcelamentosImportSummaryDto();
        var startRow = headerRow + 1;
        var lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

        for (int row = startRow; row <= lastRow; row++)
        {
            var linhaValue = GetCellStringValue(ws, row, colLinha);
            var qtParcelas = GetCellStringValue(ws, row, colQtParcelas);
            if (!IsValidParcelamentoRow(linhaValue, qtParcelas))
            {
                continue;
            }

            summary.Lidos++;

            var itemStr = GetCellStringValue(ws, row, colItem);
            var anoRefStr = GetCellStringValue(ws, row, colAnoRef);

            var anoRef = TryNullableInt(anoRefStr);
            var item = TryNullableInt(itemStr);

            if (!item.HasValue)
            {
                summary.Erros.Add(new ParcelamentosImportErrorDto
                {
                    LinhaExcel = row,
                    Motivo = "Item inválido ou vazio.",
                    Valor = itemStr
                });
                continue;
            }

            if (!anoRef.HasValue)
            {
                summary.Erros.Add(new ParcelamentosImportErrorDto
                {
                    LinhaExcel = row,
                    Motivo = "AnoRef inválido ou vazio.",
                    Valor = anoRefStr
                });
                continue;
            }

            ParseParcelas(qtParcelas, out var parcelaAtual, out var totalParcelas);

            var valorCheio = GetCellDecimalValue(ws, row, colValorCheio);
            var desconto = GetCellDecimalValue(ws, row, colDesconto);
            var valorComDesconto = GetCellDecimalValue(ws, row, colValorComDesconto);

            var parcelamento = new ParcelamentoLine
            {
                AnoRef = anoRef,
                Item = item,
                Linha = string.IsNullOrWhiteSpace(linhaValue) ? null : linhaValue.Trim(),
                Cliente = NormalizeText(GetCellStringValue(ws, row, colCliente)),
                QtParcelas = string.IsNullOrWhiteSpace(qtParcelas) ? null : qtParcelas.Trim(),
                ParcelaAtual = parcelaAtual,
                TotalParcelas = totalParcelas,
                ValorCheio = valorCheio,
                Desconto = desconto,
                ValorComDesconto = valorComDesconto,
                UpdatedAt = DateTime.UtcNow
            };

            if (existingByKey.TryGetValue((anoRef.Value, item.Value), out var existingId))
            {
                var existingEntity = await _db.ParcelamentoLines
                    .FirstOrDefaultAsync(x => x.Id == existingId, cancellationToken);
                if (existingEntity == null)
                {
                    existingByKey.Remove((anoRef ?? 0, item.Value));
                }
                else
                {
                    existingEntity.AnoRef = parcelamento.AnoRef;
                    existingEntity.Item = parcelamento.Item;
                    existingEntity.Linha = parcelamento.Linha;
                    existingEntity.Cliente = parcelamento.Cliente;
                    existingEntity.QtParcelas = parcelamento.QtParcelas;
                    existingEntity.ParcelaAtual = parcelamento.ParcelaAtual;
                    existingEntity.TotalParcelas = parcelamento.TotalParcelas;
                    existingEntity.ValorCheio = parcelamento.ValorCheio;
                    existingEntity.Desconto = parcelamento.Desconto;
                    existingEntity.ValorComDesconto = parcelamento.ValorComDesconto;
                    existingEntity.UpdatedAt = parcelamento.UpdatedAt;

                    await _db.ParcelamentoMonthValues
                        .Where(x => x.ParcelamentoLineId == existingEntity.Id)
                        .ExecuteDeleteAsync(cancellationToken);

                    var monthValues = BuildMonthValuesFromMap(ws, row, monthMap, existingEntity.Id, summary, out var rawMonths);
                    AuditParcelamentoRow(row, valorCheio, desconto, valorComDesconto, rawMonths, summary);
                    if (monthValues.Count > 0)
                    {
                        await _db.ParcelamentoMonthValues.AddRangeAsync(monthValues, cancellationToken);
                    }

                    summary.Atualizados++;
                    continue;
                }
            }

            parcelamento.CreatedAt = DateTime.UtcNow;
            parcelamento.MonthValues = BuildMonthValuesFromMap(ws, row, monthMap, parcelamento.Id, summary, out var rawMonthsNew);
            AuditParcelamentoRow(row, valorCheio, desconto, valorComDesconto, rawMonthsNew, summary);
            summary.Inseridos++;

            await _db.ParcelamentoLines.AddAsync(parcelamento, cancellationToken);
        }

        await _db.SaveChangesAsync(cancellationToken);
        return summary;
    }

    private static IXLWorksheet? FindWorksheet(XLWorkbook wb)
    {
        return wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("PARCELAMENTOS DE APARELHOS"))
               ?? wb.Worksheets.FirstOrDefault(w => NormalizeHeader(w.Name) == NormalizeHeader("PARCELAMENTOS"))
               ?? wb.Worksheets.FirstOrDefault(w =>
               {
                   var normalized = NormalizeHeader(w.Name);
                   return normalized.Contains("PARCELAMENTO") || normalized.Contains("PARCELAMENTOS") || normalized.Contains("PARECALEMENTO");
               });
    }

    private static List<ParcelamentoMonthValue> BuildMonthValuesFromMap(
        IXLWorksheet ws,
        int row,
        List<(int Column, int Year, int Month)> monthMap,
        Guid parcelamentoId,
        ParcelamentosImportSummaryDto summary,
        out Dictionary<string, decimal?> rawValues)
    {
        var monthValues = new List<ParcelamentoMonthValue>();
        rawValues = new Dictionary<string, decimal?>();
        foreach (var (column, year, month) in monthMap)
        {
            var value = GetCellDecimalValue(ws, row, column);
            rawValues[$"C{column}:{year}-{month:00}"] = value;
            if (!value.HasValue) continue;

            monthValues.Add(new ParcelamentoMonthValue
            {
                ParcelamentoLineId = parcelamentoId,
                Competencia = new DateOnly(year, month, 1),
                Valor = value,
                CreatedAt = DateTime.UtcNow
            });
            summary.ParcelasInseridas++;
        }

        return monthValues;
    }

    private static List<(int Column, int Year, int Month)> BuildDynamicMonthMap(
        IXLWorksheet ws,
        int headerRow,
        int colValorComDesconto,
        int lastCol)
    {
        var header = ws.Row(headerRow);
        var monthHeaders = new List<(int Col, int Month)>();

        foreach (var cell in header.CellsUsed())
        {
            var col = cell.Address.ColumnNumber;
            if (colValorComDesconto > 0 && col <= colValorComDesconto) continue;
            var month = ToMonthNumber(cell.GetString());
            if (month.HasValue)
            {
                monthHeaders.Add((col, month.Value));
            }
        }

        monthHeaders = monthHeaders.OrderBy(x => x.Col).ToList();
        if (monthHeaders.Count == 0) return new List<(int Column, int Year, int Month)>();

        var firstJanIdx = monthHeaders.FindIndex(x => x.Month == 1);
        if (firstJanIdx < 0) return new List<(int Column, int Year, int Month)>();

        var block1 = ExtractMonthBlock(monthHeaders, firstJanIdx);
        var block2 = block1.NextIndex < monthHeaders.Count
            ? ExtractMonthBlock(monthHeaders, block1.NextIndex)
            : (new List<(int Col, int Month)>(), block1.NextIndex);

        var yearRow = headerRow - 1;
        var year1 = block1.Block.Count > 0
            ? FindYearForBlock(ws, yearRow, block1.Block.First().Col, block1.Block.Last().Col, lastCol)
            : null;
        var year2 = block2.Block.Count > 0
            ? FindYearForBlock(ws, yearRow, block2.Block.First().Col, block2.Block.Last().Col, lastCol)
            : null;

        var map = new List<(int Column, int Year, int Month)>();
        if (year1.HasValue)
        {
            foreach (var (col, month) in block1.Block)
                map.Add((col, year1.Value, month));
        }

        if (year2.HasValue)
        {
            foreach (var (col, month) in block2.Block)
                map.Add((col, year2.Value, month));
        }

        return map;
    }

    private static (List<(int Col, int Month)> Block, int NextIndex) ExtractMonthBlock(
        List<(int Col, int Month)> monthHeaders,
        int startIndex)
    {
        var block = new List<(int Col, int Month)>();
        var idx = startIndex;
        for (int m = 1; m <= 12; m++)
        {
            while (idx < monthHeaders.Count && monthHeaders[idx].Month != m)
                idx++;
            if (idx >= monthHeaders.Count) break;
            block.Add(monthHeaders[idx]);
            idx++;
        }
        return (block, idx);
    }

    private static int? FindYearForBlock(IXLWorksheet ws, int yearRow, int startCol, int endCol, int lastCol)
    {
        if (yearRow < 1) return null;

        for (int col = startCol; col <= endCol; col++)
        {
            var year = TryParseYear(GetCellStringValue(ws, yearRow, col));
            if (year.HasValue) return year;
        }

        int? found = null;
        var bestDistance = int.MaxValue;
        for (int col = 1; col <= lastCol; col++)
        {
            var year = TryParseYear(GetCellStringValue(ws, yearRow, col));
            if (!year.HasValue) continue;
            var dist = Math.Abs(col - startCol);
            if (dist < bestDistance)
            {
                bestDistance = dist;
                found = year;
            }
        }

        return found;
    }

    private static int? TryParseYear(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return null;
        var digits = new string(s.Where(char.IsDigit).ToArray());
        if (digits.Length == 4 && int.TryParse(digits, out var year)) return year;
        if (digits.Length > 4)
        {
            for (int i = 0; i <= digits.Length - 4; i++)
            {
                if (int.TryParse(digits.Substring(i, 4), out var y) && y >= 2000 && y <= 2100)
                    return y;
            }
        }
        return null;
    }

    private static bool TryFindParcelamentosHeader(
        IXLWorksheet ws,
        out int headerRow,
        out Dictionary<string, int> map,
        out int lastCol)
    {
        headerRow = 0;
        map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        var firstRow = ws.FirstRowUsed()?.RowNumber() ?? 1;
        var lastRow = ws.LastRowUsed()?.RowNumber() ?? firstRow;
        lastCol = ws.LastColumnUsed()?.ColumnNumber() ?? 1;

        for (int r = firstRow; r <= lastRow; r++)
        {
            var row = ws.Row(r);
            if (!row.CellsUsed().Any()) continue;
            var headerMap = BuildHeaderMap(row);
            if (!HasParcelamentosHeaders(row, headerMap)) continue;
            headerRow = r;
            map = headerMap;
            return true;
        }
        return false;
    }

    private static bool HasParcelamentosHeaders(IXLRow row, Dictionary<string, int> map)
    {
        var hasLinha = GetColAny(map, "LINHA") > 0;
        var hasCliente = GetColAny(map, "CLIENTE") > 0;
        var hasQt = GetColAny(map, "QT PARCELAS", "QT. PARCELAS", "QTPARCELAS") > 0;
        var hasCheio = GetColAny(map, "VALOR CHEIO", "VALORCHEIO") > 0;
        var hasDesc = GetColAny(map, "DESCONTO") > 0;
        var hasComDesc = GetColAny(map, "VALOR C/ DESCONTO", "VALOR C\\ DESCONTO", "VALORCOMDESCONTO", "VALOR C DESCONTO") > 0;

        var hasMonth = row.CellsUsed().Any(c => ToMonthNumber(c.GetString()).HasValue);

        return hasLinha && hasCliente && hasQt && hasCheio && hasDesc && hasComDesc && hasMonth;
    }

    private static bool IsValidParcelamentoRow(string? linhaValue, string? qtParcelas)
    {
        if (string.IsNullOrWhiteSpace(linhaValue)) return false;
        var digits = OnlyDigits(linhaValue);
        if (string.IsNullOrWhiteSpace(digits) || digits.Length < 8) return false;
        return !string.IsNullOrWhiteSpace(qtParcelas);
    }

    private static decimal? GetCellDecimalValue(IXLWorksheet ws, int row, int col)
    {
        if (col <= 0) return null;
        var cell = ws.Cell(row, col);
        if (cell.IsEmpty()) return null;

        if (cell.DataType == XLDataType.Number)
        {
            if (cell.TryGetValue<double>(out var dbl))
                return Convert.ToDecimal(dbl);
        }

        if (cell.TryGetValue<decimal>(out var dec))
            return dec;

        var s = cell.GetValue<string>();
        return TryDecimal(s);
    }

    private static void AuditParcelamentoRow(
        int row,
        decimal? valorCheio,
        decimal? desconto,
        decimal? valorComDesconto,
        Dictionary<string, decimal?> monthValues,
        ParcelamentosImportSummaryDto summary)
    {
        var issues = new List<string>();

        if (valorCheio.HasValue && desconto.HasValue && desconto.Value > 0 && valorComDesconto.HasValue
            && Math.Abs(valorCheio.Value - valorComDesconto.Value) < 0.01m)
        {
            issues.Add("ValorCheio == ValorComDesconto com desconto > 0");
        }

        if (valorCheio.HasValue && desconto.HasValue && valorComDesconto.HasValue)
        {
            var esperado = valorCheio.Value - desconto.Value;
            if (Math.Abs(esperado - valorComDesconto.Value) > 0.05m)
            {
                issues.Add("ValorComDesconto difere de ValorCheio - Desconto");
            }
        }

        if (valorCheio.HasValue && monthValues.Values.Any(v => v.HasValue && v.Value == valorCheio.Value))
        {
            issues.Add("ValorCheio igual a valor mensal (possível shift de coluna)");
        }

        var monthNonNull = monthValues.Values.Where(v => v.HasValue).Select(v => v!.Value).ToList();
        if (monthNonNull.Count >= 4 && monthNonNull.Distinct().Count() == 1)
        {
            issues.Add("Meses iguais (possível parse/shift)");
        }

        if (issues.Count == 0) return;

        var mapped = $"ValorCheio={valorCheio} Desconto={desconto} ValorComDesconto={valorComDesconto}";
        var raw = string.Join(" ", monthValues.Select(k => $"{k.Key}={k.Value}"));
        Console.WriteLine($"[AUDITORIA PARCELAMENTOS] Linha {row} | {string.Join(" | ", issues)} | {mapped} | {raw}");

        summary.Erros.Add(new ParcelamentosImportErrorDto
        {
            LinhaExcel = row,
            Motivo = $"Auditoria: {string.Join(" | ", issues)}",
            Valor = mapped
        });
    }
    private static string GetCellStringValue(IXLWorksheet ws, int row, int col)
    {
        if (col <= 0) return "";
        var cell = ws.Cell(row, col);
        if (cell.IsEmpty()) return "";
        return (cell.GetValue<string>() ?? "").Trim();
    }

    private static void ParseParcelas(string? qtParcelas, out int? parcelaAtual, out int? totalParcelas)
    {
        parcelaAtual = null;
        totalParcelas = null;
        if (string.IsNullOrWhiteSpace(qtParcelas))
        {
            return;
        }

        var parts = qtParcelas.Split('/', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
        if (parts.Length >= 1 && int.TryParse(OnlyDigits(parts[0]), out var atual))
        {
            parcelaAtual = atual;
        }

        if (parts.Length >= 2 && int.TryParse(OnlyDigits(parts[1]), out var total))
        {
            totalParcelas = total;
        }
    }

    private static Dictionary<string, int> BuildHeaderMap(IXLRow headerRow)
    {
        var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        foreach (var cell in headerRow.CellsUsed())
        {
            var k = NormalizeHeader(cell.GetString());
            if (!string.IsNullOrWhiteSpace(k) && !map.ContainsKey(k))
                map[k] = cell.Address.ColumnNumber;
        }
        return map;
    }

    private static int GetCol(Dictionary<string, int> map, string name)
        => map.TryGetValue(NormalizeHeader(name), out var c) ? c : 0;

    private static int GetColAny(Dictionary<string, int> map, params string[] headers)
    {
        foreach (var h in headers)
        {
            var k = NormalizeHeader(h);
            if (map.TryGetValue(k, out var c)) return c;
        }
        return 0;
    }

    private static string GetCellString(IXLWorksheet ws, int row, int col)
    {
        if (col <= 0) return "";
        return (ws.Cell(row, col).GetValue<string>() ?? "").Trim();
    }

    private static string? NormalizeText(string value)
        => string.IsNullOrWhiteSpace(value) ? null : value.Trim();

    private static decimal? TryDecimal(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return null;

        s = s.Replace("R$", "", StringComparison.OrdinalIgnoreCase).Trim();

        if (decimal.TryParse(s, NumberStyles.Any, new CultureInfo("pt-BR"), out var d)) return d;
        if (decimal.TryParse(s, NumberStyles.Any, CultureInfo.InvariantCulture, out d)) return d;

        var s2 = s.Replace(".", "").Replace(",", ".");
        if (decimal.TryParse(s2, NumberStyles.Any, CultureInfo.InvariantCulture, out d)) return d;

        return null;
    }

    private static int? TryNullableInt(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return null;
        var d = OnlyDigits(s);
        if (string.IsNullOrWhiteSpace(d)) return null;
        return int.TryParse(d, out var n) ? n : null;
    }

    private static string OnlyDigits(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return "";
        var sb = new StringBuilder();
        foreach (var c in s) if (char.IsDigit(c)) sb.Append(c);
        return sb.ToString();
    }

    private static int? ToMonthNumber(string? month)
    {
        if (string.IsNullOrWhiteSpace(month)) return null;
        return NormalizeHeader(month) switch
        {
            "JAN" => 1,
            "FEV" => 2,
            "MAR" => 3,
            "ABR" => 4,
            "MAI" => 5,
            "JUN" => 6,
            "JUL" => 7,
            "AGO" => 8,
            "SET" => 9,
            "OUT" => 10,
            "NOV" => 11,
            "DEZ" => 12,
            _ => null
        };
    }

    private static string NormalizeHeader(string? s)
    {
        if (string.IsNullOrWhiteSpace(s)) return "";
        s = s.Trim().ToUpperInvariant().Normalize(NormalizationForm.FormD);

        var sb = new StringBuilder();
        foreach (var c in s)
            if (CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
                sb.Append(c);

        return sb.ToString()
                 .Normalize(NormalizationForm.FormC)
                 .Replace(" ", "")
                 .Replace("\t", "")
                 .Replace("\n", "")
                 .Replace("\r", "");
    }
}