begin;

create extension if not exists pgcrypto;

create type public.app_role as enum ('admin', 'donor');
create type public.gender as enum ('male', 'female');
create type public.orphan_status as enum ('available', 'partially_covered', 'fully_covered', 'paused', 'archived');
create type public.sponsorship_status as enum ('pending', 'active', 'paused', 'ended', 'rejected');
create type public.payment_status as enum ('unpaid', 'partial', 'paid');
create type public.request_status as enum ('pending', 'contacted', 'accepted', 'rejected', 'cancelled');
create type public.document_category as enum (
  'national_id',
  'father_death_certificate',
  'birth_certificate',
  'guardian_document',
  'bank_document',
  'transfer_receipt',
  'other'
);

create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  role public.app_role not null default 'donor',
  full_name text not null check (char_length(full_name) between 3 and 120),
  phone text,
  is_active boolean not null default true,
  last_activity_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.donor_profiles (
  profile_id uuid primary key references public.profiles(id) on delete cascade,
  contact_email text not null,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint donor_contact_email_format check (contact_email ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$')
);

create table public.orphans (
  id uuid primary key default gen_random_uuid(),
  case_number text not null unique,
  full_name text not null check (char_length(full_name) between 3 and 120),
  national_id text not null unique,
  date_of_birth date not null check (date_of_birth <= current_date),
  gender public.gender not null,
  public_summary text not null check (char_length(public_summary) between 20 and 800),
  private_notes text,
  guardian_name text not null,
  guardian_phone text not null,
  guardian_relationship text not null,
  address text not null,
  bank_name text not null,
  bank_account_name text not null,
  bank_account_number text not null,
  monthly_need bigint not null default 50000 check (monthly_need >= 1000),
  covered_amount bigint not null default 0 check (covered_amount >= 0),
  status public.orphan_status not null default 'available',
  created_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.guardian_consents (
  id uuid primary key default gen_random_uuid(),
  orphan_id uuid not null references public.orphans(id) on delete cascade,
  guardian_name text not null,
  consented_to_donor_access boolean not null default false,
  consented_at timestamptz,
  revoked_at timestamptz,
  recorded_by uuid not null references public.profiles(id) on delete restrict,
  notes text,
  created_at timestamptz not null default now(),
  constraint consent_dates_valid check (
    (consented_to_donor_access = false and consented_at is null)
    or (consented_to_donor_access = true and consented_at is not null)
  ),
  constraint revoke_after_consent check (revoked_at is null or consented_at is not null)
);

create unique index guardian_consents_one_active_per_orphan
  on public.guardian_consents(orphan_id)
  where consented_to_donor_access = true and revoked_at is null;

create table public.sponsorships (
  id uuid primary key default gen_random_uuid(),
  donor_id uuid not null references public.profiles(id) on delete restrict,
  orphan_id uuid not null references public.orphans(id) on delete restrict,
  monthly_amount bigint not null check (monthly_amount >= 1000),
  status public.sponsorship_status not null default 'active',
  start_date date not null default current_date,
  end_date date,
  created_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint sponsorship_dates_valid check (end_date is null or end_date >= start_date)
);

create unique index sponsorships_one_active_pair
  on public.sponsorships(donor_id, orphan_id)
  where status = 'active';
create index sponsorships_donor_status_idx on public.sponsorships(donor_id, status);
create index sponsorships_orphan_status_idx on public.sponsorships(orphan_id, status);

create table public.monthly_support_records (
  id uuid primary key default gen_random_uuid(),
  orphan_id uuid not null references public.orphans(id) on delete restrict,
  support_year smallint not null check (support_year between 2020 and 2200),
  support_month smallint not null check (support_month between 1 and 12),
  expected_amount bigint not null check (expected_amount >= 0),
  total_received bigint not null default 0 check (total_received >= 0),
  transferred_amount bigint not null default 0 check (transferred_amount >= 0),
  payment_status public.payment_status not null default 'unpaid',
  transferred_at timestamptz,
  transfer_reference text,
  notes text,
  created_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 (orphan_id, support_year, support_month),
  constraint transferred_date_required check (transferred_amount = 0 or transferred_at is not null)
);

create index monthly_support_period_idx
  on public.monthly_support_records(support_year desc, support_month desc);
create index monthly_support_orphan_idx on public.monthly_support_records(orphan_id);

create table public.monthly_contributor_snapshots (
  id uuid primary key default gen_random_uuid(),
  monthly_support_id uuid not null references public.monthly_support_records(id) on delete cascade,
  sponsorship_id uuid references public.sponsorships(id) on delete set null,
  donor_id uuid not null references public.profiles(id) on delete restrict,
  expected_amount bigint not null check (expected_amount >= 0),
  received_amount bigint not null default 0 check (received_amount >= 0),
  created_at timestamptz not null default now(),
  unique (monthly_support_id, donor_id)
);

create index monthly_snapshots_donor_idx on public.monthly_contributor_snapshots(donor_id);

create table public.payment_records (
  id uuid primary key default gen_random_uuid(),
  monthly_support_id uuid not null references public.monthly_support_records(id) on delete cascade,
  donor_id uuid not null references public.profiles(id) on delete restrict,
  amount bigint not null check (amount > 0),
  paid_at timestamptz not null,
  external_reference text,
  notes text,
  recorded_by uuid not null references public.profiles(id) on delete restrict,
  created_at timestamptz not null default now()
);

create index payment_records_monthly_idx on public.payment_records(monthly_support_id);
create index payment_records_donor_idx on public.payment_records(donor_id, paid_at desc);

create table public.file_assets (
  id uuid primary key default gen_random_uuid(),
  stored_name uuid not null unique,
  original_name text not null,
  extension text not null check (extension in ('pdf', 'jpg', 'jpeg', 'png', 'webp')),
  mime_type text not null check (mime_type in ('application/pdf', 'image/jpeg', 'image/png', 'image/webp')),
  size_bytes bigint not null check (size_bytes > 0 and size_bytes <= 52428800),
  category public.document_category not null,
  uploaded_by uuid not null references public.profiles(id) on delete restrict,
  created_at timestamptz not null default now()
);

create table public.orphan_documents (
  id uuid primary key default gen_random_uuid(),
  orphan_id uuid not null references public.orphans(id) on delete cascade,
  file_id uuid not null unique references public.file_assets(id) on delete cascade,
  title text not null,
  created_at timestamptz not null default now()
);

create index orphan_documents_orphan_idx on public.orphan_documents(orphan_id);

create table public.transfer_receipts (
  id uuid primary key default gen_random_uuid(),
  monthly_support_id uuid not null references public.monthly_support_records(id) on delete cascade,
  file_id uuid not null unique references public.file_assets(id) on delete cascade,
  uploaded_at timestamptz not null default now()
);

create index transfer_receipts_monthly_idx on public.transfer_receipts(monthly_support_id);

create table public.sponsorship_requests (
  id uuid primary key default gen_random_uuid(),
  orphan_id uuid references public.orphans(id) on delete set null,
  requester_user_id uuid references public.profiles(id) on delete set null,
  full_name text not null check (char_length(full_name) between 3 and 120),
  email text not null,
  phone text not null,
  proposed_monthly_amount bigint not null check (proposed_monthly_amount >= 1000),
  message text,
  status public.request_status not null default 'pending',
  reviewed_by uuid references public.profiles(id) on delete set null,
  reviewed_at timestamptz,
  admin_notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint request_email_format check (email ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$')
);

create index sponsorship_requests_status_idx on public.sponsorship_requests(status, created_at desc);
create index sponsorship_requests_user_idx on public.sponsorship_requests(requester_user_id);

create table public.notifications (
  id uuid primary key default gen_random_uuid(),
  recipient_id uuid not null references public.profiles(id) on delete cascade,
  title text not null,
  body text not null,
  link text,
  read_at timestamptz,
  created_at timestamptz not null default now()
);

create index notifications_recipient_idx on public.notifications(recipient_id, read_at, created_at desc);

create table public.app_settings (
  key text primary key,
  value jsonb not null,
  description text,
  updated_by uuid references public.profiles(id) on delete set null,
  updated_at timestamptz not null default now()
);

create table public.contact_messages (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null,
  message text not null,
  handled_at timestamptz,
  handled_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now()
);

create table public.audit_logs (
  id bigint generated always as identity primary key,
  actor_id uuid references public.profiles(id) on delete set null,
  action text not null,
  entity_type text not null,
  entity_id text,
  metadata jsonb not null default '{}'::jsonb,
  ip_address inet,
  user_agent text,
  created_at timestamptz not null default now()
);

create index audit_logs_actor_idx on public.audit_logs(actor_id, created_at desc);
create index audit_logs_entity_idx on public.audit_logs(entity_type, entity_id, created_at desc);
create index audit_logs_created_idx on public.audit_logs(created_at desc);

create or replace function public.set_updated_at()
returns trigger
language plpgsql
security invoker
set search_path = ''
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger profiles_set_updated_at before update on public.profiles
for each row execute function public.set_updated_at();
create trigger donor_profiles_set_updated_at before update on public.donor_profiles
for each row execute function public.set_updated_at();
create trigger orphans_set_updated_at before update on public.orphans
for each row execute function public.set_updated_at();
create trigger sponsorships_set_updated_at before update on public.sponsorships
for each row execute function public.set_updated_at();
create trigger monthly_support_set_updated_at before update on public.monthly_support_records
for each row execute function public.set_updated_at();
create trigger sponsorship_requests_set_updated_at before update on public.sponsorship_requests
for each row execute function public.set_updated_at();

create or replace function public.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 public.handle_new_user();

create or replace function public.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 public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select coalesce(public.current_profile_role() = 'admin', false)
$$;

create or replace function public.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 public.donor_may_view_documents(target_orphan uuid)
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select public.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 public.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 public.after_sponsorship_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform public.refresh_orphan_coverage(coalesce(new.orphan_id, old.orphan_id));
  if tg_op = 'UPDATE' and old.orphan_id <> new.orphan_id then
    perform public.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 public.after_sponsorship_change();

create or replace function public.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 public.after_payment_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform public.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 public.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 public.after_payment_change();

create or replace function public.create_monthly_support(target_year smallint, target_month smallint, actor uuid)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  created_count integer := 0;
  orphan_row record;
  monthly_id uuid;
begin
  if not public.is_admin() 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;
$$;

create view public.public_orphan_profiles
with (security_barrier = true)
as
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');

alter table public.profiles enable row level security;
alter table public.donor_profiles enable row level security;
alter table public.orphans enable row level security;
alter table public.guardian_consents enable row level security;
alter table public.sponsorships enable row level security;
alter table public.monthly_support_records enable row level security;
alter table public.monthly_contributor_snapshots enable row level security;
alter table public.payment_records enable row level security;
alter table public.file_assets enable row level security;
alter table public.orphan_documents enable row level security;
alter table public.transfer_receipts enable row level security;
alter table public.sponsorship_requests enable row level security;
alter table public.notifications enable row level security;
alter table public.app_settings enable row level security;
alter table public.contact_messages enable row level security;
alter table public.audit_logs enable row level security;

create policy profiles_self_or_admin_select on public.profiles
for select to authenticated
using (id = (select auth.uid()) or public.is_admin());
create policy profiles_admin_all on public.profiles
for all to authenticated
using (public.is_admin()) with check (public.is_admin());

create policy donor_profiles_self_or_admin_select on public.donor_profiles
for select to authenticated
using (profile_id = (select auth.uid()) or public.is_admin());
create policy donor_profiles_admin_all on public.donor_profiles
for all to authenticated
using (public.is_admin()) with check (public.is_admin());

create policy orphans_admin_all on public.orphans
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy orphans_linked_donor_select on public.orphans
for select to authenticated
using (public.donor_linked_to_orphan(id));

create policy guardian_consents_admin_all on public.guardian_consents
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy guardian_consents_linked_donor_select on public.guardian_consents
for select to authenticated
using (public.donor_linked_to_orphan(orphan_id));

create policy sponsorships_admin_all on public.sponsorships
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy sponsorships_donor_select on public.sponsorships
for select to authenticated
using (donor_id = (select auth.uid()));

create policy monthly_support_admin_all on public.monthly_support_records
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy monthly_support_linked_donor_select on public.monthly_support_records
for select to authenticated
using (public.donor_linked_to_orphan(orphan_id));

create policy snapshots_admin_all on public.monthly_contributor_snapshots
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy snapshots_linked_donor_select on public.monthly_contributor_snapshots
for select to authenticated
using (
  exists (
    select 1 from public.monthly_support_records m
    where m.id = monthly_support_id and public.donor_linked_to_orphan(m.orphan_id)
  )
);

create policy payments_admin_all on public.payment_records
for all to authenticated
using (public.is_admin()) with check (public.is_admin());
create policy payments_linked_donor_select on public.payment_records
for select to authenticated
using (
  exists (
    select 1 from public.monthly_support_records m
    where m.id = monthly_support_id and public.donor_linked_to_orphan(m.orphan_id)
  )
);

create policy file_assets_admin_select on public.file_assets
for select to authenticated using (public.is_admin());
create policy file_assets_admin_all on public.file_assets
for all to authenticated using (public.is_admin()) with check (public.is_admin());
create policy file_assets_authorized_donor_select on public.file_assets
for select to authenticated
using (
  exists (
    select 1
    from public.orphan_documents d
    where d.file_id = public.file_assets.id and public.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 public.donor_linked_to_orphan(m.orphan_id)
  )
);

create policy orphan_documents_admin_all on public.orphan_documents
for all to authenticated using (public.is_admin()) with check (public.is_admin());
create policy orphan_documents_donor_select on public.orphan_documents
for select to authenticated using (public.donor_may_view_documents(orphan_id));

create policy transfer_receipts_admin_all on public.transfer_receipts
for all to authenticated using (public.is_admin()) with check (public.is_admin());
create policy transfer_receipts_donor_select on public.transfer_receipts
for select to authenticated
using (
  exists (
    select 1 from public.monthly_support_records m
    where m.id = monthly_support_id and public.donor_linked_to_orphan(m.orphan_id)
  )
);

create policy requests_admin_all on public.sponsorship_requests
for all to authenticated using (public.is_admin()) with check (public.is_admin());
create policy requests_owner_select on public.sponsorship_requests
for select to authenticated using (requester_user_id = (select auth.uid()));

create policy notifications_owner_select on public.notifications
for select to authenticated using (recipient_id = (select auth.uid()) or public.is_admin());
create policy notifications_owner_update on public.notifications
for update to authenticated
using (recipient_id = (select auth.uid()))
with check (recipient_id = (select auth.uid()));
create policy notifications_admin_all on public.notifications
for all to authenticated using (public.is_admin()) with check (public.is_admin());

create policy settings_authenticated_select on public.app_settings
for select to authenticated using (true);
create policy settings_admin_all on public.app_settings
for all to authenticated using (public.is_admin()) with check (public.is_admin());

create policy contact_admin_select on public.contact_messages
for select to authenticated using (public.is_admin());
create policy contact_admin_update on public.contact_messages
for update to authenticated using (public.is_admin()) with check (public.is_admin());

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

revoke all on public.orphans from anon;
revoke all on public.profiles from anon;
revoke all on public.file_assets from anon;
revoke all on public.orphan_documents from anon;
revoke all on public.transfer_receipts from anon;
grant select on public.public_orphan_profiles to anon, authenticated;
grant select on public.profiles, public.donor_profiles, public.orphans, public.guardian_consents,
  public.sponsorships, public.monthly_support_records, public.monthly_contributor_snapshots,
  public.payment_records, public.file_assets, public.orphan_documents, public.transfer_receipts,
  public.sponsorship_requests, public.notifications, public.app_settings, public.contact_messages,
  public.audit_logs to authenticated;
grant insert, update, delete on public.profiles, public.donor_profiles, public.orphans,
  public.guardian_consents, public.sponsorships, public.monthly_support_records,
  public.monthly_contributor_snapshots, public.payment_records, public.file_assets,
  public.orphan_documents, public.transfer_receipts, public.sponsorship_requests,
  public.notifications, public.app_settings, public.contact_messages to authenticated;
revoke update on public.profiles from authenticated;
grant update (full_name, phone, last_activity_at, updated_at) on public.profiles to authenticated;
revoke update on public.notifications from authenticated;
grant update (read_at) on public.notifications to authenticated;
grant usage, select on sequence public.audit_logs_id_seq to authenticated;
grant execute on function public.current_profile_role() to authenticated;
grant execute on function public.is_admin() to authenticated;
grant execute on function public.donor_linked_to_orphan(uuid) to authenticated;
grant execute on function public.donor_may_view_documents(uuid) to authenticated;
grant execute on function public.create_monthly_support(smallint, smallint, uuid) to authenticated;

insert into public.app_settings (key, value, description)
values
  ('default_monthly_support', '50000'::jsonb, 'قيمة الكفالة الشهرية الافتراضية بالجنيه السوداني'),
  ('organization_name', '"منصة كفالة"'::jsonb, 'اسم المؤسسة الظاهر'),
  ('session_idle_minutes', '30'::jsonb, 'مدة خمول الجلسة بالدقائق')
on conflict (key) do nothing;

commit;
