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

-- ============================================================
-- 商户每日统计与数据统计页面
-- 日期: 2026-08-22
-- 来源: 旧项目 xs_statistics_shop_oneday / xs_statistics_profit_oneday
-- 说明: 金额以分存储；脚本可重复执行。
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `sa_merchant_daily_statistics` (
    `id`                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '统计编号',
    `merchant_id`              BIGINT UNSIGNED NOT NULL COMMENT '商户编号',
    `stat_date`                DATE NOT NULL COMMENT '统计日期',
    `turnover_cents`           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '交易额（分）',
    `income_cents`             BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '收益额（分）',
    `order_count`              INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已支付订单量',
    `wechat_turnover_cents`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '微信交易额（分）',
    `alipay_turnover_cents`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付宝交易额（分）',
    `balance_turnover_cents`   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '余额交易额（分）',
    `wechat_order_count`       INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '微信订单量',
    `alipay_order_count`       INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付宝订单量',
    `balance_order_count`      INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '余额订单量',
    `device_usage_rate`        DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '设备使用率（%）',
    `device_count`             INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '当日设备数',
    `create_time`              DATETIME NULL COMMENT '创建时间',
    `update_time`              DATETIME NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_merchant_stat_date` (`merchant_id`, `stat_date`),
    KEY `idx_statistics_date` (`stat_date`)
) 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 parent_menu.`id`, '数据统计', 'MerchantStatistics', NULL, 2,
       'statistics', '/merchant/statistics', NULL, NULL, 70,
       NULL, 2, 2, 1, 2, 2, 0, NULL, 1,
       '商户每日交易、收益、订单、使用率及支付渠道统计', 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` parent_menu
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`code` = 'MerchantStatistics'
      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_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`, 'MerchantStatisticsRead' AS `code`,
           'core:merchant:statistics' AS `slug`, 'GET' AS `method`, 43 AS `sort`,
           '读取有权访问商户的每日统计' AS `remark`
    UNION ALL
    SELECT '导出商户数据统计', 'MerchantStatisticsExport',
           'core:merchant:statistics:export', 'POST', 42,
           '导出有权访问商户的每日统计'
) 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`, 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 ('MyMerchant', 'SubordinateMerchant')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` target_menu
        ON (
            target_menu.`code` = 'MerchantStatistics'
            OR target_menu.`slug` IN (
                'core:merchant:statistics',
                'core:merchant:statistics:export'
            )
        )
       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;