begin;

create or replace function public.create_orphan_transfer_for_sponsor(
  p_sponsor_id uuid,
  p_orphan_id uuid,
  p_amount bigint,
  p_transfer_date date,
  p_reference text,
  p_sponsor_visible_notes text,
  p_internal_notes text,
  p_actor uuid,
  p_receipt jsonb
)
returns table(transfer_id uuid, new_file_id uuid, sponsor_count integer)
language plpgsql
security definer
set search_path = ''
as $$
begin
  if not exists (
    select 1
    from public.profiles
    where id = p_actor and role = 'admin' and is_active = true
  ) then
    raise exception 'not authorized';
  end if;

  if not exists (
    select 1
    from public.sponsorships sponsorship
    where sponsorship.donor_id = p_sponsor_id
      and sponsorship.orphan_id = p_orphan_id
      and sponsorship.status = 'active'
  ) then
    raise exception 'sponsor orphan mismatch';
  end if;

  return query
  select created.transfer_id, created.new_file_id, created.sponsor_count
  from public.create_orphan_transfer(
    p_orphan_id,
    p_amount,
    p_transfer_date,
    p_reference,
    p_sponsor_visible_notes,
    p_internal_notes,
    p_actor,
    p_receipt
  ) created;
end;
$$;

create or replace function public.update_orphan_transfer(
  p_transfer_id uuid,
  p_sponsor_id uuid,
  p_orphan_id uuid,
  p_amount bigint,
  p_transfer_date date,
  p_reference text,
  p_sponsor_visible_notes text,
  p_internal_notes text,
  p_actor uuid
)
returns table(previous_orphan_id uuid, sponsor_count integer)
language plpgsql
security definer
set search_path = ''
as $$
declare
  original_orphan_id uuid;
  linked_sponsor_count integer;
begin
  if not exists (
    select 1
    from public.profiles
    where id = p_actor and role = 'admin' and is_active = true
  ) then
    raise exception 'not authorized';
  end if;

  if not exists (
    select 1
    from public.sponsorships sponsorship
    where sponsorship.donor_id = p_sponsor_id
      and sponsorship.orphan_id = p_orphan_id
      and sponsorship.status = 'active'
  ) then
    raise exception 'sponsor orphan mismatch';
  end if;

  select transfer.orphan_id
  into original_orphan_id
  from public.orphan_transfers transfer
  where transfer.id = p_transfer_id
  for update;

  if not found then
    raise exception 'transfer not found';
  end if;

  update public.orphan_transfers
  set
    orphan_id = p_orphan_id,
    transfer_year = extract(year from p_transfer_date)::smallint,
    transfer_month = extract(month from p_transfer_date)::smallint,
    amount = p_amount,
    transfer_date = p_transfer_date,
    reference = nullif(trim(p_reference), ''),
    sponsor_visible_notes = nullif(trim(p_sponsor_visible_notes), ''),
    internal_notes = nullif(trim(p_internal_notes), '')
  where id = p_transfer_id;

  if original_orphan_id is distinct from p_orphan_id then
    delete from public.orphan_transfer_sponsors
    where orphan_transfer_id = p_transfer_id;

    insert into public.orphan_transfer_sponsors (
      orphan_transfer_id,
      sponsorship_id,
      sponsor_id
    )
    select p_transfer_id, sponsorship.id, sponsorship.donor_id
    from public.sponsorships sponsorship
    where sponsorship.orphan_id = p_orphan_id
      and sponsorship.status = 'active';
  end if;

  select count(*)
  into linked_sponsor_count
  from public.orphan_transfer_sponsors link
  where link.orphan_transfer_id = p_transfer_id;

  return query
  select original_orphan_id, linked_sponsor_count;
end;
$$;

revoke all on function public.create_orphan_transfer_for_sponsor(
  uuid, uuid, bigint, date, text, text, text, uuid, jsonb
) from public, anon, authenticated;
grant execute on function public.create_orphan_transfer_for_sponsor(
  uuid, uuid, bigint, date, text, text, text, uuid, jsonb
) to service_role;

revoke all on function public.update_orphan_transfer(
  uuid, uuid, uuid, bigint, date, text, text, text, uuid
) from public, anon, authenticated;
grant execute on function public.update_orphan_transfer(
  uuid, uuid, uuid, bigint, date, text, text, text, uuid
) to service_role;

commit;
