# By: Riasat Ullah
# This file contains queries for handling business services.

from psycopg2 import errorcodes
from utils import constants, errors, helpers, key_manager, times, var_names
from validations import component_validator
import datetime
import json
import psycopg2
import uuid


def create_business_service(conn, timestamp, organization_id, service_name, supporting_technical_services=None,
                            supporting_business_services=None, associated_teams=None, description=None,
                            min_urgency=None):
    '''
    Creates a new business service.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization this business service is for
    :param service_name: name of the business service
    :param supporting_technical_services: (list) of reference IDs of technical services
    :param supporting_business_services: (list) of reference IDs of business services
    :param associated_teams: (list) of IDs of teams
    :param description: brief description of what the business service is for
    :param min_urgency: minimum urgency of incidents triggered by supporting technical services
            that would impact this business service
    :errors: AssertionError, DatabaseError, LookupError
    '''
    # Validate the user provided data and cross check the IDs
    tech_serv_map, bus_serv_map, team_map = get_tech_and_business_and_team_ref_id_map(conn, timestamp, organization_id)

    component_validator.validate_business_service_data(
        timestamp, organization_id, service_name, supporting_technical_services,
        supporting_business_services, associated_teams, description, min_urgency,
        list(tech_serv_map.keys()), list(bus_serv_map.keys()), list(team_map.keys())
    )

    # Map reference IDs back to internal IDs as expected by the database.
    supp_services = []
    if supporting_technical_services is not None:
        for ref_ in supporting_technical_services:
            supp_services.append({var_names.component_id: tech_serv_map[ref_], var_names.is_technical: True})

    if supporting_business_services is not None:
        for ref_ in supporting_business_services:
            supp_services.append({var_names.component_id: bus_serv_map[ref_], var_names.is_technical: False})

    if associated_teams is not None:
        associated_teams = [team_map[ref_] for ref_ in associated_teams]

    query = '''
            select create_business_service(
                %s, %s, %s, %s,
                %s, %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, organization_id, key_manager.generate_reference_key(),
                    service_name, description, min_urgency, json.dumps(supp_services), associated_teams,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def edit_business_service(conn, timestamp, business_service_ref_id, organization_id, service_name,
                          supporting_technical_services=None, supporting_business_services=None, associated_teams=None,
                          description=None, min_urgency=None):
    '''
    Edits an existing business service.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param business_service_ref_id: masked reference ID of the business service that is being changed
    :param organization_id: ID of the organization this business service is for
    :param service_name: name of the business service
    :param supporting_technical_services: (list) of IDs of technical services
    :param supporting_business_services: (list) of IDs of business services
    :param associated_teams: (list) of IDs of teams
    :param description: brief description of what the business service is for
    :param min_urgency: minimum urgency of incidents triggered by supporting technical services
            that would impact this business service
    :errors: AssertionError, DatabaseError, LookupError
    '''
    if supporting_business_services is not None and business_service_ref_id in supporting_business_services:
        raise ValueError(errors.err_service_self_dependency)

    unmasked_bus_ref_id = key_manager.unmask_reference_key(business_service_ref_id)

    # Validate the user provided data and cross check the IDs
    tech_serv_map, bus_serv_map, team_map = get_tech_and_business_and_team_ref_id_map(conn, timestamp, organization_id)

    component_validator.validate_business_service_data(
        timestamp, organization_id, service_name, supporting_technical_services,
        supporting_business_services, associated_teams, description, min_urgency,
        list(tech_serv_map.keys()), list(bus_serv_map.keys()), list(team_map.keys())
    )

    # Map reference IDs back to internal IDs as expected by the database.
    supp_services = []
    if supporting_technical_services is not None:
        for ref_ in supporting_technical_services:
            supp_services.append({var_names.component_id: tech_serv_map[ref_], var_names.is_technical: True})

    if supporting_business_services is not None:
        for ref_ in supporting_business_services:
            supp_services.append({var_names.component_id: bus_serv_map[ref_], var_names.is_technical: False})

    if associated_teams is not None:
        associated_teams = [team_map[ref_] for ref_ in associated_teams]

    query = '''
            select edit_business_service(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (unmasked_bus_ref_id, timestamp, constants.end_timestamp, organization_id, service_name,
                    description, min_urgency, json.dumps(supp_services), associated_teams,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def delete_business_service(conn, timestamp, business_service_ref_id, organization_id):
    '''
    Deletes an existing business service.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param business_service_ref_id: masked reference ID of the business service that is to be deleted
    :param organization_id: ID of the organization this business service is for
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    unmasked_bus_ref_id = key_manager.unmask_reference_key(business_service_ref_id)
    bus_id = list(get_business_service_ids_from_ref_ids(
        conn, timestamp, organization_id, [unmasked_bus_ref_id]).values())[0]

    query = '''
            select delete_business_service(%s, %s);
            '''
    query_params = (bus_id, timestamp,)

    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def get_business_service_ids_from_ref_ids(conn, timestamp, organization_id, bus_ref_ids=None):
    '''
    Get business service IDs 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 bus_ref_ids: (list) of business service reference ids
    :return: (dict) -> { ref_id: id, ... }
    '''
    assert isinstance(timestamp, datetime.datetime)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if bus_ref_ids is not None:
        assert isinstance(bus_ref_ids, list)
        conditions.append("business_service_ref_id = any(%(bus_refs)s)")
        query_params['bus_refs'] = bus_ref_ids
    query = '''
            select business_serviceid, business_service_ref_id
            from business_services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, ref_ in result:
            data[ref_] = id_
        return data
    except psycopg2.DatabaseError:
        raise


def list_business_services(conn, timestamp, organization_id, bus_ref_id=None):
    '''
    Get the list of business services of the organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param bus_ref_id: reference ID of the business service
    :return: (list of dict) of basic business service details
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    conditions = []
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    if bus_ref_id is not None:
        conditions.append(" business_service_ref_id = %(ref_id)s ")
        query_params['ref_id'] = key_manager.unmask_reference_key(bus_ref_id)

    query = '''
            with t1 as (
                select business_serviceid, business_service_ref_id, service_name, description, min_urgency
                from business_services
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    {0}
            )
            , t2 as (
                select business_serviceid, json_agg(json_build_object(
                    'team_name', team_name,
                    'team_ref_id', team_ref_id
                )) as bus_teams
                from business_service_teams as bst
                join teams using(team_id)
                where bst.start_timestamp <= %(timestamp)s
                    and bst.end_timestamp > %(timestamp)s
                    and teams.start_timestamp <= %(timestamp)s
                    and teams.end_timestamp > %(timestamp)s
                    and bst.business_serviceid in (select business_serviceid from t1)
                group by bst.business_serviceid
            )
            , t3 as (
                select business_serviceid, json_agg(json_build_object(
                    'service_name', service_name,
                    'service_ref_id', service_ref_id
                )) supp_tech
                from business_service_dependencies as bsd
                join services
                    on bsd.supporting_serviceid = services.serviceid
                where bsd.start_timestamp <= %(timestamp)s
                    and bsd.end_timestamp > %(timestamp)s
                    and services.start_timestamp <= %(timestamp)s
                    and services.end_timestamp > %(timestamp)s
                    and bsd.is_technical = true
                    and bsd.business_serviceid in (select business_serviceid from t1)
                group by bsd.business_serviceid
            )
            , t4 as (
                select bsd.business_serviceid, json_agg(json_build_object(
                    'service_name', service_name,
                    'business_service_ref_id', bss.business_service_ref_id
                )) supp_bus
                from business_service_dependencies as bsd
                join business_services as bss
                    on bsd.supporting_serviceid = bss.business_serviceid
                where bsd.start_timestamp <= %(timestamp)s
                    and bsd.end_timestamp > %(timestamp)s
                    and bss.start_timestamp <= %(timestamp)s
                    and bss.end_timestamp > %(timestamp)s
                    and bsd.is_technical = false
                    and bsd.business_serviceid in (select business_serviceid from t1)
                group by bsd.business_serviceid
            )
            , t5 as (
                select business_serviceid, count(instanceid) as inst_count
                from instance_impacted_business_services
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and business_serviceid in (select business_serviceid from t1)
                group by business_serviceid
            )
            select t1.business_service_ref_id, t1.service_name, t1.description, t1.min_urgency,
                t2.bus_teams, t3.supp_tech, t4.supp_bus, t5.inst_count
            from t1
            left join t2 using(business_serviceid)
            left join t3 using(business_serviceid)
            left join t4 using(business_serviceid)
            left join t5 using(business_serviceid)
            order by inst_count desc nulls last, service_name asc;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for bus_ref, bus_name, bus_desc, min_urg, bus_teams, supp_tech, supp_bus, inst_count in result:
            data.append({
                var_names.business_service_ref_id: key_manager.conceal_reference_key(bus_ref),
                var_names.service_name: bus_name,
                var_names.description: bus_desc,
                var_names.min_urgency: min_urg,
                var_names.teams: [[item[var_names.team_name],
                                   key_manager.conceal_reference_key(uuid.UUID(item[var_names.team_ref_id]))]
                                  for item in bus_teams] if bus_teams is not None else [],
                var_names.supporting_tech_services: [[item[var_names.service_name],
                                                      key_manager.conceal_reference_key(
                                                          uuid.UUID(item[var_names.service_ref_id]))]
                                                     for item in supp_tech] if supp_tech is not None else [],
                var_names.supporting_business_services: [[item[var_names.service_name],
                                                          key_manager.conceal_reference_key(
                                                              uuid.UUID(item[var_names.business_service_ref_id]))]
                                                         for item in supp_bus] if supp_bus is not None else [],
                var_names.is_impacted: True if inst_count is not None and inst_count > 0 else False
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_related_business_services(conn, timestamp, bus_serv_ids):
    '''
    Get the corresponding dashboards that a given list of business services is associated to.
    The self relation is also returned.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param bus_serv_ids: (list of int) of business service IDs
    :return: (dict) -> {bus id: { rel bus id: x, rel bus name: y, rel min urg: z, rel sts page ID: v }, ... }
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)

    query = " select * from get_related_business_services(%s, %s); "
    query_params = (timestamp, helpers.get_int_list(bus_serv_ids))

    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for main_bus, rel_bus, rel_bus_name, rel_bus_urg, rel_sub_count, rel_pg_id in result:
            if main_bus not in data:
                data[main_bus] = []
            data[main_bus].append({
                var_names.business_service_id: rel_bus,
                var_names.business_service_name: rel_bus_name,
                var_names.min_urgency: rel_bus_urg,
                var_names.subscribers: 0 if rel_sub_count is None else rel_sub_count,
                var_names.page_id: rel_pg_id
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_user_business_service_subscriptions(conn, timestamp, user_id):
    '''
    Get the business services that a user is subscribed to.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param user_id: user_id of the user whose subscriptions are to be fetched
    :return: (list of list) -> [ [bus name, bus ref ID], ... ]
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select bus.business_service_ref_id, bus.service_name
            from business_service_subscribers as bss
            join business_services as bus using(business_serviceid)
            where bss.start_timestamp <= %(timestamp)s
                and bss.end_timestamp > %(timestamp)s
                and bus.start_timestamp <= %(timestamp)s
                and bus.end_timestamp > %(timestamp)s
                and user_id = %(usr_id)s;
            '''

    query_params = {'timestamp': timestamp, 'usr_id': user_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for bus_ref, bus_name in result:
            data.append([bus_name, key_manager.conceal_reference_key(bus_ref)])
        return data
    except psycopg2.DatabaseError:
        raise


def update_business_service_subscriptions(conn, timestamp, organization_id, user_id, bus_ref_ids):
    '''
    Update the business service subscriptions of a user.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param user_id: user_id of the user
    :param bus_ref_ids: (list) of business service ref IDs
    :errors: AssertionError, DatabaseError, InvalidRequest
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    new_bus_serv_ids = None
    if len(bus_ref_ids) > 0:
        unmasked_bus_ref_ids = [key_manager.unmask_reference_key(item) for item in bus_ref_ids]
        new_bus_serv_ids = list(get_business_service_ids_from_ref_ids(
            conn, timestamp, organization_id, unmasked_bus_ref_ids).values())

    query = " select update_user_business_subscriptions(%s, %s, %s, %s); "
    query_params = (timestamp, constants.end_timestamp, user_id, new_bus_serv_ids,)

    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_basic_business_services_list(conn, timestamp, organization_id):
    '''
    Get the basic business services list.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :return: (list of list) -> [ [business service name, business service ref id], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select service_name, business_service_ref_id
            from business_services
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
            order by service_name;
            '''
    query_params = (timestamp, timestamp, organization_id,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for name_, key_ in result:
            data.append([name_, key_manager.conceal_reference_key(key_)])
        return data
    except psycopg2.DatabaseError:
        raise


def get_business_service_id_and_name_from_ref_id(conn, timestamp, organization_id, bus_ref_id):
    '''
    Get the business service id and name from its reference ID.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param bus_ref_id: masked business service ref id
    :return: (tuple) -> business service id, business service name
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_ref_id = key_manager.unmask_reference_key(bus_ref_id)

    query = '''
            select business_serviceid, service_name
            from business_services
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and business_service_ref_id = %s;
            '''
    query_params = (timestamp, timestamp, organization_id, unmasked_ref_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        else:
            return result[0][0], result[0][1]
    except psycopg2.DatabaseError:
        raise


def get_tech_and_business_and_team_ref_id_map(conn, timestamp, organization_id):
    '''
    Get the map of "concealed" reference IDs to IDs of all technical and business services and teams of an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :return: (tuple of dict) -> tech services map, business services map, team map
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select service_ref_id, serviceid, 'TECH'
            from services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s

            union

            select business_service_ref_id, business_serviceid, 'BUSINESS'
            from business_services
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s

            union

            select team_ref_id, team_id, 'TEAM'
            from teams
            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)
        serv_maps = dict()
        bus_maps = dict()
        team_maps = dict()

        for ref_id_, id_, d_type_ in result:
            if d_type_ == 'TECH':
                serv_maps[key_manager.conceal_reference_key(ref_id_)] = id_
            elif d_type_ == 'BUSINESS':
                bus_maps[key_manager.conceal_reference_key(ref_id_)] = id_
            elif d_type_ == 'TEAM':
                team_maps[key_manager.conceal_reference_key(ref_id_)] = id_

        return serv_maps, bus_maps, team_maps
    except psycopg2.DatabaseError:
        raise


def get_business_service_names(conn, timestamp, organization_id):
    '''
    Get business service names mapped to their IDs.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization to filer by
    :return: (dict) -> {bus id: bus name, ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select business_serviceid, service_name
            from business_services
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s;
            '''
    query_params = (timestamp, timestamp, organization_id,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, name_ in result:
            data[id_] = name_
        return data
    except psycopg2.DatabaseError:
        raise


def get_business_impact_notification_details(conn, start_time, end_time, instance_ids=None):
    '''
    Get the details needed for sending notifications about impacted business services.
    :param conn: db connection
    :param start_time: minimum timestamp for data validity
    :param end_time: maximum timestamp for data validity
    :param instance_ids: (list) of IDs of instances to filter by
    :return: (list) of instance IDs to filter by
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(start_time, datetime.datetime)
    assert isinstance(end_time, datetime.datetime)

    update_able_events = [constants.trigger_event, constants.resolve_event, constants.status_update_event,
                          constants.add_impacted_business_service_event,
                          constants.remove_impacted_business_service_event]

    query_params = {'last_time': start_time, 'curr_time': end_time, 'upd_evn': update_able_events}

    if instance_ids is None:
        condition = '''
            ti.is_open or (
                not ti.is_open
                    and ti.instance_timestamp > %(last_time)s - interval '2 DAYS'
                    and ti.resolved_on > %(last_time)s
                    and ti.resolved_on <= %(curr_time)s
            )
        '''
    else:
        condition = " ti.instanceid in (%(inst_ids)s) "
        query_params['inst_ids'] = helpers.get_int_list(instance_ids)

    query = '''
            with t1 as (
                select iibs.business_serviceid, bus.business_service_ref_id, bus.service_name as business_service_name,
                    iibs.start_timestamp as imp_start, iibs.end_timestamp as imp_end, ti.organization_id,
                    ti.instanceid, ti.organization_instanceid, ti.instance_timestamp, taskid, status, resolved_on,
                    case
                        when resolved_on is null then %(curr_time)s
                        else (resolved_on::timestamp - interval '50 milliseconds')
                    end as check_time
                from task_instances as ti
                join instance_impacted_business_services as iibs using(instanceid)
                join business_services as bus
                    on bus.business_serviceid = iibs.business_serviceid
                        and bus.organization_id = ti.organization_id
                        and bus.start_timestamp <= %(curr_time)s
                        and bus.end_timestamp > %(curr_time)s
                where {0}
            )
            , t2 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 t1)
                group by instanceid
            )
            , t3 as (
                select instanceid, json_agg(json_build_object(
                    'post_id', post_id,
                    'last_update', last_updated_on,
                    'pending', max_pending
                )) as synced_posts
                from (
                    select tmp_1.instanceid, po.post_id, po.last_updated_on, tmp_2.max_pending
                    from (select distinct instanceid from t1) as tmp_1
                    left join status_page_posts as po
                        on po.is_open
                            and po.instanceid is not null
                            and po.instanceid = tmp_1.instanceid
                    left join (
                        select t1.instanceid, pe.post_id, max(pe.event_timestamp) as max_pending
                        from t1
                        join status_page_events as pe
                            on pe.instanceid is not null
                                and pe.instanceid = t1.instanceid
                                and (is_pending or (not is_published and action_timestamp is not null))
                        group by t1.instanceid, pe.post_id
                    ) as tmp_2
                        on tmp_2.instanceid = tmp_1.instanceid
                            and (tmp_2.post_id is null or tmp_2.post_id = po.post_id)
                ) as tmp_final
                where post_id is not null or max_pending is not null
                group by instanceid
            )
            , t4 as (
                select t1.business_serviceid, t1.business_service_ref_id, t1.business_service_name,
                    t1.organization_id, min(imp_start) as imp_start, max(imp_end) as imp_end,
                    json_agg(json_build_object(
                        'instance_id', t1.instanceid,
                        'organization_instance_id', organization_instanceid,
                        'instance_timestamp', instance_timestamp,
                        'status', status,
                        'task_title', title,
                        'text_msg', text_msg,
                        'urgency_level', urgency_level,
                        'service_id', tasks.serviceid,
                        'service_name', service_name,
                        'resolved_on', resolved_on,
                        'status_update', inst_updates,
                        'posts', t3.synced_posts
                    )) as incidents
                from t1
                join tasks
                    on tasks.taskid = t1.taskid
                        and tasks.start_timestamp <= t1.check_time
                        and tasks.end_timestamp > t1.check_time
                left join services as srv
                    on srv.serviceid = tasks.serviceid
                        and srv.start_timestamp <= t1.check_time
                        and srv.end_timestamp > t1.check_time
                left join t2 on t1.instanceid = t2.instanceid
                left join t3 on t3.instanceid = t1.instanceid
                group by t1.business_serviceid, t1.business_service_ref_id,
                        t1.business_service_name, t1.organization_id
            )
            , t5 as (
                select business_serviceid, array_agg(users.policyid) as bus_sub
                from business_service_subscribers as bss
                join users
                    on users.user_id = bss.user_id
                        and users.start_timestamp <= %(curr_time)s
                        and users.end_timestamp > %(curr_time)s
                where business_serviceid in (select business_serviceid from t1)
                    and bss.start_timestamp <= %(curr_time)s
                    and bss.end_timestamp > %(curr_time)s
                group by business_serviceid
            )
            , t6 as (
                select business_serviceid, array_agg(sp.page_ref_id) as bus_auto_post_pages
                from status_page_components as comp
                join status_pages as sp
                    on sp.page_id = comp.page_id
                        and sp.start_timestamp <= %(curr_time)s
                        and sp.end_timestamp > %(curr_time)s
                        and auto_post
                where comp.start_timestamp <= %(curr_time)s
                    and comp.end_timestamp > %(curr_time)s
                    and business_serviceid in (select business_serviceid from t1)
                group by business_serviceid
            )
            select t4.business_serviceid, t4.business_service_ref_id, t4.business_service_name,
                t4.imp_start, t4.imp_end, t4.organization_id,
                t4.incidents, t5.bus_sub, t6.bus_auto_post_pages
            from t4
            left join t5 using(business_serviceid)
            left join t6 using(business_serviceid);
            '''.format(condition)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for bus_id, bus_ref, bus_name, imp_start, imp_end, org_id, bus_incs, bus_subs, bus_auto_post_pages in result:
            if bus_incs is not None:
                for item in bus_incs:
                    item[var_names.instance_timestamp] = times.get_timestamp_from_string(
                        item[var_names.instance_timestamp])
                    if item[var_names.resolved_on] is not None:
                        item[var_names.resolved_on] = times.get_timestamp_from_string(
                            item[var_names.resolved_on])
                    if item[var_names.status_update] is not None:
                        for sub_item in item[var_names.status_update]:
                            sub_item[var_names.timestamp] = times.get_timestamp_from_string(
                                sub_item[var_names.timestamp])
                    if item[var_names.posts] is not None:
                        if len(item[var_names.posts]) == 0:
                            item[var_names.posts] = None
                        else:
                            for sub_item in item[var_names.posts]:
                                if sub_item[var_names.last_update] is not None:
                                    sub_item[var_names.last_update] = times.get_timestamp_from_string(
                                        sub_item[var_names.last_update]
                                    )
                                if sub_item[var_names.pending] is not None:
                                    sub_item[var_names.pending] = times.get_timestamp_from_string(
                                        sub_item[var_names.pending]
                                    )

            if bus_auto_post_pages is not None:
                bus_auto_post_pages = [key_manager.conceal_reference_key(x) for x in bus_auto_post_pages]

            data.append({
                var_names.business_service_id: bus_id,
                var_names.business_service_ref_id: key_manager.conceal_reference_key(bus_ref),
                var_names.business_service_name: bus_name,
                var_names.start_timestamp: imp_start,
                var_names.end_timestamp: imp_end,
                var_names.organization_id: org_id,
                var_names.incidents: bus_incs,
                var_names.subscribers: bus_subs,
                var_names.status_pages: bus_auto_post_pages
            })
        return data
    except psycopg2.DatabaseError:
        raise