import 'server-only';
import { run, sql, safe } from '@/lib/db';
import type { ComparableObs } from '@/lib/explorer-series';
import { SEX_ANY } from '@/lib/explorer-params';

/**
 * Read helpers of the Data explorer (/explore, CSV export, home module). Everything is read from
 * `epidemiology_observations` joined to its cancer, geography, source and provenance rows; nothing is
 * estimated, summed across sites or extrapolated. Options (metrics, geographies, sexes, age groups,
 * years, sources) are the distinct values actually present, so the UI never advertises data it lacks.
 */

export interface MetricOption {
  metric: string;
  unit: string;
  n: number;
  n_cancers: number;
  year_min: number;
  year_max: number;
}

export interface GeographyOption {
  id: string;
  slug: string;
  name: string;
  iso3: string | null;
  kind: string;
  n: number;
  year_min: number;
  year_max: number;
}

export interface SourceOption {
  id: string;
  slug: string;
  name: string;
  license: string | null;
  license_status: string | null;
  homepage: string | null;
  attribution: string | null;
  n: number;
  retrieved_at: Date | string | null; // latest provenance retrieval behind its observations
}

export interface ExplorerOptions {
  metrics: MetricOption[];
  geographies: GeographyOption[];
  sexes: string[];
  age_groups: string[];
  year_min: number | null;
  year_max: number | null;
  sources: SourceOption[];
  n_obs: number;
}

export async function explorerOptions(): Promise<ExplorerOptions> {
  const [metrics, geographies, sexes, ages, years, sources] = await Promise.all([
    safe(
      () =>
        run<MetricOption>(sql`
          SELECT metric, min(unit) AS unit, count(*)::int AS n, count(DISTINCT cancer_id)::int AS n_cancers, min(year)::int AS year_min, max(coalesce(year_end, year))::int AS year_max
          FROM epidemiology_observations GROUP BY metric ORDER BY metric`),
      [] as MetricOption[],
    ),
    safe(
      () =>
        run<GeographyOption>(sql`
          SELECT g.id, g.slug, g.name, g.iso3, g.kind, a.n::int AS n, a.year_min::int AS year_min, a.year_max::int AS year_max
          FROM geographies g JOIN (SELECT geography_id, count(*) AS n, min(year) AS year_min, max(coalesce(year_end, year)) AS year_max FROM epidemiology_observations GROUP BY geography_id) a ON a.geography_id = g.id
          ORDER BY (g.kind = 'world') DESC, (g.kind = 'country') DESC, g.name`),
      [] as GeographyOption[],
    ),
    safe(() => run<{ sex: string }>(sql`SELECT sex FROM epidemiology_observations GROUP BY sex ORDER BY (sex = 'all') DESC, sex`), [] as Array<{ sex: string }>),
    safe(() => run<{ age_group: string }>(sql`SELECT age_group FROM epidemiology_observations GROUP BY age_group ORDER BY (age_group = 'all') DESC, age_group`), [] as Array<{ age_group: string }>),
    safe(() => run<{ y0: number | null; y1: number | null }>(sql`SELECT min(year)::int AS y0, max(coalesce(year_end, year))::int AS y1 FROM epidemiology_observations`), [] as Array<{ y0: number | null; y1: number | null }>),
    safe(
      () =>
        run<SourceOption>(sql`
          SELECT s.id, s.slug, s.name, s.license, s.license_status, s.homepage, s.attribution, a.n::int AS n,
                 (SELECT max(p.retrieved_at) FROM provenance p WHERE p.id IN (SELECT DISTINCT o2.provenance_id FROM epidemiology_observations o2 WHERE o2.source_id = s.id)) AS retrieved_at
          FROM sources s JOIN (SELECT source_id, count(*) AS n FROM epidemiology_observations GROUP BY source_id) a ON a.source_id = s.id
          ORDER BY s.slug`),
      [] as SourceOption[],
    ),
  ]);
  return {
    metrics,
    geographies,
    sexes: sexes.map((r) => r.sex),
    age_groups: ages.map((r) => r.age_group),
    year_min: years[0]?.y0 ?? null,
    year_max: years[0]?.y1 ?? null,
    sources,
    n_obs: metrics.reduce((n, m) => n + m.n, 0),
  };
}

export interface GeographyRef {
  id: string;
  slug: string;
  name: string;
  iso3: string | null;
  kind: string;
}

/** Geography by slug or ISO3 (case-insensitive). */
export async function resolveGeographyRef(ref: string): Promise<GeographyRef | null> {
  const r = ref.trim();
  if (!r) return null;
  const rows = await safe(
    () => run<GeographyRef>(sql`SELECT id, slug, name, iso3, kind FROM geographies WHERE slug = ${r.toLowerCase()} OR upper(iso3) = ${r.toUpperCase()} OR id = ${r} ORDER BY (slug = ${r.toLowerCase()}) DESC LIMIT 1`),
    [] as GeographyRef[],
  );
  return rows[0] ?? null;
}

export interface CancerRef {
  id: string;
  slug: string;
  canonical_name: string;
  top_level: boolean;
  status: string;
}

/** Cancers by slug or CI-CAN id, in the order requested; unknown references are dropped (reported by the caller). */
export async function resolveCancerRefs(refs: readonly string[]): Promise<CancerRef[]> {
  if (refs.length === 0) return [];
  const rows = await safe(
    () =>
      run<CancerRef>(sql`
        SELECT c.id, c.slug, c.canonical_name, c.top_level, c.status FROM cancers c
        WHERE c.slug = ANY(${sql.param(refs.map((r) => r.toLowerCase()))}::text[]) OR c.id = ANY(${sql.param(refs)}::text[])`),
    [] as CancerRef[],
  );
  const bySlug = new Map(rows.map((r) => [r.slug, r]));
  const byId = new Map(rows.map((r) => [r.id, r]));
  const out: CancerRef[] = [];
  for (const r of refs) {
    const hit = bySlug.get(r.toLowerCase()) ?? byId.get(r);
    if (hit && !out.some((o) => o.id === hit.id)) out.push(hit);
  }
  return out;
}

export interface CancerChoice {
  id: string;
  slug: string;
  canonical_name: string;
  n_obs: number; // observations for the selected metric × geography (0 = no data for this selection)
}

/** Top-level cancers (checkbox list), with their observation count for a metric × geography so the form can flag gaps. */
export async function topLevelCancerChoices(metric: string, geographyId: string | null): Promise<CancerChoice[]> {
  return safe(
    () =>
      run<CancerChoice>(sql`
        SELECT c.id, c.slug, c.canonical_name,
               (SELECT count(*) FROM epidemiology_observations o WHERE o.cancer_id = c.id AND o.metric = ${metric} AND (${geographyId}::text IS NULL OR o.geography_id = ${geographyId}))::int AS n_obs
        FROM cancers c WHERE c.top_level AND c.status = 'active' ORDER BY c.canonical_name`),
    [] as CancerChoice[],
  );
}

export interface TopByLatest {
  year: number | null;
  cancers: Array<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string }>;
}

/**
 * Default cancer selection: the N top-level cancers with the highest value in the latest year of the
 * metric for this geography/sex/age. One row per cancer (when two sources publish the same year the
 * larger observation is kept — the selection is a convenience, every value is then shown per source).
 */
export async function topCancersByLatest(metric: string, geographyId: string, sex: string, age: string, n = 5): Promise<TopByLatest> {
  const sexCond = sex === SEX_ANY ? sql`true` : sql`o.sex = ${sex}`;
  const rows = await safe(
    () =>
      run<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string; year: number }>(sql`
        WITH latest AS (
          SELECT max(o.year) AS year FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id
          WHERE o.metric = ${metric} AND o.geography_id = ${geographyId} AND ${sexCond} AND o.age_group = ${age} AND c.top_level AND c.status = 'active'
        )
        SELECT DISTINCT ON (c.id) c.id, c.slug, c.canonical_name, o.value, o.unit, s.slug AS source_slug, o.year
        FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id JOIN sources s ON s.id = o.source_id, latest
        WHERE o.metric = ${metric} AND o.geography_id = ${geographyId} AND ${sexCond} AND o.age_group = ${age} AND o.year = latest.year AND c.top_level AND c.status = 'active'
        ORDER BY c.id, o.value DESC`),
    [] as Array<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string; year: number }>,
  );
  const top = rows.sort((a, b) => Number(b.value) - Number(a.value)).slice(0, n);
  return { year: top[0]?.year ?? null, cancers: top.map(({ year: _y, ...r }) => ({ ...r, value: Number(r.value) })) };
}

/** Year span of a metric for a geography (null when nothing exists). */
export async function yearRangeFor(metric: string, geographyId: string | null): Promise<{ min: number; max: number } | null> {
  const rows = await safe(
    () => run<{ y0: number | null; y1: number | null }>(sql`SELECT min(year)::int AS y0, max(coalesce(year_end, year))::int AS y1 FROM epidemiology_observations WHERE metric = ${metric} AND (${geographyId}::text IS NULL OR geography_id = ${geographyId})`),
    [] as Array<{ y0: number | null; y1: number | null }>,
  );
  const r = rows[0];
  return r && r.y0 != null && r.y1 != null ? { min: r.y0, max: r.y1 } : null;
}

export interface ExplorerObsRow extends ComparableObs {
  id: number;
  cancer_id: string;
  geography_id: string;
  iso3: string | null;
  year_end: number | null;
  lower_ci: number | null;
  upper_ci: number | null;
  site_definition: string | null;
  source_id: string;
  source_name: string;
  source_license: string | null;
  provenance_id: number;
  dataset: string | null;
  dataset_version: string | null;
  source_url: string | null;
  retrieved_at: Date | string | null;
  updated_at: Date | string;
}

export interface ExplorerSelection {
  metric: string;
  cancerIds: readonly string[];
  geographyId: string;
  sex: string; // 'any' = no filter
  age: string;
  from: number | null;
  to: number | null;
  limit?: number;
}

/** Observations for a selection, sorted by cancer, geography, sex, year (then source) — the order the table and CSV use. */
export async function explorerObservations(sel: ExplorerSelection): Promise<ExplorerObsRow[]> {
  if (sel.cancerIds.length === 0) return [];
  const limit = Math.max(1, Math.min(sel.limit ?? 20_000, 50_000));
  const conds = [sql`o.metric = ${sel.metric}`, sql`o.geography_id = ${sel.geographyId}`, sql`o.cancer_id = ANY(${sql.param([...sel.cancerIds])}::text[])`, sql`o.age_group = ${sel.age}`];
  if (sel.sex !== SEX_ANY) conds.push(sql`o.sex = ${sel.sex}`);
  if (sel.from != null) conds.push(sql`coalesce(o.year_end, o.year) >= ${sel.from}`);
  if (sel.to != null) conds.push(sql`o.year <= ${sel.to}`);
  const rows = await safe(
    () =>
      run<ExplorerObsRow>(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, s.name AS source_name, s.license AS source_license,
               o.provenance_id, p.dataset, p.dataset_version, p.source_url, p.retrieved_at, o.updated_at
        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, g.name, o.sex, o.year, s.slug, o.site_definition LIMIT ${limit}`),
    [] as ExplorerObsRow[],
  );
  return rows.map((r) => ({ ...r, value: Number(r.value), lower_ci: r.lower_ci == null ? null : Number(r.lower_ci), upper_ci: r.upper_ci == null ? null : Number(r.upper_ci), year: Number(r.year), year_end: r.year_end == null ? null : Number(r.year_end), provenance_id: Number(r.provenance_id) }));
}

export interface PendingSource {
  slug: string;
  name: string;
  license_status: string;
  status: string;
}

/** Epidemiology sources registered in the catalogue that have not contributed a single observation yet (license review, credentials…). */
export async function pendingEpidemiologySources(): Promise<PendingSource[]> {
  return safe(
    () =>
      run<PendingSource>(sql`
        SELECT s.slug, s.name, s.license_status, s.status FROM sources s
        WHERE s.category = 'epidemiology' AND NOT EXISTS (SELECT 1 FROM epidemiology_observations o WHERE o.source_id = s.id)
        ORDER BY s.slug`),
    [] as PendingSource[],
  );
}

export interface CoverageMatrixRow {
  metric: string;
  unit: string;
  geography_id: string;
  geography_slug: string;
  geography_name: string;
  iso3: string | null;
  sex: string;
  age_group: string;
  source_slug: string;
  source_name: string;
  standard_population: string | null;
  estimate_types: string[];
  year_from: number;
  year_to: number;
  years: number;
  observations: number;
  n_cancers: number;
  last_updated: Date | string;
}

/** Coverage matrix: what exists, per metric × geography × sex × age × source × standard population. */
export async function coverageMatrix(f: { cancerIds?: readonly string[]; geographyId?: string | null; metric?: string | null } = {}): Promise<CoverageMatrixRow[]> {
  const conds = [sql`true`];
  if (f.cancerIds && f.cancerIds.length > 0) conds.push(sql`o.cancer_id = ANY(${sql.param([...f.cancerIds])}::text[])`);
  if (f.geographyId) conds.push(sql`o.geography_id = ${f.geographyId}`);
  if (f.metric) conds.push(sql`o.metric = ${f.metric}`);
  return safe(
    () =>
      run<CoverageMatrixRow>(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,
               s.slug AS source_slug, s.name AS source_name, o.standard_population, array_agg(DISTINCT o.estimate_type) AS estimate_types,
               min(o.year)::int AS year_from, max(coalesce(o.year_end, o.year))::int AS year_to, count(DISTINCT o.year)::int AS years, count(*)::int AS observations,
               count(DISTINCT o.cancer_id)::int AS n_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, s.slug, s.name, 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`),
    [] as CoverageMatrixRow[],
  );
}