-- Baltique OS Core 2.5.0
-- Clinical Go-Live Master Execution Programme
-- Reference migration; the PHP upgrader performs idempotent seeding and compatibility checks.

CREATE TABLE IF NOT EXISTS enterprise_clinical_go_live_programs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_key VARCHAR(120) NOT NULL UNIQUE,
 program_label VARCHAR(255) NOT NULL,
 target_go_live_date DATE NULL,
 timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/Warsaw',
 current_phase_key VARCHAR(80) NOT NULL DEFAULT 'baseline',
 status VARCHAR(40) NOT NULL DEFAULT 'planning',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 overall_score INT NOT NULL DEFAULT 0,
 owner_user_id BIGINT UNSIGNED NULL,
 settings_json JSON NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_go_live_program_status(status,target_go_live_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_go_live_workstreams (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_id BIGINT UNSIGNED NOT NULL,
 workstream_key VARCHAR(120) NOT NULL,
 sequence_no INT NOT NULL,
 phase_key VARCHAR(80) NOT NULL,
 linked_milestone_version VARCHAR(32) NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 purpose_pl TEXT NULL,
 purpose_en TEXT NULL,
 owner_role VARCHAR(80) NOT NULL,
 reviewer_role VARCHAR(80) NULL,
 start_offset_days INT NOT NULL DEFAULT 0,
 end_offset_days INT NOT NULL DEFAULT 0,
 planned_start_date DATE NULL,
 planned_end_date DATE NULL,
 blocking TINYINT(1) NOT NULL DEFAULT 1,
 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,
 result_json JSON NULL,
 actual_started_at DATETIME NULL,
 actual_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_go_live_workstream(program_id,workstream_key),
 KEY idx_go_live_workstream_phase(program_id,phase_key,sequence_no),
 KEY idx_go_live_workstream_status(program_id,status,decision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_go_live_tasks (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_id BIGINT UNSIGNED NOT NULL,
 workstream_key VARCHAR(120) NOT NULL,
 task_key VARCHAR(160) NOT NULL,
 sequence_no INT NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 description_pl TEXT NULL,
 description_en TEXT NULL,
 owner_role VARCHAR(80) NOT NULL,
 reviewer_role VARCHAR(80) NULL,
 evidence_type VARCHAR(80) NOT NULL DEFAULT 'documented_evidence',
 automated_check_key VARCHAR(160) NULL,
 linked_milestone_version VARCHAR(32) NULL,
 blocking TINYINT(1) NOT NULL DEFAULT 1,
 severity VARCHAR(30) NOT NULL DEFAULT 'critical',
 dependencies_json JSON NULL,
 start_offset_days INT NOT NULL DEFAULT 0,
 end_offset_days INT NOT NULL DEFAULT 0,
 planned_start_date DATE NULL,
 planned_end_date DATE NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'not_started',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 evidence_reference VARCHAR(1000) NULL,
 result_json JSON NULL,
 completed_by_user_id BIGINT UNSIGNED NULL,
 completed_at DATETIME NULL,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 review_notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_go_live_task(program_id,task_key),
 KEY idx_go_live_task_workstream(program_id,workstream_key,sequence_no),
 KEY idx_go_live_task_status(program_id,status,severity),
 KEY idx_go_live_task_due(program_id,planned_end_date,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_regulatory_assessments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_id BIGINT UNSIGNED NOT NULL,
 assessment_key VARCHAR(160) NOT NULL,
 topic_key VARCHAR(120) NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 question_pl TEXT NOT NULL,
 question_en TEXT NOT NULL,
 decision VARCHAR(80) NOT NULL DEFAULT 'pending',
 status VARCHAR(40) NOT NULL DEFAULT 'not_started',
 rationale TEXT NULL,
 owner_role VARCHAR(80) NOT NULL,
 reviewer_role VARCHAR(80) NULL,
 evidence_reference VARCHAR(1000) NULL,
 due_date DATE NULL,
 completed_by_user_id BIGINT UNSIGNED NULL,
 completed_at DATETIME NULL,
 reviewed_by_user_id BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 details_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_regulatory_assessment(program_id,assessment_key),
 KEY idx_regulatory_status(program_id,status,topic_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_environment_qualifications (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_id BIGINT UNSIGNED NOT NULL,
 environment_key VARCHAR(40) NOT NULL,
 check_key VARCHAR(160) NOT NULL,
 category_key VARCHAR(80) NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 blocking TINYINT(1) NOT NULL DEFAULT 1,
 status VARCHAR(40) NOT NULL DEFAULT 'not_run',
 measured_value VARCHAR(255) NULL,
 threshold_value VARCHAR(255) NULL,
 evidence_reference VARCHAR(1000) NULL,
 result_json JSON NULL,
 executed_by_user_id BIGINT UNSIGNED NULL,
 executed_at DATETIME 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_environment_check(program_id,environment_key,check_key),
 KEY idx_environment_status(program_id,environment_key,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_go_live_readiness_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL UNIQUE,
 program_id BIGINT UNSIGNED NOT NULL,
 decision VARCHAR(20) NOT NULL,
 score INT NOT NULL DEFAULT 0,
 critical_failed INT NOT NULL DEFAULT 0,
 warnings INT NOT NULL DEFAULT 0,
 tasks_total INT NOT NULL DEFAULT 0,
 tasks_completed INT NOT NULL DEFAULT 0,
 summary_json JSON NULL,
 executed_by_user_id BIGINT UNSIGNED NULL,
 executed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_go_live_readiness(program_id,executed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_cutover_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_uuid VARCHAR(80) NOT NULL UNIQUE,
 program_id BIGINT UNSIGNED NOT NULL,
 run_type VARCHAR(40) NOT NULL DEFAULT 'rehearsal',
 scheduled_at DATETIME NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 decision VARCHAR(20) NOT NULL DEFAULT 'HOLD',
 commander_user_id BIGINT UNSIGNED NULL,
 rollback_owner_user_id BIGINT UNSIGNED NULL,
 rollback_decision VARCHAR(40) NOT NULL DEFAULT 'not_needed',
 notes TEXT NULL,
 result_json JSON 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_cutover_run(program_id,run_type,status,scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_cutover_steps (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cutover_run_id BIGINT UNSIGNED NOT NULL,
 step_key VARCHAR(160) NOT NULL,
 sequence_no INT NOT NULL,
 title_pl VARCHAR(255) NOT NULL,
 title_en VARCHAR(255) NOT NULL,
 owner_role VARCHAR(80) NOT NULL,
 blocking TINYINT(1) NOT NULL DEFAULT 1,
 status VARCHAR(40) NOT NULL DEFAULT 'not_started',
 evidence_reference VARCHAR(1000) 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_cutover_step(cutover_run_id,step_key),
 KEY idx_cutover_step_status(cutover_run_id,status,sequence_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_hypercare_shifts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 program_id BIGINT UNSIGNED NOT NULL,
 shift_date DATE NOT NULL,
 shift_key VARCHAR(40) NOT NULL,
 primary_owner_user_id BIGINT UNSIGNED NULL,
 secondary_owner_user_id BIGINT UNSIGNED NULL,
 clinical_owner_user_id BIGINT UNSIGNED NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'planned',
 open_p0 INT NOT NULL DEFAULT 0,
 open_p1 INT NOT NULL DEFAULT 0,
 summary TEXT NULL,
 handover_notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_hypercare_shift(program_id,shift_date,shift_key),
 KEY idx_hypercare_status(program_id,status,shift_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Safety settings are applied by /upgrade_2_5_0.php so the migration remains portable across historical app_settings schemas.
