line-gestao-api/Controllers/VigenciaController.cs

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

namespace line_gestao_api.Controllers
{
    [ApiController]
    [Route("api/lines/vigencia")]
    public class VigenciaController : ControllerBase
    {
        private readonly AppDbContext _db;

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

        // GET /api/lines/vigencia (Linhas - Tabela Interna)
        [HttpGet]
        public async Task<ActionResult<PagedResult<VigenciaLineListDto>>> GetVigencia(
            [FromQuery] string? search,
            [FromQuery] string? client,
            [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.VigenciaLines.AsNoTracking();

            if (!string.IsNullOrWhiteSpace(client))
            {
                var c = client.Trim();
                q = q.Where(x => x.Cliente == c);
            }

            if (!string.IsNullOrWhiteSpace(search))
            {
                var s = search.Trim();
                q = q.Where(x =>
                    EF.Functions.ILike(x.Conta ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Linha ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Cliente ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.Usuario ?? "", $"%{s}%") ||
                    EF.Functions.ILike(x.PlanoContrato ?? "", $"%{s}%"));
            }

            var total = await q.CountAsync();

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

            q = sb switch
            {
                "item" => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item),
                "linha" => desc ? q.OrderByDescending(x => x.Linha) : q.OrderBy(x => x.Linha),
                "total" => desc ? q.OrderByDescending(x => x.Total) : q.OrderBy(x => x.Total),
                "dttermino" => desc ? q.OrderByDescending(x => x.DtTerminoFidelizacao) : q.OrderBy(x => x.DtTerminoFidelizacao),
                _ => desc ? q.OrderByDescending(x => x.Item) : q.OrderBy(x => x.Item),
            };

            var items = await q
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .Select(x => new VigenciaLineListDto
                {
                    Id = x.Id,
                    Item = x.Item,
                    Conta = x.Conta,
                    Linha = x.Linha,
                    Cliente = x.Cliente,
                    Usuario = x.Usuario,
                    PlanoContrato = x.PlanoContrato,
                    DtEfetivacaoServico = x.DtEfetivacaoServico,
                    DtTerminoFidelizacao = x.DtTerminoFidelizacao,
                    Total = x.Total
                })
                .ToListAsync();

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

        // ==========================================================
        //  GET /api/lines/vigencia/groups (Cards + KPIs GERAIS)
        // ==========================================================
        [HttpGet("groups")]
        public async Task<ActionResult<VigenciaGroupResponse>> GetVigenciaGroups(
            [FromQuery] string? search,
            [FromQuery] int page = 1,
            [FromQuery] int pageSize = 20,
            [FromQuery] string? sortBy = "cliente",
            [FromQuery] string? sortDir = "asc")
        {
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 20 : pageSize;

            var today = DateTime.UtcNow.Date; // UTC para evitar erro no PostgreSQL
            var limit30 = today.AddDays(30);

            // Query Base (Linhas)
            var q = _db.VigenciaLines.AsNoTracking()
                .Where(x => x.Cliente != null && x.Cliente != "");

            if (!string.IsNullOrWhiteSpace(search))
            {
                var s = search.Trim();
                q = q.Where(x => EF.Functions.ILike(x.Cliente ?? "", $"%{s}%"));
            }

            // ✅ CÁLCULO DOS KPIS GERAIS (Antes do agrupamento/paginação)
            // Isso garante que os KPIs mostrem o total do banco (ou do filtro), não só da página.
            var kpis = new VigenciaKpis
            {
                TotalLinhas = await q.CountAsync(),
                // Clientes distintos
                TotalClientes = await q.Select(x => x.Cliente).Distinct().CountAsync(),
                TotalVencidos = await q.CountAsync(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date < today),
                ValorTotal = await q.SumAsync(x => x.Total ?? 0m)
            };

            // Agrupamento para a lista paginada
            var grouped = q
                .GroupBy(x => x.Cliente!)
                .Select(g => new VigenciaClientGroupDto
                {
                    Cliente = g.Key,
                    Linhas = g.Count(),
                    Total = g.Sum(x => x.Total ?? 0m),
                    Vencidos = g.Count(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date < today),
                    AVencer30 = g.Count(x => x.DtTerminoFidelizacao != null && x.DtTerminoFidelizacao.Value.Date >= today && x.DtTerminoFidelizacao.Value.Date <= limit30),
                    ProximoVencimento = g.Where(x => x.DtTerminoFidelizacao >= today).Min(x => x.DtTerminoFidelizacao),
                    UltimoVencimento = g.Where(x => x.DtTerminoFidelizacao < today).Max(x => x.DtTerminoFidelizacao)
                });

            // Contagem para paginação
            var totalGroups = await grouped.CountAsync();

            // Ordenação
            var desc = string.Equals((sortDir ?? "asc").Trim(), "desc", StringComparison.OrdinalIgnoreCase);
            if (sortBy?.ToLower() == "linhas")
                grouped = desc ? grouped.OrderByDescending(x => x.Linhas) : grouped.OrderBy(x => x.Linhas);
            else
                grouped = desc ? grouped.OrderByDescending(x => x.Cliente) : grouped.OrderBy(x => x.Cliente);

            // Paginação
            var items = await grouped
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            // ✅ Retorna objeto composto
            return Ok(new VigenciaGroupResponse
            {
                Data = new PagedResult<VigenciaClientGroupDto>
                {
                    Page = page,
                    PageSize = pageSize,
                    Total = totalGroups, // Total de Clientes na paginação
                    Items = items
                },
                Kpis = kpis // KPIs Globais
            });
        }

        [HttpGet("clients")]
        public async Task<ActionResult<List<string>>> GetVigenciaClients()
        {
            return await _db.VigenciaLines.AsNoTracking()
                .Where(x => !string.IsNullOrEmpty(x.Cliente))
                .Select(x => x.Cliente!)
                .Distinct()
                .OrderBy(x => x)
                .ToListAsync();
        }
    }
}