-- 迁移：为 dwd_assistant_service_log_ex 新增 operator_id、operator_name 两列
-- 关联需求：dataflow-field-completion Requirements 2.1, 2.2
-- 日期：2026-02-20

BEGIN;

-- 1. 新增列
ALTER TABLE dwd.dwd_assistant_service_log_ex
    ADD COLUMN IF NOT EXISTS operator_id BIGINT;

ALTER TABLE dwd.dwd_assistant_service_log_ex
    ADD COLUMN IF NOT EXISTS operator_name TEXT;

-- 2. 列注释
COMMENT ON COLUMN dwd.dwd_assistant_service_log_ex.operator_id IS
    '【说明】操作员 ID，录入/结算这条助教服务的员工。     【ODS来源】assistant_service_records - operator_id。     【JSON字段】assistant_service_records.json - data.orderAssistantDetails - operator_id。';

COMMENT ON COLUMN dwd.dwd_assistant_service_log_ex.operator_name IS
    '【说明】操作员姓名（带职位前缀），与 operator_id 一起使用，便于直接阅读。     【ODS来源】assistant_service_records - operator_name。     【JSON字段】assistant_service_records.json - data.orderAssistantDetails - operator_name。';

COMMIT;

-- ============================================================
-- 回滚策略
-- ============================================================
-- BEGIN;
-- ALTER TABLE dwd.dwd_assistant_service_log_ex DROP COLUMN IF EXISTS operator_id;
-- ALTER TABLE dwd.dwd_assistant_service_log_ex DROP COLUMN IF EXISTS operator_name;
-- COMMIT;

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

-- 1. 确认 2 个新列均已创建且类型正确
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'dwd'
--   AND table_name = 'dwd_assistant_service_log_ex'
--   AND column_name IN ('operator_id', 'operator_name')
-- ORDER BY column_name;

-- 2. 确认列注释已添加
-- SELECT column_name,
--        col_description(
--            (SELECT oid FROM pg_class WHERE relname = 'dwd_assistant_service_log_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 = 'dwd_assistant_service_log_ex'
--       AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dwd'))
--   AND attname IN ('operator_id', 'operator_name');

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

-- 4. 重新加载后抽样验证新列有数据
-- SELECT operator_id, operator_name
-- FROM dwd.dwd_assistant_service_log_ex
-- WHERE operator_id IS NOT NULL
-- LIMIT 10;
