CREATE TABLE chats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id TEXT NOT NULL UNIQUE,
  messages JSONB NOT NULL DEFAULT '[]',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_chats_session_id ON chats(session_id);
ALTER TABLE chats ENABLE ROW LEVEL SECURITY;

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

CREATE TABLE site_embeddings (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  content TEXT NOT NULL,
  embedding extensions.vector(1536),
  page_url TEXT NOT NULL,
  page_title TEXT,
  section_heading TEXT,
  chunk_index INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX site_embeddings_embedding_idx
  ON site_embeddings USING hnsw (embedding vector_cosine_ops);

ALTER TABLE site_embeddings ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION match_site_content(
  query_embedding extensions.vector(1536),
  match_threshold FLOAT DEFAULT 0.3,
  match_count INT DEFAULT 10
)
RETURNS TABLE (
  id BIGINT, content TEXT, page_url TEXT,
  page_title TEXT, section_heading TEXT, similarity FLOAT
)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT se.id, se.content, se.page_url, se.page_title, se.section_heading,
         1 - (se.embedding <=> query_embedding) AS similarity
  FROM site_embeddings se
  WHERE 1 - (se.embedding <=> query_embedding) > match_threshold
  ORDER BY se.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;