`app/api/globe/states/route.ts`

84 lines
/**
 * GET /api/globe/states
 * Returns US state pulse heat data for the US view mode.
 *
 * Response:
 *   { states: StateHeat[] }
 */

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;

  try {
    // Try pulse_geo_heat table first
    const heatResult = await query(`
      SELECT
        state_code,
        state_name,
        heat_score,
        petition_count,
        article_count,
        org_count,
        avg_sentiment,
        top_topics,
        updated_at
      FROM pulse_geo_heat
      ORDER BY heat_score DESC
    `).catch(() => ({ rows: [] }));

    if (heatResult.rows.length > 0) {
      return NextResponse.json({
        states: heatResult.rows.map(r => ({
          state: String(r.state_code || r.state_name),
          state_name: String(r.state_name || r.state_code),
          heat_score: Number(r.heat_score) || 0,
          petition_count: Number(r.petition_count) || 0,
          article_count: Number(r.article_count) || 0,
          org_count: Number(r.org_count) || 0,
          avg_sentiment: r.avg_sentiment != null ? Number(r.avg_sentiment) : null,
          top_topics: Array.isArray(r.top_topics) ? r.top_topics : [],
        })),
      });
    }

    // Fallback: aggregate from organizations + pulse_topics
    const stateResult = await query(`
      SELECT
        UPPER(TRIM(o.state)) AS state_code,
        COUNT(DISTINCT o.id)::int AS org_count,
        0 AS petition_count,
        0 AS article_count,
        NULL AS avg_sentiment,
        ARRAY[]::text[] AS top_topics
      FROM organizations o
      WHERE o.state IS NOT NULL
        AND o.country IN ('US', 'USA', 'United States', '')
        AND LENGTH(TRIM(o.state)) BETWEEN 2 AND 3
      GROUP BY UPPER(TRIM(o.state))
      HAVING COUNT(DISTINCT o.id) > 0
      ORDER BY org_count DESC
    `).catch(() => ({ rows: [] }));

    return NextResponse.json({
      states: stateResult.rows.map(r => ({
        state: String(r.state_code),
        state_name: String(r.state_code),
        heat_score: Number(r.org_count) || 0,
        petition_count: 0,
        article_count: 0,
        org_count: Number(r.org_count) || 0,
        avg_sentiment: null,
        top_topics: [],
      })),
    });
  } catch (err) {
    console.error('[api/globe/states] error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch state data' }, { status: 500 });
  }
}