-- Phase 5B §3 — enrollments: a student's academic access to a program. Deliberately separate
-- from cohort_enrollments (002_cohorts.sql) — a self-paced enrollment has no cohort at all, and
-- a student can hold both kinds independently (see docs/PHASE5B_SELF_PACED_ENROLLMENT.md §4).
--
-- delivery_mode/program_version/curriculum_path are snapshotted from the program AT enrollment
-- time — same versioning principle as assessment_sessions (013_assessments.sql) snapshotting a
-- definition's fields at draw time, so editing a program later never silently changes what an
-- already-enrolled student is being held to.
--
-- status is server-transitioned only (enrollment.js) — pending -> active -> completed/expired,
-- or pending/active -> cancelled. 'expired' is set lazily/derived at read time from
-- access_ends_at (matching classroom.js's derivePresence pattern — no stored value to go
-- stale), so it is intentionally NOT a value the application ever writes directly except when
-- reconciling; see enrollment.js's deriveEnrollmentAccess.
CREATE TABLE IF NOT EXISTS enrollments (
  enrollment_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id          TEXT NOT NULL REFERENCES students(student_id),
  program_id          TEXT NOT NULL REFERENCES programs(program_id),
  delivery_mode       TEXT NOT NULL,               -- snapshot of programs.delivery_mode at enrollment time
  program_version     INTEGER NOT NULL,            -- snapshot of programs.version at enrollment time
  curriculum_path     TEXT NOT NULL,               -- snapshot of programs.curriculum_path at enrollment time
  status               TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'active', 'completed', 'cancelled', 'expired')),
  source               TEXT NOT NULL DEFAULT 'admin',  -- free text provenance ('admin', or whatever the integration event's own "source" said, e.g. 'website') — NEVER used as a metrics label, see metrics.js
  external_event_id   TEXT,                        -- idempotency key for integration-created enrollments; null for admin-created ones
  external_reference  TEXT,                        -- optional business reference (order/payment id) — free text, not unique, audit-only
  starts_at            TIMESTAMPTZ,                 -- null = immediate access once active
  access_ends_at      TIMESTAMPTZ,                 -- null = no expiration
  enrolled_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  activated_at         TIMESTAMPTZ,
  completed_at         TIMESTAMPTZ,
  cancelled_at         TIMESTAMPTZ,
  metadata             JSONB,
  created_by           UUID REFERENCES instructors(instructor_id),  -- null for integration-created enrollments
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_enrollments_student ON enrollments(student_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_program ON enrollments(program_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_status ON enrollments(status);
CREATE INDEX IF NOT EXISTS idx_enrollments_created_by ON enrollments(created_by);

-- Idempotency: the same external_event_id can never create two rows. A unique index (not just
-- app-level "SELECT first") is what actually makes concurrent duplicate submissions safe — see
-- enrollment.js's createFromIntegrationEvent, which relies on this via ON CONFLICT.
CREATE UNIQUE INDEX IF NOT EXISTS idx_enrollments_external_event ON enrollments(external_event_id) WHERE external_event_id IS NOT NULL;

-- At most one non-terminal (pending/active) enrollment per student+program at a time. A
-- completed/cancelled/expired enrollment doesn't block a new one — re-enrollment creates a new
-- row rather than reviving a historical one (§3: "do not corrupt historical records").
CREATE UNIQUE INDEX IF NOT EXISTS idx_enrollments_one_open_per_program ON enrollments(student_id, program_id) WHERE status IN ('pending', 'active');