-- Generated Lyrics table for storing AI-generated lyrics data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_generatedlyrics_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 {generated_lyrics_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                        -- Unique record identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                             -- When the record was created
    UPDATED_AT TIMESTAMP_TZ(9),                             -- Last update timestamp
    
    -- Interaction details
    INTERACTION_TYPE VARCHAR(255),                          -- Type of interaction
    RESULTED_IN_ERROR VARCHAR(16777216),                    -- Error information if any
    IS_COMPLETE BOOLEAN,                                    -- Whether generation is complete
    
    -- Request identifiers
    LYRICS_REQUEST_ID VARCHAR(255),                         -- Lyrics request identifier
    GENERATION_REQUEST_ID VARCHAR(255),                     -- Generation request identifier
    CREATE_SESSION_TOKEN VARCHAR(255),                      -- Session token for creation
    
    -- Input data
    PROMPT_TEXT VARCHAR(16777216),                          -- User prompt text
    MODEL_NAME VARCHAR(255),                                -- Model used for generation

    -- Model outputs
    MODEL_GENERATED_TITLE VARCHAR(16777216),                -- AI-generated title
    MODEL_GENERATED_LYRICS VARCHAR(16777216),               -- AI-generated lyrics
    MODEL_GENERATED_TAGS VARCHAR(16777216),                 -- AI-generated tags
    
    -- Actually used values
    TITLE_USED VARCHAR(16777216),                           -- Title that was used
    TAGS_USED VARCHAR(16777216),                            -- Tags that were used
    CREATE_CUSTOM_USED_MODEL_OUTPUT BOOLEAN,                -- Whether custom output was used
    
    -- User information
    USER_ID NUMBER(38,0),                                   -- User identifier
    SELECTED_FOR_AB BOOLEAN,                                -- Selected for A/B testing

    -- Frontend/UI metadata
    FRONTEND_MODEL_SELECTION VARCHAR(255),                  -- Model selected by the frontend

    -- Context information
    CONTEXT_LYRICS_EDIT VARCHAR(16777216),                  -- Context for lyrics edit
    CONTEXT_LYRICS_PREFIX VARCHAR(16777216),                -- Context prefix
    CONTEXT_LYRICS_SUFFIX VARCHAR(16777216),                -- Context suffix
    INFILL_UNDONE BOOLEAN,                                  -- Whether infill was undone

    -- Source information
    PLATFORM VARCHAR(16),                                   -- Platform of the user, e.g web, ios, android
    SOURCE VARCHAR(32),                                     -- Source of the request, e.g. create_ui, orpheus

    -- Experiment information
    EXPERIMENT_ID VARCHAR(128),                             -- Experiment identifier
    LYRICS_MODEL_KEY VARCHAR(128),                          -- Lyrics model key
    POSITION VARCHAR(16),                                   -- Position of the lyrics, left/right
    
    -- 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
);

