import { and, eq, sql } from 'drizzle-orm';
import { type Database, metricDefinitions, rankingSnapshots, rankings, entityCounters, cancers } from '@cancerindex/database';
import { rankEntities, inputsHash, percentiles, type RankInput } from './rank.js';

export interface Scope {
  geography: string; // WORLD | ISO3 | slug
  sex: 'all' | 'male' | 'female';
  ageGroup: string;
  year: number | null;
  entityLevel: 'top' | 'all';
}

export function scopeKey(s: Scope): string {
  return `geo=${s.geography}|sex=${s.sex}|age=${s.ageGroup}|year=${s.year ?? 'latest'}|level=${s.entityLevel}`;
}

export interface RankingResult {
  metricSlug: string;
  scopeKey: string;
  eligible: number;
  snapshotId: number;
}

/**
 * Ranking engine (CLAUDE.md §30-35, §245-247, §288). Every snapshot stores scope, formula version,
 * an inputs hash and per-row lineage (`inputs`) so "Why #4?" and TRACE can be answered.
 *
 * Phase 1 ships count-based metrics (trials, literature, molecular) for both entity levels and the
 * burden/lethality/gap metrics only where epidemiology observations exist for the scope.
 */
export async function computeAllRankings(db: Database): Promise<RankingResult[]> {
  const results: RankingResult[] = [];
  const defs = await db.select().from(metricDefinitions);
  const byslug = new Map(defs.map((d) => [d.slug, d]));

  const countMetrics: Array<[string, keyof typeof entityCounters.$inferSelect]> = [
    ['active_trials', 'activeTrialCount'],
    ['recruiting_trials', 'recruitingTrialCount'],
    ['phase3_trials', 'phase3TrialCount'],
    ['publications_5y', 'publicationCount5y'],
    ['publications_12m', 'publicationCount12m'],
    ['curated_evidence_items', 'evidenceCount'],
    ['associated_genes', 'geneCount'],
    ['genomic_cohorts', 'cohortCount'],
  ];

  for (const level of ['top', 'all'] as const) {
    const scope: Scope = { geography: 'WORLD', sex: 'all', ageGroup: 'all', year: null, entityLevel: level };
    const counters = await db
      .select({ id: cancers.id, c: entityCounters })
      .from(cancers)
      .innerJoin(entityCounters, and(eq(entityCounters.entityType, 'cancer'), eq(entityCounters.entityId, cancers.id)))
      .where(level === 'top' ? and(eq(cancers.status, 'active'), eq(cancers.topLevel, true)) : and(eq(cancers.status, 'active'), eq(cancers.malignant, true)));
    for (const [slug, field] of countMetrics) {
      const def = byslug.get(slug);
      if (!def) continue;
      const items: RankInput[] = counters
        .map((r) => ({ id: r.id, value: Number(r.c[field] ?? 0), inputs: { counter: field, computedAt: r.c.computedAt } }))
        // Count metrics rank only entities with at least one observed item: a shared rank of 1 among
        // hundreds of zeros carries no information (CLAUDE.md §245 eligibility rules).
        .filter((i) => i.value > 0);
      if (items.length < 3) continue;
      const snap = await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: def.sourceSlugs });
      results.push(snap);
    }
    // Publication growth (derived from two windows).
    const growthDef = byslug.get('publication_growth');
    if (growthDef) {
      const rows = await db.execute<{ cancer_id: string; m12: number; y5: number }>(sql`
        SELECT cancer_id, max(count) FILTER (WHERE window_key = '12m') AS m12, max(count) FILTER (WHERE window_key = '5y_prior') AS y5
        FROM literature_counts GROUP BY cancer_id`);
      const ids = new Set(counters.map((c) => c.id));
      const items: RankInput[] = rows
        .filter((r) => ids.has(r.cancer_id) && r.y5 != null && r.m12 != null && Number(r.y5) >= 50)
        .map((r) => ({ id: r.cancer_id, value: Number(r.m12) / (Number(r.y5) / 5), inputs: { publications_12m: Number(r.m12), publications_5y_prior: Number(r.y5), formula: growthDef.formula } }));
      if (items.length) results.push(await persistSnapshot(db, growthDef, scope, items, { descending: true, sourceIds: ['pubmed'] }));
    }
  }

  // Burden / lethality / gaps per geography-year where observations exist (top level only, §246-247).
  const scopes = await db.execute<{ geography_id: string; slug: string; iso3: string | null; year: number; sex: string; source_id: string; n: number }>(sql`
    SELECT o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id, count(*) AS n
    FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN cancers c ON c.id = o.cancer_id
    WHERE c.top_level AND o.age_group = 'all' AND o.metric IN ('incidence_count','mortality_count','as_incidence_rate','as_mortality_rate')
    GROUP BY o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id HAVING count(*) >= 10`);
  for (const s of scopes) {
    const scope: Scope = { geography: s.iso3 ?? s.slug.toUpperCase(), sex: s.sex as Scope['sex'], ageGroup: 'all', year: Number(s.year), entityLevel: 'top' };
    const obs = await db.execute<{ cancer_id: string; metric: string; value: number; id: number; estimate_type: string; site_definition: string | null }>(sql`
      SELECT o.cancer_id, o.metric, o.value, o.id, o.estimate_type, o.site_definition FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id
      WHERE c.top_level AND o.geography_id = ${s.geography_id} AND o.year = ${s.year} AND o.sex = ${s.sex} AND o.age_group = 'all' AND o.source_id = ${s.source_id}`);
    const byMetric = new Map<string, Map<string, { value: number; id: number; estimate_type: string; site: string | null }>>();
    for (const o of obs) {
      if (!byMetric.has(o.metric)) byMetric.set(o.metric, new Map());
      byMetric.get(o.metric)!.set(o.cancer_id, { value: Number(o.value), id: o.id, estimate_type: o.estimate_type, site: o.site_definition });
    }
    for (const metric of ['incidence_count', 'mortality_count', 'as_incidence_rate', 'as_mortality_rate']) {
      const def = byslug.get(metric);
      const m = byMetric.get(metric);
      if (!def || !m) continue;
      const items: RankInput[] = [...m.entries()].map(([id, v]) => ({ id, value: v.value, confidence: v.estimate_type === 'observed' ? 'HIGH' : 'MEDIUM', inputs: { observationId: v.id, estimateType: v.estimate_type, siteDefinition: v.site, sourceId: s.source_id } }));
      results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id] }));
    }
    // Mortality-to-incidence ratio
    const mirDef = byslug.get('mortality_incidence_ratio');
    const inc = byMetric.get('incidence_count');
    const mort = byMetric.get('mortality_count');
    if (mirDef && inc && mort) {
      const items: RankInput[] = [];
      for (const [id, i] of inc) {
        const d = mort.get(id);
        if (!d || i.value < 100) continue;
        items.push({ id, value: d.value / i.value, confidence: i.value >= 1000 ? 'HIGH' : 'MEDIUM', inputs: { incidenceObservationId: i.id, mortalityObservationId: d.id, incidence: i.value, deaths: d.value, formula: mirDef.formula } });
      }
      if (items.length) results.push(await persistSnapshot(db, mirDef, scope, items, { descending: true, sourceIds: [s.source_id] }));
    }
    // Gap indexes: burden percentile − activity percentile
    if (mort) {
      const burdenPct = percentiles([...mort.entries()].map(([id, v]) => ({ id, value: v.value })));
      const counters = await db.select({ id: entityCounters.entityId, trials: entityCounters.activeTrialCount, pubs: entityCounters.publicationCount5y }).from(entityCounters).where(eq(entityCounters.entityType, 'cancer'));
      const cmap = new Map(counters.map((c) => [c.id, c]));
      for (const [slug, field] of [
        ['trial_gap', 'trials'],
        ['research_gap', 'pubs'],
      ] as const) {
        const def = byslug.get(slug);
        if (!def) continue;
        const ids = [...mort.keys()].filter((id) => cmap.has(id) && (field === 'trials' || (cmap.get(id)!.pubs ?? 0) > 0));
        if (ids.length < 5) continue;
        const actPct = percentiles(ids.map((id) => ({ id, value: Number(cmap.get(id)![field] ?? 0) })));
        const items: RankInput[] = ids.map((id) => ({ id, value: (burdenPct.get(id) ?? 0) - (actPct.get(id) ?? 0), inputs: { burdenPercentile: burdenPct.get(id), activityPercentile: actPct.get(id), deaths: mort.get(id)!.value, activity: Number(cmap.get(id)![field] ?? 0), formula: def.formula } }));
        results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id, ...def.sourceSlugs] }));
      }
    }
  }
  return results;
}

export async function persistSnapshot(db: Database, def: typeof metricDefinitions.$inferSelect, scope: Scope, items: RankInput[], opts: { descending: boolean; sourceIds: string[] }): Promise<RankingResult> {
  const key = scopeKey(scope);
  const ranked = rankEntities(items, { descending: def.higherIsWorse === false ? opts.descending : opts.descending });
  const hash = await inputsHash(items);
  return db.transaction(async (tx) => {
    // Previous rank for change explanation (§311)
    const prev = await tx.execute<{ cancer_id: string; rank: number }>(sql`
      SELECT r.cancer_id, r.rank FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id
      WHERE s.metric_slug = ${def.slug} AND s.scope_key = ${key} AND s.is_current`);
    const prevMap = new Map(prev.map((p) => [p.cancer_id, Number(p.rank)]));
    await tx.update(rankingSnapshots).set({ isCurrent: false }).where(and(eq(rankingSnapshots.metricSlug, def.slug), eq(rankingSnapshots.scopeKey, key), eq(rankingSnapshots.isCurrent, true)));
    const [snap] = await tx
      .insert(rankingSnapshots)
      .values({ metricId: def.id, metricSlug: def.slug, scopeKey: key, geography: scope.geography, sex: scope.sex, ageGroup: scope.ageGroup, year: scope.year, entityLevel: scope.entityLevel, formulaVersion: def.formulaVersion, eligibleEntities: ranked.length, inputsHash: hash, sourceIds: opts.sourceIds, isCurrent: true })
      .returning({ id: rankingSnapshots.id });
    const snapshotId = snap!.id;
    const chunk = 500;
    for (let i = 0; i < ranked.length; i += chunk) {
      await tx.insert(rankings).values(
        ranked.slice(i, i + chunk).map((r) => ({
          snapshotId,
          metricSlug: def.slug,
          scopeKey: key,
          cancerId: r.id,
          rank: r.rank,
          eligibleEntities: r.eligible,
          percentile: r.percentile,
          value: r.value,
          unit: def.unit,
          confidence: r.confidence ?? 'MEDIUM',
          inputs: r.inputs ?? {},
          previousRank: prevMap.get(r.id) ?? null,
        })),
      );
    }
    return { metricSlug: def.slug, scopeKey: key, eligible: ranked.length, snapshotId };
  });
}