spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface TreeNode {5  id: string;6  slug: string;7  canonical_name: string;8  entity_type: string;9  malignant: boolean;10  primary_oncotree_code: string | null;11  primary_ncit_code: string | null;12  child_count: number;13}1415export async function hierarchyTypes(): Promise<Array<{ hierarchy_type: string; n: number }>> {16  const rows = await safe(() => run<{ hierarchy_type: string; n: string }>(sql`SELECT hierarchy_type, count(*) AS n FROM cancer_hierarchy GROUP BY 1 ORDER BY 1`), []);17  return rows.map((r) => ({ hierarchy_type: r.hierarchy_type, n: Number(r.n) }));18}1920/** Root nodes of a hierarchy: nodes that are parents but have no parent of the same type. */21export async function rootsOf(hierarchyType: string, limit = 400): Promise<TreeNode[]> {22  return safe(23    () =>24      run<TreeNode>(sql`25        SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, c.primary_oncotree_code, c.primary_ncit_code,26               (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id AND x.hierarchy_type = ${hierarchyType})::int AS child_count27        FROM cancers c28        WHERE c.status = 'active'29          AND EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE h.parent_id = c.id AND h.hierarchy_type = ${hierarchyType})30          AND NOT EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE h.child_id = c.id AND h.hierarchy_type = ${hierarchyType})31        ORDER BY c.canonical_name LIMIT ${limit}`),32    [] as TreeNode[],33  );34}3536export async function childrenOf(parentId: string, hierarchyType: string, limit = 500): Promise<TreeNode[]> {37  return safe(38    () =>39      run<TreeNode>(sql`40        SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, c.primary_oncotree_code, c.primary_ncit_code,41               (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id AND x.hierarchy_type = ${hierarchyType})::int AS child_count42        FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id43        WHERE h.parent_id = ${parentId} AND h.hierarchy_type = ${hierarchyType} AND c.status = 'active'44        ORDER BY c.canonical_name LIMIT ${limit}`),45    [] as TreeNode[],46  );47}4849/** Orphan nodes (no parent in the given hierarchy and no children) — listed separately so nothing is hidden. */50export async function orphanCount(hierarchyType: string): Promise<number> {51  const rows = await safe(52    () =>53      run<{ n: string }>(sql`54        SELECT count(*) AS n FROM cancers c WHERE c.status = 'active'55          AND NOT EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE (h.parent_id = c.id OR h.child_id = c.id) AND h.hierarchy_type = ${hierarchyType})`),56    [{ n: '0' }],57  );58  return Number(rows[0]?.n ?? 0);59}6061export interface SiteGroup {62  id: string;63  slug: string;64  name: string;65  system: string | null;66  ncit_code: string | null;67  cancers: Array<{ id: string; slug: string; canonical_name: string; entity_type: string; malignant: boolean; relation: string }>;68}6970export async function anatomicalView(): Promise<SiteGroup[]> {71  const sites = await safe(() => run<{ id: string; slug: string; name: string; system: string | null; ncit_code: string | null }>(sql`SELECT id, slug, name, system, ncit_code FROM anatomical_sites ORDER BY name`), []);72  const links = await safe(73    () =>74      run<{ site_id: string; id: string; slug: string; canonical_name: string; entity_type: string; malignant: boolean; relation: string }>(sql`75        SELECT ca.site_id, c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, ca.relation76        FROM cancer_anatomy ca JOIN cancers c ON c.id = ca.cancer_id WHERE c.status = 'active' ORDER BY c.canonical_name`),77    [],78  );79  const bySite = new Map<string, SiteGroup>();80  for (const s of sites) bySite.set(s.id, { ...s, cancers: [] });81  for (const l of links) bySite.get(l.site_id)?.cancers.push(l);82  return [...bySite.values()];83}84