-- 迁移：创建区域日粒度财务原子层表 dws_finance_area_daily 及 RLS 视图
-- 关联：board-finance-dws-area-refactor SPEC T2.1
-- 日期：2026-03-28
-- 回滚：见文件末尾

-- ============================================================
-- 1. 创建 dws_finance_area_daily 表
-- ============================================================
CREATE TABLE dws.dws_finance_area_daily (
    id                      BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    tenant_id               BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    area_code               VARCHAR(20) NOT NULL,

    -- 收入结构（4 项 + gross_amount）
    table_fee_amount        NUMERIC(14,2) NOT NULL DEFAULT 0,
    goods_amount            NUMERIC(14,2) NOT NULL DEFAULT 0,
    assistant_pd_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
    assistant_cx_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
    gross_amount            NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 优惠拆分（6 项 + discount_total）
    -- 恒等式：discount_total = groupbuy + vip + manual + gift_card + rounding + other
    discount_groupbuy       NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_vip            NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_manual         NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_gift_card      NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_rounding       NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_other          NUMERIC(14,2) NOT NULL DEFAULT 0,
    discount_total          NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 确认收入（gross_amount - discount_total）
    confirmed_income        NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 现金流（仅 area_code='all' 时有效值，其余区域为 0）
    cash_pay_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_paper_amount       NUMERIC(14,2) NOT NULL DEFAULT 0,
    scan_pay_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    groupbuy_pay_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
    recharge_cash_inflow    NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_inflow_total       NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_outflow_total      NUMERIC(14,2) NOT NULL DEFAULT 0,
    cash_balance_change     NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 卡消费（仅 area_code='all' 时有效值）
    card_consume_total      NUMERIC(14,2) NOT NULL DEFAULT 0,
    recharge_card_consume   NUMERIC(14,2) NOT NULL DEFAULT 0,
    gift_card_consume       NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 充值（仅 area_code='all' 时有效值）
    recharge_cash           NUMERIC(14,2) NOT NULL DEFAULT 0,
    first_recharge_cash     NUMERIC(14,2) NOT NULL DEFAULT 0,
    renewal_cash            NUMERIC(14,2) NOT NULL DEFAULT 0,

    -- 订单统计
    order_count             INTEGER NOT NULL DEFAULT 0,

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

    -- 唯一约束：每个站点每天每区域一行
    UNIQUE (site_id, stat_date, area_code)
);

COMMENT ON TABLE dws.dws_finance_area_daily
    IS '区域日粒度财务原子层表，按 (site_id, stat_date, area_code) 存储 9 个区域的收入/优惠/现金流预计算数据';

COMMENT ON COLUMN dws.dws_finance_area_daily.area_code
    IS '区域编码：all/hall/hallA/hallB/hallC/vip/snooker/mahjong/ktv';
COMMENT ON COLUMN dws.dws_finance_area_daily.gross_amount
    IS '毛收入 = table_fee_amount + goods_amount + assistant_pd_amount + assistant_cx_amount';
COMMENT ON COLUMN dws.dws_finance_area_daily.discount_total
    IS '优惠合计 = groupbuy + vip + manual + gift_card + rounding + other';
COMMENT ON COLUMN dws.dws_finance_area_daily.confirmed_income
    IS '确认收入 = gross_amount - discount_total';
COMMENT ON COLUMN dws.dws_finance_area_daily.discount_gift_card
    IS '赠送卡消费金额口径（非结算单 gift_card_amount）';

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

GRANT SELECT ON dws.v_dws_finance_area_daily TO app_reader;

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

GRANT SELECT ON app.v_dws_finance_area_daily TO app_reader;

-- ============================================================
-- 回滚
-- ============================================================
-- DROP VIEW IF EXISTS app.v_dws_finance_area_daily;
-- DROP VIEW IF EXISTS dws.v_dws_finance_area_daily;
-- DROP TABLE IF EXISTS dws.dws_finance_area_daily;
