import { pgTable, text, integer, bigserial, index, uniqueIndex, real, doublePrecision } from 'drizzle-orm/pg-core';
import { ciId, updatedAt } from './_common.js';

/** Time-aware epidemiology observations (CLAUDE.md §71): never overwrite a year with another. */
export const epidemiologyObservations = pgTable(
  'epidemiology_observations',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cancerId: ciId('cancer_id').notNull(),
    geographyId: ciId('geography_id').notNull(),
    year: integer('year').notNull(),
    yearEnd: integer('year_end'), // for multi-year aggregates (e.g. 2018-2022)
    sex: text('sex').notNull().default('all'), // all | male | female
    ageGroup: text('age_group').notNull().default('all'),
    metric: text('metric').notNull(), // incidence_count | incidence_rate | as_incidence_rate | mortality_count | mortality_rate | as_mortality_rate | prevalence | prevalence_5y
    value: doublePrecision('value').notNull(),
    unit: text('unit').notNull(), // count | per_100k
    lowerCi: doublePrecision('lower_ci'),
    upperCi: doublePrecision('upper_ci'),
    standardPopulation: text('standard_population'), // e.g. "US 2000 standard", "World (Segi)"
    estimateType: text('estimate_type').notNull().default('observed'), // observed | estimated | projected
    siteDefinition: text('site_definition'), // source's own site label / ICD code range
    sourceId: ciId('source_id').notNull(),
    provenanceId: integer('provenance_id').notNull(),
    ingestRunId: text('ingest_run_id'),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('epi_obs_uq').on(t.cancerId, t.geographyId, t.year, t.sex, t.ageGroup, t.metric, t.sourceId, t.siteDefinition),
    index('epi_obs_lookup_idx').on(t.metric, t.geographyId, t.year, t.sex),
    index('epi_obs_cancer_idx').on(t.cancerId, t.metric),
  ],
);

/** Survival requires context (CLAUDE.md §72). */
export const survivalObservations = pgTable(
  'survival_observations',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cancerId: ciId('cancer_id').notNull(),
    geographyId: ciId('geography_id'),
    stage: text('stage'),
    stagingSystem: text('staging_system'),
    sex: text('sex').notNull().default('all'),
    ageGroup: text('age_group').notNull().default('all'),
    diagnosisPeriod: text('diagnosis_period'),
    survivalType: text('survival_type').notNull(), // overall | relative | cause_specific | progression_free | disease_free | net
    durationMonths: integer('duration_months').notNull(),
    probability: real('probability'), // 0..1
    medianMonths: real('median_months'),
    cohortSize: integer('cohort_size'),
    lowerCi: real('lower_ci'),
    upperCi: real('upper_ci'),
    method: text('method'),
    sourceId: ciId('source_id').notNull(),
    provenanceId: integer('provenance_id').notNull(),
    ingestRunId: text('ingest_run_id'),
    updatedAt: updatedAt(),
  },
  (t) => [index('survival_obs_cancer_idx').on(t.cancerId, t.survivalType, t.durationMonths)],
);