← back to Norma

app/api/connections/viz/route.ts

391 lines

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

/* ═══════════════════════════════════════════════════════════════════════════
   GET /api/connections/viz — Visualization-specific data shapes

   ?type=matrix    → Cross-tab of source_type × target_type counts
   ?type=tree      → Hierarchical tree: entity_types → top entities → connections
   ?type=graph     → Nodes + edges for 3D/2D force graph (sampled for performance)
   ?type=sunburst  → Nested hierarchy: link_type → source_type → target_type → count
   ?type=stats     → Summary statistics for the hub dashboard
   ?type=timeline  → Connection counts by created_at date
   ═══════════════════════════════════════════════════════════════════════════ */

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 type = searchParams.get('type') || 'stats';

    switch (type) {
      case 'matrix':
        return NextResponse.json(await fetchMatrix());
      case 'tree':
        return NextResponse.json(await fetchTree());
      case 'graph':
        return NextResponse.json(await fetchGraph(searchParams));
      case 'sunburst':
        return NextResponse.json(await fetchSunburst());
      case 'stats':
        return NextResponse.json(await fetchStats());
      case 'timeline':
        return NextResponse.json(await fetchTimeline());
      default:
        return NextResponse.json({ error: `Unknown viz type: ${type}` }, { status: 400 });
    }
  } catch (err) {
    console.error('[connections/viz] Error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to load visualization data' }, { status: 500 });
  }
}

/* ── Matrix: source_type × target_type cross-tab ──────────────────────── */

async function fetchMatrix() {
  const res = await query<{
    source_type: string;
    target_type: string;
    count: string;
  }>(`
    SELECT source_type, target_type, COUNT(*) as count
    FROM mind_map_links
    GROUP BY source_type, target_type
    ORDER BY count DESC
  `);

  // Build unique entity types
  const types = new Set<string>();
  const cells: Record<string, number> = {};

  for (const r of res.rows) {
    types.add(r.source_type);
    types.add(r.target_type);
    const key = `${r.source_type}|${r.target_type}`;
    cells[key] = parseInt(r.count, 10);
  }

  const labels = Array.from(types).sort();

  // Build matrix grid
  const matrix = labels.map((src) =>
    labels.map((tgt) => cells[`${src}|${tgt}`] || 0)
  );

  return { labels, matrix, totalTypes: labels.length };
}

/* ── Tree: hierarchical entity tree ───────────────────────────────────── */

async function fetchTree() {
  // Get top entities per type with their connection counts
  const res = await query<{
    entity_type: string;
    entity_id: string;
    entity_label: string;
    link_count: string;
  }>(`
    WITH entity_counts AS (
      SELECT source_type AS entity_type, source_id AS entity_id, source_label AS entity_label,
             COUNT(*) as link_count
      FROM mind_map_links
      WHERE source_label IS NOT NULL AND source_label != ''
      GROUP BY source_type, source_id, source_label
      UNION ALL
      SELECT target_type, target_id, target_label, COUNT(*)
      FROM mind_map_links
      WHERE target_label IS NOT NULL AND target_label != ''
      GROUP BY target_type, target_id, target_label
    ),
    aggregated AS (
      SELECT entity_type, entity_id, entity_label, SUM(link_count) as total_links
      FROM entity_counts
      GROUP BY entity_type, entity_id, entity_label
    ),
    ranked AS (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_type ORDER BY total_links DESC) as rn
      FROM aggregated
    )
    SELECT entity_type, entity_id, entity_label, total_links as link_count
    FROM ranked
    WHERE rn <= 15
    ORDER BY entity_type, total_links DESC
  `);

  // Get connection details for top entities
  const connectionRes = await query<{
    source_id: string;
    source_type: string;
    source_label: string;
    target_id: string;
    target_type: string;
    target_label: string;
    link_type: string;
  }>(`
    SELECT DISTINCT ON (source_id, target_id, link_type)
      source_id, source_type, source_label,
      target_id, target_type, target_label,
      link_type
    FROM mind_map_links
    WHERE source_label IS NOT NULL AND target_label IS NOT NULL
      AND source_label != '' AND target_label != ''
    ORDER BY source_id, target_id, link_type
    LIMIT 500
  `);

  // Build tree structure
  const typeMap: Record<string, Array<{
    id: string;
    label: string;
    linkCount: number;
    children: Array<{ id: string; label: string; type: string; linkType: string }>;
  }>> = {};

  for (const r of res.rows) {
    if (!typeMap[r.entity_type]) typeMap[r.entity_type] = [];
    typeMap[r.entity_type].push({
      id: r.entity_id,
      label: r.entity_label,
      linkCount: parseInt(r.link_count, 10),
      children: [],
    });
  }

  // Attach connections as children
  for (const c of connectionRes.rows) {
    const srcType = typeMap[c.source_type];
    if (srcType) {
      const entity = srcType.find((e) => e.id === c.source_id);
      if (entity && entity.children.length < 8) {
        entity.children.push({
          id: c.target_id,
          label: c.target_label,
          type: c.target_type,
          linkType: c.link_type,
        });
      }
    }
  }

  const tree = Object.entries(typeMap).map(([type, entities]) => ({
    type,
    entityCount: entities.length,
    entities,
  }));

  return { tree };
}

/* ── Graph: nodes + edges for force-directed layouts ──────────────────── */

async function fetchGraph(params: URLSearchParams) {
  const linkType = params.get('linkType');
  const limit = Math.min(parseInt(params.get('limit') || '200', 10), 500);

  const whereClause = linkType ? `WHERE link_type = $1` : '';
  const queryParams = linkType ? [linkType] : [];

  // Get sampled edges
  const edgeRes = await query<{
    source_id: string;
    source_type: string;
    source_label: string;
    target_id: string;
    target_type: string;
    target_label: string;
    link_type: string;
    weight: string | null;
  }>(`
    SELECT source_id, source_type, source_label,
           target_id, target_type, target_label,
           link_type, weight
    FROM mind_map_links
    ${whereClause}
    ORDER BY RANDOM()
    LIMIT ${limit}
  `, queryParams);

  // Build node set
  const nodeMap = new Map<string, { id: string; type: string; label: string; connections: number }>();

  for (const e of edgeRes.rows) {
    if (!nodeMap.has(e.source_id)) {
      nodeMap.set(e.source_id, {
        id: e.source_id,
        type: e.source_type,
        label: e.source_label || e.source_type,
        connections: 0,
      });
    }
    if (!nodeMap.has(e.target_id)) {
      nodeMap.set(e.target_id, {
        id: e.target_id,
        type: e.target_type,
        label: e.target_label || e.target_type,
        connections: 0,
      });
    }
    const src = nodeMap.get(e.source_id)!;
    const tgt = nodeMap.get(e.target_id)!;
    src.connections++;
    tgt.connections++;
  }

  const nodes = Array.from(nodeMap.values());
  const edges = edgeRes.rows.map((e) => ({
    source: e.source_id,
    target: e.target_id,
    type: e.link_type,
    strength: e.weight ? parseFloat(e.weight) : 0.5,
  }));

  // Get available link types for filter
  const typesRes = await query<{ link_type: string; count: string }>(
    `SELECT link_type, COUNT(*) as count FROM mind_map_links GROUP BY link_type ORDER BY count DESC`
  );

  return {
    nodes,
    edges,
    linkTypes: typesRes.rows.map((r) => ({ type: r.link_type, count: parseInt(r.count, 10) })),
  };
}

/* ── Sunburst: nested hierarchy ───────────────────────────────────────── */

async function fetchSunburst() {
  const res = await query<{
    link_type: string;
    source_type: string;
    target_type: string;
    count: string;
  }>(`
    SELECT link_type, source_type, target_type, COUNT(*) as count
    FROM mind_map_links
    GROUP BY link_type, source_type, target_type
    ORDER BY count DESC
  `);

  // Build sunburst hierarchy: root → link_type → source_type → target_type
  const linkTypeMap: Record<string, Record<string, Record<string, number>>> = {};
  let total = 0;

  for (const r of res.rows) {
    const cnt = parseInt(r.count, 10);
    total += cnt;
    if (!linkTypeMap[r.link_type]) linkTypeMap[r.link_type] = {};
    if (!linkTypeMap[r.link_type][r.source_type]) linkTypeMap[r.link_type][r.source_type] = {};
    linkTypeMap[r.link_type][r.source_type][r.target_type] = cnt;
  }

  const children = Object.entries(linkTypeMap).map(([linkType, sources]) => ({
    name: linkType,
    children: Object.entries(sources).map(([srcType, targets]) => ({
      name: srcType,
      children: Object.entries(targets).map(([tgtType, count]) => ({
        name: tgtType,
        value: count,
      })),
    })),
  }));

  return {
    name: 'All Connections',
    total,
    children,
  };
}

/* ── Stats: summary for hub dashboard ─────────────────────────────────── */

async function fetchStats() {
  const [totalRes, typeRes, entityRes, topEntitiesRes] = await Promise.all([
    query<{ total: string }>(`SELECT COUNT(*) as total FROM mind_map_links`),
    query<{ link_type: string; count: string }>(
      `SELECT link_type, COUNT(*) as count FROM mind_map_links GROUP BY link_type ORDER BY count DESC`
    ),
    query<{ entity_type: string; count: string }>(`
      SELECT entity_type, COUNT(DISTINCT entity_id) as count FROM (
        SELECT source_type AS entity_type, source_id AS entity_id FROM mind_map_links
        UNION
        SELECT target_type, target_id FROM mind_map_links
      ) combined
      GROUP BY entity_type
      ORDER BY count DESC
    `),
    query<{ label: string; entity_type: string; total_links: string }>(`
      WITH entity_counts AS (
        SELECT source_type AS entity_type, source_id, source_label AS label, COUNT(*) AS cnt
        FROM mind_map_links WHERE source_label IS NOT NULL AND source_label != ''
        GROUP BY source_type, source_id, source_label
        UNION ALL
        SELECT target_type, target_id, target_label, COUNT(*)
        FROM mind_map_links WHERE target_label IS NOT NULL AND target_label != ''
        GROUP BY target_type, target_id, target_label
      )
      SELECT label, entity_type, SUM(cnt) AS total_links
      FROM entity_counts
      GROUP BY label, entity_type
      ORDER BY total_links DESC
      LIMIT 10
    `),
  ]);

  return {
    totalConnections: parseInt(totalRes.rows[0]?.total ?? '0', 10),
    linkTypes: typeRes.rows.map((r) => ({
      type: r.link_type,
      count: parseInt(r.count, 10),
    })),
    entityTypes: entityRes.rows.map((r) => ({
      type: r.entity_type,
      uniqueEntities: parseInt(r.count, 10),
    })),
    topEntities: topEntitiesRes.rows.map((r) => ({
      label: r.label,
      type: r.entity_type,
      totalLinks: parseInt(r.total_links, 10),
    })),
  };
}

/* ── Timeline: connection growth over time ────────────────────────────── */

async function fetchTimeline() {
  const res = await query<{
    day: string;
    link_type: string;
    count: string;
  }>(`
    SELECT DATE(created_at) as day, link_type, COUNT(*) as count
    FROM mind_map_links
    WHERE created_at IS NOT NULL
    GROUP BY DATE(created_at), link_type
    ORDER BY day
  `);

  // Group by day
  const dayMap: Record<string, Record<string, number>> = {};
  const allTypes = new Set<string>();

  for (const r of res.rows) {
    const day = r.day;
    if (!dayMap[day]) dayMap[day] = {};
    dayMap[day][r.link_type] = parseInt(r.count, 10);
    allTypes.add(r.link_type);
  }

  const timeline = Object.entries(dayMap)
    .sort(([a], [b]) => a.localeCompare(b))
    .map(([day, types]) => ({
      date: day,
      ...types,
      total: Object.values(types).reduce((s, v) => s + v, 0),
    }));

  return { timeline, linkTypes: Array.from(allTypes) };
}