-- Baltique OS Core 2.4.1
-- Clinical Sandbox & Role UAT Activation
-- Reference migration. Runtime upgrade performs the same schema creation idempotently.

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_scenarios (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 scenario_key VARCHAR(160) NOT NULL,
 scenario_version VARCHAR(32) NOT NULL DEFAULT '2.4.1',
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 description_pl TEXT NULL,
 description_en TEXT NULL,
 category_key VARCHAR(80) NOT NULL DEFAULT 'end_to_end',
 procedure_key VARCHAR(120) NULL,
 patient_locale VARCHAR(10) NOT NULL DEFAULT 'pl',
 risk_profile VARCHAR(40) NOT NULL DEFAULT 'standard',
 expected_final_decision VARCHAR(40) NOT NULL DEFAULT 'GO',
 critical TINYINT(1) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 definition_json JSON NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_sandbox_scenario(scenario_key),
 KEY idx_pro_sandbox_scenario_active(active,critical,category_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_campaigns (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 campaign_uuid VARCHAR(80) NOT NULL,
 milestone_version VARCHAR(32) NOT NULL DEFAULT '0.79.0',
 campaign_label VARCHAR(255) NOT NULL,
 environment_key VARCHAR(40) NOT NULL DEFAULT 'sandbox',
 campaign_scope VARCHAR(40) NOT NULL DEFAULT 'critical',
 status VARCHAR(40) NOT NULL DEFAULT 'running',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 scenarios_total INT NOT NULL DEFAULT 0,
 scenarios_passed INT NOT NULL DEFAULT 0,
 scenarios_failed INT NOT NULL DEFAULT 0,
 scenarios_blocked INT NOT NULL DEFAULT 0,
 started_by_user_id BIGINT UNSIGNED NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 finished_at DATETIME NULL,
 notes TEXT NULL,
 evidence_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_sandbox_campaign(campaign_uuid),
 KEY idx_pro_sandbox_campaign_status(status,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL,
 campaign_uuid VARCHAR(80) NULL,
 scenario_key VARCHAR(160) NOT NULL,
 synthetic_case_uuid VARCHAR(80) NOT NULL,
 milestone_version VARCHAR(32) NOT NULL DEFAULT '0.79.0',
 environment_key VARCHAR(40) NOT NULL DEFAULT 'sandbox',
 status VARCHAR(40) NOT NULL DEFAULT 'running',
 decision VARCHAR(40) NOT NULL DEFAULT 'HOLD',
 score INT NOT NULL DEFAULT 0,
 current_step INT NOT NULL DEFAULT 0,
 steps_total INT NOT NULL DEFAULT 0,
 steps_passed INT NOT NULL DEFAULT 0,
 steps_failed INT NOT NULL DEFAULT 0,
 steps_blocked INT NOT NULL DEFAULT 0,
 patient_locale VARCHAR(10) NOT NULL DEFAULT 'pl',
 synthetic_identity_json JSON NOT NULL,
 state_json JSON NOT NULL,
 started_by_user_id BIGINT UNSIGNED NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 finished_at DATETIME NULL,
 notes TEXT NULL,
 evidence_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_sandbox_run(run_uuid),
 KEY idx_pro_sandbox_run_campaign(campaign_uuid,status),
 KEY idx_pro_sandbox_run_scenario(scenario_key,status,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_steps (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL,
 step_key VARCHAR(160) NOT NULL,
 sequence_no INT NOT NULL,
 domain_key VARCHAR(80) NOT NULL,
 actor_role VARCHAR(80) NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 route_path VARCHAR(255) NULL,
 action_key VARCHAR(80) NOT NULL DEFAULT 'mutate',
 expected_status VARCHAR(40) NOT NULL DEFAULT 'pass',
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 expected_json JSON NOT NULL,
 actual_json JSON NULL,
 executed_by_user_id BIGINT UNSIGNED NULL,
 executed_at DATETIME NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_sandbox_step(run_uuid,step_key),
 KEY idx_pro_sandbox_step_status(run_uuid,status,sequence_no),
 KEY idx_pro_sandbox_step_role(actor_role,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_events (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NULL,
 step_key VARCHAR(160) NULL,
 event_type VARCHAR(120) NOT NULL,
 payload_json JSON NULL,
 actor_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_pro_sandbox_event_run(run_uuid,created_at),
 KEY idx_pro_sandbox_event_type(event_type,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_uat_role_assignments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 milestone_version VARCHAR(32) NOT NULL DEFAULT '0.79.1',
 role_key VARCHAR(80) NOT NULL,
 suite_key VARCHAR(160) NOT NULL DEFAULT 'role_uat',
 user_id BIGINT UNSIGNED NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'assigned',
 assigned_by_user_id BIGINT UNSIGNED NULL,
 assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 due_at DATETIME NULL,
 completed_at DATETIME NULL,
 signoff_required TINYINT(1) NOT NULL DEFAULT 1,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_uat_role_assignment(milestone_version,role_key,user_id),
 KEY idx_pro_uat_assignment_status(role_key,status,due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_uat_findings (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 finding_uuid VARCHAR(80) NOT NULL,
 milestone_version VARCHAR(32) NOT NULL DEFAULT '0.79.1',
 run_uuid VARCHAR(80) NULL,
 case_key VARCHAR(190) NULL,
 role_key VARCHAR(80) NULL,
 severity VARCHAR(30) NOT NULL DEFAULT 'P2',
 category_key VARCHAR(80) NOT NULL DEFAULT 'workflow',
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'open',
 owner_role VARCHAR(80) NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 due_at DATETIME NULL,
 created_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_uat_finding(finding_uuid),
 KEY idx_pro_uat_finding_status(status,severity,owner_role),
 KEY idx_pro_uat_finding_run(run_uuid,case_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_sandbox_cleanup_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cleanup_uuid VARCHAR(80) NOT NULL,
 campaign_uuid VARCHAR(80) NULL,
 run_uuid VARCHAR(80) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'completed',
 archived_runs INT NOT NULL DEFAULT 0,
 archived_steps INT NOT NULL DEFAULT 0,
 notes TEXT NULL,
 created_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_sandbox_cleanup(cleanup_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Safety settings are also written by the PHP upgrade so the installation remains
-- compatible with historical app_settings schemas.
