-- File: sql/schema_eujian.sql
-- SIMDATADIK - Schema Modul E-UJIAN / CBT
-- Menambah schema baru tanpa mengubah schema lama.

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `exam_question_banks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) UNSIGNED NOT NULL,
  `subject_id` bigint(20) UNSIGNED DEFAULT NULL,
  `grade_level` varchar(50) NOT NULL,
  `competency_code` varchar(150) DEFAULT NULL,
  `cognitive_level` varchar(50) DEFAULT NULL,
  `question_type` enum('single_choice','multiple_choice','short_answer','essay') NOT NULL DEFAULT 'single_choice',
  `question_text` longtext NOT NULL,
  `question_media` varchar(255) DEFAULT NULL COMMENT 'Path gambar/media pendukung',
  `options_json` longtext DEFAULT NULL COMMENT 'JSON opsi untuk soal pilihan ganda',
  `correct_answer` longtext DEFAULT NULL COMMENT 'Kunci jawaban: option key / JSON / teks',
  `score` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `status` enum('draft','published') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_eqb_school_subject_status` (`school_id`, `subject_id`, `status`),
  KEY `idx_eqb_grade_level` (`grade_level`),
  KEY `idx_eqb_competency_code` (`competency_code`),
  KEY `idx_eqb_cognitive_level` (`cognitive_level`),
  KEY `idx_eqb_question_type` (`question_type`),
  KEY `idx_eqb_created_by` (`created_by`),
  CONSTRAINT `fk_eqb_school`
    FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_eqb_subject`
    FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_eqb_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_packages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) UNSIGNED NOT NULL,
  `subject_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `exam_type` varchar(50) NOT NULL COMMENT 'UH, PTS, PAS, PAT, REMEDIAL, TRYOUT, dll',
  `exam_mode` enum('regular','tryout') NOT NULL DEFAULT 'regular',
  `grade_level` varchar(50) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `duration_minutes` int(10) UNSIGNED NOT NULL DEFAULT 60,
  `total_questions` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `question_selection_mode` enum('fixed','random') NOT NULL DEFAULT 'fixed',
  `shuffle_questions` tinyint(1) NOT NULL DEFAULT 1,
  `shuffle_options` tinyint(1) NOT NULL DEFAULT 1,
  `attempt_limit` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `allow_review_before_submit` tinyint(1) NOT NULL DEFAULT 1,
  `show_result_to_student` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_ep_school_status` (`school_id`, `status`),
  KEY `idx_ep_subject_grade` (`subject_id`, `grade_level`),
  KEY `idx_ep_exam_type` (`exam_type`),
  KEY `idx_ep_schedule` (`start_datetime`, `end_datetime`),
  KEY `idx_ep_created_by` (`created_by`),
  CONSTRAINT `fk_ep_school`
    FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ep_subject`
    FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_ep_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_package_questions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_package_id` bigint(20) UNSIGNED NOT NULL,
  `question_bank_id` bigint(20) UNSIGNED NOT NULL,
  `order_index` int(10) UNSIGNED DEFAULT NULL COMMENT 'NULL/0 = dapat dirandom oleh engine',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_epq_package_question` (`exam_package_id`, `question_bank_id`),
  KEY `idx_epq_order` (`exam_package_id`, `order_index`),
  KEY `idx_epq_question_bank` (`question_bank_id`),
  CONSTRAINT `fk_epq_exam_package`
    FOREIGN KEY (`exam_package_id`) REFERENCES `exam_packages` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_epq_question_bank`
    FOREIGN KEY (`question_bank_id`) REFERENCES `exam_question_banks` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_sessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_package_id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `started_at` datetime DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `last_activity_at` datetime DEFAULT NULL,
  `auto_submitted` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('pending','in_progress','submitted','graded') NOT NULL DEFAULT 'pending',
  `total_score` decimal(10,2) DEFAULT NULL,
  `attempt_number` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_es_package_student_attempt` (`exam_package_id`, `student_id`, `attempt_number`),
  KEY `idx_es_student_status` (`student_id`, `status`),
  KEY `idx_es_package_status` (`exam_package_id`, `status`),
  KEY `idx_es_started_at` (`started_at`),
  CONSTRAINT `fk_es_exam_package`
    FOREIGN KEY (`exam_package_id`) REFERENCES `exam_packages` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_es_student`
    FOREIGN KEY (`student_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_answers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_session_id` bigint(20) UNSIGNED NOT NULL,
  `question_bank_id` bigint(20) UNSIGNED NOT NULL,
  `answer_text` longtext DEFAULT NULL COMMENT 'Essay / isian singkat',
  `answer_option` longtext DEFAULT NULL COMMENT 'Opsi jawaban / JSON pilihan',
  `is_marked_for_review` tinyint(1) NOT NULL DEFAULT 0,
  `is_correct` tinyint(1) DEFAULT NULL,
  `score_obtained` decimal(10,2) DEFAULT NULL,
  `teacher_feedback` text DEFAULT NULL,
  `graded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `graded_at` datetime DEFAULT NULL,
  `answered_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ea_session_question` (`exam_session_id`, `question_bank_id`),
  KEY `idx_ea_question_bank` (`question_bank_id`),
  KEY `idx_ea_is_correct` (`is_correct`),
  KEY `idx_ea_graded_by` (`graded_by`),
  CONSTRAINT `fk_ea_exam_session`
    FOREIGN KEY (`exam_session_id`) REFERENCES `exam_sessions` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ea_question_bank`
    FOREIGN KEY (`question_bank_id`) REFERENCES `exam_question_banks` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ea_graded_by`
    FOREIGN KEY (`graded_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_result_analytics` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_package_id` bigint(20) UNSIGNED NOT NULL,
  `question_bank_id` bigint(20) UNSIGNED NOT NULL,
  `total_attempts` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `total_correct` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `total_incorrect` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `last_calculated_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_era_package_question` (`exam_package_id`, `question_bank_id`),
  KEY `idx_era_question_bank` (`question_bank_id`),
  CONSTRAINT `fk_era_exam_package`
    FOREIGN KEY (`exam_package_id`) REFERENCES `exam_packages` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_era_question_bank`
    FOREIGN KEY (`question_bank_id`) REFERENCES `exam_question_banks` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_class_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_package_id` bigint(20) UNSIGNED NOT NULL,
  `class_group_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_eca_package_class` (`exam_package_id`, `class_group_id`),
  KEY `idx_eca_class_group` (`class_group_id`),
  CONSTRAINT `fk_eca_exam_package`
    FOREIGN KEY (`exam_package_id`) REFERENCES `exam_packages` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_eca_class_group`
    FOREIGN KEY (`class_group_id`) REFERENCES `class_groups` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;