-- 可选的全文搜索功能添加脚本
-- 此脚本应在主数据库结构成功创建后单独执行
-- 如果遇到IMMUTABLE函数错误，可以跳过此脚本

-- ===================================================================
-- 重要提示：
-- 1. 请确保主数据库结构已成功创建
-- 2. 如果此脚本执行失败，不会影响系统的基本功能
-- 3. 可以使用简单的LIKE/ILIKE查询作为替代方案
-- ===================================================================

SELECT 'Starting optional full-text search setup...' as status;

-- ===================================================================
-- 方案1: 尝试创建简单的全文搜索索引
-- ===================================================================

DO $$
BEGIN
    BEGIN
        -- 尝试创建全文搜索索引
        CREATE INDEX IF NOT EXISTS idx_contents_title_fts 
        ON public.ak_contents USING gin(to_tsvector('simple', title));
        
        RAISE NOTICE '✓ 标题全文搜索索引创建成功';
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '⚠ 标题全文搜索索引创建失败: %', SQLERRM;
    END;
    
    BEGIN
        CREATE INDEX IF NOT EXISTS idx_contents_content_fts 
        ON public.ak_contents USING gin(to_tsvector('simple', content));
        
        RAISE NOTICE '✓ 内容全文搜索索引创建成功';
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '⚠ 内容全文搜索索引创建失败: %', SQLERRM;
    END;
END
$$;

-- ===================================================================
-- 方案2: 如果方案1失败，尝试创建IMMUTABLE函数包装器
-- ===================================================================

DO $$
BEGIN
    -- 只有在方案1的索引不存在时才尝试方案2
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = 'idx_contents_title_fts'
    ) THEN
        BEGIN
            -- 创建IMMUTABLE函数
            CREATE OR REPLACE FUNCTION public.safe_title_to_tsvector(title TEXT)
            RETURNS tsvector
            LANGUAGE sql IMMUTABLE STRICT
            AS $function$
                SELECT to_tsvector('simple', COALESCE(title, ''));
            $function$;
            
            -- 使用IMMUTABLE函数创建索引
            CREATE INDEX IF NOT EXISTS idx_contents_title_safe_fts 
            ON public.ak_contents USING gin(safe_title_to_tsvector(title));
            
            RAISE NOTICE '✓ 使用IMMUTABLE函数的标题索引创建成功';
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE '⚠ IMMUTABLE函数方案也失败: %', SQLERRM;
        END;
    END IF;
    
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = 'idx_contents_content_fts'
    ) THEN
        BEGIN
            CREATE OR REPLACE FUNCTION public.safe_content_to_tsvector(content TEXT)
            RETURNS tsvector
            LANGUAGE sql IMMUTABLE STRICT
            AS $function$
                SELECT to_tsvector('simple', COALESCE(content, ''));
            $function$;
            
            CREATE INDEX IF NOT EXISTS idx_contents_content_safe_fts 
            ON public.ak_contents USING gin(safe_content_to_tsvector(content));
            
            RAISE NOTICE '✓ 使用IMMUTABLE函数的内容索引创建成功';
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE '⚠ IMMUTABLE函数方案也失败: %', SQLERRM;
        END;
    END IF;
END
$$;

-- ===================================================================
-- 方案3: 预计算搜索向量列 (最佳性能，如果上述方案都失败)
-- ===================================================================

DO $$
BEGIN
    -- 只有在前面的索引都不存在时才尝试方案3
    IF NOT EXISTS (
        SELECT 1 FROM pg_class 
        WHERE relname IN ('idx_contents_title_fts', 'idx_contents_title_safe_fts')
    ) THEN
        BEGIN
            RAISE NOTICE '尝试方案3: 预计算搜索向量列...';
            
            -- 添加搜索向量列
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns 
                WHERE table_name = 'ak_contents' AND column_name = 'search_vector'
            ) THEN
                ALTER TABLE public.ak_contents ADD COLUMN search_vector tsvector;
            END IF;
            
            -- 创建更新函数
            CREATE OR REPLACE FUNCTION update_search_vector()
            RETURNS trigger AS $trigger$
            BEGIN
                NEW.search_vector := 
                    setweight(to_tsvector('simple', COALESCE(NEW.title, '')), 'A') ||
                    setweight(to_tsvector('simple', COALESCE(NEW.content, '')), 'B');
                RETURN NEW;
            END;
            $trigger$ LANGUAGE plpgsql;
            
            -- 创建触发器
            DROP TRIGGER IF EXISTS trig_update_search_vector ON public.ak_contents;
            CREATE TRIGGER trig_update_search_vector
                BEFORE INSERT OR UPDATE ON public.ak_contents
                FOR EACH ROW EXECUTE FUNCTION update_search_vector();
            
            -- 更新现有数据
            UPDATE public.ak_contents SET 
                search_vector = setweight(to_tsvector('simple', COALESCE(title, '')), 'A') ||
                               setweight(to_tsvector('simple', COALESCE(content, '')), 'B')
            WHERE search_vector IS NULL;
            
            -- 在搜索向量列上创建索引
            CREATE INDEX IF NOT EXISTS idx_contents_search_vector 
            ON public.ak_contents USING gin(search_vector);
            
            RAISE NOTICE '✓ 预计算搜索向量方案创建成功';
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE '⚠ 预计算搜索向量方案失败: %', SQLERRM;
        END;
    END IF;
END
$$;

-- ===================================================================
-- 验证和使用说明
-- ===================================================================

-- 检查哪些全文搜索索引创建成功了
SELECT 'Checking created full-text search indexes...' as status;

SELECT 
    indexname,
    indexdef,
    '✓ Available' as status
FROM pg_indexes 
WHERE schemaname = 'public' 
  AND (indexname LIKE '%_fts' OR indexname LIKE '%search_vector%');

-- ===================================================================
-- 查询示例
-- ===================================================================

SELECT 'Full-text search query examples:' as info;

-- 方案1和2的查询方式
/*
-- 如果创建了 idx_contents_title_fts 或 idx_contents_title_safe_fts
SELECT id, title FROM ak_contents 
WHERE to_tsvector('simple', title) @@ to_tsquery('simple', 'AI');

-- 或者使用IMMUTABLE函数（如果存在）
SELECT id, title FROM ak_contents 
WHERE safe_title_to_tsvector(title) @@ to_tsquery('simple', 'AI');
*/

-- 方案3的查询方式
/*
-- 如果创建了search_vector列和索引
SELECT id, title, ts_rank(search_vector, query) as rank
FROM ak_contents, to_tsquery('simple', 'AI') query
WHERE search_vector @@ query
ORDER BY rank DESC;
*/

-- 备用方案：简单文本搜索（总是可用）
/*
SELECT id, title FROM ak_contents WHERE title ILIKE '%AI%';
SELECT id, title FROM ak_contents WHERE content ILIKE '%technology%';
SELECT id, title FROM ak_contents 
WHERE title ILIKE '%AI%' OR content ILIKE '%AI%'
ORDER BY published_at DESC;
*/

SELECT '✅ Optional full-text search setup completed!' as final_status;
SELECT 'Check the messages above to see which search methods are available.' as note;