import 'server-only';
import { run, sql, safe } from '@/lib/db';

/**
 * Geography (country) queries for /countries and /country/[slug] (SPEC §47).
 * Everything is read straight from epidemiology_observations; nothing is estimated or extrapolated.
 * Only geographies with at least one observation are listed.
 */

export const SEXES = ['all', 'male', 'female'] as const;
export type Sex = (typeof SEXES)[number];

export const BURDEN_METRICS = ['mortality_count', 'incidence_count', 'as_mortality_rate', 'as_incidence_rate'] as const;
export type BurdenMetric = (typeof BURDEN_METRICS)[number];

export interface GeographyRow {
  id: string;
  slug: string;
  name: string;
  kind: string;
  iso2: string | null;
  iso3: string | null;
  who_region: string | null;
  population: number | null;
  population_year: number | null;
  parent_slug: string | null;
  parent_name: string | null;
}

export interface CountryListRow extends GeographyRow {
  n_obs: number;
  n_cancers: number;
  min_year: number;
  max_year: number;
  sources: Array<{ slug: string; name: string }>;
  last_updated: Date | string;
}

export const WHO_REGION_LABEL: Record<string, string> = {
  'who-afro': 'WHO African Region',
  'who-amro': 'WHO Region of the Americas',
  'who-searo': 'WHO South-East Asia Region',
  'who-euro': 'WHO European Region',
  'who-emro': 'WHO Eastern Mediterranean Region',
  'who-wpro': 'WHO Western Pacific Region',
};

/** Geographies of any kind that carry at least one epidemiology observation. */
export async function listGeographiesWithObservations(): Promise<CountryListRow[]> {
  return safe(
    () =>
      run<CountryListRow>(sql`
        SELECT g.id, g.slug, g.name, g.kind, g.iso2, g.iso3, g.who_region, g.population, g.population_year,
               pg.slug AS parent_slug, pg.name AS parent_name,
               a.n_obs::int AS n_obs, a.n_cancers::int AS n_cancers, a.min_year::int AS min_year, a.max_year::int AS max_year, a.last_updated,
               (SELECT json_agg(json_build_object('slug', s.slug, 'name', s.name) ORDER BY s.name)
                  FROM sources s WHERE s.id IN (SELECT DISTINCT o2.source_id FROM epidemiology_observations o2 WHERE o2.geography_id = g.id)) AS sources
        FROM geographies g
        LEFT JOIN geographies pg ON pg.id = g.parent_id
        JOIN (
          SELECT geography_id, count(*) AS n_obs, count(DISTINCT cancer_id) AS n_cancers, min(year) AS min_year, max(coalesce(year_end, year)) AS max_year, max(updated_at) AS last_updated
          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 CountryListRow[],
  );
}

export async function getGeographyBySlug(slug: string): Promise<GeographyRow | null> {
  const rows = await safe(
    () =>
      run<GeographyRow>(sql`
        SELECT g.id, g.slug, g.name, g.kind, g.iso2, g.iso3, g.who_region, g.population, g.population_year, pg.slug AS parent_slug, pg.name AS parent_name
        FROM geographies g LEFT JOIN geographies pg ON pg.id = g.parent_id WHERE g.slug = ${slug} LIMIT 1`),
    [] as GeographyRow[],
  );
  return rows[0] ?? null;
}

/** Scope key used by the ranking engine for a geography (ISO3, else upper-cased slug). */
export function geographyScopeCode(g: { iso3: string | null; slug: string }): string {
  return g.iso3 ?? g.slug.toUpperCase();
}

export interface CoverageRow {
  metric: string;
  sex: string;
  min_year: number;
  max_year: number;
  n_years: number;
  n_cancers: number;
  source_slug: string;
  source_name: string;
  estimate_types: string[];
  standard_population: string | null;
  last_updated: Date | string;
}

/** What the source covers for this geography: per metric × sex, the year span and entity count. */
export async function coverageFor(geographyId: string): Promise<CoverageRow[]> {
  return safe(
    () =>
      run<CoverageRow>(sql`
        SELECT o.metric, o.sex, min(o.year)::int AS min_year, max(o.year)::int AS max_year, count(DISTINCT o.year)::int AS n_years, count(DISTINCT o.cancer_id)::int AS n_cancers,
               s.slug AS source_slug, s.name AS source_name, array_agg(DISTINCT o.estimate_type) AS estimate_types, max(o.standard_population) AS standard_population, max(o.updated_at) AS last_updated
        FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id
        WHERE o.geography_id = ${geographyId} AND o.age_group = 'all'
        GROUP BY o.metric, o.sex, s.slug, s.name ORDER BY o.metric, o.sex`),
    [] as CoverageRow[],
  );
}

/** Distinct years with any observation for the geography (descending). */
export async function yearsFor(geographyId: string): Promise<number[]> {
  const rows = await safe(() => run<{ year: number }>(sql`SELECT DISTINCT year FROM epidemiology_observations WHERE geography_id = ${geographyId} ORDER BY year DESC`), [] as Array<{ year: number }>);
  return rows.map((r) => Number(r.year));
}

/**
 * "All cancer sites" observation, if the source publishes one (e.g. USCS "All Cancer Sites Combined").
 * Detected from the site definition or a cancer entity flagged as the all-sites aggregate; null when absent —
 * the page then says so instead of summing per-site rows (sites overlap and sources differ in inclusion).
 */
export interface AllSitesObs {
  metric: string;
  year: number;
  sex: string;
  value: number;
  unit: string;
  estimate_type: string;
  site_definition: string | null;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  cancer_slug: string;
  cancer_name: string;
}
export async function allSitesObservations(geographyId: string, year: number, sex: Sex): Promise<AllSitesObs[]> {
  return safe(
    () =>
      run<AllSitesObs>(sql`
        SELECT o.metric, o.year, o.sex, o.value, o.unit, o.estimate_type, o.site_definition, s.slug AS source_slug, s.name AS source_name, o.provenance_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name
        FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id JOIN cancers c ON c.id = o.cancer_id
        WHERE o.geography_id = ${geographyId} AND o.year = ${year} AND o.sex = ${sex} AND o.age_group = 'all'
          AND (o.site_definition ILIKE '%all cancer sites%' OR o.site_definition ILIKE '%all sites combined%' OR o.site_definition ILIKE '%all malignant neoplasms%')
        ORDER BY o.metric`),
    [] as AllSitesObs[],
  );
}

export interface TopCancerRow {
  cancer_id: string;
  slug: string;
  canonical_name: string;
  entity_type: string;
  value: number;
  unit: string;
  lower_ci: number | null;
  upper_ci: number | null;
  estimate_type: string;
  site_definition: string | null;
  standard_population: string | null;
  year: number;
  year_end: number | null;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  updated_at: Date | string;
  rank: number | null;
  eligible_entities: number | null;
  rank_scope_key: string | null;
}

export interface TopCancersResult {
  metric: BurdenMetric;
  requestedYear: number;
  year: number | null; // actual year used (latest ≤ requested with data for this metric/sex), null if none
  rows: TopCancerRow[];
}

/**
 * Top cancers for one metric in a geography/year/sex. If the requested year has no data for this metric
 * (e.g. incidence lags mortality by a year), the latest earlier year is used and reported in `year`.
 * Ranks come from the current ranking snapshot whose scope matches (metric_slug + scope_key), when one exists.
 */
export async function topCancersFor(geo: { id: string; iso3: string | null; slug: string }, metric: BurdenMetric, requestedYear: number, sex: Sex, limit = 40): Promise<TopCancersResult> {
  const yr = await safe(
    () => run<{ y: number | null }>(sql`SELECT max(year) AS y FROM epidemiology_observations WHERE geography_id = ${geo.id} AND metric = ${metric} AND sex = ${sex} AND age_group = 'all' AND year <= ${requestedYear}`),
    [{ y: null }],
  );
  const year = yr[0]?.y == null ? null : Number(yr[0].y);
  if (year == null) return { metric, requestedYear, year: null, rows: [] };
  const scopeKey = `geo=${geographyScopeCode(geo)}|sex=${sex}|age=all|year=${year}|level=top`;
  const rows = await safe(
    () =>
      run<TopCancerRow>(sql`
        SELECT DISTINCT ON (o.cancer_id) o.cancer_id, c.slug, c.canonical_name, c.entity_type, o.value, o.unit, o.lower_ci, o.upper_ci, o.estimate_type, o.site_definition, o.standard_population,
               o.year, o.year_end, s.slug AS source_slug, s.name AS source_name, o.provenance_id, o.updated_at,
               r.rank, r.eligible_entities, r.scope_key AS rank_scope_key
        FROM epidemiology_observations o
        JOIN cancers c ON c.id = o.cancer_id
        JOIN sources s ON s.id = o.source_id
        LEFT JOIN rankings r ON r.cancer_id = o.cancer_id AND r.metric_slug = ${metric} AND r.scope_key = ${scopeKey}
             AND r.snapshot_id = (SELECT id FROM ranking_snapshots rs WHERE rs.metric_slug = ${metric} AND rs.scope_key = ${scopeKey} AND rs.is_current ORDER BY rs.generated_at DESC LIMIT 1)
        WHERE o.geography_id = ${geo.id} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all' AND o.year = ${year}
        ORDER BY o.cancer_id, (c.top_level) DESC, o.value DESC`),
    [] as TopCancerRow[],
  );
  rows.sort((a, b) => Number(b.value) - Number(a.value) || a.canonical_name.localeCompare(b.canonical_name));
  return { metric, requestedYear, year, rows: rows.slice(0, limit) };
}

export interface TrendPoint {
  cancer_id: string;
  slug: string;
  canonical_name: string;
  year: number;
  value: number;
  lower_ci: number | null;
  upper_ci: number | null;
  estimate_type: string;
  unit: string;
  standard_population: string | null;
  source_slug: string;
}

/** Full yearly series of one metric for a set of cancers (all years available in the geography). */
export async function trendFor(geographyId: string, metric: BurdenMetric, sex: Sex, cancerIds: string[]): Promise<TrendPoint[]> {
  if (cancerIds.length === 0) return [];
  return safe(
    () =>
      run<TrendPoint>(sql`
        SELECT o.cancer_id, c.slug, c.canonical_name, o.year, o.value, o.lower_ci, o.upper_ci, o.estimate_type, o.unit, o.standard_population, s.slug AS source_slug
        FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id JOIN sources s ON s.id = o.source_id
        WHERE o.geography_id = ${geographyId} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all'
          AND o.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})
        ORDER BY c.canonical_name, o.year`),
    [] as TrendPoint[],
  );
}

export interface CagrRow {
  cancer_id: string;
  slug: string;
  canonical_name: string;
  start_year: number;
  end_year: number;
  start_value: number;
  end_value: number;
  n_years: number;
  cagr: number; // fraction per year, e.g. 0.021 = +2.1 %/yr
  unit: string;
  estimate_types: string[];
  source_slug: string;
  standard_population: string | null;
  start_provenance_id: number;
  end_provenance_id: number;
  updated_at: Date | string;
}

export const CAGR_FORMULA = 'CAGR = (value[end_year] / value[start_year])^(1 / (end_year − start_year)) − 1';
export const CAGR_FORMULA_VERSION = 'ci-asir-cagr-10y-v1 (derived on the fly, not a stored metric)';

/**
 * Compound annual growth rate of a metric over the last `window` available years, per cancer, computed on the
 * fly from observations (derived value; formula shown on the page). Returns nothing unless at least `window`
 * distinct years exist for the geography. Cancers with fewer than `window` points in the window are skipped.
 */
export async function cagrFor(geographyId: string, metric: BurdenMetric, sex: Sex, window = 10): Promise<{ rows: CagrRow[]; startYear: number; endYear: number; yearsAvailable: number } | null> {
  const years = await safe(
    () => run<{ year: number }>(sql`SELECT DISTINCT year FROM epidemiology_observations WHERE geography_id = ${geographyId} AND metric = ${metric} AND sex = ${sex} AND age_group = 'all' ORDER BY year DESC`),
    [] as Array<{ year: number }>,
  );
  const ys = years.map((r) => Number(r.year));
  if (ys.length < window) return null;
  const endYear = ys[0]!;
  const startYear = ys[window - 1]!;
  const rows = await safe(
    () =>
      run<CagrRow>(sql`
        WITH w AS (
          SELECT o.cancer_id, o.year, o.value, o.unit, o.estimate_type, o.provenance_id, o.standard_population, o.source_id, o.updated_at
          FROM epidemiology_observations o
          WHERE o.geography_id = ${geographyId} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all' AND o.year BETWEEN ${startYear} AND ${endYear}
        ), agg AS (
          SELECT cancer_id, count(DISTINCT year) AS n_years, min(year) AS y0, max(year) AS y1, array_agg(DISTINCT estimate_type) AS estimate_types, max(unit) AS unit, max(standard_population) AS standard_population, max(source_id) AS source_id, max(updated_at) AS updated_at
          FROM w GROUP BY cancer_id
        )
        SELECT a.cancer_id, c.slug, c.canonical_name, a.y0::int AS start_year, a.y1::int AS end_year, w0.value AS start_value, w1.value AS end_value, a.n_years::int AS n_years,
               CASE WHEN w0.value > 0 AND a.y1 > a.y0 THEN power(w1.value / w0.value, 1.0 / (a.y1 - a.y0)) - 1 ELSE NULL END AS cagr,
               a.unit, a.estimate_types, s.slug AS source_slug, a.standard_population, w0.provenance_id AS start_provenance_id, w1.provenance_id AS end_provenance_id, a.updated_at
        FROM agg a
        JOIN cancers c ON c.id = a.cancer_id
        JOIN sources s ON s.id = a.source_id
        JOIN w w0 ON w0.cancer_id = a.cancer_id AND w0.year = a.y0
        JOIN w w1 ON w1.cancer_id = a.cancer_id AND w1.year = a.y1
        WHERE a.n_years >= ${window} AND a.y0 = ${startYear} AND a.y1 = ${endYear} AND w0.value > 0
        ORDER BY cagr DESC NULLS LAST`),
    [] as CagrRow[],
  );
  return { rows: rows.filter((r) => r.cagr != null && Number.isFinite(Number(r.cagr))).map((r) => ({ ...r, cagr: Number(r.cagr), start_value: Number(r.start_value), end_value: Number(r.end_value) })), startYear, endYear, yearsAvailable: ys.length };
}

/** Geography slugs for the sitemap chunk (only those with observations). */
export async function geographySlugsForSitemap(): Promise<Array<{ slug: string; updated_at: Date | string }>> {
  return safe(
    () => run<{ slug: string; updated_at: Date | string }>(sql`SELECT g.slug, max(o.updated_at) AS updated_at FROM geographies g JOIN epidemiology_observations o ON o.geography_id = g.id GROUP BY g.slug ORDER BY g.slug`),
    [] as Array<{ slug: string; updated_at: Date | string }>,
  );
}