from app_types import S3Path
from constants import FIRST_CHART_DAY, LAST_CHART_DAY

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


__all__ = ["Extract"]


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

    def _get_latest_track_data_query(self, from_table: str) -> str:
        queries = [
            f"""
            SELECT
                ID,
                {FIRST_CHART_DAY} AS DATE,
                ANY_VALUE(VIEWS) AS VIEWS,
                ANY_VALUE(LIKES) AS LIKES,
                ANY_VALUE(COMMENTS) AS COMMENTS
            FROM {from_table}
            GROUP BY ID, VIDEO_YTID, DATE
            QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, VIDEO_YTID 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(VIEWS) AS VIEWS,
                    ANY_VALUE(LIKES) AS LIKES,
                    ANY_VALUE(COMMENTS) AS COMMENTS
                FROM {from_table}
                WHERE DATE <= DATEADD(Day, -{days}, CURRENT_DATE)
                GROUP BY ID, VIDEO_YTID, DATE
                QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, VIDEO_YTID ORDER BY DATE DESC) = 1
                """
            )

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

    @property
    def query(self):
        return f"""
        WITH BASIC_DATA AS (
            SELECT
                T_NON_SIGNED_TRACK.ID,
                T_NON_SIGNED_TRACK.ISRC,
                T_NON_SIGNED_TRACK.NAME,
                T_NON_SIGNED_TRACK.ARTWORK_URL,
                T_NON_SIGNED_TRACK.LABEL,
                T_NON_SIGNED_TRACK.RELEASE_DATE,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID,
                T_NON_SIGNED_TRACK.ARTIST_ID,
                ANY_VALUE(T_NON_SIGNED_ARTIST.NAME) AS ARTIST_NAME,
                ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2) AS COUNTRY,
                ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_TRACK_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES,
                V_YOUTUBE.ID AS V_YOUTUBE_ID,
                V_YOUTUBE.VIEWS_LATEST AS VIEWS_LATEST,
                MAX(V_YOUTUBE.UPLOAD_DATE) AS UPLOAD_DATE
            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 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_YOUTUBE AS V_YOUTUBE
                ON T_NON_SIGNED_TRACK.ISRC = V_YOUTUBE.ISRC AND V_YOUTUBE.VALID = TRUE
            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.RELEASE_DATE,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID,
                T_NON_SIGNED_TRACK.ARTIST_ID,
                V_YOUTUBE.ID,
                V_YOUTUBE.VIEWS_LATEST
        )
        , STATISTICS_RAW AS (
            SELECT
                BASIC_DATA.ID AS ID,
                YT_VIDEO_STATISTICS.YOUTUBE AS VIDEO_YTID,
                {LAST_CHART_DAY} AS DATE,
                ANY_VALUE(YT_VIDEO_STATISTICS.VIEWS) AS VIEWS,
                ANY_VALUE(YT_VIDEO_STATISTICS.LIKES) AS LIKES,
                ANY_VALUE(YT_VIDEO_STATISTICS.COMMENTS) AS COMMENTS
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.YOUTUBE_STAT AS YT_VIDEO_STATISTICS
                ON BASIC_DATA.V_YOUTUBE_ID = YT_VIDEO_STATISTICS.YOUTUBE
            WHERE
                TIMESTP <= {LAST_CHART_DAY}
            GROUP BY
                TIMESTP,
                BASIC_DATA.ID,
                YT_VIDEO_STATISTICS.YOUTUBE
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY BASIC_DATA.ID, YT_VIDEO_STATISTICS.YOUTUBE ORDER BY TIMESTP DESC) = 1
            UNION ALL
            SELECT
                BASIC_DATA.ID,
                YT_VIDEO_STATISTICS.YOUTUBE AS VIDEO_YTID,
                TO_DATE(YT_VIDEO_STATISTICS.TIMESTP) AS DATE,
                ANY_VALUE(YT_VIDEO_STATISTICS.VIEWS) AS VIEWS,
                ANY_VALUE(YT_VIDEO_STATISTICS.LIKES) AS LIKES,
                ANY_VALUE(YT_VIDEO_STATISTICS.COMMENTS) AS COMMENTS
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.YOUTUBE_STAT AS YT_VIDEO_STATISTICS
                ON BASIC_DATA.V_YOUTUBE_ID = YT_VIDEO_STATISTICS.YOUTUBE
            WHERE
                TIMESTP > {LAST_CHART_DAY}
                AND
                TIMESTP <= {FIRST_CHART_DAY}
            GROUP BY
                TIMESTP,
                BASIC_DATA.ID,
                YT_VIDEO_STATISTICS.YOUTUBE
        ),
        CHART_DATA AS (
            SELECT
                ID,
                ARRAY_AGG(VIEWS) WITHIN GROUP (ORDER BY DATE) AS VIEWS_CHART,
                ARRAY_AGG(LIKES) WITHIN GROUP (ORDER BY DATE) AS LIKES_CHART,
                ARRAY_AGG(COMMENTS) WITHIN GROUP (ORDER BY DATE) AS COMMENTS_CHART
            FROM (
                SELECT
                    ID,
                    DATE,
                    SUM(VIEWS) AS VIEWS,
                    SUM(LIKES) AS LIKES,
                    SUM(COMMENTS) AS COMMENTS
                FROM ({self._get_latest_track_data_query("STATISTICS_RAW")})
                GROUP BY
                    ID, DATE
            )
            GROUP BY
                ID
        )
        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,
            ANY_VALUE(BASIC_DATA.ARTIST_NAME) AS ARTIST_NAME,
            ANY_VALUE(BASIC_DATA.ARTIST_ID) AS CM_ARTIST_ID,
            IFNULL(ANY_VALUE(BASIC_DATA.COUNTRY), '{N_A_VALUE}') AS COUNTRY,
            ANY_VALUE(BASIC_DATA.GENRES) AS GENRES,
            COUNT( DISTINCT BASIC_DATA.V_YOUTUBE_ID) AS VIDEOS,
            MAX(TO_DATE(BASIC_DATA.UPLOAD_DATE)) AS LATEST_RELEASE_DATE,
            IFNULL(ANY_VALUE(CHART_DATA.COMMENTS_CHART), []) AS COMMENTS_CHART,
            IFNULL(ANY_VALUE(CHART_DATA.LIKES_CHART), []) AS LIKES_CHART,
            IFNULL(ANY_VALUE(CHART_DATA.VIEWS_CHART), []) AS VIEWS_CHART
        FROM BASIC_DATA
        LEFT JOIN CHART_DATA
            ON BASIC_DATA.ID = CHART_DATA.ID
        GROUP BY BASIC_DATA.ID
        ORDER BY BASIC_DATA.ID
        """