/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `appointment_quotas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `appointment_quotas` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `clinic_id` bigint unsigned NOT NULL,
  `date` date NOT NULL,
  `day_of_week` tinyint NOT NULL COMMENT '1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday',
  `time_slot` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quota` int NOT NULL DEFAULT '0',
  `booked` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointment_quotas_clinic_id_date_time_slot_unique` (`clinic_id`,`date`,`time_slot`),
  CONSTRAINT `appointment_quotas_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `audit_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `audit_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `actor_id` bigint unsigned NOT NULL,
  `entity_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_id` bigint unsigned NOT NULL,
  `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_events_entity_type_entity_id_index` (`entity_type`,`entity_id`),
  KEY `audit_events_actor_id_created_at_index` (`actor_id`,`created_at`),
  CONSTRAINT `audit_events_actor_id_foreign` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `babies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `babies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `year_of_birth` int NOT NULL,
  `month_of_birth` int NOT NULL,
  `pregnancy_outcome` enum('full-term','preterm','miscarriage') COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_of_deliver` enum('Normal','C-section','Forcep','Vacum') COLLATE utf8mb4_unicode_ci NOT NULL,
  `deliver_place` enum('Government Hospital','Private Hospital','Clinic','House') COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` enum('Boy','Girl') COLLATE utf8mb4_unicode_ci NOT NULL,
  `weight` decimal(5,2) NOT NULL,
  `breastfeeding` enum('Less than 6 months','more than 6 months','1 year - 2 years') COLLATE utf8mb4_unicode_ci NOT NULL,
  `baby_condition` enum('Healty','Unhealthy') COLLATE utf8mb4_unicode_ci NOT NULL,
  `condition_notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `babies_mother_id_year_of_birth_month_of_birth_index` (`mother_id`,`year_of_birth`,`month_of_birth`),
  CONSTRAINT `babies_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `cache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cache` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `cache_locks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cache_locks` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `clinic_appointments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clinic_appointments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `clinic_id` bigint unsigned NOT NULL,
  `assigned_nurse_id` bigint unsigned DEFAULT NULL,
  `date` date NOT NULL,
  `slot_label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `status` enum('confirmed','cancelled','completed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'confirmed',
  `reminders_sent` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_mother_clinic_slot` (`mother_id`,`clinic_id`,`date`,`slot_label`),
  KEY `clinic_appointments_clinic_id_foreign` (`clinic_id`),
  KEY `clinic_appointments_assigned_nurse_id_foreign` (`assigned_nurse_id`),
  CONSTRAINT `clinic_appointments_assigned_nurse_id_foreign` FOREIGN KEY (`assigned_nurse_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clinic_appointments_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clinic_appointments_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `clinic_change_attachments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clinic_change_attachments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `request_id` bigint unsigned NOT NULL,
  `path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mime_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clinic_change_attachments_request_id_index` (`request_id`),
  CONSTRAINT `clinic_change_attachments_request_id_foreign` FOREIGN KEY (`request_id`) REFERENCES `clinic_change_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `clinic_change_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clinic_change_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `request_id` bigint unsigned NOT NULL,
  `author_id` bigint unsigned NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clinic_change_messages_author_id_foreign` (`author_id`),
  KEY `clinic_change_messages_request_id_created_at_index` (`request_id`,`created_at`),
  CONSTRAINT `clinic_change_messages_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clinic_change_messages_request_id_foreign` FOREIGN KEY (`request_id`) REFERENCES `clinic_change_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `clinic_change_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clinic_change_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `current_clinic_id` bigint unsigned NOT NULL,
  `requested_clinic_id` bigint unsigned NOT NULL,
  `reason` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `supporting_documents` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `preferred_effective_date` date DEFAULT NULL,
  `urgency` enum('NORMAL','URGENT') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NORMAL',
  `urgency_justification` text COLLATE utf8mb4_unicode_ci,
  `status` enum('DRAFT','PENDING','INFO_REQUESTED','APPROVED','REJECTED','WITHDRAWN') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'DRAFT',
  `decision_effective_date` date DEFAULT NULL,
  `decision_note` text COLLATE utf8mb4_unicode_ci,
  `rejection_reason` text COLLATE utf8mb4_unicode_ci,
  `reviewed_by_old` bigint unsigned DEFAULT NULL,
  `reviewed_at_old` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `decision_by` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_pending_request` (`user_id`),
  UNIQUE KEY `clinic_change_requests_uuid_unique` (`uuid`),
  KEY `clinic_change_requests_current_clinic_id_foreign` (`current_clinic_id`),
  KEY `clinic_change_requests_requested_clinic_id_foreign` (`requested_clinic_id`),
  KEY `clinic_change_requests_reviewed_by_foreign` (`reviewed_by_old`),
  KEY `clinic_change_requests_decision_by_foreign` (`decision_by`),
  KEY `idx_user_status` (`user_id`,`status`),
  KEY `idx_user_active_requests` (`user_id`,`status`),
  CONSTRAINT `clinic_change_requests_current_clinic_id_foreign` FOREIGN KEY (`current_clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clinic_change_requests_decision_by_foreign` FOREIGN KEY (`decision_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clinic_change_requests_requested_clinic_id_foreign` FOREIGN KEY (`requested_clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clinic_change_requests_reviewed_by_foreign` FOREIGN KEY (`reviewed_by_old`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clinic_change_requests_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `clinics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clinics` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lat` decimal(10,8) DEFAULT NULL,
  `lng` decimal(11,8) DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `color_code_histories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `color_code_histories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `color_code` enum('red','yellow','green','white') COLLATE utf8mb4_unicode_ci NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pic_id` bigint unsigned NOT NULL,
  `pic_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `changed_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `color_code_histories_mother_id_changed_at_index` (`mother_id`,`changed_at`),
  KEY `color_code_histories_color_code_index` (`color_code`),
  KEY `color_code_histories_pic_id_index` (`pic_id`),
  CONSTRAINT `color_code_histories_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `color_code_histories_pic_id_foreign` FOREIGN KEY (`pic_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `email_notifications`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `email_notifications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `appointment_id` bigint unsigned NOT NULL,
  `type` enum('confirmation','reminder_7d','reminder_1d','cancellation','reschedule') COLLATE utf8mb4_unicode_ci NOT NULL,
  `scheduled_for` timestamp NOT NULL,
  `status` enum('pending','sent','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `email_notifications_appointment_id_type_index` (`appointment_id`,`type`),
  KEY `email_notifications_scheduled_for_status_index` (`scheduled_for`,`status`),
  CONSTRAINT `email_notifications_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `clinic_appointments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `expectant_mothers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `expectant_mothers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `series_number` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `registration_completed` tinyint(1) NOT NULL DEFAULT '0',
  `zone_assignment_pending` tinyint(1) NOT NULL DEFAULT '0',
  `ic_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_full_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ethnicity` enum('Malay','Chinese','Indian','Orang Asli','Kadazan-Dusun','Majau','Murut','Suluk','Iban','Bidayuh','Melanau') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nationality` enum('Malaysian','Foreigner') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nationality_other` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `educational_level` enum('elementary school','secondary school','diploma','bachelor degree','master degree','doctor of philosophy') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occupation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `home_address` text COLLATE utf8mb4_unicode_ci,
  `family_home_address` text COLLATE utf8mb4_unicode_ci,
  `work_address` text COLLATE utf8mb4_unicode_ci,
  `postnatal_address` text COLLATE utf8mb4_unicode_ci,
  `husband_full_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `husband_ic_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `husband_occupation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `husband_work_address` text COLLATE utf8mb4_unicode_ci,
  `husband_phone_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `number_of_fetus` int DEFAULT NULL,
  `number_of_living_children` int DEFAULT NULL,
  `marriage_date` date DEFAULT NULL,
  `menache` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `family_planning_method` tinyint(1) DEFAULT NULL,
  `family_planning_method_specify` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_smoking_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `husband_smoking_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tibi_screening_cough` tinyint(1) DEFAULT NULL,
  `mother_diabetes` tinyint(1) NOT NULL DEFAULT '0',
  `mother_hipertension` tinyint(1) NOT NULL DEFAULT '0',
  `mother_allergy` tinyint(1) NOT NULL DEFAULT '0',
  `mother_psychiatry` tinyint(1) NOT NULL DEFAULT '0',
  `mother_asthma` tinyint(1) NOT NULL DEFAULT '0',
  `mother_heart_disease` tinyint(1) NOT NULL DEFAULT '0',
  `mother_tibi` tinyint(1) NOT NULL DEFAULT '0',
  `mother_anemia` tinyint(1) NOT NULL DEFAULT '0',
  `mother_thalasemia` tinyint(1) NOT NULL DEFAULT '0',
  `mother_tiroid` tinyint(1) NOT NULL DEFAULT '0',
  `mother_cancer` tinyint(1) NOT NULL DEFAULT '0',
  `mother_other` tinyint(1) NOT NULL DEFAULT '0',
  `mother_other_specify` text COLLATE utf8mb4_unicode_ci,
  `father_diabetes` tinyint(1) NOT NULL DEFAULT '0',
  `father_hipertension` tinyint(1) NOT NULL DEFAULT '0',
  `father_allergy` tinyint(1) NOT NULL DEFAULT '0',
  `father_psychiatry` tinyint(1) NOT NULL DEFAULT '0',
  `father_asthma` tinyint(1) NOT NULL DEFAULT '0',
  `father_heart_disease` tinyint(1) NOT NULL DEFAULT '0',
  `father_tibi` tinyint(1) NOT NULL DEFAULT '0',
  `father_anemia` tinyint(1) NOT NULL DEFAULT '0',
  `father_thalasemia` tinyint(1) NOT NULL DEFAULT '0',
  `father_tiroid` tinyint(1) NOT NULL DEFAULT '0',
  `father_cancer` tinyint(1) NOT NULL DEFAULT '0',
  `father_other` tinyint(1) NOT NULL DEFAULT '0',
  `father_other_specify` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `avatar_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_color_code` enum('red','yellow','green','white') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'white',
  `clinic_id` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expectant_mothers_series_number_unique` (`series_number`),
  KEY `expectant_mothers_user_id_foreign` (`user_id`),
  KEY `expectant_mothers_current_color_code_index` (`current_color_code`),
  KEY `expectant_mothers_clinic_id_foreign` (`clinic_id`),
  CONSTRAINT `expectant_mothers_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE SET NULL,
  CONSTRAINT `expectant_mothers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `failed_jobs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `job_batches`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `jobs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `kick_hiccup_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `kick_hiccup_entries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `type` enum('kick','hiccup') COLLATE utf8mb4_unicode_ci NOT NULL,
  `occurred_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kick_hiccup_entries_mother_id_occurred_at_index` (`mother_id`,`occurred_at`),
  KEY `kick_hiccup_entries_mother_id_type_occurred_at_index` (`mother_id`,`type`,`occurred_at`),
  CONSTRAINT `kick_hiccup_entries_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `maternal_health_records`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `maternal_health_records` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `expectant_mother_id` bigint unsigned NOT NULL,
  `nurse_id` bigint unsigned DEFAULT NULL,
  `record_date` date NOT NULL,
  `gestational_week` int DEFAULT NULL,
  `weight` decimal(5,2) DEFAULT NULL,
  `blood_pressure` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fundal_height` decimal(4,1) DEFAULT NULL,
  `fetal_heart_rate` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fetal_position` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `symptoms` text COLLATE utf8mb4_unicode_ci,
  `medications` text COLLATE utf8mb4_unicode_ci,
  `recommendations` text COLLATE utf8mb4_unicode_ci,
  `next_appointment_notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `clinic_appointment_id` bigint unsigned DEFAULT NULL,
  `status` enum('draft','completed','reviewed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `visit_number` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_clinic_appointment_health_record` (`clinic_appointment_id`),
  KEY `maternal_health_records_nurse_id_foreign` (`nurse_id`),
  KEY `maternal_health_records_expectant_mother_id_record_date_index` (`expectant_mother_id`,`record_date`),
  CONSTRAINT `maternal_health_records_clinic_appointment_id_foreign` FOREIGN KEY (`clinic_appointment_id`) REFERENCES `clinic_appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `maternal_health_records_expectant_mother_id_foreign` FOREIGN KEY (`expectant_mother_id`) REFERENCES `expectant_mothers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `maternal_health_records_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `maternal_test_results`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `maternal_test_results` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `health_record_id` bigint unsigned NOT NULL,
  `test_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'antenatal_blood, immunization, medical_checkup, etc.',
  `test_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Display name of the test',
  `test_data` json NOT NULL COMMENT 'Flexible JSON structure for each test type',
  `performed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `performed_by_nurse_id` bigint unsigned NOT NULL,
  `signature_data` text COLLATE utf8mb4_unicode_ci COMMENT 'Base64 encoded signature image',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `maternal_test_results_health_record_id_index` (`health_record_id`),
  KEY `maternal_test_results_test_type_index` (`test_type`),
  KEY `maternal_test_results_performed_at_index` (`performed_at`),
  KEY `maternal_test_results_performed_by_nurse_id_index` (`performed_by_nurse_id`),
  CONSTRAINT `maternal_test_results_health_record_id_foreign` FOREIGN KEY (`health_record_id`) REFERENCES `maternal_health_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `maternal_test_results_performed_by_nurse_id_foreign` FOREIGN KEY (`performed_by_nurse_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `mother_assignment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `mother_assignment` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `zone_id` bigint unsigned NOT NULL,
  `nurse_id` bigint unsigned DEFAULT NULL,
  `assigned_at` timestamp NOT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `assignment_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'auto',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mother_assignment_mother_id_ended_at_index` (`mother_id`,`ended_at`),
  KEY `mother_assignment_zone_id_ended_at_index` (`zone_id`,`ended_at`),
  KEY `mother_assignment_nurse_id_ended_at_index` (`nurse_id`,`ended_at`),
  CONSTRAINT `mother_assignment_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `expectant_mothers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `mother_assignment_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `mother_assignment_zone_id_foreign` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `nurse_duties`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `nurse_duties` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nurse_id` bigint unsigned NOT NULL,
  `zone_id` bigint unsigned NOT NULL,
  `clinic_id` bigint unsigned NOT NULL,
  `duty_date` date NOT NULL,
  `day_of_week` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nurse_duties_nurse_id_zone_id_duty_date_unique` (`nurse_id`,`zone_id`,`duty_date`),
  KEY `nurse_duties_zone_id_foreign` (`zone_id`),
  KEY `nurse_duties_created_by_foreign` (`created_by`),
  KEY `nurse_duties_clinic_id_duty_date_index` (`clinic_id`,`duty_date`),
  KEY `nurse_duties_nurse_id_duty_date_index` (`nurse_id`,`duty_date`),
  CONSTRAINT `nurse_duties_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duties_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duties_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duties_zone_id_foreign` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `nurse_duty_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `nurse_duty_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nurse_id` bigint unsigned NOT NULL,
  `zone_id` bigint unsigned NOT NULL,
  `clinic_id` bigint unsigned NOT NULL,
  `duty_date` date NOT NULL,
  `day_of_week` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` enum('created','updated','deleted') COLLATE utf8mb4_unicode_ci NOT NULL,
  `performed_by` bigint unsigned NOT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `performed_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nurse_duty_history_performed_by_foreign` (`performed_by`),
  KEY `nurse_duty_history_clinic_id_performed_at_index` (`clinic_id`,`performed_at`),
  KEY `nurse_duty_history_nurse_id_performed_at_index` (`nurse_id`,`performed_at`),
  KEY `nurse_duty_history_zone_id_performed_at_index` (`zone_id`,`performed_at`),
  KEY `nurse_duty_history_duty_date_performed_at_index` (`duty_date`,`performed_at`),
  CONSTRAINT `nurse_duty_history_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duty_history_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duty_history_performed_by_foreign` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_duty_history_zone_id_foreign` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `nurse_zone`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `nurse_zone` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nurse_id` bigint unsigned NOT NULL,
  `zone_id` bigint unsigned NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `coverage_areas` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nurse_zone_zone_id_foreign` (`zone_id`),
  KEY `nurse_zone_nurse_id_zone_id_end_date_index` (`nurse_id`,`zone_id`,`end_date`),
  CONSTRAINT `nurse_zone_nurse_id_foreign` FOREIGN KEY (`nurse_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `nurse_zone_zone_id_foreign` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `password_reset_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` enum('expectant_mother','nurse','head_nurse','clinic_admin') COLLATE utf8mb4_unicode_ci DEFAULT 'expectant_mother',
  `status` enum('active','inactive','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `must_change_password` tinyint(1) NOT NULL DEFAULT '0',
  `password_changed_at` timestamp NULL DEFAULT NULL,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `clinic_id` bigint unsigned DEFAULT NULL,
  `staff_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `profile_picture` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `language` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_staff_id_unique` (`staff_id`),
  KEY `users_clinic_id_foreign` (`clinic_id`),
  CONSTRAINT `users_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `zone_change_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `zone_change_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mother_id` bigint unsigned NOT NULL,
  `old_zone_id` bigint unsigned DEFAULT NULL,
  `new_zone_id` bigint unsigned NOT NULL,
  `old_nurse_id` bigint unsigned DEFAULT NULL,
  `new_nurse_id` bigint unsigned DEFAULT NULL,
  `change_reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `changed_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zone_change_logs_mother_id_foreign` (`mother_id`),
  KEY `zone_change_logs_old_zone_id_foreign` (`old_zone_id`),
  KEY `zone_change_logs_new_zone_id_foreign` (`new_zone_id`),
  KEY `zone_change_logs_old_nurse_id_foreign` (`old_nurse_id`),
  KEY `zone_change_logs_new_nurse_id_foreign` (`new_nurse_id`),
  KEY `zone_change_logs_changed_by_foreign` (`changed_by`),
  CONSTRAINT `zone_change_logs_changed_by_foreign` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `zone_change_logs_mother_id_foreign` FOREIGN KEY (`mother_id`) REFERENCES `expectant_mothers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `zone_change_logs_new_nurse_id_foreign` FOREIGN KEY (`new_nurse_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `zone_change_logs_new_zone_id_foreign` FOREIGN KEY (`new_zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `zone_change_logs_old_nurse_id_foreign` FOREIGN KEY (`old_nurse_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `zone_change_logs_old_zone_id_foreign` FOREIGN KEY (`old_zone_id`) REFERENCES `zones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `zones`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `zones` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `clinic_id` bigint unsigned NOT NULL,
  `zone_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zone_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `boundary_notes` text COLLATE utf8mb4_unicode_ci,
  `coverage_areas` json DEFAULT NULL,
  `postcodes` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `zones_clinic_id_zone_code_unique` (`clinic_id`,`zone_code`),
  CONSTRAINT `zones_clinic_id_foreign` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (1,'0001_01_01_000000_create_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (2,'0001_01_01_000001_create_cache_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (3,'0001_01_01_000002_create_jobs_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (4,'2025_09_08_043220_create_expectant_mothers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (5,'2025_09_08_043302_create_color_codes_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (6,'2025_09_08_043325_create_appointments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (7,'2025_09_08_043352_create_maternal_health_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (8,'2025_09_08_043413_create_kick_hiccup_trackers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (9,'2025_09_08_135856_create_clinics_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (10,'2025_09_08_140048_create_clinic_appointments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (11,'2025_09_08_140059_create_whats_app_notifications_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (12,'2025_09_09_030023_create_kick_hiccup_entries_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (13,'2025_09_09_034756_create_babies_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (14,'2025_09_09_034855_add_avatar_url_to_expectant_mothers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (15,'2025_09_09_081256_add_current_color_code_to_expectant_mothers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (16,'2025_09_09_081315_create_color_code_histories_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (17,'2025_09_12_030425_create_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (18,'2025_09_12_030435_create_maternal_record_tests_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (19,'2025_09_12_070812_add_immunization_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (20,'2025_09_13_025928_add_medical_checkup_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (21,'2025_09_13_033957_add_ogtt_screening_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (22,'2025_09_13_034900_add_blood_sugar_monitoring_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (23,'2025_09_13_040059_add_blood_pressure_monitoring_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (24,'2025_09_13_045120_add_weight_monitoring_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (25,'2025_09_18_094356_add_hemoglobin_monitoring_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (26,'2025_09_18_100923_add_ultrasound_examination_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (27,'2025_09_18_110950_add_antenatal_color_code_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (28,'2025_09_18_132311_add_vte_risk_assessment_to_maternal_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (29,'2025_09_18_134610_create_antenatal_visits_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (30,'2025_09_22_132412_add_clinic_assignment_to_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (31,'2025_09_22_132501_create_appointment_quotas_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (32,'2025_09_22_132535_create_nurse_shifts_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (33,'2025_09_22_132614_modify_appointments_table_for_workflow',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (34,'2025_09_22_132652_add_clinic_id_to_expectant_mothers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (35,'2025_09_22_142056_add_status_and_password_fields_to_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (36,'2025_09_24_023254_create_clinic_change_requests_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (37,'2025_09_24_040208_enhance_clinic_change_requests_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (38,'2025_09_24_040254_create_clinic_change_attachments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (39,'2025_09_24_040403_create_clinic_change_messages_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (40,'2025_09_24_040434_create_audit_events_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (41,'2025_09_24_155042_add_unique_active_clinic_change_request_constraint',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (42,'2025_10_04_031541_add_phone_to_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (43,'2025_10_05_150000_create_zones_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (44,'2025_10_05_150001_create_nurse_zone_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (45,'2025_10_05_150002_create_mother_assignment_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (46,'2025_10_05_150003_create_zone_change_logs_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (47,'2025_10_05_160000_add_clinic_admin_role',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (48,'2025_10_06_000001_update_appointment_quotas_for_time_slots',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (49,'2025_10_08_004231_add_time_slot_to_appointments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (50,'2025_10_08_182616_add_coverage_areas_to_zones_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (51,'2025_10_08_220000_add_coverage_areas_to_nurse_zone_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (52,'2025_10_17_161856_add_zone_assignment_pending_to_expectant_mothers_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (53,'2025_10_17_180513_remove_nurse_id_from_appointment_quotas_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (54,'2025_10_17_231144_add_assigned_nurse_id_to_clinic_appointments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (55,'2025_10_18_065040_create_nurse_duties_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (56,'2025_10_24_120000_create_nurse_duty_history_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (57,'2025_10_31_000001_add_language_preference_to_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (58,'2025_11_06_111116_add_clinic_appointment_id_to_maternal_health_records_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (59,'2025_11_06_113427_add_unique_constraint_to_clinic_appointment_id_in_maternal_health_records',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (60,'2025_11_07_000002_create_maternal_test_results_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (61,'2025_12_01_164125_add_last_login_at_to_users_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (62,'2025_12_04_154024_create_email_notifications_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (63,'2025_12_04_155549_drop_whats_app_notifications_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (64,'2026_01_04_132802_add_reminders_sent_to_clinic_appointments_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (65,'2026_01_04_211530_create_daily_health_checkins_table',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (66,'2026_01_05_000000_migrate_maternal_records_to_new_system',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (67,'2026_01_05_232011_drop_old_unused_tables',1);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (68,'2026_01_13_133732_add_status_and_visit_number_to_maternal_health_records',2);
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES (69,'2026_01_18_142427_add_supporting_documents_to_clinic_change_requests_table',3);
