-- User Stats table for storing user statistics from RDS
-- This table is populated by the Glue job rds_to_s3_bots_userstats_daily_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by partition date for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {user_stats_table_name} CLUSTER BY (p_date) (
    -- Primary identifier
    ID NUMBER(38,0),                                            -- Unique user stats identifier
    USER_ID NUMBER(38,0),                                       -- User ID reference
    
    -- User profile
    DISPLAY_NAME VARCHAR(16777216),                             -- User's display name
    
    -- Content metrics
    TOTAL_CLIPS NUMBER(38,0),                                   -- Total clips created
    TOTAL_CLIPS_PUBLIC NUMBER(38,0),                            -- Total public clips
    TOTAL_PLAYLISTS NUMBER(38,0),                               -- Total playlists created
    
    -- Social metrics
    TOTAL_FOLLOWERS NUMBER(38,0),                               -- Total followers count
    TOTAL_FOLLOWING NUMBER(38,0),                               -- Total following count
    
    -- Engagement metrics - Given
    TOTAL_LIKES NUMBER(38,0),                                   -- Total likes given by user
    TOTAL_PLAYS NUMBER(38,0),                                   -- Total plays by user
    
    -- Engagement metrics - Received
    TOTAL_LIKES_RECEIVED NUMBER(38,0),                          -- Total likes received
    TOTAL_LIKES_RECEIVED_TILL_LAST_MONTH NUMBER(38,0),          -- Likes received up to last month
    TOTAL_PLAYS_RECEIVED NUMBER(38,0),                          -- Total plays received
    TOTAL_PLAYS_RECEIVED_TILL_LAST_MONTH NUMBER(38,0),          -- Plays received up to last month
    
    -- First-time achievements
    FIRST_ONEBOX BOOLEAN,                                       -- First onebox creation
    FIRST_CUSTOM BOOLEAN,                                       -- First custom creation
    FIRST_INSTRUMENTAL BOOLEAN,                                 -- First instrumental creation
    
    -- Activity timestamps
    LAST_LOGIN TIMESTAMP_TZ(9),                                 -- Last login timestamp
    LAST_CREATE TIMESTAMP_TZ(9),                                -- Last creation timestamp
    LAST_MONTHLY_UPDATE TIMESTAMP_TZ(9),                        -- Last monthly stats update
    LAST_PERIODIC_UPDATE TIMESTAMP_TZ(9),                       -- Last periodic stats update
    
    -- Streak metrics
    CONSECUTIVE_DAYS_ACTIVE NUMBER(38,0),                       -- Current consecutive active days
    LONGEST_LOGIN_STREAK NUMBER(38,0),                          -- Longest login streak
    CONSECUTIVE_CREATION_STREAK NUMBER(38,0),                   -- Current consecutive creation streak
    LONGEST_CREATION_STREAK NUMBER(38,0),                       -- Longest creation streak
    
    -- Partitioning column for optimization
    P_DATE DATE                                                 -- Partition date
);

