Untitled

-- Invoices Table Creation
CREATE OR REPLACE PROCEDURE generate_dummy_invoices AS
    -- Variables
    v_batch_size INT := 1000;

    -- Collections for bulk insert
    TYPE t_invoice_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_booking_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_due_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_total_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER;
    TYPE t_paid_amounts IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER;
    TYPE t_balances IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER;
    TYPE t_statuses IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

    l_invoice_ids t_invoice_ids;
    l_booking_ids t_booking_ids;
    l_due_dates t_due_dates;
    l_total_amounts t_total_amounts;
    l_paid_amounts t_paid_amounts;
    l_balances t_balances;
    l_statuses t_statuses;

    -- Preloaded BookingIDs
    booking_ids t_booking_ids;

    -- Array for statuses
    TYPE status_array IS TABLE OF VARCHAR2(50);
    statuses status_array := status_array('Paid', 'Pending', 'Overdue', 'Cancelled');

BEGIN
    -- 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 create 1,200,000 Invoices
    FOR batch IN 1..1200 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random data
            l_invoice_ids(i) := invoice_id_seq.NEXTVAL;
            l_booking_ids(i) := booking_ids(TRUNC(DBMS_RANDOM.VALUE(1, booking_ids.COUNT + 1)));
            l_due_dates(i) := TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.VALUE(1, 90)); -- Random due date within the next 90 days
            l_total_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(100, 5000), 2); -- Random total amount between 100 and 5000
            l_paid_amounts(i) := TRUNC(DBMS_RANDOM.VALUE(0, l_total_amounts(i)), 2); -- Random paid amount less than or equal to total amount
            l_balances(i) := l_total_amounts(i) - l_paid_amounts(i); -- Calculate balance
            l_statuses(i) := statuses(TRUNC(DBMS_RANDOM.VALUE(1, statuses.COUNT + 1))); 
        END LOOP;

        -- Bulk insert data
        FORALL i IN l_invoice_ids.FIRST..l_invoice_ids.LAST
            INSERT INTO Invoices (
                InvoiceID,
                BookingID,
                DueDate,
                TotalAmount,
                PaidAmount,
                Balance,
                Statuss
            ) VALUES (
                l_invoice_ids(i),
                l_booking_ids(i),
                l_due_dates(i),
                l_total_amounts(i),
                l_paid_amounts(i),
                l_balances(i),
                l_statuses(i)
            );

        -- Commit each batch
        COMMIT;

        -- Progress report
        DBMS_OUTPUT.PUT_LINE('Completed batch ' || batch || ' of 1200 (' || (batch * v_batch_size) || ' records)');
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Successfully generated 1,200,000 Invoices records.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END generate_dummy_invoices;
/

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