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

from ....preparation import TrackGenre
from ...base import SearchExtractBase
from ...constants import N_A_VALUE, TRACK_SPOTIFY_RAW_DATA


__all__ = ["Extract"]


class Extract(SearchExtractBase):
    depends_on = {TrackGenre}
    min_popularity: int = 10
    last_period: int = 7 * 8
    raw_data_path: S3Path = TRACK_SPOTIFY_RAW_DATA

    @property
    def query(self):
        return f"""
        WITH OTHER_DSP_DATA AS (
            SELECT
                T_NON_SIGNED_TRACK.ID AS ID,
                TRUE AS OTHER_DSP
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                ON T_NON_SIGNED_TRACK.ISRC = V_YOUTUBE.ISRC
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
                ON T_NON_SIGNED_TRACK.ISRC = V_SOUNDCLOUD.ISRC
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK AS V_TIKTOK
                ON T_NON_SIGNED_TRACK.ISRC = V_TIKTOK.ISRC
            WHERE
                V_YOUTUBE.ID IS NOT NULL
                OR
                V_SOUNDCLOUD.ID IS NOT NULL
                OR
                V_TIKTOK.ID IS NOT NULL
            ),
        BASIC_DATA AS(
            SELECT
                T_NON_SIGNED_TRACK.ID,
                T_NON_SIGNED_TRACK.ISRC AS ISRC,
                T_NON_SIGNED_TRACK.NAME,
                T_NON_SIGNED_TRACK.LABEL,
                T_NON_SIGNED_TRACK.ARTWORK_URL,
                T_NON_SIGNED_TRACK.ARTIST_ID AS CM_ARTIST_ID,
                ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_TRACK_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES,
                ANY_VALUE(T_NON_SIGNED_ARTIST.NAME) AS ARTIST,
                ANY_VALUE(T_NON_SIGNED_ARTIST.SP) AS SPOTIFY_ARTIST_ID,
                ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2) AS COUNTRY,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID AS SPOTIFY_TRACK_ID,
                T_NON_SIGNED_TRACK.RELEASE_DATE AS RELEASE_DATE,
                V_SPOTIFY.POPULARITY_SCORE AS POPULARITY_SCORE,
                V_SPOTIFY.ID AS V_SPOTIFY_ID
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
            LEFT JOIN OTHER_DSP_DATA
                ON T_NON_SIGNED_TRACK.ID = OTHER_DSP_DATA.ID
            LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_TRACK_GENRE
                ON NON_SIGNED_TRACK_GENRE.ID = T_NON_SIGNED_TRACK.ID
            JOIN DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
                ON T_NON_SIGNED_ARTIST.ID = T_NON_SIGNED_TRACK.ARTIST_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
                ON T_NON_SIGNED_TRACK.ISRC  = V_SPOTIFY.ISRC
            WHERE
                (
                    V_SPOTIFY.POPULARITY_SCORE >= {self.min_popularity}
                    AND
                    OTHER_DSP_DATA.OTHER_DSP IS NULL
                )
                    OR
            (
                T_NON_SIGNED_TRACK.RELEASE_DATE IS NOT NULL
                AND
                OTHER_DSP_DATA.OTHER_DSP IS NULL
                AND
                T_NON_SIGNED_TRACK.RELEASE_DATE >= CURRENT_DATE() - {self.last_period}
            )
            OR
                OTHER_DSP_DATA.OTHER_DSP IS NOT NULL
            GROUP BY
                T_NON_SIGNED_TRACK.ID,
                T_NON_SIGNED_TRACK.ISRC,
                T_NON_SIGNED_TRACK.NAME,
                T_NON_SIGNED_TRACK.LABEL,
                T_NON_SIGNED_TRACK.ARTWORK_URL,
                T_NON_SIGNED_TRACK.ARTIST_ID,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID,
                T_NON_SIGNED_TRACK.RELEASE_DATE,
                V_SPOTIFY.POPULARITY_SCORE,
                V_SPOTIFY.ID
        )
        ,PLAYLIST_DATA AS (
            SELECT
                V_L_SPOTIFY_PLAYLIST.SPOTIFY AS V_SPOTIFY_ID,
                V_L_SPOTIFY_PLAYLIST.SPOTIFY_PLAYLIST AS SPOTIFY_PLAYLIST,
                V_L_SPOTIFY_PLAYLIST.START_SYS_PERIOD AS START_SYS_PERIOD
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_L_SPOTIFY_PLAYLIST AS V_L_SPOTIFY_PLAYLIST
                ON BASIC_DATA.V_SPOTIFY_ID = V_L_SPOTIFY_PLAYLIST.SPOTIFY
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_PLAYLIST AS V_SPOTIFY_PLAYLIST
                ON V_L_SPOTIFY_PLAYLIST.SPOTIFY_PLAYLIST = V_SPOTIFY_PLAYLIST.ID
            WHERE
                V_SPOTIFY_PLAYLIST.ACTIVE = TRUE
                AND
                V_L_SPOTIFY_PLAYLIST.END_SYS_PERIOD IS NULL
        )
        , PLAYLIST_HISTORIC_DATA AS (
            SELECT
                BASIC_DATA.V_SPOTIFY_ID AS V_SPOTIFY_ID,
                V_L_SPOTIFY_PLAYLIST_HISTORY.SPOTIFY_PLAYLIST AS SPOTIFY_PLAYLIST,
                V_L_SPOTIFY_PLAYLIST_HISTORY.START_SYS_PERIOD AS START_SYS_PERIOD,
                V_L_SPOTIFY_PLAYLIST_HISTORY.END_SYS_PERIOD AS END_SYS_PERIOD
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_L_SPOTIFY_PLAYLIST_HISTORY AS V_L_SPOTIFY_PLAYLIST_HISTORY
                ON BASIC_DATA.V_SPOTIFY_ID = V_L_SPOTIFY_PLAYLIST_HISTORY.SPOTIFY
            -- we need join with V_SPOTIFY_PLAYLIST for correct historical count.
            -- You can find spotify list in V_L_SPOTIFY_PLAYLIST_HISTORY,
            -- but it also should be in V_SPOTIFY_PLAYLIST!
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_PLAYLIST AS V_SPOTIFY_PLAYLIST
                ON V_L_SPOTIFY_PLAYLIST_HISTORY.SPOTIFY_PLAYLIST = V_SPOTIFY_PLAYLIST.ID
        )
        , PLAYLIST_CURRENT AS (
            SELECT
                PLAYLIST_DATA.V_SPOTIFY_ID AS V_SPOTIFY_ID,
                COUNT(*) AS PLAYLIST_COUNT
            FROM PLAYLIST_DATA
            GROUP BY
                PLAYLIST_DATA.V_SPOTIFY_ID
        )
        , PLAYLIST_LAST_7_DAYS AS (
            SELECT
                V_SPOTIFY_ID,
                COUNT(*) AS PLAYLIST_COUNT
            FROM (
                SELECT
                    PLAYLIST_DATA.V_SPOTIFY_ID,
                    PLAYLIST_DATA.SPOTIFY_PLAYLIST
                FROM PLAYLIST_DATA
                WHERE
                    PLAYLIST_DATA.START_SYS_PERIOD <= CURRENT_DATE() - {self.change_days}
                UNION
                SELECT
                    PLAYLIST_HISTORIC_DATA.V_SPOTIFY_ID,
                    PLAYLIST_HISTORIC_DATA.SPOTIFY_PLAYLIST
                FROM PLAYLIST_HISTORIC_DATA
                WHERE
                    PLAYLIST_HISTORIC_DATA.END_SYS_PERIOD >= CURRENT_DATE() - {self.chart_days}
                    AND
                    PLAYLIST_HISTORIC_DATA.START_SYS_PERIOD < CURRENT_DATE() - {self.change_days}
            )
            GROUP BY
                V_SPOTIFY_ID
        )
        , PLAYLIST_LAST_28_DAYS AS (
            SELECT
                V_SPOTIFY_ID,
                COUNT(*) AS PLAYLIST_COUNT
            FROM (
                SELECT
                    PLAYLIST_DATA.V_SPOTIFY_ID,
                    PLAYLIST_DATA.SPOTIFY_PLAYLIST
                FROM PLAYLIST_DATA
                WHERE
                    PLAYLIST_DATA.START_SYS_PERIOD <= CURRENT_DATE() - {self.chart_days}
                UNION
                SELECT
                    PLAYLIST_HISTORIC_DATA.V_SPOTIFY_ID,
                    PLAYLIST_HISTORIC_DATA.SPOTIFY_PLAYLIST
                FROM PLAYLIST_HISTORIC_DATA
                WHERE
                    PLAYLIST_HISTORIC_DATA.END_SYS_PERIOD >= CURRENT_DATE() - {self.chart_days}
                    AND
                    PLAYLIST_HISTORIC_DATA.START_SYS_PERIOD < CURRENT_DATE() - {self.chart_days}
            )
            GROUP BY
                V_SPOTIFY_ID
        )
        , PLAYLIST_CHANGE_TREND_DATA AS (
            SELECT
                ID,
                SUM(PLAYLIST_COUNT) AS PLAYLIST_COUNT,
                SUM(PLAYLIST_COUNT_7) AS PLAYLIST_COUNT_7,
                SUM(PLAYLIST_COUNT_28) AS PLAYLIST_COUNT_28,
                SUM(PLAYLIST_CHANGE) AS PLAYLIST_CHANGE,
                SUM(PLAYLIST_CHANGE_28) AS PLAYLIST_CHANGE_28,
                DIV0NULL(SUM(PLAYLIST_CHANGE), SUM(PLAYLIST_COUNT_7)) * 100 AS TREND,
                DIV0NULL(SUM(PLAYLIST_CHANGE_28), SUM(PLAYLIST_COUNT_28)) * 100 AS TREND_28
            FROM (
                SELECT
                    BASIC_DATA.ID AS ID,
                    BASIC_DATA.V_SPOTIFY_ID AS V_SPOTIFY_ID,
                    PLAYLIST_CURRENT.PLAYLIST_COUNT,
                    PLAYLIST_LAST_7_DAYS.PLAYLIST_COUNT AS PLAYLIST_COUNT_7,
                    PLAYLIST_LAST_28_DAYS.PLAYLIST_COUNT AS PLAYLIST_COUNT_28,
                    PLAYLIST_CURRENT.PLAYLIST_COUNT - PLAYLIST_LAST_7_DAYS.PLAYLIST_COUNT AS PLAYLIST_CHANGE,
                    PLAYLIST_CURRENT.PLAYLIST_COUNT - PLAYLIST_LAST_28_DAYS.PLAYLIST_COUNT AS PLAYLIST_CHANGE_28
                FROM BASIC_DATA
                LEFT JOIN PLAYLIST_CURRENT
                    ON BASIC_DATA.V_SPOTIFY_ID = PLAYLIST_CURRENT.V_SPOTIFY_ID
                LEFT JOIN PLAYLIST_LAST_7_DAYS
                    ON BASIC_DATA.V_SPOTIFY_ID = PLAYLIST_LAST_7_DAYS.V_SPOTIFY_ID
                LEFT JOIN PLAYLIST_LAST_28_DAYS
                    ON BASIC_DATA.V_SPOTIFY_ID = PLAYLIST_LAST_28_DAYS.V_SPOTIFY_ID
            )
            GROUP BY
                ID
        )
        , MONTHLY_LISTENERS_HISTORICAL_POINT AS (
            SELECT
                BASIC_DATA.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                V_SPOTIFY_ARTIST_STAT.MONTHLY_LISTENERS AS MONTHLY_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
        )
        , STREAMS_HISTORICAL_POINT AS (
            SELECT
                BASIC_DATA.ID,
                MAX(V_SPOTIFY_PLAYS_STAT.PLAYS) AS STREAMS,
                V_SPOTIFY_PLAYS_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_PLAYS_STAT AS V_SPOTIFY_PLAYS_STAT
                ON BASIC_DATA.V_SPOTIFY_ID = V_SPOTIFY_PLAYS_STAT.SPOTIFY
            WHERE
                V_SPOTIFY_PLAYS_STAT.PLAYS IS NOT NULL
                AND V_SPOTIFY_PLAYS_STAT.TIMESTP <={HISTORICAL_CHART_DAY}
            GROUP BY
                BASIC_DATA.ID,
                V_SPOTIFY_PLAYS_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY BASIC_DATA.ID ORDER BY V_SPOTIFY_PLAYS_STAT.TIMESTP DESC) = 1
        )
        , MONTHLY_LISTENERS_CHART_DATA AS (
            SELECT
                SPOTIFY_ARTIST_ID,
                ARRAY_AGG(MONTHLY_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 MONTHLY_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 > {LAST_CHART_DAY}
                    AND V_SPOTIFY_ARTIST_STAT.TIMESTP <= {FIRST_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
        )
        , STREAMS_CHART_DATA AS (
            SELECT
                ID,
                POPULARITY_SCORE,
                ARRAY_AGG(STREAMS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    BASIC_DATA.ID AS ID,
                    BASIC_DATA.POPULARITY_SCORE,
                    MAX(V_SPOTIFY_PLAYS_STAT.PLAYS) AS STREAMS,
                    V_SPOTIFY_PLAYS_STAT.TIMESTP AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_PLAYS_STAT AS V_SPOTIFY_PLAYS_STAT
                    ON BASIC_DATA.V_SPOTIFY_ID = V_SPOTIFY_PLAYS_STAT.SPOTIFY
                WHERE
                    V_SPOTIFY_PLAYS_STAT.PLAYS IS NOT NULL AND V_SPOTIFY_PLAYS_STAT.PLAYS > 0
                    AND V_SPOTIFY_PLAYS_STAT.TIMESTP > {LAST_CHART_DAY}
                    AND V_SPOTIFY_PLAYS_STAT.TIMESTP <= {FIRST_CHART_DAY}
                GROUP BY
                    BASIC_DATA.ID,
                    BASIC_DATA.POPULARITY_SCORE,
                    V_SPOTIFY_PLAYS_STAT.TIMESTP
            )
            GROUP BY
                ID, POPULARITY_SCORE
            QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY POPULARITY_SCORE DESC) = 1
        )
        SELECT
            BASIC_DATA.ID AS ID,
            ANY_VALUE(BASIC_DATA.NAME) AS NAME,
            ANY_VALUE(BASIC_DATA.ARTWORK_URL) AS ARTWORK_URL,
            ANY_VALUE(BASIC_DATA.ISRC) AS ISRC,
            ANY_VALUE(BASIC_DATA.LABEL) AS LABEL,
            ANY_VALUE(BASIC_DATA.RELEASE_DATE) AS RELEASE_DATE,
            ANY_VALUE(BASIC_DATA.SPOTIFY_TRACK_ID) AS SPOTIFY_TRACK_ID,
            IFNULL(ANY_VALUE(BASIC_DATA.COUNTRY), '{N_A_VALUE}') AS COUNTRY,
            ANY_VALUE(BASIC_DATA.GENRES) AS GENRES,
            LISTAGG (DISTINCT BASIC_DATA.ARTIST, ',') AS ARTIST_NAME,
            LISTAGG (DISTINCT BASIC_DATA.CM_ARTIST_ID, ',') AS CM_ARTIST_ID,
            MAX(BASIC_DATA.POPULARITY_SCORE) AS POPULARITY,
            ANY_VALUE(PLAYLIST_CHANGE_TREND_DATA.PLAYLIST_COUNT) AS PLAYLISTS,
            ANY_VALUE(PLAYLIST_CHANGE_TREND_DATA.PLAYLIST_CHANGE) AS PLAYLISTS_CHANGE_7,
            ANY_VALUE(PLAYLIST_CHANGE_TREND_DATA.PLAYLIST_CHANGE_28) AS PLAYLISTS_CHANGE_28,
            ANY_VALUE(PLAYLIST_CHANGE_TREND_DATA.TREND) AS PLAYLISTS_TREND_7,
            ANY_VALUE(PLAYLIST_CHANGE_TREND_DATA.TREND_28) AS PLAYLISTS_TREND_28,
            ANY_VALUE(MONTHLY_LISTENERS_HISTORICAL_POINT.MONTHLY_LISTENERS) AS MONTHLY_LISTENERS_CHART_HISTORICAL_VALUE,
            ANY_VALUE(MONTHLY_LISTENERS_HISTORICAL_POINT.DATE) AS MONTHLY_LISTENERS_CHART_HISTORICAL_DATE,
            ANY_VALUE(STREAMS_HISTORICAL_POINT.STREAMS) AS STREAMS_CHART_HISTORICAL_VALUE,
            ANY_VALUE(STREAMS_HISTORICAL_POINT.DATE) AS STREAMS_CHART_HISTORICAL_DATE,
            IFNULL(ANY_VALUE(MONTHLY_LISTENERS_CHART_DATA.CHART), []) AS MONTHLY_LISTENERS_CHART,
            IFNULL(ANY_VALUE(MONTHLY_LISTENERS_CHART_DATA.CHART_DATES), [])  AS MONTHLY_LISTENERS_CHART_DATES,
            IFNULL(ANY_VALUE(STREAMS_CHART_DATA.CHART), []) AS STREAMS_CHART,
            IFNULL(ANY_VALUE(STREAMS_CHART_DATA.CHART_DATES), []) AS STREAMS_CHART_DATES
        FROM BASIC_DATA
        LEFT JOIN PLAYLIST_CHANGE_TREND_DATA
            ON BASIC_DATA.ID = PLAYLIST_CHANGE_TREND_DATA.ID
        LEFT JOIN MONTHLY_LISTENERS_HISTORICAL_POINT
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = MONTHLY_LISTENERS_HISTORICAL_POINT.SPOTIFY_ARTIST_ID
        LEFT JOIN STREAMS_HISTORICAL_POINT
            ON BASIC_DATA.ID = STREAMS_HISTORICAL_POINT.ID
        LEFT JOIN MONTHLY_LISTENERS_CHART_DATA
            ON BASIC_DATA.SPOTIFY_ARTIST_ID = MONTHLY_LISTENERS_CHART_DATA.SPOTIFY_ARTIST_ID
        LEFT JOIN STREAMS_CHART_DATA
            ON BASIC_DATA.ID = STREAMS_CHART_DATA.ID
        GROUP BY BASIC_DATA.ID
        ORDER BY BASIC_DATA.ID
        """