-- Grouped User Notification table for storing aggregated notification data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_groupedusernotification_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 {grouped_user_notification_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique notification group identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the notification was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    LAST_OPEARTION_AT TIMESTAMP_TZ(9),                  -- Last operation timestamp
    SEND_AT TIMESTAMP_TZ(9),                            -- When the notification was sent
    
    -- Notification details
    TYPE VARCHAR(255),                                  -- Type of notification
    CONTENT_ID VARCHAR(255),                            -- Primary content identifier
    SEOCNDARY_CONETENT_ID VARCHAR(255),                 -- Secondary content identifier
    
    -- Sender information
    SENDER_ID_1 NUMBER(38,0),                           -- First sender user ID
    SENDER_ID_2 NUMBER(38,0),                           -- Second sender user ID
    SENDER_ID_3 NUMBER(38,0),                           -- Third sender user ID
    
    -- Aggregation and status
    COUNT NUMBER(38,0),                                 -- Count of grouped notifications
    IS_READ BOOLEAN,                                    -- Whether notification is read
    IS_ACTIVE BOOLEAN,                                  -- Whether notification is active
    
    -- User association
    USER_ID NUMBER(38,0),                               -- Recipient user ID
    
    -- 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
);

