-- =============================================
-- 设备点巡检优化 v1.0.74
-- 作者: 周启威
-- 日期: 2026-01-13
-- 描述: 
--   1. 新增设备点位表（关联设备）
--   2. 计划明细表、执行记录明细表
--   3. 维修单点位扩展
--   4. 菜单结构调整（点检、巡检、保养、维修四大模块）
--   5. 简化流程：去掉标准层，直接在计划中选择设备点位
--   6. 维修单增加故障描述字段
-- =============================================

-- =============================================
-- 一、新增表结构
-- =============================================

-- 1.1 设备点位表（关联设备，作为基础数据）
CREATE TABLE IF NOT EXISTS `dm_inspection_item_point` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `item_id` BIGINT DEFAULT NULL COMMENT '关联点检项目ID（可选，兼容旧数据）',
  `equipment_id` BIGINT DEFAULT NULL COMMENT '关联设备ID',
  `equipment_name` VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
  `point_name` VARCHAR(100) NOT NULL COMMENT '点位名称',
  `point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
  `standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
  `upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
  `lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
  `unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
  `check_method` VARCHAR(200) DEFAULT NULL COMMENT '检查方法',
  `sort_order` INT DEFAULT 0 COMMENT '排序号',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
  `create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `update_by` VARCHAR(32) DEFAULT NULL COMMENT '更新人',
  `update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  KEY `idx_equipment_id` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备点位表';

-- 1.2 计划点位明细表（计划关联的点位）
-- 注意：如果表已存在，需要执行下面的ALTER语句修改字段
CREATE TABLE IF NOT EXISTS `dm_inspection_plan_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `plan_id` BIGINT NOT NULL COMMENT '关联计划ID',
  `item_id` BIGINT DEFAULT NULL COMMENT '关联点检项目ID（可选，兼容旧数据）',
  `item_number` VARCHAR(50) DEFAULT NULL COMMENT '项目编号',
  `item_name` VARCHAR(100) DEFAULT NULL COMMENT '项目名称',
  `item_content` VARCHAR(500) DEFAULT NULL COMMENT '项目内容',
  `item_standard` VARCHAR(500) DEFAULT NULL COMMENT '项目标准',
  `equipment_id` BIGINT DEFAULT NULL COMMENT '关联设备ID',
  `equipment_name` VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
  `point_id` BIGINT DEFAULT NULL COMMENT '关联点位ID',
  `point_name` VARCHAR(100) DEFAULT NULL COMMENT '点位名称',
  `point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
  `standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
  `upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
  `lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
  `unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
  `check_method` VARCHAR(200) DEFAULT NULL COMMENT '检查方法',
  `sort_order` INT DEFAULT 0 COMMENT '排序号',
  `create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_plan_id` (`plan_id`),
  KEY `idx_equipment_id` (`equipment_id`),
  KEY `idx_point_id` (`point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计划点位明细表';

-- 1.2.1 修复已存在的dm_inspection_plan_item表（如果表已存在，修改字段允许为空并添加新字段）
SET @dbname = DATABASE();
SET @tablename = 'dm_inspection_plan_item';

-- 修改 item_id 字段允许为空
SET @columnname = 'item_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点检项目ID（可选）'''),
  'SELECT 1'
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 修改 item_number 字段允许为空
SET @columnname = 'item_number';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''项目编号'''),
  'SELECT 1'
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 修改 item_name 字段允许为空
SET @columnname = 'item_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''项目名称'''),
  'SELECT 1'
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 修改 item_content 字段允许为空
SET @columnname = 'item_content';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目内容'''),
  'SELECT 1'
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 修改 item_standard 字段允许为空
SET @columnname = 'item_standard';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目标准'''),
  'SELECT 1'
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 equipment_id 字段
SET @columnname = 'equipment_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联设备ID'' AFTER `item_standard`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 equipment_name 字段
SET @columnname = 'equipment_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''设备名称'' AFTER `equipment_id`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 point_id 字段
SET @columnname = 'point_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `equipment_name`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 point_name 字段
SET @columnname = 'point_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 point_code 字段
SET @columnname = 'point_code';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 standard_value 字段
SET @columnname = 'standard_value';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''标准值'' AFTER `point_code`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 upper_limit 字段
SET @columnname = 'upper_limit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''上限值'' AFTER `standard_value`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 lower_limit 字段
SET @columnname = 'lower_limit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''下限值'' AFTER `upper_limit`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 unit 字段
SET @columnname = 'unit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''单位'' AFTER `lower_limit`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 添加 check_method 字段
SET @columnname = 'check_method';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(200) DEFAULT NULL COMMENT ''检查方法'' AFTER `unit`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- 1.3 执行记录明细表（记录每个点位的执行结果）
CREATE TABLE IF NOT EXISTS `dm_inspection_record_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `record_id` BIGINT NOT NULL COMMENT '关联执行记录ID',
  `point_id` BIGINT DEFAULT NULL COMMENT '关联点位ID',
  `point_name` VARCHAR(100) DEFAULT NULL COMMENT '点位名称',
  `point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
  `standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
  `upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
  `lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
  `unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
  `result` VARCHAR(20) DEFAULT NULL COMMENT '执行结果（normal正常/abnormal异常）',
  `result_value` VARCHAR(100) DEFAULT NULL COMMENT '检测值',
  `problem_desc` TEXT DEFAULT NULL COMMENT '问题描述',
  `create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_record_id` (`record_id`),
  KEY `idx_point_id` (`point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='执行记录明细表';

-- =============================================
-- 二、扩展现有表结构
-- =============================================

-- 2.1 扩展巡检计划表，增加保养类型字段
-- MySQL 5.x 兼容写法，先检查字段是否存在
SET @dbname = DATABASE();
SET @tablename = 'dm_inspection_plan';

-- 添加 maintenance_type 字段
SET @columnname = 'maintenance_type';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''保养类型（daily日常保养/periodic定期保养/overhaul大修）'' AFTER `type`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加 equipment_id 字段（单设备关联）
SET @columnname = 'equipment_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联设备ID'' AFTER `executor_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加 equipment_name 字段（单设备名称）
SET @columnname = 'equipment_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''关联设备名称'' AFTER `equipment_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加 equipment_ids 字段（多设备ID，逗号分隔）
SET @columnname = 'equipment_ids';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''关联设备ID（多个用逗号分隔）'' AFTER `equipment_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加 equipment_names 字段（多设备名称，逗号分隔）
SET @columnname = 'equipment_names';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(1000) DEFAULT NULL COMMENT ''关联设备名称（多个用逗号分隔）'' AFTER `equipment_ids`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 2.2 扩展设备项目表，增加备件字段和多设备绑定字段
SET @tablename = 'dm_inspection_item';

SET @columnname = 'spare_parts';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''所需备件'' AFTER `equipment_point`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'equipment_ids';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''绑定设备ID（多个用逗号分隔）'' AFTER `equipment_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 2.3 扩展维修单明细表，增加点位相关字段
SET @tablename = 'dm_repair_order_entry';

SET @columnname = 'point_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `item_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'point_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'point_code';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'standard_value';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''标准值'' AFTER `point_code`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'upper_limit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''上限值'' AFTER `standard_value`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'lower_limit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''下限值'' AFTER `upper_limit`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'unit';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''单位'' AFTER `lower_limit`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'result_value';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''检测值'' AFTER `unit`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'result';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''检测结果（normal正常/abnormal异常）'' AFTER `result_value`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- =============================================
-- 三、字典数据配置
-- =============================================

-- 3.1 巡检计划类型增加"保养"选项
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
VALUES (2, '保养', 'maintenance', 'inspection_plan_type', NULL, 'warning', 'N', '0', 'admin', NOW(), '保养计划');

-- 3.2 新增保养类型字典类型
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
VALUES ('保养类型', 'maintenance_type', '0', 'admin', NOW(), '保养类型：日常保养/定期保养/大修');

-- 3.3 保养类型字典数据
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
VALUES (0, '日常保养', 'daily', 'maintenance_type', NULL, 'success', 'Y', '0', 'admin', NOW(), '日常保养');
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
VALUES (1, '定期保养', 'periodic', 'maintenance_type', NULL, 'primary', 'N', '0', 'admin', NOW(), '定期保养');
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
VALUES (2, '大修', 'overhaul', 'maintenance_type', NULL, 'danger', 'N', '0', 'admin', NOW(), '大修');

-- 3.4 清理重复的频率字典数据（v1.0.71已插入，这里只保留一份）
-- 删除重复的quarterly和yearly（保留dict_sort较小的）
DELETE d1 FROM sys_dict_data d1
INNER JOIN sys_dict_data d2 
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
WHERE d1.dict_type = 'inspection_plan_frequency' AND d1.dict_value IN ('quarterly', 'yearly');

-- 3.5 清理重复的保养类型字典数据
DELETE d1 FROM sys_dict_data d1
INNER JOIN sys_dict_data d2 
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
WHERE d1.dict_type = 'maintenance_type';

-- 3.6 清理重复的执行结果字典数据
DELETE d1 FROM sys_dict_data d1
INNER JOIN sys_dict_data d2 
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
WHERE d1.dict_type = 'inspection_result';

-- =============================================
-- 四、菜单结构调整
-- =============================================

-- 4.1 创建"点检管理"二级目录（设备管理菜单ID为2168）
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '点检管理', 2168, 1, 'spotcheck', NULL, NULL, 1, 0, 'M', '0', '0', '', 'checkbox', 'admin', NOW(), '点检管理目录'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '点检管理' AND parent_id = 2168);
SET @spotcheck_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '点检管理' AND parent_id = 2168 LIMIT 1);

-- 4.2 创建"巡检管理"二级目录
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '巡检管理', 2168, 2, 'inspection', NULL, NULL, 1, 0, 'M', '0', '0', '', 'eye-open', 'admin', NOW(), '巡检管理目录'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168);
SET @inspection_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168 LIMIT 1);

-- 4.3 创建"保养管理"二级目录
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '保养管理', 2168, 3, 'maintenance', NULL, NULL, 1, 0, 'M', '0', '0', '', 'tool', 'admin', NOW(), '保养管理目录'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '保养管理' AND parent_id = 2168);
SET @maintenance_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '保养管理' AND parent_id = 2168 LIMIT 1);

-- 4.4 创建"维修管理"二级目录
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '维修管理', 2168, 4, 'repair', NULL, NULL, 1, 0, 'M', '0', '0', '', 'build', 'admin', NOW(), '维修管理目录'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '维修管理' AND parent_id = 2168);
SET @repair_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '维修管理' AND parent_id = 2168 LIMIT 1);

-- 4.5 设备点位菜单（放在设备管理一级菜单下，作为通用基础数据）
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '设备点位', 2168, 10, 'inspectionPoint', 'mes/equipment/inspectionPoint/index', NULL, 1, 0, 'C', '0', '0', 'equipment:inspectionPoint:list', 'edit', 'admin', NOW(), '设备点位管理菜单'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'inspectionPoint' AND component = 'mes/equipment/inspectionPoint/index');

-- 4.6 移动点检相关菜单到点检管理下（只有当菜单不在点检管理下时才移动）
-- 点检计划移动到点检管理下（inspectionPlan是单选设备的点检计划页面）
UPDATE `sys_menu` SET `parent_id` = @spotcheck_menu_id, `order_num` = 1, `menu_name` = '点检计划' 
WHERE `path` = 'inspectionPlan' AND `component` LIKE 'mes/equipment/inspectionPlan%' AND @spotcheck_menu_id IS NOT NULL
AND `parent_id` != @spotcheck_menu_id;

-- 点检记录：新增菜单（使用inspectionPlanRecord组件，通过query参数区分）
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '点检记录', @spotcheck_menu_id, 2, 'spotcheckRecord', 'mes/equipment/inspectionPlanRecord/index', '{"type":"spotcheck"}', 1, 0, 'C', '0', '0', 'equipment:inspectionPlanRecord:list', '#', 'admin', NOW(), '点检记录菜单'
FROM DUAL WHERE @spotcheck_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'spotcheckRecord');

-- 删除旧的点检记录菜单（inspectionItemRecord）
DELETE FROM `sys_menu` WHERE `path` = 'inspectionItemRecord' AND `component` LIKE 'mes/equipment/inspectionItemRecord%';

-- 删除设备点检项目菜单（inspectionItem）
DELETE FROM `sys_menu` WHERE `path` = 'inspectionItem' AND `component` LIKE 'mes/equipment/inspectionItem%';

-- 4.7 巡检管理下的菜单
-- 巡检计划（inspectionStandard是多选设备的巡检计划页面）
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '巡检计划', @inspection_menu_id, 1, 'inspectionStandard', 'mes/equipment/inspectionStandard/index', NULL, 1, 0, 'C', '0', '0', 'equipment:inspectionStandard:list', '#', 'admin', NOW(), '巡检计划菜单'
FROM DUAL WHERE @inspection_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'inspectionStandard');

-- 巡检记录（只有当菜单不在巡检管理下时才移动，并设置query参数）
UPDATE `sys_menu` SET `parent_id` = @inspection_menu_id, `order_num` = 2, `menu_name` = '巡检记录', `query` = '{"type":"inspection"}' 
WHERE `path` = 'inspectionPlanRecord' AND `component` LIKE 'mes/equipment/inspectionPlanRecord%' AND @inspection_menu_id IS NOT NULL;

-- 4.8 保养管理下的菜单
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '保养计划', @maintenance_menu_id, 1, 'maintenancePlan', 'mes/equipment/maintenancePlan/index', NULL, 1, 0, 'C', '0', '0', 'equipment:maintenancePlan:list', '#', 'admin', NOW(), '保养计划菜单'
FROM DUAL WHERE @maintenance_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'maintenancePlan');

INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '保养记录', @maintenance_menu_id, 2, 'maintenanceRecord', 'mes/equipment/maintenanceRecord/index', NULL, 1, 0, 'C', '0', '0', 'equipment:maintenanceRecord:list', '#', 'admin', NOW(), '保养记录菜单'
FROM DUAL WHERE @maintenance_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'maintenanceRecord');

-- 4.9 维修管理下的菜单
-- 先检查设备维修单是否已经在维修管理目录下，避免重复移动
UPDATE `sys_menu` SET `parent_id` = @repair_menu_id, `order_num` = 1 
WHERE `path` = 'faultTree' AND `component` LIKE 'mes/equipment/faultTree%' AND @repair_menu_id IS NOT NULL
AND `parent_id` != @repair_menu_id;

-- 只有当设备维修单不在维修管理目录下时才移动
UPDATE `sys_menu` SET `parent_id` = @repair_menu_id, `order_num` = 2 
WHERE `path` = 'repairOrder' AND `component` LIKE 'mes/equipment/repairOrder%' AND @repair_menu_id IS NOT NULL
AND `parent_id` != @repair_menu_id;

-- =============================================
-- 五、删除冗余字段的SQL（如需清理旧数据）
-- =============================================

-- 以下字段在简化流程后不再需要，可选择性删除：
-- ALTER TABLE `dm_inspection_item` DROP COLUMN `equipment_point`;  -- 旧的点位字段（已改用子表）
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_id`;     -- 旧的项目关联（已改用点位关联）
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_number`;
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_name`;
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_content`;
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_standard`;

-- =============================================
-- 六、回退SQL（如需回退请执行以下语句）
-- =============================================

/*
-- 删除新增的菜单
DELETE FROM `sys_menu` WHERE `path` IN ('inspectionPoint', 'inspectionStandard', 'maintenanceItem', 'maintenancePlan', 'maintenanceRecord');
DELETE FROM `sys_menu` WHERE `path` IN ('spotcheck', 'inspection', 'maintenance', 'repair') AND `parent_id` = 2168 AND `menu_type` = 'M';

-- 删除字典数据
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_type';
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'inspection_plan_type' AND `dict_value` = 'maintenance';
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'maintenance_type';

-- 删除扩展字段
ALTER TABLE `dm_inspection_plan` DROP COLUMN `maintenance_type`;
ALTER TABLE `dm_inspection_item` DROP COLUMN `spare_parts`;
ALTER TABLE `dm_inspection_item` DROP COLUMN `equipment_ids`;

-- 删除维修单扩展字段
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_id`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_name`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_code`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `standard_value`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `upper_limit`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `lower_limit`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `unit`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `result_value`;
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `result`;

-- 删除新增表
DROP TABLE IF EXISTS `dm_inspection_record_item`;
DROP TABLE IF EXISTS `dm_inspection_plan_item`;
DROP TABLE IF EXISTS `dm_inspection_item_point`;
*/


-- =============================================
-- 七、扩展执行记录表，增加点位字段
-- =============================================

-- 7.1 给dm_inspection_plan_record表添加point_id字段
SET @dbname = DATABASE();
SET @tablename = 'dm_inspection_plan_record';

SET @columnname = 'point_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `equipment_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 7.2 给dm_inspection_plan_record表添加point_name字段
SET @columnname = 'point_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;


-- =============================================
-- 八、修复dm_inspection_record_item表item_id字段
-- =============================================

-- 8.1 添加item_id字段（如果不存在则添加，如果存在则修改为允许空）
SET @dbname = DATABASE();
SET @tablename = 'dm_inspection_record_item';
SET @columnname = 'item_id';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联标准项目ID（可选）'''),
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联标准项目ID（可选）'' AFTER `record_id`')
));
PREPARE alterStatement FROM @preparedStatement;
EXECUTE alterStatement;
DEALLOCATE PREPARE alterStatement;

-- =============================================
-- 九、维修单增加富文本字段
-- =============================================

-- 9.1 给dm_repair_order表添加故障详情富文本字段
SET @tablename = 'dm_repair_order';
SET @columnname = 'fault_detail';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' TEXT DEFAULT NULL COMMENT ''故障详情（富文本）''')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 9.2 给dm_repair_order表添加更换备品备件富文本字段
SET @columnname = 'spare_parts_detail';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' TEXT DEFAULT NULL COMMENT ''更换备品备件（富文本）''')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- =============================================
-- 十、维修单明细权限配置
-- =============================================

-- 10.1 添加维修单明细查看权限按钮
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
SELECT '维修单明细', (SELECT menu_id FROM sys_menu WHERE path = 'repairOrder' AND component LIKE 'mes/equipment/repairOrder%' LIMIT 1), 5, '', NULL, NULL, 1, 0, 'F', '0', '0', 'equipment:repairOrder:detail', '#', 'admin', NOW(), '维修单明细查看权限'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'equipment:repairOrder:detail');


-- =============================================
-- 十一、修复dm_inspection_record_item表缺失字段
-- =============================================

-- 11.1 添加item_content字段（检查方法）
SET @dbname = DATABASE();
SET @tablename = 'dm_inspection_record_item';
SET @columnname = 'item_content';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''检查方法/项目内容'' AFTER `unit`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 11.2 添加item_number字段
SET @columnname = 'item_number';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''项目编号'' AFTER `point_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 11.3 添加item_name字段
SET @columnname = 'item_name';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''项目名称'' AFTER `item_number`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 11.4 添加item_standard字段
SET @columnname = 'item_standard';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目标准'' AFTER `item_content`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 11.5 添加spare_parts_used字段（保养用）
SET @columnname = 'spare_parts_used';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''使用的备件'' AFTER `problem_desc`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 11.6 添加point_code字段
SET @columnname = 'point_code';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;


-- =============================================
-- 十二、巡检记录菜单路由参数配置（点检记录已在4.6节处理）
-- =============================================

-- 12.1 获取巡检管理目录ID
SET @inspection_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168 LIMIT 1);

-- 12.2 更新巡检记录菜单（带type=inspection参数）- 备用，4.7节已处理
-- UPDATE `sys_menu` SET `query` = '{"type":"inspection"}' 
-- WHERE `path` = 'inspectionPlanRecord' AND `component` LIKE 'mes/equipment/inspectionPlanRecord%';


-- =============================================
-- 十三、维修单增加故障描述字段
-- =============================================

-- 13.1 给dm_repair_order表添加故障描述字段
SET @dbname = DATABASE();
SET @tablename = 'dm_repair_order';
SET @columnname = 'fault_desc';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''故障描述'' AFTER `spare_parts_detail`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;