-- Bot Jail table for storing bot jail and violation data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_botjail_hourly_insert
-- 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 {bot_jail_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(16777216),                               -- Unique bot jail identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the jail record was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    LATEST_JAILED_AT TIMESTAMP_TZ(9),                   -- When the user was last jailed
    LATEST_RELEASED_AT TIMESTAMP_TZ(9),                 -- When the user was last released
    
    -- User information
    USERNAME VARCHAR(16777216),                         -- Username of the jailed user
    USER_ID NUMBER(38,0),                               -- User ID of the jailed user
    USER_IP VARCHAR(16777216),                          -- IP address of the user
    
    -- Jail status
    STATUS VARCHAR(16777216),                           -- Current jail status (jailed, released, etc.)
    TOTAL_JAILED_COUNT NUMBER(38,0),                    -- Total number of times jailed
    LATEST_JAILED_REASON VARCHAR(16777216),             -- Reason for latest jailing
    
    -- Copycat violations
    TOTAL_COPYCAT_VIOLATION_COUNT NUMBER(38,0),         -- Total copycat violations
    LATEST_COPYCAT_VIOLATION_AT TIMESTAMP_TZ(9),        -- Last copycat violation timestamp
    COPYCAT_NAME VARCHAR(16777216),                     -- Name associated with copycat violation
    
    -- API violations
    TOTAL_API_VIOLATION_COUNT NUMBER(38,0),             -- Total API violations
    API_COUNTER NUMBER(38,0),                           -- API usage counter
    LATEST_API_VIOLATION_AT TIMESTAMP_TZ(9),            -- Last API violation timestamp
    
    -- hCaptcha violations
    TOTAL_HCAPTCHA_VIOLATION_COUNT NUMBER(38,0),        -- Total hCaptcha violations
    HCAPTCHA_SCORE FLOAT,                               -- hCaptcha score
    LATEST_HCAPTCHA_VIOLATION_AT TIMESTAMP_TZ(9),       -- Last hCaptcha violation timestamp
    
    -- 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
);

