"""Model class for label entity."""

from owsresponse import response
from sqlalchemy import text

from permissions.connectors import mysql, neo4j
from permissions.constants import constants
from permissions.exceptions.incomplete_result_error import IncompleteResultError
from permissions.models import vend_contact
from permissions.utils import db_entities


def _db_row_to_resource(row):
    """Convert a row from the database to a vendor/subaccount resource."""
    if row[1] is None:
        return {'type': constants.VENDOR_RESOURCE_TYPE, 'id': row[0]}

    return {'type': constants.SUBACCOUNT_RESOURCE_TYPE, 'id': row[1], 'vendor_id': row[0]}


def get_label_resources(profile_type, profile_id):
    """Return label resources associated with a profile.

    If a LabelProfile is vendor level (ex a D3 or Label), those vendors
    will be returned.

    If a LabelProfile is subaccount level, those subaccounts will be returned.
    """
    if profile_type == constants.LABELPROFILE:
        with mysql.db_read_session() as session:
            sql = 'SELECT vendor_id, subaccount_id ' 'FROM vend_contact ' 'WHERE id = :userId'

            query_result = session.execute(sql, {'userId': profile_id})
            result = query_result.fetchall()
            return response.Response(list(map(_db_row_to_resource, list(result))))

    return response.Response([])


def get_migrated_to_abacus(vendor_id):
    """Return label resource associated with vendor id."""
    with mysql.db_read_session() as session:
        sql = 'SELECT migrated_to_abacus ' 'FROM vendor ' 'WHERE vendor_id = :vendor_id'

        query_result = session.execute(sql, {'vendor_id': vendor_id})
        result = query_result.fetchone()
        if not result:
            return result
        result = dict(result)
        return result['migrated_to_abacus']


def get_labels_for_artist_id(resource_type, artist_id):
    """GET label information for this artist id.

    Args:
        resource_type (str): ArtistInfo or LabelParticipant is supported.
        artist_id (int): ArtistInfo or LabelParticipant node id.

    Returns:
        Response: a dict of label information.
    """
    with neo4j.db_session(access_mode=constants.NEO4j_READ_ACCESS) as session:
        query = f"""MATCH (l:Label)-[]->(r:{resource_type})
        WHERE
            r.id = $artist_id
        RETURN
            l as label"""
        result = session.run(query, artist_id=artist_id)
        data = result.single()
        if not data or not data.get('label'):
            return response.create_not_found_response('Label not found.')
        return response.Response(db_entities.node_to_dict(data.get('label')))


def get_vend_contact_user(session, auth0_id, vendor_id, subaccount_id=None):
    """GET existing vend_contact user with roles if exist.

    Args:
        session (session): MySQL sqlalchamy object.
        auth0_id (str): Auth0 id not uuid.
        vendor_id (int): vendor id.
        subaccount_id (int): subaccount id if exist.

    Returns:
        Response: a dict of vend_contact information.
    """
    sql = text(f"""SELECT vc.id, vc.active, GROUP_CONCAT(vcr.role_id) as all_roles
        FROM vend_contact vc
        LEFT JOIN vend_contact_roles vcr ON vcr.vend_contact_id = vc.id
        WHERE vc.auth0_user_id= :auth0_id
        AND vc.vendor_id= :vendor_id
        {'AND vc.subaccount_id= :subaccount_id' if subaccount_id else 'AND vc.subaccount_id IS NULL'}
        GROUP BY vc.id""")  # noqa for max-len

    query_result = session.execute(
        sql, {'auth0_id': auth0_id, 'vendor_id': vendor_id, 'subaccount_id': subaccount_id}
    )
    result = query_result.fetchone()
    if not result:
        return result
    result = dict(result)  # convert from RowProxy obj to dict.
    result['all_roles'] = (
        set(map(int, result['all_roles'].split(','))) if result['all_roles'] else []
    )
    return result


def get_vend_contact_with_auth0_user_id(session, auth0_id, vendor_id, subaccount_id=None):
    """GET existing vend_contact user with roles if exist.

    Args:
        session (session): MySQL sqlalchamy object.
        auth0_id (str): Auth0 id not uuid.
        vendor_id (int): vendor id.
        subaccount_id (int): subaccount id if exist.
    Returns:
        Response: a dict of vend_contact information.
    """
    sql = text(f"""SELECT vc.id, vc.active, vc.vendor_id, vc.subaccount_id, GROUP_CONCAT(vcr.role_id) as all_roles
        FROM vend_contact vc
        LEFT JOIN vend_contact_roles vcr ON vcr.vend_contact_id = vc.id
        WHERE vc.auth0_user_id= :auth0_id
        GROUP BY vc.id""")  # noqa for max-len

    query_result = session.execute(sql, {'auth0_id': auth0_id})
    rows = query_result.fetchall()
    # set auth0_primary when user does not have existing access to an account.
    auth0_primary = None if rows else 'Y'
    vend_contact_user = [
        row
        for row in rows
        if (row['vendor_id'] == vendor_id and row['subaccount_id'] == subaccount_id)
    ]
    if not vend_contact_user:
        return vend_contact_user, auth0_primary
    result = dict(vend_contact_user[0])  # convert from RowProxy obj to dict.
    result['all_roles'] = (
        set(map(int, result['all_roles'].split(','))) if result['all_roles'] else []
    )
    return result, auth0_primary


def create_full_vend_contact_user(
    session,
    auth0_id,
    last_name,
    first_name,
    email,
    role_ids,
    vendor_id,
    auth0_primary,
    master_contact,
    subaccount_id=None,
):
    """Create vend_contact, contact and vend_contact_roles.

    Args:
        session (session): MySQL sqlalchamy object.
        auth0_id (str): Auth0 id not uuid.
        name (str): User name.
        email (str): user email.
        role_ids (set): new role ids.
        vendor_id (int): vendor id.
        subaccount_id (int): subaccount id if exist.
        auth0_primary (enum): 'Y' if vend_contact is a master contact
            from gda-account-creation step function/auth0_primary account
            otherwise None.
        master_contact (bool): is master_contact or not.
    """
    if master_contact:
        mc = 'Y'
    else:
        mc = 'N'
    insert_sql = text("""INSERT INTO contact (contact_last_name, contact_first_name, contact_email )
        VALUES (:last_name, :first_name, :email);
        INSERT INTO vend_contact
        (contact_id, master, login, auth0_user_id, auth0_migration_date, auth0_primary,
        vendor_id, subaccount_id)
        VALUES
        (LAST_INSERT_ID(), :master_contact, :login, :auth0_id, NOW(), :auth0_primary, :vendor_id, :subaccount_id);
        INSERT INTO vend_contact_roles (vend_contact_id, role_id)
        SELECT LAST_INSERT_ID(), id FROM vendor_roles WHERE id IN :role_ids;""")  # noqa for max-len

    login = vend_contact.generate_vend_contact_login(
        email=email,
        vendor_id=vendor_id,
        subaccount_id=subaccount_id,
    )

    session.execute(
        insert_sql,
        {
            'last_name': last_name,
            'first_name': first_name,
            'email': email,
            'login': login,
            'auth0_id': auth0_id,
            'vendor_id': vendor_id,
            'subaccount_id': subaccount_id,
            'auth0_primary': auth0_primary,
            'role_ids': role_ids,
            'master_contact': mc,
        },
    )
    # get the newly created vend_contact id back.
    sql = text(f"""SELECT vc.id, vc.active FROM vend_contact vc
            WHERE vc.auth0_user_id= :auth0_id AND vc.vendor_id= :vendor_id
            {'AND vc.subaccount_id= :subaccount_id' if subaccount_id else 'AND vc.subaccount_id IS NULL'}
            GROUP BY vc.id""")  # noqa for max-len

    result = session.execute(
        sql, {'auth0_id': auth0_id, 'vendor_id': vendor_id, 'subaccount_id': subaccount_id}
    ).fetchone()
    if not result:
        raise IncompleteResultError(
            f'Failed to create vend_contact for user: {email} vendor:{vendor_id} '
            f'subaccount:{subaccount_id}'
        )
    return result


def update_vend_contact_roles(session, vend_contact_id, role_ids):
    """Replace vend_contact roles.

    Args:
        session (session): MySQL sqlalchamy object.
        vend_contact_id (int): vend_contact table id.
        role_ids (set): new role ids.
    """
    contact = text("""DELETE FROM vend_contact_roles WHERE vend_contact_id = :vend_contact_id;
        INSERT INTO vend_contact_roles (vend_contact_id, role_id)
        SELECT :vend_contact_id, id FROM vendor_roles WHERE id IN :role_ids;""")

    result = session.execute(contact, {'vend_contact_id': vend_contact_id, 'role_ids': role_ids})
    return result


def add_remove_vend_contact_roles(session, vend_contact_id, role_ids_to_add, role_ids_to_remove):
    """Replace vend_contact roles.

    Args:
        session (session): MySQL sqlalchamy object.
        vend_contact_id (int): vend_contact table id.
        role_ids_to_add (set): role ids to add.
        role_ids_to_remove (set): role ids to remove.
    """
    if role_ids_to_remove and len(role_ids_to_remove) > 0:
        remove_query = (
            """DELETE FROM vend_contact_roles WHERE vend_contact_id = :vend_contact_id;"""
            if 0 in role_ids_to_remove
            else """DELETE FROM vend_contact_roles
                WHERE vend_contact_id = :vend_contact_id AND role_id IN :role_ids_to_remove;"""
        )
        session.execute(
            remove_query,
            {'vend_contact_id': vend_contact_id, 'role_ids_to_remove': role_ids_to_remove},
        )
    if role_ids_to_add and len(role_ids_to_add) > 0:
        add_query = """INSERT INTO vend_contact_roles (vend_contact_id, role_id)
            SELECT :vend_contact_id, id FROM vendor_roles WHERE id IN :role_ids_to_add;"""
        session.execute(
            add_query, {'vend_contact_id': vend_contact_id, 'role_ids_to_add': role_ids_to_add}
        )


def update_vend_contact(session, vend_contact_id, active):
    """Update vend_contact table.

    Args:
        session (session): MySQL sqlalchamy object.
        vend_contact_id (int): vend_contact table id.
        active (str): Y or N value.
    """
    contact = text("""UPDATE vend_contact SET active = :active WHERE id = :vend_contact_id;""")
    result = session.execute(contact, {'vend_contact_id': vend_contact_id, 'active': active})
    return result


def deactivate_other_vend_contacts(session, auth0_id, required_vc_ids):
    """Replace vend_contact roles.

    Args:
        session (session): MySQL sqlalchamy object.
        auth0_id (str): auth0 id.
        required_vc_ids (list): vend_contact ids that should not be deactivated.
    """
    sql = text(f"""
    UPDATE vend_contact vc SET vc.active = "N"
    WHERE vc.auth0_user_id= :auth0_id
    {'AND vc.id NOT IN :required_vc_ids' if required_vc_ids else ''}
    """)

    result = session.execute(sql, {'auth0_id': auth0_id, 'required_vc_ids': required_vc_ids})
    return result


def update_auth0_primary(new_label_profile):
    """Update auth0_primary in vend_contact table.

    Args:
        new_label_profile (dict): list of new label_profile to update.
    """
    with mysql.db_session() as session:
        # this enum only holds 1 value Y or null.
        reset_sql = text("""UPDATE vend_contact
            SET auth0_primary = NULL WHERE auth0_user_id = :auth0_user_id;""")
        primary_sql = text("""UPDATE vend_contact
            SET auth0_primary = 'Y'
            WHERE auth0_user_id = :auth0_user_id AND id = :primary_contact_id;""")

        for auth0_id, metadata in new_label_profile.items():
            auth0_id = auth0_id.replace('auth0|', '')
            session.execute(reset_sql, {'auth0_user_id': auth0_id})
            session.execute(
                primary_sql,
                {'auth0_user_id': auth0_id, 'primary_contact_id': int(metadata['vend_contact_id'])},
            )