-- User Delete Request table for storing user deletion requests from RDS
-- This table is populated by the Glue job rds_to_s3_bots_userdeleterequest_daily_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 {user_delete_request_table_name} CLUSTER BY (id) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique deletion request identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the deletion request was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    DELETED_AT TIMESTAMP_TZ(9),                         -- When the user was actually deleted
    
    -- User identification
    PERSIST_USER_ID NUMBER(38,0),                       -- Persistent user ID for auditing
    USER_ID NUMBER(38,0),                               -- User ID of the user requesting deletion
    CLERK_ID VARCHAR(255),                              -- Clerk authentication ID
    USERNAME VARCHAR(255),                              -- Username of the user
    DISCORD_UID VARCHAR(255),                           -- Discord user ID if linked
    
    -- Deletion status
    IS_DELETED BOOLEAN,                                 -- Whether the deletion has been completed
    IS_OPERATED_BY_ADMIN BOOLEAN,                       -- Whether deletion was triggered by admin
    
    -- Partitioning column for optimization
    P_DATE DATE                                         -- Partition date based on created_at
);

