-- Baltique OS Core 0.78.35 — Enterprise UAT Training / SOP Center

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS enterprise_uat_training_blueprints (

              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              role_key VARCHAR(80) NOT NULL,
              training_key VARCHAR(160) NOT NULL,
              training_label VARCHAR(255) NOT NULL,
              sop_reference VARCHAR(160) NULL,
              required_flag TINYINT(1) NOT NULL DEFAULT 1,
              active TINYINT(1) NOT NULL DEFAULT 1,
              sort_order INT NOT NULL DEFAULT 100,
              evidence_json JSON NULL,
              created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at DATETIME NULL,
              PRIMARY KEY(id), UNIQUE KEY uq_enterprise_uat_training_blueprints_key (role_key, training_key), KEY idx_enterprise_uat_training_blueprints_active (active, role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_uat_training_progress (

              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              role_key VARCHAR(80) NOT NULL,
              training_key VARCHAR(160) NOT NULL,
              status VARCHAR(40) NOT NULL DEFAULT 'not_started',
              signed_off_by_user_id BIGINT UNSIGNED NULL,
              signed_off_at DATETIME NULL,
              evidence_json JSON NULL,
              created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at DATETIME NULL,
              PRIMARY KEY(id), UNIQUE KEY uq_enterprise_uat_training_progress_key (role_key, training_key), KEY idx_enterprise_uat_training_progress_status (status, role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_uat_sop_signoffs (

              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              sop_key VARCHAR(160) NOT NULL,
              owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
              status VARCHAR(40) NOT NULL DEFAULT 'pending',
              signed_off_by_user_id BIGINT UNSIGNED NULL,
              signed_off_at DATETIME NULL,
              review_due_at DATETIME NULL,
              evidence_json JSON NULL,
              created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at DATETIME NULL,
              PRIMARY KEY(id), UNIQUE KEY uq_enterprise_uat_sop_signoffs_key (sop_key, owner_role), KEY idx_enterprise_uat_sop_signoffs_status (status, owner_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_uat_training_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',
              items_seeded INT NOT NULL DEFAULT 0,
              open_items INT NOT NULL DEFAULT 0,
              signed_off_items INT NOT NULL DEFAULT 0,
              evidence_json JSON NULL,
              created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at DATETIME NULL,
              PRIMARY KEY(id), UNIQUE KEY uq_enterprise_uat_training_runs_run (run_uuid), KEY idx_enterprise_uat_training_runs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES ('core_version_07835', '1', NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value='1', updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES ('enterprise_uat_training_auto_signoff_enabled', '0', NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES ('enterprise_uat_training_auto_certify_enabled', '0', NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES ('enterprise_uat_training_auto_patient_message_enabled', '0', NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES ('enterprise_uat_training_cron_enabled', '0', NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();
