-- 迁移：财务日报新增支付方式拆分字段
-- 关联：board-finance-integration SPEC T1.1
-- 日期：2026-03-27
-- 回滚：见文件末尾

-- ============================================================
-- 1. 新增字段
-- ============================================================
ALTER TABLE dws.dws_finance_daily_summary
    ADD COLUMN IF NOT EXISTS cash_paper_amount NUMERIC(14,2) DEFAULT 0 NOT NULL,
    ADD COLUMN IF NOT EXISTS scan_pay_amount NUMERIC(14,2) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN dws.dws_finance_daily_summary.cash_paper_amount
    IS '纸币现金收款（dwd_payment.payment_method=2）';
COMMENT ON COLUMN dws.dws_finance_daily_summary.scan_pay_amount
    IS '扫码收款/离线支付（dwd_payment.payment_method=4，含微信/支付宝）';

-- ============================================================
-- 2. 更新 RLS 视图暴露新字段
-- ============================================================
CREATE OR REPLACE VIEW app.v_dws_finance_daily_summary AS
SELECT id, site_id, tenant_id, stat_date,
    gross_amount, table_fee_amount, goods_amount,
    assistant_pd_amount, assistant_cx_amount,
    discount_total, discount_groupbuy, discount_vip,
    discount_gift_card, discount_manual, discount_rounding, discount_other,
    confirmed_income,
    cash_inflow_total, cash_pay_amount,
    cash_paper_amount, scan_pay_amount,
    groupbuy_pay_amount, platform_settlement_amount, platform_fee_amount,
    recharge_cash_inflow,
    card_consume_total, recharge_card_consume AS cash_card_consume, gift_card_consume,
    cash_outflow_total, cash_balance_change,
    recharge_count, recharge_total, recharge_cash, recharge_gift,
    first_recharge_count, first_recharge_amount,
    renewal_count, renewal_amount,
    order_count, member_order_count, guest_order_count, avg_order_amount,
    created_at, updated_at
FROM dws.dws_finance_daily_summary
WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

GRANT SELECT ON app.v_dws_finance_daily_summary TO app_reader;

-- ============================================================
-- 回滚
-- ============================================================
-- DROP VIEW IF EXISTS app.v_dws_finance_daily_summary;
-- ALTER TABLE dws.dws_finance_daily_summary
--     DROP COLUMN IF EXISTS cash_paper_amount,
--     DROP COLUMN IF EXISTS scan_pay_amount;
-- 然后重建原始视图（见 docs/database/ddl/etl_feiqiu__app.sql）
