-- ============================================================
-- 图片 URL 迁移（Python 版迁移脚本的配套 SQL）
-- ============================================================
-- 此 SQL 文件仅供回退参考，实际迁移通过 Python 脚本执行：
--   cd /opt/pharmacopoeia-ai/data-pipeline && source venv/bin/activate && python migrate_image_urls.py
-- ============================================================

-- 查询当前旧 URL 数据量
SELECT 'drugs' AS tbl, COUNT(*) FROM drugs WHERE sections::text LIKE '%/images/drugs/%'
UNION ALL
SELECT 'drug_chunks', COUNT(*) FROM drug_chunks WHERE content LIKE '%/images/drugs/%';

-- 手动回滚（极端情况下）：
-- UPDATE drugs SET sections = regexp_replace(sections::text, '/images/([a-f0-9]{8})_', '/images/drugs/', 'g')::jsonb
--   WHERE sections::text LIKE '%/images/%';
-- UPDATE drug_chunks SET content = regexp_replace(content, '/images/([a-f0-9]{8})_', '/images/drugs/', 'g')
--   WHERE content LIKE '%/images/%' AND content NOT LIKE '%/images/drugs/%';