from constants import LAST_RELEASE_DAYS

from .....preparation import PrepareNonSignedTrack
from ....base import AggregatesExtractBase
from ....constants import ARTIST_TRACKS_SPOTIFY_RAW_DATA_PATH, RAW_ARTIST_ID_KEY


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedTrack}
    raw_data_path: str = str(ARTIST_TRACKS_SPOTIFY_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    top_tracks: int = 9
    min_popularity: int = 10
    last_release_days: int = LAST_RELEASE_DAYS

    query: str = f"""
    SELECT
        CM_ARTIST_ID,
        CM_TRACK_ID,
        RELEASE_DATE,
        POPULARITY_SCORE,
        SPOTIFY_TRACK_ID
    FROM (
        SELECT
            T_NON_SIGNED_TRACK.ARTIST_ID AS CM_ARTIST_ID,
            T_NON_SIGNED_TRACK.ID AS CM_TRACK_ID,
            T_NON_SIGNED_TRACK.RELEASE_DATE AS RELEASE_DATE,
            T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID AS SPOTIFY_TRACK_ID,
            MAX(V_SPOTIFY.POPULARITY_SCORE) AS POPULARITY_SCORE
        FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
            ON T_NON_SIGNED_TRACK.ISRC = V_SPOTIFY.ISRC
        WHERE
            T_NON_SIGNED_TRACK.RELEASE_DATE >= CURRENT_DATE() - {last_release_days}
        OR
            POPULARITY_SCORE >= {min_popularity}
        GROUP BY
            T_NON_SIGNED_TRACK.ARTIST_ID,
            T_NON_SIGNED_TRACK.ID,
            T_NON_SIGNED_TRACK.RELEASE_DATE,
            T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID
    )
    QUALIFY ROW_NUMBER() OVER (PARTITION BY CM_ARTIST_ID ORDER BY POPULARITY_SCORE DESC NULLS LAST) <= {top_tracks}
    ORDER BY
        CM_ARTIST_ID, POPULARITY_SCORE DESC NULLS LAST
    """