import { NextResponse } from 'next/server'
import { query } from '@/lib/db'

// GET() sem parâmetros seria tratado como rota estática pelo Next e cacheado
// permanentemente a partir do build — precisa ser explicitamente dinâmico.
export const dynamic = 'force-dynamic'

export async function GET() {
  const [totals] = await query<{
    total: string
    leads: string
    prospects: string
    ganhos: string
  }>(`
    SELECT
      COUNT(*)                                              AS total,
      COUNT(*) FILTER (WHERE cl.crm_type = 'lead')        AS leads,
      COUNT(*) FILTER (WHERE cl.crm_type = 'prospect')    AS prospects,
      COUNT(*) FILTER (WHERE cl.stage = 'ganho')          AS ganhos
    FROM empresas_monitoradas e
    LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
  `)

  const byStage = await query<{ stage: string; count: string }>(`
    SELECT COALESCE(cl.stage, 'novo') AS stage, COUNT(*) AS count
    FROM empresas_monitoradas e
    LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
    GROUP BY COALESCE(cl.stage, 'novo')
    ORDER BY count DESC
  `)

  const byDeteccao = await query<{ mes: string; count: string }>(`
    SELECT TO_CHAR(DATE_TRUNC('month', primeira_deteccao), 'YYYY-MM') AS mes,
           COUNT(*) AS count
    FROM empresas_monitoradas
    GROUP BY 1
    ORDER BY 1 DESC
    LIMIT 12
  `)

  return NextResponse.json({ totals, byStage, byDeteccao })
}