#Step 14 — 뷰와 생성 컬럼

USE shop;
CREATE TABLE s14_customers AS SELECT * FROM customers;
ALTER TABLE s14_customers ADD PRIMARY KEY (customer_id);

CREATE VIEW v14_order_summary AS
SELECT o.order_id, o.order_date, o.status,
       c.name AS customer_name, c.grade, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'CANCELLED';

-- 이제 복잡한 JOIN 을 몰라도 테이블처럼 조회할 수 있다
SELECT * FROM v14_order_summary WHERE grade = 'VIP' ORDER BY total_amount DESC LIMIT 5;

CREATE OR REPLACE VIEW v14_order_summary AS ... ;   -- 재정의 (있으면 교체)
ALTER VIEW v14_order_summary AS ... ;               -- 재정의
DROP VIEW IF EXISTS v14_order_summary;              -- 삭제
SHOW CREATE VIEW v14_order_summary;                 -- 정의 확인

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME LIKE 'v14%';

CREATE VIEW v14_vip AS
SELECT customer_id, name, grade, city, points
FROM s14_customers
WHERE grade = 'VIP';

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='v14_vip';

UPDATE v14_vip SET points = points + 1000 WHERE customer_id = 1;
SELECT customer_id, name, points FROM s14_customers WHERE customer_id = 1;

CREATE VIEW v14_grouped AS
SELECT grade, COUNT(*) AS cnt, AVG(points) AS avg_points
FROM s14_customers GROUP BY grade;

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='v14_grouped';

UPDATE v14_grouped SET cnt = 99 WHERE grade = 'VIP';

-- CHECK OPTION 이 없는 v14_vip (WHERE grade='VIP')
UPDATE v14_vip SET grade = 'GOLD' WHERE customer_id = 1;   -- VIP 뷰에서 GOLD 로 바꿈?!
SELECT customer_id, name, grade FROM s14_customers WHERE customer_id = 1;

SELECT COUNT(*) AS still_in_view FROM v14_vip WHERE customer_id = 1;

CREATE OR REPLACE VIEW v14_vip AS
SELECT customer_id, name, grade, city, points
FROM s14_customers WHERE grade = 'VIP'
WITH CHECK OPTION;

UPDATE v14_vip SET grade = 'GOLD' WHERE customer_id = 1;

CREATE VIEW v14_high AS
SELECT id, name, score FROM s14_scores WHERE score >= 60 WITH CHECK OPTION;

INSERT INTO v14_high (name, score) VALUES ('통과', 80);   -- OK
INSERT INTO v14_high (name, score) VALUES ('탈락', 40);   -- score < 60

CREATE OR REPLACE VIEW v14_vip AS                    -- 부모: grade='VIP'
SELECT customer_id, name, grade, city, points FROM s14_customers WHERE grade = 'VIP';

CREATE OR REPLACE VIEW v14_vip_seoul AS              -- 자식: city='서울', LOCAL
SELECT * FROM v14_vip WHERE city = '서울'
WITH LOCAL CHECK OPTION;

-- LOCAL 은 자기 조건(city)만 검사한다. 부모 조건(grade)은 안 본다 → 통과!
UPDATE v14_vip_seoul SET grade = 'GOLD' WHERE customer_id = 1;
SELECT customer_id, grade, city FROM s14_customers WHERE customer_id = 1;

CREATE OR REPLACE VIEW v14_vip_seoul AS
SELECT * FROM v14_vip WHERE city = '서울'
WITH CASCADED CHECK OPTION;

-- CASCADED 는 부모 뷰의 grade='VIP' 조건까지 검사한다 → 거부!
UPDATE v14_vip_seoul SET grade = 'GOLD' WHERE customer_id = 1;

CREATE ALGORITHM=MERGE VIEW v14_merge AS
SELECT log_id, customer_id, path, status_code, logged_at FROM access_logs;

CREATE ALGORITHM=TEMPTABLE VIEW v14_temptable AS
SELECT log_id, customer_id, path, status_code, logged_at FROM access_logs;

EXPLAIN SELECT * FROM v14_merge WHERE log_id = 1;

EXPLAIN SELECT * FROM v14_temptable WHERE log_id = 1;

SELECT COUNT(*) FROM v14_merge     WHERE status_code = 500;   -- (0.096 sec)
SELECT COUNT(*) FROM v14_temptable WHERE status_code = 500;   -- (0.136 sec)

CREATE TABLE s14_gen (
  product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(50) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  cost       DECIMAL(10,2) NOT NULL,
  margin     DECIMAL(10,2) AS (price - cost) VIRTUAL,                      -- 가상
  margin_pct DECIMAL(5,2)  AS (ROUND((price-cost)/price*100, 2)) STORED,   -- 저장
  name_upper VARCHAR(50)   AS (UPPER(name)) VIRTUAL,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB;

INSERT INTO s14_gen (name, price, cost) VALUES ('shirt', 39000, 18000), ('laptop', 1290000, 980000);
SELECT product_id, name, price, cost, margin, margin_pct, name_upper FROM s14_gen;

INSERT INTO s14_gen (name, price, cost, margin) VALUES ('x', 100, 50, 999);

UPDATE s14_gen SET price = 45000 WHERE name = 'shirt';
SELECT product_id, name, price, cost, margin, margin_pct FROM s14_gen WHERE name = 'shirt';

-- name_upper 는 위에서 만든 VIRTUAL 생성 컬럼 (UPPER(name))
ALTER TABLE s14_gen ADD INDEX idx_name_upper (name_upper);

EXPLAIN SELECT * FROM s14_gen WHERE UPPER(name) = 'SHIRT';

ALTER TABLE s14_gen ADD INDEX idx_func_margin ((price - cost));
ANALYZE TABLE s14_gen;   -- 통계를 갱신해야 옵티마이저가 선택도를 제대로 판단한다

EXPLAIN SELECT product_id, name FROM s14_gen WHERE (price - cost) = 3500.00;

CREATE TABLE s14_inv (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   VARCHAR(50) NOT NULL,
  secret VARCHAR(50) NOT NULL INVISIBLE,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO s14_inv (id, name, secret) VALUES (1, '홍길동', 'password123');

SELECT * FROM s14_inv;              -- secret 이 안 보인다

SELECT id, name, secret FROM s14_inv;   -- 명시하면 보인다

ALTER TABLE s14_inv ALTER COLUMN secret SET VISIBLE;    -- 다시 보이게
ALTER TABLE s14_inv ALTER COLUMN secret SET INVISIBLE;  -- 다시 숨기게

-- =====================================================================
-- Step 14 — 뷰와 생성 컬럼 : practice.sql
-- ---------------------------------------------------------------------
-- 실행:  mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 --force shop < practice.sql
--        ( --force : 일부러 에러를 내는 예제가 있어서 계속 진행 )
--
-- ⚠️ 공용 테이블에는 "뷰(읽기)"만 만듭니다. 변경 실습은 s14_ 사본에서.
-- =====================================================================
USE shop;

DROP VIEW IF EXISTS v14_vip_seoul, v14_vip, v14_grouped, v14_order_summary,
                    v14_high, v14_merge, v14_temptable;
DROP TABLE IF EXISTS s14_customers, s14_gen, s14_inv, s14_scores;


-- ---------------------------------------------------------------------
-- [14-0] 실습 준비 — 변경 실습용 사본
-- ---------------------------------------------------------------------
CREATE TABLE s14_customers AS SELECT * FROM customers;
ALTER TABLE s14_customers ADD PRIMARY KEY (customer_id);


-- ---------------------------------------------------------------------
-- [14-1] 뷰 기본 — 복잡한 JOIN 을 이름 붙여 감춘다
-- ---------------------------------------------------------------------
CREATE VIEW v14_order_summary AS
SELECT o.order_id, o.order_date, o.status,
       c.name AS customer_name, c.grade, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'CANCELLED';

SELECT * FROM v14_order_summary WHERE grade = 'VIP' ORDER BY total_amount DESC LIMIT 5;

-- 뷰 정의와 업데이트 가능 여부 확인
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME LIKE 'v14%';

-- 뷰 관리 명령 (정의 확인). 대화형 클라이언트에서는 \G 로 세로 출력하면 보기 좋다.
SHOW CREATE VIEW v14_order_summary;


-- ---------------------------------------------------------------------
-- [14-2] 업데이트 가능한 뷰
-- ---------------------------------------------------------------------
CREATE VIEW v14_vip AS
SELECT customer_id, name, grade, city, points
FROM s14_customers
WHERE grade = 'VIP';

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='v14_vip';       -- YES

-- 뷰를 통한 UPDATE → 원본(s14_customers)이 바뀐다
UPDATE v14_vip SET points = points + 1000 WHERE customer_id = 1;
SELECT customer_id, name, points FROM s14_customers WHERE customer_id = 1;   -- 13500

-- 집계가 있는 뷰는 업데이트 불가
CREATE VIEW v14_grouped AS
SELECT grade, COUNT(*) AS cnt, AVG(points) AS avg_points
FROM s14_customers GROUP BY grade;

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME='v14_grouped';   -- NO

UPDATE v14_grouped SET cnt = 99 WHERE grade = 'VIP';      -- ERROR 1288


-- ---------------------------------------------------------------------
-- [14-3] WITH CHECK OPTION
-- ---------------------------------------------------------------------
-- CHECK OPTION 없는 뷰: 조건을 벗어나는 UPDATE 가 통과 → 행이 뷰에서 사라진다
UPDATE v14_vip SET grade = 'GOLD' WHERE customer_id = 1;
SELECT customer_id, name, grade FROM s14_customers WHERE customer_id = 1;    -- GOLD 로 바뀜
SELECT COUNT(*) AS still_in_view FROM v14_vip WHERE customer_id = 1;         -- 0 (증발!)
UPDATE s14_customers SET grade = 'VIP' WHERE customer_id = 1;                -- 원복

-- WITH CHECK OPTION 을 걸면 그런 변경을 거부
CREATE OR REPLACE VIEW v14_vip AS
SELECT customer_id, name, grade, city, points
FROM s14_customers WHERE grade = 'VIP'
WITH CHECK OPTION;

UPDATE v14_vip SET grade = 'GOLD' WHERE customer_id = 1;   -- ERROR 1369 CHECK OPTION failed

-- INSERT 도 검사한다 (깨끗한 데모용 테이블)
DROP TABLE IF EXISTS s14_scores;
CREATE TABLE s14_scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  score INT NOT NULL
) ENGINE=InnoDB;
CREATE VIEW v14_high AS
SELECT id, name, score FROM s14_scores WHERE score >= 60 WITH CHECK OPTION;

INSERT INTO v14_high (name, score) VALUES ('통과', 80);    -- OK
INSERT INTO v14_high (name, score) VALUES ('탈락', 40);    -- ERROR 1369 (score < 60)
SELECT * FROM s14_scores;

-- LOCAL vs CASCADED
CREATE OR REPLACE VIEW v14_vip AS
SELECT customer_id, name, grade, city, points FROM s14_customers WHERE grade = 'VIP';

-- LOCAL: 자기 조건(city)만 검사, 부모 조건(grade)은 안 봄 → grade 변경 통과
CREATE OR REPLACE VIEW v14_vip_seoul AS
SELECT * FROM v14_vip WHERE city = '서울' WITH LOCAL CHECK OPTION;
UPDATE v14_vip_seoul SET grade = 'GOLD' WHERE customer_id = 1;
SELECT customer_id, grade, city FROM s14_customers WHERE customer_id = 1;    -- GOLD (통과)
UPDATE s14_customers SET grade = 'VIP' WHERE customer_id = 1;

-- CASCADED: 부모 뷰의 grade='VIP' 조건까지 검사 → 거부
CREATE OR REPLACE VIEW v14_vip_seoul AS
SELECT * FROM v14_vip WHERE city = '서울' WITH CASCADED CHECK OPTION;
UPDATE v14_vip_seoul SET grade = 'GOLD' WHERE customer_id = 1;   -- ERROR 1369


-- ---------------------------------------------------------------------
-- [14-4] ALGORITHM MERGE vs TEMPTABLE
-- ---------------------------------------------------------------------
CREATE ALGORITHM=MERGE VIEW v14_merge AS
SELECT log_id, customer_id, path, status_code, logged_at FROM access_logs;

CREATE ALGORITHM=TEMPTABLE VIEW v14_temptable AS
SELECT log_id, customer_id, path, status_code, logged_at FROM access_logs;

-- MERGE: PK 조건이 원본까지 전달됨 → type: const, rows=1
EXPLAIN SELECT * FROM v14_merge WHERE log_id = 1;

-- TEMPTABLE: <derived2> 임시테이블을 거친다
EXPLAIN SELECT * FROM v14_temptable WHERE log_id = 1;

-- 실제 시간 비교 (status_code 인덱스는 아직 없음)
SELECT COUNT(*) FROM v14_merge     WHERE status_code = 500;   -- 약 0.096 sec
SELECT COUNT(*) FROM v14_temptable WHERE status_code = 500;   -- 약 0.136 sec (임시테이블 구체화)


-- ---------------------------------------------------------------------
-- [14-5] 생성 컬럼 (VIRTUAL vs STORED)
-- ---------------------------------------------------------------------
CREATE TABLE s14_gen (
  product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(50) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  cost       DECIMAL(10,2) NOT NULL,
  margin     DECIMAL(10,2) AS (price - cost) VIRTUAL,                      -- 읽을 때 계산
  margin_pct DECIMAL(5,2)  AS (ROUND((price-cost)/price*100, 2)) STORED,   -- 디스크에 저장
  name_upper VARCHAR(50)   AS (UPPER(name)) VIRTUAL,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB;

INSERT INTO s14_gen (name, price, cost) VALUES ('shirt', 39000, 18000), ('laptop', 1290000, 980000);
SELECT product_id, name, price, cost, margin, margin_pct, name_upper FROM s14_gen;

-- 생성 컬럼에 직접 값 INSERT 불가 → ERROR 3105
INSERT INTO s14_gen (name, price, cost, margin) VALUES ('x', 100, 50, 999);

-- 원본이 바뀌면 생성 컬럼 자동 갱신
UPDATE s14_gen SET price = 45000 WHERE name = 'shirt';
SELECT product_id, name, price, cost, margin, margin_pct FROM s14_gen WHERE name = 'shirt';


-- ---------------------------------------------------------------------
-- [14-6] 함수 인덱스 만들기
-- ---------------------------------------------------------------------
-- 함수 인덱스가 실제로 사용되는 걸 보려면 데이터가 충분해야 한다 → 5000행 추가
INSERT INTO s14_gen (name, price, cost)
SELECT CONCAT('item', n), 1000 + n, 500 FROM tally WHERE n <= 5000;

-- 방법 1) 생성 컬럼 + 인덱스
--   인덱스 없이 UPPER(name) 조회 → type: ALL (풀스캔)
EXPLAIN SELECT * FROM s14_gen WHERE UPPER(name) = 'SHIRT';

ALTER TABLE s14_gen ADD INDEX idx_name_upper (name_upper);
--   생성 컬럼에 인덱스 → UPPER(name) 을 옵티마이저가 자동 매핑 → type: ref
EXPLAIN SELECT * FROM s14_gen WHERE UPPER(name) = 'SHIRT';

-- 방법 2) 함수 기반 인덱스 (8.0.13+) — 표현식에 직접, 괄호로 감싼다
ALTER TABLE s14_gen ADD INDEX idx_func_margin ((price - cost));
ANALYZE TABLE s14_gen;                                          -- 통계 갱신 필수!

--   함정: 리터럴 타입이 표현식 타입(DECIMAL)과 맞아야 인덱스를 탄다
--   3500 (정수) 이 아니라 3500.00 (DECIMAL) 으로 써야 한다
EXPLAIN SELECT product_id, name FROM s14_gen WHERE (price - cost) = 3500.00;    -- type: ref
SELECT product_id, name, margin FROM s14_gen WHERE (price - cost) = 3500.00;

-- 인덱스에 표현식이 그대로 보인다
SHOW INDEX FROM s14_gen;


-- ---------------------------------------------------------------------
-- [14-7] 인비저블 컬럼 (8.0.23+)
-- ---------------------------------------------------------------------
CREATE TABLE s14_inv (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   VARCHAR(50) NOT NULL,
  secret VARCHAR(50) NOT NULL INVISIBLE,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO s14_inv (id, name, secret) VALUES (1, '홍길동', 'password123');

SELECT * FROM s14_inv;                    -- secret 안 보임
SELECT id, name, secret FROM s14_inv;     -- 명시하면 보임

-- 토글
ALTER TABLE s14_inv ALTER COLUMN secret SET VISIBLE;
SELECT * FROM s14_inv;                     -- 이제 secret 보임
ALTER TABLE s14_inv ALTER COLUMN secret SET INVISIBLE;


-- ---------------------------------------------------------------------
-- 정리
-- ---------------------------------------------------------------------
DROP VIEW IF EXISTS v14_vip_seoul, v14_vip, v14_grouped, v14_order_summary,
                    v14_high, v14_merge, v14_temptable;
DROP TABLE IF EXISTS s14_customers, s14_gen, s14_inv, s14_scores;

SELECT 'Step 14 practice 완료' AS msg;

-- =====================================================================
-- Step 14 — 뷰와 생성 컬럼 : exercise.sql  (문제 6개)
-- ---------------------------------------------------------------------
-- 정답은 solution.sql. 새로 만드는 객체는 v14_ex_ / s14_ex_ 접두사를 쓰세요.
-- 공용 테이블에는 뷰(읽기)만 만들고, 변경 실습은 사본에서 하세요.
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [문제 1] 고객별 주문 통계 뷰
-- ---------------------------------------------------------------------
-- v14_ex_customer_stats 뷰를 만드세요. 컬럼:
--   customer_id, name, grade,
--   order_cnt      : 그 고객의 (취소 아닌) 주문 수
--   total_spent    : (취소 아닌) 주문 total_amount 합계
--   last_order_date: 마지막 주문일
-- CANCELLED 주문은 제외합니다.
-- 주문이 한 건도 없는 고객도 결과에 포함되어야 합니다 (order_cnt = 0).
--
-- 만든 뒤 total_spent 상위 5명을 조회하세요.

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 2] 이 뷰들은 업데이트 가능한가?
-- ---------------------------------------------------------------------
-- 아래 각 뷰가 업데이트 가능한지(IS_UPDATABLE) 예측하고, 이유를 주석으로 쓰세요.
-- 그런 다음 information_schema.VIEWS 로 실제로 확인하세요.

DROP TABLE IF EXISTS s14_ex_prod;
CREATE TABLE s14_ex_prod AS SELECT product_id, name, price, cost, stock, status FROM products;
ALTER TABLE s14_ex_prod ADD PRIMARY KEY (product_id);

CREATE OR REPLACE VIEW v14_ex_a AS
  SELECT product_id, name, price FROM s14_ex_prod WHERE status = 'ON_SALE';

CREATE OR REPLACE VIEW v14_ex_b AS
  SELECT status, COUNT(*) AS cnt FROM s14_ex_prod GROUP BY status;

CREATE OR REPLACE VIEW v14_ex_c AS
  SELECT DISTINCT status FROM s14_ex_prod;

CREATE OR REPLACE VIEW v14_ex_d AS
  SELECT product_id, name, price, price * 1.1 AS price_with_vat FROM s14_ex_prod;

-- (2-1) 예측을 주석으로:  a=?, b=?, c=?, d=?
-- (2-2) 확인 쿼리 작성:
-- (2-3) v14_ex_d 는 업데이트 가능하다면, price_with_vat 컬럼을 UPDATE 할 수 있을까요?
--       직접 시도해서 결과를 주석으로 남기세요.

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 3] WITH CHECK OPTION 으로 재고 통제
-- ---------------------------------------------------------------------
-- "재고가 있는 상품만" 보여주는 v14_ex_instock 뷰를 만들되,
-- 이 뷰를 통해서는 stock 을 0 이하로 만들 수 없도록 하세요.
-- (즉, 뷰를 통한 UPDATE 로 재고를 다 소진시켜 뷰에서 사라지게 하는 것을 막습니다)
--
-- 검증:
--   (a) stock 을 1 로 줄이는 UPDATE → 성공해야 함
--   (b) stock 을 0 으로 줄이는 UPDATE → 거부되어야 함
-- (s14_ex_prod 를 대상으로 하세요)

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 4] LOCAL vs CASCADED 결과 예측
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s14_ex_emp;
CREATE TABLE s14_ex_emp (
  emp_id INT PRIMARY KEY,
  dept   VARCHAR(20) NOT NULL,
  salary INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO s14_ex_emp VALUES (1,'개발',5000),(2,'개발',3000),(3,'영업',4000);

CREATE OR REPLACE VIEW v14_ex_dev AS
  SELECT * FROM s14_ex_emp WHERE dept = '개발';                    -- CHECK OPTION 없음

CREATE OR REPLACE VIEW v14_ex_dev_highpay AS
  SELECT * FROM v14_ex_dev WHERE salary >= 4000 WITH LOCAL CHECK OPTION;

-- (4-1) 다음 UPDATE 는 성공할까 실패할까? 예측 후 실행:
--       UPDATE v14_ex_dev_highpay SET dept = '영업' WHERE emp_id = 1;
--       (dept 를 바꾸면 부모 뷰 v14_ex_dev 의 조건에서 벗어남. 하지만 LOCAL 이라면?)
-- (4-2) WITH LOCAL 을 WITH CASCADED 로 바꾸면 결과가 달라지나요? 확인하세요.

-- 여기에 작성:



-- ---------------------------------------------------------------------
-- [문제 5] 생성 컬럼 + 인덱스로 이메일 도메인 검색
-- ---------------------------------------------------------------------
-- 고객 이메일에서 "@ 뒤 도메인"으로 검색을 자주 한다고 합시다.
--   WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com'
-- 이 조건은 email 인덱스를 못 탑니다.
--
-- s14_ex_cust 사본에 email_domain 생성 컬럼을 추가하고 인덱스를 걸어,
-- 도메인 검색이 인덱스를 타도록 만드세요. EXPLAIN 으로 type 이 바뀌는지 확인하세요.

DROP TABLE IF EXISTS s14_ex_cust;
CREATE TABLE s14_ex_cust AS SELECT customer_id, email, name FROM customers;
ALTER TABLE s14_ex_cust ADD PRIMARY KEY (customer_id);
-- (도메인 분포를 만들기 위해 일부 이메일을 다른 도메인으로 바꿔둡니다)
UPDATE s14_ex_cust SET email = REPLACE(email, 'example.com', 'test.org') WHERE customer_id % 3 = 0;

-- 여기에 작성 (ALTER TABLE ... ADD COLUMN ... AS (...) / ADD INDEX / EXPLAIN):



-- ---------------------------------------------------------------------
-- [문제 6] 함수 기반 인덱스가 안 먹는 쿼리 진단
-- ---------------------------------------------------------------------
-- 아래 테이블에 함수 기반 인덱스 idx_ym ((YEAR(logged_at))) 이 있습니다.
DROP TABLE IF EXISTS s14_ex_log;
CREATE TABLE s14_ex_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  logged_at DATETIME NOT NULL,
  amount INT NOT NULL,
  INDEX idx_ym ((YEAR(logged_at)))
) ENGINE=InnoDB;
INSERT INTO s14_ex_log (logged_at, amount)
SELECT TIMESTAMPADD(DAY, n, '2022-01-01'), n FROM tally WHERE n <= 1000;
ANALYZE TABLE s14_ex_log;

-- 아래 세 쿼리 중 idx_ym 을 "타는" 것과 "못 타는" 것을 EXPLAIN 으로 구분하고,
-- 못 타는 것은 왜 그런지 주석으로 설명하세요. (고칠 수 있으면 고쳐보세요)
--
--   (a) SELECT * FROM s14_ex_log WHERE YEAR(logged_at) = 2023;
--   (b) SELECT * FROM s14_ex_log WHERE YEAR(logged_at) + 0 = 2023;
--   (c) SELECT * FROM s14_ex_log WHERE logged_at >= '2023-01-01' AND logged_at < '2024-01-01';

-- 여기에 작성:

-- =====================================================================
-- Step 14 — 뷰와 생성 컬럼 : solution.sql  (정답 + 해설)
-- =====================================================================
USE shop;


-- ---------------------------------------------------------------------
-- [정답 1] 고객별 주문 통계 뷰
-- ---------------------------------------------------------------------
DROP VIEW IF EXISTS v14_ex_customer_stats;
CREATE VIEW v14_ex_customer_stats AS
SELECT c.customer_id, c.name, c.grade,
       COUNT(o.order_id)                AS order_cnt,       -- LEFT JOIN 이므로 NULL 은 안 셈
       COALESCE(SUM(o.total_amount), 0) AS total_spent,
       MAX(o.order_date)                AS last_order_date
FROM customers c
LEFT JOIN orders o                                          -- 주문 없는 고객도 살리려면 LEFT
       ON o.customer_id = c.customer_id
      AND o.status <> 'CANCELLED'                           -- 취소 제외는 "ON 절"에 둔다!
GROUP BY c.customer_id, c.name, c.grade;

SELECT * FROM v14_ex_customer_stats ORDER BY total_spent DESC LIMIT 5;

-- 해설
--   * CANCELLED 제외 조건을 WHERE 에 두면 안 됩니다. WHERE o.status<>'CANCELLED' 는
--     주문이 아예 없는 고객(o.status = NULL)까지 걸러내서 LEFT JOIN 이 INNER JOIN 처럼 됩니다.
--     "자식 테이블 조건은 ON 절, 부모 테이블 조건은 WHERE 절" 이 LEFT JOIN 의 철칙입니다(Step 07).
--   * COUNT(o.order_id) 는 NULL 을 세지 않으므로 주문 없는 고객은 0 이 됩니다.
--     COUNT(*) 로 쓰면 LEFT JOIN 이 만든 NULL 행까지 1 로 세어 틀립니다.


-- ---------------------------------------------------------------------
-- [정답 2] 이 뷰들은 업데이트 가능한가?
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s14_ex_prod;
CREATE TABLE s14_ex_prod AS SELECT product_id, name, price, cost, stock, status FROM products;
ALTER TABLE s14_ex_prod ADD PRIMARY KEY (product_id);

CREATE OR REPLACE VIEW v14_ex_a AS
  SELECT product_id, name, price FROM s14_ex_prod WHERE status = 'ON_SALE';
CREATE OR REPLACE VIEW v14_ex_b AS
  SELECT status, COUNT(*) AS cnt FROM s14_ex_prod GROUP BY status;
CREATE OR REPLACE VIEW v14_ex_c AS
  SELECT DISTINCT status FROM s14_ex_prod;
CREATE OR REPLACE VIEW v14_ex_d AS
  SELECT product_id, name, price, price * 1.1 AS price_with_vat FROM s14_ex_prod;

-- (2-1) 예측:
--   a = YES  : 단순 WHERE 필터. 원본 행과 1:1 대응 → 업데이트 가능
--   b = NO   : GROUP BY + COUNT 집계 → 어느 원본 행을 바꿀지 특정 불가
--   c = NO   : DISTINCT → 여러 원본 행이 한 결과 행으로 접힘 → 특정 불가
--   d = YES  : 파생 컬럼(price_with_vat)이 있지만 원본 컬럼도 있어 행 대응이 유지됨 → 뷰 자체는 업데이트 가능

-- (2-2) 확인
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='shop' AND TABLE_NAME IN ('v14_ex_a','v14_ex_b','v14_ex_c','v14_ex_d')
ORDER BY TABLE_NAME;
-- +------------+--------------+
-- | v14_ex_a   | YES          |
-- | v14_ex_b   | NO           |
-- | v14_ex_c   | NO           |
-- | v14_ex_d   | YES          |
-- +------------+--------------+

-- (2-3) v14_ex_d 는 업데이트 가능하지만, "파생 컬럼 자체"는 UPDATE 할 수 없다:
UPDATE v14_ex_d SET price_with_vat = 999 WHERE product_id = 1;
--   → ERROR 1348 (HY000): Column 'price_with_vat' is not updatable
--
-- 해설: 뷰가 업데이트 가능하다는 것과, 그 뷰의 "모든 컬럼"이 업데이트 가능하다는 것은 다릅니다.
--   price 처럼 원본 컬럼을 그대로 노출한 컬럼은 UPDATE 되지만,
--   price*1.1 같은 계산 컬럼은 "역으로 원본에 무엇을 써야 하는지" 알 수 없어 UPDATE 불가입니다.
UPDATE v14_ex_d SET price = 50000 WHERE product_id = 1;    -- 이건 성공 (원본 컬럼이라서)


-- ---------------------------------------------------------------------
-- [정답 3] WITH CHECK OPTION 으로 재고 통제
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v14_ex_instock AS
SELECT product_id, name, stock, status
FROM s14_ex_prod
WHERE stock > 0
WITH CHECK OPTION;

-- 재고가 있는 상품 하나를 고른다
SELECT product_id, stock FROM s14_ex_prod WHERE stock > 0 ORDER BY product_id LIMIT 1;

-- (a) stock 을 1 로 → 여전히 stock > 0 이므로 성공
UPDATE v14_ex_instock SET stock = 1 WHERE product_id = 1;
SELECT product_id, stock FROM s14_ex_prod WHERE product_id = 1;

-- (b) stock 을 0 으로 → 뷰 조건(stock > 0) 위반 → 거부
UPDATE v14_ex_instock SET stock = 0 WHERE product_id = 1;
--   → ERROR 1369 (HY000): CHECK OPTION failed 'shop.v14_ex_instock'
--
-- 해설: CHECK OPTION 이 없었다면 stock=0 UPDATE 가 통과하면서 그 상품이 뷰에서 사라졌을 것입니다.
--   "재고 있는 상품 뷰"를 통해 재고를 0 으로 만드는 모순을 DB 가 막아줍니다.


-- ---------------------------------------------------------------------
-- [정답 4] LOCAL vs CASCADED 결과 예측
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s14_ex_emp;
CREATE TABLE s14_ex_emp (
  emp_id INT PRIMARY KEY, dept VARCHAR(20) NOT NULL, salary INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO s14_ex_emp VALUES (1,'개발',5000),(2,'개발',3000),(3,'영업',4000);

CREATE OR REPLACE VIEW v14_ex_dev AS
  SELECT * FROM s14_ex_emp WHERE dept = '개발';                    -- CHECK OPTION 없음

-- (4-1) LOCAL 버전
CREATE OR REPLACE VIEW v14_ex_dev_highpay AS
  SELECT * FROM v14_ex_dev WHERE salary >= 4000 WITH LOCAL CHECK OPTION;

UPDATE v14_ex_dev_highpay SET dept = '영업' WHERE emp_id = 1;
--   → 성공! emp_id=1 이 dept='영업' 이 된다.
--   해설: LOCAL 은 "이 뷰 자신의 조건(salary >= 4000)"만 검사합니다.
--         dept 를 바꿔도 salary 는 그대로 5000 이라 자기 조건은 만족합니다.
--         부모 뷰 v14_ex_dev 의 조건(dept='개발')은 LOCAL 이라 검사하지 않으므로 통과합니다.
--         (단, dept='영업'이 되었으니 이제 이 행은 부모 뷰에서 사라집니다.)
SELECT emp_id, dept, salary FROM s14_ex_emp WHERE emp_id = 1;      -- 영업, 5000
UPDATE s14_ex_emp SET dept = '개발' WHERE emp_id = 1;              -- 원복

-- (4-2) CASCADED 버전
CREATE OR REPLACE VIEW v14_ex_dev_highpay AS
  SELECT * FROM v14_ex_dev WHERE salary >= 4000 WITH CASCADED CHECK OPTION;

UPDATE v14_ex_dev_highpay SET dept = '영업' WHERE emp_id = 1;
--   → ERROR 1369 CHECK OPTION failed
--   해설: CASCADED 는 부모 뷰 v14_ex_dev 의 조건(dept='개발')까지 검사합니다.
--         dept 를 '영업'으로 바꾸면 부모 조건을 위반하므로 거부됩니다.
--
-- 결론: 같은 UPDATE 가 LOCAL 이면 성공, CASCADED 면 실패. 기본값은 CASCADED 입니다.


-- ---------------------------------------------------------------------
-- [정답 5] 생성 컬럼 + 인덱스로 이메일 도메인 검색
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s14_ex_cust;
CREATE TABLE s14_ex_cust AS SELECT customer_id, email, name FROM customers;
ALTER TABLE s14_ex_cust ADD PRIMARY KEY (customer_id);
UPDATE s14_ex_cust SET email = REPLACE(email, 'example.com', 'test.org') WHERE customer_id % 3 = 0;

-- Before: 함수를 씌운 조건은 인덱스를 못 탄다 → type: ALL
EXPLAIN SELECT * FROM s14_ex_cust WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';

-- 생성 컬럼 추가 + 인덱스
ALTER TABLE s14_ex_cust
  ADD COLUMN email_domain VARCHAR(120) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
ALTER TABLE s14_ex_cust ADD INDEX idx_domain (email_domain);
ANALYZE TABLE s14_ex_cust;

-- After: 생성 컬럼으로 조회 → type: ref
EXPLAIN SELECT * FROM s14_ex_cust WHERE email_domain = 'example.com';

-- 보너스: 원래의 함수 표현식으로 그대로 써도 옵티마이저가 생성 컬럼 인덱스로 자동 매핑한다!
--         (표현식이 생성 컬럼 정의와 글자까지 일치하면 됨)
EXPLAIN SELECT * FROM s14_ex_cust WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';
-- → 둘 다 type: ref, key: idx_domain 으로 나옵니다.
--
-- 해설: 애플리케이션 코드를 email_domain 으로 고치지 않아도 됩니다.
--   원래 쓰던 SUBSTRING_INDEX(...) 조건 그대로 두면, MySQL 이 "아, 이건 생성 컬럼과 같네" 하고
--   인덱스를 씁니다. 무중단으로 인덱스를 도입하는 좋은 방법입니다.


-- ---------------------------------------------------------------------
-- [정답 6] 함수 기반 인덱스가 안 먹는 쿼리 진단
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS s14_ex_log;
CREATE TABLE s14_ex_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  logged_at DATETIME NOT NULL,
  amount INT NOT NULL,
  INDEX idx_ym ((YEAR(logged_at)))
) ENGINE=InnoDB;
INSERT INTO s14_ex_log (logged_at, amount)
SELECT TIMESTAMPADD(DAY, n, '2022-01-01'), n FROM tally WHERE n <= 1000;
ANALYZE TABLE s14_ex_log;

-- (a) YEAR(logged_at) = 2023  →  인덱스를 탄다 (type: ref, key: idx_ym)
EXPLAIN SELECT * FROM s14_ex_log WHERE YEAR(logged_at) = 2023;
--   표현식이 인덱스 정의 YEAR(logged_at) 와 정확히 일치 → 매핑 성공.

-- (b) YEAR(logged_at) + 0 = 2023  →  못 탄다 (type: ALL)
EXPLAIN SELECT * FROM s14_ex_log WHERE YEAR(logged_at) + 0 = 2023;
--   이유: 인덱스 표현식은 "YEAR(logged_at)" 인데 쿼리 표현식은 "YEAR(logged_at) + 0" 입니다.
--         옵티마이저는 수학적으로 같은지 따지지 않고 표현식을 "문자 그대로" 매칭합니다.
--         + 0 하나 붙었다고 인덱스가 죽습니다.
--   고치기: + 0 을 빼서 (a) 형태로 쓴다.

-- (c) logged_at >= '2023-01-01' AND logged_at < '2024-01-01'  →  못 탄다 (type: ALL)
EXPLAIN SELECT * FROM s14_ex_log WHERE logged_at >= '2023-01-01' AND logged_at < '2024-01-01';
--   이유: 이 조건은 logged_at "원본 컬럼"에 대한 범위 검색인데,
--         우리에겐 YEAR(logged_at) 함수 인덱스만 있고 logged_at 자체 인덱스는 없습니다.
--         함수 인덱스 YEAR(logged_at) 로는 원본 컬럼의 범위 검색을 처리할 수 없습니다.
--   고치기: logged_at 원본에 일반 인덱스를 만든다.
ALTER TABLE s14_ex_log ADD INDEX idx_logged (logged_at);
ANALYZE TABLE s14_ex_log;
EXPLAIN SELECT * FROM s14_ex_log WHERE logged_at >= '2023-01-01' AND logged_at < '2024-01-01';
--   → 이제 type: range, key: idx_logged 로 바뀝니다.
--
-- 💡 중요한 교훈:
--   (a) YEAR(logged_at)=2023 과 (c) 범위 검색은 "같은 데이터"를 찾지만,
--   실무에서는 (c) 의 범위 형태(sargable)가 더 낫습니다.
--   YEAR() 함수 인덱스는 "연도 단위" 조회에만 쓸 수 있지만,
--   logged_at 원본 인덱스는 "임의 기간"(지난 7일, 이번 분기 등) 조회를 전부 커버합니다.
--   즉 함수 인덱스를 남발하기보다, 원본 컬럼에 범위로 접근하도록 쿼리를 짜는 편이
--   더 범용적입니다. (Step 15/16 의 "인덱스를 못 타는 패턴"에서 다시 다룹니다.)


-- ---------------------------------------------------------------------
-- 정리
-- ---------------------------------------------------------------------
DROP VIEW IF EXISTS v14_ex_customer_stats, v14_ex_a, v14_ex_b, v14_ex_c, v14_ex_d,
                    v14_ex_instock, v14_ex_dev_highpay, v14_ex_dev;
DROP TABLE IF EXISTS s14_ex_prod, s14_ex_emp, s14_ex_cust, s14_ex_log;

SELECT 'Step 14 solution 완료' AS msg;