-- DentalCare v0.9.0.0
-- Hardening Foundation
--
-- Runtime application uses idempotent setup_apply_v0900() and records this
-- file's SHA-256 in system_migration_ledger. The SQL is retained for fresh
-- installer schema review and deterministic release auditing.

ALTER TABLE `{{prefix}}system_migration_ledger`
  ADD COLUMN IF NOT EXISTS `package_version` VARCHAR(40) NULL AFTER `version`,
  ADD COLUMN IF NOT EXISTS `attempt_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `status`,
  ADD COLUMN IF NOT EXISTS `started_at` DATETIME NULL AFTER `applied_by`,
  ADD COLUMN IF NOT EXISTS `completed_at` DATETIME NULL AFTER `applied_at`,
  ADD COLUMN IF NOT EXISTS `updated_at` DATETIME NULL AFTER `completed_at`,
  ADD COLUMN IF NOT EXISTS `execution_ms` INT UNSIGNED NULL AFTER `updated_at`;

CREATE TABLE IF NOT EXISTS `{{prefix}}system_hardening_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `release_version` VARCHAR(40) NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `blocked_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `review_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `fingerprint_sha256` CHAR(64) NOT NULL,
  `result_json` LONGTEXT NOT NULL,
  `run_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_hardening_release_status` (`release_version`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
