-- Migration: Clean up excessive attachments, dirty bodies, and orphan test sources

-- 1. Cap excessive historical attachments per announcement (keep newest 50 per announcement)
DELETE FROM attachment
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY announcement_id ORDER BY id DESC) AS rn
        FROM attachment
    ) t
    WHERE t.rn > 50
)
AND id NOT IN (SELECT attachment_id FROM "position" WHERE attachment_id IS NOT NULL);

-- 2. Clean dirty, empty, or anti-crawler body texts and clear associated hashes
UPDATE announcement 
SET body_text = NULL, simhash = NULL, content_hash = NULL 
WHERE body_text = '' 
   OR (LENGTH(body_text) < 120 AND body_text ~* '\.(pdf|docx?|xlsx?|zip)$')
   OR LENGTH(body_text) < 60
   OR body_text LIKE '%环境异常%' 
   OR body_text LIKE '%完成验证%';

-- 3. Delete orphan test sources with 0 announcements
DELETE FROM source 
WHERE id IN ('test_auth', 'yn.ynrsksw.special8')
  AND NOT EXISTS (SELECT 1 FROM announcement WHERE announcement.source_id = source.id);