# By: Riasat Ullah
# This module works with policies in the database.

from dbqueries import db_routines
from objects.policy import Policy
from objects.policy_level import PolicyLevel
from objects.routine import Routine
from objects.routine_layer import RoutineLayer
from psycopg2 import errorcodes
from utils import constants, errors, helpers, key_manager, permissions, times, var_names
from validations import component_validator, string_validator
import configuration as configs
import datetime
import json
import psycopg2


def create_policy(conn, timestamp, organization_id, policy_name, policy_levels: list):
    '''
    Creates a policy. You can have user policies and group policies. User policies are created by the system.
    Group policies are created by users.
    :param conn: db connection
    :param timestamp: (datetime.datetime) timestamp when the group was created
    :param organization_id: (int) the organization id this policy is for
    :param policy_name: (string) the name of the group
    :param policy_levels: (list) of policy levels data
    :errors: AssertionError, DatabaseError, ValueError (from dependencies)
    '''
    org_routines = db_routines.get_basic_routines_list(conn, timestamp, organization_id)
    component_validator.validate_policy_data(timestamp, organization_id, policy_name, policy_levels, org_routines)
    levels_json = generate_policy_levels_json(conn, timestamp, organization_id, policy_levels)

    query = '''
            select create_policy(
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, key_manager.generate_reference_key(),
                    policy_name, True, levels_json,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def edit_policy(conn, timestamp, user_id, organization_id, policy_ref_id, policy_name, policy_levels: list,
                check_adv_perm=False, has_comp_perm=False, has_team_perm=False):
    '''
    Edit a policy. When a user changes its profile details or a group policies details change,
    this function should be used.
    :param conn: db connection
    :param timestamp: (datetime.datetime) timestamp when the group was created
    :param user_id: user_id of the user editing the details
    :param organization_id: (int) ID of the organization the policy belongs to
    :param policy_ref_id: (UUID) reference ID of the policy that needs to be changed
    :param policy_name: (string) the name of the group
    :param policy_levels: (list) of policy levels 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
    :return: (int) policy ID
    :errors: AssertionError, DatabaseError, LookupError, ValueError (from dependencies)
    '''
    unmasked_policy_ref_id = key_manager.unmask_reference_key(policy_ref_id)
    assert isinstance(user_id, int)
    org_routines = db_routines.get_basic_routines_list(conn, timestamp, organization_id)

    component_validator.validate_policy_data(timestamp, organization_id, policy_name, policy_levels, org_routines)
    levels_json = generate_policy_levels_json(conn, timestamp, organization_id, policy_levels)

    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select edit_policy(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.policy_component_type_id, adv_perm_type,
                    organization_id, unmasked_policy_ref_id, timestamp, constants.end_timestamp,
                    policy_name, levels_json, user_id,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    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_policy(conn, timestamp, user_id, organization_id, policy_id, check_adv_perm=False,
                  has_comp_perm=False, has_team_perm=False):
    '''
    Delete a policy. Edits all associated policies and tasks as needed.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: user_id of the user deleting the policy
    :param organization_id: ID of the organization the policy is for
    :param policy_id: ID of the policy that is 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, DependencyFound, PermissionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert isinstance(organization_id, int)
    assert isinstance(policy_id, int)

    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select delete_policy(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.policy_component_type_id, adv_perm_type,
                    organization_id, policy_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 get_policies(conn, timestamp, with_policyid=None, with_policy_ref_id=None, with_org_id=None, valid_only=False,
                 policy_type=None, for_display=False, with_user_id=None, check_adv_perm=False, user_teams_only=False):
    '''
    Gets all the policies (objects) according to the provided parameters.
    :param conn: db connection
    :param timestamp: timestamp to check on
    :param with_policyid: (int or list of int) policy id(s)
    :param with_policy_ref_id: (str) reference policy id
    :param with_org_id: (int) ID of the organization
    :param valid_only: (boolean) specifies if only valid group routines will be retrieved or not
    :param policy_type: (str) for all types, don't pass in a value. For user -> USER; for group -> GROUP
    :param for_display: (boolean) True if the Policy(s)/Routine(s) should be mapped on to their reference IDs
    :param with_user_id: the ID of the user to filter by
    :param check_adv_perm: True if advanced permissions should be checked
    :param user_teams_only: True if only the policies associated to the user's teams are wanted
    :return: (dictionary) of Policy objects
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query_params = {'timestamp': timestamp, 'pol_comp_type': configs.policy_component_type_id}
    det_conditions = []
    valid_time_str = ''

    if with_policyid is not None:
        det_conditions.append(" policyid = any(%(pid)s) ")
        query_params['pid'] = helpers.get_int_list(with_policyid)

    if with_policy_ref_id is not None:
        det_conditions.append(" policy_ref_id = %(pol_ref_id)s ")
        query_params['pol_ref_id'] = key_manager.unmask_reference_key(with_policy_ref_id)

    if with_org_id is not None:
        assert isinstance(with_org_id, int)
        det_conditions.append(" organization_id = %(org_id)s ")
        query_params['org_id'] = with_org_id

    if policy_type is not None:
        assert policy_type in [constants.user_policy, constants.group_policy]
        det_conditions.append(" is_group_policy = %(is_group)s ")
        query_params['is_group'] = (policy_type == constants.group_policy)

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

    if check_adv_perm:
        assert isinstance(with_user_id, int)
        assert with_org_id is not None
        det_conditions.append('''
            policyid not in (select component_id from components_user_cannot_view(
                                %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint))
        ''')
        query_params['usr_id'] = with_user_id
        query_params['comp_type_id'] = configs.policy_component_type_id

    if user_teams_only:
        assert isinstance(with_user_id, int)
        assert with_org_id is not None
        det_conditions.append('''
            policyid in (select component_id from user_team_components(
                            %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint))
        ''')
        query_params['usr_id'] = with_user_id
        query_params['comp_type_id'] = configs.policy_component_type_id

    query = '''
            with t1 as(
                select policyid, policy_ref_id, organization_id, policy_name, is_group_policy
                from policies
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    {0}
            )
            , t2 as (
                select policyid, level, minutes, routineid
                from policy_levels
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and policyid in (select policyid from t1)
            )
            , t3 as(
                select routineid, routine_ref_id, organization_id, routine_name, routine_timezone
                from routines
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and routineid in (select routineid from t2)
            )
            , t4 as(
                select routineid, layer, jsonb_agg(jsonb_build_object(
                    'assignee_name', assignee_user_id,
                    'preferred_username', users.preferred_username,
                    'start_period', start_period,
                    'end_period', end_period,
                    'display_name', first_name || ' ' || last_name,
                    'assignee_policy_id', users.policyid,
                    'policy_ref_id', pol.policy_ref_id
                )) as rotations
                from routine_rotations as rot
                join users on rot.assignee_user_id = users.user_id
                join policies as pol on users.policyid = pol.policyid
                where rot.routineid in (select routineid from t2)
                    and rot.start_timestamp <= %(timestamp)s
                    and rot.end_timestamp > %(timestamp)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and pol.start_timestamp <= %(timestamp)s
                    and pol.end_timestamp > %(timestamp)s
                    {1}
                group by routineid, layer
            )
            , t5 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', t4.rotations
                )) as layers
                from routine_layers as rl
                join t4 on rl.routineid = t4.routineid
                    and rl.layer = t4.layer
                where rl.routineid in (select routineid from t2)
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    {1}
                group by rl.routineid
            )
            , t6 as (
                select t3.routineid, jsonb_build_object(
                    'routine_id', t3.routineid,
                    'routine_ref_id', t3.routine_ref_id,
                    'organization_id', t3.organization_id,
                    'routine_name', t3.routine_name,
                    'timezone', t3.routine_timezone,
                    'routine_layers', t5.layers
                ) as routine
                from t3 join t5 using (routineid)
            )
            , t7 as (
                select t2.policyid, t2.level, t2.minutes, json_agg(t6.routine) as lev_routines
                from t2 join t6 using (routineid)
                group by t2.policyid, t2.level, t2.minutes
            )
            , t8 as (
                select t7.policyid, json_agg(json_build_object(
                    'assignee_level', t7.level,
                    'level_minutes', t7.minutes,
                    'routines', t7.lev_routines
                )) as levels
                from t7
                group by t7.policyid
            )
            , t9 as (
                select t1.policyid, json_agg(json_build_object(
                    'service_name', service_name,
                    'service_ref_id', service_ref_id
                )) as associated_services
                from t1
                join services as srv
                    on t1.policyid = srv.for_policyid
                where srv.start_timestamp <= %(timestamp)s
                    and srv.end_timestamp > %(timestamp)s
                group by t1.policyid
            )
            , t10 as (
                select t1.policyid, array_agg(tag) as policy_tags
                from t1
                left join tag_associations as taga
                    on taga.component_type_id = %(pol_comp_type)s
                        and taga.component_id = t1.policyid
                where taga.start_timestamp <= %(timestamp)s
                    and taga.end_timestamp > %(timestamp)s
                group by t1.policyid
            )
            select t1.*, t8.levels, t9.associated_services, t10.policy_tags
            from t1
            join t8 using (policyid)
            left join t9 using(policyid)
            left join t10 using(policyid);
            '''.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, org_id, policy_name, is_group_policy, levels, associated_serv, pol_tags in result:

            pol_id = id_ if not for_display else ref_id

            policy_levels = []
            for item in levels:
                policy_levels.append(PolicyLevel.create_level(item, for_display))

            type_of_policy = constants.group_policy if is_group_policy else constants.user_policy

            if associated_serv is None:
                associated_serv = []
            else:
                associated_serv = [[item[var_names.service_name], item[var_names.service_ref_id]]
                                   for item in associated_serv]

            policy = Policy(pol_id, org_id, policy_name, type_of_policy, policy_levels, reference_id=ref_id,
                            associated_services=associated_serv, tags=pol_tags)
            data[id_] = policy
        return data
    except psycopg2.DatabaseError:
        raise


def policy_name_is_unique(conn, timestamp, organization_id, policy_name, ignore_policy=None):
    '''
    Checks if a policy 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 policy_name: policy name to check
    :param ignore_policy: the policy ref id to ignore
    :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(policy_name)

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

    ignore_cond = ''
    if ignore_policy is not None:
        ignore_cond = ' and policy_ref_id != %(pid)s '
        query_params['pid'] = key_manager.unmask_reference_key(ignore_policy)

    query = '''
            select policy_name from policies
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and policy_name = %(policy_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)
    except psycopg2.DatabaseError:
        raise


def get_policy_roles_to_hand_off(conn, timestamp, interval, ending_buffer):
    '''
    Get 'upcoming' and 'ending' on call policy roles. This query is specifically for handoff notifications.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param interval: (int) minutes
    :param ending_buffer: (int) minutes to buffer for ending on-call roles
    :return: (list) -> [{policy ID: , assignee level: , routine ID: , start_period: , end_period: ,
                        user_policyid: , data_type: ... }, ...]
                  -> rotation_start is the time when the next potential on call role will be on
                  -> rotation_end is the time when the on call role will end
                  -> We say potential because in the db query we have not accounted for rotation and overrides.
                     The query simply gets everyone who is in the rotation list for the timeframe. The final
                     determination will be done in the OnCallNotifier.
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(interval, int) and interval > 0
    assert isinstance(ending_buffer, int)
    query = '''
            with t1 as (
                select distinct routine_timezone,
                    (date_trunc('minute', %(timestamp)s) at time zone 'UTC' at time zone routine_timezone)::timestamp
                    as reg_timestamp
                from routines
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
            )
            , t2 as (
                select distinct users.organization_id, users.user_id, users.policyid as user_policyid,
                    first_name || ' ' || last_name as display_name, email, user_language, min_buff::int, rr.routineid
                from users
                join handoff_notification_rules hnr using(user_id)
                join (
                    select notification_rule_id, rules, jsonb_object_keys(rules) as min_buff
                    from notification_rules
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                ) as notr using (notification_rule_id)
                join routine_rotations as rr on rr.assignee_user_id = users.user_id
                where users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and hnr.start_timestamp <= %(timestamp)s
                    and hnr.end_timestamp > %(timestamp)s
                    and rr.start_timestamp <= %(timestamp)s
                    and rr.end_timestamp > %(timestamp)s
            )
            , t3 as (
                select t2.*, t1.reg_timestamp, r.routine_name, r.routine_timezone, rl.layer,
                    rl.valid_start, rl.valid_end, rl.rotation_start, rl.shift_length,
                    (t1.reg_timestamp::date + rotation_start)::timestamp as today_start,
                    (t1.reg_timestamp::date + rotation_start)::timestamp + shift_length as today_end,
                    extract(epoch from (t1.reg_timestamp::date + rotation_start)::timestamp - t1.reg_timestamp)/60 as today_start_diff,
                    extract(epoch from ((t1.reg_timestamp::date + rotation_start)::timestamp + shift_length) - t1.reg_timestamp)/60 as today_end_diff,
                    (t1.reg_timestamp::date - interval '1 day' + rotation_start)::timestamp as yester_start,
                    (t1.reg_timestamp::date - interval '1 day' + rotation_start)::timestamp + shift_length as yester_end,
                    extract(epoch from (t1.reg_timestamp::date - interval '1 day' + rotation_start)::timestamp - t1.reg_timestamp)/60 as yester_start_diff,
                    extract(epoch from ((t1.reg_timestamp::date - interval '1 day' + rotation_start)::timestamp + shift_length) - t1.reg_timestamp)/60 as yester_end_diff,
                    (t1.reg_timestamp::date + interval '1 day' + rotation_start)::timestamp as tomor_start,
                    (t1.reg_timestamp::date + interval '1 day' + rotation_start)::timestamp + shift_length as tomor_end,
                    extract(epoch from (t1.reg_timestamp::date + interval '1 day' + rotation_start)::timestamp - t1.reg_timestamp)/60 as tomor_start_diff,
                    extract(epoch from ((t1.reg_timestamp::date + interval '1 day' + rotation_start)::timestamp + shift_length) - t1.reg_timestamp)/60 as tomor_end_diff,
                    pl.level, p.policy_name, p.policyid
                from t2
                join routines as r using(routineid)
                join routine_layers as rl using(routineid)
                join t1 using(routine_timezone)
                join policy_levels as pl using(routineid)
                join policies as p using(policyid)
                where r.is_group_routine = true
                    and r.start_timestamp <= %(timestamp)s
                    and r.end_timestamp > %(timestamp)s
                    and rl.start_timestamp <= %(timestamp)s
                    and rl.end_timestamp > %(timestamp)s
                    and pl.start_timestamp <= %(timestamp)s
                    and pl.end_timestamp > %(timestamp)s
                    and p.start_timestamp <= %(timestamp)s
                    and p.end_timestamp > %(timestamp)s
                    and p.is_group_policy = true
            )
            , t4 as (
                    select t3.*, case 
                        when today_start_diff >= min_buff and today_start_diff < min_buff + %(gap)s then today_start
                        when yester_start_diff >= min_buff and yester_start_diff < min_buff + %(gap)s then yester_start
    	                else tomor_start
                    end as hand_off_time, 'upcoming' as notf_type
                    from t3
                    where valid_start <= (t3.today_start at time zone routine_timezone at time zone 'UTC')::timestamp
                        and valid_end > (t3.today_start at time zone routine_timezone at time zone 'UTC')::timestamp
                        and (
        	            (today_start_diff >= min_buff and today_start_diff < min_buff + %(gap)s)
                            or
                            (yester_start_diff >= min_buff and yester_start_diff < min_buff + %(gap)s)
        	            or
        	            (tomor_start_diff >= min_buff and tomor_start_diff < min_buff + %(gap)s)
                        )

                    union

                    select t3_temp.*, case 
    	                when today_end_diff >= %(end_buff)s and today_end_diff < %(end_buff)s + %(gap)s then today_end
                        when yester_end_diff >= %(end_buff)s and yester_end_diff < %(end_buff)s + %(gap)s then yester_end
    	                else tomor_end
                    end as hand_off_time, 'ending' as notf_type
                    from (
                        select organization_id, user_id, user_policyid, display_name, email, user_language, max(min_buff),
                            routineid, reg_timestamp, routine_name, routine_timezone, layer, valid_start, valid_end,
                            rotation_start, shift_length, today_start, today_end, today_start_diff, today_end_diff,
                            yester_start, yester_end, yester_start_diff, yester_end_diff,
                            tomor_start, tomor_end, tomor_start_diff, tomor_end_diff,
                            level, policy_name, policyid
                        from t3
                        group by organization_id, user_id, user_policyid, display_name, email, user_language, routineid,
                            reg_timestamp, routine_name, routine_timezone, layer, valid_start, valid_end, rotation_start,
                            shift_length, today_start, today_end, today_start_diff, today_end_diff,
                            yester_start, yester_end, yester_start_diff, yester_end_diff,
                            tomor_start, tomor_end, tomor_start_diff, tomor_end_diff,
                            level, policy_name, policyid
                        ) as t3_temp
                    where valid_start <= %(timestamp)s
                        and valid_end > %(timestamp)s
                        and (
        	           (today_end_diff >= %(end_buff)s and today_end_diff < %(end_buff)s + %(gap)s)
                           or
                           (yester_end_diff >= %(end_buff)s and yester_end_diff < %(end_buff)s + %(gap)s)
        	           or
        	           (tomor_end_diff >= %(end_buff)s and tomor_end_diff < %(end_buff)s + %(gap)s)
                        )
            )
            , t5 as (
                select routineid, layer, jsonb_agg(jsonb_build_object(
                    'assignee_name', assignee_user_id,
                    'preferred_username', users.preferred_username,
                    'start_period', start_period,
                    'end_period', end_period,
                    'display_name', first_name || ' ' || last_name,
                    'assignee_policy_id', users.policyid,
                    'policy_ref_id', pol.policy_ref_id
                )) as rotations
                from routine_rotations as rot
                join users on rot.assignee_user_id = users.user_id
                join policies as pol on users.policyid = pol.policyid
                where rot.routineid in (select routineid from t4)
                    and rot.start_timestamp <= %(timestamp)s
                    and rot.end_timestamp > %(timestamp)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and pol.start_timestamp <= %(timestamp)s
                    and pol.end_timestamp > %(timestamp)s
                group by routineid, layer
            )
            , t6 as(
                select rl.routineid, jsonb_agg(jsonb_build_object(
                    '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', t5.rotations
                )) as layers
                from routine_layers as rl
                join t5
                    on rl.routineid = t5.routineid
                        and rl.layer = t5.layer
                where rl.routineid in (select routineid from t5)
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                group by rl.routineid
            )
            , t7 as (
                select aut.user_id, array_agg(push_token) as push_tokens
                from authentication_tokens as aut
                join (
                    select user_id, max(end_timestamp) as max_end from authentication_tokens
                    where platform_type = 'APP'
                        and end_timestamp >= %(timestamp)s - interval '120 days'
                        and user_id in (select user_id from t1)
                    group by user_id
                ) as foo
                    on aut.user_id = foo.user_id
                        and aut.end_timestamp >= foo.max_end
                where aut.platform_type = 'APP'
                group by aut.user_id
            )
            select distinct t4.organization_id, t4.policyid, policy_name, level, routineid, routine_name,
                routine_timezone, t6.layers, min_buff, notf_type,
                (hand_off_time at time zone routine_timezone at time zone 'UTC')::timestamp as hand_off_time,
                t4.user_id, t4.user_policyid, t4.display_name, t4.user_language, t4.email, t7.push_tokens
            from t4
            join t6 using(routineid)
            left join t7 using(user_id);
            '''
    query_params = {'timestamp': timestamp, 'gap': interval, 'end_buff': ending_buffer}
    try:
        result = conn.fetch(query, query_params)
        data = []

        for org_id, pol_id, pol_name, pol_lev, rou_id, rou_name, rou_tz, layers, min_buff, ntf_type, hoff_time,\
                usr_id, usr_pol_id, usr_disp_name, usr_lang, usr_email, usr_tokens in result:

            routine_layers = []
            for item in layers:
                routine_layers.append(RoutineLayer.create_layer(item))
            routine = Routine(rou_id, org_id, rou_name, rou_tz, routine_layers)

            data.append({
                var_names.organization_id: org_id,
                var_names.policy_id: pol_id,
                var_names.policy_name: pol_name,
                var_names.assignee_level: pol_lev,
                var_names.routines: routine,
                var_names.minutes_buffer: min_buff,
                var_names.data_type: ntf_type,
                var_names.handoff_timestamp: hoff_time,
                var_names.user_id: usr_id,
                var_names.user_policyid: usr_pol_id,
                var_names.display_name: usr_disp_name,
                var_names.language: usr_lang,
                var_names.email: usr_email,
                var_names.push_token: usr_tokens
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_historical_policies(conn, start_time, end_time, organization_id, user_id=None):
    '''
    Get the historical data of policies and on-call assignments. These will be used to create the on-call 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 user_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)
    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)

    conditions = []
    query_params = {'org_id': organization_id, 'start_time': start_time, 'end_time': end_time}
    if user_id is not None:
        conditions.append('''
            pl.routineid in (
                select routineid from routine_rotations
                where assignee_user_id = %(usr_id)s
                    and start_timestamp <= %(end_time)s
                    and end_timestamp > %(start_time)s
                    and valid_start <= %(end_time)s
                    and valid_end > %(start_time)s
            )
        ''')
        query_params['usr_id'] = user_id

    query = '''
            with t1 as (
                select p.policyid, p.policy_ref_id, p.organization_id, p.policy_name,
                    pl.level, pl.minutes, pl.start_timestamp, pl.end_timestamp, pl.routineid
                from policies as p
                join policy_levels as pl
                    on pl.policyid = p.policyid
                        and pl.start_timestamp <= p.start_timestamp
                        and pl.end_timestamp > p.start_timestamp
                where p.is_group_policy = true
                    and p.organization_id = %(org_id)s
                    and p.start_timestamp <= %(end_time)s
                    and p.end_timestamp > %(start_time)s
                    {0}
            )
            , t2 as (
                select t1.policyid, t1.policy_ref_id, t1.organization_id, p3.policy_name,
                    t1.level, t1.minutes, t1.start_timestamp, t1.end_timestamp, t1.routineid
                from t1
                join (
                    select policyid, policy_name from policies
                    join (
                        select policyid, max(end_timestamp) as max_end from policies group by policyid
                    ) as p2 using(policyid)
                    where end_timestamp = max_end
                        and policyid in (select policyid from t1)
                ) as p3 using(policyid)
            )
            , t3 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 in (select routineid from t2)
                    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
            )
            , t4 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,
                    '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', t3.rotations
                ) as layers
                from routine_layers as rl
                join t3
                    on rl.routineid = t3.routineid
                        and rl.layer = t3.layer
                        and rl.start_timestamp <= t3.start_timestamp
                        and rl.end_timestamp > t3.start_timestamp
                        and rl.valid_start <= t3.valid_start
                        and rl.valid_end > t3.valid_start
                where rl.routineid in (select routineid from t2)
            )
            , t5 as (
                select r.routineid, r.routine_ref_id, r.is_group_routine, r.start_timestamp, r.end_timestamp,
                json_build_object(
                    'routine_id', r.routineid,
                    'routine_ref_id', r.routine_ref_id,
                    'organization_id', r.organization_id,
                    'routine_name', r.routine_name,
                    'timezone', r.routine_timezone,
                    'routine_layers', json_agg(t4.layers)
                ) as rou
                from routines as r
                join t4
                    on t4.routineid = r.routineid
                        and (
                            (t4.start_timestamp <= r.start_timestamp and t4.end_timestamp >= r.end_timestamp)
                            or
                            (t4.start_timestamp >= r.start_timestamp and t4.end_timestamp <= r.end_timestamp)
                        )
                group by r.organization_id, r.routineid, r.routine_ref_id, r.routine_name, r.routine_timezone,
                    r.is_group_routine, r.start_timestamp, r.end_timestamp
            )
            select t2.organization_id, t2.policyid, t2.policy_ref_id, t2.policy_name, t2.level,
                t2.start_timestamp, t2.end_timestamp, t2.routineid, t5.routine_ref_id, t5.is_group_routine,
                json_agg(json_build_object(
                    'start_timestamp', t5.start_timestamp,
                    'end_timestamp', t5.end_timestamp,
                    'routines', t5.rou
                )) as possibly_valid_routines
            from t2
            join t5
                on t5.routineid = t2.routineid
                    and t5.start_timestamp <= t2.end_timestamp
                    and t5.end_timestamp > t2.start_timestamp
            group by t2.organization_id, t2.policyid, t2.policy_ref_id, t2.policy_name, t2.level,
                t2.start_timestamp, t2.end_timestamp, t2.routineid, t5.routine_ref_id, t5.is_group_routine
            order by t2.policyid, t2.start_timestamp, t2.level, t2.policy_name;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for org_id, pol_id, pol_ref, pol_name, pol_lvl, pol_start, pol_end, rou_id, rou_ref, is_grp, rous in result:

            formatted_rous = []
            for item in rous:
                formatted_rous.append({
                    var_names.start_timestamp: times.get_timestamp_from_string(item[var_names.start_timestamp]),
                    var_names.end_timestamp: times.get_timestamp_from_string(item[var_names.end_timestamp]),
                    var_names.routines: Routine.create_routine(item[var_names.routines])
                })

            key = (pol_id, pol_lvl)
            if key not in data:
                data[key] = []
            data[key].append({
                var_names.organization_id: org_id,
                var_names.policy_id: pol_id,
                var_names.policy_ref_id: key_manager.conceal_reference_key(pol_ref),
                var_names.policy_name: pol_name,
                var_names.assignee_level: pol_lvl,
                var_names.start_timestamp: pol_start,
                var_names.end_timestamp: pol_end,
                var_names.routine_id: rou_id,
                var_names.routine_ref_id: key_manager.conceal_reference_key(rou_ref),
                var_names.for_group: is_grp,
                var_names.routines: formatted_rous
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_policy_dispatch_info(conn, timestamp, policy_ids):
    '''
    Get all information needed to dispatch an alert to a user.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param policy_ids: list of policy ids to look for
    :return: (dict of dict) -> keyed on policy ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(policy_ids, list)
    query = '''
            with t1 as (
                select users.policyid, users.user_id, email, iso_country_code, phone,
                    users.organization_id, users.user_timezone, users.user_language, usp.permissions
                from users
                join user_permissions as usp using(user_id)
                where users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and usp.start_timestamp <= %(timestamp)s
                    and usp.end_timestamp > %(timestamp)s
                    and users.policyid = any(%(pid)s)
            )
            , t2 as (
                select user_id, json_agg(json_build_object(
                    'notification_rules', rules,
                    'urgency_level', urgency_levels
                )) as alerting_rules
                from alert_notification_rules as anr
                join notification_rules as nr using (notification_rule_id)
                where anr.start_timestamp <= %(timestamp)s
                    and anr.end_timestamp > %(timestamp)s
                    and nr.start_timestamp <= %(timestamp)s
                    and nr.end_timestamp > %(timestamp)s
                    and user_id in (select user_id from t1)
                group by user_id
            )
            , t3 as (
                select aut.user_id, array_agg(push_token) as push_tokens
                from authentication_tokens as aut
                join (
                    select user_id, max(end_timestamp) as max_end from authentication_tokens
                    where platform_type = 'APP'
                        and end_timestamp >= %(timestamp)s - interval '120 days'
                        and user_id in (select user_id from t1)
                    group by user_id
                ) as foo
                    on aut.user_id = foo.user_id
                        and aut.end_timestamp >= foo.max_end
                where aut.platform_type = 'APP'
                group by aut.user_id
            )
            select policyid, user_id, email, iso_country_code, phone, t3.push_tokens, organization_id,
                user_timezone, user_language, permissions, t2.alerting_rules
            from t1
            join t2 using(user_id)
            left join t3 using(user_id);
            '''
    query_params = {'timestamp': timestamp, 'pid': policy_ids}
    try:
        result = conn.fetch(query, query_params)
        data = dict()

        # Push tokens are put in a "set" to avoid duplication.
        for id_, user_id_, email_, iso_code_, phone_, tokens_, org_id_, tz_, lang_, perm_, alerting_rules in result:
            if id_ not in data:
                data[id_] = {
                    var_names.user_id: user_id_,
                    var_names.email: email_,
                    var_names.iso_country_code: iso_code_,
                    var_names.phone: phone_,
                    var_names.push_token: list(set(tokens_)) if tokens_ is not None else [],
                    var_names.organization_id: org_id_,
                    var_names.timezone: tz_,
                    var_names.language: lang_,
                    var_names.user_permissions: perm_,
                    var_names.notification_rules: [None for i in range(0, len(configs.allowed_urgency_levels))]
                }

            for item in alerting_rules:
                urgencies = item[var_names.urgency_level]
                details = item[var_names.notification_rules]

                for urg in urgencies:
                    data[id_][var_names.notification_rules][urg - 1] = details

        return data
    except psycopg2.DatabaseError:
        raise


def get_policy_ids_from_ref_ids(conn, timestamp, organization_id, policy_ref_ids, unmasked=True):
    '''
    Get the policy ids of policies given their reference ids.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: organization id the policies belong to
    :param policy_ref_ids: (list) of policy reference ids (concealed)
    :param unmasked: True if the reference IDs have been unmasked
    :return: (dict) -> { ref ID: policy ID, ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(policy_ref_ids, list)

    if not unmasked:
        policy_ref_ids = [key_manager.unmask_reference_key(x) for x in policy_ref_ids]

    query = '''
            select policy_ref_id, policyid from policies
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and policy_ref_id = any(%s);
            '''
    query_params = (timestamp, timestamp, organization_id, policy_ref_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for ref_, id_ in result:
            data[ref_] = id_
        return data
    except psycopg2.DatabaseError:
        raise


def list_policy_ids_from_ref_ids(conn, timestamp, organization_id, policy_ref_ids=None, unmasked=True, as_dict=False):
    '''
    Get the policy ids of policies given their reference ids as a list.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: organization id the policies belong to
    :param policy_ref_ids: (list) of policy reference ids
    :param unmasked: True if the given reference IDs are unmasked; False otherwise
    :param as_dict: True if the data should be returned as dict
    :return: (list) -> [policy ID 1, policy ID 2, ...]  |  (dict) -> {ref_id: pol_id, ...}
    '''
    assert isinstance(timestamp, datetime.datetime)

    conditions = []
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    if policy_ref_ids is not None:
        assert isinstance(policy_ref_ids, list)
        if not unmasked:
            policy_ref_ids = [key_manager.unmask_reference_key(x) for x in policy_ref_ids]
        conditions.append('policy_ref_id = any(%(pol_list)s)')
        query_params['pol_list'] = policy_ref_ids

    query = '''
            select policyid, policy_ref_id from policies
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        if as_dict:
            data = dict()
            for id_, ref_ in result:
                data[ref_] = id_
            return data
        else:
            data = []
            for id_, ref_ in result:
                data.append(id_)
            return data
    except psycopg2.DatabaseError:
        raise


def get_policy_open_org_instance_ids(conn, timestamp, policy_id):
    '''
    Gets the organization instance ids of the instances a given policy is associated to.
    This function should be used prior to deleting a policy to warn about its associations.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param policy_id: policy ID
    :return: (list) -> organization instance IDs
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(policy_id, int)
    query = '''
            select organization_instanceid from task_instances
            where is_open = true
                and instanceid in (
                    select instanceid from instance_assignments
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and for_policyid = %(pid)s
                );
            '''
    query_params = {'timestamp': timestamp, 'pid': policy_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_policy_task_titles(conn, timestamp, policy_id):
    '''
    Gets the task titles that a given policy is associated to. This function should be used
    prior to deleting a policy to warn about its associations.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param policy_id: policy ID
    :return: (list) -> titles
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(policy_id, int)
    query = '''
            select array_agg(title) from tasks
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and taskid in (
                    select taskid from task_additional_policies
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and policyid = %(pid)s
                );
            '''
    query_params = {'timestamp': timestamp, 'pid': policy_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0 or result[0][0] is None:
            return []
        else:
            return result[0][0]
    except psycopg2.DatabaseError:
        raise


def get_policy_service_names(conn, timestamp, policy_id):
    '''
    Gets the name of services that a given policy is associated to. This function should be used
    prior to deleting a policy to warn about its associations.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param policy_id: policy ID
    :return: (list) -> service names
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(policy_id, int)
    query = '''
            select array_agg(service_name) from services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and for_policyid = %(pid)s;
            '''
    query_params = {'timestamp': timestamp, 'pid': policy_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0 or result[0][0] is None:
            return []
        else:
            return result[0][0]
    except psycopg2.DatabaseError:
        raise


def generate_policy_levels_json(conn, timestamp, organization_id, levels):
    '''
    Maps routine reference IDs to routine IDs and then gets the json format of levels data needed
    for creating/editing policy layers using the create_policy/edit_policy function in the db.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization these policy levels are for
    :param levels: (list) of levels data
    :return: (json) of policy levels data
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    unmasked_routine_ref_ids = [key_manager.unmask_reference_key(z) for y in levels
                                for z in y[var_names.routines]]
    routine_id_mapping = db_routines.get_routine_ids_from_ref_id(conn, timestamp, unmasked_routine_ref_ids,
                                                                 organization_id)
    levels_struct = []
    for level_data in levels:
        for ref_id in level_data[var_names.routines]:
            ref_id = key_manager.unmask_reference_key(ref_id)
            assert ref_id in routine_id_mapping

            levels_struct.append({
                var_names.assignee_level: level_data[var_names.assignee_level],
                var_names.level_minutes: level_data[var_names.level_minutes],
                var_names.routine_id: routine_id_mapping[ref_id]
            })
    return json.dumps(levels_struct)


def get_basic_policies_list(conn, timestamp, organization_id, group_only=False, user_id=None):
    '''
    Get the basic policies 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 escalation policies are required;
        False if both escalation and user policies are wanted
    :param user_id: ID of the user to check team permissions for
    :return: (list of list) -> [ [policy name, policy ref id], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if group_only:
        conditions.append(' is_group_policy = true ')

    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append('''
            policyid not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint
                )
            )
        ''')
        query_params['usr_id'] = user_id
        query_params['comp_type_id'] = configs.policy_component_type_id

    query = '''
            select policy_name, policy_ref_id
            from policies
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0}
            order by policy_name;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    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 internalize_policy_ref_ids_in_routing_actions(conn, timestamp, organization_id, actions: dict):
    '''
    Convert policy ref IDs provided in conditional routing "actions" to policy ID.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param actions: (dict) of actions to take for the conditional routing
    :return: (dict) of actions with internalized policy IDs
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(actions, dict)

    if var_names.route_to in actions:
        unmasked_provided_ref = key_manager.unmask_reference_key(actions[var_names.route_to])
        pol_list = list_policy_ids_from_ref_ids(conn, timestamp, organization_id, [unmasked_provided_ref])
        if len(pol_list) == 1:
            actions[var_names.route_to] = pol_list[0]
        else:
            raise LookupError(errors.err_unknown_resource)

    return actions


def get_policy_name_ref_keyed_on_id(conn, timestamp, organization_id, policy_ids):
    '''
    Get the policy name and ref id of policies keyed on their ID.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param policy_ids: (list of int) policy IDs
    :return: (dict of list) -> { pol ID: [pol name, pol ref], ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(policy_ids, list)

    query = '''
            select policyid, policy_name, policy_ref_id
            from policies
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and policyid = any(%s);
            '''
    query_params = (timestamp, timestamp, organization_id, policy_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, name_, ref_ in result:
            data[id_] = [name_, key_manager.conceal_reference_key(ref_)]
        return data
    except psycopg2.DatabaseError:
        raise