CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_admission_detailed`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `cashier_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
-- Admissions: stay, room, days and the room charge with its payment.
-- Paid / discount / cashier come from the Payment rows of each bill (other
-- currency converted to $). Payment status: Paid (nothing left), Partial
-- (something paid), Unpaid, or No charge.
SELECT
    y.`Admitted`,
    y.`Discharged`,
    y.`No.`,
    y.`Patient`,
    y.`Patient ID`,
    y.`Doctor`,
    y.`Room`,
    y.`Days`,
    y.`Status`,
    y.`Billing`,
    CONCAT('$ ', FORMAT(y.charge, 2))   AS `Charge`,
    CONCAT('$ ', FORMAT(y.paid, 2))     AS `Paid`,
    CONCAT('$ ', FORMAT(y.discount, 2)) AS `Discount`,
    CONCAT('$ ', FORMAT(y.balance, 2))  AS `Balance`,
    y.pstatus                           AS `Payment status`,
    COALESCE(y.cashier, '')             AS `Cashier`
FROM (
    SELECT x.*,
        GREATEST(x.charge - x.paid - x.discount, 0) AS balance,
        CASE WHEN x.charge <= 0 THEN 'No charge'
             WHEN x.charge - x.paid - x.discount <= 0.009 THEN 'Paid'
             WHEN x.paid + x.discount > 0 THEN 'Partial'
             ELSE 'Unpaid' END AS pstatus
    FROM (
        SELECT
        DATE_FORMAT(COALESCE(t.date_in, t.date), '%d %b, %Y') AS `Admitted`,
        COALESCE(DATE_FORMAT(t.date_out, '%d %b, %Y'), 'Still admitted') AS `Discharged`,
        COALESCE(t.admission_no, t.id) AS `No.`,
        p.name AS `Patient`,
        p.patient_id AS `Patient ID`,
        COALESCE(d.name, '') AS `Doctor`,
        COALESCE(rm.name, '') AS `Room`,
        GREATEST(COALESCE(NULLIF(t.days, 0), DATEDIFF(COALESCE(t.date_out, CURDATE()), COALESCE(t.date_in, t.date))), 1) AS `Days`,
        COALESCE(t.status, '') AS `Status`,
        IF(IFNULL(t.cost, 0) > 0, 'Processed', 'Estimated (room rate × days)') AS `Billing`,
        IF(IFNULL(t.cost, 0) > 0, t.cost, IFNULL(rm.cost, 0) * GREATEST(COALESCE(NULLIF(t.days, 0), DATEDIFF(COALESCE(t.date_out, CURDATE()), COALESCE(t.date_in, t.date))), 1)) AS charge,
        (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.payment / pm.exchange_rate, pm.payment)), 0) FROM payments pm WHERE pm.reference = 'Admission' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS paid,
        (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.discount / pm.exchange_rate, pm.discount) + IFNULL(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.promotion / pm.exchange_rate, pm.promotion), 0)), 0) FROM payments pm WHERE pm.reference = 'Admission' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS discount,
        (SELECT GROUP_CONCAT(DISTINCT u.name SEPARATOR ', ') FROM payments pm JOIN users u ON u.id = pm.created_by WHERE pm.reference = 'Admission' AND pm.reference_id = t.id AND pm.status <> 'Refund') AS cashier,
        t.sort_date AS sort_date
        FROM (SELECT a.*, COALESCE(a.date_in, a.date) AS sort_date FROM admissions a) t LEFT JOIN patients p ON p.id = t.patient_id LEFT JOIN hrm_staffs d ON d.id = t.doctor_id LEFT JOIN inp_rooms rm ON rm.id = t.room
        WHERE DATE(COALESCE(t.date_in, t.date)) BETWEEN start_date AND end_date AND (doctor_filter = 0 OR t.doctor_id = doctor_filter)
          AND (cashier_filter = 0 OR EXISTS (SELECT 1 FROM payments pm WHERE pm.reference = 'Admission' AND pm.reference_id = t.id AND pm.status <> 'Refund' AND pm.created_by = cashier_filter))
    ) x
) y
WHERE (status_filter = '0' OR status_filter = '' OR y.pstatus = status_filter)
ORDER BY y.sort_date, 1;
END
