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_SOUNDCLOUD_RAW_DATA, N_A_VALUE


__all__ = ["Extract"]


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

    def _get_latest_track_data_query(self, from_table: str):
        queries = [
            f"""
                SELECT
                    ID,
                    {FIRST_CHART_DAY} AS DATE,
                    ANY_VALUE(STAT) AS STAT
                FROM {from_table}
                GROUP BY
                    ID, SCID, DATE
                QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, SCID ORDER BY DATE DESC) = 1
            """
        ]
        for days in range(2, self.chart_days + 1):
            queries.append(
                f"""
                    SELECT
                        ID,
                        DATEADD(Day, -{days}, current_date) AS DATE,
                        ANY_VALUE(STAT) AS STAT
                    FROM {from_table}
                    WHERE DATE <= DATEADD(Day, -{days}, current_date)
                    GROUP BY ID, SCID, DATE
                    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, SCID ORDER BY DATE DESC) = 1
                """
            )

        return " UNION ALL\n".join(reversed(queries))

    @property
    def query(self):
        return f"""
WITH T_NON_SIGNED_ARTIST AS (
    SELECT
        T_NON_SIGNED_ARTIST.ID AS ID,
        ANY_VALUE(SC) AS ACCOUNT_ID,
        ANY_VALUE(NAME) AS NAME,
        ANY_VALUE(SP) AS SPOTIFY_ARTIST_ID,
        ANY_VALUE(CODE2) AS CODE2,
        ANY_VALUE(LABEL) AS LABEL,
        ANY_VALUE(ARTWORK_URL) AS ARTWORK_URL,
        ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_ARTIST_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES
    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST
    LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_ARTIST_GENRE ON NON_SIGNED_ARTIST_GENRE.ID = T_NON_SIGNED_ARTIST.ID
    WHERE T_NON_SIGNED_ARTIST.SC IS NOT NULL
    GROUP BY T_NON_SIGNED_ARTIST.ID
), SOUNDCLOUD_ACCOUNTS AS (
    SELECT
        SOUNDCLOUD_ACCOUNT_DATA.SOUNDCLOUD_ID,
        SOUNDCLOUD_ACCOUNT_DATA.ACCOUNT_ID
    FROM T_NON_SIGNED_ARTIST
    JOIN (
        SELECT
            V_SOUNDCLOUD.ID AS SOUNDCLOUD_ID,
            (PARSE_JSON(V_SOUNDCLOUD.SOUNDCLOUD_USER)):id AS ACCOUNT_ID
        FROM DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
        WHERE
            V_SOUNDCLOUD.SOUNDCLOUD_USER IS NOT NULL
    ) AS SOUNDCLOUD_ACCOUNT_DATA
        ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = SOUNDCLOUD_ACCOUNT_DATA.ACCOUNT_ID
    GROUP BY
        SOUNDCLOUD_ACCOUNT_DATA.SOUNDCLOUD_ID,
        SOUNDCLOUD_ACCOUNT_DATA.ACCOUNT_ID
), CM_TRACK_COUNT_STAT AS (
    SELECT
         T_NON_SIGNED_ARTIST.ID,
         V_SOUNDCLOUD_USER_STAT.TRACK_COUNT
    FROM T_NON_SIGNED_ARTIST
    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_USER_STAT AS V_SOUNDCLOUD_USER_STAT
        ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID
    WHERE
        V_SOUNDCLOUD_USER_STAT.TRACK_COUNT IS NOT NULL
    QUALIFY ROW_NUMBER () OVER (PARTITION BY T_NON_SIGNED_ARTIST.ID ORDER BY  V_SOUNDCLOUD_USER_STAT.TIMESTP DESC NULLS LAST) = 1
), CM_LATEST_RELEASE_DATE_STAT AS (
    SELECT
        T_NON_SIGNED_ARTIST.ID,
        MAX(V_SOUNDCLOUD.CREATED_AT)::DATE AS LATEST_RELEASE_DATE
    FROM T_NON_SIGNED_ARTIST
    JOIN SOUNDCLOUD_ACCOUNTS
        ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = SOUNDCLOUD_ACCOUNTS.ACCOUNT_ID
    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
        ON SOUNDCLOUD_ACCOUNTS.SOUNDCLOUD_ID = V_SOUNDCLOUD.ID
    GROUP BY
        T_NON_SIGNED_ARTIST.ID
), ALL_TRACKS AS (
    SELECT
        T_NON_SIGNED_ARTIST.ID,
        V_SOUNDCLOUD_STAT.SOUNDCLOUD_ID AS SCID,
        V_SOUNDCLOUD_STAT.PLAYBACK_COUNT AS PLAYBACK_COUNT,
        V_SOUNDCLOUD_STAT.COMMENTS AS COMMENT_COUNT,
        V_SOUNDCLOUD_STAT.LIKES AS FAVORITINGS_COUNT,
        V_SOUNDCLOUD_STAT.TIMESTP AS AS_OF
    FROM T_NON_SIGNED_ARTIST
    JOIN (
        SELECT
            *
        FROM SOUNDCLOUD_ACCOUNTS
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_STAT AS V_SOUNDCLOUD_STAT
            ON SOUNDCLOUD_ACCOUNTS.SOUNDCLOUD_ID = V_SOUNDCLOUD_STAT.SOUNDCLOUD
        QUALIFY ROW_NUMBER() OVER (PARTITION BY V_SOUNDCLOUD_STAT.SOUNDCLOUD ORDER BY V_SOUNDCLOUD_STAT.TIMESTP DESC NULLS LAST) <= { self.chart_days +1 }
    ) AS V_SOUNDCLOUD_STAT
        ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = V_SOUNDCLOUD_STAT.ACCOUNT_ID
), FOLLOWERS_HISTORICAL_POINT AS (
    SELECT
        T_NON_SIGNED_ARTIST.ID,
        IFNULL(SOUNDCLOUD_USER_STAT.FOLLOWERS, SOUNDCLOUD_ARTIST.FOLLOWERS) AS FOLLOWERS,
        IFNULL(SOUNDCLOUD_USER_STAT.DATE, 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 DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_USER_STAT AS V_SOUNDCLOUD_USER_STAT
        JOIN T_NON_SIGNED_ARTIST
            ON V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID = T_NON_SIGNED_ARTIST.ACCOUNT_ID
        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 SOUNDCLOUD_USER_STAT
        ON T_NON_SIGNED_ARTIST.ID = 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.ACCOUNT_ID = V_SOUNDCLOUD_ARTIST.ID
        WHERE
            DATE <= {HISTORICAL_CHART_DAY}
    ) AS SOUNDCLOUD_ARTIST
        ON T_NON_SIGNED_ARTIST.ID = 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(SOUNDCLOUD_USER_STAT.FOLLOWERS, SOUNDCLOUD_ARTIST.FOLLOWERS) AS FOLLOWERS,
        IFNULL(SOUNDCLOUD_USER_STAT.DATE, 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 DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_USER_STAT AS V_SOUNDCLOUD_USER_STAT
        JOIN T_NON_SIGNED_ARTIST
            ON V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID = T_NON_SIGNED_ARTIST.ACCOUNT_ID
        WHERE
            V_SOUNDCLOUD_USER_STAT.TIMESTP <= {FIRST_CHART_DAY}
            AND
            V_SOUNDCLOUD_USER_STAT.TIMESTP >= {LAST_CHART_DAY}
        QUALIFY row_number() over (partition by V_SOUNDCLOUD_USER_STAT.ACCOUNT_ID ORDER BY V_SOUNDCLOUD_USER_STAT.TIMESTP DESC) = 1
    ) AS SOUNDCLOUD_USER_STAT
        ON T_NON_SIGNED_ARTIST.ID = 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.ACCOUNT_ID = V_SOUNDCLOUD_ARTIST.ID
        WHERE
            DATE <= {FIRST_CHART_DAY}
            AND
            DATE >= {LAST_CHART_DAY}
    ) AS SOUNDCLOUD_ARTIST
        ON T_NON_SIGNED_ARTIST.ID = SOUNDCLOUD_ARTIST.ID
    )
    GROUP BY ID
), COMMENTS_RAW AS (
  SELECT
    ID,
    SCID,
    STAT,
    MIN(DATE) AS DATE
  FROM (
    SELECT
        ID,
        SCID,
        {LAST_CHART_DAY} AS DATE,
        ANY_VALUE(COMMENT_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF <= {LAST_CHART_DAY}
        AND
        COMMENT_COUNT > 0
    GROUP BY ID, SCID, AS_OF
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, SCID ORDER BY AS_OF DESC) = 1 UNION ALL
    SELECT
        ID,
        SCID,
        AS_OF AS DATE,
        ANY_VALUE(COMMENT_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF <= {FIRST_CHART_DAY}
        AND
        AS_OF >= {LAST_CHART_DAY}
        AND
        COMMENT_COUNT > 0
    GROUP BY
        ID, SCID, AS_OF
  )
  GROUP BY ID, SCID, STAT
), STREAMS_RAW AS (
  SELECT
    ID,
    SCID,
    STAT,
    MIN(DATE) AS DATE
  FROM (
    SELECT
        ID,
        SCID,
        {LAST_CHART_DAY} AS DATE,
        ANY_VALUE(PLAYBACK_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF <= {LAST_CHART_DAY}
        AND
        PLAYBACK_COUNT > 0
    GROUP BY
        ID, SCID, AS_OF
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, SCID ORDER BY AS_OF DESC) = 1
    UNION ALL
    SELECT
        ID,
        SCID,
        AS_OF AS DATE,
        ANY_VALUE(PLAYBACK_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF >= {LAST_CHART_DAY}
        AND
        AS_OF <= {FIRST_CHART_DAY}
        AND
        PLAYBACK_COUNT > 0
    GROUP BY
        ID, SCID, AS_OF
  )
  GROUP BY
    ID, SCID, STAT
), LIKES_RAW AS (
  SELECT
    ID, SCID, STAT, MIN(DATE) AS DATE
  FROM (
    SELECT
        ID,
        SCID,
        {LAST_CHART_DAY} AS DATE,
        ANY_VALUE(FAVORITINGS_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF <= {LAST_CHART_DAY}
        AND
        FAVORITINGS_COUNT > 0
    GROUP BY
        ID, SCID, AS_OF
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, SCID ORDER BY AS_OF DESC) = 1 UNION ALL
    SELECT
        ID,
        SCID,
        AS_OF AS DATE,
        ANY_VALUE(FAVORITINGS_COUNT) AS STAT
    FROM ALL_TRACKS
    WHERE
        AS_OF <= {FIRST_CHART_DAY}
        AND
        AS_OF >= {LAST_CHART_DAY}
        AND
        FAVORITINGS_COUNT > 0
    GROUP BY
        ID, SCID, AS_OF
  )
  GROUP BY ID, SCID, STAT
), STREAMS AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE DESC) AS CHART
  FROM (
      SELECT
      ID,
      DATE,
      SUM (STAT) AS STAT
      FROM ({self._get_latest_track_data_query("STREAMS_RAW")}) GROUP BY ID, DATE
  ) GROUP BY ID
), LIKES AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE DESC) AS CHART
  FROM (
      SELECT
      ID,
      DATE,
      SUM (STAT) AS STAT
      FROM ({self._get_latest_track_data_query("LIKES_RAW")}) GROUP BY ID, DATE
  ) GROUP BY ID
), COMMENTS AS (
  SELECT ID, ARRAY_AGG(STAT) WITHIN GROUP (ORDER BY DATE DESC) AS CHART
  FROM (
      SELECT
      ID,
      DATE,
      SUM (STAT) AS STAT
      FROM ({self._get_latest_track_data_query("COMMENTS_RAW")}) GROUP BY ID, DATE
  ) GROUP BY ID
)
SELECT
    T_NON_SIGNED_ARTIST.ID AS ID,
    IFNULL(ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2), '{N_A_VALUE}') AS COUNTRY,
    ANY_VALUE(T_NON_SIGNED_ARTIST.GENRES) AS GENRES,
    ANY_VALUE(T_NON_SIGNED_ARTIST.NAME) AS NAME,
    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.SPOTIFY_ARTIST_ID) AS SPOTIFY_ARTIST_ID,
    IFNULL(ANY_VALUE(CM_LATEST_RELEASE_DATE_STAT.LATEST_RELEASE_DATE), MAX(WH_TRACKS.FIRST_SEEN)::date) AS LATEST_RELEASE_DATE, --double check once new tables are available
    IFNULL(ANY_VALUE(CM_TRACK_COUNT_STAT.TRACK_COUNT), ANY_VALUE(WH_USERS.LAST_TRACK_COUNT)) AS TRACKS,

    IFNULL(ANY_VALUE(FOLLOWERS.CHART), []) AS FOLLOWERS_CHART,
    IFNULL(ANY_VALUE(FOLLOWERS.CHART_DATES), []) AS FOLLOWERS_CHART_DATES,

    ANY_VALUE(FOLLOWERS_HISTORICAL_POINT.DATE) AS FOLLOWERS_CHART_HISTORICAL_DATE,
    ANY_VALUE(FOLLOWERS_HISTORICAL_POINT.FOLLOWERS) AS FOLLOWERS_CHART_HISTORICAL_VALUE,

    IFNULL(ANY_VALUE(COMMENTS.CHART), []) AS COMMENTS_CHART,
    IFNULL(ANY_VALUE(LIKES.CHART), []) AS LIKES_CHART,
    IFNULL(ANY_VALUE(STREAMS.CHART), []) AS PLAYS_CHART
FROM T_NON_SIGNED_ARTIST
LEFT JOIN WHITELIST_REPLICA.MAIN.SC_TRACKS_ALL AS WH_TRACKS ON TO_CHAR(WH_TRACKS.ARTIST_SCID) = T_NON_SIGNED_ARTIST.ACCOUNT_ID
LEFT JOIN CM_LATEST_RELEASE_DATE_STAT
    ON T_NON_SIGNED_ARTIST.ID = CM_LATEST_RELEASE_DATE_STAT.ID
LEFT JOIN WHITELIST_REPLICA.MAIN.SC_USERS AS WH_USERS ON TO_CHAR(WH_USERS.SCID) = T_NON_SIGNED_ARTIST.ACCOUNT_ID AND WH_USERS.ACTIVE = 1 AND WH_USERS.LAST_TRACK_COUNT > 0
LEFT JOIN CM_TRACK_COUNT_STAT
    ON T_NON_SIGNED_ARTIST.ID = CM_TRACK_COUNT_STAT.ID
LEFT JOIN COMMENTS ON COMMENTS.ID = T_NON_SIGNED_ARTIST.ID
LEFT JOIN LIKES ON LIKES.ID = T_NON_SIGNED_ARTIST.ID
LEFT JOIN STREAMS ON STREAMS.ID = T_NON_SIGNED_ARTIST.ID
LEFT JOIN FOLLOWERS ON FOLLOWERS.ID = T_NON_SIGNED_ARTIST.ID
LEFT JOIN FOLLOWERS_HISTORICAL_POINT ON FOLLOWERS_HISTORICAL_POINT.ID = FOLLOWERS.ID
GROUP BY T_NON_SIGNED_ARTIST.ID
ORDER BY T_NON_SIGNED_ARTIST.ID
"""