# By: Riasat Ullah
# This file contains TeamDynamix integration related db queries.

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


def get_team_dynamix_accounts(conn, timestamp, organization_id):
    '''
    Get the list of TeamDynamix accounts associated with an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: the organization ID
    :return: (list) of domains
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select external_id from organization_integration_type_details as oitd
            join integration_types as it
                on it.integration_type_id = oitd.integration_type_id
                    and it.start_date <= %(timestamp)s
                    and it.end_date > %(timestamp)s
            where oitd.start_timestamp <= %(timestamp)s
                and oitd.end_timestamp > %(timestamp)s
                and oitd.organization_id = %(org_id)s
                and it.integration_type = %(int_type)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'int_type': intt.team_dynamix}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_team_dynamix_account_details(conn, timestamp, organization_id, integration_key=None, account_domain=None,
                                     integration_id=None):
    '''
    Get the TeamDynamix domain and credentials associated with a TeamDynamix 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) TeamDynamix 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}
    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->>'user_object_id'
            from organization_integration_type_details
            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 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.user_object_id: 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_team_dynamix_credentials(conn, timestamp, organization_id, account_domain, account_admin, account_password,
                                    account_admin_uid):
    '''
    Update a TeamDynamix admin credentials. Ensure that there is an existing record of the TeamDynamix 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: TeamDynamix account domain
    :param account_admin: TeamDynamix admin username
    :param account_password: TeamDynamix admin password
    :param account_admin_uid: UID of the admin
    :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(account_admin, str)
    assert isinstance(account_password, str)
    assert isinstance(account_admin_uid, str)

    external_info = {var_names.username: account_admin, var_names.password: account_password,
                     var_names.user_object_id: account_admin_uid}
    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.team_dynamix,
                    '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