CREATE OR REPLACE TASK SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_SUBSCRIPTIONS_DAILY_UPDATE
    WAREHOUSE = SUNO_PROD_LARGE
    AFTER SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_DAILY_UPSERT
    AS
    DECLARE
        start_time TIMESTAMP;
        row_count NUMBER;
        old_row_count NUMBER;
        new_row_count NUMBER;
        p_date DATE;
    BEGIN
        p_date := DATE(SYSDATE() - INTERVAL '1 DAY');

        -- Drop Stripe late payment invoices
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_LATE_PAYMENT_INVOICES_PROC();

        SELECT COUNT(*) INTO :new_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD;

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_LATE_PAYMENT_INVOICES_PROC', :start_time, :new_row_count - :old_row_count, 'SUCCESS', 'DAILY');

        -- Delete full refunds
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_DELETE_FULL_REFUND_PROC();

        SELECT COUNT(*) INTO :new_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD;

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_DELETE_FULL_REFUND_PROC', :start_time, :new_row_count - :old_row_count, 'SUCCESS', 'DAILY');

        -- Update Stripe Pro Annual -> Premier Annual mid-cycle upgrade
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE 1=1
            AND provider_product_identifier = 'Upgrade to Suno Premier Plan'
            AND period_start_ts_utc = period_end_ts_utc;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_PREMIER_ANNUAL_UPGRADE_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_PREMIER_ANNUAL_UPGRADE_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Update dates on Stripe mid-cycle upgrade/downgrade invoices
        start_time := CURRENT_TIMESTAMP();

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_MIDCYCLE_DOWNGRADE_UPGRADE_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_MIDCYCLE_DOWNGRADE_UPGRADE_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Update Stripe original transaction ID
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE billing_provider = 'Stripe' AND provider_original_transaction_id IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_ORIGINAL_TRANSACTION_ID_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_ORIGINAL_TRANSACTION_ID_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Drop invalid Stripe subscription periods
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE billing_provider = 'Stripe' AND period_start_ts_utc >= period_end_ts_utc;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_INVALID_PERIODS_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_INVALID_PERIODS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Update Stripe billing cycle number
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE billing_provider = 'Stripe' AND billing_cycle_number IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_BILLING_CYCLE_NUMBER_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_BILLING_CYCLE_NUMBER_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Update Stripe cancellation timestamps
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE billing_provider = 'Stripe' AND canceled_at_ts_utc IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_CANCELLATIONS_PROC(:p_date);

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_CANCELLATIONS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        RETURN 'SUCCESS';
    END;


CREATE OR REPLACE TASK SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_SUBSCRIPTIONS_DAILY_UPDATE
    WAREHOUSE = SUNO_PROD_LARGE
    AFTER SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_SUBSCRIPTIONS_DAILY_UPDATE
    AS
    DECLARE
        start_time TIMESTAMP;
        row_count NUMBER;
        old_row_count NUMBER;
        new_row_count NUMBER;
        p_date DATE;
    BEGIN
        p_date := DATE(SYSDATE() - INTERVAL '1 DAY');
        -- Update Revcat original subscription purchase timestamp
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE 1=1
            AND billing_provider = 'RevenueCat'
            AND original_subscription_purchase_ts_utc IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_ORIGINAL_SUBSCRIPTION_PURCHASE_TS_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_REVCAT_ORIGINAL_SUBSCRIPTION_PURCHASE_TS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');


        -- Update Revcat mid-cycle upgrade/downgrade invoices
        start_time := CURRENT_TIMESTAMP();
        WITH src AS (
            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
                ) AS next_period_start_ts_utc
            FROM
                suno_prod.prod.fact_subscription_period
            WHERE 1=1
                AND billing_provider = 'RevenueCat'
        )
        SELECT COUNT(*) INTO :row_count
        FROM src
        WHERE period_end_ts_utc > next_period_start_ts_utc;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_MIDCYCLE_DOWNGRADE_UPGRADE_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_REVCAT_MIDCYCLE_DOWNGRADE_UPGRADE_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Update Revcat cancellation timestamps
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE billing_provider = 'RevenueCat' AND canceled_at_ts_utc IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_CANCELLATIONS_PROC(:p_date);

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_REVCAT_CANCELLATIONS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        RETURN 'SUCCESS';
    END;


CREATE OR REPLACE TASK SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_ALL_SUBSCRIPTIONS_DAILY_UPDATE
    WAREHOUSE = SUNO_PROD_LARGE
    AFTER SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_SUBSCRIPTIONS_DAILY_UPDATE
    AS
    DECLARE
        start_time TIMESTAMP;
        old_row_count NUMBER;
        new_row_count NUMBER;
        row_count NUMBER;
    BEGIN
        -- Update is_user_first_subscription_period
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE is_user_first_subscription_period IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD_PROC();

        SELECT COUNT(*) INTO :new_row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE is_user_first_subscription_period IS NOT NULL;

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_IS_USER_FIRST_SUBSCRIPTION_PERIOD_PROC', :start_time, :new_row_count - :old_row_count, 'SUCCESS', 'DAILY');

        -- Fill in null subscription plan IDs
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :row_count
        FROM SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD
        WHERE subscription_plan_id IS NULL;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_SUBSCRIPTION_PLAN_ID_PROC();

        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_SUBSCRIPTION_PLAN_ID_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        RETURN 'SUCCESS';
    END;
