-- 备选方案：不使用自定义函数的全文搜索索引
-- 如果IMMUTABLE函数方法失败，可以使用此备选方案

-- ===================================================================
-- 方案1: 使用表达式索引 (推荐)
-- ===================================================================

-- 删除可能存在的有问题的索引
DROP INDEX IF EXISTS public.idx_contents_fts_title;
DROP INDEX IF EXISTS public.idx_contents_fts_content;

-- 删除可能有问题的函数
DROP FUNCTION IF EXISTS public.content_title_to_tsvector(TEXT);
DROP FUNCTION IF EXISTS public.content_body_to_tsvector(TEXT);

-- 方法1: 使用简单的表达式索引
CREATE INDEX IF NOT EXISTS idx_contents_fts_title_simple 
ON public.ak_contents USING gin((to_tsvector('simple', title)));

CREATE INDEX IF NOT EXISTS idx_contents_fts_content_simple 
ON public.ak_contents USING gin((to_tsvector('simple', content)));

-- ===================================================================
-- 方案2: 使用预计算列 (最佳性能)
-- ===================================================================

-- 添加预计算的tsvector列
DO $$
BEGIN
    -- 检查列是否已存在
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'ak_contents' 
          AND column_name = 'title_search_vector'
    ) THEN
        ALTER TABLE public.ak_contents 
        ADD COLUMN title_search_vector tsvector;
    END IF;
    
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'ak_contents' 
          AND column_name = 'content_search_vector'
    ) THEN
        ALTER TABLE public.ak_contents 
        ADD COLUMN content_search_vector tsvector;
    END IF;
END
$$;

-- 更新现有数据的搜索向量
UPDATE public.ak_contents SET 
    title_search_vector = to_tsvector('simple', COALESCE(title, '')),
    content_search_vector = to_tsvector('simple', COALESCE(content, ''))
WHERE title_search_vector IS NULL OR content_search_vector IS NULL;

-- 创建触发器自动更新搜索向量
CREATE OR REPLACE FUNCTION public.update_content_search_vectors()
RETURNS trigger AS $$
BEGIN
    NEW.title_search_vector := to_tsvector('simple', COALESCE(NEW.title, ''));
    NEW.content_search_vector := to_tsvector('simple', COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
DROP TRIGGER IF EXISTS trig_update_content_search_vectors ON public.ak_contents;
CREATE TRIGGER trig_update_content_search_vectors
    BEFORE INSERT OR UPDATE ON public.ak_contents
    FOR EACH ROW EXECUTE FUNCTION public.update_content_search_vectors();

-- 在预计算列上创建索引
CREATE INDEX IF NOT EXISTS idx_contents_title_search_vector 
ON public.ak_contents USING gin(title_search_vector);

CREATE INDEX IF NOT EXISTS idx_contents_content_search_vector 
ON public.ak_contents USING gin(content_search_vector);

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

-- 使用方案1的查询方式 (表达式索引)
/*
SELECT id, title FROM ak_contents 
WHERE to_tsvector('simple', title) @@ to_tsquery('simple', 'AI');

SELECT id, title FROM ak_contents 
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', 'technology');
*/

-- 使用方案2的查询方式 (预计算列)
/*
SELECT id, title FROM ak_contents 
WHERE title_search_vector @@ to_tsquery('simple', 'AI');

SELECT id, title FROM ak_contents 
WHERE content_search_vector @@ to_tsquery('simple', 'technology');

-- 组合搜索并按相关性排序
SELECT 
    id, title,
    ts_rank(title_search_vector, query) + ts_rank(content_search_vector, query) as rank
FROM ak_contents, to_tsquery('simple', 'AI & technology') query
WHERE title_search_vector @@ query OR content_search_vector @@ query
ORDER BY rank DESC;
*/