spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34/**5 * Trial map queries. Country aggregates come from the DERIVED table `trial_site_country_counts`6 * (rebuilt by `pnpm cix intel`, formula ci-trial-sites-v1); the city layer and the distinct-trial7 * headline are read live from `trial_locations` because they are not precomputed.8 */910export const SITE_PHASES = ['PHASE1', 'PHASE2', 'PHASE3', 'PHASE4'] as const;11export type SitePhase = (typeof SITE_PHASES)[number];12export const SITE_METRICS = ['sites', 'trials'] as const;13export type SiteMetric = (typeof SITE_METRICS)[number];1415export interface SiteScope {16  /** Top-level cancer id, or null for every oncology trial. */17  cancerId: string | null;18  phase: SitePhase | null;19  recruitingOnly: boolean;20}2122export interface SiteCountryRow {23  country: string;24  iso3: string | null;25  sites: number;26  trials: number;27  formula_version: string;28  computed_at: Date | string;29}3031/** Country aggregates for one scope, sorted by sites desc (≈ 180 rows at most). */32export async function countryCounts(s: SiteScope): Promise<SiteCountryRow[]> {33  const rows = await safe(34    () =>35      run<SiteCountryRow & { sites: string | number; trials: string | number }>(sql`36        SELECT country, iso3, sites, trials, formula_version, updated_at AS computed_at37        FROM trial_site_country_counts38        WHERE cancer_id IS NOT DISTINCT FROM ${s.cancerId} AND phase IS NOT DISTINCT FROM ${s.phase} AND recruiting_only = ${s.recruitingOnly}39        ORDER BY sites DESC, country`),40    [],41  );42  return rows.map((r) => ({ ...r, sites: Number(r.sites), trials: Number(r.trials) }));43}4445/** True when the derived table has been populated at all (distinguishes "not computed" from "no match"). */46export async function siteCountsAvailable(): Promise<boolean> {47  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM trial_site_country_counts`), [{ n: '0' }]);48  return Number(r[0]?.n ?? 0) > 0;49}5051export interface TopLevelCancerOption {52  id: string;53  slug: string;54  canonical_name: string;55}5657/** Active top-level cancers (the only cancer scopes precomputed for the map). */58export async function listTopLevelCancers(): Promise<TopLevelCancerOption[]> {59  return safe(() => run<TopLevelCancerOption>(sql`SELECT id, slug, canonical_name FROM cancers WHERE status = 'active' AND top_level ORDER BY canonical_name`), []);60}6162export interface LiveScope {63  /** Cancer + descendants (semi-join on trial_conditions), or null for every trial. */64  cancerIds: string[] | null;65  phase: SitePhase | null;66  recruitingOnly: boolean;67}6869/** WHERE fragment shared by the live queries; alias `l` = trial_locations, `t` = clinical_trials (joined only when needed). */70function liveWhere(s: LiveScope): { where: ReturnType<typeof sql>; needsTrial: boolean } {71  const parts = [sql`l.country IS NOT NULL AND l.country <> ''`];72  let needsTrial = false;73  if (s.phase) {74    needsTrial = true;75    parts.push(s.phase === 'PHASE1' ? sql`(t.phases && ARRAY['PHASE1','EARLY_PHASE1']::text[])` : sql`${s.phase} = ANY(t.phases)`);76  }77  if (s.recruitingOnly) {78    needsTrial = true;79    parts.push(sql`coalesce(l.status, t.overall_status) = 'RECRUITING'`);80  }81  if (s.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = l.trial_id AND tc.cancer_id IN (${sql.join(s.cancerIds.map((i) => sql`${i}`), sql`, `)}))`);82  return { where: sql.join(parts, sql` AND `), needsTrial };83}8485/** Distinct studies with ≥ 1 site in a named country for the scope (live; ≈ 100–150 ms on 1.2 M rows). */86export async function distinctTrialCount(s: LiveScope): Promise<number> {87  if (s.cancerIds && s.cancerIds.length === 0) return 0;88  const { where, needsTrial } = liveWhere(s);89  const join = needsTrial ? sql`JOIN clinical_trials t ON t.id = l.trial_id` : sql``;90  const r = await safe(() => run<{ n: string }>(sql`SELECT count(DISTINCT l.trial_id) AS n FROM trial_locations l ${join} WHERE ${where}`), [{ n: '0' }]);91  return Number(r[0]?.n ?? 0);92}9394export interface SiteCityRow {95  country: string;96  city: string;97  state: string | null;98  lat: number;99  lng: number;100  sites: number;101  trials: number;102}103104export const CITY_LIMIT = 300;105106/**107 * City aggregates (registrant-entered city/state, mean of geocoded lat/lng, sites, distinct trials),108 * top `limit` by sites. Live on trial_locations: ≈ 0.3–0.5 s with a cancer or recruiting filter,109 * but ≈ 3 s for the whole registry without any filter — that case returns [] and the caller omits110 * the layer (documented in docs/methodology/trial-map.md).111 */112export async function cityCounts(s: LiveScope, limit = CITY_LIMIT): Promise<SiteCityRow[]> {113  if (s.cancerIds && s.cancerIds.length === 0) return [];114  if (!s.cancerIds && !s.recruitingOnly) return [];115  const { where, needsTrial } = liveWhere(s);116  const join = needsTrial ? sql`JOIN clinical_trials t ON t.id = l.trial_id` : sql``;117  const rows = await safe(118    () =>119      run<{ country: string; city: string; state: string | null; lat: number; lng: number; sites: string; trials: string }>(sql`120        SELECT l.country, l.city, l.state, avg(l.lat)::float8 AS lat, avg(l.lng)::float8 AS lng, count(*) AS sites, count(DISTINCT l.trial_id) AS trials121        FROM trial_locations l ${join}122        WHERE l.lat IS NOT NULL AND l.lng IS NOT NULL AND l.city IS NOT NULL AND ${where}123        GROUP BY l.country, l.city, l.state124        ORDER BY sites DESC, trials DESC, l.country, l.city125        LIMIT ${limit}`),126    [],127  );128  return rows.map((r) => ({ ...r, lat: Number(r.lat), lng: Number(r.lng), sites: Number(r.sites), trials: Number(r.trials) }));129}130