begin;

-- One transfer represents one payment to an orphan. Sponsor visibility is
-- modeled separately so several active sponsors can share the same record
-- without duplicating the transferred amount or receipt.
create table public.orphan_transfer_sponsors (
  orphan_transfer_id uuid not null,
  sponsorship_id uuid not null,
  sponsor_id uuid not null,
  created_at timestamptz not null default now(),
  constraint orphan_transfer_sponsors_pkey
    primary key (orphan_transfer_id, sponsor_id),
  constraint orphan_transfer_sponsors_transfer_fkey
    foreign key (orphan_transfer_id)
    references public.orphan_transfers(id) on delete cascade,
  constraint orphan_transfer_sponsors_sponsorship_fkey
    foreign key (sponsorship_id)
    references public.sponsorships(id) on delete restrict,
  constraint orphan_transfer_sponsors_sponsor_id_fkey
    foreign key (sponsor_id)
    references public.profiles(id) on delete restrict,
  constraint orphan_transfer_sponsors_transfer_relationship_key
    unique (orphan_transfer_id, sponsorship_id)
);

create index orphan_transfer_sponsors_sponsor_idx
  on public.orphan_transfer_sponsors(sponsor_id, orphan_transfer_id);
create index orphan_transfer_sponsors_sponsorship_idx
  on public.orphan_transfer_sponsors(sponsorship_id);

-- Preserve the original sponsor and extend every existing transfer to every
-- currently active sponsor of the same orphan.
insert into public.orphan_transfer_sponsors (
  orphan_transfer_id,
  sponsorship_id,
  sponsor_id
)
select transfer.id, transfer.sponsorship_id, transfer.sponsor_id
from public.orphan_transfers transfer
where transfer.sponsorship_id is not null
  and transfer.sponsor_id is not null
on conflict do nothing;

insert into public.orphan_transfer_sponsors (
  orphan_transfer_id,
  sponsorship_id,
  sponsor_id
)
select transfer.id, sponsorship.id, sponsorship.donor_id
from public.orphan_transfers transfer
join public.sponsorships sponsorship
  on sponsorship.orphan_id = transfer.orphan_id
 and sponsorship.status = 'active'
on conflict do nothing;

drop policy if exists orphan_transfers_read on public.orphan_transfers;
drop policy if exists transfer_receipts_read on public.transfer_receipts;
drop policy if exists file_assets_read on public.file_assets;

drop trigger if exists orphan_transfer_links_valid on public.orphan_transfers;
drop function if exists private.validate_orphan_transfer_links();
drop function if exists public.create_orphan_transfer(
  uuid, bigint, date, public.transfer_status, text, text, text, uuid, jsonb
);

alter table public.orphan_transfers
  drop column sponsor_monthly_payment_id,
  drop column sponsorship_id,
  drop column sponsor_id;

create or replace function public.create_orphan_transfer(
  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 $$
declare
  created_transfer_id uuid;
  created_file_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.orphans where id = p_orphan_id
  ) then
    raise exception 'orphan not found';
  end if;

  select count(*)
  into linked_sponsor_count
  from public.sponsorships sponsorship
  where sponsorship.orphan_id = p_orphan_id
    and sponsorship.status = 'active';

  if linked_sponsor_count = 0 then
    raise exception 'no active sponsors';
  end if;

  insert into public.orphan_transfers (
    orphan_id,
    transfer_year,
    transfer_month,
    amount,
    transfer_date,
    reference,
    sponsor_visible_notes,
    internal_notes,
    created_by
  )
  values (
    p_orphan_id,
    extract(year from p_transfer_date)::smallint,
    extract(month from p_transfer_date)::smallint,
    p_amount,
    p_transfer_date,
    nullif(trim(p_reference), ''),
    nullif(trim(p_sponsor_visible_notes), ''),
    nullif(trim(p_internal_notes), ''),
    p_actor
  )
  returning id into created_transfer_id;

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

  if p_receipt is not null then
    if p_receipt ->> 'extension' not in ('jpg', 'jpeg', 'png', 'webp')
      or p_receipt ->> 'mime_type' not in ('image/jpeg', 'image/png', 'image/webp')
      or p_receipt ->> 'sha256' !~ '^[a-f0-9]{64}$'
    then
      raise exception 'invalid transfer receipt';
    end if;

    insert into public.file_assets (
      stored_name,
      original_name,
      extension,
      mime_type,
      size_bytes,
      category,
      uploaded_by,
      sha256
    )
    values (
      (p_receipt ->> 'stored_name')::uuid,
      p_receipt ->> 'original_name',
      p_receipt ->> 'extension',
      p_receipt ->> 'mime_type',
      (p_receipt ->> 'size_bytes')::bigint,
      'transfer_receipt',
      p_actor,
      p_receipt ->> 'sha256'
    )
    returning id into created_file_id;

    insert into public.transfer_receipts (
      orphan_transfer_id,
      file_id,
      uploaded_by
    )
    values (created_transfer_id, created_file_id, p_actor);
  end if;

  return query
  select created_transfer_id, created_file_id, linked_sponsor_count;
end;
$$;

create or replace function public.delete_sponsor_monthly_payment(
  p_payment_id uuid,
  p_actor uuid
)
returns void
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;

  delete from public.sponsor_monthly_payments
  where id = p_payment_id;
end;
$$;

alter table public.orphan_transfer_sponsors enable row level security;

create policy orphan_transfer_sponsors_read
on public.orphan_transfer_sponsors
for select to authenticated
using (
  private.is_admin()
  or (
    sponsor_id = (select auth.uid())
    and exists (
      select 1
      from public.profiles
      where id = (select auth.uid()) and is_active = true
    )
  )
);
create policy orphan_transfer_sponsors_insert_admin
on public.orphan_transfer_sponsors
for insert to authenticated
with check (private.is_admin());
create policy orphan_transfer_sponsors_update_admin
on public.orphan_transfer_sponsors
for update to authenticated
using (private.is_admin())
with check (private.is_admin());
create policy orphan_transfer_sponsors_delete_admin
on public.orphan_transfer_sponsors
for delete to authenticated
using (private.is_admin());

create policy orphan_transfers_read
on public.orphan_transfers
for select to authenticated
using (
  private.is_admin()
  or (
    exists (
      select 1
      from public.profiles
      where id = (select auth.uid()) and is_active = true
    )
    and exists (
      select 1
      from public.orphan_transfer_sponsors link
      where link.orphan_transfer_id = id
        and link.sponsor_id = (select auth.uid())
    )
  )
);

create policy transfer_receipts_read
on public.transfer_receipts
for select to authenticated
using (
  private.is_admin()
  or exists (
    select 1
    from public.orphan_transfer_sponsors link
    join public.profiles sponsor
      on sponsor.id = link.sponsor_id
    where link.orphan_transfer_id = orphan_transfer_id
      and link.sponsor_id = (select auth.uid())
      and sponsor.is_active = true
  )
);

create policy file_assets_read
on public.file_assets
for select to authenticated
using (
  private.is_admin()
  or exists (
    select 1
    from public.orphan_documents document
    where document.file_id = public.file_assets.id
      and private.donor_may_view_documents(document.orphan_id)
  )
  or exists (
    select 1
    from public.transfer_receipts receipt
    join public.orphan_transfer_sponsors link
      on link.orphan_transfer_id = receipt.orphan_transfer_id
    join public.profiles sponsor
      on sponsor.id = link.sponsor_id
    where receipt.file_id = public.file_assets.id
      and link.sponsor_id = (select auth.uid())
      and sponsor.is_active = true
  )
);

revoke all on public.orphan_transfer_sponsors from anon, authenticated;
grant select on public.orphan_transfer_sponsors to authenticated;
grant all on public.orphan_transfer_sponsors to service_role;

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

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

notify pgrst, 'reload schema';

commit;
