-- 验证IMMUTABLE错误修复
-- 此脚本检查主数据库结构是否能成功创建，不包含任何可能导致IMMUTABLE错误的索引

SELECT 'Testing database structure without IMMUTABLE function issues...' as test_status;

-- ===================================================================
-- 1. 检查表是否存在
-- ===================================================================

SELECT 'Checking tables...' as step;

SELECT 
    COUNT(*) as table_count,
    CASE 
        WHEN COUNT(*) >= 20 THEN '✓ Tables created successfully'
        ELSE '⚠ Some tables may be missing'
    END as status
FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name LIKE 'ak_%';

-- ===================================================================
-- 2. 检查索引是否创建成功
-- ===================================================================

SELECT 'Checking indexes...' as step;

SELECT 
    COUNT(*) as index_count,
    CASE 
        WHEN COUNT(*) >= 15 THEN '✓ Indexes created successfully'
        ELSE '⚠ Some indexes may be missing'
    END as status
FROM pg_indexes 
WHERE schemaname = 'public' AND indexname LIKE 'idx_%';

-- ===================================================================
-- 3. 检查是否有全文搜索索引（应该没有）
-- ===================================================================

SELECT 'Checking full-text search indexes (should be empty)...' as step;

SELECT 
    COUNT(*) as fts_index_count,
    CASE 
        WHEN COUNT(*) = 0 THEN '✓ No problematic FTS indexes found'
        ELSE '⚠ Found FTS indexes that might cause issues'
    END as status
FROM pg_indexes 
WHERE schemaname = 'public' 
  AND (indexdef LIKE '%to_tsvector%' OR indexdef LIKE '%gin%to_%');

-- ===================================================================
-- 4. 检查基本索引是否存在
-- ===================================================================

SELECT 'Checking basic indexes...' as step;

SELECT 
    indexname,
    '✓ Available' as status
FROM pg_indexes 
WHERE schemaname = 'public' 
  AND indexname IN (
    'idx_contents_title_text',
    'idx_contents_category', 
    'idx_contents_published',
    'idx_contents_status'
  );

-- ===================================================================
-- 5. 测试基本查询功能
-- ===================================================================

SELECT 'Testing basic query functionality...' as step;

-- 测试简单文本搜索（不使用全文搜索）
DO $$
BEGIN
    -- 检查ak_contents表是否可以查询
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ak_contents') THEN
        RAISE NOTICE '✓ ak_contents table is queryable';
        
        -- 测试基本的文本搜索功能
        PERFORM count(*) FROM ak_contents WHERE title ILIKE '%test%';
        RAISE NOTICE '✓ Basic text search with ILIKE works';
        
    ELSE
        RAISE NOTICE '⚠ ak_contents table not found';
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '⚠ Error testing queries: %', SQLERRM;
END
$$;

-- ===================================================================
-- 6. 显示可用的搜索方法
-- ===================================================================

SELECT 'Available search methods:' as info;

SELECT '1. Case-insensitive text search:' as method,
       'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'';' as example
UNION ALL
SELECT '2. Multiple field search:' as method,
       'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'' OR content ILIKE ''%keyword%'';' as example
UNION ALL  
SELECT '3. Combined with other filters:' as method,
       'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'' AND status = ''published'' ORDER BY published_at DESC;' as example;

SELECT '✅ Database structure verification completed!' as final_status;
SELECT 'The database should now be free of IMMUTABLE function errors.' as note;
SELECT 'For advanced full-text search, run fulltext_search_optional.sql separately.' as recommendation;