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


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedTrack}
    raw_data_path: str = str(TRACK_VIDEOS_YOUTUBE_RAW_DATA_PATH)
    partition_key: str = RAW_TRACK_ID_KEY
    query: str = """
        WITH BASIC_DATA AS (
            SELECT
                T_NON_SIGNED_TRACK.ID AS CM_TRACK_ID,
                ANY_VALUE(V_YOUTUBE.ID) AS VIDEO_ID,
                ANY_VALUE(V_YOUTUBE.TITLE) AS TITLE,
                ANY_VALUE(V_YOUTUBE.VIEWS_LATEST) AS VIEWS_LATEST,
                ANY_VALUE(V_YOUTUBE.YOUTUBE_CHANNEL_ID) AS YOUTUBE_CHANNEL_ID
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                ON V_YOUTUBE.ISRC = T_NON_SIGNED_TRACK.ISRC
            WHERE
                V_YOUTUBE.VALID = TRUE
                AND V_YOUTUBE.TITLE IS NOT NULL
            GROUP BY T_NON_SIGNED_TRACK.ID
        ), WHITELIST_DATA AS (
            SELECT
                YT_VIDEO_STATISTICS.VIDEO_YTID,
                YT_VIDEO_STATISTICS.VIEW_COUNT,
                YT_VIDEO_STATISTICS.LIKE_COUNT
            FROM (
                SELECT
                    YT_VIDEO_STATISTICS.VIDEO_YTID,
                    MAX(YT_VIDEO_STATISTICS.AS_OF) AS MAX_AS_OF
                FROM BASIC_DATA
                JOIN WHITELIST_REPLICA.MAIN.YT_VIDEO_STATISTICS AS YT_VIDEO_STATISTICS
                    ON BASIC_DATA.VIDEO_ID = YT_VIDEO_STATISTICS.VIDEO_YTID
                GROUP BY
                    YT_VIDEO_STATISTICS.VIDEO_YTID
            ) AS MAX_AS_OF_DATA
            JOIN WHITELIST_REPLICA.MAIN.YT_VIDEO_STATISTICS AS YT_VIDEO_STATISTICS
                ON MAX_AS_OF_DATA.VIDEO_YTID = YT_VIDEO_STATISTICS.VIDEO_YTID
                AND MAX_AS_OF_DATA.MAX_AS_OF = YT_VIDEO_STATISTICS.AS_OF
        ), AGGREGATED_DATA AS (
            SELECT
                BASIC_DATA.CM_TRACK_ID,
                BASIC_DATA.VIDEO_ID,
                BASIC_DATA.TITLE,
                BASIC_DATA.YOUTUBE_CHANNEL_ID,
                WHITELIST_DATA.LIKE_COUNT,
                GREATEST(
                    COALESCE(BASIC_DATA.VIEWS_LATEST, WHITELIST_DATA.VIEW_COUNT)
                    ) AS VIEW_COUNT
            FROM BASIC_DATA
            LEFT JOIN WHITELIST_DATA
                ON BASIC_DATA.VIDEO_ID = WHITELIST_DATA.VIDEO_YTID
        ),TOP_RESULTS AS (
            SELECT
                CM_TRACK_ID,
                VIDEO_ID,
                TITLE,
                YOUTUBE_CHANNEL_ID,
                LIKE_COUNT AS LIKES,
                VIEW_COUNT AS VIEWS,
                ROW_NUMBER() OVER (PARTITION BY CM_TRACK_ID ORDER BY VIEW_COUNT DESC NULLS LAST) AS TOP_YOUTUBE_VIDEO
            FROM AGGREGATED_DATA
            QUALIFY
                TOP_YOUTUBE_VIDEO <= 6 -- LIMIT OF most viewed youtube videos
        )
        SELECT
            CM_TRACK_ID,
            VIDEO_ID,
            TITLE,
            YOUTUBE_CHANNEL_ID,
            VIEWS,
            LIKES
        FROM TOP_RESULTS
    """