{{
    config(
        materialized='table'
        , snowflake_warehouse='DBT_DEV_LARGE'
    )
}}

WITH daily_notifications_sent AS (
    SELECT * FROM {{ ref('int_user_notifications_sent_daily') }}
)

, agg_notifications_by_user_category AS (
    SELECT
        sender_user_id
        , notification_category
        , MAX(n_notifications_sent_in_category) AS max_daily_notifications_sent_in_category
        , SUM(n_notifications_sent_in_category) AS total_notifications_sent_in_category
        , COUNT(DISTINCT p_date) AS n_days_notifications_sent_in_category
        , MAX(n_notifications_sent_on_day) AS max_daily_notifications_sent_on_day
    FROM daily_notifications_sent
    GROUP BY 1, 2
)

SELECT
    sender_user_id
    , MAX(max_daily_notifications_sent_on_day) AS max_daily_notifications_sent_on_day
    , MAX(CASE WHEN notification_category = 'clip_like' THEN max_daily_notifications_sent_in_category END) AS max_daily_notifications_sent_clip_like
    , MAX(CASE WHEN notification_category = 'clip_like' THEN n_days_notifications_sent_in_category END) AS n_days_notifications_sent_clip_like
    , MAX(CASE WHEN notification_category = 'clip_like' THEN total_notifications_sent_in_category END) AS total_notifications_sent_clip_like
    , MAX(CASE WHEN notification_category = 'follow' THEN max_daily_notifications_sent_in_category END) AS max_daily_notifications_sent_follow
    , MAX(CASE WHEN notification_category = 'follow' THEN n_days_notifications_sent_in_category END) AS n_days_notifications_sent_follow
    , MAX(CASE WHEN notification_category = 'follow' THEN total_notifications_sent_in_category END) AS total_notifications_sent_follow
    , MAX(CASE WHEN notification_category = 'comment_related' THEN max_daily_notifications_sent_in_category END) AS max_daily_notifications_sent_comment_related
    , MAX(CASE WHEN notification_category = 'comment_related' THEN n_days_notifications_sent_in_category END) AS n_days_notifications_sent_comment_related
    , MAX(CASE WHEN notification_category = 'comment_related' THEN total_notifications_sent_in_category END) AS total_notifications_sent_comment_related
    , MAX(CASE WHEN notification_category = 'other' THEN max_daily_notifications_sent_in_category END) AS max_daily_notifications_sent_other
    , MAX(CASE WHEN notification_category = 'other' THEN n_days_notifications_sent_in_category END) AS n_days_notifications_sent_other

FROM
    agg_notifications_by_user_category
GROUP BY
    1
