-- 添加 product_id 字段到 livestream_draw_logs 表
-- 用于直接记录奖品关联的商品ID，避免依赖 livestream_prizes 表

ALTER TABLE livestream_draw_logs 
ADD COLUMN product_id BIGINT NULL COMMENT '关联商品ID(快照)' AFTER prize_name;

-- 方式1: 从当前有效的 livestream_prizes 关联回填
UPDATE livestream_draw_logs ldl
JOIN livestream_prizes lp ON lp.id = ldl.prize_id
SET ldl.product_id = lp.product_id
WHERE ldl.product_id IS NULL AND lp.product_id IS NOT NULL;

-- 方式2: 通过奖品名称精确匹配 products 表回填（针对奖品已删除的历史记录）
UPDATE livestream_draw_logs ldl
JOIN products p ON p.name = ldl.prize_name
SET ldl.product_id = p.id
WHERE ldl.product_id IS NULL AND p.deleted_at IS NULL;

-- 方式3: 通过奖品名称模糊匹配（处理名称略有差异的情况）
-- 注意：这个查询可能匹配到多个商品，使用 MIN(p.id) 取第一个匹配
UPDATE livestream_draw_logs ldl
JOIN (
    SELECT ldl2.id as log_id, MIN(p.id) as product_id
    FROM livestream_draw_logs ldl2
    JOIN products p ON p.name LIKE CONCAT('%', ldl2.prize_name, '%') 
                    OR ldl2.prize_name LIKE CONCAT('%', p.name, '%')
    WHERE ldl2.product_id IS NULL AND p.deleted_at IS NULL
    GROUP BY ldl2.id
) matched ON matched.log_id = ldl.id
SET ldl.product_id = matched.product_id
WHERE ldl.product_id IS NULL;
