CREATE TABLE members (
    member_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    birth_date DATE,
    gender ENUM('M', 'F', 'O') DEFAULT 'O',
    member_grade ENUM('BRONZE', 'SILVER', 'GOLD', 'VIP') DEFAULT 'BRONZE',
    total_purchase_amount DECIMAL(15,2) DEFAULT 0,
    point_balance INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    marketing_agreed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_member_grade (member_grade),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_addresses (
    address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL,
    address_name VARCHAR(50) NOT NULL COMMENT '배송지명 (예: 집, 회사)',
    recipient_name VARCHAR(50) NOT NULL,
    recipient_phone VARCHAR(20) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    address_line1 VARCHAR(200) NOT NULL COMMENT '기본주소',
    address_line2 VARCHAR(200) COMMENT '상세주소',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
    INDEX idx_member_id (member_id),
    INDEX idx_is_default (member_id, is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    grade_name VARCHAR(20) NOT NULL,
    min_purchase_amount DECIMAL(15,2) NOT NULL COMMENT '등급 달성 최소 구매액',
    discount_rate DECIMAL(5,2) DEFAULT 0 COMMENT '할인율 (%)',
    point_rate DECIMAL(5,2) DEFAULT 1 COMMENT '적립률 (%)',
    free_shipping_threshold DECIMAL(10,2) COMMENT '무료배송 기준금액',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 기본 등급 데이터 삽입
INSERT INTO member_grades (grade_name, min_purchase_amount, discount_rate, point_rate) VALUES
('BRONZE', 0, 0, 1),
('SILVER', 100000, 3, 1.5),
('GOLD', 500000, 5, 2),
('VIP', 1000000, 10, 3);

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_category_id INT NULL COMMENT '상위 카테고리 ID',
    category_name VARCHAR(50) NOT NULL,
    category_level TINYINT NOT NULL COMMENT '카테고리 깊이 (1: 대분류, 2: 중분류, 3: 소분류)',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id),
    INDEX idx_parent (parent_category_id),
    INDEX idx_level (category_level),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    product_code VARCHAR(50) UNIQUE NOT NULL COMMENT '상품 고유 코드',
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2) COMMENT '할인가',
    cost_price DECIMAL(10,2) COMMENT '원가 (관리용)',
    stock_quantity INT DEFAULT 0,
    min_order_quantity INT DEFAULT 1,
    max_order_quantity INT DEFAULT 999,
    weight DECIMAL(8,2) COMMENT '무게(kg)',
    manufacturer VARCHAR(100) COMMENT '제조사',
    brand VARCHAR(100) COMMENT '브랜드',
    origin_country VARCHAR(50) COMMENT '원산지',
    is_display BOOLEAN DEFAULT TRUE,
    is_soldout BOOLEAN DEFAULT FALSE,
    view_count INT DEFAULT 0,
    sales_count INT DEFAULT 0,
    rating_average DECIMAL(3,2) DEFAULT 0,
    review_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category (category_id),
    INDEX idx_product_code (product_code),
    INDEX idx_price (price),
    INDEX idx_created_at (created_at),
    INDEX idx_sales_count (sales_count),
    INDEX idx_rating (rating_average),
    FULLTEXT INDEX ft_idx_name_desc (product_name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 옵션 그룹 테이블 (예: 색상, 사이즈)
CREATE TABLE product_option_groups (
    option_group_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    group_name VARCHAR(50) NOT NULL COMMENT '옵션명 (예: 색상, 사이즈)',
    display_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 옵션 값 테이블 (예: 빨강, 파랑 / S, M, L)
CREATE TABLE product_option_values (
    option_value_id INT PRIMARY KEY AUTO_INCREMENT,
    option_group_id INT NOT NULL,
    value_name VARCHAR(50) NOT NULL,
    additional_price DECIMAL(10,2) DEFAULT 0 COMMENT '추가 금액',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (option_group_id) REFERENCES product_option_groups(option_group_id) ON DELETE CASCADE,
    INDEX idx_option_group (option_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 옵션 조합별 재고 관리 (예: 빨강-S, 빨강-M, 파랑-S...)
CREATE TABLE product_option_stocks (
    stock_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    option_combination VARCHAR(200) NOT NULL COMMENT '옵션 조합 (JSON 또는 구분자)',
    sku VARCHAR(100) UNIQUE COMMENT 'Stock Keeping Unit',
    stock_quantity INT DEFAULT 0,
    additional_price DECIMAL(10,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_images (
    image_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500),
    image_type ENUM('MAIN', 'SUB', 'DETAIL') DEFAULT 'SUB',
    display_order INT DEFAULT 0,
    alt_text VARCHAR(200) COMMENT 'SEO용 대체 텍스트',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_type (product_id, image_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL COMMENT '주문번호 (고객에게 보여지는)',
    member_id BIGINT NOT NULL,
    order_status ENUM('PENDING', 'PAID', 'PREPARING', 'SHIPPING', 'DELIVERED', 'CANCELLED', 'REFUNDED') DEFAULT 'PENDING',
    payment_method ENUM('CARD', 'BANK_TRANSFER', 'VIRTUAL_ACCOUNT', 'MOBILE', 'POINT') NOT NULL,
    payment_status ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED') DEFAULT 'PENDING',
    
    -- 금액 정보
    total_product_price DECIMAL(12,2) NOT NULL COMMENT '상품 총액',
    total_discount_amount DECIMAL(12,2) DEFAULT 0 COMMENT '할인 총액',
    delivery_fee DECIMAL(8,2) DEFAULT 0 COMMENT '배송비',
    total_payment_amount DECIMAL(12,2) NOT NULL COMMENT '최종 결제 금액',
    used_point INT DEFAULT 0 COMMENT '사용 포인트',
    earned_point INT DEFAULT 0 COMMENT '적립 포인트',
    
    -- 배송 정보
    recipient_name VARCHAR(50) NOT NULL,
    recipient_phone VARCHAR(20) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    delivery_message TEXT COMMENT '배송 메시지',
    
    -- 기타
    order_memo TEXT COMMENT '주문 메모',
    admin_memo TEXT COMMENT '관리자 메모',
    cancelled_at TIMESTAMP NULL,
    cancel_reason TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    INDEX idx_member (member_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (order_status),
    INDEX idx_created_at (created_at),
    INDEX idx_payment_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
    order_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL COMMENT '주문 당시 상품명 (변경 대비)',
    product_code VARCHAR(50) NOT NULL,
    option_info VARCHAR(500) COMMENT '선택한 옵션 정보 (JSON)',
    sku VARCHAR(100) COMMENT 'SKU',
    
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL COMMENT '단가',
    discount_amount DECIMAL(10,2) DEFAULT 0 COMMENT '상품별 할인액',
    total_price DECIMAL(12,2) NOT NULL COMMENT '소계 (단가 * 수량 - 할인)',
    
    item_status ENUM('PENDING', 'CONFIRMED', 'PREPARING', 'SHIPPING', 'DELIVERED', 'CANCELLED', 'REFUNDED') DEFAULT 'PENDING',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order (order_id),
    INDEX idx_product (product_id),
    INDEX idx_status (item_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cart_items (
    cart_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    option_info VARCHAR(500) COMMENT '선택한 옵션 (JSON)',
    sku VARCHAR(100),
    quantity INT NOT NULL DEFAULT 1,
    is_selected BOOLEAN DEFAULT TRUE COMMENT '주문 시 선택 여부',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_member (member_id),
    INDEX idx_product (product_id),
    UNIQUE KEY unique_cart_item (member_id, product_id, sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
    payment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    payment_method ENUM('CARD', 'BANK_TRANSFER', 'VIRTUAL_ACCOUNT', 'MOBILE', 'POINT') NOT NULL,
    payment_amount DECIMAL(12,2) NOT NULL,
    payment_status ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED', 'REFUNDED') DEFAULT 'PENDING',
    
    -- PG사 정보
    pg_provider VARCHAR(50) COMMENT 'PG사명 (예: 토스페이먼츠, 나이스페이)',
    pg_transaction_id VARCHAR(100) COMMENT 'PG사 거래 ID',
    approval_number VARCHAR(50) COMMENT '승인번호',
    
    -- 카드 정보 (마스킹 처리)
    card_company VARCHAR(50),
    card_number_masked VARCHAR(20) COMMENT '마스킹된 카드번호 (예: 1234-****-****-5678)',
    installment_month TINYINT DEFAULT 0 COMMENT '할부 개월 (0: 일시불)',
    
    -- 가상계좌 정보
    virtual_account_bank VARCHAR(50),
    virtual_account_number VARCHAR(50),
    virtual_account_holder VARCHAR(50),
    virtual_account_due_date TIMESTAMP NULL,
    
    paid_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    cancel_reason TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    INDEX idx_order (order_id),
    INDEX idx_pg_transaction (pg_transaction_id),
    INDEX idx_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 복합 인덱스 예시 (주문 조회 최적화)
CREATE INDEX idx_order_search 
ON orders(member_id, order_status, created_at);

-- 이렇게 하면 아래 쿼리들이 모두 인덱스를 활용할 수 있어!
-- SELECT * FROM orders WHERE member_id = 123;
-- SELECT * FROM orders WHERE member_id = 123 AND order_status = 'PAID';
-- SELECT * FROM orders WHERE member_id = 123 AND order_status = 'PAID' 
--   ORDER BY created_at DESC;

-- 커버링 인덱스 (쿼리에 필요한 모든 컬럼을 인덱스에 포함)
CREATE INDEX idx_product_list 
ON products(category_id, is_display, created_at, product_id, product_name, price);

-- 이렇게 하면 테이블 접근 없이 인덱스만으로 결과 반환 가능!
-- SELECT product_id, product_name, price 
-- FROM products 
-- WHERE category_id = 10 AND is_display = TRUE 
-- ORDER BY created_at DESC;

-- ❌ 나쁜 예: SELECT *는 피하자
SELECT * FROM products WHERE category_id = 10;

-- ✅ 좋은 예: 필요한 컬럼만 선택
SELECT product_id, product_name, price, thumbnail_url 
FROM products 
WHERE category_id = 10;

-- ❌ 나쁜 예: 함수 사용으로 인덱스 무효화
SELECT * FROM orders 
WHERE DATE(created_at) = '2024-01-15';

-- ✅ 좋은 예: 범위 조건으로 변경
SELECT * FROM orders 
WHERE created_at >= '2024-01-15 00:00:00' 
  AND created_at < '2024-01-16 00:00:00';

-- ❌ 나쁜 예: OR 조건은 인덱스 활용이 어려움
SELECT * FROM products 
WHERE category_id = 10 OR category_id = 20;

-- ✅ 좋은 예: IN 사용
SELECT * FROM products 
WHERE category_id IN (10, 20);

-- ❌ 나쁜 예: LIKE의 앞쪽 와일드카드
SELECT * FROM products 
WHERE product_name LIKE '%셔츠%';

-- ✅ 좋은 예: 전문 검색 인덱스 활용
SELECT * FROM products 
WHERE MATCH(product_name, description) AGAINST('셔츠' IN NATURAL LANGUAGE MODE);

-- ❌ 나쁜 예: Race Condition 발생 가능
-- 1. 재고 확인
SELECT stock_quantity FROM products WHERE product_id = 123;
-- 2. 재고가 충분하면 차감
UPDATE products SET stock_quantity = stock_quantity - 1 
WHERE product_id = 123;

-- ✅ 좋은 예 1: 원자적 업데이트 (Atomic Update)
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 123 
  AND stock_quantity >= 1;

-- 영향받은 행이 0이면 재고 부족
-- 영향받은 행이 1이면 성공

-- ✅ 좋은 예 2: 비관적 잠금 (Pessimistic Lock)
START TRANSACTION;

SELECT stock_quantity 
FROM products 
WHERE product_id = 123 
FOR UPDATE;  -- 행 잠금

-- 재고 확인 후 차감
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 123;

COMMIT;

-- ✅ 좋은 예 3: 낙관적 잠금 (Optimistic Lock)
-- products 테이블에 version 컬럼 추가
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- 업데이트 시 version도 함께 체크
UPDATE products 
SET stock_quantity = stock_quantity - 1,
    version = version + 1
WHERE product_id = 123 
  AND version = 5  -- 읽었을 때의 version
  AND stock_quantity >= 1;

-- 영향받은 행이 0이면 다른 트랜잭션이 먼저 수정함 (재시도 필요)

-- 재고 이력 테이블
CREATE TABLE stock_histories (
    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    sku VARCHAR(100),
    change_type ENUM('IN', 'OUT', 'ADJUST', 'RESERVE', 'RELEASE') NOT NULL,
    change_quantity INT NOT NULL COMMENT '변동량 (양수: 증가, 음수: 감소)',
    before_quantity INT NOT NULL,
    after_quantity INT NOT NULL,
    reference_type VARCHAR(50) COMMENT '참조 타입 (ORDER, RETURN, MANUAL)',
    reference_id BIGINT COMMENT '참조 ID',
    memo TEXT,
    created_by BIGINT COMMENT '처리자 ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_product (product_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 쿠폰 마스터 테이블
CREATE TABLE coupons (
    coupon_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    coupon_code VARCHAR(50) UNIQUE NOT NULL,
    coupon_name VARCHAR(100) NOT NULL,
    coupon_type ENUM('FIXED', 'PERCENT', 'FREE_SHIPPING') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL COMMENT '할인값 (금액 또는 %)',
    min_purchase_amount DECIMAL(10,2) DEFAULT 0 COMMENT '최소 구매 금액',
    max_discount_amount DECIMAL(10,2) COMMENT '최대 할인 금액 (% 쿠폰용)',
    
    total_quantity INT COMMENT '총 발행 수량 (NULL: 무제한)',
    used_quantity INT DEFAULT 0,
    
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL,
    
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_code (coupon_code),
    INDEX idx_valid_period (valid_from, valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 회원별 쿠폰 발급 테이블
CREATE TABLE member_coupons (
    member_coupon_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL,
    coupon_id BIGINT NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMP NULL,
    order_id BIGINT COMMENT '사용한 주문 ID',
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
    FOREIGN KEY (coupon_id) REFERENCES coupons(coupon_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    INDEX idx_member (member_id),
    INDEX idx_coupon (coupon_id),
    INDEX idx_used (is_used)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_reviews (
    review_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    member_id BIGINT NOT NULL,
    order_item_id BIGINT NOT NULL COMMENT '구매 확인용',
    
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT NOT NULL,
    
    is_photo_review BOOLEAN DEFAULT FALSE,
    helpful_count INT DEFAULT 0 COMMENT '도움됨 수',
    
    admin_reply TEXT COMMENT '관리자 답변',
    admin_replied_at TIMESTAMP NULL,
    
    is_visible BOOLEAN DEFAULT TRUE,
    is_best BOOLEAN DEFAULT FALSE COMMENT '베스트 리뷰 여부',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (order_item_id) REFERENCES order_items(order_item_id),
    INDEX idx_product (product_id),
    INDEX idx_member (member_id),
    INDEX idx_rating (rating),
    INDEX idx_created_at (created_at),
    UNIQUE KEY unique_review (order_item_id) -- 한 주문 상품당 하나의 리뷰만
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 리뷰 이미지 테이블
CREATE TABLE review_images (
    image_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    review_id BIGINT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (review_id) REFERENCES product_reviews(review_id) ON DELETE CASCADE,
    INDEX idx_review (review_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wishlists (
    wishlist_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_member (member_id),
    INDEX idx_product (product_id),
    UNIQUE KEY unique_wishlist (member_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE point_histories (
    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL,
    point_type ENUM('EARN', 'USE', 'CANCEL', 'EXPIRE') NOT NULL,
    point_amount INT NOT NULL COMMENT '포인트 금액 (양수: 적립, 음수: 사용)',
    point_balance INT NOT NULL COMMENT '변동 후 잔액',
    
    reason VARCHAR(100) NOT NULL COMMENT '적립/사용 사유',
    reference_type VARCHAR(50) COMMENT '참조 타입 (ORDER, REVIEW, EVENT)',
    reference_id BIGINT COMMENT '참조 ID',
    
    expire_date DATE COMMENT '포인트 만료일',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    INDEX idx_member (member_id),
    INDEX idx_created_at (created_at),
    INDEX idx_expire_date (expire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 카테고리별 상품 목록 (인기순, 페이징)
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    p.discount_price,
    COALESCE(p.discount_price, p.price) AS final_price,
    p.rating_average,
    p.review_count,
    p.sales_count,
    pi.image_url AS thumbnail
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id 
    AND pi.image_type = 'MAIN'
WHERE p.category_id = 10
    AND p.is_display = TRUE
    AND p.is_soldout = FALSE
ORDER BY p.sales_count DESC, p.created_at DESC
LIMIT 20 OFFSET 0;  -- 페이지당 20개, 1페이지

-- 가격대별 필터링
SELECT 
    p.product_id,
    p.product_name,
    COALESCE(p.discount_price, p.price) AS final_price
FROM products p
WHERE p.category_id = 10
    AND COALESCE(p.discount_price, p.price) BETWEEN 10000 AND 50000
    AND p.is_display = TRUE
ORDER BY final_price ASC;

-- 전문 검색 (상품명, 설명)
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    MATCH(p.product_name, p.description) AGAINST('셔츠' IN NATURAL LANGUAGE MODE) AS relevance
FROM products p
WHERE MATCH(p.product_name, p.description) AGAINST('셔츠' IN NATURAL LANGUAGE MODE)
    AND p.is_display = TRUE
ORDER BY relevance DESC, p.sales_count DESC
LIMIT 20;

-- 주문 생성 (트랜잭션 필수!)
START TRANSACTION;

-- 1. 주문 생성
INSERT INTO orders (
    order_number, member_id, order_status, payment_method,
    total_product_price, total_discount_amount, delivery_fee, 
    total_payment_amount, used_point,
    recipient_name, recipient_phone, postal_code, 
    address_line1, address_line2
) VALUES (
    'ORD20240115001234', 123, 'PENDING', 'CARD',
    50000, 5000, 3000, 48000, 0,
    '홍길동', '010-1234-5678', '12345',
    '서울시 강남구', '테헤란로 123'
);

SET @order_id = LAST_INSERT_ID();

-- 2. 주문 상세 생성 및 재고 차감
INSERT INTO order_items (
    order_id, product_id, product_name, product_code,
    quantity, unit_price, discount_amount, total_price
)
SELECT 
    @order_id,
    p.product_id,
    p.product_name,
    p.product_code,
    c.quantity,
    COALESCE(p.discount_price, p.price),
    0,
    COALESCE(p.discount_price, p.price) * c.quantity
FROM cart_items c
JOIN products p ON c.product_id = p.product_id
WHERE c.member_id = 123 AND c.is_selected = TRUE;

-- 3. 재고 차감 (원자적 업데이트)
UPDATE products p
JOIN cart_items c ON p.product_id = c.product_id
SET p.stock_quantity = p.stock_quantity - c.quantity,
    p.sales_count = p.sales_count + c.quantity
WHERE c.member_id = 123 
    AND c.is_selected = TRUE
    AND p.stock_quantity >= c.quantity;

-- 4. 재고 부족 체크
SELECT COUNT(*) INTO @stock_check
FROM cart_items c
JOIN products p ON c.product_id = p.product_id
WHERE c.member_id = 123 
    AND c.is_selected = TRUE
    AND p.stock_quantity < 0;

-- 재고 부족 시 롤백
IF @stock_check > 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '재고가 부족합니다';
END IF;

-- 5. 포인트 사용 (사용한 경우)
-- UPDATE members SET point_balance = point_balance - 사용포인트
-- INSERT INTO point_histories ...

-- 6. 장바구니 비우기
DELETE FROM cart_items 
WHERE member_id = 123 AND is_selected = TRUE;

COMMIT;

-- 일별 매출 통계
SELECT 
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_payment_amount) AS total_sales,
    AVG(total_payment_amount) AS avg_order_value,
    COUNT(DISTINCT member_id) AS unique_customers
FROM orders
WHERE order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
    AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

-- 카테고리별 매출 순위
SELECT 
    c.category_name,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.total_price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
    AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY c.category_id, c.category_name
ORDER BY total_sales DESC
LIMIT 10;

-- 베스트셀러 상품 (최근 30일)
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.total_price) AS total_revenue,
    p.rating_average,
    p.review_count
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
    AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY p.product_id
ORDER BY total_sold DESC
LIMIT 20;

-- 회원 등급별 구매 패턴
SELECT 
    m.member_grade,
    COUNT(DISTINCT m.member_id) AS member_count,
    COUNT(o.order_id) AS total_orders,
    ROUND(COUNT(o.order_id) / COUNT(DISTINCT m.member_id), 2) AS avg_orders_per_member,
    SUM(o.total_payment_amount) AS total_sales,
    ROUND(AVG(o.total_payment_amount), 0) AS avg_order_value
FROM members m
LEFT JOIN orders o ON m.member_id = o.member_id 
    AND o.order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
    AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY m.member_grade
ORDER BY total_sales DESC;

-- RFM 분석 (Recency, Frequency, Monetary)
SELECT 
    m.member_id,
    m.email,
    m.name,
    DATEDIFF(CURDATE(), MAX(o.created_at)) AS recency_days,
    COUNT(o.order_id) AS frequency,
    SUM(o.total_payment_amount) AS monetary
FROM members m
LEFT JOIN orders o ON m.member_id = o.member_id
    AND o.order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
WHERE m.is_active = TRUE
GROUP BY m.member_id
HAVING frequency > 0
ORDER BY monetary DESC, frequency DESC, recency_days ASC;

-- 이탈 위험 고객 (최근 90일 구매 없음, 과거 구매 이력 있음)
SELECT 
    m.member_id,
    m.email,
    m.name,
    m.total_purchase_amount,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(CURDATE(), MAX(o.created_at)) AS days_since_last_order
FROM members m
JOIN orders o ON m.member_id = o.member_id
WHERE o.order_status IN ('PAID', 'PREPARING', 'SHIPPING', 'DELIVERED')
GROUP BY m.member_id
HAVING days_since_last_order > 90
ORDER BY m.total_purchase_amount DESC
LIMIT 100;

-- MySQL 백업 명령어 예시
-- 전체 백업
mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events > backup_$(date +%Y%m%d).sql

-- 특정 데이터베이스만 백업
mysqldump -u root -p shopping_mall --single-transaction \
  --routines --triggers > shopping_mall_$(date +%Y%m%d).sql

-- 복구
mysql -u root -p shopping_mall < backup_20240115.sql

-- 바이너리 로그 활성화 (my.cnf)
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=ROW
expire_logs_days=7