import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { traceValue } from '@cancerindex/ranking';
import { NotFound } from '../lib/errors.js';
import { paginate } from '../lib/envelope.js';
import { pageQuery } from '../lib/pagination.js';
import { resolveCancer } from '../lib/resolve.js';
import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';

const scopeQuery = {
  geography: z.string().default('WORLD').describe('WORLD or ISO3 / geography slug (upper-cased)'),
  sex: z.enum(['all', 'male', 'female']).default('all'),
  age: z.string().default('all').describe('Age group key (all)'),
  year: z.coerce.number().int().optional().describe('Reference year; omitted = latest available (count metrics have no year)'),
  level: z.enum(['top', 'all']).default('top').describe('top = mutually exclusive ranking set (§247); all = every active malignant entity'),
};

/** Pick the current snapshot for a metric + scope; without `year` prefer the most recent year, then year-less snapshots. */
async function findSnapshot(app: Parameters<FastifyPluginAsyncZod>[0], metric: string, s: { geography: string; sex: string; age: string; year?: number; level: string }) {
  const rows = await app.db.execute<Record<string, unknown>>(sql`
    SELECT s.*, m.name AS metric_name, m.description AS metric_description, m.formula, m.unit, m.higher_is_worse, m.category, m.aggregation, m.eligibility, m.experimental, m.source_slugs
    FROM ranking_snapshots s JOIN metric_definitions m ON m.slug = s.metric_slug
    WHERE s.metric_slug = ${metric} AND s.is_current AND upper(s.geography) = ${s.geography.toUpperCase()} AND s.sex = ${s.sex} AND s.age_group = ${s.age} AND s.entity_level = ${s.level}
      ${s.year !== undefined ? sql`AND s.year = ${s.year}` : sql``}
    ORDER BY s.year DESC NULLS LAST, s.generated_at DESC LIMIT 1`);
  return rows[0] ?? null;
}

export const rankingRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/rankings/metrics', { schema: { tags: ['rankings'], summary: 'Metric catalog: formula, version, unit, eligibility and the scopes with a current snapshot', response: ok(AnyList) } }, async () => {
    const rows = await app.db.execute<Record<string, unknown>>(sql`
      SELECT m.*, coalesce(json_agg(json_build_object('scopeKey', s.scope_key, 'geography', s.geography, 'sex', s.sex, 'ageGroup', s.age_group, 'year', s.year, 'entityLevel', s.entity_level, 'eligibleEntities', s.eligible_entities, 'generatedAt', s.generated_at, 'inputsHash', s.inputs_hash) ORDER BY s.entity_level, s.geography, s.year DESC) FILTER (WHERE s.id IS NOT NULL), '[]'::json) AS scopes
      FROM metric_definitions m LEFT JOIN ranking_snapshots s ON s.metric_id = m.id AND s.is_current
      GROUP BY m.id ORDER BY m.category, m.slug`);
    const data = camelRows(rows);
    return respond(app, data, rows.flatMap((r) => (r.source_slugs as string[]) ?? []));
  });

  app.get('/rankings', { schema: { tags: ['rankings'], summary: 'Current ranking snapshot for a metric and scope, with snapshot metadata and the metric definition (§33, §179)', querystring: z.object({ metric: z.string().default('active_trials'), ...scopeQuery, ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {
    const q = req.query;
    const snap = await findSnapshot(app, q.metric, q);
    if (!snap) {
      const def = await app.db.execute<Record<string, unknown>>(sql`SELECT * FROM metric_definitions WHERE slug = ${q.metric}`);
      if (!def[0]) throw new NotFound('metric', q.metric);
      // Metric exists but no snapshot for this scope: "Data not yet available" (never an empty fake ranking).
      return respond(app, { metric: camel(def[0]), snapshot: null, rows: [], status: 'not_available', message: `No current ranking for ${q.metric} in scope geo=${q.geography} sex=${q.sex} age=${q.age} year=${q.year ?? 'latest'} level=${q.level}.` }, (def[0].source_slugs as string[]) ?? [], paginate(0, q.limit, q.offset));
    }
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT r.id AS ranking_id, r.rank, r.previous_rank, r.percentile, r.value, r.unit, r.confidence, r.eligible_entities, r.inputs, r.breakdown,
        c.id AS cancer_id, c.slug, c.canonical_name AS name, c.short_name, c.entity_type, c.hematologic, c.top_level, count(*) OVER() AS total
      FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)}
      ORDER BY r.rank, c.canonical_name LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const { metric_name, metric_description, formula, unit, higher_is_worse, category, aggregation, eligibility, experimental, source_slugs, ...snapshot } = snap;
    const data = {
      metric: { slug: snap.metric_slug, name: metric_name, description: metric_description, formula, formulaVersion: snap.formula_version, unit, higherIsWorse: higher_is_worse, category, aggregation, eligibility, experimental, sourceSlugs: source_slugs },
      snapshot: camel(snapshot),
      rows: rows.map((r) => ({
        rankingId: r.ranking_id,
        rank: r.rank,
        previousRank: r.previous_rank,
        rankChange: r.previous_rank == null ? null : num(r.previous_rank) - num(r.rank),
        percentile: r.percentile,
        value: r.value,
        unit: r.unit,
        confidence: r.confidence,
        eligibleEntities: r.eligible_entities,
        cancer: { id: r.cancer_id, slug: r.slug, name: r.name, shortName: r.short_name, entityType: r.entity_type, hematologic: r.hematologic, topLevel: r.top_level },
        inputs: r.inputs,
        breakdown: r.breakdown,
        explain: `/v1/rankings/${snap.metric_slug as string}/${r.cancer_id as string}/explain?geography=${encodeURIComponent(q.geography)}&sex=${q.sex}&age=${q.age}&level=${q.level}${snap.year ? `&year=${snap.year as number}` : ''}`,
      })),
    };
    return respond(app, data, (snap.source_ids as string[]) ?? [], paginate(total, q.limit, q.offset));
  });

  app.get('/rankings/:metric/:cancerId/explain', { schema: { tags: ['rankings'], summary: '"Why this rank?" — the row inputs, previous rank, snapshot metadata and a lineage trace down to provenance and raw records (§183, §252)', params: z.object({ metric: z.string(), cancerId: z.string() }), querystring: z.object(scopeQuery), response: ok(AnyRecord) } }, async (req) => {
    const { id } = await resolveCancer(app.db, req.params.cancerId);
    const snap = await findSnapshot(app, req.params.metric, req.query);
    if (!snap) throw new NotFound('ranking snapshot', `${req.params.metric} in the requested scope`);
    const rows = await app.db.execute<Record<string, unknown>>(sql`
      SELECT r.*, c.slug, c.canonical_name AS name FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)} AND r.cancer_id = ${id}`);
    const row = rows[0];
    if (!row) throw new NotFound('ranking row', `${req.params.metric}/${id} (not eligible in this scope)`);
    const [trace, neighbours] = await Promise.all([
      traceValue(app.db, 'rankings', String(row.id)),
      app.db.execute<Record<string, unknown>>(sql`SELECT r.rank, r.value, c.id, c.slug, c.canonical_name AS name FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)} AND r.rank BETWEEN ${num(row.rank) - 2} AND ${num(row.rank) + 2} ORDER BY r.rank`),
    ]);
    const { metric_name, metric_description, formula, unit, higher_is_worse, category, aggregation, eligibility, experimental, source_slugs, ...snapshot } = snap;
    const data = {
      cancer: { id, slug: row.slug, name: row.name },
      metric: { slug: snap.metric_slug, name: metric_name, description: metric_description, formula, formulaVersion: snap.formula_version, unit, higherIsWorse: higher_is_worse, category, aggregation, eligibility, experimental, sourceSlugs: source_slugs },
      snapshot: camel(snapshot),
      rank: row.rank,
      previousRank: row.previous_rank,
      rankChange: row.previous_rank == null ? null : num(row.previous_rank) - num(row.rank),
      percentile: row.percentile,
      value: row.value,
      unit: row.unit,
      confidence: row.confidence,
      eligibleEntities: row.eligible_entities,
      inputs: row.inputs,
      breakdown: row.breakdown,
      neighbours: camelRows(neighbours),
      trace,
      reproduce: `pnpm cix rank  # recompute; compare ranking_snapshots.inputs_hash = ${snap.inputs_hash as string}`,
    };
    return respond(app, data, (snap.source_ids as string[]) ?? []);
  });
};