from ..artist import PrepareNonSignedArtist
from ..base import PrepareBase
from ..contsants import NON_SIGNED_ARTIST_TABLE, NON_SIGNED_TRACK_TABLE


__all__ = ["Prepare"]


class Prepare(PrepareBase):
    depends_on = {PrepareNonSignedArtist}
    priority = 0

    table_name: str = NON_SIGNED_TRACK_TABLE
    query = f"""
    WITH PREPARED_CM_TRAK_CM_ARTIST AS (
        SELECT
            V_L_CM_TRACK_CM_ARTIST.CM_TRACK,
            ANY_VALUE(V_L_CM_TRACK_CM_ARTIST.CM_ARTIST) AS CM_ARTIST
        FROM DELPHI_EXPLORATION.CHARTMETRIC.V_L_CM_TRACK_CM_ARTIST AS V_L_CM_TRACK_CM_ARTIST
        WHERE
            V_L_CM_TRACK_CM_ARTIST.ORDERING = 0
        GROUP BY
            V_L_CM_TRACK_CM_ARTIST.CM_TRACK
    )
    , BASIC_DATA AS (
        SELECT
            {NON_SIGNED_ARTIST_TABLE}.ID AS ARTIST_ID,
            V_CM_TRACK.ID,
            V_CM_TRACK.ISRC,
            V_CM_TRACK.IMAGE_URL AS ARTWORK_URL,
            CAST(V_CM_TRACK.NAME AS VARCHAR) AS NAME
        FROM DNA.DNA_PUBLIC.{NON_SIGNED_ARTIST_TABLE} AS {NON_SIGNED_ARTIST_TABLE}
        JOIN PREPARED_CM_TRAK_CM_ARTIST
            ON PREPARED_CM_TRAK_CM_ARTIST.CM_ARTIST = {NON_SIGNED_ARTIST_TABLE}.ID
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_TRACK AS V_CM_TRACK
            ON V_CM_TRACK.ID = PREPARED_CM_TRAK_CM_ARTIST.CM_TRACK
        GROUP BY
            {NON_SIGNED_ARTIST_TABLE}.ID,
            V_CM_TRACK.ID,
            V_CM_TRACK.ISRC,
            V_CM_TRACK.IMAGE_URL,
            V_CM_TRACK.NAME
    ), V_CM_ALBUM_DATA AS (
        SELECT
            V_L_CM_TRACK_CM_ALBUM.CM_TRACK AS ID,
            V_CM_ALBUM.RELEASE_DATE AS RELEASE_DATE,
            V_CM_ALBUM.LABEL AS LABEL
        FROM BASIC_DATA
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_L_CM_TRACK_CM_ALBUM AS V_L_CM_TRACK_CM_ALBUM
            ON BASIC_DATA.ID = V_L_CM_TRACK_CM_ALBUM.CM_TRACK
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_ALBUM AS V_CM_ALBUM
            ON V_L_CM_TRACK_CM_ALBUM.CM_ALBUM = V_CM_ALBUM.ID
        QUALIFY ROW_NUMBER() OVER (PARTITION BY V_L_CM_TRACK_CM_ALBUM.CM_TRACK ORDER BY V_CM_ALBUM.RELEASE_DATE NULLS LAST) = 1
    ), SPOTIFY_DATA AS (
        SELECT
            BASIC_DATA.ID,
            V_SPOTIFY_ALBUM.LABEL,
            V_SPOTIFY_ALBUM.RELEASE_DATE,
            V_SPOTIFY.SPOTIFY_TRACK_ID
        FROM BASIC_DATA
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
            ON BASIC_DATA.ISRC = V_SPOTIFY.ISRC
        LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ALBUM AS V_SPOTIFY_ALBUM
            ON V_SPOTIFY.SPOTIFY_ALBUM_ID = V_SPOTIFY_ALBUM.SPOTIFY_ALBUM_ID
        QUALIFY ROW_NUMBER () OVER (PARTITION BY BASIC_DATA.ID ORDER BY V_SPOTIFY_ALBUM.POPULARITY DESC NULLS LAST, V_SPOTIFY_ALBUM.RELEASE_DATE NULLS LAST) = 1
    )
    SELECT
        BASIC_DATA.ID,
        BASIC_DATA.ISRC,
        BASIC_DATA.ARTIST_ID,
        BASIC_DATA.NAME,
        IFNULL(SPOTIFY_DATA.LABEL, V_CM_ALBUM_DATA.LABEL) AS LABEL,
        BASIC_DATA.ARTWORK_URL,
        V_CM_ALBUM_DATA.RELEASE_DATE AS RELEASE_DATE,
        SPOTIFY_DATA.SPOTIFY_TRACK_ID
    FROM BASIC_DATA
    LEFT JOIN V_CM_ALBUM_DATA
        ON BASIC_DATA.ID = V_CM_ALBUM_DATA.ID
    LEFT JOIN SPOTIFY_DATA
        ON BASIC_DATA.ID = SPOTIFY_DATA.ID
    """