spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import { getMetric, latestSnapshotForGeo, rankingRows, type MetricDef, type RankingRow, type Snapshot } from '@/lib/queries/rankings';45/** One burden scope with computed research-gap components (SPEC §34, §113). */6export interface GapScope {7  geography: string;8  year: number;9  sex: string;10  burden_source_id: string;11  source_slug: string;12  source_name: string;13  n_total: number;14  n_eligible: number;15  formula_version: string;16  computed_at: Date | string;17}1819export function gapScopeKey(s: Pick<GapScope, 'geography' | 'year' | 'sex'>): string {20  return `geo=${s.geography}|sex=${s.sex}|age=all|year=${s.year}|level=top`;21}2223export async function listGapScopes(): Promise<GapScope[]> {24  return safe(25    () =>26      run<GapScope>(sql`27        SELECT r.geography, r.year, r.sex, r.burden_source_id, s.slug AS source_slug, s.name AS source_name,28               count(*)::int AS n_total, count(*) FILTER (WHERE r.eligible)::int AS n_eligible,29               max(r.formula_version) AS formula_version, max(r.updated_at) AS computed_at30        FROM research_gap_components r JOIN sources s ON s.id = r.burden_source_id31        GROUP BY r.geography, r.year, r.sex, r.burden_source_id, s.slug, s.name32        ORDER BY r.geography, r.year DESC, r.sex, count(*) DESC, s.slug`),33    [] as GapScope[],34  );35}3637/**38 * Resolve the requested scope: geography (default USA), sex (default all), year (default latest),39 * source (default: the source with the most component rows in that geography/year/sex).40 */41export function pickGapScope(scopes: GapScope[], want: { geography?: string; year?: number | null; sex?: string; source?: string }): GapScope | null {42  const geo = (want.geography || 'USA').toUpperCase();43  let pool = scopes.filter((s) => s.geography.toUpperCase() === geo);44  if (pool.length === 0) pool = scopes;45  const sex = want.sex || 'all';46  const bySex = pool.filter((s) => s.sex === sex);47  if (bySex.length) pool = bySex;48  if (want.year) {49    const byYear = pool.filter((s) => Number(s.year) === want.year);50    if (byYear.length) pool = byYear;51  }52  const latest = Math.max(...pool.map((s) => Number(s.year)));53  pool = pool.filter((s) => Number(s.year) === latest);54  if (want.source) {55    const bySrc = pool.filter((s) => s.source_slug === want.source || s.burden_source_id === want.source);56    if (bySrc.length) pool = bySrc;57  }58  return [...pool].sort((a, b) => b.n_total - a.n_total || a.source_slug.localeCompare(b.source_slug))[0] ?? null;59}6061export interface GapComponent {62  component_id: number;63  cancer_id: string;64  slug: string;65  canonical_name: string;66  short_name: string | null;67  hematologic: boolean;68  deaths: number | null;69  incidence: number | null;70  active_trials: number;71  phase3_trials: number;72  publications_5y: number;73  approved_drugs: number;74  death_share: number | null;75  trial_share: number | null;76  publication_share: number | null;77  trial_gap_ratio: number | null;78  research_gap_ratio: number | null;79  trials_per_1000_deaths: number | null;80  publications_per_1000_deaths: number | null;81  eligible: boolean;82  ineligible_reason: string | null;83  formula_version: string;84  inputs: Record<string, unknown>;85  computed_at: Date | string;86  trial_gap_rank: number | null;87  research_gap_rank: number | null;88}8990export async function gapComponents(scope: Pick<GapScope, 'geography' | 'year' | 'sex' | 'burden_source_id'>): Promise<GapComponent[]> {91  const key = gapScopeKey(scope);92  return safe(93    () =>94      run<GapComponent>(sql`95        SELECT r.id AS component_id, r.cancer_id, c.slug, c.canonical_name, c.short_name, c.hematologic,96               r.deaths, r.incidence, r.active_trials, r.phase3_trials, r.publications_5y, r.approved_drugs,97               r.death_share, r.trial_share, r.publication_share, r.trial_gap_ratio, r.research_gap_ratio,98               r.trials_per_1000_deaths, r.publications_per_1000_deaths, r.eligible, r.ineligible_reason,99               r.formula_version, r.inputs, r.updated_at AS computed_at,100               tg.rank AS trial_gap_rank, rg.rank AS research_gap_rank101        FROM research_gap_components r JOIN cancers c ON c.id = r.cancer_id102        LEFT JOIN rankings tg ON tg.cancer_id = r.cancer_id AND tg.metric_slug = 'trial_gap_ratio' AND tg.scope_key = ${key}103          AND tg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'trial_gap_ratio' AND scope_key = ${key} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)104        LEFT JOIN rankings rg ON rg.cancer_id = r.cancer_id AND rg.metric_slug = 'research_gap_ratio' AND rg.scope_key = ${key}105          AND rg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'research_gap_ratio' AND scope_key = ${key} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)106        WHERE r.geography = ${scope.geography} AND r.year = ${Number(scope.year)} AND r.sex = ${scope.sex} AND r.burden_source_id = ${scope.burden_source_id}107        ORDER BY r.eligible DESC, r.research_gap_ratio DESC NULLS LAST, c.canonical_name`),108    [] as GapComponent[],109  );110}111112/** Sums over the eligible set (what the shares were divided by). */113export function gapSums(rows: GapComponent[]): { deaths: number; activeTrials: number; publications5y: number; eligible: number } {114  const e = rows.filter((r) => r.eligible);115  return {116    deaths: e.reduce((s, r) => s + Number(r.deaths ?? 0), 0),117    activeTrials: e.reduce((s, r) => s + Number(r.active_trials), 0),118    publications5y: e.reduce((s, r) => s + Number(r.publications_5y), 0),119    eligible: e.length,120  };121}122123/** The latest scope for a geography (sex all, most-populated source) — used by the home module and cancer card. */124export async function latestGapScope(geography = 'USA'): Promise<GapScope | null> {125  const scopes = await listGapScopes();126  return pickGapScope(scopes, { geography, sex: 'all' });127}128129/** One cancer's component row in the latest scope of a geography (null when not part of the scope). */130export async function gapComponentForCancer(cancerId: string, geography = 'USA'): Promise<{ scope: GapScope; row: GapComponent; sums: ReturnType<typeof gapSums> } | null> {131  const scope = await latestGapScope(geography);132  if (!scope) return null;133  const rows = await gapComponents(scope);134  const row = rows.find((r) => r.cancer_id === cancerId);135  if (!row) return null;136  return { scope, row, sums: gapSums(rows) };137}138139/** Home module: the ratio-based gap metrics for a geography (latest snapshot), top rows with lineage. */140export async function ratioGapRankings(geo: string, limit = 8): Promise<Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }>> {141  const out: Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }> = [];142  for (const slug of ['trial_gap_ratio', 'research_gap_ratio']) {143    const snapshot = await latestSnapshotForGeo(slug, geo);144    if (!snapshot) continue;145    const metric = await getMetric(slug);146    if (!metric) continue;147    const rows = await rankingRows(snapshot.id, limit);148    if (rows.length) out.push({ metric, snapshot, rows });149  }150  return out;151}152153/** Metric definitions used on the research-gap page (formula + version per column). */154export async function gapMetricDefs(): Promise<Map<string, MetricDef>> {155  const rows = await safe(() => run<MetricDef>(sql`SELECT m.*, 0::int AS snapshot_count FROM metric_definitions m WHERE m.slug IN ('trial_gap_ratio','research_gap_ratio','trials_per_1000_deaths','publications_per_1000_deaths')`), [] as MetricDef[]);156  return new Map(rows.map((r) => [r.slug, r]));157}158