-- Fix vector operator issue in semantic search
-- The issue is that the <=> operator is not found, indicating pgvector extension issues

-- Ensure pgvector extension is available
CREATE EXTENSION IF NOT EXISTS vector;

-- Drop and recreate the semantic search function with proper vector handling
DROP FUNCTION IF EXISTS semantic_search(UUID, vector, FLOAT, INTEGER);

-- Recreate the function with explicit vector schema references
CREATE OR REPLACE FUNCTION semantic_search(
    search_book_id UUID,
    query_embedding vector(1536),
    similarity_threshold FLOAT DEFAULT 0.7,
    max_results INTEGER DEFAULT 20
)
RETURNS TABLE(
    chunk_id UUID,
    file_id UUID,
    file_name TEXT,
    content TEXT,
    similarity FLOAT,
    line_start INTEGER,
    line_end INTEGER
) 
SECURITY DEFINER
SET search_path = public, extensions
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        fc.id as chunk_id,
        fc.file_id,
        fsi.name as file_name,
        fc.content,
        (1 - (fc.embedding <-> query_embedding)) as similarity,
        fc.line_start,
        fc.line_end
    FROM file_chunks fc
    JOIN file_system_items fsi ON fc.file_id = fsi.id
    WHERE fc.book_id = search_book_id
    AND (1 - (fc.embedding <-> query_embedding)) > similarity_threshold
    ORDER BY fc.embedding <-> query_embedding
    LIMIT max_results;
END;
$$ LANGUAGE plpgsql;

-- Create vector index for better performance (if not exists)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'file_chunks' 
        AND indexname = 'idx_file_chunks_embedding'
    ) THEN
        CREATE INDEX idx_file_chunks_embedding ON file_chunks 
        USING ivfflat (embedding vector_cosine_ops) 
        WITH (lists = 100);
    END IF;
END $$;