-- DentalCare 0.5.8.2
-- Scalable patient-care lists and receptionist waitlist workflow.

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_waitlist` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `earliest_date` DATE NOT NULL,
  `latest_date` DATE NOT NULL,
  `preferred_time_from` TIME NULL,
  `preferred_time_to` TIME NULL,
  `priority` ENUM('routine','soon','urgent') NOT NULL DEFAULT 'routine',
  `status` ENUM('waiting','offered','booked','declined','cancelled','expired') NOT NULL DEFAULT 'waiting',
  `notes` TEXT NULL,
  `offer_expires_at` DATETIME NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_waitlist_branch_status_dates` (`branch_id`,`status`,`earliest_date`,`latest_date`),
  KEY `idx_waitlist_service_doctor` (`service_id`,`dentist_user_id`,`status`),
  KEY `idx_waitlist_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_waitlist_appointment` (`appointment_id`),
  CONSTRAINT `{{prefix}}fk_waitlist_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_waitlist_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_waitlist_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
