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

export interface EpiObs {
  id: number;
  geography_id: string;
  geography_name: string;
  geography_slug: string;
  iso3: string | null;
  year: number;
  year_end: number | null;
  sex: string;
  age_group: string;
  metric: string;
  value: number;
  unit: string;
  lower_ci: number | null;
  upper_ci: number | null;
  standard_population: string | null;
  estimate_type: string;
  site_definition: string | null;
  source_id: string;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  ingest_run_id: string | null;
  updated_at: Date;
}

export async function epidemiologyFor(cancerId: string, limit = 2000): Promise<EpiObs[]> {
  return safe(
    () =>
      run<EpiObs>(sql`
        SELECT o.*, g.name AS geography_name, g.slug AS geography_slug, g.iso3, s.slug AS source_slug, s.name AS source_name
        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id
        WHERE o.cancer_id = ${cancerId}
        ORDER BY g.kind, g.name, o.metric, o.sex, o.age_group, o.year LIMIT ${limit}`),
    [] as EpiObs[],
  );
}

export interface SurvObs {
  id: number;
  geography_name: string | null;
  stage: string | null;
  staging_system: string | null;
  sex: string;
  age_group: string;
  diagnosis_period: string | null;
  survival_type: string;
  duration_months: number;
  probability: number | null;
  median_months: number | null;
  cohort_size: number | null;
  lower_ci: number | null;
  upper_ci: number | null;
  method: string | null;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  updated_at: Date;
}

export async function survivalFor(cancerId: string, limit = 1000): Promise<SurvObs[]> {
  return safe(
    () =>
      run<SurvObs>(sql`
        SELECT o.*, g.name AS geography_name, s.slug AS source_slug, s.name AS source_name
        FROM survival_observations o LEFT JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id
        WHERE o.cancer_id = ${cancerId}
        ORDER BY o.survival_type, coalesce(o.stage, ''), o.diagnosis_period, o.duration_months LIMIT ${limit}`),
    [] as SurvObs[],
  );
}

// ---------- Registry-level figures (overview hero, compare page, home) ----------

export interface RegistryAncestor {
  id: string;
  slug: string;
  canonical_name: string;
  depth: number; // hierarchy steps from the requested entity (0 = the entity itself)
}

/**
 * Nearest ancestor that belongs to the mutually exclusive top-level registry set (§246-247), walking every
 * hierarchy type upwards. Returns the entity itself (depth 0) when it is top-level; null when no top-level
 * ancestor exists (e.g. non-malignant branches).
 */
export async function nearestRegistryAncestor(cancerId: string): Promise<RegistryAncestor | null> {
  const rows = await safe(
    () =>
      run<RegistryAncestor>(sql`
        WITH RECURSIVE up AS (
          SELECT c.id, 0 AS depth, ARRAY[c.id]::varchar[] AS path FROM cancers c WHERE c.id = ${cancerId}
          UNION ALL
          SELECT h.parent_id, up.depth + 1, up.path || h.parent_id FROM up JOIN cancer_hierarchy h ON h.child_id = up.id
          WHERE up.depth < 12 AND NOT (h.parent_id = ANY(up.path))
        )
        SELECT c.id, c.slug, c.canonical_name, min(up.depth)::int AS depth
        FROM up JOIN cancers c ON c.id = up.id
        WHERE c.top_level AND c.status = 'active'
        GROUP BY c.id, c.slug, c.canonical_name ORDER BY depth ASC LIMIT 1`),
    [] as RegistryAncestor[],
  );
  return rows[0] ?? null;
}

export interface LatestFigure {
  cancer_id: string;
  metric: string;
  year: number;
  year_end: number | null;
  sex: string;
  value: number;
  unit: string;
  lower_ci: number | null;
  upper_ci: number | null;
  estimate_type: string;
  standard_population: string | null;
  site_definition: string | null;
  geography_name: string;
  geography_slug: string;
  iso3: string | null;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  updated_at: Date | string;
}

/**
 * Latest-year observation per (cancer, metric) for one geography (ISO3) and sex, all ages.
 * One row per cancer × metric; the year may differ between metrics (incidence usually lags mortality).
 */
export async function latestFiguresFor(cancerIds: string[], iso3 = 'USA', sex = 'all'): Promise<LatestFigure[]> {
  if (cancerIds.length === 0) return [];
  return safe(
    () =>
      run<LatestFigure>(sql`
        SELECT DISTINCT ON (o.cancer_id, o.metric) o.cancer_id, o.metric, o.year, o.year_end, o.sex, o.value, o.unit, o.lower_ci, o.upper_ci, o.estimate_type, o.standard_population, o.site_definition,
               g.name AS geography_name, g.slug AS geography_slug, g.iso3, s.slug AS source_slug, s.name AS source_name, o.provenance_id, o.updated_at
        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id
        WHERE g.iso3 = ${iso3} AND o.sex = ${sex} AND o.age_group = 'all'
          AND o.metric IN ('mortality_count','incidence_count','as_mortality_rate','as_incidence_rate')
          AND o.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})
        ORDER BY o.cancer_id, o.metric, o.year DESC, (o.estimate_type = 'observed') DESC, o.updated_at DESC`),
    [] as LatestFigure[],
  );
}

export const EPI_METRIC_LABEL: Record<string, string> = {
  incidence_count: 'New cases',
  incidence_rate: 'Incidence rate (crude)',
  as_incidence_rate: 'Incidence rate (age-standardized)',
  mortality_count: 'Deaths',
  mortality_rate: 'Mortality rate (crude)',
  as_mortality_rate: 'Mortality rate (age-standardized)',
  prevalence: 'Prevalence',
  prevalence_5y: '5-year prevalence',
};