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

-- 1.0.345 S1 看板异步重算任务表、全局锁，以及陈旧 S1 RUNNING 日志收口。
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `ado_s1_dashboard_rebuild_job` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `factory_id` BIGINT NOT NULL DEFAULT 1,
  `status` VARCHAR(20) NOT NULL,
  `trigger_type` VARCHAR(20) NOT NULL,
  `requested_by` BIGINT NULL,
  `batch_id` VARCHAR(100) NULL,
  `transform_run_log_id` BIGINT NULL,
  `stage_rows` INT NOT NULL DEFAULT 0,
  `standard_rows` INT NOT NULL DEFAULT 0,
  `dwd_rows` INT NOT NULL DEFAULT 0,
  `kpi_rows` INT NOT NULL DEFAULT 0,
  `atomic_rows` INT NOT NULL DEFAULT 0,
  `error_message` VARCHAR(2000) NULL,
  `submitted_at` DATETIME(3) NOT NULL,
  `started_at` DATETIME(3) NULL,
  `finished_at` DATETIME(3) NULL,
  `heartbeat_at` DATETIME(3) NULL,
  `duration_ms` INT NULL,
  `create_time` DATETIME(3) NOT NULL,
  `update_time` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_s1_rebuild_status` (`status`, `submitted_at`),
  KEY `ix_s1_rebuild_scope` (`tenant_id`, `factory_id`, `status`, `submitted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ado_s1_mdp_run_lock` (
  `lock_name` VARCHAR(64) NOT NULL,
  `holder_id` VARCHAR(100) NULL,
  `job_id` BIGINT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'FREE',
  `acquired_at` DATETIME(3) NULL,
  `heartbeat_at` DATETIME(3) NULL,
  PRIMARY KEY (`lock_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ado_s1_mdp_run_lock` (`lock_name`, `status`)
SELECT 'S1_MDP_FULL', 'FREE'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `ado_s1_mdp_run_lock` WHERE `lock_name`='S1_MDP_FULL');

UPDATE `mdp_transform_run_log`
SET `status`='FAILED',
    `end_time`=NOW(3),
    `error_message`='HTTP 请求已取消/服务中断后的存量收口',
    `update_time`=NOW(3),
    `duration_ms`=LEAST(2147483647, GREATEST(0, TIMESTAMPDIFF(SECOND, `start_time`, NOW())) * 1000)
WHERE `job_code`='S1_MDP_SYNC_TRANSFORM'
  AND `status`='RUNNING'
  AND `start_time` < DATE_SUB(NOW(), INTERVAL 30 MINUTE);