import { sql } from 'drizzle-orm';
import type { Database } from '@cancerindex/database';
import { proposeDrugMerges } from './drug-duplicates.js';

export const DRUG_PIPELINE_FORMULA_VERSION = 'ci-drug-pipeline-v1';

/** Stages, least to most advanced (funnel order on /pipeline). `withdrawn` sits outside the funnel. */
export const PIPELINE_STAGES = ['phase_not_stated', 'phase1', 'phase2', 'phase3', 'phase4', 'approved', 'withdrawn'] as const;
export type PipelineStage = (typeof PIPELINE_STAGES)[number];

/** drug_approvals.status values that count as a current market authorization. */
export const PIPELINE_APPROVED_STATUSES = ['approved', 'accelerated', 'conditional'] as const;
/** Registry statuses counted as "active" (same list as trial-intelligence / counters). */
export const PIPELINE_ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'] as const;
/** Registry phase labels → rank. EARLY_PHASE1 and PHASE1 share rank 1 (both → stage phase1); NA → 0. */
export const PHASE_RANK: Record<string, number> = { PHASE4: 4, PHASE3: 3, PHASE2: 2, PHASE1: 1, EARLY_PHASE1: 1, NA: 0 };

export const PIPELINE_THRESHOLDS = {
  /** cancer_hierarchy traversal depth from a top-level cancer down to trial / approval cancers (same as counters). */
  maxHierarchyDepth: 12,
} as const;

export interface DrugPipelineResult {
  rows: number;
  drugRows: number;
  cancerRows: number;
  mergeProposals: number;
  mergeCandidates: number;
  ms: number;
}

/**
 * Highest registry phase over a set of trials (pure, unit-tested). Returns the phase label:
 * PHASE4 > PHASE3 > PHASE2 > PHASE1 ≡ EARLY_PHASE1 (PHASE1 wins the label when both occur) > NA;
 * null when no trial carries a known phase label.
 */
export function maxPhase(phaseLists: Iterable<readonly string[]>): string | null {
  let best = -1;
  let label: string | null = null;
  let sawPhase1 = false;
  for (const phases of phaseLists) {
    for (const p of phases) {
      const r = PHASE_RANK[p];
      if (r === undefined) continue;
      if (p === 'PHASE1') sawPhase1 = true;
      if (r > best) {
        best = r;
        label = p;
      }
    }
  }
  if (best < 0) return null;
  if (best === 1) return sawPhase1 ? 'PHASE1' : 'EARLY_PHASE1';
  return label;
}

/** Phase label from a SQL-side rank (mirrors `maxPhase`). */
export function phaseLabel(rank: number | null, hasPhase1: boolean): string | null {
  if (rank == null || rank < 0) return null;
  if (rank >= 4) return 'PHASE4';
  if (rank === 3) return 'PHASE3';
  if (rank === 2) return 'PHASE2';
  if (rank === 1) return hasPhase1 ? 'PHASE1' : 'EARLY_PHASE1';
  return 'NA';
}

export interface StageInput {
  /** Approvals with status ∈ PIPELINE_APPROVED_STATUSES (for the cancer when scoped, any cancer when not). */
  approvedLike: number;
  /** Approvals with any other status (withdrawn, superseded). */
  withdrawnLike: number;
  /** Interventional trials linking the drug (and the cancer when scoped). */
  totalTrials: number;
  /** Output of `maxPhase` over those trials. */
  maxPhase: string | null;
}

/**
 * Stage rule (pure, unit-tested; docs/methodology/pipeline.md):
 *   approved            any approval with status approved | accelerated | conditional
 *   withdrawn           approvals exist but all are withdrawn / superseded
 *   phase4 … phase1     otherwise, by the highest registry phase among interventional trials
 *                       (PHASE2+PHASE3 → phase3, PHASE1+PHASE2 → phase2, EARLY_PHASE1 → phase1)
 *   phase_not_stated    trials exist but none states a phase (NA / empty)
 *   null                no trials and no approvals → no row
 */
export function stageFor(input: StageInput): PipelineStage | null {
  if (input.approvedLike > 0) return 'approved';
  if (input.withdrawnLike > 0) return 'withdrawn';
  if (input.totalTrials <= 0) return null;
  switch (input.maxPhase) {
    case 'PHASE4':
      return 'phase4';
    case 'PHASE3':
      return 'phase3';
    case 'PHASE2':
      return 'phase2';
    case 'PHASE1':
    case 'EARLY_PHASE1':
      return 'phase1';
    default:
      return 'phase_not_stated';
  }
}

type AggRow = {
  drug_id: string;
  top_id: string | null;
  total_trials: string | number | null;
  active_trials: string | number | null;
  recruiting_trials: string | number | null;
  phase3_trials: string | number | null;
  phase_rank: string | number | null;
  has_phase1: boolean | null;
  first_trial_date: string | null;
  approvals: string | number | null;
  approved_like: string | number | null;
  jurisdictions: string[] | null;
  first_approval_date: string | null;
  latest_approval_date: string | null;
};

const n =(v: string | number | null | undefined) => (v == null ? 0 : Number(v));

/**
 * Recompute `drug_pipeline`: one row per drug (cancer_id NULL, across all cancers) and one per
 * (drug, top-level cancer). A trial reaches a top-level cancer through `trial_conditions.cancer_id`
 * and its ancestors in `cancer_hierarchy` (depth ≤ 12); an approval through `drug_approvals.cancer_id`
 * the same way (approvals without a cancer only feed the unscoped row). Counts are over
 * interventional studies (DISTINCT trials). Set-based SQL over temp tables, one transaction, the
 * stage decided by the pure `stageFor` rule. Finally, salt-form / alias duplicates among drugs are
 * *proposed* to `entity_merges` (never merged here).
 */
export async function computeDrugPipeline(db: Database): Promise<DrugPipelineResult> {
  const t0 = Date.now();
  const depth = sql.raw(String(PIPELINE_THRESHOLDS.maxHierarchyDepth));
  const activeSql = sql.raw(`ARRAY[${PIPELINE_ACTIVE_STATUSES.map((s) => `'${s}'`).join(',')}]::text[]`);
  const approvedSql = sql.raw(`ARRAY[${PIPELINE_APPROVED_STATUSES.map((s) => `'${s}'`).join(',')}]::text[]`);
  const rankCase = sql.raw(`CASE p ${Object.entries(PHASE_RANK)
    .map(([k, v]) => `WHEN '${k}' THEN ${v}`)
    .join(' ')} ELSE NULL END`);

  const { drugRows, cancerRows } = await db.transaction(async (tx) => {
    // Top-level cancer → every descendant (itself included), across all hierarchy types.
    await tx.execute(sql`CREATE TEMP TABLE _dp_top (top_id varchar(32), cancer_id varchar(32), PRIMARY KEY (top_id, cancer_id)) ON COMMIT DROP`);
    await tx.execute(sql`
      INSERT INTO _dp_top
      WITH RECURSIVE d AS (
        SELECT id AS top_id, id AS cancer_id, 0 AS depth FROM cancers WHERE top_level AND status = 'active'
        UNION
        SELECT d.top_id, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.cancer_id WHERE d.depth < ${depth}
      )
      SELECT DISTINCT top_id, cancer_id FROM d`);
    // Interventional trials with their activity flags and highest phase rank.
    await tx.execute(sql`
      CREATE TEMP TABLE _dp_trial ON COMMIT DROP AS
      SELECT t.id,
        t.overall_status = ANY(${activeSql}) AS active,
        t.overall_status = 'RECRUITING' AS recruiting,
        (SELECT max(${rankCase}) FROM unnest(t.phases) AS p) AS phase_rank,
        'PHASE1' = ANY(t.phases) AS has_phase1,
        'PHASE3' = ANY(t.phases) AS has_phase3,
        CASE WHEN t.start_date ~ '^\\d{4}' THEN t.start_date END AS start_date
      FROM clinical_trials t WHERE t.study_type = 'INTERVENTIONAL'`);
    await tx.execute(sql`CREATE INDEX ON _dp_trial (id)`);
    await tx.execute(sql`
      CREATE TEMP TABLE _dp_td ON COMMIT DROP AS
      SELECT DISTINCT ti.drug_id, ti.trial_id FROM trial_interventions ti JOIN _dp_trial tr ON tr.id = ti.trial_id WHERE ti.drug_id IS NOT NULL`);
    await tx.execute(sql`
      CREATE TEMP TABLE _dp_tdc ON COMMIT DROP AS
      SELECT DISTINCT td.drug_id, td.trial_id, tp.top_id
      FROM _dp_td td JOIN trial_conditions tc ON tc.trial_id = td.trial_id AND tc.cancer_id IS NOT NULL JOIN _dp_top tp ON tp.cancer_id = tc.cancer_id`);

    const trialAgg = (scoped: boolean) => sql`
      SELECT x.drug_id, ${scoped ? sql`x.top_id` : sql`NULL::varchar`} AS top_id,
        count(*) AS total_trials,
        count(*) FILTER (WHERE tr.active) AS active_trials,
        count(*) FILTER (WHERE tr.recruiting) AS recruiting_trials,
        count(*) FILTER (WHERE tr.has_phase3) AS phase3_trials,
        max(tr.phase_rank) AS phase_rank,
        bool_or(tr.has_phase1) AS has_phase1,
        min(tr.start_date) AS first_trial_date
      FROM ${scoped ? sql`_dp_tdc` : sql`_dp_td`} x JOIN _dp_trial tr ON tr.id = x.trial_id
      GROUP BY x.drug_id${scoped ? sql`, x.top_id` : sql``}`;
    const approvalAgg = (scoped: boolean) => sql`
      SELECT a.drug_id, ${scoped ? sql`tp.top_id` : sql`NULL::varchar`} AS top_id,
        count(*) AS approvals,
        count(*) FILTER (WHERE a.status = ANY(${approvedSql})) AS approved_like,
        array_agg(DISTINCT a.jurisdiction ORDER BY a.jurisdiction) AS jurisdictions,
        min(a.approval_date) FILTER (WHERE a.status = ANY(${approvedSql}) AND a.approval_date IS NOT NULL) AS first_approval_date,
        max(a.approval_date) FILTER (WHERE a.status = ANY(${approvedSql}) AND a.approval_date IS NOT NULL) AS latest_approval_date
      FROM drug_approvals a ${scoped ? sql`JOIN _dp_top tp ON tp.cancer_id = a.cancer_id` : sql``}
      GROUP BY a.drug_id${scoped ? sql`, tp.top_id` : sql``}`;
    const combined = (scoped: boolean) => sql`
      SELECT COALESCE(t.drug_id, a.drug_id) AS drug_id, COALESCE(t.top_id, a.top_id) AS top_id,
        t.total_trials, t.active_trials, t.recruiting_trials, t.phase3_trials, t.phase_rank, t.has_phase1, t.first_trial_date,
        a.approvals, a.approved_like, a.jurisdictions, a.first_approval_date, a.latest_approval_date
      FROM (${trialAgg(scoped)}) t FULL OUTER JOIN (${approvalAgg(scoped)}) a ON a.drug_id = t.drug_id ${scoped ? sql`AND a.top_id = t.top_id` : sql``}
      WHERE EXISTS (SELECT 1 FROM drugs d WHERE d.id = COALESCE(t.drug_id, a.drug_id))`;

    const unscoped = (await tx.execute<AggRow>(combined(false))) as unknown as AggRow[];
    const scoped = (await tx.execute<AggRow>(combined(true))) as unknown as AggRow[];

    await tx.execute(sql`DELETE FROM drug_pipeline`);
    let drugRows = 0;
    let cancerRows = 0;
    const batch: Array<Record<string, unknown>> = [];
    const push = (r: AggRow, scope: 'all' | 'top_level_cancer') => {
      const approvedLike = n(r.approved_like);
      const approvals = n(r.approvals);
      const total = n(r.total_trials);
      const rank = r.phase_rank == null ? null : Number(r.phase_rank);
      const mp = total > 0 ? phaseLabel(rank, !!r.has_phase1) : null;
      const stage = stageFor({ approvedLike, withdrawnLike: approvals - approvedLike, totalTrials: total, maxPhase: mp });
      if (!stage) return;
      batch.push({
        drug_id: r.drug_id,
        cancer_id: r.top_id,
        stage,
        max_phase: mp,
        active_trials: n(r.active_trials),
        recruiting_trials: n(r.recruiting_trials),
        phase3_trials: n(r.phase3_trials),
        total_trials: total,
        approvals,
        // text[] inside unnest cannot carry a nested array → serialized as JSON text, expanded in the INSERT below.
        jurisdictions: JSON.stringify(r.jurisdictions ?? []),
        first_approval_date: r.first_approval_date,
        latest_approval_date: r.latest_approval_date,
        first_trial_date: r.first_trial_date,
        formula_version: DRUG_PIPELINE_FORMULA_VERSION,
        inputs: JSON.stringify({
          scope,
          approvedLike,
          withdrawnLike: approvals - approvedLike,
          phaseRank: rank,
          approvedStatuses: PIPELINE_APPROVED_STATUSES,
          activeStatuses: PIPELINE_ACTIVE_STATUSES,
          ancestorMapping: `trial_conditions.cancer_id / drug_approvals.cancer_id → top-level ancestor via cancer_hierarchy (depth ≤ ${PIPELINE_THRESHOLDS.maxHierarchyDepth})`,
          stageRule: 'approved > withdrawn > highest registry phase (PHASE4, PHASE3, PHASE2, PHASE1/EARLY_PHASE1) > phase_not_stated',
        }),
      });
      if (scope === 'all') drugRows++;
      else cancerRows++;
    };
    for (const r of unscoped) push(r, 'all');
    for (const r of scoped) if (r.top_id) push(r, 'top_level_cancer');
    // Insert in batches of 1,000 rows.
    for (let i = 0; i < batch.length; i += 1000) await flushBatch(tx, batch.slice(i, i + 1000));
    return { drugRows, cancerRows };
  });

  const merges = await proposeDrugMerges(db);
  return { rows: drugRows + cancerRows, drugRows, cancerRows, mergeProposals: merges.inserted, mergeCandidates: merges.candidates, ms: Date.now() - t0 };
}

async function flushBatch(tx: Pick<Database, 'execute'>, rows: Array<Record<string, unknown>>): Promise<void> {
  if (!rows.length) return;
  const col = (k: string) => sql.param(rows.map((r) => r[k]));
  await tx.execute(sql`
    INSERT INTO drug_pipeline (drug_id, cancer_id, stage, max_phase, active_trials, recruiting_trials, phase3_trials, total_trials, approvals, jurisdictions, first_approval_date, latest_approval_date, first_trial_date, formula_version, inputs)
    SELECT u.drug_id, u.cancer_id, u.stage, u.max_phase, u.active_trials, u.recruiting_trials, u.phase3_trials, u.total_trials, u.approvals,
      COALESCE(ARRAY(SELECT jsonb_array_elements_text(u.jurisdictions_json::jsonb)), '{}'::text[]), u.first_approval_date, u.latest_approval_date, u.first_trial_date, u.formula_version, u.inputs::jsonb
    FROM unnest(
      ${col('drug_id')}::varchar[], ${col('cancer_id')}::varchar[], ${col('stage')}::text[], ${col('max_phase')}::text[],
      ${col('active_trials')}::int[], ${col('recruiting_trials')}::int[], ${col('phase3_trials')}::int[], ${col('total_trials')}::int[], ${col('approvals')}::int[],
      ${col('jurisdictions')}::text[], ${col('first_approval_date')}::text[], ${col('latest_approval_date')}::text[], ${col('first_trial_date')}::text[],
      ${col('formula_version')}::text[], ${col('inputs')}::text[]
    ) AS u(drug_id, cancer_id, stage, max_phase, active_trials, recruiting_trials, phase3_trials, total_trials, approvals, jurisdictions_json, first_approval_date, latest_approval_date, first_trial_date, formula_version, inputs)`);
}