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

/** Metric catalog (CLAUDE.md §250-251): every derived number has a versioned formula. */
export const metricDefinitions = pgTable(
  'metric_definitions',
  {
    id: ciId().primaryKey(), // CI-METRIC-…
    slug: text('slug').notNull(), // active_trials | mortality_incidence_ratio | …
    name: text('name').notNull(),
    description: text('description').notNull(),
    formula: text('formula').notNull(),
    formulaVersion: text('formula_version').notNull(), // ci-mir-v1
    unit: text('unit').notNull(),
    higherIsWorse: boolean('higher_is_worse'),
    aggregation: text('aggregation'),
    validDimensions: text('valid_dimensions').array().notNull().default([]),
    sourceSlugs: text('source_slugs').array().notNull().default([]),
    category: text('category').notNull(), // burden | lethality | trend | rarity | treatment | trials | research | molecular | unmet_need | composite
    eligibility: jsonb('eligibility').$type<Record<string, unknown>>().notNull().default({}),
    experimental: boolean('experimental').notNull().default(false),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('metric_definitions_slug_uq').on(t.slug)],
);

/** Ranking snapshot = one metric × one scope × one formula version at one time (CLAUDE.md §33, §184). */
export const rankingSnapshots = pgTable(
  'ranking_snapshots',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    metricId: ciId('metric_id').notNull(),
    metricSlug: text('metric_slug').notNull(),
    scopeKey: text('scope_key').notNull(), // e.g. "geo=world|sex=all|age=all|year=2022|level=top"
    geography: text('geography').notNull().default('WORLD'),
    sex: text('sex').notNull().default('all'),
    ageGroup: text('age_group').notNull().default('all'),
    year: integer('year'),
    entityLevel: text('entity_level').notNull().default('top'), // top | histology | subtype | rare | all
    formulaVersion: text('formula_version').notNull(),
    eligibleEntities: integer('eligible_entities').notNull(),
    inputsHash: text('inputs_hash').notNull(),
    sourceIds: text('source_ids').array().notNull().default([]),
    isCurrent: boolean('is_current').notNull().default(true),
    generatedAt: timestamp('generated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('ranking_snapshots_lookup_idx').on(t.metricSlug, t.scopeKey, t.isCurrent)],
);

export const rankings = pgTable(
  'rankings',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    snapshotId: integer('snapshot_id').notNull(),
    metricSlug: text('metric_slug').notNull(),
    scopeKey: text('scope_key').notNull(),
    cancerId: ciId('cancer_id').notNull(),
    rank: integer('rank').notNull(),
    eligibleEntities: integer('eligible_entities').notNull(),
    percentile: real('percentile').notNull(),
    value: doublePrecision('value').notNull(),
    unit: text('unit').notNull(),
    confidence: text('confidence').notNull().default('MEDIUM'), // HIGH | MEDIUM | LOW | INSUFFICIENT_DATA
    inputs: jsonb('inputs').$type<Record<string, unknown>>().notNull().default({}), // lineage: observation ids, counts, formula inputs
    breakdown: jsonb('breakdown').$type<Record<string, number>>(), // composite components
    previousRank: integer('previous_rank'),
    generatedAt: timestamp('generated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('rankings_uq').on(t.snapshotId, t.cancerId), index('rankings_cancer_idx').on(t.cancerId, t.metricSlug), index('rankings_lookup_idx').on(t.metricSlug, t.scopeKey, t.rank)],
);

/** Precomputed counters per entity, refreshed deterministically from canonical relations (CLAUDE.md §287). */
export const entityCounters = pgTable(
  'entity_counters',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    trialCount: integer('trial_count').notNull().default(0),
    activeTrialCount: integer('active_trial_count').notNull().default(0),
    recruitingTrialCount: integer('recruiting_trial_count').notNull().default(0),
    phase3TrialCount: integer('phase3_trial_count').notNull().default(0),
    publicationCount: integer('publication_count').notNull().default(0),
    publicationCount5y: integer('publication_count_5y').notNull().default(0),
    publicationCount12m: integer('publication_count_12m').notNull().default(0),
    geneCount: integer('gene_count').notNull().default(0),
    variantCount: integer('variant_count').notNull().default(0),
    drugCount: integer('drug_count').notNull().default(0),
    approvedDrugCount: integer('approved_drug_count').notNull().default(0),
    evidenceCount: integer('evidence_count').notNull().default(0),
    cohortCount: integer('cohort_count').notNull().default(0),
    subtypeCount: integer('subtype_count').notNull().default(0),
    descendantCount: integer('descendant_count').notNull().default(0),
    epidemiologyObsCount: integer('epidemiology_obs_count').notNull().default(0),
    survivalObsCount: integer('survival_obs_count').notNull().default(0),
    completeness: jsonb('completeness').$type<Record<string, number>>().notNull().default({}),
    computedAt: updatedAt(),
  },
  (t) => [uniqueIndex('entity_counters_uq').on(t.entityType, t.entityId), index('entity_counters_trials_idx').on(t.entityType, t.activeTrialCount)],
);

/** Public API keys (CLAUDE.md §190-191). */
export const apiKeys = pgTable(
  'api_keys',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    keyHash: text('key_hash').notNull(),
    prefix: text('prefix').notNull(),
    label: text('label'),
    ownerEmail: text('owner_email'),
    tier: text('tier').notNull().default('free'), // free | research | pro | institutional
    rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(60),
    active: boolean('active').notNull().default(true),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('api_keys_hash_uq').on(t.keyHash)],
);

/** Cached AI syntheses (CLAUDE.md §44, §322) — model, prompt version and record snapshot recorded. */
export const aiAnswers = pgTable(
  'ai_answers',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    kind: text('kind').notNull(), // cancer_summary | ask
    subjectId: text('subject_id'),
    questionHash: text('question_hash').notNull(),
    question: text('question'),
    answer: jsonb('answer').$type<Record<string, unknown>>().notNull(),
    model: text('model').notNull(),
    promptVersion: text('prompt_version').notNull(),
    sourceSnapshot: jsonb('source_snapshot').$type<Record<string, unknown>>().notNull().default({}),
    dataAsOf: timestamp('data_as_of', { withTimezone: true }).notNull(),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('ai_answers_uq').on(t.kind, t.questionHash, t.promptVersion)],
);