# By: Riasat Ullah
# This file only contains database queries for registering and handling organization member specific requests.

from dbqueries import db_organizations, db_users
from exceptions.user_exceptions import InvalidPassword, InvalidRequest, NotUniqueValue
from modules import credentials
from utils import constants, errors, helpers, key_manager, permissions, roles, times, var_names
from validations import organization_validator, string_validator
import configuration as configs
import datetime
import json
import psycopg2


def register_standard_member(conn, timestamp, organization_id, org_perm, role_id, job_title, email, first_name,
                             last_name, password, iso_code, phone, preferred_username, user_timezone, user_language,
                             user_profile_picture, onb_flow_id):
    '''
    Registers a new organization member account with standard rights. This should not be used to register the very
    first admin user. The very first admin user gets registered along with the organization itself.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param org_perm: permissions of the organization
    :param role_id: (int) the ID of the role the user will have in the organization
    :param job_title: (str) the job title the user has in the organization
    :param email: email address of the user
    :param first_name: first name of the user
    :param last_name: last name of the user
    :param password: user's password
    :param iso_code: 2 letter ISO code of the user's phone country
    :param phone: user's phone number including country code (without +)
    :param preferred_username: the preferred username of the user; the taskcall email address will come from this
    :param user_timezone: the timezone the user is in
    :param user_language: the language the user's account should be set up in
    :param user_profile_picture: url for retrieving the user's profile picture
    :param onb_flow_id: unique ID that identifies the user's onboarding flow
    :errors: AssertionError, TypeError, ValueError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(role_id, int)
    assert role_id != roles.owner_role_id and role_id not in roles.get_stakeholder_user_role_ids()

    email = email.lower()
    assert db_users.email_is_unique(conn, email, timestamp)

    if not string_validator.is_valid_password(password):
        raise InvalidPassword(errors.err_password_invalid)
    if not string_validator.is_valid_preferred_username(preferred_username):
        raise InvalidRequest(errors.err_preferred_username_invalid)
    if not preferred_username_is_unique_in_organization(conn, timestamp, organization_id, preferred_username):
        raise NotUniqueValue(errors.err_preferred_username_not_unique)

    phone_code = helpers.get_phone_code(iso_code)
    organization_validator.validate_user_info(first_name, last_name, iso_code, phone_code, phone,
                                              user_timezone, user_language)
    assert string_validator.is_web_url(user_profile_picture)

    subdomain = db_organizations.get_organization_subdomain(conn, timestamp, organization_id)
    taskcall_email = helpers.construct_taskcall_email_address(preferred_username, subdomain)
    hash_password, salt = credentials.convert_text_to_hash(password)
    user_perm = permissions.get_user_permission(org_perm, role_id)

    routine_key = key_manager.generate_reference_key()
    policy_key = key_manager.generate_reference_key()

    query = '''
            select add_organization_member(
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            );
            '''

    query_params = (organization_id, timestamp, constants.end_timestamp, email,
                    role_id, job_title, first_name, last_name,
                    iso_code, phone_code, phone, preferred_username,
                    taskcall_email, salt, hash_password, user_perm,
                    routine_key, user_timezone, user_language, user_profile_picture,
                    policy_key, configs.standard_wait_minutes, constants.root_destination, onb_flow_id,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def register_stakeholder(conn, timestamp, organization_id, org_perm, role_id, job_title, email, first_name, last_name,
                         password, iso_code, phone, preferred_username, user_timezone, user_language,
                         user_profile_picture):
    '''
    Registers a new organization member account. This should not be used to register the very first admin user.
    The very first admin user gets registered along with the organization itself.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param org_perm: permissions of the organization
    :param role_id: (int) the ID of the role the user will have in the organization
    :param job_title: (str) the job title the user has in the organization
    :param email: email address of the user
    :param first_name: first name of the user
    :param last_name: last name of the user
    :param password: user's password
    :param iso_code: 2 letter ISO code of the user's phone country
    :param phone: user's phone number including country code (without +)
    :param preferred_username: the preferred username of the user; the taskcall email address will come from this
    :param user_timezone: the timezone the user is in
    :param user_language: the language the user's account should be set up in
    :param user_profile_picture: url for retrieving the user's profile picture
    :errors: AssertionError, DatabaseError, InvalidPassword, TypeError, ValueError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(role_id, int) and role_id in roles.get_stakeholder_user_role_ids()

    email = email.lower()
    assert db_users.email_is_unique(conn, email, timestamp)

    if not string_validator.is_valid_password(password):
        raise InvalidPassword(errors.err_password_invalid)
    if not string_validator.is_valid_preferred_username(preferred_username):
        raise InvalidRequest(errors.err_preferred_username_invalid)
    if not preferred_username_is_unique_in_organization(conn, timestamp, organization_id, preferred_username):
        raise NotUniqueValue(errors.err_preferred_username_not_unique)

    phone_code = helpers.get_phone_code(iso_code)
    organization_validator.validate_user_info(first_name, last_name, iso_code, phone_code, phone,
                                              user_timezone, user_language)
    assert string_validator.is_web_url(user_profile_picture)

    hash_password, salt = credentials.convert_text_to_hash(password)
    user_perm = permissions.get_user_permission(org_perm, role_id)

    query = '''
            select add_stakeholder(
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s
            );
            '''
    query_params = (organization_id, timestamp, constants.end_timestamp, email,
                    role_id, job_title, first_name, last_name,
                    iso_code, phone_code, phone, preferred_username,
                    salt, password, user_perm, user_timezone,
                    user_language, user_profile_picture)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def delete_standard_member(conn, timestamp, organization_id, member_id, member_pid, member_rid,
                           replacing_user_id, replacing_pid, replacing_rid, new_inst_assignments,
                           updated_route_actions=None, updated_workflows=None):
    '''
    Delete a member from an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param member_id: user_id of the member
    :param member_pid: policy ID of the member
    :param member_rid: routine ID of the member
    :param replacing_user_id: user_id of the user who will be replacing the member for incident assignments and roles
    :param replacing_pid: policy ID of the replacing member
    :param replacing_rid: routine ID of the replacing member
    :param new_inst_assignments: (1 item list) -> [{for policy id: , policy id: }]
    :param updated_route_actions: (list of dict) -> [{routing id: , actions: }, ...]
    :param updated_workflows: (list of dict) -> [{workflow id: , workflow name: , workflow: }, ...]
    :return: (list of dict) -> [ {instance_id: , end_timestamp: , assignee_level: }, ... ]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(member_id, int)
    assert isinstance(replacing_user_id, int)

    query = '''
            select * from delete_organization_member(
                %s, %s, %s, %s, %s,
                %s, %s, %s, %s, %s,
                %s, %s
            );
            '''

    updated_route_actions = json.dumps(updated_route_actions) if updated_route_actions is not None else None
    updated_response_actions = json.dumps(updated_workflows) if updated_workflows is not None else None

    query_params = (timestamp, constants.end_timestamp, organization_id, member_id, member_pid,
                    member_rid, replacing_user_id, replacing_pid, replacing_rid, json.dumps(new_inst_assignments),
                    updated_route_actions, updated_response_actions,)
    try:
        result = conn.fetch(query, query_params)
        return result[0][0]
    except psycopg2.DatabaseError:
        raise


def delete_stakeholder(conn, timestamp, organization_id, user_id, updated_workflows=None):
    '''
    Deletes a stakeholder.
    :param conn: db connection
    :param timestamp: timestamp the request is being made on
    :param organization_id: ID of the organization the stakeholder belongs to
    :param user_id: user_id of the user to be deleted
    :param updated_workflows: (list of dict) -> [{workflow id: , workflow name: , workflow: }, ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    updated_workflows = json.dumps(updated_workflows) if updated_workflows is not None else None

    query = " select delete_stakeholder(%s, %s, %s, %s, %s); "
    query_params = (organization_id, user_id, timestamp, constants.end_timestamp, updated_workflows,)

    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def preferred_username_is_unique_in_organization(conn, timestamp, organization_id, pref_user_name):
    '''
    Checks if a preferred username is unique in an organization or not.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param pref_user_name: the preferred username to check for
    :return: (list of tuples) -> (user_id, preferred_username)
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    if not string_validator.is_valid_preferred_username(pref_user_name):
        raise InvalidRequest(errors.err_preferred_username_invalid)
    query = '''
            select user_id from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and preferred_username = %s;
            '''
    query_params = (timestamp, timestamp, organization_id, pref_user_name,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return True
        else:
            return False
    except psycopg2.DatabaseError:
        raise


def get_member_role_id(conn, timestamp, organization_id, user_id):
    '''
    Gets the user role that a member has at a given point in time.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the user belongs to
    :param user_id: user_id of the user
    :return: (int) role ID
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    query = '''
            select role_id from organization_members
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = %s
                and member_id = %s;
            '''
    query_params = (timestamp, timestamp, organization_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]
    except psycopg2.DatabaseError:
        raise


def transfer_account_ownership(conn, timestamp, organization_id, org_perm, current_owner,
                               current_owner_new_role_id, new_owner):
    '''
    Transfer the ownership of the account to a new owner. Only the current owner can do this.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: the organization ID
    :param org_perm: permissions of the organization
    :param current_owner: user_id of the current owner
    :param current_owner_new_role_id: (int) ID of the new role the current owner will assume
    :param new_owner: user_id of the new owner
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(current_owner, int)
    assert isinstance(new_owner, int)

    current_owner_new_perm = permissions.get_user_permission(org_perm, current_owner_new_role_id)
    new_owner_new_role_id = roles.owner_role_id
    new_owner_new_perm = permissions.get_user_permission(org_perm, new_owner_new_role_id)

    query = '''
            select transfer_account_ownership(
                %s, %s, %s,
                %s, %s::smallint, %s,
                %s, %s::smallint, %s
            );
            '''
    query_params = (organization_id, timestamp, constants.end_timestamp,
                    current_owner, current_owner_new_role_id, current_owner_new_perm,
                    new_owner, new_owner_new_role_id, new_owner_new_perm,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def update_member_role(conn, timestamp, organization_id, org_perm, user_id, new_role_id):
    '''
    Updates a user's role. This can be used for all users (except for the owner) whose roles
    are changing from one non-stakeholder role to another.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the user belongs to
    :param org_perm: permissions of the organization
    :param user_id: user_id of the user
    :param new_role_id: (int) ID of the new role the user will take
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    new_user_perm = permissions.get_user_permission(org_perm, new_role_id)

    query = '''
            do
            $body$

            declare
                user_job_title organization_members.job_title%%type;

            begin

            update organization_members set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and organization_id = %(org_id)s
                and member_id = %(usr_id)s
            returning job_title into user_job_title;

            update user_permissions set end_timestamp = %(timestamp)s
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and user_id = %(usr_id)s;

            insert into organization_members values(
                %(org_id)s, %(timestamp)s, %(end_time)s, %(usr_id)s, %(role_id)s, user_job_title
            );

            insert into user_permissions values(%(usr_id)s, %(timestamp)s, %(end_time)s, %(role_perm)s);

            end;
            $body$
            '''
    query_params = {'timestamp': timestamp, 'end_time': constants.end_timestamp,
                    'org_id': organization_id, 'usr_id': user_id,
                    'role_id': new_role_id, 'role_perm': new_user_perm}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def upgrade_to_standard_user(conn, timestamp, organization_id, org_perm, user_id, new_role_id):
    '''
    Upgrades a stakeholder account to a standard user account.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the user belongs to
    :param org_perm: permissions of the organization
    :param user_id: user_id of the user
    :param new_role_id: (int) ID of new role the user will take
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    new_user_perm = permissions.get_user_permission(org_perm, new_role_id)
    routine_ref = key_manager.generate_reference_key()
    policy_ref = key_manager.generate_reference_key()

    preferred_username, subdomain = get_preferred_username_and_subdomain(conn, timestamp, user_id)
    taskcall_email = helpers.construct_taskcall_email_address(preferred_username, subdomain)

    query = '''
            select upgrade_from_stakeholder(
                %s, %s, %s, %s,
                %s::smallint, %s, %s, %s,
                %s, %s, %s
            );
            '''
    query_params = (organization_id, timestamp, constants.end_timestamp, user_id,
                    new_role_id, new_user_perm, routine_ref, policy_ref,
                    configs.standard_wait_minutes, taskcall_email, constants.root_destination,)
    print(query_params)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def downgrade_to_stakeholder(conn, timestamp, organization_id, org_perm, user_id, new_role_id):
    '''
    Downgrades a standard user account to a stakeholder account.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param organization_id: ID of the organization the user belongs to
    :param org_perm: permissions of the organization
    :param user_id: user_id of the user
    :param new_role_id: (int) ID of the new role the user will take
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(user_id, int)

    new_user_perm = permissions.get_user_permission(org_perm, new_role_id)

    query = " select downgrade_to_stakeholder(%s, %s, %s, %s, %s::smallint, %s); "
    query_params = (organization_id, timestamp, constants.end_timestamp, user_id, new_role_id, new_user_perm,)
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise


def get_organization_id_from_user_id(conn, timestamp, user_id):
    '''
    Gets the basic information of an account.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param user_id: user_id of the user whose account information is being requested
    :return: (int) organization id
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    query = '''
            select organization_id from users
            where start_timestamp <= %s
                and end_timestamp > %s
                and user_id = %s;
            '''
    query_params = (timestamp, timestamp, user_id,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        elif len(result) > 1:
            raise RuntimeError(errors.err_internal_multiple_entries_found)
        else:
            return result[0][0]
    except psycopg2.DatabaseError:
        raise


def get_preferred_username_and_subdomain(conn, timestamp, user_id):
    '''
    Get the preferred username of a user and the subdomain of the organization the user is in.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param user_id: user_id of the user
    :return: (tuple) -> (preferred user_id, subdomain)
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    query = '''
            select preferred_username, subdomain
            from users
            join organizations as orgs using(organization_id)
            where users.user_id = %(usr_id)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and orgs.start_timestamp <= %(timestamp)s
                and orgs.end_timestamp > %(timestamp)s;
            '''
    query_params = {'timestamp': timestamp, 'usr_id': user_id}
    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_unknown_resource)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found)
    except psycopg2.DatabaseError:
        raise


def get_member_roles_and_titles(conn, timestamp, organization_id):
    '''
    Get the user_id, role ids and titles of all the members of an organization(s).
    :param conn: db connection
    :param timestamp: timestamp the request is being made on
    :param organization_id: (int or list) of organization IDs
    :return: (dict of list) -> {org ID: [ {user_id: , role_id: , job_title: }, ... ], ... }
    '''
    assert isinstance(timestamp, datetime.datetime)
    query = '''
            select organization_id, member_id, role_id, job_title
            from organization_members
            where start_timestamp <= %s
                and end_timestamp > %s
                and organization_id = any(%s);
            '''
    query_params = (timestamp, timestamp, helpers.get_int_list(organization_id),)
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        for org_id, member_id, role_id, job_title in result:
            if org_id not in data:
                data[org_id] = []
            data[org_id].append({
                var_names.user_id: member_id,
                var_names.role_id: role_id,
                var_names.job_title: job_title
            })
        return data
    except psycopg2.DatabaseError:
        raise


def get_members_list(conn, timestamp, organization_id, with_team_member_id=None, row_limit=None, row_offset=None,
                     search_words=None, team_ref_id=None):
    '''
    Gets the list of active members of an organization.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param with_team_member_id: filter by those who are team members of this user
    :param row_limit: (optional) number of members to fetch
    :param row_offset: (optional) number of members to skip ahead
    :param search_words: (str) keywords to search with
    :param team_ref_id: (concealed) reference key of the team to filter users by
    :return: (list of tuple) -> [ (preferred username, display_name, email, role name, job title, is locked), ...]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)

    query_params = {'timestamp': timestamp, 'org_id': organization_id}
    conditions = []

    if with_team_member_id is not None:
        assert isinstance(with_team_member_id, int)
        conditions.append('''
            mem.member_id in (
                select user_id from team_members
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and team_id in (
                        select team_id from team_members
                        where start_timestamp <= %(timestamp)s
                            and end_timestamp > %(timestamp)s
                            and user_id = %(usr_id)s
                    )
            )
        ''')
        query_params['usr_id'] = with_team_member_id

    if search_words is not None:
        assert isinstance(search_words, str)
        conditions.append('''(
            (LOWER(users.first_name || users.last_name) like '%%' || LOWER(%(search_words)s) || '%%')
            or (mem.job_title is not null and LOWER(mem.job_title) like '%%' || LOWER(%(search_words)s) || '%%')
        )''')
        query_params['search_words'] = search_words

    if team_ref_id is not None:
        if with_team_member_id is not None:
            assert isinstance(with_team_member_id, int)
            conditions.append('''
                mem.member_id in (
                    select user_id from team_members
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and team_id in (
                            select team_id from teams
                            where start_timestamp <= %(timestamp)s
                                and end_timestamp > %(timestamp)s
                                and team_ref_id = %(tm_ref_id)s
                        )
                )
            ''')
            query_params['tm_ref_id'] = key_manager.unmask_reference_key(team_ref_id)

    limit_cond = ''
    if row_limit is not None:
        assert isinstance(row_limit, int)
        limit_cond += ' limit {0} '.format(str(row_limit))
    if row_offset is not None:
        assert isinstance(row_offset, int)
        limit_cond += ' offset {0} '.format(str(row_offset))

    query = '''
            select users.preferred_username, users.first_name || ' ' || users.last_name,
                users.email, mem.role_id, mem.job_title, users.photo_url, lck.user_id
            from organization_members as mem
            join users on mem.organization_id = users.organization_id
                and mem.member_id = users.user_id
            left join locked_accounts as lck
                on mem.member_id = lck.user_id
                    and lck.start_timestamp <= %(timestamp)s
                    and lck.end_timestamp > %(timestamp)s
            where mem.organization_id = %(org_id)s
                and mem.start_timestamp <= %(timestamp)s
                and mem.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                {0}
            order by users.first_name
                {1};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '', limit_cond)
    try:
        result = conn.fetch(query, query_params)
        members = []
        for pref_name, disp_name, user_email, role_id, job_title, photo_url, lck_usr in result:
            role_name = roles.user_role_maps[role_id]
            is_locked = False if lck_usr is None else True
            members.append((pref_name, disp_name, user_email, role_name, job_title, photo_url, is_locked))

        return members
    except psycopg2.DatabaseError:
        raise


def get_non_stakeholder_internal_details(conn, timestamp, organization_id, start_period=None, end_period=None,
                                         user_id=None):
    '''
    Get the identification details of non-stakeholder members.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: organization id
    :param start_period: (optional) start of the period to check from
    :param end_period: (optional) end of the period to check till
    :param user_id: (optional) (int) ID of the user
    :return: (list of tuple) -> [(user_id_1, preferred username, display_name, pol id, timezone), ...]]
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    search_cond = []
    query_params = {'timestamp': timestamp, 'org_id': organization_id}

    if start_period is None and end_period is None:
        search_cond.append('''
                users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                and mem.start_timestamp <= %(timestamp)s
                and mem.end_timestamp > %(timestamp)s
        ''')
    else:
        if start_period is not None:
            search_cond.append(" users.end_timestamp > %(start_period)s and mem.end_timestamp > %(start_period)s ")
            query_params['start_period'] = times.get_date_from_string(start_period)
        if end_period is not None:
            search_cond.append(" users.start_timestamp <= %(end_period)s and mem.start_timestamp <= %(end_period)s ")
            query_params['end_period'] = times.get_date_from_string(end_period)

    if user_id is not None:
        search_cond.append(' mem.member_id = %(usr_id)s ')
        query_params['usr_id'] = user_id

    query = '''
            with t1 as (
                select mem.member_id, max(users.start_timestamp) as max_tmsp
                from organization_members as mem
                join users
                    on mem.member_id = users.user_id
                        and mem.organization_id = users.organization_id
                where mem.organization_id = %(org_id)s
                    and policyid is not null
                    {0}
                group by mem.member_id
            )
            select t1.member_id, users.preferred_username, users.first_name || ' ' || users.last_name,
                users.policyid, users.user_timezone
            from t1
            join users
                on users.user_id = t1.member_id
                    and users.start_timestamp <= t1.max_tmsp
                    and users.end_timestamp > t1.max_tmsp;
            '''.format(' and ' + ' and '.join(search_cond) if len(search_cond) > 0 else '')
    try:
        result = conn.fetch(query, query_params)
        members = []
        for member_, pref_username, display_name, pol_id, tz in result:
            members.append((member_, pref_username, display_name, pol_id, tz))
        return members
    except psycopg2.DatabaseError:
        raise


def unlock_member(conn, timestamp, organization_id, member_id, unlocked_by=None):
    '''
    Unlock an organization member.
    :param conn: db connection
    :param timestamp: timestamp when the user is being unlocked
    :param organization_id: ID of the organization
    :param member_id: ID of the user whose account is being unlocked
    :param unlocked_by: ID of the user who is unlocking
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(organization_id, int)
    assert isinstance(member_id, int)
    if unlocked_by is not None:
        assert isinstance(unlocked_by, int)

    query = '''
            begin;

            update locked_accounts set end_timestamp = %(timestamp)s, unlocked_by = %(unlk_by)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 organization_id = %(org_id)s
                        and user_id = %(mem_id)s
                );

            end;
            '''
    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'mem_id': member_id, 'unlk_by': unlocked_by}
    try:
        conn.execute(query, query_params)
    except psycopg2.DatabaseError:
        raise