DatabaseProgramming: Suspend accounts for invoice overdue.sql

CREATE OR REPLACE PROCEDURE proc_suspend_overdue_accounts()
LANGUAGE plpgsql
AS $$
DECLARE
    v_accounts_suspended INT;
    v_subscriptions_suspended INT;
BEGIN
    -- site invoices gi markirame so overdue
    UPDATE invoices i
    SET status = 'overdue'
    FROM accounts a
    LEFT JOIN billing_cycles bc
        ON bc.billing_cycle_id = a.billing_cycle_id
    WHERE i.account_id = a.account_id
      AND i.status = 'issued'
      AND i.due_date + bc.grace_days < CURRENT_DATE;


    -- suspendirame accounts
    UPDATE accounts a
    SET account_status = 'suspended',
        updated_at = CURRENT_TIMESTAMP
    WHERE a.account_status = 'active'
      AND EXISTS (
          SELECT 1
          FROM invoices i
          WHERE i.account_id = a.account_id
            AND i.status = 'overdue'
      );

    GET DIAGNOSTICS v_accounts_suspended = ROW_COUNT;


    -- suspendirame subscriptions
    UPDATE subscriptions s
    SET status = 'suspended'
    WHERE s.status = 'active'
      AND s.account_id IN (
          SELECT account_id
          FROM accounts
          WHERE account_status = 'suspended'
      );

    GET DIAGNOSTICS v_subscriptions_suspended = ROW_COUNT;


    RAISE NOTICE '% account(s) suspended.', v_accounts_suspended;
    RAISE NOTICE '% subscription(s) suspended.', v_subscriptions_suspended;
END;
$$;

--korisnikot ne smee da gi pravi drugi raboti se dodeka ne se plati fakturata

SELECT
    a.account_id,
    a.account_number,
    a.account_status,
    i.invoice_id,
    i.due_date,
    COALESCE(bc.grace_days, 0) AS grace_days,
    i.due_date + COALESCE(bc.grace_days, 0) AS suspend_after,
    i.status
FROM accounts a
JOIN invoices i
    ON i.account_id = a.account_id
LEFT JOIN billing_cycles bc
    ON bc.billing_cycle_id = a.billing_cycle_id
WHERE a.account_status = 'active'
  AND i.status = 'issued'
  AND i.due_date + COALESCE(bc.grace_days, 0) < CURRENT_DATE;


BEGIN;

CALL proc_suspend_overdue_accounts();

ROLLBACK;