--
-- Host: localhost    Database: examlab_db
-- ------------------------------------------------------


--
-- Table structure for table `cache`
--

CREATE TABLE `cache` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cache`
--


--
-- Table structure for table `cache_locks`
--

CREATE TABLE `cache_locks` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_locks_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cache_locks`
--


--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `grade` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `academic_year` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` VALUES (1,'X-A','X','2025/2026','2026-04-13 04:47:33','2026-04-13 04:47:33'),(2,'X-B','X','2025/2026','2026-04-13 04:47:33','2026-04-13 04:47:33'),(3,'XI-A','XI','2025/2026','2026-04-13 04:47:33','2026-04-13 04:47:33'),(4,'XI-B','XI','2025/2026','2026-04-13 04:47:33','2026-04-13 04:47:33');

--
-- Table structure for table `exam_sessions`
--

CREATE TABLE `exam_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exam_id` bigint unsigned NOT NULL,
  `session_number` int NOT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `exam_sessions_exam_id_index` (`exam_id`),
  CONSTRAINT `exam_sessions_exam_id_foreign` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `exam_sessions`
--

INSERT INTO `exam_sessions` VALUES (1,2,1,'2026-04-13 12:02:00','2026-04-13 16:02:00','2026-04-13 05:03:04','2026-04-13 05:03:04');

--
-- Table structure for table `exams`
--

CREATE TABLE `exams` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('excel','word') COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_id` bigint unsigned NOT NULL,
  `duration_minutes` int NOT NULL DEFAULT '60',
  `scheduled_at` timestamp NULL DEFAULT NULL,
  `status` enum('draft','active','closed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `created_by` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `exams_created_by_foreign` (`created_by`),
  KEY `exams_class_id_status_type_index` (`class_id`,`status`,`type`),
  CONSTRAINT `exams_class_id_foreign` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exams_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `exams`
--

INSERT INTO `exams` VALUES (2,'UM','word',1,60,'2026-04-13 12:01:00','active',1,'2026-04-13 05:02:01','2026-04-13 05:03:29');

--
-- Table structure for table `excel_cells`
--

CREATE TABLE `excel_cells` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `question_id` bigint unsigned NOT NULL,
  `row` int NOT NULL,
  `col` int NOT NULL,
  `content` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT '1',
  `is_answer_cell` tinyint(1) NOT NULL DEFAULT '0',
  `expected_formula_keyword` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expected_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `excel_cells_question_id_index` (`question_id`),
  CONSTRAINT `excel_cells_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `excel_cells`
--


--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `job_batches`
--


--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--


--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES (1,'0001_01_01_000000_create_users_table',1),(2,'0001_01_01_000001_create_cache_table',1),(3,'0001_01_01_000002_create_jobs_table',1),(4,'2024_01_01_000001_create_classes_table',1),(5,'2024_01_01_000002_add_fields_to_users_table',1),(6,'2024_01_01_000003_create_exams_table',1),(7,'2024_01_01_000004_create_questions_table',1),(8,'2024_01_01_000005_create_excel_cells_table',1),(9,'2024_01_01_000006_create_exam_sessions_table',1),(10,'2024_01_01_000007_create_student_sessions_table',1),(11,'2024_01_01_000008_create_student_answers_table',1),(12,'2024_01_01_000009_create_results_table',1),(13,'2026_04_13_070654_create_personal_access_tokens_table',1);

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `password_reset_tokens`
--


--
-- Table structure for table `personal_access_tokens`
--

CREATE TABLE `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `abilities` text COLLATE utf8mb4_unicode_ci,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `personal_access_tokens`
--


--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exam_id` bigint unsigned NOT NULL,
  `instruction` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('excel','word') COLLATE utf8mb4_unicode_ci NOT NULL,
  `word_content` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `questions_exam_id_index` (`exam_id`),
  CONSTRAINT `questions_exam_id_foreign` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` VALUES (2,2,'Rapikan','word','<p><strong>ajkdkkbbdj jksnndnb a </strong></p><p style=\"text-align: right;\"><strong>mjkdkln jjskdn jkkkllsjn jjdklsnn nnjskkd</strong></p><p style=\"text-align: center;\">njskkkahbnndjjkam nnsjks</p><ol><li><p style=\"text-align: left;\">djkjfkl </p></li></ol><p></p>','2026-04-13 05:02:46','2026-04-13 05:02:46');

--
-- Table structure for table `results`
--

CREATE TABLE `results` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `student_session_id` bigint unsigned NOT NULL,
  `score` double DEFAULT NULL,
  `feedback` text COLLATE utf8mb4_unicode_ci,
  `corrected_by` bigint unsigned DEFAULT NULL,
  `corrected_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `results_corrected_by_foreign` (`corrected_by`),
  KEY `results_student_session_id_index` (`student_session_id`),
  CONSTRAINT `results_corrected_by_foreign` FOREIGN KEY (`corrected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `results_student_session_id_foreign` FOREIGN KEY (`student_session_id`) REFERENCES `student_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `results`
--

INSERT INTO `results` VALUES (1,1,100,'mantaf',1,'2026-04-13 05:26:07','2026-04-13 05:26:07','2026-04-13 05:26:07');

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--


--
-- Table structure for table `student_answers`
--

CREATE TABLE `student_answers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `student_session_id` bigint unsigned NOT NULL,
  `question_id` bigint unsigned NOT NULL,
  `excel_cell_answers` json DEFAULT NULL,
  `word_answer` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_answers_question_id_foreign` (`question_id`),
  KEY `student_answers_student_session_id_question_id_index` (`student_session_id`,`question_id`),
  CONSTRAINT `student_answers_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `student_answers_student_session_id_foreign` FOREIGN KEY (`student_session_id`) REFERENCES `student_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `student_answers`
--

INSERT INTO `student_answers` VALUES (1,1,2,NULL,'<p><strong>ajkdkkbbdj jksnndnb a</strong></p><p style=\"text-align: left;\"><strong>mjkdkln jjskdn jkkkllsjn jjdklsnn nnjskkd</strong></p><p style=\"text-align: left;\">njskkkahbnndjjkam nnsjks</p><ol><li><p style=\"text-align: left;\">djkjfkl</p></li></ol><p></p>','2026-04-13 05:03:47','2026-04-13 05:03:50'),(2,3,2,NULL,NULL,'2026-04-13 05:14:32','2026-04-13 05:14:32'),(3,5,2,NULL,NULL,'2026-04-13 05:16:24','2026-04-13 05:16:24'),(4,2,2,NULL,NULL,'2026-04-13 05:20:39','2026-04-13 05:20:39');

--
-- Table structure for table `student_sessions`
--

CREATE TABLE `student_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exam_session_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `status` enum('waiting','ongoing','submitted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `started_at` timestamp NULL DEFAULT NULL,
  `submitted_at` timestamp NULL DEFAULT NULL,
  `tab_switch_count` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_sessions_user_id_foreign` (`user_id`),
  KEY `student_sessions_exam_session_id_user_id_index` (`exam_session_id`,`user_id`),
  CONSTRAINT `student_sessions_exam_session_id_foreign` FOREIGN KEY (`exam_session_id`) REFERENCES `exam_sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `student_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `student_sessions`
--

INSERT INTO `student_sessions` VALUES (1,1,2,'submitted','2026-04-13 05:03:37','2026-04-13 05:03:56',0,'2026-04-13 05:03:04','2026-04-13 05:03:56'),(2,1,3,'submitted','2026-04-13 05:20:34','2026-04-13 05:20:39',0,'2026-04-13 05:03:04','2026-04-13 05:20:39'),(3,1,4,'submitted','2026-04-13 05:14:23','2026-04-13 05:14:32',0,'2026-04-13 05:03:04','2026-04-13 05:14:32'),(4,1,5,'waiting',NULL,NULL,0,'2026-04-13 05:03:04','2026-04-13 05:03:04'),(5,1,6,'submitted','2026-04-13 05:15:53','2026-04-13 05:19:31',2,'2026-04-13 05:03:04','2026-04-13 05:19:31');

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` enum('admin','student') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'student',
  `nis` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `class_id` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_class_id_foreign` (`class_id`),
  CONSTRAINT `users_class_id_foreign` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,'Admin ExamLab','admin@examlab.test','admin',NULL,NULL,'$2y$12$OFkA0RvxIw9rtn1P9S7aHONYeokGv.6QHTkAV57KrILPVfvN8PXFK',NULL,'2026-04-13 04:47:34','2026-04-13 04:47:34',NULL),(2,'Andi Pratama','andi.pratama@siswa.test','student','2526001',NULL,'$2y$12$ZB4TIKEZBpECjg8PKxIoeuzuXfEgfRA1kaZT6ZABnmEcpCIyeS.Qu',NULL,'2026-04-13 04:47:34','2026-04-13 04:47:34',1),(3,'Budi Santoso','budi.santoso@siswa.test','student','2526002',NULL,'$2y$12$pbmRJdSKiaolHaZJI63AYOZwHZeVzKZiZeKjyQhbbz2YnpQ3qmvEy',NULL,'2026-04-13 04:47:34','2026-04-13 04:47:34',1),(4,'Citra Dewi','citra.dewi@siswa.test','student','2526003',NULL,'$2y$12$/LeHzDtelzs9oHXH0O.VTef3gNzHri810jUZx1wBq28VKXIigw7/i',NULL,'2026-04-13 04:47:35','2026-04-13 04:47:35',1),(5,'Dian Rahayu','dian.rahayu@siswa.test','student','2526004',NULL,'$2y$12$7aCq8qn61dFsNyxODJByCO4seDaB0TvP30AzcwonMIjieucNDLDYm',NULL,'2026-04-13 04:47:35','2026-04-13 04:47:35',1),(6,'Eko Wahyudi','eko.wahyudi@siswa.test','student','2526005',NULL,'$2y$12$84BLHQYFPrNUB9PnWc6sYenO9KsltAISsQZBa7/voJo8nDTsXEzP2',NULL,'2026-04-13 04:47:36','2026-04-13 04:47:36',1);


