import 'server-only';
import { run, sql, safe } from '@/lib/db';
import type { CancerCore, Counters } from '@/lib/queries/cancers';
import { latestFiguresFor, nearestRegistryAncestor, type LatestFigure, type RegistryAncestor } from '@/lib/queries/epidemiology';
import { rankingsForCancers, pickLatestScopes, type CancerRanking } from '@/lib/queries/rankings';

/**
 * Compare engine (SPEC §100): side-by-side facts for 2–4 cancers. Only facts that exist in the database are
 * shown; registry figures for entities below the top level fall back to the nearest top-level ancestor and
 * are labelled as such (see /methodology#compare).
 */

export const COMPARE_MIN = 2;
export const COMPARE_MAX = 4;

export interface CompareEntity {
  cancer: CancerCore;
  parents: Array<{ slug: string; canonical_name: string; hierarchy_type: string }>;
  counters: Counters | null;
  registry: RegistryAncestor | null; // entity whose observations are shown (self when top-level)
  figures: Map<string, LatestFigure>; // metric → latest observation (sex=all, USA)
  ranks: Array<CancerRanking & { cancer_id: string }>; // latest year per scope
}

export function parseCompareIds(raw: string | undefined): string[] {
  if (!raw) return [];
  const seen = new Set<string>();
  const out: string[] = [];
  for (const part of raw.split(',')) {
    const s = part.trim().toLowerCase();
    if (!/^[a-z0-9][a-z0-9-]{0,120}$/.test(s) || seen.has(s)) continue;
    seen.add(s);
    out.push(s);
    if (out.length >= COMPARE_MAX) break;
  }
  return out;
}

export async function resolveCancers(slugs: string[]): Promise<CancerCore[]> {
  if (slugs.length === 0) return [];
  const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE slug IN (${sql.join(slugs.map((s) => sql`${s}`), sql`, `)}) AND status <> 'merged'`), [] as CancerCore[]);
  const bySlug = new Map(rows.map((r) => [r.slug, r]));
  return slugs.map((s) => bySlug.get(s)).filter((r): r is CancerCore => !!r);
}

export async function loadCompare(slugs: string[]): Promise<CompareEntity[]> {
  const cancers = await resolveCancers(slugs);
  if (cancers.length === 0) return [];
  const ids = cancers.map((c) => c.id);
  const [parents, counters, ancestors] = await Promise.all([
    safe(
      () =>
        run<{ child_id: string; slug: string; canonical_name: string; hierarchy_type: string }>(sql`
          SELECT h.child_id, p.slug, p.canonical_name, h.hierarchy_type FROM cancer_hierarchy h JOIN cancers p ON p.id = h.parent_id
          WHERE h.child_id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)}) ORDER BY h.hierarchy_type, p.canonical_name`),
      [],
    ),
    safe(() => run<Counters & { entity_id: string }>(sql`SELECT * FROM entity_counters WHERE entity_type = 'cancer' AND entity_id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`), []),
    Promise.all(cancers.map((c) => (c.top_level ? Promise.resolve<RegistryAncestor | null>({ id: c.id, slug: c.slug, canonical_name: c.canonical_name, depth: 0 }) : nearestRegistryAncestor(c.id)))),
  ]);
  const registryIds = [...new Set(ancestors.filter((a): a is RegistryAncestor => !!a).map((a) => a.id))];
  const rankIds = [...new Set([...ids, ...registryIds])];
  const [figures, ranks] = await Promise.all([latestFiguresFor(registryIds, 'USA', 'all'), rankingsForCancers(rankIds)]);
  const latestRanks = pickLatestScopes(ranks);
  const countersById = new Map(counters.map((c) => [c.entity_id, c]));
  return cancers.map((c, i) => {
    const registry = ancestors[i] ?? null;
    const figs = new Map<string, LatestFigure>();
    if (registry) for (const f of figures) if (f.cancer_id === registry.id) figs.set(f.metric, f);
    return {
      cancer: c,
      parents: parents.filter((p) => p.child_id === c.id).slice(0, 4),
      counters: countersById.get(c.id) ?? null,
      registry,
      figures: figs,
      ranks: latestRanks.filter((r) => r.cancer_id === c.id || (registry && registry.depth > 0 && r.cancer_id === registry.id && /^(mortality_count|incidence_count|as_mortality_rate|as_incidence_rate|mortality_incidence_ratio|trial_gap|research_gap)$/.test(r.metric_slug))),
    };
  });
}