select * from customer; -- 6

-- 1. 회원가입
insert into customer(name, email) values('cos', '[email protected]');
commit;

-- 2. 상품 목록
select p.product_id, p.name pname, p.price, p.stock, c.name cname
from product p inner join category c
on p.category_id = c.category_id;

-- 3. 장바구니 담기 (ssar이 운동화1개, 청바지2개 장바구니 담기)

-- 3.1 cart insert
select * from cart; -- 6
insert into cart(customer_id) values(6);

-- 3.2 cart item insert 2번
insert into cart_item(cart_id, product_id, qty) 
values(6, 4, 1);
insert into cart_item(cart_id, product_id, qty) 
values(6, 5, 2);

commit;

-- 4 장바구니 잘담겼는지 확인 (ssar=6, cart_id=6)
select p.name pname, p.price price, r1.qty
from
(
select * from cart_item 
where cart_id = (select cart_id from cart where customer_id = 6)
) r1 inner join product p
on p.product_id = r1.product_id;

-- 5. 주문하기
select * from orders;
-- 5.1 주문생성 (customer_id=6, status, ordered_at) 로그인한 아이디, complete, now()
insert into orders(customer_id, ordered_at) values(6, now());

-- 5.2 주문아이템 생성 - 6번
select * from order_item;
insert into order_item(order_id, product_id, qty, price_at_order)
values(6, 4, 1, 120000);
insert into order_item(order_id, product_id, qty, price_at_order)
values(6, 5, 2, 120000);

-- 5.3 주문스냅샵 생성
insert into order_snapshot(order_id, total_amount, detail, snapshot_at) 
values(6, 240000, '[{"price": 120000, "qty": 1, "product_id": 4, "product_name": "운동화"}, {"price": 120000, "qty": 2, "product_id": 5, "product_name": "청바지"}]', now());

-- 5.4 상품 재고 감소
update product set stock = 6 where product_id = 4;
update product set stock = 6 where product_id = 5;

select * from product;

commit;

-- 5.5 주문 취소
select * from orders;
select * from order_item;
select * from order_snapshot;

update orders set status='cancel' where order_id = 6;
update product set stock = 7 where product_id = 4;
update product set stock = 8 where product_id = 5;
commit;