CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_turnaround`(IN `start_date` DATE, IN `end_date` DATE, IN `kind` VARCHAR(10))
BEGIN
-- Turnaround time per request: from the request to the first result (lab) or
-- report (imaging), for the requests with a result in the period.
SELECT DATE_FORMAT(x.at, '%d %b, %Y %H:%i') AS `Requested`, DATE_FORMAT(x.done_at, '%d %b, %Y %H:%i') AS `Result`,
       x.kind AS `Kind`, x.req AS `Request`, COALESCE(p.name, '') AS `Patient`, x.tests AS `Tests`,
       FORMAT(TIMESTAMPDIFF(MINUTE, x.at, x.done_at) / 60, 1) AS `Turnaround (h)`
FROM (
    SELECT l.created_at at, COALESCE((SELECT MIN(res.created_at) FROM lab_request_results res WHERE res.lab_request_id = l.id), l.updated_at) done_at,
           'Lab' kind, CONCAT('#', COALESCE(l.request_no, l.id)) req, l.patient pid,
           COALESCE((SELECT GROUP_CONCAT(COALESCE(NULLIF(r.routine_name, ''), lt.name) SEPARATOR ', ') FROM lab_request_routines lrr
                     LEFT JOIN lab_lab_type_routines r ON r.id = lrr.routine_id LEFT JOIN lab_lab_types lt ON lt.id = l.lab_type WHERE lrr.lab_request_id = l.id), '') tests
    FROM lab_requests l WHERE l.status = 'Processed' AND (kind = '0' OR kind = '' OR kind = 'Lab')
    UNION ALL
    SELECT i.created_at, COALESCE((SELECT MIN(rep.created_at) FROM imaging_reports rep WHERE rep.imaging_request_id = i.id), i.updated_at),
           'Imaging', CONCAT('#', COALESCE(i.request_no, i.id)), i.patient, COALESCE(it.name, '')
    FROM image_requests i LEFT JOIN image_types it ON it.id = i.lab_type
    WHERE i.status = 'Processed' AND (kind = '0' OR kind = '' OR kind = 'Imaging')
) x
LEFT JOIN patients p ON p.id = x.pid
WHERE DATE(x.done_at) BETWEEN start_date AND end_date
ORDER BY TIMESTAMPDIFF(MINUTE, x.at, x.done_at) DESC;
END
