"""Methods specific to The Orchard."""
import pymysql

import backoff
import sqlalchemy

from integration_scripts.connectors.logging import logger
from integration_scripts.connectors import mysql

from integration_scripts.db_utils import run_query

from integration_scripts import db_utils

from integration_scripts.sql.select_access_token_oa import \
    SELECT_ACCESS_TOKEN_OA
from integration_scripts.sql.select_access_token_alw import \
    SELECT_ACCESS_TOKEN_ALW
from integration_scripts.sql.select_upc_from_project_code import \
    SELECT_UPC_FROM_PROJECT_CODE
from integration_scripts.sql.select_upc_from_product_code import \
    SELECT_UPC_FROM_PRODUCT_CODE
from integration_scripts.sql.select_upc_from_release_id import \
    SELECT_UPC_FROM_RELEASE_ID
from integration_scripts.sql.select_upc_from_release_id_list import \
    SELECT_UPC_FROM_RELEASE_ID_LIST
from integration_scripts.sql.select_product_code_from_upc import \
    SELECT_PRODUCT_CODE_FROM_UPC
from integration_scripts.sql.select_release_id_from_upc import \
    SELECT_RELEASE_ID_FROM_UPC
from integration_scripts.sql.select_tuid_from_upc_vol_track import \
    SELECT_TUID_FROM_UPC_VOL_TRACK
from integration_scripts.sql.select_bulk_tuid_from_release_id import \
    SELECT_BULK_TUID_FROM_RELEASE_ID


@backoff.on_exception(backoff.expo,
                      (pymysql.err.Error, sqlalchemy.exc.SQLAlchemyError),
                      max_tries=5, max_time=1200, max_value=300)
@mysql.ar_db_session_wrap
def get_user_id_alw(session, label_id):
    """Get an ALW user_id from a given label_id.

    Args:
        session: The wrapping session
        label_id (str): label_id for which to retrieve access token

    Returns:
        str
    """
    # Init vars
    params = {'vendor_id': label_id}
    sql = SELECT_ACCESS_TOKEN_ALW
    query_results = db_utils.zip_resultproxy_dict(
        db_utils.run_query(session, sql=sql, params=params))
    result = None
    user_id = None

    for result in query_results:
        user_id = result['user_id']

    if not result:
        raise ValueError(
            'No ALW user_id available for label: {}'.format(label_id))

    return user_id


@backoff.on_exception(backoff.expo,
                      (pymysql.err.Error, sqlalchemy.exc.SQLAlchemyError),
                      max_tries=5, max_time=1200, max_value=300)
@mysql.ar_db_session_wrap
def get_access_token_alw(session, label_id):
    """Get an ALW access token from art_relations for a given label_id.

    Args:
        session: The wrapping session
        label_id (str): label_id for which to retrieve access token

    Returns:
        str
    """
    # Init vars
    params = {'vendor_id': label_id}
    sql = SELECT_ACCESS_TOKEN_ALW
    query_results = db_utils.zip_resultproxy_dict(
        db_utils.run_query(session, sql=sql, params=params))
    access_token = None
    result = None

    for result in query_results:
        access_token = result['access_token']

    if not result:
        raise ValueError(
            'No ALW access_token available for label: {}'.format(label_id))

    return access_token


@backoff.on_exception(backoff.expo,
                      (pymysql.err.Error, sqlalchemy.exc.SQLAlchemyError),
                      max_tries=5, max_time=1200, max_value=300)
@mysql.ar_db_session_wrap
def get_access_token_oa(session, user_id):
    """Get an OA access token from art_relations for a given user id.

    Args:
        session: (SQLAlchemy)       Session from db wrapper
        user_id (str): user_id for which to retrieve access token

    Returns:
        str
    """

    # Init vars
    params = {'login': user_id}
    sql = SELECT_ACCESS_TOKEN_OA
    query_results = db_utils.zip_resultproxy_dict(
        db_utils.run_query(session, sql=sql, params=params))
    access_token = None
    result = None

    for result in query_results:
        access_token = result['access_token']

    if not result:
        raise ValueError(
            'No OA access_token available for user: {}'.format(user_id))

    return access_token


def fetch_access_token(label_id=None, user_login=None):
    """Retrieve the correct access token for Bulk Upload access."""
    # If there is any data for this single label

    # Build Request Object for JSON
    if user_login:
        # OA Login
        logger.info('Fetching OA access token for {}.'.format(user_login))

        access_token = get_access_token_oa(login=user_login)

    elif label_id:  # ALW Login
        logger.info('Fetching ALW access token for {}.'.format(label_id))

        access_token = get_access_token_alw(label_id=label_id)

    else:
        raise ValueError('\'fetch_access_token()\' requires either a '
                         'label_id or a user_login')
    return access_token


@mysql.ar_db_session_wrap
def get_upc_from_project_code(session, project_code):
    """Get all audio assets in ripper audio view."""
    if not project_code:
        raise RuntimeError(
            'Project Code must be passed to get UPC.')

    sql = SELECT_UPC_FROM_PROJECT_CODE
    params = {
        'project_code': project_code
    }

    result = run_query(session, sql=sql, params=params)

    res = result.first()
    upc = res.upc

    return upc


@mysql.ar_db_session_wrap
def get_upc_from_product_code(session, product_code):
    """Get UPC from product code."""
    if not product_code:
        raise RuntimeError(
            'Product Code must be passed to get UPC.')

    sql = SELECT_UPC_FROM_PRODUCT_CODE
    params = {
        'product_code': product_code
    }

    result = run_query(session, sql=sql, params=params)

    res = result.first()
    upc = res.upc

    return upc


@mysql.ar_db_session_wrap
def get_upc_from_release_id(session, release_id):
    """Get UPC from release id."""
    if not release_id:
        raise RuntimeError(
            'Release ID must be passed to get UPC.')

    sql = SELECT_UPC_FROM_RELEASE_ID
    params = {
        'release_id': release_id
    }

    result = run_query(session, sql=sql, params=params)

    res = result.first()
    upc = res.upc

    return upc


@mysql.ar_db_session_wrap
def get_bulk_upc_from_release_ids(session, release_id_list):
    """Get UPC list from a list of release ids."""

    params = {
        # 'release_id_list': release_id_list
        'release_id_list': ','.join(map(str, release_id_list))
    }

    sql = SELECT_UPC_FROM_RELEASE_ID_LIST

    results = run_query(session, sql=sql, params=params)

    res = [x.upc for x in list(results)]

    return res


@mysql.ar_db_session_wrap
def get_product_code_from_upc(session, upc):
    """Get all audio assets in ripper audio view."""
    if not upc:
        raise RuntimeError(
            'UPC must be passed to get Product Code.')

    sql = SELECT_PRODUCT_CODE_FROM_UPC
    params = {
        'upc': upc
    }

    result = run_query(session, sql=sql, params=params)

    res = result.first()
    product_code = res.product_code

    return product_code


@mysql.ar_db_session_wrap
def get_release_id_from_upc(session, upc):
    """Get all audio assets in ripper audio view."""
    if not upc:
        raise RuntimeError(
            'UPC must be passed to get Release ID.')

    sql = SELECT_RELEASE_ID_FROM_UPC
    params = {
        'upc': upc
    }

    result = run_query(session, sql=sql, params=params)

    res = result.first()
    relid = res.release_id

    return relid


@mysql.ar_db_session_wrap
def get_tuid_from_upc_vol_track(session, upc, track_num, vol):
    """Get all audio assets in ripper audio view."""
    if not upc and track_num and vol:
        raise RuntimeError(
            'UPC, Track Num, and Volume must be passed to get tuid.')

    sql = SELECT_TUID_FROM_UPC_VOL_TRACK

    params = {
        'upc': upc,
        'track_num': track_num,
        'vol': vol
    }

    result = run_query(session, sql=sql, params=params)

    if result.rowcount > 1:
        raise RuntimeError(
            'Multiple ({}) TUID\'s found for vol {}, track {} on '
            'UPC {}.'.format(result.rowcount, vol, track_num, upc))

    res = result.first()
    tuid = res.id

    return tuid


@mysql.ar_db_session_wrap
def get_bulk_tuid_from_release_ids(session, release_id_list):
    """Get all tuids from from a list of release ids.

    Args:
        session: (SQLAlchemy) a db session
        release_id_list: (list) A list of tuples which contain
        (UPC, vol_no, trk_no)

    Returns:
        list of tuid's

    """
    params = {
        # 'release_id_list': release_id_list
        'release_id_list': ','.join(map(str, release_id_list))
    }

    sql = SELECT_BULK_TUID_FROM_RELEASE_ID

    results = run_query(session, sql=sql, params=params)

    res = [x.id for x in list(results)]

    return res


def remap_transitional_territories(terr_list):
    """Remap transitional territories."""
    return_list = set()

    for i in terr_list:

        # ['AN','CS','FX','GZ','TP','ZZ']
        if i == 'AN':
            return_list.add('BQ')
            return_list.add('CW')
            return_list.add('SX')
        elif i == 'FX':
            return_list.add('FR')
        elif i == 'CS':
            return_list.add('ME')
            return_list.add('RS')
        elif i == 'GZ':
            return_list.add('PS')
        elif i == 'TP':
            return_list.add('TL')
        elif i == 'ZZ':
            pass
        else:
            return_list.add(i)

    return list(return_list)