{%- 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'],
        cluster_by=['p_date'],
        snowflake_warehouse='SUNO_PROD_MEDIUM',
    )
}}

WITH

segment_active_users_daily AS (
    SELECT
        user_uid
        , p_date
        , n_events AS n_events_web
        , 0 AS n_events_ios
        , 0 AS n_events_android
    FROM
        {{ ref('int_active_users_daily_web_user_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , n_events AS n_events_web
        , 0 AS n_events_ios
        , 0 AS n_events_android
    FROM
        {{ ref('int_active_users_daily_web_page_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , n_events AS n_events_web
        , 0 AS n_events_ios
        , 0 AS n_events_android
    FROM
        {{ ref('int_active_users_daily_web_hook_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , n_events AS n_events_web
        , 0 AS n_events_ios
        , 0 AS n_events_android
    FROM
        {{ ref('int_active_users_daily_web_audio_player_actions') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , n_events AS n_events_web
        , 0 AS n_events_ios
        , 0 AS n_events_android
    FROM
        {{ ref('int_active_users_daily_web_audio_actions') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , 0 AS n_events_web
        , n_events_ios
        , n_events_android
    FROM
        {{ ref('int_active_users_daily_app_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_uid
        , p_date
        , 0 AS n_events_web
        , n_events_ios
        , n_events_android
    FROM
        {{ ref('int_active_users_daily_app_audio_actions') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}
)

, backend_active_users_daily AS (
    SELECT
        user_id
        , p_date
        , n_events_web
        , n_events_ios
        , n_events_android
    FROM
        {{ ref('int_active_users_daily_bk_realtime_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}

    UNION ALL

    SELECT
        user_id
        , p_date
        , n_events_web
        , n_events_ios
        , n_events_android
    FROM
        {{ ref('int_active_users_daily_bk_nonrealtime_event') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}
)

, session_active_users_daily AS (
    SELECT
        user_id
        , p_date
        , n_sessions_web
        , n_sessions_ios
        , n_sessions_android
    FROM
        {{ ref('int_active_users_daily_user_session_state') }}
    WHERE 1=1
        AND {{ partition_filter_daily('p_date') }}
)

, segment_users_daily_with_user_id AS (
    SELECT
        du.user_id
        , s.user_uid
        , s.p_date
        , SUM(s.n_events_web + s.n_events_ios + s.n_events_android) AS n_events
        , SUM(s.n_events_web) AS n_events_web
        , SUM(s.n_events_ios) AS n_events_ios
        , SUM(s.n_events_android) AS n_events_android
    FROM
        segment_active_users_daily s
    LEFT JOIN
        {{ source('snowflake', 'dim_user') }} AS du
        ON s.user_uid = du.user_uid
    WHERE 1=1
        AND du.user_id IS NOT NULL
    GROUP BY
        1, 2, 3
)

, backend_users_daily_with_user_uid AS (
    SELECT
        b.user_id
        , du.user_uid
        , b.p_date
        , SUM(b.n_events_web + b.n_events_ios + b.n_events_android) AS n_events
        , SUM(b.n_events_web) AS n_events_web
        , SUM(b.n_events_ios) AS n_events_ios
        , SUM(b.n_events_android) AS n_events_android
    FROM
        backend_active_users_daily b
    LEFT JOIN
        {{ source('snowflake', 'dim_user') }} AS du
        ON b.user_id = du.user_id
    WHERE 1=1
        AND du.user_id IS NOT NULL
    GROUP BY
        1, 2, 3
)

, session_users_daily_with_user_uid AS (
    SELECT
        s.user_id
        , du.user_uid
        , s.p_date
        , s.n_sessions_web
        , s.n_sessions_ios
        , s.n_sessions_android
    FROM
        session_active_users_daily s
    LEFT JOIN
        {{ source('snowflake', 'dim_user') }} AS du
        ON s.user_id = du.user_id
    WHERE 1=1
        AND du.user_uid IS NOT NULL
)

, combined_users_daily AS (
    SELECT
        COALESCE(su.user_id, bu.user_id, se.user_id) AS user_id
        , COALESCE(su.user_uid, bu.user_uid, se.user_uid) AS user_uid
        , COALESCE(su.p_date, bu.p_date, se.p_date) AS p_date
        , COALESCE(se.n_sessions_web, 0) AS n_sessions_web
        , COALESCE(se.n_sessions_ios, 0) AS n_sessions_ios
        , COALESCE(se.n_sessions_android, 0) AS n_sessions_android
        , COALESCE(su.n_events_web, 0) AS n_frontend_events_web
        , COALESCE(su.n_events_ios, 0) AS n_frontend_events_ios
        , COALESCE(su.n_events_android, 0) AS n_frontend_events_android
        , COALESCE(bu.n_events_web, 0) AS n_backend_events_web
        , COALESCE(bu.n_events_ios, 0) AS n_backend_events_ios
        , COALESCE(bu.n_events_android, 0) AS n_backend_events_android
    FROM segment_users_daily_with_user_id su
    FULL OUTER JOIN
        backend_users_daily_with_user_uid bu
        ON su.user_id = bu.user_id
        AND su.user_uid = bu.user_uid
        AND su.p_date = bu.p_date
    FULL OUTER JOIN
        session_users_daily_with_user_uid se
        ON se.user_id = COALESCE(su.user_id, bu.user_id)
        AND se.user_uid = COALESCE(su.user_uid, bu.user_uid)
        AND se.p_date = COALESCE(su.p_date, bu.p_date)
)

SELECT
    user_id
    , user_uid
    , p_date
    , n_sessions_web
    , n_sessions_ios
    , n_sessions_android
    , n_frontend_events_web
    , n_frontend_events_ios
    , n_frontend_events_android
    , n_backend_events_web
    , n_backend_events_ios
    , n_backend_events_android
    , n_sessions_web > 0 OR n_frontend_events_web > 0 OR n_backend_events_web > 0 AS is_active_web
    , n_sessions_ios > 0 OR n_frontend_events_ios > 0 OR n_backend_events_ios > 0 AS is_active_ios
    , n_sessions_android > 0 OR n_frontend_events_android > 0 OR n_backend_events_android > 0 AS is_active_android
    , is_active_web OR is_active_ios OR is_active_android AS is_active
FROM
    combined_users_daily
