# By: Riasat Ullah

# This file contains all database queries in relation to an user and their information.

from exceptions.user_exceptions import InvalidUsername, InvalidPassword, LockedAccount
from modules import credentials
from objects.user import User
from psycopg2 import errorcodes
from utils import constants, errors, helpers, key_manager, permissions, roles, var_names
from validations import organization_validator, string_validator
import configuration
import datetime
import json
import psycopg2


def email_is_unique(conn, user_email, timestamp):
    '''
    Checks if the provided user_id is unique at a given time.
    The check is done against active accounts.
    :param conn: db connection
    :param user_email: (str) the email to verify
    :param timestamp: (datetime.datetime) the time and date to look at
    :return: (boolean) True if it is unique; False otherwise
    '''
    assert string_validator.is_email_address(user_email)
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select email from users
            where email = %s
                and start_timestamp <= %s
                and end_timestamp > %s;
            '''
    query_params = (user_email, timestamp, timestamp,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return True
        else:
            return False
    except psycopg2.DatabaseError:
        raise


def get_password(conn, user_id, timestamp):
    '''
    Query to request for a user's password given a user_id and timestamp to fetch for.
    :param conn: db connection
    :param user_id: user_id (string)
    :param timestamp: timestamp relative to when the check will run (datetime.datetime)
    :return: tuple; (password, salt)
    :errors: AssertionError, LockedAccount, KeyError (when password is missing), ValueError, DatabaseError
    '''
    assert isinstance(user_id, int)
    assert isinstance(timestamp, datetime.datetime)

    query = '''
            with pwd as(
                select user_id, hash_password, hash_salt
                from user_passwords
                where user_id = %(user_name)s
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
            )
            , locked as(
                select user_id from locked_accounts
                where user_id = %(user_name)s
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
            )
            select hash_password, hash_salt, locked.user_id
            from pwd
            left join locked using(user_id)
            '''
    query_params = {'timestamp': timestamp, 'user_name': user_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise KeyError(errors.err_unknown_resource)
        elif len(result) > 1:
            raise SystemError(errors.err_internal_multiple_entries_found)
        else:
            password = result[0][0]
            salt = result[0][1]
            account_locked = result[0][2]
            if password is None and salt is None and account_locked is not None:
                raise LockedAccount(errors.err_user_account_locked)
            if account_locked is not None and account_locked == user_id:
                raise LockedAccount(errors.err_user_account_locked)
            return password, salt
    except psycopg2.DatabaseError:
        raise


def reset_password(conn, user_id, new_password, timestamp, system_generated=False):
    '''
    Resets the password by end-dating the old one and creating a new one.
    :param conn: db connection
    :param user_id: user_id
    :param new_password: new password
    :param timestamp: reset time
    :param system_generated: (boolean) True if the password was generated by the system
    :errors: AssertionError, InvalidPassword, SqlInjection
    '''
    assert isinstance(user_id, int)
    if not string_validator.is_valid_password(new_password):
        raise InvalidPassword(errors.err_password_invalid)
    assert isinstance(timestamp, datetime.datetime)

    hash_password, salt = credentials.convert_text_to_hash(new_password)
    query = '''
            do
            $body$
            begin
            
            update user_passwords set end_timestamp = %(timestamp)s
            where user_id = %(user_name)s
                and start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s;

            insert into user_passwords values (
                %(user_name)s, %(timestamp)s, %(end_time)s, %(salt)s, %(hash)s, %(sys_gen)s
            );

            end;
            $body$
            '''
    query_params = {'user_name': user_id, 'timestamp': timestamp, 'end_time': constants.end_timestamp,
                    'salt': salt, 'hash': hash_password, 'sys_gen': system_generated}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_user(conn, timestamp, keyed_on=var_names.user_id, user_id=None, policy_id=None,
             preferred_username=None, organization_id=None, email=None):
    '''
    Gets user object(s) that qualify within the given parameters.
    :param conn: db connection
    :param timestamp: timestamp to look for
    :param keyed_on: what should the return dictionary be keyed on
    :param user_id: (str or list of str) user_id(s) to look for
    :param policy_id: (int or list of int) policy id(s) to look for
    :param preferred_username: (str) preferred username of the user
    :param organization_id: ID of the organizations the user(s) belong to
    :param email: email of the user
    :return: (dictionary) of user objects keyed on keyed_on value
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert keyed_on in {var_names.user_id, var_names.email, var_names.policy_id, var_names.preferred_username}
    query_params = {'timestamp': timestamp}

    conditions = []
    if user_id is not None:
        conditions.append(" user_id = any(%(usr_ids)s) ")
        query_params['usr_ids'] = helpers.get_int_list(user_id)
    if policy_id is not None:
        conditions.append(" policyid = any(%(pid)s) ")
        query_params['pid'] = helpers.get_int_list(policy_id)
    if preferred_username is not None:
        conditions.append(" preferred_username = %(pref_name)s ")
        query_params['pref_name'] = preferred_username
    if organization_id is not None:
        conditions.append(" organization_id = %(org_id)s ")
        query_params['org_id'] = organization_id
    if email is not None:
        assert string_validator.is_email_address(email)
        conditions.append(" email = %(user_email)s ")
        query_params['user_email'] = email

    query = '''
            select user_id, first_name, last_name, email, iso_country_code, country_code, phone, policyid,
                preferred_username, taskcall_email, organization_id, user_timezone, user_language, photo_url
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                {0};
            '''.format('' if len(conditions) == 0 else ' and ' + ' and '.join(x for x in conditions))
    try:
        result = conn.fetch(query, query_params)
        user_dict = dict()
        key_index = 0
        if keyed_on == var_names.email:
            key_index = 3
        if keyed_on == var_names.policy_id:
            key_index = 7
        if keyed_on == var_names.preferred_username:
            key_index = 8

        for item in result:
            user = User(item[0], item[1], item[2], item[3], item[4], item[5], item[6],
                        item[7], item[8], item[9], item[10], item[11], item[12], item[13])
            user_dict[item[key_index]] = user
        return user_dict
    except psycopg2.DatabaseError:
        raise


def get_user_display_name(conn, timestamp, organization_id, user_id=None, pref_username=None):
    '''
    Gets the display name of the user.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization the user belongs to
    :param user_id: ID of the user
    :param pref_username: preferred username of the user
    :return: (str) display name of the user
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert user_id is not None or pref_username is not None

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if user_id is not None:
        conditions.append(" user_id = %(usr_id)s ")
        query_params['usr_id'] = user_id
    if pref_username is not None:
        conditions.append("preferred_username = %(pref_name)s ")
        query_params['pref_name'] = pref_username

    query = '''
            select first_name || ' ' || last_name
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0][0]
        elif len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_multiple_users_display_name(conn, timestamp, organization_id, user_ids):
    '''
    Gets the display name of users.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization the user belongs to
    :param user_ids: (list) of user IDs
    :return: (dict) user ID -> display name
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select user_id, first_name || ' ' || last_name
            from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and user_id = any(%s);
            '''

    query_params = (timestamp, timestamp, organization_id, user_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for usr_id, disp_name in result:
            data[usr_id] = disp_name
        return data
    except psycopg2.DatabaseError:
        raise


def get_user_public_profile(conn, timestamp, organization_id, preferred_username):
    '''
    Get basic details of a user that can be displayed to others.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param organization_id: ID of the organization the user belongs to
    :param preferred_username: preferred username of the user
    :return: (dict) of basic information
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select first_name || ' ' || last_name, email, taskcall_email, phone
            from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and preferred_username = %s;
            '''
    query_params = (timestamp, timestamp, organization_id, preferred_username,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        else:
            for display_name, email, taskcall_email, phone in result:
                data = {
                    var_names.display_name: display_name,
                    var_names.email: email,
                    var_names.taskcall_email: taskcall_email,
                    var_names.phone: phone
                }
                return data
    except psycopg2.DatabaseError:
        raise


def get_user_details(conn, timestamp, organization_id, user_id=None, preferred_username=None):
    '''
    Get a user's details/profile.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: ID of the organization
    :param user_id: ID of the user whose details are being requested
    :param preferred_username: preferred username of the user whose details are being requested
    :return: (dict) of user details
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert user_id is not None or preferred_username is not None

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append(" users.user_id = %(usr_id)s ")
        query_params['usr_id'] = user_id

    if preferred_username is not None:
        conditions.append(" users.preferred_username = %(pref_name)s ")
        query_params['pref_name'] = preferred_username

    query = '''
            select email, first_name, last_name, iso_country_code, country_code, phone, preferred_username,
                taskcall_email, user_timezone, user_language, photo_url, mem.role_id, mem.job_title,
                array_remove(array_agg(taga.tag), null) as user_tags
            from users
            join organization_members as mem
                on users.user_id = mem.member_id
            left join tag_associations as taga
                on taga.component_type_id is null
                    and taga.component_id = users.user_id
                    and taga.organization_id = %(org_id)s
                    and taga.start_timestamp <= %(timestamp)s
                    and taga.end_timestamp > %(timestamp)s
            where mem.start_timestamp <= %(timestamp)s
                and mem.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and users.organization_id = %(org_id)s
                {0}
            group by email, first_name, last_name, iso_country_code, country_code, phone, preferred_username,
                taskcall_email, user_timezone, user_language, photo_url, mem.role_id, mem.job_title;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')

    try:
        result = conn.fetch(query, query_params)
        if len(result) > 0:
            for email, first_name, last_name, iso_code, country_code, phone, pref_name,\
                    tc_email, time_zone, lang, photo_url, mem_rol_id, mem_job_ttl, user_tags in result:
                data = {
                    var_names.email: email,
                    var_names.first_name: first_name,
                    var_names.last_name: last_name,
                    var_names.iso_country_code: iso_code,
                    var_names.country_code: country_code,
                    var_names.phone: phone,
                    var_names.preferred_username: pref_name,
                    var_names.taskcall_email: tc_email,
                    var_names.timezone: time_zone,
                    var_names.language: lang,
                    var_names.profile_picture: photo_url,
                    var_names.user_role: roles.user_role_maps[mem_rol_id],
                    var_names.job_title: mem_job_ttl,
                    var_names.tags: user_tags
                }
                return data
        else:
            raise InvalidUsername(errors.err_unknown_resource)
    except psycopg2.DatabaseError as e:
        raise psycopg2.DatabaseError(e)


def update_user_details(conn, timestamp, organization_id, user_id, first_name, last_name,
                        iso_code, phone, timezone, language, job_title):
    '''
    Updates basic details of a user. The profile picture url cannot be updated with this function.
    :param conn: db connection
    :param timestamp: timestamp when the changes are being made
    :param organization_id: ID of the organization the user belongs to
    :param user_id: user_id
    :param first_name: first name of the user
    :param last_name: last name of the user
    :param iso_code: 2 letter of the ISO code of the user's phone's country
    :param phone: user's phone number
    :param timezone: the timezone the user is in
    :param language: the language the user's account should be set up in
    :param job_title: user's job title
    '''
    assert isinstance(user_id, int)
    try:
        user = get_user_details(conn, timestamp, organization_id, user_id=user_id)
        if user[var_names.first_name] != first_name or\
            user[var_names.last_name] != last_name or\
            user[var_names.iso_country_code] != iso_code or\
            user[var_names.phone] != phone or\
            user[var_names.timezone] != timezone or\
            user[var_names.language] != language or\
                user[var_names.job_title] != job_title:

            phone_code = helpers.get_phone_code(iso_code)
            organization_validator.validate_user_info(first_name, last_name, iso_code, phone_code, phone,
                                                      timezone, language)
            if job_title is not None:
                assert string_validator.is_standard_name(job_title)

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

            query_params = (user_id, timestamp, constants.end_timestamp, first_name, last_name, iso_code,
                            phone_code, phone, timezone, language, job_title,)

            conn.execute(query, query_params)
    except KeyError as e:
        raise KeyError(errors.err_unknown_resource) from e
    except psycopg2.DatabaseError:
        raise


def update_user_profile_picture(conn, timestamp, organization_id, user_id, picture_url):
    '''
    Updates basic details of a user.
    :param conn: db connection
    :param timestamp: timestamp when the changes are being made
    :param organization_id: ID of the organization the user belongs to
    :param user_id: user_id
    :param picture_url: url from where the user's profile picture can be retrieved
    '''
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    assert string_validator.is_web_url(picture_url)

    query = '''
            update users set photo_url = %s
            where organization_id = %s
                and user_id = %s
                and start_timestamp <= %s
                and end_timestamp > %s;
            '''
    query_params = (picture_url, organization_id, user_id, timestamp, timestamp,)
    try:
        conn.execute(query, query_params)
    except KeyError as e:
        raise KeyError(errors.err_unknown_resource) from e
    except psycopg2.DatabaseError:
        raise


def store_login_attempt(conn, user_id, timestamp, access_method, ip_address, success):
    '''
    Keeps record of a user's login attempt
    :param conn: db connection
    :param user_id: user_id
    :param timestamp: timestamp the attempt was made
    :param access_method: the type of system the attempt was made from (App, Web)
    :param ip_address: ip address where the attempt was made from
    :param success: (boolean) True if the user was able to login; False otherwise
    :errors: AssertionError, SqlInjection, DatabaseError
    '''
    assert isinstance(user_id, int)
    assert isinstance(timestamp, datetime.datetime)
    assert access_method in configuration.allowed_login_methods
    assert string_validator.is_valid_ip_address(ip_address)

    query = "insert into user_login_attempts values(%s, %s, %s, %s, %s);"
    query_params = (user_id, timestamp, success, access_method, ip_address,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def failed_login_attempts(conn, user_id, start_time, end_time):
    '''
    Gets details of all the login attempts a user has made
    within a period of time in ascending order.
    :param conn: db connection
    :param user_id: user_id
    :param start_time: (datetime.datetime) starting range time
    :param end_time: (datetime.datetime) ending range time
    :return: (list of dictionaries) details of the login attempts
    '''
    assert isinstance(user_id, int)
    assert isinstance(start_time, datetime.datetime)
    assert isinstance(end_time, datetime.datetime)

    query = '''
            select login_timestamp, access_method, ip_address
            from user_login_attempts
            where user_id = %s
                and login_timestamp >= %s
                and login_timestamp < %s
                and success = false
            order by login_timestamp desc;
            '''
    query_params = (user_id, start_time, end_time,)
    try:
        result = conn.fetch(query, query_params)
        attempts = []
        for login_timestamp, access_method, ip_address in result:
            attempts.append({var_names.login_timestamp: login_timestamp,
                             var_names.access_method: access_method,
                             var_names.ip_address: ip_address})
        return attempts
    except psycopg2.DatabaseError:
        raise


def store_user_id_retrieval_attempt(conn, user_id, timestamp, date, access_method, ip_address, success):
    '''
    Keeps record of the times a user has attempted to retrieve their user_id
    :param conn: db connection
    :param user_id: user_id
    :param timestamp: timestamp the attempt was made
    :param date: date the attempt was made on
    :param access_method: the type of system the attempt was made from (App, Web)
    :param ip_address: ip address where the attempt was made from
    :param success: (boolean) True if the user was able to login; False otherwise
    '''
    assert isinstance(user_id, int)
    assert isinstance(date, datetime.date)
    assert isinstance(timestamp, datetime.datetime)

    query = "insert into user_id_retrieval_attempts values(%s, %s, %s, %s, %s, %s);"
    query_params = (user_id, date, timestamp, success, access_method, ip_address,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def user_id_retrieval_attempts(conn, user_id, start_time, end_time):
    '''
    Gets the details of all the attempts a user has made to retrieve their user_id in ascending order.
    :param conn: db connection
    :param user_id: user_id
    :param start_time: (datetime.datetime) starting range time
    :param end_time: (datetime.datetime) ending range time
    :return: (list of dictionaries) details of the attempts
    '''
    assert isinstance(user_id, int)
    assert isinstance(start_time, datetime.datetime)
    assert isinstance(end_time, datetime.datetime)

    query = '''
            select attempt_date, attempt_timestamp, success,
                access_method, ip_address
            from user_id_retrieval_attempts
            where user_id = %s
                and attempt_date >= %s
                and attempt_date <= %s
                and attempt_timestamp >= %s
                and attempt_timestamp <= %s
            order by attempt_timestamp desc
            '''
    query_params = (user_id, start_time.date(), end_time.date(), start_time, end_time,)
    try:
        result = conn.fetch(query, query_params)
        attempts = []
        for login_date, login_timestamp, success, access_method, ip_address in result:
            attempts.append({var_names.login_date: login_date,
                             var_names.login_timestamp: login_timestamp,
                             var_names.attempt_success: success,
                             var_names.access_method: access_method,
                             var_names.ip_address: ip_address}
                            )
        return attempts
    except psycopg2.DatabaseError:
        raise


def store_password_reset_attempt(conn, user_id, timestamp, ip_address):
    '''
    Keeps record of the times a user has attempted to reset their password.
    :param conn: db connection
    :param user_id: ID of the user
    :param timestamp: timestamp the attempt was made
    :param ip_address: ip address where the attempt was made from
    '''
    assert isinstance(user_id, int)
    assert isinstance(timestamp, datetime.datetime)

    query = "insert into password_reset_attempts values(%s, %s, %s);"
    query_params = (user_id, timestamp, ip_address,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def password_reset_attempts(conn, user_id, start_time, end_time):
    '''
    Gets the details of all the attempts a user has made to reset their password in ascending order.
    :param conn: db connection
    :param user_id: ID of the user
    :param start_time: (datetime.datetime) starting range time
    :param end_time: (datetime.datetime) ending range time
    :return: (list of dictionaries) details of the attempts
    '''
    assert isinstance(user_id, int)
    assert isinstance(start_time, datetime.datetime)
    assert isinstance(end_time, datetime.datetime)

    query = '''
            select attempt_timestamp, ip_address
            from password_reset_attempts
            where user_id = %s
                and attempt_timestamp >= %s
                and attempt_timestamp <= %s
            order by attempt_timestamp desc
            '''
    query_params = (user_id, start_time, end_time,)
    try:
        result = conn.fetch(query, query_params)
        attempts = []
        for access_timestamp, ip_address in result:
            attempts.append({var_names.timestamp: access_timestamp,
                             var_names.ip_address: ip_address})
        return attempts
    except psycopg2.DatabaseError:
        raise


def lock_account(conn, user_id, timestamp, reason):
    '''
    Lock a user account. Before locking, it makes sure that there is no
    existing lock and that the user is active.
    :param conn: db connection
    :param user_id: user_id on the account
    :param timestamp: timestamp to start the lock from
    :param reason: reason why the account is being locked
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(user_id, int)
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            do
            $body$
            begin

            if not exists (select 1 from locked_accounts
                           where user_id = %(user_name)s
                                and start_timestamp <= %(timestamp)s
                                and end_timestamp > %(timestamp)s
                          )
                and exists (select 1 from users
                            where user_id = %(user_name)s
                                and start_timestamp <= %(timestamp)s
                                and end_timestamp > %(timestamp)s
                            )
            then
                insert into locked_accounts values (
                    %(user_name)s, %(timestamp)s, %(end_time)s, %(reason)s, null, null
                );
            end if;

            end;
            $body$
            '''
    query_params = {'user_name': user_id, 'timestamp': timestamp,
                    'end_time': constants.end_timestamp, 'reason': reason}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def unlock_account(conn, timestamp, preferred_username):
    '''
    Unlocks a locked account.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param preferred_username: preferred username of the user whose account is to be unlocked
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            update locked_accounts set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and user_id in (
                    select user_id from users
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and preferred_username = %(pref_name)s
                );
            '''
    query_params = {'timestamp': timestamp, 'pref_name': preferred_username}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_locked_accounts(conn, timestamp, organization_id):
    '''
    Get the list of locked user accounts in an organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is made
    :param organization_id: ID of the organization
    :return: (list of list) -> [[full name, pref name], ...]
    :errors: DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    query = '''
            select users.first_name || ' ' || users.last_name, users.preferred_username
            from locked_accounts as la
            join users using(user_id)
            where la.start_timestamp <= %(timestamp)s
                and la.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and users.organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        return result
    except psycopg2.DatabaseError:
        raise


def save_personal_account_verification_code(conn, start_timestamp, end_timestamp, email, code_salt, code_hash,
                                            verification_type=constants.new_account_verification):
    '''
    Save the verification code assigned to the email of a user who is creating a new personal account.
    The verification code will be used to verify that the email address truly belongs to the user.
    :param conn: db connection
    :param start_timestamp: timestamp the code is valid from
    :param end_timestamp: timestamp the code expires
    :param email: email address of the user
    :param code_salt: the salt used to hash the verification code
    :param code_hash: the hash of verification code
    :param verification_type: the type of verification the code is being saved for
    :error: AssertionError, DatabaseError
    '''
    assert isinstance(start_timestamp, datetime.datetime)
    assert isinstance(end_timestamp, datetime.datetime)
    assert string_validator.is_email_address(email)
    assert verification_type in configuration.allowed_verification_types

    # End date any existing entry for verification code to make sure that if a user
    # decides to re-start the verification process, there are no obstacles there.
    query = '''
            do
            $body$
            begin

            update verification_codes set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and email = %(email)s
                and verification_type = %(verification_type)s;

            insert into verification_codes values (
                %(email)s, %(timestamp)s, %(end_time)s, %(verification_type)s, %(salt)s, %(hash)s, 0
            );

            end;
            $body$
            '''
    query_params = {'email': email.lower(), 'timestamp': start_timestamp, 'end_time': end_timestamp,
                    'verification_type': verification_type, 'salt': code_salt, 'hash': code_hash}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_verification_code(conn, timestamp, email, verification_type=constants.new_account_verification):
    '''
    Gets the verification code associated with an email address that is to be used to open a new account.
    :param conn: db connection
    :param timestamp: timestamp when the request for verification is being made
    :param email: the email address associated with the new account
    :param verification_type: the type of verification the code is being saved for
    :return: (hash of the code, salt)
    :errors: AssertionError, KeyError, ValueError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_email_address(email)
    assert verification_type in configuration.allowed_verification_types

    # There should never be any older verification codes open because all open verification codes are
    # end-dated when a new one is created. This allows multiple requests to generate code to be handled.
    # Refer to the "save_personal_account_verification_code" function.
    query = '''
            select code_salt, code_hash from verification_codes
            where start_timestamp <= %s
                and end_timestamp > %s
                and email = %s
                and verification_type = %s
                and verification_attempts < %s;
            '''
    query_params = (timestamp, timestamp, email.lower(),
                    verification_type, configuration.allowed_failed_verification_attempts,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            salt = result[0][0]
            code = result[0][1]
            return code, salt
        elif len(result) == 0:
            raise KeyError(errors.err_unknown_resource)
        elif len(result) > 1:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def update_verification_code_entry(conn, timestamp, email, expire=False, is_verified=None,
                                   verification_type=constants.new_account_verification):
    '''
    Updates the verification code entry of a new account.
    :param conn: db connection
    :param timestamp: timestamp when the update is being made
    :param email: email address associated with the new account
    :param expire: (boolean) True if the entry should be expire; default is False
    :param is_verified: (boolean) True if the code has been verified; False otherwise
    :param verification_type: the type of verification the code is being saved for
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_email_address(email)
    assert verification_type in configuration.allowed_verification_types

    update_set_cond = ""
    if is_verified:
        update_set_cond += ", is_verified = true "

    update_set_cond += ", end_timestamp = %(timestamp)s " if expire or is_verified else ""

    query = '''
            do
            $body$
            begin
            
            if exists (select 1 from verification_codes
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and email = %(email)s
                    and verification_type = %(verification_type)s
                    and verification_attempts < %(allowed_attempts)s
                    and (is_verified is null or is_verified = false)
            ) then
                update verification_codes
                set verification_attempts = verification_attempts + 1
                    {0}
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and email = %(email)s
                    and verification_type = %(verification_type)s;
            else
                update verification_codes
                set verification_attempts = verification_attempts + 1,
                    end_timestamp = %(timestamp)s
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and email = %(email)s
                    and verification_type = %(verification_type)s;
            end if;        
            
            end;
            $body$
            '''.format(update_set_cond)

    query_params = {'timestamp': timestamp, 'email': email, 'verification_type': verification_type,
                    'allowed_attempts': configuration.allowed_failed_verification_attempts - 1}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def was_password_reset_attempt_verified(conn, timestamp, email):
    '''
    Check if the last verification code generated was actually verified or not.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param email: email to check for
    :return: (boolean) True if the last code was verified; False otherwise
    '''
    assert string_validator.is_email_address(email)
    assert isinstance(timestamp, datetime.datetime)

    check_start = timestamp - datetime.timedelta(minutes=configuration.password_reset_token_lifetime)

    query = '''
            select is_verified from verification_codes
            where start_timestamp >= %s
                and end_timestamp < %s
                and email = %s
                and verification_type = %s
            order by start_timestamp desc;
            '''
    query_params = (check_start, timestamp, email, constants.password_reset_verification,)
    try:
        result = conn.fetch(query, query_params)
        if result[0][0] is True:
            return True
        else:
            return False
    except psycopg2.DatabaseError:
        raise


def save_authentication_tokens(conn, start_time, end_time, user_id, org_id, platform, access_token,
                               refresh_token, push_token=None, session_id=None):
    '''
    Saves the authentication tokens issued during user login
    including push tokens and sessions ids where appropriate.
    :param conn: db connection
    :param start_time: the time the access token is valid from
    :param end_time: the time the access token is valid till
    :param user_id: user_id of the user whose device the token is for
    :param org_id: ID of the organization the user is in
    :param access_token: the access token issued to the user
    :param refresh_token: the refresh token issued to the user
    :param platform: APP or WEBSITE
    :param push_token: the unique token that identifies a device for sending push notifications
    :param session_id: the session ID of the session created by the web server for the user
    :errors: AssertionError, DatabaseError
    '''
    # we have to decode the token if it is in bytes
    if isinstance(access_token, bytes):
        access_token = access_token.decode()
    if isinstance(refresh_token, bytes):
        refresh_token = refresh_token.decode()

    if platform == constants.web:
        assert session_id is not None

    assert isinstance(start_time, datetime.datetime)
    assert isinstance(end_time, datetime.datetime)
    assert isinstance(user_id, int)
    assert isinstance(org_id, int)
    assert platform in configuration.allowed_login_methods
    assert string_validator.is_jwt_token(access_token)
    assert string_validator.is_jwt_token(refresh_token)

    query = '''
            insert into authentication_tokens values(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''
    query_params = (user_id, start_time, end_time, org_id, platform,
                    access_token, refresh_token, push_token, session_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def delete_authentication_tokens(conn, timestamp, user_id, access_token):
    '''
    Delete the push token of a user's device for a particular session.
    :param conn: db connection
    :param timestamp: timestamp when this token is being added
    :param user_id: user_id of the user whose device the token is for
    :param access_token: user token that identifies a particular session
    :errors: AssertionError, DatabaseError
    '''
    # we have to decode the token because it is in bytes
    if isinstance(access_token, bytes):
        access_token = access_token.decode()

    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert string_validator.is_jwt_token(access_token)

    query = '''
            update authentication_tokens set end_timestamp = %(timestamp)s
            where user_id = %(usr_id)s
                and start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and access_token = %(access_token)s;
            '''
    query_params = {'timestamp': timestamp, 'usr_id': user_id, 'access_token': access_token}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def is_valid_refresh_token(conn, timestamp, refresh_token, access_token, user_id, org_id):
    '''
    Checks if a refresh token is valid or not.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param refresh_token: the refresh token to validate
    :param access_token: access token
    :param user_id: user_id of the user the refresh token was issued to
    :param org_id: ID of the organization the refresh token is for
    :return: (boolean) True if it is valid; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_jwt_token(refresh_token)
    assert string_validator.is_jwt_token(access_token)
    assert isinstance(user_id, int)
    assert isinstance(org_id, int)

    # We are checking if there is valid entry for an expired access token.
    query = '''
            select user_id from authentication_tokens
            where end_timestamp <= %s
                and refresh_token = %s
                and access_token = %s
                and user_id = %s
                and organization_id = %s;
            '''
    query_params = (timestamp, refresh_token, access_token, user_id, org_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) > 0:
            return True
        else:
            return False
    except psycopg2.DatabaseError:
        raise


def get_user_token_details(conn, timestamp, user_id):
    '''
    Get the details needed to create a user token.
    :param conn: db connection
    :param timestamp: timestamp when this request is made
    :param user_id: user_id of the user
    :return: (tuple) -> (organization id, user permission, organization permission, permission end-date)
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    query = '''
            select users.organization_id, user_perm.permissions, org_perm.permissions, org_perm.end_timestamp,
                users.first_name || ' ' || users.last_name, users.user_language, users.photo_url
            from users
            join user_permissions as user_perm
                on users.user_id = user_perm.user_id
                    and user_perm.start_timestamp <= %(timestamp)s
                    and user_perm.end_timestamp > %(timestamp)s
            join organization_permissions as org_perm
                on users.organization_id = org_perm.organization_id
                    and org_perm.start_timestamp <= %(timestamp)s
                    and org_perm.end_timestamp > %(timestamp)s
            where 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) == 1:
            for org_id, user_perm, org_perm, perm_end, display_name, user_lang, photo_url in result:
                return org_id, user_perm, org_perm, perm_end, display_name, user_lang, photo_url
        else:
            raise KeyError(errors.err_unknown_resource)
    except psycopg2.DatabaseError:
        raise


def get_user_ids_and_taskcall_email(conn, timestamp, org_id, user_list):
    '''
    Get the policy id, routine id and taskcall email of a user.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param org_id: organization ID the users belong to
    :param user_list: (list) of user_ids to check for
    :return: (dict) -> { user_id: (policy id, routine id, taskcall_email), ... }
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_list, list)
    for item in user_list:
        assert isinstance(item, int)

    query = '''
            select users.user_id, pl.policyid, pl.routineid, users.taskcall_email
            from users join policy_levels as pl using (policyid)
            where users.user_id = any(%(user_list)s)
                and users.organization_id = %(org_id)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and pl.start_timestamp <= %(timestamp)s
                and pl.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'user_list': user_list, 'org_id': org_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for name_, pol_, rou_, t_email_ in result:
            data[name_] = (pol_, rou_, t_email_)
        return data
    except psycopg2.DatabaseError:
        raise


def get_users_with_assigned_emails(conn, timestamp, organization_id):
    '''
    Gets the user_id, preferred username and the user policy ID of all the users
    in an organization who have taskcall email addresses assigned to them.
    (Only stakeholders do not get email addresses assigned to them)
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :return: (list of tuple) -> [ (user_id, taskcall email, policy ID), ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    query = '''
            select user_id, taskcall_email, policyid
            from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and taskcall_email is not null;
            '''
    query_params = (timestamp, timestamp, organization_id,)
    try:
        result = conn.fetch(query, query_params)
        return result
    except psycopg2.DatabaseError:
        raise


def get_alert_notification_rules(conn, timestamp, preferred_username=None, user_id=None, organization_ids=None):
    '''
    Gets alert notification rules given a user_id or a list of organization IDs.
    :param conn: database connection
    :param timestamp: timestamp when this request was made
    :param preferred_username: preferred username of the user
    :param user_id: user_id of the user whose alert notification rules are being requested
    :param organization_ids: (list) of organization IDs
    :return: (dict of list) -> {user_id: [ {urgency_level: [1, 2],
                notification_rules: {order: {notification_method: x, minutes_buffer: y}, ... } } ], ...}
    '''
    assert isinstance(timestamp, datetime.datetime)
    query_params = {'timestamp': timestamp}

    conditions = []
    if preferred_username is not None:
        assert string_validator.is_valid_preferred_username(preferred_username)
        conditions.append(''' user_id in (
                                select user_id from users
                                where start_timestamp <= %(timestamp)s
                                    and end_timestamp > %(timestamp)s
                                    and preferred_username = %(pref_name)s
                          )''')
        query_params['pref_name'] = preferred_username
    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append(" user_id = %(user_name)s ")
        query_params['user_name'] = user_id
    if organization_ids is not None:
        conditions.append(''' user_id in (
                                select member_name from organization_members
                                where start_timestamp <= %(timestamp)s
                                    and end_timestamp > %(timestamp)s
                                    and organization_id = any(%(org_id)s)
                          )''')
        query_params['org_id'] = helpers.get_int_list(organization_ids)

    query = '''
            select user_id, anr.notification_rule_id, rules, urgency_levels
            from alert_notification_rules as anr
            join notification_rules as nr using (notification_rule_id)
            where anr.start_timestamp <= %(timestamp)s
                and anr.end_timestamp > %(timestamp)s
                and nr.start_timestamp <= %(timestamp)s
                and nr.end_timestamp > %(timestamp)s
                {0}
            order by urgency_levels;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for user_, rule_id_, rules_, urgency_ in result:
            if user_ not in data:
                data[user_] = []
            data[user_].append({
                var_names.notification_rule_id: rule_id_,
                var_names.notification_rules: rules_,
                var_names.urgency_level: urgency_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def create_alert_notification_rule(conn, timestamp, user_id, urgency_level, notification_rules):
    '''
    Create a new alert notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: user_id of the user who this notification rule is for
    :param urgency_level: urgency levels this notification rule is for
    :param notification_rules: (dict) of rules -> { minutes_buffer: [ notification_method, ...], ...}
    :return: (int) -> rule ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    urgency_set = set(urgency_level)
    assert urgency_set.issubset(set(configuration.allowed_urgency_levels))
    organization_validator.validate_notification_rules(notification_rules)

    query = " select create_alert_notification_rule(%s, %s, %s, %s, %s); "
    query_params = (user_id, timestamp, constants.end_timestamp, list(urgency_set), json.dumps(notification_rules))
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_notification_alert_duplicate_urgency)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def edit_alert_notification_rule(conn, timestamp, rule_id, user_id, urgency_level, notification_rules):
    '''
    Edit an existing alert notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param rule_id: the ID of the rule to edit
    :param user_id: user_id of the user who this notification rule is for
    :param urgency_level: urgency levels this notification rule is for
    :param notification_rules: (dict) of rules -> { minutes_buffer: [ notification_method, ...], ...}
    :return: (int) -> rule ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(rule_id, int)
    urgency_set = set(urgency_level)
    assert urgency_set.issubset(set(configuration.allowed_urgency_levels))
    organization_validator.validate_notification_rules(notification_rules)

    query = " select edit_alert_notification_rule(%s, %s, %s, %s, %s, %s); "
    query_params = (user_id, timestamp, constants.end_timestamp, rule_id, list(urgency_set),
                    json.dumps(notification_rules))
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.IntegrityError as e:
        if e.pgcode == errorcodes.CHECK_VIOLATION:
            raise LookupError(errors.err_notification_alert_duplicate_urgency)
        else:
            raise
    except psycopg2.DatabaseError:
        raise


def delete_alert_notification_rule(conn, timestamp, rule_id, user_id):
    '''
    Edit an existing alert notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param rule_id: ID of the rule to delete
    :param user_id: user_id of the user who this notification rule is for
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(rule_id, int)

    query = " select delete_alert_notification_rule(%s, %s, %s); "
    query_params = (user_id, timestamp, rule_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_hand_off_notification_rules(conn, timestamp, preferred_username=None, user_id=None, organization_ids=None):
    '''
    Gets hand-off notification rules given a user_id or a list of organization IDs.
    :param conn: database connection
    :param timestamp: timestamp when this request was made
    :param preferred_username: preferred username of the user
    :param user_id: user_id of the user whose alert notification rules are being requested
    :param organization_ids: (list) of organization IDs
    :return: (dict of list) -> {user_id: [ {only_in_service: True, policy_id: None,
                notification_rules: {order: {notification_method: x, minutes_buffer: y}, ... } } ], ...}
    '''
    assert isinstance(timestamp, datetime.datetime)
    query_params = {'timestamp': timestamp}

    conditions = []
    if preferred_username is not None:
        assert string_validator.is_valid_preferred_username(preferred_username)
        conditions.append(''' user_id in (
                                select user_id from users
                                where start_timestamp <= %(timestamp)s
                                    and end_timestamp > %(timestamp)s
                                    and preferred_username = %(pref_name)s
                          )''')
        query_params['pref_name'] = preferred_username
    if user_id is not None:
        assert isinstance(user_id, int)
        conditions.append(" user_id = %(user_name)s ")
        query_params['user_name'] = user_id
    if organization_ids is not None:
        conditions.append(''' user_id in (
                                select member_name from organization_members
                                where start_timestamp <= %(timestamp)s
                                    and end_timestamp > %(timestamp)s
                                    and organization_id = any(%(org_id)s)
                          )''')
        query_params['org_id'] = helpers.get_int_list(organization_ids)

    query = '''
            select user_id, hnr.notification_rule_id, rules, only_in_service, policyid
            from handoff_notification_rules as hnr
            join notification_rules as nr using (notification_rule_id)
            where hnr.start_timestamp <= %(timestamp)s
                and hnr.end_timestamp > %(timestamp)s
                and nr.start_timestamp <= %(timestamp)s
                and nr.end_timestamp > %(timestamp)s
                {0}
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for user_, rule_id_, rules_, only_in_serv_, pol_id_ in result:
            if user_ not in data:
                data[user_] = []
            data[user_].append({
                var_names.notification_rule_id: rule_id_,
                var_names.notification_rules: rules_,
                var_names.only_in_service: only_in_serv_,
                var_names.policy_id: pol_id_
            })
        return data
    except psycopg2.DatabaseError:
        raise


def create_hand_off_notification_rule(conn, timestamp, user_id, notification_rules):
    '''
    Create a new hand off notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: user_id of the user who this notification rule is for
    :param notification_rules: (dict) of rules -> { minutes_buffer: [ notification_method, ...], ...}
    :return: (int) -> rule ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    organization_validator.validate_notification_rules(notification_rules)

    query = " select create_hand_off_notification_rule(%s, %s, %s, %s); "
    query_params = (user_id, timestamp, constants.end_timestamp, json.dumps(notification_rules))
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.DatabaseError:
        raise


def edit_hand_off_notification_rule(conn, timestamp, rule_id, user_id, notification_rules):
    '''
    Edit an existing alert notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param rule_id: the ID of the rule to edit
    :param user_id: user_id of the user who this notification rule is for
    :param notification_rules: (dict) of rules -> { minutes_buffer: [ notification_method, ...], ...}
    :return: (int) -> rule ID
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(rule_id, int)
    organization_validator.validate_notification_rules(notification_rules)

    query = " select edit_hand_off_notification_rule(%s, %s, %s, %s, %s); "
    query_params = (user_id, timestamp, constants.end_timestamp, rule_id, json.dumps(notification_rules))
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.DatabaseError:
        raise


def delete_hand_off_notification_rule(conn, timestamp, rule_id, user_id):
    '''
    Edit an existing alert notification rule.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param rule_id: ID of the rule to delete
    :param user_id: user_id of the user who this notification rule is for
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(rule_id, int)

    query = " select delete_hand_off_notification_rule(%s, %s, %s); "
    query_params = (user_id, timestamp, rule_id)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_basic_user_list(conn, timestamp, organization_id, user_ids=None):
    '''
    Get the basic user list.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param user_ids: (list) of user IDs to filter by
    :return: (list of list) -> [ [display name, preferred username], ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    cond = ''
    if user_ids is not None:
        assert isinstance(user_ids, list)
        cond = ' and user_id = any(%(usr_id)s) '
        query_params['usr_id'] = user_ids

    query = '''
            select first_name || ' ' || last_name, preferred_username
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0}
            order by first_name;
            '''.format(cond)

    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_user_ids_from_preferred_usernames(conn, timestamp, organization_id, pref_names=None, as_dict=False):
    '''
    Get the user IDs of users given their preferred usernames.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param pref_names: (list) of preferred usernames
    :param as_dict: (boolean) True if it should be retrieved as dict
    :return: (list) of user IDs  |  if as_dict = True then dict -> {pref name: user id, ...}
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []
    if pref_names is not None:
        assert isinstance(pref_names, list)
        conditions.append('preferred_username = any(%(pref_names)s)')
        query_params['pref_names'] = pref_names

    query = '''
            select preferred_username, user_id from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                {0};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)

        if as_dict:
            data_dict = dict()
            for pref_, id_ in result:
                data_dict[pref_] = id_
            return data_dict

        else:
            data_list = []
            for pref_name, id_ in result:
                data_list.append(id_)
            return data_list
    except psycopg2.DatabaseError:
        raise


def get_user_id_and_login_rights_from_email(conn, timestamp, email):
    '''
    Get the user ID of a user given his/her email.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param email: email of the user
    :return: (tuple) -> (user ID, boolean to allow login or not)
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_email_address(email)

    query = '''
            select users.user_id, mem.role_id, oso.allow_direct_login
            from users
            join organization_members as mem
                on mem.organization_id = users.organization_id
                    and mem.member_id = users.user_id
                    and mem.start_timestamp <= %(timestamp)s
                    and mem.end_timestamp > %(timestamp)s
            left join organization_sso as oso
                on oso.organization_id = users.organization_id
                    and oso.start_timestamp <= %(timestamp)s
                    and oso.end_timestamp > %(timestamp)s
            where users.email = %(eml)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'eml': email}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            user_id, role_id, allow_login = result[0][0], result[0][1], result[0][2]

            if allow_login or allow_login is None or role_id == roles.owner_role_id:
                allow_login = True
            else:
                allow_login = False

            return user_id, allow_login
        elif len(result) == 0:
            raise LookupError(errors.err_invalid_email_or_region)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_user_id_and_sso_permits(conn, timestamp, subdomain, integration_type, email):
    '''
    Checks if an organization with a given subdomain has a specific type of SSO integration and
    if a given email is associated to it or not.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param subdomain: the TaskCall subdomain of the organization that the user is supposedly associated to
    :param integration_type: the type of integration they have
    :param email: email of the user
    :return: (tuple) -> (user id, has auto provisioning)
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_valid_subdomain(subdomain)
    assert integration_type in configuration.allowed_sso_types
    assert string_validator.is_email_address(email)

    query = '''
            select users.user_id, oss.allow_auto_provision
            from organizations as org
            join organization_sso as oss using(organization_id)
            join integration_types as intt using(integration_type_id)
            left join users
                on users.organization_id = org.organization_id
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and users.email = %(eml)s
            where org.subdomain = %(sub_dom)s
                and org.start_timestamp <= %(timestamp)s
                and org.end_timestamp > %(timestamp)s
                and oss.start_timestamp <= %(timestamp)s
                and oss.end_timestamp > %(timestamp)s
                and intt.integration_type = %(intt_name)s;
            '''
    query_params = {'timestamp': timestamp, 'sub_dom': subdomain, 'intt_name': integration_type, 'eml': email}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0][0], result[0][1]
        elif len(result) == 0:
            raise LookupError(errors.err_invalid_email_or_region)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_user_organization_subdomain(conn, timestamp, email):
    '''
    Identifies a user given his email and fetches the subdomain of the organization he belongs to.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param email: email of the user
    :return: (str) -> subdomain
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_email_address(email)

    query = '''
            select subdomain from users
            join organizations as org using(organization_id)
            where users.email = %(eml)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and org.start_timestamp <= %(timestamp)s
                and org.end_timestamp > %(timestamp)s;
            '''
    query_params = {'eml': email, 'timestamp': timestamp}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0][0]
        elif len(result) == 0:
            raise LookupError(errors.err_invalid_email_or_region)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_preferred_username_id_and_permissions(conn, timestamp, organization_id):
    '''
    Gets the user IDs and permissions of all the users of an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param organization_id: ID of the organization
    :return: (dict) -> { pref_name: [id, perm], ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select preferred_username, users.user_id, up.permissions
            from users
            join user_permissions as up using(user_id)
            where up.start_timestamp <= %(timestamp)s
                and up.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and users.organization_id = %(org_id)s;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for name_, id_, perm_ in result:
            data[name_] = [id_, perm_]
        return data
    except psycopg2.DatabaseError:
        raise


def get_user_name_pref_keyed_on_id(conn, timestamp, organization_id, user_ids):
    '''
    Get the display name and preferred username of users keyed on their ID.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization
    :param user_ids: (list of int) user IDs
    :return: (dict of list) -> { user ID: [display name, preferred username], ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_ids, list)

    query = '''
            select user_id, first_name || ' ' || last_name, preferred_username
            from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and user_id = any(%s);
            '''
    query_params = (timestamp, timestamp, organization_id, user_ids,)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, name_, pref_name_ in result:
            data[id_] = [name_, pref_name_]
        return data
    except psycopg2.DatabaseError:
        raise


def get_user_policy_ids_from_preferred_usernames(conn, timestamp, organization_id, pref_names):
    '''
    Get the list of policy ids of users given their preferred usernames.
    :param conn: db connection
    :param timestamp: timestamp this request is being made on
    :param organization_id: ID of the organization
    :param pref_names: (str or list) of string of preferred usernames
    :return: (list of int) -> policy IDs
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(pref_names, str) or isinstance(pref_names, list)

    query = '''
            select policyid from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and preferred_username = any(%(pref_names)s)
                and policyid is not null;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id,
                    'pref_names': helpers.get_string_list(pref_names)}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for item in result:
            data.append(item[0])
        return data
    except psycopg2.DatabaseError:
        raise


def get_user_advanced_component_permissions(conn, timestamp, preferred_username):
    '''
    Get the advanced component permissions of a user.
    Owner, admins, full stakeholder and limited stakeholders cannot have advanced component permissions.
    Hence, we have put an additional check in the query to ensure that results are obtained for only users
    with flexible roles.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made on
    :param preferred_username: preferred username of the user
    :return: (list of dict) -> [{ component_type: , reference_id: , name: , user_role: }, ...]
    :errors: AssertionError, DatabaseError, SystemError
    '''
    assert isinstance(timestamp, datetime.datetime)

    query = '''
            with t1 as (
                select users.user_id from users
                join organization_members as org_mem
                    on users.user_id = org_mem.member_id
                join user_role_map as urm
                    on urm.role_id = org_mem.role_id
                where users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and users.preferred_username = %(pref_name)s
                    and org_mem.start_timestamp <= %(timestamp)s
                    and org_mem.end_timestamp > %(timestamp)s
                    and urm.is_owner is not true
                    and urm.is_admin is not true
                    and urm.is_stakeholder is not true
            )
            , t2 as (
                select * from advanced_permissions
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and user_id in (select user_id from t1)
            )
            select component_type_id, routine_ref_id, routine_name, user_role
            from t2
            join routines
                on t2.component_id = routines.routineid
            where t2.component_type_id = %(rou_comp_id)s
                and routines.start_timestamp <= %(timestamp)s
                and routines.end_timestamp > %(timestamp)s
                and routines.is_group_routine = true
            union
            select component_type_id, policy_ref_id, policy_name, user_role
            from t2
            join policies
                on t2.component_id = policies.policyid
            where t2.component_type_id = %(pol_comp_id)s
                and policies.start_timestamp <= %(timestamp)s
                and policies.end_timestamp > %(timestamp)s
                and policies.is_group_policy = true
            union
            select component_type_id, service_ref_id, service_name, user_role
            from t2
            join services
                on t2.component_id = services.serviceid
            where t2.component_type_id = %(srv_comp_id)s
                and services.start_timestamp <= %(timestamp)s
                and services.end_timestamp > %(timestamp)s
            union
            select component_type_id, group_ref_id, group_name, user_role
            from t2
            join external_groups
                on t2.component_id = external_groups.group_id
            where t2.component_type_id = %(grp_comp_id)s
                and external_groups.start_timestamp <= %(timestamp)s
                and external_groups.end_timestamp > %(timestamp)s
            order by component_type_id;
            '''
    query_params = {'timestamp': timestamp, 'pref_name': preferred_username,
                    'rou_comp_id': configuration.routine_component_type_id,
                    'pol_comp_id': configuration.policy_component_type_id,
                    'srv_comp_id': configuration.service_component_type_id,
                    'grp_comp_id': configuration.group_component_type_id}
    try:
        result = conn.fetch(query, query_params)
        data = []
        for comp_typ_id, comp_ref, comp_name, usr_role in result:
            if comp_typ_id == configuration.routine_component_type_id:
                comp_type_name = var_names.routines
            elif comp_typ_id == configuration.policy_component_type_id:
                comp_type_name = var_names.policies
            elif comp_typ_id == configuration.service_component_type_id:
                comp_type_name = var_names.services
            elif comp_typ_id == configuration.group_component_type_id:
                comp_type_name = var_names.groups
            else:
                raise SystemError(errors.err_internal_component_type_id_invalid + ' ' + str(comp_typ_id))

            data.append({
                var_names.component_type: comp_type_name,
                var_names.reference_id: key_manager.conceal_reference_key(comp_ref),
                var_names.name: comp_name,
                var_names.user_role: usr_role
            })

        return data
    except psycopg2.DatabaseError:
        raise


def add_user_advanced_component_permission(conn, timestamp, org_id, user_pref, comp_type_id, comp_ref, user_role):
    '''
    Add a new advanced component permission for a user on a specific component.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: organization ID
    :param user_pref: preferred username of the user who this permission is for
    :param comp_type_id: component type ID
    :param comp_ref: reference ID of the component (concealed)
    :param user_role: (str) the role the user should have on this component
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert comp_type_id in [configuration.routine_component_type_id, configuration.policy_component_type_id,
                            configuration.service_component_type_id, configuration.group_component_type_id]
    assert string_validator.is_valid_preferred_username(user_pref)
    assert user_role in roles.advanced_component_roles

    user_role_perm = permissions.create_advanced_component_permission(user_role)
    unmasked_comp_ref = key_manager.unmask_reference_key(comp_ref)

    query = '''
            select add_advanced_component_permission(
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s
            );
            '''
    query_params = (org_id, user_pref, timestamp, constants.end_timestamp,
                    comp_type_id, unmasked_comp_ref, user_role, user_role_perm,)
    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_user_advanced_component_permission(conn, timestamp, org_id, user_pref, comp_type_id, comp_ref):
    '''
    Delete the advanced component permissions a user has on a specific component.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: the organization ID
    :param user_pref: preferred username of the user who this permission is for
    :param comp_type_id: component type ID
    :param comp_ref: reference ID of the component (concealed)
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(org_id, int)
    assert comp_type_id in [configuration.routine_component_type_id, configuration.policy_component_type_id,
                            configuration.service_component_type_id, configuration.group_component_type_id]
    assert string_validator.is_valid_preferred_username(user_pref)
    unmasked_comp_ref = key_manager.unmask_reference_key(comp_ref)

    query = "select delete_advanced_component_permission(%s, %s, %s, %s::smallint, %s);"
    query_params = (org_id, user_pref, timestamp, comp_type_id, unmasked_comp_ref,)
    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_user_phone_numbers(conn, timestamp, organization_id, user_ids: list):
    '''
    Get the details of the phone numbers associated to a given list of users in an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: (int) the organization ID
    :param user_ids: (list) of user IDs
    :return: (dict) -> {user ID: phone, ...}
    :errors: AssertionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query = '''
            select user_id, phone
            from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and user_id = any(%(usr_ids)s);
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'usr_ids': helpers.get_int_list(user_ids)}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for usr_, phone_ in result:
            data[usr_] = phone_
        return data
    except psycopg2.DatabaseError:
        raise


def get_admin_emails(conn, start_date, end_date, org_ids=None):
    '''
    Get the email addresses of all the admins of the organization. The most recent admin's latest email is retrieved.
    :param conn: db connection
    :param start_date: (datetime.date) start period
    :param end_date: (datetime.date) end period
    :param org_ids: (int or list) of organization IDs
    :return: (dict) -> {org_id: email, ...}
    '''
    assert isinstance(start_date, datetime.date)
    assert isinstance(end_date, datetime.date)

    # Increasing the end date by one day so the start date and end date logic can pull the last day's data as well
    end_date = start_date + datetime.timedelta(days=1)

    query_params = {'start_date': start_date, 'end_date': end_date, 'owner_role_id': roles.owner_role_id}
    conditions = []
    if org_ids is not None:
        org_ids = helpers.get_int_list(org_ids)
        conditions.append(" users.organization_id = any(%(org_ids)s) ")
        query_params['org_ids'] = org_ids
    query = '''
            select distinct on(users.organization_id) users.organization_id, email
            from users
            join organization_members as om on users.user_id = om.member_id
            where users.start_timestamp < %(end_date)s
                and users.end_timestamp >= %(start_date)s
                and om.start_timestamp < %(end_date)s
                and om.end_timestamp >= %(start_date)s
                and om.role_id = %(owner_role_id)s
                {0}
            order by users.organization_id, users.start_timestamp desc;
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for id_, email_ in result:
            data[id_] = email_
        return data
    except psycopg2.DatabaseError:
        raise


def has_user_onboarding_flow(conn, timestamp, organization_id, user_id, flow_id):
    '''
    Checks if a valid onboarding flow exists for the user or not.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: ID of the organization the user belongs to
    :param user_id: ID of the user
    :param flow_id: onboarding flow ID
    :return: (boolean) True if an onboarding flow exists; False otherwise
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)
    assert isinstance(flow_id, str)

    query = '''
            select flow_id from user_onboarding
            where start_timestamp <= %s
                and flow_end > %s
                and organization_id = %s
                and user_id = %s
                and flow_id = %s;
            '''
    query_params = (timestamp, timestamp, organization_id, user_id, flow_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return False
        else:
            return True
    except psycopg2.DatabaseError:
        raise


def end_onboarding_email_subscription(conn, timestamp, user_id):
    '''
    End the onboarding email subscription for a user so no more onboarding emails are sent to them.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param user_id: ID of the user
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)

    query = '''
            update user_onboarding set email_end = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and email_end > %(timestamp)s
                and user_id = %(usr_id)s;
            '''
    query_params = {'timestamp': timestamp, 'usr_id': user_id}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_user_id_from_account_id_and_preferred_username(conn, timestamp, account_id, pref_name):
    '''
    Gets the ID of a user given their preferred username and the account ID of the user's organization.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param account_id: (str) account ID of the organization the user belongs to
    :param pref_name: (str) preferred username
    :return: user ID
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(account_id, str)
    assert isinstance(pref_name, str)
    query = '''
            select user_id from users
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and preferred_username = %(pref_name)s
                and organization_id in (
                    select organization_id from organizations
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and account_id = %(acc_id)s
                );
            '''
    query_params = {'timestamp': timestamp, 'acc_id': account_id, 'pref_name': pref_name}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return None
        else:
            return result[0][0]
    except psycopg2.DatabaseError:
        raise