-- 迁移：为 dwd.dim_assistant_ex 新增 4 个字段
-- 来源：assistant_accounts_master ODS 表中已有同名列，直接映射
-- 关联需求：dataflow-field-completion Requirements 1.1, 1.2, 1.3

BEGIN;

-- 1. 新增列
ALTER TABLE dwd.dim_assistant_ex ADD COLUMN IF NOT EXISTS system_role_id BIGINT;
ALTER TABLE dwd.dim_assistant_ex ADD COLUMN IF NOT EXISTS job_num TEXT;
ALTER TABLE dwd.dim_assistant_ex ADD COLUMN IF NOT EXISTS cx_unit_price NUMERIC(18,2);
ALTER TABLE dwd.dim_assistant_ex ADD COLUMN IF NOT EXISTS pd_unit_price NUMERIC(18,2);

-- 2. 添加列注释
COMMENT ON COLUMN dwd.dim_assistant_ex.system_role_id IS
    '【说明】系统角色 ID，标识助教在系统中的角色类型。     【ODS来源】assistant_accounts_master - system_role_id。     【JSON字段】assistant_accounts_master.json - data.assistantInfos - system_role_id。';
COMMENT ON COLUMN dwd.dim_assistant_ex.job_num IS
    '【说明】工号，助教的内部编号标识。     【ODS来源】assistant_accounts_master - job_num。     【JSON字段】assistant_accounts_master.json - data.assistantInfos - job_num。';
COMMENT ON COLUMN dwd.dim_assistant_ex.cx_unit_price IS
    '【说明】促销单价（元），助教提供促销服务时的计费单价。     【ODS来源】assistant_accounts_master - cx_unit_price。     【JSON字段】assistant_accounts_master.json - data.assistantInfos - cx_unit_price。';
COMMENT ON COLUMN dwd.dim_assistant_ex.pd_unit_price IS
    '【说明】陪打单价（元），助教提供陪打服务时的计费单价。     【ODS来源】assistant_accounts_master - pd_unit_price。     【JSON字段】assistant_accounts_master.json - data.assistantInfos - pd_unit_price。';

COMMIT;

-- ============================================================
-- 回滚策略
-- ============================================================
-- BEGIN;
-- ALTER TABLE dwd.dim_assistant_ex DROP COLUMN IF EXISTS system_role_id;
-- ALTER TABLE dwd.dim_assistant_ex DROP COLUMN IF EXISTS job_num;
-- ALTER TABLE dwd.dim_assistant_ex DROP COLUMN IF EXISTS cx_unit_price;
-- ALTER TABLE dwd.dim_assistant_ex DROP COLUMN IF EXISTS pd_unit_price;
-- COMMIT;

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

-- 1. 确认 4 个新列均已创建且类型正确
-- SELECT column_name, data_type, numeric_precision, numeric_scale
-- FROM information_schema.columns
-- WHERE table_schema = 'dwd'
--   AND table_name = 'dim_assistant_ex'
--   AND column_name IN ('system_role_id', 'job_num', 'cx_unit_price', 'pd_unit_price')
-- ORDER BY column_name;

-- 2. 确认列注释已添加
-- SELECT column_name,
--        col_description(
--            (SELECT oid FROM pg_class WHERE relname = 'dim_assistant_ex'
--             AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd')),
--            attnum
--        ) AS comment
-- FROM pg_attribute
-- WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'dim_assistant_ex'
--       AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd'))
--   AND attname IN ('system_role_id', 'job_num', 'cx_unit_price', 'pd_unit_price');

-- 3. 确认 ODS 源表中同名列存在
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'ods'
--   AND table_name = 'assistant_accounts_master'
--   AND column_name IN ('system_role_id', 'job_num', 'cx_unit_price', 'pd_unit_price')
-- ORDER BY column_name;

-- 4. 重新加载后抽样验证新列有数据
-- SELECT system_role_id, job_num, cx_unit_price, pd_unit_price
-- FROM dwd.dim_assistant_ex
-- WHERE scd2_is_current = 1
-- LIMIT 10;
