-- Create TCOMP2701_INVOICE, the main table

CREATE TABLE TCOMP2701_INVOICE (
    invoice_id INT,
    customer_id INT,
    invoice_date DATE,
    due_date DATE,
    payment_date DATE,
    payment_status VARCHAR(20),
    payment_method VARCHAR(50),
    total_amount DECIMAL(18, 2),
    tax_amount DECIMAL(18, 2),
    discount_amount DECIMAL(18, 2),
    net_amount DECIMAL(18, 2),
    currency VARCHAR(10),
    billing_address_line1 VARCHAR(200),
    billing_address_line2 VARCHAR(200),
    billing_city VARCHAR(100),
    billing_state VARCHAR(50),
    billing_postal_code VARCHAR(20),
    billing_country VARCHAR(50),
    shipping_address_line1 VARCHAR(200),
    shipping_address_line2 VARCHAR(200),
    shipping_city VARCHAR(100),
    shipping_state VARCHAR(50),
    shipping_postal_code VARCHAR(20),
    shipping_country VARCHAR(50),
    item_description1 VARCHAR(200),
    item_quantity1 INT,
    item_price1 DECIMAL(18, 2),
    item_total1 DECIMAL(18, 2),
    item_description2 VARCHAR(200),
    item_quantity2 INT,
    item_price2 DECIMAL(18, 2),
    item_total2 DECIMAL(18, 2),
    item_description3 VARCHAR(200),
    item_quantity3 INT,
    item_price3 DECIMAL(18, 2),
    item_total3 DECIMAL(18, 2),
    item_description4 VARCHAR(200),
    item_quantity4 INT,
    item_price4 DECIMAL(18, 2),
    item_total4 DECIMAL(18, 2),
    item_description5 VARCHAR(200),
    item_quantity5 INT,
    item_price5 DECIMAL(18, 2),
    item_total5 DECIMAL(18, 2),
    created_timestamp TIMESTAMP,
    updated_timestamp TIMESTAMP,
    invoice_notes TEXT,
    terms_conditions TEXT,
    salesperson_id INT
);

-- Insert 100K x 49 / rows x cols random data
INSERT INTO TCOMP2701_INVOICE
SELECT
    SEQ4() AS invoice_id,
    UNIFORM(0, 100000, RANDOM()) AS customer_id, -- Assuming customer_id ranges from 0 to 100000 in TCOMP2701
    DATEADD('day', UNIFORM(1, 365, RANDOM()), CURRENT_DATE) AS invoice_date,
    DATEADD('day', UNIFORM(30, 60, RANDOM()), CURRENT_DATE) AS due_date,
    DATEADD('day', UNIFORM(30, 60, RANDOM()), CURRENT_DATE) AS payment_date,
    CASE WHEN UNIFORM(0, 2, RANDOM()) = 0 THEN 'Paid' ELSE 'Unpaid' END AS payment_status,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'Credit Card'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'PayPal'
         ELSE 'Bank Transfer' END AS payment_method,
    UNIFORM(100, 10000, RANDOM())::DECIMAL(18, 2) AS total_amount,
    UNIFORM(0, 2000, RANDOM())::DECIMAL(18, 2) AS tax_amount,
    UNIFORM(0, 500, RANDOM())::DECIMAL(18, 2) AS discount_amount,
    UNIFORM(100, 10000, RANDOM())::DECIMAL(18, 2) - UNIFORM(0, 500, RANDOM())::DECIMAL(18, 2) AS net_amount,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'USD'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'EUR'
         ELSE 'GBP' END AS currency,
    'Billing Address_' || SEQ4() AS billing_address_line1,
    'Billing Address_' || SEQ4() || ' Secondary' AS billing_address_line2,
    'Billing City_' || UNIFORM(1, 100, RANDOM()) AS billing_city,
    'Billing State_' || UNIFORM(1, 50, RANDOM()) AS billing_state,
    LPAD(UNIFORM(10000, 99999, RANDOM())::STRING, 5, '0') AS billing_postal_code,
    'Billing Country_' || UNIFORM(1, 50, RANDOM()) AS billing_country,
    'Shipping Address_' || SEQ4() AS shipping_address_line1,
    'Shipping Address_' || SEQ4() || ' Secondary' AS shipping_address_line2,
    'Shipping City_' || UNIFORM(1, 100, RANDOM()) AS shipping_city,
    'Shipping State_' || UNIFORM(1, 50, RANDOM()) AS shipping_state,
    LPAD(UNIFORM(10000, 99999, RANDOM())::STRING, 5, '0') AS shipping_postal_code,
    'Shipping Country_' || UNIFORM(1, 50, RANDOM()) AS shipping_country,
    'Item Description ' || UNIFORM(1, 100, RANDOM()) || ' A' AS item_description1,
    UNIFORM(1, 10, RANDOM()) AS item_quantity1,
    UNIFORM(10, 100, RANDOM())::DECIMAL(18, 2) AS item_price1,
    UNIFORM(10, 1000, RANDOM())::DECIMAL(18, 2) AS item_total1,
    'Item Description ' || UNIFORM(1, 100, RANDOM()) || ' B' AS item_description2,
    UNIFORM(1, 10, RANDOM()) AS item_quantity2,
    UNIFORM(10, 100, RANDOM())::DECIMAL(18, 2) AS item_price2,
    UNIFORM(10, 1000, RANDOM())::DECIMAL(18, 2) AS item_total2,
    'Item Description ' || UNIFORM(1, 100, RANDOM()) || ' C' AS item_description3,
    UNIFORM(1, 10, RANDOM()) AS item_quantity3,
    UNIFORM(10, 100, RANDOM())::DECIMAL(18, 2) AS item_price3,
    UNIFORM(10, 1000, RANDOM())::DECIMAL(18, 2) AS item_total3,
    'Item Description ' || UNIFORM(1, 100, RANDOM()) || ' D' AS item_description4,
    UNIFORM(1, 10, RANDOM()) AS item_quantity4,
    UNIFORM(10, 100, RANDOM())::DECIMAL(18, 2) AS item_price4,
    UNIFORM(10, 1000, RANDOM())::DECIMAL(18, 2) AS item_total4,
    'Item Description ' || UNIFORM(1, 100, RANDOM()) || ' E' AS item_description5,
    UNIFORM(1, 10, RANDOM()) AS item_quantity5,
    UNIFORM(10, 100, RANDOM())::DECIMAL(18, 2) AS item_price5,
    UNIFORM(10, 1000, RANDOM())::DECIMAL(18, 2) AS item_total5,
    CURRENT_TIMESTAMP AS created_timestamp,
    CURRENT_TIMESTAMP AS updated_timestamp,
    'Note for invoice ' || SEQ4() AS invoice_notes,
    'Terms and conditions apply' AS terms_conditions,
    UNIFORM(1, 50, RANDOM()) AS salesperson_id
FROM TABLE(GENERATOR(ROWCOUNT => 100000));

-- Creat the lookup table TCOMP2701 (should have been called TCOMP2701_CUSTOMER)
CREATE TABLE TCOMP2701 (
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(150),
    customer_phone VARCHAR(20),
    customer_dob DATE,
    customer_since DATE,
    loyalty_points INT,
    account_balance DECIMAL(18, 2),
    account_status VARCHAR(20),
    credit_limit DECIMAL(18, 2),
    total_purchases INT,
    total_returns INT,
    total_spent DECIMAL(18, 2),
    last_purchase_date DATE,
    last_return_date DATE,
    preferred_store VARCHAR(100),
    preferred_payment_method VARCHAR(50),
    email_opt_in BOOLEAN,
    sms_opt_in BOOLEAN,
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50),
    shipping_address_line1 VARCHAR(200),
    shipping_address_line2 VARCHAR(200),
    shipping_city VARCHAR(100),
    shipping_state VARCHAR(50),
    shipping_postal_code VARCHAR(20),
    shipping_country VARCHAR(50),
    preferred_shipping_method VARCHAR(50),
    rewards_tier VARCHAR(20),
    average_order_value DECIMAL(18, 2),
    highest_order_value DECIMAL(18, 2),
    lifetime_order_count INT,
    lifetime_order_value DECIMAL(18, 2),
    average_days_between_orders INT,
    customer_segment VARCHAR(50),
    marketing_source VARCHAR(100),
    last_login_date TIMESTAMP,
    last_password_change_date TIMESTAMP,
    account_created_timestamp TIMESTAMP,
    account_updated_timestamp TIMESTAMP,
    preferred_language VARCHAR(50),
    preferred_currency VARCHAR(10),
    customer_notes TEXT,
    referral_code VARCHAR(50),
    referred_by VARCHAR(50)
);

-- Insert 100K x 49 / rows x cols random data
INSERT INTO TCOMP2701
SELECT
    SEQ4() AS customer_id,
    'Customer_' || SEQ4() AS customer_name,
    'customer_' || SEQ4() || '@example.com' AS customer_email,
    '123-456-' || LPAD(SEQ4()::STRING, 4, '0') AS customer_phone,
    DATEADD('day', UNIFORM(1, 15000, RANDOM()), DATE '1970-01-01') AS customer_dob,
    DATEADD('day', UNIFORM(1, 10000, RANDOM()), DATE '2010-01-01') AS customer_since,
    UNIFORM(0, 10000, RANDOM()) AS loyalty_points,
    UNIFORM(0, 10000, RANDOM())::DECIMAL(18, 2) AS account_balance,
    CASE WHEN UNIFORM(0, 2, RANDOM()) = 0 THEN 'Active' ELSE 'Inactive' END AS account_status,
    UNIFORM(0, 5000, RANDOM())::DECIMAL(18, 2) AS credit_limit,
    UNIFORM(0, 100, RANDOM()) AS total_purchases,
    UNIFORM(0, 10, RANDOM()) AS total_returns,
    UNIFORM(0, 10000, RANDOM())::DECIMAL(18, 2) AS total_spent,
    DATEADD('day', UNIFORM(1, 365, RANDOM()), CURRENT_DATE) AS last_purchase_date,
    DATEADD('day', UNIFORM(1, 365, RANDOM()), CURRENT_DATE) AS last_return_date,
    'Store_' || UNIFORM(1, 10, RANDOM()) AS preferred_store,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'Credit Card'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'PayPal'
         ELSE 'Bank Transfer' END AS preferred_payment_method,
    IFF(UNIFORM(0, 2, RANDOM()) = 0, TRUE, FALSE) AS email_opt_in,
    IFF(UNIFORM(0, 2, RANDOM()) = 0, TRUE, FALSE) AS sms_opt_in,
    'Address_' || SEQ4() AS address_line1,
    'Address_' || SEQ4() || ' Secondary' AS address_line2,
    'City_' || UNIFORM(1, 100, RANDOM()) AS city,
    'State_' || UNIFORM(1, 50, RANDOM()) AS state,
    LPAD(UNIFORM(10000, 99999, RANDOM())::STRING, 5, '0') AS postal_code,
    'Country_' || UNIFORM(1, 50, RANDOM()) AS country,
    'Shipping Address_' || SEQ4() AS shipping_address_line1,
    'Shipping Address_' || SEQ4() || ' Secondary' AS shipping_address_line2,
    'Shipping City_' || UNIFORM(1, 100, RANDOM()) AS shipping_city,
    'Shipping State_' || UNIFORM(1, 50, RANDOM()) AS shipping_state,
    LPAD(UNIFORM(10000, 99999, RANDOM())::STRING, 5, '0') AS shipping_postal_code,
    'Shipping Country_' || UNIFORM(1, 50, RANDOM()) AS shipping_country,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'Standard'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'Express'
         ELSE 'Next Day' END AS preferred_shipping_method,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'Bronze'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'Silver'
         ELSE 'Gold' END AS rewards_tier,
    UNIFORM(0, 1000, RANDOM())::DECIMAL(18, 2) AS average_order_value,
    UNIFORM(0, 5000, RANDOM())::DECIMAL(18, 2) AS highest_order_value,
    UNIFORM(0, 100, RANDOM()) AS lifetime_order_count,
    UNIFORM(0, 100000, RANDOM())::DECIMAL(18, 2) AS lifetime_order_value,
    UNIFORM(0, 60, RANDOM()) AS average_days_between_orders,
    'Segment_' || UNIFORM(1, 10, RANDOM()) AS customer_segment,
    'Source_' || UNIFORM(1, 10, RANDOM()) AS marketing_source,
    DATEADD('second', UNIFORM(1, 31536000, RANDOM()), CURRENT_TIMESTAMP) AS last_login_date,
    DATEADD('second', UNIFORM(1, 31536000, RANDOM()), CURRENT_TIMESTAMP) AS last_password_change_date,
    DATEADD('second', UNIFORM(1, 31536000, RANDOM()), CURRENT_TIMESTAMP) AS account_created_timestamp,
    DATEADD('second', UNIFORM(1, 31536000, RANDOM()), CURRENT_TIMESTAMP) AS account_updated_timestamp,
    'Language_' || UNIFORM(1, 10, RANDOM()) AS preferred_language,
    CASE WHEN UNIFORM(0, 3, RANDOM()) = 0 THEN 'USD'
         WHEN UNIFORM(0, 3, RANDOM()) = 1 THEN 'EUR'
         ELSE 'GBP' END AS preferred_currency,
    'Note for customer ' || SEQ4() AS customer_notes,
    'REF' || LPAD(SEQ4()::STRING, 5, '0') AS referral_code,
    'REF_BY' || LPAD(SEQ4()::STRING, 5, '0') AS referred_by
FROM TABLE(GENERATOR(ROWCOUNT => 100000)); 