CREATE TABLE IF NOT EXISTS system_package_pipeline_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 package_id BIGINT UNSIGNED NULL,
 package_name VARCHAR(255) NOT NULL DEFAULT '',
 package_version VARCHAR(50) NOT NULL DEFAULT '',
 archive_name VARCHAR(255) NOT NULL DEFAULT '',
 run_type VARCHAR(30) NOT NULL DEFAULT 'simulation',
 status VARCHAR(30) NOT NULL DEFAULT 'RUNNING',
 decision VARCHAR(50) NOT NULL DEFAULT 'PENDING',
 score TINYINT UNSIGNED NOT NULL DEFAULT 0,
 blocking_step VARCHAR(100) NULL,
 summary_json LONGTEXT NULL,
 duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
 executed_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 finished_at DATETIME NULL,
 PRIMARY KEY (id), KEY idx_pipeline_package(package_id), KEY idx_pipeline_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS system_package_pipeline_steps (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 run_id BIGINT UNSIGNED NOT NULL,
 step_code VARCHAR(100) NOT NULL,
 step_label VARCHAR(160) NOT NULL,
 step_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
 decision VARCHAR(50) NOT NULL DEFAULT 'PENDING',
 score TINYINT UNSIGNED NOT NULL DEFAULT 0,
 critical TINYINT(1) NOT NULL DEFAULT 0,
 message TEXT NULL,
 issues_json LONGTEXT NULL,
 warnings_json LONGTEXT NULL,
 details_json LONGTEXT NULL,
 duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id), UNIQUE KEY uq_pipeline_step(run_id,step_code),
 CONSTRAINT fk_pipeline_step_run FOREIGN KEY(run_id) REFERENCES system_package_pipeline_runs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
