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_SOUNDCLOUD_RAW_DATA


__all__ = ["Extract"]


class Extract(SearchExtractBase):
    depends_on = {TrackGenre}
    raw_data_path: S3Path = TRACK_SOUNDCLOUD_RAW_DATA

    @property
    def query(self):
        return f"""
WITH T_NON_SIGNED_TRACK AS (
  SELECT
    T_NON_SIGNED_TRACK.ID AS ID,
    ANY_VALUE(T_NON_SIGNED_TRACK.ARTIST_ID) AS ARTIST_ID,
    ANY_VALUE(T_NON_SIGNED_TRACK.ISRC) AS ISRC,
    ANY_VALUE(T_NON_SIGNED_TRACK.NAME) AS NAME,
    ANY_VALUE(T_NON_SIGNED_TRACK.LABEL) AS LABEL,
    ANY_VALUE(T_NON_SIGNED_TRACK.ARTWORK_URL) AS ARTWORK_URL,
    ANY_VALUE(T_NON_SIGNED_TRACK.RELEASE_DATE) AS RELEASE_DATE,
    ANY_VALUE(T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID) AS SPOTIFY_TRACK_ID,
    MAX(V_SOUNDCLOUD.CREATED_AT) AS CREATED_AT,
    ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_TRACK_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES,
    V_SOUNDCLOUD.ID AS SC_ID
  FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
  LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_TRACK_GENRE ON NON_SIGNED_TRACK_GENRE.ID = T_NON_SIGNED_TRACK.ID
  JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
    ON V_SOUNDCLOUD.ISRC = T_NON_SIGNED_TRACK.ISRC
  GROUP BY T_NON_SIGNED_TRACK.ID, V_SOUNDCLOUD.ID -- I'm not sure that grouping by V_SOUNDCLOUD.ID is valid appraoch. But it was before me
)
, T_NON_SIGNED_ARTIST AS (
    SELECT
        *
    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
    WHERE
        T_NON_SIGNED_ARTIST.SC IS NOT NULL
)
, MAX_PLAYBACK_COUNT_TRACKS AS (
  SELECT
      T_NON_SIGNED_TRACK.ID AS ID,
      T_NON_SIGNED_TRACK.SC_ID
  FROM T_NON_SIGNED_TRACK
  JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_STAT AS V_SOUNDCLOUD_STAT
      ON V_SOUNDCLOUD_STAT.SOUNDCLOUD = T_NON_SIGNED_TRACK.SC_ID
  QUALIFY ROW_NUMBER() OVER (PARTITION BY T_NON_SIGNED_TRACK.ID ORDER BY V_SOUNDCLOUD_STAT.PLAYBACK_COUNT DESC NULLS LAST) = 1
)
, ALL_TRACKS AS (
  SELECT
      T_NON_SIGNED_TRACK.ID AS ID,
      V_SOUNDCLOUD_STAT.TIMESTP AS DATE,
      MAX(V_SOUNDCLOUD_STAT.PLAYBACK_COUNT) AS PLAYBACK_COUNT,
      MAX(V_SOUNDCLOUD_STAT.COMMENTS) AS COMMENT_COUNT,
      MAX(V_SOUNDCLOUD_STAT.LIKES) AS FAVORITINGS_COUNT
  FROM T_NON_SIGNED_TRACK
  JOIN MAX_PLAYBACK_COUNT_TRACKS
    ON T_NON_SIGNED_TRACK.SC_ID = MAX_PLAYBACK_COUNT_TRACKS.SC_ID
  JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_STAT AS V_SOUNDCLOUD_STAT
    ON V_SOUNDCLOUD_STAT.SOUNDCLOUD = T_NON_SIGNED_TRACK.SC_ID
  GROUP BY
    T_NON_SIGNED_TRACK.ID, V_SOUNDCLOUD_STAT.TIMESTP
  QUALIFY
    ROW_NUMBER() OVER (PARTITION BY T_NON_SIGNED_TRACK.ID ORDER BY V_SOUNDCLOUD_STAT.TIMESTP DESC NULLS LAST) <= {self.chart_days + 1}
)
, STREAMS AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE) AS CHART, ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
  FROM (
    SELECT ID, DATE, ANY_VALUE(PLAYBACK_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE > {LAST_CHART_DAY}
        AND
        DATE <= {FIRST_CHART_DAY}
        AND
        PLAYBACK_COUNT > 0
    GROUP BY ID, DATE
  ) GROUP BY ID
), LIKES AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE) AS CHART, ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
  FROM (
    SELECT ID, DATE, ANY_VALUE(FAVORITINGS_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE > {LAST_CHART_DAY}
        AND
        DATE <= {FIRST_CHART_DAY}
        AND
        FAVORITINGS_COUNT > 0
    GROUP BY ID, DATE
  ) GROUP BY ID
), COMMENTS AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE) AS CHART, ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
  FROM (
    SELECT ID, DATE, ANY_VALUE(COMMENT_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE > {LAST_CHART_DAY}
        AND
        DATE <= {FIRST_CHART_DAY}
        AND
        COMMENT_COUNT > 0
    GROUP BY ID, DATE
  ) GROUP BY ID
), STREAMS_HISTORICAL_POINT AS (
    SELECT ID, DATE, ANY_VALUE(PLAYBACK_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE <= {HISTORICAL_CHART_DAY}
        AND
        PLAYBACK_COUNT > 0
    GROUP BY ID, DATE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) = 1
), LIKES_HISTORICAL_POINT AS (
    SELECT ID, DATE, ANY_VALUE(FAVORITINGS_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE <= {HISTORICAL_CHART_DAY}
        AND FAVORITINGS_COUNT > 0
    GROUP BY ID, DATE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) = 1
), COMMENTS_HISTORICAL_POINT AS (
    SELECT ID, DATE, ANY_VALUE(COMMENT_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        DATE <= {HISTORICAL_CHART_DAY}
        AND COMMENT_COUNT > 0
    GROUP BY ID, DATE
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) = 1
),
FOLLOWERS_HISTORICAL_POINT AS (
    SELECT
        T_NON_SIGNED_ARTIST.ID,
        IFNULL(OLD_SOUNDCLOUD_USER_STAT.FOLLOWERS, OLD_SOUNDCLOUD_ARTIST.FOLLOWERS) AS STAT,
        IFNULL(OLD_SOUNDCLOUD_USER_STAT.DATE, OLD_SOUNDCLOUD_ARTIST.DATE) AS DATE
    FROM T_NON_SIGNED_ARTIST
    LEFT JOIN (
        SELECT
            T_NON_SIGNED_ARTIST.ID AS ID,
            V_SOUNDCLOUD_USER_STAT.TIMESTP::date AS DATE,
            V_SOUNDCLOUD_USER_STAT.FOLLOWERS
        FROM T_NON_SIGNED_ARTIST
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_USER_STAT AS V_SOUNDCLOUD_USER_STAT
            ON V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID = T_NON_SIGNED_ARTIST.SC
        WHERE
            V_SOUNDCLOUD_USER_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
        QUALIFY row_number() over (partition by V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID ORDER BY V_SOUNDCLOUD_USER_STAT.TIMESTP DESC) = 1
    ) AS OLD_SOUNDCLOUD_USER_STAT
        ON T_NON_SIGNED_ARTIST.ID = OLD_SOUNDCLOUD_USER_STAT.ID
    LEFT JOIN (
        SELECT
            T_NON_SIGNED_ARTIST.ID,
            V_SOUNDCLOUD_ARTIST.FOLLOWERS_COUNT AS FOLLOWERS,
            V_SOUNDCLOUD_ARTIST.MODIFIED_AT::date AS DATE
        FROM T_NON_SIGNED_ARTIST
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_ARTIST AS V_SOUNDCLOUD_ARTIST
            ON T_NON_SIGNED_ARTIST.SC = V_SOUNDCLOUD_ARTIST.ID
        WHERE
            DATE <= {HISTORICAL_CHART_DAY}
    ) AS OLD_SOUNDCLOUD_ARTIST
        ON T_NON_SIGNED_ARTIST.ID = OLD_SOUNDCLOUD_ARTIST.ID
)
, FOLLOWERS AS (
    SELECT
        ID,
        ARRAY_AGG(FOLLOWERS) WITHIN GROUP (ORDER BY DATE) AS CHART,
        ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE)      AS CHART_DATES
    FROM (
        SELECT
            T_NON_SIGNED_ARTIST.ID,
            IFNULL(OLD_SOUNDCLOUD_USER_STAT.FOLLOWERS, OLD_SOUNDCLOUD_ARTIST.FOLLOWERS) AS FOLLOWERS,
            IFNULL(OLD_SOUNDCLOUD_USER_STAT.DATE, OLD_SOUNDCLOUD_ARTIST.DATE) AS DATE
        FROM T_NON_SIGNED_ARTIST
        LEFT JOIN (
            SELECT
                T_NON_SIGNED_ARTIST.ID AS ID,
                V_SOUNDCLOUD_USER_STAT.TIMESTP::date AS DATE,
                V_SOUNDCLOUD_USER_STAT.FOLLOWERS
            FROM T_NON_SIGNED_ARTIST
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_USER_STAT AS V_SOUNDCLOUD_USER_STAT
                ON V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID = T_NON_SIGNED_ARTIST.SC
            WHERE
                V_SOUNDCLOUD_USER_STAT.TIMESTP > {LAST_CHART_DAY}
                AND
                V_SOUNDCLOUD_USER_STAT.TIMESTP <= {FIRST_CHART_DAY}
        ) AS OLD_SOUNDCLOUD_USER_STAT
            ON T_NON_SIGNED_ARTIST.ID = OLD_SOUNDCLOUD_USER_STAT.ID
        LEFT JOIN (
            SELECT
                T_NON_SIGNED_ARTIST.ID,
                V_SOUNDCLOUD_ARTIST.FOLLOWERS_COUNT AS FOLLOWERS,
                V_SOUNDCLOUD_ARTIST.MODIFIED_AT::date AS DATE
            FROM T_NON_SIGNED_ARTIST
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_ARTIST AS V_SOUNDCLOUD_ARTIST
                ON T_NON_SIGNED_ARTIST.SC = V_SOUNDCLOUD_ARTIST.ID
            WHERE
                DATE > {LAST_CHART_DAY}
                AND
                DATE <= {FIRST_CHART_DAY}
        ) AS OLD_SOUNDCLOUD_ARTIST
            ON T_NON_SIGNED_ARTIST.ID = OLD_SOUNDCLOUD_ARTIST.ID
    )
    GROUP BY ID
)
SELECT
    T_NON_SIGNED_TRACK.ID AS ID,
    ANY_VALUE(T_NON_SIGNED_TRACK.ARTWORK_URL) AS ARTWORK_URL,
    ANY_VALUE(T_NON_SIGNED_TRACK.ISRC) AS ISRC,
    ANY_VALUE(T_NON_SIGNED_TRACK.NAME) AS NAME,
    ANY_VALUE(T_NON_SIGNED_TRACK.LABEL) AS LABEL,
    ANY_VALUE(T_NON_SIGNED_TRACK.ARTIST_ID) AS CM_ARTIST_ID,
    ANY_VALUE(T_NON_SIGNED_ARTIST.NAME) AS ARTIST_NAME,
    IFNULL(ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2), '{N_A_VALUE}') AS COUNTRY,
    ANY_VALUE(T_NON_SIGNED_TRACK.GENRES) AS GENRES,
    ANY_VALUE(T_NON_SIGNED_TRACK.RELEASE_DATE) AS RELEASE_DATE,
    ANY_VALUE(T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID) AS SPOTIFY_TRACK_ID,
    IFNULL(CAST(MAX(T_NON_SIGNED_TRACK.CREATED_AT) AS DATE), MAX(WH_SC_TRACKS.CREATED_AT)) AS LATEST_RELEASE_DATE,
    ANY_VALUE(T_NON_SIGNED_TRACK.SC_ID) AS SOUNDCLOUD_TRACK_ID,

    IFNULL(ANY_VALUE(STREAMS.CHART), []) AS PLAYS_CHART,
    IFNULL(ANY_VALUE(STREAMS.CHART_DATES), []) AS PLAYS_CHART_DATES,
    IFNULL(ANY_VALUE(LIKES.CHART), []) AS LIKES_CHART,
    IFNULL(ANY_VALUE(LIKES.CHART_DATES), []) AS LIKES_CHART_DATES,
    IFNULL(ANY_VALUE(COMMENTS.CHART), []) AS COMMENTS_CHART,
    IFNULL(ANY_VALUE(COMMENTS.CHART_DATES), []) AS COMMENTS_CHART_DATES,
    IFNULL(ANY_VALUE(FOLLOWERS.CHART), []) AS FOLLOWERS_CHART,
    IFNULL(ANY_VALUE(FOLLOWERS.CHART_DATES), []) AS FOLLOWERS_CHART_DATES,

    ANY_VALUE(STREAMS_HISTORICAL_POINT.DATE) AS PLAYS_CHART_HISTORICAL_DATE,
    ANY_VALUE(STREAMS_HISTORICAL_POINT.STAT) AS PLAYS_CHART_HISTORICAL_VALUE,
    ANY_VALUE(LIKES_HISTORICAL_POINT.DATE) AS LIKES_CHART_HISTORICAL_DATE,
    ANY_VALUE(LIKES_HISTORICAL_POINT.STAT) AS LIKES_CHART_HISTORICAL_VALUE,
    ANY_VALUE(COMMENTS_HISTORICAL_POINT.DATE) AS COMMENTS_CHART_HISTORICAL_DATE,
    ANY_VALUE(COMMENTS_HISTORICAL_POINT.STAT) AS COMMENTS_CHART_HISTORICAL_VALUE,
    ANY_VALUE(FOLLOWERS_HISTORICAL_POINT.DATE) AS FOLLOWERS_CHART_HISTORICAL_DATE,
    ANY_VALUE(FOLLOWERS_HISTORICAL_POINT.STAT) AS FOLLOWERS_CHART_HISTORICAL_VALUE
FROM T_NON_SIGNED_TRACK
LEFT 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
LEFT JOIN WHITELIST_REPLICA.MAIN.SC_TRACKS_ALL AS WH_SC_TRACKS ON T_NON_SIGNED_TRACK.SC_ID = WH_SC_TRACKS.SCID
LEFT JOIN STREAMS ON T_NON_SIGNED_TRACK.ID = STREAMS.ID
LEFT JOIN STREAMS_HISTORICAL_POINT ON T_NON_SIGNED_TRACK.ID = STREAMS_HISTORICAL_POINT.ID
LEFT JOIN LIKES ON T_NON_SIGNED_TRACK.ID = LIKES.ID
LEFT JOIN LIKES_HISTORICAL_POINT ON T_NON_SIGNED_TRACK.ID = LIKES_HISTORICAL_POINT.ID
LEFT JOIN COMMENTS ON T_NON_SIGNED_TRACK.ID = COMMENTS.ID
LEFT JOIN COMMENTS_HISTORICAL_POINT ON T_NON_SIGNED_TRACK.ID = COMMENTS_HISTORICAL_POINT.ID
LEFT JOIN FOLLOWERS ON T_NON_SIGNED_TRACK.ARTIST_ID = FOLLOWERS.ID
LEFT JOIN FOLLOWERS_HISTORICAL_POINT ON T_NON_SIGNED_TRACK.ARTIST_ID = FOLLOWERS_HISTORICAL_POINT.ID
GROUP BY T_NON_SIGNED_TRACK.ID
ORDER BY T_NON_SIGNED_TRACK.ID
"""