CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN_INSERT()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        -- Free plan
        MERGE INTO SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN dsp
        USING (
            SELECT
                MD5_TO_UUID(id || name || 'none') AS id
                , CASE
                    WHEN id = '4497580c-f4eb-4f86-9f0e-960eb7c48d7d' THEN 'Free'
                    WHEN id = '30cc669c-1ee3-4e91-8801-8bc7570060e9' THEN 'Student Pro'
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'Basic'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'Pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'Pro v2'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'Premier'
                    ELSE name
                    END AS tier
                , NULL AS renewal_interval
                , daily_credits AS credits
                , 'day' AS credits_reset_interval
                , id AS plan_id_rds
                , name AS plan_name_rds
                , plan_key AS plan_key_rds
                , level AS plan_level
                , monthly_price_usd AS price_usd
                , NULL AS stripe_product_id
                , NULL AS stripe_price_id
                , NULL AS revcat_product_identifier_android
                , NULL AS revcat_product_identifier_ios
                , created_at::TIMESTAMP_NTZ AS created_at_rds
                , updated_at::TIMESTAMP_NTZ AS updated_at_rds
            FROM
                suno_prod.prod.rds_usage_plan
            WHERE 1=1
                AND id = '4497580c-f4eb-4f86-9f0e-960eb7c48d7d'
        ) AS source
        ON dsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                dsp.tier = source.tier
                , dsp.renewal_interval = source.renewal_interval
                , dsp.credits = source.credits
                , dsp.credits_reset_interval = source.credits_reset_interval
                , dsp.plan_id_rds = source.plan_id_rds
                , dsp.plan_name_rds = source.plan_name_rds
                , dsp.plan_level = source.plan_level
                , dsp.plan_key_rds = source.plan_key_rds
                , dsp.price_usd = source.price_usd
                , dsp.stripe_product_id = source.stripe_product_id
                , dsp.stripe_price_id = source.stripe_price_id
                , dsp.revcat_product_identifier_android = source.revcat_product_identifier_android
                , dsp.revcat_product_identifier_ios = source.revcat_product_identifier_ios
                , dsp.created_at_rds = source.created_at_rds
                , dsp.updated_at_rds = source.updated_at_rds
        WHEN NOT MATCHED THEN
            INSERT (
                id
                , tier
                , renewal_interval
                , credits
                , credits_reset_interval
                , plan_id_rds
                , plan_name_rds
                , plan_key_rds
                , plan_level
                , price_usd
                , stripe_product_id
                , stripe_price_id
                , revcat_product_identifier_android
                , revcat_product_identifier_ios
                , created_at_rds
                , updated_at_rds
            )
            VALUES (
                source.id
                , source.tier
                , source.renewal_interval
                , source.credits
                , source.credits_reset_interval
                , source.plan_id_rds
                , source.plan_name_rds
                , source.plan_key_rds
                , source.plan_level
                , source.price_usd
                , source.stripe_product_id
                , source.stripe_price_id
                , source.revcat_product_identifier_android
                , source.revcat_product_identifier_ios
                , source.created_at_rds
                , source.updated_at_rds
            );

        -- Monthly plans
        MERGE INTO SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN dsp
        USING (
            SELECT
                MD5_TO_UUID(id || name || 'month') AS id
                , CASE
                    WHEN id = '4497580c-f4eb-4f86-9f0e-960eb7c48d7d' THEN 'Free'
                    WHEN id = '30cc669c-1ee3-4e91-8801-8bc7570060e9' THEN 'Student Pro'
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'Basic'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'Pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'Pro v2'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'Premier'
                    ELSE name
                    END AS tier
                , 'month' AS renewal_interval
                , monthly_credits AS credits
                , 'month' AS credits_reset_interval
                , id AS plan_id_rds
                , name AS plan_name_rds
                , plan_key AS plan_key_rds
                , level AS plan_level
                , monthly_price_usd AS price_usd
                , stripe_monthly_price_id AS stripe_price_id
                , stripe_product_id
                -- The subscription plans from the 2025-05-01 XP have both `-a` and `-b` variants,
                -- but due to the structure of this table (one row per tier-interval) we coerce these all to the A variant
                , CASE
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'repeating_subscription:monthly-basic-20250501-a'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'repeating_subscription:monthly-pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'repeating_subscription:monthly-pro-20250501-a'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'repeating_subscription:monthly-premier'
                    ELSE NULL
                    END AS revcat_product_identifier_android
                , CASE
                    WHEN id = '30cc669c-1ee3-4e91-8801-8bc7570060e9' THEN 'monthly.student'
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'monthly.basic_2025_05_01_a'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'monthly.pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'monthly.pro_2025_05_01_a'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'monthly.premier'
                    ELSE NULL
                    END AS revcat_product_identifier_ios
                , created_at::TIMESTAMP_NTZ AS created_at_rds
                , updated_at::TIMESTAMP_NTZ AS updated_at_rds
            FROM
                suno_prod.prod.rds_usage_plan
            WHERE 1=1
                AND stripe_monthly_price_id IS NOT NULL
        ) AS source
        ON dsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                dsp.tier = source.tier
                , dsp.renewal_interval = source.renewal_interval
                , dsp.credits = source.credits
                , dsp.credits_reset_interval = source.credits_reset_interval
                , dsp.plan_id_rds = source.plan_id_rds
                , dsp.plan_name_rds = source.plan_name_rds
                , dsp.plan_key_rds = source.plan_key_rds
                , dsp.plan_level = source.plan_level
                , dsp.price_usd = source.price_usd
                , dsp.stripe_product_id = source.stripe_product_id
                , dsp.stripe_price_id = source.stripe_price_id
                , dsp.revcat_product_identifier_android = source.revcat_product_identifier_android
                , dsp.revcat_product_identifier_ios = source.revcat_product_identifier_ios
                , dsp.created_at_rds = source.created_at_rds
                , dsp.updated_at_rds = source.updated_at_rds
        WHEN NOT MATCHED THEN
            INSERT (
                id
                , tier
                , renewal_interval
                , credits
                , credits_reset_interval
                , plan_id_rds
                , plan_name_rds
                , plan_key_rds
                , plan_level
                , price_usd
                , stripe_product_id
                , stripe_price_id
                , revcat_product_identifier_android
                , revcat_product_identifier_ios
                , created_at_rds
                , updated_at_rds
            )
            VALUES (
                source.id
                , source.tier
                , source.renewal_interval
                , source.credits
                , source.credits_reset_interval
                , source.plan_id_rds
                , source.plan_name_rds
                , source.plan_key_rds
                , source.plan_level
                , source.price_usd
                , source.stripe_product_id
                , source.stripe_price_id
                , source.revcat_product_identifier_android
                , source.revcat_product_identifier_ios
                , source.created_at_rds
                , source.updated_at_rds
            );

        -- Annual plans
        MERGE INTO SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN dsp
        USING (
            SELECT
                MD5_TO_UUID(id || name || 'year') AS id
                , CASE
                    WHEN id = '4497580c-f4eb-4f86-9f0e-960eb7c48d7d' THEN 'Free'
                    WHEN id = '30cc669c-1ee3-4e91-8801-8bc7570060e9' THEN 'Student Pro'
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'Basic'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'Pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'Pro v2'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'Premier'
                    ELSE name
                    END AS tier
                , 'year' AS renewal_interval
                , monthly_credits AS credits
                , 'month' AS credits_reset_interval
                , id AS plan_id_rds
                , name AS plan_name_rds
                , plan_key AS plan_key_rds
                , level AS plan_level
                , annual_price_usd AS price_usd
                , stripe_annual_price_id AS stripe_price_id
                , stripe_product_id
                -- The subscription plans from the 2025-05-01 XP have both `-a` and `-b` variants,
                -- but due to the structure of this table (one row per tier-interval) we coerce these all to the A variant
                , CASE
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'repeating_subscription:yearly-basic-20250501-a'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'repeating_subscription:yearly-pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'repeating_subscription:yearly-pro-20250501-a'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'repeating_subscription:yearly-premier'
                    ELSE NULL
                    END AS revcat_product_identifier_android
                , CASE
                    WHEN id = '30cc669c-1ee3-4e91-8801-8bc7570060e9' THEN 'yearly.student'
                    WHEN id = '157b2ffa-6aec-488c-8c0b-d581633dd16f' THEN 'yearly.basic_2025_05_01_a'
                    WHEN id = '3eaebef3-ef46-446a-931c-3d50cd1514f1' THEN 'yearly.pro'
                    WHEN id = '60364dfb-34db-4736-97b5-2987eb5f5a41' THEN 'yearly.pro_2025_05_01_a'
                    WHEN id = 'e1235dd7-9f4d-4738-aeb2-1470466cba27' THEN 'yearly.premier'
                    ELSE NULL
                    END AS revcat_product_identifier_ios
                , created_at::TIMESTAMP_NTZ AS created_at_rds
                , updated_at::TIMESTAMP_NTZ AS updated_at_rds
            FROM
                suno_prod.prod.rds_usage_plan
            WHERE 1=1
                AND stripe_annual_price_id IS NOT NULL
        ) AS source
        ON dsp.id = source.id
        WHEN MATCHED THEN
            UPDATE SET
                dsp.tier = source.tier
                , dsp.renewal_interval = source.renewal_interval
                , dsp.credits = source.credits
                , dsp.credits_reset_interval = source.credits_reset_interval
                , dsp.plan_id_rds = source.plan_id_rds
                , dsp.plan_name_rds = source.plan_name_rds
                , dsp.plan_key_rds = source.plan_key_rds
                , dsp.plan_level = source.plan_level
                , dsp.price_usd = source.price_usd
                , dsp.stripe_product_id = source.stripe_product_id
                , dsp.stripe_price_id = source.stripe_price_id
                , dsp.revcat_product_identifier_android = source.revcat_product_identifier_android
                , dsp.revcat_product_identifier_ios = source.revcat_product_identifier_ios
                , dsp.created_at_rds = source.created_at_rds
                , dsp.updated_at_rds = source.updated_at_rds
        WHEN NOT MATCHED THEN
            INSERT (
                id
                , tier
                , renewal_interval
                , credits
                , credits_reset_interval
                , plan_id_rds
                , plan_name_rds
                , plan_key_rds
                , plan_level
                , price_usd
                , stripe_product_id
                , stripe_price_id
                , revcat_product_identifier_android
                , revcat_product_identifier_ios
                , created_at_rds
                , updated_at_rds
            )
            VALUES (
                source.id
                , source.tier
                , source.renewal_interval
                , source.credits
                , source.credits_reset_interval
                , source.plan_id_rds
                , source.plan_name_rds
                , source.plan_key_rds
                , source.plan_level
                , source.price_usd
                , source.stripe_product_id
                , source.stripe_price_id
                , source.revcat_product_identifier_android
                , source.revcat_product_identifier_ios
                , source.created_at_rds
                , source.updated_at_rds
            );

        RETURN 'SUCCESS';

    END;
    $$;
