-- RDS_BOTS_USERCONFIG table for storing user configuration 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_config_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique user config identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the config was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- User preferences and settings
    USER_ID NUMBER(38,0),                               -- User ID
    SHOWN_CREATION_TOUR BOOLEAN,                        -- Whether creation tour was shown
    TRENDING_SONG_LANGUAGE VARCHAR(255),                -- Preferred language for trending songs
    TRENDING_SONG_RANKBY VARCHAR(255),                  -- How to rank trending songs
    HAS_ACCEPTED_TIMBALAND_TERMS BOOLEAN,               -- Whether Timbaland terms were accepted
    PERFERRED_TAGS VARCHAR(255),                        -- User's preferred tags (note: typo in original)
    SHOWN_CLAIM_USERNAME BOOLEAN,                       -- Whether claim username prompt was shown
    HAS_SET_REMIX_PERM BOOLEAN,                         -- Whether remix permissions were set
    
    -- 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
);

