-- Vion Training — notifications & scheduling schema (Phase 5/6)
-- Run this in the Supabase SQL Editor AFTER db/schema.sql. Safe to re-run.

-- ──────────────────────────────────────────────────────────────────────────
-- scheduled_sessions — a rep's planned training slots (drives 5h reminders).
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.scheduled_sessions (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null references public.profiles(id) on delete cascade,
  scenario_id   text,                              -- optional: a specific scenario to practice
  scheduled_at  timestamptz not null,              -- when the rep plans to train
  note          text,
  reminder_sent boolean not null default false,    -- flipped once the 5h email goes out
  created_at    timestamptz not null default now()
);
create index if not exists scheduled_user_idx on public.scheduled_sessions(user_id);
create index if not exists scheduled_at_idx   on public.scheduled_sessions(scheduled_at);

alter table public.scheduled_sessions enable row level security;

-- Owner manages their own slots; admins can read all.
drop policy if exists scheduled_owner_all on public.scheduled_sessions;
create policy scheduled_owner_all on public.scheduled_sessions
  for all using (user_id = auth.uid()) with check (user_id = auth.uid());

drop policy if exists scheduled_admin_read on public.scheduled_sessions;
create policy scheduled_admin_read on public.scheduled_sessions
  for select using (public.is_admin());

-- ──────────────────────────────────────────────────────────────────────────
-- email_log — idempotency guard so cron retries never double-send an email.
-- Written only server-side (service key); no client access.
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.email_log (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid references public.profiles(id) on delete cascade,
  kind       text not null,                        -- 'session' | 'weekly' | 'reminder'
  ref        text not null,                        -- session id / week key / scheduled id
  created_at timestamptz not null default now(),
  unique (kind, ref)
);
alter table public.email_log enable row level security;  -- no policies => only service key reaches it

-- ──────────────────────────────────────────────────────────────────────────
-- Grants for the new tables (mirror db/schema.sql section 6).
-- ──────────────────────────────────────────────────────────────────────────
grant all privileges on public.scheduled_sessions, public.email_log to service_role;
grant select, insert, update, delete on public.scheduled_sessions to authenticated;