CREATE DEFINER=CURRENT_USER PROCEDURE `daily_salesrpt`(IN `start_date` DATE, IN `end_date` DATE, IN `type_filter` VARCHAR(500), IN `user_filter` INT, IN `patient_filter` INT, IN `doctor_filter` INT)
BEGIN
SELECT 
    ROW_NUMBER() OVER (ORDER BY payments.id) AS Sqn,
    DATE_FORMAT(payments.created_at, '%e %b, %Y') AS Date,
    COALESCE(u.name, '')                        AS Cashier,
    CASE WHEN payments.reference = 'emergency' THEN 'Emergency' WHEN BINARY payments.reference = 'Pharmacy' THEN 'Pharmacy (customer payment)' WHEN payments.reference = 'pharmacy' THEN 'Pharmacy' WHEN payments.reference = 'appointment' THEN 'Appointment' ELSE COALESCE(payments.reference, '') END AS Type,
    COALESCE(ROUND(payments.payment, 2), 0)     AS Payment,
    COALESCE(ROUND(payments.discount, 2), 0)    AS Discount,
    COALESCE(ROUND(payments.promotion, 2), 0)   AS Promotion,
    COALESCE(apt.name, '')                      AS PaymentMethod,
    
    COALESCE(
        p_lr.name,
        p_ir.name,
        p_ap.name,
        p_op.name,
        p_sv.name,
        p_ad.name,
        p_phar.name,
        p_cp.name,
        p_dl.name,
        p_em.name,
        ''
    ) AS Patient,
    
    COALESCE(
        d_lr.name,
        d_ir.name,
        d_ap.name,
        d_op.name,
        d_sv.name,
        d_ad.name,
        d_phar.name,
        d_em.name,
        ''
    ) AS Doctor
FROM payments
LEFT JOIN users u ON u.id = payments.created_by
LEFT JOIN acc_payment_types apt ON apt.id = payments.payment_method
LEFT JOIN lab_requests   lr   ON payments.reference = 'Lab request'   AND payments.reference_id = lr.id
LEFT JOIN patients       p_lr ON p_lr.id = lr.patient
LEFT JOIN hrm_staffs          d_lr ON d_lr.id = lr.doctor_id
LEFT JOIN image_requests ir   ON payments.reference = 'Image request' AND payments.reference_id = ir.id
LEFT JOIN patients       p_ir ON p_ir.id = ir.patient
LEFT JOIN hrm_staffs          d_ir ON d_ir.id = ir.doctor_id
LEFT JOIN appointments   ap   ON payments.reference = 'Appointment'   AND payments.reference_id = ap.id
LEFT JOIN patients       p_ap ON p_ap.id = ap.patient_id
LEFT JOIN hrm_staffs          d_ap ON d_ap.id = ap.doctor_id
LEFT JOIN operations     op   ON payments.reference = 'Operation'     AND payments.reference_id = op.id
LEFT JOIN patients       p_op ON p_op.id = op.patient
LEFT JOIN hrm_staffs          d_op ON d_op.id = op.dr
LEFT JOIN services       sv   ON payments.reference = 'Service'       AND payments.reference_id = sv.id
LEFT JOIN patients       p_sv ON p_sv.id = sv.patient
LEFT JOIN hrm_staffs          d_sv ON d_sv.id = sv.doctor_id
LEFT JOIN admissions     ad   ON payments.reference = 'Admission'     AND payments.reference_id = ad.id
LEFT JOIN patients       p_ad ON p_ad.id = ad.patient_id
LEFT JOIN hrm_staffs          d_ad ON d_ad.id = ad.doctor_id
-- "pharmacy" = payment on a pharmacy invoice (phar_pos); "Pharmacy" (capital P)
-- = lump-sum Pharmacy customer payment (phar_customer_payments)
LEFT JOIN phar_pos       phar   ON BINARY payments.reference = 'pharmacy' AND payments.reference_id = phar.id
LEFT JOIN patients       p_phar ON p_phar.id = phar.customer_id
LEFT JOIN users          d_phar ON d_phar.id = phar.created_by
LEFT JOIN phar_customer_payments cp ON BINARY payments.reference = 'Pharmacy' AND payments.reference_id = cp.id
LEFT JOIN patients       p_cp   ON p_cp.id = cp.customer_id
LEFT JOIN deliveries     dl     ON payments.reference = 'Delivery'    AND payments.reference_id = dl.id
LEFT JOIN patients       p_dl   ON p_dl.id = dl.patient_id
LEFT JOIN emergencies    em     ON payments.reference = 'emergency'   AND payments.reference_id = em.id
LEFT JOIN patients       p_em   ON p_em.id = em.patient_id
LEFT JOIN hrm_staffs     d_em   ON d_em.id = em.doctor_id
WHERE payments.status <> 'Refund' 
  AND DATE(payments.created_at) BETWEEN start_date AND end_date
  AND (type_filter = '0' OR CONVERT(payments.reference USING utf8mb4) COLLATE utf8mb4_unicode_ci = CONVERT(type_filter USING utf8mb4) COLLATE utf8mb4_unicode_ci)
  AND (user_filter = 0      OR user_filter  = u.id)
  AND (patient_filter = 0   OR patient_filter = COALESCE(
        p_lr.id, p_ir.id, p_ap.id, p_op.id, p_sv.id, p_ad.id, p_phar.id, p_cp.id, p_dl.id, p_em.id
  ))
  AND (doctor_filter = 0    OR doctor_filter = COALESCE(
        d_lr.id, d_ir.id, d_ap.id, d_op.id, d_sv.id, d_ad.id, d_phar.id, d_em.id
  ));
END
