from .....preparation import PrepareNonSignedArtist
from ....base import AggregatesExtractBase
from ....constants import ARTIST_ALBUMS_SPOTIFY_RAW_DATA_PATH, RAW_ARTIST_ID_KEY


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedArtist}
    raw_data_path: str = str(ARTIST_ALBUMS_SPOTIFY_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    query: str = """
    SELECT
        NSA.ID                            as CM_ARTIST_ID,
        V_SPOTIFY_ALBUM.SPOTIFY_ALBUM_ID  as SPOTIFY_ALBUM_ID,
        V_SPOTIFY_ALBUM.RELEASE_DATE      as RELEASE_DATE
    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS NSA
    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
        ON V_SPOTIFY.SPOTIFY_ARTIST_ID = NSA.SP
    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ALBUM AS V_SPOTIFY_ALBUM
        ON V_SPOTIFY.SPOTIFY_ALBUM_ID = V_SPOTIFY_ALBUM.SPOTIFY_ALBUM_ID
    WHERE
        V_SPOTIFY_ALBUM.RELEASE_DATE IS NOT NULL
        AND
        V_SPOTIFY_ALBUM.UPC IS NOT NULL
        AND
        V_SPOTIFY_ALBUM.NAME IS NOT NULL
        AND
        V_SPOTIFY_ALBUM.ARTWORK_URL IS NOT NULL
    GROUP BY
        NSA.ID,
        V_SPOTIFY_ALBUM.SPOTIFY_ALBUM_ID,
        V_SPOTIFY_ALBUM.RELEASE_DATE
    ORDER BY
        CM_ARTIST_ID,
        RELEASE_DATE DESC NULLS LAST
    """