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

export interface MetricDef {
  id: string;
  slug: string;
  name: string;
  description: string;
  formula: string;
  formula_version: string;
  unit: string;
  higher_is_worse: boolean | null;
  aggregation: string | null;
  valid_dimensions: string[];
  source_slugs: string[];
  category: string;
  eligibility: Record<string, unknown>;
  experimental: boolean;
  snapshot_count: number;
}

// NOTE: `rankings`/`ranking_snapshots` use generated_at; `entity_counters` and `literature_counts`
// declare computedAt via the updatedAt() helper, so their physical column is `updated_at`.
export async function listMetrics(): Promise<MetricDef[]> {
  const rows = await safe(
    () =>
      run<MetricDef>(sql`
        SELECT m.*, (SELECT count(*) FROM ranking_snapshots s WHERE s.metric_slug = m.slug AND s.is_current)::int AS snapshot_count
        FROM metric_definitions m ORDER BY m.category, m.name`),
    [] as MetricDef[],
  );
  return rows;
}

export async function getMetric(slug: string): Promise<MetricDef | null> {
  const rows = await safe(() => run<MetricDef>(sql`SELECT m.*, (SELECT count(*) FROM ranking_snapshots s WHERE s.metric_slug = m.slug AND s.is_current)::int AS snapshot_count FROM metric_definitions m WHERE m.slug = ${slug}`), [] as MetricDef[]);
  return rows[0] ?? null;
}

export interface Snapshot {
  id: number;
  metric_id: string;
  metric_slug: string;
  scope_key: string;
  geography: string;
  sex: string;
  age_group: string;
  year: number | null;
  entity_level: string;
  formula_version: string;
  eligible_entities: number;
  inputs_hash: string;
  source_ids: string[];
  is_current: boolean;
  generated_at: Date;
}

export async function snapshotsForMetric(slug: string): Promise<Snapshot[]> {
  return safe(() => run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current ORDER BY (entity_level = 'top') DESC, geography, year DESC NULLS LAST, sex`), [] as Snapshot[]);
}

export async function getSnapshot(slug: string, scopeKey: string | null): Promise<Snapshot | null> {
  const rows = await safe(
    () =>
      run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current AND ${scopeKey ? sql`scope_key = ${scopeKey}` : sql`true`}
        ORDER BY (entity_level = 'top') DESC, (geography = 'WORLD') DESC, year DESC NULLS LAST LIMIT 1`),
    [] as Snapshot[],
  );
  return rows[0] ?? null;
}

export interface RankingRow {
  id: number;
  rank: number;
  previous_rank: number | null;
  cancer_id: string;
  slug: string;
  canonical_name: string;
  value: number;
  unit: string;
  confidence: string;
  percentile: number;
  eligible_entities: number;
  inputs: Record<string, unknown>;
}

export async function rankingRows(snapshotId: number, limit = 1000): Promise<RankingRow[]> {
  return safe(
    () =>
      run<RankingRow>(sql`
        SELECT r.id, r.rank, r.previous_rank, r.cancer_id, c.slug, c.canonical_name, r.value, r.unit, r.confidence, r.percentile, r.eligible_entities, r.inputs
        FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${snapshotId} ORDER BY r.rank, c.canonical_name LIMIT ${limit}`),
    [] as RankingRow[],
  );
}

/** Every current ranking row for one cancer — the "Why this rank?" panel (§183). */
export interface CancerRanking {
  id: number;
  metric_slug: string;
  metric_name: string;
  unit: string;
  scope_key: string;
  formula_version: string;
  formula: string;
  rank: number;
  previous_rank: number | null;
  eligible_entities: number;
  value: number;
  confidence: string;
  percentile: number;
  inputs: Record<string, unknown>;
  generated_at: Date;
  inputs_hash: string;
  source_ids: string[];
}
export async function rankingsForCancer(cancerId: string): Promise<CancerRanking[]> {
  return safe(
    () =>
      run<CancerRanking>(sql`
        SELECT r.id, r.metric_slug, m.name AS metric_name, r.unit, r.scope_key, s.formula_version, m.formula, r.rank, r.previous_rank, r.eligible_entities, r.value, r.confidence, r.percentile, r.inputs, s.generated_at, s.inputs_hash, s.source_ids
        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id AND s.is_current JOIN metric_definitions m ON m.slug = r.metric_slug
        WHERE r.cancer_id = ${cancerId} ORDER BY m.category, m.name, s.scope_key`),
    [] as CancerRanking[],
  );
}

/** Home preview: the best available current top-level snapshot for a list of preferred metrics. */
export async function previewSnapshot(preferred: string[]): Promise<{ snapshot: Snapshot; metric: MetricDef; rows: RankingRow[] } | null> {
  for (const slug of preferred) {
    const snap = await getSnapshot(slug, null);
    if (!snap) continue;
    const metric = await getMetric(slug);
    if (!metric) continue;
    const rows = await rankingRows(snap.id, 10);
    if (rows.length) return { snapshot: snap, metric, rows };
  }
  return null;
}

/**
 * Current ranking rows for several cancers at once (compare page, overview hero). Same shape as
 * rankingsForCancer; callers keep the latest year per (metric, scope-without-year) with pickLatestScopes.
 */
export async function rankingsForCancers(cancerIds: string[]): Promise<Array<CancerRanking & { cancer_id: string }>> {
  if (cancerIds.length === 0) return [];
  return safe(
    () =>
      run<CancerRanking & { cancer_id: string }>(sql`
        SELECT r.id, r.cancer_id, r.metric_slug, m.name AS metric_name, r.unit, r.scope_key, s.formula_version, m.formula, r.rank, r.previous_rank, r.eligible_entities, r.value, r.confidence, r.percentile, r.inputs, s.generated_at, s.inputs_hash, s.source_ids
        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id AND s.is_current JOIN metric_definitions m ON m.slug = r.metric_slug
        WHERE r.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) ORDER BY m.category, m.name, s.scope_key`),
    [],
  );
}

/** Keep, per (cancer, metric, scope without year), only the latest year. Ties keep the first row. */
export function pickLatestScopes<T extends { metric_slug: string; scope_key: string; cancer_id?: string }>(rows: T[]): T[] {
  const yearOf = (k: string) => Number(/year=(\d{4})/.exec(k)?.[1] ?? 0);
  const out = new Map<string, T>();
  for (const r of rows) {
    const key = `${r.cancer_id ?? ''}|${r.metric_slug}|${r.scope_key.replace(/year=[^|]*/, '')}`;
    const cur = out.get(key);
    if (!cur || yearOf(r.scope_key) > yearOf(cur.scope_key)) out.set(key, r);
  }
  return [...out.values()];
}

/**
 * Pick the most relevant current rank for one metric: prefer the requested geography, then the requested
 * entity level (top for top-level entities, all otherwise), then the latest year.
 */
export function bestRankFor<T extends { metric_slug: string; scope_key: string }>(rows: T[], metricSlug: string, opts: { geo?: string; level?: 'top' | 'all' } = {}): T | null {
  const cands = rows.filter((r) => r.metric_slug === metricSlug);
  if (cands.length === 0) return null;
  const score = (k: string) => {
    let s = 0;
    if (opts.geo && k.includes(`geo=${opts.geo}|`)) s += 100;
    if (opts.level && k.endsWith(`level=${opts.level}`)) s += 10;
    s += Number(/year=(\d{4})/.exec(k)?.[1] ?? 0) / 10_000;
    return s;
  };
  return [...cands].sort((a, b) => score(b.scope_key) - score(a.scope_key))[0] ?? null;
}

/** Latest current snapshot of a metric for a geography code (e.g. USA), top level. */
export async function latestSnapshotForGeo(slug: string, geo: string, sex = 'all'): Promise<Snapshot | null> {
  const rows = await safe(
    () => run<Snapshot>(sql`SELECT * FROM ranking_snapshots WHERE metric_slug = ${slug} AND is_current AND geography = ${geo} AND sex = ${sex} AND entity_level = 'top' ORDER BY year DESC NULLS LAST, generated_at DESC LIMIT 1`),
    [] as Snapshot[],
  );
  return rows[0] ?? null;
}

/** Home module: the gap indexes (trial gap, research gap) for a geography, top rows with lineage. */
export async function gapRankings(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', 'research_gap']) {
    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;
}

export async function listSnapshots(limit = 200): Promise<Array<Snapshot & { metric_name: string; row_count: number }>> {
  return safe(
    () => run<Snapshot & { metric_name: string; row_count: number }>(sql`SELECT s.*, m.name AS metric_name, (SELECT count(*) FROM rankings r WHERE r.snapshot_id = s.id)::int AS row_count FROM ranking_snapshots s JOIN metric_definitions m ON m.id = s.metric_id ORDER BY s.generated_at DESC LIMIT ${limit}`),
    [],
  );
}