`app/api/graph/journalist/[id]/route.ts`

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

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

interface JournalistGraphNode {
  id: string;
  type: 'journalist' | 'article' | 'topic' | 'organization' | 'contact' | 'category';
  label: string;
  data: Record<string, unknown>;
}

interface JournalistGraphEdge {
  source: string;
  target: string;
  type: string;
  weight: number;
}

// ─── GET /api/graph/journalist/[id] ──────────────────────────────────────────

export async function GET(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> },
) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { id } = await params;

    // Fetch journalist
    const jResult = await query(
      'SELECT * FROM journalists WHERE id = $1',
      [id],
    );
    if (jResult.rowCount === 0) {
      return NextResponse.json({ error: 'Journalist not found' }, { status: 404 });
    }
    const journalist = jResult.rows[0];

    const nodes: JournalistGraphNode[] = [];
    const edges: JournalistGraphEdge[] = [];
    const nodeIdSet = new Set<string>();

    // Center node: journalist
    const jNodeId = `jrn-${id}`;
    nodes.push({
      id: jNodeId,
      type: 'journalist',
      label: journalist.name as string,
      data: {
        outlet: journalist.outlet,
        beat: journalist.beat,
        email: journalist.email,
        twitter: journalist.twitter_handle,
      },
    });
    nodeIdSet.add(jNodeId);

    // Articles (limit 30)
    const articles = await query(
      `SELECT id, title, url, outlet, published_date, summary, topics, sentiment,
              mentioned_people, mentioned_orgs, word_count
       FROM journalist_articles
       WHERE journalist_id = $1
       ORDER BY published_date DESC NULLS LAST
       LIMIT 30`,
      [id],
    );

    for (const a of articles.rows) {
      const artNodeId = `art-${a.id}`;
      nodes.push({
        id: artNodeId,
        type: 'article',
        label: (a.title as string).slice(0, 60),
        data: {
          title: a.title,
          url: a.url,
          outlet: a.outlet,
          published_date: a.published_date,
          summary: a.summary,
          sentiment: a.sentiment,
          word_count: a.word_count,
        },
      });
      nodeIdSet.add(artNodeId);
      edges.push({ source: jNodeId, target: artNodeId, type: 'wrote', weight: 1.0 });
    }

    // Topics: aggregate from articles, match against trending_topics
    const topicAgg = await query(
      `SELECT unnest(topics) as topic, COUNT(*) as cnt
       FROM journalist_articles WHERE journalist_id = $1
       GROUP BY topic ORDER BY cnt DESC LIMIT 20`,
      [id],
    );

    for (const t of topicAgg.rows) {
      const topicName = t.topic as string;
      const cnt = Number(t.cnt);

      // Try to find real topic in trending_topics
      const topicMatch = await query(
        `SELECT id, topic, score FROM trending_topics
         WHERE LOWER(topic) LIKE '%' || $1 || '%'
            OR $1 LIKE '%' || LOWER(topic) || '%'
         ORDER BY score DESC LIMIT 1`,
        [topicName.toLowerCase()],
      );

      let topicNodeId: string;
      if (topicMatch.rowCount && topicMatch.rowCount > 0) {
        topicNodeId = `topic-${topicMatch.rows[0].id}`;
        if (!nodeIdSet.has(topicNodeId)) {
          nodes.push({
            id: topicNodeId,
            type: 'topic',
            label: topicMatch.rows[0].topic as string,
            data: { score: topicMatch.rows[0].score, mention_count: cnt },
          });
          nodeIdSet.add(topicNodeId);
        }
      } else {
        topicNodeId = `vtopic-${topicName.toLowerCase().replace(/\s+/g, '-')}`;
        if (!nodeIdSet.has(topicNodeId)) {
          nodes.push({
            id: topicNodeId,
            type: 'topic',
            label: topicName,
            data: { score: 0, mention_count: cnt, virtual: true },
          });
          nodeIdSet.add(topicNodeId);
        }
      }

      const weight = Math.min(1.0, 0.3 + cnt * 0.15);
      edges.push({ source: jNodeId, target: topicNodeId, type: 'covers_topic', weight });

      // Connect articles to their topics
      for (const a of articles.rows) {
        const artTopics = (a.topics as string[]) || [];
        if (artTopics.some(at => at.toLowerCase() === topicName.toLowerCase())) {
          edges.push({ source: `art-${a.id}`, target: topicNodeId, type: 'about_topic', weight: 0.4 });
        }
      }
    }

    // Organizations from journalist_orgs, match against organizations table
    const orgs = await query(
      'SELECT org_name, org_type, relationship, strength FROM journalist_orgs WHERE journalist_id = $1',
      [id],
    );

    for (const org of orgs.rows) {
      const orgName = org.org_name as string;
      const strength = Number(org.strength);

      const orgMatch = await query(
        `SELECT id, name, type FROM organizations
         WHERE LOWER(name) = LOWER($1)
            OR LOWER(name) LIKE '%' || LOWER($1) || '%'
            OR LOWER($1) LIKE '%' || LOWER(name) || '%'
         ORDER BY LENGTH(name) ASC LIMIT 1`,
        [orgName],
      );

      let orgNodeId: string;
      if (orgMatch.rowCount && orgMatch.rowCount > 0) {
        orgNodeId = `org-${orgMatch.rows[0].id}`;
        if (!nodeIdSet.has(orgNodeId)) {
          nodes.push({
            id: orgNodeId,
            type: 'organization',
            label: orgMatch.rows[0].name as string,
            data: { type: orgMatch.rows[0].type, strength, relationship: org.relationship },
          });
          nodeIdSet.add(orgNodeId);
        }
      } else {
        orgNodeId = `vorg-${orgName.toLowerCase().replace(/\s+/g, '-')}`;
        if (!nodeIdSet.has(orgNodeId)) {
          nodes.push({
            id: orgNodeId,
            type: 'organization',
            label: orgName,
            data: { strength, relationship: org.relationship, virtual: true },
          });
          nodeIdSet.add(orgNodeId);
        }
      }

      const weight = 0.3 + (strength - 1) * 0.175;
      edges.push({ source: jNodeId, target: orgNodeId, type: 'covers_org', weight });
    }

    // Mentioned people: match against contacts
    const peopleAgg = await query(
      `SELECT unnest(mentioned_people) as person, COUNT(*) as cnt
       FROM journalist_articles WHERE journalist_id = $1
       GROUP BY person ORDER BY cnt DESC LIMIT 20`,
      [id],
    );

    for (const p of peopleAgg.rows) {
      const personName = (p.person as string).trim();
      if (personName.length < 3) continue;
      const parts = personName.split(/\s+/);

      const contactMatch = parts.length >= 2
        ? await query(
          `SELECT id, first_name, last_name, company, headline FROM contacts
           WHERE LOWER(first_name || ' ' || last_name) = LOWER($1)
              OR (LOWER(first_name) = LOWER($2) AND LOWER(last_name) = LOWER($3))
           LIMIT 1`,
          [personName, parts[0], parts[parts.length - 1]],
        )
        : { rowCount: 0, rows: [] };

      let personNodeId: string;
      if (contactMatch.rowCount && contactMatch.rowCount > 0) {
        const ct = contactMatch.rows[0];
        personNodeId = `con-${ct.id}`;
        if (!nodeIdSet.has(personNodeId)) {
          nodes.push({
            id: personNodeId,
            type: 'contact',
            label: `${ct.first_name} ${ct.last_name}`,
            data: { company: ct.company, headline: ct.headline },
          });
          nodeIdSet.add(personNodeId);
        }
      } else {
        personNodeId = `person-${personName.toLowerCase().replace(/\s+/g, '-')}`;
        if (!nodeIdSet.has(personNodeId)) {
          nodes.push({
            id: personNodeId,
            type: 'contact',
            label: personName,
            data: { virtual: true, mention_count: p.cnt },
          });
          nodeIdSet.add(personNodeId);
        }
      }

      edges.push({ source: jNodeId, target: personNodeId, type: 'mentions_person', weight: 0.5 });

      // Connect specific articles to the people they mention
      for (const a of articles.rows) {
        const mentioned = (a.mentioned_people as string[]) || [];
        if (mentioned.some(m => m.toLowerCase().includes(personName.toLowerCase()))) {
          edges.push({ source: `art-${a.id}`, target: personNodeId, type: 'mentions', weight: 0.3 });
        }
      }
    }

    // Existing mind_map_links for this journalist
    const existingLinks = await query(
      `SELECT source_type, source_id, target_type, target_id, link_type, COALESCE(weight, 0.5) AS weight
       FROM mind_map_links
       WHERE (source_type = 'journalist' AND source_id = $1::uuid)
          OR (target_type = 'journalist' AND target_id = $1::uuid)`,
      [id],
    );

    for (const link of existingLinks.rows) {
      const srcId = link.source_type === 'journalist'
        ? jNodeId
        : `${link.source_type}-${link.source_id}`;
      const tgtId = link.target_type === 'journalist'
        ? jNodeId
        : `${link.target_type}-${link.target_id}`;
      if (nodeIdSet.has(srcId) && nodeIdSet.has(tgtId)) {
        edges.push({
          source: srcId,
          target: tgtId,
          type: link.link_type as string,
          weight: Number(link.weight) || 0.5,
        });
      }
    }

    // Deduplicate edges
    const edgeMap = new Map<string, JournalistGraphEdge>();
    for (const e of edges) {
      const key = `${e.source}|${e.target}|${e.type}`;
      const existing = edgeMap.get(key);
      if (!existing || e.weight > existing.weight) {
        edgeMap.set(key, e);
      }
    }

    return NextResponse.json({
      journalist,
      nodes,
      edges: Array.from(edgeMap.values()),
      stats: {
        articles: articles.rowCount,
        topics: topicAgg.rowCount,
        orgs: orgs.rowCount,
        people: peopleAgg.rowCount,
      },
    });
  } catch (err) {
    console.error('[api/graph/journalist] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}