{%- 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
    , days_since_user_joined

    -- Aggregated listen 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 create metrics (sum across platforms)
    , SUM(hook_create_count) AS hook_create_count

    -- Activity flags (true if active on ANY platform)
    , MAX(is_active) AS is_active
    , MAX(is_active_create) AS is_active_create
    , MAX(is_active_listen) AS is_active_listen
    , MAX(is_active_listen_own) AS is_active_listen_own
    , MAX(is_active_listen_others) AS is_active_listen_others

    -- 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

    -- Historical activity (aggregate across platforms - true if ANY platform was active)
    , OBJECT_CONSTRUCT(
        'prev_day', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:prev_day:is_active::BOOLEAN)
            , 'is_active_create', MAX(historical_activity:prev_day:is_active_create::BOOLEAN)
            , 'is_active_listen', MAX(historical_activity:prev_day:is_active_listen::BOOLEAN)
            , 'is_active_listen_own', MAX(historical_activity:prev_day:is_active_listen_own::BOOLEAN)
            , 'is_active_listen_others', MAX(historical_activity:prev_day:is_active_listen_others::BOOLEAN)
        )
        , '7d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:"7d":is_active::BOOLEAN)
            , 'is_active_create', MAX(historical_activity:"7d":is_active_create::BOOLEAN)
            , 'is_active_listen', MAX(historical_activity:"7d":is_active_listen::BOOLEAN)
            , 'is_active_listen_own', MAX(historical_activity:"7d":is_active_listen_own::BOOLEAN)
            , 'is_active_listen_others', MAX(historical_activity:"7d":is_active_listen_others::BOOLEAN)
        )
        , 'prev_7d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:prev_7d:is_active::BOOLEAN)
            , 'is_active_create', MAX(historical_activity:prev_7d:is_active_create::BOOLEAN)
            , 'is_active_listen', MAX(historical_activity:prev_7d:is_active_listen::BOOLEAN)
            , 'is_active_listen_own', MAX(historical_activity:prev_7d:is_active_listen_own::BOOLEAN)
            , 'is_active_listen_others', MAX(historical_activity:prev_7d:is_active_listen_others::BOOLEAN)
        )
        , '28d', OBJECT_CONSTRUCT(
            'is_active', MAX(historical_activity:"28d":is_active::BOOLEAN)
            , 'is_active_create', MAX(historical_activity:"28d":is_active_create::BOOLEAN)
            , 'is_active_listen', MAX(historical_activity:"28d":is_active_listen::BOOLEAN)
            , 'is_active_listen_own', MAX(historical_activity:"28d":is_active_listen_own::BOOLEAN)
            , 'is_active_listen_others', MAX(historical_activity:"28d":is_active_listen_others::BOOLEAN)
        )
    ) AS historical_activity

    , 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
    , OBJECT_CONSTRUCT(
        'v1', OBJECT_CONSTRUCT(
            'play_duration_seconds', SUM(CASE WHEN is_high_intent_listening:v1::BOOLEAN THEN play_duration_seconds ELSE 0 END)
            , 'play_count', COUNT(CASE WHEN is_high_intent_listening:v1::BOOLEAN THEN 1 ELSE NULL END)
        )
        , 'v2', OBJECT_CONSTRUCT(
            'play_duration_seconds', SUM(CASE WHEN is_high_intent_listening:v2::BOOLEAN THEN play_duration_seconds ELSE 0 END)
            , 'play_count', COUNT(CASE WHEN is_high_intent_listening:v2::BOOLEAN THEN 1 ELSE NULL END)
            , 'own', OBJECT_CONSTRUCT(
                'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:own:play_duration_seconds::FLOAT)
                , 'play_count', SUM(high_intent_listening_breakdown:v2:own:play_count::INT)
            )
            , 'others', OBJECT_CONSTRUCT(
                'play_duration_seconds', SUM(high_intent_listening_breakdown:v2:others:play_duration_seconds::FLOAT)
                , 'play_count', SUM(high_intent_listening_breakdown:v2:others:play_count::INT)
            )
        )
    ) AS high_intent_listening_breakdown

    , p_date

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