1. DDL

-- 4) 코드 테이블: category (관리자 관리 코드)
CREATE TABLE category (
    category_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 1) 마스터: customer
CREATE TABLE customer (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 1) 마스터: product
CREATE TABLE product (
    product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL,
    name  VARCHAR(100) NOT NULL,
    price INT NOT NULL,
    stock INT NOT NULL,
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id) REFERENCES category(category_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 2) 트랜잭션: orders
CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 2) 트랜잭션: cart (고객당 1개 가정)
CREATE TABLE cart (
    cart_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL UNIQUE,
    CONSTRAINT fk_cart_customer
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 3) 구성: order_item
CREATE TABLE order_item (
    order_item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    qty INT NOT NULL,
    price_at_order INT NOT NULL,
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id) REFERENCES product(product_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 3) 구성: cart_item
CREATE TABLE cart_item (
    cart_item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    cart_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    qty INT NOT NULL,
    CONSTRAINT fk_cart_item_cart
        FOREIGN KEY (cart_id) REFERENCES cart(cart_id),
    CONSTRAINT fk_cart_item_product
        FOREIGN KEY (product_id) REFERENCES product(product_id),
    UNIQUE KEY uq_cart_item (cart_id, product_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 5) 이력: login_log (INSERT-only)
CREATE TABLE login_log (
    login_log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_login_log_customer
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

-- 6) 스냅샷: order_snapshot (orders 1:1)
CREATE TABLE order_snapshot (
    order_id BIGINT PRIMARY KEY,
    total_amount INT NOT NULL,
    snapshot_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_snapshot_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

2. DML

✅ 1️⃣ 코드 테이블: category (관리자 관리)

INSERT INTO category (name)VALUES
('가방'),
('아우터'),
('신발'),
('상의'),
('하의');

✅ 2️⃣ 마스터: customer

INSERT INTO customer (name, email)VALUES
('홍길동','[email protected]'),
('이순신','[email protected]'),
('강감찬','[email protected]'),
('유관순','[email protected]'),
('김구','[email protected]');

✅ 3️⃣ 마스터: product

INSERT INTO product (category_id, name, price, stock)VALUES
(1,'백팩',50000,10),
(1,'크로스백',70000,5),
(2,'패딩',150000,3),
(3,'운동화',120000,7),
(5,'청바지',60000,8);

✅ 4️⃣ 트랜잭션: cart (고객당 1개)

INSERT INTO cart (customer_id)VALUES
(1),
(2),
(3),
(4),
(5);

✅ 5️⃣ 구성: cart_item

INSERT INTO cart_item (cart_id, product_id, qty)VALUES
(1,1,1),
(1,4,1),
(2,2,2),
(3,3,1),
(4,5,3);

✅ 6️⃣ 트랜잭션: orders