-- ============================================================
-- 设备保养模块数据库修复脚本
-- 修复保养部位和保养项目字段缺失问题
-- 创建时间: 2026-05-18
-- ============================================================

-- ============================================================
-- 问题说明：
-- 1. maintenance_task 表缺少 maintenance_location (保养部位) 字段
-- 2. device_maintenance 表缺少 maintenance_items (保养项目) 字段
-- ============================================================


-- ----------------------------
-- 修复 1: 在 maintenance_task 表添加 maintenance_location (保养部位) 字段
-- ----------------------------
-- 先检查字段是否存在，不存在则添加
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'maintenance_task'
                   AND COLUMN_NAME = 'maintenance_location');
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `maintenance_task` ADD COLUMN `maintenance_location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT ''保养部位''',
    'SELECT ''maintenance_location column already exists'' AS result');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ----------------------------
-- 修复 2: 在 device_maintenance 表添加 maintenance_items (保养项目) 字段
-- ----------------------------
-- 先检查字段是否存在，不存在则添加
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'device_maintenance'
                   AND COLUMN_NAME = 'maintenance_items');
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `device_maintenance` ADD COLUMN `maintenance_items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT ''保养项目''',
    'SELECT ''maintenance_items column already exists'' AS result');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ============================================================
-- 验证执行：查询表结构确认字段已添加
-- ============================================================
-- SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME IN ('maintenance_task', 'device_maintenance')
-- AND COLUMN_NAME IN ('maintenance_location', 'maintenance_items');