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

WITH step_1 AS (
    SELECT
        *

        -- Message parsed 
        , TRY_PARSE_JSON(message) AS message_parsed

        -- is_from_choices (checks if current message is from choices)
        , (author = 'user' AND ARRAY_CONTAINS(
            message::VARIANT
            , LAG(tc_sm_choices)
                OVER (PARTITION BY session_id ORDER BY created_at)
        )) AS is_from_choices

        -- is_user_annoyed_heuristic (checks for keywords indicating user annoyance)
        , REGEXP_LIKE(
            LOWER(content)
            , 'please|right now|just generate|generate now|just do|come on|hurry|why not'
            || '|again|seriously|asap|immediately|for god|annoyed|frustrat|wtf|damn|ugh|why can''t|why cant'
        ) AS is_user_annoyed_heuristic

        -- Created at for users messages only
        , IFF(author = 'user', created_at, null) AS user_message_created_at
        , IFF(
            tool_call_name IS NOT null AND tool_call_name != 'simple_message'
            , created_at
            , null
        ) AS tool_call_created_at
        , IFF(author = 'orpheus' AND message_type = 'chat', created_at, null)
            AS orpheus_chat_created_at
        -- Message number for a chat
        , ROW_NUMBER()
            OVER (PARTITION BY session_id ORDER BY created_at)
            AS message_number

        -- Previous chat message author, message type and tool call name for consecutive analysis
        , LAG(author)
            OVER (PARTITION BY session_id ORDER BY created_at)
            AS previous_chat_message_author
        , LAG(message_type)
            OVER (PARTITION BY session_id ORDER BY created_at)
            AS previous_chat_message_message_type
    FROM
        {{ ref('int_orpheus__cleaned') }}
    WHERE
        type = 'message'
        AND user_id IS NOT null
)

, step_2 AS (
    SELECT
        *

        --Get last user message created at for a user_id and session_id
        , MAX(user_message_created_at) OVER (
            PARTITION BY user_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS previous_global_message_time
        , MAX(user_message_created_at) OVER (
            PARTITION BY session_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS previous_chat_message_time
        , MAX(created_at) OVER (
            PARTITION BY session_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS last_any_message_time
        , MAX(tool_call_created_at) OVER (
            PARTITION BY session_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS previous_tool_call_time

        -- Max message number for a session_id
        , MAX(message_number)
            OVER (PARTITION BY session_id)
            AS max_message_number_in_chat

        -- Parsing messages
        -- Message is { clipIds: [ clip_id1, ... ] } when created a clip
        , message_parsed:clipIds AS clipids_created
        -- Message is { type: 'image', url: 'image_url' } when created an image
        , IFF(
            message_parsed:type::VARCHAR = 'image', message_parsed:url, null
        ) AS images_url_created
    FROM step_1
)

, step_3 AS (
    SELECT
        *

        -- Difference in seconds between the previous message 
        -- and the current message across all chats
        , DATEDIFF(
            SECOND
            , previous_global_message_time
            , user_message_created_at
        ) AS time_between_messages_in_seconds_all_chats
        , DATEDIFF(
            SECOND
            , previous_chat_message_time
            , user_message_created_at
        ) AS time_between_messages_in_seconds_chat

        , DATEDIFF(
            SECOND
            , last_any_message_time
            , created_at
        ) AS time_diff_any_message_in_seconds
        , DATEDIFF(
            SECOND
            , previous_chat_message_time
            , orpheus_chat_created_at
        ) AS time_orpheus_answered_message_in_seconds

        -- Difference in seconds between the previous tool call 
        -- and the current tool call
        , DATEDIFF(
            SECOND
            , previous_tool_call_time
            , tool_call_created_at
        ) AS time_between_tool_calls_in_seconds

        -- Flag for last user message in a session_id
        , (author = 'user' AND message_number = max_message_number_in_chat)
            AS is_user_last_message_in_chat

    FROM step_2
)

, step_4 AS (
    SELECT
        *

        -- Define new session
        , (author = 'user' AND (
            previous_global_message_time IS null
            OR time_between_messages_in_seconds_all_chats
            > {{ var('create_session_timeout_minutes') }} * 60
        )) AS is_new_session

    FROM step_3
)

, step_5 AS (
    SELECT
        *

        -- Enumerate sessions per user 
        , SUM(CASE WHEN is_new_session THEN 1 ELSE 0 END)
            OVER (
                PARTITION BY user_id
                ORDER BY created_at ROWS UNBOUNDED PRECEDING
            )
            AS session_number
    FROM step_4
)

SELECT
    -- Core identifiers
    full_id
    , session_id
    , user_id
    , user_uid
    , clerk_id
    , type

    -- Event details
    , finish_reason
    , message

    -- Message processing
    , author
    , message_type
    , tool_call_name

    -- Tool call information
    , tool_call_id_used
    , tool_call_id
    , tc_sm_choices
    , created_at

    -- Timestamps
    , updated_at
    , p_date

    -- Partitioning
    , p_hour
    , previous_global_message_time

    -- Time between messages
    , previous_chat_message_time
    , time_between_messages_in_seconds_all_chats
    , time_between_messages_in_seconds_chat
    , time_orpheus_answered_message_in_seconds
    , time_between_tool_calls_in_seconds
    , time_diff_any_message_in_seconds
    , is_new_session

    -- Sessions related metrics
    , session_number
    , message_number

    --General counts metrics
    , max_message_number_in_chat
    , images_url_created

    -- General interaction metrics
    , clipids_created
    , is_from_choices
    , is_user_annoyed_heuristic
    , is_user_last_message_in_chat

    -- User session ID
    , MD5(user_id || '|' || session_number) AS user_session_id

FROM step_5
