rootnode-academy/platform/api/migrations/034_phase7_offerings_registrations_agreements.sql

-- Phase 7 §3/§12/§14/§20 — offerings (a purchasable/enrollable delivery of a program),
-- registrations (pre-enrollment application/interest, separate from academic entitlement),
-- agreements (versioned acceptance records, not legal text), and curriculum packages (a named,
-- versioned reference list into EXISTING courses — never a content clone). See
-- docs/OFFERING_AND_REGISTRATION_MODEL.md for the full reasoning and lifecycle diagrams.

-- ═══════════════════════════════════════════════════════════════════════════════════════════
-- curriculum_packages / curriculum_package_items — a named, versioned SUBSET reference into
-- existing courses. curriculum_package_items stores no content of its own, only which
-- course_id(s) (whole courses, matching how V1's own course granularity already works) belong
-- to the package and in what order — exactly the same "reference, never clone" principle
-- COURSE_CONTENT_MODEL.md already established for lab activities referencing scenario_id.
-- ═══════════════════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS curriculum_packages (
  curriculum_package_id  TEXT PRIMARY KEY,          -- slug, e.g. 'winter-linux-systems-v1'
  name                    TEXT NOT NULL,
  program_id              TEXT NOT NULL REFERENCES programs(program_id),
  description             TEXT,
  version                 INTEGER NOT NULL DEFAULT 1,
  status                  TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'retired')),
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS curriculum_package_items (
  curriculum_package_id  TEXT NOT NULL REFERENCES curriculum_packages(curriculum_package_id),
  course_id               TEXT NOT NULL REFERENCES courses(course_id),
  sequence                INTEGER NOT NULL,
  PRIMARY KEY (curriculum_package_id, course_id)
);

-- ═══════════════════════════════════════════════════════════════════════════════════════════
-- offerings — connects a program (+ optional curriculum_package subset) to a delivery format,
-- schedule, capacity, and location/organization/cohort. Status transitions are validated
-- server-side only (offerings.js) — this table has no trigger enforcing them because the
-- application layer is the single writer (admin routes), matching every other status-machine
-- table in this schema (enrollments, classroom_sessions) which also rely on server-side
-- transition logic rather than a DB trigger.
--
-- reserved_seats is a maintained counter, not derived via COUNT(*) at read time — capacity
-- reservation (offerings.js's reserveSeat()) increments it with a single atomic
-- `UPDATE ... WHERE reserved_seats < capacity RETURNING ...`, avoiding the
-- SELECT-count-then-INSERT race explicitly flagged in this phase's authorization.
-- ═══════════════════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS offerings (
  offering_id              TEXT PRIMARY KEY,
  slug                     TEXT NOT NULL UNIQUE,
  name                     TEXT NOT NULL,
  program_id               TEXT NOT NULL REFERENCES programs(program_id),
  curriculum_package_id    TEXT REFERENCES curriculum_packages(curriculum_package_id),
  delivery_mode            TEXT NOT NULL CHECK (delivery_mode IN ('self_paced', 'cohort', 'hybrid', 'camp', 'workshop', 'school', 'after_school', 'homeschool', 'community', 'one_to_one')),
  status                   TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'enrollment_open', 'full', 'in_progress', 'completed', 'cancelled')),
  starts_at                TIMESTAMPTZ,
  ends_at                  TIMESTAMPTZ,
  enrollment_opens_at      TIMESTAMPTZ,
  enrollment_closes_at     TIMESTAMPTZ,
  capacity                 INTEGER,                 -- NULL = unlimited (typical for self_paced)
  reserved_seats           INTEGER NOT NULL DEFAULT 0,
  location_id              TEXT REFERENCES locations(location_id),
  organization_id          TEXT REFERENCES organizations(organization_id),
  cohort_id                TEXT REFERENCES cohorts(cohort_id),
  recommended_min_age      INTEGER,                 -- guidance only, never an authorization gate (Step 21)
  recommended_max_age      INTEGER,
  description              TEXT,
  metadata                 JSONB NOT NULL DEFAULT '{}'::jsonb,   -- room for descriptive price/marketing fields later, never payment data
  version                  INTEGER NOT NULL DEFAULT 1,
  created_by               UUID REFERENCES instructors(instructor_id),
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (capacity IS NULL OR reserved_seats <= capacity)
);
CREATE INDEX IF NOT EXISTS idx_offerings_program ON offerings(program_id);
CREATE INDEX IF NOT EXISTS idx_offerings_status ON offerings(status);

CREATE TABLE IF NOT EXISTS instructor_offerings (
  offering_id     TEXT NOT NULL REFERENCES offerings(offering_id),
  instructor_id   UUID NOT NULL REFERENCES instructors(instructor_id),
  role            TEXT NOT NULL DEFAULT 'teacher',  -- reuses cohort_instructors' own role_in_cohort vocabulary — see admin-offerings-routes.js
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (offering_id, instructor_id)
);

-- ═══════════════════════════════════════════════════════════════════════════════════════════
-- offering_registrations — pre-enrollment interest/application, separate from `enrollments`
-- (academic entitlement, unchanged). A registration MAY exist with no student_id yet (a
-- guardian registering a not-yet-created student) and is converted to a real enrollment only at
-- `confirmed` -> `enrollment_activated`. No payment fields — that boundary belongs to a future
-- commerce layer entirely (Step 23).
-- ═══════════════════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS offering_registrations (
  registration_id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  offering_id               TEXT NOT NULL REFERENCES offerings(offering_id),
  student_id                TEXT REFERENCES students(student_id),          -- null until matched/created
  guardian_id               TEXT REFERENCES guardians(guardian_id),        -- who registered, if a minor
  registrant_display_name   TEXT NOT NULL,       -- captured even before a student record exists
  registrant_email          TEXT,
  is_minor                  BOOLEAN NOT NULL DEFAULT false,
  status                    TEXT NOT NULL DEFAULT 'started' CHECK (status IN ('started', 'submitted', 'reviewed', 'agreements_pending', 'payment_pending', 'confirmed', 'enrollment_activated', 'cancelled')),
  readiness_notes           TEXT,
  accessibility_notes       TEXT,
  emergency_contact_name    TEXT,
  emergency_contact_phone   TEXT,
  enrollment_id             UUID REFERENCES enrollments(enrollment_id),    -- set once converted
  source                    TEXT NOT NULL DEFAULT 'admin',
  external_reference        TEXT,
  metadata                  JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at                TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (NOT is_minor OR guardian_id IS NOT NULL)   -- a minor's registration must always carry a responsible guardian
);
CREATE INDEX IF NOT EXISTS idx_offering_registrations_offering ON offering_registrations(offering_id);
CREATE INDEX IF NOT EXISTS idx_offering_registrations_status ON offering_registrations(status);

-- ═══════════════════════════════════════════════════════════════════════════════════════════
-- agreement_definitions / agreement_acceptances — a versioned ACCEPTANCE RECORD model only.
-- No legal text is authored here (body_summary is a plain-language description for admin UI
-- display, explicitly NOT attorney-approved legal language — see
-- docs/STUDENT_GUARDIAN_DATA_MODEL.md's "requires legal review" list). accepted_version is
-- captured on the acceptance row (not just looked up from the live definition) so a later
-- edit to an agreement's text/version never retroactively reinterprets what someone actually
-- agreed to — the same "session snapshots its own version" principle
-- COURSE_CONTENT_MODEL.md §6b already established for assessments.
-- ═══════════════════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS agreement_definitions (
  agreement_id                  TEXT PRIMARY KEY,     -- slug, e.g. 'participation-agreement'
  title                          TEXT NOT NULL,
  body_summary                  TEXT,                 -- plain-language description, NOT legal text
  version                        INTEGER NOT NULL DEFAULT 1,
  requires_guardian_for_minor    BOOLEAN NOT NULL DEFAULT true,
  status                         TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'retired')),
  created_at                     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS agreement_acceptances (
  acceptance_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agreement_id               TEXT NOT NULL REFERENCES agreement_definitions(agreement_id),
  accepted_version            INTEGER NOT NULL,
  accepted_by_type             TEXT NOT NULL CHECK (accepted_by_type IN ('student', 'guardian', 'instructor_on_behalf')),
  accepted_by_student_id      TEXT REFERENCES students(student_id),
  accepted_by_guardian_id     TEXT REFERENCES guardians(guardian_id),
  registration_id              UUID REFERENCES offering_registrations(registration_id),
  enrollment_id                UUID REFERENCES enrollments(enrollment_id),
  ip_address                   TEXT,
  accepted_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (accepted_by_student_id IS NOT NULL OR accepted_by_guardian_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_agreement_acceptances_registration ON agreement_acceptances(registration_id);