from .....preparation import PrepareNonSignedArtist
from ....base import AggregatesExtractBase
from ....constants import ARTIST_VIDEOS_TIKTOK_RAW_DATA_PATH, RAW_ARTIST_ID_KEY


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedArtist}
    raw_data_path: str = str(ARTIST_VIDEOS_TIKTOK_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    query: str = """
        SELECT
            CM_ARTIST_ID,
            USERNAME,
            VIDEO_ID,
            DESCRIPTION,
            LIKES,
            VIEWS
        FROM (
            SELECT
                MAX_ID_DATA.CM_ARTIST_ID,
                V_TIKTOK_VIDEO.USERNAME,
                V_TIKTOK_VIDEO.VIDEO_ID,
                V_TIKTOK_VIDEO.DESCRIPTION,
                V_TIKTOK_TOP_VIDEOS_STAT.LIKES,
                V_TIKTOK_TOP_VIDEOS_STAT.VIEWS,
                ROW_NUMBER() OVER (PARTITION BY MAX_ID_DATA.CM_ARTIST_ID ORDER BY V_TIKTOK_TOP_VIDEOS_STAT.VIEWS DESC NULLS LAST) AS TOP_VIEWS_TIKTOK
            FROM (
                SELECT
                    T_NON_SIGNED_ARTIST.ID AS CM_ARTIST_ID,
                    MAX(V_TIKTOK_TOP_VIDEOS_STAT.ID) AS MAX_ID
                FROM (
                    SELECT
                        T_NON_SIGNED_ARTIST.ID,
                        T_NON_SIGNED_ARTIST.TT AS ACCOUNT_ID
                    FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
                    WHERE
                        T_NON_SIGNED_ARTIST.TT IS NOT NULL
                ) AS T_NON_SIGNED_ARTIST
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS V_TIKTOK_VIDEO
                    ON V_TIKTOK_VIDEO.USER_ID  = T_NON_SIGNED_ARTIST.ACCOUNT_ID
                LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                    ON V_TIKTOK_VIDEO.ID = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
                GROUP BY
                    T_NON_SIGNED_ARTIST.ID,
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
            ) AS MAX_ID_DATA
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                ON V_TIKTOK_TOP_VIDEOS_STAT.ID = MAX_ID_DATA.MAX_ID
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS V_TIKTOK_VIDEO
                ON V_TIKTOK_VIDEO.ID = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
            QUALIFY
                TOP_VIEWS_TIKTOK <= 6 -- LIMIT OF top popular artist tiktok video
        )
    """