-- One row for each user-platform, even if the user has no activity on that platform on that date.
-- User will have a row for each platform as long as they joined prior to the date.
CREATE OR REPLACE PROCEDURE agg_user_platform_daily_spine_proc(p_date VARCHAR)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        -- Delete existing records for the date
        DELETE FROM agg_user_platform_daily_spine
        WHERE p_date = :p_date;

        -- Insert new records
        INSERT INTO agg_user_platform_daily_spine
        WITH

        users AS (
            SELECT
                d.id AS discord_id
                , d.user_id AS user_id
                , d.uid AS user_uid
                , CASE
                    WHEN u.plan_key = 'pro_20250501' THEN 'Pro v2 Plan'
                    WHEN u.plan_key = 'pro' THEN 'Pro Plan'
                    ELSE u.name
                END AS subscription_tier
                , DATEDIFF('day', a.date_joined, DATE(:p_date)) AS days_since_user_joined
            FROM
                rds_discord_info d
            INNER JOIN
                rds_auth_user a
                ON a.id = d.user_id
            LEFT JOIN
                rds_usage_plan u
                ON u.id = d.subscription_plan_id
            WHERE 1=1
                AND DATE(a.date_joined) <= DATE(:p_date) -- All users who joined on or before this date
        )

        -- Followers/following counts are pulled from rds_profile_follow.
        -- Because we save an entire snapshot of the table each day, this accurately reflects the number of followers/following on p_date.
        -- If this procedure is called on a later date (e.g. backfilling), the number of followers/following will still be accurate for that day.
        , follow_info AS (
            SELECT
                user_id
                , SUM(following_cnt) AS total_following_cnt
                , SUM(follower_cnt) AS total_follower_cnt
            FROM (
                SELECT
                    following_profile_id AS user_id
                    , COUNT(*) AS following_cnt
                    , 0 AS follower_cnt
                FROM
                    rds_profile_follow
                WHERE 1=1
                    AND DATE(p_date) = DATE(:p_date)
                    AND followed_profile_id IS NOT NULL
                    AND following_profile_id IS NOT NULL
                GROUP BY
                    following_profile_id

                UNION ALL

                SELECT
                    followed_profile_id AS user_id
                    , 0 AS following_cnt
                    , COUNT(*) AS follower_cnt
                FROM
                    rds_profile_follow
                WHERE 1=1
                    AND DATE(p_date) = DATE(:p_date)
                    AND followed_profile_id IS NOT NULL
                    AND following_profile_id IS NOT NULL
                GROUP BY
                    followed_profile_id
            )
            GROUP BY
                user_id
        )

        , clip_info AS (
            SELECT
                user_id
                , IFF(platform IS NULL, 'web', platform) AS platform
                , COUNT(*) AS clip_total_cnt
                , COUNT_IF(type = 'gen') AS gen_clip_total_cnt
            FROM
                dim_clip
            WHERE 1=1
                AND DATE(p_date) = DATE(:p_date)
                AND user_id IS NOT NULL
                AND status = 'complete'
            GROUP BY
                user_id, IFF(platform IS NULL, 'web', platform)
        )

        , play_info AS (
            SELECT
                user_id
                , user_uid
                , platform
                , SUM(play_duration_sec) AS play_duration_sec
                , SUM(play_cnt) AS play_cnt
                , SUM(CASE WHEN is_user_song_owner THEN play_duration_sec ELSE 0 END) AS play_duration_sec_own
                , SUM(CASE WHEN NOT is_user_song_owner THEN play_duration_sec ELSE 0 END) AS play_duration_sec_others
                , SUM(CASE WHEN is_user_song_owner THEN play_cnt ELSE 0 END) AS play_cnt_own
                , SUM(CASE WHEN NOT is_user_song_owner THEN play_cnt ELSE 0 END) AS play_cnt_others
            FROM agg_play_info_hourly
            WHERE 1=1
                AND DATE(p_date) = DATE(:p_date)
                AND play_duration_threshold = 5
            GROUP BY
                user_id, user_uid, platform
        )

        , action_info AS (
            -- Web actions
            SELECT
                c.user_id AS user_id
                , 'web' AS platform
                , SUM(CASE
                    WHEN action_name = 'LikeSong' THEN 1
                    WHEN action_name = 'UndoLikeSong' THEN -1
                    ELSE 0 END) AS net_likes_received_cnt
            FROM
                web_audio_actions a
            INNER JOIN
                dim_clip c
                ON a.clip_id = c.clip_id
            WHERE 1=1
                AND DATE(a.p_date) = DATE(:p_date)
                AND DATE(c.p_date) = DATE(:p_date)
                AND a.user_id IS NOT NULL
                AND action_name IN ('LikeSong', 'UndoLikeSong')
                AND NOT is_user_song_owner
            GROUP BY
                c.user_id

            UNION ALL

            -- iOS actions
            SELECT
                c.user_id AS user_id
                , 'ios' AS platform
                , SUM(CASE
                    WHEN action_name = 'LikeSong' THEN 1
                    WHEN action_name = 'UndoLikeSong' THEN -1
                    ELSE 0 END) AS net_likes_received_cnt
            FROM
                app_audio_actions a
            INNER JOIN
                dim_clip c
                ON a.clip_id = c.clip_id
            WHERE 1=1
                AND DATE(a.p_date) = DATE(:p_date)
                AND DATE(c.p_date) = DATE(:p_date)
                AND LOWER(os_name) IN ('ios', 'ipados')
                AND a.user_id IS NOT NULL
                AND action_name IN ('LikeSong', 'UndoLikeSong')
                AND NOT is_user_song_owner
            GROUP BY
                c.user_id

            -- TODO: Add Android actions when we have them
        )

        , likes_info AS (
            SELECT
                user_id
                , SUM(net_likes_received_cnt) AS net_likes_received_cnt
            FROM action_info
            GROUP BY user_id
        )

        , platforms AS (
            SELECT DISTINCT
                platform
            FROM (
                SELECT platform FROM clip_info
                UNION ALL
                SELECT platform FROM play_info
                UNION ALL
                SELECT platform FROM action_info
            )
        ), user_play_count as (
            select user_id, source as platform, count(*) as play_cnt
            from bk_realtime_event
            where p_date = :p_date and name = 'Play_Count'
            group by user_id, source
        )

        SELECT
            u.user_id
            , u.user_uid
            , u.subscription_tier
            , pl.platform
            , COALESCE(c.clip_total_cnt, 0) AS clip_total_cnt
            , COALESCE(c.gen_clip_total_cnt, 0) AS gen_clip_total_cnt
            , COALESCE(p.play_cnt, 0) AS play_cnt
            , COALESCE(p.play_cnt_own, 0) AS play_cnt_own
            , COALESCE(p.play_cnt_others, 0) AS play_cnt_others
            , COALESCE(p.play_duration_sec, 0) AS play_duration_sec
            , COALESCE(p.play_duration_sec_own, 0) AS play_duration_sec_own
            , COALESCE(p.play_duration_sec_others, 0) AS play_duration_sec_others
            , COALESCE(f.total_follower_cnt, 0) AS total_follower_cnt
            , COALESCE(f.total_following_cnt, 0) AS total_following_cnt
            , COALESCE(l.net_likes_received_cnt, 0) AS net_likes_received_cnt
            , IFF(COALESCE(c.clip_total_cnt, 0) > 0 OR COALESCE(p.play_cnt, 0) > 0 OR COALESCE(p.play_duration_sec, 0) > 0 OR COALESCE(upc.play_cnt, 0) > 0, TRUE, FALSE) AS is_active
            , IFF(COALESCE(c.clip_total_cnt, 0) > 0, TRUE, FALSE) AS is_active_create
            , IFF(COALESCE(p.play_cnt, 0) > 0 OR COALESCE(p.play_duration_sec, 0) > 0 OR COALESCE(upc.play_cnt, 0) > 0, TRUE, FALSE) AS is_active_listen
            , IFF(COALESCE(p.play_cnt_own, 0) > 0 OR COALESCE(p.play_duration_sec_own, 0) > 0, TRUE, FALSE) AS is_active_listen_own
            , IFF(COALESCE(p.play_cnt_others, 0) > 0 OR COALESCE(p.play_duration_sec_others, 0) > 0, TRUE, FALSE) AS is_active_listen_others
            -- Historical flags will be set by update_historical_activity_proc
            , NULL AS is_prev_day_active
            , NULL AS is_prev_day_active_create
            , NULL AS is_prev_day_active_listen
            , NULL AS is_prev_day_active_listen_own
            , NULL AS is_prev_day_active_listen_others
            , NULL AS is_7d_active
            , NULL AS is_7d_active_create
            , NULL AS is_7d_active_listen
            , NULL AS is_7d_active_listen_own
            , NULL AS is_7d_active_listen_others
            , NULL AS is_prev_7d_active
            , NULL AS is_prev_7d_active_create
            , NULL AS is_prev_7d_active_listen
            , NULL AS is_prev_7d_active_listen_own
            , NULL AS is_prev_7d_active_listen_others
            , NULL AS is_28d_active
            , NULL AS is_28d_active_create
            , NULL AS is_28d_active_listen
            , NULL AS is_28d_active_listen_own
            , NULL AS is_28d_active_listen_others
            , NULL AS is_prev_28d_active
            , NULL AS is_prev_28d_active_create
            , NULL AS is_prev_28d_active_listen
            , NULL AS is_prev_28d_active_listen_own
            , NULL AS is_prev_28d_active_listen_others
            , :p_date AS p_date
            , u.days_since_user_joined
            , false as is_online
        FROM
            users u
        CROSS JOIN
            platforms pl
        LEFT JOIN
            clip_info c
            ON u.user_id = c.user_id
            AND pl.platform = c.platform
        LEFT JOIN
            play_info p
            ON u.user_id = p.user_id
            AND u.user_uid = p.user_uid
            AND pl.platform = p.platform
        LEFT JOIN
            follow_info f
            ON u.discord_id = f.user_id
        LEFT JOIN
            likes_info l
            ON u.user_id = l.user_id
        left join user_play_count upc
            on u.user_id = upc.user_id
            and upc.platform = pl.platform;

        merge into agg_user_platform_daily_spine as target
        using (
            select
                user_id,
                platform
            from user_session_state
            where p_date = :p_date
            group by user_id, platform
        ) as source
        on target.user_id = source.user_id and target.p_date = :p_date and target.platform = source.platform
        when matched then
            update set is_online = true;

        RETURN 'SUCCESS';
    END;
    $$;
