"""
Apollo API utilities.
"""
import json
import operator
from apollo_utils.service.exceptions import NotFound
from botocore.exceptions import ClientError
from collections import defaultdict, namedtuple
from datetime import datetime
from flask import current_app as app
from itertools import groupby
from sqlalchemy import Integer, String, and_, cast, func, literal, or_, select, union, union_all
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.orm.query import Query, aliased
from typing import Any, Dict, Iterable, List, Optional, Tuple

from apollo_main_db.apollo.models import ApolloCopies, ApolloGoVersion, ApolloMarket, ApolloMarketRegion, \
    ApolloRecentSearch, ApolloRegion, ApolloRegionDetail, ApolloUserVersionCarousel, ApolloVersionCarousel, Genre, \
    Globalization, Market, StarredContent
from apollo_main_db.apple.models import AppleMusicArtist, AppleMusicSong, AppleMusicSongArtist
from apollo_main_db.sony.models import SonyRelease
from apollo_main_db.spotify.models import SpotifyArtist, SpotifyTrack2, SpotifyTrackArtist
from src.constants.core import CA_VENDORS, JOB_CHOICES, VENDOR_APPLE, VENDOR_SPOTIFY, VENDORS, IdType, VendorBitMask, \
    VendorType
from src.constants.users.visits import VISITS_LIMIT, VisitDsp
from src.db.base import session
from src.db.users.visits import get_visits_query
from src.legacy.apollo_api.constants import TRACK_SEARCH_TYPE, SearchHistoryData
from src.legacy.apollo_api.exceptions import AlreadyStarredError, AlreadyUnstarredError, TrackNotFound
from src.legacy.apollo_api.serializers import MobileVersionsSchema, UserVersionCarouselSchema, VersionCarouselSchema
from src.legacy.apple_music.constants import APPLE_GLOBAL_MARKET, APPLE_GLOBAL_MARKET_REPLACEMENT
from src.legacy.core.boto import get_session
from src.legacy.core.clients import clients
from src.legacy.core.sony_check import check_is_sony
from src.legacy.redis_db import keys as redis_keys
from src.legacy.redis_db.decorators import cache_value, decode_str
from src.legacy.redis_db.util import add_compared_track, del_compared_track, del_compared_tracks, get_compared_tracks
from src.legacy.spotify.constants import SPOTIFY_PLAYLIST_URI_PREFIX, SPOTIFY_URI_PREFIX


VendorTracksKeys = namedtuple("VendorTracksKeys", ["ids_list", "isrc_list"])


def get_most_visited_tracks(user_id: str, market: str) -> List[dict]:
    """Get list of visited tracks by count and last date.

    Args:
        user_id (str): User ID.
        market (str): Market code.

    Returns:
        List[dict]: Subset of visit info by count/last date desc.
    """
    data = []
    visits = dict()

    # Get count of visits grouped by track_id and limited for VISITS_LIMIT
    visits[VENDOR_APPLE] = get_visits_query(user_id, VENDOR_APPLE)
    visits[VENDOR_SPOTIFY] = get_visits_query(user_id, VENDOR_SPOTIFY)

    for visit in session.query(union(*visits.values()).alias()):
        data.append(
            {
                "song_id": int(visit.instance_id) if visit.dsp_id == VisitDsp.APPLE else visit.instance_id,
                "visit_count": visit.count,
                "vendor": VisitDsp.NAME_MAPPING[visit.dsp_id],
                "last_visit_date": visit.last_visit_at,
            }
        )

    # Sort by visit count and last date descending
    data.sort(key=operator.itemgetter("visit_count", "last_visit_date"), reverse=True)

    # get most visited id's
    data = data[:VISITS_LIMIT]
    track_ids = {
        VENDOR_APPLE: [x["song_id"] for x in data if x["vendor"] == VENDOR_APPLE],
        VENDOR_SPOTIFY: [x["song_id"] for x in data if x["vendor"] == VENDOR_SPOTIFY],
    }

    # This additional queries used for performance.
    # Simple SQL-join with previous queries leads to the full table scan.
    songs_db = {
        t.id: t
        for t in (
            session.query(
                AppleMusicSong.id,
                AppleMusicSong.name,
                AppleMusicSong.artist_name,
                AppleMusicSong.artwork_url,
                AppleMusicSong.isrc,
            )
            .distinct()
            .filter(AppleMusicSong.id.in_(track_ids[VENDOR_APPLE]))
            .outerjoin(SonyRelease, SonyRelease.isrc == AppleMusicSong.isrc)
            .all()
        )
    }

    market_apple = APPLE_GLOBAL_MARKET_REPLACEMENT if market == APPLE_GLOBAL_MARKET else market

    # get artist names
    query_artists = (
        session.query(AppleMusicSongArtist.song_id, AppleMusicArtist.id, AppleMusicArtist.name)
        .select_from(AppleMusicSongArtist)
        .join(AppleMusicArtist, AppleMusicSongArtist.artist_id == AppleMusicArtist.id)
        .filter(AppleMusicSongArtist.song_id.in_(track_ids[VENDOR_APPLE]))
        .filter(AppleMusicSongArtist.storefront == market_apple)
        .filter(AppleMusicArtist.storefront == market_apple)
        .order_by(AppleMusicSongArtist.song_id, AppleMusicSongArtist.artist_order)
        .distinct()
        .all()
    )

    # get additional info from dsp
    songs_dsp = {t["id"]: t for t in clients.vendor.get_tracks(track_ids[VENDOR_APPLE], VENDOR_APPLE, market)}

    artists = {track_id: list(artists) for track_id, artists in groupby(query_artists, lambda i: i[0])}

    sony_tracks = check_is_sony(track_ids[VENDOR_SPOTIFY], id_type=IdType.ID, vendor=VendorType.SPOTIFY, market=market)

    # Append track name,artist_name, image_url to resulting data
    for visit in data:
        if visit["vendor"] == VENDOR_APPLE:
            song_id = visit["song_id"]
            visit.update(
                {
                    "is_sony": False,
                    "artists": [{"id": a[1], "name": a[2]} for a in artists[song_id]] if song_id in artists else [],
                }
            )

            if song_id in songs_db:
                song = songs_db[song_id]
                visit.update({"name": song.name, "artist_name": song.artist_name, "image_url": song.artwork_url})
            # There are some tracks that does not exists in our database. Let's mix data from DSP-API.
            if song_id in songs_dsp and "name" not in visit:
                song = songs_dsp[song_id]
                visit.update({"name": song["name"], "image_url": song["image"]})

    songs_db = {
        t.id: t
        for t in (
            session.query(
                SpotifyTrack2.id,
                SpotifyTrack2.name,
                SpotifyArtist.name.label("artist_name"),
                SonyRelease.isrc.label("sony_track"),
            )
            .outerjoin(SpotifyTrackArtist, SpotifyTrackArtist.track_id == SpotifyTrack2.id)
            .outerjoin(SpotifyArtist, SpotifyTrackArtist.artist_id == SpotifyArtist.id)
            .outerjoin(SonyRelease, SonyRelease.isrc == SpotifyTrack2.isrc)
            .filter(SpotifyTrack2.id.in_(track_ids[VENDOR_SPOTIFY]))
            .all()
        )
    }

    # get artist names
    query_artists = (
        session.query(SpotifyTrackArtist.track_id, SpotifyArtist.id, SpotifyArtist.name)
        .filter(SpotifyTrackArtist.track_id.in_(track_ids[VENDOR_SPOTIFY]))
        .filter(SpotifyTrackArtist.artist_id == SpotifyArtist.id)
        .order_by(SpotifyTrackArtist.track_id, SpotifyTrackArtist.order)
        .all()
    )

    # get additional info from dsp
    songs_dsp = {t["id"]: t for t in clients.vendor.get_tracks(track_ids[VENDOR_SPOTIFY], VENDOR_SPOTIFY, market)}

    artists = {track_id: list(artists) for track_id, artists in groupby(query_artists, lambda i: i[0])}

    # Append track name,artist_name to resulting data
    for visit in data:
        if visit["vendor"] == VENDOR_SPOTIFY:
            song_id = visit["song_id"]
            visit.update(
                {
                    "is_sony": song_id in sony_tracks,
                    "artists": [{"id": a[1], "name": a[2]} for a in artists[song_id]] if song_id in artists else [],
                }
            )
            if song_id in songs_db:
                song = songs_db[song_id]
                visit.update({"name": song.name, "artist_name": song.artist_name})
            # There are some tracks that does not exists in our database. Let's mix data from DSP-API.
            if song_id in songs_dsp:
                song = songs_dsp[song_id]
                visit["image_url"] = song["image"]
                if "name" not in visit:
                    visit.update({"name": song["name"], "artists": song.get("artists", [])})
    return data


def add_visited_track(user_id: str, spotify_id: str = "", apple_id: int = None) -> bool:
    """Add log entry of track page visit to SQS.

    Args:
        user_id: ID of the user who visited the page.
        spotify_id: ID of the track if it is a Spotify track.
        apple_id: ID of the track if it is an Apple Music track.

    Returns:
        True if data has been added to SQS. False on errors.

    Raises:
        ValueError: If track ID is not provided or both spotify_id and apple_id are provided.

    """
    if not (bool(spotify_id) ^ bool(apple_id)):
        raise ValueError

    new_entry = {
        "created_at": datetime.utcnow(),
        "user_id": user_id,
        "spotify_id": spotify_id,
        "apple_id": int(apple_id) if apple_id else None,
    }

    sqs_client = get_session().client("sqs")
    try:
        sqs_client.send_message(
            QueueUrl=app.config["TRACK_VISITS_MESSAGE_QUEUE_URL"], MessageBody=json.dumps(new_entry, default=str)
        )
    except ClientError as e:
        app.logger.error(e)
        return False

    return True


def get_starred_tracks_query(user_id: str, isrc_list: List[str] = None) -> Query:
    """Return base starred tracks query for current user."""
    starred_tracks_query = session.query(
        StarredContent.uri.label("uri"),
        StarredContent.isrc.label("isrc"),
        (func.replace(StarredContent.uri, SPOTIFY_URI_PREFIX, "")).label("track_id"),
        func.IF(StarredContent.uri.startswith(SPOTIFY_URI_PREFIX), VENDOR_SPOTIFY, VENDOR_APPLE).label("vendor"),
    ).filter(
        StarredContent.user_id == user_id,
        StarredContent.isrc.isnot(None),
        StarredContent.isrc.op("regexp")(r"^[A-Za-z]{2}[A-Za-z0-9]{3}[0-9]{7}$"),
    )

    if isrc_list:
        starred_tracks_query = starred_tracks_query.filter(StarredContent.isrc.in_(isrc_list))

    return starred_tracks_query.group_by(StarredContent.isrc)


def get_starred_tracks_by_streams_and_name_query(base_query: Query, streams_query: Query, names_query: Query) -> Query:
    """Return starred tracks query joined with streams and track names queries with ordering."""
    base_query = base_query.subquery()
    fields = [base_query.c.uri, base_query.c.isrc, base_query.c.track_id, base_query.c.vendor]
    outer_joins = []
    order_by = []
    if streams_query.c:
        fields.append(streams_query.c.streams.label("streams"))
        outer_joins.append(streams_query)
        order_by.append(streams_query.c.streams.desc())
    if names_query.c:
        fields.append(names_query.c.name.label("name"))
        outer_joins.append(names_query)
        order_by.append(names_query.c.name.asc())
    query = session.query(*fields)
    for q in outer_joins:
        query = query.outerjoin(q, q.c.isrc == base_query.c.isrc)
    if order_by:
        query = query.order_by(*order_by)
    return query


def get_streams_subquery(isrc_to_streams: Dict[str, int]) -> Query:
    """Return temporal subquery with streams data."""
    rows = [(isrc, streams) for isrc, streams in isrc_to_streams.items()]
    statements = [
        select([cast(literal(i), String).label("isrc"), cast(literal(s), Integer).label("streams")])
        if index == 0
        else select([literal(i), literal(s)])  # no type cast
        for index, (i, s) in enumerate(rows)
    ]
    return union_all(*statements).alias("streams_tbl")


def get_names_subquery(extra_data: Dict[str, Any]) -> Query:
    """Return temporal subquery with track names data."""
    rows = [(isrc, data.get("name", "")) for isrc, data in extra_data.items()]
    statements = [
        select([cast(literal(i), String).label("isrc"), cast(literal(n), String).label("name")])
        if index == 0
        else select([literal(i), literal(n)])  # no type cast
        for index, (i, n) in enumerate(rows)
    ]
    return union_all(*statements).alias("names_tbl")


def get_vendor_extra_starred_data(spotify_keys: VendorTracksKeys, apple_keys: VendorTracksKeys, market: str) -> Dict:
    """
    Retrieve tracks data from Apple and Spotify for specific market.

    Args:
        spotify_keys (VendorTracksKeys): Spotify tracks ISRCs and IDs.
        apple_keys (VendorTracksKeys): Apple tracks ISRCs and IDs.
        market (str): Market string value.

    Returns:
        Dict: Tracks data.
    """
    spotify_data, spotify_missing_isrcs = get_spotify_tracks_data(spotify_keys, market)
    apple_data, apple_missing_isrcs = get_apple_tracks_data(apple_keys, market)
    spotify_db_data = get_db_spotify_tracks_data(spotify_missing_isrcs)
    apple_db_data = get_db_apple_tracks_data(apple_missing_isrcs)

    data = {**spotify_data, **apple_data, **spotify_db_data, **apple_db_data}
    for k, v in data.items():
        v.update({"available": k not in apple_missing_isrcs + spotify_missing_isrcs})
    return {**spotify_data, **apple_data, **spotify_db_data, **apple_db_data}


def get_db_spotify_tracks_data(isrc_list: List[str]) -> Dict:
    """
    Retrieve Spotify tracks data from DB.

    Args:
        isrc_list (List[str]): List of ISRCs.

    Returns:
        Dict: Spotify tracks data and list of missing tracks ISRC data.
    """
    result = {}

    if not isrc_list:
        return result

    query = (
        session.query(
            SpotifyTrack2.id.label("track_id"),
            SpotifyTrack2.name.label("track_name"),
            func.upper(SpotifyTrack2.isrc).label("isrc"),
            SpotifyArtist.name.label("artist_name"),
            SpotifyArtist.id.label("artist_id"),
        )
        .outerjoin(SpotifyTrackArtist, SpotifyTrackArtist.track_id == SpotifyTrack2.id)
        .outerjoin(SpotifyArtist, SpotifyTrackArtist.artist_id == SpotifyArtist.id)
        .filter(SpotifyTrack2.isrc.in_(isrc_list))
        .group_by(SpotifyTrack2.isrc)
    )

    for q in query.all():
        artist_data = {"id": q.artist_id, "name": q.artist_name}
        track_data = {"name": q.track_name, "isrc": q.isrc, "artists": [artist_data], "vendor": VENDOR_SPOTIFY}
        result[q.isrc] = track_data

    return result


def get_db_apple_tracks_data(isrc_list: List[str]) -> Dict:
    """
    Retrieve Apple tracks data from DB.

    Args:
        isrc_list (List[str]): List of ISRCs.

    Returns:
        Dict: Apple tracks data and list of missing tracks ISRC data.
    """
    result = {}

    if not isrc_list:
        return result

    query = (
        session.query(
            AppleMusicSong.id.label("track_id"),
            AppleMusicSong.name.label("track_name"),
            AppleMusicSong.artist_id,
            AppleMusicSong.artist_name,
            func.upper(AppleMusicSong.isrc).label("isrc"),
        )
        .filter(AppleMusicSong.isrc.in_(isrc_list))
        .group_by(AppleMusicSong.isrc)
    )

    for q in query.all():
        artist_data = {"id": q.artist_id, "name": q.artist_name}
        track_data = {
            "name": q.track_name,
            "isrc": q.isrc,
            "artists": [artist_data],
            "vendor": VENDOR_APPLE,
            "id": q.track_id,
        }
        result[q.isrc] = track_data

    return result


def get_spotify_tracks_data(spotify_keys: VendorTracksKeys, market: str) -> Tuple[Dict, List]:
    """
    Retrieve from DSP Spotify tracks data.

    Args:
        market (str): Country code value.
        spotify_keys (VendorTracksKeys): Spotify IDs and ISRCs values.

    Returns:
        Tuple[Dict, List]: Tracks data per ISRC value and missing data tracks ISRC values.
    """
    result_data = {}
    spotify_response_by_ids = clients.vendor.get_tracks(spotify_keys.ids_list, VENDOR_SPOTIFY, market)
    result_data.update({track["isrc"].upper(): track for track in spotify_response_by_ids if track["isrc"] is not None})
    missing_isrc_list = list(set(spotify_keys.isrc_list) - set(result_data.keys()))

    if missing_isrc_list:
        apple_response_by_isrcs = clients.vendor.get_tracks(missing_isrc_list, VENDOR_APPLE, market, by_isrc=True)
        result_data.update(
            {track["isrc"].upper(): track for track in apple_response_by_isrcs if track["isrc"] is not None}
        )
        missing_isrc_list = list(set(spotify_keys.isrc_list) - set(result_data.keys()))

    return result_data, missing_isrc_list


def get_apple_tracks_data(apple_keys: VendorTracksKeys, market: str) -> Tuple[Dict, List]:
    """
    Retrieve from DSP Apple tracks data.

    Args:
        market (str): Country code value.
        apple_keys (VendorTracksKeys): Spotify IDs and ISRCs values.

    Returns:
        Tuple[Dict, List]: Tracks data per ISRC value and missing data tracks ISRC values..
    """
    result_data = {}
    apple_response = clients.vendor.get_tracks(apple_keys.isrc_list, VENDOR_APPLE, market, by_isrc=True)
    result_data.update({track["isrc"].upper(): track for track in apple_response if track["isrc"] is not None})
    missing_isrc_list = list(set(apple_keys.isrc_list) - set(result_data.keys()))

    if missing_isrc_list:
        spotify_ids_map = _get_vendor_ids_by_isrc(missing_isrc_list, vendor=VENDOR_SPOTIFY, limit=1)
        spotify_ids = [val[0] for val in spotify_ids_map.values() if val]
        spotify_response = clients.vendor.get_tracks(spotify_ids, VENDOR_SPOTIFY, market)
        result_data.update({track["isrc"].upper(): track for track in spotify_response if track["isrc"] is not None})
        missing_isrc_list = list(set(apple_keys.isrc_list) - set(result_data.keys()))

    return result_data, missing_isrc_list


def get_related_search_keys(
    vendor_to_chunk_keys: Dict[str, VendorTracksKeys], extra_data: Dict[str, Any]
) -> Dict[str, VendorTracksKeys]:
    """Returns keys by vendors to search related data in another vendors for these keys.
    Excludes from vendor_to_chunk_keys those keys for which extra data was already taken from another
    (not original) vendor.
    Args:
        vendor_to_chunk_keys: dict of vendor to keys (id, isrc).
        extra_data: dict of isrc: track_data, got for keys on the first search.
    """
    vendor_to_keys = {vendor: VendorTracksKeys([], []) for vendor in VENDORS}
    vendor_to_chunk_isrc_set = {vendor: set(keys.isrc_list) for vendor, keys in vendor_to_chunk_keys.items()}
    for isrc, track_data in extra_data.items():
        vendor = track_data.get("vendor")
        if vendor in VENDORS and isrc in vendor_to_chunk_isrc_set[vendor]:
            # getting particular isrc with another vendor in extra_data means metadata was already found in related
            # vendor, there is no point in searching again.
            keys = vendor_to_keys[vendor]
            keys.isrc_list.append(isrc)
            track_id = track_data.get("id")
            if track_id:
                keys.ids_list.append(track_id)
    return vendor_to_keys


def get_related_vendors_data(
    vendor_to_keys: Dict[str, VendorTracksKeys], market: str, vendors: tuple = CA_VENDORS
) -> Dict[str, Dict[str, str]]:
    """Returns id for passed keys (id and isrc) and market in another vendors.

    :param vendor_to_keys: dict of vendor: vendor's keys (id and isrc).
    :param market: market to get data for.
    :param vendors: tuple of vendors to get data for.
    :return: {isrc1:
                     {
                        vendor1: id of track in vendor1,
                        ...
                     },
                     ...
             }
    """

    def get_id_list(isrc_list: List[str], _vendor: str) -> List[str]:
        isrc_to_ids = _get_vendor_ids_by_isrc(isrc_list, vendor=_vendor, limit=1)
        return [val[0] for val in isrc_to_ids.values() if val]

    vendors_set = set(vendors)
    isrc_to_data = defaultdict(dict)
    for vendor, keys in vendor_to_keys.items():
        related_vendors = vendors_set - {vendor}
        for related_vendor in related_vendors:
            by_isrc = related_vendor == VENDOR_APPLE
            data_list = clients.vendor.get_tracks(
                keys.isrc_list if by_isrc else get_id_list(keys.isrc_list, related_vendor),
                related_vendor,
                market,
                by_isrc=by_isrc,
            )
            for isrc_item in data_list:
                isrc = isrc_item.get("isrc")
                if isrc is not None:
                    isrc_to_data[isrc.upper()][related_vendor] = {
                        "id": isrc_item.get("id") or "",
                        "album_id": next(iter(isrc_item.get("album_ids", [])), ""),
                    }
    return isrc_to_data


def add_starred_track(user_id: str, uri: str, isrc: str, check: bool = True):
    """Add starred tracks for user.

    Args:
        user_id (str): User ID.
        uri (str): Starred track URI.
        isrc (str): Track ISRC.
        check (bool): if True firstly checks if track is already starred.

    """
    if check and check_track_is_starred(user_id, isrc):
        raise AlreadyStarredError
    session.add(StarredContent(user_id=user_id, isrc=isrc, uri=uri))
    session.commit()


def check_track_is_starred(user_id: str, isrc: str) -> bool:
    """Add starred tracks for user.

    Args:
        user_id (str): User ID.
        isrc (str): Track ISRC.

    Returns:
          True if track is starred.
    """
    return session.query(StarredContent.user_id).filter_by(isrc=isrc, user_id=user_id).scalar() is not None


def del_starred_track(user_id: str, isrc: str):
    """Remove starred track for user.

    Args:
        user_id (str): User ID.
        isrc (str): Starred track ISRC.

    """
    rows_affected = (
        session.query(StarredContent).filter_by(user_id=user_id, isrc=isrc).delete(synchronize_session=False)
    )
    if not rows_affected:
        raise AlreadyUnstarredError
    session.commit()


def _get_vendor_ids_by_isrc(isrc_list: list, vendor: str, limit: int or None = None) -> Dict:
    """Get all or specific vendor IDs by isrc.

    Args:
        isrc_list (list): Track isrc.
        vendor (str): Vendor code.
        limit (int or None): Optional result count.

    Returns:
        Dict: Dict of vendor IDs for each isrc.

    Raises:
        NotImplemented: Vendor unknown.

    """
    if vendor == VENDOR_APPLE:
        query = session.query(AppleMusicSong.id, AppleMusicSong.isrc).filter(AppleMusicSong.isrc.in_(isrc_list))
    elif vendor == VENDOR_SPOTIFY:
        query = session.query(SpotifyTrack2.id, SpotifyTrack2.isrc).filter(SpotifyTrack2.isrc.in_(isrc_list))
    else:
        raise NotImplementedError()

    data = query.all()
    result = defaultdict(lambda: set())

    for item in data:
        _id, isrc = item
        ids_num = len(result[isrc])

        if limit is None or (ids_num + 1) <= limit:
            result[isrc].add(str(_id))
            continue

    return {k: list(v) for k, v in result.items()}


def _get_isrc_by_id(**kwargs: dict) -> str:
    """Get ISRC by vendor ID.

    Args:
        kwargs (dict): Vendor IDs.

    Returns:
        str: ISRC.

    Raises:
        ValueError: All IDs are None.
        exceptions.NotFound: Track not found.

    """
    if "spotify_id" in kwargs and kwargs["spotify_id"]:
        isrc_row = session.query(SpotifyTrack2.isrc).filter(SpotifyTrack2.id == kwargs["spotify_id"]).first()
    elif "apple_id" in kwargs and kwargs["apple_id"]:
        isrc_row = session.query(AppleMusicSong.isrc).filter(AppleMusicSong.id == kwargs["apple_id"]).first()
    else:
        raise ValueError()
    if not isrc_row:
        raise NotFound()
    return isrc_row[0]


def get_job_category_name_by_id(category_id: int):
    """Retrieve job category name by its id value.

    Arguments:
        category_id (int): Job category id

    Raises:
        ValueError: Unexpected category_d value

    Returns:
        str: Job category name
    """
    category_choices = dict(JOB_CHOICES)

    for k, v in category_choices.items():
        if v == category_id:
            return k

    raise ValueError(f"'category_id' unexpected value: {category_id}")


def add_track_to_comparison(user_id: str, data: dict) -> dict:
    """Add track to comparison.

    Args:
        user_id (str): User ID.
        data (dict): Request arguments.

    Returns:
        dict: Track data.

    """
    market = data["market"]

    if data["apple_id"]:
        apple_track = (
            session.query(AppleMusicSong.isrc)
            .filter(AppleMusicSong.id == data["apple_id"])
            .order_by(AppleMusicSong.storefront)
            .first()
        )
        if not apple_track:
            raise TrackNotFound("Apple Music track with ID {} is not found in the database.".format(data["apple_id"]))
        if apple_track.isrc:
            data["isrc"] = apple_track.isrc
            spotify_track = (
                session.query(SpotifyTrack2.id)
                .filter(SpotifyTrack2.isrc == data["isrc"])
                .order_by(SpotifyTrack2.id)
                .first()
            )
            if spotify_track:
                data["spotify_id"] = spotify_track.id
    else:
        spotify_id = data["spotify_id"]

        spotify_track = session.query(SpotifyTrack2.isrc).filter(SpotifyTrack2.id == spotify_id).first()

        if not spotify_track or not spotify_track.isrc:
            spotify_track = clients.vendor.get_track(spotify_id, VENDOR_SPOTIFY, market, raw_results=True)

            if not spotify_track:
                raise TrackNotFound("Spotify track with ID {} is not found in the database.".format(spotify_id))

            track_isrc = spotify_track.get("external_ids", {}).get("isrc", data.get("isrc"))

            insert_stmt = insert(SpotifyTrack2).values(
                TrackId=spotify_track["id"],
                Name=spotify_track.get("name", ""),
                Duration=spotify_track.get("duration_ms", 0) // 1000,
                ISRC=track_isrc,
                Popularity=spotify_track.get("popularity", 0),
            )
            on_conflict_stmt = insert_stmt.on_duplicate_key_update(ISRC=insert_stmt.inserted.ISRC)
            session.execute(on_conflict_stmt)
            session.commit()

            if track_isrc:
                data["isrc"] = track_isrc
        else:
            if spotify_track.isrc:
                data["isrc"] = spotify_track.isrc

        if data.get("isrc"):
            apple_track = (
                session.query(AppleMusicSong.id)
                .filter(AppleMusicSong.isrc == data["isrc"])
                .order_by(AppleMusicSong.storefront)
                .first()
            )

            if apple_track:
                data["apple_id"] = apple_track.id

    del data["market"]
    add_compared_track(user_id, data)

    set_id_sony_comparison_track(data, market)

    return data


def set_id_sony_comparison_track(data: Dict, market):
    """Set is_sony flag to adding track response.

    Args:
        data: Track data.
        market: Market code.
    """
    data["is_sony"] = False
    spotify_id = data["spotify_id"]
    if spotify_id:
        sony_ids = check_is_sony([spotify_id], id_type=IdType.ID, vendor=VendorType.SPOTIFY, market=market)
        if spotify_id in sony_ids:
            data["is_sony"] = True


def get_comparison_tracks(user_id: str, market: str) -> List[dict]:
    """Get a list of comparison tracks with Sony/Non Sony flag.

    Now it works correctly only for Spotify, but it is ok as we can add only Spotify tracks to comparison
    as there is Spotify only track search.

    Args:
        user_id (str): User ID.
        market (str): Market code.

    Returns:
        List[dict]: Comparison tracks data with Sony/Non Sony flag.

    """
    tracks = get_compared_tracks(user_id)

    if not tracks:
        return []

    sony_ids = []
    tracks_mapping = {}

    spotify_ids_to_check = [track["spotify_id"] for track in tracks if track["spotify_id"]]

    if spotify_ids_to_check:
        sony_ids = check_is_sony(spotify_ids_to_check, id_type=IdType.ID, vendor=VendorType.SPOTIFY, market=market)

        tracks_mapping = {
            track["id"]: track
            for track in clients.vendor.get_tracks(spotify_ids_to_check, VENDOR_SPOTIFY, market, raw_results=True)
        }

    for track in tracks:
        spotify_id = track["spotify_id"]
        track["is_sony"] = spotify_id in sony_ids
        track["data"] = tracks_mapping.get(spotify_id)

    return tracks


def check_comparison_tracks(user_id: str, market: str) -> List[dict]:
    """Check comparison tracks are Sony, delete if not, return a list of all tracks with the flag.

    Args:
        user_id (str): User ID.
        market (str): Market code.

    Returns:
        List[dict]: Comparison track list.

    """
    tracks = get_comparison_tracks(user_id, market)

    non_sony_tracks = [t for t in tracks if not t["is_sony"]]
    if non_sony_tracks:
        apple_to_delete = [t["apple_id"] for t in non_sony_tracks if t["apple_id"]]
        spotify_to_delete = [t["spotify_id"] for t in non_sony_tracks if t["spotify_id"]]
        del_compared_tracks(user_id=user_id, spotify_ids=spotify_to_delete, apple_ids=apple_to_delete)

    return tracks


def delete_comparison_track(user_id: str, data: dict):
    """Delete a track from comparison.

    Args:
        user_id (str): User ID.
        data (dict): Request data.

    """
    del_compared_track(user_id, **data)


def separate_vendor_keys(starred_tracks: Iterable) -> Dict[str, VendorTracksKeys]:
    """Separate starred query to separate dicts with ids and isrc based on vendor.

    Args:
        starred_tracks (List): Starred tracks list.

    Returns:
        dict of vendor: [List[str], List[str] - (ids and isrc values).

    """
    vendor_to_keys = {vendor: VendorTracksKeys([], []) for vendor in VENDORS}

    for track in starred_tracks:
        if not track.track_id or not track.isrc or not (track.vendor in VENDORS):
            continue
        keys = vendor_to_keys[track.vendor]
        keys.ids_list.append(track.track_id)
        keys.isrc_list.append(track.isrc)
    return vendor_to_keys


@cache_value(redis_keys.APOLLO_MOBILE_VERSION, redis_keys.APOLLO_MOBILE_VERSION_TTL)
def get_latest_mobile_version(os: str) -> dict:
    """Return latest published ApolloGo version for required OS.

    Args:
        os (str): OS value (e.g. iOS or Android).

    Returns:
        dict: Deserialized ApolloGoVersion obj data.

    """
    version_obj = (
        session.query(ApolloGoVersion.os, ApolloGoVersion.version, ApolloGoVersion.created_at)
        .filter(ApolloGoVersion.os == os)
        .order_by(ApolloGoVersion.id.desc())
        .first()
    )

    return MobileVersionsSchema().dump(version_obj)


@cache_value(redis_keys.APOLLO_VERSION_COPIES, redis_keys.APOLLO_VERSION_COPIES_TTL)
def get_os_version_copies(version: str, os: str) -> dict:
    """Return latest copies data for specified os and version.

    Args:
        os (str): OS value (e.g. iOS or Android).
        version (str): Version value in semver format.

    Returns:
        dict: Deserialized from JSON string copies data.

    """
    result = {}
    copies_obj = (
        session.query(ApolloCopies.data)
        .filter(ApolloCopies.version == version, ApolloCopies.os == os)
        .order_by(ApolloCopies.id.desc())
        .first()
    )

    if copies_obj and copies_obj.data:
        result = decode_str(copies_obj.data)

    return result


def _get_market_id(market_id: int) -> Tuple[Optional[int], Optional[int]]:
    result = (
        session.query(Market.market_id, ApolloMarket.id)
        .select_from(Market)
        .outerjoin(ApolloMarket, ApolloMarket.code == Market.market_name)
        .filter(Market.market_id == market_id)
        .first()
    )
    if not result:
        return None, None
    return result


def _save_market(market_data: dict, market_id: Optional[int]) -> Market:
    market = Market()
    for key, value in market_data.items():
        setattr(market, key, value)
    if market_id:
        session.merge(market)
    else:
        session.add(market)
    session.commit()

    # for create copy genres and globalization from global
    if not market_id:
        fallback_market = get_fallback_market()
        if fallback_market:
            fallback_market_id, fallback_language_code = fallback_market
            copy_genres(market.market_id, fallback_market_id)
            copy_globalization(market.language_code, fallback_language_code)

    return market


def _save_apollo_market(market_data: dict, apollo_market_id: Optional[int]) -> ApolloMarket:
    """Upsert new Apollo market from old market data.

    Args:
        market_data: Old market (app instance) data.
        apollo_market_id: Market ID.

    Returns:
        New market object.

    """
    apollo_market = ApolloMarket()

    field_mapping = {
        "code": "market_name",
        "name": "market_fullname",
        "full_name": "market_fullname",
        "is_primary": "active",
    }
    for dest_key, orig_key in field_mapping.items():
        if orig_key in market_data:
            setattr(apollo_market, dest_key, market_data[orig_key])

    apollo_market.id = apollo_market_id

    apollo_market.vendors = VendorBitMask.EMPTY.value
    for vendor in market_data.get("services", "").split(","):
        if vendor:
            apollo_market.vendors |= VendorBitMask.get_mask(vendor).value

    if apollo_market_id:
        session.merge(apollo_market)
    else:
        session.add(apollo_market)
    session.commit()

    return apollo_market


def save_market(data: dict) -> Market:
    """Create or update market, copy genres and globalization setting for new.

    Args:
        data: Market data.

    Returns:
        Created/updated market.

    """
    market_id, apollo_market_id = None, None
    if "market_id" in data:
        market_id, apollo_market_id = _get_market_id(data["market_id"])
        if not market_id:
            raise NotFound

    market = _save_market(data, market_id)
    _save_apollo_market(data, apollo_market_id)

    return market


def get_fallback_market() -> Tuple[int, str]:
    """Get fallback (global) market.

    Returns:
        Market ID and language code.

    """
    return (
        session.query(Market.market_id, Market.language_code)
        .filter(Market.fallback.is_(True))
        .filter(Market.workout.is_(False))
        .first()
    )


def copy_genres(market_id: int, fallback_market_id: int):
    """Copy genres from fallback (global) market to a new one.

    Args:
        market_id: Copy to market.
        fallback_market_id: Copy from market.

    """
    aliased_genre = aliased(Genre)
    fallback_genres = (
        session.query(
            literal(str(market_id)).label("intApplicationInstanceID"),
            aliased_genre.name,
            aliased_genre.last_fm_tag,
            aliased_genre.active,
            aliased_genre.category_id,
        )
        .filter(aliased_genre.market_id == fallback_market_id)
        .subquery()
    )

    insert_query = insert(Genre).from_select(
        [
            fallback_genres.c.intApplicationInstanceID,
            fallback_genres.c.strName,
            fallback_genres.c.strLastFmTag,
            fallback_genres.c.blnActive,
            fallback_genres.c.intCategoryID,
        ],
        fallback_genres,
    )

    session.execute(insert_query)
    session.commit()


def copy_globalization(language_code: str, fallback_language_code: str):
    """Copy globalization settings from fallback language to a new one.

    Args:
        language_code: Copy to language.
        fallback_language_code: Copy from language.

    """
    globalization_exists = session.query(
        session.query(Globalization).filter(Globalization.language_code == language_code).exists()
    ).scalar()

    if globalization_exists:
        return

    aliased_globalization = aliased(Globalization)
    fallback_globalization = (
        session.query(
            literal(language_code).label("strCulture"), aliased_globalization.key, aliased_globalization.value
        )
        .filter(aliased_globalization.language_code == fallback_language_code)
        .subquery()
    )

    insert_query = insert(Globalization).from_select(
        [fallback_globalization.c.strCulture, fallback_globalization.c.strKey, fallback_globalization.c.strValue],
        fallback_globalization,
    )

    session.execute(insert_query)
    session.commit()


def delete_market(market_id: int):
    """Delete market.

    Args:
        market_id: Market ID.

    """
    (
        session.query(ApolloMarket)
        .filter(ApolloMarket.code == Market.market_name)
        .filter(Market.market_id == market_id)
        .delete(synchronize_session=False)
    )
    rows_affected = session.query(Market).filter(Market.market_id == market_id).delete(synchronize_session=False)
    session.query(Genre).filter(Genre.market_id == market_id).delete(synchronize_session=False)
    session.commit()

    return rows_affected


@cache_value(redis_keys.APOLLO_MARKETS, redis_keys.APOLLO_MARKETS_TTL, key_args_indexes=(0, 1))
def get_markets_data(extended: bool, region_type: str, mapping_only: bool) -> List[dict]:
    """Return Markets objects from tblApolloMarket table.

    Args:
        extended: Show all markets or isPrimary only.
        region_type: Market to region mapping type.
        mapping_only: Return only markets that participate in region_type mapping or all (other region for not present).

    Returns:
        dict: Deserialized ApolloMarkets objects data.

    """
    field_list = [ApolloMarket.id, ApolloMarket.code, ApolloMarket.full_name]
    query = session.query(ApolloMarket).order_by(ApolloMarket.name)

    if not extended:
        query = query.filter(ApolloMarket.is_primary.is_(True))

    if region_type:
        query = (
            query.outerjoin(
                ApolloMarketRegion,
                and_(ApolloMarketRegion.type == region_type, ApolloMarketRegion.market_code == ApolloMarket.code),
            )
            .join(ApolloRegion, ApolloRegion.code == func.IFNULL(ApolloMarketRegion.region_code, "other"))
            .outerjoin(
                ApolloRegionDetail,
                and_(ApolloRegionDetail.type == region_type, ApolloRegionDetail.code == ApolloRegion.code),
            )
        )

        if mapping_only:
            query = query.filter(ApolloMarketRegion.type.isnot(None))

        field_list.extend(
            [
                func.IFNULL(ApolloMarketRegion.market_name_override, ApolloMarket.name).label("name"),
                ApolloRegion.code.label("region_code"),
                func.IFNULL(ApolloRegionDetail.name_override, ApolloRegion.name).label("region_name"),
                ApolloRegionDetail.rank.label("region_rank"),
            ]
        )
    else:
        field_list.append(ApolloMarket.name)

    query = query.with_entities(*field_list)

    result = []
    for item in query:
        result_item = item._asdict()
        if region_type:
            result_item["region"] = {
                "code": result_item.pop("region_code"),
                "name": result_item.pop("region_name"),
                "rank": result_item.pop("region_rank"),
            }
        result.append(result_item)

    return result


@cache_value(redis_keys.APOLLO_USER_CAROUSEL, redis_keys.APOLLO_USER_CAROUSEL_TTL)
def get_user_viewed_carousel_data(user_id: str, carousel_id: int) -> dict or None:
    """Retrieve viewed carousel data for user.

    Args:
        user_id (str): User ID
        carousel_id (int): ApolloVersionCarousel PK value.

    Returns:
        dict or None: Dict with user version carousel data or None.
    """
    user_viewed_carousel = (
        session.query(ApolloUserVersionCarousel)
        .filter(ApolloUserVersionCarousel.user_id == user_id, ApolloUserVersionCarousel.carousel_id == carousel_id)
        .first()
    )
    return UserVersionCarouselSchema().dump(user_viewed_carousel) if user_viewed_carousel else None


@cache_value(redis_keys.APOLLO_VERSION_CAROUSEL, redis_keys.APOLLO_VERSION_CAROUSEL_TTL)
def get_version_carousel(os: str, version: str) -> dict or None:
    """Retrieve carousel version object for specific os.

    Args:
        os (str): OS value.
        version (str): Semver formatted version string.

    Returns:
        dict or None: Dict with carousel data or None.
    """
    carousel_version = (
        session.query(ApolloVersionCarousel)
        .filter(ApolloVersionCarousel.version == version, ApolloVersionCarousel.os == os)
        .first()
    )
    return VersionCarouselSchema().dump(carousel_version) if carousel_version else None


def get_user_viewed_carousel_fields_dict(user_id: str, carousel_id: int) -> dict:
    """Build fields dict value for Model creation.

    Args:
        user_id (str): User ID
        carousel_id (int): ApolloVersionCarousel PK value.

    Returns:
        dict: ApolloUserVersionCarousel model fields dict.
    """
    dt_now = datetime.now()
    return dict(user_id=user_id, carousel_id=carousel_id, created_at=dt_now, updated_at=dt_now)


def get_apollo_recent_search_filters(user_id: str, type: str, vendors: List[str]) -> list:
    """Retrieve a list of filter conditions for query.

    Args:
        user_id (str): User identifier.
        type (str): Items type value (track or playlist)
        vendors (List[str]): Vendors list.

    Returns:
        list: List of filter conditions for query.
    """
    filters = [ApolloRecentSearch.user_id == user_id]

    if type == SearchHistoryData.TRACK:
        filters.append(ApolloRecentSearch.search_type == TRACK_SEARCH_TYPE)
        if vendors and VENDOR_SPOTIFY in vendors:
            filters.append(ApolloRecentSearch.source == SearchHistoryData.SOURCE_SPOTIFY)
        elif vendors and VENDOR_APPLE in vendors:
            filters.append(ApolloRecentSearch.source == SearchHistoryData.SOURCE_APPLE)
    elif type == SearchHistoryData.PLAYLIST:
        if not vendors:
            filters.append(
                or_(
                    ApolloRecentSearch.uri.startswith(SPOTIFY_PLAYLIST_URI_PREFIX),
                    ApolloRecentSearch.uri.startswith("pl."),
                )
            )
        elif VENDOR_APPLE in vendors:
            filters.append(
                and_(
                    ApolloRecentSearch.source == SearchHistoryData.SOURCE_APPLE,
                    ApolloRecentSearch.uri.startswith("pl."),
                )
            )
        elif VENDOR_SPOTIFY in vendors:
            filters.append(
                and_(
                    ApolloRecentSearch.source == SearchHistoryData.SOURCE_SPOTIFY,
                    ApolloRecentSearch.uri.startswith(SPOTIFY_PLAYLIST_URI_PREFIX),
                )
            )

    return filters


def get_track_id_to_track_info(vendor: str, track_ids: List[str]) -> Dict:
    """Retrieve track_id to track information mapping

    Args:
        vendor (str): Vendor identifier (spotify or apple).
        track_ids (List[str]): List of tracks_id(s).

    Returns:
        Dict: track_id to track information mapping

    Raises:
        APIInvalidResponse: If there is no realisation for provided vendor.
    """
    if vendor == VENDOR_APPLE:
        query = (
            session.query(
                AppleMusicSong.id,
                AppleMusicSong.isrc,
                AppleMusicSong.name,
                AppleMusicSong.artist_name,
                AppleMusicSong.artwork_url,
                AppleMusicSong.isrc,
            )
            .distinct()
            .filter(AppleMusicSong.id.in_(track_ids))
            .outerjoin(SonyRelease, SonyRelease.isrc == AppleMusicSong.isrc)
        )
    elif vendor == VENDOR_SPOTIFY:
        query = (
            session.query(
                SpotifyTrack2.id,
                SpotifyTrack2.isrc,
                SpotifyTrack2.name,
                SpotifyArtist.name.label("artist_name"),
                SonyRelease.isrc.label("sony_track"),
            )
            .outerjoin(SpotifyTrackArtist, SpotifyTrackArtist.track_id == SpotifyTrack2.id)
            .outerjoin(SpotifyArtist, SpotifyTrackArtist.artist_id == SpotifyArtist.id)
            .outerjoin(SonyRelease, SonyRelease.isrc == SpotifyTrack2.isrc)
            .filter(SpotifyTrack2.id.in_(track_ids))
        )
    else:
        raise NotImplementedError()

    result = {track.id: track._asdict() for track in query.all()}

    return result