CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_LATE_PAYMENT_INVOICES_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        DELETE FROM suno_prod.prod.fact_subscription_period
        WHERE provider_transaction_id IN (
            -- Get renewal invoices (subscription_cycle) that have both succeeded and failed charges.
            -- These are the invoices that had a failed charge, but were later successful
            -- A subsequent invoice (not captured in this query) is created to identify start/end of subscription period
            -- c.captured has some issues (0.6% of failed invoices are marked as captured), so we use c.status instead
            SELECT
                i.id
            FROM
                gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.invoices i
            LEFT JOIN
                gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.charges c
                ON i.id = c.invoice_id
            WHERE 1=1
                AND i.billing_reason = 'subscription_cycle'
            GROUP BY 1
            HAVING
                BOOLOR_AGG(c.status = 'succeeded')
                AND BOOLOR_AGG(c.status = 'failed')
        );

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_DELETE_FULL_REFUND_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        DELETE FROM suno_prod.prod.fact_subscription_period
        WHERE provider_transaction_id IN (
            SELECT
                pi.invoice_id
            FROM
                gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.payment_intents pi
            INNER JOIN
                gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.refunds re
                ON re.refund_payment_intent = pi.id
            WHERE 1=1
                AND re.id IS NOT NULL
                AND re.amount = pi.amount
                AND re.status = 'succeeded'
        );

        RETURN 'SUCCESS';
    END;
    $$;


-- When a user performs a mid-cycle upgrade from Pro Monthly/Annual to Premier Monthly/Annual,
-- they receive a mid-cycle update invoice where start_date = end_date.
-- We need to pull the end_date from the previous Pro Monthly/Annual subscription period and
-- update the new Premier Monthly/Annual subscription period with the correct end date.
-- There are also other metadata that need to be updated for the new Premier subscription period.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_PREMIER_UPGRADE_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            SELECT
                id
                , provider_subscription_id
                , period_start_ts_utc
                , period_end_ts_utc
                , LAG(period_end_ts_utc) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc
                ) AS prev_period_end_ts_utc
                , LAG(subscription_plan_id) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc
                ) AS prev_subscription_plan_id
                , LAG(tier) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc, period_end_ts_utc
                ) AS prev_tier
                , LAG(renewal_interval) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc, period_end_ts_utc
                ) AS prev_renewal_interval
                , LAG(provider_product_identifier) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc, period_end_ts_utc
                ) AS prev_provider_product_identifier
            FROM
                suno_prod.prod.fact_subscription_period
            WHERE 1=1
                AND billing_provider = 'Stripe'
        ) src
        ON fsp.id = src.id
        AND fsp.provider_product_identifier = 'Upgrade to Suno Premier Plan'
        AND fsp.period_start_ts_utc = fsp.period_end_ts_utc
        WHEN MATCHED THEN
            UPDATE SET
                fsp.tier = 'Premier'
                , fsp.renewal_interval = src.prev_renewal_interval
                , fsp.period_end_ts_utc = src.prev_period_end_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;


-- When a user performs any of the following downgrades/upgrades mid-cycle:
-- - Downgrades tier
-- - Upgrades tier
-- - Upgrades from monthly to annual *on Pro only*
-- Then they receive a midcycle update invoice, and we need to update the previous subscription period's end ts.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_MIDCYCLE_DOWNGRADE_UPGRADE_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            SELECT
                id
                , provider_subscription_id
                , period_start_ts_utc
                , period_end_ts_utc
                , LEAD(period_start_ts_utc) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc, period_end_ts_utc
                ) AS next_period_start_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period
            WHERE 1=1
                AND billing_provider = 'Stripe'
        ) src
        ON fsp.id = src.id
        AND fsp.period_end_ts_utc > src.next_period_start_ts_utc
        AND src.next_period_start_ts_utc IS NOT NULL
        WHEN MATCHED THEN
            UPDATE SET fsp.period_end_ts_utc = src.next_period_start_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_ORIGINAL_TRANSACTION_ID_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH stripe_original_transaction_ids AS (
                SELECT
                    provider_subscription_id
                    , MIN_BY(provider_transaction_id, period_start_ts_utc) AS provider_original_transaction_id
                FROM
                    suno_prod.prod.fact_subscription_period
                WHERE 1=1
                    AND billing_provider = 'Stripe'
                GROUP BY 1
            )

            SELECT
                fsp.id
                , sot.provider_original_transaction_id
            FROM
                suno_prod.prod.fact_subscription_period fsp
            LEFT JOIN
                stripe_original_transaction_ids sot
                ON fsp.provider_subscription_id = sot.provider_subscription_id
            WHERE 1=1
                AND fsp.billing_provider = 'Stripe'
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.provider_original_transaction_id = src.provider_original_transaction_id;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO suno_prod.prod.fact_subscription_period fsp
        USING (
            WITH
            -- Get the first subscription period for each user
            user_first_subscription_period_ids AS (
                SELECT
                    user_id
                    , MIN_BY(id, period_start_ts_utc) AS user_first_subscription_period_id
                FROM
                    suno_prod.prod.fact_subscription_period
                GROUP BY 1
            )

            SELECT
                fsp.id
                , fsp.user_id
                , ufs.user_first_subscription_period_id
                , fsp.id = ufs.user_first_subscription_period_id AS is_user_first_subscription_period
            FROM
                suno_prod.prod.fact_subscription_period fsp
            LEFT JOIN
                user_first_subscription_period_ids ufs
                ON fsp.user_id = ufs.user_id
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.is_user_first_subscription_period = src.is_user_first_subscription_period;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_INVALID_PERIODS_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        DELETE FROM suno_prod.prod.fact_subscription_period
        WHERE id IN (
            SELECT
                id
            FROM
                suno_prod.prod.fact_subscription_period
            WHERE 1=1
                AND billing_provider = 'Stripe'
                AND period_start_ts_utc >= period_end_ts_utc
        );

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_BILLING_CYCLE_NUMBER_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH
            -- Get the billing cycle number for each subscription period
            billing_cycle_numbers AS (
                SELECT
                    user_id
                    , provider_transaction_id
                    , period_start_ts_utc
                    , provider_original_transaction_id
                    , ROW_NUMBER() OVER (PARTITION BY user_id, provider_original_transaction_id ORDER BY period_start_ts_utc) AS billing_cycle_number
                FROM
                    suno_prod.prod.fact_subscription_period
                WHERE 1=1
                    AND billing_provider = 'Stripe'
            )

            SELECT
                fsp.id
                , fsp.period_start_ts_utc
                , bc.billing_cycle_number
            FROM
                suno_prod.prod.fact_subscription_period fsp
            LEFT JOIN
                billing_cycle_numbers bc
                ON fsp.user_id = bc.user_id
                AND fsp.provider_transaction_id = bc.provider_transaction_id
                AND fsp.period_start_ts_utc = bc.period_start_ts_utc
                AND fsp.provider_original_transaction_id = bc.provider_original_transaction_id
        ) src
        ON fsp.id = src.id
        AND fsp.period_start_ts_utc = src.period_start_ts_utc
        AND fsp.billing_provider = 'Stripe'
        WHEN MATCHED THEN
            UPDATE SET fsp.billing_cycle_number = src.billing_cycle_number;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_CANCELLATIONS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            SELECT
                id AS stripe_subscription_id
                , canceled_at
            FROM
                gswudfy_stripe_us_east_2_share_ccntdmk_suno.stripe.subscriptions
            WHERE 1=1
                AND status = 'canceled'
                AND DATE(canceled_at) = :p_date
        ) src
        ON fsp.provider_subscription_id = src.stripe_subscription_id
        AND fsp.billing_provider = 'Stripe'
        WHEN MATCHED THEN
            UPDATE SET fsp.canceled_at_ts_utc = src.canceled_at;

        RETURN 'SUCCESS';
    END;
    $$;


-- Fill in null subscription plan IDs based on the tier and renewal interval
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_SUBSCRIPTION_PLAN_ID_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            SELECT
                fsp.id
                , dsp.id AS subscription_plan_id
            FROM
                suno_prod.prod.fact_subscription_period fsp
            INNER JOIN
                suno_prod.prod.dim_subscription_plan dsp
                ON fsp.tier = dsp.tier
                AND fsp.renewal_interval = dsp.renewal_interval
            WHERE 1=1
                AND fsp.subscription_plan_id IS NULL
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.subscription_plan_id = src.subscription_plan_id;

        RETURN 'SUCCESS';
    END;
    $$;

CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_ORIGINAL_SUBSCRIPTION_PURCHASE_TS_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH revcat_original_subscription_purchase_ts AS (
                SELECT
                    provider_original_transaction_id
                    , MIN(period_start_ts_utc) AS original_subscription_purchase_ts_utc
                FROM
                    suno_prod.prod.fact_subscription_period
                WHERE 1=1
                    AND billing_provider = 'RevenueCat'
                GROUP BY 1
            )

            SELECT
                fsp.id
                , ros.original_subscription_purchase_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period fsp
            LEFT JOIN
                revcat_original_subscription_purchase_ts ros
                ON fsp.provider_original_transaction_id = ros.provider_original_transaction_id
            WHERE 1=1
                AND fsp.billing_provider = 'RevenueCat'
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.original_subscription_purchase_ts_utc = src.original_subscription_purchase_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_CANCELLATIONS_PROC(p_date DATE)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH cancellations AS (
                SELECT
                    provider_original_transaction_id
                    , ANY_VALUE(canceled_at_ts_utc) AS canceled_at_ts_utc
                FROM
                    suno_prod.prod.fact_subscription_period
                WHERE 1=1
                    AND billing_provider = 'RevenueCat'
                    AND canceled_at_ts_utc IS NOT NULL
                    AND DATE(canceled_at_ts_utc) = :p_date
                GROUP BY 1
            )

            SELECT
                fsp.id
                , c.canceled_at_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period fsp
            INNER JOIN
                cancellations c
                ON fsp.provider_original_transaction_id = c.provider_original_transaction_id
            WHERE 1=1
                AND fsp.billing_provider = 'RevenueCat'
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.canceled_at_ts_utc = src.canceled_at_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;


CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_ALL_CANCELLATIONS_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            WITH cancellations AS (
                SELECT
                    provider_original_transaction_id
                    , ANY_VALUE(canceled_at_ts_utc) AS canceled_at_ts_utc
                FROM
                    suno_prod.prod.fact_subscription_period
                WHERE 1=1
                    AND billing_provider = 'RevenueCat'
                    AND canceled_at_ts_utc IS NOT NULL
                GROUP BY 1
            )

            SELECT
                fsp.id
                , c.canceled_at_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period fsp
            INNER JOIN
                cancellations c
                ON fsp.provider_original_transaction_id = c.provider_original_transaction_id
            WHERE 1=1
                AND fsp.billing_provider = 'RevenueCat'
        ) src
        ON fsp.id = src.id
        WHEN MATCHED THEN
            UPDATE SET fsp.canceled_at_ts_utc = src.canceled_at_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;


-- This procedure fixes start/end dates on Revcat subscriptions that end after the next subscription period starts.
CREATE OR REPLACE PROCEDURE SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_MIDCYCLE_DOWNGRADE_UPGRADE_PROC()
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD fsp
        USING (
            SELECT
                id
                , provider_subscription_id
                , period_start_ts_utc
                , period_end_ts_utc
                , LEAD(period_start_ts_utc) OVER (
                    PARTITION BY provider_subscription_id
                    ORDER BY period_start_ts_utc, period_end_ts_utc
                ) AS next_period_start_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period
            WHERE 1=1
                AND billing_provider = 'RevenueCat'
        ) src
        ON fsp.id = src.id
        AND fsp.period_end_ts_utc > src.next_period_start_ts_utc
        AND src.next_period_start_ts_utc IS NOT NULL
        WHEN MATCHED THEN
            UPDATE SET fsp.period_end_ts_utc = src.next_period_start_ts_utc;

        RETURN 'SUCCESS';
    END;
    $$;
