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_TIKTOK_RAW_DATA


__all__ = ["Extract"]


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

    def _get_posts_tt_data_query(self, from_table: str) -> str:
        queries = [
            f"""
            SELECT
                POSTS_DATA_RAW.ID,
                {FIRST_CHART_DAY} AS DATE,
                POSTS_DATA_RAW.TIKTOK,
                ANY_VALUE(POSTS_DATA_RAW.POSTS) AS POSTS
            FROM {from_table} AS POSTS_DATA_RAW
            WHERE
                POSTS_DATA_RAW.DATE <= {FIRST_CHART_DAY}
            GROUP BY
                POSTS_DATA_RAW.ID,
                POSTS_DATA_RAW.DATE,
                POSTS_DATA_RAW.TIKTOK
            QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, POSTS_DATA_RAW.TIKTOK ORDER BY DATE DESC NULLS LAST) = 1
            """
        ]
        for days in range(2, self.chart_days + 1):
            queries.append(
                f"""
                SELECT
                    POSTS_DATA_RAW.ID,
                    DATEADD(Day, -{days}, CURRENT_DATE) AS DATE,
                    POSTS_DATA_RAW.TIKTOK,
                    ANY_VALUE(POSTS_DATA_RAW.POSTS) AS POSTS
                FROM {from_table} AS POSTS_DATA_RAW
                WHERE
                    POSTS_DATA_RAW.DATE <= DATEADD(Day, -{days}, CURRENT_DATE)
                GROUP BY
                    POSTS_DATA_RAW.ID,
                    POSTS_DATA_RAW.DATE,
                    POSTS_DATA_RAW.TIKTOK
                QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, POSTS_DATA_RAW.TIKTOK ORDER BY DATE DESC NULLS LAST) = 1
                """
            )
        return " UNION ALL ".join(reversed(queries))

    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} WHERE DATE <= {FIRST_CHART_DAY}
            GROUP BY ID, DATE, TIKTOK, TIKTOK_VIDEO
            QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TIKTOK, TIKTOK_VIDEO 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, DATE, TIKTOK, TIKTOK_VIDEO
                QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, TIKTOK, TIKTOK_VIDEO 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.ARTWORK_URL,
                T_NON_SIGNED_TRACK.NAME,
                T_NON_SIGNED_TRACK.LABEL,
                T_NON_SIGNED_TRACK.ARTIST_ID,
                T_NON_SIGNED_TRACK.RELEASE_DATE,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_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_TIKTOK.ID AS V_TIKTOK_ID,
                V_TIKTOK.ARTIST AS TIKTOK_ARTIST
            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_TRACK.ARTIST_ID = T_NON_SIGNED_ARTIST.ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK AS V_TIKTOK
                ON T_NON_SIGNED_TRACK.ISRC = V_TIKTOK.ISRC AND V_TIKTOK.IS_DUPLICATE = FALSE AND V_TIKTOK.ACTIVE = TRUE
            GROUP BY
                T_NON_SIGNED_TRACK.ID,
                T_NON_SIGNED_TRACK.ISRC,
                T_NON_SIGNED_TRACK.ARTWORK_URL,
                T_NON_SIGNED_TRACK.NAME,
                T_NON_SIGNED_TRACK.LABEL,
                T_NON_SIGNED_TRACK.ARTIST_ID,
                T_NON_SIGNED_TRACK.RELEASE_DATE,
                T_NON_SIGNED_TRACK.SPOTIFY_TRACK_ID,
                V_TIKTOK.ID,
                V_TIKTOK.ARTIST
            )
            , TRACK_VIDEOS_AND_MENTIONS AS (
                SELECT
                    AGGREGATED_METRICS.ARTIST_ID,
                    AGGREGATED_METRICS.V_TIKTOK_ID,
                    SUM(AGGREGATED_METRICS.MENTIONS) AS MENTIONS
                FROM
                (
                    SELECT
                        BASIC_DATA.ARTIST_ID,
                        ZEROIFNULL(count(DISTINCT V_TIKTOK_VIDEO.ID)) AS COUNT_VIDEO,
                        ZEROIFNULL(count(DISTINCT VIDEO_MENTIONS.VIDEO_ID)) AS MENTIONS,
                        BASIC_DATA.V_TIKTOK_ID
                    FROM BASIC_DATA
                    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS V_TIKTOK_VIDEO
                        ON BASIC_DATA.V_TIKTOK_ID = V_TIKTOK_VIDEO.TRACK_ID
                    JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS VIDEO_MENTIONS
                        ON V_TIKTOK_VIDEO.ID = VIDEO_MENTIONS.ID
                    GROUP BY
                        BASIC_DATA.ARTIST_ID,
                        BASIC_DATA.V_TIKTOK_ID
                ) AS AGGREGATED_METRICS
                GROUP BY
                    AGGREGATED_METRICS.ARTIST_ID,
                    AGGREGATED_METRICS.V_TIKTOK_ID
            )
            , STATISTICS_RAW AS (
                SELECT
                    BASIC_DATA.ID,
                    {LAST_CHART_DAY} AS DATE,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO,
                    V_TIKTOK_TOP_VIDEOS_STAT.VIEWS,
                    V_TIKTOK_TOP_VIDEOS_STAT.LIKES,
                    V_TIKTOK_TOP_VIDEOS_STAT.COMMENTS
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                    ON BASIC_DATA.V_TIKTOK_ID = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK
                WHERE
                    V_TIKTOK_TOP_VIDEOS_STAT.VIEWS IS NOT NULL
                    AND
                    V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP <= {LAST_CHART_DAY}
                QUALIFY
                    ROW_NUMBER () OVER (
                    PARTITION BY
                        BASIC_DATA.ID, V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK, V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
                    ORDER BY V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP DESC) = 1
                UNION ALL
                SELECT
                    BASIC_DATA.ID,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP AS DATE,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK AS TIKTOK,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO AS TIKTOK_VIDEO,
                    MAX(V_TIKTOK_TOP_VIDEOS_STAT.VIEWS) AS VIEWS,
                    MAX(V_TIKTOK_TOP_VIDEOS_STAT.LIKES) AS LIKES,
                    MAX(V_TIKTOK_TOP_VIDEOS_STAT.COMMENTS) AS COMMENTS
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                    ON BASIC_DATA.V_TIKTOK_ID = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK
                WHERE
                    V_TIKTOK_TOP_VIDEOS_STAT.VIEWS IS NOT NULL
                    AND
                    V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP > {LAST_CHART_DAY}
                    AND
                    V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP <= {FIRST_CHART_DAY}
                GROUP BY
                    BASIC_DATA.ID,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
            )
            , ARRAY_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
            )
            ,POSTS_DATA_RAW AS (
                SELECT
                    BASIC_DATA.ID AS ID,
                    {LAST_CHART_DAY} AS DATE,
                    V_TIKTOK_STAT.TIKTOK AS TIKTOK,
                    V_TIKTOK_STAT.POSTS AS POSTS
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_STAT AS V_TIKTOK_STAT
                    ON BASIC_DATA.V_TIKTOK_ID = V_TIKTOK_STAT.TIKTOK
                WHERE
                    V_TIKTOK_STAT.TIMESTP <= {LAST_CHART_DAY}
                QUALIFY ROW_NUMBER() OVER(PARTITION BY BASIC_DATA.ID, V_TIKTOK_STAT.TIKTOK ORDER BY V_TIKTOK_STAT.TIMESTP DESC NULLS LAST) =1
                UNION ALL
                SELECT
                    BASIC_DATA.ID AS ID,
                    V_TIKTOK_STAT.TIMESTP AS DATE,
                    V_TIKTOK_STAT.TIKTOK AS TIKTOK,
                    V_TIKTOK_STAT.POSTS AS POSTS
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_STAT AS V_TIKTOK_STAT
                    ON BASIC_DATA.V_TIKTOK_ID = V_TIKTOK_STAT.TIKTOK
                WHERE
                    V_TIKTOK_STAT.TIMESTP > {LAST_CHART_DAY}
                    AND
                    V_TIKTOK_STAT.TIMESTP <= {FIRST_CHART_DAY}
            )
            , POSTS_ARRAY_DATA AS (
                SELECT
                    ID,
                    ARRAY_AGG(POSTS) WITHIN GROUP (ORDER BY DATE) AS POSTS_CHART
                FROM (
                SELECT
                    ID,
                    DATE,
                    SUM(POSTS) AS POSTS
                FROM
                (
                    {self._get_posts_tt_data_query("POSTS_DATA_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,
                SUM( DISTINCT TRACK_VIDEOS_AND_MENTIONS.MENTIONS) AS MENTIONS,
                IFNULL(ANY_VALUE(POSTS_ARRAY_DATA.POSTS_CHART), []) AS POSTS_LATEST_CHART,
                IFNULL(ANY_VALUE(ARRAY_DATA.COMMENTS_CHART), []) AS COMMENTS_CHART,
                IFNULL(ANY_VALUE(ARRAY_DATA.LIKES_CHART), []) AS LIKES_CHART,
                IFNULL(ANY_VALUE(ARRAY_DATA.VIEWS_CHART), []) AS VIEWS_CHART
            FROM BASIC_DATA
            LEFT JOIN TRACK_VIDEOS_AND_MENTIONS
                ON BASIC_DATA.V_TIKTOK_ID = TRACK_VIDEOS_AND_MENTIONS.V_TIKTOK_ID
            LEFT JOIN ARRAY_DATA
                ON BASIC_DATA.ID = ARRAY_DATA.ID
            LEFT JOIN POSTS_ARRAY_DATA
                ON BASIC_DATA.ID = POSTS_ARRAY_DATA.ID
            GROUP BY BASIC_DATA.ID
            ORDER BY BASIC_DATA.ID
        """