SELECT
    roster_instance.roster_id AS rosterId,
    cdt.cloud_date AS shiftDate,
    roster_instance.period_start AS periodStart,
    roster_instance.period_end AS periodEnd,
    roster_instance.period_index AS periodIndex,

    ri.roster_item_id AS rosterItemId,
    ri.slot_time AS slotTime,
    ri.slot_occupancy AS slotOccupancy,
    ri_cd.data_json AS rosterItemCustomData,

    pl.location_code AS locationCode,
    pl.name AS locationName,
    pl.description AS locationDescription,
    pl.latitude AS latitude,
    pl.longitude AS longitude,
    pl.timezone AS timezone,

    t.name AS shiftName,
    t.description AS shiftDescription,
    t.start_time_seconds AS startTimeSeconds,
    t.end_time_seconds AS endTimeSeconds,
    t.break_time_list AS breakTime

FROM
    (
        SELECT
            DISTINCT
            CASE
                WHEN(r.booking_period = 'MONTHLY' ) THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( MONTH, r.start_date, cd.cloud_date ) DIV (r.booking_period_value))* r.booking_period_value ) MONTH)
                WHEN (r.booking_period = 'WEEKLY') THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( WEEK , r.start_date , cd.cloud_date) DIV (r.booking_period_value))*(r.booking_period_value)) WEEK)
                WHEN (r.booking_period = 'DAILY') THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( DAY, r.start_date , cd.cloud_date) DIV (r.booking_period_value))*(r.booking_period_value)) DAY)
                END AS period_start,

            CASE
                WHEN(r.booking_period = 'MONTHLY' ) THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( MONTH, r.start_date, cd.cloud_date ) DIV (r.booking_period_value))* r.booking_period_value ) + booking_period_value MONTH)
                WHEN (r.booking_period = 'WEEKLY') THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( WEEK , r.start_date , cd.cloud_date) DIV (r.booking_period_value))*(r.booking_period_value)) + booking_period_value WEEK)
                WHEN (r.booking_period = 'DAILY') THEN DATE_ADD(r.start_date , INTERVAL ((TIMESTAMPDIFF( DAY, r.start_date , cd.cloud_date) DIV (r.booking_period_value))*(r.booking_period_value)) + booking_period_value DAY)
                END AS period_end,

            CASE
                WHEN(r.booking_period = 'MONTHLY' ) THEN TIMESTAMPDIFF( MONTH, r.start_date, cd.cloud_date ) DIV (r.booking_period_value)
                WHEN (r.booking_period = 'WEEKLY') THEN TIMESTAMPDIFF( WEEK , r.start_date , cd.cloud_date) DIV (r.booking_period_value)
                WHEN (r.booking_period = 'DAILY') THEN TIMESTAMPDIFF( DAY, r.start_date , cd.cloud_date) DIV (r.booking_period_value)
                END AS period_index,
            r.*
        FROM
            roster r
                CROSS JOIN cloud_date_table cd
                LEFT JOIN visit_scenario_config vsc ON
                    r.id = vsc.id
        WHERE
            cd.cloud_date BETWEEN :fromparam AND :toparam
          AND r.deleted_date IS NULL
          AND r.start_date <= cd.cloud_date
          AND (r.end_date IS NULL OR r.end_date >= cd.cloud_date)
          AND r.customer_id = :customerparam
          AND r.roster_creator = 'VISIT_JOB'

          :ROSTER_FILTER
          :KIOSK_FILTER
          :PUBLIC_FILTER

          AND r.start_date <= :toparam
          AND (r.end_date IS NULL
            OR r.end_date >= :fromparam)
    ) AS roster_instance
        INNER JOIN cloud_date_table cdt ON
                cdt.cloud_date >= roster_instance.period_start
            AND cdt.cloud_date < roster_instance.period_end
        INNER JOIN roster_item ri ON
                ri.roster_id = roster_instance.roster_id
            AND ri.deleted_date IS NULL
            AND
                ( CASE
                      WHEN(roster_instance.roster_type = 'MONTHLY' ) THEN DATEDIFF(cdt.cloud_date, DATE_ADD(roster_instance.start_date , INTERVAL ((TIMESTAMPDIFF( MONTH, roster_instance.start_date, cdt.cloud_date ) DIV (roster_instance.repetition))* roster_instance.repetition ) MONTH))
                      WHEN (roster_instance.roster_type = 'WEEKLY') THEN DATEDIFF(cdt.cloud_date, DATE_ADD(roster_instance.start_date , INTERVAL ((TIMESTAMPDIFF( WEEK , roster_instance.start_date , cdt.cloud_date) DIV (roster_instance.repetition))*(roster_instance.repetition)) WEEK))
                      WHEN (roster_instance.roster_type = 'DAILY') THEN DATEDIFF(cdt.cloud_date, DATE_ADD(roster_instance.start_date , INTERVAL ((TIMESTAMPDIFF( DAY, roster_instance.start_date , cdt.cloud_date) DIV (roster_instance.repetition))*(roster_instance.repetition)) DAY))
                    END ) = ri.day_number
        INNER JOIN (
        SELECT
            ri.roster_item_id
        FROM
            roster_item ri
                LEFT JOIN rosteritem_person_group rpg ON
                    ri.roster_item_id = rpg.roster_item_id
                LEFT JOIN user_group_membership ugm ON
                    rpg.assigned_group_id = ugm.assigned_group_id
        WHERE
                ri.customer_id = :customerparam
            :EMPLOYEE_FILTER
        GROUP BY
            ri.roster_item_id

        ) AS employee_roster_item
        ON ri.roster_item_id = employee_roster_item.roster_item_id

        LEFT JOIN customdata ri_cd ON
            ri.custom_data_id = ri_cd.custom_data_id
        LEFT JOIN task t ON
            ri.task_id = t.task_id
        LEFT JOIN physical_locations pl ON
            ri.job_location_id = pl.id
        WHERE
        roster_instance.customer_id = :customerparam
        AND (roster_instance.end_date IS NULL OR roster_instance.end_date >= roster_instance.period_end)
        :BOOKING_FILTER
        :PERIOD_INDEX_FILTER
ORDER BY
    periodStart, shiftDate