import datetime
from typing import List, Optional

from sqlalchemy import and_, distinct, func
from sqlalchemy.orm import Query, Session

from apollo_main_db.apollo.models import Market, StarredContent, UserMarket
from apollo_main_db.push_notifications.models import UserDeviceToken
from apollo_main_db.spotify.models import SpotifyCharts, SpotifyTrack2
from spotify_charts_notifications import constants
from spotify_charts_notifications.main_db import session_scope


def get_markets() -> Query:
    """Get chart markets"""
    with session_scope() as session:
        return session.query(distinct(SpotifyCharts.market)).filter(
            and_(SpotifyCharts.market != '', SpotifyCharts.market.isnot(None))).all()


def get_chart_last_dates(market: str) -> List[Optional[datetime.date]]:
    """Get two most recent dates of Spotify chart data.
    Args:
        market: str market.
    Returns:
        List of up to two dates in format %Y-%m-%d.
    """
    with session_scope() as session:
        dates = session.query(distinct(SpotifyCharts.date)).filter_by(
            market=market, time_window_id=0, list_type_id=0
        ).order_by(SpotifyCharts.date.desc())[:2]
        try:
            return [d.strftime("%Y-%m-%d") for d, in dates]
        except AttributeError:
            return []


def get_base_query(session: Session, market: str, date: str) -> Query:
    """Get base song entities query

    Args:
        session: DB session.
        market: str market.
        date: str date in format %Y-%m-%d.
    """
    return session.query(
        SpotifyCharts.track_url.label('track_url'),
        SpotifyCharts.track_name.label('track_name'),
        SpotifyCharts.artist_name.label('artist_name'),
        SpotifyCharts.position.label('position'),
        SpotifyTrack2.isrc.label('isrc'),
    ).join(
        SpotifyTrack2,
        SpotifyTrack2.id == func.REPLACE(SpotifyCharts.track_url, constants.SPOTIFY_URL_PREFIX, '')
    ).filter(
        SpotifyCharts.time_window_id == 0,
        SpotifyCharts.list_type_id == 0,
        SpotifyCharts.market == market,
        SpotifyCharts.date == date,
    )


def get_diff_tracks_by_dates(session: Session, market: str, start_date: str, end_date: str) -> Query:
    """Get track entities difference between two dates.
    Using this method we can get added or removed tracks in charts.

    Args:
        session (Session): DB session.
        market: str market.
        start_date: str date in format %Y-%m-%d.
        end_date: str date in format %Y-%m-%d.
    """
    query_for_yesterday = get_base_query(session, market, end_date).subquery()
    query_for_today = get_base_query(session, market, start_date)
    return query_for_today.outerjoin(
        query_for_yesterday,
        query_for_yesterday.c.isrc == SpotifyCharts.isrc
    ).filter(query_for_yesterday.c.position.is_(None))


def get_major_moves(session: Session, market: str, start_date: str, end_date: str) -> Query:
    """Get track entities difference between two dates.
    Using this method we can get major moves for tracks inside chart.

    Args:
        session (Session): DB session.
        market: str market.
        start_date: str date in format %Y-%m-%d.
        end_date: str date in format %Y-%m-%d.
    """
    query_for_yesterday = get_base_query(session, market, end_date).subquery()
    query_for_today = get_base_query(session, market, start_date).subquery()
    return session.query(
        query_for_today.c.track_url,
        query_for_today.c.track_name,
        query_for_today.c.position,
        query_for_today.c.artist_name,
        query_for_today.c.isrc,
        (query_for_today.c.position - func.min(query_for_yesterday.c.position)).label('moves')
    ).join(
        query_for_yesterday,
        query_for_yesterday.c.isrc == query_for_today.c.isrc
    ).group_by(
        query_for_today.c.isrc, query_for_today.c.position
    ).having(
        func.abs(
            query_for_today.c.position - func.min(query_for_yesterday.c.position)
        ) >= constants.THRESHOLD_MAJOR_MOVES
    )


def get_only_starred_tracks_with_device_tokens(
        session: Session, tracks_sub_query: Query, market: str) -> Query:
    """Filtering tracks query by starred tracks and join with User devices

    Args:
        session (Session): DB session.
        tracks_sub_query: Query with track_url, position,
        track_name, artist_name aliased columns.
        market: str market.
    """
    return session.query(
        StarredContent.user_id,
        Market.market_name,
        func.group_concat(
            UserDeviceToken.token.op('ORDER BY')(UserDeviceToken.created_at.desc())
        ).label('device_tokens'),
        tracks_sub_query
    ).join(
        tracks_sub_query,
        func.upper(StarredContent.isrc) == func.upper(tracks_sub_query.c.isrc)
    ).join(
        UserDeviceToken, UserDeviceToken.user_id == StarredContent.user_id
    ).join(
        UserMarket,
        UserMarket.user_id == StarredContent.user_id
    ).join(
        Market,
        Market.market_id == UserMarket.market_id
    ).filter(
        Market.market_name == market,
        UserDeviceToken.is_active.is_(True),
        StarredContent.isrc.isnot(None),
        StarredContent.isrc.op('regexp')(r'[A-Za-z]{2}[A-Za-z0-9]{3}[0-9]{7}')
    ).group_by(
        tracks_sub_query.c.isrc,
        tracks_sub_query.c.position,
        StarredContent.user_id
    )


def process_moves_isrc_duplicates(query: Query) -> List:
    """Delete entities (set moves = 0 for MM == delete) for non top isrc position per chart per isrc.

    If there are some tracks with the same isrc in the chart non top items should be considered as not moving.

    Args:
        query: Moves query.

    Returns:
        List: Query result items.
    """

    results = {}

    for track in query:
        key = f"{track.user_id}_{track.isrc}"
        saved_track = results.get(key)

        if saved_track is None or track.position < saved_track.position:
            results[key] = track

    return list(results.values())