-- 迁移：创建看板缓存层表 dws_finance_board_cache 及 RLS 视图
-- 关联：board-finance-dws-area-refactor SPEC T5.1
-- 日期：2026-03-28
-- 回滚：见文件末尾

-- ============================================================
-- 1. 创建 dws_finance_board_cache 表
-- ============================================================
CREATE TABLE dws.dws_finance_board_cache (
    id                  BIGSERIAL PRIMARY KEY,
    site_id             BIGINT NOT NULL,
    time_range          VARCHAR(20) NOT NULL,
    area_code           VARCHAR(20) NOT NULL,
    start_date          DATE NOT NULL,
    end_date            DATE NOT NULL,
    prev_start_date     DATE,
    prev_end_date       DATE,

    -- overview 8 项核心指标
    occurrence          NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount            NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_rate       NUMERIC(8,4) NOT NULL DEFAULT 0,
    confirmed_revenue   NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_in             NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_out            NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_balance        NUMERIC(14,2) NOT NULL DEFAULT 0,
    balance_rate        NUMERIC(8,4) NOT NULL DEFAULT 0,

    -- 数据指纹（MD5，用于检测源数据变化触发缓存失效）
    data_fingerprint    VARCHAR(64),
    computed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- 唯一约束：每个站点每个时间范围每区域一行
    UNIQUE (site_id, time_range, area_code)
);

COMMENT ON TABLE dws.dws_finance_board_cache
    IS '看板缓存层表，缓存已完成周期的 overview 聚合结果，按 (site_id, time_range, area_code) 唯一';

COMMENT ON COLUMN dws.dws_finance_board_cache.time_range
    IS '时间范围：lastMonth/lastWeek/lastQuarter/quarter3/half6（仅已完成周期缓存）';
COMMENT ON COLUMN dws.dws_finance_board_cache.area_code
    IS '区域编码：all/hall/hallA/hallB/hallC/vip/snooker/mahjong/ktv';
COMMENT ON COLUMN dws.dws_finance_board_cache.start_date
    IS '当期起始日期';
COMMENT ON COLUMN dws.dws_finance_board_cache.end_date
    IS '当期结束日期';
COMMENT ON COLUMN dws.dws_finance_board_cache.prev_start_date
    IS '上期起始日期（环比用），无上期时为 NULL';
COMMENT ON COLUMN dws.dws_finance_board_cache.prev_end_date
    IS '上期结束日期（环比用），无上期时为 NULL';
COMMENT ON COLUMN dws.dws_finance_board_cache.occurrence
    IS '发生额（毛收入 gross_amount 的周期汇总）';
COMMENT ON COLUMN dws.dws_finance_board_cache.discount
    IS '优惠合计（discount_total 的周期汇总）';
COMMENT ON COLUMN dws.dws_finance_board_cache.discount_rate
    IS '优惠占比 = discount / occurrence';
COMMENT ON COLUMN dws.dws_finance_board_cache.confirmed_revenue
    IS '确认收入 = occurrence - discount';
COMMENT ON COLUMN dws.dws_finance_board_cache.cash_in
    IS '现金流入合计（仅 area_code=all 时有效值）';
COMMENT ON COLUMN dws.dws_finance_board_cache.cash_out
    IS '现金流出合计（仅 area_code=all 时有效值）';
COMMENT ON COLUMN dws.dws_finance_board_cache.cash_balance
    IS '现金余额变动 = cash_in - cash_out';
COMMENT ON COLUMN dws.dws_finance_board_cache.balance_rate
    IS '余额变动率';
COMMENT ON COLUMN dws.dws_finance_board_cache.data_fingerprint
    IS '源数据指纹（MD5），用于检测补录导致的数据变化';
COMMENT ON COLUMN dws.dws_finance_board_cache.computed_at
    IS '缓存计算时间';

-- ============================================================
-- 2. 创建 RLS 视图
-- ============================================================
CREATE OR REPLACE VIEW dws.v_dws_finance_board_cache AS
SELECT *
FROM dws.dws_finance_board_cache
WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

GRANT SELECT ON dws.v_dws_finance_board_cache TO app_reader;

-- ============================================================
-- 3. 创建 app schema 导出视图（后端通过 app.v_* 访问）
-- ============================================================
CREATE OR REPLACE VIEW app.v_dws_finance_board_cache AS
SELECT *
FROM dws.dws_finance_board_cache
WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

GRANT SELECT ON app.v_dws_finance_board_cache TO app_reader;

-- ============================================================
-- 回滚
-- ============================================================
-- DROP VIEW IF EXISTS app.v_dws_finance_board_cache;
-- DROP VIEW IF EXISTS dws.v_dws_finance_board_cache;
-- DROP TABLE IF EXISTS dws.dws_finance_board_cache;
