-- Without a transaction: if step 2 fails, money disappears!
UPDATE accounts SET balance = balance - 5000 WHERE id = 1; -- debit Ada
UPDATE accounts SET balance = balance + 5000 WHERE id = 2; -- credit Grace
-- If the server crashes between these two lines → Ada lost 5000, Grace got nothing

-- With a transaction — both succeed or neither does:
START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;

-- Check if everything looks correct:
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;  -- save both changes permanently
-- OR:
-- ROLLBACK; -- undo both changes if something went wrong

START TRANSACTION;  -- begin a transaction (also: BEGIN)
COMMIT;             -- save all changes permanently
ROLLBACK;           -- undo all changes back to START TRANSACTION
SAVEPOINT sp1;      -- create a named save point within the transaction
ROLLBACK TO sp1;    -- undo back to the savepoint (not all the way back)
RELEASE SAVEPOINT sp1; -- remove the savepoint

START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 99);
-- Oops — product 99 does not exist, foreign key error

ROLLBACK TO after_order;  -- undo the order_item insert only
                           -- the order itself is still in the transaction

COMMIT;  -- save the order (without the bad item)

START TRANSACTION;
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 10;
INSERT INTO order_items (order_id, product_id, qty) VALUES (55, 10, 1);
COMMIT;
-- Either BOTH changes happen, or NEITHER does.
-- Atomicity: the transaction is indivisible — an atom.

-- Foreign key constraint = consistency rule:
INSERT INTO order_items (order_id, product_id)
VALUES (999, 1);  -- order_id 999 does not exist
-- ❌ Error: foreign key violation
-- The database refuses the change to stay in a consistent state

-- Session A:
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 5;
-- Not yet committed — other sessions cannot see this change yet

-- Session B (simultaneously):
SELECT stock FROM products WHERE id = 5;
-- Sees the original stock value (not Session A's uncommitted change)
-- This is isolation: transactions are invisible until committed

COMMIT;
-- After this line, the changes are written to disk (the transaction log)
-- Even if the server crashes one millisecond later, the data is safe
-- The database will recover and the committed data will be there

START TRANSACTION;

-- Step 1: Check stock is available
SELECT stock INTO @current_stock
FROM products WHERE id = 42 FOR UPDATE;
-- FOR UPDATE locks the row so no other transaction can change stock simultaneously

IF @current_stock < 1 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock';
END IF;

-- Step 2: Deduct stock
UPDATE products
SET stock = stock - 1,
    updated_at = NOW()
WHERE id = 42;

-- Step 3: Record the order
INSERT INTO orders (customer_id, status, created_at)
VALUES (101, 'confirmed', NOW());

SET @new_order_id = LAST_INSERT_ID();

-- Step 4: Record the order item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @new_order_id, 42, 1, price FROM products WHERE id = 42;

-- Step 5: Apply loyalty discount if customer has 10+ orders
UPDATE orders
SET discount_pct = 10
WHERE id = @new_order_id
  AND (SELECT COUNT(*) FROM orders WHERE customer_id = 101) >= 10;

COMMIT;
-- All 4 changes saved atomically — or none if any step failed

WITH order_summary AS (
  SELECT
    c.name         AS customer,
    COUNT(o.id)    AS total_orders,
    SUM(oi.unit_price * oi.quantity) AS lifetime_value,
    RANK() OVER (ORDER BY SUM(oi.unit_price * oi.quantity) DESC) AS value_rank
  FROM customers c
  JOIN orders o     ON c.id = o.customer_id
  JOIN order_items oi ON o.id = oi.order_id
  GROUP BY c.id, c.name
)
SELECT * FROM order_summary WHERE value_rank <= 10;