risk_control_platform/sql/risk_update_20260706.sql

-- =============================================================
-- 风控平台 数据库增量脚本  2026-07-06
-- 适用版本: risk_control_platform 2026-07-06 之前的部署
-- 合并自以下三份独立脚本(均已在 2026-07-06 当天执行过):
--   1. risk_hidden_danger_dict.sql          隐患处理结果字典
--   2. risk_inspection_record_audit.sql     巡检记录审核字段 + audit_status 字典
--   3. risk_special_equipment_maintenance.sql 特种设备维护字段 + maintenance_status 字典
--
-- 本脚本特征:
--   - 全部 SQL 幂等,可重复执行(基于 INFORMATION_SCHEMA 判断)
--   - 新环境用 risk_control_platform.sql 部署;已有环境用本脚本增量升级
--   - 不修改业务表已有数据,只补字段、索引、字典
-- =============================================================

SET NAMES utf8mb4;

-- =============================================================
-- 1. 巡检记录表 risk_inspection_record 加审核字段
-- =============================================================

-- 1.1 audit_status
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND COLUMN_NAME  = 'audit_status') = 0,
  'ALTER TABLE `risk_inspection_record` ADD COLUMN `audit_status` VARCHAR(20) DEFAULT ''待审核'' COMMENT ''审核状态(待审核/审核通过/审核不通过)''',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.2 auditor_id
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND COLUMN_NAME  = 'auditor_id') = 0,
  'ALTER TABLE `risk_inspection_record` ADD COLUMN `auditor_id` BIGINT DEFAULT NULL COMMENT ''审核人ID''',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.3 auditor_name
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND COLUMN_NAME  = 'auditor_name') = 0,
  'ALTER TABLE `risk_inspection_record` ADD COLUMN `auditor_name` VARCHAR(50) DEFAULT NULL COMMENT ''审核人''',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.4 audit_time
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND COLUMN_NAME  = 'audit_time') = 0,
  'ALTER TABLE `risk_inspection_record` ADD COLUMN `audit_time` DATETIME DEFAULT NULL COMMENT ''审核时间''',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.5 audit_opinion
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND COLUMN_NAME  = 'audit_opinion') = 0,
  'ALTER TABLE `risk_inspection_record` ADD COLUMN `audit_opinion` TEXT COMMENT ''审核意见/不通过原因''',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.6 索引 idx_audit_status
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_record'
       AND INDEX_NAME   = 'idx_audit_status') = 0,
  'ALTER TABLE `risk_inspection_record` ADD KEY `idx_audit_status` (`audit_status`)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- =============================================================
-- 2. 特种设备表 risk_special_equipment 加维护字段
-- =============================================================

-- 2.1 next_maintenance_date
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_special_equipment'
       AND COLUMN_NAME  = 'next_maintenance_date') = 0,
  'ALTER TABLE `risk_special_equipment` ADD COLUMN `next_maintenance_date` DATE DEFAULT NULL COMMENT ''下次维护时间(完成维护时由用户录入)'' AFTER `install_date`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2.2 maintenance_status
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_special_equipment'
       AND COLUMN_NAME  = 'maintenance_status') = 0,
  'ALTER TABLE `risk_special_equipment` ADD COLUMN `maintenance_status` VARCHAR(20) DEFAULT ''未维护'' COMMENT ''维护状态(未维护/待维护/维护中/正常)'' AFTER `next_maintenance_date`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2.3 索引 idx_maintenance_status
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_special_equipment'
       AND INDEX_NAME   = 'idx_maintenance_status') = 0,
  'ALTER TABLE `risk_special_equipment` ADD KEY `idx_maintenance_status` (`maintenance_status`)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2.4 索引 idx_next_maintenance_date
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_special_equipment'
       AND INDEX_NAME   = 'idx_next_maintenance_date') = 0,
  'ALTER TABLE `risk_special_equipment` ADD KEY `idx_next_maintenance_date` (`next_maintenance_date`)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2.5 初始化历史设备 maintenance_status
UPDATE `risk_special_equipment`
   SET `maintenance_status` = '未维护'
 WHERE `maintenance_status` IS NULL OR `maintenance_status` = '';


-- =============================================================
-- 3. 维护记录表 risk_equipment_maintenance 加字段
-- =============================================================

SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_equipment_maintenance'
       AND COLUMN_NAME  = 'next_maintenance_date') = 0,
  'ALTER TABLE `risk_equipment_maintenance` ADD COLUMN `next_maintenance_date` DATE DEFAULT NULL COMMENT ''下次维护时间(维护完成时录入)'' AFTER `actual_end_time`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- =============================================================
-- 4. 字典 seed: 隐患处理结果 risk_handle_result
-- =============================================================

-- 4.1 字典类型
INSERT INTO `sys_dict_type`
    (`dict_name`, `dict_type`, `status`, `create_dept`, `create_by`, `create_time`, `remark`)
VALUES
    ('隐患处理结果', 'risk_handle_result', '0', 103, 1, NOW(), '安全隐患处理结果字典')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 4.2 字典数据 seed(只保留隐患已处置 / 隐患误报 两项)
DELETE FROM `sys_dict_data`
 WHERE `dict_type` = 'risk_handle_result'
   AND `dict_value` IN ('隐患已消除', '已设置警戒', '已上报整改', '临时管控', '其他');

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_handle_result', '', 'success', 'N', '0', 103, 1, NOW(), '隐患已经现场处置完成'),
    (2, '隐患误报',   '隐患误报',   'risk_handle_result', '', 'info',    'N', '0', 103, 1, NOW(), '经核实为误报,无需处置')
ON DUPLICATE KEY UPDATE `update_time` = NOW();


-- =============================================================
-- 5. 字典 seed: 审核状态 audit_status (巡检/作业/人员变动通用)
-- =============================================================

-- 5.1 字典类型(若不存在则补)
INSERT IGNORE INTO `sys_dict_type`
    (`dict_name`, `dict_type`, `remark`, `create_dept`, `create_by`, `create_time`, `update_by`, `update_time`)
VALUES
    ('审核状态', 'audit_status', '巡检/作业/人员变动等业务通用审核状态', 103, 1, NOW(), 1, NOW());

-- 5.2 字典数据
DELETE FROM `sys_dict_data`
 WHERE `dict_type` = 'audit_status'
   AND `dict_label` IN ('待审核', '审核通过', '审核不通过');

INSERT INTO `sys_dict_data`
    (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `remark`, `create_dept`, `create_by`, `create_time`, `update_by`, `update_time`)
VALUES
    (1, '待审核',     '待审核',     'audit_status', '', 'warning', 'N', '巡检人提交后默认状态', 103, 1, NOW(), 1, NOW()),
    (2, '审核通过',   '审核通过',   'audit_status', '', 'success', 'N', '审核人审核通过',       103, 1, NOW(), 1, NOW()),
    (3, '审核不通过', '审核不通过', 'audit_status', '', 'danger',  'N', '审核不通过需填原因',   103, 1, NOW(), 1, NOW());


-- =============================================================
-- 6. 字典 seed: 维护状态 maintenance_status
-- =============================================================

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  0, '未维护',  '未维护',  'maintenance_status', '', 'info',    'N', '0', 103, 1, NOW(), '设备从未做过维护'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_status' AND `dict_value` = '未维护');

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 10, '待维护',  '待维护',  'maintenance_status', '', 'warning', 'N', '0', 103, 1, NOW(), '下一次维护时间已过'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_status' AND `dict_value` = '待维护');

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 20, '维护中',  '维护中',  'maintenance_status', '', 'primary', 'N', '0', 103, 1, NOW(), '正在进行维护作业'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_status' AND `dict_value` = '维护中');

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 30, '已维护',  '已维护',  'maintenance_status', '', 'success', 'N', '0', 103, 1, NOW(), '本次维护已完成且尚未到期'
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_status' AND `dict_value` = '已维护');


-- =============================================================
-- 7. (可选)历史数据清洗
-- =============================================================

-- 7.1 隐患历史"处理中"状态归并到"待处理"
UPDATE `risk_hidden_danger`
   SET `handle_status` = '待处理'
 WHERE `handle_status` = '处理中';


-- =============================================================
-- 8. 验证清单(可手动 SELECT 确认)
-- =============================================================
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE()
--     AND TABLE_NAME = 'risk_inspection_record'
--     AND COLUMN_NAME IN ('audit_status','auditor_id','auditor_name','audit_time','audit_opinion');
--
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE()
--     AND TABLE_NAME = 'risk_special_equipment'
--     AND COLUMN_NAME IN ('next_maintenance_date','maintenance_status');
--
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE()
--     AND TABLE_NAME = 'risk_equipment_maintenance'
--     AND COLUMN_NAME = 'next_maintenance_date';
--
-- SELECT dict_type, dict_label, dict_value FROM sys_dict_data
--  WHERE dict_type IN ('risk_handle_result','audit_status','maintenance_status')
--  ORDER BY dict_type, dict_sort;