45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
-- 插入：为符合条件的超级个体用户各补一条 vip ¥1980 已付订单（仅无 vip 已付记录者）
|
||
-- 执行前请先跑 backfill-vip-orders-preview.sql，并备份 orders 表
|
||
|
||
INSERT INTO orders (
|
||
id,
|
||
order_sn,
|
||
user_id,
|
||
open_id,
|
||
product_type,
|
||
product_id,
|
||
amount,
|
||
description,
|
||
status,
|
||
payment_method,
|
||
transaction_id,
|
||
pay_time,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
SELECT
|
||
CONCAT('BFV', MD5(CONCAT(u.id, ':vip1980:backfill'))),
|
||
CONCAT('BFV', MD5(CONCAT(u.id, ':vip1980:backfill'))),
|
||
u.id,
|
||
COALESCE(u.open_id, ''),
|
||
'vip',
|
||
'vip_annual',
|
||
1980.00,
|
||
'卡若创业派对VIP年度会员（365天）【数据补录：与超级个体权益对齐】',
|
||
'paid',
|
||
'manual',
|
||
'MANUAL_BACKFILL_VIP',
|
||
COALESCE(u.vip_activated_at, u.updated_at, u.created_at, NOW()),
|
||
NOW(),
|
||
NOW()
|
||
FROM users u
|
||
WHERE u.deleted_at IS NULL
|
||
AND COALESCE(u.is_vip, 0) = 1
|
||
AND (u.vip_expire_date IS NULL OR u.vip_expire_date > NOW())
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM orders o
|
||
WHERE o.user_id = u.id
|
||
AND o.product_type = 'vip'
|
||
AND o.status IN ('paid', 'completed', 'success')
|
||
);
|