-- 为 SIM 充值日志和用量快照增加不可变身份快照，允许删除 sim_card 后继续审计。
-- 执行前请备份数据库。

ALTER TABLE sim_recharge_log
    ADD COLUMN iccid_snapshot VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'ICCID 快照' AFTER sim_card_id,
    ADD COLUMN carrier_snapshot VARCHAR(16) NOT NULL DEFAULT '' COMMENT '运营商快照' AFTER iccid_snapshot;

ALTER TABLE sim_usage_record
    ADD COLUMN iccid_snapshot VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'ICCID 快照' AFTER sim_card_id,
    ADD COLUMN carrier_snapshot VARCHAR(16) NOT NULL DEFAULT '' COMMENT '运营商快照' AFTER iccid_snapshot;

UPDATE sim_recharge_log l
JOIN sim_card c ON c.id = l.sim_card_id
SET l.iccid_snapshot = c.iccid, l.carrier_snapshot = c.carrier
WHERE l.iccid_snapshot = '';

UPDATE sim_usage_record r
JOIN sim_card c ON c.id = r.sim_card_id
SET r.iccid_snapshot = c.iccid, r.carrier_snapshot = c.carrier
WHERE r.iccid_snapshot = '';