-- =============================================================================
-- 迁移：补充 ETL 字段差异分析中识别的缺失字段
-- 日期：2026-03-26
-- 来源：field_gap_analysis.md 深度评估
-- 影响：5 张 ODS 表 + 6 张 DWD 表
-- =============================================================================

BEGIN;

-- ── ODS 层新增列 ──

-- 1. member_profiles: 4 个 API 字段
ALTER TABLE ods.member_profiles ADD COLUMN IF NOT EXISTS other_pay_money_sum numeric(18,2);
ALTER TABLE ods.member_profiles ADD COLUMN IF NOT EXISTS last_consume_time timestamp without time zone;
ALTER TABLE ods.member_profiles ADD COLUMN IF NOT EXISTS non_consume_day_num integer;
ALTER TABLE ods.member_profiles ADD COLUMN IF NOT EXISTS first_consumption integer;

-- 2. assistant_service_records: 1 个 API 字段
ALTER TABLE ods.assistant_service_records ADD COLUMN IF NOT EXISTS deduct_leave_seconds integer DEFAULT 0;

-- 3. store_goods_sales_records: 2 个 API 字段
ALTER TABLE ods.store_goods_sales_records ADD COLUMN IF NOT EXISTS activity_amount numeric(18,2) DEFAULT 0;
ALTER TABLE ods.store_goods_sales_records ADD COLUMN IF NOT EXISTS activity_id bigint DEFAULT 0;

-- 4. goods_stock_summary: 1 个 API 字段（注意 ODS 列名全小写）
ALTER TABLE ods.goods_stock_summary ADD COLUMN IF NOT EXISTS createtime timestamp without time zone;

-- 5. table_fee_transactions: order_from
ALTER TABLE ods.table_fee_transactions ADD COLUMN IF NOT EXISTS order_from integer;

-- 6. assistant_service_records: order_from
ALTER TABLE ods.assistant_service_records ADD COLUMN IF NOT EXISTS order_from integer;

-- 7. store_goods_sales_records: order_from
ALTER TABLE ods.store_goods_sales_records ADD COLUMN IF NOT EXISTS order_from integer;

-- 8. settlement_records: order_from（来自 settleList.orderFrom，展开后为 orderfrom）
-- 注意：settlement_records 的 ODS 列名是全小写无下划线（与 API camelCase 对应）
ALTER TABLE ods.settlement_records ADD COLUMN IF NOT EXISTS orderfrom integer;

-- ── DWD 层新增列 ──

-- 1. dim_member_ex: 4 个字段 + birthday
ALTER TABLE dwd.dim_member_ex ADD COLUMN IF NOT EXISTS other_pay_money_sum numeric(18,2);
ALTER TABLE dwd.dim_member_ex ADD COLUMN IF NOT EXISTS last_consume_time timestamp with time zone;
ALTER TABLE dwd.dim_member_ex ADD COLUMN IF NOT EXISTS non_consume_day_num integer;
ALTER TABLE dwd.dim_member_ex ADD COLUMN IF NOT EXISTS first_consumption integer;
-- birthday 已在 dim_member 主表中存在，无需重复添加

-- 2. dim_member_card_account_ex: pdassisnatlevel + cxassisnatlevel 已存在于 DDL 中
-- 确认：goodscategoryid, tableareaid, pdassisnatlevel, cxassisnatlevel 已在 DWD DDL 和 FACT_MAPPINGS 中

-- 3. dwd_assistant_service_log_ex: deduct_leave_seconds + order_from
ALTER TABLE dwd.dwd_assistant_service_log_ex ADD COLUMN IF NOT EXISTS deduct_leave_seconds integer DEFAULT 0;
ALTER TABLE dwd.dwd_assistant_service_log_ex ADD COLUMN IF NOT EXISTS order_from integer;

-- 4. dwd_store_goods_sale_ex: activity_amount + activity_id + order_from
ALTER TABLE dwd.dwd_store_goods_sale_ex ADD COLUMN IF NOT EXISTS activity_amount numeric(18,2) DEFAULT 0;
ALTER TABLE dwd.dwd_store_goods_sale_ex ADD COLUMN IF NOT EXISTS activity_id bigint DEFAULT 0;
ALTER TABLE dwd.dwd_store_goods_sale_ex ADD COLUMN IF NOT EXISTS order_from integer;

-- 5. dwd_goods_stock_summary: create_time
ALTER TABLE dwd.dwd_goods_stock_summary ADD COLUMN IF NOT EXISTS create_time timestamp with time zone;

-- 6. dwd_table_fee_log_ex: order_from
ALTER TABLE dwd.dwd_table_fee_log_ex ADD COLUMN IF NOT EXISTS order_from integer;

-- 7. dwd_settlement_head_ex: order_from
ALTER TABLE dwd.dwd_settlement_head_ex ADD COLUMN IF NOT EXISTS order_from integer;

COMMIT;
