CREATE TABLE IF NOT EXISTS `{{prefix}}chair_session_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `chair_id` BIGINT UNSIGNED NOT NULL,
  `chair_name` VARCHAR(100) NOT NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `inhouse_provider_user_id` BIGINT UNSIGNED NULL,
  `assignment_type` VARCHAR(40) NOT NULL DEFAULT 'session',
  `starts_at` DATETIME NOT NULL,
  `ends_at` DATETIME NOT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'active',
  `reason` VARCHAR(500) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_chair_session_chair_time` (`branch_id`,`chair_id`,`status`,`starts_at`,`ends_at`),
  KEY `idx_chair_session_dentist_time` (`branch_id`,`dentist_user_id`,`status`,`starts_at`,`ends_at`),
  KEY `idx_chair_session_inhouse_time` (`branch_id`,`inhouse_provider_user_id`,`status`,`starts_at`,`ends_at`),
  CONSTRAINT `{{prefix}}fk_chair_session_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chair_session_chair` FOREIGN KEY (`chair_id`) REFERENCES `{{prefix}}clinic_chairs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chair_session_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_session_inhouse` FOREIGN KEY (`inhouse_provider_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_session_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}chair_status_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chair_id` BIGINT UNSIGNED NOT NULL,
  `event_type` VARCHAR(60) NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `provider_user_id` BIGINT UNSIGNED NULL,
  `note` VARCHAR(500) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chair_event_chair` (`chair_id`,`created_at`),
  KEY `idx_chair_event_queue` (`queue_entry_id`),
  KEY `idx_chair_event_type` (`event_type`,`created_at`),
  CONSTRAINT `{{prefix}}fk_chair_event_chair` FOREIGN KEY (`chair_id`) REFERENCES `{{prefix}}clinic_chairs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chair_event_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_event_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_event_provider` FOREIGN KEY (`provider_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_event_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}chair_visit_movements` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `from_chair_id` BIGINT UNSIGNED NULL,
  `from_chair_name` VARCHAR(100) NULL,
  `to_chair_id` BIGINT UNSIGNED NULL,
  `to_chair_name` VARCHAR(100) NULL,
  `reason` VARCHAR(500) NOT NULL,
  `moved_by` BIGINT UNSIGNED NULL,
  `moved_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chair_movement_queue` (`queue_entry_id`,`moved_at`),
  KEY `idx_chair_movement_patient` (`patient_id`,`moved_at`),
  KEY `idx_chair_movement_from` (`from_chair_id`,`moved_at`),
  KEY `idx_chair_movement_to` (`to_chair_id`,`moved_at`),
  CONSTRAINT `{{prefix}}fk_chair_move_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chair_move_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_move_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_move_from` FOREIGN KEY (`from_chair_id`) REFERENCES `{{prefix}}clinic_chairs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_move_to` FOREIGN KEY (`to_chair_id`) REFERENCES `{{prefix}}clinic_chairs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chair_move_user` FOREIGN KEY (`moved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
