-- 迁移：为 dwd_member_balance_change_ex 新增 relate_id 列
-- 关联需求：dataflow-field-completion Requirements 5.1, 5.2
-- 说明：relate_id 为关联业务单据 ID，指向触发余额变动的业务记录（充值单、订单等）
-- ODS 源列：ods.member_balance_changes.relate_id（BIGINT，已存在）

BEGIN;

-- 新增列
ALTER TABLE dwd.dwd_member_balance_change_ex
    ADD COLUMN IF NOT EXISTS relate_id BIGINT;

-- 添加列注释
COMMENT ON COLUMN dwd.dwd_member_balance_change_ex.relate_id IS
    '【说明】关联业务单据 ID，指向触发本次余额变动的业务记录（如充值单、订单、结算单等），按 from_type 不同指向不同表。     【示例】2957881518788421。     【ODS来源】member_balance_changes - relate_id。     【JSON字段】member_balance_changes.json - data.tenantMemberCardLogs - relate_id。';

COMMIT;

-- ============================================================
-- 回滚策略
-- ============================================================
-- BEGIN;
-- ALTER TABLE dwd.dwd_member_balance_change_ex DROP COLUMN IF EXISTS relate_id;
-- COMMIT;

-- ============================================================
-- 验证 SQL
-- ============================================================

-- 1. 确认新列已创建且类型正确
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'dwd'
--   AND table_name = 'dwd_member_balance_change_ex'
--   AND column_name = 'relate_id';

-- 2. 确认列注释已添加
-- SELECT col_description(
--     (SELECT oid FROM pg_class WHERE relname = 'dwd_member_balance_change_ex'
--      AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd')),
--     (SELECT attnum FROM pg_attribute
--      WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'dwd_member_balance_change_ex'
--            AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd'))
--      AND attname = 'relate_id')
-- ) AS relate_id_comment;

-- 3. 确认 ODS 源表中同名列存在
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'ods'
--   AND table_name = 'member_balance_changes'
--   AND column_name = 'relate_id';

-- 4. 重新加载后抽样验证新列有数据
-- SELECT relate_id, balance_change_id
-- FROM dwd.dwd_member_balance_change_ex
-- WHERE relate_id IS NOT NULL
-- LIMIT 10;
