-- =====================================================
-- 车辆异常运行监控告警功能 - 数据库升级脚本
-- 用于更新已存在的表结构
-- =====================================================

-- 检查并修改 tb_vehicle_alert_config 表结构
-- 如果表已存在但字段不正确，需要先删除旧字段，再添加新字段

-- 1. 删除旧的唯一索引
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `uk_type_target`;

-- 2. 检查是否存在 target_id 字段，如果存在则删除
SET @exist_target_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND COLUMN_NAME = 'target_id');

SET @sql_drop_target_id = IF(@exist_target_id > 0, 
    'ALTER TABLE `tb_vehicle_alert_config` DROP COLUMN `target_id`', 
    'SELECT ''target_id column does not exist''');
PREPARE stmt FROM @sql_drop_target_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 添加 dept_id 字段（如果不存在）
SET @exist_dept_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND COLUMN_NAME = 'dept_id');

SET @sql_add_dept_id = IF(@exist_dept_id = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD COLUMN `dept_id` bigint(20) DEFAULT NULL COMMENT ''部门ID（部门配置时使用）'' AFTER `config_type`', 
    'SELECT ''dept_id column already exists''');
PREPARE stmt FROM @sql_add_dept_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. 添加 vehicle_id 字段（如果不存在）
SET @exist_vehicle_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND COLUMN_NAME = 'vehicle_id');

SET @sql_add_vehicle_id = IF(@exist_vehicle_id = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD COLUMN `vehicle_id` bigint(20) DEFAULT NULL COMMENT ''车辆ID（车辆配置时使用）'' AFTER `dept_id`', 
    'SELECT ''vehicle_id column already exists''');
PREPARE stmt FROM @sql_add_vehicle_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. 检查是否存在 notify_roles 字段，如果存在则删除
SET @exist_notify_roles := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND COLUMN_NAME = 'notify_roles');

SET @sql_drop_notify_roles = IF(@exist_notify_roles > 0, 
    'ALTER TABLE `tb_vehicle_alert_config` DROP COLUMN `notify_roles`', 
    'SELECT ''notify_roles column does not exist''');
PREPARE stmt FROM @sql_drop_notify_roles;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. 修改 enabled 字段为 status（如果存在 enabled）
SET @exist_enabled := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND COLUMN_NAME = 'enabled');

SET @sql_change_enabled = IF(@exist_enabled > 0, 
    'ALTER TABLE `tb_vehicle_alert_config` CHANGE COLUMN `enabled` `status` char(1) DEFAULT ''0'' COMMENT ''状态（0-启用 1-停用）''', 
    'SELECT ''enabled column does not exist, may already be status''');
PREPARE stmt FROM @sql_change_enabled;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. 删除旧索引
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `idx_target_id`;
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `idx_enabled`;

-- 8. 创建新的唯一索引
-- 先检查索引是否存在
SET @exist_uk_vehicle := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND INDEX_NAME = 'uk_vehicle_config');

SET @sql_add_uk_vehicle = IF(@exist_uk_vehicle = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD UNIQUE KEY `uk_vehicle_config` (`config_type`, `vehicle_id`)', 
    'SELECT ''uk_vehicle_config index already exists''');
PREPARE stmt FROM @sql_add_uk_vehicle;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist_uk_dept := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND INDEX_NAME = 'uk_dept_config');

SET @sql_add_uk_dept = IF(@exist_uk_dept = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD UNIQUE KEY `uk_dept_config` (`config_type`, `dept_id`)', 
    'SELECT ''uk_dept_config index already exists''');
PREPARE stmt FROM @sql_add_uk_dept;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. 创建新的普通索引
SET @exist_idx_dept := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND INDEX_NAME = 'idx_dept_id');

SET @sql_add_idx_dept = IF(@exist_idx_dept = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_dept_id` (`dept_id`)', 
    'SELECT ''idx_dept_id index already exists''');
PREPARE stmt FROM @sql_add_idx_dept;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist_idx_vehicle := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND INDEX_NAME = 'idx_vehicle_id');

SET @sql_add_idx_vehicle = IF(@exist_idx_vehicle = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_vehicle_id` (`vehicle_id`)', 
    'SELECT ''idx_vehicle_id index already exists''');
PREPARE stmt FROM @sql_add_idx_vehicle;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist_idx_status := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'tb_vehicle_alert_config' 
    AND INDEX_NAME = 'idx_status');

SET @sql_add_idx_status = IF(@exist_idx_status = 0, 
    'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_status` (`status`)', 
    'SELECT ''idx_status index already exists''');
PREPARE stmt FROM @sql_add_idx_status;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 10. 更新已有的全局配置记录
UPDATE `tb_vehicle_alert_config` 
SET `dept_id` = NULL, `vehicle_id` = NULL 
WHERE `config_type` = 'GLOBAL';

-- 升级完成提示
SELECT '数据库升级完成！tb_vehicle_alert_config 表结构已更新' AS 'Status';