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

-- 代理合同附件、合同管理隐藏页及接口权限，可重复执行。

CREATE TABLE IF NOT EXISTS `sa_agent_contract` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '合同资料编号',
    `agent_id` BIGINT UNSIGNED NOT NULL COMMENT 'sa_system_user.id',
    `contract_urls` JSON NOT NULL COMMENT '合同照片地址',
    `identity_urls` JSON NOT NULL COMMENT '身份信息照片地址',
    `extra_urls` JSON NOT NULL 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_contract_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`, '合同管理', 'AgentContract', NULL, 2,
       'contract', '/agent/contract', NULL, NULL, 69,
       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` = 'AgentContract' AND `delete_time` IS NULL);

UPDATE `sa_system_menu`
SET `name` = '合同管理', `path` = 'contract', `component` = '/agent/contract',
    `is_hidden` = 1, `remark` = '维护下级代理合同及身份附件', `update_time` = NOW()
WHERE `code` = 'AgentContract' 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`, 'AgentContractRead' AS `code`, 'core:agent:contract:read' AS `slug`, 'GET' AS `method`, 88 AS `sort`, '读取有权管理的代理合同资料' AS `remark`
    UNION ALL SELECT '保存代理合同', 'AgentContractUpdate', 'core:agent:contract:update', 'PUT', 87, '保存有权管理的代理合同资料'
) 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:contract:read' AND `delete_time` IS NULL;

UPDATE `sa_system_menu`
SET `name` = '保存代理合同', `method` = 'PUT', `remark` = '保存有权管理的代理合同资料', `update_time` = NOW()
WHERE `slug` = 'core:agent:contract: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` = 'AgentContract' OR target_menu.`slug` IN ('core:agent:contract:read', 'core:agent:contract: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;