water/server/plugin/saiadmin/db/20260908_add_device_operation_service.sql

-- 设备运营参数；不填成本或有效分成时保持 NULL，不生成虚假经营数据。可重复执行。
CREATE TABLE IF NOT EXISTS `sa_device_operation_profile` (
  `device_id` BIGINT UNSIGNED NOT NULL,
  `manage_agent_id` BIGINT UNSIGNED NOT NULL,
  `merchant_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cost_price` DECIMAL(10,2) UNSIGNED NULL COMMENT '设备实际投入成本（元）',
  `merchant_rate` DECIMAL(5,2) UNSIGNED NULL COMMENT '商户占总交易额的有效比例，非相对上级比例',
  `manager_rate` DECIMAL(5,2) UNSIGNED NULL COMMENT '设备管理人扣除下级后的有效比例',
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `create_time` DATETIME NOT NULL,
  `update_time` DATETIME NOT NULL,
  PRIMARY KEY (`device_id`)
) 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 page_menu.`id`,permissions.`name`,permissions.`code`,permissions.`slug`,3,
 NULL,NULL,permissions.`method`,NULL,permissions.`sort`,NULL,2,2,2,2,2,0,NULL,1,
 permissions.`remark`,1,1,NOW(),NOW(),NULL
FROM `sa_system_menu` page_menu
CROSS JOIN (
 SELECT '查询设备运营' `name`, 'DeviceOperationIndex' `code`, 'core:operation:device:index' `slug`,
 'GET' `method`,100 `sort`,'按本人或递归下级管理设备查询经营统计' `remark`
 UNION ALL SELECT '设置设备运营参数','DeviceOperationUpdate','core:operation:device:update',
 'PUT',90,'维护设备真实成本和商户/管理人有效分成'
) permissions
WHERE page_menu.`code`='DeviceOperations' AND page_menu.`delete_time` IS NULL
 AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` e WHERE e.`slug`=permissions.`slug` AND e.`delete_time` IS NULL);

-- 页面访问者继承读取权限；参数修改需单独授权，不能将只读角色升级为可写。
INSERT INTO `sa_system_role_menu` (`role_id`,`menu_id`)
SELECT DISTINCT source.`role_id`,target.`id`
FROM `sa_system_role_menu` source
JOIN `sa_system_menu` page_menu ON page_menu.`id`=source.`menu_id`
 AND page_menu.`code`='DeviceOperations' AND page_menu.`delete_time` IS NULL
JOIN `sa_system_menu` target ON target.`parent_id`=page_menu.`id`
 AND target.`slug`='core:operation:device:index' AND target.`delete_time` IS NULL
LEFT JOIN `sa_system_role_menu` existing ON existing.`role_id`=source.`role_id` AND existing.`menu_id`=target.`id`
WHERE existing.`id` IS NULL;