CREATE OR REPLACE PROCEDURE agg_user_to_user_engagement_weekly_proc(p_date VARCHAR)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        -- Delete existing records for the date
        DELETE FROM SUNO_PROD.PROD.AGG_USER_TO_USER_ENGAGEMENT_WEEKLY
        WHERE p_date = :p_date;

        -- Insert new aggregation results
        INSERT INTO SUNO_PROD.PROD.AGG_USER_TO_USER_ENGAGEMENT_WEEKLY (
            user_id,
            engage_user_id,
            total_score,
            relationship,
            p_date
        )
        WITH interaction_scores AS (
          SELECT
            USER_ID,
            TRY_PARSE_JSON(PROPERTIES):clip_id::STRING AS clip_id,
            SUM(
              CASE NAME
                WHEN 'ClipCommentCreate' THEN 3
                WHEN 'ClipLike' THEN 2
                ELSE 0
              END
            ) AS score
          FROM suno_prod.prod.bk_nonrealtime_event
          WHERE NAME IN ('ClipCommentCreate', 'ClipLike')
            AND TRY_PARSE_JSON(PROPERTIES):clip_id IS NOT NULL
            AND TIMESTAMP >= DATEADD(DAY, -28, CURRENT_TIMESTAMP())
          GROUP BY USER_ID, TRY_PARSE_JSON(PROPERTIES):clip_id::STRING
        ),
        listen_scores AS (
          SELECT
            USER_ID,
            CLIP_ID AS clip_id,
            COUNT(*) AS score
          FROM suno_prod.prod.agg_play_info_hourly
          WHERE TO_DATE(P_DATE) >= DATEADD(DAY, -28, CURRENT_DATE())
          GROUP BY USER_ID, CLIP_ID
        ),
        user_clip_scores AS (
          SELECT USER_ID, clip_id, SUM(score) AS score
          FROM (
            SELECT * FROM interaction_scores
            UNION ALL
            SELECT * FROM listen_scores
          ) AS scores
          GROUP BY USER_ID, clip_id
        ),
        interactions_by_user AS (
          SELECT
            s.USER_ID AS interacting_user_id,
            c.USER_ID AS creator_user_id,
            s.score
          FROM user_clip_scores s
          JOIN suno_prod.prod.dim_clip c ON s.clip_id = c.clip_id
          WHERE s.USER_ID != c.USER_ID
        ),
        user_profile_map AS (
          SELECT user_id, id AS profile_id
          FROM suno_prod.prod.rds_discord_info
        ),
        relationships AS (
          SELECT FOLLOWING_PROFILE_ID, FOLLOWED_PROFILE_ID
          FROM suno_prod.prod.rds_profile_follow
        ),
        follow_only_scores AS (
          SELECT
            up1.user_id AS interacting_user_id,
            up2.user_id AS creator_user_id,
            10 AS score
          FROM relationships r
          JOIN user_profile_map up1 ON r.FOLLOWING_PROFILE_ID = up1.profile_id
          JOIN user_profile_map up2 ON r.FOLLOWED_PROFILE_ID = up2.profile_id
          WHERE NOT EXISTS (
            SELECT 1
            FROM interactions_by_user i
            WHERE i.interacting_user_id = up1.user_id AND i.creator_user_id = up2.user_id
          )
        ),
        combined_scores AS (
          SELECT * FROM interactions_by_user
          UNION ALL
          SELECT * FROM follow_only_scores
        ),
        scored_with_relationships AS (
          SELECT
            cs.interacting_user_id,
            cs.creator_user_id,
            SUM(cs.score) AS total_score,
            CASE
              WHEN rel_m.FOLLOWING_PROFILE_ID IS NOT NULL AND rel_m2.FOLLOWING_PROFILE_ID IS NOT NULL THEN 'mutual'
              WHEN rel_m.FOLLOWING_PROFILE_ID IS NOT NULL THEN 'following'
              WHEN rel_m2.FOLLOWING_PROFILE_ID IS NOT NULL THEN 'followed'
              ELSE 'none'
            END AS relationship
          FROM combined_scores cs
          LEFT JOIN user_profile_map p1 ON cs.interacting_user_id = p1.user_id
          LEFT JOIN user_profile_map p2 ON cs.creator_user_id = p2.user_id
          LEFT JOIN relationships rel_m ON rel_m.FOLLOWING_PROFILE_ID = p1.profile_id AND rel_m.FOLLOWED_PROFILE_ID = p2.profile_id
          LEFT JOIN relationships rel_m2 ON rel_m2.FOLLOWING_PROFILE_ID = p2.profile_id AND rel_m2.FOLLOWED_PROFILE_ID = p1.profile_id
          GROUP BY cs.interacting_user_id, cs.creator_user_id,
                   rel_m.FOLLOWING_PROFILE_ID, rel_m2.FOLLOWING_PROFILE_ID
        )
        SELECT
          interacting_user_id AS user_id,
          creator_user_id AS engage_user_id,
          total_score,
          relationship,
          :p_date AS p_date
        FROM scored_with_relationships
        ORDER BY user_id, total_score DESC;

        RETURN 'SUCCESS';
    END;
    $$; 