-- Baltique OS Core 0.78.32 — Security / Audit / Privacy
-- Reference migration. Runtime PHP upgrade performs idempotent schema checks.

CREATE TABLE IF NOT EXISTS enterprise_security_controls (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  control_key VARCHAR(160) NOT NULL,
  control_label VARCHAR(255) NOT NULL,
  domain_key VARCHAR(80) NOT NULL DEFAULT 'security',
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  status VARCHAR(40) NOT NULL DEFAULT 'observed',
  owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
  evidence_json JSON NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_security_controls_first (control_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_audit_event_index (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_hash CHAR(64) NOT NULL,
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(120) NOT NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  case_spine_id BIGINT UNSIGNED NULL,
  event_at DATETIME NULL,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_audit_event_index_first (event_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_privacy_retention_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_key VARCHAR(160) NOT NULL,
  data_domain VARCHAR(120) NOT NULL,
  retention_mode VARCHAR(60) NOT NULL DEFAULT 'manual_review',
  owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
  auto_delete_enabled TINYINT(1) NOT NULL DEFAULT 0,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_privacy_retention_rules_first (rule_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_security_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(60) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  controls_scanned INT NOT NULL DEFAULT 0,
  findings_open INT NOT NULL DEFAULT 0,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_security_runs_first (run_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
