water/server/plugin/saiadmin/db/20260822_add_merchant_service.sql

-- ============================================================
-- 我的商户服务
-- 日期: 2026-08-22
-- 说明: 从旧 api 项目迁移直属商户、分润和聚合统计数据模型
--       本脚本可重复执行，不写入演示业务数据。
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_merchant` (
    `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商户编号',
    `agent_id`                  BIGINT UNSIGNED NOT NULL COMMENT '直属代理（对应旧 admin_user_dependen.pid）',
    `profit_user_id`            BIGINT UNSIGNED NULL COMMENT '分润人系统用户编号',
    `name`                      VARCHAR(100) NOT NULL COMMENT '商户名称',
    `store_phone`               VARCHAR(32) NOT NULL DEFAULT '' COMMENT '商户电话',
    `storefront_image`          VARCHAR(500) NOT NULL DEFAULT '' COMMENT '门头照',
    `industry`                  VARCHAR(100) NOT NULL DEFAULT '' COMMENT '行业分类',
    `region`                    VARCHAR(150) NOT NULL DEFAULT '' COMMENT '城市区域',
    `address`                   VARCHAR(255) NOT NULL DEFAULT '' COMMENT '详细地址',
    `description`               TEXT NULL COMMENT '商户介绍',
    `products`                  TEXT NOT NULL COMMENT '运营产品 JSON',
    `profit_method`             VARCHAR(30) NOT NULL DEFAULT '比例分成' COMMENT '分成方式',
    `profit_rate`               DECIMAL(5,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '相对分润比例',
    `contact`                   VARCHAR(64) NOT NULL DEFAULT '' COMMENT '联系人',
    `phone`                     VARCHAR(32) NOT NULL COMMENT '商户登录手机号',
    `password`                  VARCHAR(255) NOT NULL COMMENT '商户登录密码哈希',
    `permissions`               TEXT NOT NULL COMMENT '商户权限 JSON',
    `distributed`               TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否铺货：0否，1是',
    `status`                    TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1启用，2停用',
    `agent_type`                VARCHAR(32) NOT NULL DEFAULT 'agent' COMMENT '直属代理类型',
    `total_amount`              DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '总交易额快照',
    `total_income`              DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '总收益快照',
    `withdrawable_amount`       DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '可提现金额快照',
    `order_count`               INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单量快照',
    `device_count`              INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '设备数快照',
    `yesterday_usage_rate`      DECIMAL(7,2) NOT NULL DEFAULT 0.00 COMMENT '昨日使用率',
    `three_day_usage_rate`      DECIMAL(7,2) NOT NULL DEFAULT 0.00 COMMENT '近三天使用率',
    `seven_day_usage_rate`      DECIMAL(7,2) NOT NULL DEFAULT 0.00 COMMENT '近七天使用率',
    `yesterday_average_order`   DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '昨日客单价',
    `three_day_average_order`   DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '近三天客单价',
    `seven_day_average_order`   DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '近七天客单价',
    `created_by`                BIGINT UNSIGNED NULL COMMENT '创建者',
    `updated_by`                BIGINT UNSIGNED NULL COMMENT '更新者',
    `create_time`               DATETIME NULL COMMENT '创建时间',
    `update_time`               DATETIME NULL COMMENT '更新时间',
    `delete_time`               DATETIME NULL COMMENT '软删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_merchant_phone_active` (`phone`, `delete_time`),
    KEY `idx_merchant_agent_status` (`agent_id`, `status`, `delete_time`),
    KEY `idx_merchant_profit_user` (`profit_user_id`),
    KEY `idx_merchant_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='商户信息';

UPDATE `sa_system_menu`
SET `remark` = CASE `code`
        WHEN 'MyMerchant' THEN '当前代理直属商户列表'
        WHEN 'SubordinateMerchant' THEN '下级代理直属商户列表'
        WHEN 'AddMerchant' THEN '创建直属商户'
    END,
    `update_time` = NOW()
WHERE `code` IN ('MyMerchant', 'SubordinateMerchant', 'AddMerchant')
  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 parent_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` parent_menu
INNER JOIN (
    SELECT '商户列表' AS `name`, 'MerchantIndex' AS `code`, 'core:merchant:index' AS `slug`, 'GET' AS `method`, 100 AS `sort`, '按登录代理隔离商户数据' AS `remark`
    UNION ALL SELECT '商户详情', 'MerchantRead', 'core:merchant:read', 'GET', 90, '读取有权访问的商户'
    UNION ALL SELECT '导出商户', 'MerchantExport', 'core:merchant:export', 'POST', 85, '按当前筛选条件导出有权访问的商户'
    UNION ALL SELECT '新增商户', 'MerchantSave', 'core:merchant:save', 'POST', 80, '创建直属商户'
    UNION ALL SELECT '编辑商户', 'MerchantUpdate', 'core:merchant:update', 'PUT', 70, '编辑有权访问的商户'
    UNION ALL SELECT '删除商户', 'MerchantDestroy', 'core:merchant:destroy', 'DELETE', 60, '软删除有权访问的商户'
    UNION ALL SELECT '重置商户密码', 'MerchantPassword', 'core:merchant:password', 'POST', 50, '重置商户登录密码'
) permission_data
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`slug` = permission_data.`slug`
      AND existing_menu.`delete_time` IS NULL
WHERE parent_menu.`code` = 'Merchant'
  AND parent_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.`code` IN ('MyMerchant', 'SubordinateMerchant', 'AddMerchant')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` merchant_menu
        ON merchant_menu.`code` = 'Merchant'
       AND merchant_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` permission_menu
        ON permission_menu.`parent_id` = merchant_menu.`id`
       AND permission_menu.`type` = 3
       AND permission_menu.`delete_time` IS NULL
       AND (
           permission_menu.`slug` IN ('core:merchant:index', 'core:merchant:read')
           OR (source_menu.`code` = 'AddMerchant' AND permission_menu.`slug` = 'core:merchant:save')
           OR (source_menu.`code` IN ('MyMerchant', 'SubordinateMerchant') AND permission_menu.`slug` IN ('core:merchant:export', 'core:merchant:update', 'core:merchant:destroy', 'core:merchant:password'))
       )
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;