-- Hook Comment table for storing hook comment data from RDS
-- This table is populated by the Glue job rds_to_s3_hook_comment_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 {hook_comment_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique hook comment identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the comment was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Comment content
    CONTENT TEXT,                                       -- Comment text content
    TRACK_TIME_SECOND INTEGER,                          -- Time in track when comment was made (seconds)
    
    -- Status and moderation
    IS_DELETED BOOLEAN,                                 -- Soft delete flag
    DELETED_BY TEXT,                                    -- Who deleted the comment (user/moderator/system)
    
    -- Engagement metrics
    NUM_LIKES INTEGER,                                  -- Number of likes on the comment
    NUM_REPLIES INTEGER,                                -- Number of replies to the comment
    
    -- Structured data
    RAW_MENTIONS VARIANT,                               -- Mentioned users (JSON)
    
    -- References
    HOOK_ID VARCHAR(255),                               -- Associated hook identifier
    PARENT_ID VARCHAR(255),                             -- Parent comment ID (for replies)
    USER_ID INTEGER,                                    -- User who created the comment
    
    -- 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
);

