-- Discord Info table for storing user Discord information from RDS
-- This table is populated by the Glue job rds_to_s3_bots_discordinfo_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Full snapshot table - replaced hourly
CREATE TRANSIENT TABLE IF NOT EXISTS {discord_info_table_name} (
    -- Primary identifier
    ID NUMBER(38,0),                                    -- Unique Discord info identifier
    
    -- User references
    USER_ID NUMBER(38,0),                               -- User ID reference
    UID VARCHAR(255),                                   -- Discord UID
    CLERK_ID VARCHAR(255),                              -- Clerk authentication ID
    
    -- User profile
    DISPLAY_NAME VARCHAR(255),                          -- User display name
    HANDLE VARCHAR(255),                                -- User handle
    HANDLE_UPDATED_AT VARCHAR(255),                     -- When handle was last updated
    BIRTHDAY VARCHAR(255),                              -- User birthday
    PHONE_NUMBER VARCHAR(255),                          -- User phone number
    
    -- Terms of service
    HAS_ACCEPTED_TOS BOOLEAN,                           -- Whether user has accepted TOS
    
    -- Subscription details
    SUBSCRIPTION_STATUS VARCHAR(255),                   -- Current subscription status
    SUBSCRIPTION_PLAN_ID VARCHAR(255),                  -- Subscription plan ID
    SUBSCRIPTION_ANCHOR VARCHAR(255),                   -- Subscription anchor date
    SUBSCRIPTION_CANCEL_AT VARCHAR(255),                -- When subscription will be cancelled
    SUBSCRIPTION_PERIOD_END VARCHAR(255),               -- Subscription period end date
    ACTIVE_SUBSCRIPTION_PLATFORM VARCHAR(255),          -- Platform of active subscription
    
    -- Payment integration
    STRIPE_CUSTOMER_ID VARCHAR(16777216),               -- Stripe customer ID
    STRIPE_SUBSCRIPTION_ID VARCHAR(16777216),           -- Stripe subscription ID
    REVCAT_SUBSCRIPTION_ID VARCHAR(255),                -- RevenueCat subscription ID
    
    -- Credits
    EXTRA_CREDITS_BALANCE NUMBER(38,0),                 -- Extra credits balance
    EXTRA_CREDITS_USED NUMBER(38,0),                    -- Extra credits used
    
    -- Platform
    REGISTERED_PLATFORM VARCHAR(255),                   -- Platform where user registered
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23)
);

