-- Audio Upload table for storing audio upload data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_audioupload_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 {audio_upload_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique audio upload identifier
    
    -- Timestamps
    CREATED_AT VARCHAR(255),                            -- When the audio upload was created
    UPDATED_AT VARCHAR(255),                            -- Last update timestamp
    
    -- Audio details
    AUDIO_S3_ID VARCHAR(255),                           -- S3 ID for the audio file
    DURATION FLOAT,                                     -- Duration of the audio in seconds
    HAS_VOCAL BOOLEAN,                                  -- Whether the audio has vocals
    
    -- Upload information
    UPLOAD_TYPE VARCHAR(255),                           -- Type of upload
    ERROR_TYPE VARCHAR(255),                            -- Error type if upload failed
    
    -- References
    CLIP_ID VARCHAR(255),                               -- Associated clip identifier
    REQUESTED_CLIP_ID VARCHAR(255),                     -- Requested clip identifier
    USER_ID NUMBER(38,0),                               -- User who uploaded the audio
    
    -- Media and metadata
    IMAGE_S3_ID VARCHAR(255),                           -- S3 ID for associated image
    TITLE VARCHAR(16777216),                            -- Title of the audio upload
    INFERRED_LYRICS VARCHAR(16777216),                  -- Inferred lyrics from the audio
    
    -- Partitioning columns for optimization
    P_DATE VARCHAR(255),                                -- Partition date
    P_HOUR VARCHAR(255)                                 -- Partition hour
);

