-- =============================================================================
-- 迁移脚本：dws_assistant_salary_calc 唯一约束变更
-- 说明：配合档位分段工资计算，唯一键加入 assistant_level_code
-- 日期：2026-02-22
-- 关联需求：需求 A（助教月度聚合按档位分段统计）— 任务 7.4
-- =============================================================================

-- ---- 正向迁移 ----

-- 1. 删除旧唯一约束
ALTER TABLE dws.dws_assistant_salary_calc
    DROP CONSTRAINT IF EXISTS uk_dws_assistant_salary;

-- 2. 创建新唯一约束（加入 assistant_level_code）
ALTER TABLE dws.dws_assistant_salary_calc
    ADD CONSTRAINT uk_dws_assistant_salary
    UNIQUE (site_id, assistant_id, salary_month, assistant_level_code);

-- ---- 回滚 ----
-- ALTER TABLE dws.dws_assistant_salary_calc
--     DROP CONSTRAINT IF EXISTS uk_dws_assistant_salary;
-- ALTER TABLE dws.dws_assistant_salary_calc
--     ADD CONSTRAINT uk_dws_assistant_salary
--     UNIQUE (site_id, assistant_id, salary_month);

-- ---- 验证 SQL ----
-- 1. 确认新约束存在且包含 4 列
-- SELECT conname, array_agg(a.attname ORDER BY x.n)
-- FROM pg_constraint c
-- JOIN pg_class r ON r.oid = c.conrelid
-- JOIN pg_namespace ns ON ns.oid = r.relnamespace
-- CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
-- JOIN pg_attribute a ON a.attrelid = r.oid AND a.attnum = x.attnum
-- WHERE ns.nspname = 'dws' AND r.relname = 'dws_assistant_salary_calc'
--   AND c.contype = 'u'
-- GROUP BY conname;
-- 预期: uk_dws_assistant_salary | {site_id,assistant_id,salary_month,assistant_level_code}

-- 2. 确认旧约束不存在（只有一个唯一约束）
-- SELECT count(*) FROM pg_constraint c
-- JOIN pg_class r ON r.oid = c.conrelid
-- JOIN pg_namespace ns ON ns.oid = r.relnamespace
-- WHERE ns.nspname = 'dws' AND r.relname = 'dws_assistant_salary_calc'
--   AND c.contype = 'u';
-- 预期: 1

-- 3. 测试插入同一助教同月不同档位不冲突
-- INSERT INTO dws.dws_assistant_salary_calc (site_id, tenant_id, assistant_id, salary_month, assistant_level_code)
-- VALUES (1, 1, 100, '2026-01-01', 10), (1, 1, 100, '2026-01-01', 20);
-- 预期: 成功插入 2 行
-- DELETE FROM dws.dws_assistant_salary_calc WHERE site_id = 1 AND assistant_id = 100 AND salary_month = '2026-01-01';
