-- =====================================================================
-- 강의: AI 시대 DB 모델링 & SQL 완벽 마스터 : 구조를 읽고, AI를 검증하는 법
-- 실습용 샘플 데이터 — 쇼핑몰 도메인
--
-- 다시 실행해도 안전하도록 DROP DATABASE 부터 시작합니다.
-- DBeaver SQL Editor 에서 파일 열고 Alt+X (전체 실행) 한 번이면 됩니다.
-- =====================================================================

SET NAMES utf8mb4;

DROP DATABASE IF EXISTS shop;
CREATE DATABASE shop
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE shop;


-- =====================================================================
-- 1. 회원 (members)
-- =====================================================================
CREATE TABLE members (
  member_id   INT          NOT NULL AUTO_INCREMENT COMMENT '회원ID',
  member_name VARCHAR(50)  NOT NULL                COMMENT '이름',
  gender      CHAR(1)      NOT NULL                COMMENT '성별 (M/F)',
  age         INT          NOT NULL                COMMENT '나이',
  joined_at   DATE         NOT NULL                COMMENT '가입일',
  PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='회원';


-- =====================================================================
-- 2. 카테고리 (categories)
-- =====================================================================
CREATE TABLE categories (
  category_id   INT         NOT NULL AUTO_INCREMENT COMMENT '카테고리ID',
  category_name VARCHAR(50) NOT NULL                COMMENT '카테고리명',
  PRIMARY KEY (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='상품 카테고리';


-- =====================================================================
-- 3. 상품 (products)
--    - category_id 는 categories 의 자식 (FK)
-- =====================================================================
CREATE TABLE products (
  product_id   INT          NOT NULL AUTO_INCREMENT COMMENT '상품ID',
  product_name VARCHAR(100) NOT NULL                COMMENT '상품명',
  price        INT          NOT NULL                COMMENT '가격(원)',
  category_id  INT          NOT NULL                COMMENT '카테고리ID(FK)',
  PRIMARY KEY (product_id),
  CONSTRAINT fk_products_category
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='상품';


-- =====================================================================
-- 4. 주문 (orders)
--    - member_id 는 members 의 자식 (FK)
-- =====================================================================
CREATE TABLE orders (
  order_id   INT  NOT NULL AUTO_INCREMENT COMMENT '주문ID',
  member_id  INT  NOT NULL                COMMENT '회원ID(FK)',
  ordered_at DATE NOT NULL                COMMENT '주문일',
  PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_member
    FOREIGN KEY (member_id) REFERENCES members(member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='주문';


-- =====================================================================
-- 5. 주문상세 (order_items)
--    - orders 와 products 사이 N:M 관계를 풀어주는 테이블
--    - PK 가 (order_id, product_id) 복합키 → 한 주문에 같은 상품 중복 X
-- =====================================================================
CREATE TABLE order_items (
  order_id   INT NOT NULL COMMENT '주문ID(FK)',
  product_id INT NOT NULL COMMENT '상품ID(FK)',
  quantity   INT NOT NULL COMMENT '수량',
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_oi_order
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
  CONSTRAINT fk_oi_product
    FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='주문상세';


-- =====================================================================
-- 데이터 적재 — 회원 20명 (16명은 주문 있음, 마지막 4명은 주문 없음)
-- =====================================================================
INSERT INTO members (member_id, member_name, gender, age, joined_at) VALUES
  (1,  '김철수', 'M', 35, '2025-01-15'),
  (2,  '이영희', 'F', 28, '2025-02-03'),
  (3,  '박민수', 'M', 42, '2025-02-10'),
  (4,  '정수진', 'F', 31, '2025-03-05'),
  (5,  '최지훈', 'M', 25, '2025-03-20'),
  (6,  '강서연', 'F', 38, '2025-04-12'),
  (7,  '윤도현', 'M', 29, '2025-05-08'),
  (8,  '임하늘', 'F', 33, '2025-06-15'),
  (9,  '조성민', 'M', 45, '2025-07-22'),
  (10, '한예원', 'F', 27, '2025-08-30'),
  (11, '신우진', 'M', 22, '2025-09-14'),
  (12, '오지은', 'F', 40, '2025-10-05'),
  (13, '배준호', 'M', 36, '2025-11-18'),
  (14, '송민지', 'F', 24, '2025-12-22'),
  (15, '권현석', 'M', 50, '2026-01-08'),
  (16, '노수아', 'F', 30, '2026-02-15'),
  (17, '황태웅', 'M', 26, '2026-02-28'),  -- 주문 없음
  (18, '안소영', 'F', 44, '2026-03-10'),  -- 주문 없음
  (19, '류재민', 'M', 32, '2026-03-25'),  -- 주문 없음
  (20, '백서윤', 'F', 21, '2026-04-02');  -- 주문 없음 (최근 가입자)


-- =====================================================================
-- 데이터 적재 — 카테고리 5개
-- =====================================================================
INSERT INTO categories (category_id, category_name) VALUES
  (1, '의류'),
  (2, '전자기기'),
  (3, '식품'),
  (4, '도서'),
  (5, '뷰티');


-- =====================================================================
-- 데이터 적재 — 상품 30개
--   카테고리별: 의류 8 / 전자기기 7 / 식품 6 / 도서 5 / 뷰티 4
-- =====================================================================
INSERT INTO products (product_id, product_name, price, category_id) VALUES
  -- 의류 (1~8)
  (1,  '라운드 티셔츠',     19000, 1),
  (2,  '슬림핏 청바지',     49000, 1),
  (3,  '후드 집업',         65000, 1),
  (4,  '면 셔츠',           39000, 1),
  (5,  '면바지',            42000, 1),
  (6,  '카디건',            55000, 1),
  (7,  '니트',              48000, 1),
  (8,  '패딩 점퍼',        129000, 1),
  -- 전자기기 (9~15)
  (9,  '무선 이어폰',       89000, 2),
  (10, '블루투스 스피커',   65000, 2),
  (11, '보조배터리',        25000, 2),
  (12, 'USB-C 케이블',       8000, 2),
  (13, '무선 마우스',       32000, 2),
  (14, '노트북 거치대',     28000, 2),
  (15, '키보드',            75000, 2),
  -- 식품 (16~21)
  (16, '프리미엄 그래놀라', 12000, 3),
  (17, '유기농 견과류',     18000, 3),
  (18, '콜드브루 원두',     22000, 3),
  (19, '트러플 오일',       35000, 3),
  (20, '다크 초콜릿',        9000, 3),
  (21, '유기농 꿀',         28000, 3),
  -- 도서 (22~26)
  (22, 'SQL 입문서',        24000, 4),
  (23, '데이터 모델링 실전', 32000, 4),
  (24, 'AI 시대 개발자',    28000, 4),
  (25, '클린 코드',         35000, 4),
  (26, '프로그래밍 기초',   22000, 4),
  -- 뷰티 (27~30)
  (27, '수분 크림',         32000, 5),
  (28, '클렌징 폼',         18000, 5),
  (29, '향수',              89000, 5),
  (30, '립스틱',            25000, 5);


-- =====================================================================
-- 데이터 적재 — 주문 40건 (회원 1~16번이 주문)
--   주문일: 2025-02 ~ 2026-04 분산
-- =====================================================================
INSERT INTO orders (order_id, member_id, ordered_at) VALUES
  (1,  1,  '2025-02-01'),
  (2,  2,  '2025-02-15'),
  (3,  3,  '2025-03-01'),
  (4,  4,  '2025-03-15'),
  (5,  5,  '2025-04-01'),
  (6,  1,  '2025-04-10'),
  (7,  6,  '2025-04-20'),
  (8,  2,  '2025-05-05'),
  (9,  7,  '2025-05-25'),
  (10, 9,  '2025-06-01'),
  (11, 4,  '2025-06-15'),
  (12, 3,  '2025-07-02'),
  (13, 10, '2025-07-15'),
  (14, 1,  '2025-08-01'),
  (15, 8,  '2025-08-10'),
  (16, 9,  '2025-08-22'),
  (17, 6,  '2025-09-05'),
  (18, 2,  '2025-09-15'),
  (19, 11, '2025-09-30'),
  (20, 1,  '2025-10-12'),
  (21, 12, '2025-10-20'),
  (22, 13, '2025-10-30'),
  (23, 14, '2025-11-08'),
  (24, 15, '2025-11-15'),
  (25, 4,  '2025-11-22'),
  (26, 3,  '2025-12-01'),
  (27, 1,  '2025-12-10'),
  (28, 9,  '2025-12-18'),
  (29, 7,  '2025-12-28'),
  (30, 6,  '2026-01-05'),
  (31, 10, '2026-01-15'),
  (32, 16, '2026-01-25'),
  (33, 2,  '2026-02-05'),
  (34, 1,  '2026-02-15'),
  (35, 12, '2026-02-22'),
  (36, 14, '2026-03-01'),
  (37, 15, '2026-03-08'),
  (38, 13, '2026-03-15'),
  (39, 12, '2026-03-25'),
  (40, 9,  '2026-04-02');


-- =====================================================================
-- 데이터 적재 — 주문상세 (한 주문에 1~3 상품, 수량 1~3)
--   * 수량이 2~3 인 주문상세가 일부 섞여 있음 → JOIN 뻥튀기 함정 시연용
-- =====================================================================
INSERT INTO order_items (order_id, product_id, quantity) VALUES
  -- order 1: 김철수
  (1,  1,  1),
  (1,  16, 2),
  -- order 2: 이영희
  (2,  22, 1),
  -- order 3: 박민수
  (3,  9,  1),
  (3,  11, 1),
  -- order 4: 정수진
  (4,  27, 1),
  (4,  30, 1),
  -- order 5: 최지훈
  (5,  23, 1),
  -- order 6: 김철수
  (6,  9,  1),
  -- order 7: 강서연
  (7,  1,  2),
  (7,  7,  1),
  -- order 8: 이영희
  (8,  22, 1),
  (8,  24, 1),
  -- order 9: 윤도현
  (9,  13, 1),
  -- order 10: 조성민  ← 수량 3 (JOIN 뻥튀기 시연용)
  (10, 8,  1),
  (10, 4,  1),
  (10, 16, 3),
  -- order 11: 정수진
  (11, 27, 2),
  -- order 12: 박민수
  (12, 9,  1),
  (12, 12, 3),
  -- order 13: 한예원
  (13, 28, 1),
  -- order 14: 김철수 (책 3권)
  (14, 22, 1),
  (14, 23, 1),
  (14, 24, 1),
  -- order 15: 임하늘
  (15, 1,  1),
  -- order 16: 조성민
  (16, 11, 2),
  -- order 17: 강서연
  (17, 22, 1),
  -- order 18: 이영희
  (18, 27, 1),
  (18, 30, 1),
  -- order 19: 신우진
  (19, 9,  1),
  -- order 20: 김철수
  (20, 8,  1),
  -- order 21: 오지은
  (21, 7,  1),
  (21, 6,  1),
  -- order 22: 배준호
  (22, 9,  1),
  (22, 11, 1),
  -- order 23: 송민지
  (23, 1,  2),
  -- order 24: 권현석
  (24, 22, 1),
  -- order 25: 정수진
  (25, 27, 1),
  (25, 28, 1),
  (25, 30, 1),
  -- order 26: 박민수
  (26, 8,  1),
  -- order 27: 김철수
  (27, 9,  1),
  (27, 13, 1),
  -- order 28: 조성민
  (28, 8,  2),
  (28, 23, 1),
  -- order 29: 윤도현
  (29, 13, 1),
  (29, 15, 1),
  -- order 30: 강서연
  (30, 6,  1),
  -- order 31: 한예원
  (31, 27, 1),
  (31, 29, 1),
  -- order 32: 노수아
  (32, 22, 1),
  -- order 33: 이영희
  (33, 23, 1),
  -- order 34: 김철수
  (34, 23, 1),
  -- order 35: 오지은
  (35, 27, 1),
  (35, 28, 1),
  -- order 36: 송민지
  (36, 28, 2),
  -- order 37: 권현석
  (37, 24, 1),
  (37, 25, 1),
  -- order 38: 배준호
  (38, 14, 1),
  -- order 39: 오지은
  (39, 6,  1),
  (39, 7,  1),
  (39, 27, 1),
  -- order 40: 조성민
  (40, 22, 1),
  (40, 23, 1);


-- =====================================================================
-- 적재 결과 확인 — 셀프 점검용 (실행해도 되고 무시해도 됨)
-- =====================================================================
SELECT '회원'      AS table_name, COUNT(*) AS row_count FROM members
UNION ALL
SELECT '카테고리', COUNT(*) FROM categories
UNION ALL
SELECT '상품',     COUNT(*) FROM products
UNION ALL
SELECT '주문',     COUNT(*) FROM orders
UNION ALL
SELECT '주문상세', COUNT(*) FROM order_items;
