-- Video Hook table for storing video hook data from RDS
-- This table is populated by the Glue job rds_to_s3_video_hook_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_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique video hook identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the video hook was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Video source
    ORIGINAL_CLIP VARCHAR(255),                         -- Original clip identifier
    
    -- Status and metadata
    STATUS TEXT,                                        -- Status of video processing (pending, processing, completed, failed)
    TITLE TEXT,                                         -- Video hook title
    START_CLIP_TIMESTAMP FLOAT,                         -- Start time of clip in seconds
    END_CLIP_TIMESTAMP FLOAT,                           -- End time of clip in seconds
    
    -- S3 storage identifiers
    RENDERED_VIDEO_S3_ID TEXT,                          -- S3 identifier for rendered video
    RENDERED_VIDEO_PREVIEW_S3_ID TEXT,                  -- S3 identifier for video preview
    THUMBNAIL_IMAGE_S3_ID TEXT,                         -- S3 identifier for thumbnail image
    RAW_VIDEO_UPLOAD_ID VARCHAR(255),                   -- Raw video upload identifier
    
    -- User and ownership
    USER_ID INTEGER,                                    -- User who created the video hook
    IS_TEST BOOLEAN,                                    -- Whether this is a test video hook
    
    -- Display settings
    ALLOW_COMMENTS BOOLEAN,                             -- Whether comments are allowed
    CAPTION TEXT,                                       -- Video caption/description
    SHOW_LYRICS BOOLEAN,                                -- Whether to display lyrics
    
    -- Content moderation
    CONTENT_RATING TEXT,                                -- Content rating (G, PG, R, etc.)
    CONTENT_RATING_TAGS ARRAY,                          -- Array of content rating tags
    
    -- Video specifications
    VIDEO_DURATION FLOAT,                               -- Duration of video in seconds
    VIDEO_STREAMING_RESOLUTIONS ARRAY,                  -- Available streaming resolutions
    
    -- Status flags
    IS_DELETED BOOLEAN,                                 -- Soft delete flag
    RENDERED_WATERMARKED_VIDEO_S3_ID TEXT,             -- S3 identifier for watermarked version
    STAFF_REVIEW_STATUS INTEGER,                        -- Staff review status code
    
    -- Additional identifiers
    VIDEO_UPLOAD_IDS ARRAY,                             -- Array of video upload identifiers
    CREATION_SOURCE TEXT,                               -- Source of creation (web, mobile, api, etc.)
    METADATA_ID VARCHAR(255),                           -- Metadata identifier
    
    -- 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
);
