USE caris_db;
SET
  @start_date = "2019-01-01";
SET
  @end_date = "2019-03-31";
SELECT 
    patient_code,
    COALESCE(ti.dob, tm.infant_dob, ts.date_of_birth) AS dob,
    IF(TIMESTAMPDIFF(YEAR,
            COALESCE(IF(tm.infant_dob = '0000-00-00',
                        NULL,
                        tm.infant_dob),
                    IF(ts.date_of_birth = '0000-00-00',
                        NULL,
                        ts.date_of_birth),
                    ti.dob),
            @start_date) < 1
            AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                    NULL,
                    tm.infant_dob),
                IF(ts.date_of_birth = '0000-00-00',
                    NULL,
                    ts.date_of_birth),
                ti.dob) != '0000-00-00',
        'under1',
        IF(TIMESTAMPDIFF(YEAR,
                COALESCE(IF(tm.infant_dob = '0000-00-00',
                            NULL,
                            tm.infant_dob),
                        IF(ts.date_of_birth = '0000-00-00',
                            NULL,
                            ts.date_of_birth),
                        ti.dob),
                @start_date) >= 1
                AND TIMESTAMPDIFF(YEAR,
                COALESCE(IF(tm.infant_dob = '0000-00-00',
                            NULL,
                            tm.infant_dob),
                        IF(ts.date_of_birth = '0000-00-00',
                            NULL,
                            ts.date_of_birth),
                        ti.dob),
                @start_date) < 10
                AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                        NULL,
                        tm.infant_dob),
                    IF(ts.date_of_birth = '0000-00-00',
                        NULL,
                        ts.date_of_birth),
                    ti.dob) != '0000-00-00',
            'bt_1_9',
            IF(TIMESTAMPDIFF(YEAR,
                    COALESCE(IF(tm.infant_dob = '0000-00-00',
                                NULL,
                                tm.infant_dob),
                            IF(ts.date_of_birth = '0000-00-00',
                                NULL,
                                ts.date_of_birth),
                            ti.dob),
                    @start_date) < 1
                    AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                            NULL,
                            tm.infant_dob),
                        IF(ts.date_of_birth = '0000-00-00',
                            NULL,
                            ts.date_of_birth),
                        ti.dob) != '0000-00-00'
                    AND (ti.gender = 1 OR tm.infant_gender = 1),
                'm_under1',
                IF(TIMESTAMPDIFF(YEAR,
                        COALESCE(IF(tm.infant_dob = '0000-00-00',
                                    NULL,
                                    tm.infant_dob),
                                IF(ts.date_of_birth = '0000-00-00',
                                    NULL,
                                    ts.date_of_birth),
                                ti.dob),
                        @start_date) < 1
                        AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                NULL,
                                tm.infant_dob),
                            IF(ts.date_of_birth = '0000-00-00',
                                NULL,
                                ts.date_of_birth),
                            ti.dob) != '0000-00-00'
                        AND (ti.gender = 2 OR tm.infant_gender = 2),
                    'f_under1',
                    IF(TIMESTAMPDIFF(YEAR,
                            COALESCE(IF(tm.infant_dob = '0000-00-00',
                                        NULL,
                                        tm.infant_dob),
                                    IF(ts.date_of_birth = '0000-00-00',
                                        NULL,
                                        ts.date_of_birth),
                                    ti.dob),
                            @start_date) >= 1
                            AND TIMESTAMPDIFF(YEAR,
                            COALESCE(IF(tm.infant_dob = '0000-00-00',
                                        NULL,
                                        tm.infant_dob),
                                    IF(ts.date_of_birth = '0000-00-00',
                                        NULL,
                                        ts.date_of_birth),
                                    ti.dob),
                            @start_date) < 10
                            AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                    NULL,
                                    tm.infant_dob),
                                IF(ts.date_of_birth = '0000-00-00',
                                    NULL,
                                    ts.date_of_birth),
                                ti.dob) != '0000-00-00'
                            AND (ti.gender = 1 OR tm.infant_gender = 1),
                        'm_bt_1_9',
                        IF(TIMESTAMPDIFF(YEAR,
                                COALESCE(IF(tm.infant_dob = '0000-00-00',
                                            NULL,
                                            tm.infant_dob),
                                        IF(ts.date_of_birth = '0000-00-00',
                                            NULL,
                                            ts.date_of_birth),
                                        ti.dob),
                                @start_date) >= 1
                                AND TIMESTAMPDIFF(YEAR,
                                COALESCE(IF(tm.infant_dob = '0000-00-00',
                                            NULL,
                                            tm.infant_dob),
                                        IF(ts.date_of_birth = '0000-00-00',
                                            NULL,
                                            ts.date_of_birth),
                                        ti.dob),
                                @start_date) < 10
                                AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                        NULL,
                                        tm.infant_dob),
                                    IF(ts.date_of_birth = '0000-00-00',
                                        NULL,
                                        ts.date_of_birth),
                                    ti.dob) != '0000-00-00'
                                AND (ti.gender = 2 OR tm.infant_gender = 2),
                            'f_bt_1_9',
                            IF(TIMESTAMPDIFF(YEAR,
                                    COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                NULL,
                                                tm.infant_dob),
                                            IF(ts.date_of_birth = '0000-00-00',
                                                NULL,
                                                ts.date_of_birth),
                                            ti.dob),
                                    @start_date) >= 10
                                    AND TIMESTAMPDIFF(YEAR,
                                    COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                NULL,
                                                tm.infant_dob),
                                            IF(ts.date_of_birth = '0000-00-00',
                                                NULL,
                                                ts.date_of_birth),
                                            ti.dob),
                                    @start_date) < 15
                                    AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                            NULL,
                                            tm.infant_dob),
                                        IF(ts.date_of_birth = '0000-00-00',
                                            NULL,
                                            ts.date_of_birth),
                                        ti.dob) != '0000-00-00'
                                    AND (ti.gender = 1 OR tm.infant_gender = 1),
                                'm_bt_10_14',
                                IF(TIMESTAMPDIFF(YEAR,
                                        COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                    NULL,
                                                    tm.infant_dob),
                                                IF(ts.date_of_birth = '0000-00-00',
                                                    NULL,
                                                    ts.date_of_birth),
                                                ti.dob),
                                        @start_date) >= 10
                                        AND TIMESTAMPDIFF(YEAR,
                                        COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                    NULL,
                                                    tm.infant_dob),
                                                IF(ts.date_of_birth = '0000-00-00',
                                                    NULL,
                                                    ts.date_of_birth),
                                                ti.dob),
                                        @start_date) < 15
                                        AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                NULL,
                                                tm.infant_dob),
                                            IF(ts.date_of_birth = '0000-00-00',
                                                NULL,
                                                ts.date_of_birth),
                                            ti.dob) != '0000-00-00'
                                        AND (ti.gender = 2 OR tm.infant_gender = 2),
                                    'f_bt_10_14',
                                    IF(TIMESTAMPDIFF(YEAR,
                                            COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                        NULL,
                                                        tm.infant_dob),
                                                    IF(ts.date_of_birth = '0000-00-00',
                                                        NULL,
                                                        ts.date_of_birth),
                                                    ti.dob),
                                            @start_date) >= 15
                                            AND TIMESTAMPDIFF(YEAR,
                                            COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                        NULL,
                                                        tm.infant_dob),
                                                    IF(ts.date_of_birth = '0000-00-00',
                                                        NULL,
                                                        ts.date_of_birth),
                                                    ti.dob),
                                            @start_date) < 18
                                            AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                    NULL,
                                                    tm.infant_dob),
                                                IF(ts.date_of_birth = '0000-00-00',
                                                    NULL,
                                                    ts.date_of_birth),
                                                ti.dob) != '0000-00-00'
                                            AND (ti.gender = 1 OR tm.infant_gender = 1),
                                        'm_bt_15_17',
                                        IF(TIMESTAMPDIFF(YEAR,
                                                COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                            NULL,
                                                            tm.infant_dob),
                                                        IF(ts.date_of_birth = '0000-00-00',
                                                            NULL,
                                                            ts.date_of_birth),
                                                        ti.dob),
                                                @start_date) >= 15
                                                AND TIMESTAMPDIFF(YEAR,
                                                COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                            NULL,
                                                            tm.infant_dob),
                                                        IF(ts.date_of_birth = '0000-00-00',
                                                            NULL,
                                                            ts.date_of_birth),
                                                        ti.dob),
                                                @start_date) < 18
                                                AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                        NULL,
                                                        tm.infant_dob),
                                                    IF(ts.date_of_birth = '0000-00-00',
                                                        NULL,
                                                        ts.date_of_birth),
                                                    ti.dob) != '0000-00-00'
                                                AND (ti.gender = 2 OR tm.infant_gender = 2),
                                            'f_bt_15_17',
                                            IF(TIMESTAMPDIFF(YEAR,
                                                    COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                NULL,
                                                                tm.infant_dob),
                                                            IF(ts.date_of_birth = '0000-00-00',
                                                                NULL,
                                                                ts.date_of_birth),
                                                            ti.dob),
                                                    @start_date) >= 18
                                                    AND TIMESTAMPDIFF(YEAR,
                                                    COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                NULL,
                                                                tm.infant_dob),
                                                            IF(ts.date_of_birth = '0000-00-00',
                                                                NULL,
                                                                ts.date_of_birth),
                                                            ti.dob),
                                                    @start_date) < 25
                                                    AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                            NULL,
                                                            tm.infant_dob),
                                                        IF(ts.date_of_birth = '0000-00-00',
                                                            NULL,
                                                            ts.date_of_birth),
                                                        ti.dob) != '0000-00-00'
                                                    AND (ti.gender = 1 OR tm.infant_gender = 1),
                                                'm_bt_18_24',
                                                IF(TIMESTAMPDIFF(YEAR,
                                                        COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                    NULL,
                                                                    tm.infant_dob),
                                                                IF(ts.date_of_birth = '0000-00-00',
                                                                    NULL,
                                                                    ts.date_of_birth),
                                                                ti.dob),
                                                        @start_date) >= 18
                                                        AND TIMESTAMPDIFF(YEAR,
                                                        COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                    NULL,
                                                                    tm.infant_dob),
                                                                IF(ts.date_of_birth = '0000-00-00',
                                                                    NULL,
                                                                    ts.date_of_birth),
                                                                ti.dob),
                                                        @start_date) < 25
                                                        AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                NULL,
                                                                tm.infant_dob),
                                                            IF(ts.date_of_birth = '0000-00-00',
                                                                NULL,
                                                                ts.date_of_birth),
                                                            ti.dob) != '0000-00-00'
                                                        AND (ti.gender = 2 OR tm.infant_gender = 2),
                                                    'f_bt_18_24',
                                                    IF(TIMESTAMPDIFF(YEAR,
                                                            COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                        NULL,
                                                                        tm.infant_dob),
                                                                    IF(ts.date_of_birth = '0000-00-00',
                                                                        NULL,
                                                                        ts.date_of_birth),
                                                                    ti.dob),
                                                            @start_date) >= 25
                                                            AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                    NULL,
                                                                    tm.infant_dob),
                                                                IF(ts.date_of_birth = '0000-00-00',
                                                                    NULL,
                                                                    ts.date_of_birth),
                                                                ti.dob) != '0000-00-00'
                                                            AND (ti.gender = 1 OR tm.infant_gender = 1),
                                                        'm_25',
                                                        IF(TIMESTAMPDIFF(YEAR,
                                                                COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                            NULL,
                                                                            tm.infant_dob),
                                                                        IF(ts.date_of_birth = '0000-00-00',
                                                                            NULL,
                                                                            ts.date_of_birth),
                                                                        ti.dob),
                                                                @start_date) >= 25
                                                                AND COALESCE(IF(tm.infant_dob = '0000-00-00',
                                                                        NULL,
                                                                        tm.infant_dob),
                                                                    IF(ts.date_of_birth = '0000-00-00',
                                                                        NULL,
                                                                        ts.date_of_birth),
                                                                    ti.dob) != '0000-00-00'
                                                                AND (ti.gender = 2 OR tm.infant_gender = 2),
                                                            'f_25',
                                                            '')))))))))))))) AS tranche,
    IF(regime.id_patient IS NOT NULL,
        'yes',
        'non') AS on_arv,
    office,
    IF(statut.id_patient IS NOT NULL,
        'pos',
        'neg') AS statut
FROM
    (SELECT 
        id_patient, MIN(date) AS date
    FROM
        (SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        questionnaire_child
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        patient.created_at >= @start_date
            AND date IS NOT NULL
            AND date != '0000-00-00') ques UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        patient.id AS id_patient, date_of_visit AS date
    FROM
        patient
    LEFT JOIN openfn.odk_child_visit od ON patient.patient_code = od.patient_code
    WHERE
        date_of_visit IS NOT NULL
            AND date_of_visit != '0000-00-00'
            AND patient.created_at >= @start_date) odk UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    LEFT JOIN club ON club.id = id_club
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        is_present = 1 AND club_type != 1
            AND patient.created_at >= @start_date) club UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        testing_mereenfant UNION SELECT 
        id_patient, date_blood_taken AS date
    FROM
        testing_specimen UNION SELECT 
        id_patient, blood_draw_date AS date
    FROM
        testing_result) pcr
    LEFT JOIN patient ON patient.id = pcr.id_patient
    WHERE
        patient.created_at >= @start_date) p UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        tracking_followup
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        patient.created_at >= @start_date) o) last
    GROUP BY id_patient) lastone
        LEFT JOIN
    patient ON patient.id = lastone.id_patient
        LEFT JOIN
    tracking_infant ti ON ti.id_patient = lastone.id_patient
        LEFT JOIN
    testing_mereenfant tm ON lastone.id_patient = tm.id_patient
        LEFT JOIN
    testing_specimen ts ON ts.id_patient = lastone.id_patient
        LEFT JOIN
    lookup_hospital ON CONCAT(lookup_hospital.city_code,
            '/',
            lookup_hospital.hospital_code) = LEFT(patient_code, 8)
        LEFT JOIN
    (SELECT DISTINCT
        id_patient
    FROM
        tracking_regime
    WHERE
        category = 'regime_infant_treatment') regime ON regime.id_patient = lastone.id_patient
        LEFT JOIN
    (SELECT 
        id AS id_patient
    FROM
        view_patient_positive UNION SELECT 
        id_patient
    FROM
        tracking_regime
    WHERE
        category = 'regime_infant_treatment') statut ON statut.id_patient = lastone.id_patient
WHERE
    lastone.date BETWEEN @start_date AND @end_date
        AND patient.created_at >= @start_date
        AND lastone.id_patient IS NOT NULL
        AND linked_to_id_patient = 0
GROUP BY lastone.id_patient