31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
ALTER TABLE `activity_reward_settings`
|
||
ADD COLUMN `price_snapshot_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '奖品配置时商品价格快照（分）' AFTER `product_id`,
|
||
ADD COLUMN `price_snapshot_at` DATETIME(3) NULL COMMENT '奖品价格快照时间' AFTER `price_snapshot_cents`;
|
||
|
||
ALTER TABLE `user_inventory`
|
||
ADD COLUMN `value_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '资产价值快照（分）' AFTER `product_id`,
|
||
ADD COLUMN `value_source` TINYINT NOT NULL DEFAULT 0 COMMENT '价值来源：0未知 1奖励快照 2商品回退 3人工修复' AFTER `value_cents`,
|
||
ADD COLUMN `value_snapshot_at` DATETIME(3) NULL COMMENT '资产价值快照时间' AFTER `value_source`;
|
||
|
||
UPDATE `activity_reward_settings` ars
|
||
LEFT JOIN `products` p ON p.id = ars.product_id
|
||
SET ars.price_snapshot_cents = COALESCE(p.price, 0),
|
||
ars.price_snapshot_at = NOW(3)
|
||
WHERE ars.price_snapshot_cents = 0;
|
||
|
||
UPDATE `user_inventory` ui
|
||
LEFT JOIN `activity_reward_settings` ars ON ars.id = ui.reward_id
|
||
SET ui.value_cents = ars.price_snapshot_cents,
|
||
ui.value_source = 1,
|
||
ui.value_snapshot_at = COALESCE(ars.price_snapshot_at, NOW(3))
|
||
WHERE ui.value_cents = 0
|
||
AND ui.reward_id > 0
|
||
AND ars.price_snapshot_cents > 0;
|
||
|
||
UPDATE `user_inventory` ui
|
||
LEFT JOIN `products` p ON p.id = ui.product_id
|
||
SET ui.value_cents = COALESCE(p.price, 0),
|
||
ui.value_source = 2,
|
||
ui.value_snapshot_at = NOW(3)
|
||
WHERE ui.value_cents = 0;
|