crear_funcion_rpc_compatible.sql

-- Función RPC compatible con el esquema creado por Embeddings/
-- Esta función debe reemplazar la función actual search_legal_chunks

DROP FUNCTION IF EXISTS search_legal_chunks(vector, double precision, integer);

CREATE OR REPLACE FUNCTION search_legal_chunks(
    query_embedding vector(1024),
    match_threshold float DEFAULT 0.7,  -- Cambiado de 0.3 a 0.7 para mejor precisión
    match_count int DEFAULT 10          -- Aumentado de 5 a 10 para más resultados
)
RETURNS TABLE (
    chunk_id int,
    document_id character varying(255),
    content text,
    similarity float,
    metadata jsonb
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        lc.chunk_id,
        lc.document_id,
        lc.content,
        1 - (lc.embedding <=> query_embedding) as similarity,
        lc.metadata
    FROM legal_chunks lc
    WHERE 1 - (lc.embedding <=> query_embedding) > match_threshold
    ORDER BY lc.embedding <=> query_embedding
    LIMIT match_count;
END;
$$;

-- Función RPC para obtener metadatos completos con JOIN
CREATE OR REPLACE FUNCTION search_legal_chunks_with_metadata(
    query_embedding vector(1024),
    match_threshold float DEFAULT 0.7,
    match_count int DEFAULT 10
)
RETURNS TABLE (
    chunk_id int,
    document_id character varying(255),
    content text,
    similarity float,
    title text,
    state text,
    document_type text,
    file_path text,
    metadata jsonb
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        lc.chunk_id,
        lc.document_id,
        lc.content,
        1 - (lc.embedding <=> query_embedding) as similarity,
        ld.title,
        ld.state,
        ld.document_type,
        ld.file_path,
        lc.metadata
    FROM legal_chunks lc
    LEFT JOIN legal_documents ld ON lc.document_id = ld.document_id
    WHERE 1 - (lc.embedding <=> query_embedding) > match_threshold
    ORDER BY lc.embedding <=> query_embedding
    LIMIT match_count;
END;
$$;

-- Función RPC para búsqueda por estado
CREATE OR REPLACE FUNCTION search_legal_chunks_by_state(
    query_embedding vector(1024),
    estado text,
    match_threshold float DEFAULT 0.7,
    match_count int DEFAULT 10
)
RETURNS TABLE (
    chunk_id int,
    document_id character varying(255),
    content text,
    similarity float,
    title text,
    state text,
    document_type text,
    file_path text,
    metadata jsonb
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        lc.chunk_id,
        lc.document_id,
        lc.content,
        1 - (lc.embedding <=> query_embedding) as similarity,
        ld.title,
        ld.state,
        ld.document_type,
        ld.file_path,
        lc.metadata
    FROM legal_chunks lc
    LEFT JOIN legal_documents ld ON lc.document_id = ld.document_id
    WHERE 1 - (lc.embedding <=> query_embedding) > match_threshold
        AND ld.state = estado
    ORDER BY lc.embedding <=> query_embedding
    LIMIT match_count;
END;
$$;

-- Crear índices para optimizar búsquedas
CREATE INDEX IF NOT EXISTS legal_chunks_embedding_idx 
ON legal_chunks USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

CREATE INDEX IF NOT EXISTS legal_chunks_document_id_idx 
ON legal_chunks (document_id);

CREATE INDEX IF NOT EXISTS legal_documents_state_idx 
ON legal_documents (state);

CREATE INDEX IF NOT EXISTS legal_documents_document_type_idx 
ON legal_documents (document_type);

-- Comentarios
COMMENT ON FUNCTION search_legal_chunks IS 'Búsqueda vectorial básica compatible con el esquema de Embeddings/';
COMMENT ON FUNCTION search_legal_chunks_with_metadata IS 'Búsqueda vectorial con metadatos completos usando JOIN';
COMMENT ON FUNCTION search_legal_chunks_by_state IS 'Búsqueda vectorial filtrada por estado';