DatabaseProgramming: 04 Suspend overdue accounts.sql

DROP PROCEDURE IF EXISTS public.proc_suspend_overdue_accounts();
DROP PROCEDURE IF EXISTS public.proc_suspend_overdue_accounts(date);

CREATE PROCEDURE public.proc_suspend_overdue_accounts(p_as_of_date date DEFAULT CURRENT_DATE)
LANGUAGE plpgsql
AS $$
DECLARE
    v_invoices_marked integer;
    v_accounts_suspended integer;
    v_subscriptions_suspended integer;
BEGIN
    UPDATE public.invoices i
    SET status = 'overdue'
    WHERE i.status IN ('issued', 'partially_paid')
      AND i.due_date < p_as_of_date
      AND public.fn_get_invoice_remaining_balance(i.invoice_id) > 0.01;
    GET DIAGNOSTICS v_invoices_marked = ROW_COUNT;

    UPDATE public.accounts a
    SET account_status = 'suspended'
    WHERE a.account_status = 'active'
      AND EXISTS (
          SELECT 1 FROM public.invoices i
          WHERE i.account_id = a.account_id
            AND i.status = 'overdue'
            AND public.fn_get_invoice_remaining_balance(i.invoice_id) > 0.01
      );
    GET DIAGNOSTICS v_accounts_suspended = ROW_COUNT;

    PERFORM set_config('app.change_reason', 'Suspended automatically because account has overdue debt', true);
    UPDATE public.subscriptions s
    SET status = 'suspended'
    WHERE s.status = 'active'
      AND EXISTS (
          SELECT 1 FROM public.accounts a
          WHERE a.account_id = s.account_id AND a.account_status = 'suspended'
      );
    GET DIAGNOSTICS v_subscriptions_suspended = ROW_COUNT;

    RAISE NOTICE '% invoice(s) marked overdue, % account(s) and % subscription(s) suspended.',
        v_invoices_marked, v_accounts_suspended, v_subscriptions_suspended;
END;
$$;