"""Logic for Charts."""

from charts.connectors import snowflake
from charts import config, features

from charts.logic import sql_loader
from charts.constants.artist_mappings import artist_aggregated_rankings_order_by_mapping, artist_aggregated_rankings_order_direction_mapping


def get_artist_aggregated_rankings(
        artist_id, chart_filter: dict, isrc,
        public_sound_recording_id, limit, offset,
        order_by, order_direction, only_current):
    """Get artist aggregated rankings."""
    assert limit > 0
    assert offset >= 0
    assert offset >= 0
    assert order_direction  \
        in artist_aggregated_rankings_order_direction_mapping
    assert order_by in artist_aggregated_rankings_order_by_mapping

    params = {
        'artist_id': artist_id,
        'isrc': isrc,
        'public_sound_recording_id': public_sound_recording_id,
        'limit': limit,
        'offset': offset,
    }
    column_names = [
        'chartId',
        'isrc',
        'trackId',
        'firstTimestamp',
        'firstPosition',
        'peakTimestamp',
        'peakPosition',
        'mostRecentTimestamp',
        'mostRecentPosition',
        'previousTimestamp',
        'previousPosition',
        'daysOnChart',
        'position',
        'change',
        'exitTimestamp',
        'publicSoundRecordingId',
        'soundRecordingName'
    ]
    additional_where_clauses = []

    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    for p, column in config.CHART_FILTER_PROPERTY_TO_COLUMN.items():
        if chart_filter.get(p):
            param_name = f'chart_{p}'
            params[param_name] = chart_filter[p]
            additional_where_clauses.append(
                f'dc.{column} = :{param_name}'
            )
    if isrc and isrc != '':
        additional_where_clauses.append('isrc = :isrc')
    if public_sound_recording_id and public_sound_recording_id != '':
        additional_where_clauses.append(
            'public_sound_recording_id = :public_sound_recording_id')
    if only_current is True or only_current == 'true':
        additional_where_clauses.append(
            'position IS NOT NULL')
    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_aggregated')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
        order_by=artist_aggregated_rankings_order_by_mapping[order_by],
        order_direction=artist_aggregated_rankings_order_direction_mapping[
            order_direction
        ],
    )
    rows = snowflake.fetchall(sql, params)
    result = [{c: v for c, v in zip(column_names, row)} for row in rows]
    return result


def artist_songs(
        artist_id, chart_filter: dict, isrc,
        public_sound_recording_id, only_current):
    """Get artist songs."""
    params = {
        'artist_id': artist_id,
    }
    column_names = [
        'isrc',
        'songName',
        'psrId',
        'count',
    ]
    additional_where_clauses = []

    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    for p, column in config.CHART_FILTER_PROPERTY_TO_COLUMN.items():
        if chart_filter.get(p):
            param_name = f'chart_{p}'
            params[param_name] = chart_filter[p]
            additional_where_clauses.append(
                f'dc.{column} = :{param_name}'
            )
    if isrc and isrc != '':
        additional_where_clauses.append('isrc = :isrc')
        params['isrc'] = isrc
    if public_sound_recording_id and public_sound_recording_id != '':
        additional_where_clauses.append(
            'public_sound_recording_id = :public_sound_recording_id')
        params['public_sound_recording_id'] = public_sound_recording_id
    if only_current is True or only_current == 'true':
        additional_where_clauses.append(
            'ac.most_recent_timestamp >= dc.latest_chart_date')
    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_songs')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
    )
    rows = snowflake.fetchall(sql, params)
    result = [{c: v for c, v in zip(column_names, row)} for row in rows]
    return result


def artist_charts(
        artist_id, chart_filter: dict, isrc,
        public_sound_recording_id, only_current):
    """Get artist charts."""
    params = {
        'artist_id': artist_id,
    }
    column_names = [
        'chartId',
        'count',
    ]
    additional_where_clauses = []

    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    for p, column in config.CHART_FILTER_PROPERTY_TO_COLUMN.items():
        if chart_filter.get(p):
            param_name = f'chart_{p}'
            params[param_name] = chart_filter[p]
            additional_where_clauses.append(
                f'dc.{column} = :{param_name}'
            )
    if isrc and isrc != '':
        additional_where_clauses.append('isrc = :isrc')
        params['isrc'] = isrc
    if public_sound_recording_id and public_sound_recording_id != '':
        additional_where_clauses.append(
            'public_sound_recording_id = :public_sound_recording_id')
        params['public_sound_recording_id'] = public_sound_recording_id
    if only_current is True or only_current == 'true':
        additional_where_clauses.append(
            'ac.most_recent_timestamp >= dc.latest_chart_date')
    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_charts')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
    )
    rows = snowflake.fetchall(sql, params)
    result = [{c: v for c, v in zip(column_names, row)} for row in rows]
    return result


def artist_countries(
        artist_id, chart_filter: dict, isrc,
        public_sound_recording_id, only_current):
    """Get artist countries."""
    params = {
        'artist_id': artist_id,
    }
    column_names = [
        'countryCode',
        'count',
    ]
    additional_where_clauses = []

    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    for p, column in config.CHART_FILTER_PROPERTY_TO_COLUMN.items():
        if chart_filter.get(p):
            param_name = f'chart_{p}'
            params[param_name] = chart_filter[p]
            additional_where_clauses.append(
                f'dc.{column} = :{param_name}'
            )
    if isrc and isrc != '':
        additional_where_clauses.append('isrc = :isrc')
        params['isrc'] = isrc
    if public_sound_recording_id and public_sound_recording_id != '':
        additional_where_clauses.append(
            'public_sound_recording_id = :public_sound_recording_id')
        params['public_sound_recording_id'] = public_sound_recording_id
    if only_current is True or only_current == 'true':
        additional_where_clauses.append(
            'ac.most_recent_timestamp >= dc.latest_chart_date')
    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_countries')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
    )
    rows = snowflake.fetchall(sql, params)
    result = [{c: v for c, v in zip(column_names, row)} for row in rows]
    return result


def artist_current_appearances(
        artist_id, chart_date, chart_id,
        chart_filter: dict, isrc,
        public_sound_recording_id, only_current):
    """Get artist appearances."""
    params = {
        'artist_id': artist_id,
        'chart_id': chart_id or '',
        'chart_date': chart_date or '',
    }
    column_names = [
        'count',
    ]
    additional_where_clauses = []

    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    for p, column in config.CHART_FILTER_PROPERTY_TO_COLUMN.items():
        if chart_filter.get(p):
            param_name = f'chart_{p}'
            params[param_name] = chart_filter[p]
            additional_where_clauses.append(
                f'dc.{column} = :{param_name}'
            )
    if isrc and isrc != '':
        additional_where_clauses.append('isrc = :isrc')
        params['isrc'] = isrc
    if public_sound_recording_id and isrc != '':
        additional_where_clauses.append(
            'public_sound_recording_id = :public_sound_recording_id')
        params['public_sound_recording_id'] = public_sound_recording_id
    additional_where_clauses.append(
        'ac.most_recent_timestamp >= dc.latest_chart_date')
    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_appearances')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
    )
    row = snowflake.fetchone(sql, params)
    result = {c: v for c, v in zip(column_names, row)}
    return result


def artist_chart_highlights(artist_id, limit, offset):
    """Get artist chart highlights."""
    params = {
        'artist_id': artist_id,
        'limit': int(limit) if limit else '',
        'offset': int(offset) if offset else '',
    }
    item_column_names = [
        'appearances',
        'currentAppearances',
        'isrc',
        'psrId',
        'psrName',
    ]

    additional_where_clauses = []
    controlled_charts_filter = features.get_ff_charts_sql_filter()
    if controlled_charts_filter is not None:
        additional_where_clauses.append(controlled_charts_filter)

    additional_where_clause = ' AND '.join(additional_where_clauses)
    if additional_where_clause:
        additional_where_clause = f'AND {additional_where_clause}'
    sql = sql_loader.load_query('artist_chart_highlights')
    sql = sql.format(
        additional_where_clause=additional_where_clause,
    )
    rows = snowflake.fetchall(sql, params)
    result_items = [
        {c: v for c, v in zip(item_column_names, row)} for row in rows]
    total_appearances = rows[0][-2] if rows else 0
    total_current_appearances = rows[0][-1] if rows else 0
    result = {
        'items': result_items,
        'totalAppearances': total_appearances,
        'totalCurrentAppearances': total_current_appearances
    }
    return result