-- =============================================================================
-- 迁移：刷新 FDW 外部表 v_dws_finance_recharge_summary，同步赠送卡细分字段
-- 日期：2026-03-20
-- 关联 SPEC：gift-card-breakdown（赠送卡矩阵细分数据）
-- 前置：
--   1. ETL 库已执行 DDL 迁移（DWS 表新增 6 列）
--   2. ETL 库已执行 RLS 视图重建（app.v_dws_finance_recharge_summary 包含 6 个新字段）
-- 幂等策略：先 DROP 外部表再 IMPORT FOREIGN SCHEMA，支持重复执行不报错
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. 删除旧的外部表（IF EXISTS 保证幂等）
-- -----------------------------------------------------------------------------
DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_finance_recharge_summary;

-- -----------------------------------------------------------------------------
-- 2. 重新导入外部表（从 ETL 库 app schema 获取最新列定义）
--    LIMIT TO 仅导入目标视图，避免影响其他外部表
-- -----------------------------------------------------------------------------
IMPORT FOREIGN SCHEMA app
    LIMIT TO (v_dws_finance_recharge_summary)
    FROM SERVER etl_feiqiu_server
    INTO fdw_etl;


-- =============================================================================
-- 回滚（恢复为旧版外部表，需重新导入）
-- =============================================================================
-- DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_finance_recharge_summary;
-- IMPORT FOREIGN SCHEMA app
--     LIMIT TO (v_dws_finance_recharge_summary)
--     FROM SERVER etl_feiqiu_server
--     INTO fdw_etl;
-- 注意：回滚前需先将 ETL 库的 RLS 视图恢复为不含细分字段的版本

-- =============================================================================
-- 验证 SQL
-- =============================================================================
-- 1. 确认外部表存在且包含新字段（应有 6 个 gift_* 细分字段）
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'fdw_etl'
--   AND table_name = 'v_dws_finance_recharge_summary'
--   AND column_name LIKE 'gift_%'
-- ORDER BY ordinal_position;
-- 预期：gift_card_balance + 6 个新字段 = 7 行

-- 2. 确认外部表可查询
-- SELECT gift_liquor_balance, gift_table_fee_balance, gift_voucher_balance,
--        gift_liquor_recharge, gift_table_fee_recharge, gift_voucher_recharge
-- FROM fdw_etl.v_dws_finance_recharge_summary
-- LIMIT 1;

-- 3. 确认外部表总列数正确（原有列 + 6 个新列）
-- SELECT COUNT(*) AS column_count
-- FROM information_schema.columns
-- WHERE table_schema = 'fdw_etl'
--   AND table_name = 'v_dws_finance_recharge_summary';
