"""Placements metadata model."""
import json
from datetime import datetime
from typing import Optional

from ddtrace import tracer

from sound_recordings.connectors import snowflake
from sound_recordings.constants import cache
from sound_recordings.constants.ordering import ORDER_DIRECTIONS
from sound_recordings.utils import format_sql
from sound_recordings.utils.cache import cache_in_redis

SQLLoader = snowflake.SQLLoader(__file__)

# order should always corresponds to order of fields in SQL
RECENT_PLACEMENTS_METADATA_FIELDS = [
    "playlist_id",
    "store_id",
    "playlist_name",
    "playlist_uri",
    "storefront",
    "added",
    "artwork_url",
    "country",
    "followers",
    "track_count",
    "position",
    "previous_position",
    "total_streams",
    "isrc",
    "user_display_name",
    "owner_category",
    "count",
    "total_count",
    "storefronts",
]

PLACEMENTS_METADATA_FIELDS = [
    "playlist_id",
    "store_id",
    "playlist_name",
    "playlist_uri",
    "storefront",
    "added",
    "artwork_url",
    "country",
    "followers",
    "track_count",
    "position",
    "previous_position",
    "total_streams",
    "isrc",
    "user_display_name",
    "owner_category",
    "count",
    "storefronts",
]


@tracer.wrap(name="get_placements_metadata")
@cache_in_redis(ttl=cache.SECONDS_PER_HOUR)
def get_placements_metadata(
    permissions_filter,
    limit,
    offset,
    order_dir,
    order_by,
    isrc,
    store_ids,
    owner_categories,
    min_followers,
) -> dict:
    """Return placements metadata by isrc.

    Args:
        permissions_filter (dict): dict containing resources users can access
        limit (int): the number of records to be fetched
        offset (int): the number of records to be skipped before fetching
        order_by (str): selector for column of interest
        order_dir (str): order direction ASC or DESC
        isrc (str): ISRC of track to fetch streams for.
        store_ids (array int): Store ids to filter by
        owner_categories (array str): Owner categories to filter by
        min_followers (int): Minimum number of followers to filter by (Spotify)

    Returns:
        list: placements metadata per track

    """
    if order_dir.upper() not in ORDER_DIRECTIONS:
        raise Exception("Invalid order_dir value")

    if order_by not in PLACEMENTS_METADATA_FIELDS:
        raise Exception("Invalid order_by value")

    query_name = "placements_metadata"

    sql = SQLLoader.load_query(query_name)
    sql = format_sql.format_with_permissions_filter(
        sql,
        permissions_filter,
        extra_format={
            "target_table": _get_placements_table(),
            "order_by": order_by,
            "order_dir": order_dir,
        },
    )

    placements_metadata_records = snowflake.fetchall(
        sql,
        {
            **permissions_filter,
            "isrc": isrc,
            "store_ids": store_ids,
            "owner_categories": owner_categories,
            "min_followers": min_followers,
            "limit": limit,
            "offset": offset,
        },
    )

    total_count = int(placements_metadata_records[0][0])
    records = json.loads(placements_metadata_records[0][1])

    items = [_normalize_record(r) for r in records]

    return {"total_count": total_count, "items": items}


@tracer.wrap(name="get_placement")
@cache_in_redis(ttl=cache.SECONDS_PER_HOUR)
def get_placement(permissions_filter, isrc, playlist_id) -> Optional[dict]:
    """Return a playlist placement for a given ISRC and playlist_id.

    Path parameters:
        permissions_filter (dict): dict containing resources users can access
        playlist_id (str): The playlist the song is located.
        isrc (str): The song's ISRC for this placement.

    Returns:
        A placement dict or None if not found
    """
    query_name = "placement"

    sql = SQLLoader.load_query(query_name)
    sql = format_sql.format_with_permissions_filter(
        sql, permissions_filter, extra_format={"target_table": _get_placements_table()}
    )

    placements_metadata_record = snowflake.fetchone(
        sql, {**permissions_filter, "playlist_id": playlist_id, "isrc": isrc}
    )
    if not placements_metadata_record:
        return None
    placements_metadata_record = _parse_storefronts_for_record(
        placements_metadata_record
    )
    return dict(zip(PLACEMENTS_METADATA_FIELDS[:], placements_metadata_record))


@tracer.wrap(name="get_recent_placements_metadata")
@cache_in_redis(ttl=cache.SECONDS_PER_HOUR)
def get_recent_placements_metadata(
    permissions_filter,
    limit,
    offset,
    store_ids,
    owner_categories,
    min_followers,
    label_ids=None,
    subaccount_ids=None,
):
    """Return recent placements metadata.

    Args:
        permissions_filter (dict): dict containing resources users can access
        limit (int): How many items to return
        offset (int): Offset for pagination
        store_ids (array int): Store ids to filter by
        owner_categories (array str): Owner categories to filter by
        min_followers (int): Minimum number of followers to filter by (Spotify)
        label_ids (array int): Placements within those label ids
        subaccount_ids (array int): Placements within those subaccount_ids

    Returns:
        list: recent placements metadata

    """
    query_name = "recent_placements_metadata"

    sql = SQLLoader.load_query(query_name)
    sql = format_sql.format_with_permissions_filter(
        sql,
        permissions_filter,
        extra_format={
            "target_table": _get_placements_table(),
            "vendor_filter_clause": _get_vendor_filter_clause(
                label_ids, subaccount_ids
            ),
        },
    )

    recent_placements_metadata_fields = RECENT_PLACEMENTS_METADATA_FIELDS[:]

    recent_placements_metadata = snowflake.fetchall(
        sql,
        {
            **permissions_filter,
            "limit": limit,
            "offset": offset,
            "store_ids": store_ids,
            "owner_categories": owner_categories,
            "min_followers": min_followers,
            "label_ids_for_filtering": label_ids,
            "subaccount_ids_for_filtering": subaccount_ids,
        },
    )

    recent_placements_metadata = _parse_storefronts_for_records(
        recent_placements_metadata
    )

    return [
        dict(zip(recent_placements_metadata_fields, record))
        for record in recent_placements_metadata
    ]


def get_placements_total_count(
    permissions_filter,
    owner_categories,
    min_followers,
    label_ids=None,
    subaccount_ids=None,
):
    """Return recent placements metadata total count of placements.

        Args:
        permissions_filter (dict): dict containing resources users can access
        owner_categories (array str): Owner categories to filter by
        min_followers (int): Minimum number of followers to filter by (Spotify)
        label_ids (array int): Placements within those label ids
        subaccount_ids (array int): Placements within those subaccount_ids

    Returns:
        int: recent placements total count
    """
    total_count_query_name = "recent_placements_metadata_total_count"
    sql = SQLLoader.load_query(total_count_query_name)
    sql = format_sql.format_with_permissions_filter(
        sql,
        permissions_filter,
        extra_format={
            "target_table": _get_placements_table(),
            "vendor_filter_clause": _get_vendor_filter_clause(
                label_ids, subaccount_ids
            ),
        },
    )

    total_count = snowflake.fetchall(
        sql,
        {
            **permissions_filter,
            "owner_categories": owner_categories,
            "min_followers": min_followers,
            "label_ids_for_filtering": label_ids,
            "subaccount_ids_for_filtering": subaccount_ids,
        },
    )

    if not total_count:
        return 0

    try:
        result = total_count[0][0]
    except IndexError:
        result = 0

    return result


def _parse_date_time(storefronts):
    time_format = "%a, %d %b %Y %H:%M:%S"
    for s in storefronts:
        if s.get("added"):
            s["added"] = datetime.strptime(s["added"][:-2], time_format)
    return storefronts


def _parse_storefronts_for_record(record):
    if not record[-1]:
        return record
    record = list(record)
    storefronts = _parse_date_time(json.loads(record.pop()))
    record.append(storefronts)
    return tuple(record)


def _normalize_record(record):
    """Return a normalized placement record.

    Args:
        record (dict): The item returned from datastore

    Returns:
        A normalized record dict for placement
    """
    record = _nullify_absent_fields(record)
    record = _parse_storefronts_for_record_dict(record)
    return record


def _nullify_absent_fields(record):
    """Put None in the fields which are not present in the record.

    Args:
        record (dict): The item returned from datastore

    Returns:
        A record dict will all fields in PLACEMENTS_METADATA_FIELDS
    """
    for field in PLACEMENTS_METADATA_FIELDS:
        if field not in record:
            record[field] = None
    return record


def _parse_storefronts_for_record_dict(record):
    """Parse the date in storefronts of the record.

    Args:
        record (dict): The item returned from datastore

    Returns:
        A record dict with date parsed for all storefronts
    """
    if not record.get("storefronts"):
        return record
    storefronts = _parse_date_time(record.get("storefronts"))
    record["storefronts"] = storefronts
    return record


def _parse_storefronts_for_records(records):
    """Parse string formatted json to python data types.

    Args:
        records (list): list of db results with 'storefronts' as string

    Returns:
        list of results with 'storefronts' parsed into python data types

    """
    return [_parse_storefronts_for_record(r) for r in records]


def _get_placements_table():
    """Get the table name for placements.

    Returns:
        string of table to target for playlist placements
    """
    return "RECENT_PLACEMENTS_BY_TRACK_PLAYLIST_COUNTRY_FEED_ROLLUP"  # noqa


def _get_vendor_filter_clause(label_ids, subaccount_ids):
    """Get the vendor filter clause to filter by label/subacc.

    Args:
        label_ids (array int): label ids for filtering
        subaccount_ids (array int): subaccount ids for filtering

    Returns:
        the vendor filter clause string
    """
    vendor_filter_clause = " true "
    if label_ids or subaccount_ids:
        if label_ids and subaccount_ids:
            vendor_filter_clause = (
                " product_id IN "
                "(SELECT product_id FROM dim_release "
                "WHERE labelid IN (:label_ids_for_filtering) "
                "OR subaccountid IN (:subaccount_ids_for_filtering)) "
            )
        elif label_ids:
            vendor_filter_clause = (
                " product_id IN "
                "(SELECT product_id FROM dim_release "
                "WHERE labelid IN (:label_ids_for_filtering)) "
            )
        else:
            vendor_filter_clause = (
                " product_id IN "
                "(SELECT product_id FROM dim_release "
                "WHERE subaccountid IN (:subaccount_ids_for_filtering)) "
            )
    return vendor_filter_clause