-- Suscripciones de notificaciones push (Web Push) por usuario (encargado o repartidor).
create table if not exists public.push_subs (
  id uuid primary key default gen_random_uuid(),
  perfil_id uuid not null references public.perfiles(id) on delete cascade,
  endpoint text not null unique,
  p256dh text not null,
  auth text not null,
  contexto text,
  creado_en timestamptz not null default now()
);
create index if not exists idx_push_subs_perfil on public.push_subs(perfil_id);

alter table public.push_subs enable row level security;
drop policy if exists push_subs_leer_propias on public.push_subs;
create policy push_subs_leer_propias on public.push_subs for select to authenticated using (perfil_id = auth.uid());
drop policy if exists push_subs_borrar_propias on public.push_subs;
create policy push_subs_borrar_propias on public.push_subs for delete to authenticated using (perfil_id = auth.uid());

-- Guarda/actualiza la suscripcion del usuario logueado (la Edge Function lee con service role).
create or replace function public.guardar_push_sub(p_endpoint text, p_p256dh text, p_auth text, p_contexto text default null)
returns void
language plpgsql
security definer
set search_path to 'public'
as $$
begin
  if auth.uid() is null then raise exception 'No autorizado'; end if;
  insert into public.push_subs (perfil_id, endpoint, p256dh, auth, contexto)
  values (auth.uid(), p_endpoint, p_p256dh, p_auth, p_contexto)
  on conflict (endpoint) do update
    set perfil_id = excluded.perfil_id,
        p256dh    = excluded.p256dh,
        auth      = excluded.auth,
        contexto  = excluded.contexto,
        creado_en = now();
end;
$$;
grant execute on function public.guardar_push_sub(text, text, text, text) to authenticated;