CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_imaging_reports`(IN `start_date` DATE, IN `end_date` DATE, IN `doctor_filter` INT, IN `status_filter` VARCHAR(20))
BEGIN
-- Imaging reports: every imaging request in the period with its report —
-- reported or still pending, who reported it, and what it concluded.
SELECT
    DATE_FORMAT(i.date, '%d %b, %Y')                                   AS `Date`,
    CONCAT('#', COALESCE(i.request_no, i.id))                          AS `Request`,
    p.name                                                             AS `Patient`,
    p.patient_id                                                       AS `Patient ID`,
    COALESCE(it.name, '')                                              AS `Exam`,
    COALESCE(d.name, '')                                               AS `Requesting doctor`,
    CASE WHEN i.status = 'Processed' THEN 'Reported' ELSE 'Pending report' END AS `Report status`,
    COALESCE(REPLACE(REPLACE(r.report_category, '_', ' '), 'ultrasound', 'Ultrasound'), '') AS `Category`,
    COALESCE(rd.name, '')                                              AS `Reporting doctor`,
    COALESCE(DATE_FORMAT(r.created_at, '%d %b, %Y'), '')               AS `Reported on`,
    COALESCE(LEFT(COALESCE(NULLIF(r.conclusion, ''), NULLIF(r.impression, ''), r.findings), 160), '') AS `Conclusion`
FROM image_requests i
LEFT JOIN patients p     ON p.id = i.patient
LEFT JOIN image_types it ON it.id = i.lab_type
LEFT JOIN hrm_staffs d   ON d.id = i.doctor_id
LEFT JOIN imaging_reports r ON r.id = (SELECT MAX(r2.id) FROM imaging_reports r2 WHERE r2.imaging_request_id = i.id)
LEFT JOIN hrm_staffs rd  ON rd.id = r.reporting_doctor_id
WHERE i.date BETWEEN start_date AND end_date
  AND (doctor_filter = 0 OR r.reporting_doctor_id = doctor_filter OR i.doctor_id = doctor_filter)
  AND (status_filter = '0' OR status_filter = ''
       OR (status_filter = 'Reported' AND i.status = 'Processed')
       OR (status_filter = 'Pending report' AND (i.status IS NULL OR i.status <> 'Processed')))
ORDER BY i.date, i.id;
END
