-- DentalCare v0.5.9.8.44 Appointment and Schedule Final Guard
-- Idempotent safety tables for provider assignment, chair/provider conflict evidence, and strict appointment status handling.

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_assignment_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `assignment_type` ENUM('clinic_decides','provider_unavailable','chair_conflict','manual_review') NOT NULL DEFAULT 'clinic_decides',
  `priority` ENUM('urgent','normal','low') NOT NULL DEFAULT 'normal',
  `status` ENUM('pending','in_review','resolved','cancelled') NOT NULL DEFAULT 'pending',
  `reason` VARCHAR(500) NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `assigned_dentist_user_id` BIGINT UNSIGNED NULL,
  `assigned_inhouse_provider_user_id` BIGINT UNSIGNED NULL,
  `assigned_chair_id` BIGINT UNSIGNED NULL,
  `resolution_note` VARCHAR(500) NULL,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_assignment_appointment` (`appointment_id`),
  KEY `idx_assignment_branch_status` (`branch_id`,`status`,`priority`,`requested_at`),
  KEY `idx_assignment_request` (`appointment_request_id`,`status`),
  KEY `idx_assignment_patient` (`patient_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_safety_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `event_label` VARCHAR(190) NOT NULL,
  `severity` ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  `message` VARCHAR(1000) NULL,
  `metadata_json` LONGTEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_safety_event_appointment` (`appointment_id`,`created_at`),
  KEY `idx_safety_event_request` (`appointment_request_id`,`created_at`),
  KEY `idx_safety_event_type` (`event_type`,`severity`,`created_at`),
  KEY `idx_safety_event_branch` (`branch_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}settings` (`group_name`,`setting_key`,`setting_value`,`is_secret`,`created_at`) VALUES
('appointments','guard_provider_chair_conflicts','1',0,NOW()),
('appointments','guard_require_assignment_before_checkin','1',0,NOW()),
('appointments','guard_no_show_after_start_only','1',0,NOW()),
('appointments','guard_cancel_active_visit_from_queue','1',0,NOW())
ON DUPLICATE KEY UPDATE `setting_value`=`setting_value`, `updated_at`=NOW();
