#Step 14 — 성능과 최종 프로젝트

JPAQuery<Customer> query = queryFactory
        .selectFrom(customer)
        .where(customer.grade.eq(Grade.VIP)
                .and(customer.points.goe(10000)))
        .orderBy(customer.points.desc());

System.out.println(query);      // toString()

List<Customer> result = query.fetch();

// 괄호가 어떻게 붙었는지 JPQL 로 확인
System.out.println(queryFactory.selectFrom(customer)
        .where(customer.city.eq("서울")
                .and(customer.grade.eq(Grade.VIP))
                .or(customer.points.goe(10000))));

Statistics stats = em.getEntityManagerFactory()
        .unwrap(SessionFactory.class).getStatistics();
stats.clear();

// ... 쿼리 실행 ...

System.out.println("쿼리 개수: " + stats.getQueryExecutionCount());
System.out.println("엔티티 로드: " + stats.getEntityLoadCount());
System.out.println("컬렉션 페치: " + stats.getCollectionFetchCount());

List<Order> orders = queryFactory
        .selectFrom(order)
        .limit(10)
        .fetch();

for (Order o : orders) {
    System.out.println(o.getId() + " → " + o.getCustomer().getName());
}

List<Order> orders = queryFactory
        .selectFrom(order)
        .join(order.customer, customer).fetchJoin()
        .limit(10)
        .fetch();

for (Order o : orders) {
    System.out.println(o.getId() + " → " + o.getCustomer().getName());
}

@BatchSize(size = 100)
@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
private List<OrderItem> orderItems = new ArrayList<>();

List<Order> orders = queryFactory
        .selectFrom(order)
        .limit(10)
        .fetch();

for (Order o : orders) {
    System.out.println(o.getId() + " → " + o.getOrderItems().size());
}

List<OrderSummary> result = queryFactory
        .select(new QOrderSummary(
                order.id,
                order.orderDate,
                order.totalAmount,
                customer.name,
                customer.grade))
        .from(order)
        .join(order.customer, customer)
        .limit(10)
        .fetch();

// ❌ 전부 셉니다
List<Customer> result = queryFactory
        .selectFrom(customer)
        .where(JPAExpressions
                .select(order.count())
                .from(order)
                .where(order.customer.eq(customer))
                .gt(0L))
        .fetch();

// ✅ 첫 행에서 멈춥니다
List<Customer> result = queryFactory
        .selectFrom(customer)
        .where(JPAExpressions
                .selectOne()
                .from(order)
                .where(order.customer.eq(customer))
                .exists())
        .fetch();

// ❌ 엔티티를 만들고 버림
boolean exists = queryFactory.selectFrom(customer)
        .where(customer.email.eq(email))
        .fetchFirst() != null;

// ✅ 1만 읽음
Integer found = queryFactory.selectOne()
        .from(customer)
        .where(customer.email.eq(email))
        .fetchFirst();
boolean exists = found != null;

// A — 엔티티 전체
List<Product> entities = queryFactory
        .selectFrom(product)
        .fetch();

// B — 필요한 두 컬럼만
List<Tuple> tuples = queryFactory
        .select(product.name, product.price)
        .from(product)
        .fetch();

List<Product> result = queryFactory
        .selectFrom(product)
        .where(product.status.eq(ProductStatus.ON_SALE)
                .and(product.price.between(new BigDecimal("100000"), new BigDecimal("500000"))))
        .orderBy(product.price.desc())
        .fetch();

// ❌
.where(order.orderDate.year().eq(2025))
.where(customer.name.lower().eq("김서준"))
.orderBy(new CaseBuilder().when(...).then(...).otherwise(...).desc())

// ✅ 범위로 바꿉니다
.where(order.orderDate.goe(LocalDateTime.of(2025, 1, 1, 0, 0))
        .and(order.orderDate.lt(LocalDateTime.of(2026, 1, 1, 0, 0))))

// ✅ 콜레이션으로 해결합니다 (utf8mb4_0900_ai_ci 는 이미 대소문자 무시)
.where(customer.name.eq("김서준"))

// ✅ 정렬용 컬럼을 두거나, 애플리케이션에서 정렬합니다

// ❌ contains 는 '%키워드%' 를 만듭니다
.where(product.name.contains("노트북"))

// ✅ 앞부분 일치 — 인덱스 사용 가능
.where(product.name.startsWith("노트북"))

// customer.id 는 NumberPath<Long>, DB 는 BIGINT — 이 자체는 문제 없습니다.
// 문제는 문자열 컬럼에 숫자를 비교하거나 그 반대일 때 생깁니다.

// ❌ 템플릿에서는 타입 검사가 없습니다
Expressions.booleanTemplate("{0} = {1}", customer.id, "3")

// ❌
.where(product.name.eq("노트북")
        .or(product.status.eq(ProductStatus.SOLD_OUT))
        .or(product.stock.eq(0)))

// ✅ 같은 컬럼의 or 는 in 으로
.where(product.status.in(ProductStatus.SOLD_OUT, ProductStatus.HIDDEN))

// ✅ 다른 컬럼이면 union 으로 쪼갭니다 (각 쿼리가 자기 인덱스를 씁니다)
List<Product> byName = queryFactory.selectFrom(product)
        .where(product.name.eq("노트북")).fetch();
List<Product> byStock = queryFactory.selectFrom(product)
        .where(product.stock.eq(0)).fetch();
// 자바에서 합칩니다 (JPQL 은 UNION 을 지원하지 않습니다)

@Transactional(readOnly = true)
public List<ProductSearchResponse> search(ProductSearchCond cond, Pageable pageable) {
    ...
}

package com.example.shop.step14;

import com.example.shop.entity.ProductStatus;

import java.math.BigDecimal;
import java.util.List;

public record ProductSearchCond(
        String keyword,
        Long categoryId,
        BigDecimal minPrice,
        BigDecimal maxPrice,
        Boolean inStockOnly,
        List<ProductStatus> statuses,
        String sort,            // "price" | "rating" | "created"
        String direction        // "asc" | "desc"
) {}

package com.example.shop.step14;

import com.example.shop.entity.ProductStatus;
import com.querydsl.core.annotations.QueryProjection;

import java.math.BigDecimal;
import java.time.LocalDateTime;

public record ProductSearchResponse(
        Long productId,
        String name,
        BigDecimal price,
        Integer stock,
        ProductStatus status,
        String categoryName,
        Double avgRating,
        Long reviewCount,
        LocalDateTime createdAt
) {
    @QueryProjection
    public ProductSearchResponse {
    }
}

package com.example.shop.step14;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface ProductRepositoryCustom {
    Page<ProductSearchResponse> search(ProductSearchCond cond, Pageable pageable);
    Page<ProductSearchResponse> searchWithGroupBy(ProductSearchCond cond, Pageable pageable);
}

package com.example.shop.step14;

import com.example.shop.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository
        extends JpaRepository<Product, Long>, ProductRepositoryCustom {
}

package com.example.shop.step14;

import com.example.shop.entity.ProductStatus;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.JPAExpressions;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.support.PageableExecutionUtils;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import static com.example.shop.entity.QCategory.category;
import static com.example.shop.entity.QProduct.product;
import static com.example.shop.entity.QReview.review;

@RequiredArgsConstructor
public class ProductRepositoryImpl implements ProductRepositoryCustom {

    private final JPAQueryFactory queryFactory;

    // ── 정렬 화이트리스트 (Step 10) ─────────────────────────────────
    // 사용자 입력은 이 Map 의 "키"로만 쓰입니다.
    // SQL 에 들어가는 것은 코드에 이미 존재하는 표현식뿐입니다.
    private static final Map<String, OrderSpecifier<?>> SORT_ASC = Map.of(
            "price",   product.price.asc(),
            "created", product.createdAt.asc(),
            "name",    product.name.asc());

    private static final Map<String, OrderSpecifier<?>> SORT_DESC = Map.of(
            "price",   product.price.desc(),
            "created", product.createdAt.desc(),
            "name",    product.name.desc());

    private static final OrderSpecifier<?> DEFAULT_SORT = product.id.desc();

    @Override
    public Page<ProductSearchResponse> search(ProductSearchCond cond, Pageable pageable) {

        // ── 콘텐츠 쿼리 ────────────────────────────────────────────
        List<ProductSearchResponse> content = queryFactory
                .select(new QProductSearchResponse(
                        product.id,
                        product.name,
                        product.price,
                        product.stock,
                        product.status,
                        category.name,
                        // 평균 평점 — 상관 서브쿼리
                        JPAExpressions
                                .select(review.rating.avg())
                                .from(review)
                                .where(review.product.eq(product)),
                        // 후기 수 — 상관 서브쿼리
                        JPAExpressions
                                .select(review.count())
                                .from(review)
                                .where(review.product.eq(product)),
                        product.createdAt))
                .from(product)
                .join(product.category, category)
                .where(
                        keywordContains(cond.keyword()),
                        categoryIn(cond.categoryId()),
                        priceGoe(cond.minPrice()),
                        priceLoe(cond.maxPrice()),
                        inStock(cond.inStockOnly()),
                        statusIn(cond.statuses()))
                .orderBy(toOrder(cond.sort(), cond.direction()), product.id.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        // ── count 쿼리 (분리) ──────────────────────────────────────
        // 서브쿼리와 join 을 뺀 최소 형태입니다.
        JPAQuery<Long> countQuery = queryFactory
                .select(product.count())
                .from(product)
                .where(
                        keywordContains(cond.keyword()),
                        categoryIn(cond.categoryId()),
                        priceGoe(cond.minPrice()),
                        priceLoe(cond.maxPrice()),
                        inStock(cond.inStockOnly()),
                        statusIn(cond.statuses()));

        // count 쿼리를 "필요할 때만" 실행합니다 (Step 09)
        return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
    }

    // ── 동적 조건 (Step 04, 10) ─────────────────────────────────────
    // null 을 반환하면 where 에서 무시됩니다.
    // BooleanBuilder 대신 메서드로 분리하면 재사용과 조합이 됩니다.

    private BooleanExpression keywordContains(String keyword) {
        return (keyword == null || keyword.isBlank())
                ? null
                : product.name.contains(keyword);
    }

    /** 선택한 카테고리 + 그 하위 카테고리 (Step 07) */
    private BooleanExpression categoryIn(Long categoryId) {
        if (categoryId == null) {
            return null;
        }
        return product.category.id.eq(categoryId)
                .or(product.category.id.in(
                        JPAExpressions
                                .select(category.id)
                                .from(category)
                                .where(category.parent.id.eq(categoryId))));
    }

    private BooleanExpression priceGoe(BigDecimal min) {
        return min == null ? null : product.price.goe(min);
    }

    private BooleanExpression priceLoe(BigDecimal max) {
        return max == null ? null : product.price.loe(max);
    }

    private BooleanExpression inStock(Boolean inStockOnly) {
        return Boolean.TRUE.equals(inStockOnly) ? product.stock.gt(0) : null;
    }

    private BooleanExpression statusIn(List<ProductStatus> statuses) {
        return (statuses == null || statuses.isEmpty())
                ? null
                : product.status.in(statuses);
    }

    /** 정렬 화이트리스트 (Step 10). 입력은 키로만 쓰입니다. */
    private OrderSpecifier<?> toOrder(String sort, String direction) {
        if (sort == null) {
            return DEFAULT_SORT;
        }
        Map<String, OrderSpecifier<?>> table =
                "asc".equalsIgnoreCase(direction) ? SORT_ASC : SORT_DESC;
        return table.getOrDefault(sort, DEFAULT_SORT);
    }
}

JPAExpressions.select(review.rating.avg())
        .from(review)
        .where(review.product.eq(product))
// →
Expressions.numberTemplate(Double.class, "coalesce({0}, {1})",
        JPAExpressions.select(review.rating.avg())
                .from(review).where(review.product.eq(product)),
        0.0)

public record ProductSearchResponse(...) {
    @QueryProjection
    public ProductSearchResponse {
        avgRating = avgRating == null ? 0.0 : avgRating;
    }
}

@Override
public Page<ProductSearchResponse> searchWithGroupBy(ProductSearchCond cond, Pageable pageable) {

    List<ProductSearchResponse> content = queryFactory
            .select(new QProductSearchResponse(
                    product.id,
                    product.name,
                    product.price,
                    product.stock,
                    product.status,
                    category.name,
                    review.rating.avg(),
                    review.count(),
                    product.createdAt))
            .from(product)
            .join(product.category, category)
            .leftJoin(review).on(review.product.eq(product))    // ★ left join 필수
            .where(
                    keywordContains(cond.keyword()),
                    categoryIn(cond.categoryId()),
                    priceGoe(cond.minPrice()),
                    priceLoe(cond.maxPrice()),
                    inStock(cond.inStockOnly()),
                    statusIn(cond.statuses()))
            .groupBy(product.id, product.name, product.price, product.stock,
                     product.status, category.name, product.createdAt)   // ★ 전부 나열
            .orderBy(toOrder(cond.sort(), cond.direction()), product.id.desc())
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

    JPAQuery<Long> countQuery = queryFactory
            .select(product.count())
            .from(product)
            .where(/* 위와 동일 */);

    return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
}

private static final Map<String, OrderSpecifier<?>> SORT_DESC_B = Map.of(
        "price",   product.price.desc(),
        "created", product.createdAt.desc(),
        "name",    product.name.desc(),
        "rating",  review.rating.avg().desc());     // ★ 집계값 정렬

package com.example.shop.step14;

import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.web.PageableDefault;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/products")
@RequiredArgsConstructor
public class ProductSearchController {

    private final ProductSearchService productSearchService;

    @GetMapping("/search")
    public Page<ProductSearchResponse> search(
            @ModelAttribute ProductSearchCond cond,
            @PageableDefault(size = 20) Pageable pageable) {
        return productSearchService.search(cond, pageable);
    }
}

package com.example.shop.step14;

import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)      // 14-8
public class ProductSearchService {

    private final ProductRepository productRepository;

    public Page<ProductSearchResponse> search(ProductSearchCond cond, Pageable pageable) {
        return productRepository.searchWithGroupBy(cond, pageable);
    }
}

@Test
void 상품검색_SQL이_바뀌지_않았는가() {
    // JPQL 스냅샷 (14-1 ④)
    JPAQuery<?> query = buildSearchQuery(cond);
    assertThat(query.toString()).isEqualTo(EXPECTED_JPQL);

    // 쿼리 개수 (14-1 ⑤)
    stats.clear();
    query.fetch();
    assertThat(stats.getQueryExecutionCount()).isEqualTo(1L);
}