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

from apollo_main_db.apollo.models import Market, StarredContent, UserMarket
from apollo_main_db.apple.models import AppleMusicChart, AppleMusicSong, AppleMusicChartSongEntry
from apollo_main_db.push_notifications.models import UserDeviceToken
from apple_charts_notifications.constants import CHART_NAME, THRESHOLD_MAJOR_MOVES
from apple_charts_notifications.main_db import session_scope


def get_markets() -> Query:
    """Get chart markets"""
    with session_scope() as session:
        return session.query(distinct(AppleMusicChart.storefront)).all()


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

    Args:
        session (Session): DB session.
        market: str market.
        date: str date in format %Y-%m-%d.
    """
    return session.query(
        AppleMusicChartSongEntry.song_id.label('song_id'),
        AppleMusicChartSongEntry.name.label('song_name'),
        AppleMusicChartSongEntry.artist_name.label('artist_name'),
        (AppleMusicChartSongEntry.position + 1).label('position'),
        AppleMusicSong.isrc.label('isrc'),
    ).join(
        AppleMusicChart,
        AppleMusicChart.id == AppleMusicChartSongEntry.chart_id
    ).join(
        AppleMusicSong,
        AppleMusicSong.id == AppleMusicChartSongEntry.song_id
    ).filter(
        AppleMusicChart.chart_name == CHART_NAME,
        AppleMusicChart.storefront == market,
        AppleMusicChart.date == date,
    )


def exists_entities_for_date(market: str, date: str) -> bool:
    """Check does exist entities for given date

    Args:
        market: str market.
        date: str date in format %Y-%m-%d.
    """
    with session_scope() as session:
        query = session.query(AppleMusicChart).filter(
            AppleMusicChart.chart_name == CHART_NAME,
            AppleMusicChart.storefront == market,
            AppleMusicChart.date == date,
        ).exists()
        return session.query(query).scalar()


def get_diff_tracks_by_dates(session: Session, market: str, start_date: str, end_date: str) -> Query:
    """Get song 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.song_id == AppleMusicChartSongEntry.song_id
    ).filter(query_for_yesterday.c.song_id.is_(None))


def get_major_moves(session: Session, market: str, start_date: str, end_date: str) -> Query:
    """Get song 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.song_id,
        query_for_today.c.isrc,
        query_for_today.c.song_name,
        query_for_today.c.position,
        query_for_today.c.artist_name,
        (query_for_today.c.position - query_for_yesterday.c.position).label(
            'moves')
    ).join(
        query_for_yesterday,
        query_for_yesterday.c.song_id == query_for_today.c.song_id
    ).filter(
        func.abs(
            query_for_today.c.position - query_for_yesterday.c.position
        ) >= 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 song_id, position,
        song_name, artist_name aliased columns.
        market: str market.
    """
    query = session.query(
        StarredContent.user_id,
        Market.market_name,
        func.group_concat(
            UserDeviceToken.token.distinct().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.song_id, StarredContent.user_id)
    return query