/*!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 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4ad27c4a-ab01-11f0-83a9-69c395a36f43:1-10105';
/*!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 AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;
/*!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 */;
/*!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 AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;
/*!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 */;
/*!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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;
/*!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 */;
/*!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 AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;
/*!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 */;
/*!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 AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!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 */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;