-- Baltique OS Core 0.78.23 — Enterprise Inbound Parser Case Binding
CREATE TABLE IF NOT EXISTS enterprise_inbound_binding_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_key VARCHAR(160) NOT NULL,
  channel_key VARCHAR(60) NOT NULL DEFAULT 'any',
  strategy_key VARCHAR(120) NOT NULL,
  confidence_score INT NOT NULL DEFAULT 50,
  auto_bind_allowed TINYINT(1) NOT NULL DEFAULT 0,
  requires_review TINYINT(1) NOT NULL DEFAULT 1,
  source_fields_json JSON NULL,
  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_inbound_policy (policy_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_inbound_case_binding_candidates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inbound_source_table VARCHAR(120) NOT NULL DEFAULT 'communication_hub_messages',
  inbound_source_id BIGINT UNSIGNED NOT NULL,
  message_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  case_spine_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  policy_key VARCHAR(160) NOT NULL,
  channel_key VARCHAR(60) NOT NULL DEFAULT 'unknown',
  candidate_key VARCHAR(190) NOT NULL DEFAULT '',
  candidate_value VARCHAR(255) NULL,
  confidence_score INT NOT NULL DEFAULT 0,
  binding_status VARCHAR(60) NOT NULL DEFAULT 'suggested',
  binding_reason TEXT NULL,
  evidence_json JSON NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_inbound_case_binding_events (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, candidate_id BIGINT UNSIGNED NULL, inbound_source_table VARCHAR(120) NULL, inbound_source_id BIGINT UNSIGNED NULL, event_type VARCHAR(80) NOT NULL, from_status VARCHAR(60) NULL, to_status VARCHAR(60) NULL, details_json JSON NULL, created_by_user_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_inbound_case_binding_runs (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_uuid VARCHAR(80) NOT NULL, run_type VARCHAR(80) NOT NULL DEFAULT 'manual_sync', scanned_messages INT UNSIGNED NOT NULL DEFAULT 0, created_candidates INT UNSIGNED NOT NULL DEFAULT 0, updated_candidates INT UNSIGNED NOT NULL DEFAULT 0, high_confidence_candidates INT UNSIGNED NOT NULL DEFAULT 0, needs_review_candidates 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_inbound_run (run_uuid)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
