-- Function to debug similarity scores
-- This will help us see what similarity values are being calculated

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;