-- Clip Extra table for storing additional clip metadata and statistics from RDS
-- This table is populated by the Glue job rds_to_s3_bots_clipextra_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by clip_id for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {clip_extra_table_name} CLUSTER BY (clip_id) (
    -- Primary identifier
    CLIP_ID VARCHAR(255),                               -- Unique clip identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the record was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Download and share counts
    DOWNLOAD_AUDIO_COUNT NUMBER(38,0),                  -- Number of audio downloads
    DOWNLOAD_VIDEO_COUNT NUMBER(38,0),                  -- Number of video downloads
    DOWNLOAD_AUDIO_WAV_COUNT NUMBER(38,0),              -- Number of WAV audio downloads
    SHARE_COUNT NUMBER(38,0),                           -- Number of shares
    
    -- Content metadata
    IS_PUBLIC_APPROVED BOOLEAN,                         -- Whether clip is approved for public
    INFERRED_LANGUAGE VARCHAR(16777216),                -- Inferred language of the clip
    LYRICS_COPYRIGHT_METADATA VARCHAR(16777216),        -- Copyright metadata for lyrics
    
    -- 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
);

