-- 清理数据库重复数据

-- 1. 清理 positions 表的重复数据
-- 保留每个 (body_id, time) 组合的最新一条记录

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. 清理 nasa_cache 表的重复数据
-- 保留每个 cache_key 的最新一条记录

WITH duplicates AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY cache_key
               ORDER BY created_at DESC
           ) as rn
    FROM nasa_cache
)
DELETE FROM nasa_cache
WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

-- 3. 验证清理结果
SELECT 'Positions duplicates check' as check_name,
       COUNT(*) - COUNT(DISTINCT (body_id, time)) as duplicate_count
FROM positions
UNION ALL
SELECT 'NASA cache duplicates check' as check_name,
       COUNT(*) - COUNT(DISTINCT cache_key) as duplicate_count
FROM nasa_cache;