{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key=['user_id', 'user_uid', 'hook_id', 'p_date', 'p_hour'],
        cluster_by=['p_date', 'p_hour']
    )
}}

SELECT
    user_id
    , user_uid
    , hook_id
    , p_date
    , p_hour

    -- Aggregated playback metrics
    , COUNT(*) AS hook_session_count
    , SUM(hook_session_duration_seconds) AS hook_session_duration_seconds
    , SUM(play_duration_seconds) AS play_duration_seconds
    , SUM(unique_play_duration_seconds) AS unique_play_duration_seconds
    , SUM(CASE WHEN is_long_watch THEN 1 ELSE 0 END) AS long_watch_session_count
    , SUM(repeat_count) AS repeat_count

    -- Aggregated like/unlike counts
    , SUM(like_count) AS like_count
    , SUM(undo_like_count) AS undo_like_count
    , SUM(like_count - undo_like_count) AS net_like_count
    , SUM(dislike_count) AS dislike_count
    , SUM(undo_dislike_count) AS undo_dislike_count
    , SUM(dislike_count - undo_dislike_count) AS net_dislike_count

    -- Aggregated comment counts
    , SUM(comment_open_tap_count) AS comment_open_tap_count
    , SUM(comment_begin_write_count) AS comment_begin_write_count
    , SUM(comment_count) AS comment_count

    -- Aggregated share counts
    , SUM(share_tap_count) AS share_tap_count
    , SUM(share_count) AS share_count

    -- Aggregated profile and follow/unfollow counts
    , SUM(profile_tap_count) AS profile_tap_count
    , SUM(follow_count) AS follow_count
    , SUM(unfollow_count) AS unfollow_count
    , SUM(follow_count - unfollow_count) AS net_follow_count

    -- Aggregated playlist event counts
    , SUM(playlist_add_tap_count) AS playlist_add_tap_count
    , SUM(playlist_add_count) AS playlist_add_count

    -- Aggregated create hook counts
    , SUM(create_hook_tap_count) AS create_hook_tap_count
    , SUM(create_hook_submit_count) AS create_hook_submit_count
    , SUM(create_hook_success_count) AS create_hook_success_count
    , SUM(remix_tap_count) AS remix_tap_count

    -- Aggregated misc button taps & event fire counts
    , SUM(song_pill_tap_count) AS song_pill_tap_count
    , SUM(go_to_full_song_tap_count) AS go_to_full_song_tap_count
    , SUM(song_title_tap_count) AS song_title_tap_count

    -- Aggregated report/hide creator counts
    , SUM(report_count) AS report_count
    , SUM(hide_creator_count) AS hide_creator_count

    -- Context aggregations (first occurrence chronologically in the hour)
    , MIN_BY(context_type, session_start_ts_utc) AS primary_context_type
    , MIN_BY(platform, session_start_ts_utc) AS primary_platform
    , MIN_BY(country, session_start_ts_utc) AS primary_country
    , MIN_BY(city, session_start_ts_utc) AS primary_city

    -- Recommended interactions (sessions with recommendation_item_id)
    , OBJECT_CONSTRUCT(
        'hook_session_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN 1 ELSE 0 END),
        'hook_session_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN hook_session_duration_seconds ELSE 0 END),
        'play_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN play_duration_seconds ELSE 0 END),
        'unique_play_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN unique_play_duration_seconds ELSE 0 END),
        'long_watch_session_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL AND is_long_watch THEN 1 ELSE 0 END),
        'repeat_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN repeat_count ELSE 0 END),
        'like_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN like_count ELSE 0 END),
        'undo_like_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN undo_like_count ELSE 0 END),
        'net_like_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN like_count - undo_like_count ELSE 0 END),
        'dislike_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN dislike_count ELSE 0 END),
        'undo_dislike_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN undo_dislike_count ELSE 0 END),
        'net_dislike_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN dislike_count - undo_dislike_count ELSE 0 END),
        'comment_open_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN comment_open_tap_count ELSE 0 END),
        'comment_begin_write_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN comment_begin_write_count ELSE 0 END),
        'comment_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN comment_count ELSE 0 END),
        'share_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN share_tap_count ELSE 0 END),
        'share_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN share_count ELSE 0 END),
        'profile_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN profile_tap_count ELSE 0 END),
        'follow_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN follow_count ELSE 0 END),
        'unfollow_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN unfollow_count ELSE 0 END),
        'net_follow_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN follow_count - unfollow_count ELSE 0 END),
        'playlist_add_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN playlist_add_tap_count ELSE 0 END),
        'playlist_add_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN playlist_add_count ELSE 0 END),
        'create_hook_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN create_hook_tap_count ELSE 0 END),
        'create_hook_submit_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN create_hook_submit_count ELSE 0 END),
        'create_hook_success_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN create_hook_success_count ELSE 0 END),
        'remix_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN remix_tap_count ELSE 0 END),
        'song_pill_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN song_pill_tap_count ELSE 0 END),
        'go_to_full_song_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN go_to_full_song_tap_count ELSE 0 END),
        'song_title_tap_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN song_title_tap_count ELSE 0 END),
        'report_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN report_count ELSE 0 END),
        'hide_creator_count', SUM(CASE WHEN recommendation_item_id IS NOT NULL THEN hide_creator_count ELSE 0 END)
    ) AS recommended_interactions

    -- Non-recommended interactions (organic sessions only)
    , OBJECT_CONSTRUCT(
        'hook_session_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN 1 ELSE 0 END),
        'hook_session_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NULL THEN hook_session_duration_seconds ELSE 0 END),
        'play_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NULL THEN play_duration_seconds ELSE 0 END),
        'unique_play_duration_seconds', SUM(CASE WHEN recommendation_item_id IS NULL THEN unique_play_duration_seconds ELSE 0 END),
        'long_watch_session_count', SUM(CASE WHEN recommendation_item_id IS NULL AND is_long_watch THEN 1 ELSE 0 END),
        'repeat_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN repeat_count ELSE 0 END),
        'like_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN like_count ELSE 0 END),
        'undo_like_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN undo_like_count ELSE 0 END),
        'net_like_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN like_count - undo_like_count ELSE 0 END),
        'dislike_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN dislike_count ELSE 0 END),
        'undo_dislike_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN undo_dislike_count ELSE 0 END),
        'net_dislike_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN dislike_count - undo_dislike_count ELSE 0 END),
        'comment_open_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN comment_open_tap_count ELSE 0 END),
        'comment_begin_write_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN comment_begin_write_count ELSE 0 END),
        'comment_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN comment_count ELSE 0 END),
        'share_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN share_tap_count ELSE 0 END),
        'share_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN share_count ELSE 0 END),
        'profile_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN profile_tap_count ELSE 0 END),
        'follow_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN follow_count ELSE 0 END),
        'unfollow_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN unfollow_count ELSE 0 END),
        'net_follow_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN follow_count - unfollow_count ELSE 0 END),
        'playlist_add_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN playlist_add_tap_count ELSE 0 END),
        'playlist_add_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN playlist_add_count ELSE 0 END),
        'create_hook_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN create_hook_tap_count ELSE 0 END),
        'create_hook_submit_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN create_hook_submit_count ELSE 0 END),
        'create_hook_success_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN create_hook_success_count ELSE 0 END),
        'remix_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN remix_tap_count ELSE 0 END),
        'song_pill_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN song_pill_tap_count ELSE 0 END),
        'go_to_full_song_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN go_to_full_song_tap_count ELSE 0 END),
        'song_title_tap_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN song_title_tap_count ELSE 0 END),
        'report_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN report_count ELSE 0 END),
        'hide_creator_count', SUM(CASE WHEN recommendation_item_id IS NULL THEN hide_creator_count ELSE 0 END)
    ) AS organic_interactions

FROM {{ source('snowflake', 'hook_session') }}
WHERE 1=1
    AND {{ partition_filter_hourly('p_date', 'p_hour') }}
GROUP BY
    user_id
    , user_uid
    , hook_id
    , p_date
    , p_hour
