CREATE TABLE IF NOT EXISTS `asr_wrk_analysis` (
  `wrk_no` int NOT NULL COMMENT '工作号',
  `wms_wrk_no` varchar(64) DEFAULT NULL COMMENT 'WMS任务号',
  `io_type` int DEFAULT NULL COMMENT '入出库类型',
  `final_wrk_sts` bigint DEFAULT NULL COMMENT '最终工作状态',
  `source_sta_no` int DEFAULT NULL COMMENT '源站',
  `sta_no` int DEFAULT NULL COMMENT '目标站',
  `source_loc_no` varchar(64) DEFAULT NULL COMMENT '源库位',
  `loc_no` varchar(64) DEFAULT NULL COMMENT '目标库位',
  `crn_no` int DEFAULT NULL COMMENT '堆垛机号',
  `dual_crn_no` int DEFAULT NULL COMMENT '双工位堆垛机号',
  `rgv_no` int DEFAULT NULL COMMENT 'RGV号',
  `appe_time` datetime DEFAULT NULL COMMENT '创建时间',
  `finish_time` datetime DEFAULT NULL COMMENT '完成时间',
  `total_duration_ms` bigint DEFAULT NULL COMMENT '总耗时毫秒',
  `station_start_time` datetime DEFAULT NULL COMMENT '站点开始时间',
  `station_end_time` datetime DEFAULT NULL COMMENT '站点结束时间',
  `station_duration_ms` bigint DEFAULT NULL COMMENT '站点耗时毫秒',
  `crane_start_time` datetime DEFAULT NULL COMMENT '堆垛机开始时间',
  `crane_end_time` datetime DEFAULT NULL COMMENT '堆垛机结束时间',
  `crane_duration_ms` bigint DEFAULT NULL COMMENT '堆垛机耗时毫秒',
  `has_fault` int DEFAULT '0' COMMENT '是否故障',
  `fault_count` int DEFAULT '0' COMMENT '故障次数',
  `fault_duration_ms` bigint DEFAULT '0' COMMENT '故障耗时毫秒',
  `crn_fault_count` int DEFAULT '0' COMMENT '单堆垛机故障次数',
  `crn_fault_duration_ms` bigint DEFAULT '0' COMMENT '单堆垛机故障耗时毫秒',
  `dual_crn_fault_count` int DEFAULT '0' COMMENT '双工位堆垛机故障次数',
  `dual_crn_fault_duration_ms` bigint DEFAULT '0' COMMENT '双工位堆垛机故障耗时毫秒',
  `rgv_fault_count` int DEFAULT '0' COMMENT 'RGV故障次数',
  `rgv_fault_duration_ms` bigint DEFAULT '0' COMMENT 'RGV故障耗时毫秒',
  `metric_completeness` varchar(16) DEFAULT 'PARTIAL' COMMENT '数据完整性',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`wrk_no`),
  KEY `idx_wrk_analysis_finish_time` (`finish_time`),
  KEY `idx_wrk_analysis_appe_time` (`appe_time`),
  KEY `idx_wrk_analysis_io_type` (`io_type`),
  KEY `idx_wrk_analysis_final_wrk_sts` (`final_wrk_sts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务执行分析汇总表';

SET @wrk_analysis_inbound_status_migrated := CASE
    WHEN EXISTS (
        SELECT 1
        FROM `asr_bas_wrk_status`
        WHERE `wrk_sts` = 3
          AND `wrk_desc` = '站点运行完成'
    ) THEN 1
    ELSE 0
END;

UPDATE `asr_wrk_mast`
SET `wrk_sts` = CASE
    WHEN `wrk_sts` = 3 THEN 4
    WHEN `wrk_sts` = 4 THEN 5
    ELSE `wrk_sts`
END
WHERE @wrk_analysis_inbound_status_migrated = 0
  AND `wrk_sts` IN (3, 4);

UPDATE `asr_wrk_mast_log`
SET `wrk_sts` = CASE
    WHEN `wrk_sts` = 3 THEN 4
    WHEN `wrk_sts` = 4 THEN 5
    ELSE `wrk_sts`
END
WHERE @wrk_analysis_inbound_status_migrated = 0
  AND `wrk_sts` IN (3, 4);

UPDATE `asr_bas_crnp_err_log`
SET `wrk_sts` = CASE
    WHEN `wrk_sts` = 3 THEN 4
    WHEN `wrk_sts` = 4 THEN 5
    ELSE `wrk_sts`
END
WHERE @wrk_analysis_inbound_status_migrated = 0
  AND `wrk_sts` IN (3, 4);

UPDATE `asr_bas_dual_crnp_err_log`
SET `wrk_sts` = CASE
    WHEN `wrk_sts` = 3 THEN 4
    WHEN `wrk_sts` = 4 THEN 5
    ELSE `wrk_sts`
END
WHERE @wrk_analysis_inbound_status_migrated = 0
  AND `wrk_sts` IN (3, 4);

UPDATE `asr_bas_rgv_err_log`
SET `wrk_sts` = CASE
    WHEN `wrk_sts` = 3 THEN 4
    WHEN `wrk_sts` = 4 THEN 5
    ELSE `wrk_sts`
END
WHERE @wrk_analysis_inbound_status_migrated = 0
  AND `wrk_sts` IN (3, 4);

INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
VALUES (3, '站点运行完成', NOW(), NOW())
ON DUPLICATE KEY UPDATE
`wrk_desc` = VALUES(`wrk_desc`),
`modi_time` = NOW();

INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
VALUES (4, '设备搬运中', NOW(), NOW())
ON DUPLICATE KEY UPDATE
`wrk_desc` = VALUES(`wrk_desc`),
`modi_time` = NOW();

INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
VALUES (5, '设备搬运完成', NOW(), NOW())
ON DUPLICATE KEY UPDATE
`wrk_desc` = VALUES(`wrk_desc`),
`modi_time` = NOW();

UPDATE `asr_bas_wrk_status`
SET `wrk_desc` = '站点运行中',
    `modi_time` = NOW()
WHERE `wrk_sts` = 2;

INSERT INTO `asr_wrk_analysis` (
    `wrk_no`,
    `wms_wrk_no`,
    `io_type`,
    `final_wrk_sts`,
    `source_sta_no`,
    `sta_no`,
    `source_loc_no`,
    `loc_no`,
    `crn_no`,
    `dual_crn_no`,
    `rgv_no`,
    `appe_time`,
    `finish_time`,
    `total_duration_ms`,
    `station_duration_ms`,
    `crane_duration_ms`,
    `has_fault`,
    `fault_count`,
    `fault_duration_ms`,
    `crn_fault_count`,
    `crn_fault_duration_ms`,
    `dual_crn_fault_count`,
    `dual_crn_fault_duration_ms`,
    `rgv_fault_count`,
    `rgv_fault_duration_ms`,
    `metric_completeness`,
    `create_time`,
    `update_time`
)
SELECT
    l.`wrk_no`,
    l.`wms_wrk_no`,
    l.`io_type`,
    l.`wrk_sts`,
    l.`source_sta_no`,
    l.`sta_no`,
    l.`source_loc_no`,
    l.`loc_no`,
    l.`crn_no`,
    l.`dual_crn_no`,
    l.`rgv_no`,
    l.`appe_time`,
    COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`) AS `finish_time`,
    CASE
        WHEN l.`appe_time` IS NULL OR COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`) IS NULL THEN NULL
        ELSE GREATEST(TIMESTAMPDIFF(MICROSECOND, l.`appe_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), 0) DIV 1000
    END AS `total_duration_ms`,
    CASE WHEN l.`io_type` = 201 THEN 0 ELSE NULL END AS `station_duration_ms`,
    NULL AS `crane_duration_ms`,
    CASE WHEN COALESCE(c.`fault_count`, 0) + COALESCE(d.`fault_count`, 0) + COALESCE(r.`fault_count`, 0) > 0 THEN 1 ELSE 0 END AS `has_fault`,
    COALESCE(c.`fault_count`, 0) + COALESCE(d.`fault_count`, 0) + COALESCE(r.`fault_count`, 0) AS `fault_count`,
    COALESCE(c.`fault_duration_ms`, 0) + COALESCE(d.`fault_duration_ms`, 0) + COALESCE(r.`fault_duration_ms`, 0) AS `fault_duration_ms`,
    COALESCE(c.`fault_count`, 0) AS `crn_fault_count`,
    COALESCE(c.`fault_duration_ms`, 0) AS `crn_fault_duration_ms`,
    COALESCE(d.`fault_count`, 0) AS `dual_crn_fault_count`,
    COALESCE(d.`fault_duration_ms`, 0) AS `dual_crn_fault_duration_ms`,
    COALESCE(r.`fault_count`, 0) AS `rgv_fault_count`,
    COALESCE(r.`fault_duration_ms`, 0) AS `rgv_fault_duration_ms`,
    'PARTIAL' AS `metric_completeness`,
    NOW(),
    NOW()
FROM `asr_wrk_mast_log` l
LEFT JOIN (
    SELECT
        e.`wrk_no`,
        COUNT(*) AS `fault_count`,
        SUM(
            CASE
                WHEN e.`start_time` IS NULL THEN 0
                ELSE GREATEST(
                    TIMESTAMPDIFF(
                        MICROSECOND,
                        e.`start_time`,
                        COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
                    ),
                    0
                ) DIV 1000
            END
        ) AS `fault_duration_ms`
    FROM `asr_bas_crnp_err_log` e
    LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
    GROUP BY e.`wrk_no`
) c ON c.`wrk_no` = l.`wrk_no`
LEFT JOIN (
    SELECT
        e.`wrk_no`,
        COUNT(*) AS `fault_count`,
        SUM(
            CASE
                WHEN e.`start_time` IS NULL THEN 0
                ELSE GREATEST(
                    TIMESTAMPDIFF(
                        MICROSECOND,
                        e.`start_time`,
                        COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
                    ),
                    0
                ) DIV 1000
            END
        ) AS `fault_duration_ms`
    FROM `asr_bas_dual_crnp_err_log` e
    LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
    GROUP BY e.`wrk_no`
) d ON d.`wrk_no` = l.`wrk_no`
LEFT JOIN (
    SELECT
        e.`task_no` AS `wrk_no`,
        COUNT(*) AS `fault_count`,
        SUM(
            CASE
                WHEN e.`start_time` IS NULL THEN 0
                ELSE GREATEST(
                    TIMESTAMPDIFF(
                        MICROSECOND,
                        e.`start_time`,
                        COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
                    ),
                    0
                ) DIV 1000
            END
        ) AS `fault_duration_ms`
    FROM `asr_bas_rgv_err_log` e
    LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`task_no`
    GROUP BY e.`task_no`
) r ON r.`wrk_no` = l.`wrk_no`
ON DUPLICATE KEY UPDATE
    `wms_wrk_no` = VALUES(`wms_wrk_no`),
    `io_type` = VALUES(`io_type`),
    `final_wrk_sts` = VALUES(`final_wrk_sts`),
    `source_sta_no` = VALUES(`source_sta_no`),
    `sta_no` = VALUES(`sta_no`),
    `source_loc_no` = VALUES(`source_loc_no`),
    `loc_no` = VALUES(`loc_no`),
    `crn_no` = VALUES(`crn_no`),
    `dual_crn_no` = VALUES(`dual_crn_no`),
    `rgv_no` = VALUES(`rgv_no`),
    `appe_time` = VALUES(`appe_time`),
    `finish_time` = VALUES(`finish_time`),
    `total_duration_ms` = VALUES(`total_duration_ms`),
    `station_duration_ms` = VALUES(`station_duration_ms`),
    `crane_duration_ms` = VALUES(`crane_duration_ms`),
    `has_fault` = VALUES(`has_fault`),
    `fault_count` = VALUES(`fault_count`),
    `fault_duration_ms` = VALUES(`fault_duration_ms`),
    `crn_fault_count` = VALUES(`crn_fault_count`),
    `crn_fault_duration_ms` = VALUES(`crn_fault_duration_ms`),
    `dual_crn_fault_count` = VALUES(`dual_crn_fault_count`),
    `dual_crn_fault_duration_ms` = VALUES(`dual_crn_fault_duration_ms`),
    `rgv_fault_count` = VALUES(`rgv_fault_count`),
    `rgv_fault_duration_ms` = VALUES(`rgv_fault_duration_ms`),
    `metric_completeness` = VALUES(`metric_completeness`),
    `update_time` = NOW();

SET @wrk_analysis_parent_id := COALESCE(
  (
    SELECT `id`
    FROM `sys_resource`
    WHERE `code` = 'logReport' AND `level` = 1
    ORDER BY `id`
    LIMIT 1
  ),
  (
    SELECT `id`
    FROM `sys_resource`
    WHERE `code` = 'develop' AND `level` = 1
    ORDER BY `id`
    LIMIT 1
  )
);

INSERT INTO `sys_resource`(`code`, `name`, `resource_id`, `level`, `sort`, `status`)
SELECT 'wrkAnalysis/wrkAnalysis.html', '任务执行分析', @wrk_analysis_parent_id, 2, 996, 1
FROM dual
WHERE @wrk_analysis_parent_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_resource`
    WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2
  );

UPDATE `sys_resource`
SET `name` = '任务执行分析',
    `resource_id` = @wrk_analysis_parent_id,
    `level` = 2,
    `sort` = 996,
    `status` = 1
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2;

SET @wrk_analysis_id := (
  SELECT `id`
  FROM `sys_resource`
  WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2
  ORDER BY `id`
  LIMIT 1
);

INSERT INTO `sys_resource`(`code`, `name`, `resource_id`, `level`, `sort`, `status`)
SELECT 'wrkAnalysis/wrkAnalysis.html#view', '查看', @wrk_analysis_id, 3, 1, 1
FROM dual
WHERE @wrk_analysis_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_resource`
    WHERE `code` = 'wrkAnalysis/wrkAnalysis.html#view' AND `level` = 3
  );

UPDATE `sys_resource`
SET `name` = '查看',
    `resource_id` = @wrk_analysis_id,
    `level` = 3,
    `sort` = 1,
    `status` = 1
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html#view' AND `level` = 3;