spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import type { CancerCore, Counters } from '@/lib/queries/cancers';4import { latestFiguresFor, nearestRegistryAncestor, type LatestFigure, type RegistryAncestor } from '@/lib/queries/epidemiology';5import { rankingsForCancers, pickLatestScopes, type CancerRanking } from '@/lib/queries/rankings';67/**8 * Compare engine (SPEC §100): side-by-side facts for 2–4 cancers. Only facts that exist in the database are9 * shown; registry figures for entities below the top level fall back to the nearest top-level ancestor and10 * are labelled as such (see /methodology#compare).11 */1213export const COMPARE_MIN = 2;14export const COMPARE_MAX = 4;1516export interface CompareEntity {17  cancer: CancerCore;18  parents: Array<{ slug: string; canonical_name: string; hierarchy_type: string }>;19  counters: Counters | null;20  registry: RegistryAncestor | null; // entity whose observations are shown (self when top-level)21  figures: Map<string, LatestFigure>; // metric → latest observation (sex=all, USA)22  ranks: Array<CancerRanking & { cancer_id: string }>; // latest year per scope23}2425export function parseCompareIds(raw: string | undefined): string[] {26  if (!raw) return [];27  const seen = new Set<string>();28  const out: string[] = [];29  for (const part of raw.split(',')) {30    const s = part.trim().toLowerCase();31    if (!/^[a-z0-9][a-z0-9-]{0,120}$/.test(s) || seen.has(s)) continue;32    seen.add(s);33    out.push(s);34    if (out.length >= COMPARE_MAX) break;35  }36  return out;37}3839export async function resolveCancers(slugs: string[]): Promise<CancerCore[]> {40  if (slugs.length === 0) return [];41  const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE slug IN (${sql.join(slugs.map((s) => sql`${s}`), sql`, `)}) AND status <> 'merged'`), [] as CancerCore[]);42  const bySlug = new Map(rows.map((r) => [r.slug, r]));43  return slugs.map((s) => bySlug.get(s)).filter((r): r is CancerCore => !!r);44}4546export async function loadCompare(slugs: string[]): Promise<CompareEntity[]> {47  const cancers = await resolveCancers(slugs);48  if (cancers.length === 0) return [];49  const ids = cancers.map((c) => c.id);50  const [parents, counters, ancestors] = await Promise.all([51    safe(52      () =>53        run<{ child_id: string; slug: string; canonical_name: string; hierarchy_type: string }>(sql`54          SELECT h.child_id, p.slug, p.canonical_name, h.hierarchy_type FROM cancer_hierarchy h JOIN cancers p ON p.id = h.parent_id55          WHERE h.child_id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)}) ORDER BY h.hierarchy_type, p.canonical_name`),56      [],57    ),58    safe(() => run<Counters & { entity_id: string }>(sql`SELECT * FROM entity_counters WHERE entity_type = 'cancer' AND entity_id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`), []),59    Promise.all(cancers.map((c) => (c.top_level ? Promise.resolve<RegistryAncestor | null>({ id: c.id, slug: c.slug, canonical_name: c.canonical_name, depth: 0 }) : nearestRegistryAncestor(c.id)))),60  ]);61  const registryIds = [...new Set(ancestors.filter((a): a is RegistryAncestor => !!a).map((a) => a.id))];62  const rankIds = [...new Set([...ids, ...registryIds])];63  const [figures, ranks] = await Promise.all([latestFiguresFor(registryIds, 'USA', 'all'), rankingsForCancers(rankIds)]);64  const latestRanks = pickLatestScopes(ranks);65  const countersById = new Map(counters.map((c) => [c.entity_id, c]));66  return cancers.map((c, i) => {67    const registry = ancestors[i] ?? null;68    const figs = new Map<string, LatestFigure>();69    if (registry) for (const f of figures) if (f.cancer_id === registry.id) figs.set(f.metric, f);70    return {71      cancer: c,72      parents: parents.filter((p) => p.child_id === c.id).slice(0, 4),73      counters: countersById.get(c.id) ?? null,74      registry,75      figures: figs,76      ranks: latestRanks.filter((r) => r.cancer_id === c.id || (registry && registry.depth > 0 && r.cancer_id === registry.id && /^(mortality_count|incidence_count|as_mortality_rate|as_incidence_rate|mortality_incidence_ratio|trial_gap|research_gap)$/.test(r.metric_slug))),77    };78  });79}80