-- =============================================================================
-- OCCP System — Online Curriculum Completion Percentage System
-- MMC CAST College of Medicine
--
-- SQLite schema (local prototype / zero-configuration development).
-- The PostgreSQL / Supabase equivalent lives in db/schema.postgres.sql and is
-- kept structurally identical so the same application queries run on both.
--
-- Portability conventions:
--   * primary keys        TEXT  (UUID v4 generated by the application)
--   * timestamps          TEXT  (ISO-8601 UTC, e.g. 2026-08-24T01:02:03.000Z)
--   * booleans            INTEGER 0/1
--   * decimals            REAL
--   * JSON payloads       TEXT
-- =============================================================================

PRAGMA foreign_keys = ON;

-- -----------------------------------------------------------------------------
-- programs — the academic programs / colleges this installation serves
--
-- A program owns its curricula and its students. A user whose program_id is NULL
-- sees every program (Super Administrators, and any deliberately global account);
-- a user with a program_id can only reach that program's records.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS programs (
  id          TEXT PRIMARY KEY,
  code        TEXT NOT NULL,
  name        TEXT NOT NULL,
  active      INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  notes       TEXT,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_programs_code ON programs(code);

-- -----------------------------------------------------------------------------
-- profiles — application users and their role
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS profiles (
  id              TEXT PRIMARY KEY,
  email           TEXT NOT NULL,
  email_key       TEXT NOT NULL,
  full_name       TEXT NOT NULL,
  role            TEXT NOT NULL CHECK (role IN ('SUPER_ADMIN','DEAN_ADMIN','ENCODER','VIEWER')),
  password_hash   TEXT NOT NULL,
  password_salt   TEXT NOT NULL,
  active          INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  program_id      TEXT REFERENCES programs(id),
  created_at      TEXT NOT NULL,
  updated_at      TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_profiles_email_key ON profiles(email_key);
CREATE INDEX IF NOT EXISTS ix_profiles_program ON profiles(program_id);

-- -----------------------------------------------------------------------------
-- sessions — server-side session store for cookie authentication
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
  id           TEXT PRIMARY KEY,
  user_id      TEXT NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  created_at   TEXT NOT NULL,
  expires_at   TEXT NOT NULL,
  user_agent   TEXT
);
CREATE INDEX IF NOT EXISTS ix_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS ix_sessions_expires ON sessions(expires_at);

-- -----------------------------------------------------------------------------
-- auth_attempts — basic rate limiting for login and import endpoints
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_attempts (
  id          TEXT PRIMARY KEY,
  bucket      TEXT NOT NULL,
  created_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_auth_attempts_bucket ON auth_attempts(bucket, created_at);

-- -----------------------------------------------------------------------------
-- curricula — curriculum versions (2023 / 2024 / 2025 / approved source set)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS curricula (
  id                           TEXT PRIMARY KEY,
  code                         TEXT NOT NULL,
  name                         TEXT NOT NULL,
  effective_year               INTEGER,
  version                      TEXT NOT NULL DEFAULT '1',
  status                       TEXT NOT NULL DEFAULT 'DRAFT'
                                 CHECK (status IN ('DRAFT','DRAFT_REVIEW','PUBLISHED','ARCHIVED')),
  year_long_credit_policy      TEXT NOT NULL DEFAULT 'ON_FINAL_PASS'
                                 CHECK (year_long_credit_policy IN ('ON_FINAL_PASS','PER_COMPONENT')),
  institutional_type           TEXT,
  source_name                  TEXT,
  source_workbook              TEXT,
  primary_effective_ay         TEXT,
  secondary_sheet_effective_ay TEXT,
  mapping_status               TEXT,
  source_total_units_declared  REAL,
  clerkship_reference_days     INTEGER,
  notes                        TEXT,
  published_at                 TEXT,
  published_by                 TEXT REFERENCES profiles(id),
  program_id                   TEXT REFERENCES programs(id),
  created_at                   TEXT NOT NULL,
  updated_at                   TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_curricula_code ON curricula(code);
CREATE UNIQUE INDEX IF NOT EXISTS ux_curricula_institutional_type
  ON curricula(institutional_type) WHERE institutional_type IS NOT NULL;
CREATE INDEX IF NOT EXISTS ix_curricula_status ON curricula(status);
CREATE INDEX IF NOT EXISTS ix_curricula_program ON curricula(program_id);

-- -----------------------------------------------------------------------------
-- curriculum_courses — unit-bearing curriculum requirements
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS curriculum_courses (
  id                          TEXT PRIMARY KEY,
  curriculum_id               TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE,
  course_code                 TEXT NOT NULL,
  course_code_key             TEXT NOT NULL,
  course_title                TEXT NOT NULL,
  units                       REAL NOT NULL CHECK (units >= 0),
  lecture_units               REAL CHECK (lecture_units IS NULL OR lecture_units >= 0),
  laboratory_units            REAL CHECK (laboratory_units IS NULL OR laboratory_units >= 0),
  hours_per_week              REAL CHECK (hours_per_week IS NULL OR hours_per_week >= 0),
  intended_year               INTEGER NOT NULL CHECK (intended_year >= 1),
  term                        TEXT NOT NULL,
  category                    TEXT,
  prerequisite_text           TEXT,
  required                    INTEGER NOT NULL DEFAULT 1 CHECK (required IN (0,1)),
  counts_toward_program_units INTEGER NOT NULL DEFAULT 1 CHECK (counts_toward_program_units IN (0,1)),
  is_year_long_component      INTEGER NOT NULL DEFAULT 0 CHECK (is_year_long_component IN (0,1)),
  year_long_group_id          TEXT,
  component_term              TEXT,
  final_grade_term            TEXT,
  sort_order                  INTEGER NOT NULL DEFAULT 0,
  active                      INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  notes                       TEXT,
  source_sheet                TEXT,
  source_row                  INTEGER,
  source_notes                TEXT,
  created_at                  TEXT NOT NULL,
  updated_at                  TEXT NOT NULL
);
-- Course codes are unique per curriculum version AND academic period only.
-- They are NOT assumed globally unique (SPEC 52.9 #4).
CREATE UNIQUE INDEX IF NOT EXISTS ux_curriculum_courses_scope
  ON curriculum_courses(curriculum_id, course_code_key, intended_year, term);
CREATE INDEX IF NOT EXISTS ix_curriculum_courses_curriculum ON curriculum_courses(curriculum_id);
CREATE INDEX IF NOT EXISTS ix_curriculum_courses_code_key ON curriculum_courses(course_code_key);
CREATE INDEX IF NOT EXISTS ix_curriculum_courses_group ON curriculum_courses(year_long_group_id);

-- -----------------------------------------------------------------------------
-- non_unit_requirements — summer training and clinical clerkship
-- These NEVER enter the credited-unit numerator or the denominator.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS non_unit_requirements (
  id                 TEXT PRIMARY KEY,
  curriculum_id      TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE,
  requirement_type   TEXT NOT NULL
                       CHECK (requirement_type IN ('SUMMER_TRAINING','CLERKSHIP_CORE','CLERKSHIP_ELECTIVE')),
  code               TEXT,
  title              TEXT NOT NULL,
  academic_year      INTEGER,
  term               TEXT,
  required_hours     REAL CHECK (required_hours IS NULL OR required_hours >= 0),
  required_days      REAL CHECK (required_days IS NULL OR required_days >= 0),
  required_months    REAL CHECK (required_months IS NULL OR required_months >= 0),
  elective           INTEGER NOT NULL DEFAULT 0 CHECK (elective IN (0,1)),
  elective_group     TEXT,
  minimum_selections INTEGER,
  prerequisite_text  TEXT,
  sort_order         INTEGER NOT NULL DEFAULT 0,
  active             INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  source_sheet       TEXT,
  source_row         INTEGER,
  notes              TEXT,
  created_at         TEXT NOT NULL,
  updated_at         TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_non_unit_curriculum ON non_unit_requirements(curriculum_id);

-- -----------------------------------------------------------------------------
-- curriculum_assignment_rules — configurable student-number resolver
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS curriculum_assignment_rules (
  id                    TEXT PRIMARY KEY,
  name                  TEXT NOT NULL,
  curriculum_id         TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE,
  rule_type             TEXT NOT NULL CHECK (rule_type IN ('PREFIX','REGEX','EXACT','NUMERIC_RANGE')),
  prefix                TEXT,
  regex_pattern         TEXT,
  exact_student_number  TEXT,
  numeric_start         REAL,
  numeric_end           REAL,
  priority              INTEGER NOT NULL DEFAULT 100,
  active                INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  description           TEXT,
  created_at            TEXT NOT NULL,
  updated_at            TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_rules_active_priority ON curriculum_assignment_rules(active, priority);

-- -----------------------------------------------------------------------------
-- students
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS students (
  id                            TEXT PRIMARY KEY,
  student_number                TEXT NOT NULL,
  student_number_key            TEXT NOT NULL,
  last_name                     TEXT NOT NULL,
  first_name                    TEXT NOT NULL,
  middle_name                   TEXT,
  suffix                        TEXT,
  premed_course                 TEXT,
  premed_school                 TEXT,
  graduation_year               INTEGER,
  gwa                           TEXT,
  nmat_score                    REAL,
  nmat_year                     INTEGER,
  curriculum_id                 TEXT REFERENCES curricula(id),
  curriculum_assignment_method  TEXT NOT NULL DEFAULT 'UNRESOLVED'
                                  CHECK (curriculum_assignment_method IN ('AUTO','MANUAL','UNRESOLVED')),
  curriculum_assignment_rule_id TEXT REFERENCES curriculum_assignment_rules(id) ON DELETE SET NULL,
  curriculum_override_reason    TEXT,
  active                        INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
  notes                         TEXT,
  last_assessed_at              TEXT,
  updated_by                    TEXT REFERENCES profiles(id),
  program_id                    TEXT REFERENCES programs(id),
  created_at                    TEXT NOT NULL,
  updated_at                    TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_students_number_key ON students(student_number_key);
CREATE INDEX IF NOT EXISTS ix_students_curriculum ON students(curriculum_id);
CREATE INDEX IF NOT EXISTS ix_students_last_name ON students(last_name);
CREATE INDEX IF NOT EXISTS ix_students_first_name ON students(first_name);
CREATE INDEX IF NOT EXISTS ix_students_active ON students(active);
CREATE INDEX IF NOT EXISTS ix_students_program ON students(program_id);

-- -----------------------------------------------------------------------------
-- student_course_assessments — one active row per (student, curriculum course)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS student_course_assessments (
  id                               TEXT PRIMARY KEY,
  student_id                       TEXT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  curriculum_course_id             TEXT NOT NULL REFERENCES curriculum_courses(id) ON DELETE CASCADE,
  grade_raw                        TEXT,
  grade_numeric                    REAL,
  status                           TEXT NOT NULL DEFAULT 'NOT_TAKEN'
                                     CHECK (status IN ('NOT_TAKEN','IN_PROGRESS','PASSED','CREDITED','FAILED','FOR_VALIDATION')),
  equivalent_source_school         TEXT,
  equivalent_course_code           TEXT,
  equivalent_course_title          TEXT,
  equivalent_source_units          REAL CHECK (equivalent_source_units IS NULL OR equivalent_source_units >= 0),
  approved_credited_units_override REAL CHECK (approved_credited_units_override IS NULL OR approved_credited_units_override >= 0),
  remarks                          TEXT,
  updated_by                       TEXT REFERENCES profiles(id),
  created_at                       TEXT NOT NULL,
  updated_at                       TEXT NOT NULL
);
-- Structural guarantee that a curriculum requirement can never double count.
CREATE UNIQUE INDEX IF NOT EXISTS ux_assessment_student_course
  ON student_course_assessments(student_id, curriculum_course_id);
CREATE INDEX IF NOT EXISTS ix_assessment_student ON student_course_assessments(student_id);
CREATE INDEX IF NOT EXISTS ix_assessment_course ON student_course_assessments(curriculum_course_id);
CREATE INDEX IF NOT EXISTS ix_assessment_status ON student_course_assessments(status);

-- -----------------------------------------------------------------------------
-- student_non_unit_progress — summer training / clerkship tracking
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS student_non_unit_progress (
  id                      TEXT PRIMARY KEY,
  student_id              TEXT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  non_unit_requirement_id TEXT NOT NULL REFERENCES non_unit_requirements(id) ON DELETE CASCADE,
  selected                INTEGER NOT NULL DEFAULT 0 CHECK (selected IN (0,1)),
  status                  TEXT NOT NULL DEFAULT 'NOT_STARTED'
                            CHECK (status IN ('NOT_STARTED','IN_PROGRESS','COMPLETED','WAIVED')),
  completed_hours         REAL,
  completed_months        REAL,
  remarks                 TEXT,
  updated_by              TEXT REFERENCES profiles(id),
  created_at              TEXT NOT NULL,
  updated_at              TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_non_unit_progress
  ON student_non_unit_progress(student_id, non_unit_requirement_id);
CREATE INDEX IF NOT EXISTS ix_non_unit_progress_student ON student_non_unit_progress(student_id);

-- -----------------------------------------------------------------------------
-- audit_logs — append only
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_logs (
  id            TEXT PRIMARY KEY,
  actor_user_id TEXT REFERENCES profiles(id),
  actor_email   TEXT,
  action        TEXT NOT NULL,
  entity_type   TEXT NOT NULL,
  entity_id     TEXT,
  before_json   TEXT,
  after_json    TEXT,
  reason        TEXT,
  program_id    TEXT REFERENCES programs(id),
  created_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_audit_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS ix_audit_created ON audit_logs(created_at);
CREATE INDEX IF NOT EXISTS ix_audit_actor ON audit_logs(actor_user_id);
CREATE INDEX IF NOT EXISTS ix_audit_program ON audit_logs(program_id);

-- -----------------------------------------------------------------------------
-- import_jobs
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS import_jobs (
  id                  TEXT PRIMARY KEY,
  import_type         TEXT NOT NULL CHECK (import_type IN ('CURRICULUM','STUDENT','ASSESSMENT')),
  file_name           TEXT NOT NULL,
  status              TEXT NOT NULL,
  total_rows          INTEGER NOT NULL DEFAULT 0,
  accepted_rows       INTEGER NOT NULL DEFAULT 0,
  rejected_rows       INTEGER NOT NULL DEFAULT 0,
  uploaded_by         TEXT REFERENCES profiles(id),
  error_summary_json  TEXT,
  created_at          TEXT NOT NULL,
  completed_at        TEXT
);
CREATE INDEX IF NOT EXISTS ix_import_jobs_created ON import_jobs(created_at);

-- -----------------------------------------------------------------------------
-- curriculum_validation_issues — source discrepancies, never silently discarded
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS curriculum_validation_issues (
  id              TEXT PRIMARY KEY,
  curriculum_id   TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE,
  code            TEXT NOT NULL,
  severity        TEXT NOT NULL CHECK (severity IN ('ERROR','WARNING','INFO')),
  message         TEXT NOT NULL,
  details_json    TEXT,
  acknowledged    INTEGER NOT NULL DEFAULT 0 CHECK (acknowledged IN (0,1)),
  acknowledged_by TEXT REFERENCES profiles(id),
  acknowledged_at TEXT,
  created_at      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_validation_curriculum ON curriculum_validation_issues(curriculum_id);

-- -----------------------------------------------------------------------------
-- system_settings — key/value institutional configuration
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_settings (
  key        TEXT PRIMARY KEY,
  value      TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  updated_by TEXT REFERENCES profiles(id)
);
