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

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


def servicenow_integration_exists(conn, timestamp, organization_id, inst_url):
    '''
    Checks if a ServiceNow integration exists or not.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param inst_url: ServiceNow instance url
    :return: (list) names of existing integrations
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select oitd.external_id, srv.service_name
            from organization_integration_type_details as oitd
            left join service_integrations as sint
                on sint.vendor_endpoint = oitd.external_id
                    and sint.organization_id = oitd.organization_id
                    and sint.start_timestamp <= %(timestamp)s
                    and sint.end_timestamp > %(timestamp)s
            left join services as srv
                on srv.serviceid = sint.serviceid
                    and srv.start_timestamp <= %(timestamp)s
                    and srv.end_timestamp > %(timestamp)s
            where oitd.start_timestamp <= %(timestamp)s
                and oitd.end_timestamp > %(timestamp)s
                and oitd.organization_id = %(org_id)s
                and oitd.external_id = %(inst_url)s
                and oitd.integration_type_id in (
                    select integration_type_id from integration_types
                    where start_date <= %(timestamp)s
                        and end_date > %(timestamp)s
                        and integration_type = %(srv_now_integ)s
                );
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id,
                    'inst_url': inst_url, 'srv_now_integ': intt.servicenow}
    try:
        result = conn.fetch(query, query_params)
        data = []
        if len(result) > 0:
            if result[0][1] is None:
                cleanup_query = "select delete_organization_integration_type_details(%s, %s, %s, %s);"
                cleanup_params = (timestamp, organization_id, inst_url, intt.servicenow,)
                conn.execute(cleanup_query, cleanup_params)
            else:
                data.append(result[0][1])
        return data
    except psycopg2.DatabaseError:
        raise


def get_servicenow_instance_details(conn, timestamp, organization_id, integration_key=None, host_address=None,
                                    integration_id=None):
    '''
    Get the instance details associated to a ServiceNow 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 host_address: (str) ServiceNow instance url
    :param integration_id: (int) the ID of the integration
    :return: (dict) -> {vendor_endpoint: ..., username: ..., password: ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'now_typ': intt.servicenow}
    conditions = []
    if integration_key is not None:
        conditions.append(''' external_id in (
            select vendor_endpoint 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 host_address is not None:
        assert string_validator.is_web_url(host_address)
        conditions.append(' external_id = %(addr)s ')
        query_params['addr'] = host_address
    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'
            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 = %(now_typ)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: result[0][0],
                var_names.username: result[0][1],
                var_names.password: result[0][2]
            }
        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_servicenow_credentials(conn, timestamp, organization_id, integ_key, srv_admin, srv_password):
    '''
    Update a ServiceNow user credentials. Ensure that an existing ServieNow details exist.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to check against
    :param srv_host: ServiceNow instance url
    :param srv_admin: ServiceNow admin username
    :param srv_password: ServiceNow admin password
    :return: (boolean) True if it exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_integ_key = key_manager.unmask_reference_key(integ_key)

    external_info = {var_names.username: srv_admin, var_names.password: srv_password}
    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 in (
                    select vendor_endpoint from service_integrations
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and integration_key = %(integ_key)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.servicenow,
                    'integ_key': unmasked_integ_key, '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_servicenow_mappings(conn, timestamp, organization_id, additional_info):
    '''
    Internalize the mappings (users, services, policies) in the additional info field of a ServiceNow 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 jsonb_build_object('users', jsonb_object_agg(preferred_username, user_id)) as dt
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
            
            union
            
            select jsonb_build_object('services', jsonb_object_agg(service_ref_id, serviceid)) as dt
            from services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
            
            union
            
            select jsonb_build_object('policies', jsonb_object_agg(policy_ref_id, policyid)) as dt
            from policies
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and is_group_policy = true
                and organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for item in result:
            main_dict = item[0]
            if var_names.users in main_dict:
                for pref, u_id in main_dict[var_names.users].items():
                    main_dict[var_names.users][pref] = int(u_id)
                data.update(main_dict)

            elif var_names.services in main_dict or var_names.policies in main_dict:
                category = list(main_dict.keys())[0]
                repl_dict = dict()
                for sp_ref, sp_id in main_dict[category].items():
                    repl_dict[uuid.UUID(sp_ref)] = int(sp_id)
                data.update({category: repl_dict})

            else:
                data.update(main_dict)

        def_user = additional_info[var_names.default_user]
        def_service = key_manager.unmask_reference_key(additional_info[var_names.default_service])

        if def_user in data[var_names.users]:
            additional_info[var_names.default_user] = data[var_names.users][def_user]

        if def_service in data[var_names.services]:
            additional_info[var_names.default_service] = data[var_names.services][def_service]

        for category in [var_names.services, var_names.policies]:
            mapped_category = additional_info[category]
            if mapped_category is not None and len(mapped_category) > 0:
                new_map = dict()
                for key in mapped_category:
                    val = key_manager.unmask_reference_key(mapped_category[key])
                    if val in data[category]:
                        new_map[key] = data[category][val]
                additional_info[category] = new_map

        return additional_info
    except psycopg2.DatabaseError:
        raise


def externalize_servicenow_mappings(conn, timestamp, organization_id, additional_info):
    '''
    Externalize the mappings (users, services, policies) in the additional info field of a ServiceNow 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 externalized)
    :return: (dict) externalized additional info
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(additional_info, dict)

    query = '''
            select jsonb_build_object('users', jsonb_object_agg(user_id, preferred_username)) as dt
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s

            union

            select jsonb_build_object('services', jsonb_object_agg(serviceid, service_ref_id)) as dt
            from services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s

            union

            select jsonb_build_object('policies', jsonb_object_agg(policyid, policy_ref_id)) as dt
            from policies
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and is_group_policy = true
                and organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for item in result:
            main_dict = item[0]
            if var_names.users in main_dict:
                repl_dict = dict()
                for u_id, pref in main_dict[var_names.users].items():
                    repl_dict[int(u_id)] = pref
                data.update({var_names.users: repl_dict})

            elif var_names.services in main_dict or var_names.policies in main_dict:
                category = list(main_dict.keys())[0]
                repl_dict = dict()
                for sp_id, sp_ref in main_dict[category].items():
                    repl_dict[int(sp_id)] = uuid.UUID(sp_ref)
                data.update({category: repl_dict})

            else:
                data.update(main_dict)

        def_user = additional_info[var_names.default_user]
        def_service = additional_info[var_names.default_service]

        if def_user in data[var_names.users]:
            additional_info[var_names.default_user] = data[var_names.users][def_user]

        if def_service in data[var_names.services]:
            additional_info[var_names.default_service] =\
                key_manager.conceal_reference_key(data[var_names.services][def_service])

        for category in [var_names.services, var_names.policies]:
            mapped_category = additional_info[category]
            if mapped_category is not None and len(mapped_category) > 0:
                new_map = dict()
                for key in mapped_category:
                    val = mapped_category[key]
                    if val in data[category]:
                        new_map[key] = key_manager.conceal_reference_key(data[category][val])
                additional_info[category] = new_map

        return additional_info
    except psycopg2.DatabaseError:
        raise