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

SELECT
  user_id
  , clip_id
  , SUM(
    CASE
      WHEN name = 'ClipPinClip' THEN 1
      WHEN name = 'ClipUnpinClip' THEN -1
    END
  ) AS net_action_value
  , MIN(IFF(name = 'ClipPinClip', timestamp, NULL)) AS first_pin_timestamp
  , COUNT_IF(name = 'ClipUnpinClip') > 0 AS has_unpin
  , MAX(timestamp) AS last_action_timestamp
FROM
  {{ ref('stg_backend_events__clip_pin_unpin_clip') }}
WHERE 1=1
  {% if is_incremental() %}
  -- Only recompute for clips that have had pin/unpin actions since the last run
  AND clip_id IN (
    SELECT DISTINCT clip_id
    FROM {{ ref('stg_backend_events__clip_pin_unpin_clip') }}
    WHERE timestamp > (SELECT MAX(last_action_timestamp) FROM {{ this }})
  )
  {% endif %}
GROUP BY 1, 2
