-- Add archiving for notifications
alter table notifications
  add column if not exists archived_at timestamptz;

create index if not exists idx_notifications_user_archived
  on notifications (user_id, archived_at, created_at desc);

-- Update unread count to ignore archived notifications
create or replace function get_unread_notification_count(p_user_id uuid)
returns integer
language plpgsql
stable
security definer
as $$
begin
  return (
    select count(*)::integer
    from notifications
    where user_id = p_user_id
      and is_read = false
      and archived_at is null
  );
end;
$$;