risk_control_platform/sql/risk_special_equipment_maintenance.sql

-- ============================================
-- 特种设备维护模块 - 数据结构升级
-- 按用户需求明确存取逻辑:
--   1. 维护记录存 risk_equipment_maintenance(多条,不变)
--   2. 设备主表存 next_maintenance_date + maintenance_status(冗余字段,便于列表查询)
--   3. 维护状态动态判定:
--      - 最新记录='维护中' → '维护中'
--      - 最新记录='已维护' 且 next_maintenance_date < NOW() → '待维护'
--      - 最新记录='已维护' 且 next_maintenance_date >= NOW() → '正常'
--      - 无维护记录 → '未维护'
-- ============================================

-- 1. 设备表新增:下次维护时间 + 维护状态
ALTER TABLE risk_special_equipment
  ADD COLUMN `next_maintenance_date` DATE DEFAULT NULL
    COMMENT '下次维护时间(完成维护时由用户录入)' AFTER `install_date`,
  ADD COLUMN `maintenance_status` VARCHAR(20) DEFAULT '未维护'
    COMMENT '维护状态(未维护/待维护/维护中/正常)' AFTER `next_maintenance_date`,
  ADD KEY `idx_maintenance_status` (`maintenance_status`),
  ADD KEY `idx_next_maintenance_date` (`next_maintenance_date`);

-- 2. 维护记录表加:预计维护日期(用于列表显示)
-- (plan_start_time/plan_end_time 已存在,可作为预计时间)
-- 加 next_maintenance_date 字段:维护完成时录入的下次维护时间(冗余到记录表,便于历史追溯)
ALTER TABLE risk_equipment_maintenance
  ADD COLUMN `next_maintenance_date` DATE DEFAULT NULL
    COMMENT '下次维护时间(维护完成时录入)' AFTER `actual_end_time`;

-- 3. 初始化历史数据
UPDATE risk_special_equipment
SET maintenance_status = '未维护'
WHERE maintenance_status IS NULL OR maintenance_status = '';

-- ============================================
-- 4. 字典 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 = '已维护');