CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_visit_payments`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `source_filter` VARCHAR(20), IN `mode` VARCHAR(20))
BEGIN
-- Per visit (appointment): what was paid in the period — card, lab, imaging,
-- services, operations, pharmacy of the visit — and the visit doctor's
-- outpatient commission on it (operation % for an operation he did) (HRM -> Doctor commission). Bill type filter:
-- only that kind of bill. Payments traced by v_doctor_commission_lines.
-- Same six columns in both modes, so the totals land under Paid and Commission.
IF mode = 'Detail' THEN
    SELECT CONCAT(DATE_FORMAT(a.date, '%d %b, %Y'), IF(a.appointment_no IS NULL, '', CONCAT(' · #', a.appointment_no)), ' · ',
                  COALESCE(p.name, '-'), IF(p.patient_id IS NULL, '', CONCAT(' (', p.patient_id, ')'))) AS `Visit`,
           COALESCE(d.name, '-') AS `Doctor`,
           CONCAT('$ ', FORMAT(l.amount, 2)) AS `Paid`,
           CONCAT(FORMAT(l.pct, 2), ' %') AS `Commission %`,
           CONCAT('$ ', FORMAT(l.amount * l.pct / 100, 2)) AS `Commission`,
           CONCAT_WS(' · ', l.source, CONCAT('paid ', DATE_FORMAT(l.paid_at, '%d %b, %Y %h:%i %p'))) AS `Details`
    FROM v_doctor_commission l
    JOIN appointments a ON a.id = l.appt_id
    LEFT JOIN patients p ON p.id = a.patient_id
    LEFT JOIN hrm_staffs d ON d.id = l.doctor_id
    WHERE l.kind = 'Outpatient' AND DATE(l.paid_at) BETWEEN start_date AND end_date
      AND (doctor_filter = 0 OR l.doctor_id = doctor_filter)
      AND (source_filter IN ('', '0') OR l.source = source_filter)
    ORDER BY a.date DESC, a.id DESC, l.paid_at;
ELSE
    SELECT CONCAT(DATE_FORMAT(a.date, '%d %b, %Y'), IF(a.appointment_no IS NULL, '', CONCAT(' · #', a.appointment_no)), ' · ',
                  COALESCE(p.name, '-'), IF(p.patient_id IS NULL, '', CONCAT(' (', p.patient_id, ')'))) AS `Visit`,
           COALESCE(d.name, '-') AS `Doctor`,
           CONCAT('$ ', FORMAT(SUM(x.amount), 2)) AS `Paid`,
           GROUP_CONCAT(DISTINCT CONCAT(FORMAT(x.pct, 2), ' %') ORDER BY x.pct SEPARATOR ' / ') AS `Commission %`,
           CONCAT('$ ', FORMAT(SUM(x.com), 2)) AS `Commission`,
           GROUP_CONCAT(CONCAT(x.source, ' $ ', FORMAT(x.amount, 2)) ORDER BY x.source SEPARATOR ' · ') AS `Paid by type`
    FROM (
        SELECT l.appt_id, l.doctor_id, l.source, SUM(l.amount) amount, l.pct, SUM(l.amount * l.pct) / 100 com
        FROM v_doctor_commission l
        WHERE l.kind = 'Outpatient' AND l.appt_id IS NOT NULL AND DATE(l.paid_at) BETWEEN start_date AND end_date
          AND (doctor_filter = 0 OR l.doctor_id = doctor_filter)
          AND (source_filter IN ('', '0') OR l.source = source_filter)
        GROUP BY l.appt_id, l.doctor_id, l.source, l.pct
    ) x
    JOIN appointments a ON a.id = x.appt_id
    LEFT JOIN patients p ON p.id = a.patient_id
    LEFT JOIN hrm_staffs d ON d.id = x.doctor_id
    GROUP BY x.appt_id, x.doctor_id, a.id, a.date, a.appointment_no, p.name, p.patient_id, d.name
    ORDER BY a.date DESC, a.id DESC;
END IF;
END
