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  publications: number;12  drugs: number;13  sources: number;14  activeSources: number;15  evidenceItems: number;16  epidemiologyObservations: number;17  lastIngestAt: Date | null;18}1920const EMPTY: SiteCounts = { cancers: 0, subtypes: 0, allEntities: 0, genes: 0, variants: 0, trials: 0, publications: 0, drugs: 0, sources: 0, activeSources: 0, evidenceItems: 0, epidemiologyObservations: 0, lastIngestAt: null };2122/** Live counts for the home ticker. Each label on the page states exactly what is counted (§248-249). */23export async function getSiteCounts(): Promise<SiteCounts> {24  return safe(async () => {25    const [r] = await run<Record<string, string | Date | null>>(sql`26      SELECT27        (SELECT count(*) FROM cancers WHERE status = 'active' AND malignant) AS cancers,28        (SELECT count(*) FROM cancers WHERE status = 'active' AND entity_type IN ('subtype','molecular_subtype','histology')) AS subtypes,29        (SELECT count(*) FROM cancers WHERE status = 'active') AS all_entities,30        (SELECT count(*) FROM genes) AS genes,31        (SELECT count(*) FROM variants) AS variants,32        (SELECT count(*) FROM clinical_trials) AS trials,33        (SELECT count(*) FROM publications) AS publications,34        (SELECT count(*) FROM drugs) AS drugs,35        (SELECT count(*) FROM sources) AS sources,36        (SELECT count(*) FROM sources WHERE status = 'active') AS active_sources,37        (SELECT count(*) FROM civic_evidence_items) AS evidence_items,38        (SELECT count(*) FROM epidemiology_observations) AS epi,39        (SELECT max(finished_at) FROM ingest_runs WHERE status IN ('succeeded','partial')) AS last_ingest40    `);41    if (!r) return EMPTY;42    return {43      cancers: Number(r.cancers),44      subtypes: Number(r.subtypes),45      allEntities: Number(r.all_entities),46      genes: Number(r.genes),47      variants: Number(r.variants),48      trials: Number(r.trials),49      publications: Number(r.publications),50      drugs: Number(r.drugs),51      sources: Number(r.sources),52      activeSources: Number(r.active_sources),53      evidenceItems: Number(r.evidence_items),54      epidemiologyObservations: Number(r.epi),55      lastIngestAt: r.last_ingest ? new Date(r.last_ingest as Date) : null,56    };57  }, EMPTY);58}5960/** Deterministic daily pick among sparse-data malignant entities (seeded by UTC date). */61export 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> {62  return safe(async () => {63    const cnt = await run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers WHERE status = 'active' AND malignant`);64    const total = Number(cnt[0]?.n ?? 0);65    if (!total) return null;66    // Seed: days since epoch (UTC). Same pick for everyone during one UTC day.67    const day = Math.floor(Date.now() / 86_400_000);68    const offset = ((day * 2654435761) >>> 0) % total; // Knuth multiplicative hash for spread69    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`70      SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.primary_oncotree_code, c.rare_cancer,71             p.canonical_name AS parent_name, p.slug AS parent_slug72      FROM cancers c73      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 true74      WHERE c.status = 'active' AND c.malignant75      ORDER BY c.id OFFSET ${offset} LIMIT 1`);76    if (!row) return null;77    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 };78  }, null);79}80