CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_registrations`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
-- Patient registrations per day: new patients registered, and the patients
-- who came (appointment) — split into new (first visit ever) and returning.
SELECT DATE_FORMAT(d.d, '%d %b, %Y') AS `Date`,
       d.registered AS `New registrations`,
       d.visits AS `Visits`,
       d.first_visit AS `New patients (first visit)`,
       d.visits - d.first_visit AS `Returning patients`
FROM (
    SELECT x.d,
           (SELECT COUNT(*) FROM patients p WHERE DATE(p.created_at) = x.d AND IFNULL(p.type, 'patient') = 'patient') registered,
           (SELECT COUNT(DISTINCT a.patient_id) FROM appointments a WHERE a.date = x.d) visits,
           (SELECT COUNT(DISTINCT a.patient_id) FROM appointments a WHERE a.date = x.d
              AND NOT EXISTS (SELECT 1 FROM appointments b WHERE b.patient_id = a.patient_id AND b.date < x.d)) first_visit
    FROM (
        SELECT DATE(created_at) d FROM patients WHERE DATE(created_at) BETWEEN start_date AND end_date
        UNION SELECT date FROM appointments WHERE date BETWEEN start_date AND end_date
    ) x
) d
ORDER BY d.d;
END
