begin;

create or replace function public.create_orphan_with_documents_v2(
  p_orphan_id uuid,
  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_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_orphan_id is null or exists (
    select 1 from public.orphans where id = p_orphan_id
  ) then
    raise exception 'invalid orphan id';
  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 (
    id,
    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 (
    p_orphan_id,
    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 case_number into 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 (
      p_orphan_id,
      created_file_id,
      document ->> 'title',
      (document ->> 'document_type')::public.document_category
    );
  end loop;

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

create or replace function private.derive_transfer_status_from_receipt()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if tg_op = 'INSERT' then
    new.status := 'pending'::public.transfer_status;
  else
    new.status := case
      when exists (
        select 1
        from public.transfer_receipts receipt
        where receipt.orphan_transfer_id = new.id
      )
      then 'paid'::public.transfer_status
      else 'pending'::public.transfer_status
    end;
  end if;
  return new;
end;
$$;

drop trigger if exists transfer_status_is_receipt_derived on public.orphan_transfers;
create trigger transfer_status_is_receipt_derived
before insert or update on public.orphan_transfers
for each row execute function private.derive_transfer_status_from_receipt();

update public.orphan_transfers transfer
set status = case
  when exists (
    select 1
    from public.transfer_receipts receipt
    where receipt.orphan_transfer_id = transfer.id
  )
  then 'paid'::public.transfer_status
  else 'pending'::public.transfer_status
end;

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

notify pgrst, 'reload schema';

commit;
