← back to Norma

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

182 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: 'pipeline_item';
  data: Record<string, unknown>;
}

interface OverlayEdge {
  source: string;
  target: string;
  type: 'pipeline_flow';
  animated: boolean;
}

// ─── 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;
  }
}

/** Pipeline statuses in order of progression. */
const STATUS_ORDER = ['draft', 'reviewed', 'approved', 'posting', 'live', 'tracking'];

// ─── GET /api/graph/pipeline-overlay ─────────────────────────────────

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

  try {
    // Fetch all non-draft pipeline items
    const items = await safeQuery<{
      id: string;
      pulse_topic_id: string | null;
      title: string;
      body: string;
      target: string | null;
      category: string | null;
      platform: string;
      status: string;
      ai_confidence: number | null;
      signature_count: number;
      signature_goal: number | null;
      geo_states: string[] | null;
      posted_at: string | null;
      created_at: string;
      updated_at: string;
    }>(
      `SELECT id, pulse_topic_id, title, body, target, category,
              platform, status, ai_confidence, signature_count,
              signature_goal, geo_states, posted_at, created_at, updated_at
       FROM petition_pipeline
       WHERE status != 'draft'
       ORDER BY
         CASE status
           WHEN 'live' THEN 0
           WHEN 'tracking' THEN 1
           WHEN 'posting' THEN 2
           WHEN 'approved' THEN 3
           WHEN 'reviewed' THEN 4
           ELSE 5
         END,
         updated_at DESC`,
    );

    const nodes: OverlayNode[] = [];
    const edges: OverlayEdge[] = [];
    const seenPlatforms = new Set<string>();
    const seenCategories = new Set<string>();

    // Create pipeline_item nodes
    for (const item of items) {
      const isActive = item.status === 'posting' || item.status === 'live';

      nodes.push({
        id: `pipe-${item.id}`,
        label: item.title.length > 60 ? item.title.slice(0, 57) + '...' : item.title,
        type: 'pipeline_item',
        data: {
          status: item.status,
          platform: item.platform,
          confidence: item.ai_confidence,
          signatures: item.signature_count,
          signature_goal: item.signature_goal,
          target: item.target,
          category: item.category,
          geo_states: item.geo_states,
          posted_at: item.posted_at,
          created_at: item.created_at,
          updated_at: item.updated_at,
          body_preview: item.body.length > 120 ? item.body.slice(0, 117) + '...' : item.body,
        },
      });

      // Track platform for virtual platform nodes
      if (item.platform) {
        seenPlatforms.add(item.platform);
      }

      // Track category for virtual category nodes
      if (item.category) {
        seenCategories.add(item.category);
      }

      // Edge: pulse_topic -> pipeline_item (if linked to a pulse topic)
      if (item.pulse_topic_id) {
        edges.push({
          source: `pulse-${item.pulse_topic_id}`,
          target: `pipe-${item.id}`,
          type: 'pipeline_flow',
          animated: isActive,
        });
      }

      // Edge: pipeline_item -> platform node
      if (item.platform) {
        edges.push({
          source: `pipe-${item.id}`,
          target: `platform-${item.platform}`,
          type: 'pipeline_flow',
          animated: isActive,
        });
      }
    }

    // Create virtual platform destination nodes (for the flow visualization)
    for (const platform of seenPlatforms) {
      nodes.push({
        id: `platform-${platform}`,
        label: platform.charAt(0).toUpperCase() + platform.slice(1),
        type: 'pipeline_item',
        data: {
          status: 'platform',
          platform,
          is_platform_node: true,
        },
      });
    }

    // Build status distribution
    const statusCounts: Record<string, number> = {};
    for (const item of items) {
      statusCounts[item.status] = (statusCounts[item.status] || 0) + 1;
    }

    return NextResponse.json({
      nodes,
      edges,
      stats: {
        total_items: items.length,
        platforms: seenPlatforms.size,
        status_distribution: statusCounts,
        status_order: STATUS_ORDER,
      },
    });
  } catch (err: unknown) {
    console.error('[api/graph/pipeline-overlay] Error:', (err as Error).message);
    return NextResponse.json(
      { error: (err as Error).message },
      { status: 500 },
    );
  }
}