water/server/plugin/saiadmin/db/20260828_add_device_operations.sql

-- 我的设备：设备归属、供电/断电、解绑及二维码操作权限和审计表。
-- 可重复执行；运行前请备份生产数据库。

CREATE TABLE IF NOT EXISTS `sa_device_command_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志编号',
    `device_id` BIGINT UNSIGNED NOT NULL COMMENT '设备编号',
    `device_sn` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '设备 SN 快照',
    `command` VARCHAR(40) NOT NULL COMMENT '命令类型',
    `action` VARCHAR(20) NOT NULL COMMENT '操作：on/off',
    `request_payload` TEXT NULL COMMENT '下发参数',
    `response_payload` TEXT NULL COMMENT '指令服务响应',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '1成功、2失败',
    `operator_id` BIGINT UNSIGNED NOT NULL COMMENT '操作人',
    `create_time` DATETIME NOT NULL COMMENT '操作时间',
    PRIMARY KEY (`id`),
    KEY `idx_device_command_time` (`device_id`, `create_time`),
    KEY `idx_operator_time` (`operator_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备控制指令审计日志';

INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT device_menu.`id`, permission_data.`name`, permission_data.`code`, permission_data.`slug`, 3,
       NULL, NULL, permission_data.`method`, NULL, permission_data.`sort`, NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, permission_data.`remark`, 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` device_menu
INNER JOIN (
    SELECT '设备管理详情' AS `name`, 'DeviceRead' AS `code`, 'core:device:read' AS `slug`, 'GET' AS `method`, 45 AS `sort`, '查看设备归属与当前商户' AS `remark`
    UNION ALL SELECT '设备供电断电', 'DevicePower', 'core:device:power', 'POST', 44, '向已配置设备指令服务下发开关命令'
    UNION ALL SELECT '设备解绑', 'DeviceUnbind', 'core:device:unbind', 'POST', 43, '解除设备与商户的当前绑定'
) permission_data
LEFT JOIN `sa_system_menu` existing_menu ON existing_menu.`slug` = permission_data.`slug` AND existing_menu.`delete_time` IS NULL
WHERE device_menu.`code` = 'MyDevice' AND device_menu.`delete_time` IS NULL AND existing_menu.`id` IS NULL;

INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT source_role.`role_id`, permission_menu.`id`
FROM `sa_system_role_menu` source_role
INNER JOIN `sa_system_menu` source_menu ON source_menu.`id` = source_role.`menu_id` AND source_menu.`slug` = 'core:device:index' AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` permission_menu ON permission_menu.`slug` IN ('core:device:read', 'core:device:power', 'core:device:unbind') AND permission_menu.`delete_time` IS NULL
LEFT JOIN `sa_system_role_menu` existing_role ON existing_role.`role_id` = source_role.`role_id` AND existing_role.`menu_id` = permission_menu.`id`
WHERE existing_role.`id` IS NULL;