SELECT
    P.person_id,
    P.employee_code,
    P.first_name,
    P.last_name,
    P.email,
    P.primary_telephone,
    P.address1,
    P.address2,
    P.city,
    P.country,
    P.state_province_region,
    P.zip_or_postal_code,
    P.is_blocked,
    CD.data_json AS customdata,
    VA.id AS appointment_id,
    VA.appointment_start AS appointment_start,
    VA.appointment_end AS appointment_end,
    VA.time_zone AS appointment_timezone,
    VA.slot_index AS appointment_slot_index,
    VA.sequence_index AS appointment_sequence_index,
    VA.appointment_status AS appointment_status,
    RI.visit_scenario AS scenario_name,
    R.roster_name AS schedule_name,
    PL_RI.name AS job_location,
    RI.slot_time AS slot_time,
    RI.slot_occupancy AS slot_occupancy,
    DATETIME(EVENT_DATA.first_checkin_time , EVENT_DATA.first_checkin_timezone|| ' seconds' ) AS first_checkin_time,
    EVENT_DATA.first_checkin_timezone,
    D_IN.description AS first_checkin_device,
    PL_IN.name AS first_checkin_location,
    DATETIME(EVENT_DATA.last_checkout_time, EVENT_DATA.last_checkout_timezone|| ' seconds' ) AS last_checkout_time,
    EVENT_DATA.last_checkout_timezone,
    D_OUT.description AS last_checkout_device,
    PL_OUT.name AS last_checkout_location,
    EVENT_DATA.access_count AS access_count,
    EVENT_DATA.last_attendance_event AS last_attendance_event,
    EVENT_DATA.spent_time AS spent_time

FROM

    (SELECT
         :onparam AS calculated_on,
         *
     FROM
         (
             SELECT
                 BASE.appointment_id,
                 BASE.appointment_day,
                 BASE.appointment_timezone AS appointment_timezone,
                 DATETIME(BASE.appointment_start, (-BASE.appointment_timezone)|| ' seconds' ) AS appointment_start,
                 DATETIME(BASE.appointment_end, (-BASE.appointment_timezone)|| ' seconds' ) AS appointment_end,
                 DATETIME(BASE.appointment_day, (-BASE.appointment_timezone + BASE.shift_start_second)|| ' seconds' ) AS appointment_shift_start,
                 DATETIME(BASE.appointment_day, (-BASE.appointment_timezone + BASE.shift_end_second)|| ' seconds' ) AS appointment_shift_end,
                 DATETIME(BASE.appointment_day, (-BASE.appointment_timezone + BASE.cin_start)|| ' seconds' ) AS appointment_cin_start,
                 DATETIME(BASE.appointment_day, (-BASE.appointment_timezone + BASE.cout_end)|| ' seconds' ) AS appointment_cout_end
             FROM
                 (
                     SELECT
                         va.id AS appointment_id,
                         va.time_zone AS appointment_timezone,
                         va.appointment_start,
                         va.appointment_end,
                         CASE
                             WHEN (t.task_id IS NOT NULL
                                 AND (STRFTIME ('%s',
                                                va.appointment_start) -STRFTIME ('%s',
                                                                                 DATE(va.appointment_start))) < t.start_time_seconds) THEN DATE(va.appointment_start, '-1 days')
                         ELSE DATE(va.appointment_start)
                         END AS appointment_day,

                         t.start_time_seconds AS shift_start_second,

                         CASE
                         WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                         ELSE t.end_time_seconds
                         END AS shift_end_second,

                         CASE
                         WHEN (t.checkin_start IS NOT NULL) THEN
                         (CASE
                         WHEN (t.start_time_seconds >= t.checkin_start) THEN t.checkin_start
                         ELSE t.checkin_start - 86400
                         END)
                         ELSE
                         (CASE
                         WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds
                         ELSE t.end_time_seconds - 86400
                         END)
                         END AS cin_start,

                         CASE
                         WHEN (t.checkout_end IS NOT NULL) THEN
                         (CASE
                         WHEN ((CASE
                         WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                         ELSE t.end_time_seconds
                         END) < t.checkout_end) THEN t.checkout_end
                         ELSE t.checkout_end + 86400
                         END)
                         ELSE
                         t.start_time_seconds + 86400
                         END AS cout_end
                     FROM
                         visitor_appointments va
                         LEFT JOIN roster_item ri ON
                         va.attendant_roster_item_id = ri.roster_item_id
                         LEFT JOIN roster r ON
                         ri.roster_id = r.roster_id
                         LEFT JOIN task t ON
                         ri.task_id = t.task_id
                     WHERE
                       va.appointment_start BETWEEN DATE(:onparam , '-2 DAYS') AND DATE(:onparam , '+2 DAYS')
                       AND va.request_status = 'APPROVED'
                       AND va.customer_id = :customerparam
                       AND r.deleted_date IS NULL

                 ) AS BASE) AS CURRENT_APPOINTMENTS
     WHERE
             CURRENT_APPOINTMENTS.appointment_cin_start <= :onparam
       AND CURRENT_APPOINTMENTS.appointment_cout_end > :onparam ) AS APPOINTMENTS


        LEFT JOIN
    (SELECT
         EVENT_CORE.visit_appointment,
         CIN.event_id AS first_checkin,
         CIN.event_time AS first_checkin_time,
         CIN.event_time_zone AS first_checkin_timezone,
         CIN.device_id AS first_checkin_device,
         CIN.physical_location_id AS first_checkin_location,
         COUT.event_id AS last_checkout,
         COUT.event_time AS last_checkout_time,
         COUT.event_time_zone AS last_checkout_timezone,
         COUT.device_id AS last_checkout_device,
         COUT.physical_location_id AS last_checkout_location,
         EVENT_CORE.access_count AS access_count,
         EVENT_CORE.spent_time AS spent_time,
         CASE
             WHEN last_checkin IS NULL THEN NULL
             WHEN last_checkout IS NULL OR last_checkout < last_checkin THEN 0
             ELSE 1
         END AS last_attendance_event

     FROM
         (
             SELECT
                 cin.visit_appointment ,
                 MIN(CASE WHEN cin.event_type = 0 THEN cin.event_time ELSE NULL END) AS first_checkin,
                 MAX(CASE WHEN cin.event_type = 0 THEN cin.event_time ELSE NULL END) AS last_checkin,
                 MAX(cout.event_time) AS last_checkout,
                 SUM(CASE WHEN cin.event_type = 'ACCESS' THEN 1 ELSE 0 END) AS access_count,
                 SUM(CASE WHEN cout.event_time IS NOT NULL THEN (JULIANDAY(cout.event_time)*86400 - JULIANDAY(cin.event_time) * 86400 ) ELSE 0 END ) AS spent_time
             FROM
                 eventlog cin
                     INNER JOIN person p ON
                         cin.person_id = p.person_id
                     LEFT JOIN eventlog cout ON
                             cin.next_event = cout.event_id
                         AND cout.event_time <= :onparam
                     LEFT JOIN visitor_appointments VA ON
                         cin.visit_appointment = VA.id
             WHERE
                     p.customer_id = :customerparam
               AND VA.appointment_start BETWEEN DATE(:onparam , '-2 DAYS') AND DATE(:onparam , '+2 DAYS')
                  AND VA.request_status = 'APPROVED'
                  AND p.person_type = 'VISITOR'
                  AND cin.is_deleted = FALSE
                  AND cin.event_time <= :onparam
     GROUP BY
         cin.visit_appointment) AS EVENT_CORE
        LEFT JOIN eventlog CIN
                  ON
                              EVENT_CORE.visit_appointment = CIN.visit_appointment
                          AND
                              EVENT_CORE.first_checkin = CIN.event_time
                          AND CIN.event_type = 0
                          AND CIN.is_deleted = false
        LEFT JOIN eventlog COUT
                  ON
                              EVENT_CORE.visit_appointment = COUT.visit_appointment
                          AND
                              EVENT_CORE.last_checkout = COUT.event_time
                          AND COUT.event_type = 1
                          AND COUT.is_deleted = false) AS EVENT_DATA

ON APPOINTMENTS.appointment_id = EVENT_DATA.visit_appointment
    LEFT JOIN visitor_appointments VA ON APPOINTMENTS.appointment_id = VA.id
    INNER JOIN visit V ON
    VA.visit_id = V.id
    LEFT JOIN person P ON
    V.visitor_id = P.person_id
    LEFT JOIN customdata CD ON
    P.custom_data_id = CD.custom_data_id
    LEFT JOIN roster_item RI ON
    VA.attendant_roster_item_id = RI.roster_item_id
    LEFT JOIN physical_locations PL_RI ON
    RI.job_location_id = PL_RI.id
    LEFT JOIN roster R ON
    RI.roster_id = R.roster_id
    LEFT JOIN device D_IN ON
    EVENT_DATA.first_checkin_device = D_IN.device_id
    LEFT JOIN physical_locations PL_IN ON
    EVENT_DATA.first_checkin_location = PL_IN.id
    LEFT JOIN device D_OUT ON
    EVENT_DATA.last_checkout_device = D_OUT.device_id
    LEFT JOIN physical_locations PL_OUT ON
    EVENT_DATA.last_checkout_location = PL_OUT.id
WHERE VA.customer_id =:customerparam
  :PERSON_QRY
--   AND P.person_id = :person_param
  :ROSTER_QRY
--   AND R.roster_id = :schedule_param
  :APPOINTMENT_QRY
--   AND VA.id = :appointment_param
  ORDER BY VA.appointment_start