{{
    config(
        materialized='table',
        snowflake_warehouse='SUNO_PROD_LARGE'
    )
}}

-- Fact Studio User Sessions
-- One row per Studio session, tracking user engagement with Studio
-- Sessions are defined by 30-minute gaps of inactivity between Studio "use" events

WITH studio_events_with_sessions AS (
    SELECT *
    FROM {{ ref('int_studio__events_with_sessions') }}
)

, main_session_metrics AS (
    SELECT
        user_session_id
        , ANY_VALUE(user_id) AS user_id
        , ANY_VALUE(user_uid) AS user_uid
        , 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
        , MAX(last_event_timestamp) AS last_event_timestamp
        , DATEADD(
            MINUTE
            , {{ var('create_session_timeout_minutes') }}
            , MAX(last_event_timestamp)
        ) AS session_end_time
        , DATEDIFF(SECOND, MIN(created_at), MAX(last_event_timestamp)) AS session_duration_seconds
    FROM studio_events_with_sessions
    GROUP BY user_session_id
)

, session_started_reason AS (
    SELECT
        se.user_session_id
        , ARRAY_AGG(DISTINCT se.context:trigger::VARCHAR) WITHIN GROUP (ORDER BY se.context:trigger::VARCHAR)
            AS session_started_reason
    FROM studio_events_with_sessions AS se
    WHERE se.event_name = 'NavigatedToStudio'
    GROUP BY se.user_session_id
)

, play_duration_metrics AS (
    SELECT
        se.user_session_id
        , SUM(COALESCE(se.play_duration, 0)) AS play_duration_total_seconds
        , SUM(IFF(se.context_type = 'studio_new_preview', COALESCE(se.play_duration, 0), 0))
            AS play_duration_sidebars_seconds
        , SUM(IFF(se.context_type = 'studio_new_timeline', COALESCE(se.play_duration, 0), 0))
            AS play_duration_timeline_seconds
    FROM studio_events_with_sessions AS se
    WHERE se.event_source = 'web_audio_player_actions'
    GROUP BY se.user_session_id
)

, project_load_events AS (
    SELECT
        se.user_session_id
        , se.event_timestamp
        , se.properties:project_id::VARCHAR AS project_id
        , se.properties:user_id::VARCHAR AS project_user_id
        , se.user_id AS session_user_id
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_source = 'bk_nonrealtime_event'
        AND se.event_name = 'StudioProjectLoad'
)

, project_create_metrics AS (
    SELECT
        se.user_session_id
        , COUNT_IF(se.event_name IN ('StudioProjectCreate', 'StudioProjectClone')) AS n_projects_created
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_source = 'bk_nonrealtime_event'
        AND se.event_name IN ('StudioProjectCreate', 'StudioProjectClone')
    GROUP BY se.user_session_id
)

, project_metrics AS (
    SELECT
        pl.user_session_id
        , COUNT(DISTINCT pl.project_id) AS n_projects_opened
        , COUNT_IF(pl.project_user_id != pl.session_user_id::VARCHAR OR pl.project_user_id IS null)
            AS n_projects_opened_not_owned
        , ARRAY_AGG(DISTINCT pl.project_id) AS studio_project_ids_opened
    FROM project_load_events AS pl
    GROUP BY pl.user_session_id
)

, create_song_events AS (
    SELECT
        se.user_session_id
        , se.event_timestamp
        , se.new_clip_ids
        , se.new_clip_ids AS new_clip_ids_array
        , ARRAY_SIZE(se.new_clip_ids) AS clip_count
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_source = 'web_audio_creation'
        AND se.event_name = 'CreateSong'
        AND se.new_clip_ids IS NOT null
)

, clips_made_metrics AS (
    SELECT
        user_session_id
        , COUNT(DISTINCT clip_id) AS n_clips_total
        , COUNT(DISTINCT IFF(clip_count <= 2, clip_id, null)) AS n_clips_non_stem
        , COUNT(DISTINCT IFF(clip_count > 2, clip_id, null)) AS n_clips_stem
        , COUNT(DISTINCT IFF(clip_count > 2, event_timestamp, null)) AS clicks_extract_stem
        , ARRAY_AGG(DISTINCT clip_id) AS all_clip_ids
    FROM (
        SELECT
            cse.user_session_id
            , cse.clip_count
            , cse.event_timestamp
            , value::VARCHAR AS clip_id
        FROM create_song_events AS cse
        , LATERAL FLATTEN(INPUT => cse.new_clip_ids_array)
    )
    GROUP BY user_session_id
)

, clips_made_by_task AS (
    SELECT
        flattened.user_session_id
        , dc.task AS task_name
        , COUNT(*) AS task_count
    FROM (
        SELECT
            cse.user_session_id
            , f.value::VARCHAR AS clip_id
        FROM create_song_events AS cse
        , LATERAL FLATTEN(INPUT => cse.new_clip_ids_array) AS f
    ) AS flattened
    LEFT JOIN {{ ref('dim_clip') }} AS dc
        ON flattened.clip_id = dc.clip_id
    GROUP BY flattened.user_session_id, dc.task
)

, clips_made_by_task_object AS (
    SELECT
        user_session_id
        , OBJECT_AGG(task_name, task_count) AS clips_made_by_task
    FROM clips_made_by_task
    GROUP BY user_session_id
)

, clicks_create_metrics AS (
    SELECT
        se.user_session_id
        , COUNT_IF(se.event_name = 'StudioContextualGenerateClicked') AS clicks_create_timeline
        , COUNT_IF(se.event_name = 'StudioCreateFormGenerateClicked') AS clicks_create_custom_form
    FROM studio_events_with_sessions AS se
    GROUP BY se.user_session_id
)

, clicks_create_timeline_by_mode AS (
    SELECT
        se.user_session_id
        , se.context:contextualGenerateMode::VARCHAR AS contextual_generate_mode
        , COUNT(*) AS mode_count
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_name = 'StudioContextualGenerateClicked'
        AND se.context:contextualGenerateMode IS NOT null
    GROUP BY se.user_session_id, contextual_generate_mode
)

, clicks_create_timeline_by_mode_object AS (
    SELECT
        user_session_id
        , OBJECT_AGG(contextual_generate_mode, mode_count) AS clicks_create_timeline_by_mode
    FROM clicks_create_timeline_by_mode
    GROUP BY user_session_id
)

, download_metrics AS (
    SELECT
        flattened.user_session_id
        , SUM(IFF(flattened.clip_count <= 2, cim.n_download_events, 0)) AS single_clip_downloads
        , SUM(IFF(flattened.clip_count > 2, cim.n_download_events, 0)) AS stem_clip_downloads
    FROM (
        SELECT
            cse.user_session_id
            , cse.clip_count
            , f.value::VARCHAR AS clip_id
        FROM create_song_events AS cse
        , LATERAL FLATTEN(INPUT => cse.new_clip_ids_array) AS f
    ) AS flattened
    LEFT JOIN {{ ref('clip_initial_metrics') }} AS cim
        ON flattened.clip_id = cim.clip_id
    GROUP BY flattened.user_session_id
)

, audio_upload_by_type AS (
    SELECT
        se.user_session_id
        , COALESCE(se.upload_type, 'unknown') AS upload_type
        , COUNT(*) AS upload_count
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_source = 'web_audio_creation'
        AND se.event_name = 'UploadSong'
    GROUP BY se.user_session_id, upload_type
)

, audio_upload_object_rows AS (
    SELECT
        user_session_id
        , 'total' AS key_name
        , SUM(upload_count) AS key_value
    FROM audio_upload_by_type
    GROUP BY user_session_id

    UNION ALL

    SELECT
        user_session_id
        , upload_type AS key_name
        , upload_count AS key_value
    FROM audio_upload_by_type
)

, audio_upload_object AS (
    SELECT
        user_session_id
        , OBJECT_AGG(key_name, key_value) AS audio_uploads
    FROM audio_upload_object_rows
    GROUP BY user_session_id
)

, studio_project_clip_imports AS (
    SELECT
        ms.user_session_id
        , ms.session_start_time
        , spc.clip_id
        , spc.created_at AS import_timestamp
        , dc.type AS clip_type
        , dc.task AS clip_task
    FROM main_session_metrics AS ms
    INNER JOIN {{ source('rds', 'rds_bots_studio_project_clip') }} AS spc
        ON
            ms.session_start_time <= spc.created_at
            AND ms.session_end_time > spc.created_at
    INNER JOIN {{ source('rds', 'rds_studio_project') }} AS sp
        ON
            spc.studio_project_id = sp.id
            AND ms.user_id = sp.user_id
    LEFT JOIN {{ ref('dim_clip') }} AS dc
        ON spc.clip_id = dc.clip_id
)

, import_times AS (
    SELECT
        user_session_id
        , session_start_time
        , import_timestamp
        , clip_type
        , clip_task
        , LAG(import_timestamp)
            OVER (PARTITION BY user_session_id ORDER BY import_timestamp)
            AS previous_import_timestamp
        , MIN(import_timestamp) OVER (PARTITION BY user_session_id) AS first_import_timestamp
    FROM studio_project_clip_imports
)

, import_metrics AS (
    SELECT
        user_session_id
        , COUNT(*) AS n_clips_imported
        , COUNT_IF(clip_task IN ('stem_condition', 'cover_stem_condition', 'gen_stem')) AS n_clips_imported_stem
        , COUNT_IF(clip_type = 'upload') AS n_clips_imported_upload
        , COUNT_IF(clip_type = 'studio_export') AS n_clips_imported_studio_export
        , COUNT_IF(clip_type NOT IN ('upload', 'record', 'studio_export') OR clip_type IS null OR clip_task NOT IN ('stem_condition', 'cover_stem_condition', 'gen_stem'))
            AS n_clips_imported_other
        , MEDIAN(DATEDIFF(SECOND, previous_import_timestamp, import_timestamp)) AS median_time_between_imports_seconds
        , MAX(DATEDIFF(SECOND, previous_import_timestamp, import_timestamp)) AS max_time_between_imports_seconds
        , DATEDIFF(SECOND, ANY_VALUE(session_start_time), MIN(first_import_timestamp)) AS time_to_first_import_seconds
    FROM import_times
    GROUP BY user_session_id
)

, import_metrics_object AS (
    SELECT
        user_session_id
        , median_time_between_imports_seconds
        , max_time_between_imports_seconds
        , time_to_first_import_seconds
        , OBJECT_CONSTRUCT(
            'total', n_clips_imported
            , 'stem', n_clips_imported_stem
            , 'upload', n_clips_imported_upload
            , 'studio_export', n_clips_imported_studio_export
            , 'other', n_clips_imported_other
        ) AS clips_imported
    FROM import_metrics
)

, export_metrics AS (
    SELECT
        se.user_session_id
        , COUNT(*) AS n_exported_clips
    FROM studio_events_with_sessions AS se
    WHERE
        se.event_source = 'bk_nonrealtime_event'
        AND se.event_name = 'StudioExport'
    GROUP BY se.user_session_id
)

, studio_autosaving_summary AS (
    SELECT
        se.user_session_id
        , MAX_BY(se.context, se.event_timestamp) AS last_autosave_context
    FROM studio_events_with_sessions AS se
    WHERE se.event_name = 'StudioAutosaving'
    GROUP BY se.user_session_id
)

, satisfaction_metrics AS (
    SELECT
        ms.user_session_id
        , IFF(
            COALESCE(em.n_exported_clips, 0) > 0
            OR COALESCE(dm.single_clip_downloads, 0) > 0
            OR COALESCE(dm.stem_clip_downloads, 0) > 0
            , true
            , false
        ) AS is_session_satisfactory
    FROM main_session_metrics AS ms
    LEFT JOIN export_metrics AS em
        ON ms.user_session_id = em.user_session_id
    LEFT JOIN download_metrics AS dm
        ON ms.user_session_id = dm.user_session_id
)

, dissatisfaction_metrics AS (
    SELECT
        ms.user_session_id
        , IFF(
            COALESCE(pdm.play_duration_timeline_seconds, 0) = 0
            AND (
                COALESCE(ccm.clicks_create_timeline, 0) > 0
                OR COALESCE(ccm.clicks_create_custom_form, 0) > 0
                OR COALESCE(cmm.clicks_extract_stem, 0) > 0
            )
            , true
            , false
        ) AS made_actions_but_never_played_timeline
        , IFF(
            COALESCE(cmm.n_clips_total, 0) > 0
            AND COALESCE(pdm.play_duration_total_seconds, 0) = 0
            , true
            , false
        ) AS clips_created_and_never_played
    FROM main_session_metrics AS ms
    LEFT JOIN play_duration_metrics AS pdm
        ON ms.user_session_id = pdm.user_session_id
    LEFT JOIN clicks_create_metrics AS ccm
        ON ms.user_session_id = ccm.user_session_id
    LEFT JOIN clips_made_metrics AS cmm
        ON ms.user_session_id = cmm.user_session_id
)

, pre_session_type AS (
    SELECT
        ms.user_session_id
        , ms.user_id
        , ms.user_uid
        , ms.session_start_time AS start_timestamp_utc
        , ms.session_end_time AS end_timestamp_utc
        , ms.last_event_timestamp AS last_event_timestamp_utc
        , ms.session_duration_seconds
        , ms.p_date
        , ms.p_hour
        , COALESCE(ssr.session_started_reason, ARRAY_CONSTRUCT()) AS session_started_reason
        , COALESCE(pdm.play_duration_total_seconds, 0) AS play_duration_total_seconds
        , COALESCE(pdm.play_duration_sidebars_seconds, 0) AS play_duration_sidebars_seconds
        , COALESCE(pdm.play_duration_timeline_seconds, 0) AS play_duration_timeline_seconds
        , COALESCE(pcm.n_projects_created, 0) AS n_projects_created
        , COALESCE(pm.n_projects_opened, 0) AS n_projects_opened
        , COALESCE(pm.n_projects_opened_not_owned, 0) AS n_projects_opened_not_owned
        , COALESCE(pm.studio_project_ids_opened, ARRAY_CONSTRUCT()) AS studio_project_ids_opened
        , COALESCE(cmm.n_clips_total, 0) AS n_clips_total
        , COALESCE(cmm.n_clips_non_stem, 0) AS n_clips_non_stem
        , COALESCE(cmm.n_clips_stem, 0) AS n_clips_stem
        , COALESCE(cmtbto.clips_made_by_task, OBJECT_CONSTRUCT()) AS clips_made_by_task
        , COALESCE(ccm.clicks_create_timeline, 0) AS clicks_create_timeline
        , COALESCE(ctbmbo.clicks_create_timeline_by_mode, OBJECT_CONSTRUCT()) AS clicks_create_timeline_by_mode
        , COALESCE(ccm.clicks_create_custom_form, 0) AS clicks_create_custom_form
        , COALESCE(cmm.clicks_extract_stem, 0) AS clicks_extract_stem
        , COALESCE(dm.single_clip_downloads, 0) AS single_clip_downloads
        , COALESCE(dm.stem_clip_downloads, 0) AS stem_clip_downloads
        , COALESCE(aumo.audio_uploads, OBJECT_CONSTRUCT('total', 0)) AS audio_uploads
        , COALESCE(imo.clips_imported, OBJECT_CONSTRUCT('total', 0)) AS clips_imported
        , COALESCE(imo.median_time_between_imports_seconds, 0) AS median_time_between_imports_seconds
        , COALESCE(imo.max_time_between_imports_seconds, 0) AS max_time_between_imports_seconds
        , COALESCE(imo.time_to_first_import_seconds, 0) AS time_to_first_import_seconds
        , COALESCE(em.n_exported_clips, 0) AS n_exported_clips
        , COALESCE(sas.last_autosave_context, OBJECT_CONSTRUCT()) AS studio_autosaving_last_context
        , COALESCE(satm.is_session_satisfactory, false) AS is_session_satisfactory
        , COALESCE(dm2.made_actions_but_never_played_timeline, false) AS made_actions_but_never_played_timeline
        , COALESCE(dm2.clips_created_and_never_played, false) AS clips_created_and_never_played
    FROM main_session_metrics AS ms
    LEFT JOIN session_started_reason AS ssr
        ON ms.user_session_id = ssr.user_session_id
    LEFT JOIN play_duration_metrics AS pdm
        ON ms.user_session_id = pdm.user_session_id
    LEFT JOIN project_create_metrics AS pcm
        ON ms.user_session_id = pcm.user_session_id
    LEFT JOIN project_metrics AS pm
        ON ms.user_session_id = pm.user_session_id
    LEFT JOIN clips_made_metrics AS cmm
        ON ms.user_session_id = cmm.user_session_id
    LEFT JOIN clips_made_by_task_object AS cmtbto
        ON ms.user_session_id = cmtbto.user_session_id
    LEFT JOIN clicks_create_metrics AS ccm
        ON ms.user_session_id = ccm.user_session_id
    LEFT JOIN clicks_create_timeline_by_mode_object AS ctbmbo
        ON ms.user_session_id = ctbmbo.user_session_id
    LEFT JOIN download_metrics AS dm
        ON ms.user_session_id = dm.user_session_id
    LEFT JOIN audio_upload_object AS aumo
        ON ms.user_session_id = aumo.user_session_id
    LEFT JOIN import_metrics_object AS imo
        ON ms.user_session_id = imo.user_session_id
    LEFT JOIN export_metrics AS em
        ON ms.user_session_id = em.user_session_id
    LEFT JOIN studio_autosaving_summary AS sas
        ON ms.user_session_id = sas.user_session_id
    LEFT JOIN satisfaction_metrics AS satm
        ON ms.user_session_id = satm.user_session_id
    LEFT JOIN dissatisfaction_metrics AS dm2
        ON ms.user_session_id = dm2.user_session_id
)

SELECT
    *
    --, NULL AS session_type
FROM pre_session_type
