CREATE OR REPLACE PROCEDURE CLIP_INITIAL_METRICS_HOURLY_UPSERT_PROC(p_date VARCHAR, p_hour NUMBER)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    DECLARE
        window_minutes NUMBER := 120;

        start_ts TIMESTAMP := TO_TIMESTAMP(p_date || ' ' || p_hour || ':00:00');
        end_ts TIMESTAMP := DATEADD('MINUTE', :window_minutes + 60, start_ts); -- Add 60 minutes to ensure we capture the full window for all songs, even those made at the end of the hour
        start_p_date DATE := DATE(start_ts);
        start_p_hour NUMBER := HOUR(start_ts);
        end_p_date DATE := DATE(end_ts);
        end_p_hour NUMBER := HOUR(end_ts);
    BEGIN
        MERGE INTO suno_prod.prod.clip_initial_metrics cim
        USING (
            WITH

            clips AS (
                SELECT
                    dc.clip_id
                    , dc.user_id
                    , du.user_uid
                    , dc.created_at::TIMESTAMP_NTZ AS clip_created_at
                    , dc.model_name
                    , dc.p_date
                    , dc.p_hour
                FROM
                    suno_prod.prod.dim_clip dc
                LEFT JOIN
                    suno_prod.prod.dim_user du
                    ON dc.user_id = du.user_id
                WHERE 1=1
                    AND dc.p_date = :start_p_date
                    AND dc.p_hour = :start_p_hour
                    AND dc.status = 'complete' -- Completed clips only
            )

            , clip_play_info AS (
                SELECT
                    fp.clip_id
                    , COALESCE(COUNT(*), 0) AS play_cnt
                    , COALESCE(SUM(fp.play_duration_seconds), 0) AS play_duration_seconds
                FROM
                    suno_prod.prod.fact_play fp
                INNER JOIN
                    clips c
                    ON fp.clip_id = c.clip_id
                WHERE 1=1
                    AND fp.p_date BETWEEN :start_p_date AND :end_p_date
                    -- If time window spans two days, use special filtering logic on partition hours
                    AND IFF(
                        :end_p_date > :start_p_date,
                        (fp.p_date = :start_p_date AND fp.p_hour BETWEEN :start_p_hour AND 23) OR (fp.p_date = :end_p_date AND fp.p_hour BETWEEN 0 AND :end_p_hour),
                        fp.p_hour BETWEEN :start_p_hour AND :end_p_hour
                    )
                    AND fp.play_start_ts_utc BETWEEN c.clip_created_at AND DATEADD('MINUTE', :window_minutes, c.clip_created_at)
                    AND fp.is_user_song_owner
                GROUP BY
                    1
            )

            , clip_backend_events AS (
                SELECT
                    PARSE_JSON(bk.properties):clip_id::STRING AS clip_id
                    , COALESCE(COUNT_IF(bk.name = 'ClipLike'), 0) AS n_like_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipUndoLike'), 0) AS n_undolike_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipDislike'), 0) AS n_dislike_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipUndoDislike'), 0) AS n_undodislike_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipShare'), 0) AS n_share_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipDownload'), 0) AS n_download_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipMakePublic'), 0) AS n_makepublic_events
                    , COALESCE(COUNT_IF(bk.name = 'ClipMakePrivate'), 0) AS n_makeprivate_events
                FROM
                    suno_prod.prod.bk_nonrealtime_event bk
                INNER JOIN
                    clips c
                    ON PARSE_JSON(bk.properties):clip_id::STRING = c.clip_id
                WHERE 1=1
                    AND bk.p_date BETWEEN :start_p_date AND :end_p_date
                    -- If time window spans two days, use special filtering logic on partition hours
                    AND IFF(
                        :end_p_date > :start_p_date,
                        (bk.p_date = :start_p_date AND bk.p_hour BETWEEN :start_p_hour AND 23) OR (bk.p_date = :end_p_date AND bk.p_hour BETWEEN 0 AND :end_p_hour),
                        bk.p_hour BETWEEN :start_p_hour AND :end_p_hour
                    )
                    AND bk.timestamp BETWEEN c.clip_created_at AND DATEADD('MINUTE', :window_minutes, c.clip_created_at)
                    AND bk.name IN ('ClipLike', 'ClipUndoLike', 'ClipDislike', 'ClipUndoDislike', 'ClipShare', 'ClipDownload', 'ClipMakePublic', 'ClipMakePrivate')
                    AND PARSE_JSON(bk.properties):is_user_object_owner::BOOLEAN
                GROUP BY
                    1
            )

            , tmp AS (
                SELECT
                    c.clip_id
                    , c.user_id
                    , c.user_uid
                    , c.clip_created_at
                    , :window_minutes AS window_minutes
                    , DATE_TRUNC('HOUR', c.clip_created_at) AS window_start_time
                    , DATEADD('MINUTE', :window_minutes, DATE_TRUNC('HOUR', c.clip_created_at)) AS window_end_time
                    , COALESCE(cpi.play_cnt, 0) AS play_cnt
                    , COALESCE(cpi.play_duration_seconds, 0) AS play_duration_seconds
                    , COALESCE(bk.n_like_events, 0) AS n_like_events
                    , COALESCE(bk.n_undolike_events, 0) AS n_undolike_events
                    , COALESCE(bk.n_like_events - bk.n_undolike_events, 0) AS net_like_events
                    , COALESCE(bk.n_dislike_events, 0) AS n_dislike_events
                    , COALESCE(bk.n_undodislike_events, 0) AS n_undodislike_events
                    , COALESCE(bk.n_dislike_events - bk.n_undodislike_events, 0) AS net_dislike_events
                    , COALESCE(bk.n_share_events, 0) AS n_share_events
                    , COALESCE(bk.n_download_events, 0) AS n_download_events
                    , COALESCE(bk.n_makepublic_events, 0) AS n_makepublic_events
                    , COALESCE(bk.n_makeprivate_events, 0) AS n_makeprivate_events
                    , COALESCE(bk.n_makepublic_events - bk.n_makeprivate_events, 0) AS net_public_toggle_events
                    , COALESCE(
                        bk.n_like_events - bk.n_undolike_events
                        - (bk.n_dislike_events - bk.n_undodislike_events)
                        + bk.n_share_events
                        + bk.n_download_events
                        + (bk.n_makepublic_events - bk.n_makeprivate_events)
                    , 0) AS net_positive_interactions
                    , OBJECT_CONSTRUCT(
                        'v1', CASE
                            WHEN play_cnt = 0 OR play_duration_seconds = 0 OR COALESCE(bk.n_dislike_events - bk.n_undodislike_events, 0) > 0 THEN FALSE
                            WHEN play_cnt >= 3 AND play_duration_seconds >= 60 THEN TRUE
                            ELSE FALSE
                        END
                    ) AS is_satisfactory_indirect
                    , OBJECT_CONSTRUCT(
                        'v1', CASE
                            WHEN play_cnt = 0 OR play_duration_seconds = 0 OR COALESCE(bk.n_dislike_events - bk.n_undodislike_events, 0) > 0 THEN FALSE
                            WHEN (
                                (bk.n_like_events - bk.n_undolike_events) > 0
                                OR bk.n_share_events > 0
                                OR bk.n_download_events > 0
                                OR (bk.n_makepublic_events - bk.n_makeprivate_events) > 0
                            ) THEN TRUE
                            ELSE FALSE
                        END
                    ) AS is_satisfactory_direct
                    , OBJECT_CONSTRUCT(
                        'model_name', c.model_name
                    ) AS metadata
                    , c.p_date
                    , c.p_hour
                FROM
                    clips c
                LEFT JOIN
                    clip_backend_events bk
                    ON c.clip_id = bk.clip_id
                LEFT JOIN
                    clip_play_info cpi
                    ON c.clip_id = cpi.clip_id
            )

            SELECT
                clip_id
                , user_id
                , user_uid
                , clip_created_at
                , window_minutes
                , window_start_time
                , window_end_time
                , play_cnt
                , play_duration_seconds
                , n_like_events
                , n_undolike_events
                , net_like_events
                , n_dislike_events
                , n_undodislike_events
                , net_dislike_events
                , n_share_events
                , n_download_events
                , n_makepublic_events
                , n_makeprivate_events
                , net_public_toggle_events
                , net_positive_interactions
                , is_satisfactory_indirect
                , is_satisfactory_direct
                , OBJECT_CONSTRUCT(
                    'v1', is_satisfactory_indirect:v1 OR is_satisfactory_direct:v1
                ) AS is_satisfactory
                , metadata
                , p_date
                , p_hour
            FROM
                tmp
        ) AS source
        ON cim.clip_id = source.clip_id
        WHEN MATCHED THEN
            UPDATE SET
                cim.user_id = source.user_id
                , cim.user_uid = source.user_uid
                , cim.clip_created_at = source.clip_created_at
                , cim.window_minutes = source.window_minutes
                , cim.window_start_time = source.window_start_time
                , cim.window_end_time = source.window_end_time
                , cim.play_cnt = source.play_cnt
                , cim.play_duration_seconds = source.play_duration_seconds
                , cim.n_like_events = source.n_like_events
                , cim.n_undolike_events = source.n_undolike_events
                , cim.net_like_events = source.net_like_events
                , cim.n_dislike_events = source.n_dislike_events
                , cim.n_undodislike_events = source.n_undodislike_events
                , cim.net_dislike_events = source.net_dislike_events
                , cim.n_share_events = source.n_share_events
                , cim.n_download_events = source.n_download_events
                , cim.n_makepublic_events = source.n_makepublic_events
                , cim.n_makeprivate_events = source.n_makeprivate_events
                , cim.net_public_toggle_events = source.net_public_toggle_events
                , cim.net_positive_interactions = source.net_positive_interactions
                , cim.is_satisfactory_indirect = source.is_satisfactory_indirect
                , cim.is_satisfactory_direct = source.is_satisfactory_direct
                , cim.is_satisfactory = source.is_satisfactory
                , cim.metadata = source.metadata
        WHEN NOT MATCHED THEN
            INSERT (
                clip_id
                , user_id
                , user_uid
                , clip_created_at
                , window_minutes
                , window_start_time
                , window_end_time
                , play_cnt
                , play_duration_seconds
                , n_like_events
                , n_undolike_events
                , net_like_events
                , n_dislike_events
                , n_undodislike_events
                , net_dislike_events
                , n_share_events
                , n_download_events
                , n_makepublic_events
                , n_makeprivate_events
                , net_public_toggle_events
                , net_positive_interactions
                , is_satisfactory_indirect
                , is_satisfactory_direct
                , is_satisfactory
                , metadata
                , p_date
                , p_hour
            ) VALUES (
                source.clip_id
                , source.user_id
                , source.user_uid
                , source.clip_created_at
                , source.window_minutes
                , source.window_start_time
                , source.window_end_time
                , source.play_cnt
                , source.play_duration_seconds
                , source.n_like_events
                , source.n_undolike_events
                , source.net_like_events
                , source.n_dislike_events
                , source.n_undodislike_events
                , source.net_dislike_events
                , source.n_share_events
                , source.n_download_events
                , source.n_makepublic_events
                , source.n_makeprivate_events
                , source.net_public_toggle_events
                , source.net_positive_interactions
                , source.is_satisfactory_indirect
                , source.is_satisfactory_direct
                , source.is_satisfactory
                , source.metadata
                , source.p_date
                , source.p_hour
            );
    RETURN 'SUCCESS';

    END;
    $$;
