`app/api/geo/state-heat/route.ts`

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

/* ─── State abbreviation → full name mapping ──────────────────────────────── */
const STATE_NAMES: Record<string, string> = {
  AL: 'Alabama', AK: 'Alaska', AZ: 'Arizona', AR: 'Arkansas', CA: 'California',
  CO: 'Colorado', CT: 'Connecticut', DE: 'Delaware', FL: 'Florida', GA: 'Georgia',
  HI: 'Hawaii', ID: 'Idaho', IL: 'Illinois', IN: 'Indiana', IA: 'Iowa',
  KS: 'Kansas', KY: 'Kentucky', LA: 'Louisiana', ME: 'Maine', MD: 'Maryland',
  MA: 'Massachusetts', MI: 'Michigan', MN: 'Minnesota', MS: 'Mississippi',
  MO: 'Missouri', MT: 'Montana', NE: 'Nebraska', NV: 'Nevada', NH: 'New Hampshire',
  NJ: 'New Jersey', NM: 'New Mexico', NY: 'New York', NC: 'North Carolina',
  ND: 'North Dakota', OH: 'Ohio', OK: 'Oklahoma', OR: 'Oregon', PA: 'Pennsylvania',
  RI: 'Rhode Island', SC: 'South Carolina', SD: 'South Dakota', TN: 'Tennessee',
  TX: 'Texas', UT: 'Utah', VT: 'Vermont', VA: 'Virginia', WA: 'Washington',
  WV: 'West Virginia', WI: 'Wisconsin', WY: 'Wyoming', DC: 'District of Columbia',
};

type Metric = 'pulse_activity' | 'cfpb_complaints' | 'sentiment' | 'petition_count';

const VALID_METRICS: Metric[] = ['pulse_activity', 'cfpb_complaints', 'sentiment', 'petition_count'];

/**
 * GET /api/geo/state-heat?metric=pulse_activity|cfpb_complaints|sentiment|petition_count
 *
 * Returns state-level aggregated values for the requested metric, plus min/max
 * for color scale calculations on the frontend.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { searchParams } = new URL(request.url);
    const metric = (searchParams.get('metric') || 'pulse_activity') as Metric;

    if (!VALID_METRICS.includes(metric)) {
      return NextResponse.json(
        { error: `Invalid metric. Must be one of: ${VALID_METRICS.join(', ')}` },
        { status: 400 },
      );
    }

    let states: { state: string; value: number; label: string }[] = [];

    switch (metric) {
      case 'pulse_activity': {
        const result = await query<{ state: string; value: number }>(
          `SELECT state, ROUND(SUM(heat_score)::numeric, 1) AS value
           FROM pulse_geo_heat
           GROUP BY state
           ORDER BY value DESC`,
        );
        states = result.rows.map((r) => ({
          state: r.state,
          value: Number(r.value),
          label: STATE_NAMES[r.state] || r.state,
        }));
        break;
      }

      case 'cfpb_complaints': {
        const result = await query<{ state: string; value: number }>(
          `SELECT state, COUNT(*)::int AS value
           FROM cfpb_complaints
           WHERE state IS NOT NULL AND state != ''
           GROUP BY state
           ORDER BY value DESC`,
        );
        states = result.rows.map((r) => ({
          state: r.state,
          value: Number(r.value),
          label: STATE_NAMES[r.state] || r.state,
        }));
        break;
      }

      case 'sentiment': {
        const result = await query<{ state: string; value: number }>(
          `SELECT state, ROUND(AVG(sentiment_avg)::numeric, 3) AS value
           FROM pulse_geo_heat
           GROUP BY state
           ORDER BY value DESC`,
        );
        states = result.rows.map((r) => ({
          state: r.state,
          value: Number(r.value),
          label: STATE_NAMES[r.state] || r.state,
        }));
        break;
      }

      case 'petition_count': {
        // Use article_count sum from pulse_geo_heat as proxy for petition activity
        const result = await query<{ state: string; value: number }>(
          `SELECT state, SUM(article_count)::int AS value
           FROM pulse_geo_heat
           GROUP BY state
           ORDER BY value DESC`,
        );
        states = result.rows.map((r) => ({
          state: r.state,
          value: Number(r.value),
          label: STATE_NAMES[r.state] || r.state,
        }));
        break;
      }
    }

    // Calculate min / max for color-scale
    const values = states.map((s) => s.value);
    const min = values.length > 0 ? Math.min(...values) : 0;
    const max = values.length > 0 ? Math.max(...values) : 0;

    return NextResponse.json({ states, min, max, metric });
  } catch (err) {
    console.error('[state-heat] Error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch state heat data' }, { status: 500 });
  }
}