from apollo_utils.core.constants import MARKET_WORLDWIDE
from apollo_utils.core.constants.dsp import DSP
from sqlalchemy import case, func
from sqlalchemy.orm import Query
from typing import Iterable

from apollo_main_db.apollo.models import BuzzCategory, BuzzUser
from apollo_main_db.spotify.models import SpotifyCustomTopPlaylist, SpotifyPersonalizedPlaylist, SpotifyPlaylist, \
    SpotifyPlaylistStreamSummary, SpotifyWeeklyTopPlaylistLatest
from src.constants.core import MARKET_GLOBAL
from src.constants.playlists.spotify.top import SPOTIFY_WEEKLY_TOP_PLAYLIST_CUSTOM_MARKETS
from src.db.base import session as db_session
from src.db.playlists.common import get_base_top_playlist_history_dates_query


def get_playlists_data(playlist_id_list: list[str]) -> Query:
    """Get playlists data.

    Args:
        playlist_id_list: Playlist ID list.

    Returns:
        Playlist data query.

    """
    return (
        db_session.query(
            SpotifyPlaylist.id.label("playlist_id"),
            SpotifyPlaylist.name,
            case(
                [(func.IFNULL(SpotifyPlaylist.country_code, "").in_(("_gl", "", "null")), MARKET_WORLDWIDE)],
                else_=SpotifyPlaylist.country_code,
            ).label("country_code"),
            SpotifyPlaylist.uri.label("link"),
            func.IF(SpotifyPersonalizedPlaylist.playlist_id.is_(None), False, True).label("is_personalized"),
            func.IFNULL(BuzzUser.user_name, SpotifyPlaylist.user_name).label("owner_account_id"),
            func.IFNULL(BuzzUser.display_name, SpotifyPlaylist.user_name).label("owner_account_name"),
            BuzzUser.category_id.label("owner_category_id"),
            BuzzCategory.name.label("owner_category_name"),
            case(
                [(func.IFNULL(BuzzUser.country_code, "").in_(("_gl", "", "null")), MARKET_WORLDWIDE)],
                else_=BuzzUser.country_code,
            ).label("owner_country_code"),
        )
        .outerjoin(BuzzUser, BuzzUser.user_name == SpotifyPlaylist.user_name)
        .outerjoin(BuzzCategory, BuzzCategory.id == BuzzUser.category_id)
        .outerjoin(SpotifyPersonalizedPlaylist, SpotifyPersonalizedPlaylist.playlist_id == SpotifyPlaylist.id)
        .filter(SpotifyPlaylist.id.in_(playlist_id_list))
    )


def get_playlist_top_weekly_query(market: str, include_obsolete: bool = True) -> Query:
    """Returns spotify weekly top playlist query with id, name fields.

    Args:
        market: Country code.
        include_obsolete: Include name field from obsolete table.

    Returns:
        Query.

    """
    field_list = [SpotifyWeeklyTopPlaylistLatest.playlist_id.label("id")]

    # all ranked playlists for selected tracks on last_date.
    playlists_query = db_session.query(SpotifyWeeklyTopPlaylistLatest)

    if include_obsolete:
        field_list.append(SpotifyPlaylist.name.label("name"))
        playlists_query = playlists_query.join(
            SpotifyPlaylist, SpotifyPlaylist.id == SpotifyWeeklyTopPlaylistLatest.playlist_id
        )

    playlists_query = playlists_query.with_entities(*field_list).distinct()

    if market == MARKET_GLOBAL:
        # This based on logic of AP-1717 where we use custom global market playlists
        playlists_query = playlists_query.filter(
            SpotifyWeeklyTopPlaylistLatest.country_code.in_(SPOTIFY_WEEKLY_TOP_PLAYLIST_CUSTOM_MARKETS)
        )
        # get playlist names in case of global market and mix it into 'playlists'
        playlists_custom = (
            db_session.query(SpotifyPlaylist.id, SpotifyPlaylist.name)
            .distinct()
            .join(SpotifyCustomTopPlaylist, SpotifyCustomTopPlaylist.playlist_id == SpotifyPlaylist.id)
        )
        playlists_query = playlists_query.union(playlists_custom)
    else:
        playlists_query = playlists_query.filter(SpotifyWeeklyTopPlaylistLatest.country_code == market)

    return playlists_query


def get_playlist_streams_weekly_query(playlist_ids: list[str], market: str) -> Query:
    """Return playlists streams count query with playlist_id, streams_latest fields.

    Args:
        playlist_ids: List of playlist_id(s)
        market: Country code.
    """
    playlist_streams_query = db_session.query(
        SpotifyPlaylistStreamSummary.playlist_id,
        func.sum(SpotifyPlaylistStreamSummary.streams_7_days).label("streams_latest"),
    ).filter(SpotifyPlaylistStreamSummary.playlist_id.in_(playlist_ids))
    if market != MARKET_GLOBAL:
        playlist_streams_query = playlist_streams_query.filter(SpotifyPlaylistStreamSummary.market == market)
    playlist_streams_query = playlist_streams_query.group_by(SpotifyPlaylistStreamSummary.playlist_id)
    return playlist_streams_query


def get_top_playlist_history_dates_query(
    track_isrc_map: dict[str, str],
    playlist_id_list: Iterable[str] | None = None,
    period: int | None = None,
    market: str | None = None,
) -> Query:
    """Returns playlist history query with playlist_id, track_id, entry_date, exit_date.
    Filter by exit_date >= start_date if last is specified.

    Args:
        playlist_id_list: list of playlist id(s)
        track_isrc_map: ISRC to date mapping.
        period: period to get.
        market: Market code.

    """
    return get_base_top_playlist_history_dates_query(DSP.SPOTIFY, playlist_id_list, track_isrc_map, period, market)