Bootcamp

# ===== Objectives =====
# 1. Stand up MySQL 8 in Docker
# 2. Design schema with proper types + indexes
# 3. Seed + query
# 4. Tune via EXPLAIN
# 5. Back up + restore

# ===== 0-5 min: stand up =====
docker run -d --name shop-db -p 3306:3306 \
  -e MYSQL_DATABASE=shop -e MYSQL_ROOT_PASSWORD=devpw \
  mysql:8.0

# Connect
docker exec -it shop-db mysql -u root -pdevpw shop

# ===== 5-20 min: schema =====
CREATE TABLE customers (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(190) NOT NULL,
  name        VARCHAR(120) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id  BIGINT UNSIGNED NOT NULL,
  status       ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  total_cents  BIGINT UNSIGNED NOT NULL,
  payload      JSON NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_customer_status_created (customer_id, status, created_at DESC),
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

# ===== 20-30 min: seed =====
INSERT INTO customers (email, name) VALUES
  ('alice@example.com', 'Alice'),
  ('bob@example.com',   'Bob'),
  ('carol@example.com', 'Carol');

INSERT INTO orders (customer_id, total_cents, status, payload) VALUES
  (1, 4995, 'paid',    JSON_OBJECT('source','web')),
  (1, 9900, 'new',     JSON_OBJECT('source','web')),
  (2, 1500, 'shipped', JSON_OBJECT('source','app'));

# ===== 30-40 min: queries =====
# top customers
SELECT c.id, c.name, SUM(o.total_cents) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status IN ('paid','shipped')
GROUP BY c.id ORDER BY revenue DESC LIMIT 10;

# Window: rank orders per customer
SELECT id, customer_id, total_cents,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rk
FROM orders;

# JSON
SELECT id, payload->>'$.source' AS source FROM orders WHERE payload->>'$.source' = 'web';

# ===== 40-50 min: EXPLAIN + tune =====
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid'
        ORDER BY created_at DESC LIMIT 20;
# Verify 'type' is 'ref' (or better), key = ix_customer_status_created, rows small

# Generated column for indexable JSON
ALTER TABLE orders
  ADD COLUMN source VARCHAR(32) GENERATED ALWAYS AS (payload->>'$.source') STORED,
  ADD KEY ix_source (source);

# ===== 50-55 min: transactions =====
START TRANSACTION;
  UPDATE customers SET name = 'Alicia' WHERE id = 1;
  INSERT INTO orders (customer_id, total_cents, status) VALUES (1, 2500, 'new');
COMMIT;

# Test
SELECT * FROM customers WHERE id = 1;
SELECT * FROM orders WHERE customer_id = 1;

# ===== 55-60 min: backup + restore =====
docker exec shop-db mysqldump -u root -pdevpw shop > shop-$(date +%F).sql
# Restore
# docker exec -i shop-db mysql -u root -pdevpw shop < shop-2026-06-18.sql

# Production: physical backups via Percona xtrabackup, or RDS snapshots

# ===== Post-bootcamp checklist =====
# - Schema uses utf8mb4 + InnoDB
# - PKs + FKs + composite indexes on hot queries
# - EXPLAIN confirmed index usage
# - Backup taken + restore verified

# ===== Pitfalls =====
# - 'utf8' (3-byte) instead of utf8mb4
# - Missing indexes on FK columns -> JOIN performance
# - Heavy SELECT * in transactions -> long-running tx
# - Forgetting to back up before schema migrations

-- 30-day MySQL bootcamp in the lesson body.