spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';3import { countryToIso3 } from './country-codes.js';45// The package index (owned by the integrator) already re-exports this module; surface the pure6// country mapping through it so apps import `countryToIso3` / `iso3ToName` from '@cancerindex/ranking'.7export * from './country-codes.js';89export const TRIAL_SITE_COUNTRY_FORMULA_VERSION = 'ci-trial-sites-v1';1011/** Phase buckets exposed by the map; EARLY_PHASE1 is folded into PHASE1, NA and empty phases only count under "any phase". */12export const TRIAL_SITE_PHASES = ['PHASE1', 'PHASE2', 'PHASE3', 'PHASE4'] as const;13export type TrialSitePhase = (typeof TRIAL_SITE_PHASES)[number];1415export interface TrialSiteCountsResult {16  /** Rows written to trial_site_country_counts. */17  rows: number;18  /** Distinct non-empty country names seen in trial_locations. */19  countries: number;20  /** Country names that could not be mapped to ISO 3166-1 alpha-3 (kept with iso3 = null). */21  unmapped: string[];22  /** Top-level cancers aggregated (in addition to the all-oncology scope). */23  cancers: number;24  ms: number;25}2627/**28 * Recompute `trial_site_country_counts` (SPEC §11 — trial map), formula `ci-trial-sites-v1`.29 *30 * One row per (cancer_id, phase, recruiting_only, country) for the cartesian product31 *   cancer_id      ∈ {NULL = every oncology trial} ∪ {each active `top_level` cancer, trials attributed32 *                    through `trial_conditions` to the cancer or any NCIt descendant (depth ≤ 12,33 *                    same traversal as counters.ts)}34 *   phase          ∈ {NULL = any phase, PHASE1, PHASE2, PHASE3, PHASE4} — a PHASE2|PHASE3 study is35 *                    counted under both; EARLY_PHASE1 counts under PHASE1; NA / no phase → any only36 *   recruiting_only∈ {false = every location, true = location status RECRUITING, or, when the37 *                    location has no status, study overall_status RECRUITING}38 *39 *   sites  = number of `trial_locations` rows (a study with 40 US sites weighs 40)40 *   trials = distinct studies with ≥ 1 site in the country41 *42 * Interventional and observational studies are both included (no study_type filter). Locations43 * with an empty country are excluded. Deterministic, set-based, one transaction (delete + insert).44 */45export async function computeTrialSiteCounts(db: Database): Promise<TrialSiteCountsResult> {46  const t0 = Date.now();47  return db.transaction(async (tx) => {48    // 1. Locations joined to their study, with the effective "recruiting" flag and phase array.49    await tx.execute(sql`50      CREATE TEMP TABLE _tl ON COMMIT DROP AS51      SELECT l.trial_id, l.country, (coalesce(l.status, t.overall_status) = 'RECRUITING') AS recruiting, t.phases52      FROM trial_locations l JOIN clinical_trials t ON t.id = l.trial_id53      WHERE l.country IS NOT NULL AND l.country <> ''`);5455    // 2. Country → ISO3 through the pure mapping (JS side; ~200 names).56    const names = await tx.execute<{ country: string }>(sql`SELECT DISTINCT country FROM _tl ORDER BY country`);57    const mapping = Array.from(names).map((r) => ({ country: r.country, iso3: countryToIso3(r.country) }));58    const unmapped = mapping.filter((m) => m.iso3 === null).map((m) => m.country);59    await tx.execute(sql`CREATE TEMP TABLE _iso (country text PRIMARY KEY, iso3 text) ON COMMIT DROP`);60    const mapped = mapping.filter((m) => m.iso3 !== null);61    for (let i = 0; i < mapped.length; i += 100) {62      const chunk = mapped.slice(i, i + 100);63      await tx.execute(sql`INSERT INTO _iso (country, iso3) VALUES ${sql.join(chunk.map((m) => sql`(${m.country}, ${m.iso3})`), sql`, `)}`);64    }6566    // 3. Location × phase bucket (NULL = any phase, plus each normalized phase present on the study).67    const phaseList = sql.raw(`(${['EARLY_PHASE1', ...TRIAL_SITE_PHASES].map((p) => `'${p}'`).join(',')})`);68    await tx.execute(sql`69      CREATE TEMP TABLE _tlp ON COMMIT DROP AS70      SELECT trial_id, country, recruiting, NULL::text AS phase FROM _tl71      UNION ALL72      SELECT l.trial_id, l.country, l.recruiting, p.phase73      FROM _tl l CROSS JOIN LATERAL (74        SELECT DISTINCT CASE WHEN x = 'EARLY_PHASE1' THEN 'PHASE1' ELSE x END AS phase75        FROM unnest(l.phases) x WHERE x IN ${phaseList}76      ) p`);77    await tx.execute(sql`CREATE INDEX ON _tlp (trial_id)`);7879    // 4. Trial × cancer scope: NULL (all oncology trials) + every active top-level cancer whose80    //    descendant set (NCIt hierarchy, depth ≤ 12) contains a mapped condition of the trial.81    await tx.execute(sql`82      CREATE TEMP TABLE _desc ON COMMIT DROP AS83      WITH RECURSIVE d AS (84        SELECT id AS ancestor, id AS descendant, 0 AS depth FROM cancers WHERE status = 'active' AND top_level85        UNION86        SELECT d.ancestor, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.descendant87        WHERE d.depth < 1288      )89      SELECT DISTINCT ancestor, descendant FROM d`);90    await tx.execute(sql`CREATE INDEX ON _desc (descendant)`);91    await tx.execute(sql`92      CREATE TEMP TABLE _tc ON COMMIT DROP AS93      SELECT NULL::varchar(32) AS cancer_id, t.id AS trial_id FROM clinical_trials t94      UNION ALL95      SELECT DISTINCT d.ancestor, tc.trial_id FROM trial_conditions tc JOIN _desc d ON d.descendant = tc.cancer_id WHERE tc.cancer_id IS NOT NULL`);96    await tx.execute(sql`CREATE INDEX ON _tc (trial_id)`);9798    // 5. Rebuild.99    await tx.execute(sql`DELETE FROM trial_site_country_counts`);100    const inserted = await tx.execute<{ n: string }>(sql`101      WITH ins AS (102        -- Schema field computedAt is declared with the shared updatedAt() helper, hence column updated_at.103        INSERT INTO trial_site_country_counts (cancer_id, phase, recruiting_only, country, iso3, sites, trials, formula_version, updated_at)104        SELECT c.cancer_id, p.phase, r.recruiting_only, p.country, i.iso3, count(*)::int, count(DISTINCT p.trial_id)::int, ${TRIAL_SITE_COUNTRY_FORMULA_VERSION}, now()105        FROM _tlp p106        JOIN _tc c ON c.trial_id = p.trial_id107        CROSS JOIN (VALUES (false), (true)) AS r(recruiting_only)108        LEFT JOIN _iso i ON i.country = p.country109        WHERE NOT r.recruiting_only OR p.recruiting110        GROUP BY c.cancer_id, p.phase, r.recruiting_only, p.country, i.iso3111        RETURNING 1112      )113      SELECT count(*)::text AS n FROM ins`);114    const cancers = await tx.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM cancers WHERE status = 'active' AND top_level`);115    return { rows: Number(inserted[0]?.n ?? 0), countries: mapping.length, unmapped, cancers: Number(cancers[0]?.n ?? 0), ms: Date.now() - t0 };116  });117}118