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

        -- Insert aggregated records
        INSERT INTO agg_user_daily_spine
        SELECT
            user_id
            , user_uid
            -- Subscription tier comes from rds_discord_info, so it is the same for all platforms for a given user-day
            , subscription_tier
            -- Clips and plays are platform-specific, so we need to sum them up for each platform
            , SUM(clip_total_cnt) as clip_total_cnt
            , SUM(gen_clip_total_cnt) as gen_clip_total_cnt
            , SUM(play_cnt) as play_cnt
            , SUM(play_cnt_own) as play_cnt_own
            , SUM(play_cnt_others) as play_cnt_others
            , SUM(play_duration_sec) as play_duration_sec
            , SUM(play_duration_sec_own) as play_duration_sec_own
            , SUM(play_duration_sec_others) as play_duration_sec_others
            -- Follower and following counts come from rds_profile_follow, so they are the same for all platforms for a given user-day
            -- Net likes are platform-agnostic, so they are the same for all platforms for a given user-day
            , ANY_VALUE(total_follower_cnt) as total_follower_cnt
            , ANY_VALUE(total_following_cnt) as total_following_cnt
            , ANY_VALUE(net_likes_received_cnt) as net_likes_received_cnt
            -- Active and active create/listen are computed separately for each platform, so we take the OR of the results for each platform
            , BOOLOR_AGG(is_active) as is_active
            , BOOLOR_AGG(is_active_create) as is_active_create
            , BOOLOR_AGG(is_active_listen) as is_active_listen
            , BOOLOR_AGG(is_active_listen_own) as is_active_listen_own
            , BOOLOR_AGG(is_active_listen_others) as is_active_listen_others
            , BOOLOR_AGG(is_prev_day_active) as is_prev_day_active
            , BOOLOR_AGG(is_prev_day_active_create) as is_prev_day_active_create
            , BOOLOR_AGG(is_prev_day_active_listen) as is_prev_day_active_listen
            , BOOLOR_AGG(is_prev_day_active_listen_own) as is_prev_day_active_listen_own
            , BOOLOR_AGG(is_prev_day_active_listen_others) as is_prev_day_active_listen_others
            , BOOLOR_AGG(is_7d_active) as is_7d_active
            , BOOLOR_AGG(is_7d_active_create) as is_7d_active_create
            , BOOLOR_AGG(is_7d_active_listen) as is_7d_active_listen
            , BOOLOR_AGG(is_7d_active_listen_own) as is_7d_active_listen_own
            , BOOLOR_AGG(is_7d_active_listen_others) as is_7d_active_listen_others
            , BOOLOR_AGG(is_prev_7d_active) as is_prev_7d_active
            , BOOLOR_AGG(is_prev_7d_active_create) as is_prev_7d_active_create
            , BOOLOR_AGG(is_prev_7d_active_listen) as is_prev_7d_active_listen
            , BOOLOR_AGG(is_prev_7d_active_listen_own) as is_prev_7d_active_listen_own
            , BOOLOR_AGG(is_prev_7d_active_listen_others) as is_prev_7d_active_listen_others
            , BOOLOR_AGG(is_28d_active) as is_28d_active
            , BOOLOR_AGG(is_28d_active_create) as is_28d_active_create
            , BOOLOR_AGG(is_28d_active_listen) as is_28d_active_listen
            , BOOLOR_AGG(is_28d_active_listen_own) as is_28d_active_listen_own
            , BOOLOR_AGG(is_28d_active_listen_others) as is_28d_active_listen_others
            , BOOLOR_AGG(is_prev_28d_active) as is_prev_28d_active
            , BOOLOR_AGG(is_prev_28d_active_create) as is_prev_28d_active_create
            , BOOLOR_AGG(is_prev_28d_active_listen) as is_prev_28d_active_listen
            , BOOLOR_AGG(is_prev_28d_active_listen_own) as is_prev_28d_active_listen_own
            , BOOLOR_AGG(is_prev_28d_active_listen_others) as is_prev_28d_active_listen_others
            , p_date
            -- Days since user joined is platform-agnostic, so it is the same for all platforms for a given user-day
            , ANY_VALUE(days_since_user_joined) as days_since_user_joined
            , false as is_online
        FROM
            agg_user_platform_daily_spine
        WHERE 1=1
            AND p_date = :p_date
        GROUP BY
            user_id, user_uid, subscription_tier, p_date;
        
        merge into agg_user_daily_spine as target
        using (
            select 
                distinct user_id
            from user_session_state
            where p_date = :p_date
        ) as source
        on target.user_id = source.user_id and target.p_date = :p_date
        when matched then
            update set is_online = true;

        RETURN 'SUCCESS';
    END;
    $$;
