-- 迁移：ODS/DWD 层会员表新增 birthday 列
-- 日期：2026-02-22
-- 关联：需求 C1（需求 4.1）— 会员生日字段 ETL 链路补齐
-- 影响：ods.member_profiles 加列、dwd.dim_member 加列
-- 幂等：ADD COLUMN IF NOT EXISTS，可重复执行

BEGIN;

-- ============================================================
-- 1. ODS 层：member_profiles 加 birthday 列
-- ============================================================
ALTER TABLE ods.member_profiles
    ADD COLUMN IF NOT EXISTS birthday DATE;

COMMENT ON COLUMN ods.member_profiles.birthday
    IS '会员生日，来源：上游 API payload 中的 birthday 字段';

-- ============================================================
-- 2. DWD 层：dim_member 加 birthday 列
-- ============================================================
ALTER TABLE dwd.dim_member
    ADD COLUMN IF NOT EXISTS birthday DATE;

COMMENT ON COLUMN dwd.dim_member.birthday
    IS '会员生日，来源：ODS member_profiles payload 中的 birthday 字段';

COMMIT;

-- ============================================================
-- 回滚（需手动执行，不在事务内）
-- ============================================================
-- BEGIN;
-- ALTER TABLE ods.member_profiles DROP COLUMN IF EXISTS birthday;
-- ALTER TABLE dwd.dim_member DROP COLUMN IF EXISTS birthday;
-- COMMIT;

-- ============================================================
-- 验证 SQL
-- ============================================================
-- 1. 确认 ods.member_profiles.birthday 列存在
-- SELECT column_name, data_type
--   FROM information_schema.columns
--  WHERE table_schema = 'ods'
--    AND table_name = 'member_profiles'
--    AND column_name = 'birthday';

-- 2. 确认 dwd.dim_member.birthday 列存在
-- SELECT column_name, data_type
--   FROM information_schema.columns
--  WHERE table_schema = 'dwd'
--    AND table_name = 'dim_member'
--    AND column_name = 'birthday';

-- 3. 确认列注释已设置
-- SELECT col_description(
--     (SELECT oid FROM pg_class WHERE relname = 'dim_member' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd')),
--     (SELECT ordinal_position FROM information_schema.columns WHERE table_schema = 'dwd' AND table_name = 'dim_member' AND column_name = 'birthday')
-- );
