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

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


def add_status_page_subscriber(conn, timestamp, organization_id, page_ref_id, subscribers_info):
    '''
    Add a new status page subscriber.
    :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
    :param subscribers_info: (list of dict) of subscribers information
    :errors: AssertionError, DatabaseError, LookupError
    '''
    unmasked_pg_ref, new_subscribers_info = validate_and_internalize_status_page_subscriber_data(
        conn, timestamp, organization_id, page_ref_id, subscribers_info
    )

    query = '''
            select add_status_page_subscriber(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''

    query_params_list = []
    all_subscriber_refs = []
    for item in new_subscribers_info:
        bus_srv = item[var_names.business_services]
        for_all_upd = True if bus_srv is None else False
        sub_ref = key_manager.generate_reference_key()

        query_params_list.append((
            organization_id, timestamp, constants.end_timestamp, unmasked_pg_ref, sub_ref,
            item[var_names.subscription_type], json.dumps(item[var_names.subscriber_details]), for_all_upd, bus_srv
        ))
        all_subscriber_refs.append(sub_ref)
    try:
        conn.execute_batch(query, query_params_list)
        return [key_manager.conceal_reference_key(x) for x in all_subscriber_refs]
    except psycopg2.DatabaseError:
        raise


def delete_status_page_subscriber(conn, timestamp, organization_id, page_ref_id, subscribers):
    '''
    Delete a status page subscriber.
    :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
    :param subscribers: (list) of reference ID of the subscribers
    :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)
    unmasked_sub_ref = [key_manager.unmask_reference_key(x) for x in subscribers]

    query = "select delete_status_page_subscriber(%s, %s, %s, %s);"
    query_params = (organization_id, timestamp, unmasked_pg_ref, unmasked_sub_ref,)
    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 confirm_status_page_subscription(conn, timestamp, page_ref_id, subscriber_ref_id):
    '''
    Confirm a status page subscription.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param page_ref_id: (concealed) reference ID of the page
    :param subscriber_ref_id: (concealed) reference ID of the subscriber
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    unmasked_sub_ref = key_manager.unmask_reference_key(subscriber_ref_id)

    query = '''
            update status_page_subscribers set is_confirmed = true
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and subscriber_ref_id = %(sub_ref)s
                and 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
                )
            returning subscriber_ref_id;
            '''
    query_params = {'timestamp': timestamp, 'pg_ref': unmasked_page_ref, 'sub_ref': unmasked_sub_ref}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return False
        return True
    except psycopg2.DatabaseError:
        raise


def list_status_page_subscribers(conn, timestamp, organization_id, page_ref_id, subscription_type=None, keywords=None,
                                 row_limit=None, row_offset=None, subscribers=None):
    '''
    List the subscribers of a status page. This is for displaying the list of subscribers on the web 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 the subscribers belong to
    :param subscription_type: (optional) subscription type to filter by
    :param keywords: (optional) keywords to search with
    :param row_limit: (optional) number of subscribers to fetch
    :param row_offset: (optional) number of subscribers to skip ahead
    :param subscribers: (optional) list of reference IDs of the subscribers
    :return: (list of dict) of subscriber details
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'pg_ref': unmasked_pg_ref}
    conditions = []
    if subscription_type is not None:
        assert subscription_type in configs.status_page_subscription_notification_methods
        conditions.append(' subscription_type = %(sub_type)s ')
        query_params['sub_type'] = subscription_type

    if keywords is not None:
        assert isinstance(keywords, str)
        conditions.append(" subscription_details::text like '%%' || %(search_words)s || '%%' ")
        query_params['search_words'] = keywords

    if subscribers is not None:
        assert isinstance(subscribers, list)
        unmasked_sub_refs = [key_manager.unmask_reference_key(x) for x in subscribers]
        conditions.append(" subscriber_ref_id = any(%(sub_ref)s) ")
        query_params['sub_ref'] = unmasked_sub_refs

    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 = '''
            with t1 as (
                select subscriber_id, subscriber_ref_id, subscription_type, subscription_details, for_all, is_confirmed
                from status_page_subscribers
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and page_id in (
                        select page_id from status_pages
                        where start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                            and organization_id = %(org_id)s
                            and page_ref_id = %(pg_ref)s
                    )
                    {0}
                order by subscriber_id desc
                {1}
            )
            , t2 as (
                select subscriber_id, json_agg(json_build_object(
                    'service_name', service_name,
                    'business_service_ref_id', business_service_ref_id
                )) as sub_bus
                from status_page_subscriber_components as sub
                join business_services as bus using(business_serviceid)
                where sub.subscriber_id in (select subscriber_id from t1)
                    and sub.start_timestamp <= %(timestamp)s
                    and sub.end_timestamp > %(timestamp)s
                    and bus.start_timestamp <= %(timestamp)s
                    and bus.end_timestamp > %(timestamp)s
                group by subscriber_id
            )
            select t1.subscriber_ref_id, t1.subscription_type, t1.subscription_details,
                t1.for_all, t1.is_confirmed, t2.sub_bus
            from t1
            left join t2 using(subscriber_id)
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '', limit_cond)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for sub_ref_, sub_type_, sub_det_, for_all_, is_conf_, for_bus_ in result:
            data.append({
                var_names.subscriber_ref_id: key_manager.conceal_reference_key(sub_ref_),
                var_names.subscription_type: sub_type_,
                var_names.all_updates: for_all_,
                var_names.is_confirmed: is_conf_,
                var_names.business_services: [
                    [x[var_names.service_name],
                     key_manager.conceal_reference_key(uuid.UUID(x[var_names.business_service_ref_id]))]
                    for x in for_bus_
                ] if for_bus_ is not None and not for_all_ else None,
                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
            })
        return data
    except psycopg2.DatabaseError:
        raise


def list_status_page_subscribers_to_notify(conn, timestamp, post_id=None):
    '''
    List the status page subscribers who should be notified. This is for internal use to dispatch notifications.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param post_id: ID of the post to notify for
    :return: (list) of subscribers and their details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(post_id, int)

    query_params = {'timestamp': timestamp, 'pst_id': post_id}
    query = '''
            select sub.subscriber_id, subscriber_ref_id, subscription_type, subscription_details
            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 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 status_page_post_components
                        where post_id = %(pst_id)s
                            and start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                    )
                )
            '''
    try:
        result = conn.fetch(query, query_params)
        data = []
        for sub_id_, sub_ref_, sub_type_, sub_det_ in result:
            data.append({
                var_names.subscriber_id: sub_id_,
                var_names.subscriber_ref_id: key_manager.conceal_reference_key(sub_ref_),
                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
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_status_page_subscribers_info_for_confirmation(conn, timestamp, page_ref_id, subscriber_ref_ids):
    '''
    Get the subscriber details needed to send confirmation requests.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param page_ref_id: (concealed) reference ID of the page
    :param subscriber_ref_ids: (list) of subscriber reference IDs
    :return: (list) of subscribers and their details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    unmasked_page_ref = key_manager.unmask_reference_key(page_ref_id)
    unmasked_sub_refs = [key_manager.unmask_reference_key(x) for x in subscriber_ref_ids]

    query_params = {'timestamp': timestamp, 'pg_ref': unmasked_page_ref, 'sub_ref': unmasked_sub_refs}
    query = '''
            select sub.subscriber_id, subscriber_ref_id, subscription_type, subscription_details,
                sp.page_name, sp.page_url, sp.page_language, sp.logo_url
            from status_page_subscribers as sub
            join status_pages as sp
                on sp.page_id = sp.page_id
                    and sp.start_timestamp <= %(timestamp)s
                    and sp.end_timestamp > %(timestamp)s
            where sub.start_timestamp <= %(timestamp)s
                and sub.end_timestamp > %(timestamp)s
                and sp.page_ref_id = %(pg_ref)s
                and sub.subscriber_ref_id = any(%(sub_ref)s)
                and (sub.is_confirmed is null or not sub.is_confirmed);
            '''
    try:
        result = conn.fetch(query, query_params)
        page_name, page_url, page_lang, logo_url, data = None, None, None, None, []
        for sub_id_, sub_ref_, sub_type_, sub_det_, pg_name_, pg_url_, pg_lang_, pg_logo_ in result:
            if page_name is None:
                page_name, page_url, page_lang, logo_url = pg_name_, pg_url_, pg_lang_, pg_logo_
            data.append({
                var_names.subscriber_id: sub_id_,
                var_names.subscriber_ref_id: key_manager.conceal_reference_key(sub_ref_),
                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
            })
        return page_name, page_url, page_lang, logo_url, data
    except psycopg2.DatabaseError:
        raise


def validate_and_internalize_status_page_subscriber_data(conn, timestamp, organization_id, page_ref_id,
                                                         subscribers_info):
    '''
    Validate status page subscriber data.
    :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
    :param subscribers_info: (list of dict) of subscribers information
    :return: (tuple) -> unmasked business service ID, list of business service IDs (or null)
    '''
    new_subscribers_info = []
    page_bus_dict = None

    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    unmasked_pg_ref = key_manager.unmask_reference_key(page_ref_id)

    for info in subscribers_info:
        try:
            assert var_names.subscription_type in info
            assert var_names.business_services in info

            sub_type = info[var_names.subscription_type]
            sub_bus = info[var_names.business_services]
            sub_det = dict()
            assert sub_type in configs.status_page_subscription_notification_methods

            if sub_type == constants.email:
                assert var_names.email in info
                assert string_validator.is_email_address(info[var_names.email])
                sub_det[var_names.email] = info[var_names.email]
            elif sub_type == constants.text:
                assert var_names.phone in info
                assert string_validator.is_phone_number(info[var_names.phone])
                sub_det[var_names.phone] = info[var_names.phone]
            elif sub_type == constants.webhook:
                assert var_names.url in info
                assert string_validator.is_web_url(info[var_names.url])
                sub_det[var_names.url] = info[var_names.url]

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

                for bus_ref_id in sub_bus:
                    unm_bus_ref = key_manager.unmask_reference_key(bus_ref_id)
                    new_bus_serv_ids.append(page_bus_dict[unm_bus_ref])

                assert len(new_bus_serv_ids) == len(sub_bus)

            new_subscribers_info.append({
                var_names.subscription_type: sub_type,
                var_names.subscriber_details: sub_det,
                var_names.business_services: new_bus_serv_ids if len(new_bus_serv_ids) > 0 else None
            })
        except AssertionError:
            pass

    return unmasked_pg_ref, new_subscribers_info