-- Fix semantic search function to work with Row Level Security
-- The issue is that the function tries to JOIN with file_system_items which has RLS policies

-- Drop the existing function
DROP FUNCTION IF EXISTS semantic_search(UUID, vector, FLOAT, INTEGER);

-- Recreate as SECURITY DEFINER to bypass RLS for the JOIN
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  -- This allows the function to bypass RLS for the file_system_items JOIN
SET search_path = public
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;