import { sql } from 'drizzle-orm';
import type { Database } from '@cancerindex/database';

const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];
// Inline literal array: drizzle expands a JS array parameter into a ($1,$2,…) tuple, which breaks ANY().
const ACTIVE_STATUSES_SQL = `ARRAY[${ACTIVE_STATUSES.map((s) => `'${s}'`).join(',')}]::text[]`;

/**
 * Recompute entity_counters for every active cancer from canonical relations (CLAUDE.md §287).
 * Trial / evidence / cohort counts aggregate over the NCIt-hierarchy descendants of each cancer
 * (a lung-cancer trial mapped to "Lung Adenocarcinoma" counts for "Lung Cancer" too).
 * Deterministic: a full rebuild in one SQL transaction.
 */
export async function refreshCounters(db: Database): Promise<number> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`CREATE TEMP TABLE _desc (ancestor varchar(32), descendant varchar(32)) ON COMMIT DROP`);
    await tx.execute(sql`
      INSERT INTO _desc
      WITH RECURSIVE d AS (
        SELECT id AS ancestor, id AS descendant, 0 AS depth FROM cancers WHERE status = 'active'
        UNION
        SELECT d.ancestor, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.descendant
        WHERE d.depth < 12
      )
      SELECT DISTINCT ancestor, descendant FROM d
    `);
    await tx.execute(sql`CREATE INDEX ON _desc (descendant)`);
    await tx.execute(sql`CREATE INDEX ON _desc (ancestor)`);
    const rows = await tx.execute<{ n: string }>(sql`
      WITH trial_map AS (
        SELECT DISTINCT d.ancestor AS cancer_id, t.id AS trial_id, t.overall_status, t.phases, t.study_type
        FROM trial_conditions tc JOIN _desc d ON d.descendant = tc.cancer_id JOIN clinical_trials t ON t.id = tc.trial_id
        WHERE tc.cancer_id IS NOT NULL
      ),
      trials AS (
        SELECT cancer_id,
          count(*) AS trial_count,
          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${sql.raw(ACTIVE_STATUSES_SQL)})) AS active_trial_count,
          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = 'RECRUITING') AS recruiting_trial_count,
          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${sql.raw(ACTIVE_STATUSES_SQL)}) AND 'PHASE3' = ANY(phases)) AS phase3_trial_count
        FROM trial_map GROUP BY cancer_id
      ),
      pubs AS (
        SELECT cancer_id,
          max(count) FILTER (WHERE window_key = 'all') AS publication_count,
          max(count) FILTER (WHERE window_key = '5y') AS publication_count_5y,
          max(count) FILTER (WHERE window_key = '12m') AS publication_count_12m
        FROM literature_counts GROUP BY cancer_id
      ),
      evid AS (
        SELECT d.ancestor AS cancer_id, count(DISTINCT e.civic_id) AS evidence_count,
          count(DISTINCT g) FILTER (WHERE g IS NOT NULL) AS civic_gene_count,
          count(DISTINCT v) FILTER (WHERE v IS NOT NULL) AS variant_count,
          count(DISTINCT t) FILTER (WHERE t IS NOT NULL) AS drug_count
        FROM civic_evidence_items e JOIN _desc d ON d.descendant = e.cancer_id
        LEFT JOIN LATERAL unnest(e.gene_ids) g ON true
        LEFT JOIN LATERAL unnest(e.variant_ids) v ON true
        LEFT JOIN LATERAL unnest(e.therapy_ids) t ON true
        WHERE e.status = 'ACCEPTED' GROUP BY d.ancestor
      ),
      cohorts AS (
        SELECT d.ancestor AS cancer_id, count(DISTINCT c.id) AS cohort_count,
          count(DISTINCT f.gene_symbol) FILTER (WHERE f.frequency >= 0.05 AND f.cases_affected >= 20) AS gdc_gene_count
        FROM genomic_cohorts c JOIN _desc d ON d.descendant = c.cancer_id
        LEFT JOIN cancer_gene_frequencies f ON f.cohort_id = c.id
        GROUP BY d.ancestor
      ),
      appr AS (
        SELECT d.ancestor AS cancer_id, count(DISTINCT a.drug_id) AS approved_drug_count
        FROM drug_approvals a JOIN _desc d ON d.descendant = a.cancer_id WHERE a.status IN ('approved','accelerated','conditional') GROUP BY d.ancestor
      ),
      epi AS (
        SELECT cancer_id, count(*) AS n FROM epidemiology_observations GROUP BY cancer_id
      ),
      surv AS (
        SELECT cancer_id, count(*) AS n FROM survival_observations GROUP BY cancer_id
      ),
      tree AS (
        SELECT ancestor AS cancer_id, count(*) - 1 AS descendant_count FROM _desc GROUP BY ancestor
      ),
      kids AS (
        SELECT parent_id AS cancer_id, count(DISTINCT child_id) AS subtype_count FROM cancer_hierarchy GROUP BY parent_id
      ),
      ins AS (
        INSERT INTO entity_counters (entity_type, entity_id, trial_count, active_trial_count, recruiting_trial_count, phase3_trial_count,
          publication_count, publication_count_5y, publication_count_12m, gene_count, variant_count, drug_count, approved_drug_count,
          evidence_count, cohort_count, subtype_count, descendant_count, epidemiology_obs_count, survival_obs_count, completeness, updated_at)
        SELECT 'cancer', c.id,
          COALESCE(t.trial_count,0), COALESCE(t.active_trial_count,0), COALESCE(t.recruiting_trial_count,0), COALESCE(t.phase3_trial_count,0),
          COALESCE(p.publication_count,0), COALESCE(p.publication_count_5y,0), COALESCE(p.publication_count_12m,0),
          GREATEST(COALESCE(e.civic_gene_count,0), COALESCE(g.gdc_gene_count,0)), COALESCE(e.variant_count,0), COALESCE(e.drug_count,0), COALESCE(a.approved_drug_count,0),
          COALESCE(e.evidence_count,0), COALESCE(g.cohort_count,0), COALESCE(k.subtype_count,0), COALESCE(tr.descendant_count,0), COALESCE(ep.n,0), COALESCE(sv.n,0),
          jsonb_build_object(
            'epidemiology', CASE WHEN COALESCE(ep.n,0) > 0 THEN 1 ELSE 0 END,
            'survival', CASE WHEN COALESCE(sv.n,0) > 0 THEN 1 ELSE 0 END,
            'trials', CASE WHEN COALESCE(t.trial_count,0) > 0 THEN 1 ELSE 0 END,
            'literature', CASE WHEN COALESCE(p.publication_count,0) > 0 THEN 1 ELSE 0 END,
            'genomics', CASE WHEN COALESCE(g.cohort_count,0) > 0 THEN 1 ELSE 0 END,
            'evidence', CASE WHEN COALESCE(e.evidence_count,0) > 0 THEN 1 ELSE 0 END,
            'therapies', CASE WHEN COALESCE(a.approved_drug_count,0) > 0 THEN 1 ELSE 0 END
          ), now()
        FROM cancers c
        LEFT JOIN trials t ON t.cancer_id = c.id
        LEFT JOIN pubs p ON p.cancer_id = c.id
        LEFT JOIN evid e ON e.cancer_id = c.id
        LEFT JOIN cohorts g ON g.cancer_id = c.id
        LEFT JOIN appr a ON a.cancer_id = c.id
        LEFT JOIN epi ep ON ep.cancer_id = c.id
        LEFT JOIN surv sv ON sv.cancer_id = c.id
        LEFT JOIN tree tr ON tr.cancer_id = c.id
        LEFT JOIN kids k ON k.cancer_id = c.id
        WHERE c.status = 'active'
        ON CONFLICT (entity_type, entity_id) DO UPDATE SET
          trial_count = EXCLUDED.trial_count, active_trial_count = EXCLUDED.active_trial_count, recruiting_trial_count = EXCLUDED.recruiting_trial_count,
          phase3_trial_count = EXCLUDED.phase3_trial_count, publication_count = EXCLUDED.publication_count, publication_count_5y = EXCLUDED.publication_count_5y,
          publication_count_12m = EXCLUDED.publication_count_12m, gene_count = EXCLUDED.gene_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count,
          approved_drug_count = EXCLUDED.approved_drug_count, evidence_count = EXCLUDED.evidence_count, cohort_count = EXCLUDED.cohort_count, subtype_count = EXCLUDED.subtype_count,
          descendant_count = EXCLUDED.descendant_count, epidemiology_obs_count = EXCLUDED.epidemiology_obs_count, survival_obs_count = EXCLUDED.survival_obs_count,
          completeness = EXCLUDED.completeness, updated_at = now()
        RETURNING 1
      )
      SELECT count(*)::text AS n FROM ins
    `);
    // Gene-level counters (cancers linked, evidence items) for gene pages.
    // `e.gene_ids @> ARRAY[g.id]::text[]` ≡ `g.id = ANY(e.gene_ids)` but uses the GIN index
    // civic_evidence_gene_ids_gin (migrate.ts); the ANY form forced one sequential scan per gene.
    await tx.execute(sql`
      INSERT INTO entity_counters (entity_type, entity_id, evidence_count, variant_count, drug_count, trial_count, updated_at)
      SELECT 'gene', g.id,
        (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]),
        (SELECT count(*) FROM variants v WHERE v.gene_id = g.id),
        (SELECT count(DISTINCT t) FROM civic_evidence_items e, unnest(e.therapy_ids) t WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]),
        0, now()
      FROM genes g
      WHERE EXISTS (SELECT 1 FROM civic_evidence_items e WHERE e.gene_ids @> ARRAY[g.id]::text[]) OR EXISTS (SELECT 1 FROM variants v WHERE v.gene_id = g.id) OR EXISTS (SELECT 1 FROM cancer_gene_frequencies f WHERE f.gene_id = g.id)
      ON CONFLICT (entity_type, entity_id) DO UPDATE SET evidence_count = EXCLUDED.evidence_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count, updated_at = now()
    `);
    await tx.execute(sql`UPDATE genes g SET is_cancer_gene = EXISTS (SELECT 1 FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]) OR EXISTS (SELECT 1 FROM cancer_gene_frequencies f WHERE f.gene_id = g.id AND f.frequency >= 0.05 AND f.cases_affected >= 20)`);
    return Number(rows[0]?.n ?? 0);
  });
}