water/server/plugin/saiadmin/db/20260826_add_agent_device_assignment.sql

-- 代理设备分配流水、去分配页面及接口权限，可重复执行。
-- 旧表 device_manager_record 映射到 sa_device_manager_record。

CREATE TABLE IF NOT EXISTS `sa_device_manager_record` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '流水编号',
    `device_id` BIGINT UNSIGNED NOT NULL COMMENT '设备编号',
    `device_sn` VARCHAR(60) NOT NULL COMMENT '设备 SN 快照',
    `goods_type` SMALLINT UNSIGNED NOT NULL COMMENT '设备类型',
    `last_agent_id` BIGINT UNSIGNED NOT NULL COMMENT '上一管理代理',
    `now_agent_id` BIGINT UNSIGNED NOT NULL COMMENT '当前管理代理',
    `action_type` TINYINT UNSIGNED NOT NULL COMMENT '1分配、2回收',
    `reason` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '操作原因',
    `operator_id` BIGINT UNSIGNED NOT NULL COMMENT '操作人',
    `action_time` DATETIME NOT NULL COMMENT '操作时间',
    `created_by` BIGINT UNSIGNED NULL COMMENT '创建者',
    `create_time` DATETIME NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_manager_record_device_time` (`device_id`, `action_time`),
    KEY `idx_manager_record_agents_time` (`last_agent_id`, `now_agent_id`, `action_time`),
    KEY `idx_manager_record_sn` (`device_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备管理权分配回收流水';

UPDATE `sa_system_menu`
SET `slug` = 'core:agent:index', `method` = 'GET', `update_time` = NOW()
WHERE `code` = 'MyAgent' AND `delete_time` IS NULL;

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`, '去分配', 'AssignDevice', NULL, 2,
       'assign', '/device/assign', NULL, 'ri:share-forward-line', 74,
       NULL, 2, 2, 1, 2, 2, 0, NULL, 1,
       '选择下级代理并分配设备管理权', 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` device_menu
WHERE device_menu.`code` = 'Device' AND device_menu.`delete_time` IS NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `code` = 'AssignDevice' AND `delete_time` IS NULL);

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 agent_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` agent_menu
INNER JOIN (
    SELECT '导出代理' AS `name`, 'AgentExport' AS `code`, 'core:agent:export' AS `slug`, 'POST' AS `method`, 95 AS `sort`, '导出有权查看的代理' AS `remark`
    UNION ALL SELECT '可分配设备', 'AgentDeviceCandidates', 'core:agent:device:candidates', 'GET', 94, '查询当前代理未铺货库存设备'
    UNION ALL SELECT '分配设备', 'AgentDeviceAssign', 'core:agent:device:assign', 'POST', 93, '将设备管理权分配给下级代理'
    UNION ALL SELECT '回收设备', 'AgentDeviceRecover', 'core:agent:device:recover', 'POST', 92, '从下级代理回收未铺货设备'
    UNION ALL SELECT '设备分配记录', 'AgentDeviceRecords', 'core:agent:device:records', 'GET', 91, '查看与下级代理之间的分配回收流水'
) permission_data
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`slug` = permission_data.`slug` AND existing_menu.`delete_time` IS NULL
WHERE agent_menu.`code` = 'Agent' AND agent_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`, target_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.`code` IN ('MyAgent', 'MyDevice', 'SubordinateDevice')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` target_menu
        ON (
            target_menu.`code` = 'AssignDevice'
            OR target_menu.`slug` IN (
                'core:agent:index', 'core:agent:export', 'core:agent:device:candidates',
                'core:agent:device:assign', 'core:agent:device:recover', 'core:agent:device:records'
            )
        )
       AND target_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` = target_menu.`id`
WHERE existing_role.`id` IS NULL;