`app/api/geo/advocacy/route.ts`

118 lines
/**
 * GET /api/geo/advocacy
 *
 * Returns zip_profiles ordered by advocacy_readiness_score DESC.
 * For identifying top campaign-targeting ZIP codes.
 *
 * Optional filters:
 *   ?state=CA       — filter by state abbreviation
 *   ?min_score=50   — minimum advocacy_readiness_score
 *
 * Returns top 100 with all zip_profile fields.
 * Requires authentication.
 */

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

export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const { searchParams } = new URL(request.url);
  const state = searchParams.get('state');
  const minScoreParam = searchParams.get('min_score');

  // Validate min_score if provided
  let minScore: number | null = null;
  if (minScoreParam) {
    minScore = Number(minScoreParam);
    if (isNaN(minScore) || minScore < 0 || minScore > 100) {
      return NextResponse.json(
        { error: 'min_score must be a number between 0 and 100' },
        { status: 400 },
      );
    }
  }

  try {
    const conditions: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

    // Always filter for non-null advocacy scores
    conditions.push('advocacy_readiness_score IS NOT NULL');

    if (state) {
      conditions.push(`state = $${paramIndex++}`);
      values.push(state.toUpperCase());
    }

    if (minScore !== null) {
      conditions.push(`advocacy_readiness_score >= $${paramIndex++}`);
      values.push(minScore);
    }

    const whereClause = conditions.length > 0
      ? `WHERE ${conditions.join(' AND ')}`
      : '';

    const { rows } = await query(
      `SELECT
         zip, state, county_fips, county_name, pop_25_plus,
         complaint_count, complaint_density, bachelors_pct, grad_pct,
         school_count, avg_median_debt, avg_pell_rate, max_debt_school,
         community_mentions, avg_sentiment_score, frustrated_pct,
         debt_distress_score, advocacy_readiness_score,
         top_servicers, top_issues, hend_resources_count,
         last_computed_at, updated_at
       FROM zip_profiles
       ${whereClause}
       ORDER BY advocacy_readiness_score DESC
       LIMIT 100`,
      values,
    );

    // Compute aggregate stats for the result set
    let totalScore = 0;
    let maxScore = 0;
    let minScoreVal = 100;
    const stateCounts: Record<string, number> = {};

    for (const row of rows) {
      const score = Number((row as { advocacy_readiness_score: string }).advocacy_readiness_score ?? 0);
      totalScore += score;
      if (score > maxScore) maxScore = score;
      if (score < minScoreVal) minScoreVal = score;

      const st = (row as { state: string | null }).state;
      if (st) {
        stateCounts[st] = (stateCounts[st] || 0) + 1;
      }
    }

    return NextResponse.json({
      filters: { state, min_score: minScore },
      summary: {
        total: rows.length,
        avg_score: rows.length > 0 ? Math.round((totalScore / rows.length) * 100) / 100 : 0,
        max_score: Math.round(maxScore * 100) / 100,
        min_score: rows.length > 0 ? Math.round(minScoreVal * 100) / 100 : 0,
        states_represented: Object.keys(stateCounts).length,
        top_states: Object.entries(stateCounts)
          .sort((a, b) => b[1] - a[1])
          .slice(0, 10)
          .map(([st, count]) => ({ state: st, count })),
      },
      profiles: rows,
    });
  } catch (err) {
    console.error('[geo/advocacy] Error:', (err as Error).message);
    return NextResponse.json(
      { error: `Failed to fetch advocacy data: ${(err as Error).message}` },
      { status: 500 },
    );
  }
}