{%- set incremental_predicate = partition_filter_daily('DBT_INTERNAL_DEST.p_date') -%}
{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        incremental_predicates=[incremental_predicate],
        unique_key=['user_id', 'p_date'],
        snowflake_warehouse='FACT_PLAY_BACKFILL_SMALL',
        cluster_by=['p_date']
    )
}}

-- Aggregate platform-level data into cross-platform daily metrics
SELECT
    user_id
    , user_uid
    , subscription_tier

    -- Aggregated clip metrics (sum across platforms)
    , SUM(clip_total_count) AS clip_total_count
    , SUM(gen_clip_total_count) AS gen_clip_total_count

    -- Aggregated play metrics (sum across platforms)
    , SUM(play_count) AS play_count
    , SUM(play_duration_seconds) AS play_duration_seconds
    , SUM(unique_play_duration_seconds) AS unique_play_duration_seconds

    -- Aggregated play breakdown
    , OBJECT_CONSTRUCT(
        'own', OBJECT_CONSTRUCT(
            'count', SUM(play_breakdown:own:count::INT)
            , 'duration_seconds', SUM(play_breakdown:own:duration_seconds::FLOAT)
            , 'unique_duration_seconds', SUM(play_breakdown:own:unique_duration_seconds::FLOAT)
        )
        , 'others', OBJECT_CONSTRUCT(
            'count', SUM(play_breakdown:others:count::INT)
            , 'duration_seconds', SUM(play_breakdown:others:duration_seconds::FLOAT)
            , 'unique_duration_seconds', SUM(play_breakdown:others:unique_duration_seconds::FLOAT)
        )
    ) AS play_breakdown

    -- Aggregated hook metrics (sum across platforms)
    , SUM(hook_create_count) AS hook_create_count
    , SUM(hook_play_count) AS hook_play_count
    , SUM(hook_play_duration_seconds) AS hook_play_duration_seconds
    , SUM(hook_unique_play_duration_seconds) AS hook_unique_play_duration_seconds

    -- Aggregated hook play breakdown
    , OBJECT_CONSTRUCT(
        'own', OBJECT_CONSTRUCT(
            'count', SUM(hook_play_breakdown:own:count::INT)
            , 'duration_seconds', SUM(hook_play_breakdown:own:duration_seconds::FLOAT)
            , 'unique_duration_seconds', SUM(hook_play_breakdown:own:unique_duration_seconds::FLOAT)
        )
        , 'others', OBJECT_CONSTRUCT(
            'count', SUM(hook_play_breakdown:others:count::INT)
            , 'duration_seconds', SUM(hook_play_breakdown:others:duration_seconds::FLOAT)
            , 'unique_duration_seconds', SUM(hook_play_breakdown:others:unique_duration_seconds::FLOAT)
        )
    ) AS hook_play_breakdown

    -- High intent listening (true if ANY platform had high intent)
    , OBJECT_CONSTRUCT(
        'v1', BOOLOR_AGG(is_high_intent_listening:v1::BOOLEAN)
        , 'v2', BOOLOR_AGG(is_high_intent_listening:v2::BOOLEAN)
    ) AS is_high_intent_listening

    -- Aggregated high intent listening breakdown
    , OBJECT_CONSTRUCT(
        'v2', OBJECT_CONSTRUCT(
            'clips', OBJECT_CONSTRUCT(
                'own', OBJECT_CONSTRUCT(
                    'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:clips:own:play_duration_seconds::FLOAT)
                    , 'play_count', SUM(high_intent_listening_breakdown:v2:clips:own:play_count::INT)
                )
                , 'others', OBJECT_CONSTRUCT(
                    'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:clips:others:play_duration_seconds::FLOAT)
                    , 'play_count', SUM(high_intent_listening_breakdown:v2:clips:others:play_count::INT)
                )
            )
            , 'hooks', OBJECT_CONSTRUCT(
                'own', OBJECT_CONSTRUCT(
                    'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:hooks:own:play_duration_seconds::FLOAT)
                    , 'play_count', SUM(high_intent_listening_breakdown:v2:hooks:own:play_count::INT)
                )
                , 'others', OBJECT_CONSTRUCT(
                    'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:hooks:others:play_duration_seconds::FLOAT)
                    , 'play_count', SUM(high_intent_listening_breakdown:v2:hooks:others:play_count::INT)
                )
            )
        )
    ) AS high_intent_listening_breakdown

    -- Activity flag (true if active on ANY platform)
    , MAX(is_active) AS is_active

    , OBJECT_CONSTRUCT(
        'clip_create', MAX(engagement_flags:clip_create::BOOLEAN),
        'clip_listen', MAX(engagement_flags:clip_listen::BOOLEAN),
        'clip_listen_own', MAX(engagement_flags:clip_listen_own::BOOLEAN),
        'clip_listen_others', MAX(engagement_flags:clip_listen_others::BOOLEAN),
        'hook_create', MAX(engagement_flags:hook_create::BOOLEAN),
        'hook_listen', MAX(engagement_flags:hook_listen::BOOLEAN),
        'hook_listen_own', MAX(engagement_flags:hook_listen_own::BOOLEAN),
        'hook_listen_others', MAX(engagement_flags:hook_listen_others::BOOLEAN)
    ) AS engagement_flags

    -- Platform-agnostic follow counts and net likes
    , MAX(total_following_cnt) AS total_following_cnt
    , MAX(total_follower_cnt) AS total_follower_cnt
    , MAX(net_likes_received_count) AS net_likes_received_count

    -- Historical activity (aggregate across platforms)
    , OBJECT_CONSTRUCT(
        'prev_day', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:prev_day:is_active::BOOLEAN)
            , 'is_clip_create', MAX(historical_activity:prev_day:is_clip_create::BOOLEAN)
            , 'is_clip_listen', MAX(historical_activity:prev_day:is_clip_listen::BOOLEAN)
            , 'is_clip_listen_own', MAX(historical_activity:prev_day:is_clip_listen_own::BOOLEAN)
            , 'is_clip_listen_others', MAX(historical_activity:prev_day:is_clip_listen_others::BOOLEAN)
            , 'is_hook_create', MAX(historical_activity:prev_day:is_hook_create::BOOLEAN)
            , 'is_hook_listen', MAX(historical_activity:prev_day:is_hook_listen::BOOLEAN)
            , 'is_hook_listen_own', MAX(historical_activity:prev_day:is_hook_listen_own::BOOLEAN)
            , 'is_hook_listen_others', MAX(historical_activity:prev_day:is_hook_listen_others::BOOLEAN)
        )
        , '7d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:"7d":is_active::BOOLEAN)
            , 'is_clip_create', MAX(historical_activity:"7d":is_clip_create::BOOLEAN)
            , 'is_clip_listen', MAX(historical_activity:"7d":is_clip_listen::BOOLEAN)
            , 'is_clip_listen_own', MAX(historical_activity:"7d":is_clip_listen_own::BOOLEAN)
            , 'is_clip_listen_others', MAX(historical_activity:"7d":is_clip_listen_others::BOOLEAN)
            , 'is_hook_create', MAX(historical_activity:"7d":is_hook_create::BOOLEAN)
            , 'is_hook_listen', MAX(historical_activity:"7d":is_hook_listen::BOOLEAN)
            , 'is_hook_listen_own', MAX(historical_activity:"7d":is_hook_listen_own::BOOLEAN)
            , 'is_hook_listen_others', MAX(historical_activity:"7d":is_hook_listen_others::BOOLEAN)
        )
        , 'prev_7d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:prev_7d:is_active::BOOLEAN)
            , 'is_clip_create', MAX(historical_activity:prev_7d:is_clip_create::BOOLEAN)
            , 'is_clip_listen', MAX(historical_activity:prev_7d:is_clip_listen::BOOLEAN)
            , 'is_clip_listen_own', MAX(historical_activity:prev_7d:is_clip_listen_own::BOOLEAN)
            , 'is_clip_listen_others', MAX(historical_activity:prev_7d:is_clip_listen_others::BOOLEAN)
            , 'is_hook_create', MAX(historical_activity:prev_7d:is_hook_create::BOOLEAN)
            , 'is_hook_listen', MAX(historical_activity:prev_7d:is_hook_listen::BOOLEAN)
            , 'is_hook_listen_own', MAX(historical_activity:prev_7d:is_hook_listen_own::BOOLEAN)
            , 'is_hook_listen_others', MAX(historical_activity:prev_7d:is_hook_listen_others::BOOLEAN)
        )
        , '28d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:"28d":is_active::BOOLEAN)
            , 'is_clip_create', MAX(historical_activity:"28d":is_clip_create::BOOLEAN)
            , 'is_clip_listen', MAX(historical_activity:"28d":is_clip_listen::BOOLEAN)
            , 'is_clip_listen_own', MAX(historical_activity:"28d":is_clip_listen_own::BOOLEAN)
            , 'is_clip_listen_others', MAX(historical_activity:"28d":is_clip_listen_others::BOOLEAN)
            , 'is_hook_create', MAX(historical_activity:"28d":is_hook_create::BOOLEAN)
            , 'is_hook_listen', MAX(historical_activity:"28d":is_hook_listen::BOOLEAN)
            , 'is_hook_listen_own', MAX(historical_activity:"28d":is_hook_listen_own::BOOLEAN)
            , 'is_hook_listen_others', MAX(historical_activity:"28d":is_hook_listen_others::BOOLEAN)
        )
    ) AS historical_activity

    , p_date
    , days_since_user_joined

FROM {{ ref('agg_user_platform_daily_spine') }}
WHERE {{ partition_filter_daily('p_date') }}
GROUP BY
    user_id, user_uid, subscription_tier, p_date, days_since_user_joined
