-- Label Maker Annotations table for storing user feedback and annotation preferences
-- This table is populated by the Glue job rds_to_s3_bots_labelmaker_annotations_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 {label_maker_annotations_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique annotation identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the annotation was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- User and task information
    USER_ID NUMBER(38,0),                               -- User who created the annotation
    TASK_ID VARCHAR(255),                               -- Associated task identifier
    
    -- Annotation content
    PREFERENCES VARIANT,                                -- User preferences data (JSON)
    FEEDBACK VARCHAR(16777216),                         -- User feedback text
    
    -- Listen metrics
    LISTEN_TIME_SECONDS VARIANT,                        -- Listen time details (JSON)
    TOTAL_LISTEN_TIME_SECONDS FLOAT,                    -- Total listen time in seconds
    SKIPPED BOOLEAN,                                    -- Whether the task was skipped
    
    -- Credits tracking
    CREDITS_EARNED NUMBER(38,0),                        -- Credits earned for annotation
    CREDITS_PROCESSED NUMBER(38,0),                     -- Credits that have been 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
);

