-- App event tracking for both iOS and Android mobile applications
-- Clustered by partition date for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {app_event_table_name} CLUSTER BY (p_date)(
    -- Event identification
    ID NUMBER(38,0) COMMENT 'Auto-incrementing event identifier',
    CATEGORY VARCHAR(16777216) COMMENT 'Event category classification',
    ACTION_NAME VARCHAR(16777216) COMMENT 'Specific action taken by user',
    ACTION_TYPE VARCHAR(16777216) COMMENT 'Type of action performed',
    
    -- UI Element details
    ELEMENT_TYPE VARCHAR(16777216) COMMENT 'Type of UI element interacted with',
    ELEMENT_ID VARCHAR(16777216) COMMENT 'Unique identifier of the UI element',
    ELEMENT_TEXT VARCHAR(16777216) COMMENT 'Text content of the UI element',
    X_INDEX NUMBER(38,0) COMMENT 'X-axis position/index of element',
    Y_INDEX NUMBER(38,0) COMMENT 'Y-axis position/index of element',
    
    -- Event context
    METHOD VARCHAR(16777216) COMMENT 'Method or function that triggered the event',
    CONDITION VARCHAR(16777216) COMMENT 'Condition under which event occurred',
    CONTEXT VARCHAR(16777216) COMMENT 'Additional event context',
    DIRECTION VARCHAR(16777216) COMMENT 'Direction of action (e.g., swipe direction)',
    CREATED_ID VARCHAR(16777216) COMMENT 'Identifier of created object/entity',
    
    -- User identification
    USER_ID VARCHAR(16777216) COMMENT 'Unique user identifier',
    ANONYMOUS_ID VARCHAR(16777216) COMMENT 'Anonymous user identifier for non-logged-in users',
    SESSION_ID VARCHAR(16777216) COMMENT 'Mobile app session identifier',
    
    -- Screen information
    SCREEN_NAME VARCHAR(16777216) COMMENT 'Name of the screen where event occurred',
    SCREEN_ELEMENT_ID VARCHAR(16777216) COMMENT 'Identifier of screen element',
    
    -- Event metadata
    SOURCE VARCHAR(16777216) COMMENT 'Source of the event (iOS/Android)',
    CLIENT_TIMESTAMP VARCHAR(16777216) COMMENT 'Timestamp when event occurred on client device',
    SERVER_TIMESTAMP VARCHAR(16777216) COMMENT 'Timestamp when event was received by server',
    
    -- Device and app information
    USER_AGENT VARCHAR(16777216) COMMENT 'User agent string from the mobile device',
    LOCALE VARCHAR(16777216) COMMENT 'User locale/language setting',
    TIMEZONE VARCHAR(16777216) COMMENT 'User timezone',
    CLIENT_IP VARCHAR(16777216) COMMENT 'User IP address',
    APP_NAMESPACE VARCHAR(16777216) COMMENT 'Application namespace/identifier',
    APP_VERSION VARCHAR(16777216) COMMENT 'Version of the mobile app',
    OS_NAME VARCHAR(16777216) COMMENT 'Operating system name (iOS/Android)',
    OS_VERSION VARCHAR(16777216) COMMENT 'Operating system version',
    
    -- Location information (enriched via geo lookup)
    COUNTRY VARCHAR(255) COMMENT 'User country (enriched from IP)',
    CITY VARCHAR(255) COMMENT 'User city (enriched from IP)',
    
    -- Additional context objects
    CONTEXT_OJECT OBJECT COMMENT 'Legacy context object field (deprecated)',
    CONTEXT_OBJECT OBJECT COMMENT 'Additional context data in JSON format',
    
    -- Partitioning columns
    P_DATE DATE COMMENT 'Partition date for table optimization',
    P_HOUR NUMBER(38,0) COMMENT 'Partition hour for table optimization'
) COMMENT='Table contains app events from both iOS and Android mobile applications';

