{{
    config(
        materialized='table'
        , snowflake_warehouse='DBT_DEV_LARGE'
        , tags = ['exclude_from_dagster=true']
    )
}}

SELECT
    -- Base user information
    aum.user_id
    , aum.user_uid

    -- User Activity Metrics from agg_user_metrics
    , aum.n_days_active
    , aum.n_days_active_create
    , aum.n_days_active_listen
    , aum.n_days_active_last_7_days as n_days_active_l7d
    , aum.n_days_active_create_last_7_days as n_days_active_create_l7d
    , aum.n_days_active_listen_last_7_days as n_days_active_listen_l7d
    , aum.n_days_active_listen_own_last_7_days as n_days_active_listen_own_l7d
    , aum.n_days_active_listen_others_last_7_days as n_days_active_listen_others_l7d
    , aum.n_days_active_last_28_days as n_days_active_l28d
    , aum.n_days_active_create_last_28_days as n_days_active_create_l28d
    , aum.n_days_active_listen_last_28_days as n_days_active_listen_l28d
    , aum.n_days_active_listen_own_last_28_days as n_days_active_listen_own_l28d
    , aum.n_days_active_listen_others_last_28_days as n_days_active_listen_others_l28d

    -- Creation Metrics from agg_user_metrics
    , aum.n_clips_created
    , aum.n_gen_clips_created
    , aum.pct_clips_created_gen
    , aum.avg_clips_created_per_active_create_day

    -- Play Metrics from agg_user_metrics
    , aum.n_plays
    , aum.n_plays_own
    , aum.n_plays_others
    , aum.pct_plays_own
    , aum.ratio_plays_others_to_own
    , aum.avg_plays_per_active_listen_day
    , aum.avg_plays_own_per_active_listen_day
    , aum.avg_plays_others_per_active_listen_day

    -- Play Duration Metrics from agg_user_metrics
    , aum.total_play_duration_seconds
    , aum.total_play_duration_seconds_own
    , aum.total_play_duration_seconds_others
    , aum.pct_play_duration_own
    , aum.ratio_play_duration_others_to_own
    , aum.avg_play_duration_seconds_own
    , aum.avg_play_duration_seconds_others

    -- User Information from agg_user_metrics
    , aum.days_since_user_joined

    -- Notification Metrics from agg_user_sent_notifications
    , ausn.max_daily_notifications_sent_on_day
    , ausn.max_daily_notifications_sent_clip_like
    , ausn.n_days_notifications_sent_clip_like
    , ausn.total_notifications_sent_clip_like
    , ausn.max_daily_notifications_sent_follow
    , ausn.n_days_notifications_sent_follow
    , ausn.total_notifications_sent_follow
    , ausn.max_daily_notifications_sent_comment_related
    , ausn.n_days_notifications_sent_comment_related
    , ausn.total_notifications_sent_comment_related
    , ausn.max_daily_notifications_sent_other
    , ausn.n_days_notifications_sent_other
    -- , ausn.total_notifications_sent_other

    -- Subscription History Metrics from int_subscription_history_summary
    , ishs.first_subscription_start_ts
    , ishs.last_subscription_end_ts
    , ishs.first_subscription_platform
    , ishs.first_subscription_billing_provider
    , ishs.first_subscription_tier
    , ishs.first_subscription_renewal_interval
    , ishs.last_subscription_platform
    , ishs.last_subscription_billing_provider
    , ishs.last_subscription_tier
    , ishs.last_subscription_renewal_interval
    , ishs.total_subscription_days
    , ishs.total_subscription_days_pro
    , ishs.total_subscription_days_premier
    -- , ishs.n_distinct_subscriptions
    , ishs.has_had_annual_subscription

    -- Clip Likes Received from agg_user_clip_likes_received
    , uc.n_clip_likes_received
    , uc.n_clip_likes_received_verified
    , uc.n_clip_receiving_likes

    -- Public Song Likes Metrics
    , uc.n_public_clip_likes_received
    , uc.n_public_clip_likes_received_verified
    , uc.n_public_clip_receiving_likes

    -- All song verified likes statistics
    , uc.max_clip_verified_likes
    , uc.avg_clip_verified_likes
    , uc.p50_clip_verified_likes

    -- Public song verified likes statistics
    , uc.max_public_clip_verified_likes
    , uc.avg_public_clip_verified_likes
    , uc.p50_public_clip_verified_likes

    -- Non-creator play counts exceeding 50% statistics
    , nc_plays_stats.max_non_creator_plays_50pct
    , nc_plays_stats.avg_non_creator_plays_50pct
    , nc_plays_stats.p50_non_creator_plays_50pct

    -- Public song count and popularity metrics from fact_clip_status
    , public_clips.n_public_songs as n_public_songs_received
    , public_clips.popularity_score as total_popularity_score

FROM
    {{ ref('agg_user_metrics') }} aum
LEFT JOIN
    {{ ref('int_user_sent_notifications') }} ausn
    ON aum.user_id = ausn.sender_user_id
LEFT JOIN
    {{ ref('int_subscription_history_summary') }} ishs
    ON aum.user_id = ishs.user_id
LEFT JOIN
    {{ ref('int_user_clip_likes_received') }} uc
    ON aum.user_id = uc.user_id
LEFT JOIN (
    SELECT
        fcs.user_id,
        COUNT(*) as n_public_songs,
        SUM(COALESCE(fcs.like_count, 0) + COALESCE(fcs.play_count_displayed, 0) + COALESCE(fcs.share_count, 0)) as popularity_score
    FROM {{ ref('stg_cleaned__fact_clip_status') }} fcs
    WHERE fcs.is_public = true
        AND NOT fcs.is_deleted
    GROUP BY fcs.user_id
) public_clips
    ON aum.user_id = public_clips.user_id
LEFT JOIN (
    SELECT
        cef.clip_creator_user_id as user_id,
        MAX(COALESCE(cef.non_creator_play_cnt_50pct, 0)) as max_non_creator_plays_50pct,
        AVG(COALESCE(cef.non_creator_play_cnt_50pct, 0)) as avg_non_creator_plays_50pct,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COALESCE(cef.non_creator_play_cnt_50pct, 0)) as p50_non_creator_plays_50pct
    FROM {{ ref('clip_engagement_features') }} cef
    WHERE cef.clip_creator_user_id IS NOT NULL
    GROUP BY cef.clip_creator_user_id
) nc_plays_stats
    ON aum.user_id = nc_plays_stats.user_id
