= UseCase007 - Add Product to Order

**Initiating actors:**

* Logged-In Consumer

* Logged-In Admin

{{{#!div style="text-align: justify; width: 100%;"
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

{{{#!div style="text-align: justify; width: 100%;"

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.

{{{#!div style="margin-left: 20px;"
{{{
SELECT stock, price 
FROM Product 
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.

{{{#!div style="margin-left: 20px;"
{{{
SELECT order_id 
FROM `Order` 
WHERE user_id = @authenticated_user_id AND status = 'Pending';

INSERT INTO `Order` (user_id, date_created, status, total_price) 
VALUES (@authenticated_user_id, CURRENT_TIMESTAMP, 'Pending', 0.00);
}}}
}}}

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.

{{{#!div style="margin-left: 20px;"
{{{
INSERT INTO OrderItem (order_id, product_id, quantity, price_at_purchase) 
VALUES (@active_order_id, @selected_product_id, @requested_quantity, @current_price)
ON DUPLICATE KEY UPDATE quantity = quantity + @requested_quantity;
}}}
}}}

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

{{{#!div style="margin-left: 20px;"
{{{
SELECT SUM(quantity * price_at_purchase) AS new_total
FROM OrderItem
WHERE order_id = @active_order_id;
}}}
}}}

}}}