Place an Order

An order lives in two tables. orders gets one row for the order itself: the customer, the date and a status. order_items gets one row for each product in it: which order, which product, how many and at what price. Placing an order means writing all of these rows.

SQLite picks the id of a new row. Right after an INSERT, SELECT last_insert_rowid() gives it, so you can use it in the rows that belong to it. Here it is for a reviews table, if the shop had one:

var cmd = db.CreateCommand();
cmd.CommandText = "INSERT INTO reviews (product_id, stars) VALUES (4, 5)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT last_insert_rowid()";
var review_id = cmd.ExecuteScalar();

The given helper Execute(sql, value1, value2, …) does both: it gives $1, $2… their values, runs sql and returns the new row's id. The given FindProduct(id) gives a product's row as a dictionary, or null. Each item's unit_price comes from the products table, not from the request: the client only sends product ids and quantities. With no transaction open, each command is saved in shop.db as soon as it runs, and the answer is 201 with the new id. An order with no items is a bad request: answer 400 before you insert anything.

POST /orders {"customer_id": 5, "items": [{"product_id": 6, "quantity": 1}, {"product_id": 4, "quantity": 3}]}
    →  201  {"id": 8}
GET /orders/8/items  →  200  {"order_id": 8, "items": [{"name": "Teapot", "quantity": 1, "unit_price": 22}, {"name": "Pen", "quantity": 3, "unit_price": 2}], "total": 28}
GET /orders/8  →  200  {"id": 8, "ordered": "<today's date>", "status": "new", "customer": "Eva Rossi"}
POST /orders {"customer_id": 1, "items": [{"product_id": 8, "quantity": 2}]}
    →  201  {"id": 9}
GET /orders/9/items  →  200  {"order_id": 9, "items": [{"name": "Gift card", "quantity": 2, "unit_price": 50}], "total": 100}
POST /orders {"customer_id": 2, "items": []}
    →  400  {"error": "An order needs at least one item"}

Your task: the route POST /orders with its JsonElement data, and the helpers FindProduct and Execute, are given. Insert the order with today's date (date('now')) and the status 'new', keep its id, add one order_items row per item with the product's current price, and answer {"id": …} with 201. When items is empty, answer 400 with the error "An order needs at least one item".

Assignment
Follow the Coding Tutorial to fill in the POST /orders route: the order row, its item rows, the 201 answer and the check for an empty order.

Hint
data.GetProperty("items") is the JSON list: EnumerateArray() goes through its items and GetArrayLength() counts them. The empty check goes at the top of the route, before the first INSERT.