CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_top_diagnoses`(IN `start_date` DATE, IN `end_date` DATE, IN `top_n` INT)
BEGIN
-- Top diagnoses: how often each diagnosis was given in consultations of
-- visits in the period, with how many different patients and male / female.
SELECT t.`Rank`, t.`Diagnosis`, t.`Times diagnosed`, t.`Patients`, t.`Male`, t.`Female`
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS `Rank`, d.name AS `Diagnosis`,
       COUNT(*) AS `Times diagnosed`, COUNT(DISTINCT a.patient_id) AS `Patients`,
       SUM(LOWER(p.gender) = 'male') AS `Male`, SUM(LOWER(p.gender) = 'female') AS `Female`
FROM appointment_consultations c
JOIN appointments a ON a.id = c.appointment_id
JOIN diagnoses d ON FIND_IN_SET(d.id, REPLACE(c.diagnoses, ' ', ''))
LEFT JOIN patients p ON p.id = a.patient_id
WHERE a.date BETWEEN start_date AND end_date AND c.diagnoses IS NOT NULL AND c.diagnoses <> ''
GROUP BY d.id, d.name
) t
WHERE top_n = 0 OR t.`Rank` <= top_n
ORDER BY t.`Rank`;
END
