-- Clip table for storing clip data from RDS
-- This table is populated by the Glue job rds_to_s3_clip_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 {clip_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique clip identifier
    
    -- Timestamps
    CREATED_AT VARCHAR(255),                            -- When the clip was created
    UPDATED_AT VARCHAR(255),                            -- Last update timestamp
    
    -- Processing details
    TIME_USED FLOAT,                                    -- Time used for generation
    METADATA VARCHAR(16777216),                         -- Clip metadata (JSON)
    STATUS VARCHAR(255),                                -- Clip status
    MODEL_NAME VARCHAR(255),                            -- Model used for generation
    
    -- User and session info
    USER_ID NUMBER(38,0),                               -- User who created the clip
    CREATED_SESSION_ID VARCHAR(255),                    -- Session ID when clip was created
    CREATION_SOURCE VARCHAR(255),                       -- Source of creation
    
    -- Content details
    PROMPT_ID VARCHAR(255),                             -- Prompt identifier
    REQUEST_ID VARCHAR(255),                            -- Request identifier
    PROMPT_TEXT VARCHAR(16777216),                      -- Text prompt used
    TITLE VARCHAR(255),                                 -- Clip title
    SLUG VARCHAR(255),                                  -- URL-friendly slug
    DISPLAY_TAGS VARCHAR(65535),                        -- Tags for display
    
    -- Media references
    S3_ID VARCHAR(16777216),                            -- S3 ID for audio file
    IMAGE_S3_ID VARCHAR(16777216),                      -- S3 ID for clip image
    
    -- Discord integration
    DISCORD_MESSAGE_ID VARCHAR(255),                    -- Discord message ID
    
    -- Generation flags
    IS_GENERATED BOOLEAN,                               -- Whether clip is AI-generated
    BATCH_INDEX NUMBER(38,0),                           -- Batch index for generation
    
    -- Status flags
    IS_DELETED BOOLEAN,                                 -- Whether clip is deleted
    IS_PUBLIC BOOLEAN,                                  -- Whether clip is public
    IS_HIDDEN BOOLEAN,                                  -- Whether clip is hidden
    ALLOW_COMMENTS BOOLEAN,                             -- Whether comments are allowed
    
    -- Engagement metrics
    UPVOTE_COUNT NUMBER(38,0),                          -- Number of upvotes
    DISLIKE_COUNT NUMBER(38,0),                         -- Number of dislikes
    FLAG_COUNT NUMBER(38,0),                            -- Number of flags
    PLAY_COUNT NUMBER(38,0),                            -- Number of plays
    SKIP_COUNT NUMBER(38,0),                            -- Number of skips
    
    -- Theme
    DAILY_THEME_ID VARCHAR(255),                        -- Daily theme identifier
    
    -- Partitioning columns for optimization
    P_DATE VARCHAR(255),                                -- Partition date
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23)
);

