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


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedTrack}
    raw_data_path: str = str(ARTIST_TRACKS_SOUNDCLOUD_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    top_tracks = 7

    query: str = f"""
    WITH T_NON_SIGNED_TRACK AS (
      SELECT
        T_NON_SIGNED_TRACK.ID AS ID,
        T_NON_SIGNED_TRACK.ARTIST_ID AS ARTIST_ID,
        T_NON_SIGNED_TRACK.ISRC AS ISRC,
        V_SOUNDCLOUD.ID AS SC_ID,
        T_NON_SIGNED_TRACK.RELEASE_DATE as RELEASE_DATE
      FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
      JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD ON V_SOUNDCLOUD.ISRC = T_NON_SIGNED_TRACK.ISRC
    )
    SELECT
        T_NON_SIGNED_TRACK.ARTIST_ID AS CM_ARTIST_ID,
        T_NON_SIGNED_TRACK.ID AS CM_TRACK_ID,
        ANY_VALUE(T_NON_SIGNED_TRACK.RELEASE_DATE) AS RELEASE_DATE,
        ANY_VALUE(T_NON_SIGNED_TRACK.SC_ID) AS SOUNDCLOUD_ID,
        MAX(stat.PLAYBACK_COUNT) as PLAYS
    FROM T_NON_SIGNED_TRACK
    LEFT JOIN WHITELIST_REPLICA.MAIN.SC_TRACKS_ALL AS WH_SC_TRACKS ON T_NON_SIGNED_TRACK.SC_ID = WH_SC_TRACKS.SCID
    LEFT JOIN WHITELIST_REPLICA.MAIN.SC_TRACK_STATS AS stat ON stat.TRACK_SCID = WH_SC_TRACKS.SCID
    GROUP BY T_NON_SIGNED_TRACK.ARTIST_ID, T_NON_SIGNED_TRACK.ID
    QUALIFY ROW_NUMBER() OVER (PARTITION BY CM_ARTIST_ID ORDER BY PLAYS DESC NULLS LAST) <= {top_tracks}
    ORDER BY CM_ARTIST_ID, PLAYS DESC NULLS LAST
    """