{{ 
  config(
    materialized = 'table'
  ) 
}}

WITH individual_subscriptions AS (
    -- First get individual subscription periods with tier information
    SELECT 
        user_id
        , provider_original_transaction_id
        , subscribed_on_platform
        , MIN(original_subscription_purchase_ts_utc) AS first_subscription_ts
        , MAX(period_end_ts_utc) AS last_active_ts
        , MAX(canceled_at_ts_utc) AS canceled_at_ts
        -- Get the subscription plan and renewal interval for this specific transaction ID
        , MIN(subscription_plan_id) AS subscription_plan_id
        , MIN(renewal_interval) AS renewal_interval
        , MIN(is_user_first_subscription_period) AS is_user_first_subscription_period
    FROM {{ ref('stg_cleaned__fact_subscription_period') }}
    WHERE user_id IS NOT NULL
    GROUP BY 
        1, 2, 3
),

-- First, order all subscriptions by start time for each user
ordered_subscriptions AS (
    SELECT 
        user_id
        , first_subscription_ts
        , last_active_ts
        , canceled_at_ts
        , subscription_plan_id
        , renewal_interval
        , is_user_first_subscription_period
        , subscribed_on_platform
        , LAG(last_active_ts) OVER (
            PARTITION BY user_id 
            ORDER BY first_subscription_ts
          ) AS prev_subscription_end
    FROM individual_subscriptions
),

-- Calculate if each subscription should start a new group or continue an existing one
subscription_transitions AS (
    SELECT 
        user_id
        , first_subscription_ts
        , last_active_ts
        , canceled_at_ts
        , subscription_plan_id
        , renewal_interval
        , is_user_first_subscription_period
        , subscribed_on_platform
        , prev_subscription_end
        , CASE 
            WHEN prev_subscription_end IS NULL THEN 1 -- First subscription starts a new group
            WHEN DATEDIFF("minute", prev_subscription_end, first_subscription_ts) <= 15 THEN 0 -- Continue existing group
            ELSE 1 -- Start a new group
          END AS starts_new_group
    FROM ordered_subscriptions
),

-- Assign group numbers by summing the transition indicators
subscription_groups AS (
    SELECT 
        st.*
        , SUM(starts_new_group) OVER (
            PARTITION BY user_id 
            ORDER BY first_subscription_ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          ) AS subscription_group
    FROM subscription_transitions st
),

-- Finally, consolidate the subscription spans for each group
subscription_spans AS (
    SELECT 
        user_id
        , MIN(first_subscription_ts) AS first_subscription_ts
        , MAX(last_active_ts) AS last_active_ts
        , MIN(CASE WHEN rn_desc = 1 THEN canceled_at_ts END) AS last_period_canceled_at_ts -- canceled at ts of the last subscription period 
        , DATEDIFF('month', MIN(first_subscription_ts), MAX(last_active_ts)) AS months_active
        , subscribed_on_platform
        , COUNT(DISTINCT first_subscription_ts) AS count_separate_subscriptions
        , MIN(CASE WHEN rn = 1 THEN is_user_first_subscription_period END) AS is_user_first_subscription_span
        , MIN(CASE WHEN rn = 1 THEN subscription_plan_id END) AS initial_subscription_plan_id
        , MIN(CASE WHEN rn_desc = 1 THEN subscription_plan_id END) AS final_subscription_plan_id
        , MIN(CASE WHEN rn = 1 THEN renewal_interval END) AS initial_renewal_interval
        , MIN(CASE WHEN rn_desc = 1 THEN renewal_interval END) AS final_renewal_interval
    FROM (
        SELECT 
            sg.*
            , ROW_NUMBER() OVER (PARTITION BY user_id, subscription_group ORDER BY first_subscription_ts ASC) AS rn
            , ROW_NUMBER() OVER (PARTITION BY user_id, subscription_group ORDER BY first_subscription_ts DESC) AS rn_desc
        FROM subscription_groups sg
    )
    GROUP BY user_id, subscribed_on_platform, subscription_group
)

SELECT * FROM subscription_spans

