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);
        }

        const int startRow = 6;
        const int colAnoRef = 3;
        const int colItem = 4;
        const int colLinha = 5;
        const int colCliente = 6;
        const int colQtParcelas = 7;
        const int colValorCheio = 8;
        const int colDesconto = 9;
        const int colValorComDesconto = 10;
        var monthMap = BuildFixedMonthMap();

        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 lastRow = ws.LastRowUsed()?.RowNumber() ?? startRow;

        for (int row = startRow; row <= lastRow; row++)
        {
            var linhaValue = GetCellStringValue(ws, row, colLinha);
            var itemStr = GetCellStringValue(ws, row, colItem);
            var anoRefStr = GetCellStringValue(ws, row, colAnoRef);
            if (string.IsNullOrWhiteSpace(itemStr)
                && string.IsNullOrWhiteSpace(linhaValue)
                && string.IsNullOrWhiteSpace(anoRefStr))
            {
                continue;
            }

            summary.Lidos++;

            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;
            }

            var qtParcelas = GetCellStringValue(ws, row, colQtParcelas);
            ParseParcelas(qtParcelas, out var parcelaAtual, out var totalParcelas);

            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 = TryDecimal(GetCellStringValue(ws, row, colValorCheio)),
                Desconto = TryDecimal(GetCellStringValue(ws, row, colDesconto)),
                ValorComDesconto = TryDecimal(GetCellStringValue(ws, row, colValorComDesconto)),
                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);
                    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);
            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<(int Column, int Year, int Month)> BuildFixedMonthMap()
    {
        var map = new List<(int Column, int Year, int Month)>
        {
            (11, 2025, 12)
        };

        for (int month = 1; month <= 12; month++)
        {
            map.Add((11 + month, 2026, month));
        }

        for (int month = 1; month <= 6; month++)
        {
            map.Add((23 + month, 2027, month));
        }

        return map;
    }

    private static List<ParcelamentoMonthValue> BuildMonthValuesFromMap(
        IXLWorksheet ws,
        int row,
        List<(int Column, int Year, int Month)> monthMap,
        Guid parcelamentoId,
        ParcelamentosImportSummaryDto summary)
    {
        var monthValues = new List<ParcelamentoMonthValue>();
        foreach (var (column, year, month) in monthMap)
        {
            var valueStr = GetCellStringValue(ws, row, column);
            var value = TryDecimal(valueStr);
            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 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", "");
    }
}