DatabaseCreation: Account balance trigger.sql

CREATE OR REPLACE FUNCTION trg_fn_sync_account_balance_on_payment()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_invoice_total NUMERIC;
    v_total_paid    NUMERIC;
BEGIN
    --promena na account balance
    IF TG_OP = 'INSERT' THEN

        -- New completed payment reduces customer debt
        IF NEW.status = 'completed' THEN
            UPDATE accounts
            SET current_balance = current_balance - NEW.amount,
                updated_at = CURRENT_TIMESTAMP
            WHERE account_id = NEW.account_id;
        END IF;

    ELSIF TG_OP = 'UPDATE' THEN

        -- sve okej
        IF OLD.status <> 'completed' AND NEW.status = 'completed' THEN
            UPDATE accounts
            SET current_balance = current_balance - NEW.amount,
                updated_at = CURRENT_TIMESTAMP
            WHERE account_id = NEW.account_id;

        -- dokolku ima greshka ili e odbien ili pending seuste se vrakjaat parite
        ELSIF OLD.status = 'completed' AND NEW.status <> 'completed' THEN
            UPDATE accounts
            SET current_balance = current_balance + OLD.amount,
                updated_at = CURRENT_TIMESTAMP
            WHERE account_id = NEW.account_id;

        -- ova e dokolku treba da se vratat pari (ima promena vo cenata na nesto)
        ELSIF OLD.status = 'completed'
              AND NEW.status = 'completed'
              AND OLD.amount <> NEW.amount THEN
            UPDATE accounts
            SET current_balance = current_balance - (NEW.amount - OLD.amount),
                updated_at = CURRENT_TIMESTAMP
            WHERE account_id = NEW.account_id;
        END IF;

    END IF;


    IF NEW.invoice_id IS NOT NULL THEN  --pravime update na invoice status

        SELECT total_amount
        INTO v_invoice_total
        FROM invoices
        WHERE invoice_id = NEW.invoice_id;

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

        UPDATE invoices
        SET status =
            CASE
                WHEN v_total_paid >= v_invoice_total THEN 'paid'
                WHEN due_date < CURRENT_DATE THEN 'overdue'
                ELSE 'issued'
            END
        WHERE invoice_id = NEW.invoice_id
          AND status <> 'cancelled';

    END IF;

    RETURN NEW;
END;
$$;


DROP TRIGGER trg_sync_account_balance_on_payment ON payments;
DROP function  trg_fn_sync_account_balance_on_payment();

CREATE TRIGGER trg_sync_account_balance_on_payment
AFTER INSERT OR UPDATE ON payments
FOR EACH ROW
EXECUTE FUNCTION trg_fn_sync_account_balance_on_payment();