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'

const STAGE_ORDER = ['novo', 'contato', 'aguardando', 'qualificado', 'produto', 'reuniao', 'ganho', 'desqualificado']

export async function GET() {
  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')`,
  )

  const topRows = await query<{ stage: string; cnpj: string; razao_social: string; municipio: string }>(
    `WITH ranked AS (
       SELECT
         COALESCE(cl.stage, 'novo') AS stage,
         e.cnpj, e.razao_social, e.municipio,
         ROW_NUMBER() OVER (
           PARTITION BY COALESCE(cl.stage, 'novo')
           ORDER BY cl.cadastrado_em DESC NULLS LAST, e.razao_social
         ) AS rn
       FROM empresas_monitoradas e
       LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
     )
     SELECT stage, cnpj, razao_social, municipio FROM ranked WHERE rn <= 4`,
  )

  const stageMap: Record<string, {
    count: number
    top: { cnpj: string; razao_social: string; municipio: string }[]
  }> = {}

  for (const s of STAGE_ORDER) {
    stageMap[s] = { count: 0, top: [] }
  }
  for (const r of byStage) {
    if (stageMap[r.stage]) stageMap[r.stage].count = parseInt(r.count)
  }
  for (const r of topRows) {
    if (stageMap[r.stage]) stageMap[r.stage].top.push({ cnpj: r.cnpj, razao_social: r.razao_social, municipio: r.municipio })
  }

  return NextResponse.json(stageMap)
}