← back to Norma

app/api/graph/pulse-overlay/route.ts

148 lines

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

// ─── Types ───────────────────────────────────────────────────────────

interface OverlayNode {
  id: string;
  label: string;
  type: 'pulse_topic' | 'state';
  data: Record<string, unknown>;
}

interface OverlayEdge {
  source: string;
  target: string;
  weight: number;
  type: 'geo_heat';
}

// ─── Helpers ─────────────────────────────────────────────────────────

async function safeQuery<T extends Record<string, unknown>>(
  sql: string,
  params?: unknown[],
): Promise<T[]> {
  try {
    const { rows } = await query<T>(sql, params);
    return rows;
  } catch (err: unknown) {
    const msg = (err as Error).message ?? '';
    if (msg.includes('does not exist') || msg.includes('undefined_table')) {
      return [];
    }
    throw err;
  }
}

// ─── GET /api/graph/pulse-overlay ────────────────────────────────────

export async function GET(_request: NextRequest) {
  const auth = requireRole(_request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    // 1. Fetch all pulse topics
    const topics = await safeQuery<{
      id: string;
      name: string;
      category: string;
      urgency: string;
      description: string | null;
      created_at: string;
    }>(
      `SELECT id, name, category, urgency, description, created_at
       FROM pulse_topics
       ORDER BY
         CASE urgency
           WHEN 'critical' THEN 0
           WHEN 'high' THEN 1
           WHEN 'medium' THEN 2
           WHEN 'low' THEN 3
           ELSE 4
         END,
         created_at DESC`,
    );

    // 2. Fetch geo heat connections
    const geoHeat = await safeQuery<{
      id: number;
      topic_id: string;
      state: string;
      heat_score: number;
      article_count: number;
      sentiment_avg: number;
    }>(
      `SELECT id, topic_id, state, heat_score, article_count, sentiment_avg
       FROM pulse_geo_heat
       WHERE heat_score > 0
       ORDER BY heat_score DESC`,
    );

    // 3. Build nodes and edges
    const nodes: OverlayNode[] = [];
    const edges: OverlayEdge[] = [];
    const stateNodeIds = new Set<string>();

    // Add pulse_topic nodes
    for (const topic of topics) {
      nodes.push({
        id: `pulse-${topic.id}`,
        label: topic.name,
        type: 'pulse_topic',
        data: {
          urgency: topic.urgency,
          category: topic.category,
          description: topic.description,
          created_at: topic.created_at,
        },
      });
    }

    // Add state nodes + geo_heat edges
    for (const heat of geoHeat) {
      const stateNodeId = `state-${heat.state}`;

      // Create state node if it doesn't exist yet
      if (!stateNodeIds.has(stateNodeId)) {
        stateNodeIds.add(stateNodeId);
        nodes.push({
          id: stateNodeId,
          label: heat.state,
          type: 'state',
          data: {
            heat_score: heat.heat_score,
            article_count: heat.article_count,
            sentiment_avg: heat.sentiment_avg,
          },
        });
      }

      // Edge: topic -> state
      edges.push({
        source: `pulse-${heat.topic_id}`,
        target: stateNodeId,
        weight: Math.min(1, heat.heat_score / 100),
        type: 'geo_heat',
      });
    }

    return NextResponse.json({
      nodes,
      edges,
      stats: {
        topics: topics.length,
        states: stateNodeIds.size,
        geo_connections: geoHeat.length,
      },
    });
  } catch (err: unknown) {
    console.error('[api/graph/pulse-overlay] Error:', (err as Error).message);
    return NextResponse.json(
      { error: (err as Error).message },
      { status: 500 },
    );
  }
}