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

WITH orpheus_ids AS (
    SELECT
        clipid.value AS clip_id
        , m.user_session_id
    FROM {{ ref('int_orpheus__messages') }} AS m
    , LATERAL FLATTEN(input => m.clipids_created) AS clipid
    WHERE m.clipids_created IS NOT null
)

, clip_initial_metrics AS (
    SELECT *
    FROM {{ ref('clip_initial_metrics') }}
    WHERE p_date >= '{{ var("orpheus_project_start_date") }}'
)

, clips AS (
    SELECT
        id AS clip_id
        , TRY_PARSE_JSON(metadata):create_mode::VARCHAR AS create_mode
        , TRY_PARSE_JSON(metadata):credit_cost::NUMBER AS credit_cost
        , TRY_PARSE_JSON(metadata):lang::VARCHAR AS lang
        , TRY_PARSE_JSON(metadata):source::VARCHAR AS source
        , TRY_PARSE_JSON(metadata):type::VARCHAR AS clip_type
        , TRY_PARSE_JSON(metadata):task::VARCHAR AS task_name
    FROM
        {{ source('rds', 'clip') }}
    WHERE p_date >= '{{ var("orpheus_project_start_date") }}'
)

SELECT
    cim.clip_id
    , cim.user_id
    , cim.user_uid
    , cim.clip_created_at
    , cim.window_minutes
    , cim.window_start_time
    , cim.window_end_time
    , cim.play_cnt
    , cim.play_duration_seconds
    , cim.n_like_events
    , cim.n_undolike_events
    , cim.net_like_events
    , cim.n_dislike_events
    , cim.n_undodislike_events
    , cim.net_dislike_events
    , cim.n_share_events
    , cim.n_download_events
    , cim.n_makepublic_events
    , cim.n_makeprivate_events
    , cim.net_public_toggle_events
    , cim.net_positive_interactions
    , cim.is_satisfactory_indirect
    , cim.is_satisfactory_direct
    , cim.is_satisfactory
    , cim.metadata
    , cim.p_date
    , cim.p_hour
    , clips.credit_cost
    , clips.lang
    , clips.source
    , clips.clip_type
    , clips.task_name
    , orpheus_ids.user_session_id
    , IFF(
        orpheus_ids.clip_id IS NOT null
        , 'orpheus'
        , clips.create_mode
    ) AS create_mode
FROM clip_initial_metrics AS cim
LEFT JOIN
    orpheus_ids ON cim.clip_id = orpheus_ids.clip_id
LEFT JOIN
    clips ON cim.clip_id = clips.clip_id
