-- Clip Embedding table for storing clip embeddings and similarity data from RDS
-- This table is populated by the Glue job rds_to_s3_clip_embedding_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_embedding_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    CLIP_ID VARCHAR(255),                               -- Unique clip identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the embedding was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Embedding vectors and similarity data
    VECTOR ARRAY,                                       -- Main embedding vector
    SIMILAR_CLIP_IDS ARRAY,                             -- Array of similar clip IDs
    LYRIC_SEMANTIC_EMBEDDING ARRAY,                     -- Lyric semantic embedding vector
    DITTO_SELF_SIM_EMBEDDING ARRAY,                     -- Ditto self-similarity embedding
    DITTO_GENRE_EMBEDDING ARRAY,                        -- Ditto genre embedding vector
    
    -- Processing status
    IS_PROCESSING BOOLEAN,                              -- Whether embedding is currently being processed
    
    -- 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
);

