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


__all__ = ["Extract"]


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

    @property
    def query(self):
        return f"""
        WITH NON_SIGNED_TIKTOK_ACCOUNT_METRICS AS (
            SELECT
                T_NON_SIGNED_ARTIST.ID AS ID,
                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.NAME) AS NAME,
                ANY_VALUE(T_NON_SIGNED_ARTIST.SP) AS SPOTIFY_ARTIST_ID,
                ANY_VALUE(T_NON_SIGNED_ARTIST.CODE2) AS CODE2,
                ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_ARTIST_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES,
                ANY_VALUE(STAT.TIKTOK_USER) AS TIKTOK_USER,
                ANY_VALUE(STAT.VIDEOS) AS VIDEOS,
                ANY_VALUE(V_TIKTOK_USER.USER_ID) AS USER_ID,
                ANY_VALUE(V_TIKTOK_USER.USER_NAME) AS user_name
        FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
        LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_ARTIST_GENRE ON NON_SIGNED_ARTIST_GENRE.ID = T_NON_SIGNED_ARTIST.ID
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER AS V_TIKTOK_USER
            ON T_NON_SIGNED_ARTIST.TT = V_TIKTOK_USER.USER_ID
            AND T_NON_SIGNED_ARTIST.TT IS NOT NULL
            JOIN (
                SELECT
                    V_TIKTOK_USER_STAT.TIKTOK_USER AS TIKTOK_USER,
                    MAX(ID) AS MAX_ID
                FROM DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS V_TIKTOK_USER_STAT
                GROUP BY
                    V_TIKTOK_USER_STAT.TIKTOK_USER
            ) AS STAT2
            ON V_TIKTOK_USER.ID = STAT2.TIKTOK_USER
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS STAT
                ON STAT.ID = STAT2.MAX_ID
         GROUP BY T_NON_SIGNED_ARTIST.ID
        )
        , ARTIST_VIDEO_METRICS AS (
        SELECT
            TT.TIKTOK_USER,
            cast(IFNULL(SUM(VIDEOS.DURATION) / COUNT(VIDEOS.ID), 0) AS integer) AS AVG_DURATION,
            MAX(VIDEOS.TIMESTP) as MAX_DATE
        FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS AS TT
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO as VIDEOS
            ON TT.USER_ID = VIDEOS.USER_ID
        GROUP BY
            TT.TIKTOK_USER
        )
        , ARTIST_MENTIONES AS (
        SELECT
            TT.TIKTOK_USER,
            ZEROIFNULL(COUNT(DISTINCT VIDEO_MENTIONS.VIDEO_ID)) AS MENTIONS
        FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS AS TT
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK AS TRACK
            ON TRACK.ARTIST = TT.Name AND TRACK.IS_DUPLICATE  = FALSE
        JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS VIDEO
            ON VIDEO.TRACK_ID = TRACK.ID
        LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS VIDEO_MENTIONS
            ON VIDEO.ID = VIDEO_MENTIONS.ID
                AND regexp_like(VIDEO_MENTIONS.DESCRIPTION, concat('.*', TT.USER_NAME, '.*'), 'i')
        GROUP BY
            TT.TIKTOK_USER
        )
        , FOLLOWERS_HISTORICAL_POINT AS (
            SELECT
                V_TIKTOK_USER_STAT.TIKTOK_USER AS TIKTOK_USER,
                V_TIKTOK_USER_STAT.FOLLOWERS AS FOLLOWERS,
                V_TIKTOK_USER_STAT.TIMESTP::date AS DATE
            FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS V_TIKTOK_USER_STAT
                ON NON_SIGNED_TIKTOK_ACCOUNT_METRICS.TIKTOK_USER = V_TIKTOK_USER_STAT.TIKTOK_USER
            WHERE
                V_TIKTOK_USER_STAT.FOLLOWERS > 0
                AND V_TIKTOK_USER_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                V_TIKTOK_USER_STAT.TIKTOK_USER,
                V_TIKTOK_USER_STAT.FOLLOWERS,
                V_TIKTOK_USER_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY V_TIKTOK_USER_STAT.TIKTOK_USER ORDER BY V_TIKTOK_USER_STAT.TIMESTP DESC) = 1
        )
        , FOLLOWERS AS (
            SELECT
                TIKTOK_USER,
                ARRAY_AGG(FOLLOWERS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    V_TIKTOK_USER_STAT.TIKTOK_USER AS TIKTOK_USER,
                    V_TIKTOK_USER_STAT.FOLLOWERS AS FOLLOWERS,
                    V_TIKTOK_USER_STAT.TIMESTP  AS DATE
                FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS V_TIKTOK_USER_STAT
                    ON NON_SIGNED_TIKTOK_ACCOUNT_METRICS.TIKTOK_USER = V_TIKTOK_USER_STAT.TIKTOK_USER
                WHERE
                    V_TIKTOK_USER_STAT.FOLLOWERS IS NOT NULL
                    AND V_TIKTOK_USER_STAT.TIMESTP >= {LAST_CHART_DAY}
                    AND V_TIKTOK_USER_STAT.TIMESTP <= {FIRST_CHART_DAY}
                GROUP BY
                    V_TIKTOK_USER_STAT.TIKTOK_USER,
                    V_TIKTOK_USER_STAT.FOLLOWERS,
                    V_TIKTOK_USER_STAT.TIMESTP
            )
            GROUP BY
                TIKTOK_USER
        )
        , LIKES_HISTORICAL_POINT AS (
            SELECT
                V_TIKTOK_USER_STAT.TIKTOK_USER AS TIKTOK_USER,
                CAST(V_TIKTOK_USER_STAT.LIKES AS INTEGER) AS LIKES,
                V_TIKTOK_USER_STAT.TIMESTP::date AS DATE
            FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS V_TIKTOK_USER_STAT
                ON NON_SIGNED_TIKTOK_ACCOUNT_METRICS.TIKTOK_USER = V_TIKTOK_USER_STAT.TIKTOK_USER
            WHERE
                V_TIKTOK_USER_STAT.LIKES IS NOT NULL
                AND V_TIKTOK_USER_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                V_TIKTOK_USER_STAT.TIKTOK_USER,
                V_TIKTOK_USER_STAT.LIKES,
                V_TIKTOK_USER_STAT.TIMESTP
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY V_TIKTOK_USER_STAT.TIKTOK_USER ORDER BY V_TIKTOK_USER_STAT.TIMESTP DESC) = 1
        )
        , LIKES AS (
            SELECT
                TIKTOK_USER,
                ARRAY_AGG(LIKES) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    V_TIKTOK_USER_STAT.TIKTOK_USER AS TIKTOK_USER,
                    CAST(V_TIKTOK_USER_STAT.LIKES AS INTEGER) AS LIKES,
                    V_TIKTOK_USER_STAT.TIMESTP  AS DATE
                FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS V_TIKTOK_USER_STAT
                    ON NON_SIGNED_TIKTOK_ACCOUNT_METRICS.TIKTOK_USER = V_TIKTOK_USER_STAT.TIKTOK_USER
                WHERE
                    V_TIKTOK_USER_STAT.LIKES IS NOT NULL
                    AND V_TIKTOK_USER_STAT.TIMESTP >= {LAST_CHART_DAY}
                    AND V_TIKTOK_USER_STAT.TIMESTP <= {FIRST_CHART_DAY}
                GROUP BY
                    V_TIKTOK_USER_STAT.TIKTOK_USER,
                    V_TIKTOK_USER_STAT.LIKES,
                    V_TIKTOK_USER_STAT.TIMESTP
            )
            GROUP BY
                TIKTOK_USER
        )
        SELECT
            TT_ACCOUNT_METRICS.ID AS ID,
            ANY_VALUE(TT_ACCOUNT_METRICS.LABEL) AS LABEL,
            ANY_VALUE(TT_ACCOUNT_METRICS.ARTWORK_URL) AS ARTWORK_URL,
            ANY_VALUE(TT_ACCOUNT_METRICS.NAME) AS NAME,
            ANY_VALUE(TT_ACCOUNT_METRICS.SPOTIFY_ARTIST_ID) AS SPOTIFY_ARTIST_ID,
            IFNULL(ANY_VALUE(TT_ACCOUNT_METRICS.CODE2), '{N_A_VALUE}') AS COUNTRY,
            ANY_VALUE(TT_ACCOUNT_METRICS.GENRES) AS GENRES,
            ANY_VALUE(TT_ACCOUNT_METRICS.TIKTOK_USER) AS TIKTOK_USER,
            ANY_VALUE(TT_ACCOUNT_METRICS.VIDEOS) AS VIDEOS,
            ANY_VALUE(TT_ACCOUNT_METRICS.USER_NAME) AS USER_NAME,
            ZEROIFNULL(ANY_VALUE(ARTIST_VIDEO_METRICS.AVG_DURATION)) AS AVG_DURATION,
            ZEROIFNULL(ANY_VALUE(ARTIST_MENTIONES.MENTIONS)) AS MENTIONS,
            TO_DATE(ANY_VALUE(ARTIST_VIDEO_METRICS.MAX_DATE)) AS LATEST_RELEASE_DATE,
            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(LIKES.CHART), []) AS LIKES_CHART,
            IFNULL(ANY_VALUE(LIKES.CHART_DATES), []) AS LIKES_CHART_DATES,
            ANY_VALUE(LIKES_HISTORICAL_POINT.DATE) AS LIKES_CHART_HISTORICAL_DATE,
            ANY_VALUE(LIKES_HISTORICAL_POINT.LIKES) AS LIKES_CHART_HISTORICAL_VALUE
        FROM NON_SIGNED_TIKTOK_ACCOUNT_METRICS AS TT_ACCOUNT_METRICS
        LEFT JOIN ARTIST_VIDEO_METRICS
            ON ARTIST_VIDEO_METRICS.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        LEFT JOIN ARTIST_MENTIONES
            ON ARTIST_MENTIONES.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        LEFT JOIN FOLLOWERS_HISTORICAL_POINT
            ON FOLLOWERS_HISTORICAL_POINT.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        LEFT JOIN FOLLOWERS
            ON FOLLOWERS.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        LEFT JOIN LIKES_HISTORICAL_POINT
            ON LIKES_HISTORICAL_POINT.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        LEFT JOIN LIKES
            ON LIKES.TIKTOK_USER = TT_ACCOUNT_METRICS.TIKTOK_USER
        GROUP BY TT_ACCOUNT_METRICS.ID
        ORDER BY TT_ACCOUNT_METRICS.ID
        """