{{ config(
    materialized='incremental',
    unique_key='clip_id'
) }}

SELECT
  cc.clip_id
  , COUNT_IF(NOT cc.is_deleted) AS n_comments
  , COUNT_IF(NOT cc.is_deleted AND cc.parent_id IS NULL) AS n_non_reply_comments
  , COUNT_IF(NOT cc.is_deleted AND cc.parent_id IS NOT NULL) AS n_reply_comments
  , COUNT_IF(cc.is_deleted) AS n_deleted_comments
  , MAX(cc.updated_at) AS updated_at
FROM
  {{ ref('stg_rds__clip_comment') }} cc
WHERE 1=1
{% if is_incremental() %}
  AND cc.updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
GROUP BY 1
