Advanced

import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;

// => Entity
@Entity
// => Marks class as JPA entity (database table mapping)
public class Product {
    @Id @GeneratedValue
    // => Primary key field
    private Long id;
    private String name;
    private BigDecimal price;
    private boolean active;
    // getters/setters
}

// => Specification for active products
public class ProductSpecifications {
    public static Specification<Product> isActive() {
        return (Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            // => Root: represents Product entity in query
            // => CriteriaBuilder: creates predicates, expressions
            return cb.equal(root.get("active"), true);
            // => Same as: WHERE active = true
        };
    }
}

// => Repository with JpaSpecificationExecutor
public interface ProductRepository extends JpaRepository<Product, Long>,
                                           JpaSpecificationExecutor<Product> {
    // => JpaSpecificationExecutor adds findAll(Specification) methods
}

// => Usage in service
@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public List<Product> getActiveProducts() {
        // => Execute specification
        return productRepository.findAll(ProductSpecifications.isActive());
        // => Returns only products where active = true
    }
}

public class ProductSpecifications {
    public static Specification<Product> isActive() {
        return (root, query, cb) -> cb.equal(root.get("active"), true);
    }

    public static Specification<Product> hasMinPrice(BigDecimal minPrice) {
        return (root, query, cb) ->
            cb.greaterThanOrEqualTo(root.get("price"), minPrice);
            // => WHERE price >= :minPrice
    }

    public static Specification<Product> hasCategory(String category) {
        return (root, query, cb) ->
            cb.equal(root.get("category"), category);
            // => WHERE category = :category
    }
}

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public List<Product> searchProducts(BigDecimal minPrice, String category) {
        // => Combine specifications with AND
        Specification<Product> spec = Specification
            .where(ProductSpecifications.isActive())
            // => WHERE active = true
            .and(ProductSpecifications.hasMinPrice(minPrice))
            // => AND price >= :minPrice
            .and(ProductSpecifications.hasCategory(category));
            // => AND category = :category

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
        // => Final query: WHERE active = true AND price >= :minPrice AND category = :category
    }

    public List<Product> searchFlexible(BigDecimal minPrice, String cat1, String cat2) {
        // => Complex combination: AND with OR
        Specification<Product> spec = Specification
            .where(ProductSpecifications.isActive())
            .and(ProductSpecifications.hasMinPrice(minPrice))
            .and(ProductSpecifications.hasCategory(cat1)
                .or(ProductSpecifications.hasCategory(cat2)));
                // => WHERE active = true AND price >= :minPrice
                //    AND (category = :cat1 OR category = :cat2)

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }
}

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public List<Product> searchWithFilters(
        String name,
        BigDecimal minPrice,
        BigDecimal maxPrice,
        String category
    ) {
        // => Start with null specification (no filtering)
        Specification<Product> spec = Specification.where(null);
        // => Specification.where(null) returns a pass-through specification

        // => Add filters conditionally
        if (name != null && !name.isEmpty()) {
            spec = spec.and(hasNameContaining(name));
            // => Only add if name parameter provided
        }

        if (minPrice != null) {
            spec = spec.and(hasMinPrice(minPrice));
            // => Only add if minPrice parameter provided
        }

        if (maxPrice != null) {
            spec = spec.and(hasMaxPrice(maxPrice));
            // => Only add if maxPrice parameter provided
        }

        if (category != null && !category.isEmpty()) {
            spec = spec.and(hasCategory(category));
            // => Only add if category parameter provided
        }

        // => Execute composed specification
        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
        // => Query adapts to provided parameters
        // => Example: If only name and minPrice provided:
        //    WHERE name LIKE :name AND price >= :minPrice
    }

    // => Helper specifications
    private static Specification<Product> hasNameContaining(String name) {
        return (root, query, cb) ->
            cb.like(cb.lower(root.get("name")), "%" + name.toLowerCase() + "%");
            // => Case-insensitive LIKE with wildcards
    }

    private static Specification<Product> hasMaxPrice(BigDecimal maxPrice) {
        return (root, query, cb) ->
            cb.lessThanOrEqualTo(root.get("price"), maxPrice);
            // => WHERE price <= :maxPrice
    }
}

// => Entities with relationship
@Entity
// => Marks class as JPA entity (database table mapping)
public class Order {
    @Id @GeneratedValue
    // => Primary key field
    private Long id;
    private LocalDateTime orderDate;
    private String status;

    @ManyToOne
    // => Defines entity relationship for foreign key mapping
    @JoinColumn(name = "customer_id")
    // => Foreign key column: customer_id
    private Customer customer;
    // => Order belongs to Customer

    @OneToMany(mappedBy = "order")
    // => Defines entity relationship for foreign key mapping
    private List<OrderItem> items = new ArrayList<>();
    // => Order has many OrderItems
}

@Entity
// => Marks class as JPA entity (database table mapping)
public class Customer {
    @Id @GeneratedValue
    // => Primary key field
    private Long id;
    private String email;
    private String tier; // GOLD, SILVER, BRONZE
}

@Entity
// => Marks class as JPA entity (database table mapping)
public class OrderItem {
    @Id @GeneratedValue
    // => Primary key field
    private Long id;
    private Integer quantity;

    @ManyToOne
    // => Defines entity relationship for foreign key mapping
    @JoinColumn(name = "order_id")
    // => Foreign key column: order_id
    private Order order;

    @ManyToOne
    // => Defines entity relationship for foreign key mapping
    @JoinColumn(name = "product_id")
    // => Foreign key column: product_id
    private Product product;
}

// => Specifications with joins
public class OrderSpecifications {
    public static Specification<Order> hasCustomerEmail(String email) {
        return (root, query, cb) -> {
            // => Join to Customer entity
            Join<Order, Customer> customerJoin = root.join("customer");
            // => Navigate from Order to Customer relationship

            return cb.equal(customerJoin.get("email"), email);
            // => WHERE customer.email = :email
            // => SQL: ... JOIN customer ON order.customer_id = customer.id
            //         WHERE customer.email = :email
        };
    }

    public static Specification<Order> hasCustomerTier(String tier) {
        return (root, query, cb) -> {
            Join<Order, Customer> customerJoin = root.join("customer");
            return cb.equal(customerJoin.get("tier"), tier);
            // => Filter by joined entity property
        };
    }

    public static Specification<Order> hasProductInItems(String productName) {
        return (root, query, cb) -> {
            // => Multiple joins: Order -> OrderItem -> Product
            Join<Order, OrderItem> itemsJoin = root.join("items");
            // => Join from Order to OrderItem

            Join<OrderItem, Product> productJoin = itemsJoin.join("product");
            // => Join from OrderItem to Product

            return cb.equal(productJoin.get("name"), productName);
            // => WHERE product.name = :productName
            // => SQL: ... JOIN order_item ON order.id = order_item.order_id
            //         JOIN product ON order_item.product_id = product.id
            //         WHERE product.name = :productName
        };
    }
}

@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;

    public List<Order> findGoldCustomerOrders() {
        // => Find all orders from GOLD tier customers
        return orderRepository.findAll(
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
            OrderSpecifications.hasCustomerTier("GOLD")
        );
        // => Automatically generates proper join SQL
    }

    public List<Order> findOrdersWithProduct(String productName, String customerEmail) {
        // => Combine multiple joins
        Specification<Order> spec = Specification
            .where(OrderSpecifications.hasProductInItems(productName))
            .and(OrderSpecifications.hasCustomerEmail(customerEmail));
            // => WHERE product.name = :productName AND customer.email = :customerEmail
            // => Multiple joins in single query

        return orderRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }
}

public class ProductSpecifications {
    public static Specification<Product> isNotActive() {
        return (root, query, cb) -> {
            // => Use NOT to negate predicate
            Predicate activePredicate = cb.equal(root.get("active"), true);
            return cb.not(activePredicate);
            // => WHERE NOT (active = true)
            // => Equivalent to: WHERE active = false
        };
    }

    public static Specification<Product> isNotInCategories(List<String> excludedCategories) {
        return (root, query, cb) -> {
            // => Negate IN clause
            Predicate inPredicate = root.get("category").in(excludedCategories);
            return cb.not(inPredicate);
            // => WHERE NOT (category IN (:categories))
            // => Equivalent to: WHERE category NOT IN (:categories)
        };
    }

    public static Specification<Product> hasComplexFilter(
        BigDecimal minPrice,
        BigDecimal maxPrice,
        List<String> excludedCategories
    ) {
        return (root, query, cb) -> {
            // => Build multiple predicates
            Predicate pricePredicate = cb.between(
                root.get("price"),
                minPrice,
                maxPrice
            );
            // => price BETWEEN :minPrice AND :maxPrice

            Predicate categoryPredicate = cb.not(
                root.get("category").in(excludedCategories)
            );
            // => category NOT IN (:excludedCategories)

            Predicate activePredicate = cb.equal(root.get("active"), true);
            // => active = true

            // => Combine with AND
            return cb.and(pricePredicate, categoryPredicate, activePredicate);
            // => WHERE (price BETWEEN :minPrice AND :maxPrice)
            //    AND (category NOT IN (:excludedCategories))
            //    AND (active = true)
        };
    }
}

// => Alternative: Using Specification composition with NOT
@Service
public class ProductService {
    public List<Product> findActiveProductsExcludingCategories(
        List<String> excludedCategories
    ) {
        // => Compose with Specification.not()
        Specification<Product> spec = Specification
            .where(ProductSpecifications.isActive())
            .and(Specification.not(
                ProductSpecifications.hasCategory(excludedCategories.get(0))
            ));
            // => WHERE active = true AND NOT (category = :category)

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }

    public List<Product> findWithOrCondition(String cat1, String cat2, BigDecimal minPrice) {
        // => Complex OR with AND
        Specification<Product> spec = (root, query, cb) -> {
            Predicate category1 = cb.equal(root.get("category"), cat1);
            Predicate category2 = cb.equal(root.get("category"), cat2);
            Predicate categoryOr = cb.or(category1, category2);
            // => (category = :cat1 OR category = :cat2)

            Predicate pricePredicate = cb.greaterThanOrEqualTo(root.get("price"), minPrice);
            // => price >= :minPrice

            return cb.and(categoryOr, pricePredicate);
            // => WHERE (category = :cat1 OR category = :cat2) AND price >= :minPrice
        };

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }
}

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public Page<Product> searchProductsPaged(
        String category,
        BigDecimal minPrice,
        int page,
        int size,
        String sortBy,
        boolean ascending
    ) {
        // => Build specification
        Specification<Product> spec = Specification
            .where(ProductSpecifications.isActive());

        if (category != null) {
            spec = spec.and(ProductSpecifications.hasCategory(category));
        }

        if (minPrice != null) {
            spec = spec.and(ProductSpecifications.hasMinPrice(minPrice));
        }

        // => Create sort
        Sort sort = ascending
            ? Sort.by(sortBy).ascending()
            : Sort.by(sortBy).descending();
        // => Sort by specified field and direction

        // => Create pageable
        Pageable pageable = PageRequest.of(page, size, sort);
        // => page: 0-based index
        // => size: items per page
        // => sort: sorting criteria

        // => Execute with specification and pagination
        return productRepository.findAll(spec, pageable);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
        // => Returns Page<Product> with:
        //    - content: List of products for current page
        //    - totalElements: total matching products
        //    - totalPages: total number of pages
        //    - number: current page number
        //    - size: page size
    }

    public List<Product> searchWithMultipleSort(String category) {
        // => Sort by multiple fields
        Sort sort = Sort.by(
            Sort.Order.desc("price"),    // Primary: price descending
            Sort.Order.asc("name")       // Secondary: name ascending
        );
        // => ORDER BY price DESC, name ASC

        Specification<Product> spec =
            ProductSpecifications.hasCategory(category);

        return productRepository.findAll(spec, sort);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
        // => Returns sorted list without pagination
    }

    public Page<Product> searchWithDynamicSort(
        Specification<Product> spec,
        int page,
        int size,
        List<String> sortFields,
        List<String> directions
    ) {
        // => Build dynamic sort from lists
        List<Sort.Order> orders = new ArrayList<>();
        // => Creates transient entity (not yet persisted, id=null)

        for (int i = 0; i < sortFields.size(); i++) {
            String field = sortFields.get(i);
            String direction = i < directions.size() ? directions.get(i) : "ASC";

            Sort.Order order = direction.equalsIgnoreCase("DESC")
                ? Sort.Order.desc(field)
                : Sort.Order.asc(field);

            orders.add(order);
        }

        Sort sort = Sort.by(orders);
        // => Dynamic multi-field sorting

        Pageable pageable = PageRequest.of(page, size, sort);
        return productRepository.findAll(spec, pageable);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }
}

// => REST Controller usage
@RestController
@RequestMapping("/api/products")
public class ProductController {
    @Autowired
    private ProductService productService;

    @GetMapping("/search")
    public ResponseEntity<Page<Product>> searchProducts(
        @RequestParam(required = false) String category,
        @RequestParam(required = false) BigDecimal minPrice,
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "name") String sortBy,
        @RequestParam(defaultValue = "true") boolean ascending
    ) {
        Page<Product> products = productService.searchProductsPaged(
            category, minPrice, page, size, sortBy, ascending
        );

        return ResponseEntity.ok(products);
        // => Returns JSON with pagination metadata:
        // {
        //   "content": [...],
        //   "totalElements": 150,
        //   "totalPages": 8,
        //   "number": 0,
        //   "size": 20,
        //   "sort": { "sorted": true, "unsorted": false }
        // }
    }
}

public class OrderSpecifications {
    public static Specification<Order> distinctCustomerOrders() {
        return (root, query, cb) -> {
            // => Mark query as distinct
            query.distinct(true);
            // => SELECT DISTINCT ... prevents duplicate rows

            return cb.conjunction(); // => No WHERE clause (all orders)
            // => cb.conjunction() creates always-true predicate
        };
    }

    public static Specification<Order> withDistinctCustomers(String status) {
        return (root, query, cb) -> {
            query.distinct(true);
            // => DISTINCT eliminates duplicate customers

            Join<Order, Customer> customerJoin = root.join("customer");
            // => Join to Customer

            return cb.equal(root.get("status"), status);
            // => SELECT DISTINCT customer.* FROM orders
            //    JOIN customer ... WHERE status = :status
        };
    }
}

// => Using query method for aggregations
public interface OrderRepository extends JpaRepository<Order, Long>,
                                         JpaSpecificationExecutor<Order> {
    // => COUNT query with specification
    long count(Specification<Order> spec);
    // => Returns count of matching orders

    // => For custom aggregations, use @Query or Criteria API
    @Query("SELECT o.status, COUNT(o) FROM Order o GROUP BY o.status")
    List<Object[]> countByStatus();
    // => Returns [[status1, count1], [status2, count2], ...]
}

@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;

    public long countActiveOrders() {
        // => Count with specification
        Specification<Order> spec = (root, query, cb) ->
            cb.equal(root.get("status"), "ACTIVE");

        return orderRepository.count(spec);
        // => SELECT COUNT(*) FROM orders WHERE status = 'ACTIVE'
    }

    public List<Order> findDistinctCustomerOrders(String status) {
        Specification<Order> spec =
            OrderSpecifications.withDistinctCustomers(status);

        return orderRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
        // => DISTINCT prevents duplicate orders from joins
    }
}

// => Advanced: Custom aggregation with Criteria API
@Repository
public class CustomOrderRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Object[]> getOrderCountByCustomerTier() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // => Get CriteriaBuilder from EntityManager

        CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        // => Query returns Object[] (multiple columns)

        Root<Order> root = query.from(Order.class);
        // => FROM Order

        Join<Order, Customer> customerJoin = root.join("customer");
        // => JOIN Customer

        query.multiselect(
            customerJoin.get("tier"),    // => SELECT customer.tier
            cb.count(root)                // => COUNT(*)
        );
        // => SELECT customer.tier, COUNT(*)

        query.groupBy(customerJoin.get("tier"));
        // => GROUP BY customer.tier

        return entityManager.createQuery(query).getResultList();
        // => Returns [[tier1, count1], [tier2, count2], ...]
    }

    public List<Object[]> getOrderSumByStatus(BigDecimal minTotal) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        Root<Order> root = query.from(Order.class);

        query.multiselect(
            root.get("status"),
            cb.sum(root.get("totalAmount"))
        );
        // => SELECT status, SUM(total_amount)

        query.groupBy(root.get("status"));
        // => GROUP BY status

        query.having(
            cb.greaterThan(cb.sum(root.get("totalAmount")), minTotal)
        );
        // => HAVING SUM(total_amount) > :minTotal

        return entityManager.createQuery(query).getResultList();
    }
}

public class ProductSpecifications {
    public static Specification<Product> hasNameIgnoreCase(String name) {
        return (root, query, cb) -> {
            // => Convert both sides to lowercase
            return cb.equal(
                cb.lower(root.get("name")),
                name.toLowerCase()
            );
            // => WHERE LOWER(name) = LOWER(:name)
            // => Case-insensitive exact match
        };
    }

    public static Specification<Product> nameContainsIgnoreCase(String keyword) {
        return (root, query, cb) -> {
            String pattern = "%" + keyword.toLowerCase() + "%";

            return cb.like(
                cb.lower(root.get("name")),
                pattern
            );
            // => WHERE LOWER(name) LIKE LOWER(:pattern)
            // => Case-insensitive partial match
        };
    }

    public static Specification<Product> hasNonNullDescription() {
        return (root, query, cb) -> {
            return cb.isNotNull(root.get("description"));
            // => WHERE description IS NOT NULL
        };
    }

    public static Specification<Product> hasNullOrEmptyDescription() {
        return (root, query, cb) -> {
            Predicate isNull = cb.isNull(root.get("description"));
            // => description IS NULL

            Predicate isEmpty = cb.equal(root.get("description"), "");
            // => description = ''

            return cb.or(isNull, isEmpty);
            // => WHERE description IS NULL OR description = ''
        };
    }

    public static Specification<Product> hasDescriptionOrDefault() {
        return (root, query, cb) -> {
            // => Use COALESCE for null handling
            Expression<String> descriptionOrDefault = cb.coalesce()
                .value(root.get("description"))
                .value("No description")
                .build();
            // => COALESCE(description, 'No description')

            return cb.equal(descriptionOrDefault, "No description");
            // => WHERE COALESCE(description, 'No description') = 'No description'
            // => Matches null or 'No description'
        };
    }
}

@Service
public class ProductService {
    public List<Product> searchByNameFuzzy(String keyword) {
        // => Case-insensitive search with null check
        Specification<Product> spec = (root, query, cb) -> {
            if (keyword == null || keyword.trim().isEmpty()) {
                return cb.conjunction(); // => Return all if no keyword
            }

            String pattern = "%" + keyword.trim().toLowerCase() + "%";

            return cb.like(
                cb.lower(root.get("name")),
                pattern
            );
        };

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }

    public List<Product> findProductsWithDescription() {
        // => Find products with non-empty description
        Specification<Product> spec = (root, query, cb) -> {
            Predicate notNull = cb.isNotNull(root.get("description"));
            Predicate notEmpty = cb.notEqual(root.get("description"), "");

            return cb.and(notNull, notEmpty);
            // => WHERE description IS NOT NULL AND description != ''
        };

        return productRepository.findAll(spec);
// => Executes SELECT * FROM table
// => Loads ALL records into memory (dangerous for large tables)
// => Returns List<Entity> (never null, empty list if no records)
    }
}

@Repository
public class ProductCriteriaRepository {
    @PersistenceContext
    private EntityManager entityManager;
    // => EntityManager provides access to Criteria API

    public List<Product> findActiveProducts() {
        // Step 1: Get CriteriaBuilder
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // => CriteriaBuilder: factory for creating query components

        // Step 2: Create CriteriaQuery
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        // => Defines return type (Product)

        // Step 3: Define Root
        Root<Product> root = query.from(Product.class);
        // => Root: starting point for query, represents Product table
        // => FROM Product

        // Step 4: Build predicates
        Predicate activePredicate = cb.equal(root.get("active"), true);
        // => WHERE active = true

        // Step 5: Apply WHERE clause
        query.where(activePredicate);
        // => Adds predicate to query

        // Step 6: Create and execute TypedQuery
        TypedQuery<Product> typedQuery = entityManager.createQuery(query);
        // => TypedQuery: executable query with type safety

        return typedQuery.getResultList();
        // => Returns List<Product>
    }

    public List<Product> findProductsByPriceRange(BigDecimal min, BigDecimal max) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> root = query.from(Product.class);

        // => Multiple predicates combined with AND
        Predicate minPredicate = cb.greaterThanOrEqualTo(root.get("price"), min);
        Predicate maxPredicate = cb.lessThanOrEqualTo(root.get("price"), max);
        Predicate combined = cb.and(minPredicate, maxPredicate);
        // => WHERE price >= :min AND price <= :max

        query.where(combined);

        return entityManager.createQuery(query).getResultList();
    }

    public List<Product> findProductsSorted(String sortField, boolean ascending) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> root = query.from(Product.class);

        // => Apply sorting
        if (ascending) {
            query.orderBy(cb.asc(root.get(sortField)));
            // => ORDER BY :sortField ASC
        } else {
            query.orderBy(cb.desc(root.get(sortField)));
            // => ORDER BY :sortField DESC
        }

        return entityManager.createQuery(query).getResultList();
    }
}

@Repository
public class OrderCriteriaRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Order> findOrdersByCustomerEmail(String email) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> query = cb.createQuery(Order.class);
        Root<Order> root = query.from(Order.class);

        // => Join to Customer
        Join<Order, Customer> customerJoin = root.join("customer");
        // => FROM Order o JOIN Customer c ON o.customer_id = c.id

        // => Use joined entity in predicate
        Predicate emailPredicate = cb.equal(customerJoin.get("email"), email);
        // => WHERE c.email = :email

        query.where(emailPredicate);

        return entityManager.createQuery(query).getResultList();
    }

    public List<Order> findOrdersWithProductCategory(String category) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> query = cb.createQuery(Order.class);
        Root<Order> root = query.from(Order.class);

        // => Chain joins: Order -> OrderItem -> Product
        Join<Order, OrderItem> itemJoin = root.join("items");
        Join<OrderItem, Product> productJoin = itemJoin.join("product");
        // => FROM Order o
        //    JOIN OrderItem oi ON o.id = oi.order_id
        //    JOIN Product p ON oi.product_id = p.id

        Predicate categoryPredicate = cb.equal(
            productJoin.get("category"),
            category
        );
        // => WHERE p.category = :category

        query.where(categoryPredicate);
        query.distinct(true); // => Prevent duplicates from join

        return entityManager.createQuery(query).getResultList();
    }

    public List<Order> findOrdersByCustomerTierAndStatus(String tier, String status) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> query = cb.createQuery(Order.class);
        Root<Order> root = query.from(Order.class);

        // => Join for customer tier
        Join<Order, Customer> customerJoin = root.join("customer");

        // => Multiple predicates: joined and root entity
        Predicate tierPredicate = cb.equal(customerJoin.get("tier"), tier);
        // => WHERE customer.tier = :tier

        Predicate statusPredicate = cb.equal(root.get("status"), status);
        // => AND order.status = :status

        query.where(cb.and(tierPredicate, statusPredicate));

        return entityManager.createQuery(query).getResultList();
    }

    public List<Order> findOrdersWithLeftJoin() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> query = cb.createQuery(Order.class);
        Root<Order> root = query.from(Order.class);

        // => LEFT JOIN (includes orders without items)
        root.join("items", JoinType.LEFT);
        // => FROM Order o LEFT JOIN OrderItem oi ON o.id = oi.order_id
        // => Returns all orders, even those with no items

        return entityManager.createQuery(query).getResultList();
    }
}