begin;

-- Retire every database surface that exposed orphan cases publicly or supported
-- the removed sponsorship-request workflow.
drop trigger if exists orphan_sync_public_profile on public.orphans;
drop function if exists private.sync_public_orphan_profile();
drop table if exists public.public_orphan_profiles;
drop table if exists public.sponsorship_requests;
drop type if exists public.request_status;

-- Sponsor data now lives in profiles. Preserve the useful notes before removing
-- the email-dependent extension table.
alter table public.profiles add column if not exists sponsor_notes text;
update public.profiles profile
set sponsor_notes = donor.notes
from public.donor_profiles donor
where donor.profile_id = profile.id
  and nullif(trim(donor.notes), '') is not null
  and nullif(trim(profile.sponsor_notes), '') is null;
drop table if exists public.donor_profiles;

-- Normalize existing sponsor phones to E.164, then enforce uniqueness. Admin
-- profiles may remain email-authenticated and do not require a phone number.
update public.profiles
set phone = case
  when regexp_replace(phone, '[^0-9+]', '', 'g') like '+%' then
    '+' || regexp_replace(regexp_replace(phone, '[^0-9+]', '', 'g'), '[^0-9]', '', 'g')
  when regexp_replace(phone, '[^0-9]', '', 'g') like '00%' then
    '+' || substring(regexp_replace(phone, '[^0-9]', '', 'g') from 3)
  when regexp_replace(phone, '[^0-9]', '', 'g') like '0%' then
    '+249' || substring(regexp_replace(phone, '[^0-9]', '', 'g') from 2)
  when regexp_replace(phone, '[^0-9]', '', 'g') like '249%' then
    '+' || regexp_replace(phone, '[^0-9]', '', 'g')
  else
    '+249' || regexp_replace(phone, '[^0-9]', '', 'g')
end
where role = 'donor'
  and nullif(trim(phone), '') is not null;

do $$
begin
  if exists (
    select phone
    from public.profiles
    where nullif(trim(phone), '') is not null
    group by phone
    having count(*) > 1
  ) then
    raise exception 'Duplicate sponsor phone numbers must be resolved before applying this migration';
  end if;

  if exists (
    select 1
    from public.profiles
    where role = 'donor'
      and (phone is null or phone !~ '^\+[1-9][0-9]{7,14}$')
  ) then
    raise exception 'Every sponsor must have a valid E.164 phone number before applying this migration';
  end if;
end;
$$;

create unique index if not exists profiles_phone_unique
  on public.profiles(phone)
  where phone is not null;

alter table public.profiles
  drop constraint if exists profiles_sponsor_phone_required;
alter table public.profiles
  add constraint profiles_sponsor_phone_required
  check (role = 'admin' or phone ~ '^\+[1-9][0-9]{7,14}$');

-- New Auth users are sponsors by default. The bootstrap script may explicitly
-- mark an account as the first administrator in trusted user metadata.
create or replace function private.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, role, full_name, phone)
  values (
    new.id,
    case
      when new.raw_user_meta_data ->> 'initial_role' = 'admin' then 'admin'::public.app_role
      else 'donor'::public.app_role
    end,
    coalesce(nullif(trim(new.raw_user_meta_data ->> 'full_name'), ''), 'مستخدم جديد'),
    coalesce(nullif(trim(new.phone), ''), nullif(trim(new.raw_user_meta_data ->> 'phone'), ''))
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

-- Case numbers are generated by PostgreSQL so concurrent inserts cannot collide.
create sequence if not exists public.orphan_case_number_seq;
do $$
declare
  maximum_number bigint;
begin
  select max((regexp_match(case_number, '^ORP-([0-9]+)$'))[1]::bigint)
  into maximum_number
  from public.orphans
  where case_number ~ '^ORP-[0-9]+$';

  if maximum_number is null then
    perform setval('public.orphan_case_number_seq', 1, false);
  else
    perform setval('public.orphan_case_number_seq', maximum_number, true);
  end if;
end;
$$;

alter table public.orphans
  alter column case_number set default
    ('ORP-' || lpad(nextval('public.orphan_case_number_seq')::text, 6, '0'));

create or replace function private.prevent_case_number_change()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if old.case_number is distinct from new.case_number then
    raise exception 'Case numbers are immutable';
  end if;
  return new;
end;
$$;

drop trigger if exists orphan_case_number_immutable on public.orphans;
create trigger orphan_case_number_immutable
before update of case_number on public.orphans
for each row execute function private.prevent_case_number_change();

alter table public.orphans
  drop constraint if exists orphans_public_summary_check;
alter table public.orphans
  rename column public_summary to sponsor_notes;
alter table public.orphans
  alter column sponsor_notes set default '',
  alter column sponsor_notes set not null;
alter table public.orphans
  add constraint orphans_sponsor_notes_length
  check (char_length(sponsor_notes) <= 3000);

alter table public.orphans drop column national_id;

update public.orphans
set bank_name = 'بنك الخرطوم'
where nullif(trim(bank_name), '') is null;
alter table public.orphans
  alter column bank_name set default 'بنك الخرطوم';

alter table public.sponsorships
  add column if not exists stop_reason text,
  add column if not exists notes text;

-- Preserve old file rows while replacing obsolete document labels.
alter type public.document_category rename to document_category_legacy;
create type public.document_category as enum (
  'orphan_national_id',
  'father_death_certificate',
  'guardian_national_id',
  'transfer_receipt',
  'supporting_document'
);

alter table public.file_assets
  alter column category type public.document_category
  using (
    case category::text
      when 'national_id' then 'orphan_national_id'
      when 'father_death_certificate' then 'father_death_certificate'
      when 'guardian_document' then 'guardian_national_id'
      when 'transfer_receipt' then 'transfer_receipt'
      else 'supporting_document'
    end
  )::public.document_category;
drop type public.document_category_legacy;

alter table public.orphan_documents
  add column document_type public.document_category;
update public.orphan_documents document
set document_type = asset.category
from public.file_assets asset
where asset.id = document.file_id;
alter table public.orphan_documents
  alter column document_type set not null;

do $$
begin
  if exists (
    select orphan_id, document_type
    from public.orphan_documents
    where document_type in (
      'orphan_national_id',
      'father_death_certificate',
      'guardian_national_id'
    )
    group by orphan_id, document_type
    having count(*) > 1
  ) then
    raise exception 'Duplicate required orphan documents must be consolidated before applying this migration';
  end if;
end;
$$;

create unique index orphan_documents_required_type_unique
  on public.orphan_documents(orphan_id, document_type)
  where document_type in (
    'orphan_national_id',
    'father_death_certificate',
    'guardian_national_id'
  );

alter table public.monthly_support_records
  add column if not exists pledged_amount bigint not null default 0
  check (pledged_amount >= 0);
update public.monthly_support_records monthly
set pledged_amount = coalesce((
  select sum(snapshot.expected_amount)
  from public.monthly_contributor_snapshots snapshot
  where snapshot.monthly_support_id = monthly.id
), 0);

-- A linked, active sponsor can view every required orphan document. No public
-- access and no consent-only bypass remains.
create or replace function private.donor_may_view_documents(target_orphan uuid)
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select private.donor_linked_to_orphan(target_orphan)
$$;

-- Sponsors may read only their own contributor and partial-payment rows. The
-- aggregate monthly record exposes the non-identifying pledged/transfer totals.
drop policy if exists snapshots_read on public.monthly_contributor_snapshots;
create policy snapshots_read on public.monthly_contributor_snapshots
for select to authenticated
using (
  private.is_admin()
  or (
    donor_id = (select auth.uid())
    and exists (
      select 1
      from public.monthly_support_records monthly
      where monthly.id = monthly_support_id
        and private.donor_linked_to_orphan(monthly.orphan_id)
    )
  )
);

drop policy if exists payments_read on public.payment_records;
create policy payments_read on public.payment_records
for select to authenticated
using (
  private.is_admin()
  or (
    donor_id = (select auth.uid())
    and exists (
      select 1
      from public.monthly_support_records monthly
      where monthly.id = monthly_support_id
        and private.donor_linked_to_orphan(monthly.orphan_id)
    )
  )
);

drop policy if exists sponsorships_read on public.sponsorships;
create policy sponsorships_read on public.sponsorships
for select to authenticated
using (
  private.is_admin()
  or (
    donor_id = (select auth.uid())
    and exists (
      select 1 from public.profiles
      where id = (select auth.uid()) and is_active = true
    )
  )
);

drop policy if exists notifications_read on public.notifications;
create policy notifications_read on public.notifications
for select to authenticated
using (
  private.is_admin()
  or (
    recipient_id = (select auth.uid())
    and exists (
      select 1 from public.profiles
      where id = (select auth.uid()) and is_active = true
    )
  )
);

drop policy if exists notifications_update on public.notifications;
create policy notifications_update on public.notifications
for update to authenticated
using (
  recipient_id = (select auth.uid())
  and exists (
    select 1 from public.profiles
    where id = (select auth.uid()) and is_active = true
  )
)
with check (
  recipient_id = (select auth.uid())
  and exists (
    select 1 from public.profiles
    where id = (select auth.uid()) and is_active = true
  )
);

-- Keep sponsor phone changes under server-side administrator control so Auth
-- and the profile row cannot drift apart.
revoke update on public.profiles from authenticated;
grant update (full_name, last_activity_at, updated_at) on public.profiles to authenticated;

create or replace function public.create_monthly_support(
  target_year smallint,
  target_month smallint,
  actor uuid
)
returns integer
language plpgsql
security invoker
set search_path = ''
as $$
declare
  created_count integer := 0;
  orphan_row record;
  monthly_id uuid;
  total_pledged bigint;
begin
  if not private.is_admin() or actor <> (select auth.uid()) then
    raise exception 'not authorized';
  end if;

  for orphan_row in
    select o.id, o.monthly_need
    from public.orphans o
    where o.status not in ('paused', 'archived')
      and exists (
        select 1
        from public.sponsorships s
        where s.orphan_id = o.id
          and s.status = 'active'
          and s.start_date <= make_date(target_year, target_month, 1) + interval '1 month - 1 day'
          and (s.end_date is null or s.end_date >= make_date(target_year, target_month, 1))
      )
  loop
    select coalesce(sum(s.monthly_amount), 0)
    into total_pledged
    from public.sponsorships s
    where s.orphan_id = orphan_row.id
      and s.status = 'active'
      and s.start_date <= make_date(target_year, target_month, 1) + interval '1 month - 1 day'
      and (s.end_date is null or s.end_date >= make_date(target_year, target_month, 1));

    insert into public.monthly_support_records (
      orphan_id,
      support_year,
      support_month,
      expected_amount,
      pledged_amount,
      created_by
    )
    values (
      orphan_row.id,
      target_year,
      target_month,
      orphan_row.monthly_need,
      total_pledged,
      actor
    )
    on conflict (orphan_id, support_year, support_month) do nothing
    returning id into monthly_id;

    if monthly_id is not null then
      insert into public.monthly_contributor_snapshots (
        monthly_support_id,
        sponsorship_id,
        donor_id,
        expected_amount
      )
      select monthly_id, s.id, s.donor_id, s.monthly_amount
      from public.sponsorships s
      where s.orphan_id = orphan_row.id
        and s.status = 'active'
        and s.start_date <= make_date(target_year, target_month, 1) + interval '1 month - 1 day'
        and (s.end_date is null or s.end_date >= make_date(target_year, target_month, 1));
      created_count := created_count + 1;
    end if;
    monthly_id := null;
  end loop;
  return created_count;
end;
$$;

-- Create an orphan and all three mandatory image records in one database
-- transaction. The server deletes already-staged files if this RPC rolls back.
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 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)
    ) <> 3
    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')
    )
  then
    raise exception 'Three valid required orphan images are mandatory';
  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 (
    p_full_name,
    p_date_of_birth,
    p_gender,
    p_guardian_name,
    p_guardian_phone,
    p_guardian_relationship,
    p_address,
    coalesce(nullif(trim(p_bank_name), ''), 'بنك الخرطوم'),
    p_bank_account_name,
    p_bank_account_number,
    p_monthly_need,
    coalesce(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
    )
    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
    )
    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;
$$;

-- Replace a required document atomically. The old physical file reference is
-- returned so the server can remove it only after the database commit succeeds.
create or replace 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_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')
  then
    raise exception 'invalid orphan document';
  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
  )
  values (
    p_stored_name,
    p_original_name,
    p_extension,
    p_mime_type,
    p_size_bytes,
    p_document_type,
    p_actor
  )
  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;
$$;

revoke all on function public.create_orphan_with_documents(
  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(
  text,
  date,
  public.gender,
  text,
  text,
  text,
  text,
  text,
  text,
  text,
  bigint,
  text,
  text,
  uuid,
  jsonb
) to service_role;

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

commit;
