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

const PROSPECT_STAGES = ['qualificado', 'produto', 'reuniao', 'ganho']

export async function GET(req: NextRequest) {
  const sp = req.nextUrl.searchParams
  const search   = sp.get('q')     ?? ''
  const stage    = sp.get('stage') ?? ''
  const sort     = sp.get('sort')  ?? ''
  const page     = Math.max(1, parseInt(sp.get('page') ?? '1'))
  const pageSize = 50

  const conditions: string[] = [`COALESCE(cl.stage, 'novo') = ANY($1::text[])`]
  const params: unknown[] = [PROSPECT_STAGES]
  let p = 2

  if (search) {
    conditions.push(`(e.razao_social ILIKE $${p} OR e.nome_fantasia ILIKE $${p} OR e.cnpj ILIKE $${p} OR e.municipio ILIKE $${p})`)
    params.push(`%${search}%`); p++
  }
  if (stage && PROSPECT_STAGES.includes(stage)) {
    conditions.push(`COALESCE(cl.stage, 'novo') = $${p}`)
    params.push(stage); p++
  }

  const where  = `WHERE ${conditions.join(' AND ')}`
  const offset = (page - 1) * pageSize

  const orderBy = sort === 'abertura_desc' ? 'e.data_abertura DESC NULLS LAST'
    : sort === 'abertura_asc'              ? 'e.data_abertura ASC NULLS LAST'
    : `ARRAY_POSITION($1::text[], COALESCE(cl.stage, 'novo')), e.razao_social`

  const [{ count }] = await query<{ count: string }>(
    `SELECT COUNT(*) AS count
     FROM empresas_monitoradas e
     LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
     ${where}`,
    params,
  )

  const rows = await query(
    `SELECT
       e.cnpj, e.cnpj_basico, e.razao_social, e.nome_fantasia,
       e.cnae_principal, e.municipio, e.uf,
       e.ddd1, e.telefone1, e.email,
       e.data_abertura, e.primeira_deteccao,
       sn.opcao_simples, sn.opcao_mei,
       COALESCE(cl.crm_type, 'prospect') AS crm_type,
       COALESCE(cl.stage,    'qualificado') AS stage,
       cl.produto, cl.motivo, cl.notes, cl.cadastrado_em
     FROM empresas_monitoradas e
     LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
     LEFT JOIN simples_nacional sn ON sn.cnpj_basico = e.cnpj_basico
     ${where}
     ORDER BY ${orderBy}`,
    params,
  )

  return NextResponse.json({ rows, total: parseInt(count), page, pageSize })
}