-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------


-- Dumping structure for table candy_exam.agama
CREATE TABLE IF NOT EXISTS `agama` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.agama: ~0 rows (approximately)

-- Dumping structure for table candy_exam.bank_soal
CREATE TABLE IF NOT EXISTS `bank_soal` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `kode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `deskripsi` text COLLATE utf8mb4_general_ci,
  `created_by` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_public` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `kode` (`kode`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `bank_soal_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.bank_soal: ~0 rows (approximately)

-- Dumping structure for table candy_exam.exambro_block
CREATE TABLE IF NOT EXISTS `exambro_block` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `package_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `app_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `category` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_blocked` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.exambro_menu: ~0 rows (approximately)

-- Dumping structure for table candy_exam.hasil_ujian
CREATE TABLE IF NOT EXISTS `hasil_ujian` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `ujian_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `peserta_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `guest_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `waktu_mulai` datetime DEFAULT NULL,
  `waktu_selesai` datetime DEFAULT NULL,
  `nilai_pg` double NOT NULL DEFAULT '0',
  `nilai_esai` double NOT NULL DEFAULT '0',
  `nilai_total` double NOT NULL DEFAULT '0',
  `soal_benar` int NOT NULL DEFAULT '0',
  `soal_salah` int NOT NULL DEFAULT '0',
  `kosong` int NOT NULL DEFAULT '0',
  `poin_benar` int NOT NULL DEFAULT '0',
  `poin_salah` int NOT NULL DEFAULT '0',
  `poin_maksimal` int NOT NULL DEFAULT '0',
  `jawaban_json` text COLLATE utf8mb4_general_ci,
  `urutan_soal` json DEFAULT NULL,
  `urutan_opsi` json DEFAULT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `token_valid` tinyint(1) NOT NULL DEFAULT '0',
  `is_device_active` tinyint(1) NOT NULL DEFAULT '0',
  `status` enum('belum_mulai','sedang_ujian','selesai') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'belum_mulai',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ujian_id_peserta_id` (`ujian_id`,`peserta_id`),
  KEY `ujian_id` (`ujian_id`),
  KEY `peserta_id` (`peserta_id`),
  CONSTRAINT `hasil_ujian_peserta_id_foreign` FOREIGN KEY (`peserta_id`) REFERENCES `peserta` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `hasil_ujian_ujian_id_foreign` FOREIGN KEY (`ujian_id`) REFERENCES `ujian` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.hasil_ujian: ~0 rows (approximately)

-- Dumping structure for table candy_exam.jawaban
CREATE TABLE IF NOT EXISTS `jawaban` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `ujian_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `peserta_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `guest_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `soal_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `jawaban` text COLLATE utf8mb4_general_ci,
  `skor` float DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jawaban_peserta_id_foreign` (`peserta_id`),
  KEY `jawaban_soal_id_foreign` (`soal_id`),
  KEY `ujian_id_peserta_id_soal_id` (`ujian_id`,`peserta_id`,`soal_id`),
  CONSTRAINT `jawaban_peserta_id_foreign` FOREIGN KEY (`peserta_id`) REFERENCES `peserta` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `jawaban_soal_id_foreign` FOREIGN KEY (`soal_id`) REFERENCES `soal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `jawaban_ujian_id_foreign` FOREIGN KEY (`ujian_id`) REFERENCES `ujian` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.jawaban: ~0 rows (approximately)

-- Dumping structure for table candy_exam.jurusan
CREATE TABLE IF NOT EXISTS `jurusan` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.jurusan: ~0 rows (approximately)

-- Dumping structure for table candy_exam.kelas
CREATE TABLE IF NOT EXISTS `kelas` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.kelas: ~0 rows (approximately)

-- Dumping structure for table candy_exam.media_files
CREATE TABLE IF NOT EXISTS `media_files` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `type` enum('image','audio','video') COLLATE utf8mb4_general_ci NOT NULL,
  `mime_type` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `used_in_soal` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `media_files_used_in_soal_foreign` (`used_in_soal`),
  CONSTRAINT `media_files_used_in_soal_foreign` FOREIGN KEY (`used_in_soal`) REFERENCES `soal` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.media_files: ~0 rows (approximately)

-- Dumping structure for table candy_exam.migrations
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `group` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `namespace` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `time` int NOT NULL,
  `batch` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.migrations: ~0 rows (approximately)
INSERT INTO `migrations` (`id`, `version`, `class`, `group`, `namespace`, `time`, `batch`) VALUES
	(1, '2025-06-17-132907', 'App\\Database\\Migrations\\CreateUsersTable', 'default', 'App', 1757809987, 1),
	(2, '2025-06-17-154413', 'App\\Database\\Migrations\\CreateSettingsTable', 'default', 'App', 1757809987, 1),
	(3, '2025-06-17-173834', 'App\\Database\\Migrations\\CreateSettingsExambroTable', 'default', 'App', 1757809987, 1),
	(4, '2025-06-19-223119', 'App\\Database\\Migrations\\CreateExambroMenuTable', 'default', 'App', 1757809987, 1),
	(5, '2025-06-19-223542', 'App\\Database\\Migrations\\CreateExambroBlockTable', 'default', 'App', 1757809987, 1),
	(6, '2025-06-28-143214', 'App\\Database\\Migrations\\CreatePesertaTable', 'default', 'App', 1757809987, 1),
	(7, '2025-06-28-143320', 'App\\Database\\Migrations\\CreateBankSoalTable', 'default', 'App', 1757809987, 1),
	(8, '2025-06-28-143342', 'App\\Database\\Migrations\\CreateUjianTable', 'default', 'App', 1757809987, 1),
	(9, '2025-06-28-143453', 'App\\Database\\Migrations\\CreateSoalTable', 'default', 'App', 1757809987, 1),
	(10, '2025-06-28-143520', 'App\\Database\\Migrations\\CreateJawabanTable', 'default', 'App', 1757809987, 1),
	(11, '2025-06-28-143630', 'App\\Database\\Migrations\\CreateHasilUjianTable', 'default', 'App', 1757809987, 1),
	(12, '2025-06-29-031819', 'App\\Database\\Migrations\\CreateSoalOpsiTable', 'default', 'App', 1757809987, 1),
	(13, '2025-06-29-135202', 'App\\Database\\Migrations\\CreateTingkatTable', 'default', 'App', 1757809987, 1),
	(14, '2025-06-29-135214', 'App\\Database\\Migrations\\CreateKelasTable', 'default', 'App', 1757809987, 1),
	(15, '2025-06-29-135220', 'App\\Database\\Migrations\\CreateJurusanTable', 'default', 'App', 1757809987, 1),
	(16, '2025-06-29-135233', 'App\\Database\\Migrations\\CreateAgamaTable', 'default', 'App', 1757809987, 1),
	(17, '2025-07-02-121837', 'App\\Database\\Migrations\\CreateTopikSoal', 'default', 'App', 1757809987, 1),
	(18, '2025-07-10-134638', 'App\\Database\\Migrations\\CreateMediaFilesTable', 'default', 'App', 1757809987, 1);

-- Dumping structure for table candy_exam.soal
CREATE TABLE IF NOT EXISTS `soal` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `topik_soal_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bank_soal_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `soal_no` int unsigned DEFAULT NULL,
  `jenis_soal` enum('pg','mpg','jodohkan','benar_salah','esai','isian') COLLATE utf8mb4_general_ci NOT NULL,
  `pertanyaan` longtext COLLATE utf8mb4_general_ci NOT NULL,
  `jawaban` text COLLATE utf8mb4_general_ci,
  `bobot` float NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bank_soal_id` (`bank_soal_id`),
  CONSTRAINT `soal_bank_soal_id_foreign` FOREIGN KEY (`bank_soal_id`) REFERENCES `bank_soal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.soal: ~0 rows (approximately)

-- Dumping structure for table candy_exam.soal_opsi
CREATE TABLE IF NOT EXISTS `soal_opsi` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `soal_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `label` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `teks` longtext COLLATE utf8mb4_general_ci,
  `pasangan` text COLLATE utf8mb4_general_ci,
  `is_true` tinyint(1) NOT NULL DEFAULT '0',
  `bobot` float NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `soal_opsi_soal_id_foreign` (`soal_id`),
  CONSTRAINT `soal_opsi_soal_id_foreign` FOREIGN KEY (`soal_id`) REFERENCES `soal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.soal_opsi: ~0 rows (approximately)

-- Dumping structure for table candy_exam.tingkat
CREATE TABLE IF NOT EXISTS `tingkat` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table candy_exam.tingkat: ~0 rows (approximately)

-- Dumping structure for table candy_exam.topik_soal
CREATE TABLE IF NOT EXISTS `topik_soal` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `bank_soal_id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `nama` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `keterangan` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `topik_soal_bank_soal_id_foreign` (`bank_soal_id`),
  CONSTRAINT `topik_soal_bank_soal_id_foreign` FOREIGN KEY (`bank_soal_id`) REFERENCES `bank_soal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

