drop index if exists memories_embedding_idx;

alter table public.memories
    drop column if exists embedding;

alter table public.memories
    add column embedding vector,
    add column embedding_dimensions integer;

create index memories_embedding_idx on public.memories using hnsw (embedding vector_cosine_ops);

drop function if exists public.search_memories(vector(1536), float, int, uuid, uuid);

create or replace function public.search_memories(
    query_embedding vector,
    match_threshold float default 0.7,
    match_count int default 10,
    filter_project_id uuid default null,
    filter_folder_id uuid default null,
    required_dimensions int default null
)
returns table (
    id uuid,
    content text,
    project_id uuid,
    folder_id uuid,
    tags jsonb,
    metadata jsonb,
    similarity float,
    created_at timestamptz,
    updated_at timestamptz
)
language plpgsql
security definer
as $$
declare
    query_dimensions int;
begin
    query_dimensions := array_length(query_embedding::float[], 1);

    return query
    select
        m.id,
        m.content,
        m.project_id,
        m.folder_id,
        m.tags,
        m.metadata,
        1 - (m.embedding <=> query_embedding) as similarity,
        m.created_at,
        m.updated_at
    from public.memories m
    where m.user_id = auth.uid()
        and m.embedding is not null
        and m.embedding_dimensions = query_dimensions
        and 1 - (m.embedding <=> query_embedding) > match_threshold
        and (filter_project_id is null or m.project_id = filter_project_id)
        and (filter_folder_id is null or m.folder_id = filter_folder_id)
    order by m.embedding <=> query_embedding
    limit match_count;
end;
$$;