CREATE TABLE IF NOT EXISTS `asr_bas_station_err_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `wrk_no` int DEFAULT NULL COMMENT '工作号',
  `start_time` datetime DEFAULT NULL COMMENT '发生时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `wrk_sts` bigint DEFAULT NULL COMMENT '工作状态',
  `io_type` int DEFAULT NULL COMMENT '入出库类型',
  `station_id` int DEFAULT NULL COMMENT '站点号',
  `loc_no` varchar(64) DEFAULT NULL COMMENT '目标库位',
  `sta_no` int DEFAULT NULL COMMENT '目标站',
  `source_sta_no` int DEFAULT NULL COMMENT '源站',
  `source_loc_no` varchar(64) DEFAULT NULL COMMENT '源库位',
  `barcode` varchar(128) DEFAULT NULL COMMENT '条码',
  `err_code` int DEFAULT NULL COMMENT '异常码',
  `error` text COMMENT '异常描述',
  `status` int DEFAULT NULL COMMENT '异常情况 1未处理 2已修复',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` bigint DEFAULT NULL COMMENT '更新人',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  `system_status` longtext COMMENT '系统状态快照',
  PRIMARY KEY (`id`),
  KEY `idx_station_err_log_wrk_no` (`wrk_no`),
  KEY `idx_station_err_log_station_id` (`station_id`),
  KEY `idx_station_err_log_status` (`status`),
  KEY `idx_station_err_log_start_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='输送站点异常日志';

SET @wrk_analysis_table_exists := (
  SELECT COUNT(*)
  FROM `information_schema`.`TABLES`
  WHERE `TABLE_SCHEMA` = DATABASE()
    AND `TABLE_NAME` = 'asr_wrk_analysis'
);

SET @station_fault_count_exists := (
  SELECT COUNT(*)
  FROM `information_schema`.`COLUMNS`
  WHERE `TABLE_SCHEMA` = DATABASE()
    AND `TABLE_NAME` = 'asr_wrk_analysis'
    AND `COLUMN_NAME` = 'station_fault_count'
);

SET @station_fault_duration_exists := (
  SELECT COUNT(*)
  FROM `information_schema`.`COLUMNS`
  WHERE `TABLE_SCHEMA` = DATABASE()
    AND `TABLE_NAME` = 'asr_wrk_analysis'
    AND `COLUMN_NAME` = 'station_fault_duration_ms'
);

SET @sql_station_fault_count := IF(
  @wrk_analysis_table_exists = 1 AND @station_fault_count_exists = 0,
  'ALTER TABLE `asr_wrk_analysis` ADD COLUMN `station_fault_count` int DEFAULT 0 COMMENT ''输送站点故障次数'' AFTER `rgv_fault_duration_ms`',
  'SELECT 1'
);
PREPARE stmt_station_fault_count FROM @sql_station_fault_count;
EXECUTE stmt_station_fault_count;
DEALLOCATE PREPARE stmt_station_fault_count;

SET @sql_station_fault_duration := IF(
  @wrk_analysis_table_exists = 1 AND @station_fault_duration_exists = 0,
  'ALTER TABLE `asr_wrk_analysis` ADD COLUMN `station_fault_duration_ms` bigint DEFAULT 0 COMMENT ''输送站点故障耗时毫秒'' AFTER `station_fault_count`',
  'SELECT 1'
);
PREPARE stmt_station_fault_duration FROM @sql_station_fault_duration;
EXECUTE stmt_station_fault_duration;
DEALLOCATE PREPARE stmt_station_fault_duration;

UPDATE `asr_wrk_analysis` a
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_station_err_log` e
    LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
    GROUP BY e.`wrk_no`
) s ON s.`wrk_no` = a.`wrk_no`
SET a.`station_fault_count` = COALESCE(s.`fault_count`, 0),
    a.`station_fault_duration_ms` = COALESCE(s.`fault_duration_ms`, 0),
    a.`fault_count` = COALESCE(a.`crn_fault_count`, 0)
        + COALESCE(a.`dual_crn_fault_count`, 0)
        + COALESCE(a.`rgv_fault_count`, 0)
        + COALESCE(s.`fault_count`, 0),
    a.`fault_duration_ms` = COALESCE(a.`crn_fault_duration_ms`, 0)
        + COALESCE(a.`dual_crn_fault_duration_ms`, 0)
        + COALESCE(a.`rgv_fault_duration_ms`, 0)
        + COALESCE(s.`fault_duration_ms`, 0),
    a.`has_fault` = CASE
        WHEN COALESCE(a.`crn_fault_count`, 0)
            + COALESCE(a.`dual_crn_fault_count`, 0)
            + COALESCE(a.`rgv_fault_count`, 0)
            + COALESCE(s.`fault_count`, 0) > 0 THEN 1
        ELSE 0
    END,
    a.`update_time` = NOW()
WHERE @wrk_analysis_table_exists = 1;

SET @station_err_log_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 'basStationErrLog/basStationErrLog.html', '输送站点异常日志', @station_err_log_parent_id, 2, 995, 1
FROM dual
WHERE @station_err_log_parent_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_resource`
    WHERE `code` = 'basStationErrLog/basStationErrLog.html' AND `level` = 2
  );

UPDATE `sys_resource`
SET `name` = '输送站点异常日志',
    `resource_id` = @station_err_log_parent_id,
    `level` = 2,
    `sort` = 995,
    `status` = 1
WHERE `code` = 'basStationErrLog/basStationErrLog.html' AND `level` = 2;

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

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

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