-- Hand-written follow-up to 0000_init.sql:
--   - install required extensions (idempotent),
--   - add CHECK constraints the Drizzle TS schema declares but drizzle-kit
--     didn't carry through to the generated SQL,
--   - reserve a logical schema_version constant via a comment on the cases table.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- pgvector is optional in Phase 1. Skipped here so the migration doesn't fail
-- on droplets without the postgresql-NN-pgvector package; install it via the
-- droplet-side DEPLOY.md before Phase 2 features land.
-- CREATE EXTENSION IF NOT EXISTS vector;

-- users.role allowed values
ALTER TABLE users
  DROP CONSTRAINT IF EXISTS users_role_check;
ALTER TABLE users
  ADD CONSTRAINT users_role_check
  CHECK (role IN ('learner', 'curator', 'admin'));

-- users.training_level allowed values
ALTER TABLE users
  DROP CONSTRAINT IF EXISTS users_training_level_check;
ALTER TABLE users
  ADD CONSTRAINT users_training_level_check
  CHECK (
    training_level IS NULL
    OR training_level IN (
      'nursing_student', 'medical_student', 'resident',
      'crna_student', 'paramedic_student', 'practicing_clinician', 'other'
    )
  );

-- cases.source allowed values
ALTER TABLE cases
  DROP CONSTRAINT IF EXISTS cases_source_check;
ALTER TABLE cases
  ADD CONSTRAINT cases_source_check
  CHECK (source IN ('ptb_xl', 'user_upload', 'curated_other'));

-- cases.status allowed values
ALTER TABLE cases
  DROP CONSTRAINT IF EXISTS cases_status_check;
ALTER TABLE cases
  ADD CONSTRAINT cases_status_check
  CHECK (status IN ('processing', 'ready', 'failed'));

-- cases.visibility allowed values
ALTER TABLE cases
  DROP CONSTRAINT IF EXISTS cases_visibility_check;
ALTER TABLE cases
  ADD CONSTRAINT cases_visibility_check
  CHECK (visibility IN ('public', 'private'));

-- cases.difficulty range
ALTER TABLE cases
  DROP CONSTRAINT IF EXISTS cases_difficulty_check;
ALTER TABLE cases
  ADD CONSTRAINT cases_difficulty_check
  CHECK (difficulty IS NULL OR (difficulty BETWEEN 1 AND 5));

-- concepts.difficulty range
ALTER TABLE concepts
  DROP CONSTRAINT IF EXISTS concepts_difficulty_check;
ALTER TABLE concepts
  ADD CONSTRAINT concepts_difficulty_check
  CHECK (difficulty BETWEEN 1 AND 5);

-- case_concepts.salience allowed values
ALTER TABLE case_concepts
  DROP CONSTRAINT IF EXISTS case_concepts_salience_check;
ALTER TABLE case_concepts
  ADD CONSTRAINT case_concepts_salience_check
  CHECK (salience IN ('primary', 'secondary'));

-- concept_mastery.ewma_score range
ALTER TABLE concept_mastery
  DROP CONSTRAINT IF EXISTS concept_mastery_ewma_check;
ALTER TABLE concept_mastery
  ADD CONSTRAINT concept_mastery_ewma_check
  CHECK (ewma_score >= 0 AND ewma_score <= 1);

COMMENT ON COLUMN cases.canonical_interpretation IS
  'Interpretation schema v1.0; validated against @ekg-tutor/contracts before insert.';
COMMENT ON COLUMN attempts.canonical_snapshot IS
  'Snapshot of canonical_interpretation at attempt time. NEVER overwrite.';
