from app_types import S3Path
from constants import FIRST_CHART_DAY, HISTORICAL_CHART_DAY, LAST_CHART_DAY

from ....preparation import ArtistGenre
from ...base import SearchExtractBase
from ...constants import ARTIST_SPOTIFY_RAW_DATA, N_A_VALUE


__all__ = ["Extract"]


class Extract(SearchExtractBase):
    depends_on = {ArtistGenre}
    raw_data_path: S3Path = ARTIST_SPOTIFY_RAW_DATA

    @property
    def query(self):
        return f"""
        WITH BASIC_DATA AS (
            SELECT
                T_NON_SIGNED_ARTIST.ID AS ID,
                ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2) AS CODE2,
                ANY_VALUE(T_NON_SIGNED_ARTIST.LABEL) AS LABEL,
                ANY_VALUE(T_NON_SIGNED_ARTIST.ARTWORK_URL) AS ARTWORK_URL,
                ANY_VALUE(T_NON_SIGNED_ARTIST.NAME) AS NAME,
                ANY_VALUE(T_NON_SIGNED_ARTIST.SP) AS SPOTIFY_ARTIST_ID,
                ANY_VALUE(T_NON_SIGNED_ARTIST.RELEASE_DATE) AS LATEST_RELEASE_DATE,
                ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_ARTIST_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
            LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_ARTIST_GENRE ON NON_SIGNED_ARTIST_GENRE.ID = T_NON_SIGNED_ARTIST.ID
            GROUP BY
                T_NON_SIGNED_ARTIST.ID
        )
        , TRACKS AS (
            SELECT
                BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                COUNT(V_SPOTIFY.SPOTIFY_TRACK_ID) as QTY
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
                ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY.SPOTIFY_ARTIST_ID
            GROUP BY BASIC_DATA.SPOTIFY_ARTIST_ID
        )
        , FOLLOWERS_HISTORICAL_POINTS AS (
            SELECT
                BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.FOLLOWERS AS FOLLOWERS,
                V_SPOTIFY_ARTIST_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
            WHERE
                V_SPOTIFY_ARTIST_STAT.FOLLOWERS IS NOT NULL
                AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                BASIC_DATA.SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.FOLLOWERS,
                V_SPOTIFY_ARTIST_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY BASIC_DATA.SPOTIFY_ARTIST_ID ORDER BY V_SPOTIFY_ARTIST_STAT.TIMESTP DESC) = 1
        )
        , POPULARITY_HISTORICAL_POINTS AS (
            SELECT
                BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.POPULARITY AS POPULARITY,
                V_SPOTIFY_ARTIST_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
            WHERE
                V_SPOTIFY_ARTIST_STAT.POPULARITY IS NOT NULL
                AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                BASIC_DATA.SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.POPULARITY,
                V_SPOTIFY_ARTIST_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY BASIC_DATA.SPOTIFY_ARTIST_ID ORDER BY V_SPOTIFY_ARTIST_STAT.TIMESTP DESC) = 1
        )
        , LISTENERS_HISTORICAL_POINTS AS (
            SELECT
                BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS AS LISTENERS,
                V_SPOTIFY_ARTIST_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
            WHERE
                V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS IS NOT NULL
                AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                BASIC_DATA.SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS,
                V_SPOTIFY_ARTIST_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY BASIC_DATA.SPOTIFY_ARTIST_ID ORDER BY V_SPOTIFY_ARTIST_STAT.TIMESTP DESC) = 1
        )
        , FOLLOWERS_CHART_DATA AS (
            SELECT
                SPOTIFY_ARTIST_ID,
                ARRAY_AGG(FOLLOWERS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.FOLLOWERS AS FOLLOWERS,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP  AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                    ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                    ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
                WHERE
                    V_SPOTIFY_ARTIST_STAT.FOLLOWERS IS NOT NULL
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {FIRST_CHART_DAY}
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP >= {LAST_CHART_DAY}
                GROUP BY
                    BASIC_DATA.SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.FOLLOWERS,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP
            )
            GROUP BY
                SPOTIFY_ARTIST_ID
        )
        , POPULARITY_CHART_DATA AS (
            SELECT
                SPOTIFY_ARTIST_ID,
                ARRAY_AGG(POPULARITY) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.POPULARITY AS POPULARITY,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP  AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                    ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                    ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
                WHERE
                    V_SPOTIFY_ARTIST_STAT.POPULARITY IS NOT NULL
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {FIRST_CHART_DAY}
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP >= {LAST_CHART_DAY}
                GROUP BY
                    BASIC_DATA.SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.POPULARITY,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP
            )
            GROUP BY
                SPOTIFY_ARTIST_ID
        )
        , LISTENERS_CHART_DATA AS (
            SELECT
                SPOTIFY_ARTIST_ID,
                ARRAY_AGG(LISTENERS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS AS LISTENERS,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP  AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                    ON BASIC_DATA.SPOTIFY_ARTIST_ID = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST_STAT  AS V_SPOTIFY_ARTIST_STAT
                    ON V_SPOTIFY_ARTIST.ID = V_SPOTIFY_ARTIST_STAT.SPOTIFY_ARTIST
                WHERE
                    V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS IS NOT NULL
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {FIRST_CHART_DAY}
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP >= {LAST_CHART_DAY}
                GROUP BY
                    BASIC_DATA.SPOTIFY_ARTIST_ID,
                    V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS,
                    V_SPOTIFY_ARTIST_STAT.TIMESTP
            )
            GROUP BY
                SPOTIFY_ARTIST_ID
        )
        SELECT
            BASIC_DATA.ID AS ID,
            ANY_VALUE(BASIC_DATA.NAME) AS NAME,
            ANY_VALUE(BASIC_DATA.GENRES) AS GENRES,
            IFNULL(ANY_VALUE(BASIC_DATA.CODE2), '{N_A_VALUE}') AS COUNTRY,
            ANY_VALUE(BASIC_DATA.LABEL) AS LABEL,
            ANY_VALUE(BASIC_DATA.ARTWORK_URL) AS ARTWORK_URL,
            ANY_VALUE(BASIC_DATA.SPOTIFY_ARTIST_ID) AS SPOTIFY_ARTIST_ID,
            ANY_VALUE(BASIC_DATA.LATEST_RELEASE_DATE) AS LATEST_RELEASE_DATE,
            ANY_VALUE(FOLLOWERS_HISTORICAL_POINTS.FOLLOWERS) AS FOLLOWERS_CHART_HISTORICAL_VALUE,
            ANY_VALUE(FOLLOWERS_HISTORICAL_POINTS.DATE) AS FOLLOWERS_CHART_HISTORICAL_DATE,
            ANY_VALUE(POPULARITY_HISTORICAL_POINTS.POPULARITY) AS POPULARITY_CHART_HISTORICAL_VALUE,
            ANY_VALUE(POPULARITY_HISTORICAL_POINTS.DATE) AS POPULARITY_CHART_HISTORICAL_DATE,
            ANY_VALUE(LISTENERS_HISTORICAL_POINTS.LISTENERS) AS LISTENERS_CHART_HISTORICAL_VALUE,
            ANY_VALUE(LISTENERS_HISTORICAL_POINTS.DATE) AS LISTENERS_CHART_HISTORICAL_DATE,
            IFNULL(ANY_VALUE(FOLLOWERS_CHART_DATA.CHART), []) AS FOLLOWERS_CHART,
            IFNULL(ANY_VALUE(FOLLOWERS_CHART_DATA.CHART_DATES), []) AS FOLLOWERS_CHART_DATES,
            IFNULL(ANY_VALUE(POPULARITY_CHART_DATA.CHART), []) AS POPULARITY_CHART,
            IFNULL(ANY_VALUE(POPULARITY_CHART_DATA.CHART_DATES), []) AS POPULARITY_CHART_DATES,
            IFNULL(ANY_VALUE(LISTENERS_CHART_DATA.CHART), []) AS LISTENERS_CHART,
            IFNULL(ANY_VALUE(LISTENERS_CHART_DATA.CHART_DATES), []) AS LISTENERS_CHART_DATES,
            ANY_VALUE(TRACKS.QTY) AS TRACKS
        FROM BASIC_DATA
        LEFT JOIN FOLLOWERS_HISTORICAL_POINTS
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = FOLLOWERS_HISTORICAL_POINTS.SPOTIFY_ARTIST_ID
        LEFT JOIN POPULARITY_HISTORICAL_POINTS
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = POPULARITY_HISTORICAL_POINTS.SPOTIFY_ARTIST_ID
        LEFT JOIN LISTENERS_HISTORICAL_POINTS
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = LISTENERS_HISTORICAL_POINTS.SPOTIFY_ARTIST_ID
        LEFT JOIN FOLLOWERS_CHART_DATA
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = FOLLOWERS_CHART_DATA.SPOTIFY_ARTIST_ID
        LEFT JOIN POPULARITY_CHART_DATA
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = POPULARITY_CHART_DATA.SPOTIFY_ARTIST_ID
        LEFT JOIN LISTENERS_CHART_DATA
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = LISTENERS_CHART_DATA.SPOTIFY_ARTIST_ID
        LEFT JOIN TRACKS
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = TRACKS.SPOTIFY_ARTIST_ID
        GROUP BY BASIC_DATA.ID
        ORDER BY BASIC_DATA.ID
        """