CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_patient_information`(IN `start_date` DATE, IN `end_date` DATE, IN `gender_filter` VARCHAR(10), IN `type_filter` VARCHAR(20))
BEGIN
-- Patient information: patients registered in the period with their details
-- and how much they used the hospital (appointments, admissions, emergency).
SELECT
        DATE_FORMAT(p.created_at, '%d %b, %Y')                         AS `Registered`,
        p.patient_id                                                   AS `Patient ID`,
        p.name                                                         AS `Name`,
        CONCAT(UPPER(LEFT(COALESCE(p.gender, ''), 1)), SUBSTRING(COALESCE(p.gender, ''), 2)) AS `Gender`,
        CASE WHEN p.dob IS NOT NULL AND TIMESTAMPDIFF(YEAR, p.dob, CURDATE()) BETWEEN 0 AND 129
             THEN TIMESTAMPDIFF(YEAR, p.dob, CURDATE()) ELSE '' END   AS `Age`,
        COALESCE(p.phone, '')                                          AS `Phone`,
        COALESCE(p.address, '')                                        AS `Address`,
        COALESCE(p.marital_status, '')                                 AS `Marital status`,
        COALESCE(p.blood_group, '')                                    AS `Blood group`,
        CASE WHEN EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id) THEN 'In-patient' ELSE 'Out-patient' END AS `Type`,
        (SELECT COUNT(*) FROM appointments ap WHERE ap.patient_id = p.id) AS `Appointments`,
        (SELECT COUNT(*) FROM admissions a WHERE a.patient_id = p.id)     AS `Admissions`,
        (SELECT COUNT(*) FROM emergencies e WHERE e.patient_id = p.id)    AS `Emergency visits`,
        COALESCE((SELECT DATE_FORMAT(MAX(ap.date), '%d %b, %Y') FROM appointments ap WHERE ap.patient_id = p.id), '') AS `Last appointment`,
        COALESCE(u.name, '')                                           AS `Registered by`
    FROM patients p
    LEFT JOIN users u ON u.id = p.created_by
    WHERE p.type = 'patient'
      AND DATE(p.created_at) BETWEEN start_date AND end_date
      AND (gender_filter = '0' OR gender_filter = '' OR p.gender = gender_filter)
      AND (type_filter = '0' OR type_filter = ''
           OR (type_filter = 'In-patient' AND EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id))
           OR (type_filter = 'Out-patient' AND NOT EXISTS (SELECT 1 FROM admissions a WHERE a.patient_id = p.id)))
    ORDER BY p.created_at;
END
