CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_births_deaths`(IN `start_date` DATE, IN `end_date` DATE, IN `kind` VARCHAR(10))
BEGIN
-- Births (deliveries and birth certificates) and deaths (death certificates).
SELECT DATE_FORMAT(x.d, '%d %b, %Y') AS `Date`, x.kind AS `Event`, x.who AS `Patient / mother`, x.detail AS `Details`, x.by_ AS `By`
FROM (
    SELECT pd.dob d, 'Birth' kind, COALESCE(p.name, '') who,
           CONCAT_WS(' · ', NULLIF(pd.baby_sex, ''), IF(pd.baby_weight IS NULL OR pd.baby_weight = '', NULL, CONCAT(pd.baby_weight, ' kg')),
                     NULLIF(dm.name, ''), IF(pd.apgar_score IS NULL OR pd.apgar_score = '', NULL, CONCAT('APGAR ', pd.apgar_score)),
                     IF(IFNULL(pd.complication, '') IN ('', 'No'), NULL, CONCAT('Complication: ', COALESCE(pd.complication_text, pd.complication)))) detail,
           COALESCE(u.name, '') by_
    FROM post_deliveries pd JOIN deliveries dl ON dl.id = pd.delivery_id
    LEFT JOIN patients p ON p.id = dl.patient_id LEFT JOIN delivery_modes dm ON dm.id = pd.delivery_mode LEFT JOIN users u ON u.id = pd.created_by
    UNION ALL
    SELECT b.dob, 'Birth (certificate)', COALESCE(p.name, ''), CONCAT_WS(' · ', NULLIF(b.child_name, ''), NULLIF(b.child_gender, ''), IF(b.weight IS NULL OR b.weight = '', NULL, CONCAT(b.weight, ' kg'))), COALESCE(u.name, '')
    FROM birth_certficates b LEFT JOIN patients p ON p.id = b.patient_id LEFT JOIN users u ON u.id = b.created_by
    UNION ALL
    SELECT dc.dod, 'Death', COALESCE(p.name, ''), COALESCE(dc.death_reason, ''), COALESCE(u.name, '')
    FROM death_certficates dc LEFT JOIN patients p ON p.id = dc.patient_id LEFT JOIN users u ON u.id = dc.created_by
) x
WHERE DATE(x.d) BETWEEN start_date AND end_date
  AND (kind = '0' OR kind = '' OR (kind = 'Births' AND x.kind LIKE 'Birth%') OR (kind = 'Deaths' AND x.kind = 'Death'))
ORDER BY x.d;
END
