spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import type { SQL } from 'drizzle-orm';4import type { ApprovalRow } from '@/lib/queries/drugs';56export const APPROVALS_PAGE_SIZE = 50;7export const PIPELINE_PAGE_SIZE = 50;8/** Funnel order (least → most advanced); `withdrawn` is shown apart from the funnel. */9export const PIPELINE_FUNNEL = ['phase1', 'phase2', 'phase3', 'phase4', 'approved'] as const;10export const PIPELINE_STAGES = ['phase_not_stated', ...PIPELINE_FUNNEL, 'withdrawn'] as const;11export type PipelineStage = (typeof PIPELINE_STAGES)[number];12export const APPROVED_STATUSES = ['approved', 'accelerated', 'conditional'] as const;13const MAX_DEPTH = 12;1415/** Feed row: an approval record plus the verbatim upstream status when the source has one (DPD). */16export interface FeedApprovalRow extends ApprovalRow {17  source_status: string | null;18}1920export interface ApprovalFilters {21  authority: string;22  jurisdiction: string;23  cancer: string; // slug24  status: string;25  year: string;26  q: string;27}2829const FEED_SELECT = sql`30  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_name31  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`;3233function feedWhere(f: ApprovalFilters): SQL {34  const parts: SQL[] = [sql`true`];35  if (f.authority) parts.push(sql`lower(a.authority) = lower(${f.authority})`);36  if (f.jurisdiction) parts.push(sql`upper(a.jurisdiction) = upper(${f.jurisdiction})`);37  if (f.status) parts.push(sql`a.status = ${f.status}`);38  if (/^\d{4}$/.test(f.year)) parts.push(sql`a.approval_date >= ${`${f.year}-01-01`} AND a.approval_date <= ${`${f.year}-12-31`}`);39  if (f.cancer) {40    parts.push(sql`a.cancer_id IN (41      WITH RECURSIVE dsc AS (42        SELECT id, 0 AS depth FROM cancers WHERE slug = ${f.cancer}43        UNION44        SELECT h.child_id, dsc.depth + 1 FROM dsc JOIN cancer_hierarchy h ON h.parent_id = dsc.id WHERE dsc.depth < ${MAX_DEPTH}45      ) SELECT id FROM dsc)`);46  }47  if (f.q) {48    const like = `%${f.q}%`;49    parts.push(sql`(d.name ILIKE ${like} OR a.indication ILIKE ${like} OR EXISTS (SELECT 1 FROM drug_aliases al WHERE al.drug_id = a.drug_id AND al.alias ILIKE ${like}))`);50  }51  return sql.join(parts, sql` AND `);52}5354export async function countApprovals(f: ApprovalFilters): Promise<number> {55  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id WHERE ${feedWhere(f)}`), [{ n: '0' }]);56  return Number(r[0]?.n ?? 0);57}5859/** Feed page, latest approval date first (undated records last). */60export async function listApprovals(f: ApprovalFilters, page: number, pageSize = APPROVALS_PAGE_SIZE): Promise<FeedApprovalRow[]> {61  return safe(() => run<FeedApprovalRow>(sql`${FEED_SELECT} WHERE ${feedWhere(f)} ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`), [] as FeedApprovalRow[]);62}6364export interface AuthorityStat {65  authority: string;66  jurisdiction: string;67  n: number;68  distinct_drugs: number;69  approved_like: number;70  withdrawn: number;71  last_12m: number;72  latest_date: string | null;73  updated_at: Date | null;74}7576export async function approvalStats(): Promise<{ byAuthority: AuthorityStat[]; total: number; distinctDrugs: number; withCancer: number; last12m: number }> {77  const since = new Date(Date.now() - 365 * 86_400_000).toISOString().slice(0, 10);78  const byAuthority = await safe(79    () =>80      run<AuthorityStat>(sql`81        SELECT a.authority, a.jurisdiction, count(*)::int AS n, count(DISTINCT a.drug_id)::int AS distinct_drugs,82          count(*) FILTER (WHERE a.status IN ('approved','accelerated','conditional'))::int AS approved_like,83          count(*) FILTER (WHERE a.status = 'withdrawn')::int AS withdrawn,84          count(*) FILTER (WHERE a.approval_date >= ${since} AND a.approval_date <= to_char(now(), 'YYYY-MM-DD'))::int AS last_12m,85          max(a.approval_date) FILTER (WHERE a.approval_date <= to_char(now(), 'YYYY-MM-DD')) AS latest_date, max(a.updated_at) AS updated_at86        FROM drug_approvals a GROUP BY a.authority, a.jurisdiction ORDER BY n DESC`),87    [] as AuthorityStat[],88  );89  const totals = await safe(() => run<{ total: string; drugs: string; with_cancer: string }>(sql`SELECT count(*) AS total, count(DISTINCT drug_id) AS drugs, count(cancer_id) AS with_cancer FROM drug_approvals`), [{ total: '0', drugs: '0', with_cancer: '0' }]);90  return { byAuthority, total: Number(totals[0]?.total ?? 0), distinctDrugs: Number(totals[0]?.drugs ?? 0), withCancer: Number(totals[0]?.with_cancer ?? 0), last12m: byAuthority.reduce((s, a) => s + a.last_12m, 0) };91}9293export async function approvalFacets(): Promise<{ authorities: Array<{ authority: string; jurisdiction: string; n: number }>; statuses: Array<{ status: string; n: number }>; years: Array<{ year: string; n: number }> }> {94  const [authorities, statuses, years] = await Promise.all([95    safe(() => run<{ authority: string; jurisdiction: string; n: number }>(sql`SELECT authority, jurisdiction, count(*)::int AS n FROM drug_approvals GROUP BY 1, 2 ORDER BY n DESC`), []),96    safe(() => run<{ status: string; n: number }>(sql`SELECT status, count(*)::int AS n FROM drug_approvals GROUP BY 1 ORDER BY n DESC`), []),97    safe(() => run<{ year: string; n: number }>(sql`SELECT left(approval_date, 4) AS year, count(*)::int AS n FROM drug_approvals WHERE approval_date ~ '^\\d{4}' GROUP BY 1 ORDER BY 1 DESC`), []),98  ]);99  return { authorities, statuses, years };100}101102/** Last dated approvals across authorities (no future-dated records). */103export async function recentApprovals(limit = 8): Promise<FeedApprovalRow[]> {104  return safe(() => run<FeedApprovalRow>(sql`${FEED_SELECT} WHERE a.approval_date IS NOT NULL AND a.approval_date <= to_char(now(), 'YYYY-MM-DD') ORDER BY a.approval_date DESC, a.id DESC LIMIT ${limit}`), [] as FeedApprovalRow[]);105}106107/* ------------------------------------------------------------------------------------------------108 * Pipeline (derived, drug_pipeline)109 * ---------------------------------------------------------------------------------------------- */110111export interface PipelineRow {112  id: number;113  drug_id: string;114  drug_slug: string;115  drug_name: string;116  drug_kind: string | null;117  cancer_id: string | null;118  cancer_slug: string | null;119  cancer_name: string | null;120  stage: PipelineStage;121  max_phase: string | null;122  active_trials: number;123  recruiting_trials: number;124  phase3_trials: number;125  total_trials: number;126  approvals: number;127  jurisdictions: string[];128  first_approval_date: string | null;129  latest_approval_date: string | null;130  first_trial_date: string | null;131  formula_version: string;132  computed_at: Date;133}134135const PIPELINE_SELECT = sql`136  SELECT p.*, p.updated_at AS computed_at, d.slug AS drug_slug, d.name AS drug_name, d.kind AS drug_kind, c.slug AS cancer_slug, c.canonical_name AS cancer_name137  FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id`;138const STAGE_RANK = sql`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`;139const scopeWhere = (cancerId: string | null) => (cancerId ? sql`p.cancer_id = ${cancerId}` : sql`p.cancer_id IS NULL`);140141export async function topLevelCancerOptions(): Promise<Array<{ id: string; slug: string; canonical_name: string; drugs: number }>> {142  return safe(143    () =>144      run<{ id: string; slug: string; canonical_name: string; drugs: number }>(sql`145        SELECT c.id, c.slug, c.canonical_name, (SELECT count(*) FROM drug_pipeline p WHERE p.cancer_id = c.id)::int AS drugs146        FROM cancers c WHERE c.top_level AND c.status = 'active' ORDER BY c.canonical_name`),147    [],148  );149}150151export async function pipelineSummary(cancerId: string | null): Promise<{ stages: Array<{ stage: PipelineStage; drugs: number; active_trials: number }>; drugs: number; formulaVersion: string | null; computedAt: Date | null }> {152  const rows = await safe(() => run<{ stage: PipelineStage; drugs: number; active_trials: number }>(sql`SELECT p.stage, count(*)::int AS drugs, coalesce(sum(p.active_trials), 0)::int AS active_trials FROM drug_pipeline p WHERE ${scopeWhere(cancerId)} GROUP BY p.stage`), []);153  const meta = await safe(() => run<{ formula_version: string | null; computed_at: Date | null }>(sql`SELECT max(formula_version) AS formula_version, max(updated_at) AS computed_at FROM drug_pipeline`), [{ formula_version: null, computed_at: null }]);154  const by = new Map(rows.map((r) => [r.stage, r]));155  return {156    stages: PIPELINE_STAGES.map((stage) => ({ stage, drugs: by.get(stage)?.drugs ?? 0, active_trials: by.get(stage)?.active_trials ?? 0 })),157    drugs: rows.reduce((s, r) => s + r.drugs, 0),158    formulaVersion: meta[0]?.formula_version ?? null,159    computedAt: meta[0]?.computed_at ?? null,160  };161}162163/** Up to `perStage` representative drugs per stage (most active trials first). */164export async function pipelineTopDrugsPerStage(cancerId: string | null, perStage = 5): Promise<Map<PipelineStage, PipelineRow[]>> {165  const rows = await safe(166    () =>167      run<PipelineRow>(sql`168        SELECT * FROM (SELECT p.*, p.updated_at AS computed_at, d.slug AS drug_slug, d.name AS drug_name, d.kind AS drug_kind, c.slug AS cancer_slug, c.canonical_name AS cancer_name,169          row_number() OVER (PARTITION BY p.stage ORDER BY p.active_trials DESC, p.total_trials DESC, d.name) AS rn170          FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id WHERE ${scopeWhere(cancerId)}) x171        WHERE rn <= ${perStage} ORDER BY stage, rn`),172    [] as PipelineRow[],173  );174  const out = new Map<PipelineStage, PipelineRow[]>();175  for (const r of rows) out.set(r.stage, [...(out.get(r.stage) ?? []), r]);176  return out;177}178179export async function countPipelineRows(cancerId: string | null, stage: string): Promise<number> {180  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drug_pipeline p WHERE ${scopeWhere(cancerId)} ${stage ? sql`AND p.stage = ${stage}` : sql``}`), [{ n: '0' }]);181  return Number(r[0]?.n ?? 0);182}183184export async function listPipelineRows(cancerId: string | null, stage: string, page: number, pageSize = PIPELINE_PAGE_SIZE): Promise<PipelineRow[]> {185  return safe(186    () => run<PipelineRow>(sql`${PIPELINE_SELECT} WHERE ${scopeWhere(cancerId)} ${stage ? sql`AND p.stage = ${stage}` : sql``} ORDER BY ${STAGE_RANK} DESC, p.active_trials DESC, p.total_trials DESC, d.name LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`),187    [] as PipelineRow[],188  );189}190191/** Sources feeding the derived pipeline (registry + regulatory sources present in drug_approvals). */192export async function pipelineSourceSlugs(): Promise<string[]> {193  const rows = await safe(() => run<{ slug: string }>(sql`SELECT DISTINCT s.slug FROM sources s WHERE s.slug = 'clinicaltrials' OR s.id IN (SELECT DISTINCT source_id FROM drug_approvals) ORDER BY 1`), [] as Array<{ slug: string }>);194  return rows.map((r) => r.slug);195}196