-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Dec 11, 2025 at 06:38 PM
-- Server version: 8.0.30
-- PHP Version: 8.1.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 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');

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

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

CREATE TABLE `users` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `role` enum('student','admin') NOT NULL DEFAULT 'student',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `users` (`id`, `name`, `email`, `password_hash`, `role`, `created_at`) VALUES
(1, 'tes', 'tes@gmail.com', '$2y$10$x.EIx9yzR8GZcF9.ByOby.8/Ni5C2w3ZshEnATT9NH0BMogF0.ENK', 'student', '2025-12-10 09:42:34'),
(2, 'Siswa (Mock)', 'student@example.com', '$2y$10$htJhUtUfIcyN963nuvKjiu36pD6DjOeSHsMEAI2e0UwJXEDGHUKU.', 'student', '2025-12-10 14:37:12'),
(3, 'Antonius Henry', 'antonishenry@gmail.com', '$2y$10$cHbdelsto.rF/F00YLpj3u/4S8PZuXX6gk.XCmvyCnMjO4cIbUQbS', 'admin', '2025-12-10 15:03:05'),
(4, 'gurusd', 'gurusd@gmail.com', '$2y$10$SFJ7SKwCt2dzcGkJSQLir.2yimqKUg.dirNJgK7L4DgR7V8gHb9D.', 'admin', '2025-12-10 15:30:47'),
(5, 'michy', 'michy@gmail.com', '$2y$10$D4GB9FlPY12MxNJa27JIleYYR13Gj.V3WFLA9j49TZ83t.TCRC0Le', 'student', '2025-12-10 18:00:18'),
(6, 'kocak', 'kocak@gmail.com', '$2y$10$TV0cpxZ3inS5E7hb/yXX6u6AdrrDYYFPj/PpELCgnIexkwSANORj.', 'student', '2025-12-10 18:01:36'),
(7, 'james', 'james@gmail.com', '$2y$10$.vBVJChrxtKnBIT3AtQOE..t7mHsWXNauS0ogMvLzQXsJoi7M9gpi', 'student', '2025-12-10 18:27:45'),
(8, 'kronis', 'kronis@gmail.com', '$2y$10$jbiMNDDDgoc7qAjDMUG7gu2/aU1VZb.xQoE4/tX4TdTxGAwnd7eUS', 'student', '2025-12-10 18:28:26'),
(9, 'lalala', 'lalala@gmail.com', '$2y$10$jck4LdlQCxjR3cvw5FG6k.pYRpcg4bwPTjJYqnLZ5HgEyqc/D60wa', 'student', '2025-12-10 18:30:06'),
(10, 'rere', 'rere@gmail.com', '$2y$10$Wgtx0vhu22.2CWGtxiQoFuWck.50jJkPfeyKGUlCWw8P9EQUb8dzy', 'student', '2025-12-10 18:55:59'),
(11, 'awwaw', 'wwawa@gmail.com', '$2y$10$Bpoz3mK419dc7.f8/Fr3venQ3f1EcybSllJ3bElDV5Eiz2bBeLdRG', 'student', '2025-12-10 18:56:27'),
(12, 'ravy', 'ravy@gmail.com', '$2y$10$/YSQBawQr/db3gxKIsV.kexlf121XZCmprFoAxbSIEWxYBKtzeSpi', 'student', '2025-12-11 05:01:00');

--
-- 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`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_email` (`email`);

--
-- 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;

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

--
-- 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;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
