# By: Riasar Ullah
# This module works with integrations data in the database.

from utils import constants, integration_type_names, key_manager, var_names
import configuration
import datetime
import json
import psycopg2


def get_available_integrations(conn, timestamp, only_initiation_needed=True, detailed=False, only_service_level=True):
    '''
    Get all the integrations that are available at a given point in time.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param only_initiation_needed: True if integrations that require initiation from TaskCall are wanted
    :param detailed: True if all the fields are required; if False then only the name and type are returned
    :param only_service_level: True if only service level integrations are needed; False otherwise
    :return: (list) of list
    '''
    assert isinstance(timestamp, datetime.datetime)
    integrations_to_skip = [integration_type_names.custom_endpoint, integration_type_names.custom_integration,
                            integration_type_names.email, integration_type_names.saml]
    cond = ''
    if only_initiation_needed:
        cond += ' and initiation_needed is not false '
    if only_service_level:
        cond += ' and is_service_level is true'
    query = '''
            select integration_type, description, icon_url, doc_url, category, promo_url
            from integration_types
            where start_date <= %s
                and end_date > %s
                and not integration_type = any(%s)
                {0}
            order by description;
            '''.format(cond)
    query_params = (timestamp, timestamp, integrations_to_skip,)
    try:
        result = conn.fetch(query, query_params)
        data = []

        if detailed:
            for integ_type, desc, icon_url, doc_url, category, promo_url in result:
                data.append([desc, integ_type, icon_url, doc_url, category, promo_url])
        else:
            for integ_type, desc, icon_url, doc_url, category, promo_url in result:
                data.append([desc, integ_type])

        return data
    except psycopg2.DatabaseError:
        raise


def get_integration_open_instances_trigger_info(conn, timestamp, organization_id, service_id, integration_id):
    '''
    Get the instance id of the instance created by TaskCall for a particular DataDog alert.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the alert was triggered on
    :param service_id: ID of the service the alert was triggered on
    :param integration_id: the integration ID
    :return: (tuple) instance ID, task ID, trigger info
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(service_id, int)
    assert isinstance(integration_id, int)

    query = '''
            select inst.instanceid, tasks.taskid, trigger_info
            from tasks
            join task_instances as inst using(taskid)
            where tasks.start_timestamp <= %(timestamp)s
                and tasks.end_timestamp > %(timestamp)s
                and tasks.organization_id = %(org_id)s
                and tasks.serviceid = %(serv_id)s
                and tasks.integration_id = %(integ_id)s
                and inst.organization_id = %(org_id)s
                and inst.is_open = true;
            '''

    query_params = {'timestamp': timestamp, 'org_id': organization_id,
                    'serv_id': service_id, 'integ_id': integration_id}
    try:
        result = conn.fetch(query, query_params)
        return result
    except psycopg2.DatabaseError:
        raise


def get_instance_custom_action_integrations(conn, timestamp, organization_id, serv_ref_id=None, only_manual=True):
    '''
    Get the list of service integrations which execute custom actions.
    :param conn: db connection
    :param timestamp: timestamp when this request is made
    :param organization_id: ID of the organization
    :param serv_ref_id: concealed reference ID of the service to look into
    :param only_manual: True if only the ones that need to be handled manually are required
    :return: (list of dict) -> [{ integration key: , configuration name: }, ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_serv_ref = key_manager.unmask_reference_key(serv_ref_id)

    query = '''
            select integration_key, additional_info
            from service_integrations as sint
            join services as srv using(serviceid, organization_id)
            where sint.start_timestamp <= %(timestamp)s
                and sint.end_timestamp > %(timestamp)s
                and sint.organization_id = %(org_id)s
                and outgoing_events is not null
                and %(custom_evn)s = any(outgoing_events)
                and srv.start_timestamp <= %(timestamp)s
                and srv.end_timestamp > %(timestamp)s
                and (
                    service_ref_id = %(srv_ref)s
                    or
                    integration_type_id in (
                        select integration_type_id from integration_types
                        where start_date <= %(timestamp)s
                            and end_date > %(timestamp)s
                            and integration_type = any(%(acc_wide_integ)s)
                    )
                )
            order by integration_name;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'custom_evn': constants.custom_action_event,
                    'srv_ref': unmasked_serv_ref, 'acc_wide_integ': configuration.allowed_account_wide_integrations}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for integ_key, add_info in result:
            has_manual_attribute = True if var_names.is_manual in add_info else False
            if (only_manual and has_manual_attribute and add_info[var_names.is_manual])\
                    or not only_manual or not has_manual_attribute:
                data.append({
                    var_names.integration_key: key_manager.conceal_reference_key(integ_key),
                    var_names.configuration_name: add_info[var_names.configuration_name]
                })
        return data
    except psycopg2.DatabaseError:
        raise


def get_basic_custom_action_integrations_list(conn, timestamp, organization_id):
    '''
    Get the basic list for executable custom actions.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :return: (list of list) -> [ [configuration name, integration key], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    query = '''
            select additional_info ->> 'configuration_name', integration_key
            from service_integrations
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and outgoing_events is not null
                and %(custom_evn)s = any(outgoing_events);
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'custom_evn': constants.custom_action_event}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for conf_name, integ_key in result:
            data.append([conf_name, key_manager.conceal_reference_key(integ_key)])
        return data
    except psycopg2.DatabaseError:
        raise


def list_custom_action_integrations_from_keys(conn, timestamp, organization_id, service_ref_ids=None, unmasked=True,
                                              as_dict=False):
    '''
    Get the IDs of custom action integrations as a list given their integration keys.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: organization id the policies belong to
    :param service_ref_ids: (list) of service reference ids to filter the search by
    :param unmasked: True if the given reference IDs are unmasked; False otherwise
    :param as_dict: True if the data should be returned as dict
    :return: (list) -> [integration ID 1, integration ID 2, ...]  |  (dict) -> {ref_id: integration_id, ...}
    '''
    assert isinstance(timestamp, datetime.datetime)

    conditions = []
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'custom_evn': constants.custom_action_event}
    if service_ref_ids is not None:
        assert isinstance(service_ref_ids, list)
        if not unmasked:
            service_ref_ids = [key_manager.unmask_reference_key(x) for x in service_ref_ids]
        conditions.append('service_ref_id = any(%(srv_list)s)')
        query_params['srv_list'] = service_ref_ids

    query = '''
            select integration_id, integration_key
            from service_integrations
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and outgoing_events is not null
                and %(custom_evn)s = any(outgoing_events)
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        if as_dict:
            data = dict()
            for id_, ref_ in result:
                data[ref_] = id_
            return data
        else:
            data = []
            for id_, ref_ in result:
                data.append(id_)
            return data
    except psycopg2.DatabaseError:
        raise


def get_comprehensive_integration_details(conn, timestamp, organization_id, integration_key=None, integration_id=None,
                                          service_id=None, for_custom_action=False):
    '''
    Get the full integration details. This function should only be used for internal purposes.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param integration_key: (concealed) the unique key of the integration to look for
    :param integration_id: internal ID of the integration
    :param service_id: ID of the service
    :param for_custom_action: True if only integrations that are used to execute custom actions are required
    :return: (list of dict) full details of integrations
    :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:
        unmasked_integ_key = key_manager.unmask_reference_key(integration_key)
        conditions.append(" sint.integration_key = %(integ_key)s ")
        query_params['integ_key'] = unmasked_integ_key
    if integration_id is not None:
        assert isinstance(integration_id, int)
        conditions.append(" sint.integration_id = %(integ_id)s ")
        query_params['integ_id'] = integration_id
    if service_id is not None:
        assert isinstance(service_id, int)
        conditions.append(" sint.serviceid = %(srv_id)s ")
        query_params['srv_id'] = service_id
    if for_custom_action:
        conditions.append(" sint.outgoing_events is not null and  %(custom_evn)s = any(sint.outgoing_events) ")
        query_params['custom_evn'] = constants.custom_action_event

    query = '''
            select sint.integration_id, sint.integration_type_id, intt.integration_type, sint.integration_key,
                sint.integration_name,  integration_email, integration_url, access_token, secret_token, vendor_endpoint,
                vendor_endpoint_name, vendor_account_name, incoming_events, outgoing_events, conditions_map,
                payload_map, public_access, additional_info
            from service_integrations as sint
            join integration_types as intt using(integration_type_id)
            where sint.start_timestamp <= %(timestamp)s
                and sint.end_timestamp > %(timestamp)s
                and sint.organization_id = %(org_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for integ_id, integ_type_id, integ_type, integ_key, integ_name, integ_email, integ_url, acc_token, sec_token,\
            ven_ep, ven_ep_name, ven_acc_name, in_events, out_events, cond_map, payload_map, is_public,\
                add_info in result:
            data.append({
                var_names.integration_id: integ_id,
                var_names.integration_type_id: integ_type_id,
                var_names.integration_type: integ_type,
                var_names.integration_key: key_manager.conceal_reference_key(integ_key),
                var_names.integration_name: integ_name,
                var_names.integration_email: integ_email,
                var_names.integration_url: integ_url,
                var_names.access_token: acc_token,
                var_names.secret_token: sec_token,
                var_names.vendor_endpoint: ven_ep,
                var_names.vendor_endpoint_name: ven_ep_name,
                var_names.vendor_account_name: ven_acc_name,
                var_names.incoming_events: in_events,
                var_names.outgoing_events: out_events,
                var_names.conditions_map: cond_map,
                var_names.payload_map: payload_map,
                var_names.is_public: is_public,
                var_names.additional_info: add_info
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_vendor_synced_open_instance_ids(conn, timestamp, organization_id, integ_id, integ_type_id, vendor_id,
                                        synced_only=True):
    '''
    Get the list of instance ids and their task ids for open instances that have been synced
    with a given type of integration and a given integration.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param organization_id: ID of the organization
    :param integ_id: ID of the integration
    :param integ_type_id: the integration type ID
    :param vendor_id: the ID of the incident equivalent of the vendor
    :param synced_only: True if only the synced instances are wanted
    :return: (list of list) -> [[instance id, task id, is synced], ...]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(integ_id, int)
    assert isinstance(integ_type_id, int)
    assert isinstance(vendor_id, str)

    condition = ''
    if synced_only:
        condition = ' and ca.is_synced = true '

    query = '''
            select ca.instanceid, inst.taskid, ca.is_synced
            from instance_custom_actions as ca
            join task_instances as inst using(instanceid)
            where ca.integration_id = %(integ_id)s
                and ca.integration_type_id = %(integ_type_id)s
                and ca.vendor_id = %(ven_id)s
                and inst.organization_id = %(org_id)s
                and inst.is_open = true
                {0};
            '''.format(condition)
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'integ_id': integ_id,
                    'integ_type_id': integ_type_id, 'ven_id': vendor_id}
    try:
        result = conn.fetch(query, query_params)
        return result
    except psycopg2.DatabaseError:
        raise


def store_temporary_verification_token(conn, timestamp, end_timestamp, token, token_for, details,
                                       expire_prior_tokens_by=None):
    '''
    Store a temporary verification token. These tokens are stored to verify vendor triggered integrations primarily.
    Sometimes integrations may be triggered from the vendors where we are unable to map it to an organization on our
    side. By issuing a token and sending it back as response, we will be able to track the integration and validate
    the source when the user is redirected to our site.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param end_timestamp: timestamp till when this token will stay valid
    :param token: (str) token
    :param token_for: (str) who the token is for
    :param details: (dict) details related to the token
    :param expire_prior_tokens_by: (list) of tuples with keys and values to eliminate prior tokens
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(end_timestamp, datetime.datetime)
    assert isinstance(token, str)
    assert token_for in configuration.allowed_temporary_token_verification_types
    assert isinstance(details, dict)

    conditions = []
    if expire_prior_tokens_by is not None and len(expire_prior_tokens_by) > 0:
        for attr, val in expire_prior_tokens_by:
            assert isinstance(attr, str)
            assert isinstance(val, str)
            conditions.append(" details->>'{0}' = '{1}' ".format(attr, val))

        update_section = '''
            update temporary_verification_tokens set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                {0};
        '''.format(' and ' + ' and '.join(conditions))
    else:
        update_section = ''

    query = '''
            begin;
            {0}

            insert into temporary_verification_tokens values 
            (%(token)s, %(timestamp)s, %(end_time)s, %(vendor)s, %(det)s, 0);
            end;
            '''.format(update_section)
    query_params = {'token': token, 'timestamp': timestamp, 'end_time': end_timestamp,
                    'vendor': token_for, 'det': json.dumps(details)}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_temporary_verification_token_details(conn, timestamp, token, token_for):
    '''
    Get the details of a temporary verification token.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param token: the token to check for
    :param token_for: who the token was issued for (integration type, etc)
    :return: (dict) of details; None if a matching token is not found
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(token, str)
    assert token_for in configuration.allowed_temporary_token_verification_types

    query = '''
            update temporary_verification_tokens set verification_attempts = verification_attempts + 1
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and temp_token = %(tkn)s
                and verification_type = %(vrf_type)s
                and verification_attempts < %(allowed_attempts)s
                and (is_verified is null or is_verified = false)
            returning details;
            '''
    query_params = {'timestamp': timestamp, 'tkn': token, 'vrf_type': token_for,
                    'allowed_attempts': configuration.allowed_failed_verification_attempts}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0][0]
        return None
    except psycopg2.DatabaseError:
        raise


def confirm_temporary_token_verification(conn, timestamp, token, token_for):
    '''
    Updates a temporary token as verified.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param token: (str) token
    :param token_for: (str) who the token is for
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(token, str)
    assert token_for in configuration.allowed_temporary_token_verification_types

    query = '''
            update temporary_verification_tokens set end_timestamp = %(timestamp)s,
                is_verified = true
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and temp_token = %(tkn)s
                and verification_type = %(vrf_type)s
                and (is_verified is null or is_verified = false);
            '''
    query_params = {'timestamp': timestamp, 'tkn': token, 'vrf_type': token_for}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def expire_token_and_get_validity_details(conn, timestamp, token, token_for):
    '''
    Expire temporary token and get details.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param token: (str) token
    :param token_for: (str) who the token is for
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(token, str)
    assert token_for in configuration.allowed_temporary_token_verification_types

    query = '''
            update temporary_verification_tokens set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and temp_token = %(tkn)s
                and verification_type = %(vrf_type)s
                and (is_verified is null or is_verified = false)
            returning start_timestamp, details;
            '''
    query_params = {'timestamp': timestamp, 'tkn': token, 'vrf_type': token_for}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0]
        return None
    except psycopg2.DatabaseError:
        raise


def get_integration_key_from_id(conn, timestamp, integration_id):
    '''
    Get the integration key from the integration ID.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param integration_id: the integration ID
    :return: (concealed) integration key
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(integration_id, int)

    query = '''
            select integration_key from service_integrations
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and integration_id = %(integ_id)s;
            '''
    query_params = {'timestamp': timestamp, 'integ_id': integration_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return key_manager.conceal_reference_key(result[0][0])
        else:
            return None
    except psycopg2.DatabaseError:
        raise


def get_integration_details(conn, timestamp, integration_key, integration_type):
    '''
    Gets the details of an integration.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param integration_key: unique key for the integration
    :param integration_type: the type of integration
    :return: (tuple) org_id, org_perm, service ID, integration ID, integration type id, additional info
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_key = key_manager.unmask_reference_key(integration_key)

    query = '''
            select sint.organization_id, org_perm.permissions, sint.serviceid, sint.integration_id,
                sint.integration_type_id, additional_info
            from service_integrations as sint
            join organization_permissions as org_perm using(organization_id)
            join integration_types as intt using(integration_type_id)
            where sint.start_timestamp <= %(timestamp)s
                and sint.end_timestamp > %(timestamp)s
                and sint.integration_key = %(integ_key)s
                and org_perm.start_timestamp < %(timestamp)s
                and org_perm.end_timestamp > %(timestamp)s
                and intt.integration_type = %(integ_type)s;
            '''
    query_params = {'timestamp': timestamp, 'integ_key': unmasked_key, 'integ_type': integration_type}
    try:
        result = conn.fetch(query, query_params)
        for org_id, org_perm, serv_id, integ_id, integ_type_id, add_info in result:
            return org_id, org_perm, serv_id, integ_id, integ_type_id, add_info
        return None
    except psycopg2.DatabaseError:
        raise


def check_and_update_organization_integration_details(conn, timestamp, organization_id, integ_type,
                                                      external_id, external_info):
    '''
    Checks if an external account for a given integration type is mapped to a TaskCall organization or not.
    This check can be done prior to adding an integration to avoid duplication.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to check against
    :param integ_type: type of integration
    :param external_id: (str) the Monday.com account ID to check for
    :param external_info: (dict) of integration type details
    :return: (boolean) True if it exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert integ_type in configuration.allowed_integration_types
    assert isinstance(external_id, str)
    assert isinstance(external_info, dict)

    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': integ_type,
                    'ext_id': external_id, '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 get_basic_recent_changes_integrations_list(conn, timestamp):
    '''
    Get the basic list of recent changes integrations.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :return: (list of list) -> [ [integration name, integration type], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select description, integration_type
            from integration_types
            where start_date <= %(timestamp)s
                and end_date > %(timestamp)s
                and integration_type = any(%(rec_chg_typ)s)
            order by description;
            '''
    query_params = {'timestamp': timestamp, 'rec_chg_typ': configuration.allowed_recent_changes_integrations}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for desc_, integ_type_ in result:
            data.append([desc_, integ_type_])
        return data
    except psycopg2.DatabaseError:
        raise


def get_basic_chat_integrations_list(conn, timestamp, organization_id):
    '''
    Get the basic list of chat integrations of an organization.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :return: (list of list) -> [ [integration name, integration key], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    allowed_chat_types = [integration_type_names.slack]

    query = '''
            select integration_name, integration_key
            from service_integrations
            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 = any(%(int_types)s)
                );
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'int_types': allowed_chat_types}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for integ_name, integ_key in result:
            data.append([integ_name, key_manager.conceal_reference_key(integ_key)])
        return data
    except psycopg2.DatabaseError:
        raise


def list_chat_integrations_from_keys(conn, timestamp, organization_id, integ_keys=None, unmasked=True, as_dict=False):
    '''
    Get the IDs of services as a list given their reference ids.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: organization id the policies belong to
    :param integ_keys: (list) of integration keys
    :param unmasked: True if the given reference IDs are unmasked; False otherwise
    :param as_dict: True if the data should be returned as dict
    :return: (list) -> [integration ID 1, integration ID 2, ...]  |  (dict) -> {integration key: integration id, ...}
    '''
    assert isinstance(timestamp, datetime.datetime)
    allowed_chat_types = [integration_type_names.slack]

    conditions = []
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'int_types': allowed_chat_types}
    if integ_keys is not None:
        assert isinstance(integ_keys, list)
        if not unmasked:
            integ_keys = [key_manager.unmask_reference_key(x) for x in integ_keys]
        conditions.append('integration_key = any(%(int_keys)s)')
        query_params['int_keys'] = integ_keys

    query = '''
            select integration_id, integration_key
            from service_integrations
            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 = any(%(int_types)s)
                )
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        if as_dict:
            data = dict()
            for id_, ref_ in result:
                data[ref_] = id_
            return data
        else:
            data = []
            for id_, ref_ in result:
                data.append(id_)
            return data
    except psycopg2.DatabaseError:
        raise


def get_integration_current_account_ids(conn, timestamp, organization_id, integ_type):
    '''
    Get the list of integration accounts associated with an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: the organization ID
    :param integ_type: (str) type of integration
    :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': integ_type}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise