# By: Riasat Ullah
# This file contains functions for handling db queries for instance utilities like conference bridges.

from psycopg2 import errorcodes
from utils import constants, errors, integration_type_names as intt, var_names
from validations import string_validator
import datetime
import json
import psycopg2


def create_conference_bridge(conn, timestamp, organization_id, conference_name, dial_in=None, url=None, details=None,
                             integration_type=None, external_id=None, external_info=None):
    '''
    Pre-stores the details of a conference bridge that can be used quickly when needed.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the conference bridge is for
    :param conference_name: name to identify the conference with
    :param dial_in: dial in number
    :param url: url to join with
    :param details: details to join in the call
    :param integration_type: (str) type of conference (webex, zoom); if custom then None
    :param external_id: (str) ID of the organization on the vendor's side (when conference type is not None)
    :param external_info: (dict) of external details of the organization (when conference type is not None)
    :return: (int) conference ID
    :errors: AssertionError, DatabaseError, ValueError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert string_validator.is_standard_name(conference_name)

    if integration_type is None:
        assert dial_in is not None or url is not None
        if dial_in is not None:
            assert string_validator.is_dial_in_number(dial_in)
        if url is not None:
            assert string_validator.is_web_url(url)
        external_id, external_info = None, None
    else:
        if integration_type in [intt.webex, intt.zoom]:
            assert external_id is not None
            assert external_info is not None
            external_info = json.dumps(external_info)
            dial_in, url = None, None

    if details is not None:
        assert isinstance(details, dict)
        details = json.dumps(details)

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

    query_params = (timestamp, constants.end_timestamp, organization_id, conference_name, dial_in,
                    url, details, integration_type, external_id, external_info,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.DatabaseError:
        raise


def delete_conference_bridge(conn, timestamp, organization_id, conference_id):
    '''
    Delete the stored details of a conference bridge.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the conference bridge belongs to
    :param conference_id: ID of the conference bridge
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(conference_id, int)

    query = '''
            do
            $body$

            declare
                c_id conference_bridges.conference_id%%type;

            begin

            update conference_bridges set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and conference_id = %(conf_id)s
            returning conference_id into c_id;

            if c_id is null then
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'conf_id': conference_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 get_conference_bridge_details(conn, timestamp, conf_id):
    '''
    Gets the details of a conference bridge.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param conf_id: ID of the conference bridge
    :return: (dict) of conference bridge details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(conf_id, int)
    query = '''
            select conference_phone, conference_url, cb.details, itt.integration_type, oitd.external_id, oitd.details
            from conference_bridges as cb
            left join integration_types as itt
                on cb.integration_type_id is not null
                    and itt.integration_type_id = cb.integration_type_id
                    and itt.start_date <= %(timestamp)s
                    and itt.end_date > %(timestamp)s
            left join organization_integration_type_details as oitd
                on cb.integration_type_id is not null
                    and oitd.integration_type_id = cb.integration_type_id
                    and oitd.start_timestamp <= %(timestamp)s
                    and oitd.end_timestamp > %(timestamp)s
                    and (
                        cb.details is null
                        or
                        (cb.details is not null and cb.details->>'external_id' = oitd.external_id)
                    )
            where cb.start_timestamp <= %(timestamp)s
                and cb.end_timestamp > %(timestamp)s
                and cb.conference_id = %(conf_id)s;
            '''
    query_params = {'timestamp': timestamp, 'conf_id': conf_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for phone_, url_, det_, int_type_, ext_id_, ext_info_ in result:
            data = {
                var_names.conference_phone: phone_,
                var_names.conference_url: url_,
                var_names.additional_info: det_,
                var_names.integration_type: int_type_,
                var_names.external_id: ext_id_,
                var_names.external_info: ext_info_
            }
        return data
    except psycopg2.DatabaseError:
        raise


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

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


def get_conference_bridges(conn, timestamp, organization_id):
    '''
    Get all the conference bridges of an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :return: (list of list) -> [ [conf id, conf name, conf phone, conf url, details], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select conference_id, conference_name, intt.integration_type, conference_phone, conference_url
            from conference_bridges as cb
            left join integration_types as intt
                on cb.integration_type_id is not null
                    and intt.integration_type_id = cb.integration_type_id
                    and intt.start_date <= %(timestamp)s
                    and intt.end_date > %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
            order by conference_name;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for id_, name_, type_, phone_, url_ in result:
            data.append([id_, name_, type_, phone_, url_])
        return data
    except psycopg2.DatabaseError:
        raise