spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34/**5 * "Year in cancer" (SPEC §112): everything dated within one calendar year, from records already in6 * the index — approvals, registered studies, literature counts per entity, epidemiology observations7 * published for that year. No synthesis; each block names its source and rule.8 */910export const YEAR_MIN = 1999;1112export interface YearApprovalCounts {13  authority: string;14  jurisdiction: string;15  n: number;16  drugs: number;17  with_cancer: number;18}19export async function yearApprovalCounts(year: number): Promise<YearApprovalCounts[]> {20  return safe(21    () =>22      run<YearApprovalCounts>(sql`23        SELECT authority, jurisdiction, count(*)::int AS n, count(DISTINCT drug_id)::int AS drugs, count(*) FILTER (WHERE cancer_id IS NOT NULL)::int AS with_cancer24        FROM drug_approvals WHERE approval_date LIKE ${`${year}-%`} AND status IN ('approved','accelerated','conditional')25        GROUP BY authority, jurisdiction ORDER BY n DESC`),26    [] as YearApprovalCounts[],27  );28}2930export interface YearApproval {31  id: number;32  approval_date: string;33  authority: string;34  jurisdiction: string;35  status: string;36  approval_type: string | null;37  accelerated: boolean | null;38  indication: string;39  drug_slug: string;40  drug_name: string;41  cancer_slug: string | null;42  cancer_name: string | null;43  tumor_agnostic: boolean;44  source_slug: string;45}46/** Original approvals first (new molecules / first indications), then supplements; all with a mapped cancer first. */47export async function yearApprovals(year: number, limit = 60): Promise<YearApproval[]> {48  return safe(49    () =>50      run<YearApproval>(sql`51        SELECT a.id, a.approval_date, a.authority, a.jurisdiction, a.status, a.approval_type, a.accelerated, a.indication, a.tumor_agnostic,52               d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug53        FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id JOIN sources s ON s.id = a.source_id54        WHERE a.approval_date LIKE ${`${year}-%`} AND a.status IN ('approved','accelerated','conditional')55        ORDER BY (a.approval_type = 'ORIG') DESC, (a.cancer_id IS NOT NULL) DESC, a.approval_date DESC LIMIT ${limit}`),56    [] as YearApproval[],57  );58}5960export interface YearTrialPhase {61  phase: string;62  n: number;63  interventional: number;64  industry: number;65}66export async function yearTrialsByPhase(year: number): Promise<YearTrialPhase[]> {67  return safe(68    () =>69      run<YearTrialPhase>(sql`70        SELECT ph AS phase, count(*)::int AS n, count(*) FILTER (WHERE t.study_type = 'INTERVENTIONAL')::int AS interventional, count(*) FILTER (WHERE t.lead_sponsor_class = 'INDUSTRY')::int AS industry71        FROM clinical_trials t, unnest(CASE WHEN cardinality(t.phases) = 0 THEN ARRAY['NA'] ELSE t.phases END) ph72        WHERE t.first_posted_date LIKE ${`${year}-%`}73        GROUP BY ph ORDER BY CASE ph WHEN 'EARLY_PHASE1' THEN 1 WHEN 'PHASE1' THEN 2 WHEN 'PHASE2' THEN 3 WHEN 'PHASE3' THEN 4 WHEN 'PHASE4' THEN 5 ELSE 9 END`),74    [] as YearTrialPhase[],75  );76}7778export interface YearTrialTotals {79  total: number;80  interventional: number;81  phase3: number;82  industry: number;83  with_results: number;84  countries: number;85}86export async function yearTrialTotals(year: number): Promise<YearTrialTotals | null> {87  const rows = await safe(88    () =>89      run<YearTrialTotals>(sql`90        SELECT count(*)::int AS total, count(*) FILTER (WHERE study_type = 'INTERVENTIONAL')::int AS interventional, count(*) FILTER (WHERE 'PHASE3' = ANY(phases))::int AS phase3,91               count(*) FILTER (WHERE lead_sponsor_class = 'INDUSTRY')::int AS industry, count(*) FILTER (WHERE has_results)::int AS with_results,92               (SELECT count(DISTINCT c) FROM clinical_trials t2, unnest(t2.countries) c WHERE t2.first_posted_date LIKE ${`${year}-%`})::int AS countries93        FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`}`),94    [] as YearTrialTotals[],95  );96  const r = rows[0];97  return r && r.total > 0 ? r : null;98}99100export interface YearCancerTrials {101  id: string;102  slug: string;103  canonical_name: string;104  trials: number;105  phase3: number;106}107/** Top-level cancers by studies first posted in the year (conditions mapped to the cancer or any descendant, distinct studies). */108export async function yearTrialsByCancer(year: number, limit = 12): Promise<YearCancerTrials[]> {109  return safe(110    () =>111      run<YearCancerTrials>(sql`112        WITH RECURSIVE tops AS (113          SELECT id AS top_id, id AS cancer_id, 0 AS depth FROM cancers WHERE top_level AND status = 'active'114          UNION115          SELECT tops.top_id, h.child_id, tops.depth + 1 FROM tops JOIN cancer_hierarchy h ON h.parent_id = tops.cancer_id WHERE tops.depth < 12116        ),117        yr AS (SELECT id, phases FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`}),118        m AS (119          SELECT DISTINCT tops.top_id, yr.id AS trial_id, ('PHASE3' = ANY(yr.phases)) AS p3120          FROM yr JOIN trial_conditions tc ON tc.trial_id = yr.id AND tc.cancer_id IS NOT NULL JOIN tops ON tops.cancer_id = tc.cancer_id121        )122        SELECT c.id, c.slug, c.canonical_name, count(*)::int AS trials, count(*) FILTER (WHERE p3)::int AS phase3123        FROM m JOIN cancers c ON c.id = m.top_id GROUP BY c.id, c.slug, c.canonical_name ORDER BY trials DESC LIMIT ${limit}`),124    [] as YearCancerTrials[],125  );126}127128export interface YearSponsor {129  lead_sponsor: string;130  lead_sponsor_class: string | null;131  n: number;132  phase3: number;133}134export async function yearTopSponsors(year: number, limit = 10): Promise<YearSponsor[]> {135  return safe(136    () =>137      run<YearSponsor>(sql`138        SELECT lead_sponsor, min(lead_sponsor_class) AS lead_sponsor_class, count(*)::int AS n, count(*) FILTER (WHERE 'PHASE3' = ANY(phases))::int AS phase3139        FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`} AND study_type = 'INTERVENTIONAL' AND lead_sponsor IS NOT NULL140        GROUP BY lead_sponsor ORDER BY n DESC LIMIT ${limit}`),141    [] as YearSponsor[],142  );143}144145export interface YearLiterature {146  id: string;147  slug: string;148  canonical_name: string;149  count: number;150  prev_count: number | null;151  query: string;152  computed_at: Date | string;153}154/** Top-level cancers by PubMed records for the year (window_key yYYYY, query stored with each count). */155export async function yearLiterature(year: number, limit = 15): Promise<YearLiterature[]> {156  return safe(157    () =>158      run<YearLiterature>(sql`159        SELECT c.id, c.slug, c.canonical_name, l.count, p.count AS prev_count, l.query, l.updated_at AS computed_at160        FROM literature_counts l JOIN cancers c ON c.id = l.cancer_id161        LEFT JOIN literature_counts p ON p.cancer_id = l.cancer_id AND p.window_key = ${`y${year - 1}`}162        WHERE l.window_key = ${`y${year}`} AND c.top_level AND c.status = 'active'163        ORDER BY l.count DESC LIMIT ${limit}`),164    [] as YearLiterature[],165  );166}167168export interface YearEpi {169  source_slug: string;170  source_name: string;171  metric: string;172  geography_name: string;173  geography_slug: string;174  n: number;175  cancers: number;176}177/** Epidemiology observations whose reference year is this year (what registries published for it). */178export async function yearEpidemiology(year: number): Promise<YearEpi[]> {179  return safe(180    () =>181      run<YearEpi>(sql`182        SELECT s.slug AS source_slug, s.name AS source_name, o.metric, g.name AS geography_name, g.slug AS geography_slug, count(*)::int AS n, count(DISTINCT o.cancer_id)::int AS cancers183        FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id JOIN geographies g ON g.id = o.geography_id184        WHERE o.year = ${year} GROUP BY s.slug, s.name, o.metric, g.name, g.slug ORDER BY g.name, o.metric, s.slug`),185    [] as YearEpi[],186  );187}188189/** Years for which at least one dated fact exists (approvals, trials, literature or observations). */190export async function yearsWithData(): Promise<number[]> {191  const rows = await safe(192    () =>193      run<{ y: number }>(sql`194        SELECT DISTINCT y FROM (195          SELECT substr(approval_date, 1, 4)::int AS y FROM drug_approvals WHERE approval_date ~ '^\\d{4}'196          UNION SELECT substr(first_posted_date, 1, 4)::int FROM clinical_trials WHERE first_posted_date ~ '^\\d{4}'197          UNION SELECT year FROM epidemiology_observations198          UNION SELECT substr(window_key, 2)::int FROM literature_counts WHERE window_key ~ '^y\\d{4}$'199        ) x WHERE y >= ${YEAR_MIN} AND y <= extract(year FROM now())::int ORDER BY y DESC`),200    [] as Array<{ y: number }>,201  );202  return rows.map((r) => Number(r.y));203}204