{{
    config(
        materialized='external',
        location="@" ~ var('external_s3_env') ~ ".SUNO_DYNAMODB_EVENTS/orpheus",
        file_format=var('external_s3_env') ~ ".BASIC_JSON_LOADER",
        pattern='orpheus/pdate=.*/phour=.*/data/.*.json.gz',
        snowflake_warehouse='DYNAMODB_EVENTS_LARGE'
    )
}}

--NOTE: External materialization depends on following good SQL formatting. See snowflake_external_materialization.sql for more details.
SELECT
    -- Keys
    $1:Keys:item_id:S::VARCHAR AS id,
    $1:Keys:type:S::VARCHAR AS type,

    -- NewImage fields
    $1:NewImage:message_id:S::VARCHAR AS message_id,
    $1:NewImage:session_id:S::VARCHAR AS session_id,
    $1:NewImage:name:S::VARCHAR AS name,
    $1:NewImage:content:S::VARCHAR AS content,
    $1:NewImage:finish_reason:S::VARCHAR AS finish_reason,
    $1:NewImage:sessions::VARIANT AS sessions,
    $1:NewImage:tool_call_id:S::VARCHAR AS tool_call_id,
    $1:NewImage:tool_calls::VARIANT AS tool_calls,
    $1:NewImage:user_id:S::VARCHAR AS user_id,
    $1:NewImage:visibility:S::VARCHAR AS visibility,
    $1:NewImage:workspace_id:S::VARCHAR AS workspace_id,
    $1:NewImage:created_at:N::FLOAT AS created_at,
    $1:NewImage:updated_at:N::FLOAT AS updated_at,

    -- Parse pdate from file path using METADATA$FILENAME. NOTE: Use SPLIT_PART
    TRY_TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'pdate=', 2), '/', 1), 'YYYY-MM-DD') AS p_date,
    -- Parse phour from file path using METADATA$FILENAME. NOTE: Use SPLIT_PART
    TRY_TO_NUMBER(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'phour=', 2), '/', 1)) AS p_hour

FROM {{ source('s3', 'ddb_orpheus') }} 