ALTER TABLE `{{prefix}}clinical_encounters`
  ADD COLUMN IF NOT EXISTS `paused_at` DATETIME NULL AFTER `finalized_at`,
  ADD COLUMN IF NOT EXISTS `pause_reason` VARCHAR(500) NULL AFTER `paused_at`,
  ADD COLUMN IF NOT EXISTS `paused_by` BIGINT UNSIGNED NULL AFTER `pause_reason`,
  ADD COLUMN IF NOT EXISTS `pause_releases_chair` TINYINT(1) NOT NULL DEFAULT 1 AFTER `paused_by`,
  ADD COLUMN IF NOT EXISTS `responsibility_transferred_at` DATETIME NULL AFTER `pause_releases_chair`,
  ADD COLUMN IF NOT EXISTS `responsibility_transfer_id` BIGINT UNSIGNED NULL AFTER `responsibility_transferred_at`;

ALTER TABLE `{{prefix}}inhouse_checkup_results`
  ADD COLUMN IF NOT EXISTS `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured' AFTER `service_name`,
  ADD COLUMN IF NOT EXISTS `approved_fee_snapshot` DECIMAL(12,2) NULL AFTER `fee_status`,
  ADD COLUMN IF NOT EXISTS `no_charge_reason` VARCHAR(500) NULL AFTER `approved_fee_snapshot`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_by` BIGINT UNSIGNED NULL AFTER `no_charge_reason`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_at` DATETIME NULL AFTER `fee_authorized_by`,
  ADD COLUMN IF NOT EXISTS `invoice_id` BIGINT UNSIGNED NULL AFTER `fee_authorized_at`,
  ADD COLUMN IF NOT EXISTS `billing_status` ENUM('pending','ready','invoiced','no_charge','blocked') NOT NULL DEFAULT 'pending' AFTER `invoice_id`;

ALTER TABLE `{{prefix}}invoice_items`
  ADD COLUMN IF NOT EXISTS `inhouse_checkup_result_id` BIGINT UNSIGNED NULL AFTER `clinical_usage_item_id`;

CREATE TABLE IF NOT EXISTS `{{prefix}}billing_correction_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `assigned_dentist_user_id` BIGINT UNSIGNED NULL,
  `reason` VARCHAR(1000) NOT NULL,
  `status` ENUM('open','in_review','corrected','rejected','cancelled') NOT NULL DEFAULT 'open',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `resolution_note` VARCHAR(1000) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_billing_correction_queue` (`queue_entry_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_encounter` (`encounter_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_dentist` (`assigned_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_patient` (`patient_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}financial_correction_addenda` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `correction_type` ENUM('service_mapping','fee_policy','insurance','discount','no_charge','other') NOT NULL DEFAULT 'other',
  `reason` VARCHAR(1000) NOT NULL,
  `old_value_json` JSON NULL,
  `new_value_json` JSON NULL,
  `author_user_id` BIGINT UNSIGNED NOT NULL,
  `author_name_snapshot` VARCHAR(190) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `addendum_hash` CHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_financial_addendum_encounter` (`encounter_id`,`created_at`),
  KEY `idx_financial_addendum_invoice` (`invoice_id`,`created_at`),
  KEY `idx_financial_addendum_patient` (`patient_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_encounter_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `request_type` ENUM('consultation','transfer','emergency_takeover') NOT NULL DEFAULT 'consultation',
  `from_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `to_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `required_privilege_code` VARCHAR(100) NULL,
  `reason` VARCHAR(1000) NOT NULL,
  `status` ENUM('pending','accepted','declined','cancelled','completed') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` BIGINT UNSIGNED NULL,
  `decided_at` DATETIME NULL,
  `decision_note` VARCHAR(1000) NULL,
  `consultation_note` TEXT NULL,
  `consultation_signed_name` VARCHAR(190) NULL,
  `consultation_signed_license` VARCHAR(100) NULL,
  `consultation_signature_path` VARCHAR(500) NULL,
  `consultation_signed_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_encounter_transfer_encounter` (`encounter_id`,`status`,`requested_at`),
  KEY `idx_encounter_transfer_target` (`to_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_encounter_transfer_patient` (`patient_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

