begin;

create schema if not exists private;
revoke all on schema private from public, anon;
grant usage on schema private to authenticated;

drop trigger if exists on_auth_user_created on auth.users;
drop trigger if exists sponsorship_refresh_coverage on public.sponsorships;
drop trigger if exists payment_refresh_totals on public.payment_records;

drop policy if exists profiles_self_or_admin_select on public.profiles;
drop policy if exists profiles_admin_all on public.profiles;
drop policy if exists donor_profiles_self_or_admin_select on public.donor_profiles;
drop policy if exists donor_profiles_admin_all on public.donor_profiles;
drop policy if exists orphans_admin_all on public.orphans;
drop policy if exists orphans_linked_donor_select on public.orphans;
drop policy if exists guardian_consents_admin_all on public.guardian_consents;
drop policy if exists guardian_consents_linked_donor_select on public.guardian_consents;
drop policy if exists sponsorships_admin_all on public.sponsorships;
drop policy if exists sponsorships_donor_select on public.sponsorships;
drop policy if exists monthly_support_admin_all on public.monthly_support_records;
drop policy if exists monthly_support_linked_donor_select on public.monthly_support_records;
drop policy if exists snapshots_admin_all on public.monthly_contributor_snapshots;
drop policy if exists snapshots_linked_donor_select on public.monthly_contributor_snapshots;
drop policy if exists payments_admin_all on public.payment_records;
drop policy if exists payments_linked_donor_select on public.payment_records;
drop policy if exists file_assets_admin_select on public.file_assets;
drop policy if exists file_assets_admin_all on public.file_assets;
drop policy if exists file_assets_authorized_donor_select on public.file_assets;
drop policy if exists orphan_documents_admin_all on public.orphan_documents;
drop policy if exists orphan_documents_donor_select on public.orphan_documents;
drop policy if exists transfer_receipts_admin_all on public.transfer_receipts;
drop policy if exists transfer_receipts_donor_select on public.transfer_receipts;
drop policy if exists requests_admin_all on public.sponsorship_requests;
drop policy if exists requests_owner_select on public.sponsorship_requests;
drop policy if exists notifications_owner_select on public.notifications;
drop policy if exists notifications_owner_update on public.notifications;
drop policy if exists notifications_admin_all on public.notifications;
drop policy if exists settings_authenticated_select on public.app_settings;
drop policy if exists settings_admin_all on public.app_settings;
drop policy if exists contact_admin_select on public.contact_messages;
drop policy if exists contact_admin_update on public.contact_messages;
drop policy if exists audit_admin_select on public.audit_logs;

drop function if exists public.after_payment_change();
drop function if exists public.after_sponsorship_change();
drop function if exists public.create_monthly_support(smallint, smallint, uuid);
drop function if exists public.donor_may_view_documents(uuid);
drop function if exists public.donor_linked_to_orphan(uuid);
drop function if exists public.is_admin();
drop function if exists public.current_profile_role();
drop function if exists public.refresh_monthly_totals(uuid);
drop function if exists public.refresh_orphan_coverage(uuid);
drop function if exists public.handle_new_user();

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,
    'donor',
    coalesce(nullif(trim(new.raw_user_meta_data ->> 'full_name'), ''), 'مستخدم جديد'),
    nullif(trim(new.raw_user_meta_data ->> 'phone'), '')
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

create trigger on_auth_user_created
after insert on auth.users
for each row execute function private.handle_new_user();

create or replace function private.current_profile_role()
returns public.app_role
language sql
stable
security definer
set search_path = ''
as $$
  select role from public.profiles
  where id = (select auth.uid()) and is_active = true
$$;

create or replace function private.is_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select coalesce(private.current_profile_role() = 'admin', false)
$$;

create or replace function private.donor_linked_to_orphan(target_orphan uuid)
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1
    from public.sponsorships s
    join public.profiles p on p.id = s.donor_id
    where s.orphan_id = target_orphan
      and s.donor_id = (select auth.uid())
      and s.status = 'active'
      and p.is_active = true
  )
$$;

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)
    and exists (
      select 1 from public.guardian_consents c
      where c.orphan_id = target_orphan
        and c.consented_to_donor_access = true
        and c.revoked_at is null
    )
$$;

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 total < need then 'partially_covered'::public.orphan_status
        else 'fully_covered'::public.orphan_status
      end
  where id = target_orphan;
end;
$$;

create or replace function private.after_sponsorship_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform private.refresh_orphan_coverage(coalesce(new.orphan_id, old.orphan_id));
  if tg_op = 'UPDATE' and old.orphan_id <> new.orphan_id then
    perform private.refresh_orphan_coverage(old.orphan_id);
  end if;
  return coalesce(new, old);
end;
$$;

create trigger sponsorship_refresh_coverage
after insert or update or delete on public.sponsorships
for each row execute function private.after_sponsorship_change();

create or replace function private.refresh_monthly_totals(target_monthly uuid)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  received bigint;
  expected bigint;
begin
  select coalesce(sum(amount), 0) into received
  from public.payment_records where monthly_support_id = target_monthly;
  select expected_amount into expected from public.monthly_support_records where id = target_monthly;

  update public.monthly_support_records
  set total_received = received,
      payment_status = case
        when received <= 0 then 'unpaid'::public.payment_status
        when received < expected then 'partial'::public.payment_status
        else 'paid'::public.payment_status
      end
  where id = target_monthly;

  update public.monthly_contributor_snapshots snapshot
  set received_amount = coalesce((
    select sum(payment.amount)
    from public.payment_records payment
    where payment.monthly_support_id = target_monthly
      and payment.donor_id = snapshot.donor_id
  ), 0)
  where snapshot.monthly_support_id = target_monthly;
end;
$$;

create or replace function private.after_payment_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform private.refresh_monthly_totals(coalesce(new.monthly_support_id, old.monthly_support_id));
  if tg_op = 'UPDATE' and old.monthly_support_id <> new.monthly_support_id then
    perform private.refresh_monthly_totals(old.monthly_support_id);
  end if;
  return coalesce(new, old);
end;
$$;

create trigger payment_refresh_totals
after insert or update or delete on public.payment_records
for each row execute function private.after_payment_change();

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;
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
    insert into public.monthly_support_records (
      orphan_id, support_year, support_month, expected_amount, created_by
    )
    values (orphan_row.id, target_year, target_month, orphan_row.monthly_need, 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;
$$;

drop view if exists public.public_orphan_profiles;

create table public.public_orphan_profiles (
  id uuid primary key references public.orphans(id) on delete cascade,
  case_number text not null unique,
  date_of_birth date not null,
  gender public.gender not null,
  public_summary text not null,
  monthly_need bigint not null,
  covered_amount bigint not null,
  status public.orphan_status not null,
  created_at timestamptz not null
);

insert into public.public_orphan_profiles (
  id, case_number, date_of_birth, gender, public_summary,
  monthly_need, covered_amount, status, created_at
)
select
  id, case_number, date_of_birth, gender, public_summary,
  monthly_need, covered_amount, status, created_at
from public.orphans
where status in ('available', 'partially_covered');

create or replace function private.sync_public_orphan_profile()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if tg_op = 'DELETE' then
    delete from public.public_orphan_profiles where id = old.id;
    return old;
  end if;

  if new.status in ('available', 'partially_covered') then
    insert into public.public_orphan_profiles (
      id, case_number, date_of_birth, gender, public_summary,
      monthly_need, covered_amount, status, created_at
    )
    values (
      new.id, new.case_number, new.date_of_birth, new.gender, new.public_summary,
      new.monthly_need, new.covered_amount, new.status, new.created_at
    )
    on conflict (id) do update set
      case_number = excluded.case_number,
      date_of_birth = excluded.date_of_birth,
      gender = excluded.gender,
      public_summary = excluded.public_summary,
      monthly_need = excluded.monthly_need,
      covered_amount = excluded.covered_amount,
      status = excluded.status,
      created_at = excluded.created_at;
  else
    delete from public.public_orphan_profiles where id = new.id;
  end if;
  return new;
end;
$$;

create trigger orphan_sync_public_profile
after insert or update or delete on public.orphans
for each row execute function private.sync_public_orphan_profile();

alter table public.public_orphan_profiles enable row level security;
create policy public_orphan_profiles_read on public.public_orphan_profiles
for select to anon, authenticated using (true);
revoke all on public.public_orphan_profiles from anon, authenticated;
grant select on public.public_orphan_profiles to anon, authenticated;

create policy profiles_read on public.profiles for select to authenticated
using (id = (select auth.uid()) or private.is_admin());
create policy profiles_insert_admin on public.profiles for insert to authenticated
with check (private.is_admin());
create policy profiles_update on public.profiles for update to authenticated
using (id = (select auth.uid()) or private.is_admin())
with check (id = (select auth.uid()) or private.is_admin());
create policy profiles_delete_admin on public.profiles for delete to authenticated
using (private.is_admin());

create policy donor_profiles_read on public.donor_profiles for select to authenticated
using (profile_id = (select auth.uid()) or private.is_admin());
create policy donor_profiles_insert_admin on public.donor_profiles for insert to authenticated
with check (private.is_admin());
create policy donor_profiles_update_admin on public.donor_profiles for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy donor_profiles_delete_admin on public.donor_profiles for delete to authenticated
using (private.is_admin());

create policy orphans_read on public.orphans for select to authenticated
using (private.is_admin() or private.donor_linked_to_orphan(id));
create policy orphans_insert_admin on public.orphans for insert to authenticated
with check (private.is_admin());
create policy orphans_update_admin on public.orphans for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy orphans_delete_admin on public.orphans for delete to authenticated
using (private.is_admin());

create policy guardian_consents_read on public.guardian_consents for select to authenticated
using (private.is_admin() or private.donor_linked_to_orphan(orphan_id));
create policy guardian_consents_insert_admin on public.guardian_consents for insert to authenticated
with check (private.is_admin());
create policy guardian_consents_update_admin on public.guardian_consents for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy guardian_consents_delete_admin on public.guardian_consents for delete to authenticated
using (private.is_admin());

create policy sponsorships_read on public.sponsorships for select to authenticated
using (private.is_admin() or donor_id = (select auth.uid()));
create policy sponsorships_insert_admin on public.sponsorships for insert to authenticated
with check (private.is_admin());
create policy sponsorships_update_admin on public.sponsorships for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy sponsorships_delete_admin on public.sponsorships for delete to authenticated
using (private.is_admin());

create policy monthly_support_read on public.monthly_support_records for select to authenticated
using (private.is_admin() or private.donor_linked_to_orphan(orphan_id));
create policy monthly_support_insert_admin on public.monthly_support_records for insert to authenticated
with check (private.is_admin());
create policy monthly_support_update_admin on public.monthly_support_records for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy monthly_support_delete_admin on public.monthly_support_records for delete to authenticated
using (private.is_admin());

create policy snapshots_read on public.monthly_contributor_snapshots for select to authenticated
using (
  private.is_admin()
  or exists (
    select 1 from public.monthly_support_records m
    where m.id = monthly_support_id and private.donor_linked_to_orphan(m.orphan_id)
  )
);
create policy snapshots_insert_admin on public.monthly_contributor_snapshots for insert to authenticated
with check (private.is_admin());
create policy snapshots_update_admin on public.monthly_contributor_snapshots for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy snapshots_delete_admin on public.monthly_contributor_snapshots for delete to authenticated
using (private.is_admin());

create policy payments_read on public.payment_records for select to authenticated
using (
  private.is_admin()
  or exists (
    select 1 from public.monthly_support_records m
    where m.id = monthly_support_id and private.donor_linked_to_orphan(m.orphan_id)
  )
);
create policy payments_insert_admin on public.payment_records for insert to authenticated
with check (private.is_admin());
create policy payments_update_admin on public.payment_records for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy payments_delete_admin on public.payment_records 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 d
    where d.file_id = public.file_assets.id and private.donor_may_view_documents(d.orphan_id)
  )
  or exists (
    select 1 from public.transfer_receipts r
    join public.monthly_support_records m on m.id = r.monthly_support_id
    where r.file_id = public.file_assets.id and private.donor_linked_to_orphan(m.orphan_id)
  )
);
create policy file_assets_insert_admin on public.file_assets for insert to authenticated
with check (private.is_admin());
create policy file_assets_update_admin on public.file_assets for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy file_assets_delete_admin on public.file_assets for delete to authenticated
using (private.is_admin());

create policy orphan_documents_read on public.orphan_documents for select to authenticated
using (private.is_admin() or private.donor_may_view_documents(orphan_id));
create policy orphan_documents_insert_admin on public.orphan_documents for insert to authenticated
with check (private.is_admin());
create policy orphan_documents_update_admin on public.orphan_documents for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy orphan_documents_delete_admin on public.orphan_documents 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.monthly_support_records m
    where m.id = monthly_support_id and private.donor_linked_to_orphan(m.orphan_id)
  )
);
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 requests_read on public.sponsorship_requests for select to authenticated
using (private.is_admin() or requester_user_id = (select auth.uid()));
create policy requests_insert_admin on public.sponsorship_requests for insert to authenticated
with check (private.is_admin());
create policy requests_update_admin on public.sponsorship_requests for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy requests_delete_admin on public.sponsorship_requests for delete to authenticated
using (private.is_admin());

create policy notifications_read on public.notifications for select to authenticated
using (recipient_id = (select auth.uid()) or private.is_admin());
create policy notifications_insert_admin on public.notifications for insert to authenticated
with check (private.is_admin());
create policy notifications_update on public.notifications for update to authenticated
using (recipient_id = (select auth.uid()) or private.is_admin())
with check (recipient_id = (select auth.uid()) or private.is_admin());
create policy notifications_delete_admin on public.notifications for delete to authenticated
using (private.is_admin());

create policy settings_read on public.app_settings for select to authenticated using (true);
create policy settings_insert_admin on public.app_settings for insert to authenticated
with check (private.is_admin());
create policy settings_update_admin on public.app_settings for update to authenticated
using (private.is_admin()) with check (private.is_admin());
create policy settings_delete_admin on public.app_settings for delete to authenticated
using (private.is_admin());

create policy contact_read_admin on public.contact_messages for select to authenticated
using (private.is_admin());
create policy contact_update_admin on public.contact_messages for update to authenticated
using (private.is_admin()) with check (private.is_admin());

create policy audit_read_admin on public.audit_logs for select to authenticated
using (private.is_admin());

revoke all on all functions in schema private from public, anon, authenticated;
grant execute on function private.current_profile_role() to authenticated;
grant execute on function private.is_admin() to authenticated;
grant execute on function private.donor_linked_to_orphan(uuid) to authenticated;
grant execute on function private.donor_may_view_documents(uuid) to authenticated;

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

commit;
