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

WITH clip_level_likes AS (
    SELECT
        clh.target_user_id,
        clh.clip_id,
        COUNT_IF(clh.net_action_value > 0) AS clip_likes_received,
        COUNT_IF(clh.net_action_value > 0 AND cph.play_cnt_5s > 0) AS clip_likes_received_verified,
        -- Public song indicators
        MAX(CASE WHEN fcs.is_public = true THEN 1 ELSE 0 END) AS is_public_clip,
        COUNT_IF(clh.net_action_value > 0 AND fcs.is_public = true) AS public_clip_likes_received,
        COUNT_IF(clh.net_action_value > 0 AND cph.play_cnt_5s > 0 AND fcs.is_public = true) AS public_clip_likes_received_verified
    FROM {{ ref('int_user_clip_like_historical') }} clh
    LEFT JOIN {{ ref('int_agg_user_clip_plays') }} cph
        ON clh.clip_id = cph.clip_id
        AND clh.user_id = cph.user_id
    LEFT JOIN {{ ref('stg_cleaned__fact_clip_status') }} fcs
        ON clh.clip_id = fcs.clip_id
    WHERE clh.is_user_song_owner = FALSE
    GROUP BY clh.target_user_id, clh.clip_id
)

SELECT
    cll.target_user_id AS user_id

    -- For all songs: like received, verified like received, and like from other people
    , SUM(cll.clip_likes_received) AS n_clip_likes_received
    , SUM(cll.clip_likes_received_verified) AS n_clip_likes_received_verified
    , COUNT(DISTINCT cll.clip_id) AS n_clip_receiving_likes

    -- For public songs: like received, verified like received, and like from other people
    , SUM(cll.public_clip_likes_received) AS n_public_clip_likes_received
    , SUM(cll.public_clip_likes_received_verified) AS n_public_clip_likes_received_verified
    , COUNT(DISTINCT CASE WHEN cll.is_public_clip = 1 THEN cll.clip_id END) AS n_public_clip_receiving_likes

    -- Statistics for verified likes per clip (all clips)
    , MAX(cll.clip_likes_received_verified) AS max_clip_verified_likes
    , AVG(cll.clip_likes_received_verified) AS avg_clip_verified_likes
    , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cll.clip_likes_received_verified) AS p50_clip_verified_likes

    -- Statistics for verified likes per clip (public songs)
    , MAX(cll.public_clip_likes_received_verified) AS max_public_clip_verified_likes
    , AVG(cll.public_clip_likes_received_verified) AS avg_public_clip_verified_likes
    , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cll.public_clip_likes_received_verified) AS p50_public_clip_verified_likes
FROM clip_level_likes cll
GROUP BY 1
