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

// ── GET /api/leads ────────────────────────────────────────────────────────────
export async function GET(req: NextRequest) {
  const sp       = req.nextUrl.searchParams
  const search   = sp.get('q')         ?? ''
  const stage    = sp.get('stage')     ?? ''
  const crmType  = sp.get('type')      ?? ''
  const simples  = sp.get('simples')   ?? ''
  const uf       = sp.get('uf')        ?? ''
  const mun      = sp.get('municipio') ?? ''
  const source   = sp.get('source')    ?? ''
  const sort     = sp.get('sort')      ?? ''
  const page     = Math.max(1, parseInt(sp.get('page') ?? '1'))
  const pageSize = 50

  const conditions: string[] = []
  const params: unknown[]    = []
  let p = 1

  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) {
    conditions.push(`COALESCE(cl.stage, 'novo') = $${p}`)
    params.push(stage); p++
  }
  if (crmType) {
    conditions.push(`COALESCE(cl.crm_type, 'lead') = $${p}`)
    params.push(crmType); p++
  }
  if (uf) {
    conditions.push(`e.uf = $${p}`)
    params.push(uf.toUpperCase()); p++
  }
  if (mun) {
    conditions.push(`e.municipio ILIKE $${p}`)
    params.push(`%${mun}%`); p++
  }
  if (source) {
    conditions.push(`e.source = $${p}`)
    params.push(source); p++
  }
  // Simples filters (sem parâmetros — apenas condições de coluna)
  if (simples === 'sim') {
    conditions.push(`sn.opcao_simples = true AND (sn.opcao_mei IS NULL OR sn.opcao_mei = false)`)
  } else if (simples === 'mei') {
    conditions.push(`sn.opcao_mei = true`)
  } else if (simples === 'nao') {
    conditions.push(`(sn.opcao_simples = false OR sn.opcao_simples IS NULL)`)
  }

  const where  = conditions.length ? `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'
    : 'e.primeira_deteccao DESC, e.razao_social'

  // JOIN sn sempre presente nos dois subqueries para evitar erro com filtro Simples
  const joins = `
    LEFT JOIN crm_leads cl ON cl.cnpj = e.cnpj
    LEFT JOIN simples_nacional sn ON sn.cnpj_basico = e.cnpj_basico`

  const [{ count }] = await query<{ count: string }>(
    `SELECT COUNT(*) AS count FROM empresas_monitoradas e ${joins} ${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.source,
       e.ddd1, e.telefone1, e.email, e.data_abertura, e.primeira_deteccao,
       sn.opcao_simples, sn.opcao_mei,
       COALESCE(cl.crm_type, 'lead') AS crm_type,
       COALESCE(cl.stage,    'novo') AS stage,
       cl.produto, cl.motivo, cl.notes, cl.cadastrado_em
     FROM empresas_monitoradas e ${joins} ${where}
     ORDER BY ${orderBy}
     LIMIT ${pageSize} OFFSET ${offset}`,
    params,
  )

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

// ── POST /api/leads — cadastro manual ────────────────────────────────────────
export async function POST(req: NextRequest) {
  const body = await req.json() as {
    cnpj: string
    razao_social: string
    nome_fantasia?: string
    municipio: string
    uf: string
    ddd1?: string
    telefone1?: string
    email?: string
    cnae_principal?: string
    logradouro?: string
    numero?: string
    complemento?: string
    bairro?: string
    cep?: string
    natureza_juridica?: string
    porte_empresa?: string
    data_abertura?: string
    stage?: string
  }

  const cnpj = (body.cnpj ?? '').replace(/\D/g, '')
  if (cnpj.length !== 14) return NextResponse.json({ error: 'CNPJ inválido' }, { status: 400 })
  if (!body.razao_social?.trim()) return NextResponse.json({ error: 'Razão social obrigatória' }, { status: 400 })
  if (!body.municipio?.trim()) return NextResponse.json({ error: 'Município obrigatório' }, { status: 400 })
  if (!body.uf?.trim()) return NextResponse.json({ error: 'UF obrigatória' }, { status: 400 })

  const hoje = new Date().toISOString().slice(0, 10)

  try {
    await query(
      `INSERT INTO empresas_monitoradas
         (cnpj, cnpj_basico, razao_social, nome_fantasia, municipio, uf,
          cnae_principal, ddd1, telefone1, email,
          logradouro, numero, complemento, bairro, cep,
          natureza_juridica, porte_empresa, data_abertura,
          primeira_deteccao, ultima_atualizacao, source)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$19,'manual')
       ON CONFLICT (cnpj) DO NOTHING`,
      [
        cnpj,
        cnpj.slice(0, 8),
        body.razao_social.trim(),
        body.nome_fantasia?.trim()     || null,
        body.municipio.trim().toUpperCase(),
        body.uf.trim().toUpperCase(),
        body.cnae_principal?.trim()    || null,
        body.ddd1?.trim()              || null,
        body.telefone1?.trim()         || null,
        body.email?.trim()             || null,
        body.logradouro?.trim()        || null,
        body.numero?.trim()            || null,
        body.complemento?.trim()       || null,
        body.bairro?.trim()            || null,
        body.cep?.replace(/\D/g, '')   || null,
        body.natureza_juridica?.trim() || null,
        body.porte_empresa?.trim()     || null,
        body.data_abertura             || null,
        hoje,
      ],
    )

    const stage = body.stage ?? 'novo'
    await query(
      `INSERT INTO crm_leads (cnpj, stage, crm_type)
       VALUES ($1, $2, 'lead')
       ON CONFLICT (cnpj) DO NOTHING`,
      [cnpj, stage],
    )

    return NextResponse.json({ cnpj, ok: true })
  } catch (err: unknown) {
    const msg = err instanceof Error ? err.message : String(err)
    return NextResponse.json({ error: msg }, { status: 500 })
  }
}