# By: Riasat Ullah
# This file contains database queries for status pages.

from dbqueries.status_pages import db_status_pages
from psycopg2 import errorcodes
from utils import constants, errors, helpers, integration_type_names as intt, key_manager, times, var_names
import configuration as configs
import datetime
import json
import psycopg2
import uuid


def add_event(conn, timestamp, organization_id, page_ref_id, event_type, title, message, status,
              page_impact, next_update_time, notify_subscribers, impacted_components, post_id=None,
              maintenance_start=None, maintenance_end=None, auto_update=None, user_id=None,
              org_inst_id=None):
    '''
    Add a new event to a status page, and create a new public incident or update its status unless approval is required.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param page_ref_id: (concealed) reference ID of the status page
    :param event_type: type of event (INCIDENT, MAINTENANCE)
    :param title: title of the incident/maintenance
    :param message: message of the incident/maintenance
    :param status: status of the incident
    :param page_impact: the impact the incident will have on the page
    :param next_update_time: (integer) number of minutes after which an update will be made
    :param notify_subscribers: (boolean) should subscribers be notified or not
    :param impacted_components: (list) component types -> [{business reference id: , status: }, ...]
    :param post_id: ID of the post if it has already been created
    :param maintenance_start: time when the maintenance should start (for maintenance events)
    :param maintenance_end: time when the maintenance should end (for maintenance events)
    :param auto_update: (boolean) should the instance be updated automatically (for maintenance events)
    :param user_id: ID of the user creating the event (None if it is automatically created by the system)
    :param org_inst_id: ID of the organization instance ID this post is synced with
    :return: (tuple) -> post ID, page ID, is approval required, event ref ID
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)
    new_components = validate_and_internalize_event_data(
        conn, timestamp, organization_id, page_ref_id, event_type, title, message, status, page_impact,
        next_update_time, notify_subscribers, impacted_components, post_id, maintenance_start, maintenance_end,
        auto_update, user_id, org_inst_id
    )
    event_ref_id = key_manager.generate_reference_key()

    query = '''
            select * from add_status_page_event(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (organization_id, unmasked_pg_ref, event_ref_id, timestamp, constants.end_timestamp,
                    event_type, post_id, title, message, status,
                    page_impact, next_update_time, notify_subscribers, org_inst_id, maintenance_start,
                    maintenance_end, auto_update, json.dumps(new_components), user_id,)
    try:
        result = conn.fetch(query, query_params)

        # Make sure new post ID is not mixed up with post ID (when status updates are added)
        new_post_id, page_id, page_name, req_apv = result[0][0], result[0][1], result[0][2], result[0][3]
        concealed_event_ref_id = key_manager.conceal_reference_key(event_ref_id)

        return new_post_id, page_id, page_name, req_apv, concealed_event_ref_id
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def approve_event(conn, timestamp, organization_id, page_ref_id, event_ref_id, user_id):
    '''
    Approve a status page event.
    :param conn: db connection
    :param timestamp: timestamp when this approval is being made
    :param organization_id: ID of the organization
    :param page_ref_id: (concealed) reference ID of the page
    :param event_ref_id: (concealed) reference ID of the page
    :param user_id: ID of the user who approved the event
    :return: (tuple) -> public instance id, event type, to notify subscribers
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    unmasked_event_ref = key_manager.unmask_reference_key(event_ref_id)

    query = "select * from approve_status_page_event(%s, %s, %s, %s, %s, %s);"
    query_params = (organization_id, unmasked_page_ref, unmasked_event_ref,
                    timestamp, constants.end_timestamp, user_id,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0], result[0][1], result[0][2]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def reject_event(conn, timestamp, organization_id, page_ref_id, event_ref_id, user_id):
    '''
    Reject a status page event.
    :param conn: db connection
    :param timestamp: timestamp the event was rejected
    :param organization_id: ID of the organization the status page belongs to
    :param page_ref_id: (concealed) reference ID of the status page
    :param event_ref_id: (concealed) reference ID of the event
    :param user_id: ID of the user who rejected the event
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    unmasked_event_ref = key_manager.unmask_reference_key(event_ref_id)

    query = "select reject_status_page_event(%s, %s, %s, %s, %s);"
    query_params = (organization_id, unmasked_page_ref, unmasked_event_ref, timestamp, user_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_post(conn, timestamp, organization_id, post_id, user_id):
    '''
    Delete a status page post.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param post_id: ID of the published post to redact
    :param user_id: ID of the user who is redacting the instance
    :errors: AssertionError, DatabaseError, PermissionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(post_id, int)
    assert isinstance(user_id, int)

    query = "select delete_status_page_post(%s, %s, %s, %s);"
    query_params = (post_id, organization_id, user_id, timestamp)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def list_posts(conn, timestamp, org_id, page_ref_id, user_id, is_maintenance=False, post_type=None,
               row_limit=None, row_offset=None, search_words=None, check_adv_perm=False):
    '''
    List of the posts that have been published on a status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the status page belongs to
    :param page_ref_id: reference ID of the status page
    :param user_id: ID of the user who made the request
    :param is_maintenance: True if only maintenance posts are needed
    :param post_type: (str) type of post to fetch
    :param row_limit: number of records to fetch
    :param row_offset: number of records to skip ahead
    :param search_words: (str) keywords to search with
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (list) of published status page instances
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'pg_ref': unmasked_page_ref, 'usr_id': user_id,
                    'sts_pg_comp_type_id': configs.status_page_component_type_id}
    cond = []
    if is_maintenance:
        cond.append(" pst.is_maintenance = true ")
    else:
        cond.append(" pst.is_maintenance = false ")

    if post_type is not None:
        if (is_maintenance and post_type == constants.scheduled_state) or\
                (not is_maintenance and post_type == constants.open_state):
            cond.append(" pst.is_open = true ")
        elif (is_maintenance and post_type == constants.completed_state) or\
                (not is_maintenance and post_type == constants.resolved_state):
            cond.append(" pst.is_open = false ")

    if check_adv_perm:
        cond.append('''
            page_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(sts_pg_comp_type_id)s::smallint
                )
            )
        ''')

    if search_words is not None:
        assert isinstance(search_words, str)
        cond.append('''
            (
                LOWER(title) like '%%' || %(search_words)s || '%%'
                or
                LOWER(message) like '%%' || %(search_words)s || '%%'
            )
        ''')
        query_params['search_words'] = search_words.lower()

    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 = '''
            select pst.post_id, page_post_id, syi.organization_instanceid, pst.created_on, title, message,
                pst.status, pst.maintenance_start, pst.maintenance_end, pst.last_updated_on, pst.resolved_on,
                json_agg(json_build_object(
                    'business_service_ref_id', bus.business_service_ref_id,
                    'service_name', bus.service_name,
                    'status', pco.status
                )) as imp_bus
            from status_page_posts as pst
            join status_page_post_components as pco
                on case
                    when pst.resolved_on is null
                        and pco.post_id = pst.post_id
                        and pco.start_timestamp <= %(timestamp)s
                        and pco.end_timestamp > %(timestamp)s
                    then 1
                    when pst.resolved_on is not null
                        and pco.post_id = pst.post_id
                        and pco.start_timestamp <= pst.resolved_on
                        and pco.end_timestamp >= (pst.resolved_on::timestamp - interval '50 milliseconds')
                        and pco.end_timestamp <= (pst.resolved_on::timestamp + interval '50 milliseconds')
                    then 1
                    else 0
                end = 1
            join business_services as bus
                on bus.business_serviceid = pco.business_serviceid
                    and bus.start_timestamp <= pco.start_timestamp
                    and bus.end_timestamp > pco.start_timestamp
            left join task_instances as syi
                on pst.instanceid is not null
                    and pst.instanceid = syi.instanceid
                    and syi.organization_id = %(org_id)s
            where pst.organization_id = %(org_id)s
                and pst.created_on <= %(timestamp)s
                and pst.page_id in (
                    select page_id from status_pages
                    where page_ref_id = %(pg_ref)s
                        and organization_id = %(org_id)s
                        and start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                )
                {0}
            group by pst.post_id, page_post_id, syi.organization_instanceid,
                pst.created_on, title, message, pst.status, last_updated_on, pst.resolved_on
            order by post_id desc
                {1};
            '''.format(' and ' + ' and '.join(cond) if len(cond) > 0 else '',
                       limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for pst_id, pg_pst_id, org_inst_id, pub_time, ttl, msg, sts, mnt_start, mnt_end, \
                last_upd, res_on, imp_bus in result:

            for item in imp_bus:
                item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                    uuid.UUID(item[var_names.business_service_ref_id]))

            data.append({
                var_names.post_id: pst_id,
                var_names.page_post_id: pg_pst_id,
                var_names.organization_instance_id: org_inst_id,
                var_names.created_on: pub_time,
                var_names.is_maintenance: is_maintenance,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.maintenance_start: mnt_start,
                var_names.maintenance_end: mnt_end,
                var_names.last_update: last_upd,
                var_names.resolved_on: res_on,
                var_names.impacted_business_services: imp_bus
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_post_details(conn, timestamp, org_id, page_ref_id, page_post_id, user_id, check_adv_perm=False):
    '''
    Get the details of a post.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the status page belongs to
    :param page_ref_id: reference ID of the status page
    :param page_post_id: page specific ID of the post
    :param user_id: ID of the user who made the request
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (dict) of post details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(page_post_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'pg_ref': unmasked_page_ref, 'pg_pst_id': page_post_id,
                    'usr_id': user_id, 'sts_pg_comp_type_id': configs.status_page_component_type_id}
    cond = []
    if check_adv_perm:
        cond.append('''
            page_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(sts_pg_comp_type_id)s::smallint
                )
            )
        ''')

    query = '''
            with t1 as (
                select post_id, page_post_id, pst.organization_id, syi.organization_instanceid, created_on,
                    is_maintenance, title, message, pst.status, maintenance_start, maintenance_end, page_impact,
                    next_update_minutes, notify_subscribers, last_updated_on, pst.resolved_on
                from status_page_posts as pst
                left join task_instances as syi
                    on pst.instanceid is not null
                        and pst.instanceid = syi.instanceid
                        and syi.organization_id = %(org_id)s
                where pst.organization_id = %(org_id)s
                    and pst.page_post_id = %(pg_pst_id)s
                    and pst.created_on <= %(timestamp)s
                    and pst.page_id in (
                        select page_id from status_pages
                        where page_ref_id = %(pg_ref)s
                            and organization_id = %(org_id)s
                            and start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                    )
                    {0}
            )
            , t2 as (
                select business_serviceid, business_service_ref_id, service_name
                from business_services
                where organization_id = %(org_id)s
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
            )
            , t3 as (
                select t1.post_id, json_agg(json_build_object(
                    'business_service_ref_id', t2.business_service_ref_id,
                    'service_name', t2.service_name,
                    'status', pco.status
                )) as imp_bus
                from t1
                join status_page_post_components as pco
                    on case
                        when t1.resolved_on is null
                            and pco.post_id = t1.post_id
                            and pco.start_timestamp <= %(timestamp)s
                            and pco.end_timestamp > %(timestamp)s
                        then 1
                        when t1.resolved_on is not null
                            and pco.post_id = t1.post_id
                            and pco.start_timestamp <= t1.resolved_on
                            and pco.end_timestamp >= (t1.resolved_on::timestamp - interval '50 milliseconds')
                            and pco.end_timestamp <= (t1.resolved_on::timestamp + interval '50 milliseconds')
                        then 1
                        else 0
                    end = 1
                join t2 using(business_serviceid)
                group by t1.post_id
            )
            , t4 as (
                select ev.post_id, event_timestamp, ev.status, page_impact, notify_subscribers,
                    next_update_minutes, title, message,
                    json_agg(json_build_object(
                        'business_service_ref_id', t2.business_service_ref_id,
                        'service_name', t2.service_name,
                        'status', spc.status
                    )) as ev_bus
                from status_page_events as ev
                join status_page_event_components as spc using(eventid)
                join t2 using(business_serviceid)
                where ev.post_id in (select post_id from t1)
                    and ev.is_published
                group by ev.post_id, event_timestamp, ev.status, page_impact, notify_subscribers,
                    next_update_minutes, title, message
            )
            , t5 as (
                select post_id, json_agg(json_build_object(
                    'timestamp', event_timestamp,
                    'status', status,
                    'page_impact', page_impact,
                    'notify_subscribers', notify_subscribers,
                    'next_update', next_update_minutes,
                    'title', title,
                    'message', message,
                    'impacted_business_services', ev_bus
                )) as post_events
                from t4
                group by post_id
            )
            select t1.post_id, page_post_id, organization_instanceid, created_on, is_maintenance,
                title, message, status, maintenance_start, maintenance_end, page_impact, next_update_minutes,
                notify_subscribers, last_updated_on, resolved_on, t3.imp_bus, t5.post_events
            from t1
            join t3 using(post_id)
            join t5 using(post_id);
            '''.format(' and ' + ' and '.join(cond) if len(cond) > 0 else '')
    try:
        result = conn.fetch(query, query_params)

        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)

        for pst_id, pg_pst_id, org_inst_id, pub_time, is_mnt, ttl, msg, sts, mnt_start, mnt_end, pg_imp, nxt_upd, \
                ntf_sub, last_upd, res_on, imp_bus, pst_events in result:

            for item in imp_bus:
                item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                    uuid.UUID(item[var_names.business_service_ref_id]))

            if pst_events is not None:
                for item in pst_events:
                    for sub_item in item[var_names.impacted_business_services]:
                        sub_item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                            uuid.UUID(sub_item[var_names.business_service_ref_id]))

            data = {
                var_names.post_id: pst_id,
                var_names.page_post_id: pg_pst_id,
                var_names.organization_instance_id: org_inst_id,
                var_names.created_on: pub_time,
                var_names.is_maintenance: is_mnt,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.maintenance_start: mnt_start,
                var_names.maintenance_end: mnt_end,
                var_names.page_impact: pg_imp,
                var_names.next_update: nxt_upd,
                var_names.notify_subscribers: ntf_sub,
                var_names.last_update: last_upd,
                var_names.resolved_on: res_on,
                var_names.impacted_business_services: imp_bus,
                var_names.events: pst_events
            }
            return data
    except psycopg2.DatabaseError:
        raise


def get_posts_for_monitoring(conn, timestamp, post_ids=None, check_for_auto_update=False):
    '''
    Get the details of status page posts for monitoring. This is only for internal use.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param post_ids: (list) of status page post ids
    :param check_for_auto_update: (boolean) True if maintenance requests that need auto update should be retrieved
    :return: (dict) of dict of post details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)

    query_params = {'timestamp': timestamp}
    conditions = []
    if post_ids is not None and len(post_ids) > 0:
        conditions.append(' post_id = any(%(pst_ids)s) ')
        query_params['pst_ids'] = helpers.get_int_list(post_ids)
    if check_for_auto_update:
        conditions.append(' (is_maintenance and auto_update) ')
        conditions.append(''' (
            sp.max_pending_hours is not null
            and EXTRACT(EPOCH FROM (%(timestamp)s - created_on))/3600 > sp.max_pending_hours
        ) ''')

    query = '''
            with t1 as (
                select post_id, page_post_id, po.organization_id, created_on, is_maintenance, title, message,
                    status, maintenance_start, maintenance_end, auto_update, page_impact, next_update_minutes,
                    notify_subscribers, last_updated_on, resolved_on, sp.page_id, page_ref_id, sp.page_name,
                    sp.page_url, sp.logo_url, sp.page_timezone, sp.page_language, sp.max_pending_hours
                from status_page_posts as po
                join status_pages as sp
                    on sp.page_id = po.page_id
                        and sp.start_timestamp <= %(timestamp)s
                        and sp.end_timestamp > %(timestamp)s
                where is_open
                    {0}
            )
            , t2 as (
                select business_serviceid, business_service_ref_id, service_name
                from business_services
                where organization_id in (select organization_id from t1)
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
            )
            , t3 as (
                select t1.post_id, t2.business_serviceid, t2.business_service_ref_id, t2.service_name, pco.status
                from t1
                join status_page_post_components as pco
                    on case
                        when t1.resolved_on is null
                            and pco.post_id = t1.post_id
                            and pco.start_timestamp <= %(timestamp)s
                            and pco.end_timestamp > %(timestamp)s
                        then 1
                        when t1.resolved_on is not null
                            and pco.post_id = t1.post_id
                            and pco.start_timestamp <= t1.resolved_on
                            and pco.end_timestamp >= (t1.resolved_on::timestamp - interval '50 milliseconds')
                            and pco.end_timestamp <= (t1.resolved_on::timestamp + interval '50 milliseconds')
                        then 1
                        else 0
                    end = 1
                join t2 using(business_serviceid)
            )
            , t4 as (
                select t3.post_id, json_agg(json_build_object(
                    'business_service_id', t3.business_serviceid,
                    'business_service_ref_id', t3.business_service_ref_id,
                    'service_name', t3.service_name,
                    'status', t3.status
                )) as imp_bus
                from t3
                group by t3.post_id
            )
            , t5 as (
                select ev.post_id, event_timestamp, event_type, ev.status, page_impact,
                    notify_subscribers, next_update_minutes, title, message,
                    json_agg(json_build_object(
                        'business_service_ref_id', t2.business_service_ref_id,
                        'service_name', t2.service_name,
                        'status', spc.status
                    )) as ev_bus
                from status_page_events as ev
                join status_page_event_components as spc using(eventid)
                join t2 using(business_serviceid)
                where ev.post_id in (select post_id from t1)
                    and ev.is_published
                group by ev.post_id, event_timestamp, event_type, ev.status, page_impact,
                    notify_subscribers, next_update_minutes, title, message
            )
            , t6 as (
                select post_id, json_agg(json_build_object(
                    'timestamp', event_timestamp,
                    'event_type', event_type,
                    'status', status,
                    'page_impact', page_impact,
                    'notify_subscribers', notify_subscribers,
                    'next_update', next_update_minutes,
                    'title', title,
                    'message', message,
                    'impacted_business_services', ev_bus
                )) as post_events
                from t5
                group by post_id
            )
            , t7 as (
                select page_id, json_agg(json_build_object(
                    'subscriber_id', sub.subscriber_id,
                    'subscriber_ref_id', subscriber_ref_id,
                    'subscription_type', subscription_type,
                    'additional_info', subscription_details
                )) as pg_sub
                from status_page_subscribers as sub
                left join status_page_subscriber_components as comp
                    on comp.subscriber_id = sub.subscriber_id
                        and comp.start_timestamp <= %(timestamp)s
                        and comp.end_timestamp > %(timestamp)s
                where page_id in (select page_id from t1)
                    and sub.start_timestamp <= %(timestamp)s
                    and sub.end_timestamp > %(timestamp)s
                    and sub.is_confirmed
                    and (for_all or comp.business_serviceid in (select business_serviceid from t3))
                group by page_id
            )
            select t1.post_id, page_post_id, organization_id, created_on, is_maintenance, title, message, status,
                maintenance_start, maintenance_end, auto_update, page_impact, next_update_minutes, notify_subscribers,
                last_updated_on, resolved_on, t1.page_id, page_ref_id, page_name, page_url, logo_url, page_language,
                page_timezone, max_pending_hours, t4.imp_bus, t6.post_events, t7.pg_sub
            from t1
            join t4 using(post_id)
            join t6 using(post_id)
            left join t7 on t7.page_id = t1.page_id;
            '''.format(' and (' + ' or '.join(conditions) + ')' if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for pst_id, pg_pst_id, org_id, pub_time, is_mnt, ttl, msg, sts, mnt_start, mnt_end, auto_upd, pg_imp, nxt_upd, \
            ntf_sub, last_upd, res_on, pg_id, pg_ref_id, pg_name, pg_url, logo_url, pg_lang, pg_tz, max_pnd, imp_bus, \
                pst_events, pg_subscribers in result:

            for item in imp_bus:
                item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                    uuid.UUID(item[var_names.business_service_ref_id]))

            if pst_events is not None:
                for item in pst_events:
                    item[var_names.timestamp] = times.get_timestamp_from_string(item[var_names.timestamp])
                    for sub_item in item[var_names.impacted_business_services]:
                        sub_item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                            uuid.UUID(sub_item[var_names.business_service_ref_id]))

            fmt_sub_det = []
            if pg_subscribers is not None:
                for item in pg_subscribers:
                    sub_type_ = item[var_names.subscription_type]
                    sub_det_ = item[var_names.additional_info]
                    fmt_sub_det.append({
                        var_names.subscriber_id: item[var_names.subscriber_id],
                        var_names.subscriber_ref_id: item[var_names.subscriber_ref_id],
                        var_names.subscription_type: sub_type_,
                        var_names.email: sub_det_[var_names.email] if sub_type_ == constants.email else None,
                        var_names.phone: sub_det_[var_names.phone] if sub_type_ == constants.text else None,
                        var_names.url: sub_det_[var_names.url] if sub_type_ == constants.webhook else None,
                        var_names.additional_info: sub_det_ if sub_type_ == intt.slack else None
                    })

            data[pst_id] = {
                var_names.post_id: pst_id,
                var_names.page_post_id: pg_pst_id,
                var_names.organization_id: org_id,
                var_names.page_id: pg_id,
                var_names.page_ref_id: key_manager.conceal_reference_key(pg_ref_id),
                var_names.page_name: pg_name,
                var_names.url: pg_url,
                var_names.logo_url: logo_url,
                var_names.page_timezone: pg_tz,
                var_names.language: pg_lang,
                var_names.max_pending_hours: max_pnd,
                var_names.created_on: pub_time,
                var_names.is_maintenance: is_mnt,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.maintenance_start: mnt_start,
                var_names.maintenance_end: mnt_end,
                var_names.auto_update: auto_upd,
                var_names.page_impact: pg_imp,
                var_names.next_update: nxt_upd,
                var_names.notify_subscribers: ntf_sub,
                var_names.last_update: last_upd,
                var_names.resolved_on: res_on,
                var_names.impacted_business_services: imp_bus,
                var_names.events: pst_events,
                var_names.subscribers: fmt_sub_det
            }
        return data
    except psycopg2.DatabaseError:
        raise


def sync_post_with_system_incident(conn, timestamp, org_id, page_ref_id, post_id, org_inst_id, user_id):
    '''
    Sync a status page post with a system incident.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the post is for
    :param page_ref_id: (concealed) reference ID of the status page
    :param post_id: global ID of the post
    :param org_inst_id: organization instance ID of the system incident to sync with
    :param user_id: ID of the user who is attempting to sync the incident
    :return: instance ID
    :errors: DatabaseError, PermissionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(post_id, int)
    assert isinstance(org_inst_id, int)
    assert isinstance(user_id, int)

    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    new_event_ref_id = key_manager.generate_reference_key()

    query = "select * from sync_status_page_post_with_system_incident (%s, %s, %s, %s, %s, %s, %s);"
    query_params = (post_id, unmasked_page_ref, new_event_ref_id, org_id, timestamp, org_inst_id, user_id,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise PermissionError(errors.err_user_rights)
    except psycopg2.DatabaseError:
        raise


def list_pending_posts(conn, timestamp, org_id, page_ref_id, user_id, row_limit=None, row_offset=None,
                       check_adv_perm=False):
    '''
    List the post that are still pending for approval.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the posts are for
    :param page_ref_id: (concealed) reference ID of the status page
    :param user_id: ID of the user who is making the request
    :param row_limit: number of records to fetch
    :param row_offset: number of records to skip ahead
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (list of dict) of pending posts
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'pg_ref': unmasked_page_ref, 'usr_id': user_id,
                    'sts_pg_comp_type_id': configs.status_page_component_type_id}
    cond = []
    if check_adv_perm:
        cond.append('''
            page_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(sts_pg_comp_type_id)s::smallint
                )
            )
        ''')

    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 = '''
            select event_ref_id, event_timestamp, event_type, evn.post_id, spp.page_post_id, evn.title, evn.message,
                evn.status, users.first_name || ' ' || users.last_name, users.preferred_username
            from status_page_events as evn
            left join status_page_posts as spp
                on evn.post_id is not null
                    and evn.post_id = spp.post_id
            left join users
                on users.user_id = evn.event_by
                    and users.start_timestamp <= %(timestamp)s
                        and users.end_timestamp > %(timestamp)s
            where evn.organization_id = %(org_id)s
                and evn.event_timestamp <= %(timestamp)s
                and evn.page_id in (
                    select page_id from status_pages
                    where page_ref_id = %(pg_ref)s
                        and organization_id = %(org_id)s
                        and is_pending
                )
                {0}
            order by event_timestamp desc
                {1};
            '''.format(' and ' + ' and '.join(cond) if len(cond) > 0 else '',
                       limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for evn_ref, evn_tmsp, evn_type, pst_id, pg_pst_id, ttl, msg, sts, usr_name, usr_pref in result:

            created_by = None
            if usr_name is not None and usr_pref is not None:
                created_by = [usr_name, usr_pref]

            data.append({
                var_names.event_ref_id: key_manager.conceal_reference_key(evn_ref),
                var_names.created_on: evn_tmsp,
                var_names.event_type: evn_type,
                var_names.post_id: pst_id,
                var_names.page_post_id: pg_pst_id,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.created_by: created_by
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_pending_post_details(conn, timestamp, org_id, page_ref_id, event_ref_id, user_id, check_adv_perm=False):
    '''
    Get the details of a pending post.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the posts are for
    :param page_ref_id: (concealed) reference ID of the status page
    :param event_ref_id: (concealed) reference ID of the pending event
    :param user_id: ID of the user who is making the request
    :param check_adv_perm: (boolean) True if advanced team permissions should be checked
    :return: (list of dict) of pending posts
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(user_id, int)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    unmasked_evn_ref = key_manager.unmask_reference_key(event_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'pg_ref': unmasked_page_ref,
                    'evn_ref': unmasked_evn_ref, 'usr_id': user_id,
                    'sts_pg_comp_type_id': configs.status_page_component_type_id}
    cond = []
    if check_adv_perm:
        cond.append('''
            evn.page_id not in (
                select component_id from components_user_cannot_view(
                    %(timestamp)s, %(org_id)s, %(usr_id)s, %(sts_pg_comp_type_id)s::smallint
                )
            )
        ''')

    query = '''
            with t1 as (
                select eventid, event_ref_id, event_timestamp, event_type, evn.post_id, spp.page_post_id,
                    spp.is_maintenance, evn.title, evn.message, evn.status, evn.maintenance_start,
                    evn.maintenance_end, evn.page_impact, evn.next_update_minutes, evn.notify_subscribers
                from status_page_events as evn
                left join status_page_posts as spp
                    on evn.post_id is not null
                        and evn.post_id = spp.post_id
                where evn.organization_id = %(org_id)s
                    and evn.event_timestamp <= %(timestamp)s
                    and evn.event_ref_id = %(evn_ref)s
                    and evn.page_id in (
                        select page_id from status_pages
                        where page_ref_id = %(pg_ref)s
                            and organization_id = %(org_id)s
                            and is_pending
                    )
                    {0}
            )
            , t2 as (
                select eventid, json_agg(json_build_object(
                    'business_service_ref_id', bus.business_service_ref_id,
                    'service_name', bus.service_name,
                    'status', pec.status
                )) as imp_bus
                from status_page_event_components as pec
                join business_services as bus
                    on bus.business_serviceid = pec.business_serviceid
                        and bus.start_timestamp <= %(timestamp)s
                        and bus.end_timestamp > %(timestamp)s
                group by eventid
            )
            select event_ref_id, event_timestamp, event_type, post_id, page_post_id, is_maintenance,
                title, message, status, maintenance_start, maintenance_end, page_impact,
                next_update_minutes, notify_subscribers, t2.imp_bus
            from t1
            join t2 using(eventid);
            '''.format(' and ' + ' and '.join(cond) if len(cond) > 0 else '')
    try:
        result = conn.fetch(query, query_params)

        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)

        for evn_ref, evn_tmsp, evn_type, pst_id, pg_pst_id, is_mnt, ttl, msg, sts, mnt_start, mnt_end, pg_imp, \
                nxt_upd, ntf_sub, imp_bus in result:

            for item in imp_bus:
                item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(
                    uuid.UUID(item[var_names.business_service_ref_id]))

            data = {
                var_names.event_ref_id: key_manager.conceal_reference_key(evn_ref),
                var_names.created_on: evn_tmsp,
                var_names.event_type: evn_type,
                var_names.post_id: pst_id,
                var_names.page_post_id: pg_pst_id,
                var_names.is_maintenance: is_mnt,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.maintenance_start: mnt_start,
                var_names.maintenance_end: mnt_end,
                var_names.page_impact: pg_imp,
                var_names.next_update: nxt_upd,
                var_names.notify_subscribers: ntf_sub,
                var_names.impacted_business_services: imp_bus
            }
            return data
    except psycopg2.DatabaseError:
        raise


def list_status_page_approvers_to_notify(conn, timestamp, page_id=None, page_ref_id=None):
    '''
    Get the list of approvers to notify about a pending event.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param page_id: ID of the status page
    :param page_ref_id: (concealed) reference ID of the status page
    :return: (list) of dict of approver details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert page_id is not None or page_ref_id is not None
    query_params = {'timestamp': timestamp}
    conditions = []
    if page_id is not None:
        assert isinstance(page_id, int)
        conditions.append(' perm.page_id = %(pg_id)s ')
        query_params['pg_id'] = page_id
    if page_ref_id is not None:
        unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)
        conditions.append('''
            perm.page_id in (
                select page_id from status_pages
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and page_ref_id = %(pg_ref)s
            )
        ''')
        query_params['pg_ref'] = unmasked_pg_ref

    query = '''
            with t1 as (
                select perm.user_id, users.first_name, users.last_name, users.email, users.user_language
                from status_page_permissions as perm
                join users
                    on users.user_id = perm.user_id
                        and users.start_timestamp <= %(timestamp)s
                        and users.end_timestamp > %(timestamp)s
                where perm.start_timestamp <= %(timestamp)s
                    and perm.end_timestamp > %(timestamp)s
                    and perm.user_id is not null
                    {0}
            )
            , t2 as (
                select aut.user_id, array_agg(push_token) as push_tokens
                from authentication_tokens as aut
                join (
                    select user_id, max(end_timestamp) as max_end from authentication_tokens
                    where platform_type = 'APP'
                    and end_timestamp >= %(timestamp)s - interval '120 days'
                    and user_id in (select user_id from t1)
                    group by user_id
                ) as foo
                    on aut.user_id = foo.user_id
                        and aut.end_timestamp >= foo.max_end
                where aut.platform_type = 'APP'
                group by aut.user_id
            )
            select t1.user_id, first_name, last_name, user_language, email, push_tokens
            from t1
            left join t2 using(user_id);
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for usr_id_, first_, last_, lang_, eml_, push_ in result:
            data.append({
                var_names.user_id: usr_id_,
                var_names.first_name: first_,
                var_names.last_name: last_,
                var_names.language: lang_,
                var_names.email: eml_,
                var_names.push_token: push_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def validate_and_internalize_event_data(
        conn, timestamp, organization_id, page_ref_id, event_type, title, message, status, page_impact,
        next_update_time, notify_subscribers, impacted_components, post_id, maintenance_start,
        maintenance_end, auto_update, user_id, org_inst_id=None
):
    '''
    Validate the data of a status page incident.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param page_ref_id: (concealed) reference ID of the status page
    :param event_type: type of event
    :param title: title of the incident/maintenance
    :param message: message of the incident/maintenance
    :param status: status of the incident
    :param page_impact: the impact the incident will have on the page
    :param next_update_time: (integer) number of minutes after which an update will be made
    :param notify_subscribers: (boolean) should subscribers be notified or not
    :param impacted_components: (optional) (list) of template component types
    :param post_id: ID of the post if it has already been created
    :param maintenance_start: time when the maintenance should start (for MAINTENANCE event type)
    :param maintenance_end: time when the maintenance should end (for MAINTENANCE event type)
    :param auto_update: (boolean) should the instance be updated automatically (for maintenance events)
    :param user_id: ID of the user creating the event (None if it is automatically created by the system)
    :param org_inst_id: ID of the organization instance ID this post is synced with
    :return: (list) new components
    '''
    new_components = []

    assert event_type in [constants.incident, constants.maintenance, constants.status_update]
    assert isinstance(title, str)
    if message is not None:
        assert isinstance(message, str)

    assert status in configs.status_page_incident_statuses or status in configs.status_page_maintenance_statuses
    if page_impact is not None:
        assert page_impact in configs.status_page_impact_types
    if next_update_time is not None:
        assert isinstance(next_update_time, int)
    if notify_subscribers is not None:
        assert isinstance(notify_subscribers, bool)
    if auto_update is not None:
        assert isinstance(auto_update, bool)

    if impacted_components is not None:
        assert isinstance(impacted_components, list) and len(impacted_components) > 0
        page_bus_dict = db_status_pages.get_status_page_business_services_map(
            conn, timestamp, organization_id, page_ref_id)

        for comp in impacted_components:
            assert isinstance(comp, dict)
            assert len(comp) == 2
            unm_bus_ref = key_manager.unmask_reference_key(comp[var_names.business_service_ref_id])
            comp_sts = comp[var_names.status]
            assert comp_sts is None or comp_sts in configs.status_page_business_service_statuses

            new_components.append({
                var_names.business_service_id: page_bus_dict[unm_bus_ref],
                var_names.status: comp_sts
            })

        assert len(new_components) == len(impacted_components)

    if post_id is not None:
        assert isinstance(post_id, int)
    if event_type == constants.maintenance:
        assert isinstance(maintenance_start, datetime.datetime)
        assert isinstance(maintenance_end, datetime.datetime)
    else:
        assert maintenance_start is None
        assert maintenance_end is None
    if user_id is not None:
        assert isinstance(user_id, int)
    if org_inst_id is not None:
        assert isinstance(org_inst_id, int)

    return new_components