CREATE OR REPLACE FUNCTION public.fn_get_invoice_remaining_balance(p_invoice_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
    SELECT GREATEST(
        i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0),
        0
    )
    FROM public.invoices i
    LEFT JOIN public.payments p ON p.invoice_id = i.invoice_id
    WHERE i.invoice_id = p_invoice_id
    GROUP BY i.invoice_id, i.total_amount;
$$;