-- Video Hook Stats table for storing video hook statistics from RDS
-- This table is populated by the Glue job rds_to_s3_video_hook_stats_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_hook_stats_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique video hook stats identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the stats record was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Engagement metrics
    VIEW_COUNT INTEGER,                                 -- Number of views
    LIKE_COUNT INTEGER,                                 -- Number of likes
    DISLIKE_COUNT INTEGER,                              -- Number of dislikes
    SHARE_COUNT INTEGER,                                -- Number of shares
    
    -- Video reference
    VIDEO_HOOK_ID VARCHAR(255),                         -- Associated video hook 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
);

