CREATE DEFINER=CURRENT_USER PROCEDURE `cutomer_debts_outpatient`(IN `patient_filter` INT)
BEGIN
SELECT 
    ROW_NUMBER() OVER (ORDER BY p.id desc) AS Sqn,
    p.name AS 'Patient Name',
    
 ROUND(COALESCE(ph.total_debit, 0) - COALESCE(ph.total_credit, 0), 2) AS 'Pharmacy Balance',

ROUND(COALESCE(ap.total_debit, 0) - COALESCE(ap.total_credit, 0), 2) AS 'Appointment Balance',

ROUND(COALESCE(lab.total_debit, 0) - COALESCE(lab.total_credit, 0), 2) AS 'Lab Balance',

ROUND(COALESCE(svc.total_debit, 0) - COALESCE(svc.total_credit, 0), 2) AS 'Service Balance',

ROUND(COALESCE(ad.total_debit, 0) - COALESCE(ad.total_credit, 0), 2) AS 'Admission Balance',
ROUND(COALESCE(op.total_debit, 0) - COALESCE(op.total_credit, 0), 2) AS 'Operation Balance',

ROUND(
    COALESCE(p.opening_balance, 0) + 
    (COALESCE(ph.total_debit, 0) - COALESCE(ph.total_credit, 0)) +
    (COALESCE(ap.total_debit, 0) - COALESCE(ap.total_credit, 0)) +
    (COALESCE(lab.total_debit, 0) - COALESCE(lab.total_credit, 0)) +
    (COALESCE(ad.total_debit, 0) - COALESCE(ad.total_credit, 0)) +
    (COALESCE(svc.total_debit, 0) - COALESCE(svc.total_credit, 0))+
    (COALESCE(op.total_debit, 0) - COALESCE(op.total_credit, 0))
, 2) AS 'Total Balance'


FROM patients p




LEFT JOIN (
    SELECT 
        pos.customer_id,
        SUM(ppp.debit) AS total_debit, 
        COALESCE((
            SELECT SUM(pcp.payment + pcp.discount) 
            FROM phar_customer_payments pcp 
            WHERE pcp.customer_id = pos.customer_id
        ), 0) AS total_credit 
    FROM phar_pos pos
    INNER JOIN phar_pos_products pp ON pos.id = pp.pos_id 
 INNER JOIN phar_pos_payments ppp ON pos.id = ppp .pos_id 
    WHERE IFNULL(pos.is_deleted, 0) = 0
    GROUP BY pos.customer_id
) ph ON ph.customer_id = p.id


LEFT JOIN (
    SELECT 
        app.patient_id,
        SUM(app.price) AS total_debit, 
        COALESCE(SUM(pmt.payment + pmt.discount + pmt.promotion), 0) AS total_credit 
    FROM appointments app
    LEFT JOIN payments pmt ON pmt.reference_id = app.id AND pmt.reference = 'Appointment' AND pmt.status <> 'Refund'
    GROUP BY app.patient_id
) ap ON ap.patient_id = p.id


LEFT JOIN (
    SELECT 
        adm.patient_id,
        SUM(adm.cost) AS total_debit, 
        COALESCE(SUM(pmt.payment + pmt.discount + pmt.promotion), 0) AS total_credit 
    FROM admissions adm
    LEFT JOIN payments pmt ON pmt.reference_id = adm.id AND pmt.reference = 'Admission' AND pmt.status <> 'Refund'
    GROUP BY adm.patient_id
) ad ON ad.patient_id = p.id


LEFT JOIN (
    SELECT 
        lr.patient,
        SUM(
            COALESCE((
                SELECT SUM(lrr.price) 
                FROM lab_request_routines lrr 
                WHERE lrr.lab_request_id = lr.id
            ), 0)
        ) AS total_debit,
        SUM(
            COALESCE((
                SELECT SUM(pmt.payment + pmt.discount + pmt.promotion) 
                FROM payments pmt 
                WHERE pmt.reference_id = lr.id 
                AND pmt.reference = 'Lab request' 
                AND pmt.status <> 'Refund'
            ), 0)
        ) AS total_credit
    FROM lab_requests lr
    GROUP BY lr.patient
) lab ON lab.patient = p.id


LEFT JOIN (
    SELECT 
        ir.patient,
        SUM(it.price) AS total_debit, 
        COALESCE(SUM(pmt.payment + pmt.discount + pmt.promotion), 0) AS total_credit 
    FROM image_requests ir
    LEFT JOIN image_types it ON it.id = ir.lab_type
    LEFT JOIN payments pmt ON pmt.reference_id = ir.id AND pmt.reference = 'Image Request' AND pmt.status <> 'Refund'
    GROUP BY ir.patient
) img ON img.patient = p.id


LEFT JOIN (
    SELECT 
        sv.patient,
        SUM(sv.rate) AS total_debit, 
        COALESCE(SUM(pmt.payment + pmt.discount + pmt.promotion), 0) AS total_credit 
    FROM services sv
    LEFT JOIN payments pmt ON pmt.reference_id = sv.id AND pmt.reference = 'Service' AND pmt.status <> 'Refund'
    GROUP BY sv.patient
) svc ON svc.patient = p.id


LEFT JOIN (
    SELECT 
        op.patient,
        SUM(op.rate) AS total_debit, 
        COALESCE(SUM(pmt.payment + pmt.discount + pmt.promotion), 0) AS total_credit 
    FROM operations op
    LEFT JOIN payments pmt ON pmt.reference_id = op.id AND pmt.reference = 'Operation' AND pmt.status <> 'Refund'
    GROUP BY op.patient
) op ON op.patient = p.id

WHERE 
    
    (patient_filter IS NULL OR patient_filter = 0 OR p.id = patient_filter)
    AND
    
    (
        (COALESCE(ph.total_debit, 0) - COALESCE(ph.total_credit, 0) != 0) OR
        (COALESCE(ap.total_debit, 0) - COALESCE(ap.total_credit, 0) != 0) OR
        (COALESCE(lab.total_debit, 0) - COALESCE(lab.total_credit, 0) != 0) OR
        (COALESCE(svc.total_debit, 0) - COALESCE(svc.total_credit, 0) != 0) OR
        COALESCE(p.opening_balance, 0) != 0
    )
    
 
ORDER BY p.id desc;
END
