-- =============================================================================
-- 迁移：重建 RLS 视图 app.v_dws_finance_recharge_summary，新增赠送卡细分字段
-- 日期：2026-03-20
-- 关联 SPEC：gift-card-breakdown（赠送卡矩阵细分数据）
-- 前置：ETL 库已执行 2026-03-20_add_gift_breakdown_fields.sql（DWS 表新增 6 列）
-- =============================================================================

CREATE OR REPLACE VIEW app.v_dws_finance_recharge_summary AS
SELECT id,
    site_id,
    tenant_id,
    stat_date,
    recharge_count,
    recharge_total,
    recharge_cash,
    recharge_gift,
    first_recharge_count,
    first_recharge_cash,
    first_recharge_gift,
    first_recharge_total,
    renewal_count,
    renewal_cash,
    renewal_gift,
    renewal_total,
    recharge_member_count,
    new_member_count,
    total_card_balance,
    cash_card_balance,
    gift_card_balance,
    -- 赠送卡细分字段（6 个新增）
    gift_liquor_balance,
    gift_table_fee_balance,
    gift_voucher_balance,
    gift_liquor_recharge,
    gift_table_fee_recharge,
    gift_voucher_recharge,
    created_at,
    updated_at
FROM dws.dws_finance_recharge_summary
WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

-- =============================================================================
-- 回滚（恢复为不含细分字段的原始视图）
-- =============================================================================
-- CREATE OR REPLACE VIEW app.v_dws_finance_recharge_summary AS
-- SELECT id, site_id, tenant_id, stat_date,
--     recharge_count, recharge_total, recharge_cash, recharge_gift,
--     first_recharge_count, first_recharge_cash, first_recharge_gift, first_recharge_total,
--     renewal_count, renewal_cash, renewal_gift, renewal_total,
--     recharge_member_count, new_member_count,
--     total_card_balance, cash_card_balance, gift_card_balance,
--     created_at, updated_at
-- FROM dws.dws_finance_recharge_summary
-- WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

-- =============================================================================
-- 验证
-- =============================================================================
-- 1. 确认视图包含新字段
-- SELECT column_name FROM information_schema.columns
-- WHERE table_schema = 'app' AND table_name = 'v_dws_finance_recharge_summary'
--   AND column_name LIKE 'gift_%'
-- ORDER BY ordinal_position;
-- 预期：gift_card_balance + 6 个新字段 = 7 行

-- 2. 确认 RLS 过滤生效（需先 SET app.current_site_id）
-- SET app.current_site_id = '12345';
-- SELECT COUNT(*) FROM app.v_dws_finance_recharge_summary;

-- 3. 确认新字段可查询且有默认值
-- SET app.current_site_id = '12345';
-- SELECT gift_liquor_balance, gift_table_fee_balance, gift_voucher_balance,
--        gift_liquor_recharge, gift_table_fee_recharge, gift_voucher_recharge
-- FROM app.v_dws_finance_recharge_summary LIMIT 1;
