← back to Norma

app/api/geo/scatter/route.ts

165 lines

/**
 * GET /api/geo/scatter
 *
 * Returns zip_profiles data formatted for scatter plot visualization.
 *
 * Query parameters:
 *   ?x=complaint_density   — X-axis column (required)
 *   ?y=bachelors_pct       — Y-axis column (required)
 *   ?color=state           — Color grouping column (optional, default: state)
 *   ?state=CA              — Filter to a single state (optional)
 *
 * Allowed columns:
 *   complaint_count, complaint_density, bachelors_pct, grad_pct,
 *   avg_median_debt, avg_pell_rate, school_count, community_mentions,
 *   frustrated_pct, debt_distress_score, advocacy_readiness_score, pop_25_plus
 *
 * Limited to 5000 rows for scatter plot performance.
 * Requires authentication.
 */

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

const ALLOWED_COLUMNS = new Set([
  'complaint_count',
  'complaint_density',
  'bachelors_pct',
  'grad_pct',
  'avg_median_debt',
  'avg_pell_rate',
  'school_count',
  'community_mentions',
  'frustrated_pct',
  'debt_distress_score',
  'advocacy_readiness_score',
  'pop_25_plus',
]);

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 xCol = searchParams.get('x');
  const yCol = searchParams.get('y');
  const colorCol = searchParams.get('color') || 'state';
  const stateFilter = searchParams.get('state');

  // Validate required parameters
  if (!xCol || !yCol) {
    return NextResponse.json(
      { error: 'Both x and y parameters are required' },
      { status: 400 },
    );
  }

  // Validate column names (prevent SQL injection)
  if (!ALLOWED_COLUMNS.has(xCol)) {
    return NextResponse.json(
      { error: `Invalid x column: ${xCol}. Allowed: ${Array.from(ALLOWED_COLUMNS).join(', ')}` },
      { status: 400 },
    );
  }

  if (!ALLOWED_COLUMNS.has(yCol)) {
    return NextResponse.json(
      { error: `Invalid y column: ${yCol}. Allowed: ${Array.from(ALLOWED_COLUMNS).join(', ')}` },
      { status: 400 },
    );
  }

  // Color column can be 'state' or any allowed numeric column
  const validColorColumns = new Set([...ALLOWED_COLUMNS, 'state']);
  if (!validColorColumns.has(colorCol)) {
    return NextResponse.json(
      { error: `Invalid color column: ${colorCol}. Allowed: state, ${Array.from(ALLOWED_COLUMNS).join(', ')}` },
      { status: 400 },
    );
  }

  try {
    const conditions: string[] = [
      `${xCol} IS NOT NULL`,
      `${yCol} IS NOT NULL`,
    ];
    const values: unknown[] = [];
    let paramIndex = 1;

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

    const whereClause = `WHERE ${conditions.join(' AND ')}`;

    // Build select list with validated column names (safe from injection)
    const selectCols = new Set(['zip', 'state', xCol, yCol]);
    if (colorCol !== 'state') {
      selectCols.add(colorCol);
    }

    const selectList = Array.from(selectCols).join(', ');

    const { rows } = await query(
      `SELECT ${selectList}
       FROM zip_profiles
       ${whereClause}
       ORDER BY ${xCol} DESC
       LIMIT 5000`,
      values,
    );

    // Compute basic stats for the selected columns
    let sumX = 0;
    let sumY = 0;
    let minX = Infinity;
    let maxX = -Infinity;
    let minY = Infinity;
    let maxY = -Infinity;

    for (const row of rows) {
      const xVal = Number((row as Record<string, unknown>)[xCol] ?? 0);
      const yVal = Number((row as Record<string, unknown>)[yCol] ?? 0);
      sumX += xVal;
      sumY += yVal;
      if (xVal < minX) minX = xVal;
      if (xVal > maxX) maxX = xVal;
      if (yVal < minY) minY = yVal;
      if (yVal > maxY) maxY = yVal;
    }

    const count = rows.length;

    return NextResponse.json({
      axes: {
        x: xCol,
        y: yCol,
        color: colorCol,
      },
      filters: { state: stateFilter },
      stats: {
        count,
        x: {
          min: count > 0 ? minX : 0,
          max: count > 0 ? maxX : 0,
          avg: count > 0 ? Math.round((sumX / count) * 100) / 100 : 0,
        },
        y: {
          min: count > 0 ? minY : 0,
          max: count > 0 ? maxY : 0,
          avg: count > 0 ? Math.round((sumY / count) * 100) / 100 : 0,
        },
      },
      data: rows,
    });
  } catch (err) {
    console.error('[geo/scatter] Error:', (err as Error).message);
    return NextResponse.json(
      { error: `Failed to fetch scatter data: ${(err as Error).message}` },
      { status: 500 },
    );
  }
}