Project: Review an AI Pull Request in C#

An AI assistant opened a pull request for the shop: customers can send a discount code to POST /products/{id}/discount and get the price after the discount, in cents. It works for SPRING10 on the Lamp, so it looks done. Here is the AI's route:

app.MapPost("/products/{id}/discount", (int id, JsonElement data) => {
    using var product = Command("SELECT * FROM products WHERE id = $id", "$id", id).ExecuteReader();
    product.Read();
    var code = data.GetProperty("code").GetString();
    var cmd = db.CreateCommand();
    cmd.CommandText = $"SELECT * FROM discounts WHERE code = '{code}'";
    using var discount = cmd.ExecuteReader();
    discount.Read();
    var totalCents = (long)product["price"] * (100 - (long)discount["percent"]);
    return Results.Json(new { product = product["name"], code = discount["code"], total_cents = totalCents });
});

A review asks what the AI never tried. Missing: for product 99 or the code SUMMER50, Read() finds no row, and reading a value from it throws an exception: 500. Hostile: the interpolated string $"…'{code}'" pastes the customer's text into the SQL, so the code x' OR '1'='1 matches every row and gets SPRING10's discount. That is SQL injection. Twice: every code has uses_left, and the AI never reads it, so the one-use VIP25 works forever and the used-up WINTER15 still works.

You know each fix from earlier lessons. Here they are on another table: a $email placeholder keeps typed text a plain value (that $ is part of the SQL text, not C#'s $"…"), and one UPDATE with a condition takes a unit only if one is left. Then ExecuteNonQuery() tells you how many rows it changed.

using var user = Command("SELECT * FROM users WHERE email = $email", "$email", email).ExecuteReader();
var changed = Command("UPDATE products SET stock = stock - 1 WHERE id = $id AND stock > 0", "$id", id).ExecuteNonQuery();
if (changed == 0) {
    return Results.Json(new { error = "Out of stock" }, statusCode: 409);
}

The codes: SPRING10 (10% off, 100 uses), VIP25 (25% off, 1 use) and WINTER15 (15% off, used up). Sent in this order, the requests must get these answers:

POST /products/3/discount   {"code": "SPRING10"}      →  200  {"code": "SPRING10", "product": "Lamp", "total_cents": 2610}
POST /products/99/discount  {"code": "SPRING10"}      →  404  {"error": "Product not found"}
POST /products/3/discount   {"code": "SUMMER50"}      →  404  {"error": "Unknown code"}
POST /products/3/discount   {"code": "x' OR '1'='1"}  →  404  {"error": "Unknown code"}
POST /products/1/discount   {"code": "VIP25"}         →  200  {"code": "VIP25", "product": "Mug", "total_cents": 900}
POST /products/1/discount   {"code": "VIP25"}         →  409  {"error": "Code used up"}
POST /products/5/discount   {"code": "WINTER15"}      →  409  {"error": "Code used up"}

Your task: the database, the discounts table, the Command helper, the route and the product lookup are ready. Write the rest of the route's lambda so it fixes all three problems: 404 for a missing product or code, a $code placeholder for the code, and one atomic UPDATE that takes a use, with 409 when none is left. Keep the AI's total in cents.

Assignment
Follow the Coding Tutorial to rewrite the discount route one problem at a time: the two 404s, the placeholder, the atomic UPDATE with its 409, and the answer.

Hint
Check in this order: the product, then the code, then the use, then the price. Take a use only after both lookups found a row, so a request for a missing product never spends a code. Once the lambda returns inside an if, C# compiles it only when its end returns too, so the file runs again at the last step.