-- 为 positions 表添加唯一约束
-- 这样 ON CONFLICT 才能正常工作

-- 1. 先删除现有的重复数据（如果有）
WITH duplicates AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY body_id, time
               ORDER BY created_at DESC
           ) as rn
    FROM positions
)
DELETE FROM positions
WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

-- 2. 添加唯一约束
ALTER TABLE positions
ADD CONSTRAINT positions_body_time_unique
UNIQUE (body_id, time);

-- 3. 验证约束已创建
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'positions'
AND constraint_type = 'UNIQUE';