spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface MetricDef {5  id: string;6  slug: string;7  name: string;8  description: string;9  formula: string;10  formula_version: string;11  unit: string;12  higher_is_worse: boolean | null;13  aggregation: string | null;14  valid_dimensions: string[];15  source_slugs: string[];16  category: string;17  eligibility: Record<string, unknown>;18  experimental: boolean;19  snapshot_count: number;20}2122// NOTE: `rankings`/`ranking_snapshots` use generated_at; `entity_counters` and `literature_counts`23// declare computedAt via the updatedAt() helper, so their physical column is `updated_at`.24export async function listMetrics(): Promise<MetricDef[]> {25  const rows = await safe(26    () =>27      run<MetricDef>(sql`28        SELECT m.*, (SELECT count(*) FROM ranking_snapshots s WHERE s.metric_slug = m.slug AND s.is_current)::int AS snapshot_count29        FROM metric_definitions m ORDER BY m.category, m.name`),30    [] as MetricDef[],31  );32  return rows;33}3435export async function getMetric(slug: string): Promise<MetricDef | null> {36  const rows = await safe(() => run<MetricDef>(sql`SELECT m.*, (SELECT count(*) FROM ranking_snapshots s WHERE s.metric_slug = m.slug AND s.is_current)::int AS snapshot_count FROM metric_definitions m WHERE m.slug = ${slug}`), [] as MetricDef[]);37  return rows[0] ?? null;38}3940export interface Snapshot {41  id: number;42  metric_id: string;43  metric_slug: string;44  scope_key: string;45  geography: string;46  sex: string;47  age_group: string;48  year: number | null;49  entity_level: string;50  formula_version: string;51  eligible_entities: number;52  inputs_hash: string;53  source_ids: string[];54  is_current: boolean;55  generated_at: Date;56}5758export async function snapshotsForMetric(slug: string): Promise<Snapshot[]> {59  return safe(() => run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current ORDER BY (entity_level = 'top') DESC, geography, year DESC NULLS LAST, sex`), [] as Snapshot[]);60}6162export async function getSnapshot(slug: string, scopeKey: string | null): Promise<Snapshot | null> {63  const rows = await safe(64    () =>65      run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current AND ${scopeKey ? sql`scope_key = ${scopeKey}` : sql`true`}66        ORDER BY (entity_level = 'top') DESC, (geography = 'WORLD') DESC, year DESC NULLS LAST LIMIT 1`),67    [] as Snapshot[],68  );69  return rows[0] ?? null;70}7172export interface RankingRow {73  id: number;74  rank: number;75  previous_rank: number | null;76  cancer_id: string;77  slug: string;78  canonical_name: string;79  value: number;80  unit: string;81  confidence: string;82  percentile: number;83  eligible_entities: number;84  inputs: Record<string, unknown>;85}8687export async function rankingRows(snapshotId: number, limit = 1000): Promise<RankingRow[]> {88  return safe(89    () =>90      run<RankingRow>(sql`91        SELECT r.id, r.rank, r.previous_rank, r.cancer_id, c.slug, c.canonical_name, r.value, r.unit, r.confidence, r.percentile, r.eligible_entities, r.inputs92        FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${snapshotId} ORDER BY r.rank, c.canonical_name LIMIT ${limit}`),93    [] as RankingRow[],94  );95}9697/** Every current ranking row for one cancer — the "Why this rank?" panel (§183). */98export interface CancerRanking {99  id: number;100  metric_slug: string;101  metric_name: string;102  unit: string;103  scope_key: string;104  formula_version: string;105  formula: string;106  rank: number;107  previous_rank: number | null;108  eligible_entities: number;109  value: number;110  confidence: string;111  percentile: number;112  inputs: Record<string, unknown>;113  generated_at: Date;114  inputs_hash: string;115  source_ids: string[];116}117export async function rankingsForCancer(cancerId: string): Promise<CancerRanking[]> {118  return safe(119    () =>120      run<CancerRanking>(sql`121        SELECT r.id, r.metric_slug, m.name AS metric_name, r.unit, r.scope_key, s.formula_version, m.formula, r.rank, r.previous_rank, r.eligible_entities, r.value, r.confidence, r.percentile, r.inputs, s.generated_at, s.inputs_hash, s.source_ids122        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id AND s.is_current JOIN metric_definitions m ON m.slug = r.metric_slug123        WHERE r.cancer_id = ${cancerId} ORDER BY m.category, m.name, s.scope_key`),124    [] as CancerRanking[],125  );126}127128/** Home preview: the best available current top-level snapshot for a list of preferred metrics. */129export async function previewSnapshot(preferred: string[]): Promise<{ snapshot: Snapshot; metric: MetricDef; rows: RankingRow[] } | null> {130  for (const slug of preferred) {131    const snap = await getSnapshot(slug, null);132    if (!snap) continue;133    const metric = await getMetric(slug);134    if (!metric) continue;135    const rows = await rankingRows(snap.id, 10);136    if (rows.length) return { snapshot: snap, metric, rows };137  }138  return null;139}140141/**142 * Current ranking rows for several cancers at once (compare page, overview hero). Same shape as143 * rankingsForCancer; callers keep the latest year per (metric, scope-without-year) with pickLatestScopes.144 */145export async function rankingsForCancers(cancerIds: string[]): Promise<Array<CancerRanking & { cancer_id: string }>> {146  if (cancerIds.length === 0) return [];147  return safe(148    () =>149      run<CancerRanking & { cancer_id: string }>(sql`150        SELECT r.id, r.cancer_id, r.metric_slug, m.name AS metric_name, r.unit, r.scope_key, s.formula_version, m.formula, r.rank, r.previous_rank, r.eligible_entities, r.value, r.confidence, r.percentile, r.inputs, s.generated_at, s.inputs_hash, s.source_ids151        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id AND s.is_current JOIN metric_definitions m ON m.slug = r.metric_slug152        WHERE r.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) ORDER BY m.category, m.name, s.scope_key`),153    [],154  );155}156157/** Keep, per (cancer, metric, scope without year), only the latest year. Ties keep the first row. */158export function pickLatestScopes<T extends { metric_slug: string; scope_key: string; cancer_id?: string }>(rows: T[]): T[] {159  const yearOf = (k: string) => Number(/year=(\d{4})/.exec(k)?.[1] ?? 0);160  const out = new Map<string, T>();161  for (const r of rows) {162    const key = `${r.cancer_id ?? ''}|${r.metric_slug}|${r.scope_key.replace(/year=[^|]*/, '')}`;163    const cur = out.get(key);164    if (!cur || yearOf(r.scope_key) > yearOf(cur.scope_key)) out.set(key, r);165  }166  return [...out.values()];167}168169/**170 * Pick the most relevant current rank for one metric: prefer the requested geography, then the requested171 * entity level (top for top-level entities, all otherwise), then the latest year.172 */173export function bestRankFor<T extends { metric_slug: string; scope_key: string }>(rows: T[], metricSlug: string, opts: { geo?: string; level?: 'top' | 'all' } = {}): T | null {174  const cands = rows.filter((r) => r.metric_slug === metricSlug);175  if (cands.length === 0) return null;176  const score = (k: string) => {177    let s = 0;178    if (opts.geo && k.includes(`geo=${opts.geo}|`)) s += 100;179    if (opts.level && k.endsWith(`level=${opts.level}`)) s += 10;180    s += Number(/year=(\d{4})/.exec(k)?.[1] ?? 0) / 10_000;181    return s;182  };183  return [...cands].sort((a, b) => score(b.scope_key) - score(a.scope_key))[0] ?? null;184}185186/** Latest current snapshot of a metric for a geography code (e.g. USA), top level. */187export async function latestSnapshotForGeo(slug: string, geo: string, sex = 'all'): Promise<Snapshot | null> {188  const rows = await safe(189    () => run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current AND geography = ${geo} AND sex = ${sex} AND entity_level = 'top' ORDER BY year DESC NULLS LAST, generated_at DESC LIMIT 1`),190    [] as Snapshot[],191  );192  return rows[0] ?? null;193}194195/** Home module: the gap indexes (trial gap, research gap) for a geography, top rows with lineage. */196export async function gapRankings(geo: string, limit = 8): Promise<Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }>> {197  const out: Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }> = [];198  for (const slug of ['trial_gap', 'research_gap']) {199    const snapshot = await latestSnapshotForGeo(slug, geo);200    if (!snapshot) continue;201    const metric = await getMetric(slug);202    if (!metric) continue;203    const rows = await rankingRows(snapshot.id, limit);204    if (rows.length) out.push({ metric, snapshot, rows });205  }206  return out;207}208209export async function listSnapshots(limit = 200): Promise<Array<Snapshot & { metric_name: string; row_count: number }>> {210  return safe(211    () => run<Snapshot & { metric_name: string; row_count: number }>(sql`SELECT s.*, m.name AS metric_name, (SELECT count(*) FROM rankings r WHERE r.snapshot_id = s.id)::int AS row_count FROM ranking_snapshots s JOIN metric_definitions m ON m.id = s.metric_id ORDER BY s.generated_at DESC LIMIT ${limit}`),212    [],213  );214}215