import 'server-only';
import { run, sql, safe } from '@/lib/db';
import type { SQL } from 'drizzle-orm';
import { getDescendantIds } from '@/lib/queries/cancers';
import { ACTIVE_STATUSES } from '@/lib/queries/trials';
import { type CancerContext, type EdgeGroup, type FocusRef, type GraphEdge, type GraphNode, type Neighborhood, type NodeType, type PathChain, compareChains, nodeKey, sortGroups } from '@/lib/graph-model';

/**
 * Knowledge-graph neighbourhood queries. Two families of links:
 *  - source-native `knowledge_edges` rows (CIViC, ChEMBL, openFDA…) — never inferred by CancerIndex,
 *    aggregated for display per (neighbour, relationship, direction, evidence level, source);
 *  - derived relational links computed at query time from registry tables (trial_conditions,
 *    trial_interventions, cancer_gene_frequencies, drug_approvals, civic_evidence_items) — always
 *    flagged `derived: true` with the count / measurement that backs them.
 * Schema is frozen: everything is derived at query time with per-group LIMITs.
 *
 * The public API (`apps/api/src/routes/graph.ts`) duplicates this SQL: this module is `server-only`
 * and depends on the web `@/lib/db` helpers, so it cannot be imported from the Fastify app.
 */

export const DEFAULT_GROUP_LIMIT = 15;
export const EXPANDED_GROUP_LIMIT = 200;
export const TRIAL_GROUP_LIMIT = 10;
/** Cohort thresholds for the derived gene ↔ cancer link (frequency and cases affected). */
export const FREQ_MIN = 0.05;
export const CASES_MIN = 20;
export const PATHS_LIMIT = 8;
/** Cap on descendant ids rolled into a cancer focus (very broad families are truncated to their first N ids). */
export const MAX_DESCENDANTS = 600;

const inList = (ids: string[]): SQL => sql`(${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`;
const activeList = (): SQL => sql`(${sql.join(ACTIVE_STATUSES.map((s) => sql`${s}`), sql`, `)})`;
const n = (v: unknown): number => (v === null || v === undefined ? 0 : Number(v));
const groupLimit = (rel: string, more: string | null | undefined, base = DEFAULT_GROUP_LIMIT): number => (more && more.toUpperCase() === rel ? EXPANDED_GROUP_LIMIT : base);

/** Native-scale rank used only for ORDER BY (never shown; the native level is what the UI displays). */
const LEVEL_RANK = sql`CASE upper(coalesce(ke.evidence_level, '')) WHEN 'A' THEN 0 WHEN 'FDA ORIG' THEN 0 WHEN 'B' THEN 1 WHEN '4' THEN 1 WHEN 'C' THEN 2 WHEN '3' THEN 2 WHEN 'D' THEN 3 WHEN '2' THEN 3 WHEN 'E' THEN 4 WHEN '1' THEN 4 WHEN '' THEN 99 ELSE 50 END`;
const CIVIC_LEVEL_RANK = sql`CASE e.evidence_level WHEN 'A' THEN 0 WHEN 'B' THEN 1 WHEN 'C' THEN 2 WHEN 'D' THEN 3 WHEN 'E' THEN 4 ELSE 99 END`;

const hrefFor = (type: NodeType, ref: string): string => {
  switch (type) {
    case 'cancer':
      return `/cancer/${ref}`;
    case 'gene':
      return `/gene/${ref}`;
    case 'variant':
      return `/variant/${ref}`;
    case 'drug':
      return `/drug/${ref}`;
    case 'trial':
      return `/trial/${ref}`;
    default:
      return ref;
  }
};

// ---------------------------------------------------------------------------------------------
// Focus resolution
// ---------------------------------------------------------------------------------------------

export interface FocusNode extends GraphNode {
  type: Exclude<NodeType, 'approval'>;
  ref: string;
}

export async function resolveFocus(f: FocusRef): Promise<FocusNode | null> {
  const ref = f.ref.trim();
  const isCi = /^CI-[A-Z]+-\d+$/i.test(ref);
  type Row = { id: string; ref: string; label: string; sublabel: string | null };
  let rows: Row[] = [];
  switch (f.type) {
    case 'cancer':
      rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, canonical_name AS label, entity_type AS sublabel FROM cancers WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);
      break;
    case 'gene':
      rows = await safe(
        () =>
          run<Row>(sql`SELECT g.id, g.symbol AS ref, g.symbol AS label, g.name AS sublabel FROM genes g WHERE ${isCi ? sql`g.id = ${ref.toUpperCase()}` : sql`upper(g.symbol) = upper(${ref}) OR g.hgnc_id = ${ref}`}
            UNION ALL SELECT g.id, g.symbol, g.symbol, g.name FROM genes g JOIN gene_aliases a ON a.gene_id = g.id WHERE upper(a.alias) = upper(${ref}) LIMIT 1`),
        [],
      );
      break;
    case 'variant':
      rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, coalesce(gene_symbol || ' ', '') || name AS label, variant_type AS sublabel FROM variants WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);
      break;
    case 'drug':
      rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, name AS label, kind AS sublabel FROM drugs WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);
      break;
    case 'trial':
      rows = await safe(() => run<Row>(sql`SELECT id, nct_id AS ref, brief_title AS label, overall_status AS sublabel FROM clinical_trials WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`upper(nct_id) = upper(${ref})`} LIMIT 1`), []);
      break;
  }
  const r = rows[0];
  if (!r) return null;
  return { type: f.type, id: r.id, ref: r.ref, label: r.label, sublabel: r.sublabel, href: hrefFor(f.type, r.ref), degree: 0 };
}

// ---------------------------------------------------------------------------------------------
// Source-native knowledge edges
// ---------------------------------------------------------------------------------------------

interface KeRow {
  relationship_type: string;
  outgoing: boolean;
  ctx_only: boolean;
  n_type: NodeType;
  n_id: string;
  n_ref: string | null;
  n_label: string | null;
  n_sublabel: string | null;
  via_type: NodeType | null;
  via_id: string | null;
  via_ref: string | null;
  via_label: string | null;
  direction: string | null;
  evidence_level: string | null;
  evidence_category: string;
  source_id: string;
  source_slug: string;
  support: string;
  edge_ids: number[];
  provenance_ids: number[];
  context_ids: string[];
  last_seen: Date | null;
  total: string;
  rn: string;
}

/**
 * Edges where the focus is the source or the target (both directions), plus — for a cancer focus —
 * edges where the cancer is only the *context* (variant → drug in this cancer), aggregated per
 * (neighbour, relationship, direction, level, source). Per-relationship LIMIT via row_number().
 */
async function knowledgeEdges(focus: FocusNode, more: string | null): Promise<KeRow[]> {
  const t = focus.type;
  const id = focus.id;
  const ctx = t === 'cancer' ? sql`OR (${id} = ANY(ke.cancer_context_ids) AND ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id})` : sql``;
  return safe(
    () =>
      run<KeRow>(sql`
        WITH e AS (
          SELECT ke.relationship_type,
                 (ke.source_entity_type = ${t} AND ke.source_entity_id = ${id}) AS outgoing,
                 (ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id}) AS ctx_only,
                 CASE WHEN ke.source_entity_id = ${id} THEN ke.target_entity_type ELSE ke.source_entity_type END AS n_type,
                 CASE WHEN ke.source_entity_id = ${id} THEN ke.target_entity_id ELSE ke.source_entity_id END AS n_id,
                 CASE WHEN ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id} THEN ke.target_entity_type END AS via_type,
                 CASE WHEN ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id} THEN ke.target_entity_id END AS via_id,
                 ke.direction, ke.evidence_level, ke.evidence_category, ke.source_id, ke.id, ke.provenance_ids, ke.cancer_context_ids, ke.support_count, ke.last_seen_at,
                 ${LEVEL_RANK} AS lvl
          FROM knowledge_edges ke
          WHERE ke.status = 'active' AND ((ke.source_entity_type = ${t} AND ke.source_entity_id = ${id}) OR (ke.target_entity_type = ${t} AND ke.target_entity_id = ${id}) ${ctx})
        ), a AS (
          SELECT relationship_type, outgoing, ctx_only, n_type, n_id, via_type, via_id, direction, evidence_level, evidence_category, source_id, min(lvl) AS lvl,
                 sum(support_count) AS support, array_agg(id ORDER BY id) AS edge_ids,
                 (SELECT array_agg(DISTINCT x::int ORDER BY x::int) FROM unnest(string_to_array(string_agg(array_to_string(provenance_ids, ','), ','), ',')) x WHERE x <> '') AS provenance_ids,
                 (SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(string_to_array(string_agg(array_to_string(cancer_context_ids, ','), ','), ',')) x WHERE x <> '') AS context_ids,
                 max(last_seen_at) AS last_seen
          FROM e GROUP BY 1,2,3,4,5,6,7,8,9,10,11
        ), r AS (
          SELECT a.*, row_number() OVER (PARTITION BY a.relationship_type ORDER BY a.lvl, a.support DESC, a.last_seen DESC NULLS LAST, a.n_id, a.via_id) AS rn,
                 count(*) OVER (PARTITION BY a.relationship_type) AS total
          FROM a
        )
        SELECT r.relationship_type, r.outgoing, r.ctx_only, r.n_type, r.n_id, r.via_type, r.via_id, r.direction, r.evidence_level, r.evidence_category, r.source_id, s.slug AS source_slug,
               r.support, r.edge_ids, r.provenance_ids, r.context_ids, r.last_seen, r.total, r.rn,
               coalesce(c.slug, g.symbol, v.slug, d.slug) AS n_ref,
               coalesce(c.canonical_name, g.symbol, coalesce(v.gene_symbol || ' ', '') || v.name, d.name) AS n_label,
               coalesce(c.entity_type, g.name, v.variant_type, d.kind) AS n_sublabel,
               coalesce(vc.slug, vg.symbol, vv.slug, vd.slug) AS via_ref,
               coalesce(vc.canonical_name, vg.symbol, coalesce(vv.gene_symbol || ' ', '') || vv.name, vd.name) AS via_label
        FROM r
        JOIN sources s ON s.id = r.source_id
        LEFT JOIN cancers c ON r.n_type = 'cancer' AND c.id = r.n_id
        LEFT JOIN genes g ON r.n_type = 'gene' AND g.id = r.n_id
        LEFT JOIN variants v ON r.n_type = 'variant' AND v.id = r.n_id
        LEFT JOIN drugs d ON r.n_type = 'drug' AND d.id = r.n_id
        LEFT JOIN cancers vc ON r.via_type = 'cancer' AND vc.id = r.via_id
        LEFT JOIN genes vg ON r.via_type = 'gene' AND vg.id = r.via_id
        LEFT JOIN variants vv ON r.via_type = 'variant' AND vv.id = r.via_id
        LEFT JOIN drugs vd ON r.via_type = 'drug' AND vd.id = r.via_id
        WHERE r.rn <= CASE WHEN r.relationship_type = ${(more ?? '').toUpperCase()}::text THEN ${EXPANDED_GROUP_LIMIT}::int ELSE ${DEFAULT_GROUP_LIMIT}::int END
        ORDER BY r.relationship_type, r.rn`),
    [] as KeRow[],
  );
}

// ---------------------------------------------------------------------------------------------
// Derived links (registry counts) — each returns ready-made edges + nodes
// ---------------------------------------------------------------------------------------------

interface Derived {
  relationshipType: string;
  total: number;
  edges: GraphEdge[];
  nodes: GraphNode[];
}

const SRC = { clinicaltrials: 'CI-SOURCE-00000004', civic: 'CI-SOURCE-00000006', gdc: 'CI-SOURCE-00000008', openfda: 'CI-SOURCE-00000016', cbioportal: 'CI-SOURCE-00000017' } as const;

function pct(v: number): string {
  return `${(v * 100).toFixed(v >= 0.1 ? 0 : 1)} %`;
}

/** cancer ⇄ gene through cohort alteration frequencies (largest cohort per pair — biggest denominator, not highest frequency; thresholds applied to that cohort row). */
async function frequencyLinks(side: 'cancer' | 'gene', focus: FocusNode, ids: string[], limit: number): Promise<Derived> {
  type Row = { gene_id: string; symbol: string; is_cancer_gene: boolean; cancer_id: string; cancer_slug: string; cancer_name: string; alteration_type: string; cases_affected: number; cases_profiled: number; frequency: number; study_id: string; source_id: string; source_slug: string; provenance_id: number; cohorts: string; total: string };
  const where = side === 'cancer' ? sql`f.cancer_id IN ${inList(ids)} AND f.gene_id IS NOT NULL` : sql`f.gene_id = ${focus.id} AND f.cancer_id IS NOT NULL`;
  const part = side === 'cancer' ? sql`f.gene_id` : sql`f.cancer_id`;
  const order = side === 'cancer' ? sql`g.is_cancer_gene DESC, f.frequency DESC, g.symbol` : sql`f.frequency DESC, c.canonical_name`;
  const rows = await safe(
    () =>
      run<Row>(sql`
        WITH f AS (
          SELECT f.gene_id, f.cancer_id, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.provenance_id, co.study_id, co.source_id,
                 count(*) OVER (PARTITION BY ${part}) AS cohorts,
                 row_number() OVER (PARTITION BY ${part} ORDER BY f.cases_profiled DESC, f.frequency DESC, f.id) AS rn
          FROM cancer_gene_frequencies f JOIN genomic_cohorts co ON co.id = f.cohort_id
          WHERE ${where} AND f.frequency >= ${FREQ_MIN} AND f.cases_affected >= ${CASES_MIN}
        )
        SELECT f.*, g.symbol, g.is_cancer_gene, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, count(*) OVER() AS total
        FROM f JOIN genes g ON g.id = f.gene_id JOIN cancers c ON c.id = f.cancer_id JOIN sources s ON s.id = f.source_id
        WHERE f.rn = 1 ORDER BY ${order} LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const neighbor: GraphNode =
      side === 'cancer'
        ? { type: 'gene', id: r.gene_id, ref: r.symbol, label: r.symbol, sublabel: r.is_cancer_gene ? 'cancer gene' : null, href: hrefFor('gene', r.symbol), degree: 0 }
        : { type: 'cancer', id: r.cancer_id, ref: r.cancer_slug, label: r.cancer_name, sublabel: null, href: hrefFor('cancer', r.cancer_slug), degree: 0 };
    nodes.push(neighbor);
    edges.push({
      key: `dv:freq:${r.gene_id}:${r.cancer_id}`,
      relationshipType: 'ALTERED_IN',
      neighborKey: nodeKey(neighbor),
      outgoing: side === 'gene',
      direction: null,
      evidenceLevel: null,
      evidenceCategory: 'observed_data',
      cancerContext: [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }],
      supportCount: n(r.cohorts),
      sourceIds: [r.source_id],
      sourceSlugs: [r.source_slug],
      provenanceIds: [r.provenance_id],
      derived: true,
      detail: `${r.cases_affected.toLocaleString('en-US')} / ${r.cases_profiled.toLocaleString('en-US')} cases (${pct(r.frequency)}) · ${r.alteration_type} · ${r.study_id}${n(r.cohorts) > 1 ? ` · largest of ${n(r.cohorts)} cohorts` : ''}`,
    });
  }
  return { relationshipType: 'ALTERED_IN', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** cancer → trials (registry, deduplicated per trial; most recently updated first). */
async function cancerTrialLinks(focus: FocusNode, ids: string[], limit: number): Promise<Derived> {
  type Row = { id: string; nct_id: string; brief_title: string; overall_status: string | null; phases: string[]; last_update_posted_date: string | null; cancer_id: string; cancer_slug: string; cancer_name: string; match_type: string; total: string; active: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        WITH m AS (
          SELECT DISTINCT ON (tc.trial_id) tc.trial_id, tc.cancer_id, tc.match_type FROM trial_conditions tc WHERE tc.cancer_id IN ${inList(ids)} ORDER BY tc.trial_id, (tc.cancer_id = ${focus.id}) DESC, tc.id
        )
        SELECT t.id, t.nct_id, t.brief_title, t.overall_status, t.phases, t.last_update_posted_date, m.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, m.match_type,
               count(*) OVER() AS total, count(*) FILTER (WHERE t.overall_status IN ${activeList()}) OVER() AS active
        FROM m JOIN clinical_trials t ON t.id = m.trial_id JOIN cancers c ON c.id = m.cancer_id
        ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'trial', id: r.id, ref: r.nct_id, label: r.nct_id, sublabel: [r.phases.map((p) => p.replace('PHASE', 'Phase ').replace('EARLY_', 'early ')).join('/'), r.overall_status?.toLowerCase().replace(/_/g, ' ')].filter(Boolean).join(' · ') || null, href: hrefFor('trial', r.nct_id), degree: 0 };
    nodes.push(node);
    edges.push({
      key: `dv:trial:${r.id}`,
      relationshipType: 'STUDIED_IN',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: null,
      evidenceLevel: null,
      evidenceCategory: 'observed_data',
      cancerContext: [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }],
      supportCount: 1,
      sourceIds: [SRC.clinicaltrials],
      sourceSlugs: ['clinicaltrials'],
      provenanceIds: [],
      derived: true,
      detail: `${r.brief_title} · condition mapped ${r.match_type}`,
      date: r.last_update_posted_date,
    });
  }
  const total = rows.length ? n(rows[0]!.total) : 0;
  const active = rows.length ? n(rows[0]!.active) : 0;
  for (const e of edges) e.detail = `${e.detail} · ${total.toLocaleString('en-US')} trials mapped (${active.toLocaleString('en-US')} active)`;
  return { relationshipType: 'STUDIED_IN', total, edges, nodes };
}

/** cancer → drugs through registered trials (trial_interventions.drug_id × trial_conditions.cancer_id). */
async function cancerDrugTrialLinks(focus: FocusNode, ids: string[], limit: number): Promise<Derived> {
  type Row = { drug_id: string; slug: string; name: string; kind: string | null; trials: string; active: string; last: string | null; total: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT ti.drug_id, d.slug, d.name, d.kind, count(DISTINCT ti.trial_id) AS trials,
               count(DISTINCT ti.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active, max(t.last_update_posted_date) AS last, count(*) OVER() AS total
        FROM trial_conditions tc JOIN trial_interventions ti ON ti.trial_id = tc.trial_id AND ti.drug_id IS NOT NULL
        JOIN clinical_trials t ON t.id = tc.trial_id JOIN drugs d ON d.id = ti.drug_id
        WHERE tc.cancer_id IN ${inList(ids)}
        GROUP BY ti.drug_id, d.slug, d.name, d.kind ORDER BY trials DESC, d.name LIMIT ${limit}`),
    [] as Row[],
  );
  return drugTrialRows(focus, rows, 'INVESTIGATED_IN_TRIALS', true);
}

function drugTrialRows(focus: FocusNode, rows: Array<{ drug_id: string; slug: string; name: string; kind: string | null; trials: string; active: string; last: string | null; total: string }>, rel: string, outgoing: boolean): Derived {
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };
    nodes.push(node);
    edges.push({
      key: `dv:drugtrials:${r.drug_id}:${focus.id}`,
      relationshipType: rel,
      neighborKey: nodeKey(node),
      outgoing,
      direction: null,
      evidenceLevel: null,
      evidenceCategory: 'observed_data',
      cancerContext: focus.type === 'cancer' ? [{ id: focus.id, name: focus.label, slug: focus.ref }] : [],
      supportCount: n(r.trials),
      sourceIds: [SRC.clinicaltrials],
      sourceSlugs: ['clinicaltrials'],
      provenanceIds: [],
      derived: true,
      detail: `${n(r.trials).toLocaleString('en-US')} trials (${n(r.active).toLocaleString('en-US')} active)${focus.type === 'cancer' ? ' · roll-up of the cancer and its descendants' : ''}`,
      date: r.last,
    });
  }
  return { relationshipType: rel, total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** cancer ← drug through regulatory approvals (jurisdiction-aware, dated); rows already present as APPROVED_FOR knowledge edges are skipped. */
async function approvalLinks(side: 'cancer' | 'drug', focus: FocusNode, ids: string[], limit: number): Promise<Derived> {
  type Row = { id: number; drug_id: string; drug_slug: string; drug_name: string; kind: string | null; cancer_id: string | null; cancer_slug: string | null; cancer_name: string | null; tumor_agnostic: boolean; jurisdiction: string; authority: string; indication: string; approval_date: string | null; status: string; source_id: string; source_slug: string; provenance_id: number; total: string };
  const where = side === 'cancer' ? sql`a.cancer_id IN ${inList(ids)} AND NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'APPROVED_FOR' AND ke.source_entity_id = a.drug_id AND ke.target_entity_id = ${focus.id})` : sql`a.drug_id = ${focus.id} AND (a.cancer_id IS NULL OR NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'APPROVED_FOR' AND ke.source_entity_id = a.drug_id AND ke.target_entity_id = a.cancer_id))`;
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT a.id, a.drug_id, d.slug AS drug_slug, d.name AS drug_name, d.kind, a.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication, a.approval_date, a.status,
               a.source_id, s.slug AS source_slug, a.provenance_id, count(*) OVER() AS total
        FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id JOIN sources s ON s.id = a.source_id
        WHERE ${where} ORDER BY a.approval_date DESC NULLS LAST, a.id LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    let node: GraphNode;
    if (side === 'cancer') node = { type: 'drug', id: r.drug_id, ref: r.drug_slug, label: r.drug_name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.drug_slug), degree: 0 };
    else if (r.cancer_id && r.cancer_slug && r.cancer_name) node = { type: 'cancer', id: r.cancer_id, ref: r.cancer_slug, label: r.cancer_name, sublabel: null, href: hrefFor('cancer', r.cancer_slug), degree: 0 };
    else node = { type: 'approval', id: `approval:${r.id}`, ref: null, label: `${r.authority} · ${r.jurisdiction}${r.approval_date ? ` · ${r.approval_date.slice(0, 4)}` : ''}`, sublabel: r.indication, href: `/drug/${r.drug_slug}#approvals`, degree: 0 };
    nodes.push(node);
    edges.push({
      key: `dv:approval:${r.id}`,
      relationshipType: 'APPROVED_FOR',
      neighborKey: nodeKey(node),
      outgoing: side === 'drug',
      direction: null,
      evidenceLevel: r.status,
      evidenceCategory: 'regulatory_status',
      cancerContext: r.cancer_id && r.cancer_slug && r.cancer_name ? [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }] : [],
      supportCount: 1,
      sourceIds: [r.source_id],
      sourceSlugs: [r.source_slug],
      provenanceIds: [r.provenance_id],
      derived: true,
      detail: `${r.authority} (${r.jurisdiction}) · ${r.status}${r.tumor_agnostic ? ' · tumour-agnostic' : ''}${r.approval_date ? ` · ${r.approval_date}` : ' · date not published'} · ${r.indication.length > 140 ? `${r.indication.slice(0, 139)}…` : r.indication}`,
      date: r.approval_date,
    });
  }
  return { relationshipType: 'APPROVED_FOR', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** gene → variants (top by CIViC evidence count; the variant list itself is structural HGNC/ClinVar/CIViC data). */
async function geneVariantLinks(focus: FocusNode, limit: number): Promise<Derived> {
  type Row = { id: string; slug: string; name: string; variant_type: string | null; ev: string; context_ids: string[] | null; provenance_ids: number[] | null; total: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        WITH ev AS (
          SELECT vid, count(*) AS ev, (array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL))[1:5] AS context_ids, (array_agg(DISTINCT e.provenance_id))[1:20] AS provenance_ids
          FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.variant_ids) vid
          WHERE e.status = 'ACCEPTED' AND (${focus.id} = ANY(e.gene_ids) OR ${focus.label} = ANY(e.gene_symbols)) GROUP BY vid
        )
        SELECT v.id, v.slug, v.name, v.variant_type, coalesce(ev.ev, 0) AS ev, ev.context_ids, ev.provenance_ids, count(*) OVER() AS total
        FROM variants v LEFT JOIN ev ON ev.vid = v.id WHERE v.gene_id = ${focus.id}
        ORDER BY coalesce(ev.ev, 0) DESC, v.name LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'variant', id: r.id, ref: r.slug, label: r.name, sublabel: r.variant_type, href: hrefFor('variant', r.slug), degree: 0 };
    nodes.push(node);
    const ev = n(r.ev);
    edges.push({
      key: `dv:variant:${r.id}`,
      relationshipType: 'HAS_VARIANT',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: null,
      evidenceLevel: null,
      evidenceCategory: ev > 0 ? 'curated_evidence' : 'observed_data',
      cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),
      supportCount: ev,
      sourceIds: [SRC.civic],
      sourceSlugs: ['civic'],
      provenanceIds: r.provenance_ids ?? [],
      derived: true,
      detail: ev > 0 ? `${ev.toLocaleString('en-US')} accepted CIViC evidence items` : 'no accepted CIViC evidence item (variant record only)',
    });
  }
  return { relationshipType: 'HAS_VARIANT', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** variant → cancers with accepted CIViC evidence, aggregated by level (A–E) and direction. */
async function variantEvidenceLinks(focus: FocusNode, limit: number): Promise<Derived> {
  type Row = { cancer_id: string; slug: string; name: string; items: string; levels: string[]; best: string | null; sens: string; res: string; supports: string; does_not: string; provenance_ids: number[]; total: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT e.cancer_id, c.slug, c.canonical_name AS name, count(*) AS items,
               array_agg(DISTINCT e.evidence_level ORDER BY e.evidence_level) FILTER (WHERE e.evidence_level IS NOT NULL) AS levels,
               min(e.evidence_level) AS best,
               count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sens, count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS res,
               count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not,
               (array_agg(DISTINCT e.provenance_id))[1:50] AS provenance_ids, count(*) OVER() AS total
        FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id
        WHERE e.status = 'ACCEPTED' AND ${focus.id} = ANY(e.variant_ids)
        GROUP BY e.cancer_id, c.slug, c.canonical_name ORDER BY min(${CIVIC_LEVEL_RANK}), items DESC, c.canonical_name LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };
    nodes.push(node);
    const sens = n(r.sens);
    const res = n(r.res);
    edges.push({
      key: `dv:civic:${focus.id}:${r.cancer_id}`,
      relationshipType: 'HAS_EVIDENCE_IN',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: sens && res ? 'mixed' : sens ? 'sensitivity' : res ? 'resistance' : n(r.supports) && !n(r.does_not) ? 'supports' : n(r.does_not) ? 'does not support' : null,
      evidenceLevel: r.best,
      evidenceCategory: 'curated_evidence',
      cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }],
      supportCount: n(r.items),
      sourceIds: [SRC.civic],
      sourceSlugs: ['civic'],
      provenanceIds: r.provenance_ids ?? [],
      derived: true,
      detail: `${n(r.items).toLocaleString('en-US')} accepted items · levels ${(r.levels ?? []).join(', ') || '—'} · ${sens} sensitivity / ${res} resistance · ${n(r.supports)} supports / ${n(r.does_not)} does not support`,
    });
  }
  return { relationshipType: 'HAS_EVIDENCE_IN', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** variant → drugs from CIViC predictive items that have no PREDICTS_RESPONSE_TO knowledge edge yet (gap filler, flagged derived). */
async function variantDrugCivicLinks(focus: FocusNode, limit: number): Promise<Derived> {
  type Row = { drug_id: string; slug: string; name: string; kind: string | null; items: string; best: string | null; sens: string; res: string; context_ids: string[]; provenance_ids: number[]; total: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT tid AS drug_id, d.slug, d.name, d.kind, count(*) AS items, min(e.evidence_level) AS best,
               count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sens, count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS res,
               (array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL))[1:5] AS context_ids, (array_agg(DISTINCT e.provenance_id))[1:50] AS provenance_ids, count(*) OVER() AS total
        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid
        WHERE e.status = 'ACCEPTED' AND e.evidence_type = 'PREDICTIVE' AND ${focus.id} = ANY(e.variant_ids)
          AND NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'PREDICTS_RESPONSE_TO' AND ke.source_entity_id = ${focus.id} AND ke.target_entity_id = tid)
        GROUP BY tid, d.slug, d.name, d.kind ORDER BY min(${CIVIC_LEVEL_RANK}), items DESC, d.name LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };
    nodes.push(node);
    const sens = n(r.sens);
    const res = n(r.res);
    edges.push({
      key: `dv:civicdrug:${focus.id}:${r.drug_id}`,
      relationshipType: 'PREDICTS_RESPONSE_TO',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: sens && res ? 'mixed' : sens ? 'sensitivity' : res ? 'resistance' : null,
      evidenceLevel: r.best,
      evidenceCategory: 'curated_evidence',
      cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),
      supportCount: n(r.items),
      sourceIds: [SRC.civic],
      sourceSlugs: ['civic'],
      provenanceIds: r.provenance_ids ?? [],
      derived: true,
      detail: `${n(r.items)} accepted predictive items (aggregated from CIViC, no knowledge edge yet)`,
    });
  }
  return { relationshipType: 'PREDICTS_RESPONSE_TO', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** drug → trials (registry, most recently updated first) with the trial's mapped cancers as context. */
async function drugTrialLinks(focus: FocusNode, limit: number): Promise<Derived> {
  type Row = { id: string; nct_id: string; brief_title: string; overall_status: string | null; phases: string[]; last_update_posted_date: string | null; context_ids: string[] | null; total: string; active: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT t.id, t.nct_id, t.brief_title, t.overall_status, t.phases, t.last_update_posted_date,
               (SELECT (array_agg(DISTINCT tc.cancer_id))[1:5] FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IS NOT NULL) AS context_ids,
               count(*) OVER() AS total, count(*) FILTER (WHERE t.overall_status IN ${activeList()}) OVER() AS active
        FROM (SELECT DISTINCT trial_id FROM trial_interventions WHERE drug_id = ${focus.id}) ti JOIN clinical_trials t ON t.id = ti.trial_id
        ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  const total = rows.length ? n(rows[0]!.total) : 0;
  const active = rows.length ? n(rows[0]!.active) : 0;
  for (const r of rows) {
    const node: GraphNode = { type: 'trial', id: r.id, ref: r.nct_id, label: r.nct_id, sublabel: [r.phases.map((p) => p.replace('PHASE', 'Phase ').replace('EARLY_', 'early ')).join('/'), r.overall_status?.toLowerCase().replace(/_/g, ' ')].filter(Boolean).join(' · ') || null, href: hrefFor('trial', r.nct_id), degree: 0 };
    nodes.push(node);
    edges.push({
      key: `dv:drugtrial:${r.id}`,
      relationshipType: 'STUDIED_IN',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: null,
      evidenceLevel: null,
      evidenceCategory: 'observed_data',
      cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),
      supportCount: 1,
      sourceIds: [SRC.clinicaltrials],
      sourceSlugs: ['clinicaltrials'],
      provenanceIds: [],
      derived: true,
      detail: `${r.brief_title} · ${total.toLocaleString('en-US')} trials list this drug (${active.toLocaleString('en-US')} active)`,
      date: r.last_update_posted_date,
    });
  }
  return { relationshipType: 'STUDIED_IN', total, edges, nodes };
}

/** drug → cancers through registered trials. */
async function drugCancerTrialLinks(focus: FocusNode, limit: number): Promise<Derived> {
  type Row = { cancer_id: string; slug: string; name: string; trials: string; active: string; last: string | null; total: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        SELECT tc.cancer_id, c.slug, c.canonical_name AS name, count(DISTINCT tc.trial_id) AS trials,
               count(DISTINCT tc.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active, max(t.last_update_posted_date) AS last, count(*) OVER() AS total
        FROM trial_interventions ti JOIN trial_conditions tc ON tc.trial_id = ti.trial_id AND tc.cancer_id IS NOT NULL
        JOIN clinical_trials t ON t.id = ti.trial_id JOIN cancers c ON c.id = tc.cancer_id
        WHERE ti.drug_id = ${focus.id}
        GROUP BY tc.cancer_id, c.slug, c.canonical_name ORDER BY trials DESC, c.canonical_name LIMIT ${limit}`),
    [] as Row[],
  );
  const edges: GraphEdge[] = [];
  const nodes: GraphNode[] = [];
  for (const r of rows) {
    const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };
    nodes.push(node);
    edges.push({
      key: `dv:drugtrials:${focus.id}:${r.cancer_id}`,
      relationshipType: 'INVESTIGATED_IN_TRIALS',
      neighborKey: nodeKey(node),
      outgoing: true,
      direction: null,
      evidenceLevel: null,
      evidenceCategory: 'observed_data',
      cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }],
      supportCount: n(r.trials),
      sourceIds: [SRC.clinicaltrials],
      sourceSlugs: ['clinicaltrials'],
      provenanceIds: [],
      derived: true,
      detail: `${n(r.trials).toLocaleString('en-US')} trials (${n(r.active).toLocaleString('en-US')} active) · conditions mapped to this cancer only (no hierarchy roll-up)`,
      date: r.last,
    });
  }
  return { relationshipType: 'INVESTIGATED_IN_TRIALS', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };
}

/** trial → mapped conditions (cancers) and interventions (drugs). */
async function trialLinks(focus: FocusNode, limit: number): Promise<Derived[]> {
  type CRow = { cancer_id: string; slug: string; name: string; match_type: string; condition_text: string; total: string };
  type DRow = { drug_id: string; slug: string; name: string; kind: string | null; match_type: string; intervention_type: string | null; iname: string; total: string };
  const [conds, ints] = await Promise.all([
    safe(() => run<CRow>(sql`SELECT tc.cancer_id, c.slug, c.canonical_name AS name, tc.match_type, tc.condition_text, count(*) OVER() AS total FROM trial_conditions tc JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${focus.id} ORDER BY c.canonical_name LIMIT ${limit}`), [] as CRow[]),
    safe(() => run<DRow>(sql`SELECT ti.drug_id, d.slug, d.name, d.kind, ti.match_type, ti.intervention_type, ti.name AS iname, count(*) OVER() AS total FROM trial_interventions ti JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${focus.id} ORDER BY d.name LIMIT ${limit}`), [] as DRow[]),
  ]);
  const c: Derived = { relationshipType: 'CONDITION_OF', total: conds.length ? n(conds[0]!.total) : 0, edges: [], nodes: [] };
  for (const r of conds) {
    const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };
    c.nodes.push(node);
    c.edges.push({ key: `dv:cond:${focus.id}:${r.cancer_id}`, relationshipType: 'CONDITION_OF', neighborKey: nodeKey(node), outgoing: false, direction: null, evidenceLevel: null, evidenceCategory: 'observed_data', cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }], supportCount: 1, sourceIds: [SRC.clinicaltrials], sourceSlugs: ['clinicaltrials'], provenanceIds: [], derived: true, detail: `registry condition “${r.condition_text}” mapped ${r.match_type}` });
  }
  const d: Derived = { relationshipType: 'INTERVENTION_OF', total: ints.length ? n(ints[0]!.total) : 0, edges: [], nodes: [] };
  for (const r of ints) {
    const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };
    d.nodes.push(node);
    d.edges.push({ key: `dv:int:${focus.id}:${r.drug_id}`, relationshipType: 'INTERVENTION_OF', neighborKey: nodeKey(node), outgoing: false, direction: null, evidenceLevel: null, evidenceCategory: 'observed_data', cancerContext: [], supportCount: 1, sourceIds: [SRC.clinicaltrials], sourceSlugs: ['clinicaltrials'], provenanceIds: [], derived: true, detail: `registry intervention “${r.iname}” (${r.intervention_type ?? 'type not stated'}) mapped ${r.match_type}` });
  }
  return [c, d];
}

// ---------------------------------------------------------------------------------------------
// Assembly
// ---------------------------------------------------------------------------------------------

async function cancerNames(ids: Iterable<string>): Promise<Map<string, CancerContext>> {
  const uniq = [...new Set(ids)].filter(Boolean);
  if (uniq.length === 0) return new Map();
  const rows = await safe(() => run<{ id: string; slug: string; name: string }>(sql`SELECT id, slug, canonical_name AS name FROM cancers WHERE id IN ${inList(uniq)}`), [] as Array<{ id: string; slug: string; name: string }>);
  return new Map(rows.map((r) => [r.id, { id: r.id, slug: r.slug, name: r.name }]));
}

/** Descendant ids (inclusive) for a cancer focus, capped so very broad families stay bounded. */
export async function focusCancerIds(focus: FocusNode): Promise<string[]> {
  if (focus.type !== 'cancer') return [];
  const ids = await getDescendantIds(focus.id);
  return ids.length > MAX_DESCENDANTS ? [focus.id, ...ids.filter((i) => i !== focus.id).slice(0, MAX_DESCENDANTS - 1)] : ids;
}

export interface NeighborhoodOptions {
  /** Relationship type whose group is expanded to EXPANDED_GROUP_LIMIT. */
  more?: string | null;
  includeDerived?: boolean;
}

export async function loadNeighborhood(focus: FocusNode, opts: NeighborhoodOptions = {}): Promise<Neighborhood & { cancerIds: string[] }> {
  const more = opts.more ?? null;
  const includeDerived = opts.includeDerived ?? true;
  const cancerIds = await focusCancerIds(focus);
  const lim = (rel: string, base = DEFAULT_GROUP_LIMIT) => groupLimit(rel, more, base);

  const derivedTasks: Array<Promise<Derived | Derived[]>> = [];
  if (includeDerived) {
    switch (focus.type) {
      case 'cancer':
        derivedTasks.push(cancerTrialLinks(focus, cancerIds, lim('STUDIED_IN', TRIAL_GROUP_LIMIT)), frequencyLinks('cancer', focus, cancerIds, lim('ALTERED_IN')), approvalLinks('cancer', focus, cancerIds, lim('APPROVED_FOR')), cancerDrugTrialLinks(focus, cancerIds, lim('INVESTIGATED_IN_TRIALS')));
        break;
      case 'gene':
        derivedTasks.push(geneVariantLinks(focus, lim('HAS_VARIANT')), frequencyLinks('gene', focus, [], lim('ALTERED_IN')));
        break;
      case 'variant':
        derivedTasks.push(variantEvidenceLinks(focus, lim('HAS_EVIDENCE_IN')), variantDrugCivicLinks(focus, lim('PREDICTS_RESPONSE_TO')));
        break;
      case 'drug':
        derivedTasks.push(drugTrialLinks(focus, lim('STUDIED_IN', TRIAL_GROUP_LIMIT)), drugCancerTrialLinks(focus, lim('INVESTIGATED_IN_TRIALS')), approvalLinks('drug', focus, [], lim('APPROVED_FOR')));
        break;
      case 'trial':
        derivedTasks.push(trialLinks(focus, lim('CONDITION_OF')));
        break;
    }
  }
  const [ke, ...derivedRaw] = await Promise.all([focus.type === 'trial' ? Promise.resolve([] as KeRow[]) : knowledgeEdges(focus, more), ...derivedTasks]);
  const derived = derivedRaw.flat();

  // Source-native edges → GraphEdge + nodes
  const nodes = new Map<string, GraphNode>();
  const groups = new Map<string, EdgeGroup>();
  const ctxIds = new Set<string>();
  for (const r of ke) for (const c of r.context_ids ?? []) ctxIds.add(c);
  for (const d of derived) for (const e of d.edges) for (const c of e.cancerContext) if (!c.slug) ctxIds.add(c.id);
  const names = await cancerNames(ctxIds);
  const ctx = (ids: string[] | null | undefined): CancerContext[] => (ids ?? []).map((id) => names.get(id) ?? { id, name: id, slug: '' }).sort((a, b) => a.name.localeCompare(b.name));

  const addNode = (node: GraphNode) => {
    const k = nodeKey(node);
    const cur = nodes.get(k);
    if (cur) cur.degree += 1;
    else nodes.set(k, { ...node, degree: 1 });
  };
  const addEdge = (e: GraphEdge, total: number, derivedGroup: boolean) => {
    const g = groups.get(e.relationshipType) ?? { relationshipType: e.relationshipType, total: 0, edges: [], derived: derivedGroup };
    g.edges.push(e);
    g.total = Math.max(g.total, total);
    if (!derivedGroup) g.derived = false;
    groups.set(e.relationshipType, g);
  };

  for (const r of ke) {
    if (!r.n_ref || !r.n_label) continue; // dangling target (entity not loaded on this environment)
    const node: GraphNode = { type: r.n_type, id: r.n_id, ref: r.n_ref, label: r.n_label, sublabel: r.n_sublabel, href: hrefFor(r.n_type, r.n_ref), degree: 0 };
    addNode(node);
    addEdge(
      {
        key: `ke:${(r.edge_ids ?? []).join('.')}`,
        relationshipType: r.relationship_type,
        neighborKey: nodeKey(node),
        outgoing: r.outgoing,
        direction: r.direction,
        evidenceLevel: r.evidence_level,
        evidenceCategory: r.evidence_category,
        cancerContext: ctx(r.context_ids),
        supportCount: n(r.support),
        sourceIds: [r.source_id],
        sourceSlugs: [r.source_slug],
        provenanceIds: (r.provenance_ids ?? []).map(Number),
        derived: false,
        detail: (r.edge_ids?.length ?? 1) > 1 ? `${r.edge_ids.length} source records aggregated` : null,
        date: r.last_seen ? new Date(r.last_seen).toISOString().slice(0, 10) : null,
        via: r.ctx_only && r.via_type && r.via_id && r.via_ref && r.via_label ? { type: r.via_type, id: r.via_id, label: r.via_label, href: hrefFor(r.via_type, r.via_ref) } : null,
      },
      n(r.total),
      false,
    );
  }
  for (const d of derived) {
    for (let i = 0; i < d.edges.length; i++) {
      const e = d.edges[i]!;
      const node = d.nodes[i]!;
      addNode(node);
      e.cancerContext = e.cancerContext.map((c) => (c.slug ? c : (names.get(c.id) ?? c)));
      addEdge(e, d.total, true);
    }
  }

  const degreeByType: Record<NodeType, number> = { cancer: 0, gene: 0, variant: 0, drug: 0, trial: 0, approval: 0 };
  for (const node of nodes.values()) degreeByType[node.type] += 1;
  const focusOut: GraphNode = { ...focus, degree: [...groups.values()].reduce((a, g) => a + g.total, 0) };
  return { focus: focusOut, nodes: [...nodes.values()], groups: sortGroups([...groups.values()]), degreeByType, cancerIds };
}

// ---------------------------------------------------------------------------------------------
// Paths (cancer focus)
// ---------------------------------------------------------------------------------------------

/**
 * Strongest cancer → gene → variant → drug → approval → trials chains. The variant → drug hop is a
 * source-native PREDICTS_RESPONSE_TO edge (direction sensitivity) whose context includes the cancer
 * or one of its descendants; the gene hop is the variant's gene with its top cohort frequency in
 * the cancer — the cohort with the largest denominator (null when no cohort covers it — shown as "not yet available", never invented); the
 * approval hop is the earliest drug_approvals row in the cancer (or tumour-agnostic); the trial
 * hop counts registry trials listing the drug for the cancer. Ranked by level, then support.
 */
export async function loadPaths(focus: FocusNode, cancerIds: string[], limit = PATHS_LIMIT): Promise<PathChain[]> {
  if (focus.type !== 'cancer' || cancerIds.length === 0) return [];
  type Row = {
    variant_id: string; variant_slug: string; variant_name: string; gene_id: string; symbol: string; drug_id: string; drug_slug: string; drug_name: string;
    evidence_level: string | null; direction: string | null; support: string; source_ids: string[]; provenance_ids: number[]; context_ids: string[];
    frequency: number | null; cases_affected: number | null; cases_profiled: number | null; cohorts: string | null;
    approval_id: number | null; jurisdiction: string | null; authority: string | null; approval_date: string | null; approval_status: string | null; approval_cancer_id: string | null; approval_cancer_name: string | null; tumor_agnostic: boolean | null; approvals: string | null;
    trials: string | null; active_trials: string | null;
  };
  const rows = await safe(
    () =>
      run<Row>(sql`
        WITH ids AS (SELECT unnest(ARRAY[${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}]::varchar[]) AS id),
        ed AS (
          SELECT ke.source_entity_id AS variant_id, ke.target_entity_id AS drug_id, min(${LEVEL_RANK}) AS lvl, min(ke.evidence_level) AS evidence_level, min(ke.direction) AS direction,
                 sum(ke.support_count) AS support, array_agg(DISTINCT ke.source_id) AS source_ids,
                 (SELECT (array_agg(DISTINCT x::int ORDER BY x::int))[1:50] FROM unnest(string_to_array(string_agg(array_to_string(ke.provenance_ids, ','), ','), ',')) x WHERE x <> '') AS provenance_ids,
                 (SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(string_to_array(string_agg(array_to_string(ke.cancer_context_ids, ','), ','), ',')) x WHERE x <> '' AND x IN (SELECT id FROM ids)) AS context_ids
          FROM knowledge_edges ke
          WHERE ke.status = 'active' AND ke.relationship_type = 'PREDICTS_RESPONSE_TO' AND ke.direction = 'sensitivity' AND ke.source_entity_type = 'variant' AND ke.target_entity_type = 'drug'
            AND ke.cancer_context_ids && (SELECT array_agg(id)::text[] FROM ids)
          GROUP BY ke.source_entity_id, ke.target_entity_id
        ),
        fq AS (
          SELECT DISTINCT ON (f.gene_id) f.gene_id, f.frequency, f.cases_affected, f.cases_profiled, count(*) OVER (PARTITION BY f.gene_id) AS cohorts
          FROM cancer_gene_frequencies f WHERE f.cancer_id IN (SELECT id FROM ids) AND f.gene_id IS NOT NULL AND f.cases_affected >= ${CASES_MIN}
          ORDER BY f.gene_id, f.cases_profiled DESC, f.frequency DESC
        )
        SELECT ed.variant_id, v.slug AS variant_slug, v.name AS variant_name, g.id AS gene_id, g.symbol, ed.drug_id, d.slug AS drug_slug, d.name AS drug_name,
               ed.evidence_level, ed.direction, ed.support, ed.source_ids, ed.provenance_ids, ed.context_ids,
               fq.frequency, fq.cases_affected, fq.cases_profiled, fq.cohorts,
               ap.id AS approval_id, ap.jurisdiction, ap.authority, ap.approval_date, ap.status AS approval_status, ap.cancer_id AS approval_cancer_id, ac.canonical_name AS approval_cancer_name, ap.tumor_agnostic, ap.approvals,
               tr.trials, tr.active_trials
        FROM ed JOIN variants v ON v.id = ed.variant_id JOIN genes g ON g.id = v.gene_id JOIN drugs d ON d.id = ed.drug_id
        LEFT JOIN fq ON fq.gene_id = g.id
        LEFT JOIN LATERAL (
          SELECT a.id, a.jurisdiction, a.authority, a.approval_date, a.status, a.cancer_id, a.tumor_agnostic, count(*) OVER() AS approvals
          FROM drug_approvals a WHERE a.drug_id = ed.drug_id AND (a.cancer_id IN (SELECT id FROM ids) OR a.tumor_agnostic)
          ORDER BY (a.cancer_id IS NOT NULL) DESC, a.approval_date ASC NULLS LAST, a.id LIMIT 1
        ) ap ON true
        LEFT JOIN cancers ac ON ac.id = ap.cancer_id
        LEFT JOIN LATERAL (
          SELECT count(DISTINCT ti.trial_id) AS trials, count(DISTINCT ti.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active_trials
          FROM trial_interventions ti JOIN trial_conditions tc ON tc.trial_id = ti.trial_id AND tc.cancer_id IN (SELECT id FROM ids) JOIN clinical_trials t ON t.id = ti.trial_id
          WHERE ti.drug_id = ed.drug_id
        ) tr ON true
        ORDER BY ed.lvl, ed.support DESC, fq.frequency DESC NULLS LAST, g.symbol, v.name, d.name
        LIMIT ${limit}`),
    [] as Row[],
  );
  const names = await cancerNames(rows.flatMap((r) => r.context_ids ?? []));
  const chains: PathChain[] = rows.map((r) => ({
    cancer: { id: focus.id, slug: focus.ref, name: focus.label },
    gene: { id: r.gene_id, symbol: r.symbol, frequency: r.frequency, casesAffected: r.cases_affected, casesProfiled: r.cases_profiled, cohorts: n(r.cohorts) },
    variant: { id: r.variant_id, slug: r.variant_slug, name: r.variant_name },
    drug: { id: r.drug_id, slug: r.drug_slug, name: r.drug_name },
    edge: { evidenceLevel: r.evidence_level, direction: r.direction, supportCount: n(r.support), sourceIds: r.source_ids ?? [], provenanceIds: (r.provenance_ids ?? []).map(Number), contextIds: r.context_ids ?? [], contextNames: (r.context_ids ?? []).map((id) => names.get(id)?.name ?? id) },
    approval: r.approval_id ? { id: Number(r.approval_id), jurisdiction: r.jurisdiction!, authority: r.authority!, approvalDate: r.approval_date, status: r.approval_status!, cancerId: r.approval_cancer_id, cancerName: r.approval_cancer_name, tumorAgnostic: !!r.tumor_agnostic, total: n(r.approvals) } : null,
    trials: r.trials !== null && r.trials !== undefined ? { total: n(r.trials), active: n(r.active_trials) } : null,
  }));
  return chains.sort(compareChains);
}

// ---------------------------------------------------------------------------------------------
// Default focus + example foci (data-driven, never hardcoded)
// ---------------------------------------------------------------------------------------------

export interface FocusSuggestion {
  type: Exclude<NodeType, 'approval' | 'trial' | 'variant'>;
  ref: string;
  label: string;
  edges: number;
}

/** Most-connected cancers (2), genes (2) and drugs (2) by knowledge-edge count; the first cancer is the default focus. */
export async function suggestedFoci(): Promise<FocusSuggestion[]> {
  type Row = { type: FocusSuggestion['type']; ref: string; label: string; edges: string };
  const rows = await safe(
    () =>
      run<Row>(sql`
        WITH k AS (
          SELECT eid, count(*) AS n FROM (
            SELECT source_entity_id AS eid FROM knowledge_edges WHERE status = 'active'
            UNION ALL SELECT target_entity_id FROM knowledge_edges WHERE status = 'active'
            UNION ALL SELECT unnest(cancer_context_ids) FROM knowledge_edges WHERE status = 'active'
          ) x GROUP BY eid
        )
        (SELECT 'cancer' AS type, c.slug AS ref, c.canonical_name AS label, k.n AS edges FROM k JOIN cancers c ON c.id = k.eid WHERE c.status = 'active' ORDER BY k.n DESC, c.slug LIMIT 2)
        UNION ALL (SELECT 'gene', g.symbol, g.symbol, k.n FROM k JOIN genes g ON g.id = k.eid ORDER BY k.n DESC, g.symbol LIMIT 2)
        UNION ALL (SELECT 'drug', d.slug, d.name, k.n FROM k JOIN drugs d ON d.id = k.eid ORDER BY k.n DESC, d.slug LIMIT 2)`),
    [] as Row[],
  );
  return rows.map((r) => ({ type: r.type, ref: r.ref, label: r.label, edges: n(r.edges) }));
}

export async function defaultFocus(): Promise<FocusRef | null> {
  const s = (await suggestedFoci()).find((x) => x.type === 'cancer');
  return s ? { type: 'cancer', ref: s.ref } : null;
}

/** Freshness: latest last_seen_at across the focus' knowledge edges. */
export async function edgesFreshness(focus: FocusNode): Promise<Date | null> {
  const rows = await safe(() => run<{ t: Date | null }>(sql`SELECT max(last_seen_at) AS t FROM knowledge_edges WHERE source_entity_id = ${focus.id} OR target_entity_id = ${focus.id} OR ${focus.id} = ANY(cancer_context_ids)`), [{ t: null }]);
  return rows[0]?.t ?? null;
}