BEGIN;

-- Insert customer
WITH new_cust AS (
  INSERT INTO customers (name, email, country)
  VALUES ('Sakura Ltd', 'orders@sakura.jp', 'Japan')
  RETURNING cust_id
),
-- Insert order for new customer
new_order AS (
  INSERT INTO orders (cust_id, status, total)
  SELECT cust_id, 'pending', 0
  FROM new_cust
  RETURNING order_id, cust_id
),
-- Insert order items
new_items AS (
  INSERT INTO order_items (order_id, product_id, qty, unit_price)
  SELECT no.order_id, p.product_id, 2, p.price
  FROM new_order no
  CROSS JOIN (SELECT product_id, price FROM products WHERE product_id = 101) p
  RETURNING qty, unit_price
)
-- Update order total
UPDATE orders SET total = (SELECT SUM(qty * unit_price) FROM new_items)
WHERE order_id = (SELECT order_id FROM new_order);

COMMIT;