-- Baltique OS Core 0.78.34 — Clinical Sandbox / Synthetic Journey
-- Reference migration. Runtime PHP upgrade performs idempotent schema checks.

CREATE TABLE IF NOT EXISTS enterprise_synthetic_patient_blueprints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  blueprint_key VARCHAR(160) NOT NULL,
  blueprint_label VARCHAR(255) NOT NULL,
  procedure_type VARCHAR(120) NULL,
  risk_profile VARCHAR(80) NOT NULL DEFAULT 'standard',
  scenario_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),
  KEY idx_enterprise_synthetic_patient_blueprints_first (blueprint_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_synthetic_journey_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  blueprint_key VARCHAR(160) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'planned',
  case_spine_id BIGINT UNSIGNED NULL,
  synthetic_flag TINYINT(1) NOT NULL DEFAULT 1,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_synthetic_journey_runs_first (run_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_synthetic_journey_checkpoints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  checkpoint_key VARCHAR(160) NOT NULL,
  checkpoint_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  domain_key VARCHAR(80) NOT NULL DEFAULT 'enterprise',
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_synthetic_journey_checkpoints_first (run_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_sandbox_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(60) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  blueprints_seeded INT NOT NULL DEFAULT 0,
  checkpoints_seeded INT NOT NULL DEFAULT 0,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_clinical_sandbox_runs_first (run_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
