# By: Riasat Ullah
# This file contains Jira 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


def update_jira_account_tokens(conn, timestamp, organization_id, jira_cloud_id, access_token, refresh_token):
    '''
    Checks if a given Jira account is mapped to a given TaskCall organization or not.
    This check is done prior to making a Jira 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 jira_cloud_id: the Jira cloud ID to check for
    :param access_token: the access token to be entered in the json details
    :param refresh_token: the refresh token to be entered in the json details
    :return: (boolean) True if it exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(jira_cloud_id, str)
    assert isinstance(access_token, str)
    assert isinstance(refresh_token, str)

    external_info = {var_names.access_token: access_token, var_names.refresh_token: refresh_token}
    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.jira_cloud,
                    'ext_id': jira_cloud_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_jira_request_acceptance_details(conn, timestamp, organization_id, integration_key=None, cloud_id=None,
                                        integration_id=None):
    '''
    Get the Jira Cloud ID associated to a Jira 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 cloud_id: (str) jira cloud id
    :param integration_id: (int) the ID of the integration
    :return: (dict) -> {access_token: xxx, external_id: cloud id }
    '''
    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 cloud_id is not None:
        assert isinstance(cloud_id, str)
        conditions.append(' external_id = %(cld_id)s ')
        query_params['cld_id'] = cloud_id
    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->>'access_token', details->>'refresh_token'
            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.external_id: result[0][0],
                var_names.access_token: result[0][1],
                var_names.refresh_token: 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 get_jira_webhook_details(conn, timestamp, cloud_id, webhook_id):
    '''
    Gets the details of a Jira integration. This is used for incoming webhooks.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param cloud_id: Jira cloud id
    :param webhook_id: ID of the webhook
    :return: (list of dict) full details of integrations
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(cloud_id, str)

    query = '''
            select sint.organization_id, sint.serviceid, sint.integration_id, sint.integration_type_id, 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.vendor_endpoint_name = %(cloud_id)s
                and (sint.additional_info->>'webhook_id')::int = %(wbk_id)s
                and intt.integration_type = %(integ_type)s;
            '''
    query_params = {'timestamp': timestamp, 'cloud_id': cloud_id, 'integ_type': intt.jira_cloud,
                    'wbk_id': webhook_id}
    try:
        result = conn.fetch(query, query_params)
        for org_id, serv_id, integ_id, integ_type_id, add_info in result:
            return org_id, serv_id, integ_id, integ_type_id, add_info
        return None
    except psycopg2.DatabaseError:
        raise


def get_jira_server_hosts(conn, timestamp, organization_id):
    '''
    Get the list of Jira Server hosts of an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: the organization ID
    :return: (list) of web addresses
    :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.jira_server}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_jira_server_host_details(conn, timestamp, organization_id, integration_key=None, host_address=None,
                                 integration_id=None):
    '''
    Get the Jira Cloud ID associated to a Jira 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) jira server host address
    :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}
    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:
        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
                {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 get_jira_server_webhook_details(conn, timestamp, integration_key):
    '''
    Gets the details of a Jira integration. This is used for incoming webhooks.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param integration_key: integration key
    :return: (list of dict) full details of integrations
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_integ_key = key_manager.unmask_reference_key(integration_key)

    query = '''
            select sint.organization_id, sint.serviceid, sint.integration_id, sint.integration_type_id, 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.integration_key = %(integ_key)s
                and intt.integration_type = %(integ_type)s;
            '''
    query_params = {'timestamp': timestamp, 'integ_key': unmasked_integ_key, 'integ_type': intt.jira_server}
    try:
        result = conn.fetch(query, query_params)
        for org_id, serv_id, integ_id, integ_type_id, add_info in result:
            return org_id, serv_id, integ_id, integ_type_id, add_info
        return None
    except psycopg2.DatabaseError:
        raise


def update_jira_server_credentials(conn, timestamp, organization_id, server_host, server_admin, server_password):
    '''
    Update a Jira Server admin credentials. Ensure that an existing Jira Server 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 server_host: server host address
    :param server_admin: Jira admin username
    :param server_password: Jira admin password
    :return: (boolean) True if it exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_web_url(server_host)

    external_info = {var_names.username: server_admin, var_names.password: server_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 = %(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.jira_server,
                    'ext_id': server_host, '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