DatabaseProgramming: 05 Record invoice payment.sql

DROP PROCEDURE IF EXISTS public.proc_record_payment(bigint, bigint, bigint, numeric, text);

CREATE PROCEDURE public.proc_record_payment(
    p_account_id bigint,
    p_invoice_id bigint,
    p_payment_method_id bigint,
    p_amount numeric,
    p_reference_number text DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_payment_id bigint;
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Payment amount must be positive.';
    END IF;
    IF NOT EXISTS (
        SELECT 1 FROM public.invoices
        WHERE invoice_id = p_invoice_id AND account_id = p_account_id AND status <> 'cancelled'
    ) THEN
        RAISE EXCEPTION 'Invoice % is invalid for account %.', p_invoice_id, p_account_id;
    END IF;
    IF NOT EXISTS (
        SELECT 1 FROM public.payment_methods
        WHERE payment_method_id = p_payment_method_id AND status = 'active'
    ) THEN
        RAISE EXCEPTION 'Payment method % is not active.', p_payment_method_id;
    END IF;

    INSERT INTO public.payments (
        account_id, invoice_id, payment_method_id, payment_date,
        amount, reference_number, status
    ) VALUES (
        p_account_id, p_invoice_id, p_payment_method_id, CURRENT_TIMESTAMP,
        p_amount, p_reference_number, 'completed'
    ) RETURNING payment_id INTO v_payment_id;

    RAISE NOTICE 'Payment % recorded successfully.', v_payment_id;
END;
$$;