-- User Captcha State table for storing user captcha validation state from RDS
-- This table is populated by the Glue job rds_to_s3_bots_usercaptchastate_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by ID for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {user_captcha_state_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID NUMBER(38,0),                                    -- Unique captcha state identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the state was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    LAST_CAPTCHA_COMPLETED_AT TIMESTAMP_TZ(9),          -- When last captcha was completed
    
    -- Captcha requirements
    CAPTCHA_REQUIRED BOOLEAN,                           -- Whether captcha is currently required
    FAILED_LAST_TOKEN_VALIDATION BOOLEAN,               -- Whether last token validation failed
    
    -- Captcha metrics
    LAST_HCAPTCHA_SCORE FLOAT,                          -- Score from last hCaptcha (0.0-1.0)
    GENS_SINCE_LAST_CAPTCHA NUMBER(38,0),               -- Number of generations since last captcha
    STATE_CHECKS_SINCE_LAST_GEN NUMBER(38,0),           -- Number of state checks since last generation
    TOTAL_INTERACTIVE_CHALLENGES_COMPLETED NUMBER(38,0), -- Total interactive challenges completed
    
    -- References
    USER_ID NUMBER(38,0),                               -- ID of the user
    
    -- 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
);

