spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface DrugRow {5  id: string;6  slug: string;7  name: string;8  kind: string | null;9  ncit_code: string | null;10  chembl_id: string | null;11  civic_therapy_id: number | null;12  drugbank_id: string | null;13  pubchem_cid: string | null;14  unii: string | null;15  mechanism: string | null;16  target_gene_ids: string[];17  development_status: string | null;18  description: string | null;19  updated_at: Date;20  approval_count?: number;21  evidence_count?: number;22  trial_count?: number;23  aliases?: string[] | null;24}2526export async function getDrugBySlug(slug: string): Promise<DrugRow | null> {27  const rows = await safe(28    () =>29      run<DrugRow>(sql`30        SELECT d.*, (SELECT count(*) FROM drug_approvals a WHERE a.drug_id = d.id)::int AS approval_count,31               (SELECT count(*) FROM civic_evidence_items e WHERE d.id = ANY(e.therapy_ids))::int AS evidence_count,32               (SELECT count(DISTINCT ti.trial_id) FROM trial_interventions ti WHERE ti.drug_id = d.id)::int AS trial_count,33               (SELECT array_agg(DISTINCT a.alias ORDER BY a.alias) FROM drug_aliases a WHERE a.drug_id = d.id) AS aliases34        FROM drugs d WHERE d.slug = ${slug} LIMIT 1`),35    [] as DrugRow[],36  );37  return rows[0] ?? null;38}3940export async function listDrugs(opts: { q: string; kind: string; page: number; pageSize: number }): Promise<{ rows: DrugRow[]; total: number; kinds: Array<{ kind: string; n: number }> }> {41  const where = sql`${opts.kind ? sql`d.kind = ${opts.kind}` : sql`true`} AND ${opts.q ? sql`(d.name ILIKE ${'%' + opts.q + '%'} OR EXISTS (SELECT 1 FROM drug_aliases a WHERE a.drug_id = d.id AND a.alias ILIKE ${'%' + opts.q + '%'}))` : sql`true`}`;42  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drugs d WHERE ${where}`), [{ n: '0' }]);43  const rows = await safe(44    () =>45      run<DrugRow>(sql`46        SELECT d.*, (SELECT count(*) FROM drug_approvals a WHERE a.drug_id = d.id)::int AS approval_count,47               (SELECT count(*) FROM civic_evidence_items e WHERE d.id = ANY(e.therapy_ids))::int AS evidence_count,48               (SELECT count(DISTINCT ti.trial_id) FROM trial_interventions ti WHERE ti.drug_id = d.id)::int AS trial_count49        FROM drugs d WHERE ${where} ORDER BY approval_count DESC, evidence_count DESC, d.name LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),50    [] as DrugRow[],51  );52  const kinds = await safe(() => run<{ kind: string; n: string }>(sql`SELECT kind, count(*) AS n FROM drugs WHERE kind IS NOT NULL GROUP BY kind ORDER BY n DESC`), [] as Array<{ kind: string; n: string }>);53  return { rows, total: Number(total[0]?.n ?? 0), kinds: kinds.map((k) => ({ kind: k.kind, n: Number(k.n) })) };54}5556export interface ApprovalRow {57  id: number;58  drug_id: string;59  drug_slug: string;60  drug_name: string;61  cancer_id: string | null;62  cancer_slug: string | null;63  cancer_name: string | null;64  biomarker_ids: string[];65  tumor_agnostic: boolean;66  jurisdiction: string;67  authority: string;68  indication: string;69  line_of_therapy: string | null;70  disease_stage: string | null;71  approval_type: string | null;72  accelerated: boolean | null;73  conditional: boolean | null;74  approval_date: string | null;75  withdrawal_date: string | null;76  status: string;77  application_number: string | null;78  source_slug: string;79  source_name: string;80  provenance_id: number;81  updated_at: Date;82  /** Verbatim upstream status when the source publishes one (Health Canada DPD: "Cancelled Post Market", "Dormant"…). */83  source_status?: string | null;84}8586const APPROVAL_SELECT = sql`87  SELECT a.*, a.raw->>'dpdStatus' AS source_status, 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_slug, s.name AS source_name88  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_id`;8990export async function approvalsForDrug(drugId: string): Promise<ApprovalRow[]> {91  return safe(() => run<ApprovalRow>(sql`${APPROVAL_SELECT} WHERE a.drug_id = ${drugId} ORDER BY a.jurisdiction, a.approval_date DESC NULLS LAST`), [] as ApprovalRow[]);92}9394export async function approvalsForCancer(cancerIds: string[]): Promise<ApprovalRow[]> {95  if (cancerIds.length === 0) return [];96  return safe(() => run<ApprovalRow>(sql`${APPROVAL_SELECT} WHERE a.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) OR a.tumor_agnostic ORDER BY d.name, a.jurisdiction, a.approval_date DESC NULLS LAST LIMIT 500`), [] as ApprovalRow[]);97}9899/* ------------------------------------------------------------------------------------------------100 * Identifiers (drug_codes) and development pipeline (drug_pipeline) for the drug page101 * ---------------------------------------------------------------------------------------------- */102103export interface DrugCodeRow {104  id: number;105  system: string;106  code: string;107  label: string | null;108  match_type: string;109  source_slug: string | null;110  source_name: string | null;111}112113/** Display order of identifier systems on the drug page. */114export const CODE_SYSTEM_ORDER = ['atc', 'din', 'hc_drug_code', 'unii', 'rxcui', 'ncit', 'chembl', 'drugbank', 'pubchem_cid', 'civic_therapy', 'ema_product', 'mhra', 'tga'] as const;115116export async function codesForDrug(drugId: string): Promise<DrugCodeRow[]> {117  const order = sql.raw(`CASE k.system ${CODE_SYSTEM_ORDER.map((s, i) => `WHEN '${s}' THEN ${i}`).join(' ')} ELSE 99 END`);118  return safe(119    () =>120      run<DrugCodeRow>(sql`121        SELECT k.id, k.system, k.code, k.label, k.match_type, s.slug AS source_slug, s.name AS source_name122        FROM drug_codes k LEFT JOIN sources s ON s.id = k.source_id WHERE k.drug_id = ${drugId} ORDER BY ${order}, k.code`),123    [] as DrugCodeRow[],124  );125}126127export interface DrugPipelineRow {128  id: number;129  cancer_id: string | null;130  cancer_slug: string | null;131  cancer_name: string | null;132  stage: string;133  max_phase: string | null;134  active_trials: number;135  recruiting_trials: number;136  phase3_trials: number;137  total_trials: number;138  approvals: number;139  jurisdictions: string[];140  first_approval_date: string | null;141  latest_approval_date: string | null;142  first_trial_date: string | null;143  formula_version: string;144  computed_at: Date;145}146147/** Across-all-cancers row first, then one row per top-level cancer (most advanced stage first). */148export async function pipelineForDrug(drugId: string): Promise<DrugPipelineRow[]> {149  return safe(150    () =>151      run<DrugPipelineRow>(sql`152        SELECT p.id, p.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, p.stage, p.max_phase, p.active_trials, p.recruiting_trials, p.phase3_trials, p.total_trials, p.approvals, p.jurisdictions,153          p.first_approval_date, p.latest_approval_date, p.first_trial_date, p.formula_version, p.updated_at AS computed_at154        FROM drug_pipeline p LEFT JOIN cancers c ON c.id = p.cancer_id WHERE p.drug_id = ${drugId}155        ORDER BY (p.cancer_id IS NOT NULL), CASE p.stage WHEN 'approved' THEN 6 WHEN 'phase4' THEN 5 WHEN 'phase3' THEN 4 WHEN 'phase2' THEN 3 WHEN 'phase1' THEN 2 WHEN 'phase_not_stated' THEN 1 ELSE 0 END DESC, p.active_trials DESC, c.canonical_name`),156    [] as DrugPipelineRow[],157  );158}159160export async function drugSlugsForSitemap(offset: number, limit: number): Promise<Array<{ slug: string; updated_at: Date }>> {161  return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM drugs ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);162}163export async function countDrugs(): Promise<number> {164  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drugs`), [{ n: '0' }]);165  return Number(r[0]?.n ?? 0);166}167