"""Model Tier For Legacy Queries."""

from pricing.connectors import legacy_mysql
from pricing.constants import legacy_db_queries

import sqlalchemy


def get_all_product_pricing_tiers(product_id):
    """Get all pricing tiers for a product from legacy db.

    Args:
        product_id (int): The id of the product
    Returns:
        all the legacy pricing tiers for the product
    """
    query = legacy_db_queries.PRODUCT_PRICING_TIERS
    rows = query_legacy_db(query, {
        'product_id': product_id,
        'active_status': 1
    })
    return rows


def get_all_product_territory_pricing(product_id, store_id, product_type_id):
    """Get all pricing by territory for a product from legacy db.

    Args:
        product_id (int): The id of the product
        store_id (int): The id of the store
        product_type_id (int): The id of the product type
    Returns:
        all the product pricing by territory
    """
    query = legacy_db_queries.PRODUCT_STORE_PRICING
    rows = query_legacy_db(query, {
        'store_id': store_id,
        'product_type_id': product_type_id,
        'product_id': product_id,
        'active_status': 'Y',
        'scheme_level': 'release'
    })
    return rows


def get_product_pricing_exclude_stores(
        product_id, product_type_id, exclude_store_ids):
    """Get pricing by territory for a product from legacy db.

    Args:
        product_id (int): The id of the product
        product_type_id (int): The id of the product type
        exclude_store_ids ([int]): The ids of the store to exclude
    Returns:
        the product pricing by territory
    """
    if exclude_store_ids and len(exclude_store_ids) > 0:
        query = legacy_db_queries.PRODUCT_STORE_PRICING_EXCLUDE
        rows = query_legacy_db(query, {
            'product_type_id': product_type_id,
            'exclude_store_ids': tuple(exclude_store_ids),
            'customer_master_master_id': 1,
            'product_id': product_id,
            'active_status': 'Y',
            'scheme_level': 'release',
            'abbrivation': 'US'
        })
    else:
        query = legacy_db_queries.PRODUCT_STORE_PRICING_ALL
        rows = query_legacy_db(query, {
            'product_type_id': product_type_id,
            'product_id': product_id,
            'active_status': 'Y',
            'scheme_level': 'release'
        })
    return rows


def get_product_tracks(product_id):
    """Get all tracks for a product from legacy db.

    Args:
        product_id (int): The id of the product
    Returns:
        all the tracks
    """
    rows = query_legacy_db(
        legacy_db_queries.PRODUCT_TRACKS, {'product_id': product_id}
    )
    return rows


def get_product_track_territory_pricing(
        product_id, store_id, product_type_id):
    """Get all pricing by territory for a product from legacy db.

    Args:
        product_id (int): The id of the product
        store_id (int): The id of the store
        product_type_id (int): The id of the product type
    Returns:
        all the product pricing by territory
    """
    query = legacy_db_queries.TRACK_STORE_PRICING
    rows = query_legacy_db(query, {
        'store_id': store_id,
        'product_type_id': product_type_id,
        'product_id': product_id,
        'active_status': 'Y',
        'scheme_level': 'track'
    })
    return rows


def get_track_pricing_exclude_stores(
        product_id, product_type_id, exclude_store_ids):
    """Get track pricing by territory for a product from legacy db.

    Args:
        product_id (int): The id of the product
        product_type_id (int): The id of the product type
        exclude_store_ids ([int]): The ids of the store to exclude
    Returns:
        the track pricing by territory
    """
    if exclude_store_ids and len(exclude_store_ids) > 0:
        query = legacy_db_queries.TRACK_STORE_PRICING_EXCLUDE
        rows = query_legacy_db(query, {
            'product_type_id': product_type_id,
            'exclude_store_ids': tuple(exclude_store_ids),
            'customer_master_master_id': 1,
            'product_id': product_id,
            'active_status': 'Y',
            'scheme_level': 'track',
            'abbrivation': 'US'
        })
    else:
        query = legacy_db_queries.TRACK_STORE_PRICING_ALL
        rows = query_legacy_db(query, {
            'product_type_id': product_type_id,
            'product_id': product_id,
            'active_status': 'Y',
            'scheme_level': 'track'
        })
    return rows


def get_default_pricing_codes(store_id, product_type_id, scheme_level):
    """Get default price codes by territory for a store from legacy db.

    Args:
        store_id (int): The id of the product
        product_type_id (int): The id of the product type
        scheme_level (string): Can be 'track' or 'release'
    Returns:
        all the default price codes by territory
    """
    rows = query_legacy_db(legacy_db_queries.DEFAULT_PRICING_CODES, {
        'store_id': store_id,
        'product_type_id': product_type_id,
        'scheme_level': scheme_level
    })
    return rows


def get_all_film_product_ids():
    """Get all the film product ids.

    Returns:
        all the film product ids
    """
    query = legacy_db_queries.ALL_FILM_PRODUCTS
    rows = query_legacy_db(query, {
        'product_type_id': legacy_db_queries.FILM_PRODUCT_TYPE_ID,
        'excluded_subtype_id': 3
    })
    return rows


def get_music_releases():
    """Get all music releases.

    Returns:
        list: the releases
    """
    query = legacy_db_queries.MUSIC_RELEASES
    rows = query_legacy_db(query, {'product_type_id': 1})

    return rows


def query_legacy_db(query, params={}):
    """Connect to legacy db and retrieve data.

    Args:
        query (string): the sql query
        params (dict, optional): data parameters
    Returns:
        the query result as a list of dicts
    """
    session = legacy_mysql.get_session()

    try:
        result = session.execute(sqlalchemy.text(query), params).mappings().fetchall()
        session.commit()
        return result

    except Exception:
        session.rollback()

    finally:
        session.close()