-- Real device push notifications, phase 2 of the Home notification
-- feature (phase 1, migration 0043, was in-app only). Explicitly
-- requested by the user, including a real permission prompt.
--
-- push_subscriptions: one row per browser/device a user has enabled
-- push on (a user could have more than one -- phone + laptop). Self-
-- only RLS, same shape/reasoning as notification_reads (0043): holds
-- nothing sensitive beyond a push endpoint URL and its own per-device
-- encryption keys (meaningless without the server's VAPID private key
-- anyway), so a narrow self-only policy is safe. No admin bypass here
-- -- unlike notification_reads, nothing ever needs to preview another
-- person's subscriptions (View As stays read-only and never touches
-- push at all; previewing someone shouldn't subscribe THEIR phone to
-- anything, nor does the admin need to see whether they're subscribed).
--
-- The actual SEND path (src/app/api/cron/send-push/route.js) runs
-- through the service-role client on a Vercel Cron schedule, which
-- bypasses RLS entirely -- it has to read every user's subscriptions
-- to send to all of them, something no per-user RLS policy could
-- grant without effectively becoming a public-read policy.
--
-- last_pushed_at on notification_reads is a SEPARATE bookmark from
-- last_seen_at (0043) on purpose: last_seen_at only advances when
-- someone actually opens Home, but push has to fire for people who
-- HAVEN'T opened Home -- reusing last_seen_at would mean either never
-- pushing (if gated on "already seen in-app") or pushing the same
-- item forever (if not gated on anything). A distinct bookmark that
-- only the cron sender ever advances keeps the two concerns (in-app
-- badge-clearing vs. device push de-duplication) fully independent.

create table if not exists public.push_subscriptions (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid not null references auth.users(id) on delete cascade,
  endpoint    text not null unique,
  p256dh      text not null,
  auth        text not null,
  created_at  timestamptz not null default now()
);

alter table public.push_subscriptions enable row level security;

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

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

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

create policy "push_subscriptions_delete_own"
on public.push_subscriptions
for delete
to authenticated
using (user_id = auth.uid());

alter table public.notification_reads
  add column if not exists last_pushed_at timestamptz;

-- ============================================================
-- Self-verification guard
-- ============================================================

do $migration_guard$
declare
  policy_count int;
  col_count int;
begin
  select count(*) into policy_count from pg_policies where schemaname = 'public' and tablename = 'push_subscriptions';
  if policy_count <> 4 then
    raise exception 'Aborting 0045: expected 4 policies on push_subscriptions, found %', policy_count;
  end if;

  select count(*) into col_count from information_schema.columns
    where table_schema = 'public' and table_name = 'notification_reads' and column_name = 'last_pushed_at';
  if col_count <> 1 then
    raise exception 'Aborting 0045: expected last_pushed_at column on notification_reads, found %', col_count;
  end if;
end $migration_guard$;