-- =========================================================
--  SIMDATADIK - Full Core Schema (All Main Modules)
--  File  : sql/schema_core.sql
--  Descr: Tabel inti untuk seluruh modul utama SIMDATADIK
--  Target: MySQL 8+, InnoDB, utf8mb4
-- =========================================================

-- (Opsional) Buat database jika belum ada
CREATE DATABASE IF NOT EXISTS simdatadik
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE simdatadik;

-- Matikan foreign key checks sementara untuk proses DROP
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- DROP TABLES (untuk keperluan development/reset)
-- =========================================================

DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS activity_logs;

DROP TABLE IF EXISTS content_post_files;
DROP TABLE IF EXISTS content_posts;
DROP TABLE IF EXISTS content_categories;

DROP TABLE IF EXISTS lab_practicum_participants;
DROP TABLE IF EXISTS lab_practicums;
DROP TABLE IF EXISTS lab_schedules;
DROP TABLE IF EXISTS lab_assets;
DROP TABLE IF EXISTS lab_rooms;

DROP TABLE IF EXISTS library_loans;
DROP TABLE IF EXISTS library_items;
DROP TABLE IF EXISTS library_collections;
DROP TABLE IF EXISTS library_categories;
DROP TABLE IF EXISTS library_members;

DROP TABLE IF EXISTS ppdb_documents;
DROP TABLE IF EXISTS ppdb_applicants;
DROP TABLE IF EXISTS ppdb_periods;

DROP TABLE IF EXISTS grade_book_scores;
DROP TABLE IF EXISTS grade_book_items;
DROP TABLE IF EXISTS grade_books;

DROP TABLE IF EXISTS exam_answers;
DROP TABLE IF EXISTS exam_participants;
DROP TABLE IF EXISTS exam_question_sets;
DROP TABLE IF EXISTS exams;
DROP TABLE IF EXISTS exam_question_options;
DROP TABLE IF EXISTS exam_questions;
DROP TABLE IF EXISTS exam_question_banks;

DROP TABLE IF EXISTS lms_discussion_posts;
DROP TABLE IF EXISTS lms_discussions;
DROP TABLE IF EXISTS lms_assignment_submissions;
DROP TABLE IF EXISTS lms_assignments;
DROP TABLE IF EXISTS lms_materials;
DROP TABLE IF EXISTS lms_lessons;
DROP TABLE IF EXISTS lms_courses;

DROP TABLE IF EXISTS academic_calendar_events;
DROP TABLE IF EXISTS teaching_schedules;
DROP TABLE IF EXISTS class_group_subjects;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS student_enrollments;
DROP TABLE IF EXISTS class_groups;
DROP TABLE IF EXISTS grade_levels;
DROP TABLE IF EXISTS academic_terms;
DROP TABLE IF EXISTS academic_years;

DROP TABLE IF EXISTS staff_profiles;
DROP TABLE IF EXISTS teacher_profiles;
DROP TABLE IF EXISTS student_profiles;

DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS user_school_roles;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS files;
DROP TABLE IF EXISTS schools;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;

SET FOREIGN_KEY_CHECKS = 1;

-- =========================================================
-- 1. RBAC & USER MANAGEMENT
-- =========================================================

-- Tabel: roles
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    scope ENUM('global', 'school') NOT NULL DEFAULT 'school',
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_roles_slug (slug)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: permissions (opsional untuk RBAC lebih granular)
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL,
    module VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_permissions_slug (slug),
    KEY idx_permissions_module (module)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: users
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    default_role_id BIGINT UNSIGNED NULL,
    status ENUM('pending', 'active', 'suspended', 'deleted') NOT NULL DEFAULT 'pending',
    email_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_users_default_role
        FOREIGN KEY (default_role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_username (username),
    KEY idx_users_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: schools
CREATE TABLE schools (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    npsn VARCHAR(20) NULL,
    jenjang ENUM('SD', 'SMP', 'SMA', 'SMK', 'SLB', 'LAINNYA') NOT NULL DEFAULT 'LAINNYA',
    type ENUM('NEGERI', 'SWASTA', 'LAINNYA') NOT NULL DEFAULT 'LAINNYA',
    status ENUM('pending', 'approved', 'rejected', 'inactive') NOT NULL DEFAULT 'pending',
    registration_code VARCHAR(64) NOT NULL,
    logo_path VARCHAR(255) NULL,

    province VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    district VARCHAR(100) NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    website VARCHAR(150) NULL,

    approved_by_user_id BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_schools_approved_by_user
        FOREIGN KEY (approved_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_schools_npsn (npsn),
    UNIQUE KEY uq_schools_registration_code (registration_code),
    KEY idx_schools_status (status),
    KEY idx_schools_city (city),
    KEY idx_schools_jenjang (jenjang)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: user_roles (role GLOBAL, tidak terkait sekolah)
CREATE TABLE user_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_user_roles_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_user_roles_user_role (user_id, role_id),
    KEY idx_user_roles_role (role_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: user_school_roles (role per sekolah)
CREATE TABLE user_school_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_usr_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_usr_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_usr_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_usr_user_school_role (user_id, school_id, role_id),
    KEY idx_usr_user (user_id),
    KEY idx_usr_school (school_id),
    KEY idx_usr_role (role_id),
    KEY idx_usr_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: role_permissions (relasi roles - permissions)
CREATE TABLE role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_role_permissions_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_role_permissions_permission
        FOREIGN KEY (permission_id) REFERENCES permissions(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_role_permission (role_id, permission_id),
    KEY idx_role_permissions_permission (permission_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 2. Tabel FILE UPLOAD GENERIK
-- =========================================================
CREATE TABLE files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NULL,
    uploader_user_id BIGINT UNSIGNED NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NULL,
    size_bytes BIGINT UNSIGNED NULL,
    hash_sha1 CHAR(40) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_files_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_files_uploader
        FOREIGN KEY (uploader_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_files_school (school_id),
    KEY idx_files_uploader (uploader_user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 3. PROFIL PENGGUNA (Guru/Siswa/Staff)
-- =========================================================

-- Tabel: student_profiles
CREATE TABLE student_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    nisn VARCHAR(20) NULL,
    nis VARCHAR(20) NULL,
    gender ENUM('L', 'P') NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    religion VARCHAR(50) NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    admission_year VARCHAR(9) NULL, -- contoh: 2024/2025
    father_name VARCHAR(150) NULL,
    mother_name VARCHAR(150) NULL,
    guardian_name VARCHAR(150) NULL,
    status ENUM('active','graduated','moved','dropout') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_student_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_student_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_student_user_school (user_id, school_id),
    KEY idx_student_school_status (school_id, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: teacher_profiles
CREATE TABLE teacher_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    nuptk VARCHAR(30) NULL,
    nip VARCHAR(30) NULL,
    gender ENUM('L','P') NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    highest_education VARCHAR(50) NULL,
    major VARCHAR(100) NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_teacher_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_teacher_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_teacher_user_school (user_id, school_id),
    KEY idx_teacher_school_status (school_id, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: staff_profiles
CREATE TABLE staff_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    staff_type VARCHAR(100) NULL, -- contoh: TU, Pustakawan, Laboran, Bendahara
    gender ENUM('L','P') NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_staff_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_staff_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_staff_user_school (user_id, school_id),
    KEY idx_staff_school_status (school_id, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 4. CORE SIS: Tahun Ajaran, Rombel, Mapel, Jadwal
-- =========================================================

-- Tabel: academic_years
CREATE TABLE academic_years (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL, -- contoh: 2024/2025
    start_date DATE NULL,
    end_date DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_academic_years_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_ay_school_name (school_id, name),
    KEY idx_ay_school_active (school_id, is_active)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: academic_terms (semester)
CREATE TABLE academic_terms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL, -- contoh: Ganjil, Genap
    start_date DATE NULL,
    end_date DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_academic_terms_year
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_at_year_name (academic_year_id, name),
    KEY idx_at_year_active (academic_year_id, is_active)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: grade_levels (tingkat/jenjang per sekolah)
CREATE TABLE grade_levels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL, -- contoh: 1, 2, 3 atau X, XI, XII
    order_no INT NOT NULL DEFAULT 1,
    description VARCHAR(100) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_grade_levels_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_grade_school_name (school_id, name),
    KEY idx_grade_school_order (school_id, order_no)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: class_groups (rombongan belajar)
CREATE TABLE class_groups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    grade_level_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL, -- contoh: 7A, X IPA 1
    alias VARCHAR(50) NULL,
    homeroom_teacher_user_id BIGINT UNSIGNED NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_class_groups_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_class_groups_ay
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_class_groups_grade
        FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_class_groups_homeroom
        FOREIGN KEY (homeroom_teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_class_school_ay_name (school_id, academic_year_id, name),
    KEY idx_class_school_status (school_id, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: student_enrollments (buku induk / riwayat rombel per tahun)
CREATE TABLE student_enrollments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_profile_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    enrollment_date DATE NULL,
    status ENUM('active','promoted','repeated','moved','graduated') NOT NULL DEFAULT 'active',
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_enroll_student
        FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_enroll_ay
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_enroll_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_enroll_student_ay (student_profile_id, academic_year_id),
    KEY idx_enroll_class (class_group_id),
    KEY idx_enroll_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: subjects (mapel)
CREATE TABLE subjects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NULL, -- NULL: template global
    code VARCHAR(50) NULL,
    name VARCHAR(150) NOT NULL,
    group_name VARCHAR(100) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_subjects_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_subject_school (school_id),
    KEY idx_subject_name (name)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: class_group_subjects (mapel dalam rombel + guru pengampu)
CREATE TABLE class_group_subjects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_user_id BIGINT UNSIGNED NULL,
    weekly_hours INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_cgs_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cgs_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cgs_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cgs_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_cgs_class_subject (class_group_id, subject_id),
    KEY idx_cgs_teacher (teacher_user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: teaching_schedules (jadwal pelajaran)
CREATE TABLE teaching_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    class_group_subject_id BIGINT UNSIGNED NOT NULL,
    day_of_week TINYINT NOT NULL, -- 1=Senin ... 7=Minggu
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(50) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_ts_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ts_cgs
        FOREIGN KEY (class_group_subject_id) REFERENCES class_group_subjects(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_ts_school_day (school_id, day_of_week)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: academic_calendar_events
CREATE TABLE academic_calendar_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    academic_term_id BIGINT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    event_type VARCHAR(50) NULL, -- contoh: "libur", "ujian", "rapor"
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_holiday TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_ace_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ace_ay
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ace_term
        FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_ace_school_date (school_id, start_date)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. E-LEARNING (LMS)
-- =========================================================

-- Tabel: lms_courses (kelas online)
CREATE TABLE lms_courses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NULL,
    subject_id BIGINT UNSIGNED NULL,
    teacher_user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    status ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsc_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsc_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_lmsc_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_lmsc_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_lmsc_school (school_id),
    KEY idx_lmsc_teacher (teacher_user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lms_lessons (pertemuan/modul)
CREATE TABLE lms_lessons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    sequence_no INT NOT NULL DEFAULT 1,
    is_published TINYINT(1) NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsl_course
        FOREIGN KEY (course_id) REFERENCES lms_courses(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_lmsl_course_seq (course_id, sequence_no)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lms_materials (materi per lesson)
CREATE TABLE lms_materials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lesson_id BIGINT UNSIGNED NOT NULL,
    type ENUM('text','file','video','link') NOT NULL DEFAULT 'text',
    title VARCHAR(200) NOT NULL,
    content TEXT NULL, -- untuk teks / embed code / description
    file_id BIGINT UNSIGNED NULL,
    external_url VARCHAR(255) NULL,
    order_no INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsm_lesson
        FOREIGN KEY (lesson_id) REFERENCES lms_lessons(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsm_file
        FOREIGN KEY (file_id) REFERENCES files(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_lmsm_lesson_order (lesson_id, order_no)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lms_assignments (tugas)
CREATE TABLE lms_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT UNSIGNED NOT NULL,
    lesson_id BIGINT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    due_date DATETIME NULL,
    max_score DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    allow_late TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('draft','published','closed') NOT NULL DEFAULT 'published',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsa_course
        FOREIGN KEY (course_id) REFERENCES lms_courses(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsa_lesson
        FOREIGN KEY (lesson_id) REFERENCES lms_lessons(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_lmsa_course (course_id),
    KEY idx_lmsa_due_date (due_date)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lms_assignment_submissions (pengumpulan tugas)
CREATE TABLE lms_assignment_submissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    assignment_id BIGINT UNSIGNED NOT NULL,
    student_user_id BIGINT UNSIGNED NOT NULL,
    submitted_at DATETIME NULL,
    answer_text TEXT NULL,
    file_id BIGINT UNSIGNED NULL,
    score DECIMAL(5,2) NULL,
    graded_by_user_id BIGINT UNSIGNED NULL,
    graded_at DATETIME NULL,
    status ENUM('submitted','graded','returned') NOT NULL DEFAULT 'submitted',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsas_assignment
        FOREIGN KEY (assignment_id) REFERENCES lms_assignments(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsas_student
        FOREIGN KEY (student_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsas_file
        FOREIGN KEY (file_id) REFERENCES files(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_lmsas_graded_by
        FOREIGN KEY (graded_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_lmsas_assignment_student (assignment_id, student_user_id),
    KEY idx_lmsas_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- (Opsional) Forum Diskusi
CREATE TABLE lms_discussions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    is_closed TINYINT(1) NOT NULL DEFAULT 0,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsd_course
        FOREIGN KEY (course_id) REFERENCES lms_courses(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsd_creator
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_lmsd_course (course_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lms_discussion_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    discussion_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_post_id BIGINT UNSIGNED NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lmsdp_disc
        FOREIGN KEY (discussion_id) REFERENCES lms_discussions(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsdp_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_lmsdp_parent
        FOREIGN KEY (parent_post_id) REFERENCES lms_discussion_posts(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_lmsdp_disc (discussion_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6. E-UJIAN / ASSESSMENT
-- =========================================================

-- Tabel: exam_question_banks
CREATE TABLE exam_question_banks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    subject_id BIGINT UNSIGNED NULL,
    grade_level_id BIGINT UNSIGNED NULL,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_eqb_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_eqb_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_eqb_grade
        FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_eqb_creator
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_eqb_school (school_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exam_questions
CREATE TABLE exam_questions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bank_id BIGINT UNSIGNED NOT NULL,
    question_type ENUM('PG','ESSAY') NOT NULL DEFAULT 'PG',
    question_text TEXT NOT NULL,
    explanation TEXT NULL,
    default_score DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_eq_bank
        FOREIGN KEY (bank_id) REFERENCES exam_question_banks(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_eq_bank (bank_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exam_question_options (pilihan PG)
CREATE TABLE exam_question_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_id BIGINT UNSIGNED NOT NULL,
    option_label VARCHAR(10) NULL, -- A, B, C, D
    option_text TEXT NOT NULL,
    is_correct TINYINT(1) NOT NULL DEFAULT 0,
    order_no INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_eqo_question
        FOREIGN KEY (question_id) REFERENCES exam_questions(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_eqo_question (question_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exams (ujian)
CREATE TABLE exams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    subject_id BIGINT UNSIGNED NULL,
    class_group_id BIGINT UNSIGNED NULL,
    academic_term_id BIGINT UNSIGNED NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    duration_minutes INT NOT NULL,
    total_score DECIMAL(6,2) NULL,
    question_shuffle TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('draft','scheduled','ongoing','finished','archived') NOT NULL DEFAULT 'scheduled',
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_exams_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_exams_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_exams_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_exams_term
        FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_exams_creator
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_exams_school (school_id),
    KEY idx_exams_time (start_time, end_time)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exam_question_sets (soal yang dipakai di ujian)
CREATE TABLE exam_question_sets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_id BIGINT UNSIGNED NOT NULL,
    question_id BIGINT UNSIGNED NOT NULL,
    sequence_no INT NOT NULL DEFAULT 1,
    weight DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_eqs_exam
        FOREIGN KEY (exam_id) REFERENCES exams(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_eqs_question
        FOREIGN KEY (question_id) REFERENCES exam_questions(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_eqs_exam_question (exam_id, question_id),
    KEY idx_eqs_exam_seq (exam_id, sequence_no)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exam_participants
CREATE TABLE exam_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_id BIGINT UNSIGNED NOT NULL,
    student_user_id BIGINT UNSIGNED NOT NULL,
    token VARCHAR(100) NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    total_score DECIMAL(6,2) NULL,
    status ENUM('registered','in_progress','finished','cancelled') NOT NULL DEFAULT 'registered',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_exp_exam
        FOREIGN KEY (exam_id) REFERENCES exams(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_exp_student
        FOREIGN KEY (student_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_exp_exam_student (exam_id, student_user_id),
    KEY idx_exp_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: exam_answers
CREATE TABLE exam_answers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_participant_id BIGINT UNSIGNED NOT NULL,
    question_id BIGINT UNSIGNED NOT NULL,
    selected_option_id BIGINT UNSIGNED NULL,
    answer_text TEXT NULL,
    is_correct TINYINT(1) NULL,
    score DECIMAL(5,2) NULL,
    answered_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_exa_participant
        FOREIGN KEY (exam_participant_id) REFERENCES exam_participants(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_exa_question
        FOREIGN KEY (question_id) REFERENCES exam_questions(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_exa_option
        FOREIGN KEY (selected_option_id) REFERENCES exam_question_options(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_exa_participant_question (exam_participant_id, question_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 7. GRADEBOOK / NILAI
-- =========================================================

-- Tabel: grade_books (buku nilai per mapel/kelas)
CREATE TABLE grade_books (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    academic_term_id BIGINT UNSIGNED NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_gb_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_gb_ay
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_gb_term
        FOREIGN KEY (academic_term_id) REFERENCES academic_terms(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_gb_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_gb_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_gb_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_gb_school (school_id),
    KEY idx_gb_class_subject (class_group_id, subject_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: grade_book_items (komponen nilai: tugas, ujian, dsb)
CREATE TABLE grade_book_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    grade_book_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    source_type ENUM('assignment','exam','manual') NOT NULL DEFAULT 'manual',
    source_id BIGINT UNSIGNED NULL, -- id dari tabel asal (misal lms_assignments.id / exams.id)
    max_score DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    weight DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_gbi_gb
        FOREIGN KEY (grade_book_id) REFERENCES grade_books(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_gbi_gb (grade_book_id),
    KEY idx_gbi_source (source_type, source_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: grade_book_scores (nilai per siswa per item)
CREATE TABLE grade_book_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    grade_book_item_id BIGINT UNSIGNED NOT NULL,
    student_user_id BIGINT UNSIGNED NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_gbs_item
        FOREIGN KEY (grade_book_item_id) REFERENCES grade_book_items(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_gbs_student
        FOREIGN KEY (student_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_gbs_item_student (grade_book_item_id, student_user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 8. PPDB ONLINE
-- =========================================================

-- Tabel: ppdb_periods (gelombang/periode PPDB)
CREATE TABLE ppdb_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    quota INT NULL,
    description TEXT NULL,
    status ENUM('draft','open','closed','archived') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_ppdbp_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ppdbp_ay
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_ppdbp_school (school_id),
    KEY idx_ppdbp_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: ppdb_applicants (pendaftar)
CREATE TABLE ppdb_applicants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    ppdb_period_id BIGINT UNSIGNED NOT NULL,
    registration_number VARCHAR(50) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    gender ENUM('L','P') NULL,
    nisn VARCHAR(20) NULL,
    nik VARCHAR(20) NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    religion VARCHAR(50) NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    origin_school VARCHAR(150) NULL,
    parent_name VARCHAR(150) NULL,
    parent_phone VARCHAR(50) NULL,
    desired_grade_level_id BIGINT UNSIGNED NULL,
    status ENUM('submitted','verified','accepted','rejected','cancelled') NOT NULL DEFAULT 'submitted',
    verification_notes VARCHAR(255) NULL,
    accepted_class_group_id BIGINT UNSIGNED NULL,
    linked_user_id BIGINT UNSIGNED NULL,           -- user setelah diterima
    linked_student_profile_id BIGINT UNSIGNED NULL, -- profil siswa setelah diterima
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_ppdba_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ppdba_period
        FOREIGN KEY (ppdb_period_id) REFERENCES ppdb_periods(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ppdba_grade
        FOREIGN KEY (desired_grade_level_id) REFERENCES grade_levels(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_ppdba_class
        FOREIGN KEY (accepted_class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_ppdba_user
        FOREIGN KEY (linked_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_ppdba_student_profile
        FOREIGN KEY (linked_student_profile_id) REFERENCES student_profiles(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_ppdba_school_reg (school_id, registration_number),
    KEY idx_ppdba_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: ppdb_documents (berkas pendaftar)
CREATE TABLE ppdb_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ppdb_applicant_id BIGINT UNSIGNED NOT NULL,
    document_type VARCHAR(100) NOT NULL, -- contoh: ijazah, kk, akta
    file_id BIGINT UNSIGNED NOT NULL,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    verified_at DATETIME NULL,
    verified_by_user_id BIGINT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_ppdbd_applicant
        FOREIGN KEY (ppdb_applicant_id) REFERENCES ppdb_applicants(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ppdbd_file
        FOREIGN KEY (file_id) REFERENCES files(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_ppdbd_verified_by
        FOREIGN KEY (verified_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_ppdbd_type (document_type)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 9. PERPUSTAKAAN
-- =========================================================

-- Tabel: library_members
CREATE TABLE library_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    member_code VARCHAR(50) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    registered_at DATE NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_libm_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_libm_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_libm_school_user (school_id, user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: library_categories
CREATE TABLE library_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_libc_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_libc_school_name (school_id, name)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: library_collections (judul buku/koleksi)
CREATE TABLE library_collections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NULL,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(150) NULL,
    publisher VARCHAR(150) NULL,
    publish_year VARCHAR(4) NULL,
    isbn VARCHAR(30) NULL,
    collection_type ENUM('BOOK','MAGAZINE','DIGITAL','OTHER') NOT NULL DEFAULT 'BOOK',
    location VARCHAR(100) NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_libcol_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_libcol_category
        FOREIGN KEY (category_id) REFERENCES library_categories(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_libcol_school (school_id),
    KEY idx_libcol_title (title)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: library_items (eksemplar fisik)
CREATE TABLE library_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collection_id BIGINT UNSIGNED NOT NULL,
    barcode VARCHAR(50) NULL,
    status ENUM('available','borrowed','lost','damaged') NOT NULL DEFAULT 'available',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_libi_collection
        FOREIGN KEY (collection_id) REFERENCES library_collections(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_libi_barcode (barcode),
    KEY idx_libi_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: library_loans (peminjaman)
CREATE TABLE library_loans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    library_item_id BIGINT UNSIGNED NOT NULL,
    library_member_id BIGINT UNSIGNED NOT NULL,
    loan_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE NULL,
    status ENUM('borrowed','returned','overdue','lost') NOT NULL DEFAULT 'borrowed',
    fine_amount DECIMAL(10,2) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_libloan_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_libloan_item
        FOREIGN KEY (library_item_id) REFERENCES library_items(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_libloan_member
        FOREIGN KEY (library_member_id) REFERENCES library_members(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_libloan_member (library_member_id),
    KEY idx_libloan_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 10. LABORATORIUM
-- =========================================================

-- Tabel: lab_rooms
CREATE TABLE lab_rooms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(150) NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_labr_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    UNIQUE KEY uq_labr_school_name (school_id, name)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lab_assets (alat & bahan)
CREATE TABLE lab_assets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    lab_room_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NULL,
    quantity INT NOT NULL DEFAULT 0,
    unit VARCHAR(50) NULL,
    condition_status ENUM('good','fair','poor','broken') NOT NULL DEFAULT 'good',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_laba_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_laba_room
        FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_laba_school (school_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lab_schedules (jadwal penggunaan lab)
CREATE TABLE lab_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    lab_room_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NULL,
    subject_id BIGINT UNSIGNED NULL,
    teacher_user_id BIGINT UNSIGNED NULL,
    schedule_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status ENUM('scheduled','done','cancelled') NOT NULL DEFAULT 'scheduled',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_labs_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labs_room
        FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labs_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_labs_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_labs_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_labs_date (schedule_date)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lab_practicums (pencatatan praktikum)
CREATE TABLE lab_practicums (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    lab_room_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NULL,
    teacher_user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    objective TEXT NULL,
    description TEXT NULL,
    practicum_date DATE NOT NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_labp_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labp_room
        FOREIGN KEY (lab_room_id) REFERENCES lab_rooms(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labp_class
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labp_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_labp_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_labp_date (practicum_date)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: lab_practicum_participants
CREATE TABLE lab_practicum_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lab_practicum_id BIGINT UNSIGNED NOT NULL,
    student_user_id BIGINT UNSIGNED NOT NULL,
    presence_status ENUM('present','absent') NOT NULL DEFAULT 'present',
    report_file_id BIGINT UNSIGNED NULL,
    score DECIMAL(5,2) NULL,
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_labpp_practicum
        FOREIGN KEY (lab_practicum_id) REFERENCES lab_practicums(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labpp_student
        FOREIGN KEY (student_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_labpp_file
        FOREIGN KEY (report_file_id) REFERENCES files(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_labpp_practicum_student (lab_practicum_id, student_user_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 11. KONTEN PUBLIK & MODERASI
-- =========================================================

-- Tabel: content_categories
CREATE TABLE content_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_cc_slug (slug)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: content_posts (artikel, materi, event, lomba, dsb)
CREATE TABLE content_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NULL, -- NULL: global
    author_user_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    summary TEXT NULL,
    content LONGTEXT NULL,
    type ENUM('artikel','materi','soal','event','lomba','pengumuman') NOT NULL DEFAULT 'artikel',
    status ENUM('draft','pending_review','published','rejected') NOT NULL DEFAULT 'draft',
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    allow_comments TINYINT(1) NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    moderated_by_user_id BIGINT UNSIGNED NULL,
    moderated_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_cp_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cp_author
        FOREIGN KEY (author_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cp_category
        FOREIGN KEY (category_id) REFERENCES content_categories(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cp_moderator
        FOREIGN KEY (moderated_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    UNIQUE KEY uq_cp_slug (slug),
    KEY idx_cp_status (status),
    KEY idx_cp_type (type),
    KEY idx_cp_school (school_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: content_post_files (lampiran konten)
CREATE TABLE content_post_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content_post_id BIGINT UNSIGNED NOT NULL,
    file_id BIGINT UNSIGNED NOT NULL,
    caption VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cpf_post
        FOREIGN KEY (content_post_id) REFERENCES content_posts(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cpf_file
        FOREIGN KEY (file_id) REFERENCES files(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 12. SUPPORT: ACTIVITY LOGS & NOTIFICATIONS
-- =========================================================

-- Tabel: activity_logs
CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    module VARCHAR(100) NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_al_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_al_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    KEY idx_al_school (school_id),
    KEY idx_al_user (user_id),
    KEY idx_al_action (action)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Tabel: notifications
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    link_url VARCHAR(255) NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_notif_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    KEY idx_notif_user (user_id),
    KEY idx_notif_read (is_read)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- END OF SCHEMA
-- =========================================================
