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

127 lines
/**
 * GET /api/geo/hotspots
 *
 * Returns geo_hotspots LEFT JOIN zip_profiles, with optional filters:
 *   ?state=CA         — filter by state abbreviation
 *   ?type=complaint_cluster — filter by hotspot_type
 *   ?severity=critical — filter by severity level
 *
 * Ordered by z_score DESC. Limited to 100 rows.
 * 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 type = searchParams.get('type');
  const severity = searchParams.get('severity');

  // Validate hotspot_type if provided
  const validTypes = ['complaint_cluster', 'debt_desert', 'advocacy_opportunity', 'underserved'];
  if (type && !validTypes.includes(type)) {
    return NextResponse.json(
      { error: `Invalid type. Must be one of: ${validTypes.join(', ')}` },
      { status: 400 },
    );
  }

  // Validate severity if provided
  const validSeverities = ['critical', 'high', 'medium', 'low'];
  if (severity && !validSeverities.includes(severity)) {
    return NextResponse.json(
      { error: `Invalid severity. Must be one of: ${validSeverities.join(', ')}` },
      { status: 400 },
    );
  }

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

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

    if (type) {
      conditions.push(`gh.hotspot_type = $${paramIndex++}`);
      values.push(type);
    }

    if (severity) {
      conditions.push(`gh.severity = $${paramIndex++}`);
      values.push(severity);
    }

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

    const { rows } = await query(
      `SELECT
         gh.id,
         gh.zip,
         gh.hotspot_type,
         gh.z_score,
         gh.rank,
         gh.severity,
         gh.contributing_factors,
         gh.detected_at,
         zp.state,
         zp.county_name,
         zp.complaint_count,
         zp.complaint_density,
         zp.bachelors_pct,
         zp.grad_pct,
         zp.avg_median_debt,
         zp.avg_pell_rate,
         zp.school_count,
         zp.community_mentions,
         zp.frustrated_pct,
         zp.debt_distress_score,
         zp.advocacy_readiness_score,
         zp.pop_25_plus
       FROM geo_hotspots gh
       LEFT JOIN zip_profiles zp ON gh.zip = zp.zip
       ${whereClause}
       ORDER BY gh.z_score DESC
       LIMIT 100`,
      values,
    );

    // Summary stats
    const summary = {
      total: rows.length,
      by_type: {} as Record<string, number>,
      by_severity: {} as Record<string, number>,
    };

    for (const row of rows) {
      const t = (row as { hotspot_type: string }).hotspot_type;
      const s = (row as { severity: string }).severity;
      summary.by_type[t] = (summary.by_type[t] || 0) + 1;
      summary.by_severity[s] = (summary.by_severity[s] || 0) + 1;
    }

    return NextResponse.json({
      filters: { state, type, severity },
      summary,
      hotspots: rows,
    });
  } catch (err) {
    console.error('[geo/hotspots] Error:', (err as Error).message);
    return NextResponse.json(
      { error: `Failed to fetch hotspots: ${(err as Error).message}` },
      { status: 500 },
    );
  }
}