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

const PIPELINE_STAGES = ['phase_not_stated', 'phase1', 'phase2', 'phase3', 'phase4', 'approved', 'withdrawn'] as const;
const STAGE_RANK_SQL = sql.raw(`CASE p.stage WHEN 'approved' THEN 6 WHEN 'phase4' THEN 5 WHEN 'phase3' THEN 4 WHEN 'phase2' THEN 3 WHEN 'phase1' THEN 2 WHEN 'phase_not_stated' THEN 1 WHEN 'withdrawn' THEN 0 ELSE -1 END`);

/** "2024" → "2024-01-01" (from) / "2024-12-31" (to); full ISO dates pass through. */
function dateBound(v: string | undefined, edge: 'from' | 'to'): string | undefined {
  if (!v) return undefined;
  if (/^\d{4}$/.test(v)) return edge === 'from' ? `${v}-01-01` : `${v}-12-31`;
  return v;
}

const approvalSelect = (where: SQL, orderLimit: SQL) => sql`
  SELECT a.id, a.drug_id, 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, a.updated_at,
    a.raw->>'dpdStatus' AS source_status,
    d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name,
    p.source_url, p.retrieved_at, p.dataset, p.dataset_version, count(*) OVER() AS total
  FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id LEFT JOIN provenance p ON p.id = a.provenance_id
  WHERE ${where} ${orderLimit}`;

function shapeApproval(r: Record<string, unknown>): Record<string, unknown> {
  const { total: _t, drug_slug, drug_name, cancer_slug, cancer_name, source_url, retrieved_at, dataset, dataset_version, source_status, ...rest } = r;
  return {
    ...camel(rest),
    sourceStatus: source_status ?? null,
    drug: { id: r.drug_id, slug: drug_slug, name: drug_name },
    cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null,
    provenance: { sourceId: r.source_id, url: source_url, retrievedAt: retrieved_at, dataset, datasetVersion: dataset_version, category: 'regulatory_status' },
  };
}

/**
 * Regulatory routes (SPEC §13-15, §63): jurisdiction-aware, dated approval records (one record = one
 * authority's decision for one application / DIN — never a bare "approved"), a recent feed grouped
 * by month, and the derived drug development pipeline (`drug_pipeline`, formula-versioned).
 */
export const approvalRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get(
    '/approvals',
    {
      schema: {
        tags: ['regulatory'],
        summary: 'Approval records (jurisdiction-aware, dated), filterable by authority, jurisdiction, cancer (with descendants), drug, status, date range, text',
        querystring: z.object({
          authority: z.string().trim().min(1).max(60).optional().describe('e.g. FDA, Health Canada'),
          jurisdiction: z.string().trim().min(2).max(8).optional().describe('e.g. US, CA'),
          cancer: z.string().trim().min(1).optional().describe('CI-CAN id or slug; descendants included'),
          drug: z.string().trim().min(1).optional().describe('CI-DRUG id or slug'),
          status: z.enum(['approved', 'accelerated', 'conditional', 'withdrawn', 'superseded']).optional(),
          from: z.string().regex(/^\d{4}(-\d{2}-\d{2})?$/).optional().describe('approval_date ≥ (YYYY or YYYY-MM-DD)'),
          to: z.string().regex(/^\d{4}(-\d{2}-\d{2})?$/).optional().describe('approval_date ≤ (YYYY or YYYY-MM-DD)'),
          q: z.string().trim().min(1).max(100).optional().describe('drug name/alias or indication text'),
          ...pageQuery,
        }),
        response: ok(AnyList, true),
      },
    },
    async (req) => {
      const q = req.query;
      const conds: SQL[] = [sql`true`];
      if (q.authority) conds.push(sql`lower(a.authority) = lower(${q.authority})`);
      if (q.jurisdiction) conds.push(sql`upper(a.jurisdiction) = upper(${q.jurisdiction})`);
      if (q.status) conds.push(sql`a.status = ${q.status}`);
      const from = dateBound(q.from, 'from');
      const to = dateBound(q.to, 'to');
      if (from) conds.push(sql`a.approval_date >= ${from}`);
      if (to) conds.push(sql`a.approval_date <= ${to}`);
      if (q.cancer) {
        const c = await resolveCancer(app.db, q.cancer);
        const ids = await descendantIds(app.db, c.id);
        conds.push(sql`a.cancer_id = ANY(${sql.param(ids)}::text[])`);
      }
      if (q.drug) {
        const d = await resolveDrug(app.db, q.drug);
        conds.push(sql`a.drug_id = ${d.id}`);
      }
      if (q.q) {
        const norm = normalizeLabel(q.q);
        conds.push(sql`(EXISTS (SELECT 1 FROM drug_aliases al WHERE al.drug_id = a.drug_id AND (al.normalized = ${norm} OR al.normalized LIKE ${norm + '%'})) OR a.indication ILIKE ${'%' + q.q + '%'})`);
      }
      const rows = await app.db.execute<Record<string, unknown> & { total: string }>(approvalSelect(sql.join(conds, sql` AND `), sql`ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${q.limit} OFFSET ${q.offset}`));
      const total = rows.length ? num(rows[0]!.total) : 0;
      return respond(app, rows.map(shapeApproval), pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));
    },
  );

  app.get(
    '/approvals/recent',
    {
      schema: {
        tags: ['regulatory'],
        summary: 'Latest dated approval records across authorities, grouped by month (data.months)',
        querystring: z.object({ days: z.coerce.number().int().min(1).max(3650).default(90), limit: z.coerce.number().int().min(1).max(500).default(100), authority: z.string().trim().min(1).max(60).optional() }),
        response: ok(AnyRecord),
      },
    },
    async (req) => {
      const { days, limit, authority } = req.query;
      const since = new Date(Date.now() - days * 86_400_000).toISOString().slice(0, 10);
      const conds: SQL[] = [sql`a.approval_date IS NOT NULL`, sql`a.approval_date >= ${since}`, sql`a.approval_date <= to_char(now(), 'YYYY-MM-DD')`];
      if (authority) conds.push(sql`lower(a.authority) = lower(${authority})`);
      const rows = await app.db.execute<Record<string, unknown> & { total: string }>(approvalSelect(sql.join(conds, sql` AND `), sql`ORDER BY a.approval_date DESC, a.id DESC LIMIT ${limit}`));
      const total = rows.length ? num(rows[0]!.total) : 0;
      const months = new Map<string, Record<string, unknown>[]>();
      for (const r of rows) {
        const m = String(r.approval_date).slice(0, 7);
        months.set(m, [...(months.get(m) ?? []), shapeApproval(r)]);
      }
      const data = {
        since,
        days,
        total,
        returned: rows.length,
        months: [...months.entries()].map(([month, approvals]) => ({ month, count: approvals.length, approvals })),
      };
      return respond(app, data, pluck(rows, 'source_id'));
    },
  );

  app.get(
    '/pipeline',
    {
      schema: {
        tags: ['regulatory'],
        summary: 'Drug development pipeline rows (derived): stage per drug, or per drug × top-level cancer',
        querystring: z.object({
          cancer: z.string().trim().min(1).optional().describe('Top-level cancer (CI-CAN id or slug): drug × cancer rows'),
          stage: z.enum(PIPELINE_STAGES).optional(),
          drug: z.string().trim().min(1).optional().describe('CI-DRUG id or slug'),
          scope: z.enum(['drug', 'cancer']).default('drug').describe('Without `cancer`: drug = across-all-cancers rows (default), cancer = every drug × top-level cancer row'),
          ...pageQuery,
        }),
        response: ok(AnyList, true),
      },
    },
    async (req) => {
      const q = req.query;
      const conds: SQL[] = [];
      if (q.cancer) {
        const c = await resolveCancer(app.db, q.cancer);
        conds.push(sql`p.cancer_id = ${c.id}`);
      } else conds.push(q.scope === 'cancer' ? sql`p.cancer_id IS NOT NULL` : sql`p.cancer_id IS NULL`);
      if (q.stage) conds.push(sql`p.stage = ${q.stage}`);
      if (q.drug) {
        const d = await resolveDrug(app.db, q.drug);
        conds.push(sql`p.drug_id = ${d.id}`);
      }
      const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
        SELECT p.id, p.drug_id, p.cancer_id, p.stage, p.max_phase, p.active_trials, p.recruiting_trials, p.phase3_trials, p.total_trials, p.approvals, p.jurisdictions,
          p.first_approval_date, p.latest_approval_date, p.first_trial_date, p.formula_version, p.inputs, p.updated_at AS computed_at,
          d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, count(*) OVER() AS total
        FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id
        WHERE ${sql.join(conds, sql` AND `)}
        ORDER BY ${STAGE_RANK_SQL} DESC, p.active_trials DESC, p.total_trials DESC, d.name LIMIT ${q.limit} OFFSET ${q.offset}`);
      const total = rows.length ? num(rows[0]!.total) : 0;
      const data = rows.map((r) => {
        const { total: _t, drug_slug, drug_name, cancer_slug, cancer_name, ...rest } = r;
        return { ...camel(rest), drug: { id: r.drug_id, slug: drug_slug, name: drug_name }, cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null, category: 'computed_metric' };
      });
      return respond(app, data, await pipelineSources(rows.map((r) => r.drug_id as string)), paginate(total, q.limit, q.offset));
    },
  );

  app.get(
    '/pipeline/summary',
    {
      schema: {
        tags: ['regulatory'],
        summary: 'Drug counts per development stage (derived), globally or for one top-level cancer',
        querystring: z.object({ cancer: z.string().trim().min(1).optional() }),
        response: ok(AnyRecord),
      },
    },
    async (req) => {
      let cancer: { id: string; slug: string } | null = null;
      if (req.query.cancer) cancer = await resolveCancer(app.db, req.query.cancer);
      const where = cancer ? sql`p.cancer_id = ${cancer.id}` : sql`p.cancer_id IS NULL`;
      const rows = await app.db.execute<{ stage: string; drugs: string; active_trials: string; total_trials: string }>(sql`
        SELECT p.stage, count(*) AS drugs, coalesce(sum(p.active_trials), 0) AS active_trials, coalesce(sum(p.total_trials), 0) AS total_trials
        FROM drug_pipeline p WHERE ${where} GROUP BY p.stage`);
      const meta = await app.db.execute<{ formula_version: string | null; computed_at: Date | null; rows: string }>(sql`SELECT max(formula_version) AS formula_version, max(updated_at) AS computed_at, count(*) AS rows FROM drug_pipeline`);
      const byStage = Object.fromEntries(PIPELINE_STAGES.map((s) => [s, { drugs: 0, activeTrials: 0, totalTrials: 0 }]));
      for (const r of rows) byStage[r.stage] = { drugs: num(r.drugs), activeTrials: num(r.active_trials), totalTrials: num(r.total_trials) };
      const data = {
        cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,
        stages: PIPELINE_STAGES.map((stage) => ({ stage, ...byStage[stage]! })),
        drugs: rows.reduce((s, r) => s + num(r.drugs), 0),
        formulaVersion: meta[0]?.formula_version ?? null,
        computedAt: meta[0]?.computed_at ?? null,
        category: 'computed_metric',
      };
      return respond(app, data, await pipelineSources([]));
    },
  );

  /** Sources behind derived pipeline rows: the trial registry plus the regulatory sources of the drugs' approvals. */
  async function pipelineSources(drugIds: string[]): Promise<string[]> {
    const regs = drugIds.length
      ? await app.db.execute<{ source_id: string }>(sql`SELECT DISTINCT source_id FROM drug_approvals WHERE drug_id = ANY(${sql.param(drugIds)}::text[])`)
      : await app.db.execute<{ source_id: string }>(sql`SELECT DISTINCT source_id FROM drug_approvals`);
    return ['clinicaltrials', ...pluck(regs, 'source_id')];
  }
};