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

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_person(conn, timestamp, organization_id, user_id, people_details,
                  check_adv_perm=False,
                  has_comp_perm=False, has_team_perm=False):
    '''
    Create external people. Multiple people can be created at the same time.
    :param conn: db connection
    :param timestamp: timestamp when this request has been made
    :param organization_id: ID of the organization
    :param user_id: ID of the user who is creating these people
    :param people_details: (list of dict) of person data with keys:
                          first_name, last_name, title (optional), email (optional),
                          iso_country_code (optional), phone (optional),
                          language (optional), group_ref_id (optional)
    :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: list of people_ref_ids
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(people_details, list) and len(people_details) > 0

    if len(people_details) == 0:
        return []

    query_params = []
    new_people_refs = []
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    for item in people_details:
        assert isinstance(item, dict)
        first_name = item[var_names.first_name]
        last_name = item[var_names.last_name]
        title = item.get(var_names.title)
        email = item.get(var_names.email)
        iso_code = item.get(var_names.iso_country_code)
        phone = item.get(var_names.phone)
        person_language = item.get(var_names.language)
        group_ref_id = item.get(var_names.group_ref_id)

        # Validate individual person data
        itsm_validator.validate_person_data(first_name, last_name, title, email,
                                            iso_code, phone, person_language)

        # Generate new reference ID
        new_person_ref_id = key_manager.generate_reference_key()
        new_people_refs.append(new_person_ref_id)

        # Unmask group ref ID similar to edit_person()
        if group_ref_id is not None:
            group_ref_id_value = key_manager.unmask_reference_key(group_ref_id)
        else:
            group_ref_id_value = None

        query_params.append((
            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, new_person_ref_id,
            first_name, last_name, title, group_ref_id_value, email,
            iso_code, phone, person_language
        ))

    query = '''
            select create_external_person(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s
            );
            '''

    try:
        conn.execute_batch(query, query_params)
        return new_people_refs
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.RESTRICT_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def edit_person(conn, timestamp, organization_id, user_id, people_ref_id, first_name, last_name, title=None, email=None,
                iso_code=None, phone=None, person_language=None, group_ref_id=None, check_adv_perm=False,
                has_comp_perm=False, has_team_perm=False):
    '''
    Edit an external person.
    :param conn: db connection
    :param timestamp: timestamp when this request has been made
    :param organization_id: ID of the organization
    :param user_id: ID of the user trying to edit the person
    :param people_ref_id: (concealed) reference ID of the person
    :param first_name: first name of the person
    :param last_name: last name of the person
    :param title: title of the person
    :param email: email address of the person
    :param iso_code: ISO code of the person's phone number
    :param phone: person's phone number
    :param person_language: language of the person
    :param group_ref_id: (concealed) reference ID of the group the person belongs to
    :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)
    itsm_validator.validate_person_data(first_name, last_name, title, email, iso_code, phone, person_language)
    unmasked_people_ref_id = key_manager.unmask_reference_key(people_ref_id)
    unmasked_group_ref_id = None if group_ref_id is None else key_manager.unmask_reference_key(group_ref_id)
    adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION

    query = '''
            select edit_external_person(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.group_component_type_id, adv_perm_type,
                    user_id, timestamp, constants.end_timestamp, organization_id, unmasked_people_ref_id,
                    first_name, last_name, title, unmasked_group_ref_id, email,
                    iso_code, phone, person_language,)
    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_people(conn, timestamp, organization_id, user_id, people_ref_ids, check_adv_perm=False):
    '''
    Delete external people. Multiple people can be deleted at the same time.
    :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 who is trying to delete the person
    :param people_ref_ids: (list) of (concealed) reference ID of the people to remove
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    unmasked_ppl_refs = []
    if isinstance(people_ref_ids, str):
        unmasked_ppl_refs = [key_manager.unmask_reference_key(people_ref_ids)]
    if isinstance(people_ref_ids, list):
        for item in people_ref_ids:
            unmasked_ppl_refs.append(key_manager.unmask_reference_key(item))

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'ppl_ref_ids': unmasked_ppl_refs}
    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_params['usr_id'] = user_id,
        query_params['comp_type_id'] = configs.group_component_type_id

    query = '''
            begin;

            update external_people set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and people_ref_id = any(%(ppl_ref_ids)s)
                {0};

            end;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def list_people(conn, timestamp, organization_id, user_id, group_ref_id=None, keywords=None,
                row_limit=None, row_offset=None, check_adv_perm=False):
    '''
    Get the list of external people associated with an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to filter by
    :param user_id: ID of the user making the request
    :param group_ref_id: (optional) (concealed) reference ID of the group
    :param keywords: (optional) keywords to filter by
    :param row_limit: (optional) number of instances to fetch
    :param row_offset: (optional) number of instances to skip ahead
    :param check_adv_perm: (boolean) should advanced permissions be checked
    :return: (list of dict) of data
    :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}
    conditions = []
    if group_ref_id is not None:
        unmasked_group_ref_id = key_manager.unmask_reference_key(group_ref_id)
        conditions.append(" group_ref_id = %(grp_ref)s ")
        query_params['grp_ref'] = unmasked_group_ref_id

    if keywords is not None:
        assert isinstance(keywords, str)
        keywords = keywords.lower()
        conditions.append('''
            LOWER(
                first_name || last_name || coalesce(title, '') || coalesce(email, '') || coalesce(phone, '') ||
                coalesce(group_name, '') || coalesce(group_description, '')
            )
            like '%%' || %(search_words)s || '%%'
        ''')
        query_params['search_words'] = keywords

    if check_adv_perm:
        conditions.append('''
            ppl.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_params['usr_id'] = user_id,
        query_params['comp_type_id'] = configs.group_component_type_id

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

    query = '''
            select people_ref_id, first_name, last_name, title, group_name, group_ref_id,
                email, iso_country_code, phone
            from external_people as ppl
            left join external_groups as grp
                on grp.group_id = ppl.group_id
                    and grp.organization_id = ppl.organization_id
                    and grp.start_timestamp <= %(timestamp)s
                    and grp.end_timestamp > %(timestamp)s
            where ppl.start_timestamp < %(timestamp)s
                and ppl.end_timestamp > %(timestamp)s
                and ppl.organization_id = %(org_id)s
                {0}
            order by first_name
            {1};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '', limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for ppl_ref_, first_name_, last_name_, title_, grp_name_, grp_ref_, email_, iso_, phone_ in result:
            data.append({
                var_names.people_ref_id: key_manager.conceal_reference_key(ppl_ref_),
                var_names.first_name: first_name_,
                var_names.last_name: last_name_,
                var_names.title: title_,
                var_names.group: None if grp_ref_ is None else [grp_name_, key_manager.conceal_reference_key(grp_ref_)],
                var_names.email: email_,
                var_names.iso_country_code: iso_,
                var_names.phone: phone_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_basic_people_list(conn, timestamp, organization_id):
    '''
    Get the basic people list.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :return: (list of list) -> [ [full name, people ref id], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select first_name || ' ' || last_name as full_name, people_ref_id
            from external_people
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
            order by full_name;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for full_name, ppl_ref in result:
            data.append([full_name, key_manager.conceal_reference_key(ppl_ref)])
        return data
    except psycopg2.DatabaseError:
        raise