MySQL和PHP如何设计每周自动加权抽奖活动数据库？

-- 计算用户的加权分数
WITH WeightedScores AS (
    SELECT 
        user_id, 
        SUM(order_amount * order_count) AS weighted_score
    FROM user_orders
    WHERE order_date BETWEEN (SELECT start_date FROM prize_pool WHERE pool_id = LAST_INSERT_ID()) AND (SELECT end_date FROM prize_pool WHERE pool_id = LAST_INSERT_ID())
    GROUP BY user_id
),
-- 计算总加权分数
TotalWeightedScore AS (
    SELECT SUM(weighted_score) AS total_score FROM WeightedScores
)
-- 分配奖金
INSERT INTO winners (pool_id, user_id, win_amount)
SELECT 
    LAST_INSERT_ID(),  -- 获取当前奖池ID
    user_id,
    ROUND((weighted_score / (SELECT total_score FROM TotalWeightedScore)) * (SELECT prize_amount * 0.5 FROM prize_pool WHERE pool_id = LAST_INSERT_ID()), 2)  -- 计算并分配奖金 (保留两位小数)
FROM WeightedScores
WHERE weighted_score > 0;

-- 更新奖池，将剩余50%滚动到下一轮
UPDATE prize_pool
SET prize_amount = prize_amount * 0.5
WHERE pool_id = LAST_INSERT_ID();

-- 创建下一轮奖池
INSERT INTO prize_pool (start_date, end_date, prize_amount, previous_pool_id) 
VALUES (DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 7 DAY), (SELECT prize_amount * 0.5 FROM prize_pool WHERE pool_id = LAST_INSERT_ID()), LAST_INSERT_ID());

// ... 数据库连接代码 ...

// 获取上一轮奖池ID
$lastPoolId = // ... 从数据库获取 ...

// 执行SQL语句进行开奖和奖池更新
$sql = // ... 上面的SQL语句 ...
$result = $conn->query($sql);

// ... 错误处理和结果输出 ...

// 关闭数据库连接
$conn->close();