CREATE OR REPLACE TEMPORARY TABLE {{ sessions_table_name }} AS (
    SELECT
        -- Primary key and identifiers
        user_uid
        , hook_id
        , hook_session_id

        -- Playback
        , MIN(client_timestamp::TIMESTAMP_NTZ(9)) AS play_start_ts_utc
        , MAX(client_timestamp::TIMESTAMP_NTZ(9)) AS play_end_ts_utc
        , DATEDIFF('second', MIN(client_timestamp::TIMESTAMP_NTZ(9)), MAX(client_timestamp::TIMESTAMP_NTZ(9))) AS hook_session_duration_seconds
        , SUM(GREATEST(0, play_duration_seconds)) AS play_duration_seconds -- Exclude negative play durations
        , CASE
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('PlayNewHook', 'playNewHook') THEN 'play_new'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('PlayHook', 'playHook') THEN 'click_play'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('ScrollDownPlayNewHook', 'scrollDownPlayNewHook') THEN 'scroll_down'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('ScrollUpPlayNewHook', 'scrollUpPlayNewHook') THEN 'scroll_up'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('AutoRepeatPlayHook', 'autoRepeatPlayHook') THEN 'auto_repeat'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('CloseClipPlayerPlayHook', 'closeOmniplayerStartHook') THEN 'close_clip_player'
            ELSE MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9))
        END AS hook_start_reason
        , CASE
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('PauseHook', 'pauseHook') THEN 'click_pause'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('ScrollDownPauseHook', 'scrollDownPauseHook') THEN 'scroll_down'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('ScrollUpPauseHook', 'scrollUpPauseHook') THEN 'scroll_up'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('AutoRepeatPauseHook', 'autoRepeatPauseHook') THEN 'auto_repeat'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('TapSongPillPauseHook', 'openOmniplayerPauseHook') THEN 'tap_song_pill'
            ELSE MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9))
        END AS hook_end_reason
        , (
            -- If the user paused *and* resumed/repeated, then the playback is not continuous
            NOT (
                BOOLOR_AGG(action_name IN ('PauseHook', 'pauseHook'))
                AND BOOLOR_AGG(action_name IN ('PlayHook', 'playHook', 'AutoRepeatPlayHook', 'autoRepeatPlayHook'))
            )
        ) AS is_play_continuous
        , COUNT_IF(action_name IN ('AutoRepeatPlayHook', 'autoRepeatPlayHook')) AS repeat_count
        , MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN (
            'PlayHook', 'playHook', 'ScrollDownPlayNewHook', 'scrollDownPlayNewHook', 
            'ScrollUpPlayNewHook', 'scrollUpPlayNewHook', 'PlayNewHook', 'playNewHook'
        ) AS is_manually_started
        , ARRAY_SLICE(ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'action_name', action_name,
                'client_timestamp', client_timestamp::TIMESTAMP_NTZ(9),
                'server_timestamp', server_timestamp::TIMESTAMP_NTZ(9),
                'start_time', start_time,
                'end_time', end_time,
                'play_duration_seconds', play_duration_seconds
            )
        ) WITHIN GROUP (ORDER BY client_timestamp::TIMESTAMP_NTZ(9), server_timestamp::TIMESTAMP_NTZ(9)), 0, 50) AS hook_player_events

        -- Hook info
        , ANY_VALUE(is_user_hook_owner) AS is_user_hook_owner

        -- Context info
        , MIN_BY(previous_hook_session_id, client_timestamp::TIMESTAMP_NTZ(9)) AS previous_hook_session_id
        , MIN_BY(previous_clip_session_id, client_timestamp::TIMESTAMP_NTZ(9)) AS previous_clip_session_id
        , MIN_BY(context_type, client_timestamp::TIMESTAMP_NTZ(9)) AS context_type
        , MIN_BY(context_id, client_timestamp::TIMESTAMP_NTZ(9)) AS context_id
        , MODE(recommendation_item_id) AS recommendation_item_id
        , OBJECT_CONSTRUCT(
            'volume', MIN_BY(volume, client_timestamp::TIMESTAMP_NTZ(9)),
            'is_muted', MIN_BY(is_muted, client_timestamp::TIMESTAMP_NTZ(9)),
            'clickSourceUrl', MIN_BY(click_source_url, client_timestamp::TIMESTAMP_NTZ(9))
        ) AS hook_player_metadata

        -- Device and context info (taking the first value in the session)
        , ANY_VALUE(session_id) AS session_id
        , 'android' AS platform
        , MIN_BY(app_version, client_timestamp::TIMESTAMP_NTZ(9)) AS app_version
        , MIN_BY(country, client_timestamp::TIMESTAMP_NTZ(9)) AS country
        , MIN_BY(city, client_timestamp::TIMESTAMP_NTZ(9)) AS city
        , DATE(MIN_BY(server_timestamp::TIMESTAMP_NTZ(9), client_timestamp::TIMESTAMP_NTZ(9))) AS p_date
        , HOUR(MIN_BY(server_timestamp::TIMESTAMP_NTZ(9), client_timestamp::TIMESTAMP_NTZ(9))) AS p_hour
    FROM {{ dedup_table_name }}
    WHERE 1=1
        AND user_uid IS NOT NULL
        AND hook_session_id IS NOT NULL
        AND hook_session_id != ''
    GROUP BY
        user_uid
        , hook_id
        , hook_session_id
)
