import 'server-only';
import { run, sql, safe } from '@/lib/db';
import { getMetric, latestSnapshotForGeo, rankingRows, type MetricDef, type RankingRow, type Snapshot } from '@/lib/queries/rankings';

/** One burden scope with computed research-gap components (SPEC §34, §113). */
export interface GapScope {
  geography: string;
  year: number;
  sex: string;
  burden_source_id: string;
  source_slug: string;
  source_name: string;
  n_total: number;
  n_eligible: number;
  formula_version: string;
  computed_at: Date | string;
}

export function gapScopeKey(s: Pick<GapScope, 'geography' | 'year' | 'sex'>): string {
  return `geo=${s.geography}|sex=${s.sex}|age=all|year=${s.year}|level=top`;
}

export async function listGapScopes(): Promise<GapScope[]> {
  return safe(
    () =>
      run<GapScope>(sql`
        SELECT r.geography, r.year, r.sex, r.burden_source_id, s.slug AS source_slug, s.name AS source_name,
               count(*)::int AS n_total, count(*) FILTER (WHERE r.eligible)::int AS n_eligible,
               max(r.formula_version) AS formula_version, max(r.updated_at) AS computed_at
        FROM research_gap_components r JOIN sources s ON s.id = r.burden_source_id
        GROUP BY r.geography, r.year, r.sex, r.burden_source_id, s.slug, s.name
        ORDER BY r.geography, r.year DESC, r.sex, count(*) DESC, s.slug`),
    [] as GapScope[],
  );
}

/**
 * Resolve the requested scope: geography (default USA), sex (default all), year (default latest),
 * source (default: the source with the most component rows in that geography/year/sex).
 */
export function pickGapScope(scopes: GapScope[], want: { geography?: string; year?: number | null; sex?: string; source?: string }): GapScope | null {
  const geo = (want.geography || 'USA').toUpperCase();
  let pool = scopes.filter((s) => s.geography.toUpperCase() === geo);
  if (pool.length === 0) pool = scopes;
  const sex = want.sex || 'all';
  const bySex = pool.filter((s) => s.sex === sex);
  if (bySex.length) pool = bySex;
  if (want.year) {
    const byYear = pool.filter((s) => Number(s.year) === want.year);
    if (byYear.length) pool = byYear;
  }
  const latest = Math.max(...pool.map((s) => Number(s.year)));
  pool = pool.filter((s) => Number(s.year) === latest);
  if (want.source) {
    const bySrc = pool.filter((s) => s.source_slug === want.source || s.burden_source_id === want.source);
    if (bySrc.length) pool = bySrc;
  }
  return [...pool].sort((a, b) => b.n_total - a.n_total || a.source_slug.localeCompare(b.source_slug))[0] ?? null;
}

export interface GapComponent {
  component_id: number;
  cancer_id: string;
  slug: string;
  canonical_name: string;
  short_name: string | null;
  hematologic: boolean;
  deaths: number | null;
  incidence: number | null;
  active_trials: number;
  phase3_trials: number;
  publications_5y: number;
  approved_drugs: number;
  death_share: number | null;
  trial_share: number | null;
  publication_share: number | null;
  trial_gap_ratio: number | null;
  research_gap_ratio: number | null;
  trials_per_1000_deaths: number | null;
  publications_per_1000_deaths: number | null;
  eligible: boolean;
  ineligible_reason: string | null;
  formula_version: string;
  inputs: Record<string, unknown>;
  computed_at: Date | string;
  trial_gap_rank: number | null;
  research_gap_rank: number | null;
}

export async function gapComponents(scope: Pick<GapScope, 'geography' | 'year' | 'sex' | 'burden_source_id'>): Promise<GapComponent[]> {
  const key = gapScopeKey(scope);
  return safe(
    () =>
      run<GapComponent>(sql`
        SELECT r.id AS component_id, r.cancer_id, c.slug, c.canonical_name, c.short_name, c.hematologic,
               r.deaths, r.incidence, r.active_trials, r.phase3_trials, r.publications_5y, r.approved_drugs,
               r.death_share, r.trial_share, r.publication_share, r.trial_gap_ratio, r.research_gap_ratio,
               r.trials_per_1000_deaths, r.publications_per_1000_deaths, r.eligible, r.ineligible_reason,
               r.formula_version, r.inputs, r.updated_at AS computed_at,
               tg.rank AS trial_gap_rank, rg.rank AS research_gap_rank
        FROM research_gap_components r JOIN cancers c ON c.id = r.cancer_id
        LEFT JOIN rankings tg ON tg.cancer_id = r.cancer_id AND tg.metric_slug = 'trial_gap_ratio' AND tg.scope_key = ${key}
          AND tg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'trial_gap_ratio' AND scope_key = ${key} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)
        LEFT JOIN rankings rg ON rg.cancer_id = r.cancer_id AND rg.metric_slug = 'research_gap_ratio' AND rg.scope_key = ${key}
          AND rg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'research_gap_ratio' AND scope_key = ${key} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)
        WHERE r.geography = ${scope.geography} AND r.year = ${Number(scope.year)} AND r.sex = ${scope.sex} AND r.burden_source_id = ${scope.burden_source_id}
        ORDER BY r.eligible DESC, r.research_gap_ratio DESC NULLS LAST, c.canonical_name`),
    [] as GapComponent[],
  );
}

/** Sums over the eligible set (what the shares were divided by). */
export function gapSums(rows: GapComponent[]): { deaths: number; activeTrials: number; publications5y: number; eligible: number } {
  const e = rows.filter((r) => r.eligible);
  return {
    deaths: e.reduce((s, r) => s + Number(r.deaths ?? 0), 0),
    activeTrials: e.reduce((s, r) => s + Number(r.active_trials), 0),
    publications5y: e.reduce((s, r) => s + Number(r.publications_5y), 0),
    eligible: e.length,
  };
}

/** The latest scope for a geography (sex all, most-populated source) — used by the home module and cancer card. */
export async function latestGapScope(geography = 'USA'): Promise<GapScope | null> {
  const scopes = await listGapScopes();
  return pickGapScope(scopes, { geography, sex: 'all' });
}

/** One cancer's component row in the latest scope of a geography (null when not part of the scope). */
export async function gapComponentForCancer(cancerId: string, geography = 'USA'): Promise<{ scope: GapScope; row: GapComponent; sums: ReturnType<typeof gapSums> } | null> {
  const scope = await latestGapScope(geography);
  if (!scope) return null;
  const rows = await gapComponents(scope);
  const row = rows.find((r) => r.cancer_id === cancerId);
  if (!row) return null;
  return { scope, row, sums: gapSums(rows) };
}

/** Home module: the ratio-based gap metrics for a geography (latest snapshot), top rows with lineage. */
export async function ratioGapRankings(geo: string, limit = 8): Promise<Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }>> {
  const out: Array<{ metric: MetricDef; snapshot: Snapshot; rows: RankingRow[] }> = [];
  for (const slug of ['trial_gap_ratio', 'research_gap_ratio']) {
    const snapshot = await latestSnapshotForGeo(slug, geo);
    if (!snapshot) continue;
    const metric = await getMetric(slug);
    if (!metric) continue;
    const rows = await rankingRows(snapshot.id, limit);
    if (rows.length) out.push({ metric, snapshot, rows });
  }
  return out;
}

/** Metric definitions used on the research-gap page (formula + version per column). */
export async function gapMetricDefs(): Promise<Map<string, MetricDef>> {
  const rows = await safe(() => run<MetricDef>(sql`SELECT m.*, 0::int AS snapshot_count FROM metric_definitions m WHERE m.slug IN ('trial_gap_ratio','research_gap_ratio','trials_per_1000_deaths','publications_per_1000_deaths')`), [] as MetricDef[]);
  return new Map(rows.map((r) => [r.slug, r]));
}