-- 全文搜索索引修复验证脚本
-- 在修复后运行此脚本验证IMMUTABLE函数错误是否已解决

-- ===================================================================
-- 1. 验证IMMUTABLE函数创建成功
-- ===================================================================

SELECT 'Testing IMMUTABLE functions...' as test_phase;

-- 测试标题搜索函数
SELECT 'Title function test: ' || content_title_to_tsvector('AI Technology Development')::text as result;

-- 测试内容搜索函数  
SELECT 'Content function test: ' || content_body_to_tsvector('Artificial Intelligence is changing the world')::text as result;

-- ===================================================================
-- 2. 验证索引创建状态
-- ===================================================================

SELECT 'Testing index creation...' as test_phase;

-- 检查全文搜索索引是否存在
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE schemaname = 'public' 
  AND indexname IN ('idx_contents_fts_title', 'idx_contents_fts_content');

-- ===================================================================
-- 3. 验证函数属性
-- ===================================================================

SELECT 'Testing function attributes...' as test_phase;

-- 检查函数是否标记为IMMUTABLE
SELECT 
    proname as function_name,
    provolatile as volatility,
    CASE provolatile 
        WHEN 'i' THEN 'IMMUTABLE ✓'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END as volatility_status
FROM pg_proc 
WHERE proname IN ('content_title_to_tsvector', 'content_body_to_tsvector');

-- ===================================================================
-- 4. 测试全文搜索功能
-- ===================================================================

SELECT 'Testing full-text search functionality...' as test_phase;

-- 如果ak_contents表有数据，测试搜索功能
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM ak_contents LIMIT 1) THEN
        -- 测试标题搜索
        RAISE NOTICE 'Title search test: %', 
            (SELECT COUNT(*) FROM ak_contents 
             WHERE content_title_to_tsvector(title) @@ to_tsquery('simple', 'test | demo | AI'));
        
        -- 测试内容搜索
        RAISE NOTICE 'Content search test: %', 
            (SELECT COUNT(*) FROM ak_contents 
             WHERE content_body_to_tsvector(content) @@ to_tsquery('simple', 'test | demo | AI'));
    ELSE
        RAISE NOTICE 'No data in ak_contents table to test search functionality';
    END IF;
END $$;

-- ===================================================================
-- 5. 性能测试 (可选)
-- ===================================================================

SELECT 'Testing query performance...' as test_phase;

-- 使用EXPLAIN分析索引使用情况
-- 注意：如果表中没有数据，这个查询可能不会使用索引
EXPLAIN (ANALYZE false, BUFFERS false) 
SELECT id, title 
FROM ak_contents 
WHERE content_title_to_tsvector(title) @@ to_tsquery('simple', 'AI');

-- ===================================================================
-- 6. 完成验证
-- ===================================================================

SELECT '✅ IMMUTABLE function fix verification completed!' as status;
SELECT 'If no errors appeared above, the fix was successful.' as note;
SELECT 'You can now safely deploy the database structure.' as recommendation;