-- Backend events staging table for hook session processing
-- Extracts and processes all backend events (likes, dislikes, comments, follows, playlist adds, reports) before aggregation
-- Uses recommendation_item_id joins to get hook_session_id

CREATE TRANSIENT TABLE IF NOT EXISTS {{ stg_hook_session_backend_events_agg_table_name }} (
    -- Identifiers
    user_id INTEGER COMMENT 'User ID'
    , user_uid VARCHAR(255) COMMENT 'User UID'
    , hook_id VARCHAR(255) COMMENT 'Hook ID'
    , hook_session_id VARCHAR(255) COMMENT 'Hook session ID'

    -- Event metadata
    , event_timestamp TIMESTAMP_NTZ(9) COMMENT 'Timestamp of the event'
    , event_type VARCHAR(255) COMMENT 'Type of event (like, undo_like, dislike, etc.)'
    , event_count INTEGER COMMENT 'Count of events (usually 1)'
    , platform VARCHAR(255) COMMENT 'Platform where event occurred'

    -- Partition fields
    , p_date DATE COMMENT 'Date of the event'
    , p_hour INTEGER COMMENT 'Hour of the event'
) CLUSTER BY (p_date, p_hour);
