# By: Riasat Ullah
# This file contains account maintenance and management related queries.

from psycopg2 import errorcodes
from utils import constants, errors, helpers, payment_vendors, times, var_names
from validations import organization_validator, string_validator
import datetime
import json
import psycopg2


def get_subscriptions(conn, timestamp, subscription_ids=None, subscription_type=None):
    '''
    Gets details of subscriptions
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param subscription_ids: list of subscription ids to check for
    :param subscription_type: (str) type of subscription -> BASE, ADD_ON
    :return: (dict of dict) -> { sub_id: {...}, ...}
    '''
    assert isinstance(timestamp, datetime.datetime)

    query_params = {'time_date': timestamp.date(), 'sub_type': subscription_type}
    conditions = []
    if subscription_ids is not None:
        conditions.append(" subscription_id = any(%(sub_ids)s) ")
        query_params['sub_ids'] = helpers.get_int_list(subscription_ids)
    if subscription_type is not None\
            and subscription_type in [constants.base_subscription_type, constants.add_on_subscription_type,
                                      constants.supplement_subscription_type]:
        conditions.append(" subscription_type = %(sub_type)s ")
        query_params['sub_type'] = subscription_type

    query = '''
            select subscription_id, subscription_name, subscription_description, currency, fee, unit
            from subscriptions
            where start_date <= %(time_date)s
                and end_date > %(time_date)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, sub_name, sub_desc, currency, fee, unit in result:
            data[id_] = {
                var_names.subscription_name: sub_name,
                var_names.description: sub_desc,
                var_names.subscription_currency: currency,
                var_names.subscription_fee: float(fee),
                var_names.subscription_unit: unit
            }
        return data
    except psycopg2.DatabaseError:
        raise


def get_current_subscription(conn, timestamp, org_id):
    '''
    Gets the id of the base subscription an organization has at a given point in time.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: organization id to check for
    :return: (dict) -> of details of the current subscription
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    query = '''
            select sub.subscription_name, det.iso_country_code, det.billing_currency, sub.fee
            from organization_subscriptions as org_sub
            join subscriptions as sub using(subscription_id)
            join organizations as det using (organization_id)
            where org_sub.organization_id = %(org_id)s
                and org_sub.start_timestamp <= %(timestamp)s
                and org_sub.end_timestamp > %(timestamp)s
                and sub.start_date <= %(timestamp)s
                and sub.end_date > %(timestamp)s
                and sub.subscription_type = %(sub_type)s
                and det.start_timestamp <= %(timestamp)s
                and det.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': org_id, 'sub_type': constants.base_subscription_type}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        elif len(result) > 1:
            raise SystemError(errors.err_internal_multiple_entries_found)
        else:
            for sub_name, country, curr, fee in result:
                data = {
                    var_names.subscription_name: sub_name,
                    var_names.country: country,
                    var_names.billing_currency: curr,
                    var_names.subscription_fee: float(fee)
                }
                return data
    except psycopg2.DatabaseError:
        raise


def get_current_subscription_status(conn, timestamp, org_id):
    '''
    Gets the ID of the subscription the organization has.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: organization id to check for
    :return: (int) subscription ID
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    query = '''
            select subscription_id, is_trial
            from organization_subscriptions
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': org_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        elif len(result) > 1:
            raise SystemError(errors.err_internal_multiple_entries_found)
        else:
            return result[0][0], result[0][1]
    except psycopg2.DatabaseError:
        raise


def get_add_on_subscriptions(conn, timestamp, org_ids):
    '''
    Get the add-on subscriptions of a given list of organizations.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param org_ids: (list) of organization ids
    :return: (dict of list) -> {org_id: [sub id 1, sub id 2, ...], ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select os.organization_id, subscription_id
            from organization_subscriptions as os
            join subscriptions as sub using(subscription_id)
            where os.organization_id = any(%(org_ids)s)
                and os.start_timestamp <= %(timestamp)s
                and os.end_timestamp > %(timestamp)s
                and sub.start_date <= %(timestamp)s
                and sub.end_date > %(timestamp)s
                and sub.subscription_type = %(add_on_sub_type)s;
            '''
    query_params = {'timestamp': timestamp, 'org_ids': helpers.get_int_list(org_ids),
                    'add_on_sub_type': constants.add_on_subscription_type}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for o_id, sub_id in result:
            if o_id not in data:
                data[o_id] = []
            data[o_id].append(sub_id)
        return data
    except psycopg2.DatabaseError:
        raise


def update_alerting_subscription(conn, query_params_list):
    '''
    Updates an alerting subscription. Every account must always be subscribed to 1 alerting plan.
    :param conn: db connection
    :param query_params_list: (list) of query parameters
            -> [ (org id, start time, end time, sub id, org perm, user perms json,), ...]
    :errors: AssertionError, DatabaseError
    '''

    # Do not allow subscriptions to be updated during the trial period
    # Do not allow downgrading in the middle of the month (correction - only bill for the highest if downgraded?)
    # Do not allow an upgrade if no valid card is on file
    # Lets users make payments when they want to for unpaid invoices

    # By en-dating entries that have a future start date and have the infinite end date
    # we are ensuring that entries that were created to postpone a downgrade is also taken
    # care of in the case that this update is happening after a downgrade was scheduled.

    # Users with "null" roles or permissions will be deleted as users instead of having their permissions converted.
    # This feature will be useful for downgrading to the "Free" plan from a plan without the max user restriction.

    query = '''
            select update_subscription(
                %s, %s, %s, %s::smallint, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s
            );
            '''
    try:
        if len(query_params_list) == 1:
            conn.execute(query, query_params_list[0])
        else:
            conn.execute_batch(query, query_params_list)
    except psycopg2.DatabaseError:
        raise


def get_org_ids_of_concluded_trials(conn, timestamp):
    '''
    Gets the IDs of the organizations whose trial period has ended.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :return: (list) of organization IDs
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select organization_id from organization_subscriptions
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and trial_end < %(timestamp)s
                and is_trial = true;
            '''
    query_params = {'timestamp': timestamp}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_organization_owner_info_of_almost_ending_trials(conn, timestamp, in_days):
    '''
    Get the details of the owner of organizations whose trials are almost ending. The details will be used
    to send email notifications asking organizations to upgrade their subscription plan.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param in_days: (list of int) list of days till trial expiry
    :return: (list of dict) of owner details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    in_days = helpers.get_int_list(in_days)

    query = '''
            select os.organization_id, trial_end - %(timestamp)s::date, users.first_name, users.last_name, users.email
            from organization_subscriptions os
            join organization_members om using(organization_id)
            join user_role_map urm using(role_id)
            join users on users.user_id = om.member_id
                and users.organization_id = om.organization_id
            where os.start_timestamp <= %(timestamp)s
                and os.end_timestamp > %(timestamp)s
                and is_trial = true
                and trial_end - %(timestamp)s::date = any(%(in_days)s)
                and urm.is_owner = true
                and om.start_timestamp <= %(timestamp)s
                and om.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'in_days': in_days}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for org_id, days_left, first_, last_, email_ in result:
            data.append({
                var_names.organization_id: org_id,
                var_names.days_buffer: days_left,
                var_names.first_name: first_,
                var_names.last_name: last_,
                var_names.email: email_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_organization_owner_info_for_account_notifications(conn, timestamp, organization_id):
    '''
    Get the details necessary to send the owner of an organization account level notifications.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param organization_id: (int) ID of the organization.
    :return: (list of dict) of owner details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select org.organization_id, org.organization_name, users.first_name, users.last_name,
                users.email, users.user_language
            from organizations as org
            join organization_members om using(organization_id)
            join user_role_map urm using(role_id)
            join users on users.user_id = om.member_id
                and users.organization_id = om.organization_id
            where org.start_timestamp <= %(timestamp)s
                and org.end_timestamp > %(timestamp)s
                and org.organization_id = %(org_id)s
                and urm.is_owner = true
                and om.start_timestamp <= %(timestamp)s
                and om.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for org_id, org_name, first_, last_, email_, lang_ in result:
            data = {
                var_names.organization_id: org_id,
                var_names.organization_name: org_name,
                var_names.first_name: first_,
                var_names.last_name: last_,
                var_names.email: email_,
                var_names.language: lang_
            }
        return data
    except psycopg2.DatabaseError:
        raise


def get_vendor_phones(conn, timestamp):
    '''
    Gets all the default phone numbers that taskcall has in each of the countries.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :return: dict of dict -> {(country code, text_allowed): {vendor_name: x, phone: y},
                            (country code, call_allowed): { vendor_name: x, phone: y}...}
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select iso_country_code, vendor, phone_number, text_allowed, call_allowed
            from vendor_phone_numbers
            where start_timestamp <= %s
                and end_timestamp > %s
                and default_number = true;
            '''
    query_params = (timestamp, timestamp,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for code_, vendor_, number_, can_text_, can_call_ in result:
            if can_text_:
                data[(code_, var_names.text_allowed)] = {
                    var_names.vendor_name: vendor_,
                    var_names.phone: number_
                }
            if can_call_:
                data[(code_, var_names.call_allowed)] = {
                    var_names.vendor_name: vendor_,
                    var_names.phone: number_
                }
        return data
    except psycopg2.DatabaseError:
        raise


def get_default_cards(conn, timestamp, organization_ids):
    '''
    Get the default cards of organizations at a given point in time
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_ids: (int or list) of organization ids to check for
    :return:
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select organization_id, card_id from organization_cards
            where start_timestamp <= %s
                and end_timestamp > %s
                and default_card = true
                and organization_id = any(%s);
            '''
    query_params = (timestamp, timestamp, helpers.get_int_list(organization_ids),)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for org_id, card_id in result:
            data[org_id] = card_id
        return data
    except psycopg2.DatabaseError:
        raise


def get_stripe_customer_id(conn, timestamp, org_id):
    '''
    Get the stripe "customer_id" stored in TaskCall for an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization
    :errors: AssertionError, DatabaseError, SystemError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    query = '''
            select handler_card_token
            from organization_cards
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and handler = %s;
            '''
    query_params = (timestamp, timestamp, org_id, constants.handler_stripe,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return None
        else:
            all_cus_ids = set()
            for item in result:
                all_cus_ids.add(item[0][var_names.customer_id])

            if len(all_cus_ids) == 1:
                return list(all_cus_ids)[0]
            else:
                raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_cards(conn, timestamp, org_id):
    '''
    Gets the all the cards that are associated to an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param org_id: ID of the organization
    :return: list (of dict) with card info
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    query = '''
            select card_id, brand, last_four, default_card from organization_cards
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s;
            '''
    query_params = (timestamp, timestamp, org_id,)
    try:
        result = conn.fetch(query, query_params)
        data = []
        for id_, brand_, number_, default_ in result:
            data.append({
                var_names.card_id: id_,
                var_names.card_brand: brand_,
                var_names.card_last_four_digits: number_,
                var_names.default_card: default_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def add_card(conn, timestamp, org_id, brand, last_four, card_token, handler=constants.handler_stripe, default=False):
    '''
    Add a card to an organization.
    :param conn: db connection
    :param timestamp: timestamp when the card was added
    :param org_id: ID of the organization
    :param brand: the brand of the card
    :param last_four: the last four digits of the card
    :param card_token: card token from the vendor
    :param handler: the payment vendor that handles this card (Stripe, etc)
    :param default: (boolean) true if the card is going to be the default card or not
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    organization_validator.validate_payment_info(brand, last_four, card_token, handler, default)
    query = '''
            insert into organization_cards
            (start_timestamp, end_timestamp, organization_id, default_card,
            brand, last_four, handler, handler_card_token)
            values (
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (timestamp, constants.end_timestamp, org_id, default,
                    brand, last_four, handler, json.dumps(card_token),)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def delete_card(conn, timestamp, org_id, card_id):
    '''
    Delete a card associated to an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param org_id: ID of the organization
    :param card_id: internal id of the card
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(card_id, int)

    query = '''
            update organization_cards set end_timestamp = %s
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and card_id = %s
            returning handler_card_token;
            '''
    query_params = (timestamp, timestamp, timestamp, org_id, card_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            pm_id = result[0][0][var_names.payment_method]
            payment_vendors.detach_stripe_card(pm_id)
    except psycopg2.DatabaseError:
        raise


def set_card_as_default(conn, timestamp, org_id, card_id):
    '''
    Set a card as the default payment option for an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made on
    :param org_id: ID of the organization
    :param card_id: ID of the card to be set as default
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(card_id, int)
    query = '''
            do
            $body$
            begin

            if exists (select 1 from organization_cards
                       where start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                            and organization_id = %(org_id)s
                            and card_id != %(card_id)s
            ) then
                update organization_cards set default_card = true
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    and card_id = %(card_id)s;

                update organization_cards set default_card = false
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id = %(org_id)s
                    and card_id != %(card_id)s;
            else
                raise check_violation;
            end if;

            end;
            $body$
            '''
    query_params = {'timestamp': timestamp, 'org_id': org_id, 'card_id': card_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 get_month_text_call_costs(conn, organization_ids=None):
    '''
    Get the costs incurred from an organization for texting and calling as a
    percentage of the income earned from their subscription.
    :param conn: db connection
    :param organization_ids: (int or list) of organization ids
    :return: (dict) -> {org id: cost percent, ...}
    '''
    if organization_ids is not None:
        organization_ids = helpers.get_int_list(organization_ids)

    query = " select * from organization_month_text_call_costs(%s); "
    query_params = (organization_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for org_id, cost_percent in result:
            data[org_id] = float(cost_percent)
        return data
    except psycopg2.DatabaseError:
        raise


def save_onboarding_answers(conn, timestamp, org_id, answers):
    '''
    Save the answers provided by the owner in the database.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: ID of the organization the answer is from
    :param answers: (list) of answer codes
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert isinstance(answers, list)

    query = "insert into organization_onboarding values (%s, %s, %s, %s);"
    query_params = (org_id, timestamp.date(), constants.end_timestamp.date(), answers,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def details_for_onboarding_emails(conn, timestamp, look_back=60):
    '''
    Get all the details needed for sending out user onboarding emails.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param look_back: number of days to look back to
    :return: (list of dict) -> [{onboarding details ...}, {...}]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    min_start_date = timestamp.date() - datetime.timedelta(days=look_back)

    query = '''
            with t1 as (
                select users.user_id, email, first_name, preferred_username, fltr_tbl.reg_date::date
                from users
                join (
                    select user_id, min(start_timestamp) as reg_date, max(start_timestamp) as last_update
                    from users
                    where user_id in (
                        select users.user_id
                        from users
                        join user_onboarding
                            on users.user_id = user_onboarding.user_id
                        where users.start_timestamp::date >= %(min_date)s
                            and user_onboarding.email_end > %(timestamp)s
                    )
                    group by user_id
                ) as fltr_tbl
                    on users.user_id = fltr_tbl.user_id
                        and users.start_timestamp = last_update
                        and fltr_tbl.reg_date >= %(min_date)s
                order by user_id
            )
            , t2 as (
                select om.organization_id, org.account_id, onb.answers, json_agg(json_build_object(
                    'email', email,
                    'first_name', first_name,
                    'preferred_username', preferred_username,
                    'role_id', role_id,
                    'start_period', reg_date
                )) as user_det
                from t1
                join organization_members as om
                    on t1.user_id = om.member_id
                join organizations as org
                    on org.organization_id = om.organization_id
                        and org.start_timestamp <= %(timestamp)s
                        and org.end_timestamp > %(timestamp)s
                left join organization_onboarding onb
                    on onb.organization_id = om.organization_id
                        and onb.start_timestamp <= %(timestamp)s
                        and onb.end_timestamp > %(timestamp)s
                where om.start_timestamp <= %(timestamp)s
                    and om.end_timestamp > %(timestamp)s
                group by om.organization_id, org.account_id, onb.answers
            )
            , t3 as (
                select organization_id,
                    count(integration_id) filter (where category = 'Application Monitoring') as monitoring_count,
                    count(integration_id) filter (where category = 'Ticketing') as ticketing_count,
                    count(integration_id) filter (where category = 'Chat-Ops') as chat_count
                from service_integrations as si
                join integration_types using(integration_type_id)
                where organization_id in (select organization_id from t2)
                    and si.start_timestamp <= %(timestamp)s
                    and si.end_timestamp > %(timestamp)s
                group by organization_id
            )
            , t4 as (
                select organization_id, count(call_routing_id) as lcr_count
                from live_call_routing
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and organization_id in (select organization_id from t2)
                group by organization_id
            )
            select t2.account_id, t2.user_det, t2.answers, t3.monitoring_count,
                t3.ticketing_count, t3.chat_count, t4.lcr_count
            from t2
            left join t3 using(organization_id)
            left join t4 using(organization_id);
            '''
    query_params = {'min_date': min_start_date, 'timestamp': timestamp}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for acc_id, user_det, onb_answers, monitor_count, ticket_count, chat_count, lcr_count in result:
            comp_feat = []
            if monitor_count is not None and monitor_count > 0:
                comp_feat.append(constants.application_monitoring)
            if ticket_count is not None and ticket_count > 0:
                comp_feat.append(constants.ticketing)
            if chat_count is not None and chat_count > 0:
                comp_feat.append(constants.chat_ops)
            if lcr_count is not None and lcr_count > 0:
                comp_feat.append(constants.call_routing)

            for item in user_det:
                item[var_names.start_period] = times.get_date_from_string(item[var_names.start_period])

            data.append({
                var_names.account_id: acc_id,
                var_names.users: user_det,
                var_names.answers: onb_answers,
                var_names.component_features: comp_feat
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_org_info_for_public_request(conn, timestamp, subdomain, account_number):
    '''
    Get the organization ID from subdomain and account number.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param subdomain: subdomain received in the request
    :param account_number: account number received in the request
    :return: (int) organization ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_valid_subdomain(subdomain)

    query = '''
            select organization_id, permissions
            from organizations as org
            join organization_permissions as prm using(organization_id)
            where prm.start_timestamp <= %(timestamp)s
                and prm.end_timestamp > %(timestamp)s
                and org.start_timestamp <= %(timestamp)s
                and org.end_timestamp > %(timestamp)s
                and org.subdomain = %(sub_dom)s
                and org.account_id = %(acc_num)s;
            '''
    query_params = {'timestamp': timestamp, 'sub_dom': subdomain, 'acc_num': account_number}
    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


def get_org_info_for_webcal(conn, timestamp, user_id):
    '''
    Get the organization ID from subdomain and account number.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: ID of the user making the request
    :return: (int) organization ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)

    query = '''
            select org.subdomain, org.account_id, users.preferred_username
            from organizations as org
            join users using(organization_id)
            where org.start_timestamp <= %(timestamp)s
                and org.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and users.user_id = %(usr_id)s;
            '''
    query_params = {'timestamp': timestamp, 'usr_id': user_id}
    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], result[0][2]
    except psycopg2.DatabaseError:
        raise