← back to Norma

app/api/graph/partners/route.ts

514 lines

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

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

interface CenterNode {
  id: string;
  label: string;
  description: string;
}

interface IssueNode {
  id: string;
  label: string;
  category: string;
  strength: number;
  color: string;
}

interface OrgNode {
  id: string;
  label: string;
  type: 'coalition' | 'advocacy_group' | 'government' | 'petition_creator';
  shared_issues: string[];
  url: string | null;
  description: string | null;
}

interface OpportunityNode {
  id: string;
  label: string;
  category: string;
  source_org: string | null;
  uniqueness_score: number;
  description: string | null;
}

interface Connection {
  source: string;
  target: string;
  type: 'core_issue' | 'shared_cause' | 'opportunity';
}

// ─── Constants ───────────────────────────────────────────────────────

// Core issues are loaded dynamically from org brand data via getBrand().focusAreas
// Fallback used if brand data is unavailable
const DEFAULT_CORE_ISSUES = ['student_debt', 'education', 'consumer_protection', 'economic_justice'];

/** Map category names to display-friendly labels. */
const CATEGORY_DISPLAY: Record<string, string> = {
  student_debt: 'Education / Debt',
  education: 'Education',
  consumer_protection: 'Consumer Protection',
  economic_justice: 'Economic Justice',
  healthcare: 'Healthcare',
  housing: 'Housing',
  labor: 'Labor Rights',
  environment: 'Environment',
  civil_rights: 'Civil Rights',
  immigration: 'Immigration',
  technology: 'Technology',
  government: 'Government Reform',
  debt_cancellation: 'Debt Cancellation',
  economy: 'Economy',
  policy: 'Policy Reform',
  workforce: 'Workforce',
  health: 'Health Equity',
};

/** Map category names to hex colors for the visualization. */
const CATEGORY_COLORS: Record<string, string> = {
  student_debt: '#ef4444',
  education: '#3b82f6',
  consumer_protection: '#22c55e',
  economic_justice: '#eab308',
  healthcare: '#ec4899',
  housing: '#f97316',
  labor: '#8b5cf6',
  environment: '#10b981',
  civil_rights: '#6366f1',
  immigration: '#14b8a6',
  technology: '#06b6d4',
  government: '#a855f7',
  debt_cancellation: '#f43f5e',
  economy: '#eab308',
  policy: '#8b5cf6',
  workforce: '#ec4899',
  health: '#22c55e',
};

const DEFAULT_COLOR = '#71717a';

// ─── 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 ?? '';
    if (msg.includes('does not exist') || msg.includes('undefined_table')) {
      return [];
    }
    throw err;
  }
}

/** Map hend_resources source_type to our visualization type. */
function mapResourceType(
  sourceType: string,
): 'coalition' | 'advocacy_group' | 'government' | 'petition_creator' {
  switch (sourceType) {
    case 'coalition_org':
      return 'coalition';
    case 'advocacy_org':
    case 'legal_aid':
      return 'advocacy_group';
    case 'government_program':
      return 'government';
    default:
      return 'advocacy_group';
  }
}

/** Normalize a category string for matching. */
function normalizeCategory(cat: string): string {
  return cat.toLowerCase().replace(/[\s-]+/g, '_').trim();
}

/** Find which issue IDs a set of topic strings match against. */
function matchIssuesToTopics(
  topics: string[],
  issueMap: Map<string, string>,
): string[] {
  const matched: string[] = [];
  for (const topic of topics) {
    const norm = normalizeCategory(topic);
    // Direct match
    const directId = issueMap.get(norm);
    if (directId && !matched.includes(directId)) {
      matched.push(directId);
    }
    // Partial match: "student_debt_relief" matches "student_debt"
    for (const [issueCat, iId] of Array.from(issueMap.entries())) {
      if ((norm.includes(issueCat) || issueCat.includes(norm)) && !matched.includes(iId)) {
        matched.push(iId);
      }
    }
  }
  return matched;
}

// ─── GET /api/graph/partners ─────────────────────────────────────────
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    // ── Center node ─────────────────────────────────────────────────
    const brand = await getBrand();
    const coreIssues = brand.focusAreas.length > 0
      ? brand.focusAreas.map(a => a.toLowerCase().replace(/[\s-]+/g, '_'))
      : DEFAULT_CORE_ISSUES;

    const center: CenterNode = {
      id: 'center',
      label: brand.shortName,
      description: brand.name,
    };

    // ── Ring 1: Core Issues ─────────────────────────────────────────
    // Get categories from trending_topics (last 7 days)
    const topicCategories = await safeQuery<{
      category: string;
      topic_count: number;
      total_score: number;
    }>(
      `SELECT category, COUNT(*)::int AS topic_count,
              COALESCE(SUM(score), 0)::numeric AS total_score
       FROM trending_topics
       WHERE scraped_at > NOW() - INTERVAL '7 days'
         AND category IS NOT NULL
       GROUP BY category
       ORDER BY total_score DESC`,
    );

    // Get categories from student debt mention topics
    const debtTopicCategories = await safeQuery<{
      topic: string;
      mention_count: number;
    }>(
      `SELECT unnest(topics) AS topic, COUNT(*)::int AS mention_count
       FROM student_debt_mentions
       WHERE scraped_at > NOW() - INTERVAL '7 days'
         AND topics IS NOT NULL
       GROUP BY topic
       ORDER BY mention_count DESC
       LIMIT 20`,
    );

    // Combine DB categories with org core issues
    const issueCategoryMap = new Map<string, { topicCount: number; totalScore: number }>();

    // Always seed with core issues
    for (const core of coreIssues) {
      issueCategoryMap.set(core, { topicCount: 0, totalScore: 0 });
    }

    // Merge trending topic categories
    for (const tc of topicCategories) {
      const norm = normalizeCategory(tc.category);
      const existing = issueCategoryMap.get(norm);
      issueCategoryMap.set(norm, {
        topicCount: (existing?.topicCount ?? 0) + Number(tc.topic_count),
        totalScore: (existing?.totalScore ?? 0) + Number(tc.total_score),
      });
    }

    // Merge debt mention topics
    for (const dt of debtTopicCategories) {
      const norm = normalizeCategory(dt.topic);
      const existing = issueCategoryMap.get(norm);
      if (existing) {
        existing.topicCount += Number(dt.mention_count);
      } else {
        issueCategoryMap.set(norm, {
          topicCount: Number(dt.mention_count),
          totalScore: 0,
        });
      }
    }

    // Sort by combined relevance
    const sortedIssues = Array.from(issueCategoryMap.entries()).sort(
      (a, b) =>
        b[1].totalScore + b[1].topicCount - (a[1].totalScore + a[1].topicCount),
    );

    const ring1: IssueNode[] = [];
    const issueIdMap = new Map<string, string>(); // normalized category → issue node id

    for (const [cat, data] of sortedIssues) {
      const nodeId = `issue-${cat}`;
      issueIdMap.set(cat, nodeId);
      ring1.push({
        id: nodeId,
        label:
          CATEGORY_DISPLAY[cat] ??
          cat
            .replace(/_/g, ' ')
            .replace(/\b\w/g, (c) => c.toUpperCase()),
        category: cat,
        strength: data.topicCount,
        color: CATEGORY_COLORS[cat] ?? DEFAULT_COLOR,
      });
    }

    // ── Ring 2: Organizations ───────────────────────────────────────

    const ring2: OrgNode[] = [];

    // a) HEND coalition organizations
    const hendOrgs = await safeQuery<{
      id: string;
      title: string;
      organization: string | null;
      summary: string | null;
      source_type: string;
      policy_topics: string[] | null;
      states_mentioned: string[] | null;
      url: string | null;
    }>(
      `SELECT id, title, organization, summary, source_type,
              policy_topics, states_mentioned, url
       FROM hend_resources
       WHERE source_type IN ('coalition_org', 'advocacy_org', 'legal_aid',
                             'government_program', 'campaign')
       LIMIT 40`,
    );

    for (const org of hendOrgs) {
      const name = org.organization ?? org.title;
      const nodeId = `org-${org.id}`;
      const orgTopics = org.policy_topics ?? [];

      // Find which Ring 1 issues this org shares
      const sharedIssues = matchIssuesToTopics(orgTopics, issueIdMap);

      // Fallback: link to student_debt if no topic match
      if (sharedIssues.length === 0) {
        const sdIssue = issueIdMap.get('student_debt');
        if (sdIssue) sharedIssues.push(sdIssue);
      }

      ring2.push({
        id: nodeId,
        label: name,
        type: mapResourceType(org.source_type),
        shared_issues: sharedIssues,
        url: org.url,
        description: org.summary,
      });
    }

    // b) Petition creators who appear on multiple petitions
    const petitionCreators = await safeQuery<{
      creator: string;
      platform: string;
      petition_count: number;
      categories: string[] | null;
    }>(
      `SELECT creator, platform, COUNT(*)::int AS petition_count,
              array_agg(DISTINCT category) FILTER (WHERE category IS NOT NULL) AS categories
       FROM trending_petitions
       WHERE creator IS NOT NULL
       GROUP BY creator, platform
       HAVING COUNT(*) >= 2
       ORDER BY petition_count DESC
       LIMIT 20`,
    );

    for (const pc of petitionCreators) {
      const slug = pc.creator
        .toLowerCase()
        .replace(/\s+/g, '-')
        .replace(/[^a-z0-9-]/g, '');
      const nodeId = `org-pc-${slug}`;
      const categories = (pc.categories ?? []).filter(Boolean);
      const sharedIssues = matchIssuesToTopics(categories, issueIdMap);

      ring2.push({
        id: nodeId,
        label: pc.creator,
        type: 'petition_creator',
        shared_issues: sharedIssues,
        url: null,
        description: `${pc.petition_count} petitions on ${pc.platform}`,
      });
    }

    // ── Ring 3: Opportunities ───────────────────────────────────────

    const ring3: OpportunityNode[] = [];
    let oppCounter = 0;

    // a) Emerging trending categories with high scores outside org core
    const emergingCategories = await safeQuery<{
      category: string;
      cnt: number;
      avg_score: number;
    }>(
      `SELECT DISTINCT category,
              COUNT(*)::int AS cnt,
              AVG(score)::numeric AS avg_score
       FROM trending_topics
       WHERE category NOT IN ('education', 'debt_cancellation', 'consumer_protection')
         AND scraped_at > NOW() - INTERVAL '7 days'
         AND COALESCE(score, 0) > 100
       GROUP BY category
       HAVING COUNT(*) >= 3
       ORDER BY avg_score DESC
       LIMIT 15`,
    );

    for (const ec of emergingCategories) {
      if (!ec.category) continue;
      oppCounter++;
      const normCat = normalizeCategory(ec.category);
      const isCore = coreIssues.includes(normCat);

      // Uniqueness = how different from org core (100 if totally unrelated)
      let uniqueness = 80;
      if (isCore) uniqueness = 10;
      else if (['debt_cancellation', 'student_loans', 'financial_aid'].includes(normCat))
        uniqueness = 20;
      else if (['healthcare', 'housing', 'labor'].includes(normCat))
        uniqueness = 50;

      ring3.push({
        id: `opp-${oppCounter}`,
        label: `${CATEGORY_DISPLAY[normCat] ?? ec.category} Opportunity`,
        category: normCat,
        source_org: null,
        uniqueness_score: uniqueness,
        description: `${ec.cnt} trending topics averaging ${Number(ec.avg_score).toFixed(0)} score in ${ec.category}`,
      });
    }

    // b) Non-core topics from HEND organizations
    for (const org of hendOrgs) {
      if (ring3.length >= 25) break;
      const orgTopics = org.policy_topics ?? [];
      for (const topic of orgTopics) {
        if (ring3.length >= 25) break;
        const normTopic = normalizeCategory(topic);
        if (coreIssues.includes(normTopic)) continue;
        if (ring3.some((o) => o.category === normTopic)) continue;

        oppCounter++;
        ring3.push({
          id: `opp-${oppCounter}`,
          label: `${CATEGORY_DISPLAY[normTopic] ?? topic.replace(/_/g, ' ')} via ${org.organization ?? org.title}`,
          category: normTopic,
          source_org: `org-${org.id}`,
          uniqueness_score: 60,
          description: `Topic from ${org.organization ?? org.title} outside core focus`,
        });
      }
    }

    // c) Non-core categories from petition creators
    for (const pc of petitionCreators) {
      if (ring3.length >= 25) break;
      const categories = (pc.categories ?? []).filter(Boolean);
      for (const cat of categories) {
        if (ring3.length >= 25) break;
        const normCat = normalizeCategory(cat);
        if (coreIssues.includes(normCat)) continue;
        if (ring3.some((o) => o.category === normCat)) continue;

        oppCounter++;
        const slug = pc.creator
          .toLowerCase()
          .replace(/\s+/g, '-')
          .replace(/[^a-z0-9-]/g, '');
        ring3.push({
          id: `opp-${oppCounter}`,
          label: `${CATEGORY_DISPLAY[normCat] ?? cat} via ${pc.creator}`,
          category: normCat,
          source_org: `org-pc-${slug}`,
          uniqueness_score: 55,
          description: `Non-core category from petition creator ${pc.creator}`,
        });
      }
    }

    // ── Connections ─────────────────────────────────────────────────
    const connections: Connection[] = [];

    // Center org -> each Ring 1 issue
    for (const issue of ring1) {
      connections.push({
        source: 'center',
        target: issue.id,
        type: 'core_issue',
      });
    }

    // Each Ring 1 issue -> connected Ring 2 orgs
    for (const org of ring2) {
      for (const issueId of org.shared_issues) {
        connections.push({
          source: issueId,
          target: org.id,
          type: 'shared_cause',
        });
      }
    }

    // Each Ring 2 org -> Ring 3 opportunities (via source_org)
    // Or connect opportunities to the most relevant issue if no source_org
    for (const opp of ring3) {
      if (opp.source_org) {
        connections.push({
          source: opp.source_org,
          target: opp.id,
          type: 'opportunity',
        });
      } else {
        const issueId = issueIdMap.get(opp.category);
        if (issueId) {
          connections.push({
            source: issueId,
            target: opp.id,
            type: 'opportunity',
          });
        }
      }
    }

    // ── Response ────────────────────────────────────────────────────
    const stats = {
      issues: ring1.length,
      organizations: ring2.length,
      opportunities: ring3.length,
      total_connections: connections.length,
    };

    return NextResponse.json({
      rings: {
        center,
        ring1,
        ring2,
        ring3,
      },
      connections,
      stats,
    });
  } catch (err: unknown) {
    console.error('[api/graph/partners] Error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 },
    );
  }
}