-- 迁移：dws_assistant_monthly_summary 唯一约束变更
-- 日期：2026-02-22
-- 关联：需求 A（需求 2.2）— 助教月度聚合按档位分段统计
-- 影响：dws.dws_assistant_monthly_summary 唯一约束从 (site_id, assistant_id, stat_month)
--       变更为 (site_id, assistant_id, stat_month, assistant_level_code)
-- 原因：助教月内可能因升级/降级而存在多个 assistant_level_code，
--       需按档位分段统计业绩，旧约束不允许同一助教同月多行记录
-- 幂等：DROP IF EXISTS + 先检查再 ADD，可重复执行

BEGIN;

-- ============================================================
-- 1. 删除旧唯一约束
-- ============================================================
ALTER TABLE dws.dws_assistant_monthly_summary
    DROP CONSTRAINT IF EXISTS uk_dws_assistant_monthly;

-- ============================================================
-- 2. 创建新唯一约束（加入 assistant_level_code）
-- ============================================================
ALTER TABLE dws.dws_assistant_monthly_summary
    ADD CONSTRAINT uk_dws_assistant_monthly
    UNIQUE (site_id, assistant_id, stat_month, assistant_level_code);

COMMIT;

-- ============================================================
-- 回滚（需手动执行，不在事务内）
-- ============================================================
-- ⚠️ 回滚前须先清理同一 (site_id, assistant_id, stat_month) 的多行数据，
--    否则恢复旧约束会因重复键失败。
--
-- 步骤 1：清理多档位数据（保留每组最新一条）
-- DELETE FROM dws.dws_assistant_monthly_summary a
--  USING (
--      SELECT site_id, assistant_id, stat_month,
--             MAX(updated_at) AS keep_updated_at
--        FROM dws.dws_assistant_monthly_summary
--       GROUP BY site_id, assistant_id, stat_month
--  ) b
--  WHERE a.site_id = b.site_id
--    AND a.assistant_id = b.assistant_id
--    AND a.stat_month = b.stat_month
--    AND a.updated_at <> b.keep_updated_at;
--
-- 步骤 2：恢复旧约束
-- BEGIN;
-- ALTER TABLE dws.dws_assistant_monthly_summary
--     DROP CONSTRAINT IF EXISTS uk_dws_assistant_monthly;
-- ALTER TABLE dws.dws_assistant_monthly_summary
--     ADD CONSTRAINT uk_dws_assistant_monthly
--     UNIQUE (site_id, assistant_id, stat_month);
-- COMMIT;

-- ============================================================
-- 验证 SQL
-- ============================================================
-- 1. 确认新约束存在且列组合正确
-- SELECT conname, pg_get_constraintdef(oid) AS constraint_def
--   FROM pg_constraint
--  WHERE conrelid = 'dws.dws_assistant_monthly_summary'::regclass
--    AND conname = 'uk_dws_assistant_monthly';
-- 预期：1 行，constraint_def 包含 (site_id, assistant_id, stat_month, assistant_level_code)

-- 2. 确认约束列数为 4
-- SELECT COUNT(*) AS col_count
--   FROM pg_constraint c
--   JOIN LATERAL unnest(c.conkey) AS col_num ON TRUE
--  WHERE c.conrelid = 'dws.dws_assistant_monthly_summary'::regclass
--    AND c.conname = 'uk_dws_assistant_monthly';
-- 预期：4

-- 3. 确认旧的 3 列约束不存在（无同名但只有 3 列的约束）
-- SELECT conname, array_length(conkey, 1) AS col_count
--   FROM pg_constraint
--  WHERE conrelid = 'dws.dws_assistant_monthly_summary'::regclass
--    AND contype = 'u';
-- 预期：uk_dws_assistant_monthly 的 col_count = 4（非 3）
