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


__all__ = ["Extract"]


class Extract(AggregatesExtractBase):
    depends_on = {PrepareNonSignedArtist}
    raw_data_path: str = str(ARTIST_VIDEOS_YOUTUBE_RAW_DATA_PATH)
    partition_key: str = RAW_ARTIST_ID_KEY
    query: str = """
        WITH YOUTUBE_ARTISTS AS (
            SELECT
                T_NON_SIGNED_ARTIST.ID AS ID,
                T_NON_SIGNED_ARTIST.YT AS ACCOUNT_ID
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
            WHERE T_NON_SIGNED_ARTIST.YT IS NOT NULL
        ), 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 YOUTUBE_ARTISTS
                LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                    ON YOUTUBE_ARTISTS.ACCOUNT_ID = V_YOUTUBE.YOUTUBE_CHANNEL_ID
                JOIN WHITELIST_REPLICA.MAIN.YT_VIDEO_STATISTICS AS YT_VIDEO_STATISTICS
                    ON V_YOUTUBE.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
                YOUTUBE_ARTISTS.ID AS CM_ARTIST_ID,
                V_YOUTUBE.ID AS VIDEO_ID,
                V_YOUTUBE.TITLE,
                V_YOUTUBE.YOUTUBE_CHANNEL_ID,
                WHITELIST_DATA.LIKE_COUNT,
                GREATEST(
                    COALESCE(V_YOUTUBE.VIEWS_LATEST, WHITELIST_DATA.VIEW_COUNT)
                    ) AS VIEW_COUNT
            FROM YOUTUBE_ARTISTS
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                ON V_YOUTUBE.YOUTUBE_CHANNEL_ID = YOUTUBE_ARTISTS.ACCOUNT_ID
            LEFT JOIN WHITELIST_DATA
                ON V_YOUTUBE.ID = WHITELIST_DATA.VIDEO_YTID
            WHERE
                V_YOUTUBE.ID IS NOT NULL
        ), TOP_RESULTS AS (
            SELECT
                CM_ARTIST_ID,
                VIDEO_ID,
                TITLE,
                YOUTUBE_CHANNEL_ID,
                LIKE_COUNT,
                VIEW_COUNT,
                ROW_NUMBER() OVER (PARTITION BY CM_ARTIST_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_ARTIST_ID,
            VIDEO_ID,
            TITLE,
            YOUTUBE_CHANNEL_ID,
            LIKE_COUNT AS LIKES,
            VIEW_COUNT AS VIEWS
        FROM TOP_RESULTS
    """