risk_control_platform/sql/risk_accident_handle.sql

-- =============================================================
-- 风控平台 数据库增量脚本  事故上报 - 处理功能增强
-- 适用版本: 任何已部署 risk_accident 的环境
-- 兼容: 全部 SQL 幂等,可重复执行(基于 INFORMATION_SCHEMA 判断)
-- 新环境用 risk_control_platform.sql 部署;已有环境用本脚本增量升级
-- =============================================================

SET NAMES utf8mb4;

-- =============================================================
-- 1. 事故表 risk_accident 加处理相关字段
-- =============================================================

-- 1.1 handle_result(处理方式,字典 risk_accident_handle_way)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_accident'
       AND COLUMN_NAME  = 'handle_result') = 0,
  'ALTER TABLE `risk_accident` ADD COLUMN `handle_result` VARCHAR(50) DEFAULT NULL COMMENT ''处理方式(字典:risk_accident_handle_way)'' AFTER `handle_measures`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.2 handle_deadline(处理期限)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_accident'
       AND COLUMN_NAME  = 'handle_deadline') = 0,
  'ALTER TABLE `risk_accident` ADD COLUMN `handle_deadline` DATE DEFAULT NULL COMMENT ''处理期限'' AFTER `handle_result`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.3 handle_attachment_url(处理附件URL,MinIO对象存储路径)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_accident'
       AND COLUMN_NAME  = 'handle_attachment_url') = 0,
  'ALTER TABLE `risk_accident` ADD COLUMN `handle_attachment_url` VARCHAR(500) DEFAULT NULL COMMENT ''处理附件URL(MinIO对象存储路径)'' AFTER `handle_deadline`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.4 handle_attachment_name(处理附件原始文件名)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_accident'
       AND COLUMN_NAME  = 'handle_attachment_name') = 0,
  'ALTER TABLE `risk_accident` ADD COLUMN `handle_attachment_name` VARCHAR(200) DEFAULT NULL COMMENT ''处理附件原始文件名'' AFTER `handle_attachment_url`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.5 handle_time(处理时间)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_accident'
       AND COLUMN_NAME  = 'handle_time') = 0,
  'ALTER TABLE `risk_accident` ADD COLUMN `handle_time` DATETIME DEFAULT NULL COMMENT ''处理时间'' AFTER `handle_attachment_name`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- =============================================================
-- 2. 字典 risk_accident_status(事故状态)清理与新增
--    已有值: 待处理(21300)/调查中(21301)/已结束(21302)/待考核(21303)
--    新流程只用: 待处理(保留) + 归档(新增)
--    历史数据迁移: 调查中/已结束/待考核 -> 待处理(确保状态合法)
-- =============================================================

-- 2.1 删除旧流程状态(调查中/已结束/待考核),只保留 待处理 + 归档
DELETE FROM `sys_dict_data`
 WHERE `dict_type` = 'risk_accident_status'
   AND `dict_label` IN ('调查中', '已结束', '待考核');

-- 2.2 确保"待处理"存在(若已被上面 delete 误删则恢复)
INSERT INTO `sys_dict_data`
    (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 1, '待处理', '待处理', 'risk_accident_status', '', 'danger', 'Y', '0', 103, 1, NOW(), '事故已上报,等待处理'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_accident_status' AND `dict_value` = '待处理');

-- 2.3 新增"归档"
INSERT INTO `sys_dict_data`
    (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 2, '归档', '归档', 'risk_accident_status', '', 'success', 'N', '0', 103, 1, NOW(), '事故已处理完成,可归档'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_accident_status' AND `dict_value` = '归档');


-- =============================================================
-- 3. 字典 seed: 事故处理方式 risk_accident_handle_way
-- =============================================================

-- 3.1 字典类型(若不存在则补)
INSERT INTO `sys_dict_type`
    (`dict_name`, `dict_type`, `status`, `create_dept`, `create_by`, `create_time`, `remark`)
VALUES
    ('事故处理方式', 'risk_accident_handle_way', '0', 103, 1, NOW(), '事故处理方式字典(现场处置/整改完成/教育培训/其他)')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 3.2 字典数据 seed(默认 4 项: 现场处置/整改完成/教育培训/其他)
-- 先清空再 seed(用户可在字典管理界面自行扩展)
DELETE FROM `sys_dict_data`
 WHERE `dict_type` = 'risk_accident_handle_way';

INSERT INTO `sys_dict_data`
    (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_dept`, `create_by`, `create_time`, `remark`)
VALUES
    (1, '现场处置', '现场处置', 'risk_accident_handle_way', '', 'primary', 'N', '0', 103, 1, NOW(), '现场紧急处置完成'),
    (2, '整改完成', '整改完成', 'risk_accident_handle_way', '', 'success', 'N', '0', 103, 1, NOW(), '根本原因已整改'),
    (3, '教育培训', '教育培训', 'risk_accident_handle_way', '', 'warning', 'N', '0', 103, 1, NOW(), '组织相关人员培训'),
    (4, '其他',     '其他',     'risk_accident_handle_way', '', 'info',    'N', '0', 103, 1, NOW(), '其他处理方式');


-- =============================================================
-- 4. (可选)历史事故状态清洗
--    把历史'调查中'/'已结束'/'待考核'状态归并为'待处理'
--    适配新流程(只有 待处理 / 归档 两个状态)
-- =============================================================

UPDATE `risk_accident`
   SET `handle_status` = '待处理'
 WHERE `handle_status` IN ('调查中', '已结束', '待考核');


-- =============================================================
-- 5. 验证清单(可手动 SELECT 确认)
-- =============================================================
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE()
--     AND TABLE_NAME = 'risk_accident'
--     AND COLUMN_NAME IN ('handle_result','handle_deadline','handle_attachment_url','handle_attachment_name','handle_time');
--
-- SELECT dict_type, dict_label, dict_value FROM sys_dict_data
--  WHERE dict_type IN ('risk_accident_status','risk_accident_handle_way')
--  ORDER BY dict_type, dict_sort;