-- Project Clip table for storing project-clip associations from RDS
-- This table is populated by the Glue job rds_to_s3_bots_projectclip_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by ID for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {project_clip_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID NUMBER(38,0),                                    -- Unique project-clip association identifier
    
    -- Clip ordering
    RELATIVE_INDEX NUMBER(38,0),                        -- Relative position of clip in project
    PINNED BOOLEAN,                                     -- Whether the clip is pinned
    
    -- Timestamps
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- References
    CLIP_ID VARCHAR(255),                               -- ID of the clip
    PROJECT_ID VARCHAR(255),                            -- ID of the project
    UPDATED_BY_ID NUMBER(38,0),                         -- User ID who last updated this
    USER_ID NUMBER(38,0),                               -- User ID who created the association
    
    -- Metadata
    CREATED_SESSION_ID VARCHAR(255),                    -- Session ID when association was created
    
    -- 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
);

