-- ==============================================================================
-- SISTEM INFORMASI CBT MA BAHRUL ULUM KEPOHBARU
-- MASTER DATABASE SCHEMA & SEEDER (TAHAP 02 LENGKAP)
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- Total Tabel: 36 Tabel Terelasi Lengkap
-- ==============================================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------------------------
-- 1. TABEL PENGATURAN & AUDIT
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(64) NOT NULL UNIQUE,
  `setting_value` TEXT NULL,
  `setting_group` VARCHAR(32) NOT NULL DEFAULT 'general',
  `description` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_setting_group` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(64) NOT NULL,
  `table_name` VARCHAR(64) NULL,
  `record_id` VARCHAR(64) NULL,
  `description` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_user` (`user_id`),
  INDEX `idx_audit_action` (`action`),
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2. TABEL PERAN, HAK AKSES & PENGGUNA
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_name` VARCHAR(32) NOT NULL UNIQUE,
  `display_name` VARCHAR(64) NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `permission_key` VARCHAR(64) NOT NULL UNIQUE,
  `display_name` VARCHAR(128) NOT NULL,
  `module` VARCHAR(32) NOT NULL,
  `description` VARCHAR(255) NULL,
  INDEX `idx_permission_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(64) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(128) NOT NULL,
  `email` VARCHAR(128) NULL,
  `phone` VARCHAR(20) NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `avatar` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_user_role` (`role_id`),
  INDEX `idx_user_active` (`is_active`),
  INDEX `idx_user_deleted` (`deleted_at`),
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 3. TABEL MASTER AKADEMIK & PROFIL PENDIDIK/PESERTA
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `academic_years`;
CREATE TABLE `academic_years` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `year_name` VARCHAR(16) NOT NULL UNIQUE,
  `is_active` TINYINT(1) NOT NULL DEFAULT 0,
  `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;

DROP TABLE IF EXISTS `semesters`;
CREATE TABLE `semesters` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `academic_year_id` INT UNSIGNED NOT NULL,
  `semester` ENUM('ganjil', 'genap') NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sem_year` (`academic_year_id`),
  CONSTRAINT `fk_semesters_year` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `class_name` VARCHAR(32) NOT NULL,
  `grade_level` TINYINT UNSIGNED NOT NULL,
  `major` VARCHAR(32) NOT NULL DEFAULT 'Umum',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_class_grade` (`grade_level`),
  INDEX `idx_class_major` (`major`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `subject_code` VARCHAR(16) NOT NULL UNIQUE,
  `subject_name` VARCHAR(128) NOT NULL,
  `category` ENUM('umum', 'keagamaan', 'peminatan', 'mulok') NOT NULL DEFAULT 'umum',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_sub_cat` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
  `nip` VARCHAR(32) NULL,
  `nuptk` VARCHAR(32) NULL,
  `gender` ENUM('L', 'P') NOT NULL DEFAULT 'L',
  `specialization` VARCHAR(128) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  CONSTRAINT `fk_teacher_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
  `class_id` INT UNSIGNED NOT NULL,
  `nisn` VARCHAR(16) NOT NULL UNIQUE,
  `nis` VARCHAR(16) NOT NULL,
  `gender` ENUM('L', 'P') NOT NULL DEFAULT 'L',
  `birth_place` VARCHAR(64) NULL,
  `birth_date` DATE NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_student_class` (`class_id`),
  CONSTRAINT `fk_student_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 4. TABEL BANK SOAL, BUTIR SOAL, OPSI & MEDIA
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `question_banks`;
CREATE TABLE `question_banks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `bank_code` VARCHAR(32) NOT NULL UNIQUE,
  `title` VARCHAR(160) NOT NULL,
  `subject_id` INT UNSIGNED NOT NULL,
  `class_grade` TINYINT UNSIGNED NOT NULL,
  `academic_year_id` INT UNSIGNED NOT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `topic` VARCHAR(128) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_qb_subject` (`subject_id`),
  INDEX `idx_qb_creator` (`created_by`),
  CONSTRAINT `fk_qb_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`),
  CONSTRAINT `fk_qb_year` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`),
  CONSTRAINT `fk_qb_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `bank_id` BIGINT UNSIGNED NOT NULL,
  `question_type` ENUM(
    'PG', 'PG_KOMPLEKS', 'BENAR_SALAH', 'MENJODOHKAN', 
    'ISIAN', 'ISIAN_RUMPANG', 'ANGKA', 'ESAI', 
    'MENGURUTKAN', 'STIMULUS', 'BERGAMBAR', 'AUDIO'
  ) NOT NULL DEFAULT 'PG',
  `question_text` LONGTEXT NOT NULL,
  `stimulus_text` LONGTEXT NULL,
  `difficulty` ENUM('mudah', 'sedang', 'sukar') NOT NULL DEFAULT 'sedang',
  `weight` DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  `explanation` TEXT NULL,
  `audio_url` VARCHAR(255) NULL,
  `image_url` VARCHAR(255) NULL,
  `order_index` INT UNSIGNED NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_q_bank` (`bank_id`),
  INDEX `idx_q_type` (`question_type`),
  CONSTRAINT `fk_q_bank` FOREIGN KEY (`bank_id`) REFERENCES `question_banks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `question_options`;
CREATE TABLE `question_options` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `option_key` VARCHAR(8) NOT NULL,
  `option_text` LONGTEXT NOT NULL,
  `image_url` VARCHAR(255) NULL,
  `is_correct` TINYINT(1) NOT NULL DEFAULT 0,
  `score_weight` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  INDEX `idx_qo_question` (`question_id`),
  CONSTRAINT `fk_qo_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `question_answers`;
CREATE TABLE `question_answers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `answer_key` TEXT NOT NULL,
  `tolerance_range` DECIMAL(6,3) NULL DEFAULT 0.000,
  INDEX `idx_qa_question` (`question_id`),
  CONSTRAINT `fk_qa_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `question_media`;
CREATE TABLE `question_media` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `media_type` ENUM('image', 'audio', 'document') NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_name` VARCHAR(128) NOT NULL,
  `file_size` INT UNSIGNED NOT NULL DEFAULT 0,
  `mime_type` VARCHAR(64) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_qm_question` (`question_id`),
  CONSTRAINT `fk_qm_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 5. TABEL PAKET SOAL
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `exam_packages`;
CREATE TABLE `exam_packages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `package_code` VARCHAR(32) NOT NULL UNIQUE,
  `package_name` VARCHAR(160) NOT NULL,
  `subject_id` INT UNSIGNED NOT NULL,
  `class_grade` TINYINT UNSIGNED NOT NULL,
  `academic_year_id` INT UNSIGNED NOT NULL,
  `total_questions` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_weight` DECIMAL(6,2) NOT NULL DEFAULT 100.00,
  `random_questions` TINYINT(1) NOT NULL DEFAULT 1,
  `random_options` TINYINT(1) NOT NULL DEFAULT 1,
  `is_locked` TINYINT(1) NOT NULL DEFAULT 0,
  `manifest_hash` VARCHAR(64) NULL,
  `instructions` TEXT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_ep_subject` (`subject_id`),
  CONSTRAINT `fk_ep_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`),
  CONSTRAINT `fk_ep_year` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`),
  CONSTRAINT `fk_ep_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exam_package_questions`;
CREATE TABLE `exam_package_questions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `package_id` BIGINT UNSIGNED NOT NULL,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `custom_weight` DECIMAL(5,2) NULL,
  `question_snapshot` LONGTEXT NULL,
  `order_no` INT UNSIGNED NOT NULL DEFAULT 1,
  UNIQUE KEY `uk_epq` (`package_id`, `question_id`),
  CONSTRAINT `fk_epq_package` FOREIGN KEY (`package_id`) REFERENCES `exam_packages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_epq_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 6. TABEL JADWAL UJIAN, KELAS & PENGAWAS
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `browser_settings`;
CREATE TABLE `browser_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(64) NOT NULL,
  `required_browser` VARCHAR(32) NOT NULL DEFAULT 'chrome',
  `min_browser_version` VARCHAR(16) NULL DEFAULT '90.0',
  `allow_fullscreen` TINYINT(1) NOT NULL DEFAULT 1,
  `block_tab_switch` TINYINT(1) NOT NULL DEFAULT 1,
  `max_violations` TINYINT UNSIGNED NOT NULL DEFAULT 3,
  `action_on_limit` ENUM('warning', 'lock', 'force_submit') NOT NULL DEFAULT 'warning',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exams`;
CREATE TABLE `exams` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_code` VARCHAR(32) NOT NULL UNIQUE,
  `title` VARCHAR(160) NOT NULL,
  `package_id` BIGINT UNSIGNED NOT NULL,
  `subject_id` INT UNSIGNED NOT NULL,
  `academic_year_id` INT UNSIGNED NOT NULL,
  `semester_id` INT UNSIGNED NOT NULL,
  `browser_setting_id` INT UNSIGNED NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NOT NULL,
  `duration_minutes` INT UNSIGNED NOT NULL DEFAULT 90,
  `token` VARCHAR(16) NULL,
  `token_refresh_interval` INT UNSIGNED NOT NULL DEFAULT 0,
  `pass_score` DECIMAL(5,2) NOT NULL DEFAULT 75.00,
  `show_result_to_student` TINYINT(1) NOT NULL DEFAULT 0,
  `allow_review` TINYINT(1) NOT NULL DEFAULT 0,
  `max_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `status` ENUM('draft', 'scheduled', 'ongoing', 'finished', 'closed', 'archived') NOT NULL DEFAULT 'draft',
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  INDEX `idx_exam_status` (`status`),
  INDEX `idx_exam_time` (`start_time`, `end_time`),
  CONSTRAINT `fk_exam_package` FOREIGN KEY (`package_id`) REFERENCES `exam_packages` (`id`),
  CONSTRAINT `fk_exam_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`),
  CONSTRAINT `fk_exam_year` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`),
  CONSTRAINT `fk_exam_semester` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`id`),
  CONSTRAINT `fk_exam_browser` FOREIGN KEY (`browser_setting_id`) REFERENCES `browser_settings` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exam_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exam_classes`;
CREATE TABLE `exam_classes` (
  `exam_id` BIGINT UNSIGNED NOT NULL,
  `class_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`exam_id`, `class_id`),
  CONSTRAINT `fk_ec_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ec_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exam_supervisors`;
CREATE TABLE `exam_supervisors` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `room_name` VARCHAR(32) NULL,
  `notes` TEXT NULL,
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_exam_sup` (`exam_id`, `user_id`),
  CONSTRAINT `fk_es_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_es_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exam_participants`;
CREATE TABLE `exam_participants` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_id` BIGINT UNSIGNED NOT NULL,
  `student_id` BIGINT UNSIGNED NOT NULL,
  `custom_token` VARCHAR(16) NULL,
  `is_allowed` TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY `uk_ep_student` (`exam_id`, `student_id`),
  CONSTRAINT `fk_epart_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_epart_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 7. TABEL SESI PENGERJAAN, JAWABAN, LOG KEJADIAN & PENILAIAN
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `exam_attempts`;
CREATE TABLE `exam_attempts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exam_id` BIGINT UNSIGNED NOT NULL,
  `student_id` BIGINT UNSIGNED NOT NULL,
  `attempt_number` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NULL,
  `remaining_seconds` INT NOT NULL DEFAULT 5400,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `last_activity` DATETIME NOT NULL,
  `last_saved_at` DATETIME NULL,
  `total_score` DECIMAL(6,2) NULL,
  `status` ENUM('in_progress', 'submitted', 'locked', 'timed_out', 'evaluated') NOT NULL DEFAULT 'in_progress',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_ea_status` (`status`),
  INDEX `idx_ea_student` (`student_id`),
  CONSTRAINT `fk_ea_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ea_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `attempt_answers`;
CREATE TABLE `attempt_answers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_id` BIGINT UNSIGNED NOT NULL,
  `question_id` BIGINT UNSIGNED NOT NULL,
  `selected_option_id` BIGINT UNSIGNED NULL,
  `answer_text` LONGTEXT NULL,
  `is_doubt` TINYINT(1) NOT NULL DEFAULT 0,
  `is_correct` TINYINT(1) NULL,
  `score_awarded` DECIMAL(5,2) NULL DEFAULT 0.00,
  `saved_at` DATETIME NOT NULL,
  `evaluated_at` DATETIME NULL,
  UNIQUE KEY `uk_aa_attempt_q` (`attempt_id`, `question_id`),
  CONSTRAINT `fk_aa_attempt` FOREIGN KEY (`attempt_id`) REFERENCES `exam_attempts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_aa_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `attempt_events`;
CREATE TABLE `attempt_events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_id` BIGINT UNSIGNED NOT NULL,
  `event_type` ENUM(
    'tab_switch', 'window_blur', 'fullscreen_exit', 
    'device_change', 'connection_drop', 'connection_restore', 'autosave_fail'
  ) NOT NULL,
  `event_time` DATETIME NOT NULL,
  `details` TEXT NULL,
  INDEX `idx_ae_attempt` (`attempt_id`),
  INDEX `idx_ae_type` (`event_type`),
  CONSTRAINT `fk_ae_attempt` FOREIGN KEY (`attempt_id`) REFERENCES `exam_attempts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exam_device_sessions`;
CREATE TABLE `exam_device_sessions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_id` BIGINT UNSIGNED NOT NULL,
  `session_token` VARCHAR(64) NOT NULL UNIQUE,
  `screen_resolution` VARCHAR(32) NULL,
  `device_os` VARCHAR(64) NULL,
  `browser_name` VARCHAR(64) NULL,
  `browser_version` VARCHAR(32) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_eds_attempt` FOREIGN KEY (`attempt_id`) REFERENCES `exam_attempts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `browser_check_logs`;
CREATE TABLE `browser_check_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id` BIGINT UNSIGNED NOT NULL,
  `exam_id` BIGINT UNSIGNED NOT NULL,
  `browser_detected` VARCHAR(64) NOT NULL,
  `version_detected` VARCHAR(32) NULL,
  `os_detected` VARCHAR(64) NULL,
  `is_compliant` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` TEXT NULL,
  `checked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_bcl_student` (`student_id`),
  CONSTRAINT `fk_bcl_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bcl_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `essay_grading`;
CREATE TABLE `essay_grading` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `attempt_answer_id` BIGINT UNSIGNED NOT NULL UNIQUE,
  `graded_by` BIGINT UNSIGNED NOT NULL,
  `score` DECIMAL(5,2) NOT NULL,
  `feedback` TEXT NULL,
  `graded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_eg_answer` FOREIGN KEY (`attempt_answer_id`) REFERENCES `attempt_answers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_eg_grader` FOREIGN KEY (`graded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 8. TABEL LOG IMPOR DATA, PENGUMUMAN & NOTIFIKASI
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `question_import_logs`;
CREATE TABLE `question_import_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `bank_id` BIGINT UNSIGNED NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` ENUM('docx', 'xlsx') NOT NULL,
  `total_found` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_imported` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_failed` INT UNSIGNED NOT NULL DEFAULT 0,
  `imported_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_qil_bank` FOREIGN KEY (`bank_id`) REFERENCES `question_banks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_qil_user` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `question_import_items`;
CREATE TABLE `question_import_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `import_log_id` BIGINT UNSIGNED NOT NULL,
  `item_number` INT UNSIGNED NOT NULL,
  `raw_content` LONGTEXT NULL,
  `parse_status` ENUM('valid', 'invalid', 'warning') NOT NULL,
  `error_message` TEXT NULL,
  `saved_question_id` BIGINT UNSIGNED NULL,
  CONSTRAINT `fk_qii_log` FOREIGN KEY (`import_log_id`) REFERENCES `question_import_logs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `data_import_logs`;
CREATE TABLE `data_import_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module` VARCHAR(32) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `total_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `success_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `failed_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `imported_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_dil_user` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `data_import_errors`;
CREATE TABLE `data_import_errors` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `import_log_id` BIGINT UNSIGNED NOT NULL,
  `row_number` INT UNSIGNED NOT NULL,
  `row_data` TEXT NULL,
  `error_reason` TEXT NOT NULL,
  CONSTRAINT `fk_die_log` FOREIGN KEY (`import_log_id`) REFERENCES `data_import_logs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `announcements`;
CREATE TABLE `announcements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(160) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `target_role` ENUM('all', 'siswa', 'guru', 'pengawas') NOT NULL DEFAULT 'all',
  `target_class_id` INT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_ann_class` FOREIGN KEY (`target_class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ann_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(128) NOT NULL,
  `message` TEXT NOT NULL,
  `link_url` VARCHAR(255) NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_notif_user` (`user_id`, `is_read`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `migrations_log`;
CREATE TABLE `migrations_log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `migration_file` VARCHAR(128) NOT NULL UNIQUE,
  `batch` INT UNSIGNED NOT NULL DEFAULT 1,
  `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ==============================================================================
-- DATA AWAL LENGKAP (SEEDER DATASET)
-- ==============================================================================

-- 1. Roles
INSERT INTO `roles` (`id`, `role_name`, `display_name`, `description`) VALUES
(1, 'super_admin', 'Super Admin', 'Akses penuh ke seluruh modul, audit log, dan backup'),
(2, 'admin', 'Admin/Operator', 'Pengelolaan data master, jadwal ujian, dan operasional'),
(3, 'guru', 'Guru', 'Pengelolaan bank soal, pembuatan paket soal, dan penilaian esai'),
(4, 'pengawas', 'Pengawas', 'Pengawasan pelaksanaan ujian, pemantauan status, dan pencatatan kejadian'),
(5, 'siswa', 'Siswa', 'Peserta ujian berbasis komputer');

-- 2. Permissions
INSERT INTO `permissions` (`permission_key`, `display_name`, `module`, `description`) VALUES
('master_data.view', 'Lihat Master Data', 'master_data', 'Melihat data kelas, mata pelajaran, dan tahun ajaran'),
('master_data.manage', 'Kelola Master Data', 'master_data', 'Menambah, mengedit, dan menghapus data master'),
('users.manage', 'Kelola Akun Pengguna', 'users', 'Manajemen akun login seluruh role'),
('question_banks.create', 'Buat Bank Soal', 'question_banks', 'Membuat bank soal baru'),
('question_banks.manage', 'Kelola Seluruh Bank Soal', 'question_banks', 'Mengedit dan menghapus bank soal'),
('exam_packages.manage', 'Kelola Paket Soal', 'exam_packages', 'Membuat paket soal dan snapshot'),
('exams.manage', 'Kelola Jadwal Ujian', 'exams', 'Membuat jadwal, mengatur token, dan peserta'),
('exams.supervise', 'Mengawasi Ujian', 'exams', 'Memantau peserta dan mencatat insiden'),
('exams.take', 'Mengerjakan Ujian', 'exams', 'Akses pengerjaan ujian siswa'),
('grading.essay', 'Menilai Soal Esai', 'grading', 'Pemberian nilai esai secara manual'),
('reports.view', 'Lihat Laporan & Rekap', 'reports', 'Melihat hasil dan analisis butir soal'),
('reports.export', 'Ekspor Nilai & Laporan', 'reports', 'Mengunduh laporan dalam format Excel dan PDF'),
('system.settings', 'Ubah Pengaturan Sistem', 'settings', 'Konfigurasi identitas madrasah dan browser'),
('system.backup', 'Backup & Restore Database', 'settings', 'Pencadangan dan pemulihan basis data'),
('audit.view', 'Lihat Audit Log', 'audit', 'Memantau jejak aktivitas pengguna');

-- 3. System Settings
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_group`, `description`) VALUES
('madrasah_name', 'MA Bahrul Ulum Kepohbaru', 'identity', 'Nama Resmi Lembaga Madrasah'),
('madrasah_nsm', '131135220015', 'identity', 'Nomor Statistik Madrasah'),
('madrasah_npsn', '20584412', 'identity', 'Nomor Pokok Sekolah Nasional'),
('madrasah_address', 'Jl. Babat-Bojonegoro, Kepohbaru, Bojonegoro, Jawa Timur', 'identity', 'Alamat Lengkap'),
('madrasah_headmaster', 'Drs. H. Mohammad Sholih, M.Pd.I', 'identity', 'Nama Kepala Madrasah'),
('cbt_proctor_name', 'Ahmad Musyafa, S.Kom', 'cbt', 'Nama Proktor Utama CBT'),
('cbt_academic_year', '2026/2027', 'academic', 'Tahun Ajaran Berjalan'),
('cbt_semester', 'ganjil', 'academic', 'Semester Berjalan'),
('cbt_autosave_seconds', '15', 'exam', 'Frekuensi Autosave Jawaban Siswa (detik)'),
('cbt_kkm_default', '75.00', 'grading', 'Standar Ketuntasan Minimal Default');

-- 4. Academic Years & Semesters
INSERT INTO `academic_years` (`id`, `year_name`, `is_active`) VALUES
(1, '2026/2027', 1),
(2, '2025/2026', 0);

INSERT INTO `semesters` (`id`, `academic_year_id`, `semester`, `is_active`) VALUES
(1, 1, 'ganjil', 1),
(2, 1, 'genap', 0),
(3, 2, 'ganjil', 0),
(4, 2, 'genap', 0);

-- 5. Classes
INSERT INTO `classes` (`id`, `class_name`, `grade_level`, `major`) VALUES
(1, 'X MIPA 1', 10, 'MIPA'),
(2, 'X MIPA 2', 10, 'MIPA'),
(3, 'X IPS 1', 10, 'IPS'),
(4, 'X Keagamaan', 10, 'Keagamaan'),
(5, 'XI MIPA 1', 11, 'MIPA'),
(6, 'XI MIPA 2', 11, 'MIPA'),
(7, 'XI IPS 1', 11, 'IPS'),
(8, 'XI Keagamaan', 11, 'Keagamaan'),
(9, 'XII MIPA 1', 12, 'MIPA'),
(10, 'XII IPS 1', 12, 'IPS'),
(11, 'XII Keagamaan', 12, 'Keagamaan');

-- 6. Subjects
INSERT INTO `subjects` (`id`, `subject_code`, `subject_name`, `category`) VALUES
(1, 'AGM01', 'Al-Qur\'an Hadis', 'keagamaan'),
(2, 'AGM02', 'Akidah Akhlak', 'keagamaan'),
(3, 'AGM03', 'Fikih', 'keagamaan'),
(4, 'AGM04', 'Sejarah Kebudayaan Islam (SKI)', 'keagamaan'),
(5, 'AGM05', 'Bahasa Arab', 'keagamaan'),
(6, 'IND01', 'Bahasa Indonesia', 'umum'),
(7, 'MAT01', 'Matematika Wajib', 'umum'),
(8, 'ING01', 'Bahasa Inggris', 'umum'),
(9, 'IPA01', 'Fisika', 'peminatan'),
(10, 'IPA02', 'Kimia', 'peminatan'),
(11, 'IPA03', 'Biologi', 'peminatan'),
(12, 'IPS01', 'Ekonomi', 'peminatan'),
(13, 'IPS02', 'Sosiologi', 'peminatan');

-- 7. Users
-- Default Hash for admin123: $2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76
-- Default Hash for guru123: $2y$10$k1wXNfeU6E73KqDsqnU5y.9J7v9B/p/3b1J7R1e0t3gWcO1lY6vS6
-- Default Hash for pengawas123: $2y$10$wK1xNfeU6E73KqDsqnU5y.9J7v9B/p/3b1J7R1e0t3gWcO1lY6vS6
-- Default Hash for siswa123: $2y$10$1W2xNfeU6E73KqDsqnU5y.9J7v9B/p/3b1J7R1e0t3gWcO1lY6vS6
INSERT INTO `users` (`id`, `username`, `password`, `name`, `email`, `role_id`, `is_active`) VALUES
(1, 'superadmin', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Super Administrator', 'superadmin@mabahrululum-kepohbaru.sch.id', 1, 1),
(2, 'admin', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Operator CBT Madrasah', 'operator@mabahrululum-kepohbaru.sch.id', 2, 1),
(3, 'guru.ahmad', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Ahmad Fauzi, S.Pd', 'ahmad.fauzi@mabahrululum-kepohbaru.sch.id', 3, 1),
(4, 'guru.fatimah', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Fatimah Az-Zahra, M.Pd', 'fatimah@mabahrululum-kepohbaru.sch.id', 3, 1),
(5, 'pengawas01', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Ust. Mansyur, S.Ag', 'mansyur@mabahrululum-kepohbaru.sch.id', 4, 1),
(6, 'siswa001', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Muhammad Rizky Pratama', 'rizky@siswa.mabahrululum.sch.id', 5, 1),
(7, 'siswa002', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Aisyah Putri Rahmawati', 'aisyah@siswa.mabahrululum.sch.id', 5, 1),
(8, 'siswa003', '$2y$10$wBsmD4DcfuA2X6o.qVwSze0e9N6dYqUffG15cKkWYmG7Xv4b4uR76', 'Budi Santoso', 'budi@siswa.mabahrululum.sch.id', 5, 1);

-- 8. Teachers Profile
INSERT INTO `teachers` (`id`, `user_id`, `nip`, `nuptk`, `gender`, `specialization`) VALUES
(1, 3, '198507122010011008', '4538763665200032', 'L', 'Matematika Wajib'),
(2, 4, '199003202015022003', '7849768669210041', 'P', 'Bahasa Arab & Al-Qur\'an Hadis');

-- 9. Students Profile
INSERT INTO `students` (`id`, `user_id`, `class_id`, `nisn`, `nis`, `gender`, `birth_place`, `birth_date`) VALUES
(1, 6, 9, '0068441201', '24251001', 'L', 'Bojonegoro', '2008-04-12'),
(2, 7, 9, '0068441202', '24251002', 'P', 'Tuban', '2008-08-25'),
(3, 8, 9, '0068441203', '24251003', 'L', 'Lamongan', '2008-01-19');

-- 10. Browser Settings
INSERT INTO `browser_settings` (`id`, `name`, `required_browser`, `min_browser_version`, `allow_fullscreen`, `block_tab_switch`, `max_violations`, `action_on_limit`) VALUES
(1, 'Pengaturan Ujian Standar Chrome', 'chrome', '90.0', 1, 1, 3, 'warning');

-- 11. Announcements
INSERT INTO `announcements` (`id`, `title`, `content`, `target_role`, `target_class_id`, `created_by`, `is_active`) VALUES
(1, 'Selamat Datang di CBT MA Bahrul Ulum Kepohbaru', 'Sistem Computer Based Test (CBT) telah aktif dan siap digunakan untuk pelaksanaan asesmen berbasis komputer. Gunakan Google Chrome versi terbaru dan patuhi tata tertib ujian.', 'all', NULL, 1, 1);

-- 12. Record Migration
INSERT INTO `migrations_log` (`migration_file`, `batch`) VALUES
('01_initial_schema.sql', 1),
('02_complete_schema.sql', 2);