`app/api/ingest/open-states/route.ts`

129 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
import { searchStateBills, type StateBill } from '@/lib/open-states';

/**
 * POST /api/ingest/open-states
 *
 * Ingests state-level bills from Open States API.
 * Body: { keywords?: string[], jurisdiction?: string }
 * Upserts into state_bills table.
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

  let body: { keywords?: string[]; jurisdiction?: string } = {};
  try {
    body = await request.json();
  } catch {
    /* use defaults */
  }

  const keywords = body.keywords ?? ['education', 'nonprofit', 'public health'];
  const jurisdiction = body.jurisdiction ?? undefined;

  const stats = { ingested: 0, errors: 0 };
  const errorDetails: string[] = [];

  // Deduplicate across keyword searches
  const allBills = new Map<string, StateBill>();

  for (const keyword of keywords) {
    try {
      console.log('[ingest/open-states] Searching state bills for:', keyword);
      const bills = await searchStateBills(keyword, jurisdiction, undefined, 50);
      for (const bill of bills) {
        if (bill.openstatesId && !allBills.has(bill.openstatesId)) {
          allBills.set(bill.openstatesId, bill);
        }
      }
    } catch (err) {
      console.error('[ingest/open-states] Search error for keyword:', keyword, (err as Error).message);
      errorDetails.push('search_' + keyword + ': ' + (err as Error).message);
      stats.errors++;
    }
  }

  console.log('[ingest/open-states] Found', allBills.size, 'unique state bills to upsert');

  for (const bill of allBills.values()) {
    try {
      await query(
        `INSERT INTO state_bills (
          openstates_id, jurisdiction, session, identifier, title,
          classification, subject,
          latest_action_description, latest_action_date,
          sponsor_name, url, raw_json, org_id
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
        ON CONFLICT (openstates_id) DO UPDATE SET
          title = EXCLUDED.title,
          classification = EXCLUDED.classification,
          subject = EXCLUDED.subject,
          latest_action_description = EXCLUDED.latest_action_description,
          latest_action_date = EXCLUDED.latest_action_date,
          sponsor_name = EXCLUDED.sponsor_name,
          url = EXCLUDED.url,
          raw_json = EXCLUDED.raw_json,
          updated_at = NOW()`,
        [
          bill.openstatesId,
          bill.jurisdiction,
          bill.session,
          bill.identifier,
          bill.title,
          bill.classification.length > 0 ? bill.classification : null,
          bill.subject.length > 0 ? bill.subject : null,
          bill.latestActionDescription || null,
          bill.latestActionDate || null,
          bill.sponsorName || null,
          bill.url || null,
          JSON.stringify(bill),
          orgId || null,
        ],
      );
      stats.ingested++;
    } catch (err) {
      console.error('[ingest/open-states] Insert error:', (err as Error).message);
      errorDetails.push('insert: ' + (err as Error).message);
      stats.errors++;
    }
  }

  console.log('[ingest/open-states] Done:', stats.ingested, 'ingested,', stats.errors, 'errors');

  return NextResponse.json({
    success: true,
    ingested: stats.ingested,
    errors: stats.errors,
    ...(errorDetails.length > 0 ? { errorDetails } : {}),
  });
}

/* ─── GET: Return ingested state bills ───────────────────────────────────── */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { rows } = await query(`
      SELECT id, openstates_id, jurisdiction, session, identifier, title,
             classification, subject,
             latest_action_description, latest_action_date,
             sponsor_name, url, created_at
      FROM state_bills
      ORDER BY latest_action_date DESC NULLS LAST, created_at DESC
      LIMIT 200
    `);

    return NextResponse.json({ bills: rows, total: rows.length });
  } catch (err) {
    console.error('[ingest/open-states] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch state bills' }, { status: 500 });
  }
}