-- ============================================================
-- SchoolMaster ERP — Complete Database Schema
-- MySQL 8.0+ | UTF8MB4 | InnoDB
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';

CREATE DATABASE IF NOT EXISTS `olasteve_myschoo`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `olasteve_myschoo`;

-- ── School Settings ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `school_settings` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_name`      VARCHAR(200) NOT NULL DEFAULT 'SchoolMaster ERP',
  `address`          TEXT,
  `phone`            VARCHAR(20),
  `email`            VARCHAR(100),
  `website`          VARCHAR(200),
  `logo`             VARCHAR(255),
  `motto`            VARCHAR(255),
  `state`            VARCHAR(100),
  `lga`              VARCHAR(100),
  `school_type`      ENUM('nursery','primary','secondary','mixed') DEFAULT 'mixed',
  `smtp_host`        VARCHAR(255),
  `smtp_port`        SMALLINT DEFAULT 587,
  `smtp_user`        VARCHAR(255),
  `smtp_pass`        VARCHAR(255),
  `smtp_encryption`  ENUM('tls','ssl','none') DEFAULT 'tls',
  `sender_name`      VARCHAR(100),
  `sender_email`     VARCHAR(100),
  `sms_provider`     ENUM('termii','smartsms','bulksms') DEFAULT 'termii',
  `sms_api_key`      VARCHAR(255),
  `sms_sender_id`    VARCHAR(20) DEFAULT 'SchoolERP',
  `currency`         VARCHAR(10) DEFAULT 'NGN',
  `academic_year`    VARCHAR(20),
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Academic Sessions ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `academic_sessions` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(20) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date`   DATE NOT NULL,
  `is_current` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Terms ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `terms` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id`      INT UNSIGNED NOT NULL,
  `term_number`     TINYINT NOT NULL,
  `name`            VARCHAR(50) NOT NULL,
  `opening_date`    DATE,
  `closing_date`    DATE,
  `exam_date`       DATE,
  `result_date`     DATE,
  `is_current`      TINYINT(1) DEFAULT 0,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Roles ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `roles` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`         VARCHAR(50) NOT NULL UNIQUE,
  `display_name` VARCHAR(100),
  `description`  TEXT,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Permissions ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `permissions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(100) NOT NULL UNIQUE,
  `module`      VARCHAR(50),
  `description` VARCHAR(255),
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Role Permissions ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id`       INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`)       REFERENCES `roles`(`id`)       ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Users ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id`        INT UNSIGNED NOT NULL,
  `first_name`     VARCHAR(100) NOT NULL,
  `last_name`      VARCHAR(100) NOT NULL,
  `middle_name`    VARCHAR(100),
  `email`          VARCHAR(150) UNIQUE,
  `phone`          VARCHAR(20),
  `password_hash`  VARCHAR(255) NOT NULL,
  `avatar`         VARCHAR(255),
  `gender`         ENUM('male','female') NOT NULL,
  `date_of_birth`  DATE,
  `address`        TEXT,
  `state_of_origin`VARCHAR(100),
  `lga`            VARCHAR(100),
  `religion`       ENUM('christianity','islam','traditionalist','other'),
  `status`         ENUM('active','inactive','suspended') DEFAULT 'active',
  `email_verified` TINYINT(1) DEFAULT 0,
  `two_fa_enabled` TINYINT(1) DEFAULT 0,
  `two_fa_secret`  VARCHAR(100),
  `login_attempts` TINYINT UNSIGNED DEFAULT 0,
  `locked_until`   DATETIME,
  `last_login`     DATETIME,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`),
  INDEX `idx_role`   (`role_id`),
  INDEX `idx_email`  (`email`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Password Resets ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `token`      VARCHAR(64) NOT NULL UNIQUE,
  `expires_at` DATETIME NOT NULL,
  `used`       TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Class Levels ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `class_levels` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`      VARCHAR(50) NOT NULL,
  `category`  ENUM('nursery','primary','jss','sss') NOT NULL,
  `order_seq` TINYINT UNSIGNED NOT NULL,
  `created_at`TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Classes ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `classes` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `class_level_id`  INT UNSIGNED NOT NULL,
  `arm`             VARCHAR(10) NOT NULL,
  `full_name`       VARCHAR(20),
  `session_id`      INT UNSIGNED NOT NULL,
  `class_teacher_id`INT UNSIGNED,
  `capacity`        SMALLINT UNSIGNED DEFAULT 40,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_class_session` (`class_level_id`,`arm`,`session_id`),
  FOREIGN KEY (`class_level_id`)   REFERENCES `class_levels`(`id`),
  FOREIGN KEY (`session_id`)       REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`class_teacher_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Students ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `students` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`             INT UNSIGNED NOT NULL UNIQUE,
  `admission_number`    VARCHAR(30) NOT NULL UNIQUE,
  `current_class_id`    INT UNSIGNED,
  `session_id`          INT UNSIGNED,
  `passport`            VARCHAR(255),
  `birth_certificate`   VARCHAR(255),
  `blood_group`         ENUM('A+','A-','B+','B-','AB+','AB-','O+','O-'),
  `genotype`            ENUM('AA','AS','SS','AC','SC'),
  `allergies`           TEXT,
  `medical_conditions`  TEXT,
  `disability`          TEXT,
  `admission_date`      DATE NOT NULL,
  `status`              ENUM('active','graduated','transferred','withdrawn','deceased') DEFAULT 'active',
  `barcode`             VARCHAR(100) UNIQUE,
  `qr_code`             VARCHAR(255),
  `created_at`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`)          REFERENCES `users`(`id`)             ON DELETE CASCADE,
  FOREIGN KEY (`current_class_id`) REFERENCES `classes`(`id`)           ON DELETE SET NULL,
  FOREIGN KEY (`session_id`)       REFERENCES `academic_sessions`(`id`),
  INDEX `idx_admission` (`admission_number`),
  INDEX `idx_class`     (`current_class_id`),
  INDEX `idx_status`    (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Student Class History ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_class_history` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`  INT UNSIGNED NOT NULL,
  `class_id`    INT UNSIGNED NOT NULL,
  `session_id`  INT UNSIGNED NOT NULL,
  `term_id`     INT UNSIGNED,
  `action`      ENUM('enrolled','promoted','repeated','transferred','graduated') NOT NULL,
  `notes`       TEXT,
  `approved_by` INT UNSIGNED,
  `action_date` DATE NOT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`)  REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`class_id`)    REFERENCES `classes`(`id`),
  FOREIGN KEY (`session_id`)  REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Parents ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `parents` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`        INT UNSIGNED NOT NULL UNIQUE,
  `occupation`     VARCHAR(150),
  `office_address` TEXT,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Student Parents ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_parents` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`   INT UNSIGNED NOT NULL,
  `parent_id`    INT UNSIGNED NOT NULL,
  `relationship` ENUM('father','mother','guardian','other') NOT NULL,
  `is_primary`   TINYINT(1) DEFAULT 0,
  UNIQUE KEY `uq_student_parent` (`student_id`,`parent_id`),
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`)  REFERENCES `parents`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Emergency Contacts ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `emergency_contacts` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`   INT UNSIGNED NOT NULL,
  `name`         VARCHAR(150) NOT NULL,
  `phone`        VARCHAR(20)  NOT NULL,
  `relationship` VARCHAR(50),
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Staff ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `staff` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`           INT UNSIGNED NOT NULL UNIQUE,
  `staff_id_number`   VARCHAR(30) NOT NULL UNIQUE,
  `department`        VARCHAR(100),
  `designation`       VARCHAR(100),
  `qualification`     VARCHAR(255),
  `specialization`    VARCHAR(255),
  `employment_type`   ENUM('full_time','part_time','contract','volunteer') DEFAULT 'full_time',
  `employment_date`   DATE,
  `salary`            DECIMAL(12,2),
  `account_number`    VARCHAR(20),
  `bank_name`         VARCHAR(100),
  `guarantor_name`    VARCHAR(150),
  `guarantor_phone`   VARCHAR(20),
  `guarantor_address` TEXT,
  `barcode`           VARCHAR(100) UNIQUE,
  `status`            ENUM('active','inactive','suspended','terminated') DEFAULT 'active',
  `created_at`        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_staff_id` (`staff_id_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Staff Attendance ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `staff_attendance` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `staff_id`  INT UNSIGNED NOT NULL,
  `date`      DATE NOT NULL,
  `clock_in`  DATETIME,
  `clock_out` DATETIME,
  `status`    ENUM('present','absent','late','half_day','leave') DEFAULT 'present',
  `method`    ENUM('manual','barcode','biometric') DEFAULT 'manual',
  `notes`     TEXT,
  `created_at`TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_staff_date` (`staff_id`,`date`),
  FOREIGN KEY (`staff_id`) REFERENCES `staff`(`id`) ON DELETE CASCADE,
  INDEX `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Leave Requests ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `leave_requests` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `staff_id`    INT UNSIGNED NOT NULL,
  `leave_type`  ENUM('annual','sick','maternity','paternity','unpaid','other') NOT NULL,
  `start_date`  DATE NOT NULL,
  `end_date`    DATE NOT NULL,
  `days`        TINYINT UNSIGNED,
  `reason`      TEXT,
  `status`      ENUM('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` INT UNSIGNED,
  `approved_at` DATETIME,
  `notes`       TEXT,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`staff_id`)    REFERENCES `staff`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Subjects ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `subjects` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `code`       VARCHAR(20) UNIQUE,
  `category`   VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Class Subjects ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `class_subjects` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `class_id`   INT UNSIGNED NOT NULL,
  `subject_id` INT UNSIGNED NOT NULL,
  `teacher_id` INT UNSIGNED,
  `session_id` INT UNSIGNED NOT NULL,
  UNIQUE KEY `uq_class_subject_session` (`class_id`,`subject_id`,`session_id`),
  FOREIGN KEY (`class_id`)   REFERENCES `classes`(`id`)            ON DELETE CASCADE,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`)           ON DELETE CASCADE,
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`)              ON DELETE SET NULL,
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Student Attendance ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_attendance` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`      INT UNSIGNED NOT NULL,
  `class_id`        INT UNSIGNED NOT NULL,
  `session_id`      INT UNSIGNED NOT NULL,
  `term_id`         INT UNSIGNED NOT NULL,
  `date`            DATE NOT NULL,
  `status`          ENUM('present','absent','late','excused') NOT NULL,
  `time_in`         TIME,
  `time_out`        TIME,
  `method`          ENUM('manual','qr','barcode') DEFAULT 'manual',
  `recorded_by`     INT UNSIGNED,
  `parent_notified` TINYINT(1) DEFAULT 0,
  `notes`           VARCHAR(255),
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_attendance_daily` (`student_id`,`date`,`class_id`),
  FOREIGN KEY (`student_id`)  REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`class_id`)    REFERENCES `classes`(`id`),
  FOREIGN KEY (`session_id`)  REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)     REFERENCES `terms`(`id`),
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_date`         (`date`),
  INDEX `idx_student_term` (`student_id`,`term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Fee Categories ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `fee_categories` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`         VARCHAR(100) NOT NULL,
  `description`  TEXT,
  `is_compulsory`TINYINT(1) DEFAULT 1,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Fee Schedules ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `fee_schedules` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id`      INT UNSIGNED NOT NULL,
  `term_id`         INT UNSIGNED NOT NULL,
  `class_level_id`  INT UNSIGNED,
  `fee_category_id` INT UNSIGNED NOT NULL,
  `amount`          DECIMAL(12,2) NOT NULL,
  `due_date`        DATE,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`)      REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)         REFERENCES `terms`(`id`),
  FOREIGN KEY (`class_level_id`)  REFERENCES `class_levels`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`fee_category_id`) REFERENCES `fee_categories`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Student Fees ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_fees` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`      INT UNSIGNED NOT NULL,
  `fee_schedule_id` INT UNSIGNED NOT NULL,
  `amount_due`      DECIMAL(12,2) NOT NULL,
  `amount_paid`     DECIMAL(12,2) DEFAULT 0.00,
  `status`          ENUM('unpaid','partial','paid') DEFAULT 'unpaid',
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_student_fee` (`student_id`,`fee_schedule_id`),
  FOREIGN KEY (`student_id`)      REFERENCES `students`(`id`)       ON DELETE CASCADE,
  FOREIGN KEY (`fee_schedule_id`) REFERENCES `fee_schedules`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Payments ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `payments` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_fee_id`  INT UNSIGNED NOT NULL,
  `student_id`      INT UNSIGNED NOT NULL,
  `amount`          DECIMAL(12,2) NOT NULL,
  `payment_method`  ENUM('cash','pos','bank_transfer','online','cheque') NOT NULL,
  `reference`       VARCHAR(100) UNIQUE,
  `bank_name`       VARCHAR(100),
  `transaction_ref` VARCHAR(200),
  `receipt_number`  VARCHAR(30) UNIQUE,
  `payment_date`    DATE NOT NULL,
  `received_by`     INT UNSIGNED,
  `verified`        TINYINT(1) DEFAULT 0,
  `notes`           TEXT,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_fee_id`) REFERENCES `student_fees`(`id`),
  FOREIGN KEY (`student_id`)     REFERENCES `students`(`id`),
  FOREIGN KEY (`received_by`)    REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_student` (`student_id`),
  INDEX `idx_date`    (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Income Records ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `income_records` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category`    VARCHAR(100),
  `description` TEXT,
  `amount`      DECIMAL(12,2) NOT NULL,
  `date`        DATE NOT NULL,
  `recorded_by` INT UNSIGNED,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Expense Records ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `expense_records` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category`    VARCHAR(100),
  `description` TEXT,
  `amount`      DECIMAL(12,2) NOT NULL,
  `date`        DATE NOT NULL,
  `receipt`     VARCHAR(255),
  `approved_by` INT UNSIGNED,
  `recorded_by` INT UNSIGNED,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Grading Scales ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `grading_scales` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `is_default` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `grade_boundaries` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `grading_scale_id` INT UNSIGNED NOT NULL,
  `grade`            VARCHAR(5) NOT NULL,
  `min_score`        DECIMAL(5,2) NOT NULL,
  `max_score`        DECIMAL(5,2) NOT NULL,
  `remark`           VARCHAR(50),
  FOREIGN KEY (`grading_scale_id`) REFERENCES `grading_scales`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Student Results ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_results` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`   INT UNSIGNED NOT NULL,
  `class_id`     INT UNSIGNED NOT NULL,
  `subject_id`   INT UNSIGNED NOT NULL,
  `session_id`   INT UNSIGNED NOT NULL,
  `term_id`      INT UNSIGNED NOT NULL,
  `ca1`          DECIMAL(5,2) DEFAULT 0,
  `ca2`          DECIMAL(5,2) DEFAULT 0,
  `assignment`   DECIMAL(5,2) DEFAULT 0,
  `project`      DECIMAL(5,2) DEFAULT 0,
  `exam_score`   DECIMAL(5,2) DEFAULT 0,
  `total`        DECIMAL(5,2) GENERATED ALWAYS AS (`ca1`+`ca2`+`assignment`+`project`+`exam_score`) STORED,
  `grade`        VARCHAR(5),
  `remark`       VARCHAR(100),
  `teacher_id`   INT UNSIGNED,
  `is_published` TINYINT(1) DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_result` (`student_id`,`subject_id`,`term_id`,`session_id`),
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`class_id`)   REFERENCES `classes`(`id`),
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`),
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)    REFERENCES `terms`(`id`),
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_student_term` (`student_id`,`term_id`),
  INDEX `idx_class_term`   (`class_id`,`term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Term Summaries ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `term_summaries` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`        INT UNSIGNED NOT NULL,
  `class_id`          INT UNSIGNED NOT NULL,
  `session_id`        INT UNSIGNED NOT NULL,
  `term_id`           INT UNSIGNED NOT NULL,
  `total_marks`       DECIMAL(8,2),
  `average`           DECIMAL(5,2),
  `position`          SMALLINT UNSIGNED,
  `total_students`    SMALLINT UNSIGNED,
  `principal_comment` TEXT,
  `teacher_comment`   TEXT,
  `next_term_begins`  DATE,
  `is_published`      TINYINT(1) DEFAULT 0,
  `published_at`      DATETIME,
  `decision`          ENUM('promoted','repeated','graduated','pending') DEFAULT 'pending',
  UNIQUE KEY `uq_summary` (`student_id`,`term_id`,`session_id`),
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`class_id`)   REFERENCES `classes`(`id`),
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)    REFERENCES `terms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Promotion Records ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `promotion_records` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`      INT UNSIGNED NOT NULL,
  `from_class_id`   INT UNSIGNED NOT NULL,
  `to_class_id`     INT UNSIGNED,
  `session_id`      INT UNSIGNED NOT NULL,
  `decision`        ENUM('promoted','repeated','graduated','withdrawn') NOT NULL,
  `t1_average`      DECIMAL(5,2),
  `t2_average`      DECIMAL(5,2),
  `t3_average`      DECIMAL(5,2),
  `annual_average`  DECIMAL(5,2),
  `promo_exam_score`DECIMAL(5,2),
  `final_score`     DECIMAL(5,2),
  `approved_by`     INT UNSIGNED,
  `approved_at`     DATETIME,
  `notes`           TEXT,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`)    REFERENCES `students`(`id`),
  FOREIGN KEY (`from_class_id`) REFERENCES `classes`(`id`),
  FOREIGN KEY (`to_class_id`)   REFERENCES `classes`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`session_id`)    REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`approved_by`)   REFERENCES `users`(`id`)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CBT Exams ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cbt_exams` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title`               VARCHAR(200) NOT NULL,
  `subject_id`          INT UNSIGNED NOT NULL,
  `class_id`            INT UNSIGNED,
  `session_id`          INT UNSIGNED NOT NULL,
  `term_id`             INT UNSIGNED NOT NULL,
  `exam_type`           ENUM('quiz','test','exam','practice') DEFAULT 'test',
  `instructions`        TEXT,
  `duration_mins`       SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  `total_marks`         DECIMAL(6,2),
  `pass_mark`           DECIMAL(5,2),
  `randomize_questions` TINYINT(1) DEFAULT 1,
  `randomize_options`   TINYINT(1) DEFAULT 1,
  `show_result_after`   TINYINT(1) DEFAULT 0,
  `start_time`          DATETIME,
  `end_time`            DATETIME,
  `status`              ENUM('draft','published','active','closed') DEFAULT 'draft',
  `created_by`          INT UNSIGNED,
  `created_at`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`subject_id`)  REFERENCES `subjects`(`id`),
  FOREIGN KEY (`class_id`)    REFERENCES `classes`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`session_id`)  REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)     REFERENCES `terms`(`id`),
  FOREIGN KEY (`created_by`)  REFERENCES `users`(`id`)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cbt_questions` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_id`         INT UNSIGNED NOT NULL,
  `question_text`   TEXT NOT NULL,
  `question_type`   ENUM('mcq','true_false','fill_gap','essay','short_answer') NOT NULL,
  `image`           VARCHAR(255),
  `marks`           DECIMAL(5,2) DEFAULT 1,
  `difficulty`      ENUM('easy','medium','hard') DEFAULT 'medium',
  `explanation`     TEXT,
  `order_seq`       SMALLINT UNSIGNED,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`exam_id`) REFERENCES `cbt_exams`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cbt_options` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_id` INT UNSIGNED NOT NULL,
  `option_text` TEXT NOT NULL,
  `is_correct`  TINYINT(1) DEFAULT 0,
  `order_seq`   TINYINT UNSIGNED,
  FOREIGN KEY (`question_id`) REFERENCES `cbt_questions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cbt_attempts` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_id`          INT UNSIGNED NOT NULL,
  `student_id`       INT UNSIGNED NOT NULL,
  `started_at`       DATETIME NOT NULL,
  `submitted_at`     DATETIME,
  `time_taken_secs`  INT UNSIGNED,
  `total_score`      DECIMAL(8,2),
  `percentage`       DECIMAL(5,2),
  `passed`           TINYINT(1),
  `ip_address`       VARCHAR(45),
  `status`           ENUM('in_progress','submitted','timed_out') DEFAULT 'in_progress',
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_attempt` (`exam_id`,`student_id`),
  FOREIGN KEY (`exam_id`)    REFERENCES `cbt_exams`(`id`)  ON DELETE CASCADE,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cbt_answers` (
  `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_id`         INT UNSIGNED NOT NULL,
  `question_id`        INT UNSIGNED NOT NULL,
  `selected_option_id` INT UNSIGNED,
  `text_answer`        TEXT,
  `is_correct`         TINYINT(1),
  `marks_earned`       DECIMAL(5,2),
  `created_at`         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`attempt_id`)         REFERENCES `cbt_attempts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`question_id`)        REFERENCES `cbt_questions`(`id`),
  FOREIGN KEY (`selected_option_id`) REFERENCES `cbt_options`(`id`)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Assignments ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `assignments` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `teacher_id`  INT UNSIGNED NOT NULL,
  `class_id`    INT UNSIGNED NOT NULL,
  `subject_id`  INT UNSIGNED NOT NULL,
  `session_id`  INT UNSIGNED NOT NULL,
  `term_id`     INT UNSIGNED NOT NULL,
  `title`       VARCHAR(255) NOT NULL,
  `description` TEXT,
  `attachment`  VARCHAR(255),
  `max_score`   DECIMAL(5,2),
  `due_date`    DATETIME NOT NULL,
  `status`      ENUM('active','closed') DEFAULT 'active',
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`class_id`)   REFERENCES `classes`(`id`),
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`),
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)    REFERENCES `terms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `assignment_submissions` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `assignment_id`   INT UNSIGNED NOT NULL,
  `student_id`      INT UNSIGNED NOT NULL,
  `submission_file` VARCHAR(255),
  `submission_text` TEXT,
  `submitted_at`    DATETIME,
  `score`           DECIMAL(5,2),
  `feedback`        TEXT,
  `status`          ENUM('pending','submitted','graded','late') DEFAULT 'pending',
  `graded_at`       DATETIME,
  UNIQUE KEY `uq_submission` (`assignment_id`,`student_id`),
  FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`student_id`)    REFERENCES `students`(`id`)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── LMS Content ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `lms_content` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `teacher_id`   INT UNSIGNED NOT NULL,
  `class_id`     INT UNSIGNED,
  `subject_id`   INT UNSIGNED NOT NULL,
  `session_id`   INT UNSIGNED NOT NULL,
  `term_id`      INT UNSIGNED NOT NULL,
  `title`        VARCHAR(255) NOT NULL,
  `description`  TEXT,
  `content_type` ENUM('video','pdf','note','powerpoint','audio','link','other') NOT NULL,
  `file_path`    VARCHAR(500),
  `url`          VARCHAR(500),
  `file_size`    INT UNSIGNED,
  `duration`     VARCHAR(20),
  `is_published` TINYINT(1) DEFAULT 0,
  `views`        INT UNSIGNED DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`teacher_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`class_id`)   REFERENCES `classes`(`id`)           ON DELETE SET NULL,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`),
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`),
  FOREIGN KEY (`term_id`)    REFERENCES `terms`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Messages ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `messages` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sender_id`        INT UNSIGNED NOT NULL,
  `recipient_id`     INT UNSIGNED,
  `recipient_group`  VARCHAR(50),
  `subject`          VARCHAR(255),
  `body`             TEXT NOT NULL,
  `attachment`       VARCHAR(255),
  `is_read`          TINYINT(1) DEFAULT 0,
  `read_at`          DATETIME,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`sender_id`)    REFERENCES `users`(`id`),
  FOREIGN KEY (`recipient_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_recipient` (`recipient_id`),
  INDEX `idx_sender`    (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Notifications ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `notifications` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `type`       VARCHAR(50),
  `title`      VARCHAR(255),
  `body`       TEXT,
  `data`       JSON,
  `is_read`    TINYINT(1) DEFAULT 0,
  `read_at`    DATETIME,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_read` (`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SMS & Email Logs ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `recipient`    VARCHAR(20) NOT NULL,
  `message`      TEXT NOT NULL,
  `provider`     VARCHAR(50),
  `status`       ENUM('sent','failed','pending') DEFAULT 'pending',
  `provider_ref` VARCHAR(100),
  `cost`         DECIMAL(8,4),
  `triggered_by` INT UNSIGNED,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`triggered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `to_email`     VARCHAR(150) NOT NULL,
  `subject`      VARCHAR(255),
  `status`       ENUM('sent','failed','pending') DEFAULT 'pending',
  `error`        TEXT,
  `triggered_by` INT UNSIGNED,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`triggered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Library ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `library_books` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `isbn`       VARCHAR(30),
  `title`      VARCHAR(255) NOT NULL,
  `author`     VARCHAR(255),
  `publisher`  VARCHAR(200),
  `edition`    VARCHAR(50),
  `category`   VARCHAR(100),
  `quantity`   SMALLINT UNSIGNED DEFAULT 1,
  `available`  SMALLINT UNSIGNED DEFAULT 1,
  `barcode`    VARCHAR(100) UNIQUE,
  `cover`      VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `library_borrowings` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `book_id`     INT UNSIGNED NOT NULL,
  `borrower_id` INT UNSIGNED NOT NULL,
  `borrow_date` DATE NOT NULL,
  `due_date`    DATE NOT NULL,
  `return_date` DATE,
  `late_fee`    DECIMAL(8,2) DEFAULT 0,
  `status`      ENUM('borrowed','returned','overdue') DEFAULT 'borrowed',
  `issued_by`   INT UNSIGNED,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`book_id`)     REFERENCES `library_books`(`id`),
  FOREIGN KEY (`borrower_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`issued_by`)   REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Medical Records ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `medical_records` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`      INT UNSIGNED NOT NULL,
  `nurse_id`        INT UNSIGNED,
  `visit_date`      DATE NOT NULL,
  `complaint`       TEXT,
  `diagnosis`       TEXT,
  `treatment`       TEXT,
  `medication`      TEXT,
  `referral`        TEXT,
  `parent_notified` TINYINT(1) DEFAULT 0,
  `follow_up_date`  DATE,
  `notes`           TEXT,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`nurse_id`)   REFERENCES `users`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Transport ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `vehicles` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reg_number` VARCHAR(20) NOT NULL UNIQUE,
  `make`       VARCHAR(100),
  `model`      VARCHAR(100),
  `year`       YEAR,
  `capacity`   TINYINT UNSIGNED,
  `driver_id`  INT UNSIGNED,
  `status`     ENUM('active','maintenance','inactive') DEFAULT 'active',
  FOREIGN KEY (`driver_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transport_routes` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(100) NOT NULL,
  `description` TEXT,
  `fee`         DECIMAL(10,2),
  `vehicle_id`  INT UNSIGNED,
  FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_transport` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`   INT UNSIGNED NOT NULL,
  `route_id`     INT UNSIGNED NOT NULL,
  `session_id`   INT UNSIGNED NOT NULL,
  `pickup_point` VARCHAR(200),
  UNIQUE KEY `uq_student_transport` (`student_id`,`session_id`),
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`route_id`)   REFERENCES `transport_routes`(`id`),
  FOREIGN KEY (`session_id`) REFERENCES `academic_sessions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Discipline ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `disciplinary_records` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id`      INT UNSIGNED NOT NULL,
  `incident_date`   DATE NOT NULL,
  `type`            ENUM('warning','detention','suspension','expulsion','other') NOT NULL,
  `description`     TEXT NOT NULL,
  `action_taken`    TEXT,
  `suspension_days` TINYINT UNSIGNED,
  `reported_by`     INT UNSIGNED,
  `parent_notified` TINYINT(1) DEFAULT 0,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`)  REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Audit & Security ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED,
  `action`     VARCHAR(100) NOT NULL,
  `model`      VARCHAR(100),
  `model_id`   INT UNSIGNED,
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user`    (`user_id`),
  INDEX `idx_action`  (`action`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_history` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `status`     ENUM('success','failed','locked') DEFAULT 'success',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user`    (`user_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;