# By: Riasat Ullah
# This file contains all db queries for configurations apart from integrations.

from dbqueries import db_policies, db_services
from objects.conditional_route import ConditionalRoute
from psycopg2 import errorcodes
from utils import constants, errors, key_manager, times, var_names
from validations import configuration_validator
import datetime
import json
import psycopg2


def create_conditional_routing(conn, timestamp, org_id, is_enabled, rule_name, rule_description,
                               valid_start_date, valid_end_date, valid_start_time, valid_end_time, repeat_on,
                               rule_application_count, rules, actions, timezone, allow_multiple):
    '''
    Creates a new conditional routing directive.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made at
    :param org_id: ID of the organization this routing is for
    :param is_enabled: (boolean) True if the routing directive is enabled; False otherwise
    :param rule_name: the name/title of the conditional routing directive
    :param rule_description: (optional) description of what the conditional routing is for
    :param valid_start_date: the date from when this routing will apply
    :param valid_end_date: the date this routing will apply till
    :param valid_start_time: the time in the day this routing will apply from
    :param valid_end_time: the time in the day this routing will apply till
    :param repeat_on: the days the routing will apply on
    :param rule_application_count: the number of rules that must apply (>= 1)
    :param rules: the conditional routing rules
    :param actions: the actions to perform
    :param timezone: the timezone this routing will apply in; important for start and end dates and times
    :param allow_multiple: allow other conditional routing rules to be combined
    :errors: AssertionError, ValueError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    configuration_validator.validate_conditional_routing_data(
        is_enabled, rule_name, rule_description, valid_start_date, valid_end_date, valid_start_time,
        valid_end_time, repeat_on, rule_application_count, rules, actions, timezone, allow_multiple
    )
    rules = ConditionalRoute.standardize_routing_rules_info(rules)
    rules, actions = db_services.internalize_service_ref_ids_in_routing_rules_and_actions(
        conn, timestamp, org_id, rules, actions)
    actions = db_policies.internalize_policy_ref_ids_in_routing_actions(conn, timestamp, org_id, actions)

    valid_start_date = times.get_date_from_string(valid_start_date)
    valid_end_date = times.get_date_from_string(valid_end_date)
    valid_start_time = times.get_time_from_string(valid_start_time)
    valid_end_time = times.get_time_from_string(valid_end_time)
    on_all_days = True if len(repeat_on) == 7 else False
    repeat_days = None if len(repeat_on) == 0 else repeat_on

    query = '''
            select create_conditional_routing(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, org_id, key_manager.generate_reference_key(), is_enabled,
                    allow_multiple, rule_name, rule_description, valid_start_date, valid_end_date,
                    valid_start_time, valid_end_time, on_all_days, repeat_days,
                    rule_application_count, json.dumps(actions), timezone, json.dumps(rules),)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def edit_conditional_routing(conn, timestamp, org_id, routing_ref_id, is_enabled, rule_name, rule_description,
                             valid_start_date, valid_end_date, valid_start_time, valid_end_time, repeat_on,
                             rule_application_count, rules, actions, timezone, allow_multiple):
    '''
    Edit an existing conditional routing directive.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made at
    :param org_id: ID of the organization the routing is for
    :param routing_ref_id: reference ID of the conditional routing directive that is being edited
    :param is_enabled: (boolean) True if the routing directive is enabled; False otherwise
    :param rule_name: the name/title of the conditional routing directive
    :param rule_description: (optional) description of what the conditional routing is for
    :param valid_start_date: the date from when this routing will apply
    :param valid_end_date: the date this routing will apply till
    :param valid_start_time: the time in the day this routing will apply from
    :param valid_end_time: the time in the day this routing will apply till
    :param repeat_on: the days the routing will apply on
    :param rule_application_count: the number of rules that must apply (>= 1)
    :param rules: the conditional routing rules
    :param actions: the actions to perform
    :param timezone: the timezone this routing will apply in; important for start and end dates and times
    :param allow_multiple: allow other conditional routing rules to be combined
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    unmasked_ref_id = key_manager.unmask_reference_key(routing_ref_id)

    configuration_validator.validate_conditional_routing_data(
        is_enabled, rule_name, rule_description, valid_start_date, valid_end_date, valid_start_time,
        valid_end_time, repeat_on, rule_application_count, rules, actions, timezone, allow_multiple
    )
    rules = ConditionalRoute.standardize_routing_rules_info(rules)
    rules, actions = db_services.internalize_service_ref_ids_in_routing_rules_and_actions(
        conn, timestamp, org_id, rules, actions)
    actions = db_policies.internalize_policy_ref_ids_in_routing_actions(conn, timestamp, org_id, actions)

    valid_start_date = times.get_date_from_string(valid_start_date)
    valid_end_date = times.get_date_from_string(valid_end_date)
    valid_start_time = times.get_time_from_string(valid_start_time)
    valid_end_time = times.get_time_from_string(valid_end_time)
    on_all_days = True if len(repeat_on) == 7 else False
    repeat_days = None if len(repeat_on) == 0 else repeat_on

    query = '''
            select edit_conditional_routing(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, org_id, unmasked_ref_id, is_enabled,
                    allow_multiple, rule_name, rule_description, valid_start_date, valid_end_date,
                    valid_start_time, valid_end_time, on_all_days, repeat_days,
                    rule_application_count, json.dumps(actions), timezone, json.dumps(rules),)
    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 delete_conditional_routing(conn, timestamp, org_id, routing_ref_id):
    '''
    Delete a conditional routing directive.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the routing is for
    :param routing_ref_id: reference ID of the conditional routing directive
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    unmasked_ref_id = key_manager.unmask_reference_key(routing_ref_id)

    query = '''
            do
            $body$

            declare
                r_id conditional_routing.routing_id%%type;

            begin

            update conditional_routing set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and routing_ref_id = %(rout_id)s
            returning routing_id into r_id;
    
            if r_id is null then
                raise check_violation;
            end if;

            end;
            $body$
            '''

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'rout_id': unmasked_ref_id}
    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 enable_routing(conn, timestamp, org_id, routing_ref_id, enable=True):
    '''
    Enables/Disables a given routing directive.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made at
    :param org_id: ID of the organization the routing is for
    :param routing_ref_id: routing reference ID
    :param enable: (boolean) True if it should be enabled; False otherwise
    :errors: AssertionError, LookupError
    '''
    assert isinstance(org_id, int)
    assert isinstance(enable, bool)

    unmasked_ref_id = key_manager.unmask_reference_key(routing_ref_id)

    query = '''
            do
            $body$

            declare
                r_id conditional_routing.routing_id%%type;

            begin

            update conditional_routing set is_enabled = %(enable_status)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and is_enabled != %(enable_status)s
                and routing_ref_id = %(rout_ref)s
            returning routing_id into r_id;
        
            if r_id is null then
                raise check_violation;
            end if;

            end;
            $body$
            '''

    query_params = {'timestamp': timestamp, 'org_id': org_id,
                    'rout_ref': unmasked_ref_id, 'enable_status': enable}
    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 get_conditional_routing(conn, timestamp, routing_ref_id=None, routing_id=None, org_id=None, enabled_only=False,
                            id_on_ref=True):
    '''
    Get conditional routing rules given certain parameters.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param routing_id: (optional) routing ID to filter by
    :param org_id: (optional) organization ID to filter by
    :param routing_ref_id: (optional) str reference id of the conditional routing directive
    :param enabled_only: (optional) True if only enabled conditional routes are wanted; False otherwise
    :param id_on_ref: (boolean) if True then the reference ID will be used as the ID; else the routing ID will be used
    :return: (list) of ConditionalRoute objects
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query_params = {'timestamp': timestamp}

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

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

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

    if enabled_only:
        conditions.append(" is_enabled = true ")

    query = '''
            select routing_id, routing_ref_id, organization_id, is_enabled, rule_name, rule_description,
                valid_start_date, valid_end_date, valid_start_time, valid_end_time, all_days, repeat,
                rule_application_count, actions, rules, routing_timezone, allow_multiple
            from conditional_routing
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for rout_id, rout_ref_id, og_id_, is_enabled, name_, desc_, start_date, end_date, start_time, end_time,\
                all_days, repeat, rule_count, actions, rules, timezone, allow_multi in result:

            if org_id is None:
                org_id = og_id_

            rout_ref_id = key_manager.conceal_reference_key(rout_ref_id)
            main_id = rout_ref_id if id_on_ref else rout_id

            repeat_on = [] if repeat is None else repeat
            if id_on_ref:
                rules = externalize_routing_rules(conn, timestamp, org_id, rules)
            rule_objects = ConditionalRoute.create_routing_rules(rules, externalize=id_on_ref)

            # converts internal policy ID to policy ref
            if id_on_ref:
                actions = externalize_routing_actions(conn, timestamp, org_id, actions)

            data.append(ConditionalRoute(main_id, name_, desc_, rule_objects, rule_count,
                                         start_date, end_date, start_time, end_time, all_days, repeat_on,
                                         actions, is_enabled, timezone, reference_id=rout_ref_id,
                                         allow_multiple=allow_multi))
        return data
    except psycopg2.DatabaseError:
        raise


def list_conditional_routing(conn, timestamp, organization_id, row_limit=None, row_offset=None):
    '''
    Get the list of all the basic info for conditional routing rules of an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made at
    :param organization_id: ID of the organization
    :param row_limit: (optional) number of members to fetch
    :param row_offset: (optional) number of members to skip ahead
    :return: (list) of list -> [[routing id, routing name, description, status], ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    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 routing_ref_id, rule_name, rule_description, is_enabled
                from conditional_routing
                where start_timestamp <= %s
                    and end_timestamp > %s
                    and organization_id = %s
                {0}
            )
            select * from t1
            order by rule_name;
            '''.format(limit_cond)
    query_params = (timestamp, timestamp, organization_id,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for ref_id_, name_, desc_, status_ in result:
            data.append({
                var_names.routing_ref_id: key_manager.conceal_reference_key(ref_id_),
                var_names.rule_name: name_,
                var_names.rule_description: desc_,
                var_names.is_enabled: status_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_all_enabled_org_conditional_routes(conn, timestamp):
    '''
    Get enabled conditional routing rules of all organizations.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :return: (dict of list) -> {org id: [ConditionalRoute object 1, ...], ... }
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)

    query = '''
            select routing_id, organization_id, rule_name, rule_description,
                valid_start_date, valid_end_date, valid_start_time, valid_end_time, all_days, repeat,
                rule_application_count, actions, rules, routing_timezone, allow_multiple
            from conditional_routing
            where start_timestamp <= %s
                and end_timestamp > %s
                and is_enabled = true;
            '''
    query_params = (timestamp, timestamp,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for rout_id, org_id, name_, desc_, start_date, end_date, start_time, end_time,\
                all_days, repeat, rule_count, actions, rules, timezone, allow_multi in result:

            repeat_on = [] if repeat is None else repeat
            rule_objects = ConditionalRoute.create_routing_rules(rules)

            if org_id not in data:
                data[org_id] = []

            data[org_id].append(ConditionalRoute(rout_id, name_, desc_, rule_objects, rule_count, start_date, end_date,
                                                 start_time, end_time, all_days, repeat_on, actions, True, timezone,
                                                 allow_multiple=allow_multi))
        return data
    except psycopg2.DatabaseError:
        raise


def externalize_routing_rules(conn, timestamp, organization_id, rules):
    '''
    If the action is re-routing, then convert the internal policy ID to the concealed policy ref.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param rules: (list of dict) of rules (as stored in db)
    :return: (list of dict) of rules (as displayable to user)
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(rules, list)

    all_serv_mappings = None
    for item in rules:
        if item[var_names.rule_type] == var_names.service:
            serv_id = item[var_names.field_value]
            if all_serv_mappings is None:
                all_serv_mappings = db_services.get_service_name_ref_keyed_on_id(conn, timestamp, organization_id)

            item[var_names.field_value] = all_serv_mappings[serv_id][1]

    return rules


def externalize_routing_actions(conn, timestamp, organization_id, actions):
    '''
    If the action is re-routing, then convert the internal policy ID to the concealed policy ref.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param actions: (dict) of actions (as stored in db)
    :return: (dict) of actions (as displayable to user)
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(actions, dict)

    if actions[var_names.alert_handling] == constants.re_route_alert:
        pol_id = actions[var_names.route_to]
        concealed_pol_ref = db_policies.get_policy_name_ref_keyed_on_id(
            conn, timestamp, organization_id, [pol_id])[pol_id][1]

        actions[var_names.route_to] = concealed_pol_ref

    elif actions[var_names.alert_handling] == constants.re_route_service:
        srv_id = actions[var_names.route_to_service]
        concealed_srv_ref = db_services.get_service_name_ref_keyed_on_id(
            conn, timestamp, organization_id, [srv_id])[srv_id][1]

        actions[var_names.route_to_service] = concealed_srv_ref

    return actions


def get_component_removed_routing_actions(conn, timestamp, org_id, curr_pol_id, new_pol_id, with_routing_name=False):
    '''
    If any conditional routing of an organization is set for re-routing and matches a particular policy ID then
    switch it with the new policy ID and return the new "actions" of the routings that have been updated. This
    will primarily be used when a standard user is being deleted and their responsibility is being transferred
    to another user.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization
    :param curr_pol_id: (int) policy ID to look for and match
    :param new_pol_id: (int) policy ID to switch to
    :param with_routing_name: (boolean) True if the name of the conditional routing should be added in the dict
    :return: (list of dict) -> [{routing_id: .., actions: ..}, ...]
    '''
    updated_route_actions = []
    org_conds = get_conditional_routing(conn, timestamp, org_id=org_id, id_on_ref=False)
    for rout in org_conds:
        if rout.actions[var_names.alert_handling] == constants.re_route_alert \
                and rout.actions[var_names.route_to] == curr_pol_id:
            rout.actions[var_names.route_to] = new_pol_id

            dict_to_add = {var_names.routing_id: rout.routing_id, var_names.actions: rout.actions}
            if with_routing_name:
                dict_to_add[var_names.rule_name] = rout.rule_name

            updated_route_actions.append(dict_to_add)

    return updated_route_actions


def get_service_associated_conditional_routes(conn, timestamp, org_id, serv_ref):
    '''
    Get the ID and name of the conditional routes that checks against a given service in at least one rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization
    :param serv_ref: (str) concealed service reference ID
    :return: (list of tuple) - [(routing ID, rule name), ...]
    '''
    matching_routes = []

    # set id_on_ref to true to have rules of service equality be mapped on service_ref_id
    org_conds = get_conditional_routing(conn, timestamp, org_id=org_id, id_on_ref=True)
    for rout in org_conds:
        for rule in rout.routing_rules:
            if rule.rule_type == var_names.service and rule.field_name == var_names.service_ref_id\
                    and rule.expected_value == serv_ref:
                matching_routes.append((rout.routing_id, rout.rule_name))
    return matching_routes