spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34/**5 * Country-level research and regulatory activity (SPEC §22 "Clinical trial activity", §13 jurisdiction-aware6 * approvals). Trial sites come from the derived `trial_site_country_counts` (ClinicalTrials.gov locations,7 * matched to the geography by ISO 3166-1 alpha-3); approvals from `drug_approvals` for the jurisdiction code.8 */910export interface CountryTrialTotals {11  sites: number;12  trials: number;13  recruiting_sites: number;14  recruiting_trials: number;15  computed_at: Date | string | null;16}17export async function countryTrialTotals(iso3: string): Promise<CountryTrialTotals | null> {18  const rows = await safe(19    () =>20      run<CountryTrialTotals>(sql`21        SELECT coalesce(max(sites) FILTER (WHERE NOT recruiting_only), 0)::int AS sites, coalesce(max(trials) FILTER (WHERE NOT recruiting_only), 0)::int AS trials,22               coalesce(max(sites) FILTER (WHERE recruiting_only), 0)::int AS recruiting_sites, coalesce(max(trials) FILTER (WHERE recruiting_only), 0)::int AS recruiting_trials,23               max(updated_at) AS computed_at24        FROM trial_site_country_counts WHERE iso3 = ${iso3} AND cancer_id IS NULL AND phase IS NULL`),25    [] as CountryTrialTotals[],26  );27  const r = rows[0];28  return r && r.sites > 0 ? r : null;29}3031export interface CountryCancerSites {32  cancer_id: string;33  slug: string;34  canonical_name: string;35  sites: number;36  trials: number;37  recruiting_sites: number;38  recruiting_trials: number;39}40/** Sites and studies per top-level cancer in this country (any phase). */41export async function countrySitesByCancer(iso3: string, limit = 40): Promise<CountryCancerSites[]> {42  return safe(43    () =>44      run<CountryCancerSites>(sql`45        SELECT c.id AS cancer_id, c.slug, c.canonical_name,46               coalesce(max(t.sites) FILTER (WHERE NOT t.recruiting_only), 0)::int AS sites, coalesce(max(t.trials) FILTER (WHERE NOT t.recruiting_only), 0)::int AS trials,47               coalesce(max(t.sites) FILTER (WHERE t.recruiting_only), 0)::int AS recruiting_sites, coalesce(max(t.trials) FILTER (WHERE t.recruiting_only), 0)::int AS recruiting_trials48        FROM trial_site_country_counts t JOIN cancers c ON c.id = t.cancer_id49        WHERE t.iso3 = ${iso3} AND t.phase IS NULL50        GROUP BY c.id, c.slug, c.canonical_name ORDER BY sites DESC, c.canonical_name LIMIT ${limit}`),51    [] as CountryCancerSites[],52  );53}5455export interface CountryPhaseSites {56  phase: string;57  sites: number;58  trials: number;59  recruiting_trials: number;60}61export async function countrySitesByPhase(iso3: string): Promise<CountryPhaseSites[]> {62  return safe(63    () =>64      run<CountryPhaseSites>(sql`65        SELECT phase, coalesce(max(sites) FILTER (WHERE NOT recruiting_only), 0)::int AS sites, coalesce(max(trials) FILTER (WHERE NOT recruiting_only), 0)::int AS trials,66               coalesce(max(trials) FILTER (WHERE recruiting_only), 0)::int AS recruiting_trials67        FROM trial_site_country_counts WHERE iso3 = ${iso3} AND cancer_id IS NULL AND phase IS NOT NULL68        GROUP BY phase ORDER BY phase`),69    [] as CountryPhaseSites[],70  );71}7273export interface JurisdictionApprovalSummary {74  authority: string;75  status: string;76  n: number;77  drugs: number;78  latest: string | null;79}80export async function jurisdictionApprovalSummary(jurisdiction: string): Promise<JurisdictionApprovalSummary[]> {81  return safe(82    () =>83      run<JurisdictionApprovalSummary>(sql`84        SELECT authority, status, count(*)::int AS n, count(DISTINCT drug_id)::int AS drugs, max(approval_date) FILTER (WHERE approval_date ~ '^\\d{4}-\\d{2}-\\d{2}$') AS latest85        FROM drug_approvals WHERE jurisdiction = ${jurisdiction} GROUP BY authority, status ORDER BY authority, n DESC`),86    [] as JurisdictionApprovalSummary[],87  );88}8990export interface JurisdictionApproval {91  id: number;92  approval_date: string | null;93  authority: string;94  status: string;95  indication: string;96  drug_slug: string;97  drug_name: string;98  cancer_slug: string | null;99  cancer_name: string | null;100  source_slug: string;101}102export async function latestJurisdictionApprovals(jurisdiction: string, limit = 12): Promise<JurisdictionApproval[]> {103  return safe(104    () =>105      run<JurisdictionApproval>(sql`106        SELECT a.id, a.approval_date, a.authority, a.status, a.indication, 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_slug107        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_id108        WHERE a.jurisdiction = ${jurisdiction} AND a.status IN ('approved','accelerated','conditional')109        ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${limit}`),110    [] as JurisdictionApproval[],111  );112}113