{{
  config(
    materialized = 'incremental',
    incremental_strategy = 'delete+insert',
    unique_key = ['user_id', 'user_uid']
  )
}}

{% if is_incremental() %}
WITH max_dates AS (
    SELECT 
        MAX(period_start_ts_utc) - INTERVAL '7 days' AS max_start_cutoff,
        MAX(period_end_ts_utc) - INTERVAL '7 days' AS max_end_cutoff
    FROM {{ ref('stg_cleaned__fact_subscription_period') }}
),
users_with_recent_activity AS (
    SELECT DISTINCT
        fsp.user_id
    FROM {{ ref('stg_cleaned__fact_subscription_period') }} fsp
    CROSS JOIN max_dates md
    WHERE (
        fsp.period_start_ts_utc >= md.max_start_cutoff
        OR
        fsp.period_end_ts_utc >= md.max_end_cutoff
    )
)
{% endif %}

SELECT
    fsp.user_id
    , fsp.user_uid
    , MIN(fsp.period_start_ts_utc) AS first_subscription_start_ts
    , MAX(fsp.period_end_ts_utc) AS last_subscription_end_ts
    , MIN_BY(fsp.subscribed_on_platform, fsp.period_start_ts_utc) AS first_subscription_platform
    , MIN_BY(fsp.billing_provider, fsp.period_start_ts_utc) AS first_subscription_billing_provider
    , MIN_BY(fsp.tier, fsp.period_start_ts_utc) AS first_subscription_tier
    , MIN_BY(fsp.renewal_interval, fsp.period_start_ts_utc) AS first_subscription_renewal_interval
    , MAX_BY(fsp.subscribed_on_platform, fsp.period_end_ts_utc) AS last_subscription_platform
    , MAX_BY(fsp.billing_provider, fsp.period_end_ts_utc) AS last_subscription_billing_provider
    , MAX_BY(fsp.tier, fsp.period_end_ts_utc) AS last_subscription_tier
    , MAX_BY(fsp.renewal_interval, fsp.period_end_ts_utc) AS last_subscription_renewal_interval
    , SUM(fsp.period_length_days) AS total_subscription_days
    , SUM(IFF(fsp.tier IN ('Pro', 'Pro v2'), fsp.period_length_days, 0)) AS total_subscription_days_pro
    , SUM(IFF(fsp.tier IN ('Premier'), fsp.period_length_days, 0)) AS total_subscription_days_premier
    , COUNT(DISTINCT fsp.provider_original_transaction_id) AS n_distinct_subscriptions
    , BOOLOR_AGG(fsp.renewal_interval = 'year') AS has_had_annual_subscription
FROM
    {{ ref('stg_cleaned__fact_subscription_period') }} fsp
{% if is_incremental() %}
INNER JOIN users_with_recent_activity ura
    ON fsp.user_id = ura.user_id
{% endif %}
GROUP BY
    1, 2
