"""Geographic insights model."""

from oto import response as oto_response
from oto import status
from snowflake_connector import snowflake_conn
from snowflake_connector.snowflake_conn import SQLLoader

UPC_CLAUSE = 'AND DISPLAY_UPC = :upc'
ARTISTID_CLAUSE = 'AND artistid in (:artist_ids)'
SUBACCOUNT_CLAUSE = 'AND subaccountid = :subaccountid'
LABELID_CLAUSE = 'AND labelid = :labelid'
STOREID_CLAUSE = 'AND storeid in (:store_ids)'

_sql_loader = SQLLoader('analytics/queries')


def _get_product_summary_sql(params, sql_query):
    """Get geographics insights SQL.

    Extrapolate SQL template with filtering conditions if required. Filter
    conditions contains SQL params which are filled in session.execute function
    call.

    Args:
        params (dict): should have the following keys:
            labelid (int) - labelid from dim_label table
            subaccountid (int) - subaccountid from dim_subaccount table
            upcs (list[str]) - list of product upc values
            artist_ids (list[int]) - list of artist ids
            table_suffix (str) - name of suffix to be appended to table

        sql_query (str) - Sql query file name
    Returns:
        str: geographics insights SQL

    """
    raw_sql = _sql_loader.load_query(sql_query)

    order_clause = ''
    search_filter = ''
    if 'search_filter' in params:
        search_filter = 'storeid,' if params['search_filter'] == 'by_store' else 'country_code,'
        order_clause = 'ORDER BY {transaction}_number DESC'.format(transaction=params['transaction'])

    download_activity_date = ''
    if 'group_daily' in params and params['group_daily']:
        download_activity_date = 'download_activity_date,'
        order_clause = 'ORDER BY download_activity_date ASC'

    return raw_sql.format(
        search_filter=search_filter,
        download_activity_date=download_activity_date,
        labelid_clause=LABELID_CLAUSE if params['labelid'] else '',
        subaccount_clause=SUBACCOUNT_CLAUSE if params['subaccountid'] else '',
        upc_clause=UPC_CLAUSE,
        order_clause=order_clause,
        transaction=params['transaction']
    )


def _choose_query(search_filter):
    if search_filter == 'by_store':
        return 'get_product_summary_by_store'
    elif search_filter == 'by_country':
        return 'get_product_summary_by_country'
    else:
        return 'get_product_summary'


def get_product_summary(
        start_date, end_date, upc, search_filter, labelid,
        subaccountid, group_daily, transaction):
    """Get stream numbers by region.

    Args:
        start_date (str): Start date.
        end_date (str): End date.
        upcs (list): List of upcs.
        search_filter (list): List of store ids.
        labelid (int): user label id
        subaccountid (int): user subaccountid

    Returns:
        oto_response.Response: containing the geographics insights data

    """
    query_name = _choose_query(search_filter)

    query_params = {
        'query': query_name, 'start_date': start_date, 'end_date': end_date,
        'labelid': labelid, 'subaccountid': subaccountid, 'upc': upc,
        'search_filter': search_filter, 'group_daily': group_daily,
        'transaction': transaction
    }

    sql = _get_product_summary_sql(query_params, query_name)

    raw_product_summary = snowflake_conn.fetchall(sql, query_params)

    if not raw_product_summary:
        return oto_response.Response(status=status.NO_CONTENT)

    return oto_response.Response(
        message=_format_product_summary(
            raw_product_summary, group_daily, search_filter, transaction))


def _format_product_summary(raw_product_summary, group_daily, search_filter, transaction):
    """Format geographic insights by region db data.

    Args:
        data (dict): raw data from which to get formatted data.

    Returns:
        array: containing geographics insights splitted by region.

    """
    product_summary = []
    for item in raw_product_summary:
        transactions = item[0]

        if search_filter == 'by_store':
            if group_daily:
                product_summary.append({
                    'storeid': item[2],
                    'date': str(item[1]),
                    transaction: transactions
                })
            else:
                product_summary.append({
                    'storeid': item[1],
                    transaction: transactions
                })
        elif search_filter == 'by_country':
            if group_daily:
                product_summary.append({
                    'country_code': item[2],
                    'date': str(item[1]),
                    transaction: transactions
                })
            else:
                product_summary.append({
                    'country_code': item[1],
                    transaction: transactions
                })
        else:
                product_summary.append({
                    transaction: transactions
                })

    return product_summary


def _format_geographic_orchard_region(raw_geographic_orchard_regions):
    """Format geographic orchard regions db data.

    Args:
        data (dict): raw data from which to get formatted data.

    Returns:
        array: containing geographic orchard regions.

    """
    geographic_orchard_regions = []
    for item in raw_geographic_orchard_regions:
        territory_code = item[0]
        region_code = item[1]
        region_name = item[2]

        geographic_orchard_regions.append({
            'territory_code': territory_code,
            'region_code': region_code,
            'region_name': region_name
        })

    return geographic_orchard_regions