CREATE DEFINER=CURRENT_USER PROCEDURE `service_report`(IN `doctor_id_param` INT, IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `service_id_param` INT)
BEGIN
    SELECT 
        s.date,
        hs.name AS doctor_name,
        st.name AS service_type,
        ROUND(SUM(
            COALESCE(pay.total_payment, 0) * ((COALESCE(sd.rate, st.rate) * COALESCE(NULLIF(sd.quantity + 0, 0), 1)) / NULLIF(total_rates.total_rate, 0))
        ), 2) AS paid_amount
    FROM services s

    
    LEFT JOIN service_details sd 
        ON sd.request_id = s.id

    
    LEFT JOIN service_types st 
        ON sd.service_id = st.id

    
    LEFT JOIN hrm_staffs hs 
        ON hs.id = s.doctor_id

    
    LEFT JOIN (
        SELECT 
            reference_id,
            SUM(payment) AS total_payment
        FROM payments
        WHERE reference = 'Service'
        GROUP BY reference_id
    ) pay 
        ON pay.reference_id = s.id

    
    LEFT JOIN (
        SELECT 
            sd.request_id,
            SUM((COALESCE(sd.rate, st.rate) * COALESCE(NULLIF(sd.quantity + 0, 0), 1))) AS total_rate
        FROM service_details sd
        LEFT JOIN service_types st 
            ON sd.service_id = st.id
        GROUP BY sd.request_id
    ) total_rates
        ON total_rates.request_id = s.id

    WHERE s.date BETWEEN start_date_filter AND end_date_filter
      AND (doctor_id_param = 0 OR doctor_id_param = s.doctor_id )
      And(service_id_param = 0 or service_id_param = st.id)

    GROUP BY 
        s.date,
        hs.name,
        st.name

    ORDER BY 
        s.date DESC;
END
