Project: Review an AI Pull Request

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 function:

@app.post("/products/<int:product_id>/discount")
def apply_discount(product_id):
    data = request.get_json()
    product = db.execute("SELECT * FROM products WHERE id = ?", (product_id,)).fetchone()
    discount = db.execute(f"SELECT * FROM discounts WHERE code = '{data['code']}'").fetchone()
    total_cents = product["price"] * (100 - discount["percent"])
    return jsonify({"product": product["name"], "code": discount["code"], "total_cents": total_cents})

A review asks what the AI never tried. Missing: product 99 or the code SUMMER50 gives None, and the function crashes with 500. Hostile: the f-string 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 ? placeholder keeps typed text a plain value, and one UPDATE with a condition takes a unit only if one is left. Then rowcount tells you whether it did.

user = db.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()
cursor = db.execute("UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0", (product_id,))
if cursor.rowcount == 0:
    return jsonify({"error": "Out of stock"}), 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 route and the function's first line are ready. Write the body of apply_discount so it fixes all three problems: 404 for a missing product or code, a ? 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 apply_discount one problem at a time: the lookups, 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.