water/server/plugin/saiadmin/db/20260827_add_agent_profile.sql

-- 代理运营档案、修改信息页面及接口权限，可重复执行。

CREATE TABLE IF NOT EXISTS `sa_agent_profile` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '档案编号',
    `agent_id` BIGINT UNSIGNED NOT NULL COMMENT 'sa_system_user.id',
    `business_mode` VARCHAR(20) NOT NULL DEFAULT 'agent' COMMENT 'agent代理模式、employee员工模式',
    `product_codes` JSON NOT NULL COMMENT '运营产品编码',
    `profit_rates` JSON NOT NULL COMMENT '各产品分润比例',
    `permissions` JSON NOT NULL COMMENT '代理业务权限',
    `province` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '省份',
    `city` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '城市',
    `district` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '区县',
    `company_address` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '公司详细地址',
    `created_by` BIGINT UNSIGNED NULL COMMENT '创建者',
    `updated_by` BIGINT UNSIGNED NULL COMMENT '更新者',
    `create_time` DATETIME NOT NULL COMMENT '创建时间',
    `update_time` DATETIME NOT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_agent_profile_agent` (`agent_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 agent_menu.`id`, '修改代理信息', 'AgentEdit', NULL, 2,
       'edit-agent', '/agent/edit-agent', NULL, NULL, 70,
       NULL, 2, 2, 1, 2, 2, 0, NULL, 1,
       '修改下级代理运营资料', 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` agent_menu
WHERE agent_menu.`code` = 'Agent' AND agent_menu.`delete_time` IS NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `code` = 'AgentEdit' AND `delete_time` IS NULL);

UPDATE `sa_system_menu`
SET `name` = '修改代理信息', `path` = 'edit-agent', `component` = '/agent/edit-agent',
    `is_hidden` = 1, `remark` = '修改下级代理运营资料', `update_time` = NOW()
WHERE `code` = 'AgentEdit' 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`, 'AgentRead' AS `code`, 'core:agent:read' AS `slug`, 'GET' AS `method`, 90 AS `sort`, '读取有权管理的代理资料' AS `remark`
    UNION ALL SELECT '修改代理信息', 'AgentUpdate', 'core:agent:update', 'PUT', 89, '保存有权管理的代理资料'
) 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;

UPDATE `sa_system_menu`
SET `name` = '代理详情', `method` = 'GET', `remark` = '读取有权管理的代理资料', `update_time` = NOW()
WHERE `slug` = 'core:agent:read' AND `delete_time` IS NULL;

UPDATE `sa_system_menu`
SET `name` = '修改代理信息', `method` = 'PUT', `remark` = '保存有权管理的代理资料', `update_time` = NOW()
WHERE `slug` = 'core:agent:update' AND `delete_time` 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', 'SubordinateAgent')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` target_menu
        ON (target_menu.`code` = 'AgentEdit' OR target_menu.`slug` IN ('core:agent:read', 'core:agent:update'))
       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;