-- THIS IS FOR HUGGING FACE EMBEDDING MODEL: all-MiniLM-L6-V2
-- You need to change the dimension to suit the model you will be
--- making use to embed your text
--- 1536 works for OpenAI
--- 384 for all-MiniLM-L12-v2

-- Enable the pgvector extension to work with embedding vectors
create extension if not exists vector;


-- Create a table to store your document info
create table
  file_ref (
    id uuid primary key,
    user_id uuid references auth.users(id) on delete cascade,
    title text, -- corresponds to DocumentInfo.title
    description text -- corresponds to DocumentInfo.description
  );

-- Create a table to store your documents
create table
  documents (
    id uuid primary key,
    file_ref_id uuid references public.file_ref(id) on delete cascade,
    content text, -- corresponds to Document.pageContent
    metadata jsonb, -- corresponds to Document.metadata
    embedding vector (384) -- 1536 works for OpenAI embeddings, change if needed
  );

-- Create a function to search for documents
create function match_documents (
  query_embedding vector (384),
  filter jsonb default '{}'
) returns table (
  id uuid,
  content text,
  metadata jsonb,
  similarity float
) language plpgsql as $$
#variable_conflict use_column
begin
  return query
  select
    id,
    content,
    metadata,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where metadata @> filter
  order by documents.embedding <=> query_embedding;
end;
$$;