CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `public_reference` VARCHAR(32) NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_type` ENUM('new','returning') NOT NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `patient_no_entered` VARCHAR(30) NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `middle_name` VARCHAR(100) NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `birth_date` DATE NOT NULL,
  `sex` VARCHAR(20) NULL,
  `mobile` VARCHAR(40) NOT NULL,
  `email` VARCHAR(190) NULL,
  `address` TEXT NULL,
  `emergency_contact` VARCHAR(190) NULL,
  `guardian_name` VARCHAR(190) NULL,
  `guardian_mobile` VARCHAR(40) NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `preferred_dentist` VARCHAR(150) NULL,
  `preferred_date` DATE NOT NULL,
  `preferred_time` TIME NOT NULL,
  `alternate_date` DATE NULL,
  `alternate_time` TIME NULL,
  `urgency` ENUM('routine','soon','urgent') NOT NULL DEFAULT 'routine',
  `reason` TEXT NOT NULL,
  `allergies` TEXT NULL,
  `medications` TEXT NULL,
  `conditions` TEXT NULL,
  `medical_changes` TEXT NULL,
  `status` ENUM('submitted','reviewing','needs_verification','approved','scheduled','declined','cancelled') NOT NULL DEFAULT 'submitted',
  `staff_notes` TEXT NULL,
  `consent_at` DATETIME NOT NULL,
  `submitted_ip` VARCHAR(45) NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_public_reference` (`public_reference`),
  KEY `idx_request_status_created` (`status`,`created_at`),
  KEY `idx_request_patient` (`patient_id`,`created_at`),
  KEY `idx_request_preferred` (`preferred_date`,`preferred_time`),
  KEY `idx_request_identity` (`last_name`,`first_name`,`birth_date`),
  CONSTRAINT `{{prefix}}fk_request_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
