-- 测试IMMUTABLE函数修复
-- 运行此脚本检查函数是否正确创建为IMMUTABLE

-- ===================================================================
-- 1. 检查函数是否存在并且是IMMUTABLE
-- ===================================================================

SELECT 'Checking IMMUTABLE functions...' as status;

-- 检查函数存在性和IMMUTABLE属性
SELECT 
    proname as function_name,
    provolatile as volatility_code,
    CASE provolatile 
        WHEN 'i' THEN 'IMMUTABLE ✓'
        WHEN 's' THEN 'STABLE ❌'
        WHEN 'v' THEN 'VOLATILE ❌'
    END as volatility_status,
    prosrc as function_body
FROM pg_proc 
WHERE proname IN ('content_title_to_tsvector', 'content_body_to_tsvector')
AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- ===================================================================
-- 2. 测试函数功能
-- ===================================================================

SELECT 'Testing function functionality...' as status;

-- 测试函数是否能正常工作
SELECT 
    'Title function test' as test_name,
    content_title_to_tsvector('AI Technology News')::text as result;

SELECT 
    'Content function test' as test_name,
    content_body_to_tsvector('Artificial Intelligence is transforming the world')::text as result;

-- ===================================================================
-- 3. 检查索引状态
-- ===================================================================

SELECT 'Checking index status...' as status;

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

-- ===================================================================
-- 4. 手动创建索引 (如果自动创建失败)
-- ===================================================================

SELECT 'Manual index creation (if needed)...' as status;

-- 如果索引不存在，手动创建
DO $$
BEGIN
    -- 检查并创建标题索引
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname = 'idx_contents_fts_title' AND n.nspname = 'public'
    ) THEN
        RAISE NOTICE '创建标题全文搜索索引...';
        EXECUTE 'CREATE INDEX idx_contents_fts_title ON public.ak_contents USING gin(content_title_to_tsvector(title))';
        RAISE NOTICE '标题索引创建成功';
    ELSE
        RAISE NOTICE '标题索引已存在';
    END IF;
    
    -- 检查并创建内容索引
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname = 'idx_contents_fts_content' AND n.nspname = 'public'
    ) THEN
        RAISE NOTICE '创建内容全文搜索索引...';
        EXECUTE 'CREATE INDEX idx_contents_fts_content ON public.ak_contents USING gin(content_body_to_tsvector(content))';
        RAISE NOTICE '内容索引创建成功';
    ELSE
        RAISE NOTICE '内容索引已存在';
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '索引创建时发生错误: %', SQLERRM;
        RAISE NOTICE '错误代码: %', SQLSTATE;
END
$$;

-- ===================================================================
-- 5. 最终验证
-- ===================================================================

SELECT 'Final verification...' as status;

-- 再次检查索引
SELECT 
    COUNT(*) as index_count,
    CASE 
        WHEN COUNT(*) = 2 THEN '✓ 所有索引创建成功'
        ELSE '❌ 索引创建不完整'
    END as status
FROM pg_indexes 
WHERE schemaname = 'public' 
  AND indexname IN ('idx_contents_fts_title', 'idx_contents_fts_content');

SELECT '✅ IMMUTABLE函数测试完成' as final_status;