← back to Norma

app/api/graph/ideas/route.ts

403 lines

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

// ─── Types ───────────────────────────────────────────────────────────
interface GraphNode {
  id: string;
  type: 'category' | 'topic' | 'petition' | 'suggestion' | 'debt_mention';
  label: string;
  category: string | null;
  score: number;
  metadata?: Record<string, unknown>;
}

interface GraphEdge {
  source: string;
  target: string;
  weight: number;
  type?: string;
}

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

/** Safely query a table, returning empty rows if the table does not exist. */
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 ?? '';
    // relation "xxx" does not exist — table missing, return empty
    if (msg.includes('does not exist') || msg.includes('undefined_table')) {
      return [];
    }
    throw err;
  }
}

/** Parse a JSONB field that may be an array of IDs or objects. */
function parseJsonbIds(value: unknown): string[] {
  if (!value) return [];
  if (Array.isArray(value)) {
    return value.map((v) => (typeof v === 'object' && v !== null ? (v as Record<string, string>).id ?? String(v) : String(v)));
  }
  return [];
}

// ─── GET /api/graph/ideas ────────────────────────────────────────────
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 hours = Math.max(1, parseInt(searchParams.get('hours') ?? '48', 10) || 48);
    const minScore = parseFloat(searchParams.get('min_score') ?? '0') || 0;

    // ── 1. Trending topics ───────────────────────────────────────────
    const topics = await safeQuery<{
      id: string;
      topic: string;
      source: string;
      category: string | null;
      score: number;
      mentions: number;
      velocity: number | null;
      url: string | null;
      related_topics: string[] | null;
      sample_posts: unknown;
    }>(
      `SELECT id, topic, source, category, score, mentions, velocity, url,
              related_topics, sample_posts
       FROM trending_topics
       WHERE scraped_at > NOW() - ($1 || ' hours')::interval
         AND COALESCE(score, 0) >= $2
       ORDER BY score DESC
       LIMIT 200`,
      [String(hours), minScore],
    );

    // ── 2. Trending petitions ────────────────────────────────────────
    const petitions = await safeQuery<{
      id: string;
      title: string;
      platform: string;
      category: string | null;
      signature_count: number;
      growth_rate: number | null;
      is_climber: boolean;
      target: string | null;
      url: string | null;
      description: string | null;
    }>(
      `SELECT id, title, platform, category, signature_count, growth_rate,
              is_climber, target, url, description
       FROM trending_petitions
       ORDER BY signature_count DESC
       LIMIT 100`,
    );

    // ── 3. Petition suggestions ──────────────────────────────────────
    const suggestions = await safeQuery<{
      id: string;
      title: string;
      category: string | null;
      urgency: string | null;
      viability_score: number | null;
      reasoning: string | null;
      source_topics: unknown;
      source_petitions: unknown;
      status: string;
    }>(
      `SELECT id, title, category, urgency, viability_score, reasoning,
              source_topics, source_petitions, status
       FROM petition_suggestions
       WHERE status IN ('suggested', 'approved')
       ORDER BY viability_score DESC NULLS LAST
       LIMIT 50`,
    );

    // ── 4. Student debt mentions ─────────────────────────────────────
    const debtMentions = await safeQuery<{
      id: string;
      source: string;
      title: string | null;
      url: string | null;
      sentiment: string | null;
      engagement_score: number | null;
      servicers_mentioned: string[] | null;
      programs_mentioned: string[] | null;
      is_viral: boolean;
      topics: string[] | null;
    }>(
      `SELECT id, source, title, url, sentiment, engagement_score,
              servicers_mentioned, programs_mentioned, is_viral, topics
       FROM student_debt_mentions
       WHERE scraped_at > NOW() - ($1 || ' hours')::interval
       ORDER BY engagement_score DESC NULLS LAST
       LIMIT 50`,
      [String(hours)],
    );

    // ── 5. Build graph ──────────────────────────────────────────────

    const nodes: GraphNode[] = [];
    const edges: GraphEdge[] = [];

    // Track IDs for de-duplication and lookups
    const nodeIdSet = new Set<string>();
    const topicNodeMap = new Map<string, string>(); // topic text (lower) → node id
    const maxTopicScore = topics.reduce((mx, t) => Math.max(mx, Number(t.score) || 0), 1);

    // ── Category nodes (virtual) ────────────────────────────────────
    const categoryScores = new Map<string, number>();

    for (const t of topics) {
      const cat = t.category ?? 'uncategorized';
      categoryScores.set(cat, (categoryScores.get(cat) ?? 0) + (Number(t.score) || 0));
    }
    for (const p of petitions) {
      const cat = p.category ?? 'uncategorized';
      if (!categoryScores.has(cat)) categoryScores.set(cat, 0);
    }

    for (const [catName, catScore] of Array.from(categoryScores.entries())) {
      const nodeId = `cat-${catName}`;
      nodes.push({
        id: nodeId,
        type: 'category',
        label: catName,
        category: catName,
        score: catScore,
      });
      nodeIdSet.add(nodeId);
    }

    // ── Topic nodes ─────────────────────────────────────────────────
    for (const t of topics) {
      const nodeId = `topic-${t.id}`;
      const topicScore = Number(t.score) || 0;

      nodes.push({
        id: nodeId,
        type: 'topic',
        label: t.topic,
        category: t.category,
        score: topicScore,
        metadata: {
          source: t.source,
          mentions: t.mentions,
          velocity: t.velocity,
          url: t.url,
        },
      });
      nodeIdSet.add(nodeId);
      topicNodeMap.set(t.topic.toLowerCase(), nodeId);

      // Edge: topic → category
      const catId = `cat-${t.category ?? 'uncategorized'}`;
      if (nodeIdSet.has(catId)) {
        edges.push({
          source: nodeId,
          target: catId,
          weight: maxTopicScore > 0 ? topicScore / maxTopicScore : 0,
          type: 'topic_to_category',
        });
      }
    }

    // ── Cross-link topics with shared related_topics ────────────────
    const relatedIndex = new Map<string, string[]>(); // related_topic text → [topic node ids]

    for (const t of topics) {
      const nodeId = `topic-${t.id}`;
      const related = t.related_topics ?? [];
      for (const rt of related) {
        const key = rt.toLowerCase();
        if (!relatedIndex.has(key)) relatedIndex.set(key, []);
        relatedIndex.get(key)!.push(nodeId);
      }
    }

    // For each related_topic that appears in 2+ topic nodes, cross-link them
    const crossLinked = new Set<string>();
    for (const [, linkedNodes] of Array.from(relatedIndex.entries())) {
      if (linkedNodes.length < 2) continue;
      for (let i = 0; i < linkedNodes.length; i++) {
        for (let j = i + 1; j < linkedNodes.length; j++) {
          const edgeKey = [linkedNodes[i], linkedNodes[j]].sort().join('|');
          if (crossLinked.has(edgeKey)) continue;
          crossLinked.add(edgeKey);
          edges.push({
            source: linkedNodes[i],
            target: linkedNodes[j],
            weight: 0.3,
            type: 'shared_related',
          });
        }
      }
    }

    // Also link a topic to another topic if the other topic's name appears in related_topics
    for (const t of topics) {
      const nodeId = `topic-${t.id}`;
      const related = t.related_topics ?? [];
      for (const rt of related) {
        const targetNodeId = topicNodeMap.get(rt.toLowerCase());
        if (targetNodeId && targetNodeId !== nodeId) {
          const edgeKey = [nodeId, targetNodeId].sort().join('|');
          if (!crossLinked.has(edgeKey)) {
            crossLinked.add(edgeKey);
            edges.push({
              source: nodeId,
              target: targetNodeId,
              weight: 0.3,
              type: 'shared_related',
            });
          }
        }
      }
    }

    // ── Petition nodes ──────────────────────────────────────────────
    for (const p of petitions) {
      const nodeId = `pet-${p.id}`;
      nodes.push({
        id: nodeId,
        type: 'petition',
        label: p.title,
        category: p.category,
        score: Number(p.signature_count) || 0,
        metadata: {
          platform: p.platform,
          growth_rate: p.growth_rate,
          is_climber: p.is_climber,
          target: p.target,
          url: p.url,
          description: p.description,
        },
      });
      nodeIdSet.add(nodeId);

      // Edge: petition → category (if matching category exists)
      const catId = `cat-${p.category ?? 'uncategorized'}`;
      if (nodeIdSet.has(catId)) {
        edges.push({
          source: nodeId,
          target: catId,
          weight: 0.5,
          type: 'petition_to_category',
        });
      }
    }

    // ── Suggestion nodes ────────────────────────────────────────────
    const petNodeIds = new Set(petitions.map((p) => p.id));

    for (const s of suggestions) {
      const nodeId = `sug-${s.id}`;
      nodes.push({
        id: nodeId,
        type: 'suggestion',
        label: s.title,
        category: s.category,
        score: Number(s.viability_score) || 0,
        metadata: {
          urgency: s.urgency,
          reasoning: s.reasoning,
          status: s.status,
        },
      });
      nodeIdSet.add(nodeId);

      // Edge: suggestion → source topics
      const sourceTopicIds = parseJsonbIds(s.source_topics);
      for (const stId of sourceTopicIds) {
        const targetNodeId = `topic-${stId}`;
        if (nodeIdSet.has(targetNodeId)) {
          edges.push({
            source: nodeId,
            target: targetNodeId,
            weight: 0.8,
            type: 'suggestion_to_topic',
          });
        }
      }

      // Edge: suggestion → source petitions
      const sourcePetIds = parseJsonbIds(s.source_petitions);
      for (const spId of sourcePetIds) {
        const targetNodeId = `pet-${spId}`;
        if (petNodeIds.has(spId) || nodeIdSet.has(targetNodeId)) {
          edges.push({
            source: nodeId,
            target: targetNodeId,
            weight: 0.6,
            type: 'suggestion_to_petition',
          });
        }
      }
    }

    // ── Debt mention nodes (top 30) ─────────────────────────────────
    const debtSlice = debtMentions.slice(0, 30);

    for (const d of debtSlice) {
      const nodeId = `debt-${d.id}`;
      nodes.push({
        id: nodeId,
        type: 'debt_mention',
        label: d.title ?? '(untitled mention)',
        category: 'student_debt',
        score: Number(d.engagement_score) || 0,
        metadata: {
          source: d.source,
          sentiment: d.sentiment,
          servicers_mentioned: d.servicers_mentioned,
          programs_mentioned: d.programs_mentioned,
          is_viral: d.is_viral,
          url: d.url,
        },
      });
      nodeIdSet.add(nodeId);

      // Edge: debt_mention → category (education or student_debt)
      const educationCat = nodeIdSet.has('cat-education') ? 'cat-education' : null;
      const debtCat = nodeIdSet.has('cat-student_debt') ? 'cat-student_debt' : null;
      const targetCat = debtCat ?? educationCat;

      if (targetCat) {
        edges.push({
          source: nodeId,
          target: targetCat,
          weight: 0.4,
          type: 'debt_to_category',
        });
      }
    }

    // ── Stats ───────────────────────────────────────────────────────
    const stats = {
      total_nodes: nodes.length,
      total_edges: edges.length,
      categories: categoryScores.size,
      topics: topics.length,
      petitions: petitions.length,
      suggestions: suggestions.length,
      debt_mentions: debtSlice.length,
    };

    return NextResponse.json({ nodes, edges, stats });
  } catch (err: unknown) {
    console.error('[api/graph/ideas] Error:', (err as Error).message);
    return NextResponse.json(
      { error: (err as Error).message },
      { status: 500 },
    );
  }
}