from datetime import date
from sqlalchemy import and_, func
from typing import List, Tuple

from apollo_main_db.apollo.models import ApolloMarket, ApolloMarketRegion, ApolloRegion, ApolloRegionDetail, \
    MarketRegionTypeEnum
from apollo_main_db.spotify.models import MarketRankTypeEnum, SpotifyMarketRank, SpotifyNewMusicFridayDate, \
    SpotifyNewMusicFridayPlaylistTrackHistory, SpotifyPlaylist, SpotifyPlaylistFollowers, SpotifyViewPlaylist, \
    ViewPlaylistTypeEnum
from src.constants.core import VENDOR_SPOTIFY
from src.db.base import session as db_session
from src.legacy.apollo_api.constants import REGION_ALL_CODE
from src.legacy.redis_db import keys
from src.legacy.redis_db.decorators import cache_value
from src.legacy.spotify.constants import SPOTIFY_PLAYLIST_IMAGE_URL_MASK
from src.legacy.spotify.util import get_avg_and_top_positions, get_playlists_regions
from src.legacy.spotify.vendor.base import SpotifyBase


class SpotifyNewMusicFriday(SpotifyBase):
    @cache_value(keys.SPOTIFY_NMF_AVAILABLE_DATES, cache_ttl=keys.SPOTIFY_NMF_TTL)
    def get_available_dates(self) -> List[date]:
        """Get NMF available dates.

        Returns:
            List of dates.

        """
        query = db_session.query(SpotifyNewMusicFridayDate.date).order_by(SpotifyNewMusicFridayDate.date.desc())
        return [i.date for i in query.all()]

    @cache_value(keys.SPOTIFY_NMF_PLAYLISTS, cache_ttl=keys.SPOTIFY_NMF_TTL)
    def get_playlists(self, with_name: bool = True) -> List[dict]:
        """Get NMF playlists.

        Args:
            with_name: Include playlist name in result (uses obsolete table).

        Returns:
            List of playlist data dicts.

        """
        query_field_list = [
            func.lower(SpotifyViewPlaylist.market_code).label("market"),
            SpotifyViewPlaylist.playlist_id,
            SpotifyMarketRank.rank,
            SpotifyViewPlaylist.last_date,
            SpotifyViewPlaylist.last_added_ts.label("last_added"),
            func.ifnull(SpotifyPlaylistFollowers.followers, 0).label("followers"),
        ]

        query = (
            db_session.query(SpotifyViewPlaylist)
            .join(
                SpotifyMarketRank,
                and_(
                    SpotifyMarketRank.market_code == SpotifyViewPlaylist.market_code,
                    SpotifyMarketRank.type == MarketRankTypeEnum.NMF,
                ),
            )
            .outerjoin(
                SpotifyPlaylistFollowers,
                SpotifyPlaylistFollowers.playlist_id == SpotifyViewPlaylist.playlist_id,
            )
        )

        if with_name:
            query_field_list.append(SpotifyPlaylist.name)
            query = query.join(SpotifyPlaylist, SpotifyPlaylist.id == SpotifyViewPlaylist.playlist_id)

        query = (
            query.with_entities(*query_field_list)
            .filter(SpotifyViewPlaylist.type == ViewPlaylistTypeEnum.NMF)
            .order_by(SpotifyViewPlaylist.playlist_id)
        )

        return query.all()

    def get_tracklist(self, nmf_date: date, with_image: bool = True) -> List[dict]:
        """Get NMF tracks.

        Args:
            nmf_date: Selected NMF date.
            with_image: Include playlist image in result (uses obsolete table).

        Returns:
            NMF tracks data.

        """
        query_field_list = [
            SpotifyNewMusicFridayPlaylistTrackHistory.playlist_id,
            SpotifyNewMusicFridayPlaylistTrackHistory.playlist_index,
            SpotifyNewMusicFridayPlaylistTrackHistory.track_id,
            SpotifyNewMusicFridayPlaylistTrackHistory.added,
            SpotifyNewMusicFridayPlaylistTrackHistory.isrc,
            SpotifyViewPlaylist.market_code.label("market"),
            SpotifyMarketRank.rank,
        ]

        query = (
            db_session.query(SpotifyNewMusicFridayPlaylistTrackHistory)
            .select_from(SpotifyNewMusicFridayPlaylistTrackHistory)
            .join(
                SpotifyViewPlaylist,
                and_(
                    SpotifyViewPlaylist.playlist_id == SpotifyNewMusicFridayPlaylistTrackHistory.playlist_id,
                    SpotifyViewPlaylist.type == ViewPlaylistTypeEnum.NMF,
                ),
            )
            .join(
                SpotifyMarketRank,
                and_(
                    SpotifyMarketRank.market_code == SpotifyViewPlaylist.market_code,
                    SpotifyMarketRank.type == MarketRankTypeEnum.NMF,
                ),
            )
            .join(
                SpotifyNewMusicFridayDate,
                SpotifyNewMusicFridayDate.id == SpotifyNewMusicFridayPlaylistTrackHistory.date_id,
            )
        )

        if with_image:
            query_field_list.append(SpotifyPlaylist.image.label("playlist_image"))
            query = query.join(
                SpotifyPlaylist, SpotifyPlaylist.id == SpotifyNewMusicFridayPlaylistTrackHistory.playlist_id
            )

        query = query.with_entities(*query_field_list).filter(SpotifyNewMusicFridayDate.date == nmf_date)

        result = {}
        for item in query.all():
            if item.isrc not in result:
                track_data = {"isrc": item.isrc, "trackId": item.track_id, "topTenFeatureCount": [], "playlists": []}
                result[item.isrc] = track_data
            else:
                track_data = result[item.isrc]

            track_data["playlists"].append(
                {
                    "playlistId": item.playlist_id,
                    "countryCode": item.market.lower(),
                    "addedDate": item.added,
                    "position": item.playlist_index + 1,
                    "rank": item.rank,
                    "playlistImageUrl": (item.playlist_image if with_image else f"playlist-{item.playlist_id}.jpeg"),
                }
            )

            if item.playlist_index < 10 and item.playlist_id not in track_data["topTenFeatureCount"]:
                track_data["topTenFeatureCount"].append(item.playlist_id)

        for item in result.values():
            item["playlists"] = list(sorted(item["playlists"], key=lambda x: x.pop("rank")))
            item["topTenFeatureCount"] = len(item["topTenFeatureCount"])

        return list(sorted(result.values(), key=lambda x: (len(x["playlists"]), x["topTenFeatureCount"]), reverse=True))

    def get_delayed_playlists(self, last_updated: date) -> List[date]:
        """Get delayed NMF playlists.
        Args:
            last_updated (date): Most recent update date.
        Returns:
            Delayed NMF playlists.
        """
        query = (
            db_session.query(func.lower(SpotifyViewPlaylist.market_code).label("code"))
            .filter(SpotifyViewPlaylist.type == ViewPlaylistTypeEnum.NMF)
            .filter(SpotifyViewPlaylist.last_date < last_updated)
        )
        result = [item.code for item in query.all()]
        result.sort()
        return result

    @staticmethod
    def _get_track_playlists_data(isrc: str, nmf_date: date, with_name: bool = True) -> list:
        """Get NMF track playlists query.

        Args:
            isrc: ISRC.
            nmf_date: Latest NMF date.
            with_name: Include playlist name in result (uses obsolete table).

        Returns:
            NMF track playlists query.

        """
        track_history_subquery = (
            db_session.query(
                SpotifyNewMusicFridayPlaylistTrackHistory.playlist_index.label("playlist_index"),
                SpotifyNewMusicFridayPlaylistTrackHistory.playlist_id.label("playlist_id"),
            )
            .join(
                SpotifyNewMusicFridayDate,
                SpotifyNewMusicFridayDate.id == SpotifyNewMusicFridayPlaylistTrackHistory.date_id,
            )
            .filter(
                SpotifyNewMusicFridayDate.date == nmf_date,
                SpotifyNewMusicFridayPlaylistTrackHistory.isrc == isrc,
            )
            .subquery()
        )

        query_field_list = [
            SpotifyViewPlaylist.playlist_id.label("playlist_id"),
            SpotifyMarketRank.rank.label("top_market"),
            (track_history_subquery.c.playlist_index + 1).label("position"),
            func.lower(SpotifyViewPlaylist.market_code).label("code"),
            ApolloMarketRegion.region_code,
            ApolloRegion.name.label("region_name"),
            func.IFNULL(ApolloMarketRegion.market_name_override, ApolloMarket.name).label("name"),
            SpotifyViewPlaylist.last_date,
            ApolloRegionDetail.rank.label("rank"),
            ApolloRegionDetail.name_override.label("name_override"),
        ]

        query = (
            db_session.query(SpotifyViewPlaylist)
            .join(
                SpotifyMarketRank,
                and_(
                    SpotifyMarketRank.market_code == SpotifyViewPlaylist.market_code,
                    SpotifyMarketRank.type == MarketRankTypeEnum.NMF,
                ),
            )
            .join(
                ApolloMarketRegion,
                and_(
                    ApolloMarketRegion.market_code == SpotifyViewPlaylist.market_code,
                    ApolloMarketRegion.type == MarketRegionTypeEnum.NMF,
                ),
            )
            .join(ApolloRegion, ApolloRegion.code == ApolloMarketRegion.region_code)
            .join(ApolloMarket, ApolloMarket.code == SpotifyViewPlaylist.market_code)
            .outerjoin(
                ApolloRegionDetail,
                and_(
                    ApolloRegion.code == ApolloRegionDetail.code,
                    ApolloRegionDetail.type == MarketRegionTypeEnum.NMF,
                ),
            )
            .outerjoin(
                track_history_subquery,
                SpotifyViewPlaylist.playlist_id == track_history_subquery.c.playlist_id,
            )
        )

        if with_name:
            query_field_list.append(SpotifyPlaylist.name.label("playlist_name"))
            query = query.join(SpotifyPlaylist, SpotifyPlaylist.id == SpotifyViewPlaylist.playlist_id)

        query = query.with_entities(*query_field_list).filter(SpotifyViewPlaylist.type == ViewPlaylistTypeEnum.NMF)

        return query.all()

    @cache_value(keys.SPOTIFY_NMF_TRACK_DATA, keys.SPOTIFY_NMF_TRACK_DATA_TTL, key_args_indexes=(1, 2, 3, 4, 5))
    def get_track_playlists(
        self,
        isrc: str,
        nmf_date: date,
        region_code: str,
        include_inactive_markets: bool = False,
        with_name: bool = True,
    ) -> Tuple[List[dict], List[dict], dict, int, int]:
        """Get NMF track playlists.

        Args:
            isrc: ISRC.
            nmf_date: Latest NMF date.
            region_code: Chosen region code.
            include_inactive_markets: Include markets where track was not added.
            with_name: Include playlist name in result (uses obsolete table).

        Returns:
            NMF track playlists data, regions list, chosen region data, avg position and top 10 total.

        """
        track_in_playlists = self._get_track_playlists_data(isrc, nmf_date, with_name=with_name)

        playlist_list = []
        region_list, region_data = get_playlists_regions(track_in_playlists, region_code, include_inactive_markets)

        # Filter not updated markets.
        # It happens here because we need to calculate regions total values correctly.
        track_in_playlists = [i for i in track_in_playlists if i.last_date >= nmf_date]
        playlist_region_list = [i.region_code for i in track_in_playlists]
        region_list = [i for i in region_list if i["code"] in playlist_region_list or i["code"] == REGION_ALL_CODE]

        average_position, top_10_total = get_avg_and_top_positions(track_in_playlists)

        for item in track_in_playlists:
            if not include_inactive_markets and not item.position:
                continue

            if region_code and region_code != REGION_ALL_CODE and region_code != item.region_code:
                continue

            playlist_data = item._asdict()
            playlist_data["image_url"] = SPOTIFY_PLAYLIST_IMAGE_URL_MASK.format(playlist_id=item.playlist_id)
            playlist_data["dsp"] = VENDOR_SPOTIFY
            playlist_data["region"] = playlist_data.pop("region_code")
            del playlist_data["region_name"]
            del playlist_data["last_date"]
            del playlist_data["name_override"]
            del playlist_data["rank"]

            playlist_list.append(playlist_data)

        return playlist_list, region_list, region_data, average_position, top_10_total