{{
    config(
        materialized = 'incremental'
        , incremental_strategy = 'delete+insert'
        , unique_key = ['clip_id']
        , cluster_by = ['p_date', 'p_hour']
    )
}}

SELECT
    pac.clip_id
    , COALESCE(pac.clip_creator_user_id, dc.user_id) AS clip_creator_user_id
    , COUNT(*) AS n_playlist_adds
    , COUNT(DISTINCT pac.user_id) AS n_users_added_to_playlist
    , BOOLOR_AGG(pac.is_user_song_owner) AS did_owner_add_to_playlist
    , COUNT(DISTINCT CASE WHEN NOT pac.is_user_song_owner THEN pac.user_id END) AS n_non_owner_added_to_playlist
    , COUNT(DISTINCT pac.playlist_id) AS n_playlists_added_to
    , COUNT(DISTINCT CASE WHEN pac.is_user_song_owner THEN pac.playlist_id END) AS n_playlists_added_by_owner
    , COUNT(DISTINCT CASE WHEN NOT pac.is_user_song_owner THEN pac.playlist_id END) AS n_playlists_added_by_non_owner
    , MIN(pac.timestamp) AS first_playlist_add_timestamp
    , MAX(pac.timestamp) AS last_playlist_add_timestamp
    , dc.p_date
    , dc.p_hour
FROM {{ ref('int_playlist_add_clip') }} pac
LEFT JOIN {{ ref('dim_clip') }} dc
    ON pac.clip_id = dc.clip_id
WHERE 1=1
    AND pac.clip_id IS NOT NULL
    {% if is_incremental() %}
    -- Only recompute for clips that have been added to playlists since the last run
    AND pac.clip_id IN (
        SELECT DISTINCT clip_id
        FROM {{ ref('int_playlist_add_clip') }}
        WHERE timestamp > (SELECT MAX(last_playlist_add_timestamp) FROM {{ this }})
    )
    {% endif %}
GROUP BY
    pac.clip_id
    , COALESCE(pac.clip_creator_user_id, dc.user_id)
    , dc.p_date
    , dc.p_hour
