-- ============================================================
-- 评级时效推荐规则表 建表脚本（PostgreSQL）
-- ⚠️ 需大侠评审确认后手动执行；本期只读查询、不接受任何写入
-- 2026-06-24
-- ============================================================

CREATE TABLE IF NOT EXISTS t_rating_recommend_rule (
    id                   BIGSERIAL PRIMARY KEY,
    player               VARCHAR(255) NOT NULL,
    year                 VARCHAR(64)  NOT NULL,
    series               VARCHAR(255) NOT NULL,
    card_set             VARCHAR(255) NOT NULL,
    recommend_efficiency SMALLINT     NOT NULL,
    effective_time       TIMESTAMP,
    create_time          TIMESTAMP    NOT NULL DEFAULT now(),
    update_time          TIMESTAMP    NOT NULL DEFAULT now(),
    del_flag             SMALLINT     NOT NULL DEFAULT 0
);
COMMENT ON TABLE t_rating_recommend_rule IS '评级时效推荐规则表（数仓沉淀，只读查询）';

-- 支撑 L2/L3 系列主导降级查询
CREATE INDEX IF NOT EXISTS idx_rrr_series_set_year ON t_rating_recommend_rule (series, card_set, year);
-- 支撑 L1 全等命中查询
CREATE INDEX IF NOT EXISTS idx_rrr_feature ON t_rating_recommend_rule (player, year, series, card_set);