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

from dbqueries import db_business_services, db_teams, db_users
from psycopg2 import errorcodes
from utils import constants, errors, key_manager, var_names
from validations import string_validator
import configuration as configs
import datetime
import json
import psycopg2
import pytz
import uuid


def create_status_page(conn, timestamp, organization_id, to_publish, page_name, page_desc, url_ext, page_url,
                       page_components, layout_type, uptime_bar_days, show_history, subscription_methods, colors,
                       page_navbar, page_footer, operational_message, page_tz, page_lang, google_analytics_id,
                       hide_seo, is_private, sso_ref_id, ip_whitelist, max_pending_hours, auto_post, post_team_refs,
                       edit_team_refs, post_approvers, additional_details=None):
    '''
    Create a new status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization who this status page belongs to
    :param to_publish: boolean that states if the current version should be published or not
    :param page_name: name of the page
    :param page_desc: description of the page
    :param url_ext: the last part of the status page url (this has to be unique across the organization)
    :param page_url: the full page url
    :param page_components: all business services and categories that are associated with this page
    :param layout_type: single or two column layout (1 or 2)
    :param uptime_bar_days: number of days of metrics to show on the uptime bar; None implies no bar will be shown
    :param show_history: whether past incidents should be made visible or not
    :param subscription_methods: the subscription notification methods that are allowed
    :param colors: all the customizable colors
    :param page_navbar: navigation bar link to display
    :param page_footer: list of footer sections and links
    :param operational_message: the message to show when the status page is operational
    :param page_tz: timezone the status page is in
    :param page_lang: language the page is in
    :param google_analytics_id: Google Analytics ID to show on the page for traceability
    :param hide_seo: whether the page should be hidden from search engines or not
    :param is_private: whether the page should be private or not
    :param sso_ref_id: reference ID of the SSO settings to use to restrict access to the page
    :param ip_whitelist: IP addresses that are allowed to access this webpage
    :param max_pending_hours: maximum number of hours to wait before notifying about incidents pending for too long
    :param auto_post: (boolean) to automatically post system incidents on status pages and sync up
    :param post_team_refs: reference IDs of the teams that can post to the page
    :param edit_team_refs: reference IDs of the teams that can edit this page
    :param post_approvers: users who are allowed to approve posts
    :param additional_details: additional details
    :return: (concealed) reference ID of the new status page
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_web_url(page_url)
    if url_ext is not None:
        assert string_validator.is_status_dashboard_url_extension(url_ext)
        for prohibited_ext in configs.status_page_default_extensions:
            assert prohibited_ext not in url_ext.split('/')
    new_components, post_team_ids, edit_team_ids, approver_ids = validate_and_internalize_status_page_data(
        conn, timestamp, organization_id, to_publish, page_name, page_desc, page_components, layout_type,
        uptime_bar_days, show_history, subscription_methods, colors, page_navbar, page_footer, operational_message,
        page_tz, page_lang, google_analytics_id, hide_seo, is_private, sso_ref_id, ip_whitelist, max_pending_hours,
        auto_post, post_team_refs, edit_team_refs, post_approvers, additional_details
    )

    page_url = page_url.rstrip('/')
    new_page_ref_id = key_manager.generate_reference_key()
    req_approval = False if approver_ids is None else True
    page_navbar_json = json.dumps(page_navbar) if page_navbar is not None else None
    page_footer_json = json.dumps(page_footer) if page_footer is not None else None
    add_info = json.dumps(additional_details) if additional_details is not None else None

    query = '''
            select create_status_page(
                %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s
            );
            '''
    query_params = (organization_id, timestamp, constants.end_timestamp, new_page_ref_id, to_publish, page_name,
                    page_desc, url_ext, page_url, json.dumps(new_components), layout_type, uptime_bar_days,
                    show_history, subscription_methods, json.dumps(colors), page_navbar_json, page_footer_json,
                    operational_message, page_tz, page_lang, google_analytics_id, hide_seo,
                    is_private, sso_ref_id, ip_whitelist, max_pending_hours, auto_post,
                    post_team_ids, edit_team_ids, req_approval, approver_ids, add_info,)
    try:
        conn.execute(query, query_params)
        return key_manager.conceal_reference_key(new_page_ref_id)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.UNIQUE_VIOLATION:
            raise LookupError(errors.err_status_page_url_unavailable)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def edit_status_page(conn, timestamp, organization_id, page_ref_id, to_publish, page_name, page_desc, page_components,
                     layout_type, uptime_bar_days, show_history, subscription_methods, colors, page_navbar, page_footer,
                     operational_message, page_tz, page_lang, google_analytics_id, hide_seo, is_private, sso_ref_id,
                     ip_whitelist, max_pending_hours, auto_post, post_team_refs, edit_team_refs, post_approvers,
                     user_id, additional_details=None):
    '''
    Edit an existing status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization who this status page belongs to
    :param page_ref_id: (concealed) reference ID of the page
    :param to_publish: boolean that states if the current version should be published or not
    :param page_name: name of the page
    :param page_desc: description of the page
    :param page_components: all business services and categories that are associated with this page
    :param layout_type: single or two column layout (1 or 2)
    :param uptime_bar_days: number of days of metrics to show on the uptime bar; None implies no bar will be shown
    :param show_history: whether past incidents should be made visible or not
    :param subscription_methods: the subscription notification methods that are allowed
    :param colors: all the customizable colors
    :param page_navbar: navigation bar link to display
    :param page_footer: list of footer sections and links
    :param operational_message: the message to show when the status page is operational
    :param page_tz: timezone the status page is in
    :param page_lang: language the page is in
    :param google_analytics_id: Google Analytics ID to show on the page for traceability
    :param hide_seo: whether the page should be hidden from search engines or not
    :param is_private: whether the page should be private or not
    :param sso_ref_id: reference ID of the SSO settings to use to restrict access to the page
    :param ip_whitelist: IP addresses that are allowed to access this webpage
    :param max_pending_hours: maximum number of hours to wait before notifying about incidents pending for too long
    :param auto_post: (boolean) to automatically post system incidents on status pages and sync up
    :param post_team_refs: reference IDs of the teams that can post to the page
    :param edit_team_refs: reference IDs of the teams that can edit this page
    :param post_approvers: users who are allowed to approve posts
    :param user_id: ID of the user who is editing the page
    :param additional_details: additional details
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)
    new_components, post_team_ids, edit_team_ids, approver_ids = validate_and_internalize_status_page_data(
        conn, timestamp, organization_id, to_publish, page_name, page_desc, page_components, layout_type,
        uptime_bar_days, show_history, subscription_methods, colors, page_navbar, page_footer, operational_message,
        page_tz, page_lang, google_analytics_id, hide_seo, is_private, sso_ref_id, ip_whitelist, max_pending_hours,
        auto_post, post_team_refs, edit_team_refs, post_approvers, additional_details
    )

    req_approval = False if approver_ids is None else True
    page_navbar_json = json.dumps(page_navbar) if page_navbar is not None else None
    page_footer_json = json.dumps(page_footer) if page_footer is not None else None
    add_info = json.dumps(additional_details) if additional_details is not None else None

    query = '''
            select edit_status_page(
                %s, %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s
            );
            '''
    query_params = (organization_id, timestamp, constants.end_timestamp, unmasked_pg_ref, to_publish, page_name,
                    page_desc, json.dumps(new_components), layout_type, uptime_bar_days, show_history,
                    subscription_methods, json.dumps(colors), page_navbar_json, page_footer_json, operational_message,
                    page_tz, page_lang, google_analytics_id, hide_seo, is_private,
                    sso_ref_id, ip_whitelist, max_pending_hours, auto_post, post_team_ids,
                    edit_team_ids, req_approval, approver_ids, add_info, user_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_status_page(conn, timestamp, organization_id, page_ref_id, user_id):
    '''
    Delete a status page.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param page_ref_id: (concealed) reference ID of the status page
    :param user_id: ID of the user who is deleting the page
    :return: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query = "select delete_status_page(%s, %s, %s, %s);"
    query_params = (organization_id, timestamp, unmasked_pg_ref, user_id,)
    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 publish_status_page(conn, timestamp, organization_id, page_ref_id, version_id, user_id):
    '''
    Publish a status page.
    :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 version_id: version to publish
    :param user_id: ID of the user publishing the page
    :return: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query = "select publish_status_page(%s, %s, %s, %s, %s, %s);"
    query_params = (organization_id, timestamp, constants.end_timestamp, unmasked_pg_ref, version_id, user_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def unpublish_status_page(conn, timestamp, organization_id, page_ref_id, user_id):
    '''
    Unpublish a status page.
    :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 user_id: ID of the user who is unpublishing the page
    :return: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

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


def list_status_pages(conn, timestamp, organization_id):
    '''
    Get the list of all status pages that are there in an organization along with a quick summary of the ongoing
    incidents, pending items and maintenances.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :return: (list of dict) of status page basic details and summary of ongoing events
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            with t1 as (
                select page_id, page_name, page_ref_id, page_url, version_id
                from status_pages
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
            )
            , t2 as (
                select page_id, version_id as published_version_id
                from status_pages
                where is_published
                    and published_start <= %(timestamp)s
                    and published_end > %(timestamp)s
                    and page_id in (select page_id from t1)
            )
            , t3 as (
                select page_id,
                    count(post_id) filter (where is_maintenance = false) as inc_count,
                    count(post_id) filter (where is_maintenance = true) as mnt_count
                from status_page_posts
                where created_on <= %(timestamp)s
                    and organization_id = %(org_id)s
                    and is_open = true
                group by page_id
            )
            , t4 as (
                select page_id, count(eventid) as pnd_count
                from status_page_events
                where event_timestamp <= %(timestamp)s
                    and organization_id = %(org_id)s
                    and is_pending = true
                group by page_id
            )
            select t1.page_name, t1.page_ref_id, t1.page_url, version_id, t2.published_version_id,
                t3.inc_count, t4.pnd_count, t3.mnt_count
            from t1
            left join t2 using(page_id)
            left join t3 using(page_id)
            left join t4 using(page_id);
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for name_, ref_, url_, curr_ver_, pub_ver_, inc_, pnd_, mnt_ in result:
            data.append({
                var_names.page_name: name_,
                var_names.page_ref_id: key_manager.conceal_reference_key(ref_),
                var_names.url: url_,
                var_names.version: curr_ver_,
                var_names.published_version: pub_ver_,
                var_names.is_published: False if pub_ver_ is None else True,
                var_names.incident_count: inc_ if inc_ is not None else 0,
                var_names.pending: pnd_ if pnd_ is not None else 0,
                var_names.maintenances: mnt_ if mnt_ is not None else 0
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_status_page_details(conn, timestamp, organization_id, page_ref_id):
    '''
    Get the details of a status page. This should be used for viewing and editing the configurations.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param page_ref_id: reference ID of the page
    :return: (dict) -> of status page details
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query = '''
            with t1 as (
                select spg.page_id, page_ref_id, page_name, spg.description, url_extension, page_url, layout_type,
                    uptime_bar_days, show_history, subscription_methods, logo_url, favicon_url, cover_image_url, colors,
                    page_navbar, page_footer, operational_message, page_timezone, page_language, google_analytics_id,
                    hide_seo, is_private, eso.sso_name, eso.sso_ref_id, ip_whitelist, max_pending_hours, auto_post,
                    require_approval
                from status_pages as spg
                left join external_sso as eso
                    on spg.sso_id is not null
                        and eso.sso_id = spg.sso_id
                        and eso.start_timestamp <= %(timestamp)s
                        and eso.end_timestamp > %(timestamp)s
                where spg.start_timestamp <= %(timestamp)s
                    and spg.end_timestamp > %(timestamp)s
                    and spg.organization_id = %(org_id)s
                    and spg.page_ref_id = %(pg_ref)s
            )
            , t2 as (
                select * from get_status_page_components(%(org_id)s, %(timestamp)s, %(pg_ref)s)
            )
            , t3 as (
                select page_id,
                    array_agg(team_ref_id) filter (where permission_type = 1) as pg_post_perms,
                    array_agg(team_ref_id) filter (where permission_type = 2) as pg_edit_perms
                from status_page_permissions as perm
                join teams
                    on perm.team_id is not null
                        and teams.team_id = perm.team_id
                        and teams.start_timestamp <= %(timestamp)s
                        and teams.end_timestamp > %(timestamp)s
                where perm.start_timestamp <= %(timestamp)s
                    and perm.end_timestamp > %(timestamp)s
                    and perm.team_id is not null
                    and page_id in (select page_id from t1)
                group by page_id
            )
            , t4 as (
                select page_id, array_agg(preferred_username) as pg_user_perms
                from status_page_permissions as perm
                join users
                    on perm.user_id is not null
                        and 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 page_id in (select page_id from t1)
                group by page_id
            )
            select t1.*, t2.pg_cmps, t3.pg_post_perms, t3.pg_edit_perms,
                t4.pg_user_perms
            from t1
            join t2 on t1.page_id = t2.pg_id
            left join t3 using(page_id)
            left join t4 using(page_id);
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'pg_ref': unmasked_pg_ref}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)

        for pg_id, pg_ref, pg_name, desc, url_ext, pg_url, lyt, upt_days, show_hist, sub_meth, logo_url, favicon_url, \
            cover_img_url, clrs, pg_nav, pg_footer, opr_msg, pg_tz, pg_lang, ggl_id, hide_seo, is_pvt, \
            sso_name, sso_ref, ip_white, max_pnd, auto_post, req_apv, pg_comps, post_perm, \
                edit_perm, apv_perm in result:

            fmt_components = []
            for item in pg_comps:
                if var_names.category_name in item:
                    fmt_components.append({
                        var_names.category_name: item[var_names.category_name],
                        var_names.business_services: [
                            {
                                var_names.service_name: x[var_names.service_name],
                                var_names.business_service_ref_id: key_manager.conceal_reference_key(
                                    uuid.UUID(x[var_names.business_service_ref_id])
                                )
                            } for x in item[var_names.business_services]
                        ]
                    })
                else:
                    fmt_components.append({
                        var_names.service_name: item[var_names.service_name],
                        var_names.business_service_ref_id: key_manager.conceal_reference_key(
                            uuid.UUID(item[var_names.business_service_ref_id])
                        )
                    })

            data = {
                var_names.page_ref_id: key_manager.conceal_reference_key(pg_ref),
                var_names.page_name: pg_name,
                var_names.description: desc,
                var_names.url_extension: url_ext,
                var_names.url: pg_url,
                var_names.business_services: fmt_components,
                var_names.logo_url: logo_url,
                var_names.icon_url: favicon_url,
                var_names.cover_image_url: cover_img_url,
                var_names.layout_type: lyt,
                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: [sso_name, key_manager.conceal_reference_key(sso_ref)] if sso_ref is not None else None,
                var_names.ip_address: ip_white,
                var_names.max_pending_hours: max_pnd,
                var_names.auto_post: auto_post,
                var_names.post_permissions: [key_manager.conceal_reference_key(x) for x in post_perm]
                if post_perm is not None else None,
                var_names.edit_permissions: [key_manager.conceal_reference_key(x) for x in edit_perm]
                if edit_perm is not None else None,
                var_names.require_approval: req_apv,
                var_names.approvers: apv_perm
            }
            return data
    except psycopg2.DatabaseError:
        raise


def get_basic_status_pages_list(conn, timestamp, organization_id):
    '''
    Get the basic status pages 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) -> [ [status page name, status page ref id], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select page_name, page_ref_id
            from status_pages
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
            order by page_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_status_page_components(conn, timestamp, organization_id, page_ref_id):
    '''
    Get the components (categories and business services) associated with the published version of a status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param page_ref_id: reference ID of the status page
    :return: (dict) -> {ref_id: business service ID, ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query = "select * from get_status_page_components(%s, %s, %s);"
    query_params = (organization_id, timestamp, unmasked_pg_ref,)
    try:
        result = conn.fetch(query, query_params)
        fmt_components = []
        if len(result) > 0:
            pg_comps = result[0][1]
            for item in pg_comps:
                if var_names.category_name in item:
                    fmt_components.append({
                        var_names.category_name: item[var_names.category_name],
                        var_names.business_services: [
                            {
                                var_names.service_name: x[var_names.service_name],
                                var_names.business_service_ref_id: key_manager.conceal_reference_key(
                                    uuid.UUID(x[var_names.business_service_ref_id])
                                )
                            } for x in item[var_names.business_services]
                        ]
                    })
                else:
                    fmt_components.append({
                        var_names.service_name: item[var_names.service_name],
                        var_names.business_service_ref_id: key_manager.conceal_reference_key(
                            uuid.UUID(item[var_names.business_service_ref_id])
                        )
                    })

        return fmt_components
    except psycopg2.DatabaseError:
        raise


def get_status_page_business_services_map(conn, timestamp, organization_id, page_ref_id, is_published=False):
    '''
    Get a dictionary of status page business service reference IDs mapped to their IDs.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param page_ref_id: reference ID of the status page
    :param is_published: (boolean) True if only the published business services are wanted; False otherwise
    :return: (dict) -> {ref_id: business service ID, ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query = "select * from get_status_page_business_services(%s, %s, %s, %s);"
    query_params = (organization_id, timestamp, unmasked_pg_ref, is_published,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for bus_id, bus_ref in result:
            data[bus_ref] = bus_id
        return data
    except psycopg2.DatabaseError:
        raise


def validate_and_internalize_status_page_data(
        conn, timestamp, org_id, to_publish, page_name, page_desc, page_components, layout_type, uptime_bar_days,
        show_history, subscription_methods, colors, page_navbar, page_footer, operational_message, page_tz, page_lang,
        google_analytics_id, hide_seo, is_private, sso_ref_id, ip_whitelist, max_pending_hours, auto_post,
        post_team_refs, edit_team_refs, post_approvers, additional_details=None
):
    '''
    Validate the details of a status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization who this status page belongs to
    :param to_publish: boolean that states if the current version should be published or not
    :param page_name: name of the page
    :param page_desc: description of the page
    :param page_components: all business services and categories that are associated with this page
    :param layout_type: single or two column layout (1 or 2)
    :param uptime_bar_days: number of days of metrics to show on the uptime bar; None implies no bar will be shown
    :param show_history: whether past incidents should be made visible or not
    :param subscription_methods: the subscription notification methods that are allowed
    :param colors: all the customizable colors
    :param page_navbar: navigation bar link to display
    :param page_footer: list of footer sections and links
    :param operational_message: the message to show when the status page is operational
    :param page_tz: timezone the status page is in
    :param page_lang: language the page is in
    :param google_analytics_id: Google Analytics ID to show on the page for traceability
    :param hide_seo: whether the page should be hidden from search engines or not
    :param is_private: whether the page should be private or not
    :param sso_ref_id: reference ID of the SSO settings to use to restrict access to the page
    :param ip_whitelist: IP addresses that are allowed to access this webpage
    :param max_pending_hours: maximum number of hours to wait before notifying about incidents pending for too long
    :param auto_post: (boolean) to automatically post system incidents on status pages and sync up
    :param post_team_refs: reference IDs of the teams that can post to the page
    :param edit_team_refs: reference IDs of the teams that can edit this page
    :param post_approvers: users who are allowed to approve posts
    :param additional_details: additional details
    :return: (tuple) -> component IDs, post teams IDs, edit team IDs, approver user IDs
    '''
    bus_srv_dict = db_business_services.get_business_service_ids_from_ref_ids(conn, timestamp, org_id)

    new_components, post_tm_ids, edit_tm_ids, apv_user_ids = [], None, None, None

    # basic info
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(to_publish, bool)
    assert string_validator.is_standard_name(page_name)
    if page_desc is not None:
        assert isinstance(page_desc, str)

    # components
    assert isinstance(page_components, list)
    for i in range(0, len(page_components)):
        comp = page_components[i]
        assert isinstance(comp, dict)
        if var_names.category_name in comp:
            assert string_validator.is_standard_name(comp[var_names.category_name])
            assert len(comp) == 2
            assert var_names.business_service_ref_id in comp
            cat_bus = []
            for b in range(0, len(comp[var_names.business_service_ref_id])):
                bus = comp[var_names.business_service_ref_id][b]
                unm_bus_ref = key_manager.unmask_reference_key(bus)
                cat_bus.append({
                    var_names.ranking: b,
                    var_names.business_service_id: bus_srv_dict[unm_bus_ref]
                })

            new_components.append({
                var_names.component_type: 'category',
                var_names.category_name: comp[var_names.category_name],
                var_names.ranking: i,
                var_names.business_services: cat_bus
            })
        else:
            assert len(comp) == 1
            assert var_names.business_service_ref_id in comp
            unm_bus_ref = key_manager.unmask_reference_key(comp[var_names.business_service_ref_id])
            new_components.append({
                var_names.component_type: 'business_service',
                var_names.ranking: i,
                var_names.business_service_id: bus_srv_dict[unm_bus_ref]
            })

    # design
    assert layout_type in [1, 2]
    if uptime_bar_days is not None:
        assert isinstance(uptime_bar_days, int)
    assert isinstance(show_history, bool)
    if subscription_methods is not None:
        assert set(subscription_methods).issubset(set(configs.status_page_subscription_notification_methods))

    flexible_colors = [
        var_names.background_color, var_names.navbar_color, var_names.footer_color,
        var_names.font_color, var_names.light_font_color, var_names.link_color,
        var_names.operational_color, var_names.outage_color, var_names.warning_color,
        var_names.border_color, var_names.no_data_color, var_names.graph_color
    ]
    footer_item_keys = [var_names.tab_name, var_names.link]
    link_item_keys = [var_names.title, var_names.link]
    assert len(colors) == len(flexible_colors) and set(list(colors.keys())).issubset(set(flexible_colors))
    for key in colors:
        assert string_validator.is_hex_color(colors[key])
    if page_navbar is not None:
        assert isinstance(page_navbar, dict)
        assert len(page_navbar) == len(link_item_keys) and set(list(page_navbar.keys())).issubset(set(link_item_keys))
    if page_footer is not None:
        assert isinstance(page_footer, list) and len(page_footer) <= 2
        for item in page_footer:
            assert isinstance(item, dict)
            assert len(item) == len(footer_item_keys) and set(list(set(item.keys()))).issubset(set(footer_item_keys))
            for sub_item in item[var_names.link]:
                assert isinstance(sub_item, dict)
                assert len(sub_item) == len(link_item_keys) and set(list(sub_item.keys())).issubset(set(link_item_keys))

    assert isinstance(operational_message, str)
    assert page_tz in pytz.all_timezones
    assert page_lang in configs.allowed_languages
    assert isinstance(google_analytics_id, str)
    assert isinstance(hide_seo, bool)
    assert isinstance(is_private, bool)
    if is_private:
        assert sso_ref_id is not None or ip_whitelist is not None
        if sso_ref_id is not None:
            key_manager.unmask_reference_key(sso_ref_id)

        if ip_whitelist is not None:
            assert isinstance(ip_whitelist, list)
            for ip in ip_whitelist:
                assert string_validator.is_valid_ip_address(ip)
    else:
        assert sso_ref_id is None
        assert ip_whitelist is None

    # permissions
    assert isinstance(auto_post, bool)
    if max_pending_hours is not None:
        assert isinstance(max_pending_hours, int)

    if post_team_refs is not None or edit_team_refs is not None:
        team_dict = db_teams.get_team_ids_from_ref_ids(conn, timestamp, org_id, as_dict=True)
        if post_team_refs is not None:
            assert isinstance(post_team_refs, list)
            post_tm_ids = []
            for ref_ in post_team_refs:
                unm_post = key_manager.unmask_reference_key(ref_)
                assert unm_post in team_dict
                post_tm_ids.append(team_dict[unm_post])
        if edit_team_refs is not None:
            assert isinstance(edit_team_refs, list)
            edit_tm_ids = []
            for ref_ in edit_team_refs:
                unm_edit = key_manager.unmask_reference_key(ref_)
                assert unm_edit in team_dict
                edit_tm_ids.append(team_dict[unm_edit])

    if post_approvers is not None:
        assert isinstance(post_approvers, list)
        user_dict = db_users.get_user_ids_from_preferred_usernames(conn, timestamp, org_id, as_dict=True)
        apv_user_ids = []
        for item in post_approvers:
            assert string_validator.is_valid_preferred_username(item)
            assert item in user_dict
            apv_user_ids.append(user_dict[item])

    if additional_details is not None:
        assert isinstance(additional_details, dict)

    return new_components, post_tm_ids, edit_tm_ids, apv_user_ids


def update_state_page_images_info(conn, timestamp, org_id, page_ref_id, images):
    '''
    Updates the greeting and ending audio file information only. This expects new audio files to have been stored.
    :param conn: db connection
    :param timestamp: timestamp when this live call routing specification is being created
    :param org_id: ID of the organization the live call routing is for
    :param page_ref_id: (concealed) reference ID of the status page
    :param images: (dict) -> {image type url: url, ...}
    :errors: AssertionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(images, dict) and len(images) > 0
    assert set(images.keys()).issubset({var_names.logo_url, var_names.icon_url, var_names.cover_image_url})
    unmasked_ref_id = key_manager.unmask_reference_key(page_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': org_id, 'ref_id': unmasked_ref_id}
    upd_stmt = []

    # logo and favicon must always be provided, but cover image is optional
    if var_names.logo_url in images:
        assert string_validator.is_web_url(images[var_names.logo_url])
        upd_stmt.append(" logo_url = %(logo)s ")
        query_params['logo'] = images[var_names.logo_url]

    if var_names.icon_url in images:
        assert string_validator.is_web_url(images[var_names.icon_url])
        upd_stmt.append(" favicon_url = %(fvc)s ")
        query_params['fvc'] = images[var_names.icon_url]

    if var_names.cover_image_url in images:
        if images[var_names.cover_image_url] is not None:
            assert string_validator.is_web_url(images[var_names.cover_image_url])
        upd_stmt.append(" cover_image_url = %(cvr)s ")
        query_params['cvr'] = images[var_names.cover_image_url]

    # We update the entry directly because file history does not seem important as the actual file is deleted itself.
    query = '''
            update status_pages set {0}
            where page_ref_id = %(ref_id)s
                and organization_id = %(org_id)s
                and start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s;
            '''.format(','.join(upd_stmt))
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_status_page_published_image_urls(conn, timestamp, org_id, page_ref_id):
    '''
    Get ths urls of all the images associated with a published status page.
    :param conn: db connection
    :param timestamp: timestamp when this live call routing specification is being created
    :param org_id: ID of the organization the live call routing is for
    :param page_ref_id: (concealed) reference ID of the status page
    :errors: AssertionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    unmasked_ref_id = key_manager.unmask_reference_key(page_ref_id)

    query = '''
            select logo_url, favicon_url, cover_image_url
            from status_pages
            where page_ref_id = %(ref_id)s
                and organization_id = %(org_id)s
                and is_published
                and published_start <= %(timestamp)s
                and published_end > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': org_id, 'ref_id': unmasked_ref_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for logo_, fav_, cover_ in result:
            data = {
                var_names.logo_url: logo_,
                var_names.icon_url: fav_,
                var_names.cover_image_url: cover_
            }
        return data
    except psycopg2.DatabaseError:
        raise


def get_status_page_published_url(conn, timestamp, page_ref_id):
    '''
    Get the live url of a status page.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param page_ref_id: (concealed) reference ID of the status page
    :return: (str) page url
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)

    query = '''
            select page_url from status_pages
            where is_published
                and published_start <= %(timestamp)s
                and published_end > %(timestamp)s
                and page_ref_id = %(pg_ref)s;
            '''
    query_params = {'timestamp': timestamp, 'pg_ref': unmasked_page_ref}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError
        return result[0][0]
    except psycopg2.DatabaseError:
        raise