-- ============================================================
-- 迁移：修正 dim_store_goods.batch_stock_qty 和
--       dim_store_goods_ex.provisional_total_cost 的列注释
-- 日期：2026-02-20
-- 关联需求：dataflow-field-completion Requirements 10.3
-- ============================================================
-- 变更说明：
--   本次仅修正列 COMMENT，无 DDL 结构变更（列已存在）。
--   batch_stock_qty 的 ODS 来源从 stock 修正为 batch_stock_quantity
--   provisional_total_cost 的 ODS 来源从 total_purchase_cost 修正为 provisional_total_cost
--
-- 代码侧变更（不在本脚本范围）：
--   dwd_load_task.py FACT_MAPPINGS 已同步修正
--   修正后需重新执行 DWD_LOAD_FROM_ODS 以回填历史数据
-- ============================================================

BEGIN;

-- 1. 修正 batch_stock_qty 注释：ODS 来源 stock → batch_stock_quantity
COMMENT ON COLUMN dwd.dim_store_goods.batch_stock_qty IS
    '【说明】批次库存数量，区别于当前库存（stock）。     【示例】18。     【ODS来源】store_goods_master - batch_stock_quantity。     【JSON字段】store_goods_master.json - data.orderGoodsList - batchStockQuantity。';

-- 2. 修正 provisional_total_cost 注释：ODS 来源 total_purchase_cost → provisional_total_cost
COMMENT ON COLUMN dwd.dim_store_goods_ex.provisional_total_cost IS
    '【说明】暂估总成本，区别于实际采购成本（total_purchase_cost）。     【示例】0.0。     【ODS来源】store_goods_master - provisional_total_cost。     【JSON字段】store_goods_master.json - data.orderGoodsList - provisionalTotalCost。';

COMMIT;

-- ============================================================
-- 回滚策略
-- ============================================================
-- BEGIN;
-- COMMENT ON COLUMN dwd.dim_store_goods.batch_stock_qty IS
--     '【说明】数量/时长字段，用于统计与计量。     【示例】18（数量/时长字段，用于统计与计量）。     【ODS来源】store_goods_master - stock。     【JSON字段】store_goods_master.json - data.orderGoodsList - stock。';
-- COMMENT ON COLUMN dwd.dim_store_goods_ex.provisional_total_cost IS
--     '【说明】金额字段，用于计费/结算/核算等金额计算。     【示例】0.0（金额字段，用于计费/结算/核算等金额计算）。     【ODS来源】store_goods_master - total_purchase_cost。     【JSON字段】store_goods_master.json - data.orderGoodsList - total_purchase_cost。';
-- COMMIT;

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

-- 1. 确认 batch_stock_qty 注释已更新为 batch_stock_quantity
SELECT col_description(
    (SELECT oid FROM pg_class WHERE relname = 'dim_store_goods'
     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'
           AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd'))
     AND attname = 'batch_stock_qty')
) AS batch_stock_qty_comment;
-- 预期：包含 'batch_stock_quantity'，不包含 '- stock。'

-- 2. 确认 provisional_total_cost 注释已更新为 provisional_total_cost
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 = 'provisional_total_cost')
) AS provisional_total_cost_comment;
-- 预期：包含 'provisional_total_cost'，不包含 'total_purchase_cost'

-- 3. 确认两列均存在且类型未变
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'dwd'
  AND (
    (table_name = 'dim_store_goods' AND column_name = 'batch_stock_qty')
    OR
    (table_name = 'dim_store_goods_ex' AND column_name = 'provisional_total_cost')
  )
ORDER BY table_name, column_name;
-- 预期：batch_stock_qty → integer; provisional_total_cost → numeric(18,2)

-- 4. 重新加载后抽样验证 batch_stock_qty 来自 batch_stock_quantity（非 stock）
-- SELECT g.batch_stock_qty AS dwd_val,
--        o.batch_stock_quantity AS ods_batch,
--        o.stock AS ods_stock
-- FROM dwd.dim_store_goods g
-- JOIN (SELECT DISTINCT ON (id) * FROM ods.store_goods_master ORDER BY id, fetched_at DESC) o
--   ON g.site_goods_id = o.id
-- WHERE o.batch_stock_quantity IS DISTINCT FROM o.stock
-- LIMIT 20;
