AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.367.sql

-- WP-SD5: persist automatic effectiveness evaluation and audit log.
SET @auto_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND COLUMN_NAME='AutoVerifyResult')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD COLUMN `AutoVerifyResult` VARCHAR(32) NULL',
  'SELECT 1');
PREPARE auto_stmt FROM @auto_sql;
EXECUTE auto_stmt;
DEALLOCATE PREPARE auto_stmt;

SET @autoj_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND COLUMN_NAME='AutoVerifyJson')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD COLUMN `AutoVerifyJson` TEXT NULL',
  'SELECT 1');
PREPARE autoj_stmt FROM @autoj_sql;
EXECUTE autoj_stmt;
DEALLOCATE PREPARE autoj_stmt;

SET @autot_sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND COLUMN_NAME='AutoVerifyTime')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD COLUMN `AutoVerifyTime` DATETIME NULL',
  'SELECT 1');
PREPARE autot_stmt FROM @autot_sql;
EXECUTE autot_stmt;
DEALLOCATE PREPARE autot_stmt;

CREATE TABLE IF NOT EXISTS `ado_smart_ops_improvement_verify_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `plan_id` BIGINT NOT NULL,
  `auto_result` VARCHAR(32) NULL,
  `manual_result` VARCHAR(32) NULL,
  `remark` TEXT NULL,
  `operator_user_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_improve_verify_log` (`tenant_id`,`plan_id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;