RatingRecommend/docs/superpowers/specs/ddl/2026-06-25-rating-datasync-trend.sql

-- ============================================================
-- 数仓同步 + 卡片趋势 扩展建表/改表脚本（PostgreSQL）
-- ⚠️ 需大侠评审确认后手动执行；执行器不主动连库执行
-- 2026-06-25
-- 依赖：先执行 2026-06-24-rating-recommend-rule.sql（已建 t_rating_recommend_rule）
-- ============================================================

-- ------------------------------------------------------------
-- 1. 规则主表加列：卡片唯一ID/卡号/价值/批次号
-- ------------------------------------------------------------
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS card_id          VARCHAR(64);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS card_no          VARCHAR(64);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS current_value    NUMERIC(12,2);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS value_change_pct NUMERIC(8,4);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS value_min        NUMERIC(12,2);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS value_max        NUMERIC(12,2);
ALTER TABLE t_rating_recommend_rule ADD COLUMN IF NOT EXISTS batch_no         VARCHAR(32);

COMMENT ON COLUMN t_rating_recommend_rule.card_id IS '数仓卡片唯一ID（去重键）';
COMMENT ON COLUMN t_rating_recommend_rule.card_no IS '卡号（如 066/080）';
COMMENT ON COLUMN t_rating_recommend_rule.current_value IS '当前价';
COMMENT ON COLUMN t_rating_recommend_rule.value_change_pct IS '涨跌幅（百分数，如 7.05 表示 +7.05%）';
COMMENT ON COLUMN t_rating_recommend_rule.value_min IS '价格区间下限';
COMMENT ON COLUMN t_rating_recommend_rule.value_max IS '价格区间上限';
COMMENT ON COLUMN t_rating_recommend_rule.batch_no IS '同步批次号（如 2026062501）';

-- 批次内卡片唯一
CREATE UNIQUE INDEX IF NOT EXISTS uk_rrr_batch_card ON t_rating_recommend_rule (batch_no, card_id);
-- 带批次的特征降级查询索引
CREATE INDEX IF NOT EXISTS idx_rrr_batch_feature ON t_rating_recommend_rule (batch_no, series, card_set, year);

-- ------------------------------------------------------------
-- 2. POP 明细表（按机构，一卡多行）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t_rating_card_pop (
    id          BIGSERIAL PRIMARY KEY,
    card_id     VARCHAR(64) NOT NULL,
    agency      VARCHAR(32) NOT NULL,
    grade_all   INT,
    grade_10    INT,
    grade_9     INT,
    grade_8     INT,
    grade_7     INT,
    batch_no    VARCHAR(32) NOT NULL,
    create_time TIMESTAMP   NOT NULL DEFAULT now()
);
COMMENT ON TABLE t_rating_card_pop IS '卡片 POP report 明细（按评级机构 PSA/BGS/Nexphostis）';
CREATE INDEX IF NOT EXISTS idx_rcp_batch_card ON t_rating_card_pop (batch_no, card_id);

-- ------------------------------------------------------------
-- 3. 价格历史表（增量累积，趋势曲线数据源）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t_rating_card_value_history (
    id          BIGSERIAL PRIMARY KEY,
    card_id     VARCHAR(64)  NOT NULL,
    price       NUMERIC(12,2) NOT NULL,
    record_date DATE         NOT NULL,
    create_time TIMESTAMP    NOT NULL DEFAULT now()
);
COMMENT ON TABLE t_rating_card_value_history IS '卡片价格历史（每日增量累积，趋势曲线用）';
-- 同卡同日只一条，保证每日推送幂等
CREATE UNIQUE INDEX IF NOT EXISTS uk_rcvh_card_date ON t_rating_card_value_history (card_id, record_date);

-- ------------------------------------------------------------
-- 4. 生效批次指针表（单行，记录当前对外生效的批次号）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t_rating_recommend_active_batch (
    id              BIGSERIAL PRIMARY KEY,
    active_batch_no VARCHAR(32),
    update_time     TIMESTAMP NOT NULL DEFAULT now()
);
COMMENT ON TABLE t_rating_recommend_active_batch IS '推荐规则生效批次指针（单行）';

-- 初始化一行空指针（首次同步前查询将无生效批次→走兜底，符合预期）
INSERT INTO t_rating_recommend_active_batch (active_batch_no)
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM t_rating_recommend_active_batch);