-- Session History table for tracking user sessions
-- This table is populated by the Glue job rds_to_s3_bots_session_history_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by session_id for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {session_history_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    SESSION_ID VARCHAR(255),                            -- Unique session identifier
    
    -- Session details
    SESSION_TYPE NUMBER(38,0),                          -- Type of session
    USER_ID NUMBER(38,0),                               -- User identifier
    IP VARCHAR(255),                                    -- IP address
    PLATFORM VARCHAR(255),                              -- Platform identifier (web/mobile/app)
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the session was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    EXPIRE_TIME TIMESTAMP_TZ(9),                        -- Session expiration time
    
    -- Session metrics
    LENGTH_IN_SEC NUMBER(38,0),                         -- Session length in seconds
    PROPERTIES VARCHAR(16777216),                       -- Session properties (JSON string)
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on created_at
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23) based on created_at
);
