CREATE TRANSIENT TABLE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD (
    -- Primary key and subscription identifiers
    id VARCHAR NOT NULL PRIMARY KEY UNIQUE COMMENT 'Unique ID for this subscription period. MD5 hash of billing provider name, provider customer ID, and provider invoice/transaction ID.'
    , user_id INTEGER COMMENT 'User ID (can be null for deleted users)'
    , user_uid VARCHAR COMMENT 'User UID (can be null for deleted users)'
    , subscription_plan_id VARCHAR FOREIGN KEY REFERENCES SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN(id) COMMENT 'Subscription plan ID referencing dim_subscription_plan table'

    -- Subscription provider information
    , billing_provider VARCHAR COMMENT 'Name of billing provider (stripe, revcat). Stripe for web, Revcat for iOS and Android'
    , subscribed_on_platform VARCHAR COMMENT 'Platform on which user subscribed for this period (web, ios, android). For iOS and Android, sometimes the user subscribes on one platform but the billing platform switches to another (e.g. user subscribes on iOS, then subscribes on Android)'
    , provider_subscription_id VARCHAR COMMENT 'Stripe subscription ID or Revcat transaction ID. For Stripe, the subscription ID stays the same across periods, but for Revcat, the transaction ID changes with each renewal.'
    , provider_customer_id VARCHAR COMMENT 'Stripe customer ID or Revcat app user ID (which is sometimes the Suno user UID)'
    , provider_transaction_id VARCHAR COMMENT 'Stripe invoice ID or Revcat transaction ID'
    , provider_original_transaction_id VARCHAR COMMENT 'For Stripe renewals, the original invoice ID. For Revcat renewals, the original transaction ID'

    -- Subscription information
    , tier VARCHAR COMMENT 'Subscription tier (Pro, Premium, Student Pro)'
    , renewal_interval VARCHAR COMMENT 'Renewal interval (month, year)'
    , provider_product_identifier VARCHAR COMMENT 'Stripe price nickname or RevenueCat product identifier'
    , period_origin_type VARCHAR COMMENT 'How this period was created (new, auto-renewal, upgrade, downgrade)'
    , period_start_ts_utc TIMESTAMP_NTZ(9) COMMENT 'Start time of subscription period'
    , period_end_ts_utc TIMESTAMP_NTZ(9) COMMENT 'End time of subscription period'
    , original_subscription_purchase_ts_utc TIMESTAMP_NTZ(9) COMMENT 'When user originally started this subscription, i.e. MIN(period_start_ts_utc) OVER (PARTITION BY provider_original_transaction_id)'
    , canceled_at_ts_utc TIMESTAMP_NTZ(9) COMMENT 'When subscription was canceled, if applicable'

    -- Analytics information
    , billing_cycle_number INTEGER COMMENT 'Billing cycle number within this subscription period. 1 for new subscription, increments with each subsequent change. A mid-cycle subscription update will increment the billing cycle number.'
    , is_user_first_subscription_period BOOLEAN COMMENT 'Whether this is the users first subscription period'
    , is_trial BOOLEAN COMMENT 'Whether this is a trial period offered through the subscription provider'
    , is_intro_offer BOOLEAN COMMENT 'Whether this subscription used intro pricing offered through the subscription provider'
    , is_promotional_price BOOLEAN COMMENT 'Whether this subscription used promotional pricing offered through the subscription provider'
)
COMMENT = 'One row for each subscription period. Periods can be created by new subscription, auto-renewal, upgrade, or downgrade.';
