import type { VercelRequest, VercelResponse } from '@vercel/node'
import { neon } from '@neondatabase/serverless'

const PRIMARY_CITY = 'Dar es Salaam'

export default async function handler(req: VercelRequest, res: VercelResponse) {
  if (!process.env.DATABASE_URL) {
    return res.status(500).json({ error: 'DATABASE_URL not set' })
  }
  const sql = neon(process.env.DATABASE_URL)
  const zoneFilter = typeof req.query.zone_id === 'string' ? req.query.zone_id : undefined

  const rows = zoneFilter
    ? await sql`
        SELECT w.worker_id, w.name, w.name_swahili, w.nida_id, w.phone,
               w.zone_id, w.occupation, w.age, w.years_outdoor,
               w.household_size, w.mobile_money, w.tasaf_enrolled,
               w.enrolled_at,
               z.name AS zone_name, z.settlement_type
        FROM workers w
        JOIN zones z ON z.zone_id = w.zone_id
        WHERE z.city = ${PRIMARY_CITY}
          AND w.zone_id = ${zoneFilter}
        ORDER BY w.worker_id
      `
    : await sql`
        SELECT w.worker_id, w.name, w.name_swahili, w.nida_id, w.phone,
               w.zone_id, w.occupation, w.age, w.years_outdoor,
               w.household_size, w.mobile_money, w.tasaf_enrolled,
               w.enrolled_at,
               z.name AS zone_name, z.settlement_type
        FROM workers w
        JOIN zones z ON z.zone_id = w.zone_id
        WHERE z.city = ${PRIMARY_CITY}
        ORDER BY z.name, w.worker_id
      `

  return res.json({ workers: rows, total: rows.length })
}