import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { STOP_REASON_CATEGORIES, STOP_REASON_RULES, STOP_REASON_RULES_VERSION, TRIAL_INTELLIGENCE_FORMULA_VERSION, TRIAL_INTEL_THRESHOLDS, classifyStopReason, type StopReasonCategory } from '@cancerindex/ranking';
import { paginate } from '../lib/envelope.js';
import { descendantIds } from '../lib/descendants.js';
import { pageQuery } from '../lib/pagination.js';
import { resolveCancer } from '../lib/resolve.js';
import { AnyList, AnyRecord, camel, num, ok, respond } from '../lib/respond.js';

/** Sortable columns of GET /trials/intelligence (whitelist → physical column). */
const SORTABLE = {
  active: 'ti.active_trials',
  total: 'ti.total_trials',
  recruiting: 'ti.recruiting_trials',
  phase3Active: 'ti.phase3_active',
  phase3Recruiting: 'ti.phase3_recruiting',
  growth: 'ti.trial_growth_yoy',
  new12m: 'ti.new_trials_12m',
  avgEnrollment: 'ti.avg_enrollment',
  medianEnrollment: 'ti.median_enrollment',
  industryShare: 'ti.industry_share',
  sponsorHhi: 'ti.sponsor_hhi',
  distinctSponsors: 'ti.distinct_sponsors',
  distinctCountries: 'ti.distinct_countries',
  usShare: 'ti.us_share',
  countryHhi: 'ti.country_hhi',
  terminationShare: 'ti.termination_share',
  trialsPer1000Deaths: 'ti.trials_per_1000_deaths',
  trialsPer100kCases: 'ti.trials_per_100k_cases',
  completed: 'ti.completed_trials',
  terminated: 'ti.terminated_trials',
  withResults: 'ti.with_results',
  name: 'c.canonical_name',
} as const;
type SortKey = keyof typeof SORTABLE;
const sortKeys = Object.keys(SORTABLE) as [SortKey, ...SortKey[]];

const INTEL_COLUMNS = sql`ti.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, c.top_level, ti.entity_level,
  ti.total_trials, ti.active_trials, ti.recruiting_trials, ti.phase1_active, ti.phase2_active, ti.phase3_active, ti.phase3_recruiting, ti.phase4_active,
  ti.completed_trials, ti.terminated_trials, ti.withdrawn_trials, ti.suspended_trials, ti.with_results,
  ti.new_trials_12m, ti.new_trials_prior_12m, ti.trial_growth_yoy, ti.avg_enrollment, ti.median_enrollment, ti.total_enrollment_active,
  ti.distinct_sponsors, ti.industry_share, ti.sponsor_hhi, ti.top_sponsor, ti.top_sponsor_share,
  ti.distinct_countries, ti.us_share, ti.top_country, ti.top_country_share, ti.country_hhi,
  ti.termination_share, ti.why_stopped_breakdown, ti.trials_per_1000_deaths, ti.trials_per_100k_cases, ti.burden_geography, ti.burden_year, ti.burden_source_id,
  ti.formula_version, ti.inputs, ti.updated_at AS computed_at`;

const STOPPED_STATUSES = ['TERMINATED', 'WITHDRAWN', 'SUSPENDED'];

/**
 * Clinical-trial intelligence (SPEC §10): per-cancer derived metrics with formula version and inputs,
 * and registrant-reported stop reasons classified by explicit keyword rules (never inferred).
 * `GET /trials/sites` (trial map) is added by the Trial Map work package.
 */
export const intelligenceRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get(
    '/trials/intelligence',
    {
      schema: {
        tags: ['trials'],
        summary: 'Trial intelligence per cancer: counts, growth, enrollment, sponsor and country concentration, termination share, burden-normalized intensity (computed, formula-versioned)',
        querystring: z.object({
          level: z.enum(['top', 'all']).default('top').describe('top = mutually exclusive top-level cancers; all = every malignant entity with ≥ 1 mapped trial'),
          sort: z.enum(sortKeys).default('active'),
          order: z.enum(['asc', 'desc']).default('desc'),
          minActive: z.coerce.number().int().min(0).optional().describe('Only rows with at least this many active interventional studies'),
          ...pageQuery,
        }),
        response: ok(AnyList, true),
      },
    },
    async (req) => {
      const q = req.query;
      const conds = [sql`ti.entity_level = ${q.level}`, sql`c.status = 'active'`];
      if (q.minActive != null) conds.push(sql`ti.active_trials >= ${q.minActive}`);
      const col = SORTABLE[q.sort];
      const dir = q.order === 'asc' ? sql.raw('ASC NULLS LAST') : sql.raw('DESC NULLS LAST');
      const rows = await app.db.execute<Record<string, unknown> & { total: string; burden_source_id: string | null }>(sql`
        SELECT ${INTEL_COLUMNS}, count(*) OVER() AS total
        FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id
        WHERE ${sql.join(conds, sql` AND `)}
        ORDER BY ${sql.raw(col)} ${dir}, c.canonical_name ASC LIMIT ${q.limit} OFFSET ${q.offset}`);
      const total = rows.length ? num(rows[0]!.total) : 0;
      const data = rows.map((r) => {
        const { total: _t, ...rest } = r;
        return camel(rest);
      });
      const burdenSources = new Set(rows.map((r) => r.burden_source_id).filter((s): s is string => !!s));
      return respond(app, data, data.length ? ['clinicaltrials', ...burdenSources] : [], paginate(total, q.limit, q.offset));
    },
  );

  app.get(
    '/trials/intelligence/:cancer',
    {
      schema: {
        tags: ['trials'],
        summary: 'Trial intelligence for one cancer (both entity levels when present) with the registrant-reported stop-reason breakdown and the classification rules',
        params: z.object({ cancer: z.string().min(1).describe('CI-CAN-… id or slug') }),
        response: ok(AnyRecord),
      },
    },
    async (req) => {
      const { id, slug } = await resolveCancer(app.db, req.params.cancer);
      const rows = await app.db.execute<Record<string, unknown> & { entity_level: string; burden_source_id: string | null; why_stopped_breakdown: Record<string, number> }>(sql`
        SELECT ${INTEL_COLUMNS} FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id WHERE ti.cancer_id = ${id} ORDER BY ti.entity_level`);
      const levels: Record<string, unknown> = {};
      for (const r of rows) levels[r.entity_level] = camel(r);
      const primary = rows.find((r) => r.entity_level === 'all') ?? rows[0];
      const data = {
        cancer: { id, slug, name: (primary?.cancer_name as string | undefined) ?? null },
        available: rows.length > 0,
        formulaVersion: TRIAL_INTELLIGENCE_FORMULA_VERSION,
        levels,
        whyStoppedBreakdown: primary?.why_stopped_breakdown ?? null,
        stopReasonRules: { version: STOP_REASON_RULES_VERSION, categories: STOP_REASON_CATEGORIES, rules: STOP_REASON_RULES.map((r) => ({ category: r.category, keywords: r.keywords })), note: 'Reasons are as posted by the registrant on ClinicalTrials.gov; a category is assigned only when an explicit keyword matches, otherwise other_stated / not_stated.' },
        thresholds: TRIAL_INTEL_THRESHOLDS,
      };
      const burdenSources = rows.map((r) => r.burden_source_id).filter((s): s is string => !!s);
      return respond(app, data, rows.length ? ['clinicaltrials', ...burdenSources] : []);
    },
  );

  app.get(
    '/trials/terminated',
    {
      schema: {
        tags: ['trials'],
        summary: 'Terminated, withdrawn and suspended studies with the registrant-reported reason (raw) and its keyword-rule category; breakdown over the filtered set',
        querystring: z.object({
          cancer: z.string().optional().describe('Cancer id/slug — includes descendants'),
          reason: z.enum(STOP_REASON_CATEGORIES).optional().describe('Stop-reason category (keyword rules)'),
          status: z.enum(['TERMINATED', 'WITHDRAWN', 'SUSPENDED']).optional(),
          since: z.coerce.number().int().min(1990).max(2100).optional().describe('First posted in this year or later'),
          studyType: z.string().optional().describe('INTERVENTIONAL | OBSERVATIONAL | EXPANDED_ACCESS'),
          ...pageQuery,
        }),
        response: ok(AnyRecord, true),
      },
    },
    async (req) => {
      const q = req.query;
      const conds = [sql`t.overall_status = ANY(${sql.param(STOPPED_STATUSES)}::text[])`];
      if (q.status) conds.push(sql`t.overall_status = ${q.status}`);
      if (q.since) conds.push(sql`t.first_posted_date >= ${`${q.since}-01-01`}`);
      if (q.studyType) conds.push(sql`t.study_type = ${q.studyType.toUpperCase()}`);
      let cancer: { id: string; slug: string } | null = null;
      if (q.cancer) {
        cancer = await resolveCancer(app.db, q.cancer);
        const ids = await descendantIds(app.db, cancer.id);
        conds.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id = ANY(${sql.param(ids)}::text[]))`);
      }
      // 1) light pass over the whole filtered set: classify every reason (the category is not stored), build the breakdown.
      const light = await app.db.execute<{ id: string; why_stopped: string | null; last_update_posted_date: string | null; nct_id: string }>(sql`
        SELECT t.id, t.why_stopped, t.last_update_posted_date, t.nct_id FROM clinical_trials t WHERE ${sql.join(conds, sql` AND `)}
        ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id`);
      const breakdown = Object.fromEntries(STOP_REASON_CATEGORIES.map((c) => [c, 0])) as Record<StopReasonCategory, number>;
      const classified = light.map((r) => {
        const c = classifyStopReason(r.why_stopped);
        breakdown[c.category] += 1;
        return { id: r.id, category: c.category, matched: c.matched };
      });
      const filtered = q.reason ? classified.filter((r) => r.category === q.reason) : classified;
      const page = filtered.slice(q.offset, q.offset + q.limit);
      const byId = new Map(page.map((p) => [p.id, p]));
      // 2) full columns for the page only.
      const rows = page.length
        ? await app.db.execute<Record<string, unknown> & { id: string }>(sql`
            SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.why_stopped, t.first_posted_date, t.last_update_posted_date, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries
            FROM clinical_trials t WHERE t.id = ANY(${sql.param(page.map((p) => p.id))}::text[])`)
        : [];
      const order = new Map(page.map((p, i) => [p.id, i]));
      const trials = rows
        .sort((a, b) => (order.get(a.id) ?? 0) - (order.get(b.id) ?? 0))
        .map((r) => {
          const c = byId.get(r.id)!;
          return { ...camel(r), reasonCategory: c.category, reasonMatches: c.matched };
        });
      const data = {
        filters: { cancer: cancer ? { id: cancer.id, slug: cancer.slug, includesDescendants: true } : null, reason: q.reason ?? null, status: q.status ?? null, since: q.since ?? null, studyType: q.studyType?.toUpperCase() ?? null },
        total: light.length,
        breakdown,
        rules: { version: STOP_REASON_RULES_VERSION, categories: STOP_REASON_CATEGORIES, rules: STOP_REASON_RULES.map((r) => ({ category: r.category, keywords: r.keywords })), note: 'Reasons are registrant-reported free text; categories come from explicit keyword matches only and are never inferred.' },
        trials,
      };
      return respond(app, data, light.length ? ['clinicaltrials'] : [], paginate(filtered.length, q.limit, q.offset));
    },
  );
};