-- RDS_USER_REACTION table for storing user reactions to clips from RDS
-- This table is populated by the Glue job which dumps data from RDS to S3 
-- and then loads it into Snowflake
-- Clustered by partition date and hour for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {user_reaction_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID NUMBER(38,0),                                    -- Unique user reaction identifier
    
    -- Timestamps
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Reaction metrics
    PLAY_COUNT NUMBER(38,0),                            -- Number of times played
    SKIP_COUNT NUMBER(38,0),                            -- Number of times skipped
    
    -- Flagging information
    FLAGGED BOOLEAN,                                    -- Whether the reaction is flagged
    FLAGGED_REASON VARCHAR(255),                        -- Reason for flagging
    
    -- Reaction details
    REACTION_TYPE VARCHAR(255),                         -- Type of reaction
    CLIP_ID VARCHAR(255),                               -- ID of the clip reacted to
    USER_ID NUMBER(38,0),                               -- ID of the user
    FEATURE VARCHAR(255),                               -- Feature context
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on updated_at
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23) based on updated_at
);

