-- Get all new subscriptions from Stripe, i.e. new == subscriptions that are not renewals or upgrades/downgrades.
-- This is done by looking at invoices with billing_reason = 'subscription_create' and paid = true.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_NEW_SUBSCRIPTIONS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH

            -- New subscriptions have only one invoice line item (the subscription item)
            stripe_new_subscriptions AS (
                SELECT
                    i.id AS invoice_id
                    , i.customer_id
                    , i.date AS invoice_ts
                    , i.billing_reason
                    , i.subscription_id
                    , ili.id AS invoice_line_item_id
                    , ili.price_id
                    , i.amount_due AS invoice_amount_due
                    , i.amount_paid AS invoice_amount_paid
                    , ili.period_start
                    , ili.period_end
                    , p.nickname AS price_nickname
                    , pd.name AS product_name
                    , CASE
                        WHEN pd.id = 'prod_SCeP8L1HWMvDEK' THEN 'Basic'
                        WHEN pd.id = 'prod_RQR2Sj8VnkNi6o' THEN 'Student Pro'
                        WHEN pd.id = 'prod_OVhjcxgokoaveE' THEN 'Pro'
                        WHEN pd.id = 'prod_SCeGA4AU6uFQfB' THEN 'Pro v2'
                        WHEN pd.id = 'prod_OnHrSUwoO8VYfg' THEN 'Premier'
                        ELSE pd.name
                    END AS tier
                    , p.recurring_interval
                    , s.created AS subscription_created_ts
                    , s.canceled_at AS subscription_canceled_ts
                    , COUNT(*) OVER (PARTITION BY i.id) AS n_invoice_line_items
                FROM
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoices i
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoice_line_items ili
                    ON ili.invoice_id = i.id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.subscriptions s
                    ON s.id = i.subscription_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.prices p
                    ON p.id = ili.price_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.products pd
                    ON pd.id = p.product_id
                WHERE 1=1
                    AND DATE(i.date) = :p_date
                    AND i.billing_reason = 'subscription_create'
                    AND i.paid
            )

            , dim_user_dedupe AS (
                -- For Stripe, we need to dedupe users by their Stripe customer ID
                -- because sometimes users delete and recreate their Suno account with the same Stripe customer ID
                SELECT * FROM (
                    SELECT
                        du.user_id
                        , du.user_uid
                        , du.stripe_customer_id
                        , udr.persist_user_id IS NOT NULL AS is_user_deleted
                        , udr.deleted_at
                        , ROW_NUMBER() OVER (PARTITION BY du.stripe_customer_id ORDER BY udr.persist_user_id IS NOT NULL ASC) AS rn
                    FROM
                        suno_prod.prod.dim_user du
                    LEFT JOIN
                        suno_prod.prod.rds_user_delete_request udr
                        ON udr.persist_user_id = du.user_id
                )
                WHERE rn = 1
            )

            SELECT
                -- Primary key and subscription identifiers
                MD5_TO_UUID('Stripe' || COALESCE(du.user_id, 0) || subs.customer_id || subs.invoice_id || subs.invoice_line_item_id) AS id
                , du.user_id
                , du.user_uid
                , dsp.id AS subscription_plan_id

                -- Subscription provider information
                , 'Stripe' AS billing_provider
                , 'web' AS subscribed_on_platform
                , subs.subscription_id AS provider_subscription_id
                , subs.customer_id AS provider_customer_id
                , subs.invoice_id AS provider_transaction_id
                , subs.invoice_id AS provider_original_transaction_id -- Because this is a new subscription, this invoice is the original transaction

                -- Subscription information
                , subs.tier
                , subs.recurring_interval AS renewal_interval
                , subs.price_nickname AS provider_product_identifier
                , 'new' AS period_origin_type
                , subs.period_start AS period_start_ts_utc
                , subs.period_end AS period_end_ts_utc
                , subs.subscription_created_ts AS original_subscription_purchase_ts_utc
                , subs.subscription_canceled_ts AS canceled_at_ts_utc

                -- Analytics information
                , 1 AS billing_cycle_number -- New Stripe subscriptions, so set billing cycle number to 1
                , NULL AS is_user_first_subscription_period -- This is set in the `FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD` procedure
                , FALSE AS is_trial
                , FALSE AS is_intro_offer
                , FALSE AS is_promotional_price
            FROM
                stripe_new_subscriptions subs
            LEFT JOIN
                dim_user_dedupe du
                ON du.stripe_customer_id = subs.customer_id
            LEFT JOIN
                suno_prod.prod.dim_subscription_plan dsp
                ON dsp.stripe_price_id = subs.price_id
        ) AS source
        ON fsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                fsp.user_id = source.user_id
                , fsp.user_uid = source.user_uid
                , fsp.subscription_plan_id = source.subscription_plan_id
                , fsp.billing_provider = source.billing_provider
                , fsp.subscribed_on_platform = source.subscribed_on_platform
                , fsp.provider_subscription_id = source.provider_subscription_id
                , fsp.provider_customer_id = source.provider_customer_id
                , fsp.provider_transaction_id = source.provider_transaction_id
                , fsp.provider_original_transaction_id = source.provider_original_transaction_id
                , fsp.tier = source.tier
                , fsp.renewal_interval = source.renewal_interval
                , fsp.provider_product_identifier = source.provider_product_identifier
                , fsp.period_origin_type = source.period_origin_type
                , fsp.period_start_ts_utc = source.period_start_ts_utc
                , fsp.period_end_ts_utc = source.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc = source.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc = source.canceled_at_ts_utc
                , fsp.billing_cycle_number = source.billing_cycle_number
                , fsp.is_user_first_subscription_period = source.is_user_first_subscription_period
                , fsp.is_trial = source.is_trial
                , fsp.is_intro_offer = source.is_intro_offer
                , fsp.is_promotional_price = source.is_promotional_price
        WHEN NOT MATCHED THEN
            INSERT (
                fsp.id
                , fsp.user_id
                , fsp.user_uid
                , fsp.subscription_plan_id
                , fsp.billing_provider
                , fsp.subscribed_on_platform
                , fsp.provider_subscription_id
                , fsp.provider_customer_id
                , fsp.provider_transaction_id
                , fsp.provider_original_transaction_id
                , fsp.tier
                , fsp.renewal_interval
                , fsp.provider_product_identifier
                , fsp.period_origin_type
                , fsp.period_start_ts_utc
                , fsp.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc
                , fsp.billing_cycle_number
                , fsp.is_user_first_subscription_period
                , fsp.is_trial
                , fsp.is_intro_offer
                , fsp.is_promotional_price
            ) VALUES (
                source.id
                , source.user_id
                , source.user_uid
                , source.subscription_plan_id
                , source.billing_provider
                , source.subscribed_on_platform
                , source.provider_subscription_id
                , source.provider_customer_id
                , source.provider_transaction_id
                , source.provider_original_transaction_id
                , source.tier
                , source.renewal_interval
                , source.provider_product_identifier
                , source.period_origin_type
                , source.period_start_ts_utc
                , source.period_end_ts_utc
                , source.original_subscription_purchase_ts_utc
                , source.canceled_at_ts_utc
                , source.billing_cycle_number
                , source.is_user_first_subscription_period
                , source.is_trial
                , source.is_intro_offer
                , source.is_promotional_price
            );
        RETURN 'SUCCESS';
    END;
    $$;


-- Get all renewals from Stripe, i.e. subscriptions that had a previous period.
-- This double-counts subscriptions that have failed payments - once on the failed invoice which is eventually marked as paid,
-- and once on the replacement invoice which is discounted 100% and exists to reset the billing start date.
-- The failed payment invoices are handled in the `FACT_SUBSCRIPTION_PERIOD_DROP_STRIPE_LATE_PAYMENT_INVOICES` procedure.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_RENEWAL_SUBSCRIPTIONS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH

            stripe_renewal_subscriptions AS (
                SELECT
                    i.id AS invoice_id
                    , i.customer_id
                    , i.date AS invoice_ts
                    , i.billing_reason
                    , i.attempt_count
                    , i.subscription_id
                    , ili.id AS invoice_line_item_id
                    , ili.price_id
                    , i.amount_due AS invoice_amount_due
                    , i.amount_paid AS invoice_amount_paid
                    , ili.period_start
                    , ili.period_end
                    , p.nickname AS price_nickname
                    , pd.name AS product_name
                    , CASE
                        WHEN pd.id = 'prod_SCeP8L1HWMvDEK' THEN 'Basic'
                        WHEN pd.id = 'prod_RQR2Sj8VnkNi6o' THEN 'Student Pro'
                        WHEN pd.id = 'prod_OVhjcxgokoaveE' THEN 'Pro'
                        WHEN pd.id = 'prod_SCeGA4AU6uFQfB' THEN 'Pro v2'
                        WHEN pd.id = 'prod_OnHrSUwoO8VYfg' THEN 'Premier'
                        ELSE pd.name
                    END AS tier
                    , p.recurring_interval
                    , s.created AS subscription_created_ts
                    , s.canceled_at AS subscription_canceled_ts
                    , COUNT(*) OVER (PARTITION BY i.id) AS n_invoice_line_items
                FROM
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoices i
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoice_line_items ili
                    ON ili.invoice_id = i.id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.subscriptions s
                    ON s.id = i.subscription_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.prices p
                    ON p.id = ili.price_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.products pd
                    ON pd.id = p.product_id
                WHERE 1=1
                    AND DATE(i.date) = :p_date
                    AND (
                        (i.billing_reason = 'subscription_cycle' AND i.attempt_count = 1) -- Successful renewals with no payment failures
                        OR (i.billing_reason = 'subscription_update' AND i.attempt_count = 0) -- Update subscriptions that exist to reset the billing start date on payment failures
                        OR (i.billing_reason = 'subscription_update' AND i.attempt_count > 0) -- End-of-cycle subscription updates, or mid-cycle upgrades and cycle resets (not downgrades)
                    )
                    AND i.paid
                    AND ili.source_type = 'subscription'
                    -- Exclude credit topups
                    AND NOT REGEXP_LIKE(pd.name, '^Suno.* - \\d+ Credits$')
            )

            , dim_user_dedupe AS (
                -- For Stripe, we need to dedupe users by their Stripe customer ID
                -- because sometimes users delete and recreate their Suno account with the same Stripe customer ID
                SELECT * FROM (
                    SELECT
                        du.user_id
                        , du.user_uid
                        , du.stripe_customer_id
                        , udr.persist_user_id IS NOT NULL AS is_user_deleted
                        , udr.deleted_at
                        , ROW_NUMBER() OVER (PARTITION BY du.stripe_customer_id ORDER BY udr.persist_user_id IS NOT NULL ASC) AS rn
                    FROM
                        suno_prod.prod.dim_user du
                    LEFT JOIN
                        suno_prod.prod.rds_user_delete_request udr
                        ON udr.persist_user_id = du.user_id
                )
                WHERE rn = 1
            )

            SELECT
                -- Primary key and subscription identifiers
                MD5_TO_UUID('Stripe' || COALESCE(du.user_id, 0) || subs.customer_id || subs.invoice_id || subs.invoice_line_item_id) AS id
                , du.user_id
                , du.user_uid
                , dsp.id AS subscription_plan_id

                -- Subscription provider information
                , 'Stripe' AS billing_provider
                , 'web' AS subscribed_on_platform
                , subs.subscription_id AS provider_subscription_id
                , subs.customer_id AS provider_customer_id
                , subs.invoice_id AS provider_transaction_id
                , NULL AS provider_original_transaction_id

                -- Subscription information
                , subs.tier
                , subs.recurring_interval AS renewal_interval
                , COALESCE(subs.price_nickname, subs.product_name) AS provider_product_identifier
                , CASE
                    WHEN subs.billing_reason = 'subscription_cycle' THEN 'renewal'
                    WHEN subs.billing_reason = 'subscription_update' AND subs.attempt_count = 0 THEN 'renewal_with_cycle_reset'
                    WHEN subs.billing_reason = 'subscription_update' AND subs.attempt_count > 0 THEN 'product_change'
                END AS period_origin_type
                , subs.period_start AS period_start_ts_utc
                , subs.period_end AS period_end_ts_utc
                , subs.subscription_created_ts AS original_subscription_purchase_ts_utc
                , subs.subscription_canceled_ts AS canceled_at_ts_utc

                -- Analytics information
                , NULL AS billing_cycle_number -- This is set in the `FACT_SUBSCRIPTION_PERIOD_STRIPE_BILLING_CYCLE_NUMBER_PROC` procedure
                , NULL AS is_user_first_subscription_period -- This is set in the `FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD` procedure
                , FALSE AS is_trial
                , FALSE AS is_intro_offer
                , FALSE AS is_promotional_price
            FROM
                stripe_renewal_subscriptions subs
            LEFT JOIN
                dim_user_dedupe du
                ON du.stripe_customer_id = subs.customer_id
            LEFT JOIN
                suno_prod.prod.dim_subscription_plan dsp
                ON dsp.stripe_price_id = subs.price_id
        ) AS source
        ON fsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                fsp.user_id = source.user_id
                , fsp.user_uid = source.user_uid
                , fsp.subscription_plan_id = source.subscription_plan_id
                , fsp.billing_provider = source.billing_provider
                , fsp.subscribed_on_platform = source.subscribed_on_platform
                , fsp.provider_subscription_id = source.provider_subscription_id
                , fsp.provider_customer_id = source.provider_customer_id
                , fsp.provider_transaction_id = source.provider_transaction_id
                , fsp.provider_original_transaction_id = source.provider_original_transaction_id
                , fsp.tier = source.tier
                , fsp.renewal_interval = source.renewal_interval
                , fsp.provider_product_identifier = source.provider_product_identifier
                , fsp.period_origin_type = source.period_origin_type
                , fsp.period_start_ts_utc = source.period_start_ts_utc
                , fsp.period_end_ts_utc = source.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc = source.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc = source.canceled_at_ts_utc
                , fsp.billing_cycle_number = source.billing_cycle_number
                , fsp.is_user_first_subscription_period = source.is_user_first_subscription_period
                , fsp.is_trial = source.is_trial
                , fsp.is_intro_offer = source.is_intro_offer
                , fsp.is_promotional_price = source.is_promotional_price
        WHEN NOT MATCHED THEN
            INSERT (
                fsp.id
                , fsp.user_id
                , fsp.user_uid
                , fsp.subscription_plan_id
                , fsp.billing_provider
                , fsp.subscribed_on_platform
                , fsp.provider_subscription_id
                , fsp.provider_customer_id
                , fsp.provider_transaction_id
                , fsp.provider_original_transaction_id
                , fsp.tier
                , fsp.renewal_interval
                , fsp.provider_product_identifier
                , fsp.period_origin_type
                , fsp.period_start_ts_utc
                , fsp.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc
                , fsp.billing_cycle_number
                , fsp.is_user_first_subscription_period
                , fsp.is_trial
                , fsp.is_intro_offer
                , fsp.is_promotional_price
            ) VALUES (
                source.id
                , source.user_id
                , source.user_uid
                , source.subscription_plan_id
                , source.billing_provider
                , source.subscribed_on_platform
                , source.provider_subscription_id
                , source.provider_customer_id
                , source.provider_transaction_id
                , source.provider_original_transaction_id
                , source.tier
                , source.renewal_interval
                , source.provider_product_identifier
                , source.period_origin_type
                , source.period_start_ts_utc
                , source.period_end_ts_utc
                , source.original_subscription_purchase_ts_utc
                , source.canceled_at_ts_utc
                , source.billing_cycle_number
                , source.is_user_first_subscription_period
                , source.is_trial
                , source.is_intro_offer
                , source.is_promotional_price
            );

        RETURN 'SUCCESS';
    END;
    $$;

-- Get all invoice items from Stripe.
-- Invoice items are created for upgrade/downgrade events and credit refunds, which we manually exclude.
-- End-of-cycle upgrades/downgrades and mid-cycle upgrades (with cycle resets) are handled in the
--  `FACT_SUBSCRIPTION_PERIOD_STRIPE_RENEWAL_DAILY_INSERT` procedure, and mid-cycle downgrades are handled here.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_INVOICE_ITEMS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH

            stripe_invoice_items AS (
                SELECT
                    i.id AS invoice_id
                    , i.customer_id
                    , i.date AS invoice_ts
                    , i.billing_reason
                    , i.subscription_id
                    , ili.id AS invoice_line_item_id
                    , ili.price_id
                    , i.amount_due AS invoice_amount_due
                    , i.amount_paid AS invoice_amount_paid
                    , ili.period_start
                    , ili.period_end
                    , p.nickname AS price_nickname
                    , pd.name AS product_name
                    , CASE
                        WHEN pd.id = 'prod_SCeP8L1HWMvDEK' THEN 'Basic'
                        WHEN pd.id = 'prod_RQR2Sj8VnkNi6o' THEN 'Student Pro'
                        WHEN pd.id = 'prod_OVhjcxgokoaveE' THEN 'Pro'
                        WHEN pd.id = 'prod_SCeGA4AU6uFQfB' THEN 'Pro v2'
                        WHEN pd.id = 'prod_OnHrSUwoO8VYfg' THEN 'Premier'
                        ELSE pd.name
                    END AS tier
                    , p.recurring_interval
                    , s.created AS subscription_created_ts
                    , s.canceled_at AS subscription_canceled_ts
                    , COUNT(*) OVER (PARTITION BY i.id) AS n_invoice_line_items
                FROM
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoices i
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoice_line_items ili
                    ON ili.invoice_id = i.id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.subscriptions s
                    ON s.id = i.subscription_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.prices p
                    ON p.id = ili.price_id
                LEFT JOIN
                    gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.products pd
                    ON pd.id = p.product_id
                WHERE 1=1
                    AND DATE(i.date) = :p_date
                    AND i.billing_reason = 'subscription_update'
                    AND i.paid
                    AND ili.source_type = 'invoiceitem'
                    -- Exclude unused credit refund invoice items
                    AND pd.name NOT IN ('Discount for unused credits', 'Discount for unused monthly credits')
                    -- Exclude unused time refund invoice items
                    AND ili.description NOT LIKE '%(with 100.0% off)%'
                    -- Exclude credit topups
                    AND NOT REGEXP_LIKE(pd.name, '^Suno.* - \\d+ Credits$')
            )

            , dim_user_dedupe AS (
                -- For Stripe, we need to dedupe users by their Stripe customer ID
                -- because sometimes users delete and recreate their Suno account with the same Stripe customer ID
                SELECT * FROM (
                    SELECT
                        du.user_id
                        , du.user_uid
                        , du.stripe_customer_id
                        , udr.persist_user_id IS NOT NULL AS is_user_deleted
                        , udr.deleted_at
                        , ROW_NUMBER() OVER (PARTITION BY du.stripe_customer_id ORDER BY udr.persist_user_id IS NOT NULL ASC) AS rn
                    FROM
                        suno_prod.prod.dim_user du
                    LEFT JOIN
                        suno_prod.prod.rds_user_delete_request udr
                        ON udr.persist_user_id = du.user_id
                )
                WHERE rn = 1
            )

            SELECT
                -- Primary key and subscription identifiers
                MD5_TO_UUID('Stripe' || COALESCE(du.user_id, 0) || subs.customer_id || subs.invoice_id || subs.invoice_line_item_id) AS id
                , du.user_id
                , du.user_uid
                , dsp.id AS subscription_plan_id

                -- Subscription provider information
                , 'Stripe' AS billing_provider
                , 'web' AS subscribed_on_platform
                , subs.subscription_id AS provider_subscription_id
                , subs.customer_id AS provider_customer_id
                , subs.invoice_id AS provider_transaction_id
                , NULL AS provider_original_transaction_id

                -- Subscription information
                , subs.tier
                , subs.recurring_interval AS renewal_interval
                , COALESCE(subs.price_nickname, subs.product_name) AS provider_product_identifier
                , 'product_change' AS period_origin_type
                , subs.period_start AS period_start_ts_utc
                , subs.period_end AS period_end_ts_utc
                , subs.subscription_created_ts AS original_subscription_purchase_ts_utc
                , subs.subscription_canceled_ts AS canceled_at_ts_utc

                -- Analytics information
                , NULL AS billing_cycle_number
                , NULL AS is_user_first_subscription_period -- This is set in the `FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD` procedure
                , FALSE AS is_trial
                , FALSE AS is_intro_offer
                , FALSE AS is_promotional_price
            FROM
                stripe_invoice_items subs
            LEFT JOIN
                dim_user_dedupe du
                ON du.stripe_customer_id = subs.customer_id
            LEFT JOIN
                suno_prod.prod.dim_subscription_plan dsp
                ON dsp.stripe_price_id = subs.price_id
        ) AS source
        ON fsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                fsp.user_id = source.user_id
                , fsp.user_uid = source.user_uid
                , fsp.subscription_plan_id = source.subscription_plan_id
                , fsp.billing_provider = source.billing_provider
                , fsp.subscribed_on_platform = source.subscribed_on_platform
                , fsp.provider_subscription_id = source.provider_subscription_id
                , fsp.provider_customer_id = source.provider_customer_id
                , fsp.provider_transaction_id = source.provider_transaction_id
                , fsp.provider_original_transaction_id = source.provider_original_transaction_id
                , fsp.tier = source.tier
                , fsp.renewal_interval = source.renewal_interval
                , fsp.provider_product_identifier = source.provider_product_identifier
                , fsp.period_origin_type = source.period_origin_type
                , fsp.period_start_ts_utc = source.period_start_ts_utc
                , fsp.period_end_ts_utc = source.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc = source.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc = source.canceled_at_ts_utc
                , fsp.billing_cycle_number = source.billing_cycle_number
                , fsp.is_user_first_subscription_period = source.is_user_first_subscription_period
                , fsp.is_trial = source.is_trial
                , fsp.is_intro_offer = source.is_intro_offer
                , fsp.is_promotional_price = source.is_promotional_price
        WHEN NOT MATCHED THEN
            INSERT (
                fsp.id
                , fsp.user_id
                , fsp.user_uid
                , fsp.subscription_plan_id
                , fsp.billing_provider
                , fsp.subscribed_on_platform
                , fsp.provider_subscription_id
                , fsp.provider_customer_id
                , fsp.provider_transaction_id
                , fsp.provider_original_transaction_id
                , fsp.tier
                , fsp.renewal_interval
                , fsp.provider_product_identifier
                , fsp.period_origin_type
                , fsp.period_start_ts_utc
                , fsp.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc
                , fsp.billing_cycle_number
                , fsp.is_user_first_subscription_period
                , fsp.is_trial
                , fsp.is_intro_offer
                , fsp.is_promotional_price
            ) VALUES (
                source.id
                , source.user_id
                , source.user_uid
                , source.subscription_plan_id
                , source.billing_provider
                , source.subscribed_on_platform
                , source.provider_subscription_id
                , source.provider_customer_id
                , source.provider_transaction_id
                , source.provider_original_transaction_id
                , source.tier
                , source.renewal_interval
                , source.provider_product_identifier
                , source.period_origin_type
                , source.period_start_ts_utc
                , source.period_end_ts_utc
                , source.original_subscription_purchase_ts_utc
                , source.canceled_at_ts_utc
                , source.billing_cycle_number
                , source.is_user_first_subscription_period
                , source.is_trial
                , source.is_intro_offer
                , source.is_promotional_price
            );

        RETURN 'SUCCESS';

        END;
    $$;



CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_SUBSCRIPTIONS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH

            revcat_subscriptions AS (
                SELECT * FROM (
                    SELECT
                        rc_original_app_user_id AS customer_id
                        , rc_last_seen_app_user_id_alias AS user_uid
                        , store_transaction_id
                        , original_store_transaction_id
                        , renewal_number AS billing_cycle_number
                        , LOWER(platform) AS platform
                        , product_identifier
                        , CASE
                            -- Separate out credit topups
                            WHEN LOWER(product_identifier) LIKE '%credits%' THEN 'credit_topup'
                            -- Original iOS subscriptions - e.g. monthly.pro, yearly.premier
                            WHEN SPLIT(product_identifier, '.')[1] = 'basic_2025_05_01_a' THEN 'Basic'
                            WHEN SPLIT(product_identifier, '.')[1] = 'student' THEN 'Student Pro'
                            WHEN SPLIT(product_identifier, '.')[1] = 'pro' THEN 'Pro'
                            WHEN SPLIT(product_identifier, '.')[1] IN ('pro_2025_05_01_a', 'pro_2025_05_01_b') THEN 'Pro v2'
                            WHEN SPLIT(product_identifier, '.')[1] IN ('premier', 'premier_2025_05_01_a', 'premier_2025_05_01_b') THEN 'Premier'
                            -- Original Android subscriptions - e.g. repeating_subscription:monthly-pro, repeating_subscription:yearly-premier, repeating_subscription:monthly-basic-20250501-a
                            WHEN product_identifier LIKE 'repeating_subscription%' AND REGEXP_REPLACE(SPLIT(product_identifier, ':')[1], '^[^-]+-(.*)$', '\\1') = 'basic-20250501-a' THEN 'Basic'
                            WHEN product_identifier LIKE 'repeating_subscription%' AND REGEXP_REPLACE(SPLIT(product_identifier, ':')[1], '^[^-]+-(.*)$', '\\1') = 'pro' THEN 'Pro'
                            WHEN product_identifier LIKE 'repeating_subscription%' AND REGEXP_REPLACE(SPLIT(product_identifier, ':')[1], '^[^-]+-(.*)$', '\\1') = 'pro-20250501-a' THEN 'Pro v2'
                            WHEN product_identifier LIKE 'repeating_subscription%' AND REGEXP_REPLACE(SPLIT(product_identifier, ':')[1], '^[^-]+-(.*)$', '\\1') = 'premier' THEN 'Premier'
                            -- Deprecated Android subscriptions - e.g. monthly_pro_plan:monthly-pro-plan, annual_pro_plan:annual-pro-plan
                            WHEN platform = 'android' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[1] = 'pro' THEN 'Pro'
                            WHEN platform = 'android' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[1] = 'premier' THEN 'Premier'
                            ELSE product_identifier
                            END
                        AS tier
                        , CASE
                            -- Separate out credit topups
                            WHEN LOWER(product_identifier) LIKE '%credits%' THEN 'credit_topup'
                            -- Original iOS subscriptions - e.g. monthly.pro, yearly.premier
                            WHEN SPLIT(product_identifier, '.')[0] = 'monthly' THEN 'month'
                            WHEN SPLIT(product_identifier, '.')[0] = 'yearly' THEN 'year'
                            -- Original Android subscriptions - e.g. repeating_subscription:monthly-pro, repeating_subscription:yearly-premier
                            WHEN product_identifier LIKE 'repeating_subscription%' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[0] = 'monthly' THEN 'month'
                            WHEN product_identifier LIKE 'repeating_subscription%' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[0] = 'yearly' THEN 'year'
                            -- Deprecated Android subscriptions - e.g. monthly_pro_plan:monthly-pro-plan, annual_pro_plan:annual-pro-plan
                            WHEN platform = 'android' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[0] = 'monthly' THEN 'month'
                            WHEN platform = 'android' AND SPLIT(SPLIT(product_identifier, ':')[1], '-')[0] IN ('yearly', 'annual') THEN 'year'
                            ELSE product_identifier
                            END
                        AS renewal_interval
                        , start_time::TIMESTAMP_NTZ(9) AS start_time
                        , end_time::TIMESTAMP_NTZ(9) AS end_time
                        , effective_end_time::TIMESTAMP_NTZ(9) AS effective_end_time
                        , unsubscribe_detected_at::TIMESTAMP_NTZ(9) AS unsubscribe_detected_at
                        , is_in_intro_offer_period
                        , is_trial_period
                        , ROW_NUMBER() OVER (PARTITION BY store_transaction_id ORDER BY updated_at DESC) AS rn
                    FROM
                        suno_prod.prod.revenuecat_transaction_data
                    WHERE 1=1
                        AND DATE(start_time) = :p_date
                        AND NOT is_sandbox
                        AND NOT LOWER(product_identifier) LIKE '%credits%'
                        -- TODO: Eventually add handling for trial periods and promotional stores
                        AND NOT is_trial_period
                        AND store != 'promotional'
                        -- TODO: Understand different ownership types
                        AND ownership_type != 'FAMILY_SHARED'

                        -- Of eligible transactions as defined by the conditions above (after deduplication using rn = 1):
                        -- (1) 99.94% of subscription periods correctly have start time < end time
                        -- (2) 0.0057% of subscription periods have start = end. These are typically duplicate entries on end-of-cycle upgrades/downgrades, and sometimes cancellations
                        -- (3) 0.00491% of subscription periods have start time > end time. These are typically billing issues (e.g. grace periods)
                        AND start_time < end_time
                )
                -- When subscriptions are renewed, Revcat creates new entries for all past transactions
                -- Select only the most recent entry for each transaction
                WHERE rn = 1
            )

            SELECT
                MD5_TO_UUID('RevenueCat' || COALESCE(du.user_id, 0) || subs.customer_id || subs.store_transaction_id) AS id
                , du.user_id
                , COALESCE(subs.user_uid, du.user_uid) AS user_uid
                , COALESCE(dsp.id, dsp_backup.id) AS subscription_plan_id

                -- Subscription provider information
                , 'RevenueCat' AS billing_provider
                , subs.platform AS subscribed_on_platform
                , subs.store_transaction_id AS provider_subscription_id
                , subs.customer_id AS provider_customer_id
                , subs.store_transaction_id AS provider_transaction_id
                , subs.original_store_transaction_id AS provider_original_transaction_id

                -- Subscription information
                , subs.tier
                , subs.renewal_interval
                , subs.product_identifier AS provider_product_identifier
                , CASE
                    WHEN subs.billing_cycle_number = 1 THEN 'new'
                    ELSE 'renewal'
                    END
                AS period_origin_type
                , subs.start_time AS period_start_ts_utc
                , subs.end_time AS period_end_ts_utc
                , NULL AS original_subscription_purchase_ts_utc -- This is set in the `FACT_SUBSCRIPTION_PERIOD_REVCAT_ORIGINAL_SUBSCRIPTION_PURCHASE_TS_PROC` procedure
                , subs.unsubscribe_detected_at AS canceled_at_ts_utc

                -- Analytics information
                , subs.billing_cycle_number
                , NULL AS is_user_first_subscription_period -- This is set in the `FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD_PROC` procedure
                , COALESCE(subs.is_trial_period, FALSE) AS is_trial
                , COALESCE(subs.is_in_intro_offer_period, FALSE) AS is_intro_offer
                , FALSE AS is_promotional_price
            FROM
                revcat_subscriptions subs
            LEFT JOIN
                suno_prod.prod.dim_user du
                ON subs.user_uid = du.user_uid
            LEFT JOIN
                suno_prod.prod.dim_subscription_plan dsp
                ON (subs.product_identifier = dsp.revcat_product_identifier_ios
                    OR subs.product_identifier = dsp.revcat_product_identifier_android)
                AND subs.renewal_interval = dsp.renewal_interval
            LEFT JOIN
                suno_prod.prod.dim_subscription_plan dsp_backup
                ON subs.tier = dsp_backup.tier
                AND subs.renewal_interval = dsp_backup.renewal_interval
        ) AS source
        ON fsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                fsp.user_id = source.user_id
                , fsp.user_uid = source.user_uid
                , fsp.subscription_plan_id = source.subscription_plan_id
                , fsp.billing_provider = source.billing_provider
                , fsp.subscribed_on_platform = source.subscribed_on_platform
                , fsp.provider_subscription_id = source.provider_subscription_id
                , fsp.provider_customer_id = source.provider_customer_id
                , fsp.provider_transaction_id = source.provider_transaction_id
                , fsp.provider_original_transaction_id = source.provider_original_transaction_id
                , fsp.provider_product_identifier = source.provider_product_identifier
                , fsp.period_origin_type = source.period_origin_type
                , fsp.tier = source.tier
                , fsp.renewal_interval = source.renewal_interval
                , fsp.period_start_ts_utc = source.period_start_ts_utc
                , fsp.period_end_ts_utc = source.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc = source.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc = source.canceled_at_ts_utc
                , fsp.billing_cycle_number = source.billing_cycle_number
                , fsp.is_user_first_subscription_period = source.is_user_first_subscription_period
                , fsp.is_trial = source.is_trial
                , fsp.is_intro_offer = source.is_intro_offer
                , fsp.is_promotional_price = source.is_promotional_price
        WHEN NOT MATCHED THEN
            INSERT (
                fsp.id
                , fsp.user_id
                , fsp.user_uid
                , fsp.subscription_plan_id
                , fsp.billing_provider
                , fsp.subscribed_on_platform
                , fsp.provider_subscription_id
                , fsp.provider_customer_id
                , fsp.provider_transaction_id
                , fsp.provider_original_transaction_id
                , fsp.tier
                , fsp.renewal_interval
                , fsp.provider_product_identifier
                , fsp.period_origin_type
                , fsp.period_start_ts_utc
                , fsp.period_end_ts_utc
                , fsp.original_subscription_purchase_ts_utc
                , fsp.canceled_at_ts_utc
                , fsp.billing_cycle_number
                , fsp.is_user_first_subscription_period
                , fsp.is_trial
                , fsp.is_intro_offer
                , fsp.is_promotional_price
            ) VALUES (
                source.id
                , source.user_id
                , source.user_uid
                , source.subscription_plan_id
                , source.billing_provider
                , source.subscribed_on_platform
                , source.provider_subscription_id
                , source.provider_customer_id
                , source.provider_transaction_id
                , source.provider_original_transaction_id
                , source.tier
                , source.renewal_interval
                , source.provider_product_identifier
                , source.period_origin_type
                , source.period_start_ts_utc
                , source.period_end_ts_utc
                , source.original_subscription_purchase_ts_utc
                , source.canceled_at_ts_utc
                , source.billing_cycle_number
                , source.is_user_first_subscription_period
                , source.is_trial
                , source.is_intro_offer
                , source.is_promotional_price
            );

        RETURN 'SUCCESS';
    END;
    $$;
