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

from utils import constants, errors, key_manager, times, var_names
from validations import string_validator
import configuration as configs
import datetime
import psycopg2
import uuid


def get_status_page_current_status(conn, timestamp, is_published=True, page_ref_id=None, page_url=None):
    '''
    Get the details of the current state of the status page. It could be the unpublished version as well.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param page_ref_id: reference ID of the page
    :param page_url: URL of the page
    :param is_published: True if the published state of the page is required; False otherwise
    :return: (dict) -> of status page details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)

    unmasked_page_ref = None if page_ref_id is None else key_manager.unmask_reference_key(page_ref_id)
    if page_url is not None:
        assert string_validator.is_web_url(page_url)

    if is_published:
        assert page_url is not None or unmasked_page_ref is not None

        query = 'select * from live_published_status_page(%s, %s, %s);'
        query_params = (timestamp, page_url, unmasked_page_ref,)
    else:
        assert unmasked_page_ref is not None

        query = 'select * from current_status_page(%s, %s);'
        query_params = (timestamp, unmasked_page_ref,)

    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return None

        for pg_ref, ver_id, is_pub, pg_name, pg_url, lyt_type, upt_days, show_hist, sub_meth, logo_url, fav_url, \
            cvr_img_url, clrs, pg_nav, pg_footer, opr_msg, pg_tz, pg_lang, ggl_id, hide_seo, is_pvt, sso_ref, \
                ip_white, pg_comps, pg_posts in result:

            metrics = dict()
            if upt_days is not None:
                check_end = datetime.datetime.now().date() + datetime.timedelta(days=1)
                check_start = check_end - datetime.timedelta(days=upt_days)
                metrics = get_status_page_incident_metrics(
                    conn, timestamp, check_start, check_end, pg_ref, is_published=is_published)

            bus_imp_map = dict()
            if pg_posts is not None:
                for item in pg_posts:
                    for sub_item in item[var_names.impacted_business_services]:
                        bus_ref = uuid.UUID(sub_item[var_names.business_service_ref_id])
                        post_page_impact = item[var_names.page_impact]

                        if item[var_names.is_maintenance]:
                            bus_imp_map[bus_ref] = constants.maintenance
                        else:
                            if bus_ref in bus_imp_map:
                                old_imp = bus_imp_map[bus_ref]
                                if old_imp != constants.maintenance:
                                    if old_imp != constants.outage_state:
                                        bus_imp_map[bus_ref] = post_page_impact
                            else:
                                bus_imp_map[bus_ref] = post_page_impact

                        sub_item[var_names.business_service_ref_id] = key_manager.conceal_reference_key(bus_ref)

            fmt_page_comps = []
            for item in pg_comps:
                cat_metrics = []
                if var_names.category_name in item:
                    bus_serv_info_list = []
                    for x in item[var_names.business_services]:
                        bus_ref = uuid.UUID(x[var_names.business_service_ref_id])
                        bus_metrics = []
                        if upt_days:
                            bus_metrics = metrics[bus_ref]
                            if len(cat_metrics) == 0:
                                cat_metrics = bus_metrics
                            else:
                                for i in range(0, len(bus_metrics)):
                                    for j in range(0, len(cat_metrics)):
                                        if cat_metrics[j][var_names.period] == bus_metrics[i][var_names.period]:
                                            cat_metrics[j][var_names.partial_outage] +=\
                                                bus_metrics[i][var_names.partial_outage]
                                            cat_metrics[j][var_names.outage] += bus_metrics[i][var_names.outage]

                        bus_serv_info_list.append({
                            var_names.service_name: x[var_names.service_name],
                            var_names.business_service_ref_id: key_manager.conceal_reference_key(bus_ref),
                            var_names.status: bus_imp_map[bus_ref]
                            if bus_ref in bus_imp_map else constants.operational_state,
                            var_names.uptime_metrics: bus_metrics
                        })

                    fmt_page_comps.append({
                        var_names.category_name: item[var_names.category_name],
                        var_names.business_services: bus_serv_info_list,
                        var_names.uptime_metrics: cat_metrics
                    })
                else:
                    bus_ref = uuid.UUID(item[var_names.business_service_ref_id])
                    fmt_page_comps.append({
                        var_names.service_name: item[var_names.service_name],
                        var_names.business_service_ref_id: key_manager.conceal_reference_key(bus_ref),
                        var_names.status: bus_imp_map[bus_ref]
                        if item[var_names.business_service_ref_id] in bus_imp_map else constants.operational_state,
                        var_names.uptime_metrics: metrics[bus_ref] if upt_days and bus_ref in metrics else []
                    })

            data = {
                var_names.page_ref_id: key_manager.conceal_reference_key(pg_ref),
                var_names.version: ver_id,
                var_names.is_published: is_pub,
                var_names.page_name: pg_name,
                var_names.url: pg_url,
                var_names.layout_type: lyt_type,
                var_names.logo_url: logo_url,
                var_names.icon_url: fav_url,
                var_names.cover_image_url: cvr_img_url,
                var_names.uptime_bar_days: upt_days,
                var_names.show_past_incidents: show_hist,
                var_names.colors: clrs,
                var_names.subscription_type: sub_meth,
                var_names.navigation_bar: pg_nav,
                var_names.footer: pg_footer,
                var_names.operational_message: opr_msg,
                var_names.page_timezone: pg_tz,
                var_names.language: pg_lang,
                var_names.google_analytics_id: ggl_id,
                var_names.hide_seo: hide_seo,
                var_names.is_private: is_pvt,
                var_names.sso_ref_id: None if sso_ref is None else key_manager.conceal_reference_key(sso_ref),
                var_names.ip_address: ip_white,
                var_names.business_services: fmt_page_comps,
                var_names.posts: [] if pg_posts is None else pg_posts
            }
            return data
    except psycopg2.DatabaseError:
        raise


def get_status_page_incident_metrics(conn, timestamp, check_start, check_end, page_ref_id, is_published=True):
    '''
    Get the incident metrics data for a status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param check_start: (datetime.date) start date for the metrics
    :param check_end: (datetime.date) end date for the metrics
    :param page_ref_id: (unmasked) reference ID of the page
    :param is_published: (boolean) state of the status page -> published or not
    :return: (dict) -> [{business service ref id: [{period: , partial_outage: , outage: ], ...}]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(check_start, datetime.date)
    assert isinstance(check_end, datetime.date)
    assert isinstance(is_published, bool)

    query_params = {'timestamp': timestamp,
                    'check_start': check_start - datetime.timedelta(days=1),
                    'check_end': check_end + datetime.timedelta(days=1),
                    'pg_ref': page_ref_id}

    comp_join = []
    conditions = []
    if is_published:
        comp_join.append('''
            pg_comp.start_timestamp <= pg.published_start and pg_comp.end_timestamp > pg.published_start
        ''')
        conditions.append(" pg.published_start <= %(timestamp)s and pg.published_end > %(timestamp)s ")
    else:
        comp_join.append(" pg_comp.start_timestamp <= %(timestamp)s and pg_comp.end_timestamp > %(timestamp)s ")
        conditions.append(" pg.start_timestamp <= %(timestamp)s and pg.end_timestamp > %(timestamp)s ")

    query = '''
            with t1 as (
                select pg_comp.business_serviceid, bus.business_service_ref_id, pg.page_id, pg.page_timezone
                from status_pages as pg
                join status_page_components as pg_comp
                    on pg_comp.page_id = pg.page_id
                        {0}
                join business_services as bus
                    on bus.business_serviceid = pg_comp.business_serviceid
                        and bus.start_timestamp <= %(timestamp)s
                        and bus.end_timestamp > %(timestamp)s
                where pg.page_ref_id = %(pg_ref)s
                    {1}
            )
            , t2 as (
                select comp.business_serviceid, pst.post_id, pst.page_impact,
                    (pst.created_on at time zone 'UTC' at time zone page_timezone)::date as pst_date
                from t1
                join status_page_posts as pst
                    on pst.page_id = t1.page_id
                        and (pst.created_on at time zone 'UTC' at time zone page_timezone)::timestamp >= %(check_start)s
                        and (pst.created_on at time zone 'UTC' at time zone page_timezone)::timestamp < %(check_end)s
                        and not pst.is_maintenance
                join status_page_post_components as comp
                    on comp.post_id = pst.post_id
                        and comp.business_serviceid = t1.business_serviceid
                        and comp.start_timestamp <= pst.created_on
                        and comp.end_timestamp > pst.created_on
            )
            select t1.business_service_ref_id, t2.pst_date,
                count(t2.post_id) filter (where page_impact = 'MINOR') as minor_inc,
                count(t2.post_id) filter (where page_impact = 'MAJOR') as major_inc
            from t1
            left join t2 using(business_serviceid)
            group by t1.business_service_ref_id, t2.pst_date;
            '''.format(' and ' + ' and '.join(comp_join) if len(comp_join) > 0 else '',
                       ' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for bus_, dt_, deg_, out_ in result:
            if bus_ not in data:
                data[bus_] = dict()
            data[bus_][dt_] = [deg_, out_]

        metrics = dict()
        diff = (check_end - check_start).days
        for bus_ in data:
            metrics[bus_] = []
            for i in range(0, diff):
                curr_dt = check_start + datetime.timedelta(days=i)
                if curr_dt in data[bus_]:
                    metrics[bus_].append({
                        var_names.period: curr_dt,
                        var_names.partial_outage: data[bus_][curr_dt][0],
                        var_names.outage: data[bus_][curr_dt][1]
                    })
                else:
                    metrics[bus_].append({
                        var_names.period: curr_dt,
                        var_names.partial_outage: 0,
                        var_names.outage: 0
                    })
        return metrics
    except psycopg2.DatabaseError:
        raise


def get_status_page_published_posts(conn, timestamp, check_start=None, check_end=None, for_published_page=True,
                                    page_url=None, page_ref_id=None, organization_id=None, is_maintenance=None,
                                    is_open=None, post_id=None):
    '''
    Get the posts that have been made on a status page.
    :param conn: db connection
    :param timestamp: (datetime.datetime) timestamp when this request is being made
    :param check_start: (datetime.date) minimum date to filter posts by
    :param check_end: (datetime.date) maximum date to filter posts by
    :param for_published_page: (boolean) True if posts should only be obtained for published pages; False otherwise
    :param page_url: full url of the page to filter by
    :param page_ref_id: (concealed) reference ID of the page
    :param organization_id: (int) ID of the organization
    :param is_maintenance: (boolean) True if only maintenance posts should be retrieved; False otherwise
    :param is_open: (boolean) True if only currently ongoing incident/maintenance posts should be retrieved
    :param post_id: (int) ID of the post to fetch
    :return: (list of dict) of post details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(for_published_page, bool)

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

    if for_published_page:
        join_conditions_str = " and pg.published_start <= %(timestamp)s and pg.published_end > %(timestamp)s "
    else:
        join_conditions_str = " and pg.start_timestamp <= %(timestamp)s and pg.end_timestamp > %(timestamp)s "

    if check_start is not None:
        assert isinstance(check_start, datetime.date)
        conditions.append(''' (
            (pst.created_on at time zone 'UTC' at time zone page_timezone)::timestamp >= %(start_time)s or
            pst.maintenance_start >= %(start_time)s
        ) ''')
        query_params['start_time'] = check_start
    if check_end is not None:
        assert isinstance(check_end, datetime.date)
        check_end = check_end + datetime.timedelta(days=1)
        conditions.append(" pst.created_on < %(end_time)s ")
        conditions.append(''' (
            (pst.created_on at time zone 'UTC' at time zone page_timezone)::timestamp < %(end_time)s or
            pst.maintenance_start < %(end_time)s
        ) ''')
        query_params['end_time'] = check_end

    if page_url is not None:
        assert string_validator.is_web_url(page_url)
        conditions.append(" pg.page_url = %(pg_url)s ")
        query_params['pg_url'] = page_url

    if page_ref_id is not None:
        unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
        conditions.append(" pg.page_ref_id = %(pg_ref)s ")
        query_params['pg_ref'] = unmasked_page_ref

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

    if is_maintenance is not None:
        assert isinstance(is_maintenance, bool)
        if is_maintenance:
            conditions.append(" pst.is_maintenance ")
        else:
            conditions.append(" not pst.is_maintenance ")

    if is_open is not None:
        assert isinstance(is_open, bool)
        if is_open:
            conditions.append(" pst.is_open ")
        else:
            conditions.append(" not pst.is_open ")

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

    query = '''
            with t1 as (
                select pst.post_id, pst.page_post_id, pst.is_maintenance, pg.page_timezone, pst.created_on,
                    pst.title, pst.message, pst.status, pst.page_impact, pst.next_update_minutes,
                    pst.maintenance_start, pst.maintenance_end,
                    json_agg(json_build_object(
                        'service_name', bus.service_name,
                        'status', comp.status
                    )) as imp_bus
                from status_page_posts as pst
                join status_pages as pg
                    on pg.page_id = pst.page_id
                        {0}
                join status_page_post_components as comp
                    on comp.post_id = pst.post_id
                        and comp.start_timestamp <= pst.created_on
                        and comp.end_timestamp > pst.created_on
                join business_services as bus
                    on bus.business_serviceid = comp.business_serviceid
                        and bus.start_timestamp <= comp.start_timestamp
                        and bus.end_timestamp > comp.start_timestamp
                {1}
                group by pst.page_id, pst.post_id, pst.is_maintenance, pg.page_timezone, pst.created_on,
                    pst.title, pst.message, pst.status, pst.page_impact, pst.next_update_minutes,
                    pst.maintenance_start, pst.maintenance_end
            )
            , t2 as (
                select post_id, json_agg(json_build_object(
                        'timestamp', event_timestamp,
                        'message', message,
                        'status', status
                    ) order by event_timestamp desc) as pst_updates
                from status_page_events
                where event_timestamp <= %(timestamp)s
                    and page_id in (select page_id from t1)
                    and is_published
                group by post_id
            )
            select t1.*, t2.pst_updates
            from t1
            join t2 using(post_id);
            '''.format(join_conditions_str, ' where ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = []
        for p_id, pg_p_id, is_mnt, pg_tz, cre_on, ttl, msg, sts, pg_imp, nxt_upd, mnt_start, mnt_end, \
                imp_bus, p_upd in result:

            if is_mnt:
                post_time = times.switch_region_time(mnt_start, configs.standard_timezone, pg_tz)
            else:
                post_time = times.switch_region_time(cre_on, configs.standard_timezone, pg_tz)

            for item in p_upd:
                item[var_names.timestamp] = times.switch_region_time(
                    times.get_timestamp_from_string(item[var_names.timestamp]),
                    configs.standard_timezone, pg_tz
                )

            data.append({
                var_names.post_id: p_id,
                var_names.page_post_id: pg_p_id,
                var_names.is_maintenance: is_mnt,
                var_names.created_on: post_time,
                var_names.title: ttl,
                var_names.message: msg,
                var_names.status: sts,
                var_names.page_impact: pg_imp,
                var_names.next_update: nxt_upd,
                var_names.maintenance_start: mnt_start,
                var_names.maintenance_end: mnt_end,
                var_names.impacted_business_services: imp_bus,
                var_names.events: p_upd
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_subscription_methods_from_status_page_ref_id(conn, timestamp, page_ref_id):
    '''
    Get the organization ID and the subscription methods allowed to be displayed on a published status page.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param page_ref_id: (concealed) reference ID of the status page
    :return: (tuple) -> organization ID (int), subscription methods (list)
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)

    query = '''
            select organization_id, subscription_methods
            from status_pages
            where page_ref_id = %(pg_ref)s
                and published_start <= %(timestamp)s
                and published_end > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'pg_ref': unmasked_page_ref}
    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