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

-- ============================================================
-- 商户分页演示数据（仅供本地测试）
-- 日期: 2026-08-22
-- 生成 57 条数据，测试手机号: 19100000001 - 19100000057
-- 登录测试密码统一为: 123456
-- 本脚本可重复执行，不会重复插入未删除的相同测试手机号。
-- ============================================================

INSERT INTO `sa_merchant` (
    `agent_id`, `profit_user_id`, `name`, `store_phone`, `storefront_image`,
    `industry`, `region`, `address`, `description`, `products`,
    `profit_method`, `profit_rate`, `contact`, `phone`, `password`, `permissions`,
    `distributed`, `status`, `agent_type`, `total_amount`, `total_income`,
    `withdrawable_amount`, `order_count`, `device_count`,
    `yesterday_usage_rate`, `three_day_usage_rate`, `seven_day_usage_rate`,
    `yesterday_average_order`, `three_day_average_order`, `seven_day_average_order`,
    `created_by`, `updated_by`, `create_time`, `update_time`
)
SELECT
    CASE MOD(numbers.seq, 7)
        WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 4
        WHEN 4 THEN 5 WHEN 5 THEN 10 ELSE 100
    END AS `agent_id`,
    CASE MOD(numbers.seq, 6)
        WHEN 0 THEN 1 WHEN 1 THEN 3 WHEN 2 THEN 4
        WHEN 3 THEN 5 WHEN 4 THEN 10 ELSE 101
    END AS `profit_user_id`,
    CONCAT('分页测试商户 ', LPAD(numbers.seq, 2, '0')) AS `name`,
    CONCAT('137', LPAD(numbers.seq, 8, '0')) AS `store_phone`,
    CONCAT('https://picsum.photos/seed/sai-page-', LPAD(numbers.seq, 2, '0'), '/160/160') AS `storefront_image`,
    CASE MOD(numbers.seq, 6)
        WHEN 0 THEN '美食餐饮/餐厅'
        WHEN 1 THEN '零售/便利店'
        WHEN 2 THEN '休闲娱乐/KTV'
        WHEN 3 THEN '酒店住宿/酒店'
        WHEN 4 THEN '运动健身/健身房'
        ELSE '文化教育/书店'
    END AS `industry`,
    CASE MOD(numbers.seq, 6)
        WHEN 0 THEN '北京市 / 北京市 / 朝阳区'
        WHEN 1 THEN '广东省 / 深圳市 / 南山区'
        WHEN 2 THEN '上海市 / 上海市 / 浦东新区'
        WHEN 3 THEN '四川省 / 成都市 / 武侯区'
        WHEN 4 THEN '浙江省 / 杭州市 / 西湖区'
        ELSE '湖北省 / 武汉市 / 江汉区'
    END AS `region`,
    CONCAT('分页测试地址 ', LPAD(numbers.seq, 2, '0'), ' 号') AS `address`,
    CONCAT('【分页演示数据】第 ', numbers.seq, ' 条商户，用于验证分页、排序和筛选。') AS `description`,
    CASE MOD(numbers.seq, 6)
        WHEN 0 THEN '["车载充电器"]'
        WHEN 1 THEN '["K霸宝"]'
        WHEN 2 THEN '["蓝牙线充"]'
        WHEN 3 THEN '["充电宝"]'
        WHEN 4 THEN '["密码线充"]'
        ELSE '["密码蓝牙线充"]'
    END AS `products`,
    CASE MOD(numbers.seq, 3)
        WHEN 0 THEN '比例分成' WHEN 1 THEN '不分成' ELSE '入驻费'
    END AS `profit_method`,
    CASE WHEN MOD(numbers.seq, 3) = 0 THEN 10 + MOD(numbers.seq, 16) ELSE 0 END AS `profit_rate`,
    CONCAT('测试联系人', LPAD(numbers.seq, 2, '0')) AS `contact`,
    CONCAT('191', LPAD(numbers.seq, 8, '0')) AS `phone`,
    '$2y$10$4m/HHtGkXi4k.8qHTgcT9e0A.aA9g.ZJNJ7k3A7n5NUY0pg81QAay' AS `password`,
    CASE MOD(numbers.seq, 3)
        WHEN 0 THEN '["自营","查看订单"]'
        WHEN 1 THEN '["查看订单"]'
        ELSE '["查看订单","白名单"]'
    END AS `permissions`,
    CASE WHEN MOD(numbers.seq, 4) = 0 THEN 0 ELSE 1 END AS `distributed`,
    CASE WHEN MOD(numbers.seq, 13) = 0 THEN 2 ELSE 1 END AS `status`,
    CASE MOD(numbers.seq, 3)
        WHEN 0 THEN 'city-partner' WHEN 1 THEN 'agent' ELSE 'salesperson'
    END AS `agent_type`,
    ROUND(numbers.seq * 376.85, 2) AS `total_amount`,
    ROUND(numbers.seq * 67.83, 2) AS `total_income`,
    ROUND(numbers.seq * 34.25, 2) AS `withdrawable_amount`,
    numbers.seq * 7 AS `order_count`,
    CASE WHEN MOD(numbers.seq, 4) = 0 THEN 0 ELSE MOD(numbers.seq, 12) + 1 END AS `device_count`,
    ROUND(MOD(numbers.seq * 13, 97) + 0.25, 2) AS `yesterday_usage_rate`,
    ROUND(MOD(numbers.seq * 11, 93) + 1.50, 2) AS `three_day_usage_rate`,
    ROUND(MOD(numbers.seq * 7, 89) + 2.75, 2) AS `seven_day_usage_rate`,
    ROUND(18 + MOD(numbers.seq * 5, 38) + 0.20, 2) AS `yesterday_average_order`,
    ROUND(20 + MOD(numbers.seq * 4, 35) + 0.40, 2) AS `three_day_average_order`,
    ROUND(22 + MOD(numbers.seq * 3, 32) + 0.60, 2) AS `seven_day_average_order`,
    1 AS `created_by`, 1 AS `updated_by`,
    DATE_SUB(NOW(), INTERVAL numbers.seq DAY) AS `create_time`, NOW() AS `update_time`
FROM (
    SELECT ones.n + tens.n * 10 + 1 AS seq
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) ones
    CROSS JOIN (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) tens
) numbers
LEFT JOIN `sa_merchant` existing
       ON existing.`phone` = CONCAT('191', LPAD(numbers.seq, 8, '0'))
      AND existing.`delete_time` IS NULL
WHERE numbers.seq BETWEEN 1 AND 57
  AND existing.`id` IS NULL
ORDER BY numbers.seq;

-- 如需清理本文件生成的数据，可执行：
-- UPDATE sa_merchant SET delete_time = NOW()
-- WHERE phone BETWEEN '19100000001' AND '19100000057' AND description LIKE '【分页演示数据】%';