-- 迁移：为 dwd.dim_store_goods_ex 新增 batch_stock_quantity 列
-- 日期：2026-02-20
-- 关联需求：dataflow-field-completion（NEW_FIELDS 中定义但缺少迁移脚本）
-- 说明：批次库存数量，来源 ODS store_goods_master.batch_stock_quantity

BEGIN;

ALTER TABLE dwd.dim_store_goods_ex
    ADD COLUMN IF NOT EXISTS batch_stock_quantity NUMERIC;

COMMENT ON COLUMN dwd.dim_store_goods_ex.batch_stock_quantity IS
    '【说明】批次库存数量，区别于当前库存（stock）和主表的 batch_stock_qty。     【ODS来源】store_goods_master - batch_stock_quantity。     【JSON字段】store_goods_master.json - data.orderGoodsList - batchStockQuantity。';

COMMIT;

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

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

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

-- 2. 确认列注释已添加
-- SELECT col_description(
--     (SELECT oid FROM pg_class WHERE relname = 'dim_store_goods_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 = 'dim_store_goods_ex'
--            AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd'))
--      AND attname = 'batch_stock_quantity')
-- ) AS batch_stock_quantity_comment;

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