-- Quarantine and scan-state contract for tenant and Province documents.
ALTER TABLE tenant_document_versions
 ADD COLUMN quarantine_key CHAR(64) NULL AFTER storage_key,
 ADD COLUMN scan_state ENUM('pending','clean','infected','error','unavailable') NOT NULL DEFAULT 'unavailable' AFTER sha256,
 ADD COLUMN scanned_at DATETIME NULL AFTER scan_state,
 ADD COLUMN scan_detail VARCHAR(500) NULL AFTER scanned_at,
 ADD KEY idx_tenant_document_scan(scan_state,uploaded_at);
ALTER TABLE province_document_versions
 ADD COLUMN quarantine_key CHAR(64) NULL AFTER storage_key,
 ADD COLUMN scan_state ENUM('pending','clean','infected','error','unavailable') NOT NULL DEFAULT 'unavailable' AFTER sha256,
 ADD COLUMN scanned_at DATETIME NULL AFTER scan_state,
 ADD COLUMN scan_detail VARCHAR(500) NULL AFTER scanned_at,
 ADD KEY idx_province_document_scan(scan_state,uploaded_at);
CREATE TABLE IF NOT EXISTS document_scan_jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 scope_type ENUM('unit','province') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 document_version_id BIGINT UNSIGNED NOT NULL,
 quarantine_key CHAR(64) NOT NULL,
 sensitivity ENUM('normal','sensitive') NOT NULL DEFAULT 'normal',
 scan_state ENUM('pending','clean','infected','error','unavailable') NOT NULL DEFAULT 'pending',
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 scanned_at DATETIME NULL,
 scanner_reference VARCHAR(180) NULL,
 safe_detail VARCHAR(500) NULL,
 UNIQUE KEY uq_document_scan_job(scope_type,document_version_id),
 KEY idx_document_scan_pending(scan_state,requested_at),
 KEY idx_document_scan_scope(scope_type,province_id,unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;