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


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedArtist}
    raw_data_path: str = str(ARTIST_EXTERNAL_LINKS_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    query: str = """
            SELECT
                CM_ARTIST_ID,
                NAME,
                URL
            FROM (
                SELECT NSA.id             AS cm_artist_id,
                       V_CM_URL_TYPE.name AS name,
                       V_CM_URL.url       AS url
                FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS NSA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_URL AS V_CM_URL
                    ON CAST(NSA.id AS VARCHAR) = V_CM_URL.target_id AND V_CM_URL.target = 'cm_artist'
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_URL_TYPE AS V_CM_URL_TYPE
                    ON V_CM_URL_TYPE.id = V_CM_URL.type
                GROUP BY NSA.id, V_CM_URL_TYPE.name, V_CM_URL.url
                UNION
                SELECT
                    V_SPOTIFY_ARTIST.CM_ARTIST AS CM_ARTIST_ID,
                    'spotify' AS NAME,
                    CONCAT('https://open.spotify.com/artist/', V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID) AS URL
                FROM (
                    SELECT
                        DISTINCT ID
                        FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST
                )AS NSA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                    ON CAST(NSA.ID AS VARCHAR) = V_SPOTIFY_ARTIST.CM_ARTIST
                    UNION
                    SELECT
                        NSA.ID AS CM_ARTIST_ID,
                        'youtube' AS NAME,
                        CONCAT('https://www.youtube.com/channel/', NSA.YT) AS URL
                    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS NSA
                    WHERE
                        NSA.YT IS NOT NULL
                    UNION
                    SELECT
                        NSA.ID AS CM_ARTIST_ID,
                        'soundcloud' AS NAME,
                        (PARSE_JSON(V_SOUNDCLOUD.SOUNDCLOUD_USER)):permalink_url AS URL
                    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS NSA
                    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
                        ON NSA.SC = (PARSE_JSON(V_SOUNDCLOUD.SOUNDCLOUD_USER)):id
                    WHERE
                        NSA.SC IS NOT NULL
            )
            ORDER BY CM_ARTIST_ID, NAME
    """