실전: 쇼핑몰 데이터베이스 설계

-- ── 회원 테이블 ──
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ── 카테고리 테이블 ──
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- ── 상품 테이블 ──
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    category_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- ── 주문 테이블 ──
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- ── 주문 상세 테이블 ──
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 자주 검색되는 열에 인덱스
CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_users_email ON users (email);

-- 1. 카테고리별 상품 목록 (재고 있는 것만)
SELECT p.id, p.name, p.price, p.stock, c.name AS category
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.is_active = TRUE
  AND p.stock > 0
ORDER BY c.name, p.price;

-- 2. 회원별 총 구매 금액 (상위 10명)
SELECT u.name, u.email,
       COUNT(o.id) AS order_count,
       SUM(o.total_amount) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 10;

-- 3. 인기 상품 TOP 5 (판매량 기준)
SELECT p.name,
       SUM(oi.quantity) AS total_sold,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 5;

-- 4. 월별 매출 통계
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS monthly_revenue
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month DESC;

-- 주문 생성 트랜잭션
START TRANSACTION;

-- 1. 주문 생성
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 0, 'pending');
SET @order_id = LAST_INSERT_ID();

-- 2. 주문 아이템 추가 + 재고 확인
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @order_id, id, 2, price
FROM products
WHERE id = 101 AND stock >= 2;

-- 3. 재고 감소
UPDATE products
SET stock = stock - 2
WHERE id = 101 AND stock >= 2;

-- 4. 주문 총액 갱신
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = @order_id
)
WHERE id = @order_id;

-- 5. 모든 작업 성공 시 확정
COMMIT;