-- One-Time Free Usage table for tracking user free tier limits
-- This table is populated by the Glue job rds_to_s3_bots_onetime_free_usage_hourly_upsert
-- 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 {onetime_free_usage_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique record identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the record was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- User association
    USER_ID NUMBER(38,0),                               -- User identifier
    
    -- Cover generations
    FREE_COVER_GENERATIONS_USED NUMBER(38,0),           -- Used cover generations
    FREE_COVER_GENERATIONS_LIMIT NUMBER(38,0),          -- Cover generations limit
    
    -- Persona generations
    FREE_PERSONA_GENERATIONS_USED NUMBER(38,0),         -- Used persona generations
    FREE_PERSONA_GENERATIONS_LIMIT NUMBER(38,0),        -- Persona generations limit
    
    -- Web remasters
    FREE_WEB_REMASTERS_LIMIT NUMBER(38,0),              -- Web remasters limit
    FREE_WEB_REMASTERS_USED NUMBER(38,0),               -- Used web remasters
    
    -- Mobile remasters
    FREE_MOBILE_REMASTERS_LIMIT NUMBER(38,0),           -- Mobile remasters limit
    FREE_MOBILE_REMASTERS_USED NUMBER(38,0),            -- Used mobile remasters
    
    -- Mobile V4 generations
    FREE_MOBILE_V4_GENS_LIMIT NUMBER(38,0),             -- Mobile V4 generations limit
    FREE_MOBILE_V4_GENS_USED NUMBER(38,0),              -- Used mobile V4 generations
    
    -- Web V4 generations
    FREE_WEB_V4_GENS_LIMIT NUMBER(38,0),                -- Web V4 generations limit
    FREE_WEB_V4_GENS_USED NUMBER(38,0),                 -- Used web V4 generations
    
    -- 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
);
