-- Database Schema and Data Dump
-- Generated: 2026-01-28 15:36:31

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `admin_privileges`;
CREATE TABLE `admin_privileges` (
  `privilege_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `privilege_key` varchar(255) NOT NULL,
  `privilege_value` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`privilege_id`),
  UNIQUE KEY `user_id` (`user_id`,`privilege_key`),
  CONSTRAINT `admin_privileges_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) NOT NULL,
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `class_name` (`class_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `classes` VALUES
("1","Jss 1"),
("2","Jss 2");

DROP TABLE IF EXISTS `exam_assignments`;
CREATE TABLE `exam_assignments` (
  `assignment_id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`assignment_id`),
  UNIQUE KEY `unique_assignment` (`exam_id`,`user_id`),
  KEY `fk_ea_user` (`user_id`),
  CONSTRAINT `fk_ea_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ea_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `exams`;
CREATE TABLE `exams` (
  `exam_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `duration` int(11) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  `class_id` int(11) DEFAULT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `randomize_questions` tinyint(1) DEFAULT 1,
  `session` varchar(20) DEFAULT NULL,
  `term` varchar(20) DEFAULT NULL,
  `question_quota` int(11) DEFAULT NULL,
  `show_result_to_student` tinyint(1) DEFAULT 0,
  `allowed_attempts` int(11) DEFAULT 1,
  `exam_type` enum('test','exam') NOT NULL DEFAULT 'test',
  `total_score` int(11) NOT NULL DEFAULT 20,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `start_at` datetime DEFAULT NULL,
  `end_at` datetime DEFAULT NULL,
  PRIMARY KEY (`exam_id`),
  KEY `fk_exam_class` (`class_id`),
  KEY `fk_exam_subject` (`subject_id`),
  CONSTRAINT `fk_exam_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exam_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `media_library`;
CREATE TABLE `media_library` (
  `media_id` int(11) NOT NULL AUTO_INCREMENT,
  `file_path` varchar(255) NOT NULL,
  `caption` varchar(255) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `passages`;
CREATE TABLE `passages` (
  `passage_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  `content` text NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `session` varchar(20) DEFAULT NULL,
  `term` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`passage_id`),
  KEY `fk_passage_subject` (`subject_id`),
  KEY `fk_passage_class` (`class_id`),
  CONSTRAINT `fk_passage_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_passage_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_id` int(11) DEFAULT NULL,
  `exam_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `question_text` text NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `passage_id` int(11) DEFAULT NULL,
  `media_id` int(11) DEFAULT NULL,
  `option_a` varchar(255) NOT NULL,
  `option_b` varchar(255) NOT NULL,
  `option_c` varchar(255) NOT NULL,
  `option_d` varchar(255) NOT NULL,
  `option_e` varchar(255) DEFAULT NULL,
  `correct_answer` enum('A','B','C','D','E') NOT NULL,
  `session` varchar(20) DEFAULT NULL,
  `term` varchar(20) DEFAULT NULL,
  `question_type` enum('single_choice','multiple_choice','true_false') NOT NULL DEFAULT 'single_choice',
  PRIMARY KEY (`question_id`),
  KEY `fk_q_subject` (`subject_id`),
  KEY `fk_q_exam` (`exam_id`),
  KEY `fk_q_class` (`class_id`),
  KEY `fk_q_passage` (`passage_id`),
  KEY `fk_q_media` (`media_id`),
  CONSTRAINT `fk_q_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_q_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_q_media` FOREIGN KEY (`media_id`) REFERENCES `media_library` (`media_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_q_passage` FOREIGN KEY (`passage_id`) REFERENCES `passages` (`passage_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_q_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `results`;
CREATE TABLE `results` (
  `result_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `exam_id` int(11) NOT NULL,
  `score` decimal(5,2) NOT NULL,
  `total_questions` int(11) NOT NULL,
  `date_taken` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('completed','pending') DEFAULT 'completed',
  PRIMARY KEY (`result_id`),
  KEY `fk_res_user` (`user_id`),
  KEY `fk_res_exam` (`exam_id`),
  CONSTRAINT `fk_res_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_res_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `settings` VALUES
("1","school_name","My School CBT"),
("2","school_logo","");

DROP TABLE IF EXISTS `student_activity_log`;
CREATE TABLE `student_activity_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `activity` varchar(50) NOT NULL,
  `exam_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`log_id`),
  KEY `idx_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `student_activity_log` VALUES
("1","1","login",NULL,"2026-01-25 16:21:55"),
("2","2","login",NULL,"2026-01-25 16:23:13"),
("3","1","login",NULL,"2026-01-25 16:48:42"),
("4","3","login",NULL,"2026-01-25 16:54:40"),
("5","1","login",NULL,"2026-01-25 17:06:07"),
("6","1","login",NULL,"2026-01-25 20:58:18"),
("7","1","login",NULL,"2026-01-25 22:47:08"),
("8","1","login",NULL,"2026-01-27 21:58:21"),
("9","4","login",NULL,"2026-01-28 15:11:10"),
("10","1","login",NULL,"2026-01-28 15:11:24"),
("11","5","login",NULL,"2026-01-28 15:12:19"),
("12","4","login",NULL,"2026-01-28 15:29:40"),
("13","1","login",NULL,"2026-01-28 15:30:19");

DROP TABLE IF EXISTS `student_exam_schedule`;
CREATE TABLE `student_exam_schedule` (
  `schedule_id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `slot_start` datetime NOT NULL,
  `slot_end` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`schedule_id`),
  UNIQUE KEY `uniq_exam_user` (`exam_id`,`user_id`),
  KEY `fk_ses_user` (`user_id`),
  CONSTRAINT `fk_ses_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ses_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `subject_code` varchar(20) NOT NULL,
  PRIMARY KEY (`subject_id`),
  UNIQUE KEY `subject_code` (`subject_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `subjects` VALUES
("1","Mathematics","Math");

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'student',
  `full_name` varchar(100) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `access_pin` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `permissions` text DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `fk_user_class` (`class_id`),
  CONSTRAINT `fk_user_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `users` VALUES
("1","admin","$2y$10$v7toJ9nBMD7JhPK1..1cV.udYI73xGrLMBvR9WM7oxlxQEwKL4gtS","admin",NULL,NULL,NULL,NULL,"2026-01-25 16:19:05",NULL),
("2","djs/2025/001","$2y$10$JzV264sTQLHpKu8vsvSwUOjnpgiZVJK5bdH7P314SZsiEX8dka3Fe","student","Olayemi Erioluwa",NULL,NULL,"1","2026-01-25 16:23:03",NULL),
("4","ibidapo","$2y$10$OqksXP.zpMrPjO8ExZDZEeUbDqlqWfNPJlyIZXJFHWqAsFDiMLNJq","teacher","Mr Ibidapo",NULL,NULL,NULL,"2026-01-28 15:10:59","[\"manage_questions\",\"manage_exams\",\"manage_results\"]"),
("5","john","$2y$10$diS6x6vl8i2Jwmnxr3gQru3UP5JbMNSU.GglyYxKKj47zEgtnJaEi","admin","Mr John",NULL,NULL,NULL,"2026-01-28 15:12:12","[]");

SET FOREIGN_KEY_CHECKS=1;
