CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_doctor_commission`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `mode` VARCHAR(20))
BEGIN
-- Doctor commission (staff switched on in HRM -> Doctor commission):
-- outpatient % of what patients paid on the doctor's appointments. Admissions:
-- everything paid for the admission goes to one doctor — operation doctor
-- (operation %) if it had an operation, else delivery doctor (delivery %),
-- else the admitting doctor (inpatient %). Each payment with its doctor and
-- percent: v_doctor_commission.
IF mode = 'Per patient' THEN
    -- one row per patient (per doctor and kind): total paid, percent, commission
    SELECT CONCAT(COALESCE(p.name, '(no patient)'), IF(p.patient_id IS NULL, '', CONCAT(' (', p.patient_id, ')')), ' · ', c.doctor_name) AS `Patient · Doctor`,
           CONCAT('$ ', FORMAT(SUM(c.amount), 2)) AS `Paid`,
           CONCAT_WS(' · ', c.bucket,
                     GROUP_CONCAT(DISTINCT CONCAT(FORMAT(c.pct, 2), ' %') SEPARATOR ' / '),
                     CONCAT(COUNT(*), ' payment(s)'),
                     GROUP_CONCAT(DISTINCT c.source ORDER BY c.source SEPARATOR ', '),
                     CONCAT(DATE_FORMAT(MIN(c.paid_at), '%d %b'), IF(DATE(MIN(c.paid_at)) = DATE(MAX(c.paid_at)), '', CONCAT(' – ', DATE_FORMAT(MAX(c.paid_at), '%d %b, %Y'))))) AS `Details`,
           CONCAT('$ ', FORMAT(SUM(c.amount * c.pct) / 100, 2)) AS `Commission`
    FROM v_doctor_commission c
    JOIN hrm_staffs d ON d.id = c.doctor_id
    LEFT JOIN patients p ON p.id = c.patient_id
    WHERE DATE(c.paid_at) BETWEEN start_date AND end_date
      AND (doctor_filter = 0 OR c.doctor_id = doctor_filter)
      AND (doctor_filter <> 0 OR d.gets_commission = 1) AND (doctor_filter <> 0 OR IFNULL(d.outpatient_commission, 0) > 0 OR IFNULL(d.inpatient_commission, 0) > 0
           OR IFNULL(d.operation_commission, 0) > 0 OR IFNULL(d.delivery_commission, 0) > 0)
    GROUP BY c.doctor_id, c.doctor_name, c.patient_id, p.name, p.patient_id, c.bucket
    ORDER BY c.doctor_name, SUM(c.amount) DESC;
ELSEIF mode = 'Detail' THEN
    -- four columns, so the totals land under Paid and Commission
    SELECT CONCAT(DATE_FORMAT(c.paid_at, '%d %b, %Y'), ' · ', c.doctor_name) AS `Date · Doctor`,
           CONCAT('$ ', FORMAT(c.amount, 2)) AS `Paid`,
           CONCAT_WS(' · ', c.bucket, IF(c.source = c.bucket, NULL, c.source), NULLIF(p.name, ''), CONCAT(FORMAT(c.pct, 2), ' %')) AS `Details`,
           CONCAT('$ ', FORMAT(c.amount * c.pct / 100, 2)) AS `Commission`
    FROM v_doctor_commission c
    JOIN hrm_staffs d ON d.id = c.doctor_id
    LEFT JOIN patients p ON p.id = c.patient_id
    WHERE DATE(c.paid_at) BETWEEN start_date AND end_date
      AND (doctor_filter = 0 OR c.doctor_id = doctor_filter)
      AND (doctor_filter <> 0 OR d.gets_commission = 1) AND (doctor_filter <> 0 OR IFNULL(d.outpatient_commission, 0) > 0 OR IFNULL(d.inpatient_commission, 0) > 0
           OR IFNULL(d.operation_commission, 0) > 0 OR IFNULL(d.delivery_commission, 0) > 0)
    ORDER BY c.doctor_name, c.paid_at;
ELSE
    SELECT s.name AS `Doctor`,
           CONCAT('$ ', FORMAT(s.out_amount, 2)) AS `Outpatient paid`, CONCAT(FORMAT(s.out_pct, 2), ' %') AS `Out %`,
           CONCAT('$ ', FORMAT(s.out_com, 2)) AS `Outpatient commission`,
           CONCAT('$ ', FORMAT(s.in_amount, 2)) AS `Inpatient paid`, CONCAT(FORMAT(s.in_pct, 2), ' %') AS `In %`,
           CONCAT('$ ', FORMAT(s.in_com, 2)) AS `Inpatient commission`,
           CONCAT('$ ', FORMAT(s.op_amount, 2)) AS `Operations paid`, IFNULL(CONCAT(FORMAT(s.op_pct, 2), ' %'), 'as out/in') AS `Op %`,
           CONCAT('$ ', FORMAT(s.op_com, 2)) AS `Operation commission`,
           CONCAT('$ ', FORMAT(s.del_amount, 2)) AS `Deliveries paid`, IFNULL(CONCAT(FORMAT(s.del_pct, 2), ' %'), 'as inpatient') AS `Delivery %`,
           CONCAT('$ ', FORMAT(s.del_com, 2)) AS `Delivery commission`,
           CONCAT('<b>$ ', FORMAT(s.out_com + s.in_com + s.op_com + s.del_com, 2), '</b>') AS `Total commission`
    FROM (
        SELECT d.name, IFNULL(d.outpatient_commission, 0) out_pct, IFNULL(d.inpatient_commission, 0) in_pct,
               d.operation_commission op_pct, d.delivery_commission del_pct,
               SUM(IF(c.bucket = 'Outpatient', c.amount, 0)) out_amount, SUM(IF(c.bucket = 'Outpatient', c.amount * c.pct, 0)) / 100 out_com,
               SUM(IF(c.bucket = 'Inpatient', c.amount, 0)) in_amount, SUM(IF(c.bucket = 'Inpatient', c.amount * c.pct, 0)) / 100 in_com,
               SUM(IF(c.bucket = 'Operation', c.amount, 0)) op_amount, SUM(IF(c.bucket = 'Operation', c.amount * c.pct, 0)) / 100 op_com,
               SUM(IF(c.bucket = 'Delivery', c.amount, 0)) del_amount, SUM(IF(c.bucket = 'Delivery', c.amount * c.pct, 0)) / 100 del_com
        FROM v_doctor_commission c JOIN hrm_staffs d ON d.id = c.doctor_id
        WHERE DATE(c.paid_at) BETWEEN start_date AND end_date
          AND (doctor_filter = 0 OR c.doctor_id = doctor_filter)
          AND (doctor_filter <> 0 OR d.gets_commission = 1) AND (doctor_filter <> 0 OR IFNULL(d.outpatient_commission, 0) > 0 OR IFNULL(d.inpatient_commission, 0) > 0
               OR IFNULL(d.operation_commission, 0) > 0 OR IFNULL(d.delivery_commission, 0) > 0)
        GROUP BY d.id, d.name, d.outpatient_commission, d.inpatient_commission, d.operation_commission, d.delivery_commission
    ) s
    ORDER BY s.out_com + s.in_com + s.op_com + s.del_com DESC;
END IF;
END
