spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';34export const MAX_HIERARCHY_DEPTH = 12;56/**7 * Cancer + all descendants across every hierarchy type (ncit, oncotree, …), depth ≤ 12 — the same8 * traversal the ranking counters use (packages/ranking/src/counters.ts) so API numbers match rankings.9 */10export async function descendantIds(db: Database, cancerId: string, hierarchyType?: string): Promise<string[]> {11  const rows = await db.execute<{ id: string }>(sql`12    WITH RECURSIVE d AS (13      SELECT ${cancerId}::varchar AS id, 0 AS depth14      UNION15      SELECT h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.id16      WHERE d.depth < ${MAX_HIERARCHY_DEPTH} ${hierarchyType ? sql`AND h.hierarchy_type = ${hierarchyType}` : sql``}17    )18    SELECT DISTINCT id FROM d`);19  return rows.map((r) => r.id);20}2122/** Ancestors (for breadcrumbs), nearest first, depth ≤ 12. */23export async function ancestorChain(db: Database, cancerId: string, hierarchyType = 'ncit'): Promise<Array<{ id: string; slug: string; name: string; depth: number }>> {24  const rows = await db.execute<{ id: string; slug: string; name: string; depth: number }>(sql`25    WITH RECURSIVE a AS (26      SELECT h.parent_id AS id, 1 AS depth FROM cancer_hierarchy h WHERE h.child_id = ${cancerId} AND h.hierarchy_type = ${hierarchyType}27      UNION28      SELECT h.parent_id, a.depth + 1 FROM a JOIN cancer_hierarchy h ON h.child_id = a.id AND h.hierarchy_type = ${hierarchyType}29      WHERE a.depth < ${MAX_HIERARCHY_DEPTH}30    )31    SELECT DISTINCT ON (a.id) a.id, c.slug, c.canonical_name AS name, a.depth FROM a JOIN cancers c ON c.id = a.id ORDER BY a.id, a.depth`);32  return rows.map((r) => ({ ...r, depth: Number(r.depth) })).sort((x, y) => x.depth - y.depth);33}34