`app/api/agents/congress-by-zip/route.ts`

91 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';

/**
 * GET /api/agents/congress-by-zip?zip=90210
 * Look up congress members and their staffers by ZIP code.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const zip = request.nextUrl.searchParams.get('zip');
    if (!zip || zip.length < 5) {
      return NextResponse.json({ error: 'Valid ZIP code required' }, { status: 400 });
    }

    // Find politicians whose district covers this ZIP
    const membersResult = await query(
      `SELECT DISTINCT
        p.id, p.name, p.title, p.party, p.state, p.district,
        p.congressional_district, p.phone, p.email, p.website,
        p.social_media, p.photo_url, p.position_student_debt,
        dz.overlap_pct
       FROM politicians p
       JOIN congressional_districts cd ON cd.id = p.district_id
       JOIN district_zips dz ON dz.district_id = cd.id
       WHERE dz.zip = $1
       ORDER BY dz.overlap_pct DESC NULLS LAST`,
      [zip],
    );

    // Determine state from ZIP — use matched members or fall back to zip_profiles/zip_crosswalk
    let memberState = membersResult.rows[0]?.state || null;
    if (!memberState) {
      const stateResult = await query(
        `SELECT state FROM zip_profiles WHERE zip = $1
         UNION ALL
         SELECT state_abbr FROM zip_crosswalk WHERE zip = $1
         LIMIT 1`,
        [zip],
      );
      memberState = stateResult.rows[0]?.state || stateResult.rows[0]?.state_abbr || null;
    }

    // Get state-level senators (always, even if no district member found)
    let senators: any[] = [];
    if (memberState) {
      const existingIds = membersResult.rows.map((r: any) => r.id);
      const senResult = await query(
        `SELECT id, name, title, party, state, district, congressional_district,
                phone, email, website, social_media, photo_url, position_student_debt
         FROM politicians
         WHERE state = $1 AND (title ILIKE '%senator%' OR title ILIKE '%sen.%')
         AND office_level = 'federal'
         ${existingIds.length ? `AND id NOT IN (SELECT unnest($2::uuid[]))` : ''}`,
        existingIds.length ? [memberState, existingIds] : [memberState],
      );
      senators = senResult.rows;
    }

    const allMembers = [...membersResult.rows, ...senators];

    // Get staffers for all members
    let staffers: any[] = [];
    if (allMembers.length > 0) {
      const memberIds = allMembers.map((m: any) => m.id);
      const staffResult = await query(
        `SELECT id, politician_id, full_name, title, role_category,
                email, phone, handles_education, handles_finance, is_key_contact
         FROM congressional_staffers
         WHERE politician_id = ANY($1::uuid[])
         ORDER BY is_key_contact DESC, handles_education DESC, full_name ASC`,
        [memberIds],
      );
      staffers = staffResult.rows;
    }

    return NextResponse.json({
      zip,
      members: allMembers,
      staffers,
      total: allMembers.length,
    });
  } catch (err) {
    console.error('[agents/congress-by-zip] Error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to look up congress members' }, { status: 500 });
  }
}