# By: Riasat Ullah
# This file contains functions that handle database queries for status dashboards.

from dbqueries import db_business_services
from psycopg2 import errorcodes
from utils import constants, errors, key_manager, var_names
from validations import string_validator
import datetime
import psycopg2


def create_custom_status_dashboard(conn, timestamp, organization_id, dashboard_name, dashboard_url,
                                   business_services, is_public=True):
    '''
    Creates a custom status dashboard.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the dashboard is being created for
    :param dashboard_name: name of the dashboard
    :param dashboard_url: url extension of the dashboard
    :param business_services: (list) of reference IDs of the business services associated with the dashboard
    :param is_public: (boolean) that states whether the dashboard should be viewable without having to log in
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_standard_name(dashboard_name)
    assert string_validator.is_status_dashboard_url_extension(dashboard_url)
    assert isinstance(is_public, bool)
    assert len(business_services) > 0

    unmasked_bus_ref_ids = [key_manager.unmask_reference_key(key) for key in business_services]
    bus_id_map = db_business_services.get_business_service_ids_from_ref_ids(
        conn, timestamp, organization_id, unmasked_bus_ref_ids
    )
    if len(unmasked_bus_ref_ids) != len(bus_id_map):
        raise AssertionError(errors.err_invalid_request)

    bus_ids = [bus_id_map[ref_] for ref_ in unmasked_bus_ref_ids]

    query = '''
            select create_status_dashboard(
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, dashboard_name,
                    dashboard_url, is_public, bus_ids,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_invalid_request)
        elif e.pgcode == errorcodes.UNIQUE_VIOLATION:
            raise LookupError(errors.err_status_dashboard_url_unavailable)
    except psycopg2.DatabaseError:
        raise


def edit_custom_status_dashboard(conn, timestamp, organization_id, old_dash_url, dashboard_name, new_dash_url,
                                 business_services, is_public=True):
    '''
    Edits a custom made status dashboard.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the dashboard is being created for
    :param old_dash_url: url extension of the dashboard to use as the index to update the dashboard
    :param dashboard_name: name of the dashboard
    :param new_dash_url: new url extension of the dashboard
    :param business_services: (list) of reference IDs of the business services associated with the dashboard
    :param is_public: (boolean) that states whether the dashboard should be viewable without having to log in
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_status_dashboard_url_extension(old_dash_url)
    assert string_validator.is_standard_name(dashboard_name)
    assert string_validator.is_status_dashboard_url_extension(new_dash_url)
    assert isinstance(is_public, bool)
    assert len(business_services) > 0

    unmasked_bus_ref_ids = [key_manager.unmask_reference_key(key) for key in business_services]
    bus_id_map = db_business_services.get_business_service_ids_from_ref_ids(
        conn, timestamp, organization_id, unmasked_bus_ref_ids
    )
    if len(unmasked_bus_ref_ids) != len(bus_id_map):
        raise AssertionError(errors.err_invalid_request)

    bus_ids = [bus_id_map[ref_] for ref_ in unmasked_bus_ref_ids]

    query = '''
            select edit_status_dashboard(
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, old_dash_url,
                    dashboard_name, new_dash_url, is_public, bus_ids,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_invalid_request)
    except psycopg2.DatabaseError:
        raise


def delete_custom_status_dashboard(conn, timestamp, organization_id, dashboard_url):
    '''
    Delete a custom status dashboard.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the status dashboard belongs to
    :param dashboard_url: the url of the dashboard to use as the index to update the dashboard
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_status_dashboard_url_extension(dashboard_url)

    query = " select delete_status_dashboard(%s, %s, %s); "
    query_params = (timestamp, organization_id, dashboard_url,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_invalid_request)
    except psycopg2.DatabaseError:
        raise


def get_dashboard_business_services_impact_status(conn, timestamp, organization_id, dashboard_url=None, user_id=None):
    '''
    Get the basic details of the business services that are currently impacted, along with the details
    of the instances causing them and updates that are available on the instances.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization the requested dashboard belongs to
    :param dashboard_url: the url of the requested dashboard
    :param user_id: ID of the user making the request
    :return: (list of dict) -> [ {bus ref id: , bus name: , impacting instances: [...] }, ... ]
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    cond = ''
    if dashboard_url is not None:
        cond = '''
                and bss.business_serviceid in (
                    select business_serviceid from status_dashboard_business_services
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and dashboard_id in (
                            select dashboard_id from status_dashboards
                            where start_timestamp <= %(timestamp)s
                                and end_timestamp > %(timestamp)s
                                and url_extension = %(dash_url)s
                        )
                )
               '''
        query_params['dash_url'] = dashboard_url

    query = '''
            with t1 as(
                select business_serviceid, bss.business_service_ref_id, bss.service_name
                from business_services as bss
                where bss.start_timestamp <= %(timestamp)s
                    and bss.end_timestamp > %(timestamp)s
                    and bss.organization_id = %(org_id)s
                    {0}
            )
            , t2 as (
                select iibs.business_serviceid, inst.instanceid, organization_instanceid, tasks.title,
                    instance_timestamp, tasks.urgency_level
                from instance_impacted_business_services as iibs
                left join task_instances as inst
                    on iibs.instanceid = inst.instanceid
                        and inst.organization_id = %(org_id)s
                        and inst.is_open = true
                left join tasks
                    on tasks.taskid = inst.taskid
                        and tasks.start_timestamp <= %(timestamp)s
                        and tasks.end_timestamp > %(timestamp)s
                where iibs.start_timestamp <= %(timestamp)s
                    and iibs.end_timestamp > %(timestamp)s
                    and iibs.business_serviceid in (select business_serviceid from t1)
            )
            , t3 as (
                select instanceid, json_agg(json_build_object(
                    'timestamp', update_timestamp,
                    'status_update', status_update
                ) order by update_timestamp desc) as inst_updates
                from instance_updates
                where instanceid in (select instanceid from t2)
                group by instanceid
            )
            , t4 as (
                select instanceid, array_agg(subscriber_id) as inst_subs
                from instance_subscribers
                where instanceid in (select instanceid from t2)
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                group by instanceid
            )
            , t5 as (
                select t2.business_serviceid, json_agg(json_build_object(
                    'instance_id', t2.instanceid,
                    'organization_instance_id', t2.organization_instanceid,
                    'task_title', t2.title,
                    'instance_timestamp', t2.instance_timestamp,
                    'urgency_level', t2.urgency_level,
                    'status_update', t3.inst_updates,
                    'is_subscribed', t4.inst_subs
                )) as impacting_instances
                from t2
                left join t3 using(instanceid)
                left join t4 using(instanceid)
                group by t2.business_serviceid
            )
            select t1.business_service_ref_id, t1.service_name, t5.impacting_instances
            from t1
            left join t5 using(business_serviceid);
            '''.format(cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for bus_ref_id, bus_name, impacting_instances in result:

            if impacting_instances is not None:
                for item in impacting_instances:
                    if user_id is not None and item[var_names.is_subscribed] is not None\
                            and user_id in item[var_names.is_subscribed]:
                        item[var_names.is_subscribed] = True
                    else:
                        item[var_names.is_subscribed] = False

            data.append({
                var_names.business_service_ref_id: key_manager.conceal_reference_key(bus_ref_id),
                var_names.service_name: bus_name,
                var_names.incidents: impacting_instances if impacting_instances is not None else []
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_custom_status_dashboards(conn, timestamp, organization_id):
    '''
    Get the list of custom dashboards.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization the requested dashboard belongs to
    :return: (dict) -> of information to display on the dashboard
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select dashboard_name, url_extension
            from status_dashboards
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s;
            '''

    query_params = {'timestamp': timestamp, 'org_id': organization_id}

    try:
        result = conn.fetch(query, query_params)
        data = []
        for dash_name, dash_url in result:
            data.append([dash_name, dash_url])
        return data
    except psycopg2.DatabaseError:
        raise