-- Bots Project table for storing project data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_project_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 {bots_project_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique project identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the project was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Project details
    NAME VARCHAR(255),                                  -- Project name
    DESCRIPTION VARCHAR(16777216),                      -- Project description
    IMAGE_S3_ID VARCHAR(255),                           -- S3 ID for project image
    
    -- Status flags
    IS_TRASHED BOOLEAN,                                 -- Whether the project is trashed
    IS_PUBLIC BOOLEAN,                                  -- Whether the project is public
    
    -- References
    USER_ID NUMBER(38,0),                               -- User who created the project
    CREATED_SESSION_ID VARCHAR(255),                    -- Session ID when project was created
    
    -- 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
);

