"""Geographic insights model."""
import json

from oto import response as oto_response
from oto import status

from analytics.connectors import redis
from analytics.connectors import snowflake
from analytics.models import utils

ISRC_CLAUSE = 'AND isrc in (:isrcs)'
ARTISTID_CLAUSE = 'AND artistid in (:artist_ids)'
SUBACCOUNT_CLAUSE = 'AND subaccountid = :subaccountid'
LABELID_CLAUSE = 'AND labelid = :labelid'
STOREID_CLAUSE = 'AND storeid in (:store_ids)'
FEEDID_CLAUSE = 'AND feedid in (:feed_ids)'
DISTRIBUTOR_CLAUSE = 'AND distributor in (:distributors)'

_sql_loader = snowflake.SQLLoader('analytics/queries')


def _get_geographic_insights_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
            isrcs (list[str]) - list of track isrc values
            artist_ids (list[int]) - list of artist ids
            table_suffix (str) - name of suffix to be appended to table
            distributors (list) - list of distributors names

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

    """
    raw_sql = _sql_loader.load_query(sql_query)

    return raw_sql.format(
        labelid_clause=LABELID_CLAUSE if params['labelid'] else '',
        subaccount_clause=SUBACCOUNT_CLAUSE if params['subaccountid'] else '',
        isrc_clause=ISRC_CLAUSE if (
            params['isrcs'] and params['isrcs'][0] is not None) else '',
        artistids_clause=ARTISTID_CLAUSE if params['artist_ids'] else '',
        storeids_clause=STOREID_CLAUSE if (
            params['store_ids'] and params['store_ids'][0] is not None
        ) else '',
        feedids_clause=FEEDID_CLAUSE,
        distributor_clause=DISTRIBUTOR_CLAUSE if (params[
            'distributors'] and params['distributors'][0] is not None) else ''
    )


def get_geographic_insights(
        start_date, end_date, isrcs, artist_ids, store_ids, feed_ids,
        labelid, subaccountid, distributors):
    """Get stream numbers.

    Args:
        start_date (str): Start date
        end_date (str): End date
        isrcs (list): List of isrcs
        artist_ids (list): List of artist ids
        store_ids (list): List of store ids
        feed_ids (list): List of available feeds
        labelid (int): user label id
        subaccountid (int): user subaccountid
        distributors (list): list of distributors names

    Returns:
        oto_response.Response: containing the geographics insights data

    """
    query_params = {
        'query': 'get_geographic_insights_v3', 'start_date': start_date,
        'end_date': end_date, 'labelid': labelid, 'subaccountid': subaccountid,
        'artist_ids': artist_ids, 'store_ids': store_ids, 'feed_ids': feed_ids,
        'distributors': distributors, 'isrcs': isrcs
    }

    # respond with cached result, if available
    cache_key = utils.get_cache_key(query_params)
    cached_result = redis.client.get(cache_key)

    if cached_result:
        raw_geographic_insights = json.loads(cached_result.decode('utf8'))
        return oto_response.Response(
            message=_format_geographic_insights_by_territory(
                raw_geographic_insights))

    query_name = _choose_geographics_query(query_params)
    sql = _get_geographic_insights_sql(query_params, query_name)

    raw_geographic_insights = snowflake.fetchall(sql, query_params)

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

    redis.client.set(cache_key, json.dumps(raw_geographic_insights), ex=60*60)

    return oto_response.Response(
        message=_format_geographic_insights_by_territory(
            raw_geographic_insights))


def _choose_geographics_query(query_params):
    """Choose which geographics insights SQL query to use.

    Args:
        params (dict): the params to filter by

    Returns:
        str: Sql query file name

    """
    if not query_params['artist_ids'] and not query_params['isrcs']:
        return 'get_geographics_insights_worldwide_from_label_summary'
    elif not query_params['isrcs']:
        return 'get_geographics_insights_worldwide_from_artist_summary'
    else:
        return 'get_geographics_insights_worldwide_from_isrc_summary'


def get_geographic_insights_by_region(
        start_date, end_date, isrcs, artist_ids, store_ids, feed_ids, labelid,
        subaccountid, distributors):
    """Get stream numbers by region.

    Args:
        start_date (str): Start date.
        end_date (str): End date.
        isrcs (list): List of isrcs.
        artist_ids (list): List of artist ids.
        store_ids (list): List of store ids.
        feed_ids (list): List of feed ids.
        labelid (int): user label id
        subaccountid (int): user subaccountid
        distributors (list): list of distributors names

    Returns:
        oto_response.Response: containing the geographics insights data

    """
    query_params = {
        'query': 'get_geographic_insights_by_region_v3',
        'start_date': start_date, 'end_date': end_date, 'labelid': labelid,
        'subaccountid': subaccountid, 'artist_ids': artist_ids,
        'store_ids': store_ids, 'feed_ids': feed_ids, 'isrcs': isrcs,
        'distributors': distributors}

    # respond with cached result, if available
    cache_key = utils.get_cache_key(query_params)
    cached_result = redis.client.get(cache_key)

    if cached_result:
        raw_geographic_insights = json.loads(cached_result.decode('utf8'))
        return oto_response.Response(
            message=_format_geographic_insights_by_region(
                raw_geographic_insights))

    query_name = _choose_geographics_by_region_query(query_params)

    sql = _get_geographic_insights_sql(query_params, query_name)

    raw_geographic_insights = snowflake.fetchall(sql, query_params)

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

    redis.client.set(
        cache_key, json.dumps(raw_geographic_insights), ex=60*60)

    return oto_response.Response(
        message=_format_geographic_insights_by_region(
            raw_geographic_insights))


def _choose_geographics_by_region_query(query_params):
    """Choose which geographics by region insights SQL query to use.

    Args:
        params (dict): the params to filter by

    Returns:
        str: Sql query file name

    """
    if not query_params['artist_ids'] and not query_params['isrcs']:
        return 'get_geographics_insights_by_region_from_label_summary'
    elif not query_params['isrcs']:
        return 'get_geographics_insights_by_region_from_artist_summary'
    else:
        return 'get_geographics_insights_by_region_from_isrc_summary'


def get_geographic_orchard_regions():
    """Get geographic orchard regions.

    Returns:
        oto_response.Response: containing the geographic orchard regions

    """
    cache_key = 'get_geographic_orchard_regions_spotify_update'

    cached_result = redis.client.get(cache_key)

    if cached_result:
        raw_geographic_orchard_regions = json.loads(
            cached_result.decode('utf8'))
        return oto_response.Response(
            message=_format_geographic_orchard_region(
                raw_geographic_orchard_regions))

    query_name = 'get_geographic_orchard_regions'

    raw_geographic_orchard_regions = snowflake.fetchall(
        _sql_loader.load_query(query_name))

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

    redis.client.set(cache_key, json.dumps(raw_geographic_orchard_regions),
                     ex=86400)

    return oto_response.Response(
        message=_format_geographic_orchard_region(
            raw_geographic_orchard_regions))


def get_geographics_latest_date(feed_ids, distributors):
    """Get the most recent download activity date across summary tables.

    Args:
        feed_ids (list): List of available feed ids.
        distributors (list): List of distributors names.

    Returns:
        oto_response.Response: containing the geographic orchard max dates

    """
    cache_key = 'get_summary_geographics_max_date:{feed_ids}'.format(
        feed_ids=feed_ids)
    cached_result = redis.client.get(cache_key)

    if cached_result:
        raw_max_date_by_label = json.loads(cached_result.decode('utf8'))
        return oto_response.Response(
            message=raw_max_date_by_label)

    res = snowflake.fetchone(_sql_loader.load_query(
        'get_summary_geographics_max_date'), params={
            'feed_ids': feed_ids,
            'distributors': distributors})

    if res:
        res = res[0]
        raw_max_date_by_label = str(res)
        redis.client.set(cache_key, json.dumps(raw_max_date_by_label),
                         ex=3600)
    else:
        raw_max_date_by_label = None

    return oto_response.Response(message=raw_max_date_by_label)


def _format_geographic_insights_by_territory(raw_geographic_insights):
    """Format worldwide geographic insights db data.

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

    Returns:
        dict: containing the worldwide geographics insights data

    """
    geographic_insights = []
    for item in raw_geographic_insights:
        code = item[0]
        streams = item[1]

        geographic_insights.append({
            'territory_code': code,
            'unique_listeners': 0,
            'number_of_streams': streams
        })

    return geographic_insights


def _format_geographic_insights_by_region(raw_geographic_insights):
    """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.

    """
    geographic_insights = []
    for item in raw_geographic_insights:
        region_name = item[0]
        region_code = item[1]
        territory_code = item[2]
        streams = item[3]

        geographic_insights.append({
            'region_name': region_name,
            'region_code': region_code,
            'territory_code': territory_code,
            'unique_listeners': 0,
            'number_of_streams': streams
        })

    return geographic_insights


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