begin;

alter table public.profiles
  add column if not exists deleted_at timestamptz;

create index if not exists profiles_available_donors_idx
  on public.profiles(created_at desc)
  where role = 'donor' and deleted_at is null;

create or replace function public.get_my_total_contributions()
returns bigint
language sql
stable
security invoker
set search_path = ''
as $$
  select coalesce(sum(transfer.amount), 0)::bigint
  from public.orphan_transfers transfer
  where exists (
    select 1
    from public.orphan_transfer_sponsors link
    where link.orphan_transfer_id = transfer.id
      and link.sponsor_id = (select auth.uid())
  );
$$;

comment on function public.get_my_total_contributions() is
  'Returns the signed-in donor total of orphan transfers linked to their account.';

revoke all on function public.get_my_total_contributions()
  from public, anon;
grant execute on function public.get_my_total_contributions()
  to authenticated;

notify pgrst, 'reload schema';

commit;