-- DentalCare 0.5.9.8.22: canonical visit state and operations reconciliation.
CREATE TABLE IF NOT EXISTS `{{prefix}}visits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_code` VARCHAR(40) NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `primary_invoice_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `series_id` BIGINT UNSIGNED NULL,
  `source_type` ENUM('appointment','walk_in') NOT NULL DEFAULT 'walk_in',
  `state_code` VARCHAR(40) NOT NULL DEFAULT 'scheduled',
  `outcome_code` VARCHAR(40) NULL,
  `current_owner_role` VARCHAR(40) NULL,
  `current_owner_user_id` BIGINT UNSIGNED NULL,
  `state_reason` VARCHAR(500) NULL,
  `integrity_status` ENUM('ok','review') NOT NULL DEFAULT 'ok',
  `started_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `last_transition_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_visit_code` (`visit_code`),
  UNIQUE KEY `uq_visit_appointment` (`appointment_id`),
  UNIQUE KEY `uq_visit_queue` (`queue_entry_id`),
  KEY `idx_visit_patient_state` (`patient_id`,`state_code`,`created_at`),
  KEY `idx_visit_branch_state` (`branch_id`,`state_code`,`updated_at`),
  UNIQUE KEY `uq_visit_encounter` (`encounter_id`),
  KEY `idx_visit_invoice` (`primary_invoice_id`),
  KEY `idx_visit_followup` (`follow_up_id`),
  KEY `idx_visit_series` (`series_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_state_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NOT NULL,
  `previous_state` VARCHAR(40) NULL,
  `new_state` VARCHAR(40) NOT NULL,
  `action_code` VARCHAR(80) NULL,
  `details` VARCHAR(1000) NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_visit_state_history` (`visit_id`,`created_at`),
  KEY `idx_visit_state_action` (`action_code`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_integrity_issues` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `issue_key` CHAR(64) NOT NULL,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `issue_code` VARCHAR(80) NOT NULL,
  `severity` ENUM('warning','urgent','critical') NOT NULL DEFAULT 'warning',
  `responsible_role` VARCHAR(40) NULL,
  `entity_type` VARCHAR(60) NULL,
  `entity_id` BIGINT UNSIGNED NULL,
  `summary` VARCHAR(255) NOT NULL,
  `detail` VARCHAR(1000) NULL,
  `status` ENUM('open','in_review','resolved','ignored') NOT NULL DEFAULT 'open',
  `occurrence_count` INT UNSIGNED NOT NULL DEFAULT 1,
  `detected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_detected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `resolution_action` VARCHAR(80) NULL,
  `resolution_reason` VARCHAR(1000) NULL,
  `old_values_json` JSON NULL,
  `new_values_json` JSON NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_visit_integrity_issue_key` (`issue_key`),
  KEY `idx_visit_integrity_open` (`status`,`severity`,`last_detected_at`),
  KEY `idx_visit_integrity_branch` (`branch_id`,`status`,`severity`),
  KEY `idx_visit_integrity_visit` (`visit_id`,`status`),
  KEY `idx_visit_integrity_role` (`responsible_role`,`status`,`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
