using ClosedXML.Excel;
using line_gestao_api.Data;
using line_gestao_api.Dtos; // Certifique-se que ClientGroupDto está neste namespace
using line_gestao_api.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Globalization;
using System.Text;

namespace line_gestao_api.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    //[Authorize] // Descomente se estiver usando autenticação
    public class LinesController : ControllerBase
    {
        private readonly AppDbContext _db;

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

        // Classe auxiliar apenas para o upload (não é DTO de banco)
        public class ImportExcelForm
        {
            public IFormFile File { get; set; } = default!;
        }

        // ==========================================================
        // ✅ 1. NOVO ENDPOINT: AGRUPAR POR CLIENTE (Resumo para Aba 'Todos')
        // ==========================================================
        [HttpGet("groups")]
        public async Task<ActionResult<List<ClientGroupDto>>> GetClientGroups()
        {
            // Agrupa por nome do cliente e calcula os totais
            var groups = await _db.MobileLines
                .AsNoTracking()
                .Where(x => !string.IsNullOrEmpty(x.Cliente))
                .GroupBy(x => x.Cliente)
                .Select(g => new ClientGroupDto
                {
                    Cliente = g.Key!,
                    TotalLinhas = g.Count(),
                    // Conta quantos contêm "ativo" (ignorando maiúsculas/minúsculas)
                    Ativos = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%ativo%")),
                    // Conta quantos contêm "bloque" ou similar
                    Bloqueados = g.Count(x => EF.Functions.ILike(x.Status ?? "", "%bloque%") || EF.Functions.ILike(x.Status ?? "", "%perda%") || EF.Functions.ILike(x.Status ?? "", "%roubo%"))
                })
                .OrderBy(x => x.Cliente)
                .ToListAsync();

            return Ok(groups);
        }

        // ==========================================================
        // ✅ 2. ENDPOINT: LISTAR NOMES DE CLIENTES (Para o Dropdown)
        // ==========================================================
        [HttpGet("clients")]
        public async Task<ActionResult<List<string>>> GetClients()
        {
            var clients = await _db.MobileLines
                .AsNoTracking()
                .Select(x => x.Cliente)
                .Where(x => !string.IsNullOrEmpty(x))
                .Distinct()
                .OrderBy(x => x)
                .ToListAsync();

            return Ok(clients);
        }

        // ==========================================================
        // ✅ 3. GET ALL (TABELA PRINCIPAL - Com todos os filtros)
        // ==========================================================
        [HttpGet]
        public async Task<ActionResult<PagedResult<MobileLineListDto>>> GetAll(
            [FromQuery] string? search,
            [FromQuery] string? skil,   // Filtro PF/PJ
            [FromQuery] string? client, // Filtro Cliente Específico
            [FromQuery] int page = 1,
            [FromQuery] int pageSize = 20,
            [FromQuery] string? sortBy = "item",
            [FromQuery] string? sortDir = "asc")
        {
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 20 : pageSize;

            var q = _db.MobileLines.AsNoTracking();

            // 1. Filtro por SKIL (PF/PJ)
            if (!string.IsNullOrWhiteSpace(skil))
            {
                var sSkil = skil.Trim();
                q = q.Where(x => EF.Functions.ILike(x.Skil ?? "", $"%{sSkil}%"));
            }

            // 2. Filtro por Cliente Específico (usado no dropdown e no accordion)
            if (!string.IsNullOrWhiteSpace(client))
            {
                var sClient = client.Trim();
                q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", sClient));
            }

            // 3. Busca Genérica (Barra de pesquisa)
            if (!string.IsNullOrWhiteSpace(search))
            {
                var s = search.Trim();
                q = q.Where(x =>
                    EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Chip ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Status ?? "", $"%{s}%"));
            }

            var total = await q.CountAsync();

            // Ordenação
            var sb = (sortBy ?? "item").Trim().ToLowerInvariant();
            var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);

            if (sb == "plano") sb = "planocontrato";
            if (sb == "contrato") sb = "vencconta";

            q = sb switch
            {
                "conta" => desc ? q.OrderByDescending(x => x.Conta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Conta ?? "").ThenBy(x => x.Item),
                "linha" => desc ? q.OrderByDescending(x => x.Linha ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Linha ?? "").ThenBy(x => x.Item),
                "chip" => desc ? q.OrderByDescending(x => x.Chip ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Chip ?? "").ThenBy(x => x.Item),
                "cliente" => desc ? q.OrderByDescending(x => x.Cliente ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Cliente ?? "").ThenBy(x => x.Item),
                "usuario" => desc ? q.OrderByDescending(x => x.Usuario ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Usuario ?? "").ThenBy(x => x.Item),
                "planocontrato" => desc ? q.OrderByDescending(x => x.PlanoContrato ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.PlanoContrato ?? "").ThenBy(x => x.Item),
                "vencconta" => desc ? q.OrderByDescending(x => x.VencConta ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.VencConta ?? "").ThenBy(x => x.Item),
                "status" => desc ? q.OrderByDescending(x => x.Status ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Status ?? "").ThenBy(x => x.Item),
                "skil" => desc ? q.OrderByDescending(x => x.Skil ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Skil ?? "").ThenBy(x => x.Item),
                "modalidade" => desc ? q.OrderByDescending(x => x.Modalidade ?? "").ThenBy(x => x.Item) : q.OrderBy(x => x.Modalidade ?? "").ThenBy(x => x.Item),
                _ => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item)
            };

            var items = await q
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .Select(x => new MobileLineListDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    Conta = x.Conta,
                    Linha = x.Linha,
                    Chip = x.Chip,
                    Cliente = x.Cliente,
                    Usuario = x.Usuario,
                    PlanoContrato = x.PlanoContrato,
                    Status = x.Status,
                    Skil = x.Skil,
                    Modalidade = x.Modalidade,
                    VencConta = x.VencConta
                })
                .ToListAsync();

            return Ok(new PagedResult<MobileLineListDto>
            {
                Page = page,
                PageSize = pageSize,
                Total = total,
                Items = items
            });
        }

        // ==========================================================
        // OBTER DETALHES POR ID
        // ==========================================================
        [HttpGet("{id:guid}")]
        public async Task<ActionResult<MobileLineDetailDto>> GetById(Guid id)
        {
            var x = await _db.MobileLines.AsNoTracking().FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();

            return Ok(ToDetailDto(x));
        }

        // ==========================================================
        // ATUALIZAR (PUT)
        // ==========================================================
        [HttpPut("{id:guid}")]
        public async Task<IActionResult> Update(Guid id, [FromBody] UpdateMobileLineRequest req)
        {
            var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();

            var newLinha = OnlyDigits(req.Linha);
            var newChip = OnlyDigits(req.Chip);

            // Verifica duplicidade de linha (se alterou a linha)
            if (!string.IsNullOrWhiteSpace(newLinha) &&
                !string.Equals((x.Linha ?? ""), newLinha, StringComparison.Ordinal))
            {
                var exists = await _db.MobileLines.AsNoTracking()
                    .AnyAsync(m => m.Linha == newLinha && m.Id != id);

                if (exists)
                {
                    return Conflict(new
                    {
                        message = "Já existe um registro com essa LINHA. Não é possível salvar duplicado.",
                        linha = newLinha
                    });
                }
            }

            // Atualiza campos
            x.Item = req.Item;
            x.Conta = req.Conta?.Trim();
            x.Linha = string.IsNullOrWhiteSpace(newLinha) ? null : newLinha;
            x.Chip = string.IsNullOrWhiteSpace(newChip) ? null : newChip;
            x.Cliente = req.Cliente?.Trim();
            x.Usuario = req.Usuario?.Trim();
            x.PlanoContrato = req.PlanoContrato?.Trim();

            x.FranquiaVivo = req.FranquiaVivo;
            x.ValorPlanoVivo = req.ValorPlanoVivo;
            x.GestaoVozDados = req.GestaoVozDados;
            x.Skeelo = req.Skeelo;
            x.VivoNewsPlus = req.VivoNewsPlus;
            x.VivoTravelMundo = req.VivoTravelMundo;
            x.VivoGestaoDispositivo = req.VivoGestaoDispositivo;
            x.ValorContratoVivo = req.ValorContratoVivo;

            x.FranquiaLine = req.FranquiaLine;
            x.FranquiaGestao = req.FranquiaGestao;
            x.LocacaoAp = req.LocacaoAp;
            x.ValorContratoLine = req.ValorContratoLine;

            x.Desconto = req.Desconto;
            x.Lucro = req.Lucro;

            x.Status = req.Status?.Trim();
            x.DataBloqueio = ToUtc(req.DataBloqueio);

            x.Skil = req.Skil?.Trim();
            x.Modalidade = req.Modalidade?.Trim();
            x.Cedente = req.Cedente?.Trim();
            x.Solicitante = req.Solicitante?.Trim();

            x.DataEntregaOpera = ToUtc(req.DataEntregaOpera);
            x.DataEntregaCliente = ToUtc(req.DataEntregaCliente);
            x.VencConta = req.VencConta?.Trim();

            ApplyReservaRule(x);

            x.UpdatedAt = DateTime.UtcNow;

            try
            {
                await _db.SaveChangesAsync();
            }
            catch (DbUpdateException)
            {
                return Conflict(new
                {
                    message = "Conflito ao salvar. Verifique se a LINHA já existe em outro registro."
                });
            }

            return NoContent();
        }

        // ==========================================================
        // DELETAR (DELETE)
        // ==========================================================
        [HttpDelete("{id:guid}")]
        public async Task<IActionResult> Delete(Guid id)
        {
            var x = await _db.MobileLines.FirstOrDefaultAsync(a => a.Id == id);
            if (x == null) return NotFound();

            _db.MobileLines.Remove(x);
            await _db.SaveChangesAsync();
            return NoContent();
        }

        // ==========================================================
        // IMPORTAR EXCEL
        // ==========================================================
        [HttpPost("import-excel")]
        [Consumes("multipart/form-data")]
        [RequestSizeLimit(50_000_000)]
        public async Task<ActionResult<ImportResultDto>> ImportExcel([FromForm] ImportExcelForm form)
        {
            var file = form.File;

            if (file == null || file.Length == 0)
                return BadRequest("Arquivo inválido.");

            using var stream = file.OpenReadStream();
            using var wb = new XLWorkbook(stream);

            var ws = wb.Worksheets.FirstOrDefault(w => w.Name.Trim().Equals("GERAL", StringComparison.OrdinalIgnoreCase));
            if (ws == null)
                return BadRequest("Aba 'GERAL' não encontrada.");

            var headerRow = ws.RowsUsed().FirstOrDefault(r =>
                r.CellsUsed().Any(c => NormalizeHeader(c.GetString()) == "ITEM"));

            if (headerRow == null)
                return BadRequest("Cabeçalho da planilha (linha com 'ITÉM') não encontrado.");

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

            int colItem = GetCol(map, "ITEM");
            if (colItem == 0) return BadRequest("Coluna 'ITÉM' não encontrada.");

            var startRow = headerRow.RowNumber() + 1;

            // Opção: Deletar tudo antes de importar (Cuidado em produção!)
            await _db.MobileLines.ExecuteDeleteAsync();

            var imported = 0;
            var buffer = new List<MobileLine>(600);

            for (int r = startRow; r <= ws.LastRowUsed().RowNumber(); r++)
            {
                var itemStr = GetCellString(ws, r, colItem);
                if (string.IsNullOrWhiteSpace(itemStr)) break;

                var entity = new MobileLine
                {
                    Item = TryInt(itemStr),
                    Conta = GetCellByHeader(ws, r, map, "CONTA"),
                    Linha = OnlyDigits(GetCellByHeader(ws, r, map, "LINHA")),
                    Chip = OnlyDigits(GetCellByHeader(ws, r, map, "CHIP")),
                    Cliente = GetCellByHeader(ws, r, map, "CLIENTE"),
                    Usuario = GetCellByHeader(ws, r, map, "USUARIO"),
                    PlanoContrato = GetCellByHeader(ws, r, map, "PLANO CONTRATO"),
                    FranquiaVivo = TryDecimal(GetCellByHeader(ws, r, map, "FRAQUIA")),
                    ValorPlanoVivo = TryDecimal(GetCellByHeader(ws, r, map, "VALOR DO PLANO R$")),
                    GestaoVozDados = TryDecimal(GetCellByHeader(ws, r, map, "GESTAO VOZ E DADOS R$")),
                    Skeelo = TryDecimal(GetCellByHeader(ws, r, map, "SKEELO")),
                    VivoNewsPlus = TryDecimal(GetCellByHeader(ws, r, map, "VIVO NEWS PLUS")),
                    VivoTravelMundo = TryDecimal(GetCellByHeader(ws, r, map, "VIVO TRAVEL MUNDO")),
                    VivoGestaoDispositivo = TryDecimal(GetCellByHeader(ws, r, map, "VIVO GESTAO DISPOSITIVO")),
                    ValorContratoVivo = TryDecimal(GetCellByHeader(ws, r, map, "VALOR CONTRATO VIVO")),
                    FranquiaLine = TryDecimal(GetCellByHeader(ws, r, map, "FRANQUIA LINE")),
                    FranquiaGestao = TryDecimal(GetCellByHeader(ws, r, map, "FRANQUIA GESTAO")),
                    LocacaoAp = TryDecimal(GetCellByHeader(ws, r, map, "LOCACAO AP.")),
                    ValorContratoLine = TryDecimal(GetCellByHeader(ws, r, map, "VALOR CONTRATO LINE")),
                    Desconto = TryDecimal(GetCellByHeader(ws, r, map, "DESCONTO")),
                    Lucro = TryDecimal(GetCellByHeader(ws, r, map, "LUCRO")),
                    Status = GetCellByHeader(ws, r, map, "STATUS"),
                    DataBloqueio = TryDate(ws, r, map, "DATA DO BLOQUEIO"),
                    Skil = GetCellByHeader(ws, r, map, "SKIL"),
                    Modalidade = GetCellByHeader(ws, r, map, "MODALIDADE"),
                    Cedente = GetCellByHeader(ws, r, map, "CEDENTE"),
                    Solicitante = GetCellByHeader(ws, r, map, "SOLICITANTE"),
                    DataEntregaOpera = TryDate(ws, r, map, "DATA DA ENTREGA OPERA."),
                    DataEntregaCliente = TryDate(ws, r, map, "DATA DA ENTREGA CLIENTE"),
                    VencConta = GetCellByHeader(ws, r, map, "VENC. DA CONTA"),
                };

                ApplyReservaRule(entity);
                buffer.Add(entity);
                imported++;

                if (buffer.Count >= 500)
                {
                    await _db.MobileLines.AddRangeAsync(buffer);
                    await _db.SaveChangesAsync();
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
            {
                await _db.MobileLines.AddRangeAsync(buffer);
                await _db.SaveChangesAsync();
            }

            return Ok(new ImportResultDto { Imported = imported });
        }

        // ==========================================================
        // HELPERS
        // ==========================================================

        private static DateTime? ToUtc(DateTime? dt)
        {
            if (dt == null) return null;
            var v = dt.Value;
            return v.Kind switch
            {
                DateTimeKind.Utc => v,
                DateTimeKind.Local => v.ToUniversalTime(),
                _ => DateTime.SpecifyKind(v, DateTimeKind.Utc)
            };
        }

        private static MobileLineDetailDto ToDetailDto(MobileLine x) => new()
        {
            Id = x.Id,
            Item = x.Item,
            Conta = x.Conta,
            Linha = x.Linha,
            Chip = x.Chip,
            Cliente = x.Cliente,
            Usuario = x.Usuario,
            PlanoContrato = x.PlanoContrato,
            FranquiaVivo = x.FranquiaVivo,
            ValorPlanoVivo = x.ValorPlanoVivo,
            GestaoVozDados = x.GestaoVozDados,
            Skeelo = x.Skeelo,
            VivoNewsPlus = x.VivoNewsPlus,
            VivoTravelMundo = x.VivoTravelMundo,
            VivoGestaoDispositivo = x.VivoGestaoDispositivo,
            ValorContratoVivo = x.ValorContratoVivo,
            FranquiaLine = x.FranquiaLine,
            FranquiaGestao = x.FranquiaGestao,
            LocacaoAp = x.LocacaoAp,
            ValorContratoLine = x.ValorContratoLine,
            Desconto = x.Desconto,
            Lucro = x.Lucro,
            Status = x.Status,
            DataBloqueio = x.DataBloqueio,
            Skil = x.Skil,
            Modalidade = x.Modalidade,
            Cedente = x.Cedente,
            Solicitante = x.Solicitante,
            DataEntregaOpera = x.DataEntregaOpera,
            DataEntregaCliente = x.DataEntregaCliente,
            VencConta = x.VencConta
        };

        private static void ApplyReservaRule(MobileLine x)
        {
            var cliente = (x.Cliente ?? "").Trim();
            var usuario = (x.Usuario ?? "").Trim();
            if (cliente.Equals("RESERVA", StringComparison.OrdinalIgnoreCase) ||
                usuario.Equals("RESERVA", StringComparison.OrdinalIgnoreCase))
            {
                x.Cliente = "RESERVA";
                x.Usuario = "RESERVA";
                x.Skil = "RESERVA";
            }
        }

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

        private static string GetCellByHeader(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
        {
            var key = NormalizeHeader(header);
            if (!map.TryGetValue(key, out var col)) return "";
            return GetCellString(ws, row, col);
        }

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

        private static DateTime? TryDate(IXLWorksheet ws, int row, Dictionary<string, int> map, string header)
        {
            var key = NormalizeHeader(header);
            if (!map.TryGetValue(key, out var col)) return null;

            var cell = ws.Cell(row, col);
            if (cell.DataType == XLDataType.DateTime)
                return ToUtc(cell.GetDateTime());

            var s = cell.GetValue<string>()?.Trim();
            if (string.IsNullOrWhiteSpace(s)) return null;

            if (DateTime.TryParse(s, new CultureInfo("pt-BR"), DateTimeStyles.None, out var d))
                return ToUtc(d);

            if (DateTime.TryParse(s, CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
                return ToUtc(d);

            return null;
        }

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

        private static int TryInt(string s) => int.TryParse(OnlyDigits(s), out var n) ? n : 0;

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

        private static string NormalizeHeader(string? s)
        {
            if (string.IsNullOrWhiteSpace(s)) return "";
            s = s.Trim().ToUpperInvariant();
            var formD = s.Normalize(NormalizationForm.FormD);
            var sb = new StringBuilder();
            foreach (var ch in formD)
                if (System.Globalization.CharUnicodeInfo.GetUnicodeCategory(ch) != System.Globalization.UnicodeCategory.NonSpacingMark)
                    sb.Append(ch);

            s = sb.ToString().Normalize(NormalizationForm.FormC);
            s = s.Replace("ITEM", "ITEM")
                 .Replace("USUARIO", "USUARIO")
                 .Replace("GESTAO", "GESTAO")
                 .Replace("LOCACAO", "LOCACAO");

            s = string.Join(" ", s.Split(' ', StringSplitOptions.RemoveEmptyEntries));
            return s;
        }
    }
}