CREATE TRANSIENT TABLE SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN (
    -- Subscription plan information
    id VARCHAR NOT NULL PRIMARY KEY COMMENT 'Unique identifier for the subscription plan. MD5 hash of the RDS plan ID, RDS plan name, and billing renewal interval (or "none" if the plan is not a subscription)'
    , tier VARCHAR NOT NULL COMMENT 'Subscription plan tier (Basic, Pro, Premium, Student Pro)'
    , renewal_interval VARCHAR COMMENT 'Billing renewal interval (month, year, or NULL if the plan is not a subscription)'
    , credits INTEGER NOT NULL COMMENT 'Number of credits per credits reset interval'
    , credits_reset_interval VARCHAR NOT NULL COMMENT 'How often the credits reset (day, month)'
    , plan_id_rds VARCHAR NOT NULL COMMENT 'Original ID from the RDS table `bots_usageplan`'
    , plan_key_rds VARCHAR NOT NULL COMMENT 'Plan key from the RDS table `bots_usageplan`'
    , plan_name_rds VARCHAR NOT NULL COMMENT 'Original name from the RDS table `bots_usageplan`'
    , plan_level INTEGER NOT NULL COMMENT 'Subscription plan level; currently equivalent to the monthly subscription price (USD)'
    , price_usd DECIMAL(10,2) NOT NULL COMMENT 'Monthly subscription price (USD)'
    , stripe_product_id VARCHAR COMMENT 'Stripe product ID'
    , stripe_price_id VARCHAR COMMENT 'Stripe price ID'
    , revcat_product_identifier_ios VARCHAR COMMENT 'RevenueCat product identifier for iOS'
    , revcat_product_identifier_android VARCHAR COMMENT 'RevenueCat product identifier for Android'
    , created_at_rds TIMESTAMP_NTZ NOT NULL COMMENT 'Date and time when the subscription plan was created in RDS'
    , updated_at_rds TIMESTAMP_NTZ NOT NULL COMMENT 'Date and time when the subscription plan was last updated in RDS'
)
COMMENT = 'Each row is a unique combination of subscription tier and renewal interval. Contains information about the subscription plan, as well as the Stripe and RevenueCat product IDs.';
