begin;

do $$
begin
  create type public.transfer_status as enum ('pending', 'paid');
exception
  when duplicate_object then null;
end;
$$;

-- Every manually entered orphan field is optional. System-owned identity,
-- case number, timestamps, status, and coverage remain managed by PostgreSQL.
alter table public.orphans
  alter column full_name drop not null,
  alter column date_of_birth drop not null,
  alter column gender drop not null,
  alter column sponsor_notes drop not null,
  alter column sponsor_notes drop default,
  alter column guardian_name drop not null,
  alter column guardian_phone drop not null,
  alter column guardian_relationship drop not null,
  alter column address drop not null,
  alter column bank_name drop not null,
  alter column bank_name drop default,
  alter column bank_account_name drop not null,
  alter column bank_account_number drop not null,
  alter column monthly_need drop not null,
  alter column monthly_need drop default;

update public.orphans
set full_name = nullif(trim(full_name), ''),
    sponsor_notes = nullif(trim(sponsor_notes), ''),
    private_notes = nullif(trim(private_notes), ''),
    guardian_name = nullif(trim(guardian_name), ''),
    guardian_phone = nullif(trim(guardian_phone), ''),
    guardian_relationship = nullif(trim(guardian_relationship), ''),
    address = nullif(trim(address), ''),
    bank_name = nullif(trim(bank_name), ''),
    bank_account_name = nullif(trim(bank_account_name), ''),
    bank_account_number = nullif(trim(bank_account_number), '');

alter table public.orphans
  add column normalized_full_name text generated always as (
    lower(regexp_replace(coalesce(full_name, ''), '[[:space:][:punct:]]+', '', 'g'))
  ) stored,
  add column normalized_guardian_name text generated always as (
    lower(regexp_replace(coalesce(guardian_name, ''), '[[:space:][:punct:]]+', '', 'g'))
  ) stored,
  add column normalized_guardian_phone text generated always as (
    regexp_replace(coalesce(guardian_phone, ''), '[^0-9]+', '', 'g')
  ) stored,
  add column normalized_bank_account_number text generated always as (
    lower(regexp_replace(coalesce(bank_account_number, ''), '[[:space:][:punct:]]+', '', 'g'))
  ) stored;

create index orphans_duplicate_name_dob_idx
  on public.orphans(normalized_full_name, date_of_birth)
  where normalized_full_name <> '' and date_of_birth is not null;
create index orphans_duplicate_name_phone_idx
  on public.orphans(normalized_full_name, normalized_guardian_phone)
  where normalized_full_name <> '' and normalized_guardian_phone <> '';
create index orphans_duplicate_guardian_phone_idx
  on public.orphans(normalized_guardian_name, normalized_guardian_phone)
  where normalized_guardian_name <> '' and normalized_guardian_phone <> '';
create index orphans_duplicate_bank_idx
  on public.orphans(normalized_bank_account_number)
  where normalized_bank_account_number <> '';

alter table public.file_assets
  add column sha256 text;
alter table public.file_assets
  add constraint file_assets_sha256_format
  check (sha256 is null or sha256 ~ '^[a-f0-9]{64}$');
create index file_assets_sha256_idx on public.file_assets(sha256) where sha256 is not null;

-- Null monthly need is a valid incomplete case. Coverage still records active
-- commitments without inventing a required amount.
create or replace function private.refresh_orphan_coverage(target_orphan uuid)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  total bigint;
  need bigint;
  existing_status public.orphan_status;
begin
  select monthly_need, status into need, existing_status
  from public.orphans where id = target_orphan for update;

  select coalesce(sum(monthly_amount), 0) into total
  from public.sponsorships
  where orphan_id = target_orphan and status = 'active';

  update public.orphans
  set covered_amount = total,
      status = case
        when existing_status in ('paused', 'archived') then existing_status
        when total <= 0 then 'available'::public.orphan_status
        when need is null then 'partially_covered'::public.orphan_status
        when total < need then 'partially_covered'::public.orphan_status
        else 'fully_covered'::public.orphan_status
      end
  where id = target_orphan;
end;
$$;

-- Retain anything that cannot be mapped to a single sponsor safely. These rows
-- are admin-only and contain metadata references, never file contents.
create table public.legacy_financial_review (
  id uuid primary key default gen_random_uuid(),
  record_type text not null,
  orphan_id uuid references public.orphans(id) on delete cascade,
  legacy_monthly_support_id uuid,
  file_id uuid references public.file_assets(id) on delete set null,
  payload jsonb not null default '{}'::jsonb,
  review_status text not null default 'needs_review'
    check (review_status in ('needs_review', 'resolved')),
  created_at timestamptz not null default now(),
  resolved_at timestamptz,
  resolved_by uuid references public.profiles(id) on delete set null
);

-- Remove policies/functions that depend on the old transfer-receipt shape.
drop policy if exists file_assets_read on public.file_assets;
drop policy if exists transfer_receipts_read on public.transfer_receipts;
drop policy if exists transfer_receipts_insert_admin on public.transfer_receipts;
drop policy if exists transfer_receipts_update_admin on public.transfer_receipts;
drop policy if exists transfer_receipts_delete_admin on public.transfer_receipts;
drop trigger if exists payment_refresh_totals on public.payment_records;
drop function if exists private.after_payment_change();
drop function if exists private.refresh_monthly_totals(uuid);
drop function if exists public.create_monthly_support(smallint, smallint, uuid);

-- Keep the old rows intact under explicit legacy names until an administrator
-- reviews and archives them. No application page reads these tables afterward.
alter table public.monthly_support_records rename to legacy_monthly_support_records;
alter table public.monthly_contributor_snapshots rename to legacy_monthly_contributor_snapshots;
alter table public.payment_records rename to legacy_payment_records;

create table public.sponsor_monthly_payments (
  id uuid primary key default gen_random_uuid(),
  sponsorship_id uuid not null references public.sponsorships(id) on delete restrict,
  sponsor_id uuid not null references public.profiles(id) on delete restrict,
  orphan_id uuid not null references public.orphans(id) on delete restrict,
  payment_year smallint not null check (payment_year between 2020 and 2200),
  payment_month smallint not null check (payment_month between 1 and 12),
  expected_amount_snapshot bigint not null check (expected_amount_snapshot >= 0),
  received_amount bigint not null default 0 check (received_amount >= 0),
  payment_status public.payment_status not null default 'unpaid',
  payment_date date,
  internal_notes text,
  confirmed_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (sponsorship_id, payment_year, payment_month),
  constraint sponsor_payment_date_required
    check (received_amount = 0 or payment_date is not null)
);

create index sponsor_monthly_payments_period_idx
  on public.sponsor_monthly_payments(payment_year desc, payment_month desc);
create index sponsor_monthly_payments_sponsor_idx
  on public.sponsor_monthly_payments(sponsor_id, payment_year desc, payment_month desc);
create index sponsor_monthly_payments_orphan_idx
  on public.sponsor_monthly_payments(orphan_id, payment_year desc, payment_month desc);
create index sponsor_monthly_payments_status_idx
  on public.sponsor_monthly_payments(payment_status, payment_year desc, payment_month desc);

create table public.orphan_transfers (
  id uuid primary key default gen_random_uuid(),
  sponsor_monthly_payment_id uuid not null
    references public.sponsor_monthly_payments(id) on delete restrict,
  sponsorship_id uuid not null references public.sponsorships(id) on delete restrict,
  sponsor_id uuid not null references public.profiles(id) on delete restrict,
  orphan_id uuid not null references public.orphans(id) on delete restrict,
  transfer_year smallint not null check (transfer_year between 2020 and 2200),
  transfer_month smallint not null check (transfer_month between 1 and 12),
  amount bigint not null check (amount >= 0),
  transfer_date date not null,
  status public.transfer_status not null default 'pending',
  reference text,
  sponsor_visible_notes text,
  internal_notes text,
  created_by uuid not null references public.profiles(id) on delete restrict,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  legacy_monthly_support_id uuid unique
);

create index orphan_transfers_period_idx
  on public.orphan_transfers(transfer_year desc, transfer_month desc);
create index orphan_transfers_sponsor_idx
  on public.orphan_transfers(sponsor_id, transfer_year desc, transfer_month desc);
create index orphan_transfers_orphan_idx
  on public.orphan_transfers(orphan_id, transfer_year desc, transfer_month desc);
create index orphan_transfers_payment_idx
  on public.orphan_transfers(sponsor_monthly_payment_id);

create or replace function private.set_sponsor_payment_status()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.payment_status = case
    when new.received_amount <= 0 then 'unpaid'::public.payment_status
    when new.received_amount < new.expected_amount_snapshot then 'partial'::public.payment_status
    else 'paid'::public.payment_status
  end;
  if new.received_amount = 0 then
    new.payment_date = null;
  end if;
  return new;
end;
$$;

create trigger sponsor_monthly_payment_status
before insert or update of expected_amount_snapshot, received_amount, payment_date
on public.sponsor_monthly_payments
for each row execute function private.set_sponsor_payment_status();

create trigger sponsor_monthly_payments_set_updated_at
before update on public.sponsor_monthly_payments
for each row execute function public.set_updated_at();

create trigger orphan_transfers_set_updated_at
before update on public.orphan_transfers
for each row execute function public.set_updated_at();

create or replace function private.validate_orphan_transfer_links()
returns trigger
language plpgsql
set search_path = ''
as $$
declare
  obligation public.sponsor_monthly_payments%rowtype;
begin
  select * into obligation
  from public.sponsor_monthly_payments
  where id = new.sponsor_monthly_payment_id;

  if obligation.id is null
    or obligation.sponsorship_id <> new.sponsorship_id
    or obligation.sponsor_id <> new.sponsor_id
    or obligation.orphan_id <> new.orphan_id
    or obligation.payment_year <> new.transfer_year
    or obligation.payment_month <> new.transfer_month
  then
    raise exception 'Transfer links do not match the selected sponsor obligation';
  end if;

  return new;
end;
$$;

create trigger orphan_transfer_links_valid
before insert or update of sponsor_monthly_payment_id, sponsorship_id, sponsor_id,
  orphan_id, transfer_year, transfer_month
on public.orphan_transfers
for each row execute function private.validate_orphan_transfer_links();

-- Migrate one sponsor obligation per historical sponsorship snapshot.
insert into public.sponsor_monthly_payments (
  sponsorship_id,
  sponsor_id,
  orphan_id,
  payment_year,
  payment_month,
  expected_amount_snapshot,
  received_amount,
  payment_date,
  internal_notes,
  confirmed_by,
  created_at,
  updated_at
)
select
  snapshot.sponsorship_id,
  snapshot.donor_id,
  monthly.orphan_id,
  monthly.support_year,
  monthly.support_month,
  snapshot.expected_amount,
  snapshot.received_amount,
  case
    when snapshot.received_amount > 0 then coalesce((
      select max(payment.paid_at)::date
      from public.legacy_payment_records payment
      where payment.monthly_support_id = monthly.id
        and payment.donor_id = snapshot.donor_id
    ), monthly.updated_at::date)
    else null
  end,
  nullif(trim(monthly.notes), ''),
  coalesce((
    select payment.recorded_by
    from public.legacy_payment_records payment
    where payment.monthly_support_id = monthly.id
      and payment.donor_id = snapshot.donor_id
    order by payment.paid_at desc
    limit 1
  ), monthly.created_by),
  monthly.created_at,
  monthly.updated_at
from public.legacy_monthly_contributor_snapshots snapshot
join public.legacy_monthly_support_records monthly
  on monthly.id = snapshot.monthly_support_id
join public.sponsorships sponsorship
  on sponsorship.id = snapshot.sponsorship_id
where snapshot.sponsorship_id is not null
on conflict (sponsorship_id, payment_year, payment_month) do nothing;

insert into public.legacy_financial_review (
  record_type,
  orphan_id,
  legacy_monthly_support_id,
  payload,
  created_at
)
select
  'unmapped_sponsor_obligation',
  monthly.orphan_id,
  monthly.id,
  jsonb_build_object(
    'snapshot_id', snapshot.id,
    'sponsor_id', snapshot.donor_id,
    'expected_amount', snapshot.expected_amount,
    'received_amount', snapshot.received_amount,
    'reason', 'missing sponsorship relationship'
  ),
  monthly.created_at
from public.legacy_monthly_contributor_snapshots snapshot
join public.legacy_monthly_support_records monthly
  on monthly.id = snapshot.monthly_support_id
left join public.sponsorships sponsorship
  on sponsorship.id = snapshot.sponsorship_id
where snapshot.sponsorship_id is null or sponsorship.id is null;

-- A legacy outgoing transfer is safe to migrate only when the old monthly row
-- resolves to exactly one sponsor obligation.
insert into public.orphan_transfers (
  sponsor_monthly_payment_id,
  sponsorship_id,
  sponsor_id,
  orphan_id,
  transfer_year,
  transfer_month,
  amount,
  transfer_date,
  status,
  reference,
  sponsor_visible_notes,
  created_by,
  created_at,
  updated_at,
  legacy_monthly_support_id
)
select
  (array_agg(payment.id order by payment.id))[1],
  (array_agg(payment.sponsorship_id order by payment.sponsorship_id))[1],
  (array_agg(payment.sponsor_id order by payment.sponsor_id))[1],
  monthly.orphan_id,
  monthly.support_year,
  monthly.support_month,
  monthly.transferred_amount,
  coalesce(monthly.transferred_at::date, monthly.created_at::date),
  case
    when monthly.transferred_amount > 0
      or exists (
        select 1 from public.transfer_receipts receipt
        where receipt.monthly_support_id = monthly.id
      )
    then 'paid'::public.transfer_status
    else 'pending'::public.transfer_status
  end,
  monthly.transfer_reference,
  nullif(trim(monthly.notes), ''),
  coalesce(
    monthly.created_by,
    (array_remove(array_agg(payment.confirmed_by), null))[1],
    (select id from public.profiles where role = 'admin' order by created_at limit 1)
  ),
  monthly.created_at,
  monthly.updated_at,
  monthly.id
from public.legacy_monthly_support_records monthly
join public.sponsor_monthly_payments payment
  on payment.orphan_id = monthly.orphan_id
  and payment.payment_year = monthly.support_year
  and payment.payment_month = monthly.support_month
where monthly.transferred_amount > 0
   or exists (
     select 1 from public.transfer_receipts receipt
     where receipt.monthly_support_id = monthly.id
   )
group by monthly.id
having count(payment.id) = 1;

insert into public.legacy_financial_review (
  record_type,
  orphan_id,
  legacy_monthly_support_id,
  payload,
  created_at
)
select
  'ambiguous_orphan_transfer',
  monthly.orphan_id,
  monthly.id,
  jsonb_build_object(
    'support_year', monthly.support_year,
    'support_month', monthly.support_month,
    'transferred_amount', monthly.transferred_amount,
    'transferred_at', monthly.transferred_at,
    'transfer_reference', monthly.transfer_reference,
    'candidate_sponsors', (
      select count(*)
      from public.sponsor_monthly_payments payment
      where payment.orphan_id = monthly.orphan_id
        and payment.payment_year = monthly.support_year
        and payment.payment_month = monthly.support_month
    )
  ),
  monthly.created_at
from public.legacy_monthly_support_records monthly
where (
    monthly.transferred_amount > 0
    or exists (
      select 1 from public.transfer_receipts receipt
      where receipt.monthly_support_id = monthly.id
    )
  )
  and not exists (
    select 1 from public.orphan_transfers transfer
    where transfer.legacy_monthly_support_id = monthly.id
  );

-- Convert the receipt table from an orphan-month attachment to a strict
-- one-to-one transfer attachment.
alter table public.transfer_receipts
  rename column uploaded_at to created_at;
alter table public.transfer_receipts
  add column orphan_transfer_id uuid,
  add column uploaded_by uuid references public.profiles(id) on delete restrict;

update public.transfer_receipts receipt
set orphan_transfer_id = transfer.id,
    uploaded_by = asset.uploaded_by
from public.orphan_transfers transfer,
     public.file_assets asset
where transfer.legacy_monthly_support_id = receipt.monthly_support_id
  and asset.id = receipt.file_id;

insert into public.legacy_financial_review (
  record_type,
  orphan_id,
  legacy_monthly_support_id,
  file_id,
  payload,
  created_at
)
select
  'unmapped_transfer_receipt',
  monthly.orphan_id,
  receipt.monthly_support_id,
  receipt.file_id,
  jsonb_build_object(
    'legacy_receipt_id', receipt.id,
    'reason', 'legacy transfer did not resolve to one sponsor'
  ),
  receipt.created_at
from public.transfer_receipts receipt
join public.legacy_monthly_support_records monthly
  on monthly.id = receipt.monthly_support_id
where receipt.orphan_transfer_id is null;

delete from public.transfer_receipts where orphan_transfer_id is null;
drop index if exists transfer_receipts_monthly_idx;
alter table public.transfer_receipts
  drop constraint if exists transfer_receipts_monthly_support_id_fkey,
  drop column monthly_support_id,
  alter column orphan_transfer_id set not null,
  alter column uploaded_by set not null,
  add constraint transfer_receipts_orphan_transfer_id_fkey
    foreign key (orphan_transfer_id) references public.orphan_transfers(id) on delete cascade,
  add constraint transfer_receipts_one_per_transfer unique (orphan_transfer_id);
create index transfer_receipts_transfer_idx
  on public.transfer_receipts(orphan_transfer_id);

create or replace function private.sync_transfer_status_from_receipt()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if tg_op = 'DELETE' then
    update public.orphan_transfers
    set status = 'pending'
    where id = old.orphan_transfer_id;
    return old;
  end if;

  update public.orphan_transfers
  set status = 'paid'
  where id = new.orphan_transfer_id;
  return new;
end;
$$;

create trigger receipt_marks_transfer_paid
after insert or delete on public.transfer_receipts
for each row execute function private.sync_transfer_status_from_receipt();

-- Canonical monthly obligation generator. The amount is snapshotted exactly
-- once and later sponsorship edits cannot rewrite historical months.
create or replace function public.ensure_sponsor_monthly_payments(
  target_year smallint,
  target_month smallint,
  actor uuid
)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  created_count integer;
  month_start date := make_date(target_year, target_month, 1);
  month_end date := (make_date(target_year, target_month, 1) + interval '1 month - 1 day')::date;
begin
  if not exists (
    select 1 from public.profiles
    where id = actor and role = 'admin' and is_active = true
  ) then
    raise exception 'not authorized';
  end if;

  insert into public.sponsor_monthly_payments (
    sponsorship_id,
    sponsor_id,
    orphan_id,
    payment_year,
    payment_month,
    expected_amount_snapshot
  )
  select
    sponsorship.id,
    sponsorship.donor_id,
    sponsorship.orphan_id,
    target_year,
    target_month,
    sponsorship.monthly_amount
  from public.sponsorships sponsorship
  join public.profiles sponsor
    on sponsor.id = sponsorship.donor_id
    and sponsor.role = 'donor'
  where sponsorship.start_date <= month_end
    and (sponsorship.end_date is null or sponsorship.end_date >= month_start)
    and (
      sponsorship.status = 'active'
      or (sponsorship.status = 'ended' and sponsorship.end_date >= month_start)
    )
  on conflict (sponsorship_id, payment_year, payment_month) do nothing;

  get diagnostics created_count = row_count;
  return created_count;
end;
$$;

-- Optional orphan documents are inserted only when supplied.
create or replace function public.create_orphan_with_documents(
  p_full_name text,
  p_date_of_birth date,
  p_gender public.gender,
  p_guardian_name text,
  p_guardian_phone text,
  p_guardian_relationship text,
  p_address text,
  p_bank_name text,
  p_bank_account_name text,
  p_bank_account_number text,
  p_monthly_need bigint,
  p_sponsor_notes text,
  p_private_notes text,
  p_created_by uuid,
  p_documents jsonb
)
returns table(orphan_id uuid, generated_case_number text)
language plpgsql
security definer
set search_path = ''
as $$
declare
  created_orphan_id uuid;
  created_case_number text;
  document jsonb;
  created_file_id uuid;
begin
  if not exists (
    select 1 from public.profiles
    where id = p_created_by and role = 'admin' and is_active = true
  ) then
    raise exception 'not authorized';
  end if;

  if p_documents is null then
    p_documents := '[]'::jsonb;
  end if;

  if jsonb_typeof(p_documents) <> 'array'
    or jsonb_array_length(p_documents) > 3
    or (
      select count(distinct value ->> 'document_type')
      from jsonb_array_elements(p_documents)
    ) <> jsonb_array_length(p_documents)
    or exists (
      select 1
      from jsonb_array_elements(p_documents)
      where value ->> 'document_type' not in (
        'orphan_national_id',
        'father_death_certificate',
        'guardian_national_id'
      )
        or value ->> 'extension' not in ('jpg', 'jpeg', 'png', 'webp')
        or value ->> 'mime_type' not in ('image/jpeg', 'image/png', 'image/webp')
        or value ->> 'sha256' !~ '^[a-f0-9]{64}$'
    )
  then
    raise exception 'invalid orphan documents';
  end if;

  insert into public.orphans (
    full_name,
    date_of_birth,
    gender,
    guardian_name,
    guardian_phone,
    guardian_relationship,
    address,
    bank_name,
    bank_account_name,
    bank_account_number,
    monthly_need,
    sponsor_notes,
    private_notes,
    created_by
  )
  values (
    nullif(trim(p_full_name), ''),
    p_date_of_birth,
    p_gender,
    nullif(trim(p_guardian_name), ''),
    nullif(trim(p_guardian_phone), ''),
    nullif(trim(p_guardian_relationship), ''),
    nullif(trim(p_address), ''),
    nullif(trim(p_bank_name), ''),
    nullif(trim(p_bank_account_name), ''),
    nullif(trim(p_bank_account_number), ''),
    p_monthly_need,
    nullif(trim(p_sponsor_notes), ''),
    nullif(trim(p_private_notes), ''),
    p_created_by
  )
  returning id, case_number into created_orphan_id, created_case_number;

  for document in select value from jsonb_array_elements(p_documents)
  loop
    insert into public.file_assets (
      stored_name,
      original_name,
      extension,
      mime_type,
      size_bytes,
      category,
      uploaded_by,
      sha256
    )
    values (
      (document ->> 'stored_name')::uuid,
      document ->> 'original_name',
      document ->> 'extension',
      document ->> 'mime_type',
      (document ->> 'size_bytes')::bigint,
      (document ->> 'document_type')::public.document_category,
      p_created_by,
      document ->> 'sha256'
    )
    returning id into created_file_id;

    insert into public.orphan_documents (
      orphan_id,
      file_id,
      title,
      document_type
    )
    values (
      created_orphan_id,
      created_file_id,
      document ->> 'title',
      (document ->> 'document_type')::public.document_category
    );
  end loop;

  return query select created_orphan_id, created_case_number;
end;
$$;

drop function if exists public.replace_orphan_document(
  uuid,
  public.document_category,
  text,
  uuid,
  text,
  text,
  text,
  bigint,
  uuid
);

create function public.replace_orphan_document(
  p_orphan_id uuid,
  p_document_type public.document_category,
  p_title text,
  p_stored_name uuid,
  p_original_name text,
  p_extension text,
  p_mime_type text,
  p_size_bytes bigint,
  p_sha256 text,
  p_actor uuid
)
returns table(old_stored_name uuid, old_extension text, new_file_id uuid)
language plpgsql
security definer
set search_path = ''
as $$
declare
  existing_document_id uuid;
  existing_file_id uuid;
  previous_stored_name uuid;
  previous_extension text;
  created_file_id uuid;
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 p_document_type not in (
    'orphan_national_id',
    'father_death_certificate',
    'guardian_national_id'
  )
    or p_extension not in ('jpg', 'jpeg', 'png', 'webp')
    or p_mime_type not in ('image/jpeg', 'image/png', 'image/webp')
    or p_sha256 !~ '^[a-f0-9]{64}$'
  then
    raise exception 'invalid orphan document';
  end if;

  if not exists (select 1 from public.orphans where id = p_orphan_id) then
    raise exception 'orphan not found';
  end if;

  select document.id, asset.id, asset.stored_name, asset.extension
  into existing_document_id, existing_file_id, previous_stored_name, previous_extension
  from public.orphan_documents document
  join public.file_assets asset on asset.id = document.file_id
  where document.orphan_id = p_orphan_id
    and document.document_type = p_document_type
  for update of document;

  insert into public.file_assets (
    stored_name,
    original_name,
    extension,
    mime_type,
    size_bytes,
    category,
    uploaded_by,
    sha256
  )
  values (
    p_stored_name,
    p_original_name,
    p_extension,
    p_mime_type,
    p_size_bytes,
    p_document_type,
    p_actor,
    p_sha256
  )
  returning id into created_file_id;

  if existing_document_id is null then
    insert into public.orphan_documents (
      orphan_id,
      file_id,
      title,
      document_type
    )
    values (
      p_orphan_id,
      created_file_id,
      p_title,
      p_document_type
    );
  else
    update public.orphan_documents
    set file_id = created_file_id,
        title = p_title,
        created_at = now()
    where id = existing_document_id;

    delete from public.file_assets where id = existing_file_id;
  end if;

  return query select previous_stored_name, previous_extension, created_file_id;
end;
$$;

create or replace function public.delete_orphan_document(
  p_orphan_id uuid,
  p_document_type public.document_category,
  p_actor uuid
)
returns table(old_stored_name uuid, old_extension text)
language plpgsql
security definer
set search_path = ''
as $$
declare
  target_document_id uuid;
  target_file_id uuid;
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;

  select document.id, asset.id, asset.stored_name, asset.extension
  into target_document_id, target_file_id, old_stored_name, old_extension
  from public.orphan_documents document
  join public.file_assets asset on asset.id = document.file_id
  where document.orphan_id = p_orphan_id
    and document.document_type = p_document_type
  for update of document;

  if target_document_id is not null then
    delete from public.orphan_documents where id = target_document_id;
    delete from public.file_assets where id = target_file_id;
  end if;

  return next;
end;
$$;

create or replace function public.create_orphan_transfer(
  p_sponsor_monthly_payment_id uuid,
  p_amount bigint,
  p_transfer_date date,
  p_status public.transfer_status,
  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)
language plpgsql
security definer
set search_path = ''
as $$
declare
  obligation public.sponsor_monthly_payments%rowtype;
  created_transfer_id uuid;
  created_file_id uuid;
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;

  select * into obligation
  from public.sponsor_monthly_payments
  where id = p_sponsor_monthly_payment_id;
  if obligation.id is null then
    raise exception 'monthly sponsor payment not found';
  end if;

  insert into public.orphan_transfers (
    sponsor_monthly_payment_id,
    sponsorship_id,
    sponsor_id,
    orphan_id,
    transfer_year,
    transfer_month,
    amount,
    transfer_date,
    status,
    reference,
    sponsor_visible_notes,
    internal_notes,
    created_by
  )
  values (
    obligation.id,
    obligation.sponsorship_id,
    obligation.sponsor_id,
    obligation.orphan_id,
    obligation.payment_year,
    obligation.payment_month,
    p_amount,
    p_transfer_date,
    case when p_receipt is null then p_status else 'paid'::public.transfer_status end,
    nullif(trim(p_reference), ''),
    nullif(trim(p_sponsor_visible_notes), ''),
    nullif(trim(p_internal_notes), ''),
    p_actor
  )
  returning id into created_transfer_id;

  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;
end;
$$;

create or replace function public.replace_transfer_receipt(
  p_transfer_id uuid,
  p_stored_name uuid,
  p_original_name text,
  p_extension text,
  p_mime_type text,
  p_size_bytes bigint,
  p_sha256 text,
  p_actor uuid
)
returns table(old_stored_name uuid, old_extension text, new_file_id uuid)
language plpgsql
security definer
set search_path = ''
as $$
declare
  existing_receipt_id uuid;
  existing_file_id uuid;
  created_file_id uuid;
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.orphan_transfers where id = p_transfer_id) then
    raise exception 'transfer not found';
  end if;
  if p_extension not in ('jpg', 'jpeg', 'png', 'webp')
    or p_mime_type not in ('image/jpeg', 'image/png', 'image/webp')
    or p_sha256 !~ '^[a-f0-9]{64}$'
  then
    raise exception 'invalid transfer receipt';
  end if;

  select receipt.id, asset.id, asset.stored_name, asset.extension
  into existing_receipt_id, existing_file_id, old_stored_name, old_extension
  from public.transfer_receipts receipt
  join public.file_assets asset on asset.id = receipt.file_id
  where receipt.orphan_transfer_id = p_transfer_id
  for update of receipt;

  insert into public.file_assets (
    stored_name,
    original_name,
    extension,
    mime_type,
    size_bytes,
    category,
    uploaded_by,
    sha256
  )
  values (
    p_stored_name,
    p_original_name,
    p_extension,
    p_mime_type,
    p_size_bytes,
    'transfer_receipt',
    p_actor,
    p_sha256
  )
  returning id into created_file_id;

  if existing_receipt_id is null then
    insert into public.transfer_receipts(orphan_transfer_id, file_id, uploaded_by)
    values (p_transfer_id, created_file_id, p_actor);
  else
    update public.transfer_receipts
    set file_id = created_file_id,
        uploaded_by = p_actor,
        created_at = now()
    where id = existing_receipt_id;
    delete from public.file_assets where id = existing_file_id;
  end if;

  update public.orphan_transfers set status = 'paid' where id = p_transfer_id;
  return query select old_stored_name, old_extension, created_file_id;
end;
$$;

create or replace function public.delete_transfer_receipt(
  p_transfer_id uuid,
  p_actor uuid
)
returns table(old_stored_name uuid, old_extension text)
language plpgsql
security definer
set search_path = ''
as $$
declare
  target_receipt_id uuid;
  target_file_id uuid;
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;

  select receipt.id, asset.id, asset.stored_name, asset.extension
  into target_receipt_id, target_file_id, old_stored_name, old_extension
  from public.transfer_receipts receipt
  join public.file_assets asset on asset.id = receipt.file_id
  where receipt.orphan_transfer_id = p_transfer_id
  for update of receipt;

  if target_receipt_id is not null then
    delete from public.transfer_receipts where id = target_receipt_id;
    delete from public.file_assets where id = target_file_id;
  end if;
  return next;
end;
$$;

create or replace function public.delete_orphan_transfer(
  p_transfer_id uuid,
  p_actor uuid
)
returns table(old_stored_name uuid, old_extension text)
language plpgsql
security definer
set search_path = ''
as $$
declare
  target_file_id uuid;
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;

  select asset.id, asset.stored_name, asset.extension
  into target_file_id, old_stored_name, old_extension
  from public.transfer_receipts receipt
  join public.file_assets asset on asset.id = receipt.file_id
  where receipt.orphan_transfer_id = p_transfer_id
  for update of receipt;

  delete from public.orphan_transfers where id = p_transfer_id;
  if target_file_id is not null then
    delete from public.file_assets where id = target_file_id;
  end if;
  return next;
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;
  if exists (
    select 1 from public.orphan_transfers
    where sponsor_monthly_payment_id = p_payment_id
  ) then
    raise exception 'dependent transfers exist';
  end if;
  delete from public.sponsor_monthly_payments where id = p_payment_id;
end;
$$;

create or replace function public.delete_orphan_complete(
  p_orphan_id uuid,
  p_case_number text,
  p_actor uuid
)
returns table(
  deleted_case_number text,
  deleted_full_name text,
  removed_files jsonb
)
language plpgsql
security definer
set search_path = ''
as $$
declare
  orphan_row public.orphans%rowtype;
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;

  select * into orphan_row
  from public.orphans
  where id = p_orphan_id
  for update;
  if orphan_row.id is null then
    raise exception 'orphan not found';
  end if;
  if orphan_row.case_number <> trim(p_case_number) then
    raise exception 'case number confirmation does not match';
  end if;

  select coalesce(jsonb_agg(distinct jsonb_build_object(
    'stored_name', asset.stored_name,
    'extension', asset.extension
  )), '[]'::jsonb)
  into removed_files
  from public.file_assets asset
  where asset.id in (
    select document.file_id
    from public.orphan_documents document
    where document.orphan_id = p_orphan_id
    union
    select receipt.file_id
    from public.transfer_receipts receipt
    join public.orphan_transfers transfer
      on transfer.id = receipt.orphan_transfer_id
    where transfer.orphan_id = p_orphan_id
    union
    select review.file_id
    from public.legacy_financial_review review
    where review.orphan_id = p_orphan_id and review.file_id is not null
  );

  delete from public.orphan_transfers where orphan_id = p_orphan_id;
  delete from public.sponsor_monthly_payments where orphan_id = p_orphan_id;
  delete from public.legacy_monthly_support_records where orphan_id = p_orphan_id;
  delete from public.sponsorships where orphan_id = p_orphan_id;
  delete from public.guardian_consents where orphan_id = p_orphan_id;
  delete from public.legacy_financial_review where orphan_id = p_orphan_id;
  delete from public.orphan_documents where orphan_id = p_orphan_id;
  delete from public.notifications
    where link = '/donor/orphans/' || p_orphan_id::text
       or link like '/donor/orphans/' || p_orphan_id::text || '/%';
  delete from public.file_assets asset
  where exists (
    select 1
    from jsonb_array_elements(removed_files) item
    where item ->> 'stored_name' = asset.stored_name::text
      and item ->> 'extension' = asset.extension
  );
  delete from public.orphans where id = p_orphan_id;

  deleted_case_number := orphan_row.case_number;
  deleted_full_name := orphan_row.full_name;
  return next;
end;
$$;

-- New RLS is sponsor-specific. Legacy financial tables and review rows are
-- admin-only so the retired aggregate model cannot leak across sponsors.
alter table public.sponsor_monthly_payments enable row level security;
alter table public.orphan_transfers enable row level security;
alter table public.legacy_financial_review enable row level security;

drop policy if exists monthly_support_read on public.legacy_monthly_support_records;
drop policy if exists monthly_support_insert_admin on public.legacy_monthly_support_records;
drop policy if exists monthly_support_update_admin on public.legacy_monthly_support_records;
drop policy if exists monthly_support_delete_admin on public.legacy_monthly_support_records;
drop policy if exists snapshots_read on public.legacy_monthly_contributor_snapshots;
drop policy if exists snapshots_insert_admin on public.legacy_monthly_contributor_snapshots;
drop policy if exists snapshots_update_admin on public.legacy_monthly_contributor_snapshots;
drop policy if exists snapshots_delete_admin on public.legacy_monthly_contributor_snapshots;
drop policy if exists payments_read on public.legacy_payment_records;
drop policy if exists payments_insert_admin on public.legacy_payment_records;
drop policy if exists payments_update_admin on public.legacy_payment_records;
drop policy if exists payments_delete_admin on public.legacy_payment_records;

create policy legacy_monthly_admin on public.legacy_monthly_support_records
for all to authenticated using (private.is_admin()) with check (private.is_admin());
create policy legacy_snapshots_admin on public.legacy_monthly_contributor_snapshots
for all to authenticated using (private.is_admin()) with check (private.is_admin());
create policy legacy_payments_admin on public.legacy_payment_records
for all to authenticated using (private.is_admin()) with check (private.is_admin());
create policy legacy_review_admin on public.legacy_financial_review
for all to authenticated using (private.is_admin()) with check (private.is_admin());

create policy sponsor_monthly_payments_read on public.sponsor_monthly_payments
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 sponsor_monthly_payments_insert_admin on public.sponsor_monthly_payments
for insert to authenticated with check (private.is_admin());
create policy sponsor_monthly_payments_update_admin on public.sponsor_monthly_payments
for update to authenticated using (private.is_admin()) with check (private.is_admin());
create policy sponsor_monthly_payments_delete_admin on public.sponsor_monthly_payments
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 (
    sponsor_id = (select auth.uid())
    and exists (
      select 1 from public.profiles
      where id = (select auth.uid()) and is_active = true
    )
  )
);
create policy orphan_transfers_insert_admin on public.orphan_transfers
for insert to authenticated with check (private.is_admin());
create policy orphan_transfers_update_admin on public.orphan_transfers
for update to authenticated using (private.is_admin()) with check (private.is_admin());
create policy orphan_transfers_delete_admin on public.orphan_transfers
for delete to authenticated using (private.is_admin());

create policy transfer_receipts_read on public.transfer_receipts
for select to authenticated
using (
  private.is_admin()
  or exists (
    select 1
    from public.orphan_transfers transfer
    join public.profiles sponsor on sponsor.id = transfer.sponsor_id
    where transfer.id = orphan_transfer_id
      and transfer.sponsor_id = (select auth.uid())
      and sponsor.is_active = true
  )
);
create policy transfer_receipts_insert_admin on public.transfer_receipts
for insert to authenticated with check (private.is_admin());
create policy transfer_receipts_update_admin on public.transfer_receipts
for update to authenticated using (private.is_admin()) with check (private.is_admin());
create policy transfer_receipts_delete_admin on public.transfer_receipts
for delete to authenticated using (private.is_admin());

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_transfers transfer
      on transfer.id = receipt.orphan_transfer_id
    join public.profiles sponsor
      on sponsor.id = transfer.sponsor_id
    where receipt.file_id = public.file_assets.id
      and transfer.sponsor_id = (select auth.uid())
      and sponsor.is_active = true
  )
);

revoke all on public.sponsor_monthly_payments, public.orphan_transfers,
  public.legacy_financial_review from anon;
grant select, insert, update, delete on public.sponsor_monthly_payments,
  public.orphan_transfers, public.legacy_financial_review to authenticated;

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

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

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

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

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

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

revoke all on function public.delete_orphan_transfer(uuid, uuid)
  from public, anon, authenticated;
grant execute on function public.delete_orphan_transfer(uuid, uuid)
  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;

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

notify pgrst, 'reload schema';

commit;