`app/api/congress/route.ts`

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

/**
 * GET /api/congress?state=CA&party=D&search=pelosi&sort=opportunity
 *
 * Returns congress members with their districts, staffers,
 * community colleges, and opportunity data.
 *
 * Optimized: 4 queries total (members + count + batch staffers + batch colleges)
 * instead of N+1 pattern.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const sp = request.nextUrl.searchParams;
  const state = sp.get('state') || '';
  const party = sp.get('party') || '';
  const search = sp.get('search') || '';
  const districtCode = sp.get('district') || '';
  const sort = sp.get('sort') || 'state'; // 'state' | 'opportunity' | 'name' | 'debt'
  const oppLevel = sp.get('opp_level') || ''; // 'high' | 'med' | 'low'
  const limit = Math.min(Number(sp.get('limit') || 100), 500);
  const offset = Number(sp.get('offset') || 0);

  try {
    const conditions: string[] = ["p.office_level = 'federal'"];
    const params: unknown[] = [];
    let idx = 1;

    if (state) {
      conditions.push(`p.state = $${idx}`);
      params.push(state.toUpperCase());
      idx++;
    }
    if (party) {
      conditions.push(`p.party = $${idx}`);
      params.push(party.toUpperCase().substring(0, 1));
      idx++;
    }
    if (search) {
      conditions.push(`p.name ILIKE $${idx}`);
      params.push(`%${search}%`);
      idx++;
    }
    if (districtCode) {
      conditions.push(`p.congressional_district = $${idx}`);
      params.push(districtCode);
      idx++;
    }
    if (oppLevel) {
      switch (oppLevel) {
        case 'high':
          conditions.push(`cd.opportunity_score >= 70`);
          break;
        case 'med':
          conditions.push(`cd.opportunity_score >= 40 AND cd.opportunity_score < 70`);
          break;
        case 'low':
          conditions.push(`cd.opportunity_score < 40`);
          break;
      }
    }

    const where = conditions.join(' AND ');

    // Sort clause
    let orderBy: string;
    switch (sort) {
      case 'opportunity':
        orderBy = 'cd.opportunity_score DESC NULLS LAST, p.state, p.name';
        break;
      case 'name':
        orderBy = 'p.name';
        break;
      case 'debt':
        orderBy = 'cd.avg_cc_median_debt DESC NULLS LAST, p.state, p.name';
        break;
      default:
        orderBy = "p.state, (NULLIF(p.district, '')::int) NULLS LAST, p.name";
    }

    // Query 1: Main — politicians with district data
    const { rows: members } = await query(`
      SELECT
        p.id, p.name, p.title, p.party, p.state, p.district,
        p.congressional_district, p.phone, p.website, p.email,
        p.social_media, p.photo_url, p.bioguide_id,
        p.votes_with_party_pct, p.missed_votes_pct,
        p.committees, p.position_student_debt,
        cd.id as district_id,
        cd.zip_count,
        cd.community_college_count,
        cd.total_cc_enrollment,
        cd.avg_cc_median_debt,
        cd.avg_cc_pell_rate,
        cd.highest_debt_college,
        cd.total_complaints,
        cd.avg_distress_score,
        cd.opportunity_score,
        cd.opportunity_factors
      FROM politicians p
      LEFT JOIN congressional_districts cd
        ON cd.district_code = p.congressional_district AND cd.congress_number = 119
      WHERE ${where}
      ORDER BY ${orderBy}
      LIMIT $${idx} OFFSET $${idx + 1}
    `, [...params, limit, offset]);

    // Query 2: Total count
    const { rows: [{ total }] } = await query(
      `SELECT COUNT(*) as total
       FROM politicians p
       LEFT JOIN congressional_districts cd
         ON cd.district_code = p.congressional_district AND cd.congress_number = 119
       WHERE ${where}`,
      params,
    );

    // Collect IDs for batch lookups
    const memberIds = members.filter(m => m.id).map(m => m.id);
    const districtIds = [...new Set(members.filter(m => m.district_id).map(m => m.district_id))];

    // Query 3: Batch staffers for all members
    let staffersByPol: Record<string, any[]> = {};
    if (memberIds.length > 0) {
      const { rows: allStaffers } = await query(`
        SELECT id, politician_id, full_name, title, role_category, email, phone,
               office_location, is_dc_office, linkedin_url, twitter_handle,
               handles_education, handles_finance, is_key_contact
        FROM congressional_staffers
        WHERE politician_id = ANY($1::uuid[])
        ORDER BY
          CASE role_category
            WHEN 'leadership' THEN 1
            WHEN 'chief_of_staff' THEN 1
            WHEN 'legislative_director' THEN 2
            WHEN 'legislative' THEN 2
            WHEN 'communications' THEN 3
            WHEN 'constituent_services' THEN 4
            WHEN 'administrative' THEN 5
            ELSE 6
          END, full_name
      `, [memberIds]);

      for (const s of allStaffers) {
        if (!staffersByPol[s.politician_id]) staffersByPol[s.politician_id] = [];
        staffersByPol[s.politician_id].push(s);
      }
    }

    // Query 4: Batch community colleges for all districts
    let collegesByDist: Record<string, any[]> = {};
    if (districtIds.length > 0) {
      const { rows: allColleges } = await query(`
        SELECT district_id, school_name, city, state, zip, enrollment,
               median_debt, pell_grant_rate, completion_rate,
               retention_rate, tuition_in_state, avg_net_price,
               median_earnings_6yr, school_url
        FROM college_scorecard
        WHERE district_id = ANY($1::uuid[]) AND institution_type = 'community_college'
        ORDER BY enrollment DESC NULLS LAST
      `, [districtIds]);

      for (const c of allColleges) {
        if (!collegesByDist[c.district_id]) collegesByDist[c.district_id] = [];
        collegesByDist[c.district_id].push(c);
      }
    }

    // Assemble response
    const enriched = members.map(m => ({
      ...m,
      staffers: staffersByPol[m.id] || [],
      community_colleges: collegesByDist[m.district_id] || [],
    }));

    // Aggregate stats for the response header
    const stats = {
      total_members: Number(total),
      avg_opportunity_score: (() => {
        const scored = members.filter(m => m.opportunity_score != null);
        return scored.length > 0
          ? Math.round(scored.reduce((s, m) => s + Number(m.opportunity_score), 0) / scored.length * 10) / 10
          : null;
      })(),
      total_cc_enrollment: members.reduce((s, m) => s + (m.total_cc_enrollment || 0), 0),
      high_priority_count: members.filter(m => (m.opportunity_score || 0) >= 70).length,
    };

    return NextResponse.json({
      members: enriched,
      total: Number(total),
      stats,
      limit,
      offset,
    });
  } catch (err) {
    console.error('[congress] API error:', err);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}