CREATE OR REPLACE TASK SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_DAILY_UPSERT
    WAREHOUSE = SUNO_PROD_X_SMAL
    SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS
    DECLARE
        start_time TIMESTAMP;
        p_date DATE;
        row_count INTEGER;
        old_row_count INTEGER;
        new_row_count INTEGER;
    BEGIN
        p_date := DATE(SYSDATE() - INTERVAL '1 DAY');

        -- Stripe new subscriptions
        start_time := CURRENT_TIMESTAMP();
        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_NEW_SUBSCRIPTIONS_PROC(:p_date);
        SELECT COUNT(*) INTO :row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date AND provider_product_identifier = 'stripe' AND period_origin_type = 'new';
        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_STRIPE_NEW_SUBSCRIPTIONS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        -- Stripe renewal subscriptions
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_RENEWAL_SUBSCRIPTIONS_PROC(:p_date);

        SELECT COUNT(*) INTO :new_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date;

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

        -- Stripe invoice item subscriptions
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_STRIPE_INVOICE_ITEMS_PROC(:p_date);

        SELECT COUNT(*) INTO :new_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date;

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

        -- Revcat subscriptions
        start_time := CURRENT_TIMESTAMP();
        SELECT COUNT(*) INTO :old_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date;

        CALL SUNO_PROD.PROD.FACT_SUBSCRIPTION_PERIOD_REVCAT_SUBSCRIPTIONS_PROC(:p_date);

        SELECT COUNT(*) INTO :new_row_count
        FROM suno_prod.prod.fact_subscription_period
        WHERE DATE(period_start_ts_utc) = :p_date AND provider_product_identifier = 'revcat';
        CALL TASK_MONITOR_INSERT_PROC('FACT_SUBSCRIPTION_PERIOD_REVCAT_SUBSCRIPTIONS_PROC', :start_time, :row_count, 'SUCCESS', 'DAILY');

        RETURN 'SUCCESS';
    END;
