"""User information DB layer module.

Contains functions that retrieve general information related to user accounts.
"""

import datetime
import decimal
import json

from owsresponse import response
from sqlalchemy import Boolean, case, Column, DATETIME, Enum, ForeignKey, func, Integer, String
from sqlalchemy.orm import relationship

from users import constants as const
from users.connectors import mysql
from users.models.sql import document


class OAUser(mysql.BaseModel):
    """OA User Model."""

    __tablename__ = 'orchadmin_users'

    user_id = Column(Integer, name='id', primary_key=True, nullable=False)
    first_name = Column(String, name='f_name', nullable=False)
    last_name = Column(String, name='l_name', nullable=False)
    email = Column(String, name='email')
    active = Column(String, name='active')
    auth0_user_id = Column(String)
    office_phone = Column(String, name='office_phone')
    is_product_manager = Column(Boolean, name='is_product_manager')
    closer = Column(String, name='closer')

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


class OAUserRoles(mysql.BaseModel):
    """OA User Roles Model."""

    __tablename__ = 'orchadmin_user_roles'

    oa_user_roles_id = Column(Integer, name='id', primary_key=True)
    orchadmin_user_id = Column(Integer, ForeignKey('orchadmin_users.id'))
    orchadmin_role_id = Column(Integer)

    def to_dict(self):
        """Get a dict representation of OAUserRoles."""
        return {
            'oa_user_id': self.orchadmin_user_id,
            'oa_user_roles_id': self.oa_user_roles_id,
            'oa_role_id': self.orchadmin_role_id,
        }


class OARoles(mysql.BaseModel):
    """OA Roles Model."""

    __tablename__ = 'orchadmin_roles'

    orchadmin_role_id = Column(Integer, name='orchadmin_role_id', primary_key=True, nullable=False)
    orchadmin_role = Column(String, name='orchadmin_role', nullable=False)

    def to_dict(self):
        """Get a dict representation of OAUser."""
        return {'orchadmin_role_id': self.orchadmin_role_id, 'orchadmin_role': self.orchadmin_role}


class ALWUserVendContact(mysql.BaseModel):
    """ALWUserVendContact Model."""

    __tablename__ = 'vend_contact'

    user_id = Column(Integer, name='id', primary_key=True, nullable=False)
    contact_id = Column(Integer, ForeignKey('contact.contact_id'), name='contact_id')
    vendor_id = Column(Integer, name='vendor_id')
    subaccount_id = Column(Integer, name='subaccount_id')
    language = Column(String, name='language')
    master = Column(Enum('Y', 'N'), nullable=False, default='Y')
    login = Column(String, name='login')
    password_hash = Column(String, name='passwords')
    auth0_user_id = Column(String)
    auth0_migration_date = Column(DATETIME)
    auth0_primary = Column(Enum('Y'), nullable=True, default=None)
    active = Column(Enum('Y', 'N'), nullable=False, default='Y')

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

    def update(self, **kwargs):
        """Update object with kwargs data."""
        for key, value in kwargs.items():
            setattr(self, key, value)


class ALWUserContact(mysql.BaseModel):
    """ALWUserContact Model."""

    __tablename__ = 'contact'

    contact_id = Column(Integer, name='contact_id', primary_key=True, nullable=False)
    first_name = Column(String, name='contact_first_name')
    last_name = Column(String, name='contact_last_name')
    email = Column(String, name='contact_email')
    requested_login_email = Column(String)

    def update(self, **kwargs):
        """Update object with kwargs data."""
        for key, value in kwargs.items():
            setattr(self, key, value)

    def to_dict(self):
        """Get a dict representation of ALWUserContact."""
        return {
            'first_name': self.first_name,
            'last_name': self.last_name,
            'email': self.email,
            'requested_login_email': self.requested_login_email,
        }


class Vendor(mysql.BaseModel):
    """Vendor Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor'

    vendor_id = Column(Integer, name='vendor_id', primary_key=True, nullable=False)
    company = Column(String, name='company')
    is_distributor = Column(String, name='is_distributor', nullable=False)
    label_identifier = Column(
        Enum('Frontline', 'Client Services', 'Catalog', 'D3', 'Film', 'TV', 'Test'),
        name='label_identifier',
    )
    language = Column(String, name='language', nullable=False)
    name = Column(String, name='name', nullable=False)
    number_format = Column(String, name='number_format', nullable=False)
    owner = Column(String, name='owner', nullable=False)
    support_contact_email = Column(String, name='support_contact_email', nullable=False)
    wel_email_sender = Column(Integer, name='wel_email_sender')
    status = Column(
        Enum(
            'pitched',
            'pending',
            'verbal',
            'signed',
            'passed',
            'inactive',
            'deletion',
            'waiting_for_approval',
            'approved',
        ),
        default='signed',
    )
    assigned_to = Column(Integer, ForeignKey('orchadmin_users.id'), name='assigned_to')
    migrated_to_abacus = Column(Boolean, name='migrated_to_abacus')

    def to_dict(self):
        """Get a dict representation."""
        return {
            'vendor_id': self.vendor_id,
            'company': self.company,
            'is_distributor': self.is_distributor,
            'label_identifier': self.label_identifier,
            'language': self.language,
            'number_format': self.number_format,
            'name': self.name,
            'owner': self.owner,
            'assigned_to': self.assigned_to,
        }


class VendContact(mysql.BaseModel):
    """VendContact Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vend_contact'
    __table_args__ = {'extend_existing': True}

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    vendor_id = Column(Integer, ForeignKey('vendor.vendor_id'), name='vendor_id')
    contact_id = Column(Integer, ForeignKey('contact.contact_id'), name='contact_id')
    login = Column(String, name='login')
    subaccount_id = Column(Integer, ForeignKey('subaccount.subaccount_id'))
    active = Column(Enum('Y', 'N'), name='active', nullable=False, default='Y')
    language = Column(
        Enum('en', 'es', 'fr', 'de', 'tr', 'it', 'ru', 'pt', 'ja', 'zh-TW', 'zh-CN', 'ko'),
        default='en',
    )
    number_format = Column(Enum('us', 'europe'), default='us')
    master = Column(Enum('Y', 'N'), nullable=False, default='Y')

    contact = relationship('Contact', uselist=False)
    subaccount = relationship('Subaccount', uselist=False)
    vendor = relationship('Vendor', uselist=False)
    vend_contact_roles = relationship('VendContactRoles')

    def to_dict(self):
        """Get a dict representation."""
        return {
            'id': self._id,
            'vendor': self.vendor.to_dict(),
            'contact': self.contact.to_dict(),
            'login': self.login,
            'language': self.language,
            'number_format': self.number_format,
            'subaccount': self.subaccount.to_dict() if self.subaccount is not None else None,  # noqa
            'vend_contact_roles': list(map(lambda x: x.to_dict(), self.vend_contact_roles)),
        }


class Contact(mysql.BaseModel):
    """Contact Model for Workstation PHP $_SESSION."""

    __tablename__ = 'contact'
    __table_args__ = {'extend_existing': True}

    contact_id = Column(Integer, name='contact_id', primary_key=True, nullable=False)
    contact_email = Column(String, name='contact_email')

    def to_dict(self):
        """Get a dict representation."""
        return {'contact_id': self.contact_id, 'contact_email': self.contact_email}


class VendContactRoles(mysql.BaseModel):
    """VendContactRoles Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vend_contact_roles'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    vend_contact_id = Column(
        Integer, ForeignKey('vend_contact.id'), name='vend_contact_id', nullable=False
    )
    role_id = Column(Integer, ForeignKey('vendor_roles.id'), name='role_id')

    role = relationship('VendorRoles', uselist=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'id': self._id,
            'vend_contact_id': self.vend_contact_id,
            'role': self.role.to_dict(),
        }


class VendorRoles(mysql.BaseModel):
    """VendorRoles Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor_roles'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    role = Column(String, name='role', nullable=False)

    vendor_role_permissions = relationship('VendorRolePermissions')

    def to_dict(self):
        """Get a dict representation."""
        return {
            'id': self._id,
            'role': self.role,
            'vendor_role_permissions': list(
                map(lambda x: x.to_dict(), self.vendor_role_permissions)
            ),
        }


class VendorRolePermissions(mysql.BaseModel):
    """VendorRolePermissions Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor_role_permissions'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    role_id = Column(Integer, ForeignKey('vendor_roles.id'), name='role_id', nullable=False)
    permission_id = Column(
        Integer, ForeignKey('vendor_permissions.id'), name='permission_id', nullable=False
    )
    allow = Column(String, name='allow', nullable=False)

    permission = relationship('VendorPermissions', uselist=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'id': self._id,
            'role_id': self.role_id,
            'permission': self.permission.to_dict(),
            'allow': self.allow,
        }


class VendorPermissions(mysql.BaseModel):
    """VendorPermissions Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor_permissions'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    resource_id = Column(
        Integer, ForeignKey('vendor_resource.id'), name='resource_id', nullable=False
    )
    privilege_id = Column(Integer, ForeignKey('vendor_privilege.id'), name='privilege_id')

    privilege = relationship('VendorPrivilege', uselist=False)
    resource = relationship('VendorResource', uselist=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'id': self._id,
            'resource': self.resource.to_dict(),
            'privilege': self.privilege.to_dict() if self.privilege is not None else None,  # noqa
        }


class VendorPrivilege(mysql.BaseModel):
    """VendorPrivilege Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor_privilege'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    privilege = Column(String, name='privilege', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {'id': self._id, 'privilege': self.privilege}


class VendorResource(mysql.BaseModel):
    """VendorResource Model for Workstation PHP $_SESSION."""

    __tablename__ = 'vendor_resource'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    resource = Column(String, name='resource', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {'id': self._id, 'resource': self.resource}


class Subaccount(mysql.BaseModel):
    """Subaccount Model for Workstation PHP $_SESSION."""

    __tablename__ = 'subaccount'

    subaccount_id = Column(Integer, name='subaccount_id', primary_key=True, nullable=False)
    subaccount_name = Column(String, name='subaccount_name', nullable=False)
    date_deleted = Column(DATETIME, name='date_deleted')

    def to_dict(self):
        """Get a dict representation."""
        return {'subaccount_id': self.subaccount_id, 'subaccount_name': self.subaccount_name}


class Currency(mysql.BaseModel):
    """Currency Model."""

    __tablename__ = 'currencies'

    currency_id = Column(Integer, name='id', primary_key=True, nullable=False)
    currency_iso_code = Column(String, name='ISO_4217_code', nullable=False)
    currency_name = Column(String, name='currency_name', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'currency_id': self.currency_id,
            'currency_iso_code': self.currency_iso_code,
            'currency_name': self.currency_name,
        }


class VendorContract(mysql.BaseModel):
    """VendorContract Model."""

    __tablename__ = 'vendor_contract'

    _id = Column(Integer, name='id', primary_key=True, nullable=False)
    vendor_id = Column(
        Integer, ForeignKey('vend_contact.vendor_id'), name='vendor_id', nullable=False
    )
    currency_id = Column(Integer, ForeignKey('currencies.id'), name='currency_id', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {'id': self._id, 'vendor_id': self.vendor_id, 'currency_id': self.currency_id}


class VendorIcon(mysql.BaseModel):
    """VendorIcon Model."""

    __tablename__ = 'vendor_icon'

    vendor_icon_id = Column(Integer, name='vendor_icon_id', primary_key=True, nullable=False)
    vendor_id = Column(
        Integer, ForeignKey('vend_contact.vendor_id'), name='vendor_id', nullable=False
    )
    image_asset_id = Column(Integer, name='image_asset_id', nullable=True)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'vendor_icon_id': self.vendor_icon_id,
            'vendor_id': self.vendor_id,
            'image_asset_id': self.image_asset_id,
        }


class Feature(mysql.BaseModel):
    """Feature Model."""

    __tablename__ = 'features'

    feature_id = Column(Integer, name='feature_id', primary_key=True, nullable=False)
    feature_name = Column(String, name='feature_name', nullable=False)
    is_default = Column(Integer, name='is_default', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'feature_id': self.feature_id,
            'feature_name': self.feature_name,
            'is_default': self.is_default,
        }


class VendorRestrictedFeature(mysql.BaseModel):
    """VendorRestrictedFeature Model."""

    __tablename__ = 'vendor_restricted_features'

    vendor_restricted_features_id = Column(
        Integer, name='vendor_restricted_features_id', primary_key=True, nullable=False
    )
    vendor_id = Column(Integer, name='vendor_id', nullable=False)
    feature_id = Column(Integer, name='feature_id', nullable=False)
    preview = Column(Integer, name='preview', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {
            'vendor_restricted_features_id': self.vendor_restricted_features_id,
            'vendor_id': self.vendor_id,
            'feature_id': self.feature_id,
            'preview': self.preview,
        }


class Travelex(mysql.BaseModel):
    """Travelex Model."""

    __tablename__ = 'travelex'

    vendor_id = Column(Integer, name='vendor_id', primary_key=True, nullable=False)
    enrollment_id = Column(String, name='enrollment_id', nullable=False)

    def to_dict(self):
        """Get a dict representation."""
        return {'vendor_id': self.vendor_id, 'enrollment_id': self.enrollment_id}


@mysql.wrap_db_errors
def fetch_oa_users(user_ids=None):
    """Fetch Orchard Admin users records.

    @todo deprecate this fn.

    Args:
        user_ids: (list): A list of integer user_ids.

    Returns:
        list: A list of OA user records as dicts.

    """
    with mysql.db_read_session() as session:
        query = session.query(OAUser)
        if user_ids is not None:
            query = query.filter(OAUser.user_id.in_(user_ids))
        return response.Response(message=[result.to_dict() for result in query.all()])


def get_orchadmin_users(user_ids: list[str]) -> list[dict[str, str | bool]]:
    """
    Fetch OrchAdmin user records from the database for the given user IDs.

    Args:
        user_ids (list[str]): List of user IDs to query.

    Returns:
        list[dict[str, str | bool]]: A list of user records as dicts.
    """
    with mysql.db_read_session() as session:
        select_entities = [
            OAUser.user_id,
            OAUser.first_name,
            OAUser.last_name,
            OAUser.active,
        ]
        query = session.query(*select_entities).filter(OAUser.user_id.in_(user_ids))
        users = query.all()
        users_data = [
            {
                'user_id': str(user.user_id),
                'first_name': str(user.first_name),
                'last_name': str(user.last_name),
                'active': True if user.active == 'Y' else False,
            }
            for user in users
        ]
        return users_data


@mysql.wrap_db_errors
def fetch_alw_users(user_ids=None):
    """Fetch Workstation (ALW) user records.

    @todo deprecate this fn.

    Args:
        user_ids: (list): A list of integer user_ids.

    Returns:
        list: A list of ALW user records as dicts.

    """
    with mysql.db_read_session() as session:
        if not user_ids:
            query = (
                session.query(
                    ALWUserVendContact,
                    ALWUserContact,
                    Vendor.company,
                    VendorContract,
                    Currency.currency_iso_code,
                    VendContactRoles.role_id,
                )
                .outerjoin(ALWUserContact)
                .outerjoin(Vendor)
                .outerjoin(VendorContract)
                .outerjoin(Currency)
                .outerjoin(VendContactRoles)
            )
        else:
            query = (
                session.query(
                    ALWUserVendContact,
                    ALWUserContact,
                    Vendor.company,
                    VendorContract,
                    Currency.currency_iso_code,
                    func.group_concat(VendContactRoles.role_id.distinct()).label('role_ids'),
                )
                .outerjoin(ALWUserContact)
                .outerjoin(Vendor)
                .outerjoin(VendorContract)
                .outerjoin(Currency)
                .outerjoin(VendContactRoles)
            )

        if user_ids is not None:
            query = query.filter(ALWUserVendContact.user_id.in_(user_ids))
        users = []
        for result in query.all():
            user = result.ALWUserVendContact.to_dict()
            if result.ALWUserContact:
                user.update(result.ALWUserContact.to_dict())
            else:
                user.update({'first_name': None, 'last_name': None, 'email': None})

            if result.currency_iso_code:
                user.update({'currency': result.currency_iso_code})
            if result.company:
                user.update({'company': result.company})
            if hasattr(result, 'role_id') and result.role_id is not None:
                user.update({'role_id': result.role_id})
            if hasattr(result, 'role_ids') and result.role_ids is not None:
                user['role_ids'] = [int(role_id) for role_id in result.role_ids.split(',')]
            users.append(user)

        return response.Response(message=users)


@mysql.wrap_db_errors
def fetch_vendor_names(user_id=None, auth0_id=None):
    """Fetch Workstation (ALW) user records.

    Args:
        user_id: (str): vend contact id.
        auth0_id: (str): auth0 user id.

    Returns:
        list: A list vendor names corresponding to these users.

    """
    if not user_id and not auth0_id:
        return response.create_fatal_response(const.ERROR_MISSING_REQUIRED_ID)

    with mysql.db_read_session() as session:
        query = (
            session.query(ALWUserVendContact, Vendor)
            .join(Vendor)
            .filter(ALWUserVendContact.vendor_id == Vendor.vendor_id)
        )

        if user_id:
            query = query.filter(ALWUserVendContact.user_id == user_id)
        elif auth0_id:
            query = query.filter(ALWUserVendContact.auth0_user_id == auth0_id)

        label_names = []
        for result in query.all():
            label_name = result.Vendor.company if result.Vendor.company else result.Vendor.name
            label_names.append(label_name)
        return response.Response(message=label_names)


@mysql.wrap_db_errors
def fetch_users_by_auth0_id(auth0_id, user_id=None):
    """Fetch user records by auth0_id, excluding the current user.

    Args:
        user_id: (str): vend contact id.
        auth0_id: (str): auth0 user id.

    Returns:
        list: A list vendor names corresponding to these users.

    """
    with mysql.db_read_session() as session:
        query = session.query(ALWUserVendContact).filter(
            ALWUserVendContact.auth0_user_id == auth0_id
        )

        if user_id:
            query = query.filter(ALWUserVendContact.user_id != user_id)

        user_ids = []
        for result in query.all():
            user_ids.append(result.user_id)
        return response.Response(message=user_ids)


@mysql.wrap_db_errors
def fetch_alw_session_user_metatada(_id):
    """Fetch Workstation (ALW) $_SESSION info for a user.

    Args:
        _id: (int): vend_contact.id.

    Returns:
        dict: A dict with ALW session details.

    """
    with mysql.db_read_session() as session:
        query = session.query(VendContact).filter(VendContact._id == _id)
        result = query.one_or_none()
        if result is not None:
            return response.Response(message=result.to_dict())
        else:
            return response.create_not_found_response()


def alchemyencoder(obj):
    """JSON encoder function for SQLAlchemy special classes."""
    if isinstance(obj, datetime.date):
        return obj.isoformat()
    elif isinstance(obj, decimal.Decimal):
        return float(obj)


def get_alw_roles_for_user(_id):
    """Get workstation roles for user."""
    roles = dict(role_ids=[], role_names=[])
    sql = 'SELECT role_id, role FROM vend_contact_roles vcr \
            JOIN vendor_roles vr on vcr.role_id = vr.id \
            WHERE vend_contact_id = {}'.format(_id)
    with mysql.db_read_session() as session:
        roles_result = session.execute(sql)
        all_roles = roles_result.fetchall()
        if all_roles:
            for role in all_roles:
                roles['role_ids'].append(role[0])
                roles['role_names'].append(role[1])
        return response.Response(message=roles)


def get_oa_roles_for_user(_id):
    """Get oa roles for user."""
    roles = []
    sql = 'SELECT oaur.orchadmin_role_id, oar.orchadmin_role FROM orchadmin_user_roles oaur \
            JOIN orchadmin_roles oar on oaur.orchadmin_role_id = oar.orchadmin_role_id \
            WHERE orchadmin_user_id = {}'.format(_id)
    with mysql.db_read_session() as session:
        roles_result = session.execute(sql)
        all_roles = roles_result.fetchall()
        if all_roles:
            for role in all_roles:
                roles.append({'id': role[0], 'name': role[1]})
        return response.Response(message=roles)


@mysql.wrap_db_errors
def fetch_alw_session_user_metatada_raw(_id):
    """Fetch Workstation (ALW) $_SESSION info for a user with raw sql.

    Args:
        _id: (int): vend_contact.id.

    Returns:
        dict: A dict with ALW session details.

    """
    with mysql.db_read_session() as session:
        sql = (
            'SELECT vc.id AS vend_contact_id, vc.`subaccount_id`,\
            c.`contact_email` AS vend_contact_email, c.contact_id, vc.active,\
            vc.`login`, vc.`language`, vc.`number_format`, v.`name`,\
            v.`vendor_id`, v.`company`, v.`owner`, v.`is_distributor`,\
            v.`label_identifier`, v.`migrated_to_abacus`,\
            GROUP_CONCAT(distinct(vcr.`role_id`)) AS role_ids,\
            GROUP_CONCAT(distinct(rf.feature_name)) '
            'AS vendor_restricted_features,\
            s.`subaccount_name`, c.`contact_first_name`,\
            c.`contact_last_name`, oa.`email` AS assigned_to_email,\
            v.support_contact_email,\
            oa.`office_phone` AS assigned_to_phone,\
            vc.`auth0_user_id`, vi.`image_asset_id`,\
            tx.enrollment_id AS travelex_enrollment_id\
        FROM `vend_contact` vc\
        INNER JOIN `contact` c  ON vc.`contact_id` = c.`contact_id`\
        INNER JOIN `vendor` v  ON vc.`vendor_id` = v.`vendor_id`\
        INNER JOIN vend_contact_roles vcr ON vcr.vend_contact_id = vc.id\
        LEFT JOIN vendor_restricted_features vrf2 \
            ON vrf2.vendor_id = v.vendor_id\
        LEFT JOIN features rf ON rf.feature_id = vrf2.feature_id\
        LEFT JOIN `subaccount` s ON vc.`subaccount_id` = s.`subaccount_id`\
        LEFT JOIN `orchadmin_users` oa ON v.`assigned_to` = oa.`id`\
        LEFT JOIN vendor_icon vi ON vc.`vendor_id` = vi.`vendor_id`\
        LEFT JOIN travelex tx ON v.vendor_id = tx.vendor_id\
        WHERE vc.id = {userId}\
        GROUP BY vc.`id`\
        limit 1;'.format(userId=_id)
        )
        vend_response = session.execute(sql)
        result = vend_response.fetchall()
        if result:
            result_obj = json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            vend_contact_roles = fetch_vend_contact_roles_raw(
                result_obj[0]['vend_contact_id'], tuple(result_obj[0]['role_ids'].split(','))
            )
            raw_response = response.Response(
                message=_format_alw_session_user_metatada(result_obj[0], vend_contact_roles)
            )
            return raw_response
        else:
            return response.create_not_found_response()


@mysql.wrap_db_errors
def get_linked_account_details_by_profile_ids(vc_id, profile_ids):
    """Fetch linked accounts by a list of profile IDs (vend_contact.id).

    Resolves linked accounts via Neo4j Identity -> LabelProfile traversal
    instead of matching on auth0_user_id.

    Args:
        vc_id (int): The current user's vend_contact.id (excluded from results).
        profile_ids (list): List of vend_contact.id values from Neo4j LabelProfile.profileId.

    Returns:
        Response: A list of dict with account details.

    """
    if not profile_ids:
        return response.Response(message=[])

    with mysql.db_read_session() as session:
        vc_id_list = ', '.join(str(int(pid)) for pid in profile_ids)
        sql = 'SELECT \
            vc.id as vc_id, v.vendor_id, vc.subaccount_id, s.subaccount_name, \
            v.label_identifier, v.name as vendor_name, v.company, \
            vc.auth0_user_id, vc.auth0_primary \
        FROM vend_contact vc \
        INNER JOIN vendor v ON v.vendor_id = vc.vendor_id \
        LEFT JOIN subaccount s ON vc.subaccount_id = s.subaccount_id \
        WHERE \
            vc.id IN ({profile_ids}) \
            AND vc.id != {vc_id} \
            AND vc.active = "Y"'.format(profile_ids=vc_id_list, vc_id=int(vc_id))
        vend_response = session.execute(sql)
        result = vend_response.fetchall()
        if result:
            result_obj = json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            return response.Response(message=result_obj)
        else:
            return response.Response(message=[])


@mysql.wrap_db_errors
def get_all_accounts_with_auth0_id(auth0_id, include_deleted=False):
    """Fetch all accounts that has this auth0_user_id.

    Args:
        auth0_id (str): Auth0 id without auth0| prefix.
        include_deleted (bool): Flag to decide if we include deactivated users.

    Returns:
        dict: A list of dict with account details.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT \
            vc.id as vc_id, v.vendor_id, vc.subaccount_id, s.subaccount_name, \
            v.label_identifier, v.name as vendor_name, v.company, \
            vc.auth0_user_id, vc.auth0_primary, vc.active \
        FROM vend_contact vc \
        INNER JOIN vendor v ON v.vendor_id = vc.vendor_id \
        LEFT JOIN subaccount s ON vc.subaccount_id = s.subaccount_id \
        WHERE vc.auth0_user_id = "{user_id}"'.format(user_id=auth0_id)
        if not include_deleted:
            sql += ' AND vc.active = "Y"'

        vend_response = session.execute(sql)
        result = vend_response.fetchall()
        if result:
            result_obj = json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            return response.Response(message=result_obj)
        else:
            return response.Response(message=[])


def get_all_account_details_with_auth0_id(auth0_id, include_deleted=False):
    """Fetch all accounts that has this auth0_user_id.

    Args:
        auth0_id (str): Auth0 id without auth0| prefix.
        include_deleted (bool): Flag to decide if we include deactivated users.

    Returns:
        dict: A list of dict with account details.

    """
    with mysql.db_read_session() as session:
        sql = 'SELECT \
            vc.id as vc_id, v.vendor_id, vc.subaccount_id, s.subaccount_name, \
            v.label_identifier, v.name as vendor_name, v.company, \
            vc.auth0_user_id, vc.auth0_primary, vc.active, \
            oa.email AS assigned_to_email, v.support_contact_email\
        FROM vend_contact vc \
        INNER JOIN vendor v ON v.vendor_id = vc.vendor_id \
        LEFT JOIN subaccount s ON vc.subaccount_id = s.subaccount_id \
        LEFT JOIN orchadmin_users oa ON v.assigned_to = oa.id \
        WHERE vc.auth0_user_id = "{user_id}"'.format(user_id=auth0_id)
        if not include_deleted:
            sql += ' AND vc.active = "Y"'

        vend_response = session.execute(sql)
        result = vend_response.fetchall()
        if result:
            result_obj = json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            return response.Response(message=result_obj)
        else:
            return response.Response(message=[])


@mysql.wrap_db_errors
def fetch_vend_contact_roles_raw(vend_contact_id, role_ids):
    """Fetch expanded vend_contact role info for a user with raw sql.

    Args:
        vend_contact_id: (str): the vend_contact_id of the user.
        role_ids: (tuple): The roles given to the user

    Returns:
        dict: A dict with ALW session details.

    """
    with mysql.db_read_session() as session:
        sql = "SELECT vrp.* , vr.`resource`, vp.`privilege`,\
            vcr.`vend_contact_id`, vendor_roles.`role`,\
            vcr.`id` AS vend_contact_role_id, vr.`id` AS resource_id\
            FROM vend_contact_roles vcr\
            INNER JOIN vendor_roles ON vcr.`role_id` = vendor_roles.`id`\
            INNER JOIN vendor_role_permissions vrp\
            ON vcr.`role_id` = vrp.`role_id`\
            INNER JOIN vendor_permissions vpm\
            ON vrp.`permission_id` = vpm.`id`\
            INNER JOIN vendor_resource vr\
            ON vpm.`resource_id` = vr.`id`\
            LEFT JOIN vendor_privilege vp\
            ON vpm.`privilege_id` = vp.`id`\
            WHERE vcr.`vend_contact_id` = {} AND vrp.`allow` = 'Y';".format(vend_contact_id)
        vend_response = session.execute(sql)
        result = vend_response.fetchall()
        if result:
            result_obj = json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            formatted_results = _format_vend_contact_roles(result_obj, role_ids)
            return formatted_results
        else:
            return []


def _format_vend_contact_roles(data, role_ids):
    """Format the vend contact roles into a dict for frontend use.

    Args:
        data: (obj): vend_contact role data.
        role_ids: (tuple): The roles given to the user

    Returns:
        array: An array with vend contact roles.

    """
    vend_contact_roles = []
    for role_id in role_ids:
        vend_contact_roles.append(
            {
                'role': {
                    'id': role_id,
                    'vendor_role_permissions': [
                        _format_permission(x) for x in data if str(x['role_id']) == role_id
                    ],
                }
            }
        )
    # Use the first role permission's values to populate the other info
    for contact_role in vend_contact_roles:
        first_role = contact_role['role']['vendor_role_permissions'][0]
        contact_role['id'] = first_role['vend_contact_role_id']
        contact_role['vend_contact_id'] = first_role['vend_contact_id']
        contact_role['role']['role'] = first_role['role']
    return vend_contact_roles


def _format_alw_session_user_metatada(data, vend_contact_roles):
    """Format the raw metadata query into the expected format.

    Args:
        data: (obj): user metadata.
        vend_contact_roles: (list): Formatted vend contact roles

    Returns:
        dict: A dict with ALW session details.

    """
    subaccount = data['subaccount_id']
    if data['subaccount_id']:
        subaccount = {
            'subaccount_id': data['subaccount_id'],
            'subaccount_name': data['subaccount_name'],
        }

    if data['vendor_restricted_features']:
        vendor_restricted_features = data['vendor_restricted_features'].split(',')
    else:
        vendor_restricted_features = []

    return {
        'language': data['language'],
        'id': data['vend_contact_id'],
        'subaccount': subaccount,
        'contact': {
            'contact_email': data['vend_contact_email'],
            'contact_id': data['contact_id'],
            'contact_first_name': data['contact_first_name'],
            'contact_last_name': data['contact_last_name'],
        },
        'number_format': data['number_format'],
        'vend_contact_roles': vend_contact_roles,
        'vendor': {
            'language': data['language'],
            'number_format': data['number_format'],
            'name': data['name'],
            'vendor_id': data['vendor_id'],
            'company': data['company'],
            'owner': data['owner'],
            'is_distributor': data['is_distributor'],
            'label_identifier': data['label_identifier'],
            'assigned_to_email': data['assigned_to_email'],
            'support_contact_email': data['support_contact_email'],
            'assigned_to_phone': data['assigned_to_phone'],
            'image_asset_id': data['image_asset_id'],
            'vendor_restricted_features': vendor_restricted_features,
            'travelex_enrollment_id': data['travelex_enrollment_id'],
            'migrated_to_abacus': data['migrated_to_abacus'],
        },
        'login': data['login'],
        'auth0_user_id': data['auth0_user_id'],
        'active': data['active'],
    }


def _format_permission(data):
    """Format the vendor_role_permission for frontend use.

    Args:
        data: (obj): vendor_role_permissions data.

    Returns:
        dict: A formatted dict with vendor_role_permissions.

    """
    return {
        'role_id': data['role_id'],
        'role': data['role'],
        'id': data['id'],
        'allow': data['allow'],
        'vend_contact_role_id': data['vend_contact_role_id'],
        'vend_contact_id': data['vend_contact_id'],
        'permission': {
            'resource': {'resource': data['resource'], 'id': data['resource_id']},
            'id': data['permission_id'],
            'privilege': data['privilege'],
        },
    }


@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:
        query = (
            session.query(ALWUserVendContact, ALWUserContact)
            .join(ALWUserContact)
            .filter(ALWUserVendContact.login == login_name)
            .filter(ALWUserVendContact.password_hash == password_hash)
            .filter(ALWUserVendContact.auth0_user_id.is_(None))
        )

        result = query.one_or_none()
        if not result:
            return response.create_not_found_response()

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


@mysql.wrap_db_errors
def update_vend_contact_details(user_id, data):
    """Update vend contact data.

    Args:
        user_id: (str): vend_contact.id.
        data: (dict): updated values.

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_session() as session:
        query = session.query(ALWUserVendContact).filter(ALWUserVendContact.user_id == user_id)
        user_obj = query.one_or_none()

        if not user_obj:
            return response.create_not_found_response()

        if data.keys():
            user_obj.update(**data)
        session.commit()
        return response.Response(message=user_obj.to_dict())


def update_vend_contacts(old_auth0_user_id, new_auth0_user_id):
    """Update all vend contact with auth0_user_id.

    Args:
        old_auth0_user_id: (str): old auth0_id.
        new_auth0_user_id: (str): new auth0_id.

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_session() as session:
        row_count = (
            session.query(ALWUserVendContact)
            .filter(ALWUserVendContact.auth0_user_id == old_auth0_user_id)
            .update(
                dict(
                    auth0_user_id=new_auth0_user_id,
                )
            )
        )

        return response.Response(message='{} rows updated.'.format(row_count))


@mysql.wrap_db_errors
def update_contact_details(contact_id, data):
    """Update contact data for contact_id.

    Args:
        contact_id: (str): contact_id.
        data: (dict): updated values.

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_session() as session:
        query = session.query(ALWUserContact).filter(ALWUserContact.contact_id == contact_id)
        user_obj = query.one_or_none()
        if not user_obj:
            return response.create_not_found_response()

        user_obj.update(**data)
        session.commit()
        return response.Response(message=user_obj.to_dict())


@mysql.wrap_db_errors
def reset_users_auth0_details(auth0_id):
    """Reset Vend contact's auth0_id and auth0_migration date.

    Args:
        auth0_id (str): auth0 user id.

    Returns:
        flask.Response: containing list of label names.

    """
    with mysql.db_session() as session:
        row_count = (
            session.query(ALWUserVendContact)
            .filter(ALWUserVendContact.auth0_user_id == auth0_id)
            .update({'auth0_user_id': None, 'auth0_migration_date': None, 'auth0_primary': None})
        )

        return response.Response(message='{} rows updated.'.format(row_count))


@mysql.wrap_db_errors
def fetch_vend_contact_for_user(user_id):
    """Fetch vend contact data for a user.

    Args:
        user_id: (str): vend_contact.id.

    Returns:
        dict: A dict with user details.

    """
    with mysql.db_read_session() as session:
        query = session.query(ALWUserVendContact).filter(ALWUserVendContact.user_id == user_id)
        user_obj = query.one_or_none()

        if not user_obj:
            return response.create_not_found_response()

        return response.Response(message=user_obj.to_dict())


@mysql.wrap_db_errors
def fetch_vend_contact_and_contact(user_id):
    """Fetch vend contact data for a user.

    Args:
        user_id: (str): vend_contact.id.

    Returns:
        dict: A dict with user details.

    """
    with mysql.db_read_session() as session:
        query = (
            session.query(ALWUserVendContact, ALWUserContact)
            .join(ALWUserContact)
            .filter(ALWUserVendContact.user_id == user_id)
        )
        user_obj = query.one_or_none()

        if not user_obj:
            return response.create_not_found_response()

        user = user_obj.ALWUserVendContact.to_dict()
        if user_obj.ALWUserContact:
            user.update(user_obj.ALWUserContact.to_dict())
        return response.Response(message=user)


@mysql.wrap_db_errors
def get_active_vend_contacts_by_ids(profile_ids):
    """Fetch active vend_contact records by their IDs.

    Used to resolve primary vend_contact from LabelProfile profileIds
    (profileId in Neo4j = vend_contact.id in MySQL).

    Args:
        profile_ids (list): List of vend_contact.id values.

    Returns:
        list: List of dicts with vend_contact_id, vendor_id, auth0_primary.

    """
    if not profile_ids:
        return []

    with mysql.db_read_session() as session:
        results = (
            session.query(ALWUserVendContact)
            .filter(ALWUserVendContact.user_id.in_(profile_ids))
            .filter(ALWUserVendContact.active == 'Y')
            .all()
        )
        return [
            {
                'vend_contact_id': r.user_id,
                'vendor_id': r.vendor_id,
                'auth0_primary': r.auth0_primary,
            }
            for r in results
        ]


@mysql.wrap_db_errors
def fetch_primary_for_auth0_user(auth0_user_id):
    """Fetch primary vend contact data for an auth0 user.

    Args:
        auth0_id: (str): the auth0 id.

    Returns:
        dict: A dict with user details.

    """
    with mysql.db_read_session() as session:
        query = (
            session.query(ALWUserVendContact)
            .filter(ALWUserVendContact.auth0_user_id == auth0_user_id)
            .filter(ALWUserVendContact.auth0_primary == 'Y')
        )
        user_obj = query.one_or_none()

        if not user_obj:
            return response.create_not_found_response()

        return response.Response(message=user_obj.to_dict())


@mysql.wrap_db_errors
def reset_user_auth0_details(user_id):
    """Reset a vend_contact's auth0 information.

    Args:
        user_id: (str): vend_contact.id.

    Returns:
        flask.Response: containing vend contact info.

    """
    with mysql.db_session() as session:
        query = session.query(ALWUserVendContact).filter(ALWUserVendContact.user_id == user_id)
        user_obj = query.one_or_none()

        if not user_obj:
            return response.create_not_found_response()

        data = {'auth0_user_id': None, 'auth0_migration_date': None, 'auth0_primary': None}

        user_obj.update(**data)
        session.commit()

        return response.Response(message=user_obj.to_dict())


@mysql.wrap_db_errors
def fetch_user_basic_info(user_id=None, contact_id=None, subaccount_id=None):
    """Fetch user basic info.

    Args:
        user_id (int): vend_contact.id
        contact_id (int): vend_contact.contact_id
        subaccount_id (int): Unique id of subaccount
    Returns:
        dict: A list of dict with user details.
    """
    with mysql.db_read_session() as session:
        user = ''
        contact = ''
        subaccount = ''
        if user_id:
            user = ' AND vc.id = {user_id} '.format(user_id=user_id)
        if contact_id:
            contact = ' AND vc.contact_id = {contact_id} '.format(contact_id=contact_id)
        if subaccount_id:
            subaccount = ' AND vc.subaccount_id = {subaccount_id}'.format(
                subaccount_id=subaccount_id
            )
        sql = document.USER_INFO_SQL.format(user=user, contact=contact, subaccount=subaccount)
        result = session.execute(sql).fetchall()
        if result:
            return response.Response(
                message=json.loads(json.dumps([dict(r) for r in result], default=alchemyencoder))
            )
        return response.create_not_found_response()


@mysql.wrap_db_errors
def get_contact_details(
    account_type, account_id, user_id=None, status=None, page_offset=None, page_limit=None
):
    """Fetch contact list for this account_id.

    Args:
        account_type (str): Type of account.
        account_id (int): account id associated with the contact
        user_id (int): identifier of vend_contact table
        status (string): status of user
        page_offset (int): beginning of the list
        page_limit (int): Max length of the contact list

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_read_session() as session:
        query = session.query(
            VendContact._id.label('user_id'),
            ALWUserContact.first_name.label('contact_first_name'),
            ALWUserContact.last_name.label('contact_last_name'),
            ALWUserContact.email.label('contact_email'),
            Subaccount.subaccount_name.label('subaccount'),
            func.group_concat(VendorRoles.role.distinct()).label('permissions'),
            case({'Y': 1, 'N': 0}, value=VendContact.active).label('is_active'),
            Subaccount.date_deleted,
        )
        if account_type == 'vendor':
            query = query.filter(VendContact.vendor_id == account_id)
        else:
            query = query.filter(VendContact.subaccount_id == account_id)
        is_active = 'Y'
        if status == 'disable':
            is_active = 'N'
        query = query.filter(VendContact.active == is_active)
        query = query.filter(VendContact._id != user_id)
        query = (
            query.join(ALWUserContact)
            .outerjoin(VendContactRoles)
            .outerjoin(VendorRoles)
            .outerjoin(Subaccount)
            .group_by(VendContact._id)
            .order_by(ALWUserContact.first_name, VendContact._id)
        )
        total_records = query.all()
        result = query.limit(page_limit).offset(page_offset).all()
    if not result:
        return response.create_not_found_response()
    users = {
        'items': json.loads(json.dumps([r._asdict() for r in result], default=alchemyencoder)),
        'pagination': dict(
            type='standard',
            count=len(total_records),
            page_offset=page_offset,
            page_limit=page_limit,
        ),
    }
    return response.Response(message=users)


def get_primary_contact(account_type, account_id, active=None):
    """Fetch primary contact for this account_id.

    Args:
        account_type (str): Type of account.
        account_id (int): account id associated with the contact
        active (Optional[str]): the "active" status of the contact

    Returns:
        dict: A dict with limited user details.

    """
    with mysql.db_read_session() as session:
        query = session.query(ALWUserVendContact, ALWUserContact).join(ALWUserContact)
        if account_type == 'vendor':
            query = query.filter(ALWUserVendContact.vendor_id == account_id)
        else:
            query = query.filter(ALWUserVendContact.subaccount_id == account_id)
        query = query.filter(ALWUserVendContact.master == 'Y')
        if active is not None:
            query = query.filter(ALWUserVendContact.active == active)

        user_obj = query.one_or_none()
        if not user_obj:
            return response.create_not_found_response(message='User not found.')

        user = user_obj.ALWUserVendContact.to_dict()
        if user_obj.ALWUserContact:
            user.update(user_obj.ALWUserContact.to_dict())

    return response.Response(message=user)


@mysql.wrap_db_errors
def get_product_managers():
    """Fetch product managers."""
    with mysql.db_session() as session:
        query = (
            session.query(OAUser)
            .filter(OAUser.active == 'Y')
            .filter(OAUser.is_product_manager.is_(True))
            .order_by(OAUser.first_name)
        )

        result = query.all()
        product_managers = []
        for row in result:
            product_manager = {
                'type': 'oa',
                'user_id': row.user_id,
                'full_name': row.first_name + ' ' + row.last_name,
                'email': row.email,
                'active': row.active,
            }
            product_managers.append(product_manager)
        return response.Response(message={'items': product_managers})


def get_account_managers():
    """Get list of account managers for PM/ALR assignments from orchadmin_users table.

    Returns:
        response.Response: containing a list of users.

    """
    with mysql.db_session() as session:
        result = (
            session.query(OAUser.user_id, OAUser.first_name, OAUser.last_name)
            .distinct()
            .join(OAUserRoles, OAUser.user_id == OAUserRoles.orchadmin_user_id)
            .filter(OAUser.active == 'Y')
            .filter(OAUserRoles.orchadmin_role_id.in_((3, 6, 10)))
            .order_by(OAUser.first_name, OAUser.last_name)
            .all()
        )

        if result:
            return response.Response([r._asdict() for r in result])

    return response.create_not_found_response()


@mysql.wrap_db_errors
def get_active_closers():
    """Fetch active closers."""
    with mysql.db_session() as session:
        result = (
            session.query(OAUser.user_id, OAUser.first_name, OAUser.last_name)
            .filter(OAUser.active == 'Y')
            .filter(OAUser.closer == 'Y')
            .order_by(OAUser.first_name, OAUser.last_name)
            .all()
        )

        closers = [r._asdict() for r in result]
        return response.Response(message={'items': closers})