-- Baltique OS Core 0.78.20 — Enterprise OS Integrity & UX Unification Sweep
-- Idempotent reference migration. Runtime upgrade performs guarded ALTERs in PHP.

CREATE TABLE IF NOT EXISTS enterprise_os_integrity_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uid VARCHAR(64) NOT NULL,
  run_type VARCHAR(40) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  scanned_files INT UNSIGNED NOT NULL DEFAULT 0,
  findings_total INT UNSIGNED NOT NULL DEFAULT 0,
  critical_findings INT UNSIGNED NOT NULL DEFAULT 0,
  warning_findings INT UNSIGNED NOT NULL DEFAULT 0,
  info_findings INT UNSIGNED NOT NULL DEFAULT 0,
  summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_os_integrity_run_uid (run_uid),
  KEY idx_enterprise_os_integrity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_os_integrity_findings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  finding_key VARCHAR(160) NOT NULL,
  domain_key VARCHAR(80) NOT NULL DEFAULT 'core',
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  file_path VARCHAR(255) NULL,
  line_number INT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NULL,
  recommendation TEXT NULL,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_enterprise_os_integrity_run (run_id, severity, status),
  KEY idx_enterprise_os_integrity_file (file_path, line_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Communication Hub compatibility columns are added by guarded PHP upgrade code because
-- MySQL/MariaDB versions differ in ADD COLUMN IF NOT EXISTS support.
-- Required columns: communication_hub_messages.reply_token_hash, reply_url;
-- communication_hub_threads.inbound_unread_count, triage_status.
