spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface EpiObs {5  id: number;6  geography_id: string;7  geography_name: string;8  geography_slug: string;9  iso3: string | null;10  year: number;11  year_end: number | null;12  sex: string;13  age_group: string;14  metric: string;15  value: number;16  unit: string;17  lower_ci: number | null;18  upper_ci: number | null;19  standard_population: string | null;20  estimate_type: string;21  site_definition: string | null;22  source_id: string;23  source_slug: string;24  source_name: string;25  provenance_id: number;26  ingest_run_id: string | null;27  updated_at: Date;28}2930export async function epidemiologyFor(cancerId: string, limit = 2000): Promise<EpiObs[]> {31  return safe(32    () =>33      run<EpiObs>(sql`34        SELECT o.*, g.name AS geography_name, g.slug AS geography_slug, g.iso3, s.slug AS source_slug, s.name AS source_name35        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id36        WHERE o.cancer_id = ${cancerId}37        ORDER BY g.kind, g.name, o.metric, o.sex, o.age_group, o.year LIMIT ${limit}`),38    [] as EpiObs[],39  );40}4142export interface SurvObs {43  id: number;44  geography_name: string | null;45  stage: string | null;46  staging_system: string | null;47  sex: string;48  age_group: string;49  diagnosis_period: string | null;50  survival_type: string;51  duration_months: number;52  probability: number | null;53  median_months: number | null;54  cohort_size: number | null;55  lower_ci: number | null;56  upper_ci: number | null;57  method: string | null;58  source_slug: string;59  source_name: string;60  provenance_id: number;61  updated_at: Date;62}6364export async function survivalFor(cancerId: string, limit = 1000): Promise<SurvObs[]> {65  return safe(66    () =>67      run<SurvObs>(sql`68        SELECT o.*, g.name AS geography_name, s.slug AS source_slug, s.name AS source_name69        FROM survival_observations o LEFT JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id70        WHERE o.cancer_id = ${cancerId}71        ORDER BY o.survival_type, coalesce(o.stage, ''), o.diagnosis_period, o.duration_months LIMIT ${limit}`),72    [] as SurvObs[],73  );74}7576// ---------- Registry-level figures (overview hero, compare page, home) ----------7778export interface RegistryAncestor {79  id: string;80  slug: string;81  canonical_name: string;82  depth: number; // hierarchy steps from the requested entity (0 = the entity itself)83}8485/**86 * Nearest ancestor that belongs to the mutually exclusive top-level registry set (§246-247), walking every87 * hierarchy type upwards. Returns the entity itself (depth 0) when it is top-level; null when no top-level88 * ancestor exists (e.g. non-malignant branches).89 */90export async function nearestRegistryAncestor(cancerId: string): Promise<RegistryAncestor | null> {91  const rows = await safe(92    () =>93      run<RegistryAncestor>(sql`94        WITH RECURSIVE up AS (95          SELECT c.id, 0 AS depth, ARRAY[c.id]::varchar[] AS path FROM cancers c WHERE c.id = ${cancerId}96          UNION ALL97          SELECT h.parent_id, up.depth + 1, up.path || h.parent_id FROM up JOIN cancer_hierarchy h ON h.child_id = up.id98          WHERE up.depth < 12 AND NOT (h.parent_id = ANY(up.path))99        )100        SELECT c.id, c.slug, c.canonical_name, min(up.depth)::int AS depth101        FROM up JOIN cancers c ON c.id = up.id102        WHERE c.top_level AND c.status = 'active'103        GROUP BY c.id, c.slug, c.canonical_name ORDER BY depth ASC LIMIT 1`),104    [] as RegistryAncestor[],105  );106  return rows[0] ?? null;107}108109export interface LatestFigure {110  cancer_id: string;111  metric: string;112  year: number;113  year_end: number | null;114  sex: string;115  value: number;116  unit: string;117  lower_ci: number | null;118  upper_ci: number | null;119  estimate_type: string;120  standard_population: string | null;121  site_definition: string | null;122  geography_name: string;123  geography_slug: string;124  iso3: string | null;125  source_slug: string;126  source_name: string;127  provenance_id: number;128  updated_at: Date | string;129}130131/**132 * Latest-year observation per (cancer, metric) for one geography (ISO3) and sex, all ages.133 * One row per cancer × metric; the year may differ between metrics (incidence usually lags mortality).134 */135export async function latestFiguresFor(cancerIds: string[], iso3 = 'USA', sex = 'all'): Promise<LatestFigure[]> {136  if (cancerIds.length === 0) return [];137  return safe(138    () =>139      run<LatestFigure>(sql`140        SELECT DISTINCT ON (o.cancer_id, o.metric) o.cancer_id, o.metric, o.year, o.year_end, o.sex, o.value, o.unit, o.lower_ci, o.upper_ci, o.estimate_type, o.standard_population, o.site_definition,141               g.name AS geography_name, g.slug AS geography_slug, g.iso3, s.slug AS source_slug, s.name AS source_name, o.provenance_id, o.updated_at142        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id143        WHERE g.iso3 = ${iso3} AND o.sex = ${sex} AND o.age_group = 'all'144          AND o.metric IN ('mortality_count','incidence_count','as_mortality_rate','as_incidence_rate')145          AND o.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})146        ORDER BY o.cancer_id, o.metric, o.year DESC, (o.estimate_type = 'observed') DESC, o.updated_at DESC`),147    [] as LatestFigure[],148  );149}150151export const EPI_METRIC_LABEL: Record<string, string> = {152  incidence_count: 'New cases',153  incidence_rate: 'Incidence rate (crude)',154  as_incidence_rate: 'Incidence rate (age-standardized)',155  mortality_count: 'Deaths',156  mortality_rate: 'Mortality rate (crude)',157  as_mortality_rate: 'Mortality rate (age-standardized)',158  prevalence: 'Prevalence',159  prevalence_5y: '5-year prevalence',160};161