-- Baltique OS Core 0.63.0 — Content Block Editor & Rules Matrix

CREATE TABLE IF NOT EXISTS document_content_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_code VARCHAR(120) NOT NULL,
  rule_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  priority INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  include_mode VARCHAR(40) NOT NULL DEFAULT 'include',
  document_family VARCHAR(100) NULL,
  audience VARCHAR(80) NULL,
  template_code VARCHAR(120) NULL,
  block_id BIGINT UNSIGNED NOT NULL,
  block_code VARCHAR(120) NULL,
  block_version VARCHAR(40) NULL,
  procedure_key VARCHAR(160) NULL,
  site_key VARCHAR(160) NULL,
  anesthesia_type VARCHAR(80) NULL,
  implant_mode VARCHAR(40) NOT NULL DEFAULT 'any',
  lab_status VARCHAR(80) NULL,
  risk_flags_json JSON NULL,
  go_status VARCHAR(40) NULL,
  case_phase VARCHAR(80) NULL,
  sort_order INT NOT NULL DEFAULT 100,
  conditions_json JSON NULL,
  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_doc_content_rule_code (rule_code),
  KEY idx_doc_content_rule_match (active, document_family, audience, priority),
  KEY idx_doc_content_rule_block (block_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_content_rule_hits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  matched TINYINT(1) NOT NULL DEFAULT 0,
  context_json JSON NULL,
  reason TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_rule_hit_rule (rule_id, created_at),
  KEY idx_doc_rule_hit_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_content_rule_tests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  context_json JSON NULL,
  selected_blocks_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_rule_test_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_block_change_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  block_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  event_body TEXT NULL,
  old_json JSON NULL,
  new_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_doc_block_change (block_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
