spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface SiteCounts {5  cancers: number; // active malignant entities6  subtypes: number; // active entities with entity_type in (subtype, molecular_subtype, histology)7  allEntities: number;8  genes: number;9  variants: number;10  trials: number;11  activeTrials: number; // interventional, active statuses12  recruitingPhase3: number;13  approvedDrugs: number; // distinct drugs with ≥ 1 approval (approved/accelerated/conditional) in any jurisdiction14  approvals: number; // approval records across authorities15  publications: number;16  drugs: number;17  sources: number;18  activeSources: number;19  evidenceItems: number;20  epidemiologyObservations: number;21  lastIngestAt: Date | null;22}2324const EMPTY: SiteCounts = { cancers: 0, subtypes: 0, allEntities: 0, genes: 0, variants: 0, trials: 0, activeTrials: 0, recruitingPhase3: 0, approvedDrugs: 0, approvals: 0, publications: 0, drugs: 0, sources: 0, activeSources: 0, evidenceItems: 0, epidemiologyObservations: 0, lastIngestAt: null };2526/** Live counts for the home ticker. Each label on the page states exactly what is counted (§248-249). */27export async function getSiteCounts(): Promise<SiteCounts> {28  return safe(async () => {29    const [r] = await run<Record<string, string | Date | null>>(sql`30      SELECT31        (SELECT count(*) FROM cancers WHERE status = 'active' AND malignant) AS cancers,32        (SELECT count(*) FROM cancers WHERE status = 'active' AND entity_type IN ('subtype','molecular_subtype','histology')) AS subtypes,33        (SELECT count(*) FROM cancers WHERE status = 'active') AS all_entities,34        (SELECT count(*) FROM genes) AS genes,35        (SELECT count(*) FROM variants) AS variants,36        (SELECT count(*) FROM clinical_trials) AS trials,37        (SELECT count(*) FROM clinical_trials WHERE study_type = 'INTERVENTIONAL' AND overall_status IN ('RECRUITING','NOT_YET_RECRUITING','ENROLLING_BY_INVITATION','ACTIVE_NOT_RECRUITING')) AS active_trials,38        (SELECT count(*) FROM clinical_trials WHERE study_type = 'INTERVENTIONAL' AND overall_status = 'RECRUITING' AND 'PHASE3' = ANY(phases)) AS recruiting_phase3,39        (SELECT count(DISTINCT drug_id) FROM drug_approvals WHERE status IN ('approved','accelerated','conditional')) AS approved_drugs,40        (SELECT count(*) FROM drug_approvals) AS approvals,41        (SELECT count(*) FROM publications) AS publications,42        (SELECT count(*) FROM drugs) AS drugs,43        (SELECT count(*) FROM sources) AS sources,44        (SELECT count(*) FROM sources WHERE status = 'active') AS active_sources,45        (SELECT count(*) FROM civic_evidence_items) AS evidence_items,46        (SELECT count(*) FROM epidemiology_observations) AS epi,47        (SELECT max(finished_at) FROM ingest_runs WHERE status IN ('succeeded','partial')) AS last_ingest48    `);49    if (!r) return EMPTY;50    return {51      cancers: Number(r.cancers),52      subtypes: Number(r.subtypes),53      allEntities: Number(r.all_entities),54      genes: Number(r.genes),55      variants: Number(r.variants),56      trials: Number(r.trials),57      activeTrials: Number(r.active_trials),58      recruitingPhase3: Number(r.recruiting_phase3),59      approvedDrugs: Number(r.approved_drugs),60      approvals: Number(r.approvals),61      publications: Number(r.publications),62      drugs: Number(r.drugs),63      sources: Number(r.sources),64      activeSources: Number(r.active_sources),65      evidenceItems: Number(r.evidence_items),66      epidemiologyObservations: Number(r.epi),67      lastIngestAt: r.last_ingest ? new Date(r.last_ingest as Date) : null,68    };69  }, EMPTY);70}7172/** Deterministic daily pick among sparse-data malignant entities (seeded by UTC date). */73export async function getRareSpotlight(): Promise<{ id: string; slug: string; canonicalName: string; entityType: string; primaryOncotreeCode: string | null; rareCancer: boolean | null; parentName: string | null; parentSlug: string | null } | null> {74  return safe(async () => {75    const cnt = await run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers WHERE status = 'active' AND malignant`);76    const total = Number(cnt[0]?.n ?? 0);77    if (!total) return null;78    // Seed: days since epoch (UTC). Same pick for everyone during one UTC day.79    const day = Math.floor(Date.now() / 86_400_000);80    const offset = ((day * 2654435761) >>> 0) % total; // Knuth multiplicative hash for spread81    const [row] = await run<{ id: string; slug: string; canonical_name: string; entity_type: string; primary_oncotree_code: string | null; rare_cancer: boolean | null; parent_name: string | null; parent_slug: string | null }>(sql`82      SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.primary_oncotree_code, c.rare_cancer,83             p.canonical_name AS parent_name, p.slug AS parent_slug84      FROM cancers c85      LEFT JOIN LATERAL (SELECT pc.canonical_name, pc.slug FROM cancer_hierarchy h JOIN cancers pc ON pc.id = h.parent_id WHERE h.child_id = c.id ORDER BY h.hierarchy_type LIMIT 1) p ON true86      WHERE c.status = 'active' AND c.malignant87      ORDER BY c.id OFFSET ${offset} LIMIT 1`);88    if (!row) return null;89    return { id: row.id, slug: row.slug, canonicalName: row.canonical_name, entityType: row.entity_type, primaryOncotreeCode: row.primary_oncotree_code, rareCancer: row.rare_cancer, parentName: row.parent_name, parentSlug: row.parent_slug };90  }, null);91}92