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

-- Studio events with session identification logic
-- Consolidates all Studio-related events from multiple sources and identifies sessions
-- Sessions are defined by 30-minute gaps between Studio "use" events

WITH user_mapping AS (
    SELECT DISTINCT
        user_id
        , user_uid
    FROM {{ ref('stg_cleaned__dim_user') }}
)

, web_user_events AS (
    SELECT
        wue.user_uid
        , um.user_id
        , wue.session_id
        , wue.client_timestamp
        , wue.context
        , wue.page_url
        , wue.action_name AS event_name
        , 'web_user_event' AS event_source
        , wue.server_timestamp AS event_timestamp
        , null AS properties
        , wue.p_date
        , wue.p_hour
    FROM {{ source('segment_events', 'web_user_event') }} AS wue
    LEFT JOIN user_mapping AS um
        ON wue.user_uid = um.user_uid
    WHERE
        wue.p_date >= '{{ var("studio_project_start_date") }}'
        AND wue.action_name IN (
            'NavigatedToStudio'
            , 'StudioLoaded'
            , 'StudioAutosaving'
            , 'StudioExport'
            , 'StudioContextualGenerateClicked'
            , 'StudioCreateFormGenerateClicked'
        )
        AND um.user_id IS NOT null
)

, backend_studio_events AS (
    SELECT
        user_id
        , session_id
        , name AS event_name
        , 'bk_nonrealtime_event' AS event_source
        , timestamp AS event_timestamp
        , properties
        , p_date
        , p_hour
        , null AS user_uid
        , null AS client_timestamp
        , null AS context
        , null AS page_url
    FROM {{ source('backend_events', 'bk_nonrealtime_event') }}
    WHERE
        p_date >= '{{ var("studio_project_start_date") }}'
        AND name IN (
            'StudioProjectCreate'
            , 'StudioProjectLoad'
            , 'StudioProjectSave'
            , 'StudioProjectArchive'
            , 'StudioProjectUnarchive'
            , 'StudioProjectClone'
            , 'StudioProjectRename'
            , 'StudioProjectRevisionGet'
            , 'StudioProjectVersionsList'
            , 'StudioProjectVersionStateGet'
            , 'StudioRenderState'
        )
        AND user_id IS NOT null
)

, web_audio_creation_events AS (
    SELECT
        wac.user_id::VARCHAR AS user_uid_str
        , session_id
        , action_name AS event_name
        , 'web_audio_creation' AS event_source
        , server_timestamp AS event_timestamp
        , properties
        , p_date
        , p_hour
        , null AS client_timestamp
        , null AS context
        , null AS page_url
        , click_source_url
        , new_clip_ids
        , upload_type
    FROM {{ source('web', 'web_audio_creation') }} AS wac
    WHERE
        p_date >= '{{ var("studio_project_start_date") }}'
        AND click_source_url = '/studio'
        AND action_name IN ('UploadSong', 'CreateSong')
        AND wac.user_id IS NOT null
)

, web_audio_player_events AS (
    SELECT
        wap.user_id::VARCHAR AS user_uid_str
        , session_id
        , action_name AS event_name
        , 'web_audio_player_actions' AS event_source
        , server_timestamp AS event_timestamp
        , null AS properties
        , p_date
        , p_hour
        , client_timestamp
        , null AS context
        , null AS page_url
        , context_type
        , play_duration
    FROM {{ source('segment_events', 'web_audio_player_actions') }} AS wap
    WHERE
        p_date >= '{{ var("studio_project_start_date") }}'
        AND context_type IN ('studio_new_timeline', 'studio_new_preview')
        AND action_name IN ('PauseSong', 'SeekProgressBarPauseSong')
        AND wap.user_id IS NOT null
        -- Exclude long play durations in timeline (user may have not paused the timeline and leave it playing silence)
        AND NOT (play_duration > 60 * 15 AND context_type = 'studio_new_timeline') 
)

, all_studio_events_union AS (
    SELECT
        wue.user_uid
        , wue.user_id
        , wue.session_id
        , wue.event_name
        , wue.event_source
        , wue.event_timestamp
        , wue.client_timestamp
        , wue.context
        , wue.page_url
        , wue.properties
        , wue.p_date
        , wue.p_hour
        , null AS click_source_url
        , null AS new_clip_ids
        , null AS upload_type
        , null AS context_type
        , null AS play_duration
    FROM web_user_events AS wue

    UNION ALL

    SELECT
        um.user_uid
        , be.user_id
        , be.session_id
        , be.event_name
        , be.event_source
        , be.event_timestamp
        , be.client_timestamp
        , be.context
        , be.page_url
        , be.properties
        , be.p_date
        , be.p_hour
        , null AS click_source_url
        , null AS new_clip_ids
        , null AS upload_type
        , null AS context_type
        , null AS play_duration
    FROM backend_studio_events AS be
    LEFT JOIN user_mapping AS um
        ON be.user_id = um.user_id

    UNION ALL

    SELECT
        wac.user_uid_str AS user_uid
        , um.user_id
        , wac.session_id
        , wac.event_name
        , wac.event_source
        , wac.event_timestamp
        , wac.client_timestamp
        , wac.context
        , wac.page_url
        , wac.properties
        , wac.p_date
        , wac.p_hour
        , wac.click_source_url
        , wac.new_clip_ids
        , wac.upload_type
        , null AS context_type
        , null AS play_duration
    FROM web_audio_creation_events AS wac
    LEFT JOIN user_mapping AS um
        ON wac.user_uid_str = um.user_uid
    WHERE um.user_id IS NOT null

    UNION ALL

    SELECT
        wap.user_uid_str AS user_uid
        , um.user_id
        , wap.session_id
        , wap.event_name
        , wap.event_source
        , wap.event_timestamp
        , wap.client_timestamp
        , wap.context
        , wap.page_url
        , wap.properties
        , wap.p_date
        , wap.p_hour
        , null AS click_source_url
        , null AS new_clip_ids
        , null AS upload_type
        , wap.context_type
        , wap.play_duration
    FROM web_audio_player_events AS wap
    LEFT JOIN user_mapping AS um
        ON wap.user_uid_str = um.user_uid
    WHERE um.user_id IS NOT null
)

, all_studio_events AS (
    SELECT *
    FROM all_studio_events_union
    WHERE user_id IS NOT null
        AND user_uid IS NOT null
)

, step_1 AS (
    SELECT
        *
        , event_timestamp AS created_at
        , event_timestamp AS user_event_created_at
        , event_timestamp AS last_event_timestamp
    FROM all_studio_events
    WHERE user_id IS NOT NULL
)

, step_2 AS (
    SELECT
        *
        -- Get last Studio event created at for a user_id
        , MAX(user_event_created_at) OVER (
            PARTITION BY user_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS previous_global_event_time
    FROM step_1
)

, step_3 AS (
    SELECT
        *
        -- Difference in seconds between the previous event and the current event
        , DATEDIFF(
            SECOND
            , previous_global_event_time
            , user_event_created_at
        ) AS time_between_events_in_seconds
    FROM step_2
)

, step_4 AS (
    SELECT
        *
        -- Define new session: first event OR gap > 30 minutes
        , (
            previous_global_event_time IS NULL
            OR time_between_events_in_seconds > {{ 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
    user_id
    , user_uid
    , session_id AS backend_session_id
    , event_name
    , event_source
    , event_timestamp
    , client_timestamp
    , context
    , page_url
    , TRY_PARSE_JSON(properties) AS properties
    , created_at
    , last_event_timestamp
    , p_date
    , p_hour
    , previous_global_event_time
    , time_between_events_in_seconds
    , is_new_session
    , session_number
    -- User session ID (MD5 hash of user_id and session_number)
    , MD5(user_id || '|' || session_number) AS user_session_id
    -- Additional fields for metrics
    , click_source_url
    , TRY_PARSE_JSON(new_clip_ids) AS new_clip_ids
    , upload_type
    , context_type
    , play_duration
FROM step_5

