using line_gestao_api.Data;
using line_gestao_api.Dtos;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace line_gestao_api.Controllers
{
    [ApiController]
    [Route("api/relatorios")]
    [Authorize]
    public class RelatoriosController : ControllerBase
    {
        private readonly AppDbContext _db;

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

        [HttpGet("dashboard")]
        public async Task<ActionResult<RelatoriosDashboardDto>> GetDashboard()
        {
            var today = DateTime.UtcNow.Date;
            var last30 = today.AddDays(-30);
            var limit30 = today.AddDays(30);

            var minUtc = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

            // =========================
            // GERAL (MobileLines)
            // =========================
            var qLines = _db.MobileLines.AsNoTracking();

            var totalLinhas = await qLines.CountAsync();

            var clientesUnicos = await qLines
                .Where(x => x.Cliente != null && x.Cliente != "")
                .Select(x => x.Cliente!)
                .Distinct()
                .CountAsync();

            var ativos = await qLines.CountAsync(x =>
                EF.Functions.ILike((x.Status ?? "").Trim(), "%ativo%"));

            var bloqueadosPerdaRoubo = await qLines.CountAsync(x =>
                EF.Functions.ILike((x.Status ?? "").Trim(), "%perda%") ||
                EF.Functions.ILike((x.Status ?? "").Trim(), "%roubo%"));

            var bloqueados120Dias = await qLines.CountAsync(x =>
                EF.Functions.ILike((x.Status ?? "").Trim(), "%bloque%") &&
                EF.Functions.ILike((x.Status ?? "").Trim(), "%120%") &&
                EF.Functions.ILike((x.Status ?? "").Trim(), "%dia%"));

            var bloqueadosOutros = await qLines.CountAsync(x =>
                EF.Functions.ILike((x.Status ?? "").Trim(), "%bloque%") &&
                !(EF.Functions.ILike((x.Status ?? "").Trim(), "%120%") && EF.Functions.ILike((x.Status ?? "").Trim(), "%dia%")) &&
                !(EF.Functions.ILike((x.Status ?? "").Trim(), "%perda%") || EF.Functions.ILike((x.Status ?? "").Trim(), "%roubo%"))
            );

            var bloqueados = bloqueadosPerdaRoubo + bloqueados120Dias + bloqueadosOutros;

            var reservas = await qLines.CountAsync(x =>
                EF.Functions.ILike((x.Cliente ?? "").Trim(), "%RESERVA%") ||
                EF.Functions.ILike((x.Usuario ?? "").Trim(), "%RESERVA%") ||
                EF.Functions.ILike((x.Skil ?? "").Trim(), "%RESERVA%"));

            var topClientes = await qLines
                .Where(x => x.Cliente != null && x.Cliente != "")
                .GroupBy(x => x.Cliente!)
                .Select(g => new TopClienteDto
                {
                    Cliente = g.Key,
                    Linhas = g.Count()
                })
                .OrderByDescending(x => x.Linhas)
                .ThenBy(x => x.Cliente)
                .Take(10)
                .ToListAsync();

            // =========================
            // MUREG
            // =========================
            var qMureg = _db.MuregLines.AsNoTracking().Include(x => x.MobileLine);

            var totalMuregs = await qMureg.CountAsync();

            var muregsUltimos30 = await qMureg.CountAsync(x =>
                x.DataDaMureg != null && x.DataDaMureg.Value.Date >= last30);

            var muregsRecentes = await qMureg
                .OrderByDescending(x => x.DataDaMureg ?? minUtc)
                .ThenByDescending(x => x.Item)
                .Take(10)
                .Select(x => new MuregRecenteDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    LinhaAntiga = x.LinhaAntiga,
                    LinhaNova = x.LinhaNova,
                    ICCID = x.ICCID,
                    DataDaMureg = x.DataDaMureg,
                    Cliente = x.MobileLine != null ? x.MobileLine.Cliente : null,
                    MobileLineId = x.MobileLineId
                })
                .ToListAsync();

            var serieMureg12 = await BuildSerieUltimos12Meses_Mureg(today);

            // =========================
            // TROCA DE NÚMERO
            // =========================
            var qTroca = _db.TrocaNumeroLines.AsNoTracking();

            var totalTrocas = await qTroca.CountAsync();

            var trocasUltimos30 = await qTroca.CountAsync(x =>
                x.DataTroca != null && x.DataTroca.Value.Date >= last30);

            var trocasRecentes = await qTroca
                .OrderByDescending(x => x.DataTroca ?? minUtc)
                .ThenByDescending(x => x.Item)
                .Take(10)
                .Select(x => new TrocaRecenteDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    LinhaAntiga = x.LinhaAntiga,
                    LinhaNova = x.LinhaNova,
                    ICCID = x.ICCID,
                    DataTroca = x.DataTroca,
                    Motivo = x.Motivo
                })
                .ToListAsync();

            var serieTroca12 = await BuildSerieUltimos12Meses_Troca(today);

            // =========================
            // VIGÊNCIA
            // =========================
            var qVig = _db.VigenciaLines.AsNoTracking();

            var totalVig = await qVig.CountAsync();

            var vigVencidos = await qVig.CountAsync(x =>
                x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date < today);

            var vigAVencer30 = await qVig.CountAsync(x =>
                x.DtTerminoFidelizacao != null &&
                x.DtTerminoFidelizacao.Value.Date >= today &&
                x.DtTerminoFidelizacao.Value.Date <= limit30);

            // ✅ NOVO: série próximos 12 meses (mês/ano)
            var serieVigProx12 = await BuildSerieProximos12Meses_VigenciaEncerramentos(today);

            // ✅ NOVO: buckets de supervisão
            var vigBuckets = await BuildVigenciaBuckets(today);

            // =========================
            // USER DATA
            // =========================
            var qUserData = _db.UserDatas.AsNoTracking();

            var userDataRegistros = await qUserData.CountAsync();
            var userDataComCpf = await qUserData.CountAsync(x => x.Cpf != null && x.Cpf != "");
            var userDataComEmail = await qUserData.CountAsync(x => x.Email != null && x.Email != "");

            // =========================
            // RESPOSTA
            // =========================
            var dto = new RelatoriosDashboardDto
            {
                Kpis = new DashboardKpisDto
                {
                    TotalLinhas = totalLinhas,
                    ClientesUnicos = clientesUnicos,
                    Ativos = ativos,

                    Bloqueados = bloqueados,
                    BloqueadosPerdaRoubo = bloqueadosPerdaRoubo,
                    Bloqueados120Dias = bloqueados120Dias,
                    BloqueadosOutros = bloqueadosOutros,

                    Reservas = reservas,

                    TotalMuregs = totalMuregs,
                    MuregsUltimos30Dias = muregsUltimos30,

                    TotalVigenciaLinhas = totalVig,
                    VigenciaVencidos = vigVencidos,
                    VigenciaAVencer30 = vigAVencer30,

                    TotalTrocas = totalTrocas,
                    TrocasUltimos30Dias = trocasUltimos30,

                    UserDataRegistros = userDataRegistros,
                    UserDataComCpf = userDataComCpf,
                    UserDataComEmail = userDataComEmail
                },
                TopClientes = topClientes,
                SerieMuregUltimos12Meses = serieMureg12,
                SerieTrocaUltimos12Meses = serieTroca12,
                MuregsRecentes = muregsRecentes,
                TrocasRecentes = trocasRecentes,

                SerieVigenciaEncerramentosProx12Meses = serieVigProx12,
                VigenciaBuckets = vigBuckets
            };

            return Ok(dto);
        }

        // =========================
        // Helpers
        // =========================
        private async Task<List<SerieMesDto>> BuildSerieUltimos12Meses_Mureg(DateTime todayUtcDate)
        {
            var start = new DateTime(todayUtcDate.Year, todayUtcDate.Month, 1, 0, 0, 0, DateTimeKind.Utc)
                .AddMonths(-11);

            var raw = await _db.MuregLines.AsNoTracking()
                .Where(x => x.DataDaMureg != null && x.DataDaMureg.Value >= start)
                .GroupBy(x => new { x.DataDaMureg!.Value.Year, x.DataDaMureg!.Value.Month })
                .Select(g => new { g.Key.Year, g.Key.Month, Total = g.Count() })
                .ToListAsync();

            return Fill12Months(start, raw.Select(r => (r.Year, r.Month, r.Total)));
        }

        private async Task<List<SerieMesDto>> BuildSerieUltimos12Meses_Troca(DateTime todayUtcDate)
        {
            var start = new DateTime(todayUtcDate.Year, todayUtcDate.Month, 1, 0, 0, 0, DateTimeKind.Utc)
                .AddMonths(-11);

            var raw = await _db.TrocaNumeroLines.AsNoTracking()
                .Where(x => x.DataTroca != null && x.DataTroca.Value >= start)
                .GroupBy(x => new { x.DataTroca!.Value.Year, x.DataTroca!.Value.Month })
                .Select(g => new { g.Key.Year, g.Key.Month, Total = g.Count() })
                .ToListAsync();

            return Fill12Months(start, raw.Select(r => (r.Year, r.Month, r.Total)));
        }

        // ✅ série próximos 12 meses (vigência encerrando)
        private async Task<List<SerieMesDto>> BuildSerieProximos12Meses_VigenciaEncerramentos(DateTime todayUtcDate)
        {
            var start = new DateTime(todayUtcDate.Year, todayUtcDate.Month, 1, 0, 0, 0, DateTimeKind.Utc);
            var end = start.AddMonths(12);

            var raw = await _db.VigenciaLines.AsNoTracking()
                .Where(x => x.DtTerminoFidelizacao != null &&
                            x.DtTerminoFidelizacao.Value >= start &&
                            x.DtTerminoFidelizacao.Value < end)
                .GroupBy(x => new { x.DtTerminoFidelizacao!.Value.Year, x.DtTerminoFidelizacao!.Value.Month })
                .Select(g => new { g.Key.Year, g.Key.Month, Total = g.Count() })
                .ToListAsync();

            return Fill12MonthsForward(start, raw.Select(r => (r.Year, r.Month, r.Total)));
        }

        // ✅ buckets de supervisão de vigência
        private async Task<VigenciaBucketsDto> BuildVigenciaBuckets(DateTime todayUtcDate)
        {
            // Importante: DtTerminoFidelizacao pode ser null
            var rows = await _db.VigenciaLines.AsNoTracking()
                .Where(x => x.DtTerminoFidelizacao != null)
                .Select(x => x.DtTerminoFidelizacao!.Value)
                .ToListAsync();

            int vencidos = 0, a0_30 = 0, a31_60 = 0, a61_90 = 0, acima90 = 0;

            foreach (var dt in rows)
            {
                var days = (dt.Date - todayUtcDate).Days;

                if (days < 0) vencidos++;
                else if (days <= 30) a0_30++;
                else if (days <= 60) a31_60++;
                else if (days <= 90) a61_90++;
                else acima90++;
            }

            return new VigenciaBucketsDto
            {
                Vencidos = vencidos,
                AVencer0a30 = a0_30,
                AVencer31a60 = a31_60,
                AVencer61a90 = a61_90,
                Acima90 = acima90
            };
        }

        private static List<SerieMesDto> Fill12Months(DateTime startMonth, IEnumerable<(int year, int month, int total)> raw)
        {
            var dict = raw.ToDictionary(x => $"{x.year:D4}-{x.month:D2}", x => x.total);

            var list = new List<SerieMesDto>(12);
            for (int i = 0; i < 12; i++)
            {
                var dt = startMonth.AddMonths(i);
                var key = $"{dt.Year:D4}-{dt.Month:D2}";
                list.Add(new SerieMesDto { Mes = key, Total = dict.TryGetValue(key, out var v) ? v : 0 });
            }
            return list;
        }

        // ✅ para frente (começa no mês atual e vai +11)
        private static List<SerieMesDto> Fill12MonthsForward(DateTime startMonth, IEnumerable<(int year, int month, int total)> raw)
        {
            var dict = raw.ToDictionary(x => $"{x.year:D4}-{x.month:D2}", x => x.total);

            var list = new List<SerieMesDto>(12);
            for (int i = 0; i < 12; i++)
            {
                var dt = startMonth.AddMonths(i);
                var key = $"{dt.Year:D4}-{dt.Month:D2}";
                list.Add(new SerieMesDto { Mes = key, Total = dict.TryGetValue(key, out var v) ? v : 0 });
            }
            return list;
        }
    }
}