-- DentalCare 0.5.9.8.29: workflow exception and reconciliation center.
ALTER TABLE `{{prefix}}visit_integrity_issues`
  MODIFY COLUMN `severity` ENUM('warning','urgent','critical','approval') NOT NULL DEFAULT 'warning';

ALTER TABLE `{{prefix}}visit_integrity_issues`
  ADD COLUMN IF NOT EXISTS `issue_group` VARCHAR(40) NOT NULL DEFAULT 'workflow' AFTER `issue_code`,
  ADD COLUMN IF NOT EXISTS `source_module` VARCHAR(40) NOT NULL DEFAULT 'operations' AFTER `issue_group`,
  ADD COLUMN IF NOT EXISTS `assigned_to_user_id` BIGINT UNSIGNED NULL AFTER `responsible_role`,
  ADD COLUMN IF NOT EXISTS `assigned_by_user_id` BIGINT UNSIGNED NULL AFTER `assigned_to_user_id`,
  ADD COLUMN IF NOT EXISTS `assigned_at` DATETIME NULL AFTER `assigned_by_user_id`,
  ADD COLUMN IF NOT EXISTS `due_at` DATETIME NULL AFTER `assigned_at`,
  ADD COLUMN IF NOT EXISTS `metadata_json` JSON NULL AFTER `detail`;

CREATE INDEX IF NOT EXISTS `idx_visit_integrity_group` ON `{{prefix}}visit_integrity_issues` (`issue_group`,`status`,`severity`,`last_detected_at`);
CREATE INDEX IF NOT EXISTS `idx_visit_integrity_assignment` ON `{{prefix}}visit_integrity_issues` (`assigned_to_user_id`,`status`,`due_at`);
