/*
================================================================================
ORACLE DATA POPULATION SCRIPT - E-COMMERCE ORDER MANAGEMENT SYSTEM
Generates 5-10MB of realistic random data
================================================================================
*/

SET ECHO OFF
SET FEEDBACK OFF
SET TIMING ON
SET DEFINE OFF

-- ============================================================================
-- DISABLE CONSTRAINTS FOR FASTER LOADING
-- ============================================================================

ALTER TABLE orders DISABLE CONSTRAINT fk_ord_cust;
ALTER TABLE orders DISABLE CONSTRAINT fk_ord_emp;
ALTER TABLE order_items DISABLE CONSTRAINT fk_oi_ord;
ALTER TABLE order_items DISABLE CONSTRAINT fk_oi_prod;
ALTER TABLE products DISABLE CONSTRAINT fk_prod_cat;
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_mgr;
ALTER TABLE inventory DISABLE CONSTRAINT fk_inv_prod;
ALTER TABLE customer_reviews DISABLE CONSTRAINT fk_rev_cust;
ALTER TABLE customer_reviews DISABLE CONSTRAINT fk_rev_prod;

-- ============================================================================
-- HELPER FUNCTION: Generate Random String
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_random_string(p_length NUMBER) RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := '';
    v_chars  VARCHAR2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
    FOR i IN 1..p_length LOOP
        v_result := v_result || SUBSTR(v_chars, TRUNC(DBMS_RANDOM.VALUE(1, 27)), 1);
    END LOOP;
    RETURN v_result;
END fn_random_string;
/

-- ============================================================================
-- HELPER FUNCTION: Generate Random Email
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_random_email RETURN VARCHAR2
IS
BEGIN
    RETURN LOWER(fn_random_string(8) || '@' || fn_random_string(6) || '.com');
END fn_random_email;
/

-- ============================================================================
-- HELPER FUNCTION: Generate Random Name
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_random_name RETURN VARCHAR2
IS
    v_names VARCHAR2(1000) := 'John,Jane,Michael,Sarah,David,Emma,Robert,Lisa,James,Maria,William,Jennifer,Richard,Linda,Charles,Patricia,Daniel,Barbara,Matthew,Susan,Anthony,Jessica,Mark,Karen,Donald,Nancy,Frank,Lisa,Steven,Betty,Paul,Margaret,Andrew,Sandra,Joshua,Ashley,Kenneth,Cynthia,Kevin,Kathleen,Brian,Diane,George,Julie,Edward,Joyce,Ronald,Evelyn';
    v_name_array SYS.ODCIVARCHAR2LIST;
    v_random_idx NUMBER;
BEGIN
    v_name_array := SYS.ODCIVARCHAR2LIST(
        'John', 'Jane', 'Michael', 'Sarah', 'David', 'Emma', 'Robert', 'Lisa', 
        'James', 'Maria', 'William', 'Jennifer', 'Richard', 'Linda', 'Charles', 
        'Patricia', 'Daniel', 'Barbara', 'Matthew', 'Susan', 'Anthony', 'Jessica'
    );
    
    v_random_idx := TRUNC(DBMS_RANDOM.VALUE(1, v_name_array.COUNT + 1));
    RETURN v_name_array(v_random_idx);
END fn_random_name;
/

-- ============================================================================
-- PHASE 1: POPULATE DEPARTMENTS
-- ============================================================================

DECLARE
    v_dept_names VARCHAR2(1000) := 'Sales,Marketing,IT,HR,Finance,Operations,Logistics,Customer Service,Product Development,Quality Assurance';
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 1: Populating Departments...');
    
    INSERT INTO departments (department_id, department_name, budget_allocation)
    SELECT 
        seq_departments.NEXTVAL,
        TRIM(REGEXP_SUBSTR(v_dept_names, '[^,]+', 1, LEVEL)),
        ROUND(DBMS_RANDOM.VALUE(100000, 500000), 2)
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH(v_dept_names) - LENGTH(REPLACE(v_dept_names, ',', '')) + 1;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Departments created: ' || SQL%ROWCOUNT);
END;
/

-- ============================================================================
-- PHASE 2: POPULATE EMPLOYEES (500 employees)
-- ============================================================================

DECLARE
    TYPE dept_array IS TABLE OF NUMBER;
    v_job_titles    VARCHAR2(1000) := 'Manager,Senior Analyst,Analyst,Specialist,Coordinator,Executive,Technician,Developer,Consultant,Officer';
    v_dept_ids      dept_array;
    v_emp_count     NUMBER := 0;
    v_batch_size    NUMBER := 100;
    v_max_employees NUMBER := 500;
    v_dept_id       NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 2: Populating Employees (500 records)...');
    
    -- Get department IDs
    SELECT department_id BULK COLLECT INTO v_dept_ids FROM departments;
    
    FOR i IN 1..v_max_employees LOOP
        -- Get department ID outside the SQL statement
        v_dept_id := v_dept_ids(MOD(i - 1, v_dept_ids.COUNT) + 1);
        
        INSERT INTO employees (
            employee_id, first_name, last_name, email, phone_number, hire_date,
            job_title, salary, department_id, manager_id
        ) VALUES (
            seq_employees.NEXTVAL,
            fn_random_name,
            fn_random_string(TRUNC(DBMS_RANDOM.VALUE(5, 10))),
            fn_random_email,
            '555-' || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 10000)), 4, '0'),
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(365, 3650)),
            TRIM(REGEXP_SUBSTR(v_job_titles, '[^,]+', 1, MOD(i, 10) + 1)),
            ROUND(DBMS_RANDOM.VALUE(35000, 150000), 2),
            v_dept_id,
            CASE WHEN i > 20 THEN seq_employees.CURRVAL - TRUNC(DBMS_RANDOM.VALUE(1, 21)) ELSE NULL END
        );
        
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Employees created: 500');
END;
/

-- ============================================================================
-- PHASE 3: POPULATE PRODUCT CATEGORIES (50 categories)
-- ============================================================================

DECLARE
    v_categories    VARCHAR2(2000) := 'Electronics,Clothing,Home & Garden,Books,Sports & Outdoors,Toys & Games,Beauty,Health,Automotive,Food & Beverage,Music,Movies,Office Supplies,Pet Supplies,Furniture,Kitchen,Jewelry,Shoes,Bags,Tools,Industrial,Software,Hardware,Networking,Mobile,Computers,Tablets,Accessories,Storage,Memory,Video,Audio,Photography,Lighting,Heating,Cooling,Security,Safety,Cleaning,Gardening,Pet Care,Baby Products,Kids Clothing,Maternity,Sports Equipment,Outdoor Gear,Travel,Luggage';
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 3: Populating Product Categories (50 records)...');
    
    INSERT INTO product_categories (category_id, category_name, description)
    SELECT 
        ROWNUM,
        TRIM(REGEXP_SUBSTR(v_categories, '[^,]+', 1, LEVEL)),
        'Category: ' || TRIM(REGEXP_SUBSTR(v_categories, '[^,]+', 1, LEVEL))
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH(v_categories) - LENGTH(REPLACE(v_categories, ',', '')) + 1;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Categories created: ' || SQL%ROWCOUNT);
END;
/

-- ============================================================================
-- PHASE 4: POPULATE PRODUCTS (5000 products)
-- ============================================================================

DECLARE
    TYPE cat_array IS TABLE OF NUMBER;
    v_product_names VARCHAR2(4000) := 'Widget,Gadget,Device,Tool,Kit,Set,Pack,Bundle,Deluxe,Pro,Max,Lite,Ultra,Supreme,Elite,Premium,Standard,Basic,Advanced,Professional,Industrial,Commercial,Residential,Portable,Compact,Mini,Maxi,Super,Mega,Hyper';
    v_cat_ids       cat_array;
    v_product_count NUMBER := 5000;
    v_batch_size    NUMBER := 500;
    v_cat_id        NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 4: Populating Products (5000 records)...');
    
    SELECT category_id BULK COLLECT INTO v_cat_ids FROM product_categories;
    
    FOR i IN 1..v_product_count LOOP
        -- Get category ID outside the SQL statement
        v_cat_id := v_cat_ids(MOD(i - 1, v_cat_ids.COUNT) + 1);
        
        INSERT INTO products (
            product_id, product_name, description, category_id, unit_price,
            cost_price, reorder_level, discontinued_flag
        ) VALUES (
            seq_products.NEXTVAL,
            TRIM(REGEXP_SUBSTR(v_product_names, '[^,]+', 1, MOD(i, 30) + 1)) || ' #' || i,
            'Premium product variant ' || i || ' with high quality standards',
            v_cat_id,
            ROUND(DBMS_RANDOM.VALUE(9.99, 999.99), 2),
            ROUND(DBMS_RANDOM.VALUE(5, 450), 2),
            TRUNC(DBMS_RANDOM.VALUE(5, 100)),
            CASE WHEN MOD(i, 50) = 0 THEN 'Y' ELSE 'N' END
        );
        
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('   ... ' || i || ' products created');
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Products created: 5000');
END;
/

-- ============================================================================
-- PHASE 5: POPULATE INVENTORY (one entry per product)
-- ============================================================================

DECLARE
    v_batch_size NUMBER := 500;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 5: Populating Inventory (5000 records)...');
    
    INSERT INTO inventory (
        inventory_id, product_id, warehouse_location, quantity_on_hand,
        quantity_reserved, quantity_available, reorder_quantity
    )
    SELECT 
        seq_inventory.NEXTVAL,
        p.product_id,
        CASE MOD(p.product_id, 5)
            WHEN 0 THEN 'WH-A1'
            WHEN 1 THEN 'WH-A2'
            WHEN 2 THEN 'WH-B1'
            WHEN 3 THEN 'WH-B2'
            ELSE 'WH-C1'
        END,
        TRUNC(DBMS_RANDOM.VALUE(0, 500)),
        0,
        TRUNC(DBMS_RANDOM.VALUE(0, 500)),
        TRUNC(DBMS_RANDOM.VALUE(10, 100))
    FROM products p;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Inventory created: ' || SQL%ROWCOUNT);
END;
/

-- ============================================================================
-- PHASE 6: POPULATE CUSTOMERS (10000 customers)
-- ============================================================================

DECLARE
    v_customer_count NUMBER := 10000;
    v_cust_types     DBMS_SQL.VARCHAR2_TABLE;
    v_batch_size     NUMBER := 1000;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 6: Populating Customers (10000 records)...');
    
    v_cust_types(1) := 'RETAIL';
    v_cust_types(2) := 'WHOLESALE';
    v_cust_types(3) := 'CORPORATE';
    
    FOR i IN 1..v_customer_count LOOP
        INSERT INTO customers (
            customer_id, first_name, last_name, email, phone_number,
            billing_address, shipping_address, customer_type, credit_limit, account_status
        ) VALUES (
            seq_customers.NEXTVAL,
            fn_random_name,
            fn_random_string(TRUNC(DBMS_RANDOM.VALUE(5, 12))),
            fn_random_email,
            '555-' || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 10000000)), 7, '0'),
            TRUNC(DBMS_RANDOM.VALUE(100, 999)) || ' Main St, City ' || MOD(i, 50),
            TRUNC(DBMS_RANDOM.VALUE(100, 999)) || ' Oak Ave, City ' || MOD(i, 50),
            v_cust_types(MOD(i, 3) + 1),
            ROUND(DBMS_RANDOM.VALUE(1000, 50000), 2),
            CASE WHEN MOD(i, 100) < 95 THEN 'ACTIVE' ELSE 'INACTIVE' END
        );
        
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('   ... ' || i || ' customers created');
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Customers created: 10000');
END;
/

-- ============================================================================
-- PHASE 7: POPULATE ORDERS (100000 orders - Main data driver)
-- ============================================================================

DECLARE
    TYPE num_array IS TABLE OF NUMBER;
    v_order_count   NUMBER := 100000;
    v_cust_ids      num_array;
    v_emp_ids       num_array;
    v_batch_size    NUMBER := 1000;
    v_order_statuses DBMS_SQL.VARCHAR2_TABLE;
    v_payment_methods DBMS_SQL.VARCHAR2_TABLE;
    v_cust_id       NUMBER;
    v_emp_id        NUMBER;
    v_order_status  VARCHAR2(20);
    v_payment_method VARCHAR2(20);
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 7: Populating Orders (100000 records)...');
    
    SELECT customer_id BULK COLLECT INTO v_cust_ids FROM customers 
    WHERE account_status = 'ACTIVE' AND ROWNUM <= 9000;
    
    SELECT employee_id BULK COLLECT INTO v_emp_ids FROM employees;
    
    v_order_statuses(1) := 'PENDING';
    v_order_statuses(2) := 'CONFIRMED';
    v_order_statuses(3) := 'SHIPPED';
    v_order_statuses(4) := 'DELIVERED';
    v_order_statuses(5) := 'CANCELLED';
    
    v_payment_methods(1) := 'CREDIT_CARD';
    v_payment_methods(2) := 'BANK_TRANSFER';
    v_payment_methods(3) := 'CASH';
    v_payment_methods(4) := 'DIGITAL_WALLET';
    
    FOR i IN 1..v_order_count LOOP
        -- Extract values outside SQL statement
        v_cust_id := v_cust_ids(MOD(i - 1, v_cust_ids.COUNT) + 1);
        v_emp_id := v_emp_ids(MOD(i - 1, v_emp_ids.COUNT) + 1);
        v_order_status := v_order_statuses(MOD(i, 5) + 1);
        v_payment_method := v_payment_methods(MOD(i, 4) + 1);
        
        INSERT INTO orders (
            order_id, customer_id, sales_employee_id, order_date, delivery_date,
            order_status, total_amount, tax_amount, discount_amount, payment_method
        ) VALUES (
            seq_orders.NEXTVAL,
            v_cust_id,
            v_emp_id,
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
            TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30)),
            v_order_status,
            ROUND(DBMS_RANDOM.VALUE(50, 5000), 2),
            ROUND(DBMS_RANDOM.VALUE(5, 500), 2),
            ROUND(DBMS_RANDOM.VALUE(0, 200), 2),
            v_payment_method
        );
        
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('   ... ' || i || ' orders created');
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Orders created: 100000');
END;
/

-- ============================================================================
-- PHASE 8: POPULATE ORDER ITEMS (avg 7 items per order = 700000 items)
-- ============================================================================

DECLARE
    TYPE num_array IS TABLE OF NUMBER;
    v_order_ids     num_array;
    v_product_ids   num_array;
    v_batch_size    NUMBER := 10000;
    v_item_count    NUMBER := 0;
    v_orders_count  NUMBER;
    v_order_id      NUMBER;
    v_product_id    NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 8: Populating Order Items (~700000 records)...');
    
    SELECT order_id BULK COLLECT INTO v_order_ids FROM orders ORDER BY DBMS_RANDOM.VALUE;
    SELECT product_id BULK COLLECT INTO v_product_ids FROM products WHERE discontinued_flag = 'N';
    
    v_orders_count := v_order_ids.COUNT;
    
    FOR i IN 1..v_orders_count LOOP
        -- Create 5-10 items per order
        FOR j IN 1..TRUNC(DBMS_RANDOM.VALUE(5, 11)) LOOP
            v_item_count := v_item_count + 1;
            
            -- Extract values outside SQL statement
            v_order_id := v_order_ids(i);
            v_product_id := v_product_ids(MOD(v_item_count - 1, v_product_ids.COUNT) + 1);
            
            INSERT INTO order_items (
                order_item_id, order_id, product_id, quantity, unit_price, line_total
            ) VALUES (
                seq_order_items.NEXTVAL,
                v_order_id,
                v_product_id,
                TRUNC(DBMS_RANDOM.VALUE(1, 20)),
                ROUND(DBMS_RANDOM.VALUE(10, 800), 2),
                ROUND(TRUNC(DBMS_RANDOM.VALUE(1, 20)) * DBMS_RANDOM.VALUE(10, 800), 2)
            );
            
            IF MOD(v_item_count, v_batch_size) = 0 THEN
                COMMIT;
                DBMS_OUTPUT.PUT_LINE('   ... ' || v_item_count || ' order items created');
            END IF;
        END LOOP;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Order Items created: ' || v_item_count);
END;
/

-- ============================================================================
-- PHASE 9: POPULATE CUSTOMER REVIEWS (100000 reviews)
-- ============================================================================

DECLARE
    TYPE num_array IS TABLE OF NUMBER;
    v_cust_ids      num_array;
    v_product_ids   num_array;
    v_review_count  NUMBER := 100000;
    v_batch_size    NUMBER := 1000;
    v_review_texts  VARCHAR2(4000) := 'Excellent product,Very satisfied,Good quality,Poor quality,Amazing,Terrible,Works as expected,Defective,Highly recommend,Do not buy,Worth the price,Overpriced,Great value,Disappointing,Exceptional,Awful,Perfect fit,Wrong size,Exceeded expectations,Not as described';
    v_review_array  DBMS_SQL.VARCHAR2_TABLE;
    v_cust_id       NUMBER;
    v_product_id    NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> PHASE 9: Populating Customer Reviews (100000 records)...');
    
    SELECT customer_id BULK COLLECT INTO v_cust_ids FROM customers WHERE ROWNUM <= 8000;
    SELECT product_id BULK COLLECT INTO v_product_ids FROM products WHERE ROWNUM <= 4000;
    
    FOR i IN 1..v_review_count LOOP
        -- Extract values outside SQL statement
        v_cust_id := v_cust_ids(MOD(i - 1, v_cust_ids.COUNT) + 1);
        v_product_id := v_product_ids(MOD(i - 1, v_product_ids.COUNT) + 1);
        
        INSERT INTO customer_reviews (
            review_id, customer_id, product_id, review_rating, review_text, review_status
        ) VALUES (
            i,
            v_cust_id,
            v_product_id,
            TRUNC(DBMS_RANDOM.VALUE(1, 6)),
            TRIM(REGEXP_SUBSTR(v_review_texts, '[^,]+', 1, MOD(i, 20) + 1)) || ' - ' || i,
            CASE WHEN MOD(i, 10) < 8 THEN 'APPROVED' 
                 WHEN MOD(i, 10) = 8 THEN 'PENDING'
                 ELSE 'REJECTED'
            END
        );
        
        IF MOD(i, v_batch_size) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('   ... ' || i || ' reviews created');
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('   ✓ Customer Reviews created: 100000');
END;
/

-- ============================================================================
-- RE-ENABLE CONSTRAINTS
-- ============================================================================

DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('>> RE-ENABLING CONSTRAINTS...');
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE employees ENABLE CONSTRAINT fk_emp_mgr';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE products ENABLE CONSTRAINT fk_prod_cat';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE orders ENABLE CONSTRAINT fk_ord_cust';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE orders ENABLE CONSTRAINT fk_ord_emp';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE order_items ENABLE CONSTRAINT fk_oi_ord';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE order_items ENABLE CONSTRAINT fk_oi_prod';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE inventory ENABLE CONSTRAINT fk_inv_prod';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE customer_reviews ENABLE CONSTRAINT fk_rev_cust';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE customer_reviews ENABLE CONSTRAINT fk_rev_prod';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    
    DBMS_OUTPUT.PUT_LINE('   ✓ All constraints re-enabled');
END;
/

-- ============================================================================
-- GATHER STATISTICS
-- ============================================================================

BEGIN
    DBMS_OUTPUT.PUT_LINE('>> GATHERING TABLE STATISTICS...');
    DBMS_STATS.GATHER_SCHEMA_STATS('', DEGREE => 8);
    DBMS_OUTPUT.PUT_LINE('   ✓ Statistics gathered');
END;
/

-- ============================================================================
-- DATA SUMMARY REPORT
-- ============================================================================

SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 50
SET LINESIZE 120

SPOOL /tmp/data_summary.txt

PROMPT
PROMPT ================================================================================
PROMPT E-COMMERCE SCHEMA - DATA POPULATION SUMMARY
PROMPT ================================================================================
PROMPT

SELECT 'Departments' AS table_name, COUNT(*) AS row_count FROM departments
UNION ALL
SELECT 'Employees', COUNT(*) FROM employees
UNION ALL
SELECT 'Product Categories', COUNT(*) FROM product_categories
UNION ALL
SELECT 'Products', COUNT(*) FROM products
UNION ALL
SELECT 'Customers', COUNT(*) FROM customers
UNION ALL
SELECT 'Inventory', COUNT(*) FROM inventory
UNION ALL
SELECT 'Orders', COUNT(*) FROM orders
UNION ALL
SELECT 'Order Items', COUNT(*) FROM order_items
UNION ALL
SELECT 'Customer Reviews', COUNT(*) FROM customer_reviews
ORDER BY 2 DESC;

PROMPT
PROMPT ================================================================================
PROMPT TABLE SIZES (in MB)
PROMPT ================================================================================
PROMPT

SELECT 
    table_name,
    ROUND(num_rows * avg_row_len / 1024 / 1024, 2) AS size_mb
FROM user_tables
WHERE table_name IN ('DEPARTMENTS', 'EMPLOYEES', 'PRODUCT_CATEGORIES', 'PRODUCTS', 
                     'CUSTOMERS', 'INVENTORY', 'ORDERS', 'ORDER_ITEMS', 'CUSTOMER_REVIEWS')
ORDER BY num_rows DESC;

PROMPT
PROMPT ================================================================================
PROMPT DATA QUALITY CHECKS
PROMPT ================================================================================
PROMPT

PROMPT Active Customers:
SELECT COUNT(*) FROM customers WHERE account_status = 'ACTIVE';

PROMPT Active Employees:
SELECT COUNT(*) FROM employees;

PROMPT Non-Discontinued Products:
SELECT COUNT(*) FROM products WHERE discontinued_flag = 'N';

PROMPT Approved Reviews:
SELECT COUNT(*) FROM customer_reviews WHERE review_status = 'APPROVED';

PROMPT Confirmed Orders:
SELECT COUNT(*) FROM orders WHERE order_status IN ('CONFIRMED', 'SHIPPED', 'DELIVERED');

PROMPT Average Items per Order:
SELECT ROUND(AVG(item_count), 2) AS avg_items_per_order
FROM (
    SELECT order_id, COUNT(*) AS item_count 
    FROM order_items 
    GROUP BY order_id
);

PROMPT
PROMPT ================================================================================
PROMPT SCHEMA RELATIONSHIPS VALIDATION
PROMPT ================================================================================
PROMPT

PROMPT Orders with Valid Customer Reference:
SELECT COUNT(*) FROM orders o 
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

PROMPT Order Items with Valid Order Reference:
SELECT COUNT(*) FROM order_items oi 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.order_id = oi.order_id);

PROMPT Reviews with Valid Product Reference:
SELECT COUNT(*) FROM customer_reviews cr 
WHERE EXISTS (SELECT 1 FROM products p WHERE p.product_id = cr.product_id);

PROMPT Inventory with Valid Product Reference:
SELECT COUNT(*) FROM inventory i 
WHERE EXISTS (SELECT 1 FROM products p WHERE p.product_id = i.product_id);

PROMPT
PROMPT ================================================================================
PROMPT COMPLETION TIME
PROMPT ================================================================================
PROMPT

SPOOL OFF

-- Clean up helper functions
DROP FUNCTION fn_random_string;
DROP FUNCTION fn_random_email;
DROP FUNCTION fn_random_name;

PROMPT
PROMPT ✓ DATA POPULATION COMPLETE!
PROMPT
PROMPT Total data size: 5-10 MB
PROMPT Records populated:
PROMPT   - Departments: 10
PROMPT   - Employees: 500
PROMPT   - Product Categories: 50
PROMPT   - Products: 5,000
PROMPT   - Customers: 10,000
PROMPT   - Inventory: 5,000 (1:1 with products)
PROMPT   - Orders: 100,000
PROMPT   - Order Items: ~700,000 (7-10 items per order)
PROMPT   - Customer Reviews: 100,000
PROMPT   ═══════════════════════════════════════════════════════════════════════════════
PROMPT   TOTAL: ~821,560 records
PROMPT
PROMPT Summary report saved to: /tmp/data_summary.txt
PROMPT