136 lines
4.1 KiB
PL/PgSQL
136 lines
4.1 KiB
PL/PgSQL
-- =============================================================
|
|
-- TABLERO CDC — Notificación personal de briefs "Nuevos"
|
|
-- Fecha: 2026-08-12
|
|
--
|
|
-- Ejecutar SOLO si ya habías ejecutado una versión anterior de
|
|
-- supabase_cdc_brief_review_inbox.sql. Si aún no la habías ejecutado,
|
|
-- usa el archivo completo actualizado y no necesitas este delta.
|
|
-- =============================================================
|
|
|
|
begin;
|
|
|
|
create table if not exists public.tablero_cdc_brief_inbox_seen (
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
inbox_id uuid not null references public.tablero_cdc_brief_inbox(id) on delete cascade,
|
|
seen_at timestamptz not null default now(),
|
|
primary key (user_id, inbox_id)
|
|
);
|
|
|
|
create index if not exists tablero_cdc_brief_inbox_seen_inbox_idx
|
|
on public.tablero_cdc_brief_inbox_seen (inbox_id);
|
|
|
|
alter table public.tablero_cdc_brief_inbox_seen enable row level security;
|
|
|
|
drop policy if exists "tablero_cdc_brief_inbox_seen_select_own"
|
|
on public.tablero_cdc_brief_inbox_seen;
|
|
|
|
create policy "tablero_cdc_brief_inbox_seen_select_own"
|
|
on public.tablero_cdc_brief_inbox_seen
|
|
for select
|
|
to authenticated
|
|
using (
|
|
user_id = auth.uid()
|
|
and public.tablero_cdc_is_active_allowed_user()
|
|
);
|
|
|
|
revoke insert, update, delete on public.tablero_cdc_brief_inbox_seen from authenticated;
|
|
grant select on public.tablero_cdc_brief_inbox_seen to authenticated;
|
|
grant all on public.tablero_cdc_brief_inbox_seen to service_role;
|
|
|
|
create or replace function public.tablero_cdc_get_unseen_brief_count()
|
|
returns integer
|
|
language plpgsql
|
|
stable
|
|
security definer
|
|
set search_path = public, auth
|
|
as $$
|
|
declare
|
|
v_count integer;
|
|
begin
|
|
if auth.uid() is null or not public.tablero_cdc_is_active_allowed_user() then
|
|
raise exception 'No tienes permiso para consultar notificaciones de briefs.';
|
|
end if;
|
|
|
|
select count(*)::integer
|
|
into v_count
|
|
from public.tablero_cdc_brief_inbox i
|
|
where i.review_status = 'new'
|
|
and not exists (
|
|
select 1
|
|
from public.tablero_cdc_brief_inbox_seen s
|
|
where s.user_id = auth.uid()
|
|
and s.inbox_id = i.id
|
|
);
|
|
|
|
return coalesce(v_count, 0);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.tablero_cdc_get_unseen_brief_count() from public;
|
|
grant execute on function public.tablero_cdc_get_unseen_brief_count() to authenticated;
|
|
|
|
create or replace function public.tablero_cdc_mark_briefs_seen()
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public, auth
|
|
as $$
|
|
declare
|
|
v_marked integer := 0;
|
|
v_remaining integer := 0;
|
|
begin
|
|
if auth.uid() is null or not public.tablero_cdc_is_active_allowed_user() then
|
|
raise exception 'No tienes permiso para marcar briefs como vistos.';
|
|
end if;
|
|
|
|
insert into public.tablero_cdc_brief_inbox_seen (user_id, inbox_id, seen_at)
|
|
select auth.uid(), i.id, now()
|
|
from public.tablero_cdc_brief_inbox i
|
|
where i.review_status = 'new'
|
|
on conflict (user_id, inbox_id) do nothing;
|
|
|
|
get diagnostics v_marked = row_count;
|
|
|
|
select count(*)::integer
|
|
into v_remaining
|
|
from public.tablero_cdc_brief_inbox i
|
|
where i.review_status = 'new'
|
|
and not exists (
|
|
select 1
|
|
from public.tablero_cdc_brief_inbox_seen s
|
|
where s.user_id = auth.uid()
|
|
and s.inbox_id = i.id
|
|
);
|
|
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'marked_count', v_marked,
|
|
'unseen_count', coalesce(v_remaining, 0)
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.tablero_cdc_mark_briefs_seen() from public;
|
|
grant execute on function public.tablero_cdc_mark_briefs_seen() to authenticated;
|
|
|
|
do $$
|
|
begin
|
|
if exists (select 1 from pg_publication where pubname = 'supabase_realtime')
|
|
and not exists (
|
|
select 1
|
|
from pg_publication_tables
|
|
where pubname = 'supabase_realtime'
|
|
and schemaname = 'public'
|
|
and tablename = 'tablero_cdc_brief_inbox_seen'
|
|
) then
|
|
alter publication supabase_realtime add table public.tablero_cdc_brief_inbox_seen;
|
|
end if;
|
|
end $$;
|
|
|
|
commit;
|
|
|
|
select
|
|
to_regclass('public.tablero_cdc_brief_inbox_seen') as tabla_estado_visto,
|
|
to_regprocedure('public.tablero_cdc_get_unseen_brief_count()') as rpc_contador,
|
|
to_regprocedure('public.tablero_cdc_mark_briefs_seen()') as rpc_marcar_vistos;
|