# By: Riasat Ullah
# This file contains database queries for ITSM groups.

from psycopg2 import errorcodes
from utils import constants, errors, key_manager, permissions, var_names
from validations import itsm_validator
import configuration as configs
import datetime
import psycopg2


def create_group(conn, timestamp, organization_id, group_name, description, group_type,
                 sla_response=None, sla_resolution=None, internal_id=None, people_ref_ids=None):
    '''
    Create an external group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group is associated with
    :param group_name: name of the group
    :param description: description of what the group is for
    :param group_type: type of the group
    :param sla_response: (int) minutes by which the first response is expected as per the SLA with the group
    :param sla_resolution: (int) minutes by which the incident should be resolved as per the SLA with the group
    :param internal_id: (str) ID of the group on the organization's internal system
    :param people_ref_ids: (list) of (concealed) reference IDs of the people associated with the group
    :return: (concealed) group ref ID
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    itsm_validator.validate_group_data(group_name, description, group_type, sla_response, sla_resolution, internal_id)

    new_group_ref_id = key_manager.generate_reference_key()
    unmasked_ppl_ref_ids = None
    if people_ref_ids is not None:
        unmasked_ppl_ref_ids = [key_manager.unmask_reference_key(item) for item in people_ref_ids]

    query = '''
            select create_external_group(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp_str, organization_id, new_group_ref_id,
                    group_name, description, group_type, sla_response,
                    sla_resolution, internal_id, None, unmasked_ppl_ref_ids,)
    try:
        conn.execute(query, query_params)
        return key_manager.conceal_reference_key(new_group_ref_id)
    except psycopg2.DatabaseError:
        raise


def edit_group(conn, timestamp, organization_id, user_id, group_ref_id, group_name, description, group_type,
               sla_response=None, sla_resolution=None, internal_id=None, check_adv_perm=False, has_comp_perm=False,
               has_team_perm=False):
    '''
    Edit an external group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group is associated with
    :param user_id: ID of the user trying to edit the group
    :param group_ref_id: (concealed) reference ID of the group
    :param group_name: name of the group
    :param description: description of what the group is for
    :param group_type: type of the group
    :param sla_response: (int) minutes by which the first response is expected as per the SLA with the group
    :param sla_resolution: (int) minutes by which the incident should be resolved as per the SLA with the group
    :param internal_id: (str) ID of the group on the organization's internal system
    :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
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    itsm_validator.validate_group_data(group_name, description, group_type, sla_response, sla_resolution, internal_id)

    unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION
    query = '''
            select edit_external_group(
                %s, %s, %s, %s::smallint, %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.group_component_type_id, adv_perm_type,
                    user_id, timestamp, constants.end_timestamp_str, organization_id,
                    unmasked_group_ref_id, group_name, description, group_type,
                    sla_response, sla_resolution, internal_id, None,)
    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_group_people(conn, timestamp, organization_id, user_id, group_ref_id, people_ref_ids,
                     check_adv_perm=False, has_comp_perm=False, has_team_perm=False):
    '''
    Add people to a group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group belongs to
    :param user_id: ID of the user who is trying to update the people in the group
    :param group_ref_id: (concealed) reference ID of the group
    :param people_ref_ids: (list) of (concealed) reference ID of the people to add/delete
    :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, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(people_ref_ids, list) and len(people_ref_ids) > 0

    unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
    unmasked_ppl_ref_ids = [key_manager.unmask_reference_key(item) for item in people_ref_ids]
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select add_external_group_people(
                %s, %s, %s, %s::smallint,
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.group_component_type_id,
                    adv_perm_type, user_id, timestamp, constants.end_timestamp,
                    organization_id, unmasked_group_ref_id, unmasked_ppl_ref_ids,)
    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 delete_group_people(conn, timestamp, organization_id, user_id, group_ref_id, people_ref_ids,
                        check_adv_perm=False, has_comp_perm=False, has_team_perm=False):
    '''
    Delete people from a group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group belongs to
    :param user_id: ID of the user who is trying to update the people in the group
    :param group_ref_id: (concealed) reference ID of the group
    :param people_ref_ids: (list) of (concealed) reference ID of the people to add/delete
    :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, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(people_ref_ids, list) and len(people_ref_ids) > 0

    unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
    unmasked_ppl_ref_ids = [key_manager.unmask_reference_key(item) for item in people_ref_ids]
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select delete_external_group_people(
                %s, %s, %s, %s::smallint,
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.group_component_type_id,
                    adv_perm_type, user_id, timestamp, constants.end_timestamp,
                    organization_id, unmasked_group_ref_id, unmasked_ppl_ref_ids,)
    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 delete_group(conn, timestamp, organization_id, user_id, group_ref_id, to_delete_people,
                 check_adv_perm=False, has_comp_perm=False, has_team_perm=False):
    '''
    Delete a group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group belongs to
    :param user_id: ID of the user who is trying to update the people in the group
    :param group_ref_id: (concealed) reference ID of the group to delete
    :param to_delete_people: (boolean) True if the people associated with the group should be deleted; False otherwise
    :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, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(to_delete_people, bool)
    unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select delete_external_group(
                %s, %s, %s, %s::smallint,
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.group_component_type_id,
                    adv_perm_type, user_id, timestamp, constants.end_timestamp,
                    organization_id, unmasked_group_ref_id, to_delete_people,)
    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 list_groups(conn, timestamp, organization_id, user_id, check_adv_perm):
    '''
    List the groups of an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group belongs to
    :param user_id: ID of the user who is trying to update the people in the group
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :return: (list of dict) of group details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'usr_id': user_id,
                    'comp_type_id': configs.group_component_type_id}
    conditions = []

    if check_adv_perm:
        conditions.append('''
            group_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint
                )
            )
        ''')

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


def get_group_details(conn, timestamp, organization_id, user_id, group_ref_id, check_adv_perm):
    '''
    Get the details of a group.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the group belongs to
    :param user_id: ID of the user who is trying to update the people in the group
    :param group_ref_id: (concealed) reference ID of the group whose details need to be retrieved
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :return: (dict) of group details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'usr_id': user_id,
                    'comp_type_id': configs.group_component_type_id, 'ref_id': unmasked_group_ref_id}
    conditions = []

    if check_adv_perm:
        conditions.append('''
            group_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint
                )
            )
        ''')

    query = '''
            select group_ref_id, group_name, group_description, group_type
            from external_groups
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and group_ref_id = %(ref_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for ref_, name_, desc_, type_ in result:
            data = {
                var_names.group_ref_id: key_manager.conceal_reference_key(ref_),
                var_names.group_name: name_,
                var_names.description: desc_,
                var_names.group_type: type_
            }
        return data
    except psycopg2.DatabaseError:
        raise


def get_basic_groups_list(conn, timestamp, organization_id):
    '''
    Get the basic groups list.
    :param conn: db connection
    :param timestamp: timestamp this request is being made at
    :param organization_id: ID of the organization to fetch the groups for
    :return: (list of list) -> [ [group name, group ref id], ... ]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select group_name, group_ref_id
            from external_groups
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
            order by group_name;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for grp_name_, grp_ref_ in result:
            data.append([grp_name_, key_manager.conceal_reference_key(grp_ref_)])
        return data
    except psycopg2.DatabaseError:
        raise