`app/api/geo/topic-grid/route.ts`

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

/**
 * GET /api/geo/topic-grid
 *
 * Returns a matrix of topic x state heat values suitable for a grid heatmap.
 *
 * Response shape:
 * {
 *   topics: [{ id, name, category, urgency }],
 *   states: ['CA', 'TX', ...],
 *   matrix: [[value, ...], ...],          // matrix[topicIdx][stateIdx]
 *   sentiments: [[value, ...], ...],      // parallel sentiment matrix
 *   articles: [[value, ...], ...],        // parallel article-count matrix
 *   maxValue: number                       // global max for color scale
 * }
 */
export async function GET(_request: NextRequest) {
  const auth = requireRole(_request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;
  try {
    // 1) Fetch all topics
    const topicsResult = await query<{
      id: string;
      name: string;
      category: string;
      urgency: string;
    }>(
      `SELECT id, name, category, urgency
       FROM pulse_topics
       ORDER BY
         CASE urgency
           WHEN 'critical' THEN 0
           WHEN 'high'     THEN 1
           WHEN 'medium'   THEN 2
           ELSE 3
         END,
         name`,
    );
    const topics = topicsResult.rows;

    // 2) Fetch all heat data in a single query
    const heatResult = await query<{
      topic_id: string;
      state: string;
      heat_score: number;
      article_count: number;
      sentiment_avg: number;
    }>(
      `SELECT topic_id, state, heat_score, article_count, sentiment_avg
       FROM pulse_geo_heat
       ORDER BY topic_id, state`,
    );

    // 3) Build ordered list of unique states
    const stateSet = new Set<string>();
    for (const row of heatResult.rows) {
      stateSet.add(row.state);
    }
    const states = Array.from(stateSet).sort();

    // 4) Build lookup: topicId → { state → row }
    const lookup = new Map<string, Map<string, (typeof heatResult.rows)[0]>>();
    for (const row of heatResult.rows) {
      if (!lookup.has(row.topic_id)) {
        lookup.set(row.topic_id, new Map());
      }
      lookup.get(row.topic_id)!.set(row.state, row);
    }

    // 5) Build matrices (topics = rows, states = columns)
    let maxValue = 0;
    const matrix: number[][] = [];
    const sentiments: number[][] = [];
    const articles: number[][] = [];

    for (const topic of topics) {
      const heatRow: number[] = [];
      const sentRow: number[] = [];
      const artRow: number[] = [];

      const topicMap = lookup.get(topic.id);

      for (const st of states) {
        const cell = topicMap?.get(st);
        const heat = cell ? Number(cell.heat_score) : 0;
        heatRow.push(heat);
        sentRow.push(cell ? Number(cell.sentiment_avg) : 0);
        artRow.push(cell ? Number(cell.article_count) : 0);
        if (heat > maxValue) maxValue = heat;
      }

      matrix.push(heatRow);
      sentiments.push(sentRow);
      articles.push(artRow);
    }

    return NextResponse.json({
      topics: topics.map((t) => ({
        id: t.id,
        name: t.name,
        category: t.category,
        urgency: t.urgency,
      })),
      states,
      matrix,
      sentiments,
      articles,
      maxValue,
    });
  } catch (err) {
    console.error('[topic-grid] Error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch topic grid data' }, { status: 500 });
  }
}