{{
    config(
        materialized='view'
    )
}}

WITH
base AS (
    SELECT
        user_id --it is not unique

        , id AS session_id
        , name --Id is a more realiable session id than the session_id 
        , visibility

        , workspace_id
        , tool_call_id AS tool_call_id_used

        , p_date

        , p_hour
        , id || '#' || type AS full_id
        , SPLIT_PART(type, '#', 1) AS type

        , IFF(content = '', null, content) AS content
        , CASE
            WHEN (finish_reason IS null OR finish_reason = '') AND tool_call_id IS NOT null THEN 'display_generation'
            ELSE finish_reason
        END AS finish_reason
        , TRY_PARSE_JSON(tool_calls:S) AS tool_calls
        , TO_TIMESTAMP_NTZ((
            COALESCE(
                created_at::FLOAT
                , TRY_TO_DECIMAL(SPLIT_PART(type, '#', 2), 38, 6)
            )
            * 100000
        )::INT, 5) AS created_at
        , TO_TIMESTAMP_NTZ((updated_at * 100000)::INT, 5) AS updated_at
    FROM
        {{ ref('raw_orpheus_ddb') }}
)

, processed_tool_calls AS (
    -- This assumes a single function call per row (there are some outliers cases this is not true)
    -- Temporal fix to solve data being logged as a stream
    SELECT
        full_id
        , MIN(tcs.value:id)::VARCHAR AS tool_call_id
        , MIN(tcs.value:function:name)::VARCHAR AS tool_call_name
        , TRY_PARSE_JSON(LISTAGG(tcs.value:function:arguments)::VARCHAR) AS tool_call_arguments
    FROM
        base
    , LATERAL FLATTEN(INPUT => tool_calls, MODE => 'BOTH') AS tcs
    GROUP BY full_id
)

, user_information AS (
    SELECT
        user_id
        , user_uid
        , clerk_id
    FROM {{ ref('stg_cleaned__dim_user') }}
)

SELECT
    -- Core identifiers
    base.full_id
    , session_id
    , ui.user_id
    , ui.user_uid
    , base.user_id AS clerk_id
    , workspace_id

    -- Event details
    , type
    , name
    , content
    , finish_reason
    , visibility

    -- Message processing
    , tool_call_id_used
    , tool_call_id
    , tool_call_name

    -- Tool call information
    , tool_call_arguments
    , tool_calls
    , tool_call_arguments:message::VARCHAR AS tc_sm_message
    , tool_call_arguments:genre_example::VARCHAR AS tc_genre_example
    , tool_call_arguments:prompt::VARCHAR AS tc_prompt

    -- Tool call parsed fields - simple_message
    , created_at
    , updated_at
    , p_date

    -- Tool call parsed fields - write_lyrics, generate_image
    , p_hour
    , COALESCE(content, tool_call_arguments:message::VARCHAR) AS message

    -- Timestamps
    , IFF(finish_reason IS null, 'user', 'orpheus') AS author
    , IFF(finish_reason IS NOT null AND tool_call_name != 'simple_message', 'internal', 'chat') AS message_type

    -- Partitioning
    , tool_call_arguments:choices AS tc_sm_choices
    , TRY_PARSE_JSON(tool_call_arguments:current_prompt) AS tc_sm_current_prompt

FROM base
LEFT JOIN processed_tool_calls ON base.full_id = processed_tool_calls.full_id
LEFT JOIN user_information AS ui ON base.user_id = ui.clerk_id
