-- Baltique OS Core 0.78.9 — Enterprise GO/HOLD Command Gate
-- Reference migration. Prefer /upgrade_0_78_9.php because it conditionally repairs partially-created schemas.

CREATE TABLE IF NOT EXISTS enterprise_go_live_gate_domains (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gate_key VARCHAR(160) NOT NULL DEFAULT 'operative_enterprise_go_live',
  domain_key VARCHAR(120) NOT NULL,
  domain_label VARCHAR(255) NOT NULL,
  source_strategy VARCHAR(120) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'blocker',
  responsible_role VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  hard_block_go TINYINT(1) NOT NULL DEFAULT 1,
  action_key VARCHAR(160) NOT NULL DEFAULT 'resolve_go_live_gate',
  sort_order INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_go_live_domain (gate_key, domain_key),
  KEY idx_enterprise_go_live_domain_active (active, sort_order, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_case_go_live_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_spine_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  master_status VARCHAR(40) NOT NULL DEFAULT 'HOLD',
  clinical_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  document_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  financial_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  hospital_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  or_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  score INT UNSIGNED NOT NULL DEFAULT 0,
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  no_go_count INT UNSIGNED NOT NULL DEFAULT 0,
  passed_count INT UNSIGNED NOT NULL DEFAULT 0,
  source_legacy_result_id BIGINT UNSIGNED NULL,
  source_or_readiness_id BIGINT UNSIGNED NULL,
  summary_json JSON NULL,
  checked_by_user_id BIGINT UNSIGNED NULL,
  checked_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_enterprise_case_go_live_case (case_spine_id),
  KEY idx_enterprise_case_go_live_status (master_status, blocker_count, checked_at),
  KEY idx_enterprise_case_go_live_operative (operative_case_id),
  KEY idx_enterprise_case_go_live_patient (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_case_go_live_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_spine_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  domain_key VARCHAR(120) NOT NULL,
  item_key VARCHAR(160) NOT NULL,
  item_label VARCHAR(255) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'blocker',
  status VARCHAR(40) NOT NULL DEFAULT 'HOLD',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  hard_block_go TINYINT(1) NOT NULL DEFAULT 1,
  message TEXT NULL,
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  responsible_role VARCHAR(80) NULL,
  action_key VARCHAR(160) NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_case_go_live_item (case_spine_id, item_key),
  KEY idx_enterprise_case_go_live_item_case (case_spine_id, status, severity),
  KEY idx_enterprise_case_go_live_item_domain (domain_key, status),
  KEY idx_enterprise_case_go_live_item_source (source_table, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_go_live_gate_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(80) NOT NULL DEFAULT 'manual_sync',
  scanned_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,
  created_actions INT UNSIGNED NOT NULL DEFAULT 0,
  closed_actions INT UNSIGNED NOT NULL DEFAULT 0,
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_go_live_run (run_uuid),
  KEY idx_enterprise_go_live_run_type (run_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Case Spine extensions are applied conditionally by /upgrade_0_78_9.php:
-- go_live_gate_status, go_live_gate_score, go_live_gate_blocker_count,
-- go_live_gate_warning_count, go_live_gate_checked_at,
-- go_live_gate_summary, go_live_gate_result_id.
