# By: Riasat Ullah

# This file contains all queries relating to events
# that happen for an instance.

from dbqueries import db_policies
from exceptions.user_exceptions import InvalidRequest, NoOneIsOnCall
from objects.events import AcknowledgeEvent, AddConferenceBridgeEvent, AddImpactedBusinessServiceEvent, \
    AddRespondersEvent, AddSubscribersEvent, CallAnsweredEvent, CallEndedEvent, CallForwardingEvent, \
    CallOutgoingEvent, CallVoicemailPrompt, CustomActionEvent, DispatchEvent, EditTitleEvent, EscalateEvent, \
    MergeEvent, NotateEvent, ReassignEvent, RemoveImpactedBusinessServiceEvent, RemoveSubscribersEvent, \
    ResolveEvent, RunWorkflowEvent, SendChatMessageEvent, SendExternalEmailEvent, SendExternalSmsEvent, SnoozeEvent, \
    StatusUpdateEvent, TriggerEvent, UnAcknowledgeEvent, UnMergeEvent, UpdateTagsEvent, UrgencyAmendmentEvent
from objects.instance_state import InstanceState
from psycopg2 import errorcodes
from utils import constants, errors, helpers, times, var_names
import configuration
import datetime
import json
import psycopg2


def book_trigger_events(conn, triggers):
    '''
    Update the last_alert_timestamp and next_alert_timestamp of open instances.
    :param conn: db connection
    :param triggers: (list) of TriggerEvents
    :errors: DatabaseError
    '''
    query = '''
            do
            $body$
            begin

            update task_instances set status = %(open_status)s,
                last_alert_timestamp = %(event_time)s,
                next_alert_timestamp = %(next_alert)s
            where instanceid = %(inst_id)s
                and is_open = true;

            insert into instance_events
            (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
            values (
                %(inst_id)s, %(event_date)s, %(event_time)s, current_timestamp,
                %(event_type)s, %(event_method)s, %(event_log)s
            );

            end;
            $body$
            '''
    query_params_list = []
    for event in triggers:
        assert isinstance(event, TriggerEvent)
        query_params_list.append({
            'inst_id': event.instance_id, 'open_status': constants.open_state,
            'event_date': event.event_timestamp.date(), 'event_time': event.event_timestamp,
            'event_type': event.__str__(), 'event_method': constants.internal,
            'next_alert': event.next_alert_timestamp, 'event_log': event.db_log()
        })
    try:
        conn.execute_batch(query, query_params_list)
    except psycopg2.DatabaseError:
        raise


def book_acknowledge_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books an acknowledge event in the database.
    :param conn: db connection
    :param event: AcknowledgeEvent object
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (timestamp) the updated next alert timestamp
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, AcknowledgeEvent)
    assert org_id is None or isinstance(org_id, int)

    # The following checks should be done before calling this function:
    #   - the instance must still be open
    #   - the user acknowledging must be allowed to acknowledge
    query = '''
            select acknowledge_task(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(), constants.end_timestamp,
                    constants.acknowledged_state, event.__str__(), event.event_method,
                    event.db_log(), event.event_by, configuration.standard_wait_minutes,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_resolve_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Book a task resolution event in the database. If the instance is of a non-repeating
    task, then it will also end date the task for good. It returns the notice type the
    assignee expects after the task is resolved.
    :param conn: db connection
    :param event: ResolveEvent object
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (list of int) of instance IDs that were resolved (including merged instances)
            | None of no instance IDs were resolved
    :errors: AssertionError, DatabaseError, SqlInjection, LookupError
    '''
    assert isinstance(event, ResolveEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select resolve_task(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp.date(), event.event_timestamp, constants.resolved_state,
                    event.__str__(), event.event_method, event.db_log(), event.event_by,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_dispatch_events(conn, dispatches):
    '''
    Books a list of dispatch events. Only updates the last_notification_timestamp for instance assignments.
    Dispatches have a pre-condition - they expect instance assignment entries
    to be already made. They do not create any assignment entries themselves.
    :param conn: db connection
    :param dispatches: (list) of DispatchEvents
    '''
    assert isinstance(dispatches, list)

    # Instance assignment entries are not created from here. They should have already been created by other events.
    # Dispatch events are not booked in instance_events.
    query = '''
            update instance_assignments set last_notification_timestamp = %(event_time)s
            where instanceid = %(inst_id)s
                and start_timestamp <= %(event_time)s
                and end_timestamp > %(event_time)s
                and user_policyid = %(u_pid)s;
            '''
    query_params_list = []
    for event in dispatches:
        assert isinstance(event, DispatchEvent)

        query_params_list.append(
            {'inst_id': event.instance_id, 'u_pid': event.dispatched_to[0], 'event_time': event.event_timestamp}
        )
    try:
        conn.execute_batch(query, query_params_list)
    except psycopg2.DatabaseError:
        raise


def book_escalate_events(conn, escalations, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books an escalate event into the database.
    :param conn: db connection
    :param escalations: (list) of tuples (EscalateEvent, list[Assignees])
                        All assignees must be new user assignees. No checks for correct users
                        at the correct level is done here.
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    ***
        This function does not check if the user making the escalation is
        currently an assignee of the task or not since internal bulk escalations
        are also done through this function. For external escalations please
        do the check prior to using this function.
    ***
    '''
    assert isinstance(escalations, list)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select escalate_task(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params_list = []
    for item in escalations:
        event, new_assignees = item
        assert isinstance(event, EscalateEvent)

        # all assignees must be the new user assignees the instance is being escalated to
        user_struct = []
        for assignee in new_assignees:
            user_struct.append([
                {var_names.user_policyid: assignee.user_policy_id, var_names.for_policyid: assignee.for_policy_id}
            ])

        query_params_list.append(
            (is_sys_action, has_comp_perm, has_team_perm, event.instance_id, org_id,
             event.next_alert_timestamp, event.event_timestamp, event.event_timestamp.date(), constants.open_state,
             event.escalate_to_level, event.escalate_for, json.dumps(user_struct), constants.end_timestamp,
             event.__str__(), event.event_method, event.db_log(), event.event_by,)
        )
    try:
        conn.execute_batch(query, query_params_list)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_bulk_system_escalate_events(conn, timestamp, escalations):
    '''
    Books an escalate event into the database.
    :param conn: db connection
    :param timestamp: timestamp when this call is being made
                    This will be used to fetch the current assignees after the escalations have been booked.
                    This timestamp should really be greater than all other event timestamps.
    :param escalations: (list) of tuples (EscalateEvent, list[Assignees])
                        All assignees must be new user assignees. No checks for correct users
                        at the correct level is done here.
    :return: (dict) -> {instance ID: list of instance assignees}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(escalations, list)

    esc_struct = []
    all_inst_ids = []
    for item in escalations:
        event, new_assignees = item
        assert isinstance(event, EscalateEvent)

        if timestamp < event.event_timestamp:
            timestamp = event.event_timestamp

        # all assignees must be the new user assignees the instance is being escalated to
        user_struct = []
        for assignee in new_assignees:
            user_struct.append([
                {var_names.user_policyid: assignee.user_policy_id, var_names.for_policyid: assignee.for_policy_id}
            ])

        esc_struct.append({
            var_names.instance_id: event.instance_id,
            var_names.next_alert_timestamp: event.next_alert_timestamp,
            var_names.event_timestamp: event.event_timestamp,
            var_names.event_date: event.event_timestamp.date(),
            var_names.status: constants.open_state,
            var_names.escalate_to_level: event.escalate_to_level,
            var_names.for_policyid: event.escalate_for,
            var_names.assignees: json.dumps(user_struct),
            var_names.end_timestamp: constants.end_timestamp,
            var_names.event_type: event.__str__(),
            var_names.event_method: event.event_method,
            var_names.event_log: event.db_log()
        })

        all_inst_ids.append(event.instance_id)

    query = "select * from bulk_system_escalate_task(%s, %s, %s);"
    query_params = (timestamp, json.dumps(esc_struct, default=helpers.jsonify_unserializable), all_inst_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for inst_id, inst_assignees in result:
            data[inst_id] = InstanceState.create_instance_assignees(inst_assignees)
        return data
    except psycopg2.DatabaseError:
        raise


def book_snooze_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a snooze event in the database.
    :param conn: db connection
    :param event: SnoozeEvent object
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, LookupError
    '''
    assert isinstance(event, SnoozeEvent)
    assert org_id is None or isinstance(org_id, int)

    wait_till = event.event_timestamp + datetime.timedelta(minutes=event.snooze_for)

    query = '''
            select snooze_task(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(), constants.end_timestamp,
                    constants.acknowledged_state, wait_till, event.__str__(),
                    event.event_method, event.db_log(), event.event_by,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_reassign_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a re-assignment event. A re-assignment can be
    done to multiple policies at the same time.
    :param conn: db connection
    :param event: ReassignEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (list of dict) -> new instance assignments
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, ReassignEvent)
    assert org_id is None or isinstance(org_id, int)

    # get the policies of the re-assignees
    policies = db_policies.get_policies(conn, times.get_current_timestamp(), with_policyid=event.reassign_to)

    # Make sure that the event timestamp is passed to get the on-call. Otherwise the function will use the
    # current UTC time which could produce different results in non-prod environment due to different routine timezones.
    user_list = []
    for id_ in event.reassign_to:
        assignee_policy = policies[id_]
        on_call_user_policies = assignee_policy.get_on_call(level_number=1, check_datetime=event.event_timestamp)
        for on_call in on_call_user_policies:
            user_list.append({var_names.user_policyid: on_call[2],
                              var_names.for_policyid: id_})

    if len(user_list) == 0:
        raise NoOneIsOnCall(errors.err_incident_no_one_on_call)

    query = '''
            select reassign_task(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(), constants.end_timestamp,
                    constants.open_state, json.dumps(user_list), event.__str__(), event.event_method,
                    event.db_log(), event.event_by,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_add_responders_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a re-assignment event. A re-assignment can be
    done to multiple policies at the same time.
    :param conn: db connection
    :param event: ReassignEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (list of dict) -> current instance assignments
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, AddRespondersEvent)
    assert org_id is None or isinstance(org_id, int)

    # Make sure that the event timestamp is passed to get the on-call. Otherwise the function will use the
    # current UTC time which could produce different results in non-prod environment due to different routine timezones.
    policies = db_policies.get_policies(conn, times.get_current_timestamp(), with_policyid=event.new_responders)
    user_list = []
    for id_ in event.new_responders:
        assignee_policy = policies[id_]
        on_call_user_policies = assignee_policy.get_on_call(level_number=1, check_datetime=event.event_timestamp)
        for on_call in on_call_user_policies:
            user_list.append({var_names.user_policyid: on_call[2],
                              var_names.for_policyid: id_})

    if len(user_list) == 0:
        raise NoOneIsOnCall(errors.err_incident_no_one_on_call)

    query = '''
            select add_task_responders(
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(),
                    constants.end_timestamp, json.dumps(user_list), event.__str__(),
                    event.event_method, event.db_log(), event.event_by,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_un_acknowledge_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books an un-acknowledge event. Only acknowledged instances can be un'acked.
    :param conn: db connection
    :param event: UnAcknowledgeEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, UnAcknowledgeEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select un_acknowledge_task(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (
        is_sys_action, has_comp_perm, has_team_perm, event.instance_id, org_id,
        event.next_alert_timestamp, event.event_timestamp, event.event_timestamp.date(), constants.open_state,
        event.__str__(), event.event_method, event.db_log(), event.event_by,
    )
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_urgency_amendment_events(conn, amendments: list, org_id=None, has_comp_perm=False, has_team_perm=False,
                                  is_sys_action=False):
    '''
    Books an urgency amendment event.
    :param conn: db connection
    :param amendments: (list) of UrgencyAmendmentEvents
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select amend_task_urgency(
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''

    query_params_list = []
    for event in amendments:
        assert isinstance(event, UrgencyAmendmentEvent)
        query_params_list.append(
            (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
             org_id, event.event_timestamp, event.event_timestamp.date(),
             constants.end_timestamp, event.new_urgency, event.__str__(),
             event.event_method, event.db_log(), event.event_by,)
        )
    try:
        if len(query_params_list) == 1:
            conn.execute(query, query_params_list[0])
        else:
            conn.execute_batch(query, query_params_list)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        elif e.pgcode == errorcodes.RESTRICT_VIOLATION:
            raise InvalidRequest(errors.err_pre_scheduled_alert_urgency_amendment)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_notate_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a notate event.
    :param conn: db connection
    :param event: NotateEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, NotateEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select notate_task(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(), event.notes,
                    event.__str__(), event.event_method, event.db_log(), event.event_by,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_merge_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a merge event.
    :param conn: db connection
    :param event: MergeEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (int) task id of the related instance
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, MergeEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select merge_task(
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    event.related_instance_id, org_id, event.event_timestamp,
                    event.event_timestamp.date(), constants.end_timestamp, event.__str__(),
                    event.event_method, event.db_log(), event.event_by,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.InternalError as e:
        if e.pgcode == errorcodes.NO_DATA_FOUND:
            raise LookupError(errors.err_pre_scheduled_alert_incident_cannot_merge)
    except psycopg2.DatabaseError:
        raise


def book_un_merge_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a merge event.
    :param conn: db connection
    :param event: MergeEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :return: (int) instance ID of the new instance that was created
    :errors: AssertionError, DatabaseError, ValueError, LookupError
    '''
    assert isinstance(event, UnMergeEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select un_merge_task(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id, event.split_task_id,
                    org_id, event.event_timestamp, event.event_timestamp.date(), constants.end_timestamp,
                    event.__str__(), event.event_method, event.db_log(), event.event_by,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_incident_un_merge)
        else:
            raise
    except psycopg2.InternalError as e:
        if e.pgcode == errorcodes.NO_DATA_FOUND:
            raise LookupError(errors.err_incident_not_found)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_status_update_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a status update event.
    :param conn: db connection
    :param event: StatusUpdateEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, StatusUpdateEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            if %s or has_instance_access(%s, %s, %s, %s, %s, %s) then

                insert into instance_updates values(%s, %s, %s, %s);

                insert into instance_events
                (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
                values
                (%s, %s, %s, current_timestamp, %s, %s, %s);

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = (is_sys_action, event.instance_id, org_id, event.event_by, event.event_timestamp,
                    has_comp_perm, has_team_perm, event.instance_id, event.event_timestamp,
                    event.event_by, event.update,
                    event.instance_id, event.event_timestamp.date(), event.event_timestamp,
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_add_subscribers_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books an AddSubscribers event.
    :param conn: db connection
    :param event: AddSubscribers Event
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, AddSubscribersEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select add_task_subscribers(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id, org_id,
                    event.event_timestamp, event.event_by, constants.end_timestamp, event.subscribers,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_add_impacted_business_service_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                             is_sys_action=False):
    '''
    Books an AddSubscribers event.
    :param conn: db connection
    :param event: AddSubscribers Event
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, AddImpactedBusinessServiceEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select add_impacted_business_service(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_by, constants.end_timestamp,
                    event.business_service_id, False if event.event_by is None else True,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_add_conference_bridge_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                     is_sys_action=False):
    '''
    Adds a conference bridge to an instance.
    :param conn: db connection
    :param event: AddConferenceBridgeEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, AddConferenceBridgeEvent)
    assert org_id is None or isinstance(org_id, int)
    evn_det = json.dumps(event.details) if event.details is not None else None

    query = '''
            do
            $body$
            begin

            if %s or has_instance_access(%s, %s, %s, %s, %s, %s) then

                update instance_conferences set end_timestamp = %s
                where start_timestamp <= %s
                    and end_timestamp > %s
                    and instanceid = %s;

                insert into instance_conferences values(%s, %s, %s, %s, %s, %s);

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = (is_sys_action, event.instance_id, org_id, event.event_by,
                    event.event_timestamp, has_comp_perm, has_team_perm, event.event_timestamp,
                    event.event_timestamp, event.event_timestamp, event.instance_id, event.instance_id,
                    event.event_timestamp, constants.end_timestamp, event.phone, event.url,
                    evn_det,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_remove_subscribers_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                  is_sys_action=False):
    '''
    Books a RemoveSubscribers event.
    :param conn: db connection
    :param event: RemoveSubscribers Event
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, RemoveSubscribersEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select remove_task_subscribers(
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_by, event.subscribers,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_remove_impacted_business_service_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                                is_sys_action=False):
    '''
    Books an RemoveImpactedBusinessService event.
    :param conn: db connection
    :param event: RemoveImpactedBusinessService Event
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, RemoveImpactedBusinessServiceEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select remove_impacted_business_service(
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.event_by, event.business_service_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_custom_action_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Logs a custom action event for an instance. For example, Jira issue syncing action, Rundeck action, etc.
    :param conn: db connection
    :param event: CustomAction Event
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CustomActionEvent)
    assert org_id is None or isinstance(org_id, int)
    addn_info = json.dumps(event.additional_info) if event.additional_info is not None else None

    query = '''
            select log_custom_action(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s
            );
            '''
    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.instance_id,
                    org_id, event.event_timestamp, event.__str__(), event.event_method,
                    event.db_log(), event.event_by, event.is_synced, event.integration_id,
                    event.integration_type_id, event.vendor_id, event.vendor_url, addn_info,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_edit_title_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Books a status update event.
    :param conn: db connection
    :param event: EditTitleEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, EditTitleEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$

            declare t_id tasks.taskid%%type;

            begin

            if %(sys_act)s or has_instance_access(
                %(inst_id)s, %(org_id)s, %(evn_by)s, %(timestamp)s, %(comp_perm)s, %(team_perm)s
            ) then

                update tasks set end_timestamp = %(timestamp)s, current_version = false
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and not pre_scheduled
                    and organization_id = %(org_id)s
                    and taskid in (
                        select taskid from task_instances
                        where instanceid = %(inst_id)s
                    )
                returning taskid into t_id;

                if t_id is not null then
                    insert into tasks (
                        select taskid, %(timestamp)s, %(end_time)s, trigger_method, trigger_info, organization_id,
                            task_ref_id, pre_scheduled, created_by, %(new_title)s, task_timezone, task_time, repeat,
                            text_msg, true, urgency_level, task_start_date, instantiate, alert, serviceid,
                            integration_id, api_key_id, routing_id, related_taskid, task_status
                        from tasks
                        where start_timestamp <= %(timestamp)s
                            and end_timestamp = %(timestamp)s
                            and not pre_scheduled
                            and organization_id = %(org_id)s
                            and taskid = t_id
                    );

                    insert into instance_events (
                        instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log
                    ) values (
                        %(inst_id)s, %(evn_date)s, %(timestamp)s, current_timestamp,
                        %(evn_type)s, %(evn_method)s, %(evn_log)s
                    );
                end if;

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''

    query_params = {'sys_act': is_sys_action, 'inst_id': event.instance_id, 'org_id': org_id,
                    'evn_by': event.event_by, 'timestamp': event.event_timestamp, 'comp_perm': has_comp_perm,
                    'team_perm': has_team_perm, 'new_title': event.task_title, 'end_time': constants.end_timestamp,
                    'evn_date': event.event_timestamp.date(), 'evn_type': event.__str__(),
                    'evn_method': event.event_method, 'evn_log': event.db_log()}
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_update_tags_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False, is_sys_action=False):
    '''
    Updates instance tags.
    :param conn: db connection
    :param event: UpdateTagsEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, UpdateTagsEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select update_task_tags(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            )
            '''

    query_params = (is_sys_action, has_comp_perm, has_team_perm, event.event_timestamp, constants.end_timestamp,
                    event.event_by, event.instance_id, org_id, event.tags,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_call_forwarding_event(conn, event, org_id):
    '''
    Books a live call forwarding event in the database.
    :param conn: db connection
    :param event: CallForwardingEvent object
    :param org_id: ID of the organization the instance belongs to
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CallForwardingEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            update live_call_logs set forwarding_count = %s
            where instanceid = %s
                and organization_id = %s;

            insert into instance_events
            (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
            values
            (%s, %s, %s, current_timestamp, %s, %s, %s);

            end;
            $body$
            '''
    query_params = (event.forwarding_count, event.instance_id, org_id,
                    event.instance_id, event.event_timestamp.date(), event.event_timestamp,
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def book_call_answered_event(conn, event, org_id):
    '''
    Books an live call answered event in the database.
    :param conn: db connection
    :param event: CallAnsweredEvent object
    :param org_id: ID of the organization the instance belongs to
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CallAnsweredEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select answer_live_call(
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (event.instance_id, org_id, event.event_timestamp, event.event_timestamp.date(),
                    constants.end_timestamp, constants.answered_state, constants.acknowledged_state,
                    event.__str__(), constants.acknowledge_event, event.event_method,
                    event.db_log(), event.event_by, configuration.standard_wait_minutes,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_call_voicemail_prompt_event(conn, event, org_id, call_status, call_duration):
    '''
    Books a live call voice mail prompt event in the database.
    :param conn: db connection
    :param event: CallVoicemailPromptEvent object
    :param org_id: ID of the organization the instance belongs to
    :param call_status: the status of the call
    :param call_duration: duration of the call as indicated by the vendor
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CallVoicemailPrompt)
    assert isinstance(org_id, int)
    if call_status is not None:
        assert call_status in configuration.allowed_live_call_statuses
    if call_duration is not None:
        assert isinstance(call_duration, int)

    query = '''
            begin;

            update live_call_logs set call_end_timestamp = %s,
                vendor_call_duration = %s,
                call_status = %s
            where instanceid = %s
                and organization_id = %s;

            insert into instance_events
            (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
            values
            (%s, %s, %s, current_timestamp, %s, %s, %s);

            end;
            '''
    query_params = (event.event_timestamp, call_duration, call_status, event.instance_id, org_id,
                    event.instance_id, event.event_timestamp.date(), event.event_timestamp,
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def book_call_ended_event(conn, event, org_id):
    '''
    Books a live call ended event in the database.
    :param conn: db connection
    :param event: CallEndedEvent object
    :param org_id: ID of the organization the instance belongs to
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CallEndedEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            select end_live_call(
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s
            );
            '''
    query_params = (event.instance_id, org_id, event.event_timestamp, event.event_timestamp.date(),
                    event.__str__(), event.event_method, event.db_log(), event.call_duration,
                    event.call_status, event.voicemail_url,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def book_call_outgoing_event(conn, event, org_id):
    '''
    Books a live call forwarding event in the database.
    :param conn: db connection
    :param event: CallOutgoingEvent object
    :param org_id: ID of the organization the instance belongs to
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, CallOutgoingEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            insert into instance_events
            (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
            values
            (%s, %s, %s, current_timestamp, %s, %s, %s);

            end;
            $body$
            '''
    query_params = (event.instance_id, event.event_timestamp.date(), event.event_timestamp,
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def link_instance(conn, timestamp, org_id, instance_id, integration_type, vendor_id, user_id=None, addn_info=None):
    '''
    Links an incident with an external vendor's incident equivalent to primarily follow the incident.
    :param conn: db connection
    :param timestamp: timestamp when the instance is being linked
    :param org_id: ID of the organization the instance belongs to
    :param instance_id: ID of the instance to link with
    :param integration_type: the type of integration e.g - ZENDESK
    :param vendor_id: vendor provided ID of the item the instance is linked with
    :param user_id: ID of the user
    :param addn_info: additional info
    :errors: AssertionError, DatabaseError
    '''
    assert org_id is None or isinstance(org_id, int)
    assert isinstance(instance_id, int)
    assert integration_type in configuration.allowed_linkable_integrations
    assert isinstance(vendor_id, str)
    if user_id is not None:
        assert isinstance(user_id, int)
    if addn_info is not None:
        assert isinstance(addn_info, dict)

    query = '''
            do
            $body$

            declare
                integ_typ_id smallint := (
                    select integration_type_id from integration_types
                    where start_date <= %(tmsp)s
                        and end_date > %(tmsp)s
                        and integration_type = %(integ_typ)s
                );

            begin

            if exists (
                select 1 from task_instances
                where instance_timestamp <= %(tmsp)s
                    and organization_id = %(org_id)s
                    and instanceid = %(inst_id)s
                    and is_open = true
            ) and not exists (
                select 1 from linked_instances
                where integration_type_id = integ_typ_id
                    and linked_id = %(vnd_id)s
                    and instanceid = %(inst_id)s
            ) then
                insert into linked_instances values (
                    %(inst_id)s, %(tmsp)s, integ_typ_id, %(usr_id)s, %(vnd_id)s, %(addn_info)s
                );
            end if;

            end;
            $body$
            '''
    query_params = {'tmsp': timestamp, 'org_id': org_id, 'integ_typ': integration_type, 'vnd_id': vendor_id,
                    'inst_id': instance_id, 'usr_id': user_id, 'addn_info': json.dumps(addn_info)}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def unlink_instance(conn, timestamp, org_id, instance_id, integration_type, vendor_id):
    '''
    Unlinks an incident with an external vendor's incident equivalent to primarily follow the incident.
    :param conn: db connection
    :param timestamp: timestamp when the instance is being linked
    :param org_id: ID of the organization the instance belongs to
    :param instance_id: ID of the instance to link with
    :param integration_type: the type of integration e.g - ZENDESK
    :param vendor_id: vendor provided ID of the item the instance is linked with
    :errors: AssertionError, DatabaseError
    '''
    assert org_id is None or isinstance(org_id, int)
    assert isinstance(instance_id, int)
    assert integration_type in configuration.allowed_linkable_integrations
    assert isinstance(vendor_id, str)

    query = '''
            do
            $body$

            declare
                integ_typ_id smallint := (
                    select integration_type_id from integration_types
                    where start_date <= %(tmsp)s
                        and end_date > %(tmsp)s
                        and integration_type = %(integ_typ)s
                );

            begin

            if exists (
                select 1 from task_instances
                where instance_timestamp <= %(tmsp)s
                    and organization_id = %(org_id)s
                    and instanceid = %(inst_id)s
                    and is_open = true
            ) then

                delete from linked_instances
                where integration_type_id = integ_typ_id
                    and linked_id = %(vnd_id)s
                    and instanceid = %(inst_id)s;

            end if;

            end;
            $body$
            '''
    query_params = {'tmsp': timestamp, 'org_id': org_id, 'integ_typ': integration_type,
                    'vnd_id': vendor_id, 'inst_id': instance_id}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def book_run_workflow_event(conn, event):
    '''
    Book a run workflow event. This is merely a booking for record keeping. The actual actions that
    need to be performed, has to be handled manually by the workflow manager.
    :param conn: db connection
    :param event: RunWorkflowEvent
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, RunWorkflowEvent)

    query = '''
            begin;

            insert into instance_workflows values(%s, %s, %s, %s);

            insert into instance_events
            (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
            values (
                %s, %s, %s, %s, %s, %s, %s
            );

            end;
            '''
    query_params = (event.instance_id, event.workflow_id, event.event_timestamp, event.event_by,
                    event.instance_id, event.event_timestamp.date(), event.event_timestamp,
                    times.get_current_timestamp(), event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_send_external_email_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                   is_sys_action=False):
    '''
    Book send external email event.
    :param conn: db connection
    :param event: SendExternalEmailEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, SendExternalEmailEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            if %s or has_instance_access(%s, %s, %s, %s, %s, %s) then

                insert into instance_events
                (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
                values (
                    %s, %s, %s, %s, %s, %s, %s
                );

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = (is_sys_action, event.instance_id, org_id, event.event_by,
                    event.event_timestamp, has_comp_perm, has_team_perm, event.instance_id,
                    event.event_timestamp.date(), event.event_timestamp, times.get_current_timestamp(),
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_send_external_sms_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                 is_sys_action=False):
    '''
    Book send external SMS event.
    :param conn: db connection
    :param event: SendExternalEmailEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, SendExternalSmsEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            if %s or has_instance_access(%s, %s, %s, %s, %s, %s) then

                insert into instance_events
                (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
                values (
                    %s, %s, %s, %s, %s, %s, %s
                );

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = (is_sys_action, event.instance_id, org_id, event.event_by,
                    event.event_timestamp, has_comp_perm, has_team_perm, event.instance_id,
                    event.event_timestamp.date(), event.event_timestamp, times.get_current_timestamp(),
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def book_send_chat_message_event(conn, event, org_id=None, has_comp_perm=False, has_team_perm=False,
                                 is_sys_action=False):
    '''
    Book send chat message event.
    :param conn: db connection
    :param event: SendChatMessageEvent
    :param org_id: ID of the organization the instance belongs to
    :param has_comp_perm: (boolean) does the organization have advanced permissions
    :param has_team_perm: (boolean) does the organization have team permissions
    :param is_sys_action: (boolean) is this action being automatically performed by the system or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(event, SendChatMessageEvent)
    assert org_id is None or isinstance(org_id, int)

    query = '''
            do
            $body$
            begin

            if %s or has_instance_access(%s, %s, %s, %s, %s, %s) then

                insert into instance_events
                (instanceid, event_date, event_timestamp, db_timestamp, event_type, event_method, event_log)
                values (
                    %s, %s, %s, %s, %s, %s, %s
                );

            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = (is_sys_action, event.instance_id, org_id, event.event_by,
                    event.event_timestamp, has_comp_perm, has_team_perm, event.instance_id,
                    event.event_timestamp.date(), event.event_timestamp, times.get_current_timestamp(),
                    event.__str__(), event.event_method, event.db_log(),)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_user_action)
        else:
            raise
    except psycopg2.DatabaseError:
        raise