CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_discounts_refunds`(IN `start_date` DATE, IN `end_date` DATE, IN `kind` VARCHAR(20), IN `cashier_filter` INT)
BEGIN
-- Discounts and refunds (leakage): every discount given and every payment
-- refunded in the period — who did it, on which bill, how much, and why.
SELECT DATE_FORMAT(x.at, '%d %b, %Y %H:%i') AS `Date`, x.kind AS `Kind`, x.bill AS `Bill`,
       COALESCE(pt.name, '') AS `Patient`, x.cashier AS `By`,
       CONCAT('$ ', FORMAT(x.amount, 2)) AS `Amount`, x.note AS `Note`
FROM (
    SELECT p.created_at at, 'Discount' kind,
           CONCAT(CASE LOWER(p.reference) WHEN 'lab request' THEN 'Laboratory' WHEN 'image request' THEN 'Imaging' ELSE p.reference END, ' #', p.reference_id) bill,
           p.reference, p.reference_id, COALESCE(u.name, '') cashier, p.created_by,
           IF(p.currency = 2 AND p.exchange_rate > 0, (IFNULL(p.discount, 0) + IFNULL(p.promotion, 0)) / p.exchange_rate, IFNULL(p.discount, 0) + IFNULL(p.promotion, 0)) amount,
           COALESCE(p.description, '') note
    FROM payments p LEFT JOIN users u ON u.id = p.created_by
    WHERE p.status <> 'Refund' AND IFNULL(p.discount, 0) + IFNULL(p.promotion, 0) > 0 AND DATE(p.created_at) BETWEEN start_date AND end_date
    UNION ALL
    SELECT pp.date, 'Discount', CONCAT('Pharmacy invoice #', pp.pos_id), 'pharmacy', pp.pos_id, COALESCE(u.name, ''), pp.created_by,
           IF(pp.currency = 2 AND pp.exchange_rate > 0, IFNULL(pp.discount, 0) / pp.exchange_rate, IFNULL(pp.discount, 0)), ''
    FROM phar_pos_payments pp LEFT JOIN users u ON u.id = pp.created_by
    WHERE IFNULL(pp.discount, 0) > 0 AND DATE(pp.date) BETWEEN start_date AND end_date
    UNION ALL
    SELECT p.updated_at, 'Refund',
           CONCAT(CASE LOWER(p.reference) WHEN 'lab request' THEN 'Laboratory' WHEN 'image request' THEN 'Imaging' ELSE p.reference END, ' #', p.reference_id),
           p.reference, p.reference_id, COALESCE(u.name, ''), p.created_by,
           IF(p.currency = 2 AND p.exchange_rate > 0, (p.payment + IFNULL(p.discount, 0)) / p.exchange_rate, p.payment + IFNULL(p.discount, 0)),
           COALESCE(p.description, '')
    FROM payments p LEFT JOIN users u ON u.id = p.created_by
    WHERE p.status = 'Refund' AND DATE(p.updated_at) BETWEEN start_date AND end_date
) x
LEFT JOIN patients pt ON pt.id = CASE LOWER(x.reference)
    WHEN 'appointment' THEN (SELECT a.patient_id FROM appointments a WHERE a.id = x.reference_id)
    WHEN 'lab request' THEN (SELECT l.patient FROM lab_requests l WHERE l.id = x.reference_id)
    WHEN 'image request' THEN (SELECT i.patient FROM image_requests i WHERE i.id = x.reference_id)
    WHEN 'service' THEN (SELECT s.patient FROM services s WHERE s.id = x.reference_id)
    WHEN 'operation' THEN (SELECT o.patient FROM operations o WHERE o.id = x.reference_id)
    WHEN 'admission' THEN (SELECT ad.patient_id FROM admissions ad WHERE ad.id = x.reference_id)
    WHEN 'emergency' THEN (SELECT e.patient_id FROM emergencies e WHERE e.id = x.reference_id)
    WHEN 'pharmacy' THEN (SELECT ps.customer_id FROM phar_pos ps WHERE ps.id = x.reference_id)
    ELSE NULL END
WHERE (kind = '0' OR kind = '' OR x.kind = kind) AND (cashier_filter = 0 OR x.created_by = cashier_filter)
ORDER BY x.at;
END
