import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { normalizeLabel } from '@cancerindex/shared';
import { paginate } from '../lib/envelope.js';
import { ancestorChain, descendantIds } from '../lib/descendants.js';
import { boolQuery, pageQuery } from '../lib/pagination.js';
import { resolveCancer } from '../lib/resolve.js';
import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';
import { cancerStructuralSourceIds, pluck } from '../lib/sources.js';

const idParam = z.object({ id: z.string().min(1).describe('CI-CAN-… identifier or slug') });

const listQuery = z.object({
  q: z.string().trim().min(1).max(200).optional().describe('Name/alias prefix filter'),
  level: z.enum(['top', 'all']).default('all').describe('top = the mutually exclusive global ranking set (§247)'),
  type: z.string().optional().describe('entity_type filter (cancer, subtype, hematologic_malignancy, …)'),
  malignant: boolQuery,
  hematologic: boolQuery,
  pediatric: boolQuery,
  rare: boolQuery,
  sort: z.enum(['name', 'active_trials', 'publications_5y']).default('name'),
  ...pageQuery,
});

/** Column list shared by list + detail: entity + entity_counters. */
const CANCER_COLS = sql`
  c.id, c.slug, c.canonical_name, c.short_name, c.entity_type, c.malignant, c.solid_tumor, c.hematologic, c.pediatric_relevant,
  c.rare_cancer, c.top_level, c.depth, c.primary_ncit_code, c.primary_oncotree_code, c.status, c.classification_version, c.semantic_types, c.updated_at,
  ec.trial_count, ec.active_trial_count, ec.recruiting_trial_count, ec.phase3_trial_count, ec.publication_count, ec.publication_count_5y,
  ec.publication_count_12m, ec.gene_count, ec.variant_count, ec.drug_count, ec.approved_drug_count, ec.evidence_count, ec.cohort_count,
  ec.subtype_count, ec.descendant_count, ec.epidemiology_obs_count, ec.survival_obs_count, ec.completeness, ec.updated_at AS counters_computed_at`;

function shapeCancer(row: Record<string, unknown>) {
  const r = camel<Record<string, unknown>>(row);
  const counters = r.countersComputedAt
    ? {
        trials: num(r.trialCount),
        activeTrials: num(r.activeTrialCount),
        recruitingTrials: num(r.recruitingTrialCount),
        phase3Trials: num(r.phase3TrialCount),
        publications: num(r.publicationCount),
        publications5y: num(r.publicationCount5y),
        publications12m: num(r.publicationCount12m),
        genes: num(r.geneCount),
        variants: num(r.variantCount),
        drugs: num(r.drugCount),
        approvedDrugs: num(r.approvedDrugCount),
        evidenceItems: num(r.evidenceCount),
        cohorts: num(r.cohortCount),
        subtypes: num(r.subtypeCount),
        descendants: num(r.descendantCount),
        epidemiologyObservations: num(r.epidemiologyObsCount),
        survivalObservations: num(r.survivalObsCount),
        computedAt: r.countersComputedAt,
      }
    : null; // counters not yet computed → "Data not yet available", never zeros (CLAUDE.md §281)
  return {
    id: r.id,
    slug: r.slug,
    name: r.canonicalName,
    shortName: r.shortName,
    entityType: r.entityType,
    malignant: r.malignant,
    solidTumor: r.solidTumor,
    hematologic: r.hematologic,
    pediatricRelevant: r.pediatricRelevant,
    rareCancer: r.rareCancer,
    topLevel: r.topLevel,
    depth: r.depth,
    primaryNcitCode: r.primaryNcitCode,
    primaryOncotreeCode: r.primaryOncotreeCode,
    status: r.status,
    classificationVersion: r.classificationVersion,
    semanticTypes: r.semanticTypes,
    updatedAt: r.updatedAt,
    counters,
    completeness: (r.completeness as Record<string, number> | null) ?? null,
  };
}

export const cancerRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/cancers', { schema: { tags: ['cancers'], summary: 'List cancer entities with counters', querystring: listQuery, response: ok(AnyList, true) } }, async (req) => {
    const q = req.query;
    const conds = [sql`c.status = 'active'`];
    if (q.level === 'top') conds.push(sql`c.top_level`);
    if (q.type) conds.push(sql`c.entity_type = ${q.type}`);
    if (q.malignant !== undefined) conds.push(sql`c.malignant = ${q.malignant}`);
    if (q.hematologic !== undefined) conds.push(sql`c.hematologic = ${q.hematologic}`);
    if (q.pediatric !== undefined) conds.push(sql`c.pediatric_relevant = ${q.pediatric}`);
    if (q.rare !== undefined) conds.push(sql`c.rare_cancer = ${q.rare}`);
    if (q.q) {
      const norm = normalizeLabel(q.q);
      conds.push(sql`EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND (a.normalized = ${norm} OR a.normalized LIKE ${norm + '%'} OR a.normalized LIKE ${'% ' + norm + '%'}))`);
    }
    const where = sql.join(conds, sql` AND `);
    const order =
      q.sort === 'active_trials' ? sql`ec.active_trial_count DESC NULLS LAST, c.canonical_name` : q.sort === 'publications_5y' ? sql`ec.publication_count_5y DESC NULLS LAST, c.canonical_name` : sql`c.canonical_name`;
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT ${CANCER_COLS}, count(*) OVER() AS total
      FROM cancers c LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id
      WHERE ${where} ORDER BY ${order} LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const data = rows.map(shapeCancer);
    const sources = await cancerStructuralSourceIds(
      app.db,
      data.map((d) => d.id as string),
    );
    return respond(app, data, sources, paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id', { schema: { tags: ['cancers'], summary: 'Cancer entity: aliases, codes, hierarchy, anatomy, counters, current rankings, change history', params: idParam, response: ok(AnyRecord) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const db = app.db;
    const [entityRows, aliases, codes, parents, children, anatomy, rankings, changes, breadcrumbs] = await Promise.all([
      db.execute<Record<string, unknown>>(sql`SELECT ${CANCER_COLS}, c.description, c.description_provenance_id, c.merged_into, c.deprecated_reason
        FROM cancers c LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id WHERE c.id = ${id}`),
      db.execute<Record<string, unknown>>(sql`SELECT alias, alias_type, source_id, source_terminology, language FROM cancer_aliases WHERE cancer_id = ${id} ORDER BY CASE alias_type WHEN 'preferred' THEN 0 WHEN 'display' THEN 1 WHEN 'abbreviation' THEN 2 ELSE 3 END, alias`),
      db.execute<Record<string, unknown>>(sql`SELECT system, code, match_type, source_id, valid_from, valid_to FROM cancer_codes WHERE cancer_id = ${id} ORDER BY system, code`),
      db.execute<Record<string, unknown>>(sql`SELECT h.parent_id AS id, c.slug, c.canonical_name AS name, c.entity_type, h.hierarchy_type, h.source_id FROM cancer_hierarchy h JOIN cancers c ON c.id = h.parent_id WHERE h.child_id = ${id} ORDER BY h.hierarchy_type, c.canonical_name`),
      db.execute<Record<string, unknown>>(sql`SELECT h.child_id AS id, c.slug, c.canonical_name AS name, c.entity_type, c.top_level, h.hierarchy_type, h.source_id, ec.active_trial_count
        FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id
        WHERE h.parent_id = ${id} AND c.status = 'active' ORDER BY h.hierarchy_type, c.canonical_name LIMIT 500`),
      db.execute<Record<string, unknown>>(sql`SELECT s.id, s.slug, s.name, s.system, s.ncit_code, s.uberon_id, a.relation, a.source_id FROM cancer_anatomy a JOIN anatomical_sites s ON s.id = a.site_id WHERE a.cancer_id = ${id}`),
      db.execute<Record<string, unknown>>(sql`SELECT r.id AS ranking_id, r.metric_slug, m.name AS metric_name, m.unit, m.formula_version, m.category, r.rank, r.previous_rank, r.eligible_entities, r.percentile, r.value, r.confidence,
          s.scope_key, s.geography, s.sex, s.age_group, s.year, s.entity_level, s.inputs_hash, s.generated_at, s.source_ids
        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id JOIN metric_definitions m ON m.slug = r.metric_slug
        WHERE r.cancer_id = ${id} AND s.is_current ORDER BY s.entity_level, m.category, r.metric_slug, s.scope_key`),
      db.execute<Record<string, unknown>>(sql`SELECT id, kind, summary, before, after, ingest_run_id, created_at FROM change_events WHERE entity_type = 'cancer' AND entity_id = ${id} ORDER BY created_at DESC LIMIT 20`),
      ancestorChain(db, id, 'ncit'),
    ]);
    const entityRow = entityRows[0]!;
    const entity = shapeCancer(entityRow);
    let descriptionProvenance: Record<string, unknown> | null = null;
    if (entityRow.description_provenance_id) {
      const p = await db.execute<Record<string, unknown>>(sql`SELECT p.id, p.source_id, s.slug AS source_slug, p.source_url, p.dataset, p.dataset_version, p.retrieved_at, p.evidence_type, p.license FROM provenance p JOIN sources s ON s.id = p.source_id WHERE p.id = ${Number(entityRow.description_provenance_id)}`);
      descriptionProvenance = p[0] ? camel(p[0]) : null;
    }
    const rankingRows = camelRows(rankings);
    const data = {
      ...entity,
      description: entityRow.description ?? null,
      descriptionProvenance,
      mergedInto: entityRow.merged_into ?? null,
      deprecatedReason: entityRow.deprecated_reason ?? null,
      aliases: camelRows(aliases),
      codes: camelRows(codes),
      hierarchy: { parents: camelRows(parents), children: camelRows(children), breadcrumbs },
      anatomy: camelRows(anatomy),
      rankings: rankingRows,
      changes: camelRows(changes),
    };
    const sourceRefs = [
      ...(await cancerStructuralSourceIds(db, [id])),
      ...pluck(anatomy, 'source_id'),
      ...rankings.flatMap((r) => (r.source_ids as string[]) ?? []),
      ...(descriptionProvenance ? [descriptionProvenance.sourceId as string] : []),
    ];
    return respond(app, data, sourceRefs);
  });

  app.get('/cancers/:id/statistics', { schema: { tags: ['cancers'], summary: 'Epidemiology observations (time-aware) with per-row provenance', params: idParam, querystring: z.object({ metric: z.string().optional(), geography: z.string().optional().describe('geography slug or ISO3'), sex: z.enum(['all', 'male', 'female']).optional(), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const q = req.query;
    const conds = [sql`o.cancer_id = ${id}`];
    if (q.metric) conds.push(sql`o.metric = ${q.metric}`);
    if (q.sex) conds.push(sql`o.sex = ${q.sex}`);
    if (q.geography) conds.push(sql`(g.slug = ${q.geography.toLowerCase()} OR g.iso3 = ${q.geography.toUpperCase()})`);
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT o.id, o.metric, o.year, o.year_end, o.sex, o.age_group, o.value, o.unit, o.lower_ci, o.upper_ci, o.standard_population, o.estimate_type, o.site_definition,
        g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3, g.kind AS geography_kind,
        o.source_id, s.slug AS source_slug, s.name AS source_name, p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, p.methodology, p.population, o.ingest_run_id,
        count(*) OVER() AS total
      FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id LEFT JOIN provenance p ON p.id = o.provenance_id
      WHERE ${sql.join(conds, sql` AND `)}
      ORDER BY o.metric, g.name, o.sex, o.age_group, o.year LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const observations = rows.map((r) => ({
      id: r.id,
      metric: r.metric,
      year: r.year,
      yearEnd: r.year_end,
      sex: r.sex,
      ageGroup: r.age_group,
      value: r.value,
      unit: r.unit,
      lowerCi: r.lower_ci,
      upperCi: r.upper_ci,
      standardPopulation: r.standard_population,
      estimateType: r.estimate_type,
      siteDefinition: r.site_definition,
      geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3, kind: r.geography_kind },
      provenance: { sourceId: r.source_id, sourceSlug: r.source_slug, sourceName: r.source_name, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url, methodology: r.methodology, population: r.population, ingestRunId: r.ingest_run_id },
    }));
    // Series grouped by metric × geography × sex × age group (points ordered by year) for charting.
    const series = new Map<string, { metric: unknown; unit: unknown; geography: unknown; sex: unknown; ageGroup: unknown; sourceSlug: unknown; points: Array<Record<string, unknown>> }>();
    for (const o of observations) {
      const key = `${o.metric}|${o.geography.id}|${o.sex}|${o.ageGroup}|${o.provenance.sourceSlug}`;
      if (!series.has(key)) series.set(key, { metric: o.metric, unit: o.unit, geography: o.geography, sex: o.sex, ageGroup: o.ageGroup, sourceSlug: o.provenance.sourceSlug, points: [] });
      series.get(key)!.points.push({ year: o.year, yearEnd: o.yearEnd, value: o.value, lowerCi: o.lowerCi, upperCi: o.upperCi, estimateType: o.estimateType, observationId: o.id });
    }
    return respond(app, { cancerId: id, observations, series: [...series.values()] }, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/survival', { schema: { tags: ['cancers'], summary: 'Survival observations (population statistics, not individual prognosis) with provenance', params: idParam, querystring: z.object(pageQuery), response: ok(AnyRecord, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const q = req.query;
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT o.id, o.stage, o.staging_system, o.sex, o.age_group, o.diagnosis_period, o.survival_type, o.duration_months, o.probability, o.median_months, o.cohort_size, o.lower_ci, o.upper_ci, o.method,
        g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3,
        o.source_id, s.slug AS source_slug, s.name AS source_name, p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, p.methodology, p.population, o.ingest_run_id,
        count(*) OVER() AS total
      FROM survival_observations o LEFT JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id LEFT JOIN provenance p ON p.id = o.provenance_id
      WHERE o.cancer_id = ${id}
      ORDER BY o.survival_type, o.stage NULLS FIRST, o.duration_months, o.diagnosis_period LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const observations = rows.map((r) => ({
      id: r.id,
      survivalType: r.survival_type,
      durationMonths: r.duration_months,
      probability: r.probability,
      medianMonths: r.median_months,
      cohortSize: r.cohort_size,
      lowerCi: r.lower_ci,
      upperCi: r.upper_ci,
      stage: r.stage,
      stagingSystem: r.staging_system,
      sex: r.sex,
      ageGroup: r.age_group,
      diagnosisPeriod: r.diagnosis_period,
      method: r.method,
      geography: r.geography_id ? { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 } : null,
      provenance: { sourceId: r.source_id, sourceSlug: r.source_slug, sourceName: r.source_name, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url, methodology: r.methodology, population: r.population, ingestRunId: r.ingest_run_id },
    }));
    return respond(app, { cancerId: id, observations, disclaimer: 'Population survival statistics describe groups of patients diagnosed in the past; they do not predict an individual outcome.' }, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/genes', { schema: { tags: ['cancers'], summary: 'Genes: cohort alteration frequencies (with denominators) and curated CIViC evidence, for the cancer and its descendants', params: idParam, querystring: z.object({ minFrequency: z.coerce.number().min(0).max(1).optional(), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const ids = await descendantIds(app.db, id);
    const q = req.query;
    const [freq, civic] = await Promise.all([
      app.db.execute<Record<string, unknown> & { total: string }>(sql`
        SELECT f.id, f.gene_id, f.gene_symbol, g.name AS gene_name, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.rank, f.data_release, f.cancer_id,
          c.id AS cohort_id, c.study_id, c.name AS cohort_name, c.program, c.case_count, c.cases_with_ssm, c.url AS cohort_url, c.source_id, c.cancer_match_type,
          p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, count(*) OVER() AS total
        FROM cancer_gene_frequencies f JOIN genomic_cohorts c ON c.id = f.cohort_id LEFT JOIN genes g ON g.id = f.gene_id LEFT JOIN provenance p ON p.id = f.provenance_id
        WHERE f.cancer_id = ANY(${sql.param(ids)}::text[]) ${q.minFrequency !== undefined ? sql`AND f.frequency >= ${q.minFrequency}` : sql``}
        ORDER BY f.frequency DESC, f.gene_symbol LIMIT ${q.limit} OFFSET ${q.offset}`),
      app.db.execute<Record<string, unknown>>(sql`
        SELECT gs AS gene_symbol, g.id AS gene_id, g.name AS gene_name, count(*) AS evidence_items,
          count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c,
          count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic, count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic, count(*) FILTER (WHERE e.evidence_type = 'PREDISPOSING') AS predisposing,
          array_agg(DISTINCT e.cancer_id) AS cancer_ids, min(p.source_id) AS source_id, max(p.retrieved_at) AS retrieved_at, array_agg(DISTINCT e.civic_id ORDER BY e.civic_id) AS civic_ids
        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.gene_symbols) gs LEFT JOIN genes g ON upper(g.symbol) = upper(gs) LEFT JOIN provenance p ON p.id = e.provenance_id
        WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])
        GROUP BY gs, g.id, g.name ORDER BY evidence_items DESC, gs LIMIT 500`),
    ]);
    const total = freq.length ? num(freq[0]!.total) : 0;
    const frequencies = freq.map((r) => ({
      id: r.id,
      gene: { id: r.gene_id, symbol: r.gene_symbol, name: r.gene_name },
      alterationType: r.alteration_type,
      casesAffected: r.cases_affected,
      casesProfiled: r.cases_profiled,
      frequency: r.frequency,
      rank: r.rank,
      cancerId: r.cancer_id,
      cohort: { id: r.cohort_id, studyId: r.study_id, name: r.cohort_name, program: r.program, caseCount: r.case_count, casesWithSsm: r.cases_with_ssm, url: r.cohort_url, cancerMatchType: r.cancer_match_type },
      provenance: { sourceId: r.source_id, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.data_release ?? r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url },
    }));
    const curated = civic.map((r) => ({
      gene: { id: r.gene_id, symbol: r.gene_symbol, name: r.gene_name },
      evidenceItems: num(r.evidence_items),
      byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c) },
      byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic), predisposing: num(r.predisposing) },
      cancerIds: r.cancer_ids,
      civicEvidenceIds: r.civic_ids,
      provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, category: 'curated_evidence' },
    }));
    return respond(app, { cancerId: id, includesDescendants: ids.length - 1, cohortFrequencies: frequencies, curatedEvidence: curated }, [...pluck(freq, 'source_id'), ...pluck(civic, 'source_id')], paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/variants', { schema: { tags: ['cancers'], summary: 'Variants with curated evidence counts for the cancer and its descendants', params: idParam, querystring: z.object(pageQuery), response: ok(AnyList, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const ids = await descendantIds(app.db, id);
    const q = req.query;
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT v.id, v.slug, v.name, v.gene_symbol, v.gene_id, v.variant_type, v.hgvs_p, v.hgvs_c, v.clinvar_variation_id, v.civic_variant_id,
        count(*) AS evidence_items,
        count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c, count(*) FILTER (WHERE e.evidence_level IN ('D','E')) AS level_de,
        count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not_support,
        count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic, count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic,
        array_agg(DISTINCT e.cancer_id) AS cancer_ids, min(p.source_id) AS source_id, count(*) OVER() AS total
      FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.variant_ids) vid JOIN variants v ON v.id = vid LEFT JOIN provenance p ON p.id = e.provenance_id
      WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])
      GROUP BY v.id ORDER BY evidence_items DESC, v.gene_symbol, v.name LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const data = rows.map((r) => ({
      id: r.id,
      slug: r.slug,
      name: r.name,
      gene: { id: r.gene_id, symbol: r.gene_symbol },
      variantType: r.variant_type,
      hgvsP: r.hgvs_p,
      hgvsC: r.hgvs_c,
      clinvarVariationId: r.clinvar_variation_id,
      civicVariantId: r.civic_variant_id,
      evidence: { items: num(r.evidence_items), byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), DE: num(r.level_de) }, byDirection: { supports: num(r.supports), doesNotSupport: num(r.does_not_support) }, byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic) }, category: 'curated_evidence' },
      cancerIds: r.cancer_ids,
    }));
    return respond(app, data, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/drugs', { schema: { tags: ['cancers'], summary: 'Therapies: CIViC evidence counts (level/direction) and jurisdiction-aware approvals — never a bare "approved" flag (§13)', params: idParam, querystring: z.object(pageQuery), response: ok(AnyRecord, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const ids = await descendantIds(app.db, id);
    const q = req.query;
    const [evidence, approvals] = await Promise.all([
      app.db.execute<Record<string, unknown> & { total: string }>(sql`
        SELECT d.id, d.slug, d.name, d.kind, d.ncit_code, d.chembl_id, d.mechanism,
          count(*) AS evidence_items,
          count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c, count(*) FILTER (WHERE e.evidence_level IN ('D','E')) AS level_de,
          count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not_support,
          count(*) FILTER (WHERE e.significance = 'SENSITIVITYRESPONSE') AS sensitivity, count(*) FILTER (WHERE e.significance = 'RESISTANCE') AS resistance,
          array_agg(DISTINCT e.cancer_id) AS cancer_ids, array_agg(DISTINCT gs) FILTER (WHERE gs IS NOT NULL) AS gene_symbols, min(p.source_id) AS source_id, count(*) OVER() AS total
        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid LEFT JOIN LATERAL unnest(e.gene_symbols) gs ON true LEFT JOIN provenance p ON p.id = e.provenance_id
        WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])
        GROUP BY d.id ORDER BY evidence_items DESC, d.name LIMIT ${q.limit} OFFSET ${q.offset}`),
      app.db.execute<Record<string, unknown>>(sql`
        SELECT a.id, a.drug_id, d.slug AS drug_slug, d.name AS drug_name, a.cancer_id, a.biomarker_ids, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication, a.line_of_therapy, a.disease_stage, a.approval_type, a.accelerated, a.conditional,
          a.approval_date, a.withdrawal_date, a.status, a.application_number, a.source_id, p.source_url, p.retrieved_at, p.dataset_version
        FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN provenance p ON p.id = a.provenance_id
        WHERE a.cancer_id = ANY(${sql.param(ids)}::text[]) OR a.tumor_agnostic
        ORDER BY a.approval_date DESC NULLS LAST, d.name LIMIT 500`),
    ]);
    const total = evidence.length ? num(evidence[0]!.total) : 0;
    const data = {
      cancerId: id,
      includesDescendants: ids.length - 1,
      evidenceByDrug: evidence.map((r) => ({
        drug: { id: r.id, slug: r.slug, name: r.name, kind: r.kind, ncitCode: r.ncit_code, chemblId: r.chembl_id, mechanism: r.mechanism },
        evidence: { items: num(r.evidence_items), byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), DE: num(r.level_de) }, byDirection: { supports: num(r.supports), doesNotSupport: num(r.does_not_support) }, bySignificance: { sensitivity: num(r.sensitivity), resistance: num(r.resistance) }, category: 'curated_evidence' },
        geneSymbols: r.gene_symbols ?? [],
        cancerIds: r.cancer_ids,
      })),
      approvals: approvals.map((r) => ({
        id: r.id,
        drug: { id: r.drug_id, slug: r.drug_slug, name: r.drug_name },
        cancerId: r.cancer_id,
        tumorAgnostic: r.tumor_agnostic,
        biomarkerIds: r.biomarker_ids,
        jurisdiction: r.jurisdiction,
        authority: r.authority,
        indication: r.indication,
        lineOfTherapy: r.line_of_therapy,
        diseaseStage: r.disease_stage,
        approvalType: r.approval_type,
        accelerated: r.accelerated,
        conditional: r.conditional,
        approvalDate: r.approval_date,
        withdrawalDate: r.withdrawal_date,
        status: r.status,
        applicationNumber: r.application_number,
        provenance: { sourceId: r.source_id, url: r.source_url, retrievedAt: r.retrieved_at, datasetVersion: r.dataset_version, category: 'regulatory_status' },
      })),
    };
    return respond(app, data, [...pluck(evidence, 'source_id'), ...pluck(approvals, 'source_id')], paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/trials', { schema: { tags: ['cancers'], summary: 'Clinical trials mapped to the cancer or any descendant (recursive hierarchy, depth ≤ 12)', params: idParam, querystring: z.object({ status: z.string().optional().describe('ClinicalTrials.gov overall status, e.g. RECRUITING'), phase: z.string().optional().describe('PHASE1 | PHASE2 | PHASE3 | PHASE4 | EARLY_PHASE1 | NA'), interventionalOnly: boolQuery, ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const ids = await descendantIds(app.db, id);
    const q = req.query;
    const conds = [sql`tc.cancer_id = ANY(${sql.param(ids)}::text[])`];
    if (q.status) conds.push(sql`t.overall_status = ${q.status.toUpperCase()}`);
    if (q.phase) conds.push(sql`${q.phase.toUpperCase()} = ANY(t.phases)`);
    if (q.interventionalOnly) conds.push(sql`t.study_type = 'INTERVENTIONAL'`);
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.start_date, t.primary_completion_date, t.last_update_posted_date, t.has_results, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.locations_count,
        array_agg(DISTINCT jsonb_build_object('cancerId', tc.cancer_id, 'condition', tc.condition_text, 'matchType', tc.match_type)) AS mapped_conditions,
        sr.source_id, count(*) OVER() AS total
      FROM clinical_trials t JOIN trial_conditions tc ON tc.trial_id = t.id LEFT JOIN source_records sr ON sr.id = t.source_record_id
      WHERE ${sql.join(conds, sql` AND `)}
      GROUP BY t.id, sr.source_id
      ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const data = rows.map((r) => {
      const { total: _t, source_id: _s, ...rest } = r;
      return camel(rest);
    });
    const srcs = pluck(rows, 'source_id');
    return respond(app, data, srcs.length ? srcs : ['clinicaltrials'], paginate(total, q.limit, q.offset));
  });

  app.get('/cancers/:id/publications', { schema: { tags: ['cancers'], summary: 'Linked publications (entity edges with extraction method) and literature count windows (query stored verbatim)', params: idParam, querystring: z.object({ status: z.enum(['candidate', 'validated', 'all']).default('all'), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.id);
    const q = req.query;
    const [pubs, counts] = await Promise.all([
      app.db.execute<Record<string, unknown> & { total: string }>(sql`
        SELECT p.id, p.pmid, p.doi, p.pmcid, p.title, p.journal, p.pub_date, p.pub_year, p.publication_types, p.is_preprint, p.retracted, p.nct_ids, p.cited_by_count,
          e.method, e.confidence, e.status AS edge_status, e.source_id, count(*) OVER() AS total
        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id
        WHERE e.entity_type = 'cancer' AND e.entity_id = ${id} AND e.status <> 'rejected' ${q.status !== 'all' ? sql`AND e.status = ${q.status}` : sql``}
        ORDER BY p.pub_year DESC NULLS LAST, p.pub_date DESC NULLS LAST, p.pmid LIMIT ${q.limit} OFFSET ${q.offset}`),
      app.db.execute<Record<string, unknown>>(sql`
        SELECT l.id, l.window_key, l.window_start, l.window_end, l.query, l.count, l.updated_at AS computed_at, p.source_id, p.retrieved_at, p.source_url
        FROM literature_counts l LEFT JOIN provenance p ON p.id = l.provenance_id WHERE l.cancer_id = ${id} ORDER BY l.window_key`),
    ]);
    const total = pubs.length ? num(pubs[0]!.total) : 0;
    const data = {
      cancerId: id,
      literatureCounts: counts.map((r) => ({ id: r.id, windowKey: r.window_key, windowStart: r.window_start, windowEnd: r.window_end, query: r.query, count: r.count, computedAt: r.computed_at, provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, url: r.source_url, category: 'computed_metric' } })),
      publications: pubs.map((r) => {
        const { total: _t, source_id, method, confidence, edge_status, ...rest } = r;
        return { ...camel(rest), edge: { method, confidence, status: edge_status, sourceId: source_id } };
      }),
    };
    return respond(app, data, [...pluck(pubs, 'source_id'), ...pluck(counts, 'source_id')], paginate(total, q.limit, q.offset));
  });
};