-- DentalCare v0.8.0.5
-- Automated technical production validation, simplified live gates, deep backup verification, and resumable updates.

ALTER TABLE `{{prefix}}backup_logs`
  MODIFY COLUMN `status` VARCHAR(30) NOT NULL DEFAULT 'created',
  ADD COLUMN `verification_evidence_json` LONGTEXT NULL AFTER `verified_at`,
  ADD COLUMN `verification_file_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `verification_evidence_json`,
  ADD COLUMN `verification_format_version` INT UNSIGNED NULL AFTER `verification_file_count`;

CREATE TABLE IF NOT EXISTS `{{prefix}}system_update_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `run_id` CHAR(32) NOT NULL,
  `target_version` VARCHAR(40) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `plan_json` LONGTEXT NOT NULL,
  `completed_json` LONGTEXT NULL,
  `current_version` VARCHAR(40) NULL,
  `current_label` VARCHAR(190) NULL,
  `step_index` INT UNSIGNED NOT NULL DEFAULT 0,
  `step_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_error` TEXT NULL,
  `error_reference` VARCHAR(40) NULL,
  `started_by` BIGINT UNSIGNED NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_system_update_run_id` (`run_id`),
  KEY `idx_system_update_status` (`status`,`target_version`,`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
