CREATE DEFINER=CURRENT_USER PROCEDURE `customer_balancerpt`(IN `customer_id_filter` INT)
BEGIN
SELECT 
    p.name AS 'Patient Name',
    p.patient_id AS 'Patient ID',
    p.phone AS 'Phone',
    COALESCE(SUM(pay.total_debit), 0) AS 'Total Debit',
    COALESCE(MAX(cust_pay.total_paid), 0) AS 'Total Paid',
    COALESCE(SUM(pay.total_debit), 0) 
      - COALESCE(MAX(cust_pay.total_paid), 0) AS 'Balance'
FROM patients p
LEFT JOIN phar_pos pos 
    ON pos.customer_id = p.id AND IFNULL(pos.is_deleted, 0) = 0
LEFT JOIN (
    SELECT 
        pos_id,
        SUM(debit) AS total_debit
    FROM phar_pos_payments
    GROUP BY pos_id
) pay ON pay.pos_id = pos.pos_id
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(payment) AS total_paid
    FROM phar_customer_payments
    GROUP BY customer_id
) cust_pay ON cust_pay.customer_id = p.id
GROUP BY 
    p.id,
    p.name,
    p.patient_id,
    p.phone
ORDER BY 
    p.name ASC;


END
