-- Per-user "last seen" bookmark for the Home dashboard's notification
-- feed (payday, new QA score, new schedule, Trustpilot update -- see
-- src/app/home/page.js). Deliberately a brand-new, tiny table rather
-- than a column on `profiles`: profiles has had NO self-update policy
-- at all since 0008, by design ("provisioned via service role until a
-- real admin-management flow exists") -- profiles also holds is_admin/
-- is_broad_reviewer/agent_id, so a self-update policy there would be a
-- real privilege-escalation surface. This table holds nothing sensitive
-- (just a timestamp), so a narrow, brand-new self-only policy is safe.
--
-- Admin gets a SELECT-only bypass (not update) so View As previewing --
-- read-only everywhere, per this session's own governing rule -- can
-- show what the previewed person would actually see instead of the
-- real admin's own bookmark. Writing this row while previewing is
-- blocked client-side (isPreviewing guard in home/page.js), same as
-- every other write in the View As rollout.

create table if not exists public.notification_reads (
  user_id       uuid primary key references auth.users(id) on delete cascade,
  last_seen_at  timestamptz not null default now()
);

alter table public.notification_reads enable row level security;

create policy "notification_reads_select_own"
on public.notification_reads
for select
to authenticated
using (user_id = auth.uid());

create policy "notification_reads_select_admin"
on public.notification_reads
for select
to authenticated
using (
  exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

create policy "notification_reads_insert_own"
on public.notification_reads
for insert
to authenticated
with check (user_id = auth.uid());

create policy "notification_reads_update_own"
on public.notification_reads
for update
to authenticated
using (user_id = auth.uid())
with check (user_id = auth.uid());