#Step 13 — 제약 조건과 정규화

USE shop;

-- 사본 만들기 예시 (구조만 복사, 데이터는 안 옴)
CREATE TABLE s13_orders LIKE orders;

-- 구조 + 데이터까지
CREATE TABLE s13_orders_full AS SELECT * FROM orders;

DROP TABLE IF EXISTS s13_members;
CREATE TABLE s13_members (
  member_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email      VARCHAR(120) NOT NULL,                       -- 필수
  nickname   VARCHAR(30)  NOT NULL DEFAULT '익명',         -- 필수지만 기본값 있음
  grade      ENUM('BRONZE','SILVER','GOLD') NOT NULL DEFAULT 'BRONZE',
  points     INT          NOT NULL DEFAULT 0,
  memo       VARCHAR(100) NULL,                           -- 선택 (NULL 허용)
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (member_id)
) ENGINE=InnoDB;

-- email 만 주고 나머지는 DEFAULT 에 맡긴다
INSERT INTO s13_members (email) VALUES ('a@ex.com');
SELECT member_id, email, nickname, grade, points, memo FROM s13_members;

INSERT INTO s13_members (email, nickname) VALUES ('b@ex.com', NULL);

INSERT INTO s13_members (email, nickname, points) VALUES ('c@ex.com', DEFAULT, DEFAULT);
SELECT member_id, email, nickname, points FROM s13_members;

INSERT INTO s13_members (member_id, email) VALUES (1, 'dup@ex.com');

DROP TABLE IF EXISTS s13_uk;
CREATE TABLE s13_uk (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  UNIQUE KEY uk_code (code)
) ENGINE=InnoDB;

INSERT INTO s13_uk (code) VALUES ('A'), (NULL), (NULL), (NULL);   -- NULL 3개 성공
SELECT id, code FROM s13_uk;

INSERT INTO s13_uk (code) VALUES ('A');

DROP TABLE IF EXISTS s13_uk2;
CREATE TABLE s13_uk2 (
  cart_id    INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty        INT NOT NULL,
  UNIQUE KEY uk_cart_product (cart_id, product_id)
) ENGINE=InnoDB;

INSERT INTO s13_uk2 VALUES (1, 10, 2), (1, 11, 1), (2, 10, 5);  -- OK
INSERT INTO s13_uk2 VALUES (1, 10, 9);                          -- (1,10) 중복

DROP TABLE IF EXISTS s13_products;
CREATE TABLE s13_products (
  product_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(100) NOT NULL,
  price         DECIMAL(10,2) NOT NULL,
  cost          DECIMAL(10,2) NOT NULL,
  stock         INT NOT NULL DEFAULT 0,
  discount_rate TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id),
  CONSTRAINT chk_s13_price_pos CHECK (price >= 0),
  CONSTRAINT chk_s13_stock_pos CHECK (stock >= 0),
  CONSTRAINT chk_s13_margin    CHECK (price >= cost),          -- 컬럼끼리 비교 가능
  CONSTRAINT chk_s13_discount  CHECK (discount_rate BETWEEN 0 AND 90)
) ENGINE=InnoDB;

INSERT INTO s13_products (name, price, cost, stock, discount_rate)
VALUES ('정상상품', 10000, 6000, 10, 20);      -- OK

INSERT INTO s13_products (name, price, cost) VALUES ('역마진상품', 5000, 9000);

UPDATE s13_products SET stock = stock - 100 WHERE product_id = 1;   -- 10 - 100 = -90

DROP TABLE IF EXISTS s13_chk_null;
CREATE TABLE s13_chk_null (
  id  INT AUTO_INCREMENT PRIMARY KEY,
  age INT NULL,
  CONSTRAINT chk_age CHECK (age >= 18)
) ENGINE=InnoDB;

INSERT INTO s13_chk_null (age) VALUES (20), (NULL);   -- 둘 다 성공!
SELECT id, age FROM s13_chk_null;

INSERT INTO s13_chk_null (age) VALUES (10);

SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE, tc.ENFORCED
FROM information_schema.CHECK_CONSTRAINTS cc
JOIN information_schema.TABLE_CONSTRAINTS tc
  ON tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
 AND tc.CONSTRAINT_NAME   = cc.CONSTRAINT_NAME
WHERE cc.CONSTRAINT_SCHEMA = 'shop' AND tc.TABLE_NAME = 's13_products';

ALTER TABLE s13_products ALTER CHECK chk_s13_margin NOT ENFORCED;
INSERT INTO s13_products (name, price, cost) VALUES ('역마진 허용됨', 5000, 9000);  -- 통과!
SELECT product_id, name, price, cost FROM s13_products;

ALTER TABLE s13_products ALTER CHECK chk_s13_margin ENFORCED;

ALTER TABLE s13_products ADD CONSTRAINT chk_s13_name_len CHECK (CHAR_LENGTH(name) >= 2);
ALTER TABLE s13_products DROP CHECK chk_s13_name_len;

DROP TABLE IF EXISTS s13_child;  DROP TABLE IF EXISTS s13_parent;

CREATE TABLE s13_parent (
  parent_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(30) NOT NULL,
  PRIMARY KEY (parent_id)
) ENGINE=InnoDB;

CREATE TABLE s13_child (
  child_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NULL,
  name      VARCHAR(30) NOT NULL,
  PRIMARY KEY (child_id),
  CONSTRAINT fk_s13_child_parent
    FOREIGN KEY (parent_id) REFERENCES s13_parent(parent_id)
) ENGINE=InnoDB;

INSERT INTO s13_parent (parent_id, name) VALUES (1,'P1'), (2,'P2');
INSERT INTO s13_child (parent_id, name) VALUES (1,'C1'), (1,'C2'), (2,'C3');

-- 부모에 없는 99 를 넣으면?
INSERT INTO s13_child (parent_id, name) VALUES (99, 'C99');

DELETE FROM s13_parent WHERE parent_id = 1;

SHOW INDEX FROM s13_child;

CREATE TABLE s13_cat (
  cat_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name   VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE s13_post (
  post_id INT AUTO_INCREMENT PRIMARY KEY,
  cat_id  INT UNSIGNED NOT NULL,
  title   VARCHAR(30) NOT NULL,
  CONSTRAINT fk_post_cat FOREIGN KEY (cat_id) REFERENCES s13_cat(cat_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_cat VALUES (1,'공지'),(2,'자유');
INSERT INTO s13_post (cat_id,title) VALUES (1,'공지1'),(1,'공지2'),(2,'자유1');

-- ON UPDATE CASCADE: 부모 PK 를 바꾸면 자식 FK 도 따라간다
UPDATE s13_cat SET cat_id = 10 WHERE cat_id = 1;
SELECT post_id, cat_id, title FROM s13_post ORDER BY post_id;

-- ON DELETE CASCADE: 부모를 지우면 자식도 사라진다
DELETE FROM s13_cat WHERE cat_id = 10;
SELECT post_id, cat_id, title FROM s13_post ORDER BY post_id;

CREATE TABLE s13_dept (
  dept_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name    VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE s13_emp (
  emp_id  INT AUTO_INCREMENT PRIMARY KEY,
  dept_id INT UNSIGNED NULL,                 -- NULL 허용이어야 함!
  name    VARCHAR(20) NOT NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES s13_dept(dept_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_dept VALUES (1,'개발'),(2,'영업');
INSERT INTO s13_emp (dept_id,name) VALUES (1,'김코드'),(1,'박서버'),(2,'이세일');

DELETE FROM s13_dept WHERE dept_id = 1;      -- 부서가 없어져도 사원은 남는다
SELECT emp_id, dept_id, name FROM s13_emp ORDER BY emp_id;

CREATE TABLE s13_fk_bad (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dept_id INT UNSIGNED NOT NULL,                       -- NOT NULL 인데
  CONSTRAINT fk_bad FOREIGN KEY (dept_id) REFERENCES s13_dept(dept_id)
    ON DELETE SET NULL                                 -- SET NULL?
) ENGINE=InnoDB;

CREATE TABLE s13_prod (
  prod_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name    VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE s13_order_line (
  line_id INT AUTO_INCREMENT PRIMARY KEY,
  prod_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_line_prod FOREIGN KEY (prod_id) REFERENCES s13_prod(prod_id)
    ON DELETE RESTRICT ON UPDATE NO ACTION
) ENGINE=InnoDB;

INSERT INTO s13_prod VALUES (1,'상품A');
INSERT INTO s13_order_line (prod_id) VALUES (1);

DELETE FROM s13_prod WHERE prod_id = 1;             -- RESTRICT
UPDATE s13_prod SET prod_id = 9 WHERE prod_id = 1;  -- NO ACTION

CREATE TABLE s13_fk_setdefault (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  prod_id INT UNSIGNED NOT NULL DEFAULT 1,
  CONSTRAINT fk_sd FOREIGN KEY (prod_id) REFERENCES s13_prod(prod_id)
    ON DELETE SET DEFAULT
) ENGINE=InnoDB;
-- 테이블 생성 성공! (에러 없음)

INSERT INTO s13_prod VALUES (2,'상품B');
INSERT INTO s13_fk_setdefault (prod_id) VALUES (2);

DELETE FROM s13_prod WHERE prod_id = 2;

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA='shop' AND TABLE_NAME LIKE 's13%'
ORDER BY TABLE_NAME;

CREATE TABLE s13_fk_on (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amt INT NOT NULL,
  KEY idx_cust (customer_id),
  CONSTRAINT fk_s13_on FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB;

CREATE TABLE s13_fk_off (       -- 구조 동일, FK 만 없음
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amt INT NOT NULL,
  KEY idx_cust (customer_id)
) ENGINE=InnoDB;

INSERT INTO s13_fk_on (customer_id, amt)
SELECT 1 + (a.n * 7 + b.n) % 30, (a.n * b.n) % 1000
FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;

INSERT INTO s13_fk_off (customer_id, amt)
SELECT 1 + (a.n * 7 + b.n) % 30, (a.n * b.n) % 1000
FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;

CREATE TABLE s13_cas_parent (id INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE s13_cas_child (
  cid INT AUTO_INCREMENT PRIMARY KEY,
  pid INT UNSIGNED NOT NULL,
  pad CHAR(100) NOT NULL DEFAULT 'x',
  KEY idx_pid (pid),
  CONSTRAINT fk_cas_c FOREIGN KEY (pid) REFERENCES s13_cas_parent(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_cas_parent SELECT n FROM tally WHERE n <= 10;
INSERT INTO s13_cas_child (pid)
SELECT 1 + (a.n * 3 + b.n) % 10 FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;
-- 부모 10행, 자식 20만행 (부모 1행당 자식 2만행)

DELETE FROM s13_cas_parent WHERE id = 1;   -- 딱 1행 삭제!

-- 비교: 자식 2만 행을 직접 지우면?
DELETE FROM s13_cas_child WHERE pid = 2;

DROP TABLE IF EXISTS s13_ai;
CREATE TABLE s13_ai (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_code (code)
) ENGINE=InnoDB;

INSERT INTO s13_ai (code) VALUES ('a'),('b'),('c');   -- id 1,2,3

START TRANSACTION;
INSERT INTO s13_ai (code) VALUES ('rollback-me');   -- id 4 를 가져감
ROLLBACK;                                            -- 행은 사라지지만...
INSERT INTO s13_ai (code) VALUES ('d');
SELECT id, code FROM s13_ai ORDER BY id;

INSERT INTO s13_ai (code) VALUES ('a');   -- UNIQUE 위반 → 실패, 그래도 6번을 가져감
-- ERROR 1062 (23000): Duplicate entry 'a' for key 's13_ai.uk_code'

INSERT INTO s13_ai (code) VALUES ('d2');  -- 다음 id 는 6 이 아니라 7
SELECT id, code FROM s13_ai ORDER BY id;

INSERT INTO s13_ai (code) VALUES ('a') ON DUPLICATE KEY UPDATE code = VALUES(code);  -- 8 소모
INSERT INTO s13_ai (code) VALUES ('e');
SELECT id, code FROM s13_ai ORDER BY id;

DELETE FROM s13_ai;                            -- 전부 삭제 (직전 최대 id = 12)
INSERT INTO s13_ai (code) VALUES ('after-delete');
SELECT id, code FROM s13_ai;

TRUNCATE TABLE s13_ai;                         -- 전부 삭제 + 카운터 리셋
INSERT INTO s13_ai (code) VALUES ('after-truncate');
SELECT id, code FROM s13_ai;

ALTER TABLE s13_ai AUTO_INCREMENT = 1000;
INSERT INTO s13_ai (code) VALUES ('from-1000');
SELECT id, code FROM s13_ai;

-- 정규화 원칙대로라면 total_amount 는 저장하면 안 되고 매번 계산해야 함
SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id;

-- 하지만 실제로는 컬럼에 저장해 뒀다
SELECT order_id, total_amount FROM orders;

-- 이 스텝에서 만든 사본 테이블 정리
DROP TABLE IF EXISTS s13_post, s13_cat, s13_emp, s13_dept,
                     s13_order_line, s13_fk_setdefault, s13_prod,
                     s13_cas_child, s13_cas_parent,
                     s13_child, s13_parent,
                     s13_fk_on, s13_fk_off,
                     s13_members, s13_uk, s13_uk2,
                     s13_products, s13_chk_null, s13_ai;

-- =====================================================================
-- Step 13 — 제약 조건과 정규화 : practice.sql
-- ---------------------------------------------------------------------
-- README 의 예제를 순서대로 담았습니다. 위에서부터 실행하세요.
--
-- 실행:  mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 --force shop < practice.sql
--        ( --force : 일부러 에러를 내는 예제가 있으므로 계속 진행시킵니다 )
--
-- ⚠️ 공용 테이블(customers/products/orders/...)은 절대 수정하지 않습니다.
--    모든 실습은 s13_ 접두사 사본 테이블에서 합니다.
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- [13-0] 실습 준비 — 사본 테이블 만들기
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_orders;
CREATE TABLE s13_orders LIKE orders;              -- 구조 + 인덱스 + 제약 복사
SHOW INDEX FROM s13_orders;                       -- PK / idx_orders_customer 가 그대로 복사됨

DROP TABLE IF EXISTS s13_orders_full;
CREATE TABLE s13_orders_full AS SELECT * FROM orders;   -- 데이터는 오지만 PK/인덱스는 안 옴!
SHOW INDEX FROM s13_orders_full;                  -- 인덱스가 하나도 없음을 확인

DROP TABLE IF EXISTS s13_orders, s13_orders_full;


-- ---------------------------------------------------------------------
-- [13-2] NOT NULL 과 DEFAULT
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_members;
CREATE TABLE s13_members (
  member_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email      VARCHAR(120) NOT NULL,
  nickname   VARCHAR(30)  NOT NULL DEFAULT '익명',
  grade      ENUM('BRONZE','SILVER','GOLD') NOT NULL DEFAULT 'BRONZE',
  points     INT          NOT NULL DEFAULT 0,
  memo       VARCHAR(100) NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (member_id)
) ENGINE=InnoDB;

-- email 만 주고 나머지는 DEFAULT 에 맡긴다
INSERT INTO s13_members (email) VALUES ('a@ex.com');
SELECT member_id, email, nickname, grade, points, memo FROM s13_members;

-- NOT NULL 위반 : DEFAULT 가 있어도 "명시적 NULL" 은 거부된다
--   → ERROR 1048: Column 'nickname' cannot be null
INSERT INTO s13_members (email, nickname) VALUES ('b@ex.com', NULL);

-- DEFAULT 키워드로 기본값 요청
INSERT INTO s13_members (email, nickname, points) VALUES ('c@ex.com', DEFAULT, DEFAULT);
SELECT member_id, email, nickname, points FROM s13_members;


-- ---------------------------------------------------------------------
-- [13-3] PRIMARY KEY 와 UNIQUE — 결정적 차이는 NULL
-- ---------------------------------------------------------------------
-- PK 중복 → ERROR 1062
INSERT INTO s13_members (member_id, email) VALUES (1, 'dup@ex.com');

-- UNIQUE 는 NULL 을 몇 개든 허용한다
DROP TABLE IF EXISTS s13_uk;
CREATE TABLE s13_uk (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  UNIQUE KEY uk_code (code)
) ENGINE=InnoDB;

INSERT INTO s13_uk (code) VALUES ('A'), (NULL), (NULL), (NULL);   -- NULL 3개 모두 성공
SELECT id, code FROM s13_uk;

-- NULL 이 아닌 값의 중복은 막힌다 → ERROR 1062
INSERT INTO s13_uk (code) VALUES ('A');

-- 복합 UNIQUE : "한 장바구니에 같은 상품은 한 줄만"
DROP TABLE IF EXISTS s13_uk2;
CREATE TABLE s13_uk2 (
  cart_id    INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty        INT NOT NULL,
  UNIQUE KEY uk_cart_product (cart_id, product_id)
) ENGINE=InnoDB;

INSERT INTO s13_uk2 VALUES (1, 10, 2), (1, 11, 1), (2, 10, 5);   -- OK
INSERT INTO s13_uk2 VALUES (1, 10, 9);   -- (1,10) 중복 → ERROR 1062


-- ---------------------------------------------------------------------
-- [13-4] CHECK 제약 — MySQL 8.0.16+ 부터 실제로 강제된다
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_products;
CREATE TABLE s13_products (
  product_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(100) NOT NULL,
  price         DECIMAL(10,2) NOT NULL,
  cost          DECIMAL(10,2) NOT NULL,
  stock         INT NOT NULL DEFAULT 0,
  discount_rate TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id),
  CONSTRAINT chk_s13_price_pos CHECK (price >= 0),
  CONSTRAINT chk_s13_stock_pos CHECK (stock >= 0),
  CONSTRAINT chk_s13_margin    CHECK (price >= cost),            -- 컬럼끼리 비교 가능
  CONSTRAINT chk_s13_discount  CHECK (discount_rate BETWEEN 0 AND 90)
) ENGINE=InnoDB;

INSERT INTO s13_products (name, price, cost, stock, discount_rate)
VALUES ('정상상품', 10000, 6000, 10, 20);                        -- OK

-- price >= cost 위반 → ERROR 3819
INSERT INTO s13_products (name, price, cost) VALUES ('역마진상품', 5000, 9000);

-- discount 범위 위반 → ERROR 3819
INSERT INTO s13_products (name, price, cost, discount_rate) VALUES ('과할인', 10000, 1000, 95);

-- CHECK 는 UPDATE 에서도 검사된다 → ERROR 3819
UPDATE s13_products SET stock = stock - 100 WHERE product_id = 1;

-- CHECK 와 NULL : 결과가 UNKNOWN 이면 "통과"한다 (함정!)
DROP TABLE IF EXISTS s13_chk_null;
CREATE TABLE s13_chk_null (
  id  INT AUTO_INCREMENT PRIMARY KEY,
  age INT NULL,
  CONSTRAINT chk_age CHECK (age >= 18)
) ENGINE=InnoDB;

INSERT INTO s13_chk_null (age) VALUES (20), (NULL);   -- 둘 다 성공! NULL 이 통과한다
SELECT id, age FROM s13_chk_null;
INSERT INTO s13_chk_null (age) VALUES (10);           -- 이건 거부 → ERROR 3819

-- CHECK 제약 조회
SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE, tc.ENFORCED
FROM information_schema.CHECK_CONSTRAINTS cc
JOIN information_schema.TABLE_CONSTRAINTS tc
  ON tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
 AND tc.CONSTRAINT_NAME   = cc.CONSTRAINT_NAME
WHERE cc.CONSTRAINT_SCHEMA = 'shop' AND tc.TABLE_NAME = 's13_products';

-- 일시 해제 (NOT ENFORCED)
ALTER TABLE s13_products ALTER CHECK chk_s13_margin NOT ENFORCED;
INSERT INTO s13_products (name, price, cost) VALUES ('역마진 허용됨', 5000, 9000);  -- 이제 통과
SELECT product_id, name, price, cost FROM s13_products;

-- 함정: 다시 켜려면 기존 데이터를 재검사한다 → ERROR 3819
ALTER TABLE s13_products ALTER CHECK chk_s13_margin ENFORCED;

-- 위반 행을 치우고 나서야 다시 켤 수 있다
DELETE FROM s13_products WHERE name = '역마진 허용됨';
ALTER TABLE s13_products ALTER CHECK chk_s13_margin ENFORCED;   -- 이제 성공

-- CHECK 추가 / 삭제
ALTER TABLE s13_products ADD CONSTRAINT chk_s13_name_len CHECK (CHAR_LENGTH(name) >= 2);
ALTER TABLE s13_products DROP CHECK chk_s13_name_len;


-- ---------------------------------------------------------------------
-- [13-5] FOREIGN KEY 기본
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_child;
DROP TABLE IF EXISTS s13_parent;

CREATE TABLE s13_parent (
  parent_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(30) NOT NULL,
  PRIMARY KEY (parent_id)
) ENGINE=InnoDB;

CREATE TABLE s13_child (
  child_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NULL,
  name      VARCHAR(30) NOT NULL,
  PRIMARY KEY (child_id),
  CONSTRAINT fk_s13_child_parent
    FOREIGN KEY (parent_id) REFERENCES s13_parent(parent_id)   -- 참조 동작 생략 = RESTRICT
) ENGINE=InnoDB;

INSERT INTO s13_parent (parent_id, name) VALUES (1,'P1'), (2,'P2');
INSERT INTO s13_child (parent_id, name) VALUES (1,'C1'), (1,'C2'), (2,'C3');

-- 부모에 없는 값 INSERT → ERROR 1452
INSERT INTO s13_child (parent_id, name) VALUES (99, 'C99');

-- 자식이 있는 부모 DELETE → ERROR 1451 (기본 = RESTRICT)
DELETE FROM s13_parent WHERE parent_id = 1;

-- FK 컬럼에 인덱스가 자동으로 생성된다 (fk_s13_child_parent)
SHOW INDEX FROM s13_child;


-- ---------------------------------------------------------------------
-- [13-5b] ON DELETE / ON UPDATE 5종
-- ---------------------------------------------------------------------

-- (1) CASCADE : 부모를 따라간다
DROP TABLE IF EXISTS s13_post;
DROP TABLE IF EXISTS s13_cat;
CREATE TABLE s13_cat (
  cat_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name   VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE s13_post (
  post_id INT AUTO_INCREMENT PRIMARY KEY,
  cat_id  INT UNSIGNED NOT NULL,
  title   VARCHAR(30) NOT NULL,
  CONSTRAINT fk_post_cat FOREIGN KEY (cat_id) REFERENCES s13_cat(cat_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_cat VALUES (1,'공지'),(2,'자유');
INSERT INTO s13_post (cat_id,title) VALUES (1,'공지1'),(1,'공지2'),(2,'자유1');

-- ON UPDATE CASCADE : 부모 PK 를 바꾸면 자식 FK 도 따라 바뀐다
UPDATE s13_cat SET cat_id = 10 WHERE cat_id = 1;
SELECT post_id, cat_id, title FROM s13_post ORDER BY post_id;

-- ON DELETE CASCADE : 부모를 지우면 자식도 사라진다
DELETE FROM s13_cat WHERE cat_id = 10;
SELECT post_id, cat_id, title FROM s13_post ORDER BY post_id;   -- 공지1, 공지2 소멸


-- (2) SET NULL : 부모가 없어져도 자식은 남고 FK 만 NULL 이 된다
DROP TABLE IF EXISTS s13_emp;
DROP TABLE IF EXISTS s13_dept;
CREATE TABLE s13_dept (
  dept_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name    VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE s13_emp (
  emp_id  INT AUTO_INCREMENT PRIMARY KEY,
  dept_id INT UNSIGNED NULL,                    -- NULL 허용이어야 한다!
  name    VARCHAR(20) NOT NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES s13_dept(dept_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_dept VALUES (1,'개발'),(2,'영업');
INSERT INTO s13_emp (dept_id,name) VALUES (1,'김코드'),(1,'박서버'),(2,'이세일');

DELETE FROM s13_dept WHERE dept_id = 1;
SELECT emp_id, dept_id, name FROM s13_emp ORDER BY emp_id;      -- 김코드/박서버의 dept_id 가 NULL

-- (2b) SET NULL 인데 컬럼이 NOT NULL → 테이블 생성 자체가 실패 (ERROR 1830)
CREATE TABLE s13_fk_bad (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  dept_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_bad FOREIGN KEY (dept_id) REFERENCES s13_dept(dept_id) ON DELETE SET NULL
) ENGINE=InnoDB;


-- (3)(4) RESTRICT / NO ACTION : InnoDB 에서는 완전히 동일하다
DROP TABLE IF EXISTS s13_fk_setdefault;   -- 자식부터 지워야 부모를 지울 수 있다
DROP TABLE IF EXISTS s13_order_line;
DROP TABLE IF EXISTS s13_prod;
CREATE TABLE s13_prod (
  prod_id INT UNSIGNED NOT NULL PRIMARY KEY,
  name    VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE s13_order_line (
  line_id INT AUTO_INCREMENT PRIMARY KEY,
  prod_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_line_prod FOREIGN KEY (prod_id) REFERENCES s13_prod(prod_id)
    ON DELETE RESTRICT ON UPDATE NO ACTION
) ENGINE=InnoDB;

INSERT INTO s13_prod VALUES (1,'상품A');
INSERT INTO s13_order_line (prod_id) VALUES (1);

DELETE FROM s13_prod WHERE prod_id = 1;              -- RESTRICT  → ERROR 1451
UPDATE s13_prod SET prod_id = 9 WHERE prod_id = 1;   -- NO ACTION → ERROR 1451 (동일!)


-- (5) SET DEFAULT : 문법은 통과하지만 InnoDB 는 실행을 거부한다 (조용한 거짓말)
DROP TABLE IF EXISTS s13_fk_setdefault;
CREATE TABLE s13_fk_setdefault (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  prod_id INT UNSIGNED NOT NULL DEFAULT 1,
  CONSTRAINT fk_sd FOREIGN KEY (prod_id) REFERENCES s13_prod(prod_id)
    ON DELETE SET DEFAULT
) ENGINE=InnoDB;                                     -- 테이블 생성은 성공!

INSERT INTO s13_prod VALUES (2,'상품B');
INSERT INTO s13_fk_setdefault (prod_id) VALUES (2);

DELETE FROM s13_prod WHERE prod_id = 2;              -- 기본값으로 안 바뀌고 그냥 거부 → ERROR 1451

-- 참조 동작을 한눈에 확인
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'shop' AND TABLE_NAME LIKE 's13%'
ORDER BY TABLE_NAME;


-- ---------------------------------------------------------------------
-- [13-6] FK 가 성능과 락에 미치는 영향 — 실측
-- ---------------------------------------------------------------------

-- 실측 1 : FK 있음 vs 없음, 20만 행 INSERT
DROP TABLE IF EXISTS s13_fk_on;
DROP TABLE IF EXISTS s13_fk_off;

CREATE TABLE s13_fk_on (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amt         INT NOT NULL,
  KEY idx_cust (customer_id),
  CONSTRAINT fk_s13_on FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB;

CREATE TABLE s13_fk_off (          -- 구조 동일, FK 만 없음
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amt         INT NOT NULL,
  KEY idx_cust (customer_id)
) ENGINE=InnoDB;

-- FK 있음  → 0.662 sec
INSERT INTO s13_fk_on (customer_id, amt)
SELECT 1 + (a.n * 7 + b.n) % 30, (a.n * b.n) % 1000
FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;

-- FK 없음  → 0.627 sec  (약 5% 차이. 부모가 작고 캐시되어 있으면 FK 는 거의 공짜)
INSERT INTO s13_fk_off (customer_id, amt)
SELECT 1 + (a.n * 7 + b.n) % 30, (a.n * b.n) % 1000
FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;


-- 실측 2 : CASCADE 는 "1행 삭제"를 "2만행 삭제"로 바꾼다 (진짜 위험한 곳)
DROP TABLE IF EXISTS s13_cas_child;
DROP TABLE IF EXISTS s13_cas_parent;
CREATE TABLE s13_cas_parent (id INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE s13_cas_child (
  cid INT AUTO_INCREMENT PRIMARY KEY,
  pid INT UNSIGNED NOT NULL,
  pad CHAR(100) NOT NULL DEFAULT 'x',
  KEY idx_pid (pid),
  CONSTRAINT fk_cas_c FOREIGN KEY (pid) REFERENCES s13_cas_parent(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO s13_cas_parent SELECT n FROM tally WHERE n <= 10;
INSERT INTO s13_cas_child (pid)
SELECT 1 + (a.n * 3 + b.n) % 10 FROM tally a JOIN tally b ON b.n <= 20 WHERE a.n <= 10000;
SELECT COUNT(*) AS child_rows FROM s13_cas_child;      -- 200,000 (부모 1행당 2만행)

-- 부모 1행 삭제 → "Query OK, 1 row affected (0.057 sec)"  ...실제로는 2만행이 지워졌다!
DELETE FROM s13_cas_parent WHERE id = 1;
SELECT COUNT(*) AS remaining FROM s13_cas_child;        -- 180,000

-- 비교: 자식 2만행을 직접 삭제 → (0.055 sec)  ... 위와 거의 같은 시간! 이게 증거다
DELETE FROM s13_cas_child WHERE pid = 2;
SELECT COUNT(*) AS remaining FROM s13_cas_child;        -- 160,000


-- 실측 3 : FOREIGN_KEY_CHECKS = 0 은 "고아 행"을 남길 수 있다
SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO s13_child (parent_id, name) VALUES (777, '유령자식');   -- 부모 777 은 없다
SET FOREIGN_KEY_CHECKS = 1;                                          -- 다시 켜도...

-- ...기존 고아 행은 재검증되지 않는다! 직접 찾아야 한다
SELECT COUNT(*) AS orphan_rows
FROM s13_child c
LEFT JOIN s13_parent p ON p.parent_id = c.parent_id
WHERE c.parent_id IS NOT NULL AND p.parent_id IS NULL;               -- 1

DELETE FROM s13_child WHERE name = '유령자식';


-- ---------------------------------------------------------------------
-- [13-7] AUTO_INCREMENT 와 구멍(gap)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_ai;
CREATE TABLE s13_ai (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_code (code)
) ENGINE=InnoDB;

INSERT INTO s13_ai (code) VALUES ('a'),('b'),('c');       -- id 1, 2, 3

-- 구멍 1 : 롤백해도 카운터는 되돌아가지 않는다
START TRANSACTION;
INSERT INTO s13_ai (code) VALUES ('rollback-me');          -- id 4 를 가져감
ROLLBACK;                                                  -- 행은 사라지지만 4 는 안 돌아옴
INSERT INTO s13_ai (code) VALUES ('d');
SELECT id, code FROM s13_ai ORDER BY id;                   -- 1,2,3,5  ← 4 없음

-- 구멍 2 : 실패한 INSERT 도 카운터를 소모한다
INSERT INTO s13_ai (code) VALUES ('a');                    -- UNIQUE 위반 → ERROR 1062 (6 소모)
INSERT INTO s13_ai (code) VALUES ('d2');
SELECT id, code FROM s13_ai ORDER BY id;                   -- 1,2,3,5,7  ← 6 없음

SELECT AUTO_INCREMENT AS next_counter FROM information_schema.TABLES
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='s13_ai';         -- 8

-- ON DUPLICATE KEY UPDATE 도 카운터를 태운다 (upsert 배치가 id 를 폭주시키는 원인)
INSERT INTO s13_ai (code) VALUES ('a') ON DUPLICATE KEY UPDATE code = VALUES(code);   -- 8 소모
INSERT INTO s13_ai (code) VALUES ('e');
SELECT id, code FROM s13_ai ORDER BY id;                   -- 1,2,3,5,7,9  ← 8 없음

-- LAST_INSERT_ID() : 세션 단위 + 다중행 INSERT 는 "첫" 행의 id 를 준다
INSERT INTO s13_ai (code) VALUES ('g'),('h'),('i');        -- 10, 11, 12 가 들어감
SELECT LAST_INSERT_ID() AS last_id, ROW_COUNT() AS rows_inserted;   -- 10, 3  ← 12 가 아니다!

-- 구멍 3 : DELETE 는 카운터를 리셋하지 않는다
DELETE FROM s13_ai;
INSERT INTO s13_ai (code) VALUES ('after-delete');
SELECT id, code FROM s13_ai;                               -- id = 13 (비었는데도 이어감)

-- 하지만 TRUNCATE 는 리셋한다 (DELETE 와의 결정적 차이)
TRUNCATE TABLE s13_ai;
INSERT INTO s13_ai (code) VALUES ('after-truncate');
SELECT id, code FROM s13_ai;                               -- id = 1

-- 카운터 수동 조정
ALTER TABLE s13_ai AUTO_INCREMENT = 1000;
INSERT INTO s13_ai (code) VALUES ('from-1000');
SELECT id, code FROM s13_ai;                               -- 1, 1000


-- ---------------------------------------------------------------------
-- [13-8] 정규화 / 반정규화 — shop 스키마로 확인
-- ---------------------------------------------------------------------

-- 반정규화 컬럼 orders.total_amount 는 order_items 의 합계를 "복사"해 둔 것이다.
-- 정규화 원칙대로라면 매번 계산해야 한다:
SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS calculated
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id
LIMIT 5;

-- 하지만 실제로는 컬럼에 저장되어 있다 (읽기 성능을 위해):
SELECT order_id, total_amount FROM orders LIMIT 5;

-- 반정규화의 대가 : 두 값이 어긋나지 않았는지 "직접" 검증해야 한다
-- (실무에서는 이런 정합성 검증 배치를 반드시 만들어 둡니다)
-- (주의: STORED 는 MySQL 8 예약어라 별칭으로 쓸 수 없습니다 → stored_amount 로)
SELECT o.order_id,
       o.total_amount                        AS stored_amount,
       SUM(oi.quantity * oi.unit_price)      AS calculated_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING stored_amount <> calculated_amount;   -- 0건이면 정합성 OK

-- 1NF 위반의 현대적 변종 : 콤마 구분 문자열은 인덱스를 못 탄다
DROP TABLE IF EXISTS s13_bad_tags;
CREATE TABLE s13_bad_tags (
  product_id INT UNSIGNED NOT NULL PRIMARY KEY,
  tags       VARCHAR(255) NOT NULL,          -- '세일,신상,베스트'  ← 1NF 위반
  KEY idx_tags (tags)
) ENGINE=InnoDB;
INSERT INTO s13_bad_tags VALUES (1,'세일,신상'), (2,'베스트,세일'), (3,'신상');

-- 인덱스가 있어도 FIND_IN_SET 은 인덱스 "탐색"을 못 한다.
-- type: index = 인덱스를 처음부터 끝까지 훑는 풀스캔이다 (검색이 아니다!). Step 15/16 참고
EXPLAIN SELECT * FROM s13_bad_tags WHERE FIND_IN_SET('세일', tags);
-- +-------+---------------+----------+------+--------------------------+
-- | type  | possible_keys | key      | rows | Extra                    |
-- +-------+---------------+----------+------+--------------------------+
-- | index | NULL          | idx_tags |    3 | Using where; Using index |
-- +-------+---------------+----------+------+--------------------------+
-- possible_keys 가 NULL 인 것에 주목: 옵티마이저가 쓸 수 있는 "조건"이 하나도 없다.

-- 올바른 설계 : 행으로 쪼갠다
DROP TABLE IF EXISTS s13_good_tags;
CREATE TABLE s13_good_tags (
  product_id INT UNSIGNED NOT NULL,
  tag        VARCHAR(30) NOT NULL,
  PRIMARY KEY (product_id, tag),
  KEY idx_tag (tag)                          -- 이제 태그로 검색이 인덱스를 탄다
) ENGINE=InnoDB;
INSERT INTO s13_good_tags VALUES (1,'세일'),(1,'신상'),(2,'베스트'),(2,'세일'),(3,'신상');

EXPLAIN SELECT product_id FROM s13_good_tags WHERE tag = '세일';   -- type: ref, Using index


-- ---------------------------------------------------------------------
-- 실습 정리 — s13_ 사본 테이블 전부 삭제
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_post, s13_cat, s13_emp, s13_dept,
                     s13_order_line, s13_fk_setdefault, s13_prod,
                     s13_cas_child, s13_cas_parent,
                     s13_child, s13_parent,
                     s13_fk_on, s13_fk_off,
                     s13_members, s13_uk, s13_uk2,
                     s13_products, s13_chk_null, s13_ai,
                     s13_bad_tags, s13_good_tags;

SELECT 'Step 13 practice 완료' AS msg;

-- =====================================================================
-- Step 13 — 제약 조건과 정규화 : exercise.sql  (문제 8개)
-- ---------------------------------------------------------------------
-- 정답은 solution.sql 에 있습니다. 먼저 스스로 풀어보세요.
--
-- ⚠️ 공용 테이블은 절대 수정하지 마세요. 새로 만드는 테이블은 s13_ex_ 접두사를 쓰세요.
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [문제 1] 쿠폰 테이블 설계
-- ---------------------------------------------------------------------
-- 아래 요구사항을 모두 만족하는 s13_ex_coupons 테이블을 만드세요.
--
--   - coupon_id     : 자동 증가 PK
--   - code          : 쿠폰 코드. 필수이며 절대 중복될 수 없다 (NULL 도 안 됨)
--   - discount_pct  : 할인율. 필수, 기본값 10, 반드시 1 ~ 90 사이
--   - min_amount    : 최소 주문금액. 필수, 기본값 0, 음수 불가
--   - max_uses      : 최대 사용 횟수. NULL 이면 "무제한"이라는 뜻 (그래서 NULL 허용)
--                     단, 값이 있다면 1 이상이어야 한다
--   - expires_at    : 만료일시. 필수
--   - created_at    : 생성일시. 필수, 기본값은 현재 시각
--
-- 만든 뒤, 아래 INSERT 3개가 "의도대로" 동작하는지 확인하세요.
--   (1) 성공해야 함 : code='WELCOME10', expires_at='2026-12-31 23:59:59'  (나머지 기본값)
--   (2) 실패해야 함 : code='BAD', discount_pct=95, expires_at='2026-12-31 23:59:59'
--   (3) 실패해야 함 : code='WELCOME10' 중복

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 2] UNIQUE 인데 왜 중복이 들어갈까?
-- ---------------------------------------------------------------------
-- 아래 테이블은 "사업자번호는 유일해야 한다"는 요구사항으로 만들어졌습니다.
-- 그런데 운영 중에 사업자번호가 없는 개인 판매자 3명이 등록되자
-- "중복이 들어간 것 같다"는 제보가 들어왔습니다.

DROP TABLE IF EXISTS s13_ex_sellers;
CREATE TABLE s13_ex_sellers (
  seller_id INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(50) NOT NULL,
  biz_no    VARCHAR(20) NULL,
  UNIQUE KEY uk_biz_no (biz_no)
) ENGINE=InnoDB;

INSERT INTO s13_ex_sellers (name, biz_no) VALUES
  ('회사A', '111-11-11111'),
  ('개인B', NULL),
  ('개인C', NULL),
  ('개인D', NULL);

-- (2-1) 위 INSERT 가 왜 전부 성공했는지 한 줄로 설명하세요. (주석으로)
-- (2-2) "사업자번호가 있는 판매자는 반드시 유일하다"는 요구사항은 이미 지켜지고 있습니다.
--       그것을 증명하는 INSERT 문을 하나 작성하세요. (실패해야 정상)
-- (2-3) 만약 "모든 판매자는 사업자번호가 필수이며 유일해야 한다"로 요구사항이 바뀌었다면
--       테이블을 어떻게 고쳐야 할까요? ALTER 문을 쓰세요.
--       (힌트: 기존 NULL 행이 있으면 그냥은 안 됩니다. 어떻게 처리할지도 함께)

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 3] CHECK 로 비즈니스 규칙 강제하기
-- ---------------------------------------------------------------------
-- s13_ex_items 테이블에 아래 3가지 규칙을 CHECK 제약으로 추가하세요.
--   (a) sale_price 는 list_price 보다 클 수 없다
--   (b) sale_price 와 list_price 는 둘 다 0 이상이다
--   (c) qty 는 1 이상 999 이하이다
--
-- 그리고 나서: qty 컬럼이 NULL 을 허용하는데, CHECK (qty BETWEEN 1 AND 999) 만으로
--             "NULL 도 막을 수 있는가?" 를 INSERT 로 직접 확인하고 결과를 주석으로 남기세요.

DROP TABLE IF EXISTS s13_ex_items;
CREATE TABLE s13_ex_items (
  item_id    INT AUTO_INCREMENT PRIMARY KEY,
  list_price DECIMAL(10,2) NOT NULL,
  sale_price DECIMAL(10,2) NOT NULL,
  qty        INT NULL
) ENGINE=InnoDB;

-- 여기에 ALTER TABLE ... ADD CONSTRAINT 작성:



-- ---------------------------------------------------------------------
-- [문제 4] FK 참조 동작 고르기
-- ---------------------------------------------------------------------
-- 아래 4가지 시나리오 각각에 대해 ON DELETE 옵션을
-- CASCADE / SET NULL / RESTRICT 중 무엇으로 할지 고르고, 이유를 주석으로 쓰세요.
--
--   (4-1) orders → order_items
--         주문을 지우면 그 주문의 상세도 의미가 없다. 주문당 상세는 보통 1~10건.
--
--   (4-2) customers → orders
--         고객이 탈퇴해도 주문 기록은 회계/정산 때문에 남아야 한다.
--
--   (4-3) employees → employees (manager_id)
--         팀장이 퇴사하면 팀원들은 남아야 하고, 관리자만 "없음" 상태가 되어야 한다.
--
--   (4-4) categories → products
--         카테고리 하나에 상품이 수만 개 달릴 수 있다.
--         카테고리를 실수로 지웠을 때 상품이 전부 사라지면 절대 안 된다.

-- 여기에 주석으로 답:



-- ---------------------------------------------------------------------
-- [문제 5] CASCADE 지뢰 찾기
-- ---------------------------------------------------------------------
-- shop 스키마(01_schema.sql)에 실제로 걸려 있는 FK 중에서
-- ON DELETE CASCADE 인 것을 information_schema 에서 조회하는 쿼리를 작성하세요.
--
-- 그리고 그중 "가장 위험한" 것 하나를 골라, 부모 1행을 지웠을 때
-- 실제로 몇 행의 자식이 함께 삭제되는지 COUNT 로 계산하는 쿼리를 쓰세요.
-- (절대 진짜로 DELETE 하지 마세요! COUNT 만 하세요.)

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 6] AUTO_INCREMENT 구멍 예측
-- ---------------------------------------------------------------------
-- 아래 SQL 을 순서대로 실행했을 때, 마지막 SELECT 결과의 id 값들을 예측하세요.
-- 예측을 주석으로 먼저 적은 뒤에 실행해서 맞는지 확인하세요.

DROP TABLE IF EXISTS s13_ex_ai;
CREATE TABLE s13_ex_ai (
  id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  val VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_val (val)
) ENGINE=InnoDB;

INSERT INTO s13_ex_ai (val) VALUES ('x'), ('y');      -- ?
START TRANSACTION;
INSERT INTO s13_ex_ai (val) VALUES ('z');             -- ?
ROLLBACK;
INSERT INTO s13_ex_ai (val) VALUES ('x');             -- 실패 (중복)
INSERT INTO s13_ex_ai (val) VALUES ('w');             -- ?
DELETE FROM s13_ex_ai WHERE val = 'y';
INSERT INTO s13_ex_ai (val) VALUES ('v');             -- ?

-- 예측: id = ?, ?, ?  ...
SELECT id, val FROM s13_ex_ai ORDER BY id;


-- ---------------------------------------------------------------------
-- [문제 7] 3NF 위반 찾아서 분해하기
-- ---------------------------------------------------------------------
-- 아래 테이블은 3NF 를 위반하고 있습니다.

DROP TABLE IF EXISTS s13_ex_bad;
CREATE TABLE s13_ex_bad (
  enroll_id     INT AUTO_INCREMENT PRIMARY KEY,
  student_id    INT NOT NULL,
  student_name  VARCHAR(30) NOT NULL,
  course_code   VARCHAR(10) NOT NULL,
  course_title  VARCHAR(50) NOT NULL,
  professor     VARCHAR(30) NOT NULL,
  professor_tel VARCHAR(20) NOT NULL,
  score         INT NULL
) ENGINE=InnoDB;

-- (7-1) 어떤 컬럼이 어떤 컬럼에 이행적으로 종속되는지 주석으로 쓰세요.
-- (7-2) 3NF 를 만족하도록 테이블을 3~4개로 분해해서 CREATE TABLE 문을 쓰세요.
--       (PK, FK 를 반드시 포함할 것)

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 8] 반정규화 컬럼의 동기화 검증
-- ---------------------------------------------------------------------
-- orders.total_amount 는 order_items 합계를 복사해 둔 반정규화 컬럼입니다.
--
-- (8-1) 저장된 total_amount 와 실제 order_items 합계가 다른 주문을 찾는 쿼리를 쓰세요.
--       (지금은 0건이 나오는 게 정상입니다)
--
-- (8-2) 함정: 위 쿼리를 JOIN 으로만 쓰면 놓치는 경우가 있습니다.
--       "order_items 가 한 건도 없는 주문"은 JOIN 에서 아예 빠져버립니다.
--       그런 주문이 있는데 total_amount 가 0 이 아니라면 그것도 불일치입니다.
--       이 경우까지 잡아내는 쿼리로 고치세요. (힌트: LEFT JOIN 또는 상관 서브쿼리)

-- 여기에 작성:

-- =====================================================================
-- Step 13 — 제약 조건과 정규화 : solution.sql  (정답 + 해설)
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [정답 1] 쿠폰 테이블 설계
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_ex_coupons;
CREATE TABLE s13_ex_coupons (
  coupon_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code         VARCHAR(30)  NOT NULL,                  -- NOT NULL + UNIQUE 를 "둘 다" 걸어야
  discount_pct TINYINT UNSIGNED NOT NULL DEFAULT 10,   --   진짜 유일성이 보장된다
  min_amount   DECIMAL(10,2) NOT NULL DEFAULT 0,
  max_uses     INT UNSIGNED NULL,                      -- NULL = 무제한 (의미 있는 NULL)
  expires_at   DATETIME NOT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (coupon_id),
  UNIQUE KEY uk_coupon_code (code),
  CONSTRAINT chk_cp_pct     CHECK (discount_pct BETWEEN 1 AND 90),
  CONSTRAINT chk_cp_min     CHECK (min_amount >= 0),
  CONSTRAINT chk_cp_uses    CHECK (max_uses IS NULL OR max_uses >= 1)
) ENGINE=InnoDB;

-- (1) 성공
INSERT INTO s13_ex_coupons (code, expires_at) VALUES ('WELCOME10', '2026-12-31 23:59:59');
SELECT coupon_id, code, discount_pct, min_amount, max_uses FROM s13_ex_coupons;

-- (2) 실패 : discount_pct = 95 → ERROR 3819 Check constraint 'chk_cp_pct' is violated.
INSERT INTO s13_ex_coupons (code, discount_pct, expires_at) VALUES ('BAD', 95, '2026-12-31 23:59:59');

-- (3) 실패 : code 중복 → ERROR 1062 Duplicate entry 'WELCOME10' for key 'uk_coupon_code'
INSERT INTO s13_ex_coupons (code, expires_at) VALUES ('WELCOME10', '2026-12-31 23:59:59');

-- 해설
--   * max_uses 의 CHECK 를 그냥 `CHECK (max_uses >= 1)` 로만 써도 NULL 은 통과합니다
--     (NULL >= 1 은 UNKNOWN → 통과). 그래서 사실 IS NULL OR 은 없어도 동작합니다.
--     하지만 "NULL 을 의도적으로 허용한다"는 것을 코드로 명시하는 편이 읽는 사람에게 친절합니다.
--   * discount_pct 에 UNSIGNED 를 썼다고 음수가 막히는 게 아닙니다.
--     STRICT 모드가 꺼져 있으면 음수가 0 으로 조용히 바뀝니다. CHECK 가 정답입니다.


-- ---------------------------------------------------------------------
-- [정답 2] UNIQUE 인데 왜 중복이 들어갈까?
-- ---------------------------------------------------------------------
-- (셋업) exercise.sql 을 먼저 풀지 않고 이 파일만 실행해도 되도록, 문제의 테이블을 여기서 만듭니다.
DROP TABLE IF EXISTS s13_ex_sellers;
CREATE TABLE s13_ex_sellers (
  seller_id INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(50) NOT NULL,
  biz_no    VARCHAR(20) NULL,
  UNIQUE KEY uk_biz_no (biz_no)
) ENGINE=InnoDB;
INSERT INTO s13_ex_sellers (name, biz_no) VALUES
  ('회사A', '111-11-11111'), ('개인B', NULL), ('개인C', NULL), ('개인D', NULL);

-- (2-1) 답:
--   UNIQUE 제약은 NULL 을 중복으로 취급하지 않습니다. SQL 표준에서 NULL 은 "값이 없음"이라
--   NULL 끼리 서로 같다고 보지 않기 때문입니다. 그래서 NULL 은 몇 개든 들어갑니다.
--   → 이건 버그가 아니라 정상 동작이며, "중복이 들어갔다"는 제보 자체가 오해입니다.

-- (2-2) 사업자번호가 "있는" 판매자의 유일성은 지켜지고 있음을 증명:
--       → ERROR 1062: Duplicate entry '111-11-11111' for key 's13_ex_sellers.uk_biz_no'
INSERT INTO s13_ex_sellers (name, biz_no) VALUES ('회사A-복제', '111-11-11111');

-- (2-3) "모든 판매자는 사업자번호 필수 + 유일" 로 요구사항이 바뀐 경우:
--   기존 NULL 행이 있으므로 NOT NULL 로 바로 못 바꿉니다.
--   → 먼저 NULL 행을 처리(값 채우기 또는 삭제)한 뒤 ALTER 해야 합니다.

-- 1단계: NULL 행이 몇 개인지 확인
SELECT COUNT(*) AS null_biz_no FROM s13_ex_sellers WHERE biz_no IS NULL;    -- 3

-- 2단계: 값을 채운다 (실무에서는 담당자가 실제 값을 채워 넣습니다.
--         여기서는 예시로 임시값을 넣습니다 — 절대 운영에서 이러면 안 됩니다!)
UPDATE s13_ex_sellers SET biz_no = CONCAT('TEMP-', seller_id) WHERE biz_no IS NULL;

-- 3단계: 이제 NOT NULL 로 바꿀 수 있다
ALTER TABLE s13_ex_sellers MODIFY COLUMN biz_no VARCHAR(20) NOT NULL;

SHOW CREATE TABLE s13_ex_sellers;
-- uk_biz_no 는 그대로 두면 됩니다. NOT NULL + UNIQUE 조합이 완성되었습니다.


-- ---------------------------------------------------------------------
-- [정답 3] CHECK 로 비즈니스 규칙 강제하기
-- ---------------------------------------------------------------------
-- (셋업) 이 파일만 실행해도 되도록 문제의 테이블을 여기서 만듭니다.
DROP TABLE IF EXISTS s13_ex_items;
CREATE TABLE s13_ex_items (
  item_id    INT AUTO_INCREMENT PRIMARY KEY,
  list_price DECIMAL(10,2) NOT NULL,
  sale_price DECIMAL(10,2) NOT NULL,
  qty        INT NULL
) ENGINE=InnoDB;

ALTER TABLE s13_ex_items
  ADD CONSTRAINT chk_it_sale_le_list CHECK (sale_price <= list_price),
  ADD CONSTRAINT chk_it_prices_pos   CHECK (list_price >= 0 AND sale_price >= 0),
  ADD CONSTRAINT chk_it_qty_range    CHECK (qty BETWEEN 1 AND 999);

-- 규칙 확인
INSERT INTO s13_ex_items (list_price, sale_price, qty) VALUES (10000, 8000, 5);   -- OK
INSERT INTO s13_ex_items (list_price, sale_price, qty) VALUES (10000, 12000, 5);  -- ERROR 3819 (a 위반)
INSERT INTO s13_ex_items (list_price, sale_price, qty) VALUES (10000, 8000, 1500);-- ERROR 3819 (c 위반)

-- 핵심 질문: CHECK (qty BETWEEN 1 AND 999) 만으로 NULL 을 막을 수 있는가?
INSERT INTO s13_ex_items (list_price, sale_price, qty) VALUES (10000, 8000, NULL);
SELECT item_id, list_price, sale_price, qty FROM s13_ex_items;

-- 답: 막지 못합니다. NULL 이 그대로 들어갑니다.
--   NULL BETWEEN 1 AND 999  →  UNKNOWN
--   CHECK 는 결과가 "FALSE 일 때만" 거부합니다. UNKNOWN 은 통과입니다.
--
-- NULL 도 막으려면 둘 중 하나:
--   (방법 A) 컬럼에 NOT NULL 을 건다        ← 권장
--   (방법 B) CHECK (qty IS NOT NULL AND qty BETWEEN 1 AND 999)
--
-- 방법 A 로 고치기:
DELETE FROM s13_ex_items WHERE qty IS NULL;                       -- 기존 NULL 행 정리 후
ALTER TABLE s13_ex_items MODIFY COLUMN qty INT NOT NULL;
INSERT INTO s13_ex_items (list_price, sale_price, qty) VALUES (10000, 8000, NULL);  -- 이제 ERROR 1048


-- ---------------------------------------------------------------------
-- [정답 4] FK 참조 동작 고르기
-- ---------------------------------------------------------------------
--
-- (4-1) orders → order_items  :  ON DELETE CASCADE
--       주문 상세는 주문 없이는 존재 의미가 없는 "종속 엔티티"입니다.
--       주문당 상세가 1~10건으로 적으므로 CASCADE 의 대량 삭제 위험도 낮습니다.
--       → 실제로 01_schema.sql 이 이렇게 되어 있습니다.
--
-- (4-2) customers → orders    :  ON DELETE RESTRICT  (= 기본값, 생략)
--       주문은 회계 기록입니다. 고객이 탈퇴해도 절대 사라지면 안 됩니다.
--       CASCADE 였다면 탈퇴 한 번에 그 고객의 모든 주문/결제 기록이 증발합니다.
--       실무에서는 customers 를 물리 삭제하지 않고 `deleted_at` 으로 소프트 삭제합니다.
--       → 01_schema.sql 도 RESTRICT(기본값)입니다.
--
-- (4-3) employees → employees :  ON DELETE SET NULL
--       팀장이 퇴사해도 팀원은 남아야 합니다. manager_id 만 NULL(= 관리자 없음)이 되면 됩니다.
--       CASCADE 였다면 본부장 한 명 퇴사에 그 밑의 조직 전체가 삭제됩니다.
--       (주의: manager_id 가 NULL 허용이어야 SET NULL 을 걸 수 있습니다. employees 는 NULL 허용입니다.)
--
-- (4-4) categories → products :  ON DELETE RESTRICT
--       자식이 수만 건인 관계에 CASCADE 를 걸면 재앙입니다.
--       "카테고리 1건 삭제 = 상품 5만건 + 그에 딸린 리뷰 30만건 삭제"가 되고,
--       그 트랜잭션이 수십만 행에 X락을 잡은 채 서비스를 멈춥니다.
--       RESTRICT 로 막아두고, 정말 지워야 한다면 애플리케이션이 배치로 나눠서 처리하게 합니다.
--       → 01_schema.sql 도 RESTRICT 입니다.
--
-- 판단 규칙 요약:
--   * 자식이 부모 없이 무의미하고, 자식 수가 적다      → CASCADE
--   * 자식이 부모 없이도 독립적으로 의미가 있다        → SET NULL
--   * 자식이 회계/법적 기록이거나, 자식 수가 많다      → RESTRICT


-- ---------------------------------------------------------------------
-- [정답 5] CASCADE 지뢰 찾기
-- ---------------------------------------------------------------------
-- shop 스키마에서 ON DELETE CASCADE 인 FK 를 모두 조회
SELECT rc.CONSTRAINT_NAME,
       rc.TABLE_NAME            AS child_table,
       rc.REFERENCED_TABLE_NAME AS parent_table,
       rc.DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
WHERE rc.CONSTRAINT_SCHEMA = 'shop'
  AND rc.DELETE_RULE = 'CASCADE'
  AND rc.TABLE_NAME NOT LIKE 's1%';        -- 실습용 사본 테이블 제외

-- 결과
-- +----------------------+-------------+-----------+-------------+
-- | CONSTRAINT_NAME      | child_table | parent    | DELETE_RULE |
-- +----------------------+-------------+-----------+-------------+
-- | fk_order_items_order | order_items | orders    | CASCADE     |
-- | fk_payments_order    | payments    | orders    | CASCADE     |
-- | fk_reviews_customer  | reviews     | customers | CASCADE     |   ← 가장 위험!
-- | fk_reviews_product   | reviews     | products  | CASCADE     |
-- +----------------------+-------------+-----------+-------------+

-- 가장 위험한 것: fk_reviews_customer (customers → reviews CASCADE)
--   이유: orders → order_items/payments 는 "주문 1건당 상세 몇 건"이라 폭발 규모가 작습니다.
--         하지만 customers → reviews 는 "고객 1명당 리뷰 수십~수백 건"이 될 수 있고,
--         무엇보다 리뷰는 그 고객만의 것이 아니라 "상품 페이지의 자산"입니다.
--         고객 1명을 지웠을 뿐인데 상품 평점이 바뀌어 버립니다.

-- 고객 1명(customer_id=1)을 지우면 리뷰가 몇 건 함께 삭제되는가?
-- (절대 DELETE 하지 않고 COUNT 만!)
SELECT c.customer_id, c.name,
       (SELECT COUNT(*) FROM reviews r WHERE r.customer_id = c.customer_id) AS cascade_deleted_reviews
FROM customers c
WHERE c.customer_id = 1;
-- +-------------+-----------+-------------------------+
-- | customer_id | name      | cascade_deleted_reviews |
-- +-------------+-----------+-------------------------+
-- |           1 | 김민수    |                      20 |
-- +-------------+-----------+-------------------------+

-- 전체 고객에 대해 "삭제 시 파급 규모" 를 미리 확인하는 쿼리 (실무 안전 점검용)
SELECT c.customer_id, c.name,
       (SELECT COUNT(*) FROM reviews r WHERE r.customer_id = c.customer_id) AS reviews_lost
FROM customers c
ORDER BY reviews_lost DESC
LIMIT 5;

-- 💡 실무 팁: 위험한 DELETE 를 실행하기 전에는 항상 같은 WHERE 절로 SELECT COUNT 를 먼저 돌리세요.
--    CASCADE 는 "1 row affected" 라고만 보고하기 때문에, 실행 후에는 규모를 알 방법이 없습니다.


-- ---------------------------------------------------------------------
-- [정답 6] AUTO_INCREMENT 구멍 예측
-- ---------------------------------------------------------------------
-- 정답: id = 1, 5, 6   (val = 'x', 'w', 'v')
--
-- 추적:
--   INSERT ('x'),('y')  → 1, 2 사용.  카운터 = 3
--   START TRANSACTION
--   INSERT ('z')        → 3 사용.     카운터 = 4
--   ROLLBACK            → 'z' 행은 사라지지만 카운터는 4 그대로 (구멍 1)
--   INSERT ('x')        → 4 를 가져갔다가 UNIQUE 위반으로 실패. 카운터 = 5 (구멍 2)
--   INSERT ('w')        → 5 사용.     카운터 = 6
--   DELETE 'y'          → id=2 행 삭제. 카운터는 그대로 6 (구멍 3)
--   INSERT ('v')        → 6 사용.     카운터 = 7
--
--   남은 행: (1,'x'), (5,'w'), (6,'v')
--   사라진 번호: 2(DELETE), 3(ROLLBACK), 4(실패한 INSERT)
--
-- 실제 실행 결과:
-- +----+-----+
-- | id | val |
-- +----+-----+
-- |  1 | x   |
-- |  5 | w   |
-- |  6 | v   |
-- +----+-----+
--
-- 교훈: id 는 "유일성"만 보장합니다. 연속성도, 행 개수도 보장하지 않습니다.
--       "id 최댓값 = 총 가입자 수" 같은 계산은 반드시 틀립니다. COUNT(*) 를 쓰세요.


-- ---------------------------------------------------------------------
-- [정답 7] 3NF 위반 찾아서 분해하기
-- ---------------------------------------------------------------------
-- (7-1) 이행적 함수 종속 분석
--
--   PK = enroll_id
--
--   enroll_id → student_id → student_name          (이행 종속! 3NF 위반)
--   enroll_id → course_code → course_title         (이행 종속! 3NF 위반)
--   enroll_id → course_code → professor            (이행 종속! 3NF 위반)
--   enroll_id → professor   → professor_tel        (이행 종속! 3NF 위반, 2단계나 건너뜀)
--   enroll_id → score                              (정상: PK 에 직접 종속)
--
--   즉 student_name / course_title / professor / professor_tel 은
--   PK(enroll_id)가 아니라 "PK 가 아닌 다른 컬럼"에 의존하고 있습니다.
--
--   실제 이상현상:
--     - 교수 전화번호가 바뀌면 그 교수의 모든 수강 행을 UPDATE 해야 함 (갱신 이상)
--     - 아직 수강생이 없는 과목은 등록할 방법이 없음 (삽입 이상)
--     - 마지막 수강생이 취소하면 과목 정보가 통째로 사라짐 (삭제 이상)

-- (7-2) 3NF 분해 : 4개 테이블로
DROP TABLE IF EXISTS s13_ex_enroll;
DROP TABLE IF EXISTS s13_ex_course;
DROP TABLE IF EXISTS s13_ex_professor;
DROP TABLE IF EXISTS s13_ex_student;

CREATE TABLE s13_ex_student (
  student_id   INT UNSIGNED NOT NULL,
  student_name VARCHAR(30) NOT NULL,
  PRIMARY KEY (student_id)
) ENGINE=InnoDB;

CREATE TABLE s13_ex_professor (
  professor_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(30) NOT NULL,
  tel           VARCHAR(20) NOT NULL,        -- professor 에만 종속 → 여기가 제자리
  PRIMARY KEY (professor_id),
  UNIQUE KEY uk_prof_name (name)
) ENGINE=InnoDB;

CREATE TABLE s13_ex_course (
  course_code  VARCHAR(10) NOT NULL,
  course_title VARCHAR(50) NOT NULL,
  professor_id INT UNSIGNED NOT NULL,        -- course 에만 종속 → 여기가 제자리
  PRIMARY KEY (course_code),
  KEY idx_course_prof (professor_id),
  CONSTRAINT fk_course_prof FOREIGN KEY (professor_id)
    REFERENCES s13_ex_professor(professor_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE s13_ex_enroll (
  enroll_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id  INT UNSIGNED NOT NULL,
  course_code VARCHAR(10) NOT NULL,
  score       INT NULL,                      -- 오직 (학생, 과목) 조합에만 종속 → 여기가 제자리
  PRIMARY KEY (enroll_id),
  UNIQUE KEY uk_enroll (student_id, course_code),   -- 같은 학생이 같은 과목을 두 번 수강 불가
  KEY idx_enroll_course (course_code),
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id)
    REFERENCES s13_ex_student(student_id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_course FOREIGN KEY (course_code)
    REFERENCES s13_ex_course(course_code) ON DELETE RESTRICT,
  CONSTRAINT chk_enroll_score CHECK (score IS NULL OR score BETWEEN 0 AND 100)
) ENGINE=InnoDB;

-- 이제:
--   * 교수 전화번호 변경 → s13_ex_professor 의 1행만 UPDATE
--   * 수강생 없는 과목도 등록 가능
--   * 수강 취소해도 과목/교수 정보는 남음
--
-- 보너스: uk_enroll(student_id, course_code) 덕분에 중복 수강도 DB 가 막아줍니다.
--         "제약으로 막을 수 있는 것은 애플리케이션에 맡기지 말라"는 원칙의 좋은 예입니다.


-- ---------------------------------------------------------------------
-- [정답 8] 반정규화 컬럼의 동기화 검증
-- ---------------------------------------------------------------------

-- (8-1) JOIN + HAVING 버전
--   ⚠️ STORED 는 MySQL 8 예약어라 별칭으로 쓸 수 없습니다 (stored_amount 로 우회)
SELECT o.order_id,
       o.total_amount                   AS stored_amount,
       SUM(oi.quantity * oi.unit_price) AS calculated_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING stored_amount <> calculated_amount;
-- 결과: Empty set  → 현재 데이터는 정합합니다.

-- (8-2) 함정: 위 쿼리는 "order_items 가 한 건도 없는 주문"을 통째로 놓칩니다.
--       INNER JOIN 이므로 자식이 없는 주문은 결과에서 아예 사라지기 때문입니다.
--       그런 주문의 total_amount 가 0 이 아니라면 그것도 명백한 불일치인데,
--       (8-1) 쿼리는 그것을 영원히 발견하지 못합니다.

-- 고친 버전 A : 상관 서브쿼리 + COALESCE  (가장 명확)
SELECT o.order_id,
       o.total_amount AS stored_amount,
       COALESCE((SELECT SUM(oi.quantity * oi.unit_price)
                 FROM order_items oi WHERE oi.order_id = o.order_id), 0) AS calculated_amount
FROM orders o
WHERE o.total_amount <> COALESCE((SELECT SUM(oi.quantity * oi.unit_price)
                                  FROM order_items oi WHERE oi.order_id = o.order_id), 0);
-- 결과: Empty set

-- 고친 버전 B : LEFT JOIN + COALESCE
SELECT o.order_id,
       o.total_amount                                  AS stored_amount,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0)   AS calculated_amount
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id     -- LEFT 로 자식 없는 주문도 살린다
GROUP BY o.order_id, o.total_amount
HAVING stored_amount <> calculated_amount;
-- 결과: Empty set

-- 참고: 지금 shop 에는 상세가 없는 주문이 0건이라 (8-1)과 (8-2)의 결과가 같습니다.
SELECT COUNT(*) AS orders_without_items
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id);
-- +----------------------+
-- | orders_without_items |
-- +----------------------+
-- |                    0 |
-- +----------------------+
--
-- 하지만 운영 DB 에서는 "결제 실패로 상세가 롤백됐는데 헤더만 남은 주문" 같은 것이 반드시 생깁니다.
-- 정합성 검증 쿼리는 그런 "0건 케이스"를 잡으라고 있는 것입니다.
-- INNER JOIN 으로 짠 검증 배치는 정작 잡아야 할 사고를 못 잡습니다.
--
-- 💡 반정규화의 교훈:
--    total_amount 같은 반정규화 컬럼을 만들었다면, 위와 같은 정합성 검증 쿼리를
--    "야간 배치"로 매일 돌리세요. 반정규화는 성능을 빌려오고 정합성을 담보로 잡는 거래입니다.
--    담보를 확인하지 않으면 언젠가 반드시 청구서가 옵니다.


-- ---------------------------------------------------------------------
-- 정리
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s13_ex_enroll, s13_ex_course, s13_ex_professor, s13_ex_student,
                     s13_ex_coupons, s13_ex_sellers, s13_ex_items, s13_ex_bad, s13_ex_ai;

SELECT 'Step 13 solution 완료' AS msg;