# By: Riasat Ullah
# This file contains database queries for handling CI/CD change events.

from psycopg2 import errorcodes
from utils import errors, key_manager, var_names
import configuration as configs
import datetime
import json
import psycopg2


def store_change_event(conn, timestamp, organization_id, service_id, title, integration_id=None,
                       integration_type_id=None, description=None, vendor_url=None, vendor_id=None, vendor_source=None,
                       vendor_event_type=None, vendor_event_status=None, vendor_event_by=None, additional_info=None):
    '''
    Store a change event.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: ID of the organization
    :param service_id: ID of the service; will be None if event is created by API call
    :param title: title of the change event
    :param integration_id: ID of the integration; will be None if event is created by API call
    :param integration_type_id: ID of the integration type
    :param description: longer description of the change event
    :param vendor_url: URL to view the change event on the vendor's site
    :param vendor_id: ID of the change event on the vendor's site (pull request number, etc)
    :param vendor_source: source/branch for the change
    :param vendor_event_type: type of change event (pull request, push, etc)
    :param vendor_event_status: status of the change event (value provided by vendor)
    :param vendor_event_by: vendor side username of the user making the change
    :param additional_info: any additional info that should be stored
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(service_id, int)
    assert isinstance(title, str)

    if integration_id is not None or integration_type_id is not None:
        assert isinstance(integration_id, int)
        assert isinstance(integration_type_id, int)
    if description is not None:
        assert isinstance(description, str)
    if vendor_url is not None:
        assert isinstance(vendor_url, str)
    if vendor_id is not None:
        vendor_id = str(vendor_id)
    if vendor_source is not None:
        assert isinstance(vendor_source, str)
    if vendor_event_type is not None:
        assert isinstance(vendor_event_type, str)
    if vendor_event_status is not None:
        assert isinstance(vendor_event_status, str)
    if vendor_event_by is not None:
        assert isinstance(vendor_event_by, str)
    if additional_info is not None:
        assert isinstance(additional_info, dict)
        additional_info = json.dumps(additional_info)

    query = '''
            begin;

            insert into change_events (
                event_timestamp, organization_id, serviceid, integration_id, integration_type_id,
                title, description, vendor_url, vendor_id, vendor_source,
                vendor_event_type, vendor_event_status, vendor_event_by, additional_info
            ) values (
                %s, %s, %s, %s, %s::smallint,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );

            end;
            '''
    query_params = (timestamp, organization_id, service_id, integration_id, integration_type_id,
                    title, description, vendor_url, vendor_id, vendor_source,
                    vendor_event_type, vendor_event_status, vendor_event_by, additional_info,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def list_change_events(conn, timestamp, organization_id, serv_ref_ids=None, team_ref_ids=None, integ_types=None,
                       min_timestamp=None, user_id=None, check_adv_perm=False, row_limit=None, row_offset=None):
    '''
    List the change events that were registered for an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to get the events for
    :param serv_ref_ids: (list) of concealed service ref IDs to filter by
    :param team_ref_ids: (list) of concealed team ref IDs to filter by
    :param integ_types: (list) of integration types to filter by
    :param min_timestamp: (datetime.datetime) minimum date and time the event should have occurred by
    :param user_id: (int) ID of the user who is requesting to view the changes; necessary for advanced permissions
    :param check_adv_perm: (boolean) True if advanced permissions should be checked; False otherwise
    :param row_limit: (int) number of rows that should be fetched
    :param row_offset: (int) number of previous rows that should be offset
    :return: (list) of dict of change event details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id,
                    'srv_comp_type_id': configs.service_component_type_id}

    conditions = []

    if serv_ref_ids is not None:
        assert isinstance(serv_ref_ids, list)
        if len(serv_ref_ids) > 0:
            serv_ref_ids = [key_manager.unmask_reference_key(item) for item in serv_ref_ids]
            conditions.append(" srv.service_ref_id = any(%(srv_ref)s) ")
            query_params['srv_ref'] = serv_ref_ids

    if team_ref_ids is not None:
        assert isinstance(user_id, int)
        assert isinstance(team_ref_ids, list)
        if len(team_ref_ids) > 0:
            team_ref_ids = [key_manager.unmask_reference_key(item) for item in team_ref_ids]
            conditions.append('''
                tm.team_ref_id = any(%(tm_ref)s)
                and (
                    tm.is_public = true
                    or tm.team_id in (
                        select team_id from team_members
                        where start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                            and user_id = %(usr_id)s
                    )
                )
            ''')
            query_params['tm_ref'] = team_ref_ids
            query_params['usr_id'] = user_id

    if integ_types is not None:
        assert isinstance(integ_types, list)
        if len(integ_types) > 0:
            for item in integ_types:
                assert item in configs.allowed_recent_changes_integrations
            conditions.append(" itt.integration_type = any(%(itg_typ)s) ")
            query_params['itg_typ'] = integ_types

    if min_timestamp is not None:
        assert isinstance(min_timestamp, datetime.datetime)
        conditions.append(" ce.event_timestamp >= %(min_tmsp)s ")
        query_params['min_tmsp'] = min_timestamp

    if check_adv_perm:
        assert isinstance(user_id, int)
        conditions.append('''
            srv.serviceid not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(srv_comp_type_id)s::smallint
                )
            )
        ''')
        query_params['usr_id'] = user_id
        query_params['srv_comp_type_id'] = configs.service_component_type_id

    limit_cond = ''
    if row_limit is not None:
        assert isinstance(row_limit, int)
        limit_cond += ' limit {0} '.format(str(row_limit))
    if row_offset is not None:
        assert isinstance(row_offset, int)
        limit_cond += ' offset {0} '.format(str(row_offset))

    query = '''
            select ce.eventid, ce.event_timestamp, srv.service_ref_id, srv.service_name, itt.integration_type,
                itt.description, itt.icon_url, tm.team_ref_id, tm.team_name, ce.title, ce.description, ce.vendor_url,
                ce.vendor_id, ce.vendor_source, ce.vendor_event_type, ce.vendor_event_status, ce.vendor_event_by,
                ce.additional_info
            from change_events as ce
            left join services as srv
                on ce.serviceid is not null
                    and ce.serviceid = srv.serviceid
                    and srv.start_timestamp <= ce.event_timestamp
                    and srv.end_timestamp > ce.event_timestamp
            left join integration_types as itt
                on ce.integration_type_id is not null
                    and ce.integration_type_id = itt.integration_type_id
                    and itt.start_date <= %(timestamp)s
                    and itt.end_date > %(timestamp)s
            left join team_components as tc
                on ce.serviceid is not null
                    and ce.serviceid = tc.component_id
                    and tc.component_type_id = %(srv_comp_type_id)s
                    and tc.start_timestamp <= %(timestamp)s
                    and tc.end_timestamp > %(timestamp)s
            left join teams as tm
                on tm.team_id = tc.team_id
                    and tm.start_timestamp <= %(timestamp)s
                    and tm.end_timestamp > %(timestamp)s
            where ce.event_timestamp <= %(timestamp)s
                and ce.organization_id = %(org_id)s
                {0}
            order by event_timestamp desc
            {1};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '',
                       limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for ev_id, ev_tmsp, srv_ref, srv_name, itt_type, itt_name, itt_icon_url, tm_ref, tm_name, title, desc, v_url,\
                v_id, v_src, v_ev_type, v_ev_sts, v_ev_by, addn_info in result:

            data.append({
                var_names.event_id: ev_id,
                var_names.event_timestamp: ev_tmsp,
                var_names.services: [[srv_name, key_manager.conceal_reference_key(srv_ref)]]
                if srv_name is not None and srv_ref is not None else None,
                var_names.integrations: [itt_name, itt_type] if itt_name is not None and itt_type is not None else None,
                var_names.teams: [[tm_name, key_manager.conceal_reference_key(tm_ref)]]
                if tm_name is not None and tm_ref is not None else None,
                var_names.icon_url: itt_icon_url,
                var_names.title: title,
                var_names.description: desc,
                var_names.vendor_url: v_url,
                var_names.source: v_src,
                var_names.event_type: v_ev_type,
                var_names.status: v_ev_sts,
                var_names.event_by: v_ev_by,
                var_names.additional_info: addn_info
            })
        return data
    except psycopg2.DatabaseError:
        raise


def list_instance_change_events(conn, timestamp, organization_id, user_id, instance_id):
    '''
    List the change events related to an incident.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to get the events for
    :param user_id: (int) ID of the user who is requesting to view the changes; necessary for advanced permissions
    :param instance_id: (int) instance ID to filter by
    :return: (list) of dict of change event details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    assert isinstance(instance_id, int)

    query = " select * from list_instance_change_events(%s, %s, %s, %s); "
    query_params = (timestamp, organization_id, user_id, instance_id)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for ev_id, ev_tmsp, srv_id, srv_ref, srv_name, itt_type, itt_name, itt_icon_url, tm_ref, tm_name, title, desc,\
                v_url, v_id, v_src, v_ev_type, v_ev_sts, v_ev_by, addn_info, min_buff, is_dep in result:

            data.append({
                var_names.event_id: ev_id,
                var_names.event_timestamp: ev_tmsp,
                var_names.service_id: srv_id,
                var_names.services: [[srv_name, key_manager.conceal_reference_key(srv_ref)]]
                if srv_name is not None and srv_ref is not None else None,
                var_names.integrations: [itt_name, itt_type] if itt_name is not None and itt_type is not None else None,
                var_names.teams: [[tm_name, key_manager.conceal_reference_key(tm_ref)]]
                if tm_name is not None and tm_ref is not None else None,
                var_names.icon_url: itt_icon_url,
                var_names.title: title,
                var_names.description: desc,
                var_names.vendor_url: v_url,
                var_names.source: v_src,
                var_names.event_type: v_ev_type,
                var_names.status: v_ev_sts,
                var_names.event_by: v_ev_by,
                var_names.additional_info: addn_info,
                var_names.minutes_buffer: min_buff,
                var_names.dependencies: is_dep
            })
        return data
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            return []
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_change_event(conn, timestamp, event_id, organization_id, user_id):
    '''
    Delete a change event. The event is completely purged from the database.
    Only users with admin rights will be able to delete a change event.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param event_id: ID of the event
    :param organization_id: ID of the organization
    :param user_id: ID of the user deleting the event
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(event_id, int)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    query = ' select redact_change_event(%s, %s, %s, %s); '
    query_params = (timestamp, event_id, organization_id, user_id)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise