spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import { STOP_REASON_CATEGORIES, classifyStopReason, type StopReasonCategory } from '@cancerindex/ranking';45/** One `trial_intelligence` row joined with its cancer (snake_case as returned by the driver). */6export interface TrialIntelRow {7  cancer_id: string;8  cancer_slug: string;9  cancer_name: string;10  top_level: boolean;11  entity_level: 'top' | 'all';12  total_trials: number;13  active_trials: number;14  recruiting_trials: number;15  phase1_active: number;16  phase2_active: number;17  phase3_active: number;18  phase3_recruiting: number;19  phase4_active: number;20  completed_trials: number;21  terminated_trials: number;22  withdrawn_trials: number;23  suspended_trials: number;24  with_results: number;25  new_trials_12m: number;26  new_trials_prior_12m: number;27  trial_growth_yoy: number | null;28  avg_enrollment: number | null;29  median_enrollment: number | null;30  total_enrollment_active: number | null;31  distinct_sponsors: number;32  industry_share: number | null;33  sponsor_hhi: number | null;34  top_sponsor: string | null;35  top_sponsor_share: number | null;36  distinct_countries: number;37  us_share: number | null;38  top_country: string | null;39  top_country_share: number | null;40  country_hhi: number | null;41  termination_share: number | null;42  why_stopped_breakdown: Record<string, number>;43  trials_per_1000_deaths: number | null;44  trials_per_100k_cases: number | null;45  burden_geography: string | null;46  burden_year: number | null;47  burden_source_id: string | null;48  burden_source_slug: string | null;49  formula_version: string;50  inputs: Record<string, unknown>;51  computed_at: Date | string;52}5354const COLS = sql`ti.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, c.top_level, ti.entity_level,55  ti.total_trials, ti.active_trials, ti.recruiting_trials, ti.phase1_active, ti.phase2_active, ti.phase3_active, ti.phase3_recruiting, ti.phase4_active,56  ti.completed_trials, ti.terminated_trials, ti.withdrawn_trials, ti.suspended_trials, ti.with_results,57  ti.new_trials_12m, ti.new_trials_prior_12m, ti.trial_growth_yoy, ti.avg_enrollment, ti.median_enrollment, ti.total_enrollment_active,58  ti.distinct_sponsors, ti.industry_share, ti.sponsor_hhi, ti.top_sponsor, ti.top_sponsor_share,59  ti.distinct_countries, ti.us_share, ti.top_country, ti.top_country_share, ti.country_hhi,60  ti.termination_share, ti.why_stopped_breakdown, ti.trials_per_1000_deaths, ti.trials_per_100k_cases, ti.burden_geography, ti.burden_year, ti.burden_source_id,61  s.slug AS burden_source_slug, ti.formula_version, ti.inputs, ti.updated_at AS computed_at`;62const FROM = sql`FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id LEFT JOIN sources s ON s.id = ti.burden_source_id`;6364/** Rows for one entity level, most active first (`limit` caps the "all" level to the most active entities). */65export async function listTrialIntelligence(level: 'top' | 'all', limit = 10_000): Promise<TrialIntelRow[]> {66  return safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.entity_level = ${level} AND c.status = 'active' ORDER BY ti.active_trials DESC, c.canonical_name LIMIT ${limit}`), [] as TrialIntelRow[]);67}6869/** Both levels for one cancer (a top-level cancer has two rows with identical figures). */70export async function trialIntelligenceFor(cancerId: string): Promise<{ all: TrialIntelRow | null; top: TrialIntelRow | null; primary: TrialIntelRow | null }> {71  const rows = await safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.cancer_id = ${cancerId}`), [] as TrialIntelRow[]);72  const all = rows.find((r) => r.entity_level === 'all') ?? null;73  const top = rows.find((r) => r.entity_level === 'top') ?? null;74  return { all, top, primary: all ?? top };75}7677/** Home module: top-level cancers with the most recruiting Phase III studies. */78export async function topPhase3Recruiting(limit = 8): Promise<TrialIntelRow[]> {79  return safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.entity_level = 'top' AND c.status = 'active' AND ti.phase3_recruiting > 0 ORDER BY ti.phase3_recruiting DESC, ti.recruiting_trials DESC, c.canonical_name LIMIT ${limit}`), [] as TrialIntelRow[]);80}8182export const STOPPED_STATUSES = ['TERMINATED', 'WITHDRAWN', 'SUSPENDED'] as const;8384export interface TerminatedFilters {85  cancerIds: string[] | null;86  reason: StopReasonCategory | '';87  status: string;88  since: number | null;89  page: number;90  pageSize: number;91}9293export interface TerminatedRow {94  id: string;95  nct_id: string;96  brief_title: string;97  acronym: string | null;98  study_type: string | null;99  phases: string[];100  overall_status: string | null;101  why_stopped: string | null;102  first_posted_date: string | null;103  last_update_posted_date: string | null;104  enrollment_count: number | null;105  lead_sponsor: string | null;106  lead_sponsor_class: string | null;107  updated_at: Date | string;108  reason_category: StopReasonCategory;109  reason_matches: string[];110}111112function stoppedWhere(f: TerminatedFilters) {113  const parts = [sql`t.overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED')`];114  if (f.status && (STOPPED_STATUSES as readonly string[]).includes(f.status)) parts.push(sql`t.overall_status = ${f.status}`);115  if (f.since) parts.push(sql`t.first_posted_date >= ${`${f.since}-01-01`}`);116  if (f.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id = ANY(${sql.param(f.cancerIds)}::text[]))`);117  return sql.join(parts, sql` AND `);118}119120/**121 * Terminated / withdrawn / suspended studies with their registrant-reported reason classified by122 * keyword rules. The category is not stored, so a light pass classifies the whole filtered set (id +123 * text) to build the breakdown and apply the reason filter; full columns are fetched for the page only.124 */125export async function listTerminated(f: TerminatedFilters): Promise<{ rows: TerminatedRow[]; total: number; stopped: number; breakdown: Record<StopReasonCategory, number> }> {126  const breakdown = Object.fromEntries(STOP_REASON_CATEGORIES.map((c) => [c, 0])) as Record<StopReasonCategory, number>;127  if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0, stopped: 0, breakdown };128  const light = await safe(() => run<{ id: string; why_stopped: string | null }>(sql`SELECT t.id, t.why_stopped FROM clinical_trials t WHERE ${stoppedWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id`), [] as Array<{ id: string; why_stopped: string | null }>);129  const classified = light.map((r) => {130    const c = classifyStopReason(r.why_stopped);131    breakdown[c.category] += 1;132    return { id: r.id, category: c.category, matched: c.matched };133  });134  const filtered = f.reason ? classified.filter((r) => r.category === f.reason) : classified;135  const offset = (Math.max(1, f.page) - 1) * f.pageSize;136  const page = filtered.slice(offset, offset + f.pageSize);137  if (page.length === 0) return { rows: [], total: filtered.length, stopped: light.length, breakdown };138  const full = await safe(139    () =>140      run<Omit<TerminatedRow, 'reason_category' | 'reason_matches'>>(sql`141        SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.why_stopped, t.first_posted_date, t.last_update_posted_date, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.updated_at142        FROM clinical_trials t WHERE t.id = ANY(${sql.param(page.map((p) => p.id))}::text[])`),143    [] as Array<Omit<TerminatedRow, 'reason_category' | 'reason_matches'>>,144  );145  const order = new Map(page.map((p, i) => [p.id, i]));146  const byId = new Map(page.map((p) => [p.id, p]));147  const rows = full148    .sort((a, b) => (order.get(a.id) ?? 0) - (order.get(b.id) ?? 0))149    .map((r) => {150      const c = byId.get(r.id)!;151      return { ...r, reason_category: c.category, reason_matches: c.matched } satisfies TerminatedRow;152    });153  return { rows, total: filtered.length, stopped: light.length, breakdown };154}155156/** Distinct first-posted years among stopped studies (for the year filter). */157export async function stoppedYears(): Promise<number[]> {158  const rows = await safe(() => run<{ y: number }>(sql`SELECT DISTINCT left(first_posted_date, 4)::int AS y FROM clinical_trials WHERE overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED') AND first_posted_date ~ '^\\d{4}' ORDER BY 1 DESC`), [] as Array<{ y: number }>);159  return rows.map((r) => Number(r.y)).filter((y) => Number.isFinite(y));160}161