-- V15: 新增独立的 starter（入门会员）等级，free 回归为真正的免费等级
-- 1. 将现有 free 等级的价格和积分复制给新的 starter 等级
-- 2. free 等级归零（价格0、积分0），作为新用户注册的保底等级
-- 3. 已付费的 free 用户迁移到 starter

-- 插入 starter 等级（从现有 free 等级复制价格和积分）
INSERT INTO membership_levels (name, display_name, description, price, duration_days, points_bonus, is_active)
SELECT 'starter', '入门会员', '入门版会员套餐', price, duration_days, points_bonus, TRUE
FROM membership_levels WHERE name = 'free'
ON DUPLICATE KEY UPDATE
  price = (SELECT price FROM (SELECT price FROM membership_levels WHERE name = 'free') AS tmp),
  points_bonus = (SELECT points_bonus FROM (SELECT points_bonus FROM membership_levels WHERE name = 'free') AS tmp2),
  duration_days = (SELECT duration_days FROM (SELECT duration_days FROM membership_levels WHERE name = 'free') AS tmp3);

-- 将已付费用户的会员等级从 free 迁移到 starter
-- 判断条件：user_memberships 中 membership_level_id 指向 free，且该用户有成功的支付记录
UPDATE user_memberships um
SET um.membership_level_id = (SELECT id FROM membership_levels WHERE name = 'starter')
WHERE um.membership_level_id = (SELECT id FROM membership_levels WHERE name = 'free')
  AND um.user_id IN (
    SELECT DISTINCT user_id FROM payments WHERE status = 'SUCCESS'
  );

-- free 等级归零：价格0、积分0，作为新用户注册保底
UPDATE membership_levels
SET price = 0, points_bonus = 0, display_name = '免费会员', description = '免费会员（新用户注册默认）'
WHERE name = 'free';
