-- ============================================================================
-- Delivery receipts — "✓✓" tick in WhatsApp/Telegram parlance: the recipient's
-- device has fetched the message from the server, regardless of whether they
-- have actually opened the conversation yet. `message_reads` already covers
-- the "read" step; this table fills the gap between "sent" and "read".
--
-- A row exists iff the recipient user has acknowledged receiving the message
-- on at least one device. Writes are triggered client-side after successful
-- message fetch/decrypt. Select policy mirrors read receipts (opt-out via
-- profiles.show_read_receipts on BOTH ends).
-- ============================================================================
create table public.message_deliveries (
  message_id    uuid not null references public.messages(id) on delete cascade,
  user_id       uuid not null references auth.users(id) on delete cascade,
  delivered_at  timestamptz not null default now(),
  primary key (message_id, user_id)
);

alter table public.message_deliveries enable row level security;

create policy deliveries_select_reciprocal on public.message_deliveries
  for select to authenticated
  using (
    user_id = auth.uid()
    or (
      coalesce(
        (select show_read_receipts from public.profiles where profiles.user_id = auth.uid()),
        true
      )
      and coalesce(
        (select show_read_receipts from public.profiles where profiles.user_id = message_deliveries.user_id),
        true
      )
      and exists (
        select 1 from public.messages m
        where m.id = message_id and public.is_conversation_member(m.conversation_id)
      )
    )
  );

create policy deliveries_insert_own on public.message_deliveries
  for insert to authenticated
  with check (
    user_id = auth.uid()
    and exists (
      select 1 from public.messages m
      where m.id = message_id and public.is_conversation_member(m.conversation_id)
    )
  );

create index message_deliveries_message_id_idx on public.message_deliveries (message_id);
create index message_deliveries_user_id_idx on public.message_deliveries (user_id);

alter publication supabase_realtime add table public.message_deliveries;