# By: Riasat Ullah
# This file contains functions that handle team related queries.

from exceptions.user_exceptions import NotUniqueValue
from psycopg2 import errorcodes
from utils import constants, errors, key_manager, permissions, roles, times, var_names
from validations import component_validator, string_validator
import configuration as configs
import datetime
import json
import psycopg2
import uuid


def create_team(conn, timestamp, organization_id, team_name, team_users, is_public=True, description=None):
    '''
    Creates a new team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the team is for
    :param team_name: name of the team
    :param team_users: (list of list) -> [ [user_id, team role], ...]
    :param is_public: (boolean) that states if the team should be publicly accessible within the organization
    :param description: brief description of what the team does
    :errors: AssertionError, LookupError, ValueError
    '''
    component_validator.validate_team_data(timestamp, organization_id, team_name, team_users, is_public, description)

    # create team member role permissions
    users_db_format = []
    for item in team_users:
        users_db_format.append({
            var_names.preferred_username: item[0],
            var_names.user_role: item[1],
            var_names.user_permissions: permissions.create_advanced_component_permission(item[1])
        })

    query = '''
            select create_team(
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''

    query_params = (timestamp, constants.end_timestamp, organization_id, key_manager.generate_reference_key(),
                    team_name, description, is_public, json.dumps(users_db_format),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_invalid_request)
    except psycopg2.DatabaseError:
        raise


def edit_team(conn, timestamp, organization_id, user_id, team_ref_id, team_name, is_public=True, description=None):
    '''
    Edits an existing team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the team is for
    :param user_id: user_id of the user making the changes
    :param team_ref_id: reference ID of the team being edited
    :param team_name: name of the team
    :param is_public: (boolean) that states if the team should be publicly accessible within the organization
    :param description: brief description of what the team does
    :errors: AssertionError, LookupError, ValueError
    '''

    unmasked_team_ref_id = key_manager.unmask_reference_key(team_ref_id)
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_standard_name(team_name)
    assert isinstance(is_public, bool)
    assert isinstance(user_id, int)

    query = '''
            select edit_team(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''

    query_params = (timestamp, constants.end_timestamp, organization_id, unmasked_team_ref_id, user_id,
                    team_name, description, is_public, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_team(conn, timestamp, organization_id, user_id, team_ref_id):
    '''
    Delete a team.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param organization_id: ID of the organization the team belongs to
    :param user_id: ID of the user deleting the team
    :param team_ref_id: reference ID of the team that should be deleted
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_team_ref_id = key_manager.unmask_reference_key(team_ref_id)

    query = " select delete_team(%s, %s, %s, %s, %s); "
    query_params = (timestamp, organization_id, user_id, unmasked_team_ref_id,
                    permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def list_teams(conn, timestamp, organization_id, team_ids=None, user_id=None):
    '''
    Get the list of teams that a user has access to view.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param team_ids: (list) of team IDs to filter by
    :param user_id: ID of the user to check team permissions for
    :return: (list of list) -> [ [team name, team ref ID], ... ]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if team_ids is not None:
        assert isinstance(team_ids, list)
        conditions.append(" team_id = any(%(tm_id)s) ")
        query_params['tm_id'] = team_ids
    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append('''
            (
                is_public = true
                or team_id in (
                    select team_id from team_members
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and user_id = %(usr_id)s
                )
            )
        ''')
        query_params['usr_id'] = user_id

    query = '''
            select team_ref_id, team_name from teams
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0}
            order by team_name;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for ref_, name_ in result:
            data.append([name_, key_manager.conceal_reference_key(ref_)])
        return data
    except psycopg2.DatabaseError:
        raise


def user_teams_list(conn, timestamp, organization_id, user_id):
    '''
    Get the list of teams that the user is a member of.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :return: (list of list) -> [ [team name, team ref ID], ... ]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    query = '''
            select team_ref_id, team_name from teams
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and team_id in (select team_id from get_user_team_ids(%(timestamp)s, %(usr_id)s))
            order by team_name;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'usr_id': user_id}

    try:
        result = conn.fetch(query, query_params)
        data = []
        for ref_, name_ in result:
            data.append([name_, key_manager.conceal_reference_key(ref_)])
        return data
    except psycopg2.DatabaseError:
        raise


def get_team_details(conn, timestamp, organization_id, team_ref_id, user_id=None):
    '''
    Get the details of a team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param team_ref_id: reference ID of the team
    :param user_id: ID of the user to check team permissions for
    :return: (dict) -> of team details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_team_ref_id = key_manager.unmask_reference_key(team_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'ref_id': unmasked_team_ref_id,
                    'serv_comp_id': configs.service_component_type_id,
                    'pol_comp_id': configs.policy_component_type_id,
                    'rou_comp_id': configs.routine_component_type_id,
                    'grp_comp_id': configs.group_component_type_id,
                    'tm_comp_id': configs.team_component_type_id}

    conditions = []
    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append('''
            (
                is_public = true
                or team_id in (
                    select team_id from team_members
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and user_id = %(usr_id)s
                )
            )
        ''')
        query_params['usr_id'] = user_id

    query = '''
            with t1 as (
                select team_id, team_ref_id, team_name, is_public
                from teams
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    and team_ref_id = %(ref_id)s
                    {0}
            )
            , t2 as (
                select team_id, json_agg(json_build_object(
                    'preferred_username', users.preferred_username,
                    'display_name', users.first_name || ' ' || users.last_name,
                    'email', users.email,
                    'job_title', org_mem.job_title,
                    'user_role', tmm.user_role,
                    'profile_picture', users.photo_url
                )) as tm_users
                from team_members as tmm
                join users using(user_id)
                join organization_members as org_mem on org_mem.member_id = users.user_id
                where tmm.start_timestamp <= %(timestamp)s
                    and tmm.end_timestamp > %(timestamp)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and org_mem.start_timestamp <= %(timestamp)s
                    and org_mem.end_timestamp > %(timestamp)s
                    and team_id in (select team_id from t1)
                group by team_id
            )
            , t3 as (
                select team_id, json_agg(json_build_object(
                    'name', service_name,
                    'key', service_ref_id
                )) as tm_services
                from team_components as tm_com
                join services as ss
                    on tm_com.component_type_id = %(serv_comp_id)s
                        and tm_com.component_id = ss.serviceid
                where tm_com.start_timestamp <= %(timestamp)s
                    and tm_com.end_timestamp > %(timestamp)s
                    and ss.start_timestamp <= %(timestamp)s
                    and ss.end_timestamp > %(timestamp)s
                    and tm_com.team_id in (select team_id from t1)
                group by team_id
            )
            , t4 as (
                select team_id, json_agg(json_build_object(
                    'name', policy_name,
                    'key', policy_ref_id
                )) as tm_policies
                from team_components as tm_com
                join policies as pol
                    on tm_com.component_type_id = %(pol_comp_id)s
                        and tm_com.component_id = pol.policyid
                        and pol.is_group_policy = true
                where tm_com.start_timestamp <= %(timestamp)s
                    and tm_com.end_timestamp > %(timestamp)s
                    and pol.start_timestamp <= %(timestamp)s
                    and pol.end_timestamp > %(timestamp)s
                    and tm_com.team_id in (select team_id from t1)
                group by team_id
            )
            , t5 as (
                select team_id, json_agg(json_build_object(
                    'name', routine_name,
                    'key', routine_ref_id
                )) as tm_routines
                from team_components as tm_com
                join routines as rou
                    on tm_com.component_type_id = %(rou_comp_id)s
                        and tm_com.component_id = rou.routineid
                        and rou.is_group_routine = true
                where tm_com.start_timestamp <= %(timestamp)s
                    and tm_com.end_timestamp > %(timestamp)s
                    and rou.start_timestamp <= %(timestamp)s
                    and rou.end_timestamp > %(timestamp)s
                    and tm_com.team_id in (select team_id from t1)
                group by team_id
            )
            , t6 as (
                select team_id, json_agg(json_build_object(
                    'name', group_name,
                    'key', group_ref_id
                )) as tm_groups
                from team_components as tm_com
                join external_groups as grp
                    on tm_com.component_type_id = %(grp_comp_id)s
                        and tm_com.component_id = grp.group_id
                where tm_com.start_timestamp <= %(timestamp)s
                    and tm_com.end_timestamp > %(timestamp)s
                    and grp.start_timestamp <= %(timestamp)s
                    and grp.end_timestamp > %(timestamp)s
                    and tm_com.team_id in (select team_id from t1)
                group by team_id
            )
            , t7 as (
                select component_id as team_id, array_agg(tag) as tm_tags
                from tag_associations
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    and component_type_id = %(tm_comp_id)s
                    and component_id in (select team_id from t1)
                group by team_id
            )
            select t1.team_ref_id, t1.team_name, t1.is_public, t2.tm_users,
                t3.tm_services, t4.tm_policies, t5.tm_routines, t6.tm_groups, t7.tm_tags
            from t1
            join t2 using (team_id)
            left join t3 using(team_id)
            left join t4 using(team_id)
            left join t5 using(team_id)
            left join t6 using(team_id)
            left join t7 using(team_id);
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for ref_id, tm_name, is_pub, tm_users, tm_serv, tm_pol, tm_rou, tm_grp, tm_tags in result:
            user_list = [[item[var_names.preferred_username], item[var_names.display_name], item[var_names.email],
                          item[var_names.job_title], item[var_names.user_role], item[var_names.profile_picture]]
                         for item in tm_users]

            services_list = [[item[var_names.name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.key]))]
                             for item in tm_serv] if tm_serv is not None else []
            policies_list = [[item[var_names.name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.key]))]
                             for item in tm_pol] if tm_pol is not None else []
            routines_list = [[item[var_names.name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.key]))]
                             for item in tm_rou] if tm_rou is not None else []
            groups_list = [[item[var_names.name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.key]))]
                           for item in tm_grp] if tm_grp is not None else []

            data = {
                var_names.team_ref_id: key_manager.conceal_reference_key(ref_id),
                var_names.team_name: tm_name,
                var_names.is_public: is_pub,
                var_names.users: user_list,
                var_names.services: services_list,
                var_names.policies: policies_list,
                var_names.routines: routines_list,
                var_names.groups: groups_list,
                var_names.tags: tm_tags
            }

        return data
    except psycopg2.DatabaseError:
        raise


def get_team_ids_from_ref_ids(conn, timestamp, organization_id, ref_ids=None, unmasked=True, as_dict=True):
    '''
    Get team id from reference ids.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the teams belong to
    :param ref_ids: (list) of reference IDs
    :param unmasked: True if the reference IDs have been unmasked; False otherwise
    :param as_dict: True if the result should be returned as a dict; otherwise a list of user IDs is returned only
    :return: (dict) -> { ref_id: id, .... }  |  (list) -> [id 1, id 2, ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if ref_ids is not None:
        assert isinstance(ref_ids, list)
        if not unmasked:
            ref_ids = [key_manager.unmask_reference_key(x) for x in ref_ids]

        conditions.append('team_ref_id = any(%(tm_ref_id)s)')
        query_params['tm_ref_id'] = ref_ids

    query = '''
            select team_id, team_ref_id from teams
            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 item in result:
                data.append(item[0])
            return data
    except psycopg2.DatabaseError:
        raise


def get_team_member_ids(conn, timestamp, team_ids):
    '''
    Get the user_ids of the users associated to a list of teams.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param team_ids: (list of int) of team IDs
    :return: (list) of user_ids
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(team_ids, list)

    query = '''
            select user_id from team_members
            where start_timestamp <= %s
                and end_timestamp > %s
                and team_id = any(%s);
            '''
    query_params = (timestamp, timestamp, team_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def add_team_member(conn, timestamp, organization_id, user_id, team_ref_id, member_pref, member_role):
    '''
    Add a new member to a team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :param team_ref_id: reference ID of the team
    :param member_pref: preferred username of the member to be added
    :param member_role: the role the member should have in the team
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_team_ref_id = key_manager.unmask_reference_key(team_ref_id)
    assert string_validator.is_valid_preferred_username(member_pref)
    assert member_role in roles.advanced_component_roles

    member_role_perm = permissions.create_advanced_component_permission(member_role)
    query = '''
            select add_team_member (
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s
            );
            '''
    query_params = (
        timestamp, constants.end_timestamp, organization_id, None, unmasked_team_ref_id,
        member_pref, member_role, member_role_perm, user_id, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,
    )
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def edit_team_member_role(conn, timestamp, organization_id, user_id, team_ref_id, member_pref, member_new_role):
    '''
    Add a new member to a team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :param team_ref_id: reference ID of the team
    :param member_pref: preferred username of the member to be added
    :param member_new_role: the role the member should have in the team
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_team_ref = key_manager.unmask_reference_key(team_ref_id)

    member_role_perm = permissions.create_advanced_component_permission(member_new_role)
    query = '''
            select edit_team_member_role (
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, unmasked_team_ref, member_pref,
                    member_new_role, member_role_perm, user_id, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def delete_team_member(conn, timestamp, organization_id, user_id, team_ref_id, member_pref):
    '''
    Delete an existing team member.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :param team_ref_id: reference ID of the team
    :param member_pref: preferred username of the member to delete
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_team_ref = key_manager.unmask_reference_key(team_ref_id)

    query = " select delete_team_member(%s, %s, %s, %s, %s, %s); "
    query_params = (timestamp, organization_id, unmasked_team_ref,
                    member_pref, user_id, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def add_team_component(conn, timestamp, organization_id, user_id, team_ref_id, component_type, component_ref_id):
    '''
    Add a new component to the team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :param team_ref_id: reference ID of the team
    :param component_type: (str) component type -> 'routines', 'policies' or 'services'
    :param component_ref_id: reference ID of the component
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    assert component_type in [var_names.routines, var_names.policies, var_names.services, var_names.groups]

    unmasked_team_ref = key_manager.unmask_reference_key(team_ref_id)
    unmasked_comp_ref = key_manager.unmask_reference_key(component_ref_id)

    comp_type_id = None
    if component_type == var_names.routines:
        comp_type_id = configs.routine_component_type_id
    elif component_type == var_names.policies:
        comp_type_id = configs.policy_component_type_id
    elif component_type == var_names.services:
        comp_type_id = configs.service_component_type_id
    elif component_type == var_names.groups:
        comp_type_id = configs.group_component_type_id

    query = '''
            select add_team_component(
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, unmasked_team_ref,
                    comp_type_id, unmasked_comp_ref, user_id, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        result = conn.fetch(query, query_params)
        owned_by_team = result[0][0]
        if owned_by_team is not None:
            raise NotUniqueValue(owned_by_team)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def delete_team_component(conn, timestamp, organization_id, user_id, team_ref_id, component_type, component_ref_id):
    '''
    Delete an existing component from the team.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param user_id: ID of the user
    :param team_ref_id: reference ID of the team
    :param component_type: (str) component type -> 'routines', 'policies' or 'services'
    :param component_ref_id: reference ID of the component
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    assert component_type in [var_names.routines, var_names.policies, var_names.services, var_names.groups]

    unmasked_team_ref = key_manager.unmask_reference_key(team_ref_id)
    unmasked_comp_ref = key_manager.unmask_reference_key(component_ref_id)

    comp_type_id = None
    if component_type == var_names.routines:
        comp_type_id = configs.routine_component_type_id
    elif component_type == var_names.policies:
        comp_type_id = configs.policy_component_type_id
    elif component_type == var_names.services:
        comp_type_id = configs.service_component_type_id
    elif component_type == var_names.groups:
        comp_type_id = configs.group_component_type_id

    query = '''
            select delete_team_component(
                %s, %s, %s, %s::smallint,
                %s, %s, %s
            );
            '''
    query_params = (timestamp, organization_id, unmasked_team_ref, comp_type_id,
                    unmasked_comp_ref, user_id, permissions.COMPONENT_ADVANCED_EDIT_PERMISSION,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def get_team_name_ref_keyed_on_id(conn, timestamp, organization_id, team_ids):
    '''
    Get the name and ref id of teams 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 team_ids: (list of int) team IDs
    :return: (dict of list) -> { pol ID: [pol name, pol ref], ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(team_ids, list)

    query = '''
            select team_id, team_name, team_ref_id
            from teams
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and team_id = any(%s);
            '''
    query_params = (timestamp, timestamp, organization_id, team_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


def get_organization_team_components_for_analytics(conn, start_date, end_date, organization_id):
    '''
    Get the team components and other basic details of all the teams of an organization.
    :param conn: db connection
    :param start_date: (datetime.date) date to start checking from
    :param end_date: (datetime.date) date to stop checking on
    :param organization_id: (int) ID of the organization
    :return: (dict of dict) -> {team ref -> {team name: , policies: , services: }, ...}
    '''
    assert isinstance(start_date, datetime.date)
    assert isinstance(end_date, datetime.date)
    assert isinstance(organization_id, int)

    query = '''
            select tm.team_ref_id, tm.team_name, json_agg(json_build_object(
                'component_type_id', tmc.component_type_id,
                'component_id', tmc.component_id,
                'valid_start', tmc.start_timestamp,
                'valid_end', tmc.end_timestamp
            )) as tm_comp
            from teams as tm
            join team_components as tmc using(team_id)
            where tm.organization_id = %(org_id)s
                and tm.start_timestamp::date <= %(end_date)s
                and tm.end_timestamp::date > %(start_date)s
                and tmc.start_timestamp::date <= %(end_date)s
                and tmc.end_timestamp::date > %(start_date)s
                and tmc.component_type_id = any(%(comp_ids)s)
            group by tm.team_ref_id, tm.team_name;
            '''
    query_params = {'org_id': organization_id, 'start_date': start_date, 'end_date': end_date,
                    'comp_ids': [configs.policy_component_type_id, configs.service_component_type_id]}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for tm_ref, tm_name, tm_comp in result:
            data[tm_ref] = {
                var_names.team_ref_id: key_manager.conceal_reference_key(tm_ref),
                var_names.team_name: tm_name,
                var_names.policies: [(item[var_names.component_id],
                                      times.get_timestamp_from_string(item[var_names.valid_start]),
                                      times.get_timestamp_from_string(item[var_names.valid_end])) for item in tm_comp
                                     if item[var_names.component_type_id] == configs.policy_component_type_id],
                var_names.services: [(item[var_names.component_id],
                                      times.get_timestamp_from_string(item[var_names.valid_start]),
                                      times.get_timestamp_from_string(item[var_names.valid_end])) for item in tm_comp
                                     if item[var_names.component_type_id] == configs.service_component_type_id]
            }
        return data
    except psycopg2.DatabaseError:
        raise


def list_team_service_ids_from_ref_ids(conn, timestamp, organization_id, team_ref_ids, unmasked=True):
    '''
    Get IDs of the services owned by a given list of teams.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: organization id the policies belong to
    :param team_ref_ids: (list) of team reference IDs
    :param unmasked: True if the given reference IDs are unmasked; False otherwise
    :return: (list) -> [service ID 1, service ID 2, ...]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(team_ref_ids, list)

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

    query = '''
            select component_id from team_components
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and component_type_id = %(serv_type_id)s
                and team_id in (
                    select team_id from teams
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and organization_id = %(org_id)s
                        and team_ref_id = any(%(tm_ref)s)
                );
            '''
    query_params = {'timestamp': timestamp, 'serv_type_id': configs.service_component_type_id,
                    'org_id': organization_id, 'tm_ref': team_ref_ids}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise