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

WITH user_activity AS (
    -- User activity metrics
    SELECT
        aud.user_id
        , MAX(aud.p_date) AS last_active_date
        , COUNT(DISTINCT aud.p_date) AS total_active_days
        , COUNT(DISTINCT CASE WHEN aud.is_active_create THEN aud.p_date END) AS creation_active_days
    FROM {{ ref('stg_aggregated__agg_user_daily') }} AS aud
    GROUP BY aud.user_id
)

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

, creation_mastery AS (
    -- Measuring creation activity
    SELECT
        dc.user_id
        , COUNT(DISTINCT dc.clip_id) AS total_clips
    FROM {{ ref('dim_clip') }} AS dc
    GROUP BY dc.user_id
)

, publishing AS (
    SELECT
        bkne.user_id
        , COUNT(DISTINCT TRY_PARSE_JSON(bkne.properties):clip_id::string) AS clips_published
    FROM {{ ref('stg_backend_events__bk_nonrealtime_event') }} AS bkne
    WHERE bkne.name = 'ClipMakePublic'
    GROUP BY bkne.user_id
)

, subscription_data AS (
    SELECT
        user_id
        , n_distinct_subscriptions AS number_of_subscription_purchases_made
    FROM {{ ref('int_subscription_history_summary') }}
)

, credit_data AS (
    SELECT
        user_id
        , period_credit_limit + extra_credits_balance - credits_used - extra_credits_used AS remaining_credits
    FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1 AS is_latest
        FROM {{ ref('stg_rds__period_credit_usage') }}
    )
    WHERE is_latest = 1
)

, hook_data AS (
    SELECT
        user_id
        , hook_session_count AS hooks_listened_to
        , create_hook_success_count AS hooks_created
        , create_hook_success_count AS hooks_published
    FROM {{ ref('user_hook_engagement') }}
)

, orpheus_usage AS (
    SELECT
        user_id
        , COUNT(DISTINCT user_session_id) AS times_used_orpheus
    FROM {{ ref('fact_orpheus_user_sessions') }}
    GROUP BY user_id
)

-- Most listened to genre (from play data)
, most_listened_genre AS (
    SELECT
        apih.user_id
        , MODE(dc.style_tags) AS music_genre_most_listened_to
    FROM {{ ref('stg_aggregated__agg_play_info_hourly') }} AS apih
    INNER JOIN {{ ref('dim_clip') }} AS dc
        ON apih.clip_id = dc.clip_id
    WHERE
        apih.p_date >= DATEADD('day', -90, CURRENT_DATE())
        AND apih.play_duration_threshold = 5
        AND apih.is_user_song_owner = false
        AND dc.style_tags IS NOT null
        AND dc.style_tags != ''
    GROUP BY apih.user_id
)

-- Most generated genre (from created clips)
, most_generated_genre AS (
    SELECT
        dc.user_id
        , MODE(f.value::varchar) AS music_genre_most_generated
    FROM {{ ref('dim_clip') }} AS dc
    , LATERAL FLATTEN(input => SPLIT(dc.style_tags, ',')) AS f
    WHERE
        dc.p_date >= DATEADD('day', -90, CURRENT_DATE())
        AND dc.style_tags IS NOT null
        AND dc.style_tags != ''
        AND f.value IS NOT null
        AND f.value != ''
    GROUP BY dc.user_id
)

-- Self-identified persona survey response
, self_identified_persona AS (
    SELECT
        sr.user_id
        , TRY_PARSE_JSON(sr.response)[0]::varchar AS response
    FROM {{ source('rds', 'rds_survey_responses') }} AS sr
    LEFT JOIN {{ source('rds', 'rds_survey_questions') }} AS sq ON sr.question_id = sq.id
    WHERE sq.group_id = 'welcome_modal' -- welcome modal survey
)

-- Last clip played by user
, last_clip_played AS (
    SELECT
        fp.user_id
        , fp.clip_id
        , dc.title AS clip_name
        , du.display_name AS artist_name
        , CONCAT('https://suno.com/song/', fp.clip_id) AS clip_url
        , CASE
            WHEN dc.image_s3_id IS NOT null THEN CONCAT('https://cdn2.suno.ai/', dc.image_s3_id, '.jpeg')
        END AS art_url
    FROM (
        SELECT
            user_id
            , clip_id
            , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY play_start_ts_utc DESC) AS rn
        FROM {{ ref('stg_cleaned__fact_play') }}
    ) AS fp
    LEFT JOIN {{ source('rds', 'clip') }} AS dc ON fp.clip_id = dc.id
    LEFT JOIN {{ ref('stg_cleaned__dim_user') }} AS du ON dc.user_id = du.user_id
    WHERE fp.rn = 1
)

-- Last clip published by user
, last_clip_published AS (
    SELECT
        bkne.user_id
        , TRY_PARSE_JSON(bkne.properties):clip_id::string AS clip_id
        , dc.title AS clip_name
        , CONCAT('https://suno.com/song/', TRY_PARSE_JSON(bkne.properties):clip_id::string) AS clip_url
        , CASE
            WHEN dc.image_s3_id IS NOT null THEN CONCAT('https://cdn2.suno.ai/', dc.image_s3_id, '.jpeg')
        END AS art_url
    FROM (
        SELECT
            user_id
            , properties
            , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) AS rn
        FROM {{ ref('stg_backend_events__bk_nonrealtime_event') }}
        WHERE
            name = 'ClipMakePublic'
    ) AS bkne
    LEFT JOIN {{ source('rds', 'clip') }} AS dc ON TRY_PARSE_JSON(bkne.properties):clip_id::string = dc.id
    WHERE bkne.rn = 1
)

SELECT
    ud.user_id
    , ud.user_uid
    , ud.user_joined_at AS signup_date
    , ud.display_name AS user_display_name
    , ud.handle AS user_handle
    , ua.last_active_date
    , sip.response AS self_identified_persona
    , null AS power_user -- TBD
    , ud.has_active_subscription AS current_subscription_status
    , ud.current_subscription_tier
    , null AS revenue_data
    , null AS song_shared
    , mlg.music_genre_most_listened_to
    , mgg.music_genre_most_generated --TBD
    , COALESCE(cm.total_clips, 0) AS number_of_songs_created
    , COALESCE(pb.clips_published, 0) AS number_of_songs_published -- TODO: Fix this
    , COALESCE(sd.number_of_subscription_purchases_made, 0) AS number_of_subscription_purchases_made
    , COALESCE(ua.total_active_days, 0) AS times_active
    , COALESCE(ua.creation_active_days, 0) AS times_active_creating
    , COALESCE(cd.remaining_credits, 0) AS remaining_credits
    , COALESCE(ou.times_used_orpheus, 0) AS times_used_orpheus
    , COALESCE(hd.hooks_listened_to, 0) AS hooks_listened_to
    , COALESCE(hd.hooks_created, 0) AS hooks_created
    , COALESCE(hd.hooks_published, 0) AS hooks_published
    , OBJECT_CONSTRUCT(
        'name'
        , lcp.clip_name
        , 'artist_name'
        , lcp.artist_name
        , 'url'
        , lcp.clip_url
        , 'art_url'
        , lcp.art_url
    ) AS last_clip_played
    , OBJECT_CONSTRUCT(
        'name'
        , lcpub.clip_name
        , 'artist_name'
        , ud.display_name -- Only the same user can publish the clip
        , 'url'
        , lcpub.clip_url
        , 'art_url'
        , lcpub.art_url
    ) AS last_clip_published

FROM user_dimensions AS ud
LEFT JOIN user_activity AS ua ON ud.user_id = ua.user_id
LEFT JOIN creation_mastery AS cm ON ud.user_id = cm.user_id
LEFT JOIN publishing AS pb ON ud.user_id = pb.user_id
LEFT JOIN subscription_data AS sd ON ud.user_id = sd.user_id
LEFT JOIN credit_data AS cd ON ud.user_id = cd.user_id
LEFT JOIN hook_data AS hd ON ud.user_id = hd.user_id
LEFT JOIN orpheus_usage AS ou ON ud.user_id = ou.user_id
LEFT JOIN most_listened_genre AS mlg ON ud.user_id = mlg.user_id
LEFT JOIN most_generated_genre AS mgg ON ud.user_id = mgg.user_id
LEFT JOIN self_identified_persona AS sip ON ud.user_id = sip.user_id
LEFT JOIN last_clip_played AS lcp ON ud.user_id = lcp.user_id
LEFT JOIN last_clip_published AS lcpub ON ud.user_id = lcpub.user_id
