"""User information raw queries module."""

import json

from owsresponse import response, status

from users import constants
from users.connectors import mysql
from users.models.identities import get_identity_by_auth0_id, get_identity_by_email
from users.models.user_info import alchemyencoder


def _format_oa_user_data(data):
    """Get a dict representation of OAUser."""
    return {
        'type': 'oa',
        'user_id': 'oa:{}'.format(data.user_id),
        'first_name': data.first_name,
        'last_name': data.last_name,
        'email': data.email,
        'account': {'vendor_id': None, 'subaccount_id': None},
        'active': data.active,
        'office_phone': data.office_phone,
    }


def _format_contact_data(data):
    """Get dict representation for contact data."""
    return {'first_name': data.first_name, 'last_name': data.last_name, 'email': data.email}


def _format_vend_contact_data(data):
    """Get a dict representation of ALWUserVendContact."""
    return {
        'type': 'alw',
        'user_id': 'alw:{}'.format(data.user_id),
        'account': {'vendor_id': data.vendor_id, 'subaccount_id': data.subaccount_id},
        'login': data.login,
        'language': data.language,
        'auth0_user_id': data.auth0_user_id,
        'primary': data.auth0_primary,
        'active': data.active,
    }


@mysql.wrap_db_errors
def fetch_oa_user_raw(user_id):
    """Fetch Orchard Admin user records.

    Args:
        user_id: (int): Orchadmin table identifier.

    Returns:
        dict: OA user record.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT id as user_id, f_name as first_name, \
            l_name as last_name, email, active, office_phone \
        FROM orchadmin_users oa\
        WHERE oa.id = :userId'
        query_result = session.execute(sql, {'userId': user_id})
        result = query_result.fetchone()
        if not result:
            return response.create_not_found_response(message='User not found.')

        return response.Response(
            message=json.loads(json.dumps(_format_oa_user_data(result), default=alchemyencoder))
        )


@mysql.wrap_db_errors
def fetch_vend_contact_raw(user_id):
    """Fetch Workstation (ALW) user records.

    Args:
        user_id: (int): vend_contact identifier.

    Returns:
        dict: ALW user records.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT vc.auth0_user_id, vc.login, vc.auth0_primary,\
            vc.id AS user_id, vc.vendor_id, vc.subaccount_id, \
            vc.`language`, vc.active \
        FROM vend_contact vc \
        WHERE vc.id = :userId'
        query_result = session.execute(sql, {'userId': user_id})
        result = query_result.fetchone()
        if not result:
            return response.create_not_found_response(message='User not found.')

        return response.Response(
            message=json.loads(
                json.dumps(_format_vend_contact_data(result), default=alchemyencoder)
            )
        )


@mysql.wrap_db_errors
def fetch_vend_contact_raw_with_roles(user_id):
    """Fetch Workstation (ALW) user records with roles.

    Args:
        user_id: (int): vend_contact identifier.

    Returns:
        dict: ALW user records with roles.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT vc.auth0_user_id, vc.login, vc.auth0_primary,\
            vc.id AS user_id, vc.vendor_id, vc.subaccount_id, \
            vc.language, vc.active, con.contact_first_name AS first_name, \
            con.contact_last_name AS last_name, con.contact_email AS email, \
            v.company AS company, \
            GROUP_CONCAT(DISTINCT ro.role_id) AS role_ids \
        FROM vend_contact vc \
        INNER JOIN contact con ON con.contact_id = vc.contact_id \
        INNER JOIN vend_contact_roles ro ON vc.id = ro.vend_contact_id \
        INNER JOIN vendor v ON v.vendor_id = vc.vendor_id \
        WHERE vc.id = :userId GROUP BY vc.id'
        query_result = session.execute(sql, {'userId': user_id})
        result = query_result.first()
        if not result:
            return response.create_not_found_response(message='User not found.')

        user_dict = _format_vend_contact_data(result)
        user_dict.update(_format_contact_data(result))
        user_dict['company'] = result.company
        user_dict['role_ids'] = [int(role_id) for role_id in result.role_ids.split(',')]

        return response.Response(message=json.loads(json.dumps(user_dict, default=alchemyencoder)))


@mysql.wrap_db_errors
def fetch_vend_contact_details(user_id):
    """Fetch vend_contact and other related details.

    Args:
        user_id: (int): vend_contact identifier.

    Returns:
        dict: ALW user records.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT \
            vc.auth0_user_id, vc.login, vc.auth0_primary, \
            vc.id AS user_id, vc.vendor_id, vc.subaccount_id, \
            vc.`language`, vc.active, con.contact_first_name AS first_name, \
            con.contact_last_name AS last_name, con.contact_email AS email, \
            v.company AS company, vctr.currency_id AS currency_id, \
            cur.`ISO_4217_code` AS currency_iso_code, \
            GROUP_CONCAT(DISTINCT ro.role_id) AS role_ids, \
            v.wel_email_sender \
        FROM vend_contact vc  \
          INNER JOIN contact con ON con.contact_id = vc.contact_id \
          INNER JOIN vendor v ON v.vendor_id = vc.vendor_id \
          LEFT JOIN vend_contact_roles ro ON vc.id = ro.vend_contact_id \
          LEFT JOIN vw_active_vendor_contract act \
              ON vc.vendor_id = act.vendor_id \
          LEFT JOIN vendor_contract vctr ON act.vendor_contract_id = vctr.id \
          LEFT JOIN currencies cur ON cur.id = vctr.currency_id \
        WHERE vc.id = :userId GROUP BY vc.id'
        query_result = session.execute(sql, {'userId': user_id})
        result = query_result.first()
        if not result:
            return response.create_not_found_response(message='User not found.')

        user_dict = _format_vend_contact_data(result)
        user_dict.update(_format_contact_data(result))
        user_dict['currency'] = result.currency_iso_code
        user_dict['company'] = result.company
        user_dict['wel_email_sender'] = result.wel_email_sender

        user_dict['default_brand'] = constants.ORCHARD_BRAND
        identity = None
        if result.auth0_user_id:
            identity = get_identity_by_auth0_id(result.auth0_user_id)
        if not identity and result.email:
            identity = get_identity_by_email(result.email)
        if identity:
            default_brand = identity.message.get('default_brand', constants.ORCHARD_BRAND)
            if default_brand == constants.AUTH0_ORCHARD_ORG_NAME:
                default_brand = constants.ORCHARD_BRAND
            user_dict['default_brand'] = default_brand

        if result.role_ids:
            user_dict['role_ids'] = [int(role_id) for role_id in result.role_ids.split(',')]
        else:
            user_dict['role_ids'] = []
        return response.Response(message=json.loads(json.dumps(user_dict, default=alchemyencoder)))


@mysql.wrap_db_errors
def fetch_alw_user_by_login(login_name, password_hash):
    """Fetch Workstation (ALW) user ids for this login_name.

    Args:
        login_name: (str): vend_contact.login.
        password_hash: (str): hashed password.

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT \
            vc.id AS vend_contact_id, c.contact_email, \
            vc.login, vc.passwords AS password_hash, \
            vc.passwords as password, vc.auth0_user_id AS auth0_user_id \
        FROM vend_contact vc \
        INNER JOIN contact c ON c.contact_id = vc.contact_id \
        WHERE vc.login = :login'
        query_result = session.execute(sql, {'login': login_name})
        result = query_result.first()

        if not result:
            return response.create_error_response(
                'username_dont_match',
                'No matching record found with {}'.format(login_name),
                status=status.NOT_FOUND,
            )
        if result.auth0_user_id:
            return response.create_error_response(
                'migrated_user',
                'user already migrated with auth0 id: {}'.format(result.auth0_user_id),
                status=status.NOT_FOUND,
            )
        if result.password != password_hash:
            return response.create_error_response(
                'username_password_dont_match',
                'DB password {} dont match provided value: {}'.format(
                    result.password, password_hash
                ),
                status=status.NOT_FOUND,
            )

        limited_data = {
            'contact_email': result.contact_email,
            'password_hash': result.password_hash,
            'vend_contact_id': result.vend_contact_id,
            'login': result.login,
        }
        return response.Response(message=limited_data)


@mysql.wrap_db_errors
def fetch_alw_users_by_email(email):
    """Fetch Workstation (ALW) user(s) for this email.

    Args:
        email: (str): contact.contact_email.

    Returns:
        Response: A list of dict with alw user(s) details.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT \
            c.contact_email AS email, \
            vc.id AS user_id, \
            vc.vendor_id AS vendor_id \
        FROM contact c \
        INNER JOIN vend_contact vc ON c.contact_id = vc.contact_id \
        WHERE c.contact_email = :email'

        email = email.replace(' ', '')
        query_result = session.execute(sql, {'email': email})
        result = query_result.fetchall()

        if not result:
            return response.create_error_response(
                'email_dont_match',
                'No matching record found with {}'.format(email),
                status=status.NOT_FOUND,
            )

        alw_users = []
        for user in result:
            alw_email = user[0]
            user_id = user[1]
            vendor_id = user[2]

            alw_users.append({'email': alw_email, 'user_id': user_id, 'vendor_id': vendor_id})

        return response.Response(message={'items': alw_users})