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

SELECT
  -- Base clip data from dim_clip
  icdc.*
  -- Tag upsample data
  , itu.was_prompt_used_exactly

  -- User daily aggregated data
  , aud.clip_total_cnt as owner_creation_day_total_clip_cnt
  , aud.play_duration_sec_own as owner_creation_day_play_duration_sec_own
  , aud.play_duration_sec_others as owner_creation_day_play_duration_sec_others
  , COALESCE(aud.subscription_tier, 'Free') as owner_creation_day_subscription_tier

  -- Historical clip stats from stg_cleaned__fact_clip_status
  , fcs.like_count
  , fcs.dislike_count
  , fcs.flag_count
  , fcs.play_count_displayed
  , fcs.download_audio_mp3_count
  , fcs.download_audio_wav_count
  , fcs.download_video_count
  , fcs.share_count

  -- Owner initial clip metrics from clip_initial_metrics
  , cim.play_cnt AS owner_init_play_cnt
  , cim.play_duration_seconds AS owner_init_play_duration_seconds
  , cim.net_like_events AS owner_init_net_like_events
  , cim.net_dislike_events AS owner_init_net_dislike_events
  , cim.n_share_events AS owner_init_n_share_events
  , cim.n_download_events AS owner_init_n_download_events
  , cim.net_public_toggle_events AS owner_init_net_public_toggle_events
  , cim.net_positive_interactions AS owner_init_net_positive_interactions
  , cim.satisfaction_metric_version AS owner_init_satisfaction_metric_version
  , cim.is_satisfactory_indirect AS owner_init_is_satisfactory_indirect
  , cim.is_satisfactory_direct AS owner_init_is_satisfactory_direct
  , cim.is_satisfactory AS owner_init_is_satisfactory

  -- All features from clip_engagement_features
  , cef.clip_creator_user_id
  , cef.first_play_start_ts_utc
  , cef.last_play_start_ts_utc
  , cef.creator_first_play_start_ts_utc
  , cef.creator_last_play_start_ts_utc
  , cef.creator_play_cnt_0s
  , cef.creator_play_cnt_5s
  , cef.creator_play_cnt_25pct
  , cef.creator_play_cnt_50pct
  , cef.creator_play_cnt_90pct
  , cef.creator_play_duration_seconds_0s
  , cef.creator_play_duration_seconds_5s
  , cef.creator_play_duration_seconds_25pct
  , cef.creator_play_duration_seconds_50pct
  , cef.creator_play_duration_seconds_90pct
  , cef.non_creator_first_play_start_ts_utc
  , cef.non_creator_last_play_start_ts_utc
  , cef.n_non_creator_listeners_0s
  , cef.n_non_creator_listeners_5s
  , cef.n_non_creator_listeners_25pct
  , cef.n_non_creator_listeners_50pct
  , cef.n_non_creator_listeners_90pct
  , cef.non_creator_play_cnt_0s
  , cef.non_creator_play_cnt_5s
  , cef.non_creator_play_cnt_25pct
  , cef.non_creator_play_cnt_50pct
  , cef.non_creator_play_cnt_90pct
  , cef.non_creator_play_duration_seconds_0s
  , cef.non_creator_play_duration_seconds_5s
  , cef.non_creator_play_duration_seconds_25pct
  , cef.non_creator_play_duration_seconds_50pct
  , cef.non_creator_play_duration_seconds_90pct
  , cef.n_creator_likes
  , cef.n_creator_verified_likes
  , cef.creator_has_undo_like
  , cef.creator_net_pin_clip
  , cef.creator_has_unpin
  , cef.n_creator_shares
  , cef.did_owner_add_to_playlist
  , cef.n_playlists_added_by_owner
  , cef.n_non_creator_likes
  , cef.n_non_creator_verified_likes
  , cef.n_non_creator_shares
  , cef.n_unique_non_creator_users_shared
  , cef.n_non_creator_added_to_playlist
  , cef.n_playlists_added_by_non_owner

  -- Comment count data
  , iccc.n_comments
  , iccc.n_non_reply_comments

FROM
  {{ ref('dim_clip') }} icdc
LEFT JOIN
  {{ ref('stg_aggregated__agg_user_daily') }} aud
  ON icdc.user_id = aud.user_id
  AND icdc.p_date = aud.p_date
LEFT JOIN
  {{ ref('stg_cleaned__fact_clip_status') }} fcs
  ON icdc.clip_id = fcs.clip_id
LEFT JOIN
  {{ ref('clip_initial_metrics') }} cim
  ON icdc.clip_id = cim.clip_id
LEFT JOIN
  {{ ref('clip_engagement_features') }} cef
  ON icdc.clip_id = cef.clip_id
LEFT JOIN {{ ref('int_tag_upsample_used') }} itu
  ON icdc.clip_id = itu.clip_id
LEFT JOIN {{ ref('int_clip_comment_count') }} iccc
  ON icdc.clip_id = iccc.clip_id
WHERE 1=1
  AND icdc.p_date BETWEEN '2025-04-01' AND cast(current_date() - interval '1 day' as varchar) -- Give 1 day for data to settle
  AND fcs.is_public
  AND NOT fcs.is_deleted
