from ....preparation import PrepareNonSignedTrack
from ...base import AggregatesExtractBase
from ...constants import RAW_TRACK_ID_KEY, TRACK_EXTERNAL_LINKS_RAW_DATA_PATH


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedTrack}
    raw_data_path: str = str(TRACK_EXTERNAL_LINKS_RAW_DATA_PATH)
    partition_key: str = RAW_TRACK_ID_KEY
    files_count: int = 1000
    query: str = """
        SELECT
            NST.ID AS cm_track_id,
            RES.DSP,
            RES.ID
        FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS NST
        JOIN (
          SELECT 'spotify' AS DSP, V_SPOTIFY.spotify_track_id AS ID, V_SPOTIFY.isrc AS ISRC, ROW_NUMBER() OVER(partition by V_SPOTIFY.ISRC ORDER BY V_SPOTIFY.popularity_score DESC) AS list_position
          FROM V_SPOTIFY
          QUALIFY list_position = 1
          UNION
          SELECT 'tiktok' AS DSP, V_TIKTOK.tiktok_track_id AS ID, V_TIKTOK.isrc AS ISRC, ROW_NUMBER() OVER(partition by V_TIKTOK.ISRC ORDER BY V_TIKTOK.posts_latest DESC) AS list_position
          FROM V_TIKTOK
          WHERE V_TIKTOK.active = 1
          QUALIFY list_position = 1
        ) as RES ON RES.ISRC = NST.ISRC
        GROUP BY NST.ID, RES.DSP, RES.ID
        ORDER BY NST.ID, RES.DSP
    """