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

-- Create the thoughts table
create table thoughts (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  embedding vector(1536),
  metadata jsonb default '{}'::jsonb,
  parent_id uuid references thoughts(id) on delete cascade,
  chunk_index integer,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Enable Row Level Security (RLS)
alter table thoughts enable row level security;

-- Create indexes
-- HNSW index for fast vector similarity search (NEVER use IVFFlat for incrementally growing datasets)
create index on thoughts using hnsw (embedding vector_cosine_ops);

-- GIN index for fast JSONB metadata filtering
create index on thoughts using gin (metadata);

-- B-tree index for chronological sorting
create index on thoughts (created_at desc);

-- B-tree index for chunk lookups (partial index)
create index thoughts_parent_id on thoughts (parent_id) where parent_id is not null;

-- Create the match_thoughts function for semantic search
create or replace function match_thoughts(
  query_embedding vector(1536),
  match_threshold float default 0.1,
  match_count int default 10,
  filter jsonb default '{}'::jsonb
)
returns table (
  id uuid,
  content text,
  metadata jsonb,
  similarity float,
  created_at timestamptz,
  parent_id uuid,
  chunk_index integer
)
language plpgsql
as $$
begin
  return query
  select
    thoughts.id,
    thoughts.content,
    thoughts.metadata,
    1 - (thoughts.embedding <=> query_embedding) as similarity,
    thoughts.created_at,
    thoughts.parent_id,
    thoughts.chunk_index
  from thoughts
  where thoughts.metadata @> filter
    and 1 - (thoughts.embedding <=> query_embedding) > match_threshold
  order by thoughts.embedding <=> query_embedding
  limit match_count;
end;
$$;

-- Create the insert_thought function (bypasses PostgREST schema cache issues)
create or replace function insert_thought(
  p_content text,
  p_embedding vector(1536),
  p_metadata jsonb,
  p_parent_id uuid default null,
  p_chunk_index integer default null
)
returns uuid
language plpgsql as $$
declare new_id uuid;
begin
  insert into thoughts (content, embedding, metadata, parent_id, chunk_index)
  values (p_content, p_embedding, p_metadata, p_parent_id, p_chunk_index)
  returning id into new_id;
  return new_id;
end;
$$;
