-- Video Sprite table for storing video sprite data from RDS
-- This table is populated by the Glue job rds_to_s3_video_sprite_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 {video_sprite_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique video sprite identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the video sprite was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- S3 storage
    S3_ID TEXT,                                         -- S3 identifier for sprite sheet
    
    -- Thumbnail dimensions
    THUMBNAIL_WIDTH INTEGER,                            -- Width of individual thumbnail in pixels
    THUMBNAIL_HEIGHT INTEGER,                           -- Height of individual thumbnail in pixels
    
    -- Sprite sheet dimensions
    IMAGE_WIDTH INTEGER,                                -- Total width of sprite sheet in pixels
    IMAGE_HEIGHT INTEGER,                               -- Total height of sprite sheet in pixels
    
    -- Sprite configuration
    INTERVAL FLOAT,                                     -- Time interval between frames in seconds
    TOTAL_FRAMES INTEGER,                               -- Total number of frames in sprite sheet
    
    -- Video reference
    VIDEO_UPLOAD_ID VARCHAR(255),                       -- Associated video upload identifier
    
    -- 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
);

