# By: Riasat Ullah

# This file contains all functions that will be needed
# for task related queries. It only has functions to
# work with the root task; not the instances of the task.

from exceptions.user_exceptions import NoOneIsOnCall
from objects.task import Task
from utils import constants, errors, helpers, key_manager, times, var_names
from validations import task_validator
import configuration
import datetime
import json
import psycopg2
import uuid


def create_task(conn, timestamp, organization_id, start_date, title, timezone, task_time, service_id=None,
                service_policy_id=None, assignees=None, created_by=None, repeat_on=[], text_msg=None,
                urgency_level=configuration.default_urgency_level, trigger_method=constants.email, trigger_info=None,
                instantiate=True, alert=True, integration_id=None, api_key_id=None, routing_id=None,
                related_task_id=None, task_status=None, notes=None, tags=None, dedup_key=None, pre_scheduled=False,
                amended_urgency=None, next_alert_timestamp=None, status_update=None, subscribers=None,
                conference_bridge=None, impacted_business_services=None, instance_assignments=None, instance_level=1):
    '''
    Creates a new task in the database.
    :param conn: db connection
    :param timestamp: timestamp when the task was created
    :param organization_id: ID of the organization this task is for
    :param start_date: date the task should start from
    :param title: title of the task
    :param timezone: timezone the task is being created for
    :param task_time: time the task should trigger at in the format HH:MM
    :param service_id: ID of the service this task has been associated to
    :param service_policy_id: policy ID the service is associated to
    :param assignees: (list of int) of policy ids of assignees (from service, users, groups)
    :param created_by: (can be None) user_id of the user who created the task
    :param repeat_on: (list) of days the task should be repeated on
    :param text_msg: text message of the task
    :param urgency_level: urgency level of the task
    :param trigger_method: the method used to trigger the task creation [APP, EMAIL, API, etc]
    :param trigger_info: additional information that may have been provided with the trigger
    :param instantiate: (boolean) True if instances should be created for this task
    :param alert: (boolean) True if alerts should be generated from the instances created this task
    :param integration_id: ID of the integration that created the task
    :param api_key_id: ID of the API key that created the instance
    :param routing_id: conditional routing ID
    :param related_task_id: ID of any other task this task is related to. This will be useful for grouping/suppressing
    :param task_status: a status of the task - GROUPED, SUPPRESSED, etc
    :param notes: any notes that were added to the task
    :param tags: list of tags to help classify a task
    :param dedup_key: a key to stop de-duplication; if this key is present in active task instance, then instances
                    will not be created for subsequent tasks containing the key
    :param pre_scheduled: True if the task is pre_scheduled; False otherwise
    :param amended_urgency: (integer) if priority is not internally changed then this stays as None
    :param next_alert_timestamp: timestamp the alert should be re-triggered next
    :param status_update: (str) status update to add to the instance
    :param subscribers: (list of user_ids) subscribers to add to the instance
    :param conference_bridge: (dict) details of conference bridge to add to the instance
    :param impacted_business_services: (list of int) IDs of business services that will eb impacted by this instance
    :param instance_assignments: (list) of dict -> [{user_policyid: usr_pol, for_policyid: for_pid}, ...]
    :param instance_level: (int) the level the instance assignments are at
    :return: (tuple) -> (task ID, inst ID, org inst ID, inst assignees)
            if task is pre-scheduled or is not to be instantiated then inst ID and inst assignees will be None
    :errors: AssertionError, DatabaseError, ValueError
    '''
    task_validator.validate_task_details(timestamp, start_date, title, timezone, task_time, repeat_on, text_msg,
                                         urgency_level, organization_id, created_by, instantiate, alert, integration_id,
                                         api_key_id, routing_id, related_task_id, task_status, notes, tags, dedup_key,
                                         service_id, service_policy_id, assignees, amended_urgency,
                                         next_alert_timestamp, status_update, subscribers, conference_bridge,
                                         impacted_business_services)
    if start_date is None:
        # allowing datetime.datetime here although in the validation we are accepting datetime.date only
        start_date = times.get_current_timestamp()

    assert trigger_method in configuration.allowed_trigger_methods
    if trigger_info is not None:
        assert isinstance(trigger_info, dict)

    # Only if an instance needs to be created immediately with the task.
    # Instance assignments and the next alert timestamp should be provided by the data syncer.
    if not pre_scheduled and instantiate:
        assert next_alert_timestamp is not None
        assert isinstance(instance_level, int)
        if instance_assignments is None or len(instance_assignments) == 0:
            instantiate = False
            task_status = constants.suppressed_state

    task_labels = []
    if tags is not None:
        for label in tags:
            task_labels.append({var_names.label_type: var_names.tags, var_names.labels: label})

    if dedup_key is not None:
        task_labels.append({var_names.label_type: var_names.dedup_key, var_names.labels: dedup_key})

    trigger_info_json = None if trigger_info is None else json.dumps(
        trigger_info, default=helpers.jsonify_unserializable)
    repeat_days = None if repeat_on is None or len(repeat_on) == 0 else repeat_on

    if isinstance(task_time, str):
        task_time = times.get_time_from_string(task_time)
    # we re-create the task time with hour and minutes to eliminate seconds.
    task_time = datetime.time(task_time.hour, task_time.minute)

    if pre_scheduled and repeat_days is None and not task_validator.task_time_is_forward_looking(timezone, task_time):
        raise ValueError(errors.err_pre_scheduled_alert_time_not_in_future)

    json_task_labels = None if task_labels is None or len(task_labels) == 0 else json.dumps(task_labels)
    json_inst_assignments = None if instance_assignments is None or len(instance_assignments) == 0\
        else json.dumps(instance_assignments)
    conference_bridge = json.dumps(conference_bridge) if conference_bridge is not None else None

    # All alerts including grouped alerts should be kept open except suppressed alerts and alerts
    # whose instances are meant to be resolved at the same time that they are created (due to conditional routing).
    end_time = constants.end_timestamp
    if related_task_id is None and not instantiate and not alert:
        # these are the conditions for suppression
        end_time = timestamp

    if task_status == constants.resolved_state:
        end_time = timestamp

    query = '''
            select * from create_task(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''

    query_params = (timestamp, end_time, trigger_method, trigger_info_json, organization_id,
                    key_manager.generate_reference_key(), pre_scheduled, created_by, title, timezone,
                    task_time, repeat_days, text_msg, urgency_level, start_date,
                    instantiate, alert, service_id, integration_id, api_key_id,
                    routing_id, related_task_id, task_status, assignees, json_task_labels,
                    instance_level, json_inst_assignments, notes, next_alert_timestamp, amended_urgency,
                    status_update, subscribers, conference_bridge, impacted_business_services,)
    try:
        result = conn.fetch(query, query_params)
        return result[0]
    except psycopg2.DatabaseError:
        raise


def edit_task(conn, user_id, timestamp, task_ref_id, start_date, title, timezone, task_time, service_id, assignees,
              repeat_on, text_msg, urgency_level):
    '''
    Edits the details of a task. This should be used for manual tasks only. As manual tasks are only editable
    from the app or website, only service id and assignees are accepted as parameters and not their references.
    :param conn: db connection
    :param user_id: user_id of the user creating the task
    :param timestamp: the timestamp when it is being created
    :param task_ref_id: (uuid.UUID) task reference id
    :param start_date: date the task should start from
    :param title: the title of the task
    :param timezone: the timezone for which the task is being made
    :param task_time: time the task should trigger at in the format HH:MM
    :param service_id: ID of the service this task is for
    :param assignees: (list of int) of policy ids of assignees (both users and groups)
    :param repeat_on: the days the task should be repeated on
    :param text_msg: text message for the task
    :param urgency_level: the urgency level of the task
    :errors: AssertionError, DatabaseError, SqlInjection, ValueError
    '''
    # Verify that the provided details are all in the correct format
    assert isinstance(user_id, int)
    task_validator.validate_task_details(timestamp, start_date, title, timezone, task_time, repeat_on,
                                         text_msg, urgency_level, service_id=service_id, assignees=assignees)

    if start_date is None:
        # allowing datetime.datetime here although in the validation we are accepting datetime.date only
        start_date = times.get_current_timestamp(as_str=True)

    unmasked_task_id = key_manager.unmask_reference_key(task_ref_id)
    repeat_days = None if repeat_on is None or len(repeat_on) == 0 else repeat_on

    if isinstance(task_time, str):
        task_time = times.get_time_from_string(task_time)
    # we re-create the task time with hour and minutes to eliminate seconds.
    task_time = datetime.time(task_time.hour, task_time.minute)

    if repeat_days is None and not task_validator.task_time_is_forward_looking(timezone, task_time):
        raise ValueError(errors.err_pre_scheduled_alert_time_not_in_future)

    query = '''
            select edit_task(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (unmasked_task_id, timestamp, constants.end_timestamp, user_id, title,
                    timezone, task_time, repeat_days, text_msg,
                    urgency_level, start_date, assignees if assignees is not None else None, service_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def delete_task(conn, timestamp, task_ref_id):
    '''
    Delete a task. Closes all entries respective to the levels and
    assignments of the task along with the same for any instance of
    the task that may be active at the time of this deletion.
    :param conn: db connection
    :param timestamp: the timestamp when this deletion is being requested
    :param task_ref_id: (uuid.UUID) task reference id
    :errors: AssertionError, ValueError, DatabaseError, SqlInjection
    '''
    assert isinstance(timestamp, datetime.datetime)

    query = 'select delete_task(null, %s, %s, true);'
    query_params = (key_manager.unmask_reference_key(task_ref_id), timestamp,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_task_owner(conn, timestamp, task_ref_id):
    '''
    Gets the user_id of the task owner.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param task_ref_id: id of the task
    :return: (str) user_id of the owner
    :errors: AssertionError, ValueError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select created_by from tasks
            where start_timestamp <= %s
                and end_timestamp > %s
                and task_ref_id = %s;
            '''
    query_params = (timestamp, timestamp, key_manager.unmask_reference_key(task_ref_id),)
    try:
        result = conn.fetch(query, query_params)
        if len(result) > 0:
            return result[0][0]
        else:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def get_user_tasks(conn, timestamp, user_id=None, task_ref_id=None):
    '''
    Get details of task(s).
    :param conn: db connection
    :param timestamp: timestamp to check for
    :param user_id: ID of the user who created the task
    :param task_ref_id: reference ID of the task
    :return: (dict) of Task object | or (list) of Task objects
    ::errors: AssertionError, DatabaseError, TypeError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert user_id is not None or task_ref_id is not None

    query_params = {'timestamp': timestamp}
    conditions = []

    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append(" created_by = %(usr_id)s ")
        query_params['usr_id'] = user_id

    if task_ref_id is not None:
        unmasked_ref_id = key_manager.unmask_reference_key(task_ref_id)
        conditions.append(" task_ref_id = %(t_ref)s ")
        query_params['t_ref'] = unmasked_ref_id

    query = '''
            with t1 as(
                select td.taskid, task_ref_id, task_start_date, created_by,
                    users.first_name || ' ' || users.last_name as display_name,
                    title, task_timezone, task_time, repeat, text_msg,
                    urgency_level, sd.service_ref_id, sd.service_name
                from tasks as td
                left join services as sd
                    on sd.serviceid = td.serviceid
                        and sd.start_timestamp <= td.start_timestamp
                        and sd.end_timestamp > td.start_timestamp
                left join users
                    on td.created_by = users.user_id
                        and users.start_timestamp <= td.start_timestamp
                        and users.end_timestamp > td.start_timestamp
                where td.start_timestamp <= %(timestamp)s
                    and td.end_timestamp > %(timestamp)s
                    and td.pre_scheduled = true
                    {0}
                order by taskid
            )
            , t2 as(
                select tap.taskid, json_agg(json_build_object(
                    'policy_ref_id', pd.policy_ref_id,
                    'policy_name', pd.policy_name
                )) as additional_policies
                from task_additional_policies as tap
                join policies as pd using(policyid)
                where pd.start_timestamp <= %(timestamp)s
                    and pd.end_timestamp > %(timestamp)s
                    and tap.start_timestamp <= %(timestamp)s
                    and tap.end_timestamp > %(timestamp)s
                    and tap.taskid in (select taskid from t1)
                group by tap.taskid
            )
            , t3 as(
                select taskid, json_agg(json_build_object(
                    'field_name', label_type,
                    'field_value', label
                )) as labels
                from task_labels
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and taskid in (select taskid from t1)
                group by taskid
            )
            select t1.task_ref_id, task_start_date, t1.display_name, title, task_timezone, task_time,
                repeat, text_msg, urgency_level, service_ref_id, service_name,
                t2.additional_policies, t3.labels
            from t1
            left join t2 using(taskid)
            left join t3 using(taskid);
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')

    try:
        result = conn.fetch(query, query_params)
        data = []

        for ref_id_, start_date, created_by_name, title, task_tz, task_time, repeat, text_msg, \
                urgency_level, serv_ref_id, serv_name, add_pol, labels in result:

            if add_pol is not None:
                for item in add_pol:
                    item[var_names.policy_ref_id] = key_manager.conceal_reference_key(
                        uuid.UUID(item[var_names.policy_ref_id]))

            data.append({
                var_names.task_ref_id: key_manager.conceal_reference_key(ref_id_),
                var_names.start_date: start_date,
                var_names.display_name: created_by_name,
                var_names.task_title: title,
                var_names.task_timezone: task_tz,
                var_names.task_time: task_time,
                var_names.repeat: repeat if repeat is not None else [],
                var_names.text_msg: text_msg,
                var_names.urgency_level: urgency_level,
                var_names.services: [serv_name, key_manager.conceal_reference_key(serv_ref_id)]
                if serv_ref_id is not None else None,
                var_names.assignee_policies: [[item[var_names.policy_name], item[var_names.policy_ref_id]]
                                              for item in add_pol] if add_pol is not None else None,
                var_names.labels: Task.standardize_labels(labels)
            })

        return data
    except psycopg2.DatabaseError:
        raise


def get_tasks_to_trigger(conn, timestamp, task_utc_time):
    '''
    Get details of task(s).
    :param conn: db connection
    :param timestamp: timestamp to check for
    :param task_utc_time: (datetime.time) UTC time the task is scheduled to trigger at
    :return: (list) of Task objects
    ::errors: AssertionError, DatabaseError, TypeError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(task_utc_time, datetime.time)

    # we re-create the task time with hour and minutes to eliminate seconds.
    task_utc_time = datetime.time(task_utc_time.hour, task_utc_time.minute)

    query = " select * from tasks_to_trigger(%s, %s); "
    query_params = (timestamp, task_utc_time,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for id_, org_id, start_date, task_timezone, task_time, repeat, service_policy_id, additional_policies in result:

            assignees = Task.create_assignees(service_policy_id, additional_policies)
            details = {var_names.task_id: id_,
                       var_names.organization_id: org_id,
                       var_names.start_date: start_date,
                       var_names.task_timezone: task_timezone,
                       var_names.task_time: task_time,
                       var_names.repeat: repeat}

            data.append(Task(id_, details, assignees))
        return data
    except psycopg2.DatabaseError:
        raise


def get_task_alerts(conn, timestamp, organization_id, status=None, service_ref_id=None, min_trigger_date=None,
                    instance_id=None, row_limit=None, row_offset=None, user_id=None, check_adv_perm=False):
    '''
    Gets task alerts given certain parameters. This function should not be used for getting task alerts
    for an instance; use the function "get_instance_task_alerts" for that.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param status: the status of the alert -> [GROUPED, SUPPRESSED, TRIGGERED]
    :param service_ref_id: reference ID of the service
    :param min_trigger_date: the oldest time the task
    :param instance_id: (int) ID of the instance
    :param row_limit: the maximum number of rows to retrieve
    :param row_offset: number of rows to skip
    :param user_id: (int) ID of the user whose permissions should be checked
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (list) of task details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    query_params = {'timestamp': timestamp, 'org_id': organization_id}

    # handle the conditions
    conditions = []

    if status is not None:
        if status == constants.triggered_state:
            conditions.append(" task_status is null ")
        else:
            assert status in configuration.allowed_task_statuses
            conditions.append(" task_status = %(stat)s ")
            query_params['stat'] = status

    if service_ref_id is not None:
        unmasked_ref_id = key_manager.unmask_reference_key(service_ref_id)
        conditions.append(''' serviceid in (
                                select serviceid from services
                                where start_timestamp <= %(timestamp)s
                                    and end_timestamp > %(timestamp)s
                                    and organization_id = %(org_id)s
                                    and service_ref_id = %(srv_ref_id)s
                          )''')
        query_params['srv_ref_id'] = unmasked_ref_id

    if min_trigger_date is not None:
        assert isinstance(min_trigger_date, datetime.date)
        conditions.append(" tasks.start_timestamp >= %(min_date)s ")
        query_params['min_date'] = min_trigger_date

    if instance_id is not None:
        assert isinstance(instance_id, int)
        conditions.append(''' (
            task_instances.instanceid = %(inst_id)s
            or related_taskid in (select taskid from task_instances where instanceid = %(inst_id)s and current_version)
        ) ''')
        query_params['inst_id'] = instance_id

    if check_adv_perm and user_id is not None:
        assert isinstance(user_id, int)
        conditions.append('''
            (
                user_policyid in (
                    select policyid from users
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and user_id = %(usr_id)s
                        and organization_id = %(org_id)s
                )
                or
                for_policyid not in (
                    select component_id from components_user_cannot_view(
                        %(timestamp)s, %(org_id)s, %(usr_id)s, %(pol_comp_type_id)s::smallint
                    )
                )
            )
            and (
                serviceid is null
                or (
                    serviceid is not null
                    and serviceid not in (
                        select component_id from components_user_cannot_view(
                            %(timestamp)s, %(org_id)s, %(usr_id)s, %(serv_comp_type_id)s::smallint
                        )
                    )
                )
            )
        ''')
        query_params['usr_id'] = user_id
        query_params['pol_comp_type_id'] = configuration.policy_component_type_id
        query_params['serv_comp_type_id'] = configuration.service_component_type_id

    # handle the limit conditions
    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 = '''
            with t1 as (
                select tasks.taskid, min(tasks.start_timestamp) as min_start
                from tasks
                left join task_instances using(taskid)
                left join instance_assignments using(instanceid)
                where tasks.organization_id = %(org_id)s
                    {0}
                group by taskid
                order by min_start desc
                {1}
            )
            select td.task_ref_id, td.title, td.text_msg, td.urgency_level, td.task_status, inst.instanceid,
                inst.organization_instanceid, inst.resolved_on, t1.min_start,
                srv.service_ref_id, srv.service_name, sint.integration_name
            from t1
            join tasks as td
                on td.taskid = t1.taskid
                    and td.current_version
            left join task_instances as inst
                on case
                    when td.task_status is null
                        and inst.is_open
                        and td.taskid = inst.taskid
                        and td.start_timestamp <= %(timestamp)s
                        and td.end_timestamp > %(timestamp)s
                    then 1
                    when td.task_status is null
                        and not inst.is_open
                        and td.taskid = inst.taskid
                        and td.start_timestamp <= inst.resolved_on
                        and td.end_timestamp = inst.resolved_on
                    then 1
                    when td.task_status = 'GROUPED'
                        and inst.resolved_on is null
                        and td.related_taskid is not null
                        and td.related_taskid = inst.taskid
                        and td.end_timestamp > %(timestamp)s
                    then 1
                    when td.task_status = 'GROUPED'
                        and inst.resolved_on is not null
                        and td.related_taskid is not null
                        and td.related_taskid = inst.taskid
                        and td.end_timestamp = inst.resolved_on
                    then 1
                    else 0
                end = 1
            left join services as srv
                on td.serviceid is not null
                    and td.serviceid = srv.serviceid
                    and srv.start_timestamp <= td.start_timestamp
                    and srv.end_timestamp > td.start_timestamp
            left join service_integrations as sint
                on td.integration_id is not null
                    and td.integration_id = sint.integration_id
                    and sint.start_timestamp <= td.start_timestamp
                    and sint.end_timestamp > td.start_timestamp
            order by min_start desc;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '',
                       limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for task_ref, title, desc, urgency, status, inst_id, org_inst_id, res_on, created_on,\
                serv_ref, serv_name, integ_name in result:

            data.append({
                var_names.task_ref_id: key_manager.conceal_reference_key(task_ref),
                var_names.timestamp: created_on,
                var_names.task_title: title,
                var_names.text_msg: desc,
                var_names.urgency_level: urgency,
                var_names.status: status if status is not None else constants.triggered_state,
                var_names.instance_id: inst_id,
                var_names.organization_instance_id: org_inst_id,
                var_names.resolved_on: res_on,
                var_names.services: [serv_name, key_manager.conceal_reference_key(serv_ref)] if serv_ref is not None
                else None,
                var_names.integration_name: integ_name
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_task_ids_from_ref_ids(conn, timestamp, organization_id, task_ref_ids, unmasked=True):
    '''
    Get the task ids of policies 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 task_ref_ids: (list) of task reference ids (concealed)
    :param unmasked: True if the reference IDs have been unmasked
    :return: (dict) -> { ref ID: task ID, ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(task_ref_ids, list)

    if not unmasked:
        task_ref_ids = [key_manager.unmask_reference_key(x) for x in task_ref_ids]

    query = '''
            select task_ref_id, taskid from tasks
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and task_ref_id = any(%s);
            '''
    query_params = (timestamp, timestamp, organization_id, task_ref_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for ref_, id_ in result:
            data[ref_] = id_
        return data
    except psycopg2.DatabaseError:
        raise