-- ============================================================================
-- Docent AI — Supabase schema for authentication-backed chat history
-- Run this ONCE in your Supabase project:
--   Dashboard → SQL Editor → New query → paste this file → Run
-- ============================================================================

-- ── Conversations (one row per chat) ───────────────────────────────────────
create table if not exists public.chat_sessions (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid not null references auth.users (id) on delete cascade,
  title      text not null default 'New chat',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ── Messages (user + assistant turns) ──────────────────────────────────────
create table if not exists public.messages (
  id         uuid primary key default gen_random_uuid(),
  session_id uuid not null references public.chat_sessions (id) on delete cascade,
  role       text not null check (role in ('user', 'assistant')),
  content    text not null,
  sources    jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

-- User feedback on assistant answers: 'up', 'down', or null
alter table public.messages add column if not exists feedback text;

create index if not exists idx_sessions_user    on public.chat_sessions (user_id, updated_at desc);
create index if not exists idx_messages_session on public.messages (session_id, created_at);

-- ── Row Level Security ──────────────────────────────────────────────────────
-- The backend uses the service-role key (which bypasses RLS), but we still
-- enable RLS so the public anon key can never read another user's data.
alter table public.chat_sessions enable row level security;
alter table public.messages      enable row level security;

drop policy if exists "own sessions" on public.chat_sessions;
create policy "own sessions" on public.chat_sessions
  for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "own messages" on public.messages;
create policy "own messages" on public.messages
  for all
  using (exists (
    select 1 from public.chat_sessions s
    where s.id = messages.session_id and s.user_id = auth.uid()
  ))
  with check (exists (
    select 1 from public.chat_sessions s
    where s.id = messages.session_id and s.user_id = auth.uid()
  ));