-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1:3306 -- Generation Time: Apr 11, 2024 at 02:50 AM -- Server version: 8.2.0 -- PHP Version: 8.1.26 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: `hospital_care` -- -- -------------------------------------------------------- -- -- Table structure for table `appointment` -- DROP TABLE IF EXISTS `appointment`; CREATE TABLE IF NOT EXISTS `appointment` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `appointment_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `patient_id` bigint UNSIGNED NOT NULL, `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `appointmentdate` datetime DEFAULT NULL, `location` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `appointment_patient_id_foreign` (`patient_id`) ); -- -- Dumping data for table `appointment` -- INSERT INTO `appointment` (`id`, `created_at`, `updated_at`, `appointment_reason`, `patient_id`, `status`, `appointmentdate`, `location`) VALUES (2, '2024-04-01 22:57:03', '2024-04-01 22:57:03', 'nurse_visit', 1, 'Scheduled', '2024-04-09 01:23:33', 'nairobi'), (3, '2024-04-01 23:00:44', '2024-04-01 23:00:44', 'nurse_visit', 1, 'Scheduled', '2024-04-23 12:23:52', 'mombasa'), (4, '2024-04-01 23:02:17', '2024-04-01 23:02:17', 'labtest', 3, 'Scheduled', '2024-04-22 11:00:03', 'nairobi'), (5, '2024-04-01 23:13:11', '2024-04-01 23:13:11', 'labtest', 1, 'Scheduled', '2024-04-28 14:24:30', 'mombasa'), (6, '2024-04-01 23:13:17', '2024-04-01 23:13:17', 'nurse_visit', 5, 'Scheduled', '2024-04-22 11:24:26', 'nairobi'), (7, '2024-04-01 23:13:23', '2024-04-01 23:13:23', 'labtest', 4, 'Scheduled', '2024-05-22 01:27:49', 'kisumu'), (8, '2024-04-01 23:13:27', '2024-04-01 23:13:27', 'labtest', 3, 'Scheduled', '2024-04-29 11:24:21', 'mombasa'), (9, '2024-04-01 23:13:33', '2024-04-01 23:13:33', 'nurse_visit', 4, 'Scheduled', '2024-05-29 01:24:45', 'nairobi'), (10, '2024-04-01 23:13:37', '2024-04-01 23:13:37', 'nurse_visit', 1, 'Scheduled', '2024-04-19 09:24:16', 'nairobi'), (11, '2024-04-01 23:13:41', '2024-04-01 23:13:41', 'labtest', 1, 'Scheduled', '2024-05-29 10:24:45', 'kisumu'), (12, '2024-04-01 23:13:49', '2024-04-01 23:13:49', 'labtest', 1, 'Scheduled', '2024-05-29 10:24:45', 'eldoret'), (13, '2024-04-08 21:31:42', '2024-04-08 21:31:42', 'labtest', 1, 'Scheduled', '2024-05-15 12:26:55', 'kisumu'), (14, '2024-04-08 21:59:27', '2024-04-08 21:59:27', 'labtest', 7, 'Scheduled', '2024-04-17 15:59:00', 'mombasa'), (15, '2024-04-08 22:00:30', '2024-04-08 22:00:30', 'labtest', 7, 'Scheduled', '2024-04-17 15:59:00', 'mombasa'), (16, '2024-04-08 22:05:58', '2024-04-08 22:05:58', 'nurse_visit', 7, 'Scheduled', '2024-04-30 17:09:00', 'mombasa'), (17, '2024-04-08 22:07:32', '2024-04-08 22:07:32', 'nurse_visit', 7, 'Scheduled', '2024-04-21 16:10:00', 'nairobi'); -- -------------------------------------------------------- -- -- Table structure for table `available` -- DROP TABLE IF EXISTS `available`; CREATE TABLE IF NOT EXISTS `available` ( `status_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `status_name` tinyint(1) NOT NULL, PRIMARY KEY (`status_id`), UNIQUE KEY `status_id` (`status_id`), UNIQUE KEY `status_name` (`status_name`) ); -- -- Dumping data for table `available` -- INSERT INTO `available` (`status_id`, `status_name`) VALUES (1, 1), (2, 0); -- -------------------------------------------------------- -- -- Table structure for table `caregiver` -- DROP TABLE IF EXISTS `caregiver`; CREATE TABLE IF NOT EXISTS `caregiver` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `caregiver_email_unique` (`email`), UNIQUE KEY `caregiver_phone_unique` (`phone`) ); -- -- Dumping data for table `caregiver` -- INSERT INTO `caregiver` (`id`, `name`, `email`, `password`, `location`, `phone`, `created_at`, `updated_at`) VALUES (1, 'John Doe', 'john.doe@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '+254700000001', '2024-03-31 07:28:32', '2024-03-31 07:28:32'), (2, 'Jane Smith', 'jane.smith@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nakuru', '+254700000002', '2024-03-31 07:28:32', '2024-03-31 07:28:32'), (3, 'Emily Johnson', 'emily.johnson@example.com', 'b489ef012183881a15535c64f6b0646a', 'Mombasa', '+254700000003', '2024-03-31 07:28:32', '2024-03-31 07:28:32'), (4, 'Michael Brown', 'michael.brown@example.com', 'b489ef012183881a15535c64f6b0646a', 'Kisumu', '+254700000004', '2024-03-31 07:28:32', '2024-03-31 07:28:32'), (5, 'Jessica Davis', 'jessica.davis@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '+254700000005', '2024-03-31 07:28:32', '2024-03-31 07:28:32'); -- -------------------------------------------------------- -- -- Table structure for table `healthcare_facility` -- DROP TABLE IF EXISTS `healthcare_facility`; CREATE TABLE IF NOT EXISTS `healthcare_facility` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `facilty_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `facility_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `facility_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ); -- -- Dumping data for table `healthcare_facility` -- INSERT INTO `healthcare_facility` (`id`, `facilty_name`, `facility_location`, `phone`, `email`, `password`, `facility_id`, `created_at`, `updated_at`) VALUES (1, 'Nairobi Health Clinic', 'Nairobi', '+254700100200', 'nairobi@healthclinic.com', 'b489ef012183881a15535c64f6b0646a', '101', '2024-03-31 10:32:37', '2024-03-31 10:32:37'), (2, 'Nakuru Medical Center', 'Nakuru', '+254700300400', 'nakuru@medicalcenter.com', 'b489ef012183881a15535c64f6b0646a', '102', '2024-03-31 10:32:37', '2024-03-31 10:32:37'), (3, 'Mombasa Healthcare', 'Mombasa', '+254700500600', 'mombasa@healthcare.com', 'b489ef012183881a15535c64f6b0646a', '103', '2024-03-31 10:32:37', '2024-03-31 10:32:37'), (4, 'Kisumu Doctors Plaza', 'Kisumu', '+254700700800', 'kisumu@doctorsplaza.com', 'b489ef012183881a15535c64f6b0646a', '104', '2024-03-31 10:32:37', '2024-03-31 10:32:37'), (5, 'Coast Provincial Hospital', 'Mombasa', '+254700900000', 'coast@provincialhospital.com', 'b489ef012183881a15535c64f6b0646a', '105', '2024-03-31 10:32:37', '2024-03-31 10:32:37'), (6, 'Niaje mzee', 'mombasa', '254719343421', 'ahin@example.com', 'b489ef012183881a15535c64f6b0646a', '106', NULL, NULL), (7, 'Nairobi National Hopsital', 'nairobi', '2547001231231', 'nairobi@hosi.com', 'b489ef012183881a15535c64f6b0646a', NULL, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `nurse` -- DROP TABLE IF EXISTS `nurse`; CREATE TABLE IF NOT EXISTS `nurse` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `km_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `available_status` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nurse_email_unique` (`email`), UNIQUE KEY `nurse_phone_unique` (`phone`) ); -- -- Dumping data for table `nurse` -- INSERT INTO `nurse` (`id`, `name`, `email`, `password`, `location`, `phone`, `km_num`, `available_status`, `created_at`, `updated_at`) VALUES (1, 'William Onyango', 'nurse1@hospital.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '254700000001', 'KM001', 0, '2024-03-31 10:34:40', '2024-03-31 10:34:40'), (2, 'Bill Njau', 'nurse2@hospital.com', 'b489ef012183881a15535c64f6b0646a', 'Nakuru', '254700000002', 'KM002', 0, '2024-03-31 10:34:40', '2024-03-31 10:34:40'), (3, 'Shem Kigaai', 'nurse3@hospital.com', 'b489ef012183881a15535c64f6b0646a', 'Mombasa', '254700000003', 'KM003', 1, '2024-03-31 10:34:40', '2024-03-31 10:34:40'), (4, 'Ugunja John', 'nurse4@hospital.com', 'b489ef012183881a15535c64f6b0646a', 'Kisumu', '254700000004', 'KM004', 0, '2024-03-31 10:34:40', '2024-03-31 10:34:40'), (5, 'Yoana Msee', 'nurse5@hospital.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '254700000005', 'KM005', 0, '2024-03-31 10:34:40', '2024-03-31 10:34:40'), (6, 'Jane Mwangi', 'jmwangi@nurse.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '254719343423', 'KM006', 1, NULL, NULL), (7, 'Joan Wambui', 'jwambui@gmail.com', 'eb9fe5104a53b3cb2d18be5399633ea9', 'nairobi', '254719343422', NULL, 1, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `patient` -- DROP TABLE IF EXISTS `patient`; CREATE TABLE IF NOT EXISTS `patient` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `caregiver_id` bigint UNSIGNED DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `patient_email_unique` (`email`), UNIQUE KEY `patient_phone_unique` (`phone`), KEY `patient_caregiver_id_foreign` (`caregiver_id`) ); -- -- Dumping data for table `patient` -- INSERT INTO `patient` (`id`, `name`, `email`, `password`, `location`, `phone`, `caregiver_id`, `created_at`, `updated_at`) VALUES (1, 'Eve', 'eve@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '+254700000001', 1, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (2, 'Alice', 'alice@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nakuru', '+254700000002', 2, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (3, 'Bob', 'bob@example.com', 'b489ef012183881a15535c64f6b0646a', 'Mombasa', '+254700000003', 3, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (4, 'Charlie', 'charlie@example.com', 'b489ef012183881a15535c64f6b0646a', 'Kisumu', '+254700000004', 2, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (5, 'David', 'david@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '+254700000005', 3, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (6, 'David', 'daviYd@example.com', 'b489ef012183881a15535c64f6b0646a', 'Nairobi', '+254700040005', 4, '2024-03-30 16:15:42', '2024-03-30 16:15:42'), (7, 'Jamila Ngaira', 'ahin@example.com', 'b489ef012183881a15535c64f6b0646a', 'mombasa', '254719343421', 1, NULL, NULL), (9, 'John Niaje', 'ahina@example.com', '765d292bfc3ef785594b6167fe9811b1', 'mombasa', '254719343425', 5, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `patient_lab_results` -- DROP TABLE IF EXISTS `patient_lab_results`; CREATE TABLE IF NOT EXISTS `patient_lab_results` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `lab_test_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `lab_result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `healthcare_facility_id` bigint UNSIGNED NOT NULL, `patient_id` bigint UNSIGNED NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `patient_lab_results_healthcare_facility_id_foreign` (`healthcare_facility_id`), KEY `patient_lab_results_patient_id_foreign` (`patient_id`) ); -- -- Dumping data for table `patient_lab_results` -- INSERT INTO `patient_lab_results` (`id`, `lab_test_type`, `lab_result`, `healthcare_facility_id`, `patient_id`, `created_at`, `updated_at`) VALUES (6, 'Blood Test', 'Normal', 1, 1, '2024-04-10 05:00:00', '2024-04-10 05:00:00'), (7, 'Blood Test', 'High Cholesterol', 2, 3, '2024-04-10 06:15:00', '2024-04-10 06:15:00'), (8, 'Urine Test', 'Normal', 1, 3, '2024-04-10 07:30:00', '2024-04-10 07:30:00'), (9, 'MRI Scan', 'No abnormalities found', 3, 4, '2024-04-10 08:45:00', '2024-04-10 08:45:00'), (10, 'X-ray', 'Fractured bone detected', 2, 5, '2024-04-10 10:00:00', '2024-04-10 10:00:00'), (11, 'Blood Pressure', '120/80', 1, 1, '2024-04-11 05:00:00', '2024-04-11 05:00:00'), (12, 'Blood Glucose', '100', 2, 2, '2024-04-11 06:00:00', '2024-04-11 06:00:00'), (13, 'Cholesterol', '180', 3, 3, '2024-04-11 07:00:00', '2024-04-11 07:00:00'), (14, 'Blood Pressure', '130/85', 1, 4, '2024-04-11 08:00:00', '2024-04-11 08:00:00'), (15, 'Blood Glucose', '120', 2, 5, '2024-04-11 09:00:00', '2024-04-11 09:00:00'), (16, 'Cholesterol', '200', 3, 6, '2024-04-11 10:00:00', '2024-04-11 10:00:00'), (17, 'Blood Pressure', '140/90', 1, 7, '2024-04-11 11:00:00', '2024-04-11 11:00:00'), (18, 'Blood Pressure', '120/80', 1, 1, '2024-04-11 05:00:00', '2024-04-11 05:00:00'), (19, 'Blood Glucose', '100', 2, 2, '2024-04-11 06:00:00', '2024-04-11 06:00:00'), (20, 'Cholesterol', '180', 3, 3, '2024-04-11 07:00:00', '2024-04-11 07:00:00'), (21, 'Blood Pressure', '130/85', 1, 4, '2024-04-11 08:00:00', '2024-04-11 08:00:00'), (22, 'Blood Glucose', '120', 2, 5, '2024-04-11 09:00:00', '2024-04-11 09:00:00'), (23, 'Cholesterol', '200', 3, 6, '2024-04-11 10:00:00', '2024-04-11 10:00:00'), (24, 'Blood Pressure', '140/90', 1, 7, '2024-04-11 11:00:00', '2024-04-11 11:00:00'), (25, 'Blood Glucose', '110', 2, 1, '2024-04-11 12:00:00', '2024-04-11 12:00:00'), (26, 'Cholesterol', '190', 3, 2, '2024-04-11 13:00:00', '2024-04-11 13:00:00'), (27, 'Blood Pressure', '125/82', 1, 3, '2024-04-11 14:00:00', '2024-04-11 14:00:00'), (28, 'Blood Glucose', '115', 2, 4, '2024-04-11 15:00:00', '2024-04-11 15:00:00'), (29, 'Cholesterol', '210', 3, 5, '2024-04-11 16:00:00', '2024-04-11 16:00:00'), (30, 'Blood Pressure', '135/88', 1, 6, '2024-04-11 17:00:00', '2024-04-11 17:00:00'), (31, 'Blood Glucose', '105', 2, 7, '2024-04-11 18:00:00', '2024-04-11 18:00:00'); -- -------------------------------------------------------- -- -- Table structure for table `patient_visit_details` -- DROP TABLE IF EXISTS `patient_visit_details`; CREATE TABLE IF NOT EXISTS `patient_visit_details` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `visit_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `patient_id` bigint UNSIGNED NOT NULL, `appointment_id` bigint UNSIGNED NOT NULL, `nurse_id` bigint UNSIGNED NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `patient_visit_details_patient_id_foreign` (`patient_id`), KEY `patient_visit_details_appointment_id_foreign` (`appointment_id`), KEY `patient_visit_details_nurse_id_foreign` (`nurse_id`) ); -- -- Constraints for dumped tables -- -- -- Constraints for table `appointment` -- ALTER TABLE `appointment` ADD CONSTRAINT `appointment_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `patient` -- ALTER TABLE `patient` ADD CONSTRAINT `patient_caregiver_id_foreign` FOREIGN KEY (`caregiver_id`) REFERENCES `caregiver` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `patient_lab_results` -- ALTER TABLE `patient_lab_results` ADD CONSTRAINT `patient_lab_results_healthcare_facility_id_foreign` FOREIGN KEY (`healthcare_facility_id`) REFERENCES `healthcare_facility` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `patient_lab_results_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `patient_visit_details` -- ALTER TABLE `patient_visit_details` ADD CONSTRAINT `patient_visit_details_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `patient_visit_details_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `nurse` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `patient_visit_details_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE 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 */;