Untitled

-- Promotions Table (Simulation)
CREATE OR REPLACE PROCEDURE simulate_dummy_promotions AS
    -- Variables
    v_batch_size INT := 1000;

    -- Collections for bulk insert
    TYPE t_promotion_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_package_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_discount_percentages IS TABLE OF DECIMAL(5,2) INDEX BY BINARY_INTEGER;
    TYPE t_start_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_end_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;

    l_promotion_ids t_promotion_ids;
    l_package_ids t_package_ids;
    l_discount_percentages t_discount_percentages;
    l_start_dates t_start_dates;
    l_end_dates t_end_dates;

    -- Preloaded PackageIDs
    package_ids t_package_ids;

    -- Timing variables
    start_time NUMBER;
    end_time NUMBER;
    elapsed_time NUMBER;

BEGIN
    -- Record the start time
    start_time := DBMS_UTILITY.GET_TIME;

    -- Preload all PackageIDs into memory
    SELECT PackageID BULK COLLECT INTO package_ids FROM TravelPackages;

    -- Check if data exists
    IF package_ids.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No PackageIDs found in the TravelPackages table.');
        RETURN;
    END IF;

    -- Simulate generating 1,000 Promotions
    FOR batch IN 1..1 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random data
            l_promotion_ids(i) := i; -- Simulated PromotionID
            l_package_ids(i) := package_ids(TRUNC(DBMS_RANDOM.VALUE(1, package_ids.COUNT + 1)));
            l_discount_percentages(i) := TRUNC(DBMS_RANDOM.VALUE(5, 50), 2); -- Random discount between 5% and 50%
            l_start_dates(i) := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30)); -- Random start date within the last 30 days
            l_end_dates(i) := l_start_dates(i) + TRUNC(DBMS_RANDOM.VALUE(7, 30)); -- Random end date 7-30 days after start date
        END LOOP;

        -- Simulate progress report
        DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 1 (' || (batch * v_batch_size) || ' records)');

        -- Commented out actual bulk insert
        /*
        FORALL i IN l_promotion_ids.FIRST..l_promotion_ids.LAST
            INSERT INTO Promotions (
                PromotionID,
                PackageID,
                DiscountPercentage,
                StartDate,
                EndDate
            ) VALUES (
                l_promotion_ids(i),
                l_package_ids(i),
                l_discount_percentages(i),
                l_start_dates(i),
                l_end_dates(i)
            );

        -- Commit the batch
        COMMIT;
        */
    END LOOP;

    -- Record the end time
    end_time := DBMS_UTILITY.GET_TIME;

    -- Calculate elapsed time in seconds
    elapsed_time := (end_time - start_time) / 100;
    DBMS_OUTPUT.PUT_LINE('Execution Time (Simulation): ' || elapsed_time || ' seconds');
END;
/

-- Execute the procedure
BEGIN
    simulate_dummy_promotions();
END;
/