UseCase007 - Add Product to Order

Initiating actors:

• Logged-In Consumer

• Logged-In Admin

The goal of this use case is to allow an authenticated user to select a specific product and add it to an active order. This process captures the current price and quantity of the item, ensuring that the product is reserved for the transaction before the final purchase is completed.

Scenario

1. User selects a specific Product and chooses the option to add it to Order.

2. System checks the current stock level of the product in the Product table to ensure availability.

SELECT stock, price 
FROM PRODUCTS 
WHERE product_id = :selected_product_id;

3. System retrieves the current price of the product to establish the price_at_purchase for the transaction.

4. System creates or identifies an active Order record associated with the consumer’s user_id.

SELECT order_id 
FROM ORDERS 
WHERE user_id = :authenticated_user_id AND status = 'PENDING';

INSERT INTO ORDERS (order_id, user_id, payment_method, purchase_date, points_earned, points_used, status) 
VALUES (
    :new_order_id, 
    :authenticated_user_id, 
    'CARD',
    CURRENT_DATE,
    0, 
    0, 
    'PENDING'
);

5. System creates a new entry in the OrderItem table, linking the product_id to the order_id.

6. System records the specific quantity requested by the User and saves the price_at_purchase to the OrderItem record.

INSERT INTO ORDER_ITEMS (order_id, product_id, quantity, price_at_purchase) 
VALUES (:active_order_id, :selected_product_id, :requested_quantity, :current_price)
ON CONFLICT (order_id, product_id) 
DO UPDATE SET quantity = ORDER_ITEMS.quantity + EXCLUDED.quantity;

7. System updates the user's view to reflect that the item has been added to their Order total.

SELECT COALESCE(SUM(quantity * price_at_purchase), 0) AS new_total
FROM ORDER_ITEMS
WHERE order_id = :active_order_id;