CREATE OR REPLACE FUNCTION trg_fn_prevent_overpaid_invoice()
    RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_invoice_total NUMERIC;
    v_invoice_status TEXT;
    v_total_paid    NUMERIC;
BEGIN

    IF NEW.invoice_id IS NULL THEN
        RETURN NEW;
    END IF;

    IF NEW.status <> 'completed' THEN
        RETURN NEW;
    END IF;


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

    IF v_invoice_status = 'cancelled'
        THEN RAISE EXCEPTION 'PAYMENT REJECTED. Invoice % is cancelled',NEW.invoice_id;
    END IF;

    SELECT COALESCE(SUM(amount), 0) --ushe kolu ostanuva od fakturata
    INTO v_total_paid
    FROM payments
    WHERE invoice_id = NEW.invoice_id
      AND status = 'completed'
      AND payment_id <> NEW.payment_id;

    --ako e vekje platena
    IF v_total_paid >= v_invoice_total THEN
        RAISE EXCEPTION
            'Payment rejected. Invoice % is already fully paid.',
            NEW.invoice_id;
    END IF;

   --ako se plakja povekje od toa sto treba
    IF v_total_paid + NEW.amount > v_invoice_total THEN
        RAISE EXCEPTION
            'Payment rejected. Invoice % has remaining balance %, but attempted payment is %.',
            NEW.invoice_id,
            v_invoice_total - v_total_paid,
            NEW.amount;
    END IF;

    RETURN NEW;
END;
$$;
-- idejata e ako nekoj proba da plati vekje platena faktura da ja odbie
-- ili ako plati povekje od ostatokot
-- sluzhi i za protection na cutomer balance triggerot bidejki gi odbiva paymentsot


DROP TRIGGER IF EXISTS trg_prevent_overpaid_invoice ON payments;
drop function if exists trg_fn_prevent_overpaid_invoice();

CREATE TRIGGER trg_prevent_overpaid_invoice
BEFORE INSERT OR UPDATE ON payments
FOR EACH ROW
EXECUTE FUNCTION trg_fn_prevent_overpaid_invoice();