Untitled

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

    -- Collections for bulk insert
    TYPE t_payment_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER;
    TYPE t_payment_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_payment_methods IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
    TYPE t_payment_statuses IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

    l_payment_ids t_payment_ids;
    l_booking_ids t_booking_ids;
    l_amounts t_amounts;
    l_payment_dates t_payment_dates;
    l_payment_methods t_payment_methods;
    l_payment_statuses t_payment_statuses;

    -- Preloaded BookingIDs
    booking_ids t_booking_ids;

    -- Array for payment methods and statuses
    TYPE method_array IS TABLE OF VARCHAR2(100);
    TYPE status_array IS TABLE OF VARCHAR2(50);

    payment_methods method_array := method_array('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer', 'Cash');
    payment_statuses status_array := status_array('Paid', 'Pending', 'Failed', 'Refunded');

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

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

    -- Preload all BookingIDs into memory
    SELECT BookingID BULK COLLECT INTO booking_ids FROM Bookings;

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

    -- Loop to simulate 1,200,000 Payments
    FOR batch IN 1..1200 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random data
            l_payment_ids(i) := i; -- Simulated PaymentID
            l_booking_ids(i) := booking_ids(TRUNC(DBMS_RANDOM.VALUE(1, booking_ids.COUNT + 1)));
            l_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(50, 5000), 2); -- Random amount between 50 and 5000
            l_payment_dates(i) := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)); -- Random date within the last year
            l_payment_methods(i) := payment_methods(TRUNC(DBMS_RANDOM.VALUE(1, payment_methods.COUNT + 1)));
            l_payment_statuses(i) := payment_statuses(TRUNC(DBMS_RANDOM.VALUE(1, payment_statuses.COUNT + 1)));
        END LOOP;

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

        -- Commented out actual bulk insert
        /*
        FORALL i IN l_payment_ids.FIRST..l_payment_ids.LAST
            INSERT INTO Payments (
                PaymentID,
                BookingID,
                AmountPaid,
                PaymentDate,
                PaymentMethod,
                PaymentStatus
            ) VALUES (
                l_payment_ids(i),
                l_booking_ids(i),
                l_amounts(i),
                l_payment_dates(i),
                l_payment_methods(i),
                l_payment_statuses(i)
            );

        -- Commit each 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_payments();
END;
/