-- ============================================================
-- 图片 URL 迁移：/images/drugs/ → /images/
-- ============================================================
-- 执行方式:
--   docker exec -i pharmacopoeia-pg psql -U postgres -d pharmacopoeia < database/migrate_image_urls.sql
--
-- 回滚方式:
--   将下面 SQL 中的 /images/" 替换为 /images/drugs/" 再执行一遍
-- ============================================================

BEGIN;

-- 1. 更新 drugs.sections (jsonb) 中的 <img> 标签 URL
--    sections 是 jsonb，需要 text → regexp_replace → jsonb 来回转
UPDATE drugs
SET sections = regexp_replace(sections::text, '/images/drugs/', '/images/', 'g')::jsonb
WHERE sections::text LIKE '%/images/drugs/%';

-- 2. 更新 drug_chunks.content (text) 中的 <img> 标签 URL
UPDATE drug_chunks
SET content = replace(content, '/images/drugs/', '/images/')
WHERE content LIKE '%/images/drugs/%';

-- 3. 验证：确认无残留旧 URL
DO $$
DECLARE
    drugs_old INT;
    chunks_old INT;
BEGIN
    SELECT COUNT(*) INTO drugs_old FROM drugs
    WHERE sections::text LIKE '%/images/drugs/%';
    SELECT COUNT(*) INTO chunks_old FROM drug_chunks
    WHERE content LIKE '%/images/drugs/%';

    RAISE NOTICE '旧 URL 残留: drugs=%  chunks=%', drugs_old, chunks_old;

    IF drugs_old > 0 OR chunks_old > 0 THEN
        RAISE WARNING '仍有旧 URL 残留，请检查!';
    ELSE
        RAISE NOTICE '✅ 迁移完成，无旧 URL 残留';
    END IF;
END $$;

COMMIT;