import 'server-only';
import { run, sql, safe } from '@/lib/db';

export interface SiteCounts {
  cancers: number; // active malignant entities
  subtypes: number; // active entities with entity_type in (subtype, molecular_subtype, histology)
  allEntities: number;
  genes: number;
  variants: number;
  trials: number;
  activeTrials: number; // interventional, active statuses
  recruitingPhase3: number;
  approvedDrugs: number; // distinct drugs with ≥ 1 approval (approved/accelerated/conditional) in any jurisdiction
  approvals: number; // approval records across authorities
  publications: number;
  drugs: number;
  sources: number;
  activeSources: number;
  evidenceItems: number;
  epidemiologyObservations: number;
  lastIngestAt: Date | null;
}

const 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 };

/** Live counts for the home ticker. Each label on the page states exactly what is counted (§248-249). */
export async function getSiteCounts(): Promise<SiteCounts> {
  return safe(async () => {
    const [r] = await run<Record<string, string | Date | null>>(sql`
      SELECT
        (SELECT count(*) FROM cancers WHERE status = 'active' AND malignant) AS cancers,
        (SELECT count(*) FROM cancers WHERE status = 'active' AND entity_type IN ('subtype','molecular_subtype','histology')) AS subtypes,
        (SELECT count(*) FROM cancers WHERE status = 'active') AS all_entities,
        (SELECT count(*) FROM genes) AS genes,
        (SELECT count(*) FROM variants) AS variants,
        (SELECT count(*) FROM clinical_trials) AS trials,
        (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,
        (SELECT count(*) FROM clinical_trials WHERE study_type = 'INTERVENTIONAL' AND overall_status = 'RECRUITING' AND 'PHASE3' = ANY(phases)) AS recruiting_phase3,
        (SELECT count(DISTINCT drug_id) FROM drug_approvals WHERE status IN ('approved','accelerated','conditional')) AS approved_drugs,
        (SELECT count(*) FROM drug_approvals) AS approvals,
        (SELECT count(*) FROM publications) AS publications,
        (SELECT count(*) FROM drugs) AS drugs,
        (SELECT count(*) FROM sources) AS sources,
        (SELECT count(*) FROM sources WHERE status = 'active') AS active_sources,
        (SELECT count(*) FROM civic_evidence_items) AS evidence_items,
        (SELECT count(*) FROM epidemiology_observations) AS epi,
        (SELECT max(finished_at) FROM ingest_runs WHERE status IN ('succeeded','partial')) AS last_ingest
    `);
    if (!r) return EMPTY;
    return {
      cancers: Number(r.cancers),
      subtypes: Number(r.subtypes),
      allEntities: Number(r.all_entities),
      genes: Number(r.genes),
      variants: Number(r.variants),
      trials: Number(r.trials),
      activeTrials: Number(r.active_trials),
      recruitingPhase3: Number(r.recruiting_phase3),
      approvedDrugs: Number(r.approved_drugs),
      approvals: Number(r.approvals),
      publications: Number(r.publications),
      drugs: Number(r.drugs),
      sources: Number(r.sources),
      activeSources: Number(r.active_sources),
      evidenceItems: Number(r.evidence_items),
      epidemiologyObservations: Number(r.epi),
      lastIngestAt: r.last_ingest ? new Date(r.last_ingest as Date) : null,
    };
  }, EMPTY);
}

/** Deterministic daily pick among sparse-data malignant entities (seeded by UTC date). */
export 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> {
  return safe(async () => {
    const cnt = await run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers WHERE status = 'active' AND malignant`);
    const total = Number(cnt[0]?.n ?? 0);
    if (!total) return null;
    // Seed: days since epoch (UTC). Same pick for everyone during one UTC day.
    const day = Math.floor(Date.now() / 86_400_000);
    const offset = ((day * 2654435761) >>> 0) % total; // Knuth multiplicative hash for spread
    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`
      SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.primary_oncotree_code, c.rare_cancer,
             p.canonical_name AS parent_name, p.slug AS parent_slug
      FROM cancers c
      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 true
      WHERE c.status = 'active' AND c.malignant
      ORDER BY c.id OFFSET ${offset} LIMIT 1`);
    if (!row) return null;
    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 };
  }, null);
}