"""Get items from Snowflake Model."""

from snowflake_connector import snowflake_conn

ISRCS_ON_PRODUCTS_FIELDS = ['first_id', 'second_id', 'track_number']
ISRC_FIELDS = [
    'first_id', 'second_id', 'upc', 'product_id', 'artist_name', 'track_name',
    'label_id', 'subaccount_id', 'track_unique_id']
PRODUCT_FIELDS = [
    'first_id', 'second_id', 'upc', 'product_id', 'release_name', 'format',
    'release_date', 'sales_start_date', 'artist_name', 'subaccount_name',
    'label_id', 'subaccount_id']

sql_loader = snowflake_conn.SQLLoader(__file__)

def get_isrcs_on_product_for_label(label_id):
    """Get the isrcs on a product for a given label."""

    sql = sql_loader.load_query('get_isrcs_on_product')

    params = {'label_id': label_id}

    isrcs_on_products = snowflake_conn.fetchall(sql, params)

    return [
        dict(
            zip(ISRCS_ON_PRODUCTS_FIELDS, isrc_product)
        ) for isrc_product in isrcs_on_products
    ]


def get_isrcs_for_label(label_id):
    """Return isrc metadata for a given label."""

    sql = sql_loader.load_query('get_isrcs')

    params = {'label_id': label_id}

    isrcs = snowflake_conn.fetchall(sql, params)

    return [dict(zip(ISRC_FIELDS, isrc)) for isrc in isrcs]


def get_products_for_label(label_id):
    """Return metadata for all products for a given label."""

    sql = sql_loader.load_query('get_products')

    params = {'label_id': label_id}

    product_list = snowflake_conn.fetchall(sql, params)

    return [dict(zip(PRODUCT_FIELDS, product)) for product in product_list]