-- ========================================
-- 用户管理和角色权限管理 - 数据库迁移脚本
-- ========================================
USE `iot_platform`;

-- 为sys_role表添加sort字段(如果不存在)
SET @exist_sort = (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'iot_platform'
    AND TABLE_NAME = 'sys_role'
    AND COLUMN_NAME = 'sort'
);
SET @sql_add_sort = IF(@exist_sort = 0,
  'ALTER TABLE `sys_role` ADD COLUMN `sort` INT DEFAULT 0 COMMENT ''排序'' AFTER `status`;',
  'SELECT 1;'
);
PREPARE stmt FROM @sql_add_sort;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 插入更多默认角色(如果不存在)
INSERT IGNORE INTO `sys_role` (`role_name`, `role_code`, `description`, `status`, `sort`)
VALUES
  ('运维人员', 'operator', '运维操作权限', 1, 2),
  ('只读用户', 'viewer', '只读权限，仅查看数据', 1, 3);

-- 更新现有角色的排序值
UPDATE `sys_role` SET `sort` = 1 WHERE `role_code` = 'admin';
UPDATE `sys_role` SET `sort` = 4 WHERE `role_code` = 'user';

SELECT 'Migration completed successfully.' AS message;