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

WITH orpheus_messages AS (
    SELECT *
    FROM {{ ref('int_orpheus__messages') }}
)

, tool_calls_counts AS (
    SELECT
        user_session_id
        , tool_call_name
        , COUNT(*) AS tool_call_count
    FROM orpheus_messages
    WHERE tool_call_name IS NOT null
    GROUP BY user_session_id, tool_call_name
)

, tool_call_per_session AS (
    SELECT
        user_session_id
        , ARRAY_AGG(OBJECT_CONSTRUCT(
            'tool_call_name', tool_call_name
            , 'tool_call_count', tool_call_count
        )) AS tool_call_metrics
    FROM tool_calls_counts
    GROUP BY user_session_id
)

, clip_satisfaction_metrics AS (
    SELECT
        user_session_id
        , SUM(net_like_events) AS total_likes
        , SUM(n_share_events) AS total_shares
        , SUM(n_download_events) AS total_downloads
        , SUM(net_public_toggle_events) AS total_public_toggles
        , SUM(net_positive_interactions) AS total_positive_interactions
        , SUM(net_dislike_events) AS total_dislikes
        , SUM(IFF(is_satisfactory:v1::BOOLEAN, 1, 0)) AS clips_satisfied_count
        , SUM(IFF(is_satisfactory_direct:v1::BOOLEAN, 1, 0)) AS clips_satisfied_direct_count
        , SUM(IFF(is_satisfactory_indirect:v1::BOOLEAN, 1, 0)) AS clips_satisfied_indirect_count
    FROM {{ ref('int_orpheus__clips') }}
    WHERE user_session_id IS NOT null
    GROUP BY user_session_id
)

, main_session_metrics AS (
    SELECT
        user_session_id
        , ANY_VALUE(user_id) AS user_id
        , ANY_VALUE(user_uid) AS user_uid
        , ANY_VALUE(clerk_id) AS clerk_id
        , ANY_VALUE(session_number) AS session_number

        -- Time metrics
        , MIN(p_date) AS p_date
        , MIN(p_hour) AS p_hour
        , MIN(created_at) AS session_start_time
        , DATEADD(
            MINUTE
            , {{ var('create_session_timeout_minutes') }} - 1
            , MAX(created_at)
        )
            AS session_end_time
        , DATEDIFF(SECOND, MIN(created_at), MAX(created_at))
            AS session_duration_seconds
        , COALESCE(
            MEDIAN(
                IFF(
                    is_new_session
                    , null
                    , time_between_messages_in_seconds_all_chats
                )
            )
            , 0
        ) AS median_time_between_messages_in_seconds
        , COALESCE(
            MEDIAN(
                IFF(
                    is_new_session
                    , null
                    , time_orpheus_answered_message_in_seconds
                )
            )
            , 0
        ) AS median_time_orpheus_answered_message_in_seconds

        -- Chat metrics
        , ARRAY_AGG(DISTINCT session_id) AS chat_session_ids
        , COUNT(DISTINCT session_id) AS chat_sessions_count
        , MEDIAN(max_message_number_in_chat) AS median_messages_in_chat

        -- General metrics
        , OBJECT_CONSTRUCT(
            'total', COUNT(DISTINCT full_id)
            , 'user', COUNT_IF(author = 'user')
            , 'orpheus_chat', COUNT_IF(author = 'orpheus' AND message_type = 'chat')
            , 'orpheus_non_chat', COUNT_IF(author = 'orpheus' AND message_type != 'chat')
            , 'user_from_choices', SUM(IFF(is_from_choices, 1, 0))
        ) AS messages_metrics

        -- Disatisfaction metrics
        , SUM(IFF(is_user_last_message_in_chat, 1, 0))
            AS number_of_no_answered_messages
        , SUM(IFF(is_user_annoyed_heuristic, 1, 0))
            AS number_of_annoyed_messages_heuristic

        -- Creation metrics
        , ARRAY_FLATTEN(ARRAY_AGG(clipids_created)) AS clipids_created
        , COALESCE(SUM(ARRAY_SIZE(clipids_created)), 0) AS clips_created_count
        , ARRAY_AGG(images_url_created) AS images_url_created
        , COALESCE(SUM(ARRAY_SIZE(images_url_created)), 0)
            AS images_created_count

        -- Tool call metrics
        -- 30 minutes is the default session timeout, bigger than X minutes are tool calls from same chat but other user sessions
        , COALESCE(
            MEDIAN(
                IFF(
                    time_between_tool_calls_in_seconds
                    > {{ var('create_session_timeout_minutes') }} * 60
                    , null
                    , time_between_tool_calls_in_seconds
                )
            )
            , 0
        ) AS median_time_between_tool_calls_in_seconds
        , COUNT(DISTINCT tool_call_name) AS distinct_tools_used
        , ARRAY_AGG(DISTINCT tool_call_name) AS tools_used
        , COUNT_IF(
            tool_call_name IS NOT null AND tool_call_name != 'simple_message'
        ) AS total_not_simple_message_tool_calls
        , COALESCE(
            MIN(
                IFF(
                    tool_call_name IS NOT null
                    AND tool_call_name != 'simple_message'
                    , message_number
                    , null
                )
            )
            , 0
        ) AS first_message_number_with_tool_call
    FROM orpheus_messages
    GROUP BY user_session_id
)

, custom_toggle_clicked AS (
    SELECT
        user_uid
        , server_timestamp
        , context:stateAfter::STRING AS state_after
    FROM {{ source('segment_events', 'web_user_event') }}
    WHERE
        p_date >= '{{ var('orpheus_project_start_date') }}'
        AND action_name = 'CustomToggleClicked'
        -- Remove unnecesary data
        AND context:stateBefore::STRING IS NOT null
        AND context:stateAfter::STRING IS NOT null
        AND context:stateBefore::STRING != context:stateAfter::STRING
)

, custom_toggle_clicked_metrics AS (
    SELECT
        ms.user_session_id
        , COUNT_IF(ct.state_after = 'off') AS number_of_switches_to_custom
    FROM custom_toggle_clicked AS ct
    INNER JOIN main_session_metrics AS ms
        ON
            ct.user_uid = ms.user_uid
            AND ct.server_timestamp >= ms.session_start_time
            AND ct.server_timestamp < ms.session_end_time
    GROUP BY ms.user_session_id
)

, playback_metrics AS (
    SELECT
        pm.user_id
        , ms.user_session_id
        , pm.clip_id
        , pm.play_start_ts_utc
        , pm.play_duration_seconds
        , pm.unique_play_percent
        , pm.play_context_id
        , pm.play_context_type = 'orpheus'
        OR ARRAY_CONTAINS(pm.clip_id::VARIANT, ms.clipids_created) AS is_orpheus_play
    FROM
        {{ ref('stg_cleaned__fact_play') }} AS pm
    INNER JOIN main_session_metrics AS ms
        ON
            pm.user_id = ms.user_id
            AND pm.play_start_ts_utc >= ms.session_start_time
            AND pm.play_start_ts_utc < ms.session_end_time
    WHERE
        pm.p_date >= '{{ var('orpheus_project_start_date') }}'
)

, play_metrics_session AS (
    SELECT
        pm.user_session_id
        -- Aggregated play metrics for the session
        -- For play_context_type = 'orpheus' (There is an issue with the first play, so we need to check if the clip_id is in the clipids_created array)
        , ARRAY_AGG(DISTINCT IFF(pm.is_orpheus_play, pm.clip_id, null)) AS played_clip_ids_orpheus
        , COUNT(DISTINCT IFF(pm.is_orpheus_play, pm.clip_id, null)) AS number_of_songs_played_orpheus
        , COUNT_IF(pm.is_orpheus_play) AS number_of_plays_orpheus
        , SUM(IFF(pm.is_orpheus_play, pm.play_duration_seconds, 0)) AS total_play_duration_seconds_orpheus
        , MEDIAN(IFF(pm.is_orpheus_play, pm.unique_play_percent, null)) AS median_unique_play_percent_orpheus

        -- For play_context_type != 'orpheus'
        , ARRAY_AGG(DISTINCT IFF(NOT pm.is_orpheus_play, pm.clip_id, null)) AS played_clip_ids_non_orpheus
        , COUNT(DISTINCT IFF(NOT pm.is_orpheus_play, pm.clip_id, null)) AS number_of_songs_played_non_orpheus
        , COUNT_IF(NOT pm.is_orpheus_play) AS number_of_plays_non_orpheus
        , SUM(IFF(NOT pm.is_orpheus_play, pm.play_duration_seconds, 0)) AS total_play_duration_seconds_non_orpheus
        , MEDIAN(IFF(NOT pm.is_orpheus_play, pm.unique_play_percent, null)) AS median_unique_play_percent_non_orpheus

    FROM playback_metrics AS pm
    GROUP BY
        pm.user_session_id
)

, user_dimensions AS (
    SELECT
        du.user_id
        , du.user_uid
        , du.user_joined_at
        , du.has_active_subscription
        , du.current_subscription_tier
        , du.recent_country
    FROM {{ ref('stg_cleaned__dim_user') }} AS du
)

SELECT
    ms.user_session_id
    , ms.user_id
    , ms.user_uid
    , ms.clerk_id
    , ms.session_number
    , ms.p_date
    , ms.p_hour
    , ud.user_joined_at
    , ud.has_active_subscription
    , ud.current_subscription_tier
    , ud.recent_country
    , ms.session_start_time
    , ms.session_end_time
    , ms.session_duration_seconds
    , ms.median_time_between_messages_in_seconds
    , ms.median_time_orpheus_answered_message_in_seconds
    , ms.chat_session_ids
    , ms.chat_sessions_count
    , ms.median_messages_in_chat
    , ms.messages_metrics
    , ms.number_of_no_answered_messages
    , ms.number_of_annoyed_messages_heuristic
    , ms.clipids_created
    , ms.clips_created_count
    , ms.images_url_created
    , ms.images_created_count
    , ms.median_time_between_tool_calls_in_seconds
    , ms.distinct_tools_used
    , ms.tools_used
    , ms.total_not_simple_message_tool_calls
    , ms.first_message_number_with_tool_call

    --  More Tool call metrics
    , COALESCE(tcs.tool_call_metrics, ARRAY_CONSTRUCT()) AS tool_call_metrics

    -- fields from play_metrics_session (pms)
    , COALESCE(pms.played_clip_ids_orpheus, ARRAY_CONSTRUCT()) AS played_clip_ids_orpheus
    , COALESCE(pms.number_of_songs_played_orpheus, 0) AS number_of_songs_played_orpheus
    , COALESCE(pms.number_of_plays_orpheus, 0) AS number_of_plays_orpheus
    , COALESCE(pms.total_play_duration_seconds_orpheus, 0) AS total_play_duration_seconds_orpheus
    , COALESCE(pms.median_unique_play_percent_orpheus, 0) AS median_unique_play_percent_orpheus
    , COALESCE(pms.played_clip_ids_non_orpheus, ARRAY_CONSTRUCT()) AS played_clip_ids_non_orpheus
    , COALESCE(pms.number_of_songs_played_non_orpheus, 0) AS number_of_songs_played_non_orpheus
    , COALESCE(pms.number_of_plays_non_orpheus, 0) AS number_of_plays_non_orpheus
    , COALESCE(pms.total_play_duration_seconds_non_orpheus, 0) AS total_play_duration_seconds_non_orpheus
    , COALESCE(pms.median_unique_play_percent_non_orpheus, 0) AS median_unique_play_percent_non_orpheus

    -- fields from clip_satisfaction_metrics (csm)
    , COALESCE(csm.total_likes, 0) AS total_likes
    , COALESCE(csm.total_shares, 0) AS total_shares
    , COALESCE(csm.total_downloads, 0) AS total_downloads
    , COALESCE(csm.total_public_toggles, 0) AS total_public_toggles
    , COALESCE(csm.total_positive_interactions, 0) AS total_positive_interactions
    , COALESCE(csm.total_dislikes, 0) AS total_dislikes
    , COALESCE(csm.clips_satisfied_count, 0) AS clips_satisfied_count
    , COALESCE(csm.clips_satisfied_direct_count, 0) AS clips_satisfied_direct_count
    , COALESCE(csm.clips_satisfied_indirect_count, 0) AS clips_satisfied_indirect_count

    -- fields from custom_toggle_clicked_metrics (ctc)
    , COALESCE(ctc.number_of_switches_to_custom, 0) AS number_of_switches_to_custom
FROM main_session_metrics AS ms
LEFT JOIN
    tool_call_per_session AS tcs
    ON ms.user_session_id = tcs.user_session_id
LEFT JOIN play_metrics_session AS pms
    ON ms.user_session_id = pms.user_session_id
LEFT JOIN clip_satisfaction_metrics AS csm
    ON ms.user_session_id = csm.user_session_id
LEFT JOIN custom_toggle_clicked_metrics AS ctc
    ON ms.user_session_id = ctc.user_session_id
LEFT JOIN user_dimensions AS ud
    ON ms.user_id = ud.user_id
