-- Random sample data for oracle_ecommerce_schema.sql.
-- Run once, as the same schema owner, after creating the schema in an empty schema.
-- The large audit_events load writes exactly 2 GiB of ASCII details payload
-- (2,147,483,648 bytes); table/index allocation and other rows add Oracle overhead.
-- Adjust c_target_payload_bytes or c_payload_chars below to change the load size.

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET DEFINE OFF
SET SERVEROUTPUT ON

DECLARE
    l_line_id      NUMBER := 0;
    l_purchase_line_id NUMBER := 0;
    l_journal_line_id NUMBER := 0;
    l_journal_amount NUMBER(14,2);
    l_batch_size   PLS_INTEGER := 5000;
    l_batch_rows   PLS_INTEGER;
    l_offset       NUMBER;
    l_rows         NUMBER;
    c_payload_chars CONSTANT PLS_INTEGER := 2000;
    c_target_payload_bytes CONSTANT NUMBER := 2147483648;
BEGIN
    DBMS_RANDOM.SEED(20260927);

    INSERT INTO currencies (currency_code, currency_name, currency_symbol)
    VALUES ('USD', 'US Dollar', '$');
    INSERT INTO currencies (currency_code, currency_name, currency_symbol)
    VALUES ('EUR', 'Euro', 'EUR');
    INSERT INTO currencies (currency_code, currency_name, currency_symbol)
    VALUES ('GBP', 'Pound Sterling', 'GBP');

    INSERT INTO sales_channels (channel_name) VALUES ('Web Store');
    INSERT INTO sales_channels (channel_name) VALUES ('Mobile App');
    INSERT INTO sales_channels (channel_name) VALUES ('Marketplace');
    INSERT INTO sales_channels (channel_name) VALUES ('Retail POS');

    FOR i IN 1..1000 LOOP
        INSERT INTO customers (
            email, first_name, last_name, phone_number, customer_since, customer_status
        ) VALUES (
            'customer' || TO_CHAR(i) || '@example.test',
            'First' || TO_CHAR(i),
            'Last' || TO_CHAR(i),
            '+1-555-' || TO_CHAR(1000000 + i),
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 2000)),
            CASE WHEN DBMS_RANDOM.VALUE(0, 1) < 0.97 THEN 'ACTIVE' ELSE 'INACTIVE' END
        );

        INSERT INTO customer_addresses (
            customer_id, address_type, address_line1, city, region, postal_code, country_code, is_default
        ) VALUES (
            i, 'BOTH', TO_CHAR(100 + i) || ' Commerce Street',
            'City' || TO_CHAR(MOD(i, 100) + 1), 'Region ' || TO_CHAR(MOD(i, 20) + 1),
            TO_CHAR(10000 + i), 'US', 'Y'
        );
    END LOOP;

    FOR i IN 1..50 LOOP
        INSERT INTO suppliers (supplier_name, email, country_code, supplier_status)
        VALUES (
            'Supplier ' || TO_CHAR(i), 'supplier' || TO_CHAR(i) || '@example.test',
            CASE WHEN MOD(i, 3) = 0 THEN 'GB' WHEN MOD(i, 3) = 1 THEN 'US' ELSE 'DE' END,
            'ACTIVE'
        );
    END LOOP;

    FOR i IN 1..30 LOOP
        INSERT INTO product_categories (category_name)
        VALUES ('Category ' || TO_CHAR(i));
    END LOOP;

    FOR i IN 1..500 LOOP
        INSERT INTO products (
            category_id, supplier_id, sku, product_name, unit_price, product_status
        ) VALUES (
            MOD(i - 1, 30) + 1, MOD(i - 1, 50) + 1,
            'SKU-' || LPAD(TO_CHAR(i), 6, '0'),
            'Catalog Product ' || TO_CHAR(i),
            ROUND(DBMS_RANDOM.VALUE(5, 500), 2), 'ACTIVE'
        );
    END LOOP;

    FOR i IN 1..10 LOOP
        INSERT INTO warehouses (warehouse_name, country_code)
        VALUES (
            'Warehouse ' || TO_CHAR(i),
            CASE WHEN MOD(i, 2) = 0 THEN 'US' ELSE 'DE' END
        );
    END LOOP;

    FOR i IN 1..5000 LOOP
        DECLARE
            l_on_hand NUMBER(12,2) := TRUNC(DBMS_RANDOM.VALUE(10, 1000));
        BEGIN
            INSERT INTO inventory_balances (
                product_id, warehouse_id, quantity_on_hand, quantity_reserved, last_counted_at
            ) VALUES (
                MOD(i - 1, 500) + 1,
                TRUNC((i - 1) / 500) + 1,
                l_on_hand,
                TRUNC(DBMS_RANDOM.VALUE(0, l_on_hand + 1)),
                TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30))
            );
        END;
    END LOOP;

    FOR i IN 1..10 LOOP
        INSERT INTO gl_accounts (account_code, account_name, account_type)
        VALUES (
            CASE i
                WHEN 1 THEN '1000' WHEN 2 THEN '1010' WHEN 3 THEN '1100'
                WHEN 4 THEN '2000' WHEN 5 THEN '2100' WHEN 6 THEN '3000'
                WHEN 7 THEN '4000' WHEN 8 THEN '4100' WHEN 9 THEN '5000'
                ELSE '5100'
            END,
            CASE i
                WHEN 1 THEN 'Cash' WHEN 2 THEN 'Payment Clearing' WHEN 3 THEN 'Inventory'
                WHEN 4 THEN 'Accounts Payable' WHEN 5 THEN 'Tax Payable' WHEN 6 THEN 'Equity'
                WHEN 7 THEN 'Product Revenue' WHEN 8 THEN 'Shipping Revenue'
                WHEN 9 THEN 'Cost of Goods Sold' ELSE 'Shipping Expense'
            END,
            CASE
                WHEN i <= 3 THEN 'ASSET'
                WHEN i <= 5 THEN 'LIABILITY'
                WHEN i = 6 THEN 'EQUITY'
                WHEN i <= 8 THEN 'REVENUE'
                ELSE 'EXPENSE'
            END
        );
    END LOOP;

    FOR i IN 1..1000 LOOP
        INSERT INTO sales_orders (
            order_date, customer_id, channel_id, currency_id,
            billing_address_id, shipping_address_id, order_status,
            subtotal, tax_amount, shipping_amount
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            i, MOD(i - 1, 4) + 1, MOD(i - 1, 3) + 1, i, i,
            CASE WHEN MOD(i, 12) = 0 THEN 'CANCELLED'
                 WHEN MOD(i, 5) = 0 THEN 'FULFILLED'
                 ELSE 'PAID' END,
            ROUND(DBMS_RANDOM.VALUE(25, 1200), 2),
            ROUND(DBMS_RANDOM.VALUE(1, 100), 2),
            ROUND(DBMS_RANDOM.VALUE(0, 30), 2)
        );

        FOR line_no IN 1..2 LOOP
            l_line_id := l_line_id + 1;
            INSERT INTO sales_order_lines (
                order_id, product_id, line_number, quantity, unit_price, discount_amount
            ) VALUES (
                i, MOD(i * 2 + line_no - 2, 500) + 1, line_no,
                TRUNC(DBMS_RANDOM.VALUE(1, 5)),
                ROUND(DBMS_RANDOM.VALUE(5, 500), 2),
                ROUND(DBMS_RANDOM.VALUE(0, 10), 2)
            );
        END LOOP;

        INSERT INTO payments (
            payment_date, order_id, currency_id, payment_method,
            payment_status, amount, provider_reference
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            i, MOD(i - 1, 3) + 1,
            CASE MOD(i, 4) WHEN 0 THEN 'BANK' WHEN 1 THEN 'CARD'
                 WHEN 2 THEN 'WALLET' ELSE 'CASH' END,
            CASE WHEN MOD(i, 17) = 0 THEN 'FAILED' ELSE 'CAPTURED' END,
            ROUND(DBMS_RANDOM.VALUE(25, 1300), 2),
            'PAY-' || TO_CHAR(i) || '-' || DBMS_RANDOM.STRING('X', 12)
        );

        INSERT INTO shipments (
            shipped_at, order_id, warehouse_id, tracking_number, carrier_name, shipment_status
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            i, MOD(i - 1, 10) + 1, 'TRK-' || LPAD(TO_CHAR(i), 10, '0'),
            CASE MOD(i, 3) WHEN 0 THEN 'Parcel One' WHEN 1 THEN 'RapidShip' ELSE 'Global Freight' END,
            CASE WHEN MOD(i, 9) = 0 THEN 'IN_TRANSIT' ELSE 'DELIVERED' END
        );

        INSERT INTO shipment_items (shipment_id, order_line_id, quantity)
        VALUES (i, (i - 1) * 2 + 1, 1);
        INSERT INTO shipment_items (shipment_id, order_line_id, quantity)
        VALUES (i, (i - 1) * 2 + 2, 1);
    END LOOP;

    FOR i IN 1..100 LOOP
        INSERT INTO purchase_orders (
            ordered_at, supplier_id, warehouse_id, currency_id, purchase_status
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            MOD(i - 1, 50) + 1, MOD(i - 1, 10) + 1, MOD(i - 1, 3) + 1,
            CASE WHEN MOD(i, 6) = 0 THEN 'ORDERED' ELSE 'RECEIVED' END
        );

        FOR line_no IN 1..2 LOOP
            l_purchase_line_id := l_purchase_line_id + 1;
            INSERT INTO purchase_order_lines (
                purchase_order_id, product_id, line_number, quantity, unit_cost
            ) VALUES (
                i, MOD(i * 2 + line_no - 2, 500) + 1, line_no,
                TRUNC(DBMS_RANDOM.VALUE(10, 200)),
                ROUND(DBMS_RANDOM.VALUE(3, 300), 2)
            );
        END LOOP;
    END LOOP;

    FOR i IN 1..2000 LOOP
        INSERT INTO inventory_movements (
            movement_at, product_id, warehouse_id, order_line_id,
            movement_type, quantity_delta, reference_note
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            MOD(i - 1, 500) + 1, MOD(TRUNC((i - 1) / 500), 10) + 1,
            MOD(i - 1, 2000) + 1,
            'SALE', -1, 'Generated fulfillment movement'
        );
    END LOOP;

    FOR i IN 1..1000 LOOP
        l_journal_amount := ROUND(DBMS_RANDOM.VALUE(25, 1300), 2);
        INSERT INTO journal_entries (
            posted_at, currency_id, source_type, source_id, entry_status, memo
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            MOD(i - 1, 3) + 1, 'SALES_ORDER', i, 'POSTED',
            'Generated balanced sales posting ' || TO_CHAR(i)
        );

        l_journal_line_id := l_journal_line_id + 1;
        INSERT INTO journal_lines (
            journal_entry_id, account_id, line_number, debit_amount, credit_amount, line_memo
        ) VALUES (
            i, 1, 1, l_journal_amount, 0, 'Cash or clearing debit'
        );

        l_journal_line_id := l_journal_line_id + 1;
        INSERT INTO journal_lines (
            journal_entry_id, account_id, line_number, debit_amount, credit_amount, line_memo
        ) VALUES (
            i, 7, 2, 0, l_journal_amount, 'Sales revenue credit'
        );
    END LOOP;

    FOR i IN 1..100 LOOP
        INSERT INTO returns (
            returned_at, order_line_id, customer_id, return_status, quantity, reason_code
        ) VALUES (
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            MOD(i - 1, 2000) + 1, TRUNC((MOD(i - 1, 2000)) / 2) + 1,
            CASE WHEN MOD(i, 4) = 0 THEN 'REFUNDED' ELSE 'RECEIVED' END,
            1, CASE MOD(i, 3) WHEN 0 THEN 'DAMAGED' WHEN 1 THEN 'NOT_AS_DESCRIBED' ELSE 'CHANGED_MIND' END
        );
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Reference and transactional sample data inserted.');

    l_rows := CEIL(c_target_payload_bytes / c_payload_chars);
    l_offset := 1;
    WHILE l_offset <= l_rows LOOP
        l_batch_rows := LEAST(l_batch_size, l_rows - l_offset + 1);

        INSERT INTO audit_events (
            event_at, actor_customer_id, event_type, source_ip, details
        )
        SELECT
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            CASE WHEN DBMS_RANDOM.VALUE(0, 1) < 0.8
                 THEN TRUNC(DBMS_RANDOM.VALUE(1, 1001)) ELSE NULL END,
            CASE MOD(LEVEL, 5)
                WHEN 0 THEN 'CUSTOMER_LOGIN'
                WHEN 1 THEN 'PRODUCT_VIEW'
                WHEN 2 THEN 'ORDER_UPDATE'
                WHEN 3 THEN 'PAYMENT_EVENT'
                ELSE 'ADDRESS_CHANGE'
            END,
            '192.0.2.' || TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 255))),
            DBMS_RANDOM.STRING(
                'A',
                LEAST(
                    c_payload_chars,
                    c_target_payload_bytes -
                        ((l_offset + LEVEL - 2) * c_payload_chars)
                )
            )
        FROM dual
        CONNECT BY LEVEL <= l_batch_rows;

        COMMIT;
        l_offset := l_offset + l_batch_rows;
        IF MOD(l_offset - 1, 50000) < l_batch_rows THEN
            DBMS_OUTPUT.PUT_LINE(
                'Audit events generated: ' || TO_CHAR(LEAST(l_offset - 1, l_rows)) ||
                ' / ' || TO_CHAR(l_rows)
            );
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(
        'Completed audit payload: ' || TO_CHAR(l_rows) ||
        ' rows x ' || TO_CHAR(c_payload_chars) ||
        ' characters (~2 GiB), plus Oracle row and index overhead.'
    );
END;
/

PROMPT Random dataset generation complete.