-- Revert to the correct vector operator for cosine distance
-- Change from <-> (L2 distance) back to <=> (cosine distance)

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

-- Recreate the function with the <=> operator
CREATE OR REPLACE FUNCTION semantic_search(
    search_book_id UUID,
    query_embedding vector(1536),
    similarity_threshold FLOAT DEFAULT 0.01,
    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; 

-- Also update the debug function to use the correct operator
DROP FUNCTION IF EXISTS debug_similarity(UUID, vector);

CREATE OR REPLACE FUNCTION debug_similarity(
    search_book_id UUID,
    query_embedding vector(1536)
)
RETURNS TABLE (
    chunk_id UUID,
    file_name TEXT,
    content_preview TEXT,
    similarity FLOAT,
    distance FLOAT
) 
SECURITY DEFINER
SET search_path = public, extensions
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        fc.id as chunk_id,
        fsi.name as file_name,
        LEFT(fc.content, 100) as content_preview,
        (1 - (fc.embedding <=> query_embedding)) as similarity,
        (fc.embedding <=> query_embedding) as distance
    FROM file_chunks fc
    JOIN file_system_items fsi ON fc.file_id = fsi.id
    WHERE fc.book_id = search_book_id
    ORDER BY distance ASC;
END;
$$ LANGUAGE plpgsql;