-- Invite History table for storing user invitation and referral data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_invitehistory_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 {invite_history_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique invite history identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the invite was created
    CREATED_AT_NEW TIMESTAMP_NTZ(9),                    -- Alternative creation timestamp (non-TZ)
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    REWARD_CONDITION_MET_AT TIMESTAMP_TZ(9),            -- When reward conditions were met
    
    -- Reward information
    INVITEE_REWARD_AMOUNT NUMBER(38,0),                 -- Reward amount for invitee
    INVITER_REWARD_AMOUNT NUMBER(38,0),                 -- Reward amount for inviter
    
    -- User relationships
    INVITEE_ID NUMBER(38,0),                            -- ID of invited user
    INVITER_ID NUMBER(38,0),                            -- ID of user who sent invite
    
    -- Status flags
    IS_ACTIVE BOOLEAN,                                  -- Whether invite is active
    IS_RECORDED BOOLEAN,                                -- Whether reward is recorded
    
    -- Email notification flags
    HAS_SENT_CREDIT_LIMIT_DEPLETION_EMAIL BOOLEAN,      -- Credit depletion email sent flag
    HAS_SENT_EMAIL_TO_USER_CREDITS BOOLEAN,             -- User credits email sent flag
    HAS_SENT_INVITE_SUCCESS_EMAIL BOOLEAN,              -- Invite success email sent flag
    
    -- 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
);

