CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_admission_payments`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `source_filter` VARCHAR(20), IN `mode` VARCHAR(20))
BEGIN
-- Per admission: what was paid in the period — room, lab, services,
-- operations, delivery, pharmacy of the admission — and the commission on
-- ALL of it to ONE doctor: the operation doctor (operation %) if the admission
-- had an operation, else the delivery doctor (delivery %) if it had a
-- delivery with a doctor, else the admitting doctor (inpatient %). Rule and
-- percents: v_doctor_commission / HRM -> Doctor commission. Bill type filter:
-- only that kind of bill.
-- Same six columns in both modes, so the totals land under Paid and Commission.
IF mode = 'Detail' THEN
    SELECT CONCAT(IF(ad.admission_no IS NULL, '', CONCAT('#', ad.admission_no, ' · ')), COALESCE(p.name, '-'),
                  IF(p.patient_id IS NULL, '', CONCAT(' (', p.patient_id, ')')), ' · ', DATE_FORMAT(COALESCE(ad.date_in, ad.date), '%d %b, %Y'),
                  ' – ', IFNULL(DATE_FORMAT(ad.date_out, '%d %b, %Y'), 'still admitted')) AS `Admission`,
           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('commission as ', CASE l.bucket WHEN 'Operation' THEN 'operation doctor' WHEN 'Delivery' THEN 'delivery doctor' ELSE 'admitting doctor' END),
                     CONCAT('paid ', DATE_FORMAT(l.paid_at, '%d %b, %Y %h:%i %p'))) AS `Details`
    FROM v_doctor_commission l
    JOIN admissions ad ON ad.id = l.adm_id
    LEFT JOIN patients p ON p.id = ad.patient_id
    LEFT JOIN hrm_staffs d ON d.id = l.doctor_id
    WHERE l.kind = 'Inpatient' 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 COALESCE(ad.date_in, ad.date) DESC, ad.id DESC, l.paid_at;
ELSE
    SELECT CONCAT(IF(ad.admission_no IS NULL, '', CONCAT('#', ad.admission_no, ' · ')), COALESCE(p.name, '-'),
                  IF(p.patient_id IS NULL, '', CONCAT(' (', p.patient_id, ')')), ' · ', DATE_FORMAT(COALESCE(ad.date_in, ad.date), '%d %b, %Y'),
                  ' – ', IFNULL(DATE_FORMAT(ad.date_out, '%d %b, %Y'), 'still admitted')) AS `Admission`,
           COALESCE(d.name, '-') AS `Doctor`,
           CONCAT('$ ', FORMAT(SUM(x.amount), 2)) AS `Paid`,
           GROUP_CONCAT(DISTINCT CONCAT(FORMAT(x.pct, 2), ' % · ', CASE x.bucket WHEN 'Operation' THEN 'operation' WHEN 'Delivery' THEN 'delivery' ELSE 'admission' END) 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.adm_id, l.doctor_id, l.source, l.bucket, SUM(l.amount) amount, l.pct, SUM(l.amount * l.pct) / 100 com
        FROM v_doctor_commission l
        WHERE l.kind = 'Inpatient' AND l.adm_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.adm_id, l.doctor_id, l.source, l.bucket, l.pct
    ) x
    JOIN admissions ad ON ad.id = x.adm_id
    LEFT JOIN patients p ON p.id = ad.patient_id
    LEFT JOIN hrm_staffs d ON d.id = x.doctor_id
    GROUP BY x.adm_id, x.doctor_id, ad.id, ad.admission_no, ad.date_in, ad.date, ad.date_out, p.name, p.patient_id, d.name
    ORDER BY COALESCE(ad.date_in, ad.date) DESC, ad.id DESC;
END IF;
END
