{%- 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']
    )
}}

-- Daily snapshot of user subscription tiers from rds_discord_info
-- This model captures the current state each day and accumulates history
-- IMPORTANT: rds_discord_info gets updated hourly (2 minutes past each hour) with current date data
-- This model runs at 5am UTC and processes TODAY's partition (end_offset: 1)
-- Historical backfill (before 2024-06-01) should be done separately in Snowflake

WITH

-- Get the latest subscription_tier for each user for each day (using the latest hour)
discord_info AS (
    SELECT
        di.user_id,
        di.uid AS user_uid,
        up.name AS subscription_tier,
        DATE(di.p_date) AS p_date,
        ROW_NUMBER() OVER (
            PARTITION BY di.user_id, DATE(di.p_date)
            ORDER BY di.p_hour DESC, di.p_date DESC
        ) AS rn
    FROM {{ source('rds', 'rds_discord_info') }} di
    LEFT JOIN {{ source('rds', 'rds_usage_plan') }} up
        ON di.subscription_plan_id = up.id
    WHERE {{ partition_filter_daily('DATE(di.p_date)') }}
)

-- Select one row per user per day with their subscription tier
SELECT
    user_id,
    user_uid,
    COALESCE(subscription_tier, 'Free Plan') AS subscription_tier,
    p_date
FROM discord_info
WHERE rn = 1

