-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `klungklung`
--

-- --------------------------------------------------------

--
-- Table structure for table `color_tests`
--

CREATE TABLE `color_tests` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `ishihara_answers` json DEFAULT NULL,
  `d15_order` json DEFAULT NULL,
  `axis` varchar(20) DEFAULT NULL,
  `angle` decimal(6,2) DEFAULT NULL,
  `total_error` int DEFAULT NULL,
  `classification` varchar(40) DEFAULT NULL,
  `severity` varchar(20) DEFAULT NULL,
  `confidence` decimal(5,4) DEFAULT NULL,
  `ishihara_summary` text,
  `interpretation_text` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `color_tests`
--

INSERT INTO `color_tests` (`id`, `user_id`, `ishihara_answers`, `d15_order`, `axis`, `angle`, `total_error`, `classification`, `severity`, `confidence`, `ishihara_summary`, `interpretation_text`, `created_at`, `updated_at`) VALUES
(1, 4, '[{\"value\": \"1\", \"plateId\": 1}, {\"value\": \"1\", \"plateId\": 2}, {\"value\": \"0\", \"plateId\": 3}, {\"value\": \"3\", \"plateId\": 4}, {\"value\": \"3\", \"plateId\": 5}, {\"value\": \"5\", \"plateId\": 6}, {\"value\": \"7\", \"plateId\": 7}, {\"value\": \"4\", \"plateId\": 8}, {\"value\": \"6\", \"plateId\": 9}, {\"value\": \"0\", \"plateId\": 10}, {\"value\": \"3\", \"plateId\": 11}, {\"value\": \"5\", \"plateId\": 12}, {\"value\": \"8\", \"plateId\": 13}, {\"value\": \"5\", \"plateId\": 14}, {\"value\": \"6\", \"plateId\": 15}, {\"value\": \"2\", \"plateId\": 16}, {\"value\": \"4\", \"plateId\": 17}, {\"value\": \"7\", \"plateId\": 18}, {\"value\": \"3\", \"plateId\": 19}, {\"value\": \"2\", \"plateId\": 20}]', '[]', NULL, NULL, NULL, 'Normal', 'none', 0.5000, NULL, NULL, '2025-12-10 17:58:51', '2025-12-11 18:14:08'),
(2, 7, '[{\"value\": \"0\", \"plateId\": 1}, {\"value\": \"0\", \"plateId\": 2}, {\"value\": \"0\", \"plateId\": 3}, {\"value\": \"0\", \"plateId\": 4}, {\"value\": \"0\", \"plateId\": 5}, {\"value\": \"0\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Normal', 'none', 0.9500, NULL, NULL, '2025-12-10 18:27:55', '2025-12-10 18:27:55'),
(3, 9, '[{\"value\": \"4\", \"plateId\": 1}, {\"value\": \"0\", \"plateId\": 2}, {\"value\": \"7\", \"plateId\": 3}, {\"value\": \"2\", \"plateId\": 4}, {\"value\": \"3\", \"plateId\": 5}, {\"value\": \"7\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Normal', 'none', 0.9500, NULL, NULL, '2025-12-10 18:30:14', '2025-12-10 18:30:14'),
(4, 6, '[{\"value\": \"2\", \"plateId\": 1}, {\"value\": \"9\", \"plateId\": 2}, {\"value\": \"4\", \"plateId\": 3}, {\"value\": \"?\", \"plateId\": 4}, {\"value\": \"3\", \"plateId\": 5}, {\"value\": \"8\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Suspected CVD', 'moderate', 0.5000, NULL, NULL, '2025-12-10 18:30:38', '2025-12-10 18:30:38'),
(5, 10, '[{\"value\": \"3\", \"plateId\": 1}, {\"value\": \"3\", \"plateId\": 2}, {\"value\": \"2\", \"plateId\": 3}, {\"value\": \"4\", \"plateId\": 4}, {\"value\": \"4\", \"plateId\": 5}, {\"value\": \"3\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Tritanopia', 'severe', 0.6667, NULL, NULL, '2025-12-10 18:56:09', '2025-12-10 18:56:09'),
(6, 11, '[{\"value\": \"0\", \"plateId\": 1}, {\"value\": \"0\", \"plateId\": 2}, {\"value\": \"3\", \"plateId\": 3}, {\"value\": \"0\", \"plateId\": 4}, {\"value\": \"2\", \"plateId\": 5}, {\"value\": \"0\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Normal', 'none', 0.5417, NULL, NULL, '2025-12-10 18:56:40', '2025-12-10 18:56:40'),
(7, 12, '[{\"value\": \"?\", \"plateId\": 1}, {\"value\": \"7\", \"plateId\": 2}, {\"value\": \"?\", \"plateId\": 3}, {\"value\": \"21\", \"plateId\": 4}, {\"value\": \"5\", \"plateId\": 5}, {\"value\": \"9\", \"plateId\": 6}]', '[]', NULL, NULL, NULL, 'Borderline', 'mild', 0.6667, NULL, NULL, '2025-12-11 05:01:30', '2025-12-11 05:07:59');

-- --------------------------------------------------------

--
-- Table structure for table `color_test_events`
--

CREATE TABLE `color_test_events` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `event_type` varchar(40) NOT NULL,
  `payload` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `color_test_events`
--

INSERT INTO `color_test_events` (`id`, `user_id`, `event_type`, `payload`, `created_at`) VALUES
(1, 4, 'ishihara_done', '{\"count\": 6}', '2025-12-10 17:58:33'),
(2, 4, 'completed', '{\"severity\": \"none\", \"confidence\": 0.7, \"short_note\": \"Hasil awal menunjukkan tidak ada indikasi kuat buta warna.\", \"classification\": \"Normal\", \"recommendation\": \"Gunakan kontras tinggi pada materi untuk aksesibilitas.\"}', '2025-12-10 17:58:51'),
(3, 4, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:02:47'),
(4, 4, 'completed', '{\"severity\": \"none\", \"confidence\": 0.7, \"short_note\": \"Hasil awal menunjukkan tidak ada indikasi kuat buta warna.\", \"classification\": \"Normal\", \"recommendation\": \"Gunakan kontras tinggi pada materi untuk aksesibilitas.\"}', '2025-12-10 18:04:13'),
(5, 7, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:27:55'),
(6, 7, 'completed', '{\"severity\": \"none\", \"confidence\": 0.95, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-10 18:27:55'),
(7, 9, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:30:14'),
(8, 9, 'completed', '{\"severity\": \"none\", \"confidence\": 0.95, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-10 18:30:14'),
(9, 6, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:30:38'),
(10, 6, 'completed', '{\"severity\": \"moderate\", \"confidence\": 0.5, \"short_note\": \"Pola jawaban menunjukkan kemungkinan gangguan penglihatan warna.\", \"classification\": \"Suspected CVD\", \"recommendation\": \"Kami sarankan materi dengan kontras lebih tinggi dan ikon pendukung.\"}', '2025-12-10 18:30:38'),
(11, 10, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:56:09'),
(12, 10, 'completed', '{\"severity\": \"severe\", \"confidence\": 0.6666666666666666, \"short_note\": \"Pola jawaban menunjukkan kecenderungan Tritanopia.\", \"classification\": \"Tritanopia\", \"recommendation\": \"Kami sarankan materi dengan kontras lebih tinggi dan ikon pendukung.\"}', '2025-12-10 18:56:09'),
(13, 11, 'ishihara_done', '{\"count\": 6}', '2025-12-10 18:56:40'),
(14, 11, 'completed', '{\"severity\": \"none\", \"confidence\": 0.5416666666666667, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-10 18:56:40'),
(15, 12, 'ishihara_done', '{\"count\": 6}', '2025-12-11 05:01:30'),
(16, 12, 'completed', '{\"severity\": \"none\", \"confidence\": 0.5, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-11 05:01:30'),
(17, 12, 'retest_requested', '[]', '2025-12-11 05:02:34'),
(18, 12, 'retest_requested', '[]', '2025-12-11 05:05:08'),
(19, 12, 'ishihara_done', '{\"count\": 6}', '2025-12-11 05:05:39'),
(20, 12, 'completed', '{\"severity\": \"moderate\", \"confidence\": 0.5833333333333333, \"short_note\": \"Pola jawaban menunjukkan kecenderungan Protanopia.\", \"classification\": \"Protanopia\", \"recommendation\": \"Kami sarankan materi dengan kontras lebih tinggi dan ikon pendukung.\"}', '2025-12-11 05:05:39'),
(21, 12, 'retest_requested', '[]', '2025-12-11 05:05:46'),
(22, 12, 'ishihara_done', '{\"count\": 6}', '2025-12-11 05:06:07'),
(23, 12, 'completed', '{\"severity\": \"none\", \"confidence\": 0.5, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-11 05:06:07'),
(24, 12, 'retest_requested', '[]', '2025-12-11 05:06:12'),
(25, 12, 'ishihara_done', '{\"count\": 6}', '2025-12-11 05:07:59'),
(26, 12, 'completed', '{\"severity\": \"mild\", \"confidence\": 0.6666666666666666, \"short_note\": \"Pola jawaban menunjukkan kecenderungan Borderline.\", \"classification\": \"Borderline\", \"recommendation\": \"Kami sarankan materi dengan kontras lebih tinggi dan ikon pendukung.\"}', '2025-12-11 05:07:59'),
(27, 4, 'retest_requested', '[]', '2025-12-11 18:13:51'),
(28, 4, 'ishihara_done', '{\"count\": 20}', '2025-12-11 18:14:08'),
(29, 4, 'completed', '{\"severity\": \"none\", \"confidence\": 0.5, \"short_note\": \"Jawaban konsisten dengan persepsi warna normal pada screening.\", \"classification\": \"Normal\", \"recommendation\": \"Lanjutkan belajar seperti biasa. Kami tetap menjaga kontras tinggi untuk aksesibilitas.\"}', '2025-12-11 18:14:08');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `color_tests`
--
ALTER TABLE `color_tests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_color_tests_user` (`user_id`);

--
-- Indexes for table `color_test_events`
--
ALTER TABLE `color_test_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cte_user` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `color_tests`
--
ALTER TABLE `color_tests`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `color_test_events`
--
ALTER TABLE `color_test_events`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `color_tests`
--
ALTER TABLE `color_tests`
  ADD CONSTRAINT `fk_color_tests_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `color_test_events`
--
ALTER TABLE `color_test_events`
  ADD CONSTRAINT `fk_cte_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

