water/server/plugin/saiadmin/db/20260905_seed_member_activation_test_data.sql

-- 本地会员开通记录测试数据。仅关联既有 TEST-ORDER-20260901-* 测试订单，可重复执行。

INSERT INTO `sa_member_activation_record` (
    `merchant_order_sn`, `transaction_sn`, `user_id`, `manage_agent_id`, `source`,
    `device_type`, `merchant_id`, `merchant_name`, `pay_amount`, `card_type`,
    `activation_time`, `expire_time`, `status`, `remark`, `created_by`, `updated_by`,
    `create_time`, `update_time`
)
SELECT 'TEST-MEMBER-WX-20260905-001', 'TEST-MEMBER-TXN-WX-001', o.`user_id`, o.`manage_agent_id`, 1,
       o.`device_type`, o.`rent_merchant_id`, COALESCE(m.`name`, '测试商户'), 19.90, 0,
       DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL 28 DAY), 1, '微信购买月卡',
       1, 1, NOW(), NOW()
FROM `sa_rental_order` o
LEFT JOIN `sa_merchant` m ON m.`id` = o.`rent_merchant_id` AND m.`delete_time` IS NULL
WHERE o.`order_sn` = 'TEST-ORDER-20260901-K02'
  AND o.`delete_time` IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM `sa_member_activation_record`
      WHERE `merchant_order_sn` = 'TEST-MEMBER-WX-20260905-001'
  );

INSERT INTO `sa_member_activation_record` (
    `merchant_order_sn`, `transaction_sn`, `user_id`, `manage_agent_id`, `source`,
    `device_type`, `merchant_id`, `merchant_name`, `pay_amount`, `card_type`,
    `activation_time`, `expire_time`, `status`, `remark`, `created_by`, `updated_by`,
    `create_time`, `update_time`
)
SELECT 'TEST-MEMBER-ZFB-20260905-002', 'TEST-MEMBER-TXN-ZFB-002', o.`user_id`, o.`manage_agent_id`, 2,
       o.`device_type`, o.`rent_merchant_id`, COALESCE(m.`name`, '测试商户'), 49.90, 1,
       DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_ADD(NOW(), INTERVAL 89 DAY), 1, '支付宝购买季卡',
       1, 1, NOW(), NOW()
FROM `sa_rental_order` o
LEFT JOIN `sa_merchant` m ON m.`id` = o.`rent_merchant_id` AND m.`delete_time` IS NULL
WHERE o.`order_sn` = 'TEST-ORDER-20260901-PW01'
  AND o.`delete_time` IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM `sa_member_activation_record`
      WHERE `merchant_order_sn` = 'TEST-MEMBER-ZFB-20260905-002'
  );

INSERT INTO `sa_member_activation_record` (
    `merchant_order_sn`, `transaction_sn`, `user_id`, `manage_agent_id`, `source`,
    `device_type`, `merchant_id`, `merchant_name`, `pay_amount`, `card_type`,
    `activation_time`, `expire_time`, `status`, `remark`, `created_by`, `updated_by`,
    `create_time`, `update_time`
)
SELECT 'TEST-MEMBER-ADMIN-20260905-003', '', o.`user_id`, o.`manage_agent_id`, 3,
       o.`device_type`, o.`rent_merchant_id`, COALESCE(m.`name`, '测试商户'), 0.00, 3,
       DATE_SUB(NOW(), INTERVAL 367 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY), 1, '后台赠送年卡（已到期）',
       1, 1, NOW(), NOW()
FROM `sa_rental_order` o
LEFT JOIN `sa_merchant` m ON m.`id` = o.`rent_merchant_id` AND m.`delete_time` IS NULL
WHERE o.`order_sn` = 'TEST-ORDER-20260901-PW03'
  AND o.`delete_time` IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM `sa_member_activation_record`
      WHERE `merchant_order_sn` = 'TEST-MEMBER-ADMIN-20260905-003'
  );