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

const MAX_CANCERS = 8;
const METRIC_RE = /^[a-z][a-z0-9_]{1,63}$/;

/** Labels shared with the web app (apps/web/src/lib/queries/epidemiology.ts EPI_METRIC_LABEL). */
const METRIC_LABEL: Record<string, string> = {
  incidence_count: 'New cases',
  incidence_rate: 'Incidence rate (crude)',
  as_incidence_rate: 'Incidence rate (age-standardized)',
  mortality_count: 'Deaths',
  mortality_rate: 'Mortality rate (crude)',
  as_mortality_rate: 'Mortality rate (age-standardized)',
  prevalence: 'Prevalence',
  prevalence_5y: '5-year prevalence',
};

/** `cancer=a&cancer=b` or `cancer=a,b` (or both) → distinct trimmed refs, max MAX_CANCERS. */
export function splitRefs(v: string | string[] | undefined, max = MAX_CANCERS): string[] {
  const raw = (Array.isArray(v) ? v : v == null ? [] : [v]).flatMap((s) => String(s).split(','));
  const out: string[] = [];
  for (const t of raw.map((s) => s.trim()).filter(Boolean)) if (!out.includes(t)) out.push(t);
  if (out.length > max) throw new BadRequest(`at most ${max} cancers per request (got ${out.length})`);
  return out;
}

const cancerParam = z.union([z.string(), z.array(z.string())]).optional().describe('Cancer id or slug — repeatable or comma-separated, max 8');
const geographyParam = z.string().trim().min(1).max(100).optional().describe('Geography slug or ISO3 code (e.g. united-states, USA)');

type GeoRow = Record<string, unknown> & { id: string; slug: string; name: string; iso3: string | null };

/**
 * Epidemiology routes (SPEC §20, §63, §110). Observations are returned exactly as stored — one row per
 * (cancer, geography, year, sex, age group, metric, source, site definition) — with the standard population,
 * estimate type and full provenance so consumers can apply the comparability rules
 * (docs/methodology/data-explorer.md): never overlay different standard populations, sources or age groups.
 */
export const epidemiologyRoutes: FastifyPluginAsyncZod = async (app) => {
  async function resolveGeography(ref: string): Promise<GeoRow> {
    const rows = await app.db.execute<GeoRow>(sql`SELECT id, slug, name, iso3 FROM geographies WHERE slug = ${ref.toLowerCase()} OR upper(iso3) = ${ref.toUpperCase()} OR id = ${ref} ORDER BY (slug = ${ref.toLowerCase()}) DESC LIMIT 1`);
    const g = rows[0];
    if (!g) throw new NotFound('geography', ref);
    return g;
  }

  app.get(
    '/epidemiology',
    {
      schema: {
        tags: ['epidemiology'],
        summary: 'Epidemiology observations filtered by metric, cancer(s), geography, sex, age group, years, source and estimate type',
        querystring: z.object({
          metric: z.string().regex(METRIC_RE).optional().describe('incidence_count | as_incidence_rate | mortality_count | mortality_rate | as_mortality_rate | …'),
          cancer: cancerParam,
          geography: geographyParam,
          sex: z.enum(['all', 'male', 'female']).optional(),
          age: z.string().trim().max(24).optional().describe('Age group label as stored (default: any; "all" = all ages)'),
          from: z.coerce.number().int().min(1900).max(2100).optional().describe('First year (inclusive)'),
          to: z.coerce.number().int().min(1900).max(2100).optional().describe('Last year (inclusive)'),
          source: z.string().trim().max(64).optional().describe('Source slug or CI-SOURCE id'),
          estimateType: z.enum(['observed', 'estimated', 'projected']).optional(),
          ...pageQuery,
        }),
        response: ok(AnyList, true),
      },
    },
    async (req) => {
      const q = req.query;
      const conds = [sql`true`];
      if (q.metric) conds.push(sql`o.metric = ${q.metric}`);
      const refs = splitRefs(q.cancer);
      if (refs.length > 0) {
        const ids = await Promise.all(refs.map((r) => resolveCancer(app.db, r).then((c) => c.id)));
        conds.push(sql`o.cancer_id = ANY(${sql.param(ids)}::text[])`);
      }
      if (q.geography) {
        const g = await resolveGeography(q.geography);
        conds.push(sql`o.geography_id = ${g.id}`);
      }
      if (q.sex) conds.push(sql`o.sex = ${q.sex}`);
      if (q.age) conds.push(sql`o.age_group = ${q.age}`);
      if (q.from != null) conds.push(sql`coalesce(o.year_end, o.year) >= ${q.from}`);
      if (q.to != null) conds.push(sql`o.year <= ${q.to}`);
      if (q.source) conds.push(q.source.startsWith('CI-SOURCE-') ? sql`o.source_id = ${q.source}` : sql`s.slug = ${q.source.toLowerCase()}`);
      if (q.estimateType) conds.push(sql`o.estimate_type = ${q.estimateType}`);
      if (q.from != null && q.to != null && q.from > q.to) throw new BadRequest('`from` must not exceed `to`');

      const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
        SELECT o.id, o.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name,
               o.geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3,
               o.year, o.year_end, o.sex, o.age_group, o.metric, o.value, o.unit, o.lower_ci, o.upper_ci, o.standard_population, o.estimate_type, o.site_definition,
               o.source_id, s.slug AS source_slug, o.provenance_id, p.dataset, p.dataset_version, p.source_url, p.retrieved_at, o.updated_at,
               count(*) OVER() AS total
        FROM epidemiology_observations o
        JOIN cancers c ON c.id = o.cancer_id
        JOIN geographies g ON g.id = o.geography_id
        JOIN sources s ON s.id = o.source_id
        LEFT JOIN provenance p ON p.id = o.provenance_id
        WHERE ${sql.join(conds, sql` AND `)}
        ORDER BY c.canonical_name, c.id, g.name, o.sex, o.year, s.slug, o.site_definition
        LIMIT ${q.limit} OFFSET ${q.offset}`);
      const total = rows.length ? num(rows[0]!.total) : 0;
      const data = rows.map((r) => ({
        id: num(r.id),
        cancer: { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name },
        geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 },
        year: num(r.year),
        yearEnd: r.year_end == null ? null : num(r.year_end),
        sex: r.sex,
        ageGroup: r.age_group,
        metric: r.metric,
        value: num(r.value),
        unit: r.unit,
        lowerCi: r.lower_ci == null ? null : num(r.lower_ci),
        upperCi: r.upper_ci == null ? null : num(r.upper_ci),
        standardPopulation: r.standard_population,
        estimateType: r.estimate_type,
        siteDefinition: r.site_definition,
        source: { id: r.source_id, slug: r.source_slug },
        provenance: { id: num(r.provenance_id), dataset: r.dataset, datasetVersion: r.dataset_version, sourceUrl: r.source_url, retrievedAt: r.retrieved_at },
        updatedAt: r.updated_at,
      }));
      return respond(app, data, data.map((d) => d.source.id as string), paginate(total, q.limit, q.offset));
    },
  );

  app.get(
    '/epidemiology/coverage',
    {
      schema: {
        tags: ['epidemiology'],
        summary: 'Coverage matrix: metric × geography × sex × age group × source × standard population with year span and counts',
        querystring: z.object({ cancer: cancerParam, geography: geographyParam, metric: z.string().regex(METRIC_RE).optional() }),
        response: ok(AnyList),
      },
    },
    async (req) => {
      const q = req.query;
      const conds = [sql`true`];
      const refs = splitRefs(q.cancer);
      if (refs.length > 0) {
        const ids = await Promise.all(refs.map((r) => resolveCancer(app.db, r).then((c) => c.id)));
        conds.push(sql`o.cancer_id = ANY(${sql.param(ids)}::text[])`);
      }
      if (q.geography) {
        const g = await resolveGeography(q.geography);
        conds.push(sql`o.geography_id = ${g.id}`);
      }
      if (q.metric) conds.push(sql`o.metric = ${q.metric}`);
      const rows = await app.db.execute<Record<string, unknown>>(sql`
        SELECT o.metric, min(o.unit) AS unit, g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3, o.sex, o.age_group,
               o.source_id, s.slug AS source_slug, o.standard_population, array_agg(DISTINCT o.estimate_type) AS estimate_types,
               min(o.year) AS year_from, max(coalesce(o.year_end, o.year)) AS year_to, count(DISTINCT o.year) AS years, count(*) AS observations, count(DISTINCT o.cancer_id) AS cancers, max(o.updated_at) AS last_updated
        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id
        WHERE ${sql.join(conds, sql` AND `)}
        GROUP BY o.metric, g.id, g.slug, g.name, g.iso3, o.sex, o.age_group, o.source_id, s.slug, o.standard_population
        ORDER BY o.metric, g.name, (o.sex = 'all') DESC, o.sex, (o.age_group = 'all') DESC, o.age_group, s.slug, o.standard_population`);
      const data = rows.map((r) => ({
        metric: r.metric,
        unit: r.unit,
        geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 },
        sex: r.sex,
        ageGroup: r.age_group,
        source: { id: r.source_id, slug: r.source_slug },
        standardPopulation: r.standard_population,
        estimateTypes: r.estimate_types,
        yearFrom: num(r.year_from),
        yearTo: num(r.year_to),
        years: num(r.years),
        observations: num(r.observations),
        cancers: num(r.cancers),
        lastUpdated: r.last_updated,
      }));
      return respond(
        app,
        data,
        data.map((d) => d.source.id as string),
      );
    },
  );

  app.get('/epidemiology/metrics', { schema: { tags: ['epidemiology'], summary: 'Distinct epidemiology metrics present, with unit, label, year span and counts', response: ok(AnyList) } }, async () => {
    const rows = await app.db.execute<Record<string, unknown>>(sql`
      SELECT o.metric, min(o.unit) AS unit, count(*) AS n, count(DISTINCT o.cancer_id) AS cancers, count(DISTINCT o.geography_id) AS geographies, min(o.year) AS year_from, max(coalesce(o.year_end, o.year)) AS year_to,
             array_agg(DISTINCT s.slug ORDER BY s.slug) AS sources, array_remove(array_agg(DISTINCT o.standard_population), NULL) AS standard_populations
      FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id GROUP BY o.metric ORDER BY o.metric`);
    const data = rows.map((r) => ({
      metric: r.metric,
      label: METRIC_LABEL[r.metric as string] ?? String(r.metric).replace(/_/g, ' '),
      unit: r.unit,
      n: num(r.n),
      cancers: num(r.cancers),
      geographies: num(r.geographies),
      yearFrom: num(r.year_from),
      yearTo: num(r.year_to),
      sources: r.sources,
      standardPopulations: r.standard_populations,
    }));
    return respond(
      app,
      data,
      data.flatMap((d) => d.sources as string[]),
    );
  });
};