Files
junhong_cmp_fiber/migrations/000275_add_card_realname_time.up.sql
邹进 4967f4df43
Some checks failed
构建并部署到测试环境(无 SSH) / build-and-deploy (push) Has been cancelled
feat: 卡实名时间以上游 archiveTime 为准并记录实名时间历史,新增二次实名批次
2026-11-08 19:12:46 +09:00

71 lines
4.0 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- 卡实名时间与历史(add-realname-time-history)
-- tb_iot_card_realname:每卡一行,当前实名时间及来源、上游最近返回的 archiveTime、二次实名状态。
-- tb_iot_card_realname_history:只在当前实名时间变化时追加。
-- tb_iot_card_realname_reset_batch:二次实名导入批次。
CREATE TABLE IF NOT EXISTS tb_iot_card_realname (
id BIGSERIAL PRIMARY KEY,
card_id BIGINT NOT NULL,
realname_at TIMESTAMPTZ,
realname_at_source VARCHAR(20) NOT NULL DEFAULT '', -- upstream | first_observed | migrated
upstream_realname_at TIMESTAMPTZ, -- 上游最近一次返回的 archiveTime
first_observed_at TIMESTAMPTZ, -- 首次读到已实名的时间
reset_status VARCHAR(20) NOT NULL DEFAULT 'none', -- none | pending
reset_at TIMESTAMPTZ,
reset_baseline_at TIMESTAMPTZ, -- 清除前的上游实名时间
reset_batch_id BIGINT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_iot_card_realname_card ON tb_iot_card_realname (card_id);
CREATE INDEX IF NOT EXISTS idx_iot_card_realname_reset ON tb_iot_card_realname (reset_batch_id) WHERE reset_status = 'pending';
CREATE TABLE IF NOT EXISTS tb_iot_card_realname_history (
id BIGSERIAL PRIMARY KEY,
card_id BIGINT NOT NULL,
iccid VARCHAR(20) NOT NULL DEFAULT '',
before_at TIMESTAMPTZ,
after_at TIMESTAMPTZ,
after_source VARCHAR(20) NOT NULL DEFAULT '',
change_type VARCHAR(30) NOT NULL,
observation_source VARCHAR(30) NOT NULL DEFAULT '',
reset_batch_id BIGINT,
operator_id BIGINT NOT NULL DEFAULT 0,
observed_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_iot_card_realname_history_card ON tb_iot_card_realname_history (card_id, id DESC);
CREATE TABLE IF NOT EXISTS tb_iot_card_realname_reset_batch (
id BIGSERIAL PRIMARY KEY,
batch_no VARCHAR(40) NOT NULL,
import_file_key VARCHAR(500) NOT NULL DEFAULT '',
reason VARCHAR(500) NOT NULL DEFAULT '',
total_count INT NOT NULL DEFAULT 0,
succeeded_count INT NOT NULL DEFAULT 0,
skipped_count INT NOT NULL DEFAULT 0,
reverted_count INT NOT NULL DEFAULT 0,
completed_count INT NOT NULL DEFAULT 0,
items JSONB NOT NULL DEFAULT '[]',
operator_id BIGINT NOT NULL DEFAULT 0,
operator_name VARCHAR(100) NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_iot_card_realname_reset_batch_no ON tb_iot_card_realname_reset_batch (batch_no);
CREATE INDEX IF NOT EXISTS idx_iot_card_realname_reset_batch_created ON tb_iot_card_realname_reset_batch (created_at DESC);
-- 迁移补录:已实名且库里有实名时间的卡,沿用库里时间(来源 migrated),写一条迁移补录历史。可重复执行。
INSERT INTO tb_iot_card_realname (card_id, realname_at, realname_at_source, first_observed_at)
SELECT c.id, c.first_realname_at, 'migrated', c.first_realname_at
FROM tb_iot_card c
WHERE c.deleted_at IS NULL AND c.real_name_status = 1 AND c.first_realname_at IS NOT NULL
ON CONFLICT (card_id) DO NOTHING;
INSERT INTO tb_iot_card_realname_history (card_id, iccid, before_at, after_at, after_source, change_type, observation_source, observed_at)
SELECT r.card_id, c.iccid, NULL, r.realname_at, 'migrated', 'migrated', 'migration', NOW()
FROM tb_iot_card_realname r
JOIN tb_iot_card c ON c.id = r.card_id
WHERE r.realname_at_source = 'migrated'
AND NOT EXISTS (SELECT 1 FROM tb_iot_card_realname_history h WHERE h.card_id = r.card_id AND h.change_type = 'migrated');