DatabaseCreation: Outstanding customer balance.sql

CREATE OR REPLACE FUNCTION fn_get_customer_outstanding_balance(p_customer_id BIGINT)
    RETURNS NUMERIC LANGUAGE plpgsql AS $$
DECLARE v_outstanding NUMERIC;
BEGIN
    SELECT COALESCE(
               SUM(i.total_amount) - COALESCE(SUM(p_paid.amount_paid), 0), 0)
      INTO v_outstanding
      FROM accounts a
      JOIN invoices i ON i.account_id = a.account_id
      LEFT JOIN (
           SELECT invoice_id, SUM(amount) AS amount_paid
             FROM payments WHERE status = 'completed' GROUP BY invoice_id
           ) p_paid ON p_paid.invoice_id = i.invoice_id
     WHERE a.customer_id = p_customer_id
       AND i.status NOT IN ('cancelled', 'paid');

    RETURN GREATEST(v_outstanding, 0);
END;
$$;

SELECT fn_get_customer_outstanding_balance(1) AS total_outstanding;


-- ostatok na unpaide i uncalled za faktura

SELECT
    SUM(remaining_for_invoice) AS manual_total
FROM (
    SELECT
        i.invoice_id,
        i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0) AS remaining_for_invoice
    FROM accounts a
    JOIN invoices i
        ON i.account_id = a.account_id
    LEFT JOIN payments p
        ON p.invoice_id = i.invoice_id
    WHERE a.customer_id = 1
      AND i.status not in('canceled','paid')
    GROUP BY i.invoice_id, i.total_amount
) x;