第8章：实战-基于Jimmer的书店电商平台¶

@Test
void shouldAssociateCategoriesButCurrentlyIgnored() {
    // Given: 创建产品请求包含分类
    CreateProductRequest request = CreateProductRequest.builder()
        .title("Test Product")
        .categories(Arrays.asList(
            CategoryReference.builder().id("cat_001").build()
        ))
        .build();

    // When: 调用创建产品服务
    Product product = productService.createProduct(request);

    // Then: 验证分类关联正确
    assertThat(product.categories()).isNotEmpty();
    assertThat(product.categories().get(0).id()).isEqualTo("cat_001");
}

// 传统JPA实体：冗长的类定义
@Entity
@Table(name = "product")
public class Product {
    @Id
    private String id;
    private String title;
    // 大量getter/setter...
}

// Jimmer实体：优雅的接口定义
@Entity
@Table(name = "product")
public interface Product {
    @Id
    String id();
    String title();
}

/**
 * Product entity representing a product in the e-commerce system
 * Based on Medusa.js product structure
 */
@Entity
@Table(name = "product")
public interface Product {

    @Id
    String id();

    String title();

    @Nullable
    String handle();

    @Nullable
    String subtitle();

    @Nullable
    String description();

    @Column(name = "is_giftcard")
    boolean isGiftcard();

    String status();

    @Nullable
    String thumbnail();

    @Nullable
    Integer weight();

    @Nullable
    Integer length();

    @Nullable
    Integer height();

    @Nullable
    Integer width();

    @Column(name = "origin_country")
    @Nullable
    String originCountry();

    @Column(name = "hs_code")
    @Nullable
    String hsCode();

    @Column(name = "mid_code")
    @Nullable
    String midCode();

    @Nullable
    String material();

    @Column(name = "collection_id")
    @Nullable
    String collectionId();

    @Column(name = "type_id")
    @Nullable
    String typeId();

    boolean discountable();

    @Column(name = "external_id")
    @Nullable
    String externalId();

    @Nullable
    JsonNode metadata();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Column(name = "deleted_at")
    @Nullable
    LocalDateTime deletedAt();

    // 🎯 方案1：使用@Formula计算字段获取产品最小价格
    // 支持4层关联：Product -> ProductVariant -> PriceSet -> Price
    @Formula(sql = """
        (SELECT MIN(p.amount) 
         FROM price p 
         JOIN price_set ps ON p.price_set_id = ps.id 
         JOIN product_variant_price_set pvps ON ps.id = pvps.price_set_id 
         JOIN product_variant pv ON pvps.variant_id = pv.id 
         WHERE pv.product_id = %alias.id 
         AND p.amount IS NOT NULL 
         AND p.deleted_at IS NULL 
         AND ps.deleted_at IS NULL 
         AND pv.deleted_at IS NULL)
    """)
    @Nullable
    BigDecimal minPrice();

    // Relationships
    @OneToMany(mappedBy = "product")
    List<ProductVariant> variants();

    @ManyToMany
    @JoinTable(
        name = "product_tags",
        joinColumnName = "product_id",
        inverseJoinColumnName = "product_tag_id"
    )
    List<ProductTag> tags();

    @ManyToMany
    @JoinTable(
        name = "product_category_product",
        joinColumnName = "product_id",
        inverseJoinColumnName = "product_category_id"
    )
    List<ProductCategory> categories();
}

/**
 * Product variant entity representing different variations of a product
 */
@Entity
@Table(name = "product_variant")
public interface ProductVariant {

    @Id
    String id();

    String title();

    @Nullable
    String sku();

    @Nullable
    String barcode();

    @Nullable
    String ean();

    @Nullable
    String upc();

    @Column(name = "allow_backorder")
    boolean allowBackorder();

    @Column(name = "manage_inventory")
    boolean manageInventory();

    @Column(name = "hs_code")
    @Nullable
    String hsCode();

    @Column(name = "origin_country")
    @Nullable
    String originCountry();

    @Column(name = "mid_code")
    @Nullable
    String midCode();

    @Nullable
    String material();

    @Nullable
    Integer weight();

    @Nullable
    Integer length();

    @Nullable
    Integer height();

    @Nullable
    Integer width();

    @Nullable
    JsonNode metadata();

    @Column(name = "variant_rank")
    @Nullable
    Integer variantRank();

    // 🎯 @IdView的智能优化：快速获取关联ID
    @IdView("product")
    String productId();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Column(name = "deleted_at")
    @Nullable
    LocalDateTime deletedAt();

    // Relationships
    @ManyToOne
    @JoinColumn(name = "product_id")
    Product product();

    @ManyToMany
    @JoinTable(
        name = "product_variant_price_set",
        joinColumns = @JoinColumn(name = "variant_id"),
        inverseJoinColumns = @JoinColumn(name = "price_set_id")
    )
    List<PriceSet> priceSets();
}

// 传统方式：需要加载完整的产品对象
String productId = variant.product().id();

// Jimmer方式：直接获取ID，无需加载完整对象
String productId = variant.productId();

@Entity
@Table(name = "product_category")
public interface ProductCategory {

    @Id
    String id();

    String name();

    String description();

    String handle();

    // 物化路径：层级查询的性能利器
    String mpath();

    @Column(name = "is_active")
    boolean active();

    @Column(name = "is_internal")
    boolean internal();

    int rank();

    @Nullable
    @ManyToOne
    @JoinColumn(name = "parent_category_id")
    ProductCategory parentCategory();

    // 🎯 @IdView的另一个应用：快速获取父分类ID
    @IdView("parentCategory")
    @Nullable
    String parentCategoryId();

    @OneToMany(mappedBy = "parentCategory")
    List<ProductCategory> categoryChildren();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Nullable
    @Column(name = "deleted_at")
    LocalDateTime deletedAt();

    @Nullable
    JsonNode metadata();

    @ManyToMany(mappedBy = "categories")
    List<Product> products();
}

@Entity
@Table(name = "price_set")
public interface PriceSet {

    @Id
    @Column(name = "id")
    String id();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Column(name = "deleted_at")
    @Nullable
    LocalDateTime deletedAt();

    @OneToMany(mappedBy = "priceSet")
    List<Price> prices();
}

@Entity
@Table(name = "price")
public interface Price {

    @Id
    @Column(name = "id")
    String id();

    @Nullable
    @Column(name = "title")
    String title();

    @Column(name = "currency_code")
    String currencyCode();

    // 🎯 关键设计：直接使用BigDecimal，便于财务计算
    @Column(name = "amount")
    BigDecimal amount();

    @Column(name = "rules_count")
    @Nullable
    Integer rulesCount();

    @Column(name = "min_quantity")
    @Nullable
    Integer minQuantity();

    @Column(name = "max_quantity")
    @Nullable
    Integer maxQuantity();

    @Column(name = "price_list_id")
    @Nullable
    String priceListId();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Column(name = "deleted_at")
    @Nullable
    LocalDateTime deletedAt();

    @ManyToOne
    @JoinColumn(name = "price_set_id")
    PriceSet priceSet();
}

/**
 * Product tag entity for categorizing products
 */
@Entity
@Table(name = "product_tag")
public interface ProductTag {

    @Id
    String id();

    String value();

    @Column(name = "created_at")
    LocalDateTime createdAt();

    @Column(name = "updated_at")
    LocalDateTime updatedAt();

    @Column(name = "deleted_at")
    @Nullable
    LocalDateTime deletedAt();

    @Nullable
    @Column(name = "metadata")
    String metadata();
}

ProductTable table = ProductTable.$;

// 复杂查询示例
List<Product> products = sqlClient
    .createQuery(table)
    .where(table.status().eq("published"))
    .where(table.deletedAt().isNull())
    .where(table.minPrice().isNotNull())  // 使用@Formula计算的价格
    .orderBy(table.createdAt().desc())
    .select(table.fetch(
        ProductFetcher.$
            .allScalarFields()
            .minPrice()  // 包含计算属性
            .variants(ProductVariantFetcher.$
                .allScalarFields()
                .productId()  // 使用@IdView优化
            )
    ))
    .execute();

// 列表页面：只加载基础信息
Fetcher<Product> listFetcher = ProductFetcher.$
    .id().title().handle().thumbnail()
    .minPrice()
    .status();

// 详情页面：加载完整信息
Fetcher<Product> detailFetcher = ProductFetcher.$
    .allScalarFields()
    .minPrice()
    .variants(ProductVariantFetcher.$
        .allScalarFields()
        .productId()
        .priceSets(PriceSetFetcher.$
            .allScalarFields()
            .prices(PriceFetcher.$.allScalarFields())
        )
    )
    .categories(ProductCategoryFetcher.$
        .allScalarFields()
        .parentCategoryId()
    )
    .tags(ProductTagFetcher.$.allScalarFields());

@Entity
@Table(name = "product")
public interface Product {
    // ... 属性定义 ...

    // 业务方法：获取主要变体
    default Optional<ProductVariant> getPrimaryVariant() {
        return variants().stream()
            .filter(v -> v.variantRank() != null && v.variantRank() == 0)
            .findFirst();
    }

    // 业务方法：检查是否有库存
    default boolean hasStock() {
        return variants().stream()
            .anyMatch(v -> !v.manageInventory() || v.allowBackorder());
    }

    // 业务方法：获取价格范围
    default String getPriceRange(String currencyCode) {
        List<BigDecimal> prices = variants().stream()
            .flatMap(v -> v.priceSets().stream())
            .flatMap(ps -> ps.prices().stream())
            .filter(p -> currencyCode.equals(p.currencyCode()))
            .map(Price::amount)
            .sorted()
            .collect(Collectors.toList());

        if (prices.isEmpty()) return "价格未设定";
        if (prices.size() == 1) return formatPrice(prices.get(0), currencyCode);

        return formatPrice(prices.get(0), currencyCode) + " - " + 
               formatPrice(prices.get(prices.size() - 1), currencyCode);
    }

    private String formatPrice(BigDecimal amount, String currencyCode) {
        return currencyCode.toUpperCase() + " " + amount.toString();
    }
}

/**
 * Repository interface for Product entity using Jimmer
 * 使用Jimmer推荐的default方法实现复杂查询
 */
public interface ProductRepository extends JRepository<Product, String> {

    // Jimmer推荐的方式：定义表常量
    ProductTable T = ProductTable.$;

    /**
     * US3核心：综合动态查询方法
     * 使用Jimmer推荐的default方法实现复杂查询逻辑
     */
    default Page<Product> findProductsWithComprehensiveQuery(ProductQueryParams queryParams) {

        // 从queryParams中提取pageable
        Pageable pageable = queryParams.getPageable();

        // 构建动态Fetcher
        Fetcher<Product> fetcher = buildDynamicFetcher(queryParams.getRegionId());

        // 使用Jimmer SQL DSL构建查询
        var query = sql()
            .createQuery(T)
            .where(T.status().eq("published")) // 基础过滤条件
            .whereIf(
                queryParams.hasCategoryFilter(),
                T.categories(category -> category.id().eq(queryParams.getCategoryId()))
            );

        // 动态添加排序条件
        if (queryParams.hasCustomSort()) {
            query = applySorting(query, queryParams);
        }

        // 执行分页查询
        org.babyfish.jimmer.Page<Product> jimmerPage = query
            .select(T.fetch(fetcher))
            .fetchPage(pageable.getPageNumber(), pageable.getPageSize());

        // 转换为Spring Data的Page
        return new PageImpl<>(
            jimmerPage.getRows(),
            pageable,
            jimmerPage.getTotalRowCount()
        );
    }
}

/**
 * 构建动态Fetcher的辅助方法
 */
private Fetcher<Product> buildDynamicFetcher(String regionId) {
    // 基础Fetcher - 包含minPrice计算字段，用于排序和显示
    ProductFetcher fetcher = ProductFetcher.$
        .allScalarFields() // 加载所有标量字段（包括@Formula字段minPrice）
        .minPrice() // 显式包含minPrice计算字段，确保在DTO中可用
        .variants(
            ProductVariantFetcher.$
                .allScalarFields()
                .priceSets(  // 总是加载priceSets基础数据
                    PriceSetFetcher.$
                        .allScalarFields()
                        .prices(PriceFetcher.$.allScalarFields())
                )
        )
        .tags(ProductTagFetcher.$.allScalarFields());

    return fetcher;
}

/**
 * 产品服务实现 - 重构版本
 * 核心改进：
 * 1. 简化Service层逻辑，将动态查询交给Repository处理
 * 2. 采用参数对象模式，提高代码可读性和可维护性
 * 3. Service只负责参数验证、调用Repository和业务逻辑处理
 */
@Service
public class ProductServiceImpl implements ProductService {

    private static final Logger log = LoggerFactory.getLogger(ProductServiceImpl.class);

    private final ProductRepository productRepository;
    private final PriceCalculationService priceCalculationService;

    @Override
    public ProductResult getProducts(int limit, int offset, String regionId, String fields,
                                     String categoryId, String orderBy, String sort) {
        // 参数验证
        validateInputParameters(limit, offset);
        validateSortParameters(orderBy, sort);

        if (limit == 0) {
            return new ProductResult(List.of(), 0L);
        }

        // 创建查询参数对象（参数对象模式）
        ProductQueryParams queryParams = ProductQueryParams.of(pageable, regionId, categoryId, orderBy, sort);

        log.info("获取产品列表: limit={}, offset={}, queryParams={}", limit, offset, queryParams);

        try {
            // US3核心重构：使用参数对象调用Repository方法
            Page<Product> productPage = productRepository.findProductsWithComprehensiveQuery(queryParams);

            log.info("Repository返回 {} 个产品，总记录数: {}", 
                    productPage.getContent().size(), productPage.getTotalElements());

            // 转换为ProductListView并动态计算价格（Service层业务逻辑）
            List<ProductListView> result = productPage.getContent()
                    .stream()
                    .map(product -> {
                        try {
                            return this.convertToProductListViewWithPrice(product, regionId);
                        } catch (Exception e) {
                            log.error("转换产品 {} 为ProductListView时出错: {}", product.id(), e.getMessage(), e);
                            throw e;
                        }
                    })
                    .collect(Collectors.toList());

            log.info("成功返回 {} 个产品，包含动态计算的价格，总记录数: {}", 
                    result.size(), productPage.getTotalElements());

            // 返回包含总记录数的结果对象
            return new ProductResult(result, productPage.getTotalElements());

        } catch (Exception e) {
            log.error("获取产品列表失败: {}", e.getMessage(), e);
            throw new RuntimeException("获取产品列表失败", e);
        }
    }
}

@RestController
@RequestMapping("/store/products")
public class ProductController {

    private final ProductService productService;
    private static final Logger logger = LoggerFactory.getLogger(ProductController.class);

    @GetMapping
    public ResponseEntity<Map<String, Object>> getProducts(
            @RequestParam(defaultValue = "12") int limit,
            @RequestParam(defaultValue = "0") int offset,
            @RequestParam(name = "region_id", required = false) String regionId,
            @RequestParam(name = "fields", required = false) String fields,
            @RequestParam(name = "category_id", required = false) String categoryId,
            @RequestParam(name = "orderBy", required = false) String orderBy,
            @RequestParam(name = "sort", required = false) String sort) {

        // US3: Parameter validation for category and sorting
        validateCategoryId(categoryId);
        validateSortParameters(orderBy, sort);

        // 获取产品结果，包含总记录数
        ProductResult productResult = productService.getProducts(limit, offset, regionId, fields, 
                                                categoryId, orderBy, sort);

        // Return Medusa.js compatible response format with correct total count
        Map<String, Object> response = Map.of(
            "products", productResult.getProducts(),
            "count", productResult.getTotalCount(),
            "offset", offset,
            "limit", limit
        );

        logger.info("Products retrieved: {} products, total count: {}", 
                    productResult.getProducts().size(), productResult.getTotalCount());

        return ResponseEntity.ok(response);
    }

    /**
     * Validate category_id parameter format
     * Category IDs should follow the pattern: pcat_[alphanumeric]
     */
    private void validateCategoryId(String categoryId) {
        if (categoryId != null && !categoryId.trim().isEmpty()) {
            if (!categoryId.matches("^pcat_[A-Za-z0-9]+$")) {
                throw new IllegalArgumentException("Invalid category ID format");
            }
        }
    }

    /**
     * Validate orderBy and sort parameters
     */
    private void validateSortParameters(String orderBy, String sort) {
        // Validate orderBy parameter
        if (orderBy != null && !Arrays.asList("created_at", "price").contains(orderBy.toLowerCase())) {
            throw new IllegalArgumentException("Invalid orderBy parameter. Supported: created_at, price");
        }

        // Validate sort parameter  
        if (sort != null && !Arrays.asList("asc", "desc").contains(sort.toLowerCase())) {
            throw new IllegalArgumentException("Invalid sort parameter. Supported: asc, desc");
        }
    }
}

@Entity
@Table(name = "product_category")
public interface ProductCategory {

    @Id
    String id();

    String name();
    String description();
    String handle();

    // 物化路径：层级查询的性能利器
    String mpath();

    @Column(name = "is_active")
    boolean active();

    @Column(name = "is_internal")
    boolean internal();

    int rank();

    // 自引用关系：父分类
    @Nullable
    @ManyToOne
    @JoinColumn(name = "parent_category_id")
    ProductCategory parentCategory();

    // @IdView的另一个应用：快速获取父分类ID
    @IdView("parentCategory")
    @Nullable
    String parentCategoryId();

    // 自引用关系：子分类列表
    @OneToMany(mappedBy = "parentCategory")
    List<ProductCategory> categoryChildren();

    // 与产品的多对多关系
    @ManyToMany(mappedBy = "categories")
    List<Product> products();
}

@Service
@Transactional(readOnly = true) 
public class ProductCategoryServiceImpl implements ProductCategoryService {

    private final JSqlClient sqlClient;

    public ProductCategoryServiceImpl(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }

    @Override
    public List<ProductCategoryListView> getProductCategories(
            int limit, int offset, String fields, String order) {

        ProductCategoryTable table = ProductCategoryTable.$;

        // 构建动态Fetcher
        Fetcher<ProductCategory> fetcher = buildDynamicFetcher(fields);

        // 创建查询 - 只返回活跃的分类
        ConfigurableTypedRootQuery<ProductCategory> query = sqlClient
            .createQuery(table)
            .where(table.deletedAt().isNull())
            .where(table.active().eq(true));

        // 应用排序
        if (order != null) {
            switch (order) {
                case "rank":
                    query = query.orderBy(table.rank().asc());
                    break;
                case "name":
                    query = query.orderBy(table.name().asc());
                    break;
                case "created_at":
                    query = query.orderBy(table.createdAt().asc());
                    break;
                default:
                    query = query.orderBy(table.rank().asc());
            }
        } else {
            query = query.orderBy(table.rank().asc());
        }

        // 执行分页查询
        List<ProductCategory> categories = query
            .limit(limit, offset)
            .select(table.fetch(fetcher))
            .execute();

        return categories.stream()
            .map(this::convertToListView)
            .collect(Collectors.toList());
    }
}

// 通过mpath字段进行高效的层级查询
List<ProductCategory> descendants = sqlClient
    .createQuery(ProductCategoryTable.$)
    .where(ProductCategoryTable.$.mpath().like(parentCategory.mpath() + ".%"))
    .select(ProductCategoryTable.$.fetch(ProductCategoryFetcher.$.allScalarFields()))
    .execute();

/**
 * 应用排序逻辑的辅助方法
 */
private org.babyfish.jimmer.sql.ast.query.MutableRootQuery<ProductTable> applySorting(
        org.babyfish.jimmer.sql.ast.query.MutableRootQuery<ProductTable> query, 
        ProductQueryParams queryParams) {

    boolean isAsc = queryParams.isAscendingSort();
    String orderBy = queryParams.getOrderBy().toLowerCase();

    switch (orderBy) {
        case "title":
            return isAsc ? query.orderBy(T.title()) : query.orderBy(T.title().desc());
        case "created_at":
            return isAsc ? query.orderBy(T.createdAt()) : query.orderBy(T.createdAt().desc());
        case "updated_at":
            return isAsc ? query.orderBy(T.updatedAt()) : query.orderBy(T.updatedAt().desc());
        case "price":
            // 方案1：使用@Formula计算字段进行价格排序，自动处理空值
            return isAsc ? 
                query.orderBy(T.minPrice().asc().nullsLast()) : 
                query.orderBy(T.minPrice().desc().nullsLast());
        default:
            return isAsc ? query.orderBy(T.createdAt()) : query.orderBy(T.createdAt().desc());
    }
}

@Service
public class PriceCalculationServiceImpl implements PriceCalculationService {

    private static final Logger log = LoggerFactory.getLogger(PriceCalculationServiceImpl.class);

    private final JSqlClient sqlClient;

    public PriceCalculationServiceImpl(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }

    @Override
    public Map<String, CalculatedPriceDto> calculatePricesForVariants(
            List<String> variantIds, String regionId) {

        if (variantIds == null || variantIds.isEmpty()) {
            return Map.of();
        }

        // 获取指定区域的货币信息（简化实现，实际应该从region表查询）
        String currencyCode = getCurrencyForRegion(regionId);

        log.debug("计算变体价格: variants={}, region={}, currency={}", variantIds, regionId, currencyCode);

        // 批量查询变体及其价格信息
        List<ProductVariant> variants = sqlClient
            .createQuery(ProductVariantTable.$)
            .where(ProductVariantTable.$.id().in(variantIds))
            .where(ProductVariantTable.$.deletedAt().isNull())
            .select(
                ProductVariantTable.$.fetch(
                    ProductVariantFetcher.$
                        .allScalarFields()
                        .priceSets(
                            PriceSetFetcher.$
                                .allScalarFields()
                                .prices(
                                    PriceFetcher.$
                                        .allScalarFields()
                                )
                        )
                )
            )
            .execute();

        log.debug("查询到 {} 个变体数据", variants.size());

        // 为每个变体计算价格
        Map<String, CalculatedPriceDto> priceMap = new HashMap<>();
        for (ProductVariant variant : variants) {
            try {
                CalculatedPriceDto calculatedPrice = calculateVariantPrice(variant, currencyCode);
                if (calculatedPrice != null) {
                    priceMap.put(variant.id(), calculatedPrice);
                }
            } catch (Exception e) {
                log.error("计算变体 {} 价格时出错: {}", variant.id(), e.getMessage(), e);
            }
        }

        log.info("成功计算 {} 个变体的价格", priceMap.size());
        return priceMap;
    }
}

/**
 * 为特定变体计算价格
 */
private CalculatedPriceDto calculateVariantPrice(ProductVariant variant, String currencyCode) {

    // 从变体的价格集中查找匹配的价格
    List<Price> applicablePrices = variant.priceSets().stream()
        .flatMap(priceSet -> priceSet.prices().stream())
        .filter(price -> price.deletedAt() == null)
        .filter(price -> currencyCode.equals(price.currencyCode()))
        .collect(Collectors.toList());

    if (applicablePrices.isEmpty()) {
        log.debug("变体 {} 没有找到 {} 货币的价格", variant.id(), currencyCode);
        return null;
    }

    // 选择最低价格（可以根据业务规则调整）
    Price selectedPrice = applicablePrices.stream()
        .min(Comparator.comparing(Price::amount))
        .orElse(null);

    if (selectedPrice == null) {
        return null;
    }

    // 构建计算价格DTO
    BigDecimal finalAmount = calculateFinalAmount(selectedPrice.amount(), currencyCode);

    return CalculatedPriceDto.builder()
        .currencyCode(currencyCode)
        .originalAmount(selectedPrice.amount())
        .calculatedAmount(finalAmount)
        .priceId(selectedPrice.id())
        .build();
}

/**
 * 计算最终金额（可以包含折扣、税费等逻辑）
 */
private BigDecimal calculateFinalAmount(BigDecimal baseAmount, String currencyCode) {
    // 这里可以添加复杂的价格计算逻辑：
    // 1. 应用折扣规则
    // 2. 计算税费
    // 3. 货币转换
    // 4. 会员优惠

    // 当前实现：直接返回基础价格
    return baseAmount;
}

// 1. 参数接收和验证
@GetMapping
public ResponseEntity<Map<String, Object>> getProducts(
        @RequestParam(defaultValue = "12") int limit,
        @RequestParam(defaultValue = "0") int offset,
        @RequestParam(name = "category_id", required = false) String categoryId,
        @RequestParam(name = "orderBy", required = false) String orderBy,
        @RequestParam(name = "sort", required = false) String sort,
        @RequestParam(name = "region_id", required = false) String regionId) {

    // 2. 参数验证
    validateCategoryId(categoryId);
    validateSortParameters(orderBy, sort);

    // 3. 调用Service层
    ProductResult result = productService.getProducts(limit, offset, regionId, null, 
                                                     categoryId, orderBy, sort);
    // 4. 格式化响应
    return ResponseEntity.ok(formatResponse(result, offset, limit));
}

// 1. 创建查询参数对象
ProductQueryParams queryParams = ProductQueryParams.of(pageable, regionId, categoryId, orderBy, sort);

// 2. 调用Repository进行数据查询
Page<Product> productPage = productRepository.findProductsWithComprehensiveQuery(queryParams);

// 3. 转换为业务对象并计算价格
List<ProductListView> result = productPage.getContent()
    .stream()
    .map(product -> convertToProductListViewWithPrice(product, regionId))
    .collect(Collectors.toList());

// 4. 返回结果
return new ProductResult(result, productPage.getTotalElements());

// 1. 构建动态Fetcher
Fetcher<Product> fetcher = buildDynamicFetcher(queryParams.getRegionId());

// 2. 创建类型安全的查询
var query = sql()
    .createQuery(T)
    .where(T.status().eq("published"))
    .whereIf(
        queryParams.hasCategoryFilter(),
        T.categories(category -> category.id().eq(queryParams.getCategoryId()))
    );

// 3. 应用排序（包括复杂的价格排序）
if (queryParams.hasCustomSort()) {
    query = applySorting(query, queryParams);
}

// 4. 执行分页查询
return query.select(T.fetch(fetcher)).fetchPage(pageNumber, pageSize);