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

START TRANSACTION;



--
-- Database: `klinik_gigi`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_nomor_antrian` (IN `p_date` DATE)  BEGIN
    SELECT MAX(id) AS nomor_terakhir
    FROM queue
    WHERE DATE(created_at) = p_date;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_notifikasi` (IN `p_patient_id` INT)  BEGIN
    SELECT message, created_at
    FROM notifications
    WHERE user_id = p_patient_id
    ORDER BY created_at DESC;
END$$
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_antrian` (IN `p_patient_id` INT, IN `p_doctor_id` INT)  BEGIN
    INSERT INTO queue(patient_id, doctor_id, status, created_at) 
    VALUES (p_patient_id, p_doctor_id, 'menunggu', NOW());

    -- kembalikan nomor antrian (id antrian yang baru dimasukkan)
    SELECT LAST_INSERT_ID() AS nomor_antrian;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_diagnosa` (IN `p_patient_id` INT, IN `p_doctor_id` INT, IN `p_diagnosis` TEXT, IN `p_medicine` TEXT)  BEGIN
    INSERT INTO diagnoses(patient_id, doctor_id, diagnosis, medicine, created_at)
    VALUES (p_patient_id, p_doctor_id, p_diagnosis, p_medicine, NOW());
END$$

DELIMITER ;

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

--
-- Table structure for table `antrian`
--

CREATE TABLE `antrian` (
  `id` int(11) NOT NULL,
  `nama_pasien` varchar(100) NOT NULL,
  `nomor_antrian` int(11) NOT NULL,
  `status` enum('menunggu','diruangan','selesai') DEFAULT 'menunggu',
  `waktu_daftar` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `diagnoses`
--

CREATE TABLE `diagnoses` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `diagnosis` text DEFAULT NULL,
  `medicine` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `diagnoses`
--

INSERT INTO `diagnoses` (`id`, `patient_id`, `doctor_id`, `diagnosis`, `medicine`, `created_at`) VALUES
(16, 21, 1, 'Karies gigi', 'dettol', '2025-09-22 17:40:35'),
(17, 15, 1, 'Karies gigi', 'Paracetamol', '2025-09-22 17:41:01'),
(18, 67, 1, 'gigi berlubang', 'Antalgin', '2025-09-22 17:43:04');

--
-- Triggers `diagnoses`
--
DELIMITER $$
CREATE TRIGGER `after_diagnosis_insert` AFTER INSERT ON `diagnoses` FOR EACH ROW BEGIN
    INSERT INTO notifications(user_id, message, created_at)
    VALUES (NEW.patient_id, 'Diagnosa baru dari dokter sudah tersedia.', NOW());
END
$$
DELIMITER ;

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

--
-- Table structure for table `dokter`
--

CREATE TABLE `dokter` (
  `id_dokter` int(11) NOT NULL,
  `nama_dokter` varchar(100) DEFAULT NULL,
  `spesialis` varchar(100) DEFAULT NULL,
  `no_hp` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `dokter`
--

INSERT INTO `dokter` (`id_dokter`, `nama_dokter`, `spesialis`, `no_hp`) VALUES
(1, 'Dr. Sinta', 'Dokter Gigi', '081234567890'),
(2, 'Dr. Teguh', 'Dokter Gigi Spesialis Ortodonti', '082233445566'),
(3, 'Dr. Lina', 'Dokter Gigi Anak', '085677889900');

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

--
-- Table structure for table `janji_temu`
--

CREATE TABLE `janji_temu` (
  `id_janji` int(11) NOT NULL,
  `id_pasien` int(11) DEFAULT NULL,
  `id_dokter` int(11) DEFAULT NULL,
  `tanggal` date DEFAULT NULL,
  `waktu` time DEFAULT NULL,
  `catatan` text DEFAULT NULL,
  `status` enum('Menunggu','Diterima','Ditolak','Selesai') DEFAULT 'Menunggu'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `janji_temu`
--

INSERT INTO `janji_temu` (`id_janji`, `id_pasien`, `id_dokter`, `tanggal`, `waktu`, `catatan`, `status`) VALUES
(5, 1, 1, '2025-10-08', NULL, 'akakakakaka', ''),
(6, 1, 1, '2025-10-03', NULL, 'sakit gigi', ''),
(7, 1, 2, '2025-10-10', NULL, 'aaaaaa', ''),
(8, 1, 1, '2025-10-05', NULL, 'sakit dibagian kepala', ''),
(9, 1, 3, '2025-10-30', NULL, 'aku sakit mata', ''),
(10, 1, 3, '2025-10-31', NULL, 'sakit kepala', ''),
(11, 1, 3, '2025-10-31', NULL, 'sakit kepala', ''),
(12, 1, 2, '2025-10-11', NULL, 'oooooo', '');

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

--
-- Table structure for table `medicines`
--

CREATE TABLE `medicines` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `message`, `created_at`) VALUES
(15, 15, 'SILAHKAN KERUANGAN', '2025-09-15 17:38:58'),
(67, 67, 'Silahkan masuk', '2025-09-15 16:45:07'),
(69, 21, 'Diagnosa baru dari dokter sudah tersedia.', '2025-09-22 17:40:35'),
(71, 67, 'Diagnosa baru dari dokter sudah tersedia.', '2025-09-22 17:43:04');

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

--
-- Table structure for table `pasien`
--

CREATE TABLE `pasien` (
  `id_pasien` int(11) NOT NULL,
  `nama_pasien` varchar(100) DEFAULT NULL,
  `alamat` text DEFAULT NULL,
  `no_hp` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `pasien`
--

INSERT INTO `pasien` (`id_pasien`, `nama_pasien`, `alamat`, `no_hp`) VALUES
(1, 'lery test', 'bojonegoro', '7788');

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

--
-- Table structure for table `prescriptions`
--

CREATE TABLE `prescriptions` (
  `id` int(11) NOT NULL,
  `diagnosis_id` int(11) DEFAULT NULL,
  `medicine_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `queue`
--

CREATE TABLE `queue` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `status` enum('menunggu','dipanggil','selesai') DEFAULT 'menunggu',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `queue`
--

INSERT INTO `queue` (`id`, `patient_id`, `doctor_id`, `status`, `created_at`) VALUES
(1, 2, 1, 'menunggu', '2025-09-15 16:37:55'),
(12, 2, 2, 'selesai', '2025-09-15 16:46:40'),
(13, 2, 1, 'menunggu', '2025-09-22 17:33:39'),
(14, 21, 1, 'menunggu', '2025-09-22 18:10:31'),
(16, 67, 1, 'menunggu', '2025-09-22 18:11:46'),
(17, 21, 1, 'menunggu', '2025-09-22 18:21:09'),
(18, 67, 1, 'menunggu', '2025-09-22 18:22:30');

--
-- Triggers `queue`
--
DELIMITER $$
CREATE TRIGGER `after_queue_update` AFTER UPDATE ON `queue` FOR EACH ROW BEGIN
    IF NEW.status = 'dipanggil' THEN
        INSERT INTO notifications(user_id, message, created_at)
        VALUES (NEW.patient_id, 'Anda dipanggil oleh dokter. Silakan masuk.', NOW());
    END IF;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `before_queue_insert` BEFORE INSERT ON `queue` FOR EACH ROW BEGIN
    IF NEW.status IS NULL THEN
        SET NEW.status = 'menunggu';
    END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `id` int(11) NOT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `day` varchar(20) DEFAULT NULL,
  `time` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `schedules`
--

INSERT INTO `schedules` (`id`, `doctor_id`, `day`, `time`) VALUES
(12, 1, '12-01-2020', '19.00'),
(67, 67, '12-01-2020', '19.00');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `antrian`
--
ALTER TABLE `antrian`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `diagnoses`
--
ALTER TABLE `diagnoses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- Indexes for table `dokter`
--
ALTER TABLE `dokter`
  ADD PRIMARY KEY (`id_dokter`);

--
-- Indexes for table `janji_temu`
--
ALTER TABLE `janji_temu`
  ADD PRIMARY KEY (`id_janji`),
  ADD KEY `id_pasien` (`id_pasien`),
  ADD KEY `id_dokter` (`id_dokter`);

--
-- Indexes for table `medicines`
--
ALTER TABLE `medicines`
  ADD PRIMARY KEY (`id`);

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

--
-- Indexes for table `pasien`
--
ALTER TABLE `pasien`
  ADD PRIMARY KEY (`id_pasien`);

--
-- Indexes for table `prescriptions`
--
ALTER TABLE `prescriptions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `diagnosis_id` (`diagnosis_id`);

--
-- Indexes for table `queue`
--
ALTER TABLE `queue`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- Indexes for table `schedules`
--
ALTER TABLE `schedules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `antrian`
--
ALTER TABLE `antrian`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `diagnoses`
--
ALTER TABLE `diagnoses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `dokter`
--
ALTER TABLE `dokter`
  MODIFY `id_dokter` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `janji_temu`
--
ALTER TABLE `janji_temu`
  MODIFY `id_janji` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `medicines`
--
ALTER TABLE `medicines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=72;

--
-- AUTO_INCREMENT for table `pasien`
--
ALTER TABLE `pasien`
  MODIFY `id_pasien` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `prescriptions`
--
ALTER TABLE `prescriptions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `queue`
--
ALTER TABLE `queue`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `schedules`
--
ALTER TABLE `schedules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `diagnoses`
--
ALTER TABLE `diagnoses`
  ADD CONSTRAINT `diagnoses_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `diagnoses_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `janji_temu`
--
ALTER TABLE `janji_temu`
  ADD CONSTRAINT `janji_temu_ibfk_1` FOREIGN KEY (`id_pasien`) REFERENCES `pasien` (`id_pasien`),
  ADD CONSTRAINT `janji_temu_ibfk_2` FOREIGN KEY (`id_dokter`) REFERENCES `dokter` (`id_dokter`);

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `prescriptions`
--
ALTER TABLE `prescriptions`
  ADD CONSTRAINT `prescriptions_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`);

--
-- Constraints for table `queue`
--
ALTER TABLE `queue`
  ADD CONSTRAINT `queue_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `queue_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `schedules`
--
ALTER TABLE `schedules`
  ADD CONSTRAINT `schedules_ibfk_1` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`);
COMMIT;

