# By: Riasat Ullah
# This file contains functions related to routines.

from dbqueries import db_users
from objects.routine import Routine
from objects.routine_layer import RoutineLayer
from psycopg2 import errorcodes
from utils import constants, errors, helpers, key_manager, permissions, var_names
from validations import component_validator, string_validator
import configuration as configs
import datetime
import json
import psycopg2
import uuid


def create_routine(conn, timestamp, organization_id, routine_name, routine_timezone, routine_layers):
    '''
    Create a new routine.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made on
    :param organization_id: ID of the organization this routine is for
    :param routine_name: name of the routine
    :param routine_timezone: timezone of the routine
    :param routine_layers: (list of dict) layers of the routine
    :return: (int) ID of the newly created routine
    :errors: AssertionError, DatabaseError, PermissionError, ValueError
    '''
    assignable_users = db_users.get_preferred_username_id_and_permissions(conn, timestamp, organization_id)
    user_pref_id_map = dict()
    for pref_name in assignable_users:
        user_pref_id_map[pref_name] = assignable_users[pref_name][0]

    component_validator.validate_routine_data(timestamp, organization_id, routine_name, routine_timezone,
                                              routine_layers, assignable_users)
    layers_json = helpers.generate_routine_layers_json(routine_layers, user_pref_id_map, with_pg_format_array=True,
                                                       timezone=routine_timezone)

    query = '''
            select create_routine(
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (organization_id, key_manager.generate_reference_key(), timestamp, constants.end_timestamp,
                    routine_name, routine_timezone, layers_json, True,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.DatabaseError:
        raise


def edit_routine(conn, timestamp, user_id, organization_id, routine_ref_id, routine_name, routine_timezone,
                 routine_layers, check_adv_perm=False, has_comp_perm=False, has_team_perm=False):
    '''
    Edits an existing routine.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made on
    :param user_id: user_id of the user making the request
    :param organization_id: ID of the organization
    :param routine_ref_id: reference ID of the routine being edited
    :param routine_name: name of the routine
    :param routine_timezone: timezone of the routine
    :param routine_layers: (list of dict) layers of the routine
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :errors: AssertionError, DatabaseError, PermissionError, ValueError
    '''
    unmasked_routine_ref_id = key_manager.unmask_reference_key(routine_ref_id)
    assert isinstance(user_id, int)

    assignable_users = db_users.get_preferred_username_id_and_permissions(conn, timestamp, organization_id)
    user_pref_id_map = dict()
    for pref_name in assignable_users:
        user_pref_id_map[pref_name] = assignable_users[pref_name][0]

    component_validator.validate_routine_data(timestamp, organization_id, routine_name, routine_timezone,
                                              routine_layers, assignable_users)
    layers_json = helpers.generate_routine_layers_json(routine_layers, user_pref_id_map, with_pg_format_array=True,
                                                       timezone=routine_timezone)

    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select edit_routine(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.routine_component_type_id, adv_perm_type,
                    unmasked_routine_ref_id, organization_id, user_id, timestamp, constants.end_timestamp,
                    routine_name, routine_timezone, layers_json,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        elif e.pgcode == errorcodes.RESTRICT_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_routine(conn, timestamp, user_id, organization_id, routine_ref_id, check_adv_perm=False,
                   has_comp_perm=False, has_team_perm=False):
    '''
    Delete a routine.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param user_id: user_id of the user deleting the routine
    :param organization_id: ID of the organization
    :param routine_ref_id: reference ID of the routine being deleted
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :errors: AssertionError, DatabaseError, PermissionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert isinstance(organization_id, int)

    routine_ref_id = key_manager.unmask_reference_key(routine_ref_id)
    routine_id = get_routine_ids_from_ref_id(conn, timestamp, [routine_ref_id], organization_id)[routine_ref_id]

    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select delete_routine(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.routine_component_type_id, adv_perm_type,
                    organization_id, routine_id, timestamp, user_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.RESTRICT_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def add_routine_layer(conn, timestamp, user_id, organization_id, routine_ref_id, layer, check_adv_perm=False,
                      has_comp_perm=False, has_team_perm=False):
    '''
    Add a layer to an existing routine.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: user_id of the user making the request
    :param organization_id: ID of the organization
    :param routine_ref_id: (int) ID of the routine
    :param layer: (dict) of layer data
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    unmasked_ref_id = key_manager.unmask_reference_key(routine_ref_id)

    component_validator.validate_routine_layer_data(layer)
    assignable_users = db_users.get_preferred_username_id_and_permissions(conn, timestamp, organization_id)
    user_pref_id_map = dict()
    for pref_name in assignable_users:
        user_pref_id_map[pref_name] = assignable_users[pref_name][0]
    rou_tz = get_routine_timezone(conn, timestamp, unmasked_ref_id)

    component_validator.validate_routine_rotations_data(layer[var_names.rotations], assignable_users)
    layer = json.loads(helpers.generate_routine_layers_json([layer], user_pref_id_map, timezone=rou_tz))[0]

    adv_perm_type = permissions.COMPONENT_ADVANCED_RESPOND_PERMISSION

    query = '''
            select add_routine_layer(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s,
                %s, %s
            )
            '''
    query_params = (
        check_adv_perm, has_comp_perm, has_team_perm, configs.routine_component_type_id, adv_perm_type,
        organization_id, unmasked_ref_id, timestamp, constants.end_timestamp, layer[var_names.valid_start],
        layer[var_names.valid_end], layer[var_names.layer_name], layer[var_names.rotation_period],
        layer[var_names.rotation_frequency], layer[var_names.rotation_start], layer[var_names.shift_length],
        layer[var_names.skip_days], layer[var_names.is_exception],
        json.dumps(layer[var_names.rotations]), user_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        elif e.pgcode == errorcodes.RESTRICT_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def get_routines_list(conn, timestamp, user_id, organization_id, user_teams_only=False,
                      row_limit=None, row_offset=None, check_adv_perm=False):
    '''
    Get the list of routines of an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made on
    :param user_id: user_id of the user making the request
    :param organization_id: organization ID
    :param user_teams_only: True if only policies that the user's teams own are wanted
    :param row_limit: the number of entries to fetch
    :param row_offset: the number of entries to skip from the top
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (list of list) -> [ [reference id, routine name, routine timezone, [associated pol 1, associate..]], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id,
                    'user_id': user_id, 'comp_type_id': configs.routine_component_type_id}

    conditions = []
    if check_adv_perm and not user_teams_only:
        conditions.append(''' routineid not in (select component_id from components_user_cannot_view(
                                    %(timestamp)s, %(org_id)s, %(user_id)s, %(comp_type_id)s::smallint))
                          ''')

    if user_teams_only:
        conditions.append(''' routineid in (select component_id from user_team_components(
                                    %(timestamp)s, %(org_id)s, %(user_id)s, %(comp_type_id)s::smallint))
                          ''')

    limit_cond = ''
    if row_limit is not None:
        assert isinstance(row_limit, int)
        limit_cond += ' limit {0} '.format(str(row_limit))
    if row_offset is not None:
        assert isinstance(row_offset, int)
        limit_cond += ' offset {0} '.format(str(row_offset))

    query = '''
            with t1 as(
                select routineid, routine_ref_id, routine_name, routine_timezone
                from routines
                where organization_id = %(org_id)s
                    and is_group_routine = true
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    {0}
                {1}
            )
            , t2 as(
                select pol_lev.routineid, json_agg(json_build_object(
                    'policy_ref_id', pol.policy_ref_id,
                    'policy_name', pol.policy_name
                )) as associated_policies
                from policy_levels as pol_lev
                join policies as pol using(policyid)
                where pol_lev.start_timestamp <= %(timestamp)s
                    and pol_lev.end_timestamp > %(timestamp)s
                    and pol.start_timestamp <= %(timestamp)s
                    and pol.end_timestamp > %(timestamp)s
                    and pol_lev.routineid in (select routineid from t1)
                group by pol_lev.routineid
            )
            select t1.routine_ref_id, t1.routine_name, t1.routine_timezone, t2.associated_policies
            from t1 left join t2 using(routineid)
            order by t1.routine_name;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '',
                       limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for routine_ref_id, routine_name, routine_timezone, associated_policies in result:

            fmt_associations = None
            if associated_policies is not None:
                parsed_policies = []
                fmt_associations = []
                for item in associated_policies:
                    if item[var_names.policy_ref_id] not in parsed_policies:
                        parsed_policies.append(item[var_names.policy_ref_id])
                        fmt_associations.append(
                            [item[var_names.policy_name],
                             key_manager.conceal_reference_key(uuid.UUID(item[var_names.policy_ref_id]))]
                        )

            data.append({
                var_names.routine_ref_id: key_manager.conceal_reference_key(routine_ref_id),
                var_names.routine_name: routine_name,
                var_names.timezone: routine_timezone,
                var_names.associated_policies: fmt_associations
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_routines(conn, timestamp, organization_id, routine_ref_id=None, routine_id=None,
                 valid_only=False, with_user_adv_perm=None, for_display=False):
    '''
    Get details of routine(s).
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: (int) organization id
    :param routine_ref_id: (str) reference id
    :param routine_id: (int or list) routine id
    :param valid_only: (boolean) specifies if only valid routines will be retrieved or not
    :param with_user_adv_perm: if the user_id is provided, then it will check for the user's advanced permissions
    :param for_display: True if values should be returned for display purposes
    :return: (dict of Routine objects) of details mapped on routine id -> {routine id: Routine object, ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}

    det_conditions = []
    valid_time_str = ''

    if routine_ref_id is not None:
        if not isinstance(routine_ref_id, list):
            routine_ref_id = [routine_ref_id]
        unmasked_ref_ids = [key_manager.unmask_reference_key(item) for item in routine_ref_id]
        det_conditions.append(" routine_ref_id = any(%(ref_id)s) ")
        query_params['ref_id'] = unmasked_ref_ids

    if routine_id is not None:
        det_conditions.append(" routineid = any(%(rid)s) ")
        query_params['rid'] = helpers.get_int_list(routine_id)

    if with_user_adv_perm is not None:
        assert isinstance(with_user_adv_perm, int)
        det_conditions.append('''
            routineid not in (select component_id from components_user_cannot_view(
                                %(timestamp)s, %(org_id)s, %(user_id)s, %(comp_type_id)s::smallint))
        ''')
        query_params['user_id'] = with_user_adv_perm
        query_params['comp_type_id'] = configs.routine_component_type_id

    if valid_only:
        valid_time_str = " and valid_start <= %(timestamp)s and valid_end > %(timestamp)s "

    query = '''
            with t1 as(
                select routineid, routine_ref_id, routine_name, routine_timezone
                from routines
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    {0}
            )
            , t2 as(
                select routineid, layer, jsonb_agg(jsonb_build_object(
                    'assignee_name', preferred_username,
                    'start_period', start_period,
                    'end_period', end_period,
                    'display_name', first_name || ' ' || last_name,
                    'assignee_policy_id', null
                ) order by start_period) as rotations
                from routine_rotations as rot
                join users on rot.assignee_user_id = users.user_id
                where rot.routineid in (select routineid from t1)
                    and rot.start_timestamp <= %(timestamp)s
                    and rot.end_timestamp > %(timestamp)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    {1}
                group by routineid, layer
            )
            , t3 as(
                select rl.routineid, jsonb_agg(jsonb_build_object(
                    'valid_start', rl.valid_start,
                    'valid_end', rl.valid_end,
                    'layer', rl.layer,
                    'layer_name', rl.layer_name,
                    'rotation_period', rotation_period,
                    'rotation_frequency', rotation_frequency,
                    'rotation_start', rotation_start,
                    'shift_length', shift_length,
                    'skip_days', skip_days,
                    'is_exception', is_exception,
                    'rotations', t2.rotations
                )) as layers
                from routine_layers as rl
                join t2 on rl.routineid = t2.routineid
                    and rl.layer = t2.layer
                where rl.routineid in (select routineid from t1)
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    {1}
                group by rl.routineid
            )
            , t4 as (
                select pl.routineid, jsonb_agg(jsonb_build_object(
                    'policy_ref_id', pol.policy_ref_id,
                    'display_name', pol.policy_name
                )) as associated_policies
                from policy_levels as pl
                join policies as pol using(policyid)
                where pl.routineid in (select routineid from t1)
                    and pl.start_timestamp <= %(timestamp)s
                    and pl.end_timestamp > %(timestamp)s
                    and pol.start_timestamp <= %(timestamp)s
                    and pol.end_timestamp > %(timestamp)s
                group by pl.routineid
            )
            select t1.*, t3.layers, t4.associated_policies
            from t1
            join t3 using (routineid)
            left join t4 using (routineid);
            '''.format('' if len(det_conditions) == 0 else ' and ' + ' and '.join(det_conditions),
                       valid_time_str)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, ref_id, routine_name, routine_timezone, layers, associated_policies in result:

            # conceal all reference keys
            ref_id = key_manager.conceal_reference_key(ref_id)
            if associated_policies is None:
                associated_policies = []
            else:
                associated_policies = [[item[var_names.display_name],
                                        key_manager.conceal_reference_key(uuid.UUID(item[var_names.policy_ref_id]))]
                                       for item in associated_policies]

            routine_layers = []
            for item in layers:
                routine_layers.append(RoutineLayer.create_layer(item, for_display))

            routine = Routine(ref_id, organization_id, routine_name, routine_timezone, routine_layers,
                              associated_policies=associated_policies, reference_id=ref_id)
            data[id_] = routine
        return data
    except psycopg2.DatabaseError:
        raise


def routine_name_is_unique(conn, timestamp, organization_id, routine_name, ignore_routine=None):
    '''
    Checks if a routine name is unique or not.
    :param conn: db connection
    :param timestamp: timestamp when the query is being made
    :param organization_id: organization id of the organization to check in
    :param routine_name: routine name to check
    :param ignore_routine: reference ID of the routine to ignore
            (to avoid double checking uniqueness for the same routine id)
    :return: (boolean) -> True if it is unique; False otherwise
    :errors: AssertionError, DatabaseError, RuntimeError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_not_sql_injection(routine_name)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'routine_name': routine_name}
    ignore_cond = ''
    if ignore_routine is not None:
        ignore_cond = " and routine_ref_id != %(rid)s "
        query_params['rid'] = key_manager.unmask_reference_key(ignore_routine)

    query = '''
            select routine_name from routines
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and routine_name = %(routine_name)s
                {0};
            '''.format(ignore_cond)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return True
        elif len(result) == 1:
            return False
        else:
            raise SystemError(errors.err_internal_multiple_entries_found + ' Routine - ' + routine_name)
    except psycopg2.DatabaseError:
        raise


def get_routine_ids_from_ref_id(conn, timestamp, routine_ref_ids, organization_id=None):
    '''
    Get routine IDs from their reference IDs.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param routine_ref_ids: (list) of unmasked routine reference IDs
    :param organization_id: (optional) ID of the organization to filter by
    :return: (dict) of reference IDs mapped to routine IDs
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(routine_ref_ids, list) and len(routine_ref_ids) > 0

    query_params = {'timestamp': timestamp, 'ref_id': routine_ref_ids}
    org_cond = ''
    if organization_id is not None:
        assert isinstance(organization_id, int)
        org_cond = " and organization_id = %(org_id)s "
        query_params['org_id'] = organization_id

    query = '''
            select routine_ref_id, routineid from routines
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and routine_ref_id = any(%(ref_id)s)
                {0};
            '''.format(org_cond)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for ref_id, rou_id in result:
            data[ref_id] = rou_id
        return data
    except psycopg2.DatabaseError:
        raise


def get_routine_associated_policy_ids(conn, timestamp, routine_ref_id):
    '''
    Get IDs of the policies that a routine is associated to.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param routine_ref_id: reference ID of the routine
    :return: (list) of policy IDs
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_ref_id = key_manager.unmask_reference_key(routine_ref_id)

    query = '''
            select policyid from policy_levels
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and routineid in (
                    select routineid from routines
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and routine_ref_id = %(rou_ref_id)s
                );
            '''
    query_params = {'timestamp': timestamp, 'rou_ref_id': unmasked_ref_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_basic_routines_list(conn, timestamp, organization_id, group_only=False):
    '''
    Get the basic routines list.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param group_only: (boolean) True if only the group routines are required; False otherwise
    :return: (list of list) -> [ [routine name, routine ref id], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    cond = ''
    if group_only:
        cond = ' and is_group_routine = true '

    query = '''
            select routine_name, routine_ref_id
            from routines
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                {0}
            order by routine_name;
            '''.format(cond)
    query_params = (timestamp, timestamp, organization_id,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for name_, key_ in result:
            data.append([name_, key_manager.conceal_reference_key(key_)])
        return data
    except psycopg2.DatabaseError:
        raise


def get_historical_routines(conn, start_time, end_time, organization_id, routine_id):
    '''
    Get the historical data of a routine. It will be used to create the routine calendar.
    :param conn: db connection
    :param start_time: (datetime.date or datetime.datetime) date/datetime to start getting data from
    :param end_time: (datetime.date or datetime.datetime) date/datetime to get data till
    :param organization_id: ID of the organization the data should be retrieved for
    :param routine_id: ID of the user to get the data for
    :return: (dict of dict) of policy data (different format from standard policy)
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(organization_id, int)
    assert isinstance(routine_id, int)
    if isinstance(start_time, datetime.date):
        start_time = datetime.datetime.combine(start_time, datetime.time(0, 0))
    else:
        assert isinstance(start_time, datetime.datetime)
    if isinstance(end_time, datetime.date):
        end_time = datetime.datetime.combine(end_time, datetime.time(23, 59, 59))
    else:
        assert isinstance(end_time, datetime.datetime)

    query_params = {'org_id': organization_id, 'rou_id': routine_id, 'start_time': start_time, 'end_time': end_time}

    query = '''
            with t1 as (
                select routineid, layer, rot.start_timestamp, rot.end_timestamp, valid_start, valid_end,
                jsonb_agg(jsonb_build_object(
                    'assignee_name', preferred_username,
                    'start_period', start_period,
                    'end_period', end_period,
                    'display_name', first_name || ' ' || last_name,
                    'assignee_policy_id', users.policyid
                ) order by start_period) as rotations
                from routine_rotations as rot
                join users
                    on users.user_id = rot.assignee_user_id
                        and users.start_timestamp <= rot.start_timestamp
                        and users.end_timestamp > rot.start_timestamp
                where rot.routineid = %(rou_id)s
                    and rot.start_timestamp <= %(end_time)s
                    and rot.end_timestamp > %(start_time)s
                    and rot.valid_start <= %(end_time)s
                    and rot.valid_end > %(start_time)s
                group by routineid, rot.start_timestamp, rot.end_timestamp, layer, valid_start, valid_end
            )
            , t2 as (
                select rl.routineid, rl.start_timestamp, rl.end_timestamp, jsonb_build_object(
                    'start_timestamp', rl.start_timestamp,
                    'end_timestamp', rl.end_timestamp,
                    'valid_start', rl.valid_start,
                    'valid_end', rl.valid_end,
                    'layer', rl.layer,
                    'rotation_period', rotation_period,
                    'rotation_frequency', rotation_frequency,
                    'rotation_start', rotation_start,
                    'shift_length', shift_length,
                    'skip_days', skip_days,
                    'is_exception', is_exception,
                    'rotations', t1.rotations
                ) as layers
                from routine_layers as rl
                join t1
                    on rl.routineid = t1.routineid
                        and rl.layer = t1.layer
                        and rl.start_timestamp <= t1.start_timestamp
                        and rl.end_timestamp > t1.start_timestamp
                        and rl.valid_start <= t1.valid_start
                        and rl.valid_end > t1.valid_start
                where rl.routineid = %(rou_id)s
            )
            select r.routineid, r.routine_ref_id, r.routine_name, r.routine_timezone,
                r.start_timestamp, r.end_timestamp, json_agg(t2.layers) as lyrs
            from routines as r
            join t2
                on t2.routineid = r.routineid
                    and (
                        (t2.start_timestamp <= r.start_timestamp and t2.end_timestamp >= r.end_timestamp)
                        or
                        (t2.start_timestamp >= r.start_timestamp and t2.end_timestamp <= r.end_timestamp)
                    )
            where is_group_routine = true
            group by r.routineid, r.routine_ref_id, r.routine_name, r.routine_timezone,
                r.start_timestamp, r.end_timestamp
            order by r.routineid, r.start_timestamp, r.routine_name;
            '''
    try:
        result = conn.fetch(query, query_params)
        data = []
        for rou_id, rou_ref, rou_name, rou_tz, rou_start, rou_end, layers in result:

            layer_objects = []
            for item in layers:
                layer_objects.append(RoutineLayer.create_layer(item))
            rou = Routine(rou_id, organization_id, rou_name, rou_tz, layer_objects, reference_id=rou_ref)
            data.append({
                var_names.start_timestamp: rou_start,
                var_names.end_timestamp: rou_end,
                var_names.routines: rou
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_routine_timezone(conn, timestamp, routine_ref_id):
    '''
    Get the timezone of a routine.
    :param conn: database connection
    :param timestamp: timestamp when this request is being made
    :param routine_ref_id: unmasked routine ref id
    :return: (str) routine timezone
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select routine_timezone from routines
            where start_timestamp <= %s
                and end_timestamp > %s
                and routine_ref_id = %s;
            '''
    query_params = (timestamp, timestamp, routine_ref_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) > 0:
            return result[0][0]
        return None
    except psycopg2.DatabaseError:
        raise