DatabaseProgramming: 04 Payment financial sync trigger.sql

CREATE OR REPLACE FUNCTION public.fn_recalculate_account_balance(p_account_id bigint)
RETURNS void
LANGUAGE sql
AS $$
    UPDATE public.accounts a
    SET current_balance = GREATEST(COALESCE((
        SELECT SUM(i.total_amount - COALESCE((
            SELECT SUM(p.amount)
            FROM public.payments p
            WHERE p.invoice_id = i.invoice_id AND p.status = 'completed'
        ), 0))
        FROM public.invoices i
        WHERE i.account_id = p_account_id AND i.status <> 'cancelled'
    ), 0), 0)
    WHERE a.account_id = p_account_id;
$$;

CREATE OR REPLACE FUNCTION public.fn_recalculate_invoice_status(p_invoice_id bigint)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_total numeric;
    v_paid numeric;
    v_due_date date;
    v_status text;
BEGIN
    SELECT total_amount, due_date, status
    INTO v_total, v_due_date, v_status
    FROM public.invoices
    WHERE invoice_id = p_invoice_id;

    IF NOT FOUND OR v_status = 'cancelled' THEN
        RETURN;
    END IF;

    SELECT COALESCE(SUM(amount), 0)
    INTO v_paid
    FROM public.payments
    WHERE invoice_id = p_invoice_id AND status = 'completed';

    UPDATE public.invoices
    SET status = CASE
        WHEN v_paid >= v_total - 0.01 THEN 'paid'
        WHEN v_due_date < CURRENT_DATE AND v_paid < v_total - 0.01 THEN 'overdue'
        WHEN v_paid > 0 THEN 'partially_paid'
        ELSE 'issued'
    END
    WHERE invoice_id = p_invoice_id;
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_financials_on_payment ON public.payments;
CREATE OR REPLACE FUNCTION public.trg_fn_sync_financials_on_payment()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_invoice_id bigint;
    v_old_invoice_id bigint;
    v_new_account_id bigint;
    v_old_account_id bigint;
BEGIN
    IF TG_OP <> 'DELETE' THEN
        v_new_invoice_id := NEW.invoice_id;
        v_new_account_id := NEW.account_id;
    END IF;
    IF TG_OP <> 'INSERT' THEN
        v_old_invoice_id := OLD.invoice_id;
        v_old_account_id := OLD.account_id;
    END IF;

    IF v_new_invoice_id IS NOT NULL THEN
        PERFORM public.fn_recalculate_invoice_status(v_new_invoice_id);
    END IF;
    IF v_old_invoice_id IS NOT NULL AND v_old_invoice_id IS DISTINCT FROM v_new_invoice_id THEN
        PERFORM public.fn_recalculate_invoice_status(v_old_invoice_id);
    END IF;
    IF v_new_account_id IS NOT NULL THEN
        PERFORM public.fn_recalculate_account_balance(v_new_account_id);
    END IF;
    IF v_old_account_id IS NOT NULL AND v_old_account_id IS DISTINCT FROM v_new_account_id THEN
        PERFORM public.fn_recalculate_account_balance(v_old_account_id);
    END IF;

    IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_financials_on_payment
AFTER INSERT OR UPDATE OR DELETE ON public.payments
FOR EACH ROW
EXECUTE FUNCTION public.trg_fn_sync_financials_on_payment();