-- Baltique OS Core 0.78.14 — Enterprise Labs & Anesthesia Evidence Bridge
-- Reference migration. Runtime upgrade /upgrade_0_78_14.php performs the same changes idempotently.

CREATE TABLE IF NOT EXISTS enterprise_labs_anesthesia_blueprints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  blueprint_key VARCHAR(160) NOT NULL DEFAULT 'operative_labs_anesthesia_bridge',
  requirement_key VARCHAR(160) NOT NULL,
  requirement_label VARCHAR(255) NOT NULL,
  requirement_group VARCHAR(60) NOT NULL DEFAULT 'labs',
  domain_key VARCHAR(80) NOT NULL DEFAULT 'clinical',
  stage_key VARCHAR(120) NOT NULL DEFAULT 'clinical_gate',
  severity VARCHAR(40) NOT NULL DEFAULT 'blocker',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  hard_block_go TINYINT(1) NOT NULL DEFAULT 1,
  responsible_role VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  action_key VARCHAR(160) NOT NULL DEFAULT 'resolve_enterprise_labs_anesthesia_gate',
  source_tables_json JSON NULL,
  match_tokens_json JSON NULL,
  generic_accept_allowed TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_lab_an_blueprint (blueprint_key, requirement_key),
  KEY idx_enterprise_lab_an_blueprint_active (active, requirement_group, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_case_labs_anesthesia_requirements (
  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,
  requirement_key VARCHAR(160) NOT NULL,
  requirement_label VARCHAR(255) NOT NULL,
  requirement_group VARCHAR(60) NOT NULL DEFAULT 'labs',
  domain_key VARCHAR(80) NOT NULL DEFAULT 'clinical',
  stage_key VARCHAR(120) NOT NULL DEFAULT 'clinical_gate',
  severity VARCHAR(40) NOT NULL DEFAULT 'blocker',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  hard_block_go TINYINT(1) NOT NULL DEFAULT 1,
  status VARCHAR(60) NOT NULL DEFAULT 'missing',
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'HOLD',
  responsible_role VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  action_key VARCHAR(160) NOT NULL DEFAULT 'resolve_enterprise_labs_anesthesia_gate',
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  evidence_label VARCHAR(255) NULL,
  evidence_hash VARCHAR(128) NULL,
  source_received_at DATETIME NULL,
  reviewed_at DATETIME NULL,
  blocker_reason TEXT NULL,
  context_json JSON NULL,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_case_lab_an_req (case_spine_id, requirement_key),
  KEY idx_enterprise_case_lab_an_case (case_spine_id, readiness_status, requirement_group),
  KEY idx_enterprise_case_lab_an_status (status, readiness_status, severity),
  KEY idx_enterprise_case_lab_an_source (source_table, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_labs_anesthesia_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_spine_id BIGINT UNSIGNED NOT NULL,
  requirement_key VARCHAR(160) NULL,
  event_type VARCHAR(80) NOT NULL,
  from_status VARCHAR(60) NULL,
  to_status VARCHAR(60) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_enterprise_lab_an_event_case (case_spine_id, created_at),
  KEY idx_enterprise_lab_an_event_type (event_type, created_at),
  KEY idx_enterprise_lab_an_event_req (requirement_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_labs_anesthesia_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,
  evaluated_requirements 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,
  created_actions INT UNSIGNED NOT NULL DEFAULT 0,
  closed_actions INT UNSIGNED NOT NULL DEFAULT 0,
  work_queue_sync_json JSON NULL,
  go_live_sync_json JSON NULL,
  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_lab_an_run (run_uuid),
  KEY idx_enterprise_lab_an_run_type (run_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
