from apollo_utils.core.constants.dsp import DSP
from sqlalchemy import func
from sqlalchemy.orm import Query
from typing import List, Optional

from apollo_main_db import AppleMusicContainerStreamSummary, AppleMusicWeeklyTopPlaylistTrackSummary, ApplePlaylist, \
    AppleWeeklyTopPlaylistLatest
from src.constants.core import MARKET_GLOBAL
from src.constants.playlists.apple.top import APPLE_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_playlist_top_weekly_query(market: str, include_obsolete: bool = True) -> Query:
    """Returns apple playlist query with id, name fields.

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

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

    # all rank'ed playlists for selected tracks on last_date.
    playlists_query = db_session.query(AppleWeeklyTopPlaylistLatest).distinct()

    if include_obsolete:
        field_list.append(ApplePlaylist.name)
        playlists_query = (
            playlists_query.join(ApplePlaylist, ApplePlaylist.id == AppleWeeklyTopPlaylistLatest.playlist_id)
        )

    playlists_query = playlists_query.with_entities(*field_list)

    if market == MARKET_GLOBAL:
        playlists_query = playlists_query.filter(
            AppleWeeklyTopPlaylistLatest.country_code.in_(APPLE_WEEKLY_TOP_PLAYLIST_CUSTOM_MARKETS)
        )
    else:
        playlists_query = playlists_query.filter(AppleWeeklyTopPlaylistLatest.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 for now.

    Args:
        playlist_ids: List of playlist_id(s)
        market: Country code.
    """
    playlist_id = AppleMusicContainerStreamSummary.container_id.label("playlist_id")
    playlist_streams_query = (
        db_session.query(playlist_id, func.sum(AppleMusicContainerStreamSummary.streams_7_days).label("streams_latest"))
        .filter(AppleMusicContainerStreamSummary.container_id.in_(playlist_ids))
        .filter(AppleMusicContainerStreamSummary.country_code == market)
    )

    playlist_streams_query = playlist_streams_query.group_by(playlist_id).order_by(
        func.sum(AppleMusicContainerStreamSummary.streams_7_days).desc()
    )
    return playlist_streams_query


def get_top_playlist_history_dates_query(
    track_isrc_map: dict[str, str],
    playlist_id_list: list[str] | None = None,
    period: Optional[int] = None,
    market: Optional[str] = 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.
    """
    history_query = get_base_top_playlist_history_dates_query(
        DSP.APPLE, playlist_id_list, track_isrc_map, period, market
    )

    if market == MARKET_GLOBAL:
        history_query = history_query.filter(
            AppleMusicWeeklyTopPlaylistTrackSummary.storefront.in_(APPLE_WEEKLY_TOP_PLAYLIST_CUSTOM_MARKETS)
        )
    else:
        history_query = history_query.filter(AppleMusicWeeklyTopPlaylistTrackSummary.storefront == market)

    return history_query