# By: Md. Fahim Bin Amin
# This file contains SolarWinds ServiceDesk 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_solarwinds_servicedesk_accounts(conn, timestamp, organization_id):
    '''
    Get list of ServiceDesk accounts (external_id) associated with an organization.
    :param conn: db connection
    :param timestamp: timestamp when request was made
    :param organization_id: org ID
    :return: list of external_ids
    :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.solarwinds_servicedesk
    }

    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_solarwinds_servicedesk_account_details(conn, timestamp, organization_id, integration_key=None,
                                              account_domain=None, integration_id=None):
    '''
    Get the ServiceDesk domain and credentials associated with a ServiceDesk 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) ServiceDesk 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_solarwinds_servicedesk_credentials(conn, timestamp, organization_id, account_domain,
                                             account_admin, account_password, account_admin_uid):
    '''
    Update ServiceDesk admin credentials. Ensures there is already an existing record.
    :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.solarwinds_servicedesk,
        'ext_id': account_domain,
        'ext_info': json.dumps(external_info)
    }

    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return False
        return True
    except psycopg2.DatabaseError:
        raise