CREATE DEFINER=CURRENT_USER PROCEDURE `patient_balance_filter`(IN `patient_filter` INT, IN `start_date_filter` DATE, IN `end_date_filter` DATE)
BEGIN

SELECT 
    ROW_NUMBER() OVER (ORDER BY p.id DESC) AS Sqn,
    p.name AS 'Patient Name',

    
    ROUND(COALESCE(ph_debit.total_debit, 0)  - COALESCE(ph_credit.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(img.total_debit, 0) - COALESCE(img.total_credit, 0), 2) AS 'Image Balance',

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

FROM patients p


LEFT JOIN (
    SELECT
        pos.customer_id,
        SUM(pp.debit) AS total_debit
    FROM phar_pos pos
    INNER JOIN phar_pos_payments pp ON pos.id = pp.pos_id
    WHERE IFNULL(pos.is_deleted, 0) = 0 AND (start_date_filter IS NULL OR DATE(pos.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(pos.created_at) <= end_date_filter)
    GROUP BY pos.customer_id
) ph_debit ON ph_debit.customer_id = p.id


LEFT JOIN (
    SELECT
        pcp.customer_id,
        COALESCE(SUM(pcp.payment + pcp.discount), 0) AS total_credit
    FROM phar_customer_payments pcp
    WHERE (start_date_filter IS NULL OR DATE(pcp.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(pcp.created_at) <= end_date_filter)
    GROUP BY pcp.customer_id
) ph_credit ON ph_credit.customer_id = p.id


LEFT JOIN (
    SELECT
        app.patient_id,
        MAX(DATE(app.date)) AS label,
        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'
    WHERE (start_date_filter IS NULL OR DATE(app.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(app.created_at) <= end_date_filter)
    GROUP BY app.patient_id
) ap ON ap.patient_id = p.id


LEFT JOIN (
    SELECT
        lr.patient,
        MAX(DATE(lr.date)) AS label,
        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
    WHERE (start_date_filter IS NULL OR DATE(lr.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(lr.created_at) <= end_date_filter)
    GROUP BY lr.patient
) lab ON lab.patient = 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'
    WHERE (start_date_filter IS NULL OR DATE(adm.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(adm.created_at) <= end_date_filter)
    GROUP BY adm.patient_id
) ad ON ad.patient_id = 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'
    WHERE (start_date_filter IS NULL OR DATE(ir.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(ir.created_at) <= end_date_filter)
    GROUP BY ir.patient
) img ON img.patient = p.id


LEFT JOIN (
    SELECT
        sv.patient,
        MAX(DATE(sv.date)) AS label,
        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'
    WHERE (start_date_filter IS NULL OR DATE(sv.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(sv.created_at) <= end_date_filter)
    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'
    WHERE (start_date_filter IS NULL OR DATE(op.created_at) >= start_date_filter)
      AND (end_date_filter   IS NULL OR DATE(op.created_at) <= end_date_filter)
    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_debit.total_debit, 0)   - COALESCE(ph_credit.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(ad.total_debit, 0)  - COALESCE(ad.total_credit, 0)  != 0) OR
        (COALESCE(op.total_debit, 0)  - COALESCE(op.total_credit, 0)  != 0) OR
        (COALESCE(img.total_debit, 0) - COALESCE(img.total_credit, 0) != 0) OR
        COALESCE(p.opening_balance, 0) != 0
    )

ORDER BY p.id DESC;

END
