CREATE OR REPLACE TASK SUNO_PROD.PROD.FEATURE_TOP_PLAY_CLIPS_WEEKLY_DAILY_INSERT
    WAREHOUSE = SUNO_PROD_OTHER_DAILY_X_SMALL
    SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS DECLARE
        stage_path string;
        p_date date;
        p_hour string;
        query_sql string;
        row_count number;
        start_time TIMESTAMP;
    BEGIN
        start_time := CURRENT_TIMESTAMP();
        INSERT INTO PROD.FEATURE_TOP_PLAY_CLIPS_WEEKLY (
            USER_ID,
            CLIP_IDS,
            P_DATE
        )
        WITH UserPlayRank AS (
            SELECT 
                clip_id, 
                user_uid, 
                CAST(SUM(play_duration_sec) AS INT) AS total_play_duration,
                RANK() OVER (PARTITION BY user_uid ORDER BY SUM(play_duration_sec) DESC) AS play_rank
            FROM SUNO_PROD.PROD.AGG_PLAY_INFO_HOURLY
            WHERE is_user_song_owner = FALSE 
                AND PLATFORM = 'web'
                AND user_uid IN (
                    SELECT dc.uid
                    FROM SUNO_PROD.PROD.RDS_AUTH_USER AS auth_user
                    JOIN SUNO_PROD.PROD.RDS_DISCORD_INFO AS dc
                    ON dc.user_id = auth_user.id
                    WHERE auth_user.email LIKE '%@suno.com' OR auth_user.email LIKE '%@suno.ai'
                    OR dc.user_id IN (
                        35999066, 51290160, 38601284, 17540629, 2429531, 27638457, 13276075, 48754614, 29941399, 
                        50691012, 15425348, 39194820, 1, 176212, 129556, 24093402, 5643564, 20489667, 556647, 
                        17453732, 5, 41737380, 45379209, 6227557, 11431974, 22154912, 5248913, 30715110, 1404073, 
                        5286849, 21, 41423455, 16328, 47391140, 43276681, 1155849, 16, 11854891, 48556815, 
                        48551698, 3, 39761573, 15636903, 6037281, 49096290, 25465026, 30029844, 19725924, 24319199, 
                        30620235, 20677751, 4786099, 39030416, 24318407, 43279937, 1630168, 9770064, 4838327, 
                        15238229, 39207922, 20, 1215186, 25646873, 15388199, 26281033, 16711445, 25761928, 
                        4786274, 43248602, 4788511, 103832, 2380580, 6259845, 25623617, 24231843, 21537964, 
                        4849712, 24618095, 48560082, 24304132, 21639414, 4785853, 4789184, 36220136, 12878419, 
                        46761893, 152500, 12147003, 49728555, 36560506, 38664584, 25709624, 23963515, 61443587
                    )
                )
                AND clip_id IS NOT NULL
                AND p_date > DATEADD(WEEK, -4, CURRENT_DATE)
            GROUP BY clip_id, user_uid
            HAVING total_play_duration > 400
        ),
        OrderedUserPlayRank AS (
            SELECT 
                pr.*,
                auth_user.id AS user_id
            FROM UserPlayRank AS pr 
            JOIN SUNO_PROD.PROD.RDS_DISCORD_INFO AS dc
            ON dc.uid = pr.user_uid
            JOIN SUNO_PROD.PROD.RDS_AUTH_USER AS auth_user
            ON dc.user_id = auth_user.id
            WHERE play_rank <= 20
            ORDER BY user_uid, play_rank
        )
        SELECT 
            user_id,
            ARRAY_AGG(clip_id) AS top_clips,
            CURRENT_DATE AS p_date
        FROM OrderedUserPlayRank
        GROUP BY user_id;

        SELECT COUNT(*)
            INTO :row_count
        FROM PROD.FEATURE_TOP_PLAY_CLIPS_WEEKLY
        WHERE p_date = CURRENT_DATE;

        call TASK_MONITOR_INSERT_PROC('FEATURE_TOP_PLAY_CLIPS_WEEKLY_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
    end;