-- Baltique OS Core 0.67.0 — Case GO-Live Gatekeeper

CREATE TABLE IF NOT EXISTS case_go_live_gate_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_key VARCHAR(120) NOT NULL,
  domain VARCHAR(80) NOT NULL,
  label VARCHAR(255) NOT NULL,
  description TEXT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'blocker',
  responsible_role VARCHAR(80) NULL,
  check_method VARCHAR(80) NOT NULL,
  params_json JSON NULL,
  auto_block_go TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_gate_rule_key(rule_key),
  KEY idx_gate_rule_active(active,sort_order,domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS case_go_live_gate_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  gate_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  enforced_go_status VARCHAR(40) NULL,
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  passed_count INT UNSIGNED NOT NULL DEFAULT 0,
  override_count INT UNSIGNED NOT NULL DEFAULT 0,
  risk_score INT UNSIGNED NOT NULL DEFAULT 0,
  result_json JSON NULL,
  scanned_by_user_id BIGINT UNSIGNED NULL,
  scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_gate_result_case(operative_case_id),
  KEY idx_gate_result_status(gate_status, blocker_count, scanned_at),
  KEY idx_gate_result_patient(patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS case_go_live_gate_result_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  result_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  rule_id BIGINT UNSIGNED NULL,
  rule_key VARCHAR(120) NOT NULL,
  domain VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL,
  status VARCHAR(40) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NULL,
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  responsible_role VARCHAR(80) NULL,
  due_at DATETIME NULL,
  override_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_gate_item_case(operative_case_id,status,severity),
  KEY idx_gate_item_result(result_id),
  KEY idx_gate_item_rule(rule_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS case_go_live_gate_overrides (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  rule_key VARCHAR(120) NULL,
  override_scope VARCHAR(40) NOT NULL DEFAULT 'rule',
  reason TEXT NOT NULL,
  approved_by_user_id BIGINT UNSIGNED NULL,
  approved_role VARCHAR(80) NULL,
  signature_data_url MEDIUMTEXT NULL,
  signature_sha256 VARCHAR(128) NULL,
  expires_at DATETIME NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_gate_override_case(operative_case_id,status,expires_at),
  KEY idx_gate_override_rule(rule_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS case_go_live_gate_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  go_cases INT UNSIGNED NOT NULL DEFAULT 0,
  hold_cases INT UNSIGNED NOT NULL DEFAULT 0,
  no_go_cases INT UNSIGNED NOT NULL DEFAULT 0,
  blocker_total INT UNSIGNED NOT NULL DEFAULT 0,
  warning_total INT UNSIGNED NOT NULL DEFAULT 0,
  compliance_score DECIMAL(6,2) NULL,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_gate_report_month(report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS case_go_live_gate_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(80) NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_gate_audit_case(operative_case_id,created_at),
  KEY idx_gate_audit_event(event_type,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
