begin;

-- Reporting dimensions are intentionally detached from cascading foreign keys.
-- A later account or case cleanup must never erase a closed historical month.
create table public.reporting_monthly_periods (
  report_year smallint not null check (report_year between 2020 and 2200),
  report_month smallint not null check (report_month between 1 and 12),
  captured_at timestamptz not null default now(),
  capture_source text not null default 'automatic'
    check (capture_source in ('automatic', 'backfill')),
  primary key (report_year, report_month)
);

create table public.reporting_orphan_monthly_snapshots (
  report_year smallint not null check (report_year between 2020 and 2200),
  report_month smallint not null check (report_month between 1 and 12),
  orphan_id uuid not null,
  case_number text not null,
  full_name_snapshot text,
  monthly_need_snapshot bigint check (
    monthly_need_snapshot is null or monthly_need_snapshot >= 0
  ),
  status_snapshot public.orphan_status not null,
  orphan_created_at timestamptz not null,
  captured_at timestamptz not null default now(),
  primary key (report_year, report_month, orphan_id)
);

create index reporting_orphan_snapshots_orphan_idx
  on public.reporting_orphan_monthly_snapshots(
    orphan_id,
    report_year desc,
    report_month desc
  );

create table public.reporting_sponsorship_monthly_snapshots (
  report_year smallint not null check (report_year between 2020 and 2200),
  report_month smallint not null check (report_month between 1 and 12),
  sponsorship_id uuid not null,
  sponsor_id uuid not null,
  orphan_id uuid not null,
  sponsor_name_snapshot text not null,
  orphan_name_snapshot text,
  case_number_snapshot text not null,
  monthly_amount_snapshot bigint not null check (monthly_amount_snapshot >= 0),
  status_snapshot public.sponsorship_status not null,
  sponsorship_start_date date not null,
  sponsorship_end_date date,
  sponsor_created_at timestamptz not null,
  orphan_created_at timestamptz not null,
  captured_at timestamptz not null default now(),
  primary key (report_year, report_month, sponsorship_id),
  constraint reporting_sponsorship_snapshot_dates_valid
    check (
      sponsorship_end_date is null
      or sponsorship_end_date >= sponsorship_start_date
    )
);

create index reporting_sponsorship_snapshots_sponsor_idx
  on public.reporting_sponsorship_monthly_snapshots(
    sponsor_id,
    report_year desc,
    report_month desc
  );
create index reporting_sponsorship_snapshots_orphan_idx
  on public.reporting_sponsorship_monthly_snapshots(
    orphan_id,
    report_year desc,
    report_month desc
  );

comment on table public.reporting_monthly_periods is
  'Captured reporting months. Closed periods are immutable.';
comment on table public.reporting_orphan_monthly_snapshots is
  'Monthly orphan state used for historical reporting. Rows deliberately survive source deletion.';
comment on table public.reporting_sponsorship_monthly_snapshots is
  'Monthly active sponsorship state and amount snapshots. Rows deliberately survive source deletion.';

create or replace function private.protect_closed_reporting_snapshot()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  protected_year smallint;
  protected_month smallint;
begin
  if tg_op = 'INSERT' then
    if make_date(new.report_year, new.report_month, 1)
      < date_trunc('month', current_date)::date
      and exists (
        select 1
        from public.reporting_monthly_periods period
        where period.report_year = new.report_year
          and period.report_month = new.report_month
      )
    then
      raise exception 'closed reporting snapshots are immutable';
    end if;
    return new;
  end if;

  protected_year := old.report_year;
  protected_month := old.report_month;
  if make_date(protected_year, protected_month, 1)
    < date_trunc('month', current_date)::date
  then
    raise exception 'closed reporting snapshots are immutable';
  end if;
  return coalesce(new, old);
end;
$$;

create trigger reporting_periods_closed_immutable
before update or delete on public.reporting_monthly_periods
for each row execute function private.protect_closed_reporting_snapshot();

create trigger reporting_orphan_snapshots_closed_immutable
before insert or update or delete on public.reporting_orphan_monthly_snapshots
for each row execute function private.protect_closed_reporting_snapshot();

create trigger reporting_sponsorship_snapshots_closed_immutable
before insert or update or delete on public.reporting_sponsorship_monthly_snapshots
for each row execute function private.protect_closed_reporting_snapshot();

create or replace function private.capture_reporting_month(
  target_year smallint,
  target_month smallint,
  force_refresh boolean default false,
  source_name text default 'automatic'
)
returns boolean
language plpgsql
security definer
set search_path = ''
as $$
declare
  month_start date;
  month_end date;
  current_month_start date := date_trunc('month', current_date)::date;
  period_exists boolean;
begin
  if target_year not between 2020 and 2200
    or target_month not between 1 and 12
  then
    raise exception 'invalid reporting period';
  end if;
  if source_name not in ('automatic', 'backfill') then
    raise exception 'invalid reporting capture source';
  end if;

  month_start := make_date(target_year, target_month, 1);
  month_end := (month_start + interval '1 month - 1 day')::date;

  select exists (
    select 1
    from public.reporting_monthly_periods period
    where period.report_year = target_year
      and period.report_month = target_month
  ) into period_exists;

  -- Once a past month has been captured it can never be reconstructed from
  -- mutable source rows. Financial ledgers remain live and are joined later.
  if month_start < current_month_start and period_exists then
    return false;
  end if;
  if period_exists and not force_refresh then
    return false;
  end if;

  if period_exists then
    delete from public.reporting_sponsorship_monthly_snapshots
    where report_year = target_year and report_month = target_month;
    delete from public.reporting_orphan_monthly_snapshots
    where report_year = target_year and report_month = target_month;
  end if;

  insert into public.reporting_orphan_monthly_snapshots (
    report_year,
    report_month,
    orphan_id,
    case_number,
    full_name_snapshot,
    monthly_need_snapshot,
    status_snapshot,
    orphan_created_at
  )
  select
    target_year,
    target_month,
    orphan.id,
    orphan.case_number,
    orphan.full_name,
    orphan.monthly_need,
    orphan.status,
    orphan.created_at
  from public.orphans orphan
  where orphan.created_at::date <= month_end;

  insert into public.reporting_sponsorship_monthly_snapshots (
    report_year,
    report_month,
    sponsorship_id,
    sponsor_id,
    orphan_id,
    sponsor_name_snapshot,
    orphan_name_snapshot,
    case_number_snapshot,
    monthly_amount_snapshot,
    status_snapshot,
    sponsorship_start_date,
    sponsorship_end_date,
    sponsor_created_at,
    orphan_created_at
  )
  select
    target_year,
    target_month,
    sponsorship.id,
    sponsorship.donor_id,
    sponsorship.orphan_id,
    sponsor.full_name,
    orphan.full_name,
    orphan.case_number,
    sponsorship.monthly_amount,
    sponsorship.status,
    sponsorship.start_date,
    sponsorship.end_date,
    sponsor.created_at,
    orphan.created_at
  from public.sponsorships sponsorship
  join public.profiles sponsor
    on sponsor.id = sponsorship.donor_id
   and sponsor.role = 'donor'
  join public.orphans orphan
    on orphan.id = sponsorship.orphan_id
  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 is not null
        and sponsorship.end_date >= month_start
      )
    );

  insert into public.reporting_monthly_periods (
    report_year,
    report_month,
    captured_at,
    capture_source
  )
  values (target_year, target_month, now(), source_name)
  on conflict (report_year, report_month)
  do update set
    captured_at = excluded.captured_at,
    capture_source = excluded.capture_source;

  return true;
end;
$$;

create or replace function public.ensure_reporting_monthly_snapshot(
  target_year smallint,
  target_month smallint
)
returns boolean
language plpgsql
security definer
set search_path = ''
as $$
declare
  target_start date := make_date(target_year, target_month, 1);
begin
  return private.capture_reporting_month(
    target_year,
    target_month,
    target_start >= date_trunc('month', current_date)::date,
    'automatic'
  );
end;
$$;

create or replace function public.ensure_reporting_year(target_year smallint)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  current_year smallint := extract(year from current_date)::smallint;
  current_month smallint := extract(month from current_date)::smallint;
  latest_financial_month smallint;
  latest_month smallint;
  month_number smallint;
  captured_count integer := 0;
begin
  if target_year not between 2020 and 2200 then
    raise exception 'invalid reporting year';
  end if;

  select max(financial_month)::smallint
  into latest_financial_month
  from (
    select payment_month as financial_month
    from public.sponsor_monthly_payments
    where payment_year = target_year
    union all
    select transfer_month as financial_month
    from public.orphan_transfers
    where transfer_year = target_year
  ) financial_months;

  latest_month := case
    when target_year < current_year then 12
    when target_year = current_year
      then greatest(current_month, coalesce(latest_financial_month, current_month))
    else coalesce(latest_financial_month, 0)
  end;

  if latest_month = 0 then
    return 0;
  end if;

  for month_number in 1..latest_month loop
    if private.capture_reporting_month(
      target_year,
      month_number,
      make_date(target_year, month_number, 1)
        >= date_trunc('month', current_date)::date,
      'automatic'
    ) then
      captured_count := captured_count + 1;
    end if;
  end loop;

  return captured_count;
end;
$$;

create or replace function private.refresh_current_reporting_snapshot()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform private.capture_reporting_month(
    extract(year from current_date)::smallint,
    extract(month from current_date)::smallint,
    true,
    'automatic'
  );
  return null;
end;
$$;

create trigger orphan_refreshes_current_reporting_snapshot
after insert or update or delete on public.orphans
for each statement execute function private.refresh_current_reporting_snapshot();

create trigger sponsorship_refreshes_current_reporting_snapshot
after insert or update or delete on public.sponsorships
for each statement execute function private.refresh_current_reporting_snapshot();

create trigger sponsor_profile_refreshes_current_reporting_snapshot
after update of full_name, is_active on public.profiles
for each statement execute function private.refresh_current_reporting_snapshot();

-- Seed every recoverable month. Existing payment obligations and transfers
-- extend the backfill through their latest month in the current year.
do $$
declare
  earliest_month date;
  latest_financial_month date;
  latest_month date;
  month_cursor date;
begin
  select min(source_month)
  into earliest_month
  from (
    select date_trunc('month', orphan.created_at)::date as source_month
    from public.orphans orphan
    union all
    select date_trunc('month', sponsorship.start_date)::date as source_month
    from public.sponsorships sponsorship
    union all
    select make_date(payment_year, payment_month, 1) as source_month
    from public.sponsor_monthly_payments
    union all
    select make_date(transfer_year, transfer_month, 1) as source_month
    from public.orphan_transfers
  ) source_months;

  select max(source_month)
  into latest_financial_month
  from (
    select make_date(payment_year, payment_month, 1) as source_month
    from public.sponsor_monthly_payments
    where payment_year = extract(year from current_date)::smallint
    union all
    select make_date(transfer_year, transfer_month, 1) as source_month
    from public.orphan_transfers
    where transfer_year = extract(year from current_date)::smallint
  ) financial_months;

  earliest_month := greatest(
    coalesce(earliest_month, date_trunc('month', current_date)::date),
    make_date(2020, 1, 1)
  );
  latest_month := greatest(
    date_trunc('month', current_date)::date,
    coalesce(latest_financial_month, date_trunc('month', current_date)::date)
  );
  latest_month := least(
    latest_month,
    make_date(extract(year from current_date)::integer, 12, 1)
  );

  month_cursor := earliest_month;
  while month_cursor <= latest_month loop
    perform private.capture_reporting_month(
      extract(year from month_cursor)::smallint,
      extract(month from month_cursor)::smallint,
      true,
      'backfill'
    );
    month_cursor := (month_cursor + interval '1 month')::date;
  end loop;
end;
$$;

create or replace view public.reporting_monthly_history
with (security_invoker = true)
as
with sponsorship_totals as (
  select
    snapshot.report_year,
    snapshot.report_month,
    count(distinct snapshot.orphan_id)::integer as sponsored_orphan_count,
    count(distinct snapshot.sponsor_id)::integer as active_sponsor_count,
    coalesce(sum(snapshot.monthly_amount_snapshot), 0)::bigint
      as sponsor_commitment_amount
  from public.reporting_sponsorship_monthly_snapshots snapshot
  group by snapshot.report_year, snapshot.report_month
),
orphan_totals as (
  select
    orphan_snapshot.report_year,
    orphan_snapshot.report_month,
    count(*) filter (
      where extract(year from orphan_snapshot.orphan_created_at)::smallint
        = orphan_snapshot.report_year
        and extract(month from orphan_snapshot.orphan_created_at)::smallint
        = orphan_snapshot.report_month
    )::integer as new_orphan_count,
    coalesce(sum(orphan_snapshot.monthly_need_snapshot) filter (
      where exists (
        select 1
        from public.reporting_sponsorship_monthly_snapshots sponsorship_snapshot
        where sponsorship_snapshot.report_year = orphan_snapshot.report_year
          and sponsorship_snapshot.report_month = orphan_snapshot.report_month
          and sponsorship_snapshot.orphan_id = orphan_snapshot.orphan_id
      )
    ), 0)::bigint as required_sponsorship_amount
  from public.reporting_orphan_monthly_snapshots orphan_snapshot
  group by orphan_snapshot.report_year, orphan_snapshot.report_month
),
payment_totals as (
  select
    payment.payment_year as report_year,
    payment.payment_month as report_month,
    coalesce(sum(payment.received_amount), 0)::bigint as sponsor_payment_amount
  from public.sponsor_monthly_payments payment
  group by payment.payment_year, payment.payment_month
),
transfer_totals as (
  select
    transfer.transfer_year as report_year,
    transfer.transfer_month as report_month,
    coalesce(sum(transfer.amount), 0)::bigint as orphan_transfer_amount
  from public.orphan_transfers transfer
  group by transfer.transfer_year, transfer.transfer_month
)
select
  period.report_year,
  period.report_month,
  coalesce(sponsorship.sponsored_orphan_count, 0) as sponsored_orphan_count,
  coalesce(orphan.new_orphan_count, 0) as new_orphan_count,
  coalesce(sponsorship.active_sponsor_count, 0) as active_sponsor_count,
  coalesce(orphan.required_sponsorship_amount, 0)::bigint
    as required_sponsorship_amount,
  coalesce(sponsorship.sponsor_commitment_amount, 0)::bigint
    as sponsor_commitment_amount,
  coalesce(payment.sponsor_payment_amount, 0)::bigint
    as sponsor_payment_amount,
  coalesce(transfer.orphan_transfer_amount, 0)::bigint
    as orphan_transfer_amount,
  period.captured_at
from public.reporting_monthly_periods period
left join sponsorship_totals sponsorship
  on sponsorship.report_year = period.report_year
 and sponsorship.report_month = period.report_month
left join orphan_totals orphan
  on orphan.report_year = period.report_year
 and orphan.report_month = period.report_month
left join payment_totals payment
  on payment.report_year = period.report_year
 and payment.report_month = period.report_month
left join transfer_totals transfer
  on transfer.report_year = period.report_year
 and transfer.report_month = period.report_month;

alter table public.reporting_monthly_periods enable row level security;
alter table public.reporting_orphan_monthly_snapshots enable row level security;
alter table public.reporting_sponsorship_monthly_snapshots enable row level security;

create policy reporting_periods_admin_read
on public.reporting_monthly_periods
for select to authenticated
using (private.is_admin());

create policy reporting_orphan_snapshots_admin_read
on public.reporting_orphan_monthly_snapshots
for select to authenticated
using (private.is_admin());

create policy reporting_sponsorship_snapshots_admin_read
on public.reporting_sponsorship_monthly_snapshots
for select to authenticated
using (private.is_admin());

revoke all on public.reporting_monthly_periods,
  public.reporting_orphan_monthly_snapshots,
  public.reporting_sponsorship_monthly_snapshots
  from anon, authenticated;
grant select on public.reporting_monthly_periods,
  public.reporting_orphan_monthly_snapshots,
  public.reporting_sponsorship_monthly_snapshots
  to authenticated;
grant all on public.reporting_monthly_periods,
  public.reporting_orphan_monthly_snapshots,
  public.reporting_sponsorship_monthly_snapshots
  to service_role;

revoke all on public.reporting_monthly_history from anon, authenticated;
grant select on public.reporting_monthly_history to service_role;

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

revoke all on function private.protect_closed_reporting_snapshot()
  from public, anon, authenticated;
revoke all on function private.capture_reporting_month(
  smallint, smallint, boolean, text
) from public, anon, authenticated;
revoke all on function private.refresh_current_reporting_snapshot()
  from public, anon, authenticated;

notify pgrst, 'reload schema';

commit;