-- =====================================================================
-- EliteSchools v2 — Structural Schema (Nigerian School System)
-- Engine: InnoDB | Charset: utf8mb4 | MySQL 8.0+
-- Design principles:
--   1. Every relationship is a real FK.
--   2. Every transactional table has created_by / timestamps.
--   3. Every write-worthy action is captured in activity_logs.
--   4. Money is decimal(12,2), never int.
--   5. Nigerian-specific lookups (states, lgas) replace free text.
-- =====================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+01:00"; -- WAT

-- ---------------------------------------------------------------------
-- 1. LOCATION LOOKUPS (Nigeria-specific)
-- ---------------------------------------------------------------------

CREATE TABLE states (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  geo_zone ENUM('North Central','North East','North West','South East','South South','South West') NOT NULL,
  UNIQUE KEY uq_state_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lgas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  state_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  UNIQUE KEY uq_state_lga (state_id, name),
  CONSTRAINT fk_lga_state FOREIGN KEY (state_id) REFERENCES states(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 2. RBAC — IDENTITY, ROLES, PERMISSIONS
-- ---------------------------------------------------------------------

CREATE TABLE roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,               -- super_admin, head_teacher, class_teacher,
                                             -- subject_teacher, accountant, admin_staff,
                                             -- student, guardian
  role_type ENUM('staff','student','guardian') NOT NULL,
  is_system_role TINYINT(1) NOT NULL DEFAULT 0, -- cannot be deleted (e.g. super_admin)
  UNIQUE KEY uq_role_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(100) NOT NULL,              -- e.g. results.enter, fees.record_payment
  module VARCHAR(50) NOT NULL,             -- results, fees, students, exams, hr...
  description VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_permission_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','suspended','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uq_username (username),
  UNIQUE KEY uq_email (email),
  CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  login_identifier VARCHAR(150) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent TEXT,
  success TINYINT(1) DEFAULT NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_login_identifier (login_identifier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 3. STAFF & GUARDIAN PROFILES
-- ---------------------------------------------------------------------

CREATE TABLE staff_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  staff_no VARCHAR(30) NOT NULL,
  firstname VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  othername VARCHAR(50) DEFAULT NULL,
  dob DATE DEFAULT NULL,
  gender ENUM('male','female') DEFAULT NULL,
  state_id INT UNSIGNED DEFAULT NULL,
  lga_id INT UNSIGNED DEFAULT NULL,
  address TEXT,
  qualification VARCHAR(100) DEFAULT NULL,
  passport_url VARCHAR(255) DEFAULT NULL,
  employment_date DATE DEFAULT NULL,
  employment_status ENUM('active','on_leave','terminated','retired') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uq_staff_no (staff_no),
  CONSTRAINT fk_staffprofile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_staffprofile_state FOREIGN KEY (state_id) REFERENCES states(id),
  CONSTRAINT fk_staffprofile_lga FOREIGN KEY (lga_id) REFERENCES lgas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE guardians (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,   -- NULL until guardian activates portal access
  fullname VARCHAR(150) NOT NULL,
  occupation VARCHAR(100) DEFAULT NULL,
  phone VARCHAR(20) NOT NULL,
  alt_phone VARCHAR(20) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  address TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guardian_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 4. ACADEMIC STRUCTURE
-- ---------------------------------------------------------------------

CREATE TABLE sessions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,              -- e.g. 2025/2026
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_session_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE terms (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  session_id INT UNSIGNED NOT NULL,
  name ENUM('First Term','Second Term','Third Term') NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  next_term_begins DATE DEFAULT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_session_term (session_id, name),
  CONSTRAINT fk_term_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class_levels (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,              -- JSS2, SSS1, Primary 4...
  category ENUM('creche','nursery','primary','jss','sss') NOT NULL,
  sequence_order INT NOT NULL,            -- for promotion ordering
  adm_no_initial VARCHAR(20) DEFAULT NULL,
  UNIQUE KEY uq_level_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE classes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_level_id INT UNSIGNED NOT NULL,
  arm VARCHAR(30) DEFAULT NULL,           -- "Gold", "A", "Diamond"
  class_teacher_id BIGINT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_class_level FOREIGN KEY (class_level_id) REFERENCES class_levels(id),
  CONSTRAINT fk_class_teacher FOREIGN KEY (class_teacher_id) REFERENCES staff_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subjects (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) DEFAULT NULL,
  UNIQUE KEY uq_subject_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class_subjects (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED DEFAULT NULL,
  session_id INT UNSIGNED NOT NULL,
  UNIQUE KEY uq_class_subject_session (class_id, subject_id, session_id),
  CONSTRAINT fk_cs_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_cs_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_cs_teacher FOREIGN KEY (teacher_id) REFERENCES staff_profiles(id),
  CONSTRAINT fk_cs_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 5. STUDENTS & ENROLLMENT
-- ---------------------------------------------------------------------

CREATE TABLE student_clubs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_houses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,
  reg_number VARCHAR(30) NOT NULL,
  firstname VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  othername VARCHAR(50) DEFAULT NULL,
  dob DATE DEFAULT NULL,
  gender ENUM('male','female') NOT NULL,
  religion ENUM('christianity','islam','other') DEFAULT NULL,
  state_id INT UNSIGNED DEFAULT NULL,
  lga_id INT UNSIGNED DEFAULT NULL,
  nationality VARCHAR(50) NOT NULL DEFAULT 'Nigerian',
  address TEXT,
  health_conditions VARCHAR(255) DEFAULT NULL,
  passport_url VARCHAR(255) DEFAULT NULL,
  club_id INT UNSIGNED DEFAULT NULL,
  house_id INT UNSIGNED DEFAULT NULL,
  waec_exam_no VARCHAR(30) DEFAULT NULL,   -- SSS3 external exam registration
  neco_exam_no VARCHAR(30) DEFAULT NULL,
  status ENUM('active','withdrawn','graduated','suspended') NOT NULL DEFAULT 'active',
  admitted_date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uq_reg_number (reg_number),
  CONSTRAINT fk_student_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_student_state FOREIGN KEY (state_id) REFERENCES states(id),
  CONSTRAINT fk_student_lga FOREIGN KEY (lga_id) REFERENCES lgas(id),
  CONSTRAINT fk_student_club FOREIGN KEY (club_id) REFERENCES student_clubs(id) ON DELETE SET NULL,
  CONSTRAINT fk_student_house FOREIGN KEY (house_id) REFERENCES student_houses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_guardians (
  student_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NOT NULL,
  relationship VARCHAR(30) DEFAULT NULL,   -- father, mother, uncle...
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (student_id, guardian_id),
  CONSTRAINT fk_sg_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_sg_guardian FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- History-preserving enrollment: replaces the single class_id-on-students problem
CREATE TABLE student_enrollments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  status ENUM('active','promoted','repeated','withdrawn','transferred') NOT NULL DEFAULT 'active',
  enrolled_at DATE NOT NULL,
  UNIQUE KEY uq_student_session (student_id, session_id),
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_enroll_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_attendance (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present','absent','late','excused') NOT NULL,
  recorded_by BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_student_date (student_id, attendance_date),
  CONSTRAINT fk_att_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_att_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_att_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_att_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 6. RESULTS (single source of truth)
-- ---------------------------------------------------------------------

CREATE TABLE grading_systems (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  grade VARCHAR(5) NOT NULL,
  min_score DECIMAL(5,2) NOT NULL,
  max_score DECIMAL(5,2) NOT NULL,
  remark VARCHAR(50) NOT NULL,             -- Excellent, Good, Pass, Fail
  UNIQUE KEY uq_grade (grade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE marks_allocations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ca1_max INT NOT NULL,
  ca2_max INT NOT NULL,
  exam_max INT NOT NULL,
  total_max INT GENERATED ALWAYS AS (ca1_max + ca2_max + exam_max) STORED,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  marks_allocation_id INT UNSIGNED NOT NULL,
  ca1 DECIMAL(5,2) DEFAULT 0,
  ca2 DECIMAL(5,2) DEFAULT 0,
  exam DECIMAL(5,2) DEFAULT 0,
  total DECIMAL(5,2) GENERATED ALWAYS AS (ca1 + ca2 + exam) STORED,
  grade_id INT UNSIGNED DEFAULT NULL,
  entered_by BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  status ENUM('draft','submitted','approved') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_result (student_id, subject_id, term_id, session_id),
  CONSTRAINT fk_result_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_result_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_result_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_result_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_result_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_result_alloc FOREIGN KEY (marks_allocation_id) REFERENCES marks_allocations(id),
  CONSTRAINT fk_result_grade FOREIGN KEY (grade_id) REFERENCES grading_systems(id),
  CONSTRAINT fk_result_enteredby FOREIGN KEY (entered_by) REFERENCES users(id),
  CONSTRAINT fk_result_approvedby FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE behavioral_assessments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  punctuality ENUM('A','B','C','D','E') DEFAULT NULL,
  neatness ENUM('A','B','C','D','E') DEFAULT NULL,
  relationship_with_others ENUM('A','B','C','D','E') DEFAULT NULL,
  regard_for_authority ENUM('A','B','C','D','E') DEFAULT NULL,
  honesty ENUM('A','B','C','D','E') DEFAULT NULL,
  class_teacher_comment TEXT,
  head_teacher_comment TEXT,
  recorded_by BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_behavioral (student_id, term_id, session_id),
  CONSTRAINT fk_beh_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_beh_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_beh_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_beh_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_beh_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cache tables — populated by scheduled jobs, never hand-written.
CREATE TABLE class_positions_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  total_score DECIMAL(7,2) NOT NULL,
  average_score DECIMAL(5,2) NOT NULL,
  class_position INT NOT NULL,
  computed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_position (student_id, term_id, session_id),
  CONSTRAINT fk_poscache_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_poscache_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_poscache_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_poscache_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE annual_result_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  first_term_avg DECIMAL(5,2) DEFAULT NULL,
  second_term_avg DECIMAL(5,2) DEFAULT NULL,
  third_term_avg DECIMAL(5,2) DEFAULT NULL,
  annual_average DECIMAL(5,2) DEFAULT NULL,
  annual_position INT DEFAULT NULL,
  computed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_annual (student_id, session_id),
  CONSTRAINT fk_annualcache_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_annualcache_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_annualcache_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE result_pins (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pin VARCHAR(20) NOT NULL,
  serial_number VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED DEFAULT NULL, -- NULL until bound to a student at first use
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  usage_limit INT NOT NULL DEFAULT 5,
  used_count INT NOT NULL DEFAULT 0,
  status ENUM('unused','active','used_up','revoked') NOT NULL DEFAULT 'unused',
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME DEFAULT NULL,
  UNIQUE KEY uq_serial (serial_number),
  KEY idx_pin (pin),
  CONSTRAINT fk_pin_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_pin_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_pin_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_pin_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 7. FEES
-- ---------------------------------------------------------------------

CREATE TABLE fee_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL               -- Tuition, PTA Levy, Uniform, Feeding...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fee_structures (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_level_id INT UNSIGNED NOT NULL,
  fee_item_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  is_mandatory TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fee_structure (class_level_id, fee_item_id, term_id, session_id),
  CONSTRAINT fk_fs_level FOREIGN KEY (class_level_id) REFERENCES class_levels(id),
  CONSTRAINT fk_fs_item FOREIGN KEY (fee_item_id) REFERENCES fee_items(id),
  CONSTRAINT fk_fs_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_fs_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fee_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  fee_structure_id INT UNSIGNED NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  payment_method ENUM('cash','bank_transfer','card','pos','ussd') NOT NULL,
  teller_no VARCHAR(50) DEFAULT NULL,
  payment_status ENUM('cleared','owing') NOT NULL DEFAULT 'owing',
  recorded_by BIGINT UNSIGNED NOT NULL,
  paid_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_fp_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_fp_structure FOREIGN KEY (fee_structure_id) REFERENCES fee_structures(id),
  CONSTRAINT fk_fp_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 8. CBT / ONLINE EXAMS  (term_id + session_id on every exam-scoped table)
-- ---------------------------------------------------------------------

CREATE TABLE exams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  duration_minutes INT NOT NULL,
  total_marks INT NOT NULL,
  pass_mark INT DEFAULT NULL,
  shuffle_questions TINYINT(1) NOT NULL DEFAULT 1,
  max_malpractice_warnings INT NOT NULL DEFAULT 3,
  status ENUM('draft','scheduled','ongoing','closed') NOT NULL DEFAULT 'draft',
  scheduled_start DATETIME DEFAULT NULL,
  scheduled_end DATETIME DEFAULT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_exam_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_exam_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_exam_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_exam_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_exam_creator FOREIGN KEY (created_by) REFERENCES users(id),
  KEY idx_exam_term_session (term_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_questions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  exam_id BIGINT UNSIGNED NOT NULL,
  question_text TEXT NOT NULL,
  question_image VARCHAR(255) DEFAULT NULL,
  question_type ENUM('mcq','theory') NOT NULL DEFAULT 'mcq',
  option_a TEXT, option_b TEXT, option_c TEXT, option_d TEXT, option_e TEXT,
  correct_answer VARCHAR(255) DEFAULT NULL,
  marks INT NOT NULL DEFAULT 1,
  CONSTRAINT fk_eq_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_exam_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME DEFAULT NULL,
  score DECIMAL(6,2) DEFAULT NULL,
  status ENUM('in_progress','submitted','auto_submitted','disqualified') NOT NULL DEFAULT 'in_progress',
  UNIQUE KEY uq_student_exam (student_id, exam_id),
  CONSTRAINT fk_ses_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_ses_exam FOREIGN KEY (exam_id) REFERENCES exams(id),
  CONSTRAINT fk_ses_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_ses_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  KEY idx_ses_term_session (term_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_answers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_exam_session_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  answer TEXT,
  is_correct TINYINT(1) DEFAULT NULL,
  answered_at DATETIME DEFAULT NULL,
  UNIQUE KEY uq_session_question (student_exam_session_id, question_id),
  CONSTRAINT fk_ans_session FOREIGN KEY (student_exam_session_id) REFERENCES student_exam_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_ans_question FOREIGN KEY (question_id) REFERENCES exam_questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE malpractice_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_exam_session_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('tab_switch','copy_paste','right_click','fullscreen_exit','multiple_faces','other') NOT NULL,
  warning_count INT NOT NULL DEFAULT 1,
  logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_mal_session FOREIGN KEY (student_exam_session_id) REFERENCES student_exam_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 9. UNIVERSAL ACTIVITY LOG
--    Every controller/service action writes here — the single audit trail
--    for the whole platform (who did what, to what, when, from where).
-- ---------------------------------------------------------------------

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,       -- NULL for unauthenticated/system events
  role_name VARCHAR(50) DEFAULT NULL,          -- denormalized snapshot at time of action
  action VARCHAR(100) NOT NULL,                -- created, updated, deleted, approved, logged_in...
  module VARCHAR(50) NOT NULL,                 -- students, results, fees, exams, auth...
  entity_type VARCHAR(50) DEFAULT NULL,        -- table/model name affected
  entity_id BIGINT UNSIGNED DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,       -- human-readable summary
  old_values JSON DEFAULT NULL,
  new_values JSON DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_activity_user (user_id),
  KEY idx_activity_entity (entity_type, entity_id),
  KEY idx_activity_module_action (module, action),
  KEY idx_activity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 10. SCHOOL SETTINGS
-- ---------------------------------------------------------------------

CREATE TABLE school_settings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  school_name VARCHAR(255) NOT NULL,
  school_address VARCHAR(255) DEFAULT NULL,
  school_motto VARCHAR(255) DEFAULT NULL,
  school_phone VARCHAR(50) DEFAULT NULL,
  school_email VARCHAR(150) DEFAULT NULL,
  school_website VARCHAR(150) DEFAULT NULL,
  school_whatsapp VARCHAR(50) DEFAULT NULL,
  school_short_name VARCHAR(50) DEFAULT NULL,
  adm_no_prefix VARCHAR(20) DEFAULT NULL,
  logo_url VARCHAR(255) DEFAULT NULL,
  favicon_url VARCHAR(255) DEFAULT NULL,
  stamp_url VARCHAR(255) DEFAULT NULL,
  use_scratch_card TINYINT(1) NOT NULL DEFAULT 1,
  show_position_on_result TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) DEFAULT NULL,
  message TEXT NOT NULL,
  target ENUM('staff','student','guardian','all') NOT NULL DEFAULT 'all',
  created_by BIGINT UNSIGNED DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;