"""Logic for Charts."""
from typing import List, Any, Dict

from charts.connectors import snowflake
from charts import config
from charts.constants.track.charts import DEFAULT_TRACK_IN_CHART_ORDER_BY, order_by_param_to_field_mapping

from charts.logic import sql_loader
from charts import features
from charts.utils.multikeysort import multikeysort


def get_track_aggregated_rankings(public_sound_recording_id, appeared_on_date):
    params = {
        'public_sound_recording_id': public_sound_recording_id
    }
    column_names = [
        'chartId',
        'publicSoundRecordingId',
        'publicProductId',
        'trackId',
    ]

    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)

    if appeared_on_date:
        appeared_on_date_filter = """ EXISTS (
            SELECT
                1
            FROM fact_charts fc
            WHERE public_sound_recording_id = :public_sound_recording_id
            AND chart_date = :appeared_on_date
            AND fc.chartid = ac.chartid
            AND fc.chartmetric_track_id = ac.chartmetric_track_id
        )"""
        params['appeared_on_date'] = appeared_on_date
        additional_where_clauses.append(appeared_on_date_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('public_sound_recording_aggregated')
    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 get_track_charts(
        isrc: str,
        platforms: List[str],
        current_appearances: bool = True,
        order_by: str = "position"
) -> List[Dict[str, Any]]:
    """Get charts filtered by isrc & platforms

    Args:
        isrc: ISRC
        platforms: Array of platforms like "spotify", "apple" etc.
        current_appearances: filter charts where track is currently present
        order_by: sorting order

    Returns:
        List of chart dicts grouped by name
        with additional field - "countries" of a list that contains all the related charts

        result example:
        {
            "chart_name": string chart name like Apple Music Daily Top 100, Spotify Weekly Top 200
            "latest_chart_date": actual date of the chart
            "track_in_chart_most_recent_position": current position of the track in a chart
            "chart_most_recent_timestamp":
            "track_in_chart_previous_position": previous position of the track in a chart
            "chart_country": country code
            "track_last_added_date": latest date when track entered the chart
            "platform": platform like applemusic, spotify etc
            "track_in_chart_streams": streams of a track in a chart for latest_chart_date
            "countries": [
                {
                "chart_name": ...
                "latest_chart_date": ...
                "track_in_chart_most_recent_position": ...
                "chart_most_recent_timestamp":
                "track_in_chart_previous_position": ...
                "chart_country": ...
                "track_last_added_date": ...
                "platform": ...
                "track_in_chart_streams": ...,
                }
            ], --> Array of charts related to the main object grouped by "chart_name"
                    and stored in the "countries" section
        }

    """
    result = {}

    # Query params
    # reason for exclude_charts is described IN-10347 and spotify_viral_weekly charts are outdated, no longer exist
    # but the data is still present in DB so should be removed from original response.
    # This logic can be changed or removed when data team will solve the problem with the presence of outdated data
    params = {
        "isrc": isrc,
        "platforms": [platform.lower() for platform in platforms],
        "exclude_charts": config.CHARTS_TO_EXCLUDE
    }

    # Columns we are querying to map the DB result
    column_names = (
        "chart_name",
        "latest_chart_date",
        "track_in_chart_most_recent_position",
        "chart_most_recent_timestamp",
        "track_in_chart_previous_position",
        "chart_country_code",
        "track_first_added_date",
        "track_last_added_date",
        "chart_frequency",
        "platform",
        "track_in_chart_streams",
        "days_between_records_update",
        "definition_key_mapping",
        "last_date_track_was_added_to_chart"
    )

    sql = sql_loader.load_query('get_charts_by_isrc_and_platforms')

    # Condition for additional where clause to filter the result only by current appearances or get all data
    current_appearances_additional_where_clause = \
        (f" AND DATE(charts_by_platform.LATEST_CHART_DATE) {'=' if current_appearances else '>='}"
         f" DATE(aggregated_charts.most_recent_timestamp)")

    # Order by condition for the query
    query_order_by = f" ORDER BY {order_by_param_to_field_mapping[DEFAULT_TRACK_IN_CHART_ORDER_BY]}"

    sql = sql.format(
        current_appearances_additional_where_clause=current_appearances_additional_where_clause,
        order_by=query_order_by
    )

    raw_db_result = snowflake.fetchall(sql, params)
    # Map the result that is a List[Tuple[...] for every row] to get a List[Dict[...] for every row]
    # with keys from column_names and values from DB
    raw_db_result_mapped_with_column_names = [{c: v for c, v in zip(column_names, row)} for row in raw_db_result]

    if not order_by == DEFAULT_TRACK_IN_CHART_ORDER_BY:
        raw_db_result_mapped_with_column_names = multikeysort(
            raw_db_result_mapped_with_column_names,
            [order_by_param_to_field_mapping[order_by]]
        )

    # The item is a dict with column_names mapped with DB result values,
    # Here we group by all charts by chart_name and adding all related charts to "countries" list
    for item in raw_db_result_mapped_with_column_names:
        # first appearance of a chart by name. we add the item to the main section
        # and extend it with "countries" where this particular and all another related charts will be added
        if not result or not result.get(item["chart_name"]):
            result[item["chart_name"]] = {
                **item,
                "countries": [item]
            }
        # We already have a main section with "countries" so we simply add this item to "countries"
        else:
            result[item["chart_name"]]["countries"].append(item)

    return list(result.values())