begin;

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 integer;
  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::smallint,
      make_date(target_year, month_number, 1)
        >= date_trunc('month', current_date)::date,
      'automatic'::text
    ) then
      captured_count := captured_count + 1;
    end if;
  end loop;

  return captured_count;
end;
$$;

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;

notify pgrst, 'reload schema';

commit;
