-- 迁移：补录 cfg_skill_type 缺失的 3 条 skill_id
-- 原因：dwd_assistant_service_log 中存在 5525 条记录引用了不在 cfg_skill_type 中的 skill_id，
--       导致 WBI/NCI 的到店判定（settle_type=3 + BONUS EXISTS 检查）漏掉了这些到店记录。
--       典型案例：会员"梅"实际 5 天前到店，但 WBI 显示 16 天未到店，display_score 虚高至 9.42。
-- 影响：WBI、NCI、关系指数、助教服务统计、工资计算等所有依赖 cfg_skill_type JOIN 的下游。
-- 回滚：DELETE FROM dws.cfg_skill_type WHERE skill_id IN (2790683529513797, 2790683529513798, 3039912271463941);

BEGIN;

INSERT INTO dws.cfg_skill_type (skill_id, skill_name, course_type_code, course_type_name, is_active, description)
VALUES
    (2790683529513797, '基础课', 'BASE', '基础课', TRUE,
     '基础课：飞球系统原始课程类型，与"台球基础陪打"同类'),
    (2790683529513798, '附加课', 'BONUS', '附加课', TRUE,
     '附加课：飞球系统原始课程类型，与"台球超休服务"同类'),
    (3039912271463941, '包厢课', 'BASE', '基础课', TRUE,
     '包厢课：飞球系统原始课程类型，与"包厢服务"同类')
ON CONFLICT (skill_id) DO NOTHING;

COMMIT;

-- 验证 SQL（执行后应返回 6 行，且无 NULL 的 course_type_code）：
-- SELECT skill_id, skill_name, course_type_code, is_active FROM dws.cfg_skill_type ORDER BY skill_id;
--
-- 验证受影响会员数（应 > 0）：
-- SELECT COUNT(DISTINCT asl.tenant_member_id)
-- FROM dwd.dwd_assistant_service_log asl
-- JOIN dws.cfg_skill_type st ON asl.skill_id = st.skill_id
-- WHERE asl.skill_id IN (2790683529513797, 2790683529513798, 3039912271463941)
--   AND asl.is_delete = 0;
--
-- 验证梅的到店记录是否被正确识别：
-- SELECT s.order_settle_id, s.pay_time, s.settle_type,
--        EXISTS (
--            SELECT 1 FROM dwd.dwd_assistant_service_log asl
--            JOIN dws.cfg_skill_type st ON asl.skill_id = st.skill_id AND st.course_type_code = 'BONUS' AND st.is_active = TRUE
--            WHERE asl.order_settle_id = s.order_settle_id AND asl.site_id = s.site_id AND asl.tenant_member_id = s.member_id AND asl.is_delete = 0
--        ) AS has_bonus_service
-- FROM dwd.dwd_settlement_head s
-- WHERE s.member_id = 2975065345119045 AND s.pay_time >= '2026-03-08'
-- ORDER BY s.pay_time DESC;
