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}8384const APPROVAL_SELECT = sql`85  SELECT a.*, 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_name86  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`;8788export async function approvalsForDrug(drugId: string): Promise<ApprovalRow[]> {89  return safe(() => run<ApprovalRow>(sql`${APPROVAL_SELECT} WHERE a.drug_id = ${drugId} ORDER BY a.jurisdiction, a.approval_date DESC NULLS LAST`), [] as ApprovalRow[]);90}9192export async function approvalsForCancer(cancerIds: string[]): Promise<ApprovalRow[]> {93  if (cancerIds.length === 0) return [];94  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[]);95}9697export async function drugSlugsForSitemap(offset: number, limit: number): Promise<Array<{ slug: string; updated_at: Date }>> {98  return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM drugs ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);99}100export async function countDrugs(): Promise<number> {101  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drugs`), [{ n: '0' }]);102  return Number(r[0]?.n ?? 0);103}104