-- Baltique OS Core 2.4.0
-- Professional Clinical Operations & Release Readiness Rebuild
-- Reference migration. The web installer applies the same schema idempotently and seeds the evidence catalogue.
SET NAMES utf8mb4;
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS enterprise_pro_programs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_key VARCHAR(120) NOT NULL UNIQUE,
 program_label VARCHAR(255) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'active',
 current_milestone_version VARCHAR(32) NULL,
 target_go_live_at DATETIME NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 settings_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_milestones (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_key VARCHAR(120) NOT NULL,
 version VARCHAR(32) NOT NULL,
 sequence_no INT NOT NULL,
 phase_key VARCHAR(80) NOT NULL,
 phase_label_pl VARCHAR(255) NOT NULL,
 phase_label_en VARCHAR(255) NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 purpose_pl TEXT NULL,
 purpose_en TEXT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'not_started',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 score INT NOT NULL DEFAULT 0,
 blockers INT NOT NULL DEFAULT 0,
 warnings INT NOT NULL DEFAULT 0,
 owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
 started_at DATETIME NULL,
 ready_at DATETIME NULL,
 approved_at DATETIME NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 locked_at DATETIME NULL,
 lock_reason TEXT NULL,
 result_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_milestone(program_key,version),
 KEY idx_pro_milestone_status(program_key,status,sequence_no),
 KEY idx_pro_milestone_phase(phase_key,sequence_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_requirements (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 milestone_version VARCHAR(32) NOT NULL,
 requirement_key VARCHAR(160) NOT NULL,
 label_pl VARCHAR(255) NOT NULL,
 label_en VARCHAR(255) NOT NULL,
 description_pl TEXT NULL,
 description_en TEXT NULL,
 adapter_key VARCHAR(190) NOT NULL DEFAULT 'manual_evidence',
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 blocking TINYINT(1) NOT NULL DEFAULT 1,
 owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
 status VARCHAR(40) NOT NULL DEFAULT 'not_evaluated',
 message_pl TEXT NULL,
 message_en TEXT NULL,
 result_json JSON NULL,
 last_evaluated_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_requirement(milestone_version,requirement_key),
 KEY idx_pro_requirement_status(milestone_version,status,severity),
 KEY idx_pro_requirement_owner(owner_role,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_evidence (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 milestone_version VARCHAR(32) NOT NULL,
 requirement_key VARCHAR(160) NOT NULL,
 evidence_key VARCHAR(190) NOT NULL,
 evidence_type VARCHAR(80) NOT NULL DEFAULT 'note',
 title VARCHAR(255) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'submitted',
 source_type VARCHAR(80) NULL,
 source_table VARCHAR(120) NULL,
 source_id BIGINT UNSIGNED NULL,
 source_reference VARCHAR(500) NULL,
 evidence_sha256 CHAR(64) NULL,
 evidence_json JSON NULL,
 submitted_by_user_id BIGINT UNSIGNED NULL,
 submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 review_notes TEXT NULL,
 expires_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_evidence(evidence_key),
 KEY idx_pro_evidence_requirement(milestone_version,requirement_key,status),
 KEY idx_pro_evidence_review(status,submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_signoffs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 milestone_version VARCHAR(32) NOT NULL,
 signoff_key VARCHAR(160) NOT NULL,
 label_pl VARCHAR(255) NOT NULL,
 label_en VARCHAR(255) NOT NULL,
 required_role VARCHAR(80) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 decision VARCHAR(40) NULL,
 signed_by_user_id BIGINT UNSIGNED NULL,
 signed_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_signoff(milestone_version,signoff_key),
 KEY idx_pro_signoff_status(milestone_version,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_test_suites (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 suite_key VARCHAR(160) NOT NULL UNIQUE,
 milestone_version VARCHAR(32) NOT NULL,
 label_pl VARCHAR(255) NOT NULL,
 label_en VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 critical TINYINT(1) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_test_cases (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 suite_key VARCHAR(160) NOT NULL,
 case_key VARCHAR(190) NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 synthetic_only TINYINT(1) NOT NULL DEFAULT 1,
 preconditions_json JSON NULL,
 steps_json JSON NULL,
 expected_json JSON NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 100,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_test_case(suite_key,case_key),
 KEY idx_pro_test_case_suite(suite_key,active,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_test_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL UNIQUE,
 suite_key VARCHAR(160) NOT NULL,
 milestone_version VARCHAR(32) NOT NULL,
 environment_key VARCHAR(80) NOT NULL DEFAULT 'staging',
 status VARCHAR(40) NOT NULL DEFAULT 'running',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 cases_total INT NOT NULL DEFAULT 0,
 cases_passed INT NOT NULL DEFAULT 0,
 cases_failed INT NOT NULL DEFAULT 0,
 cases_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,
 KEY idx_pro_test_run_suite(suite_key,started_at),
 KEY idx_pro_test_run_milestone(milestone_version,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_test_results (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL,
 case_key VARCHAR(190) NOT NULL,
 result_status VARCHAR(40) NOT NULL DEFAULT 'not_run',
 actual_result TEXT NULL,
 defect_reference VARCHAR(255) NULL,
 evidence_json JSON NULL,
 executed_by_user_id BIGINT UNSIGNED NULL,
 executed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_test_result(run_uuid,case_key),
 KEY idx_pro_test_result_status(run_uuid,result_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_risks (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 risk_key VARCHAR(100) NOT NULL UNIQUE,
 milestone_version VARCHAR(32) NULL,
 domain_key VARCHAR(80) NOT NULL DEFAULT 'enterprise',
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 probability INT NOT NULL DEFAULT 3,
 impact INT NOT NULL DEFAULT 3,
 risk_score INT NOT NULL DEFAULT 9,
 severity VARCHAR(30) NOT NULL DEFAULT 'major',
 status VARCHAR(40) NOT NULL DEFAULT 'open',
 owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
 owner_user_id BIGINT UNSIGNED NULL,
 mitigation TEXT NULL,
 contingency TEXT NULL,
 due_at DATETIME NULL,
 closed_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,
 KEY idx_pro_risk_status(status,severity,risk_score),
 KEY idx_pro_risk_milestone(milestone_version,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_change_requests (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 change_key VARCHAR(100) NOT NULL UNIQUE,
 milestone_version VARCHAR(32) NULL,
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 change_type VARCHAR(60) NOT NULL DEFAULT 'fix',
 priority VARCHAR(30) NOT NULL DEFAULT 'P2',
 status VARCHAR(40) NOT NULL DEFAULT 'draft',
 requested_by_user_id BIGINT UNSIGNED NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 clinical_impact VARCHAR(40) NOT NULL DEFAULT 'none',
 data_migration_required TINYINT(1) NOT NULL DEFAULT 0,
 rollback_plan TEXT NULL,
 test_plan TEXT NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 implemented_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_change_status(status,priority),
 KEY idx_pro_change_milestone(milestone_version,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_freezes (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 freeze_key VARCHAR(120) NOT NULL UNIQUE,
 freeze_type VARCHAR(80) NOT NULL,
 scope_json JSON NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 reason TEXT NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_freeze_status(status,freeze_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_pilot_cohorts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cohort_key VARCHAR(120) NOT NULL UNIQUE,
 cohort_type VARCHAR(80) NOT NULL,
 label VARCHAR(255) NOT NULL,
 milestone_version VARCHAR(32) NOT NULL,
 site_key VARCHAR(120) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 scope_json JSON NULL,
 safety_rules_json JSON NULL,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_cohort_status(cohort_type,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_pilot_cases (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cohort_key VARCHAR(120) NOT NULL,
 case_reference_type VARCHAR(80) NOT NULL DEFAULT 'synthetic',
 case_reference_id BIGINT UNSIGNED NULL,
 case_reference_key VARCHAR(190) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 result VARCHAR(40) NULL,
 deviations_json JSON NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_pilot_case(cohort_key,status),
 UNIQUE KEY uq_pro_pilot_case(cohort_key,case_reference_type,case_reference_id,case_reference_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_shadow_comparisons (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cohort_key VARCHAR(120) NOT NULL,
 case_reference_key VARCHAR(190) NOT NULL,
 process_key VARCHAR(160) NOT NULL,
 reference_value TEXT NULL,
 baltique_value TEXT NULL,
 match_status VARCHAR(40) NOT NULL DEFAULT 'pending',
 difference_severity VARCHAR(30) NULL,
 notes TEXT NULL,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_shadow(cohort_key,case_reference_key,process_key),
 KEY idx_pro_shadow_status(cohort_key,match_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_incidents (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 incident_key VARCHAR(100) NOT NULL UNIQUE,
 milestone_version VARCHAR(32) NULL,
 severity VARCHAR(20) NOT NULL DEFAULT 'P2',
 category VARCHAR(80) NOT NULL DEFAULT 'application',
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'open',
 patient_safety_impact TINYINT(1) NOT NULL DEFAULT 0,
 data_integrity_impact TINYINT(1) NOT NULL DEFAULT 0,
 site_key VARCHAR(120) NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 acknowledged_at DATETIME NULL,
 contained_at DATETIME NULL,
 resolved_at DATETIME NULL,
 root_cause TEXT NULL,
 corrective_action TEXT 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,
 KEY idx_pro_incident_status(status,severity),
 KEY idx_pro_incident_milestone(milestone_version,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_incident_actions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 incident_key VARCHAR(100) NOT NULL,
 action_key VARCHAR(120) NOT NULL,
 action_label VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'open',
 due_at DATETIME NULL,
 completed_at DATETIME 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_incident_action(incident_key,action_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_deployments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 deployment_key VARCHAR(120) NOT NULL UNIQUE,
 release_version VARCHAR(40) NOT NULL,
 environment_key VARCHAR(80) NOT NULL,
 deployment_type VARCHAR(60) NOT NULL DEFAULT 'release',
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 package_sha256 CHAR(64) NULL,
 manifest_sha256 CHAR(64) NULL,
 rollback_reference VARCHAR(255) NULL,
 started_by_user_id BIGINT UNSIGNED NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 result_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_deployment_status(environment_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_deployment_steps (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 deployment_key VARCHAR(120) NOT NULL,
 step_key VARCHAR(160) NOT NULL,
 step_label VARCHAR(255) NOT NULL,
 sequence_no INT NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 required_flag TINYINT(1) NOT NULL DEFAULT 1,
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 completed_by_user_id BIGINT UNSIGNED 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_deployment_step(deployment_key,step_key),
 KEY idx_pro_deployment_steps(deployment_key,sequence_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_config_snapshots (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 snapshot_key VARCHAR(120) NOT NULL UNIQUE,
 environment_key VARCHAR(80) NOT NULL,
 release_version VARCHAR(40) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'draft',
 snapshot_sha256 CHAR(64) NOT NULL,
 settings_json JSON NOT NULL,
 feature_flags_json JSON NULL,
 module_state_json JSON NULL,
 created_by_user_id BIGINT UNSIGNED NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 locked_at DATETIME NULL,
 KEY idx_pro_config_snapshot(environment_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_go_live_events (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 event_key VARCHAR(120) NOT NULL UNIQUE,
 release_version VARCHAR(40) NOT NULL,
 site_key VARCHAR(120) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 command_owner_user_id BIGINT UNSIGNED NULL,
 starts_at DATETIME NULL,
 completed_at DATETIME NULL,
 rollback_decision VARCHAR(40) NULL,
 summary_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_go_live_status(status,starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_go_live_checkpoints (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 event_key VARCHAR(120) NOT NULL,
 checkpoint_group VARCHAR(80) NOT NULL DEFAULT 'go_live',
 checkpoint_key VARCHAR(160) NOT NULL,
 checkpoint_label VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 evidence_json JSON NULL,
 completed_by_user_id BIGINT UNSIGNED NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_go_live_checkpoint(event_key,checkpoint_key),
 KEY idx_pro_go_live_checkpoint_status(event_key,status,severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_stabilization_items (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 item_key VARCHAR(120) NOT NULL UNIQUE,
 milestone_version VARCHAR(32) NULL,
 domain_key VARCHAR(80) NOT NULL,
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 priority VARCHAR(20) NOT NULL DEFAULT 'P2',
 status VARCHAR(40) NOT NULL DEFAULT 'open',
 source_type VARCHAR(80) NULL,
 source_reference VARCHAR(255) NULL,
 owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
 owner_user_id BIGINT UNSIGNED NULL,
 target_release VARCHAR(40) NULL,
 clinical_impact VARCHAR(40) NOT NULL DEFAULT 'none',
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_stabilization(status,priority),
 KEY idx_pro_stabilization_domain(domain_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_automation_proposals (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 proposal_key VARCHAR(120) NOT NULL UNIQUE,
 automation_key VARCHAR(160) NOT NULL,
 label VARCHAR(255) NOT NULL,
 domain_key VARCHAR(80) NOT NULL,
 risk_class VARCHAR(30) NOT NULL DEFAULT 'medium',
 status VARCHAR(40) NOT NULL DEFAULT 'draft',
 human_review_required TINYINT(1) NOT NULL DEFAULT 1,
 patient_facing TINYINT(1) NOT NULL DEFAULT 0,
 clinical_decision TINYINT(1) NOT NULL DEFAULT 0,
 reversible TINYINT(1) NOT NULL DEFAULT 1,
 kill_switch_setting VARCHAR(190) NULL,
 owner_role VARCHAR(80) NOT NULL DEFAULT 'automation_owner',
 policy_json JSON NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_automation_status(status,risk_class)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_automation_trials (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 trial_key VARCHAR(120) NOT NULL UNIQUE,
 automation_key VARCHAR(160) NOT NULL,
 trial_mode VARCHAR(40) NOT NULL DEFAULT 'dry_run',
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 sample_size INT NOT NULL DEFAULT 0,
 success_count INT NOT NULL DEFAULT 0,
 false_positive_count INT NOT NULL DEFAULT 0,
 false_negative_count INT NOT NULL DEFAULT 0,
 human_override_count INT NOT NULL DEFAULT 0,
 metrics_json JSON NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_automation_trial(automation_key,status,trial_mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_model_registry (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 model_key VARCHAR(160) NOT NULL UNIQUE,
 model_label VARCHAR(255) NOT NULL,
 model_version VARCHAR(80) NOT NULL,
 model_type VARCHAR(80) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'development',
 owner_role VARCHAR(80) NOT NULL DEFAULT 'model_owner',
 training_data_reference VARCHAR(255) NULL,
 training_data_sha256 CHAR(64) NULL,
 feature_schema_json JSON NULL,
 intended_use TEXT NULL,
 prohibited_use TEXT NULL,
 approval_status VARCHAR(40) NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_model_status(status,approval_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_model_validations (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 validation_key VARCHAR(120) NOT NULL UNIQUE,
 model_key VARCHAR(160) NOT NULL,
 validation_type VARCHAR(80) NOT NULL DEFAULT 'retrospective',
 dataset_reference VARCHAR(255) NULL,
 dataset_sha256 CHAR(64) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 sample_size INT NOT NULL DEFAULT 0,
 metrics_json JSON NULL,
 calibration_json JSON NULL,
 subgroup_analysis_json JSON NULL,
 drift_baseline_json JSON NULL,
 reviewer_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_model_validation(model_key,status,validation_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_experience_experiments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 experiment_key VARCHAR(120) NOT NULL UNIQUE,
 experiment_type VARCHAR(80) NOT NULL,
 label VARCHAR(255) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 locale_scope VARCHAR(80) NOT NULL DEFAULT 'pl,en',
 patient_facing TINYINT(1) NOT NULL DEFAULT 1,
 clinical_safety_review_required TINYINT(1) NOT NULL DEFAULT 1,
 consent_required TINYINT(1) NOT NULL DEFAULT 0,
 cohort_json JSON NULL,
 outcome_metrics_json JSON NULL,
 adverse_events INT NOT NULL DEFAULT 0,
 approved_by_user_id BIGINT UNSIGNED NULL,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_experience_status(experiment_type,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_site_rollouts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 rollout_key VARCHAR(120) NOT NULL UNIQUE,
 site_key VARCHAR(120) NOT NULL,
 label VARCHAR(255) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 rollout_phase VARCHAR(60) NOT NULL DEFAULT 'assessment',
 workflow_version VARCHAR(80) NULL,
 data_boundary_status VARCHAR(40) NOT NULL DEFAULT 'pending',
 support_model_status VARCHAR(40) NOT NULL DEFAULT 'pending',
 target_go_live_at DATETIME NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_site_rollout(site_key,status,rollout_phase)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_site_rollout_checkpoints (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 rollout_key VARCHAR(120) NOT NULL,
 checkpoint_key VARCHAR(160) NOT NULL,
 checkpoint_label VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 evidence_json JSON NULL,
 completed_by_user_id BIGINT UNSIGNED NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_pro_site_checkpoint(rollout_key,checkpoint_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_site_exceptions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 exception_key VARCHAR(120) NOT NULL UNIQUE,
 site_key VARCHAR(120) NOT NULL,
 domain_key VARCHAR(80) NOT NULL,
 title VARCHAR(255) NOT NULL,
 justification TEXT NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 risk_level VARCHAR(30) NOT NULL DEFAULT 'medium',
 expires_at DATETIME NOT NULL,
 approved_by_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_site_exception(site_key,status,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_product_controls (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 control_key VARCHAR(160) NOT NULL UNIQUE,
 control_label VARCHAR(255) NOT NULL,
 domain_key VARCHAR(80) NOT NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'pending',
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 owner_role VARCHAR(80) NOT NULL,
 evidence_json JSON NULL,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_pro_product_control(status,severity,domain_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_role_assignments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 programme_role VARCHAR(80) NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 scope_key VARCHAR(160) NOT NULL DEFAULT 'global',
 status VARCHAR(40) NOT NULL DEFAULT 'active',
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 assigned_by_user_id BIGINT UNSIGNED 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_role_assignment(programme_role,user_id,scope_key),
 KEY idx_pro_role_assignment_user(user_id,status),
 KEY idx_pro_role_assignment_role(programme_role,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_pro_audit_events (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 event_uuid VARCHAR(80) NOT NULL UNIQUE,
 event_type VARCHAR(120) NOT NULL,
 entity_type VARCHAR(120) NOT NULL,
 entity_key VARCHAR(190) NULL,
 milestone_version VARCHAR(32) NULL,
 actor_user_id BIGINT UNSIGNED NULL,
 actor_role VARCHAR(80) NULL,
 ip_address VARCHAR(64) NULL,
 details_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_pro_audit_entity(entity_type,entity_key,created_at),
 KEY idx_pro_audit_milestone(milestone_version,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Safety settings and the 91-milestone catalogue are applied by /upgrade_2_4_0.php.
