line-gestao-api/Controllers/ParcelamentoController.cs

using line_gestao_api.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Globalization;

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

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

        public class ParcelamentoKpisDto
        {
            public decimal TotalGeral { get; set; }
            public int Linhas { get; set; }
            public int Clientes { get; set; }
            public string? CompetenciaInicial { get; set; } // yyyy-MM
            public string? CompetenciaFinal { get; set; }   // yyyy-MM
            public string? MesAtual { get; set; }           // yyyy-MM
            public decimal TotalMesAtual { get; set; }
        }

        public class ParcelamentoMonthlyTotalDto
        {
            public string Competencia { get; set; } = ""; // yyyy-MM
            public decimal Total { get; set; }
        }

        public class ParcelamentoMonthDetailDto
        {
            public string? Linha { get; set; }
            public string? Cliente { get; set; }
            public decimal Valor { get; set; }
        }

        // =========================
        // Clientes (dropdown)
        // =========================
        [HttpGet("clientes")]
        public async Task<ActionResult<List<string>>> GetClientes()
        {
            var clientes = await _db.ParcelamentoLines.AsNoTracking()
                .Where(x => x.Cliente != null && x.Cliente != "")
                .Select(x => x.Cliente!)
                .Distinct()
                .OrderBy(x => x)
                .ToListAsync();

            return Ok(clientes);
        }

        // =========================
        // KPIs
        // =========================
        [HttpGet("kpis")]
        public async Task<ActionResult<ParcelamentoKpisDto>> GetKpis([FromQuery] string? cliente)
        {
            var qLines = _db.ParcelamentoLines.AsNoTracking();

            if (!string.IsNullOrWhiteSpace(cliente))
                qLines = qLines.Where(x => x.Cliente == cliente);

            var qMeses = _db.ParcelamentoMonthValues.AsNoTracking()
                .Join(qLines, m => m.ParcelamentoLineId, l => l.Id, (m, l) => m);

            var totalGeral = await qMeses.SumAsync(x => (decimal?)x.Valor) ?? 0m;

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

            var minComp = await qMeses.MinAsync(x => (DateTime?)x.Competencia);
            var maxComp = await qMeses.MaxAsync(x => (DateTime?)x.Competencia);

            var now = DateTime.Now;
            var mesAtual = new DateTime(now.Year, now.Month, 1);

            var totalMesAtual = await qMeses
                .Where(x => x.Competencia == mesAtual)
                .SumAsync(x => (decimal?)x.Valor) ?? 0m;

            return Ok(new ParcelamentoKpisDto
            {
                TotalGeral = totalGeral,
                Linhas = linhas,
                Clientes = clientes,
                CompetenciaInicial = minComp?.ToString("yyyy-MM"),
                CompetenciaFinal = maxComp?.ToString("yyyy-MM"),
                MesAtual = mesAtual.ToString("yyyy-MM"),
                TotalMesAtual = totalMesAtual
            });
        }

        // =========================
        // Série mensal (gráfico)
        // =========================
        [HttpGet("monthly")]
        public async Task<ActionResult<List<ParcelamentoMonthlyTotalDto>>> GetMonthlyTotals(
            [FromQuery] string? cliente,
            [FromQuery] string? from,   // yyyy-MM
            [FromQuery] string? to      // yyyy-MM
        )
        {
            var qLines = _db.ParcelamentoLines.AsNoTracking();
            if (!string.IsNullOrWhiteSpace(cliente))
                qLines = qLines.Where(x => x.Cliente == cliente);

            var qMeses = _db.ParcelamentoMonthValues.AsNoTracking()
                .Join(qLines, m => m.ParcelamentoLineId, l => l.Id, (m, l) => m);

            if (TryParseYm(from, out var fromDt))
                qMeses = qMeses.Where(x => x.Competencia >= fromDt);

            if (TryParseYm(to, out var toDt))
                qMeses = qMeses.Where(x => x.Competencia <= toDt);

            var data = await qMeses
                .GroupBy(x => x.Competencia)
                .OrderBy(g => g.Key)
                .Select(g => new ParcelamentoMonthlyTotalDto
                {
                    Competencia = g.Key.ToString("yyyy-MM"),
                    Total = g.Sum(x => x.Valor)
                })
                .ToListAsync();

            return Ok(data);
        }

        // =========================
        // Detalhe do mês (clique no gráfico)
        // =========================
        [HttpGet("month-details")]
        public async Task<ActionResult<List<ParcelamentoMonthDetailDto>>> GetMonthDetails(
            [FromQuery] string competencia, // yyyy-MM
            [FromQuery] string? cliente
        )
        {
            if (!TryParseYm(competencia, out var comp))
                return BadRequest("competencia inválida. Use yyyy-MM (ex.: 2026-01).");

            var qLines = _db.ParcelamentoLines.AsNoTracking();
            if (!string.IsNullOrWhiteSpace(cliente))
                qLines = qLines.Where(x => x.Cliente == cliente);

            var data = await _db.ParcelamentoMonthValues.AsNoTracking()
                .Where(x => x.Competencia == comp)
                .Join(qLines, m => m.ParcelamentoLineId, l => l.Id, (m, l) => new ParcelamentoMonthDetailDto
                {
                    Linha = l.Linha,
                    Cliente = l.Cliente,
                    Valor = m.Valor
                })
                .OrderByDescending(x => x.Valor)
                .Take(200)
                .ToListAsync();

            return Ok(data);
        }

        // =========================
        // Helpers
        // =========================
        private static bool TryParseYm(string? ym, out DateTime dt)
        {
            dt = default;
            if (string.IsNullOrWhiteSpace(ym)) return false;
            return DateTime.TryParseExact(ym.Trim(), "yyyy-MM", CultureInfo.InvariantCulture, DateTimeStyles.None, out dt);
        }
    }
}