from .....preparation import PrepareNonSignedTrack
from ....base import AggregatesExtractBase
from ....constants import RAW_TRACK_ID_KEY, TRACK_VIDEOS_TIKTOK_RAW_DATA_PATH


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedTrack}
    raw_data_path: str = str(TRACK_VIDEOS_TIKTOK_RAW_DATA_PATH)
    partition_key: str = RAW_TRACK_ID_KEY
    query: str = """
        WITH TIKTOK_BASIC_DATA AS (
            SELECT
                T_NON_SIGNED_TRACK.ID AS CM_TRACK_ID,
                V_TIKTOK_VIDEO.VIDEO_ID AS VIDEO_ID,
                V_TIKTOK_VIDEO.USERNAME AS USERNAME,
                V_TIKTOK_VIDEO.ID AS V_TIKTOK_VIDEO_ID,
                V_TIKTOK_VIDEO.DESCRIPTION AS DESCRIPTION
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK AS V_TIKTOK
                ON T_NON_SIGNED_TRACK.ISRC = V_TIKTOK.ISRC
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_VIDEO AS V_TIKTOK_VIDEO
                ON V_TIKTOK.ID = V_TIKTOK_VIDEO.TRACK_ID
            GROUP BY T_NON_SIGNED_TRACK.ID, V_TIKTOK_VIDEO.VIDEO_ID, V_TIKTOK_VIDEO.USERNAME, V_TIKTOK_VIDEO.ID, V_TIKTOK_VIDEO.THUMBNAIL_URL, V_TIKTOK_VIDEO.DESCRIPTION
        )
        ,TIKTOK_VIDEO_METRICS AS (
            SELECT
                MAX_DATES_DATA.TIKTOK_VIDEO AS TIKTOK_VIDEO,
                V_TIKTOK_TOP_VIDEOS_STAT.VIEWS AS VIEWS,
                V_TIKTOK_TOP_VIDEOS_STAT.LIKES AS LIKES
            FROM (
                SELECT
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO,
                    MAX(V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP) AS MAX_TIMESTP
                FROM TIKTOK_BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                    ON TIKTOK_BASIC_DATA.V_TIKTOK_VIDEO_ID = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
                GROUP BY
                    V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
            ) AS MAX_DATES_DATA
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_TOP_VIDEOS_STAT AS V_TIKTOK_TOP_VIDEOS_STAT
                ON MAX_DATES_DATA.MAX_TIMESTP = V_TIKTOK_TOP_VIDEOS_STAT.TIMESTP
                AND MAX_DATES_DATA.TIKTOK_VIDEO = V_TIKTOK_TOP_VIDEOS_STAT.TIKTOK_VIDEO
        ), AGGREGATED_DATA AS (
            SELECT
                TIKTOK_BASIC_DATA.CM_TRACK_ID AS CM_TRACK_ID,
                TIKTOK_BASIC_DATA.VIDEO_ID AS VIDEO_ID,
                TIKTOK_BASIC_DATA.USERNAME AS USERNAME,
                TIKTOK_BASIC_DATA.DESCRIPTION AS DESCRIPTION,
                TIKTOK_VIDEO_METRICS.VIEWS AS VIEWS,
                TIKTOK_VIDEO_METRICS.LIKES AS LIKES,
                ROW_NUMBER () OVER (PARTITION BY TIKTOK_BASIC_DATA.CM_TRACK_ID
                    ORDER BY TIKTOK_VIDEO_METRICS.VIEWS DESC NULLS LAST) AS TOP_VIEWED_VIDEOS
            FROM TIKTOK_BASIC_DATA
            LEFT JOIN TIKTOK_VIDEO_METRICS
                ON TIKTOK_BASIC_DATA.V_TIKTOK_VIDEO_ID = TIKTOK_VIDEO_METRICS.TIKTOK_VIDEO
            QUALIFY
                TOP_VIEWED_VIDEOS <= 6
        )
        SELECT
            CM_TRACK_ID,
            VIDEO_ID,
            USERNAME,
            DESCRIPTION,
            VIEWS,
            LIKES
        FROM AGGREGATED_DATA
    """