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

WITH subscription_spans AS (
    SELECT *
    FROM {{ ref('int_subscription_span') }}
)

SELECT
    ss.user_id
    , u.user_uid 
    , ss.first_subscription_ts
    , ss.last_active_ts
    , ss.last_period_canceled_at_ts
    , ss.months_active
    , ss.subscribed_on_platform
    , ss.count_separate_subscriptions
    , ss.initial_subscription_plan_id
    , ss.final_subscription_plan_id
    , ss.initial_renewal_interval
    , ss.final_renewal_interval
    , ss.is_user_first_subscription_span

    --- plan details TODO: this doesn't work right now with new plans
    -- , sp_intitial.plan_id_rds as initial_usage_plan_id
    -- , sp_initial.plan_level as initial_usage_plan_level
    -- , sp_final.plan_id_rds as final_usage_plan_id
    -- , sp_final.plan_level as final_usage_plan_level

    -- approx revenue details: 
    -- , CASE WHEN sp_initial.renewal_interval = 'month' THEN sp_initial.price_usd 
    --     WHEN sp_initial.renewal_interval = 'year' THEN sp_initial.price_usd / 12
    --     END AS initial_monthly_revenue_usd
    -- , CASE WHEN sp_final.renewal_interval = 'month' THEN sp_final.price_usd 
    --     WHEN sp_final.renewal_interval = 'year' THEN sp_final.price_usd / 12
    --     END AS final_monthly_revenue_usd

    -- user details
    , coalesce(u.recent_country, 'unknown') AS country 
    , coalesce(u.registered_platform, 'unknown') AS registered_platform
    , coalesce(c.region, 'unknown') AS region
    , coalesce(bh.meets_bot_definition, FALSE) AS meets_bot_definition
    
FROM subscription_spans ss
LEFT JOIN {{ ref('stg_cleaned__dim_subscription_plan') }} sp_initial
    ON ss.initial_subscription_plan_id = sp_initial.id
LEFT JOIN {{ ref('stg_cleaned__dim_subscription_plan') }} sp_final
    ON ss.final_subscription_plan_id = sp_final.id
LEFT JOIN {{ ref('stg_cleaned__dim_user') }} u
    ON ss.user_id = u.user_id
LEFT JOIN {{ ref('stg_cleaned__dim_country') }} c
    ON lower(u.recent_country) = lower(c.country_name)
LEFT JOIN {{ ref('stg_cleaned__bot_hourly') }} bh
    ON ss.user_id = bh.user_id
