# By: Riasat Ullah
# This file contains Autotask integration related db queries.
import uuid

from utils import errors, integration_type_names as intt, key_manager, var_names
import datetime
import json
import psycopg2


def get_autotask_account_details(conn, timestamp, organization_id, integration_key=None, account_domain=None,
                                 integration_id=None):
    '''
    Get the Autotask domain and credentials associated with an Autotask integration.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param integration_key: (concealed) integration key of the integration
    :param account_domain: (str) Autotask account domain
    :param integration_id: (int) the ID of the integration
    :return: (dict) -> {vendor_endpoint_name: ..., username: ..., password: ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'integ_type': intt.autotask}
    conditions = []
    if integration_key is not None:
        conditions.append(''' external_id in (
            select vendor_endpoint_name from service_integrations
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and integration_key = %(integ_key)s
        )''')
        query_params['integ_key'] = key_manager.unmask_reference_key(integration_key)
    if account_domain is not None:
        assert isinstance(account_domain, str)
        conditions.append(' external_id = %(dmn)s ')
        query_params['dmn'] = account_domain
    if integration_id is not None:
        assert isinstance(integration_id, int)
        conditions.append(' integration_id = %(integ_id)s ')
        query_params['integ_id'] = integration_id

    query = '''
            select external_id, details->>'username', details->>'password', details->>'secret_token'
            from organization_integration_type_details
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and integration_type_id in (
                    select integration_type_id from integration_types
                    where start_date <= %(timestamp)s
                        and end_date > %(timestamp)s
                        and integration_type = %(integ_type)s
                )
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return {
                var_names.vendor_endpoint_name: result[0][0],
                var_names.username: result[0][1],
                var_names.password: result[0][2],
                var_names.secret_token: result[0][3]
            }
        elif len(result) == 0:
            raise ValueError(errors.err_unknown_resource)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def update_autotask_credentials(conn, timestamp, organization_id, account_domain, api_user, api_user_password,
                                api_user_tracking_id):
    '''
    Update an Autotask API user credentials. Ensure that there is an existing record of the Autotask account.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to check against
    :param account_domain: Autotask account domain
    :param api_user: username of the API user
    :param api_user_password: password of the API user
    :param api_user_tracking_id: tracking ID of the API user
    :return: (boolean) True if it exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(account_domain, str)
    assert isinstance(api_user, str)
    assert isinstance(api_user_password, str)
    assert isinstance(api_user_tracking_id, str)

    external_info = {var_names.username: api_user, var_names.password: api_user_password,
                     var_names.secret_token: api_user_tracking_id}
    query = '''
            update organization_integration_type_details set details = %(ext_info)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and external_id = %(ext_id)s
                and integration_type_id in (
                    select integration_type_id from integration_types
                    where start_date <= %(timestamp)s
                        and end_date > %(timestamp)s
                        and integration_type = %(integ_type)s
                )
            returning external_id;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'integ_type': intt.autotask,
                    'ext_id': account_domain, 'ext_info': json.dumps(external_info)}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return False
        else:
            return True
    except psycopg2.DatabaseError:
        raise


def internalize_autotask_mappings(conn, timestamp, organization_id, additional_info):
    '''
    Internalize the group mappings in the additional info field of an Autotask integration.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: ID of the organization
    :param additional_info: (dict) details of the mappings (including info that do not need to be internalized)
    :return: (dict) internalized additional info
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(additional_info, dict)

    query = '''
            select group_ref_id, group_id
            from external_groups
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for grp_ref, grp_id in result:
            data[grp_ref] = grp_id

        input_groups = additional_info[var_names.groups]
        if input_groups is not None and len(input_groups) > 0:
            new_groups = dict()
            for key in input_groups:
                unmasked_key = key_manager.unmask_reference_key(key)
                if unmasked_key in data:
                    new_groups[data[unmasked_key]] = input_groups[key]
            additional_info[var_names.groups] = new_groups

        return additional_info
    except psycopg2.DatabaseError:
        raise


def externalize_autotask_mappings(conn, timestamp, organization_id, additional_info):
    '''
    Externalize the group mappings in the additional info field of an Autotask integration.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: ID of the organization
    :param additional_info: (dict) details of the mappings (including info that do not need to be internalized)
    :return: (dict) internalized additional info
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(additional_info, dict)

    query = '''
            select group_id, group_ref_id
            from external_groups
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for grp_id, grp_ref in result:
            data[grp_id] = grp_ref

        stored_groups = additional_info[var_names.groups]
        if stored_groups is not None and len(stored_groups) > 0:
            new_groups = dict()
            for id_ in stored_groups:
                int_id = int(id_)
                if int_id in data:
                    ref_ = key_manager.conceal_reference_key(data[int_id])
                    new_groups[ref_] = stored_groups[id_]
            additional_info[var_names.groups] = new_groups

        return additional_info
    except psycopg2.DatabaseError:
        raise