Skip to main content

sz_orm_core/
query.rs

1//! 查询构造器
2//!
3//! 提供类似 ThinkORM 的链式查询构造 API
4//!
5//! # P0-1 软删除集成（v1.3.0+）
6//!
7//! 当 `M: Model` 实现了 `soft_delete_field()` 返回 `Some(field)` 时，
8//! `QueryBuilder` 会在以下场景自动追加 `WHERE {field} IS NULL`：
9//! - `build_select` / `build_select_with_params`
10//! - `build_count` / `build_exists` / `build_max` / `build_min` / `build_sum` / `build_avg`
11//! - `build_update` / `build_update_with_params`（防止更新已删除记录）
12//! - `build_delete` / `build_delete_with_params`（自动转为 `UPDATE SET {field} = NOW()`）
13//!
14//! 使用 `without_soft_delete()` 可临时禁用软删除过滤（用于查询已删除记录）。
15//!
16//! # P0-2 参数化 WHERE 条件（v1.3.0+）
17//!
18//! 新增类型安全的参数化 WHERE API：
19//! - `where_eq(field, value)` / `where_ne` / `where_gt` / `where_ge` / `where_lt` / `where_le`
20//! - `where_like(field, pattern)`
21//!
22//! 这些方法使用 `?` 占位符 + `Value` 绑定，杜绝 SQL 注入。
23//! 原有 `where_cond(condition: impl Into<String>)` 因字符串拼接存在注入风险，
24//! 保留以兼容复杂表达式（如 `age > 18 AND status = 'active'`），但文档标记为不推荐。
25
26use crate::db_type::DbType;
27use crate::dialect::Dialect;
28use crate::model::Model;
29use crate::typed::TypedColumn;
30use crate::value::Value;
31use std::fmt;
32
33/// 用于构造 SQL 查询的查询构造器
34pub struct QueryBuilder<M: Model> {
35    table: Option<String>,
36    select_columns: Vec<String>,
37    where_conditions: Vec<WhereCondition>,
38    order_by: Vec<OrderClause>,
39    group_by: Vec<String>,
40    having_conditions: Vec<WhereCondition>,
41    limit_value: Option<usize>,
42    offset_value: Option<usize>,
43    joins: Vec<JoinClause>,
44    dialect: Box<dyn Dialect>,
45    /// P0-1：是否禁用软删除过滤（true 表示禁用，查询包含已删除记录）
46    soft_delete_disabled: bool,
47    /// P0-3：当前租户 ID（运行时注入）。设置后自动追加 `WHERE {tenant_field} = ?`
48    tenant_id_value: Option<i64>,
49    /// P0-3：是否禁用租户过滤（true 表示禁用，跨租户查询）
50    tenant_disabled: bool,
51    /// P2-5：Keyset 分页游标条件（field, value, direction）
52    ///
53    /// 设置后，`build_select`/`build_select_with_params` 会追加 `WHERE {field} > ?` 或
54    /// `WHERE {field} < ?` 条件（取决于排序方向），实现基于游标的高效分页。
55    /// 与 OFFSET 分页相比，Keyset 分页在大数据集下性能稳定，不受数据插入/删除影响。
56    keyset_cursor: Option<KeysetCursor>,
57    #[allow(dead_code)]
58    model: std::marker::PhantomData<M>,
59}
60
61/// P2-5：Keyset 分页游标
62///
63/// 表示一个基于排序字段值的分页游标。结合 `ORDER BY {field} {direction}` 和
64/// `WHERE {field} {op} ?` 实现游标分页。
65///
66/// - `After(value)` + `Asc`：查询 `field > value` 的记录（下一页）
67/// - `Before(value)` + `Desc`：查询 `field < value` 的记录（上一页）
68#[derive(Debug, Clone)]
69struct KeysetCursor {
70    /// 排序字段名
71    field: String,
72    /// 游标值（上一页/下一页最后一行的该字段值）
73    value: Value,
74    /// 游标方向：After = 下一页（field > value），Before = 上一页（field < value）
75    direction: KeysetDirection,
76}
77
78/// P2-5：Keyset 游标方向
79#[derive(Debug, Clone, Copy, PartialEq, Eq)]
80enum KeysetDirection {
81    /// 下一页：`WHERE field > cursor_value`（配合 ASC 排序）
82    After,
83    /// 上一页：`WHERE field < cursor_value`（配合 DESC 排序）
84    Before,
85}
86
87#[derive(Debug, Clone)]
88#[allow(dead_code)]
89enum WhereCondition {
90    /// 原始字符串条件（AND）— **存在注入风险，不推荐使用**
91    ///
92    /// 保留以兼容复杂表达式如 `age > 18 AND status = 'active'`。
93    /// 调用方必须确保字符串来自可信来源。
94    And(String),
95    /// 原始字符串条件（OR）— **存在注入风险，不推荐使用**
96    Or(String),
97    /// P0-2：参数化等值条件 `field = ?`
98    Eq(String, Value),
99    /// P0-2：参数化不等条件 `field != ?`
100    Ne(String, Value),
101    /// P0-2：参数化大于条件 `field > ?`
102    Gt(String, Value),
103    /// P0-2：参数化大于等于条件 `field >= ?`
104    Ge(String, Value),
105    /// P0-2：参数化小于条件 `field < ?`
106    Lt(String, Value),
107    /// P0-2：参数化小于等于条件 `field <= ?`
108    Le(String, Value),
109    /// P0-2：参数化 LIKE 条件 `field LIKE ?`
110    Like(String, Value),
111    /// P0-2：参数化 OR 等值条件 `OR field = ?`
112    OrEq(String, Value),
113    /// P0-2：参数化 OR 不等条件 `OR field != ?`
114    OrNe(String, Value),
115    /// P0-2：参数化 OR 大于条件 `OR field > ?`
116    OrGt(String, Value),
117    /// P0-2：参数化 OR 大于等于条件 `OR field >= ?`
118    OrGe(String, Value),
119    /// P0-2：参数化 OR 小于条件 `OR field < ?`
120    OrLt(String, Value),
121    /// P0-2：参数化 OR 小于等于条件 `OR field <= ?`
122    OrLe(String, Value),
123    /// P0-2：参数化 OR LIKE 条件 `OR field LIKE ?`
124    OrLike(String, Value),
125    In(String, Vec<Value>),
126    NotIn(String, Vec<Value>),
127    Between(String, Value, Value),
128    NotBetween(String, Value, Value),
129    Null(String),
130    NotNull(String),
131    Exists(String),
132    NotExists(String),
133}
134
135#[derive(Debug, Clone)]
136struct OrderClause {
137    field: String,
138    direction: OrderDirection,
139}
140
141#[derive(Debug, Clone)]
142enum OrderDirection {
143    Asc,
144    Desc,
145}
146
147#[derive(Debug, Clone)]
148#[allow(dead_code)]
149enum JoinClause {
150    Inner(String, String, String),
151    Left(String, String, String),
152    Right(String, String, String),
153    Cross(String, String),
154}
155
156impl<M: Model> QueryBuilder<M> {
157    pub fn new(dialect: Box<dyn Dialect>) -> Self {
158        Self {
159            table: None,
160            select_columns: vec!["*".to_string()],
161            where_conditions: Vec::new(),
162            order_by: Vec::new(),
163            group_by: Vec::new(),
164            having_conditions: Vec::new(),
165            limit_value: None,
166            offset_value: None,
167            joins: Vec::new(),
168            dialect,
169            soft_delete_disabled: false,
170            tenant_id_value: None,
171            tenant_disabled: false,
172            keyset_cursor: None,
173            model: std::marker::PhantomData,
174        }
175    }
176
177    pub fn table(mut self, table: impl Into<String>) -> Self {
178        self.table = Some(table.into());
179        self
180    }
181
182    /// P0-1：临时禁用软删除过滤，用于查询已删除的记录。
183    ///
184    /// 等价于 SeaORM 的 `Entity::find().filter(Column::DeletedAt.is_not_null())`
185    /// 或 Laravel Eloquent 的 `Model::withTrashed()`。
186    ///
187    /// # 示例
188    ///
189    /// ```ignore
190    /// use sz_orm_core::query::QueryBuilder;
191    /// use sz_orm_core::dialect::MySqlDialect;
192    ///
193    /// // 查询包含已软删除的用户
194    /// let sql = QueryBuilder::<User>::new(Box::new(MySqlDialect))
195    ///     .table("users")
196    ///     .without_soft_delete()
197    ///     .build_select();
198    /// // 不会自动追加 WHERE deleted_at IS NULL
199    /// ```
200    pub fn without_soft_delete(mut self) -> Self {
201        self.soft_delete_disabled = true;
202        self
203    }
204
205    /// P0-1：返回软删除过滤是否被禁用
206    pub fn is_soft_delete_disabled(&self) -> bool {
207        self.soft_delete_disabled
208    }
209
210    /// P0-4：克隆当前 `QueryBuilder` 用于 COUNT 查询。
211    ///
212    /// 保留 `table` / `joins` / `where_conditions` / `group_by` / `having_conditions`
213    /// 以及软删除 / 租户过滤设置，清空 `select_columns` / `order_by` / `limit` / `offset` / `keyset_cursor`。
214    ///
215    /// 用于 `PaginatorTrait` 的 COUNT 子查询构造。
216    pub fn clone_for_count(&self) -> Self {
217        Self {
218            table: self.table.clone(),
219            select_columns: vec!["*".to_string()],
220            where_conditions: self.where_conditions.clone(),
221            order_by: Vec::new(),
222            group_by: self.group_by.clone(),
223            having_conditions: self.having_conditions.clone(),
224            limit_value: None,
225            offset_value: None,
226            joins: self.joins.clone(),
227            dialect: self.dialect.clone_box(),
228            soft_delete_disabled: self.soft_delete_disabled,
229            tenant_id_value: self.tenant_id_value,
230            tenant_disabled: self.tenant_disabled,
231            keyset_cursor: None,
232            model: std::marker::PhantomData,
233        }
234    }
235
236    /// P0-1：返回当前 Model 的软删除字段名（若启用）
237    ///
238    /// 内部使用，用于 `build_*` 方法决定是否追加 `WHERE {field} IS NULL`。
239    fn soft_delete_field(&self) -> Option<&'static str> {
240        if self.soft_delete_disabled {
241            return None;
242        }
243        M::soft_delete_field()
244    }
245
246    /// P0-1：构造软删除过滤条件 SQL 片段（不含 `AND` 前缀）
247    ///
248    /// 返回 `None` 表示无需过滤；返回 `Some(sql)` 表示追加 `AND {sql}` 到 WHERE 子句。
249    fn build_soft_delete_condition(&self) -> Option<String> {
250        self.soft_delete_field()
251            .map(|field| format!("{} IS NULL", self.dialect.quote(field)))
252    }
253
254    // ===================== P0-3 多租户过滤 =====================
255
256    /// P0-3：设置当前租户 ID，启用多租户自动过滤。
257    ///
258    /// 当 `M::tenant_field()` 返回 `Some(field)` 时，`QueryBuilder` 会在以下场景
259    /// 自动追加 `WHERE {field} = ?`（参数化，值通过 `params` 绑定）：
260    /// - `build_select` / `build_select_with_params`
261    /// - `build_count` / `build_exists` / `build_max` / `build_min` / `build_sum` / `build_avg`
262    /// - `build_update` / `build_update_with_params`（防止跨租户更新）
263    /// - `build_delete` / `build_delete_with_params`（防止跨租户删除）
264    ///
265    /// 使用 `without_tenant()` 可临时禁用租户过滤（用于跨租户管理查询）。
266    ///
267    /// # 示例
268    ///
269    /// ```ignore
270    /// use sz_orm_core::query::QueryBuilder;
271    /// use sz_orm_core::dialect::MySqlDialect;
272    ///
273    /// let (sql, params) = QueryBuilder::<Order>::new(Box::new(MySqlDialect))
274    ///     .table("orders")
275    ///     .with_tenant_id(42)
276    ///     .build_select_with_params();
277    /// // sql => "SELECT * FROM `orders` WHERE `tenant_id` = ?"
278    /// // params => [Value::I64(42)]
279    /// ```
280    pub fn with_tenant_id(mut self, tenant_id: i64) -> Self {
281        self.tenant_id_value = Some(tenant_id);
282        self
283    }
284
285    /// P0-3：临时禁用租户过滤，用于跨租户管理查询。
286    ///
287    /// 等价于 Laravel Eloquent 的全局作用域禁用。
288    pub fn without_tenant(mut self) -> Self {
289        self.tenant_disabled = true;
290        self
291    }
292
293    /// P0-3：返回租户过滤是否被禁用
294    pub fn is_tenant_disabled(&self) -> bool {
295        self.tenant_disabled
296    }
297
298    /// P0-3：返回当前 Model 的租户字段名（若启用且未禁用）
299    ///
300    /// 内部使用，用于 `build_*` 方法决定是否追加租户条件。
301    fn tenant_field(&self) -> Option<&'static str> {
302        if self.tenant_disabled {
303            return None;
304        }
305        M::tenant_field()
306    }
307
308    /// P0-3：返回当前租户 ID（若设置了且未禁用）
309    fn tenant_id_value(&self) -> Option<i64> {
310        if self.tenant_disabled {
311            return None;
312        }
313        self.tenant_id_value
314    }
315
316    /// P0-3：构造租户过滤条件（SQL 片段 + 参数值）
317    ///
318    /// 返回 `None` 表示无需过滤；返回 `Some((sql, value))` 表示追加 `AND {sql}` 到 WHERE 子句，
319    /// 并将 `value` 加入参数列表。
320    fn build_tenant_condition(&self) -> Option<(String, Value)> {
321        let field = self.tenant_field()?;
322        let tid = self.tenant_id_value()?;
323        Some((
324            format!("{} = ?", self.dialect.quote(field)),
325            Value::I64(tid),
326        ))
327    }
328
329    /// 设置 SELECT 列。
330    ///
331    /// **M-3 安全警告**：本方法直接拼接 `columns` 到 SQL，**不**进行标识符校验或 quote。
332    /// 调用方必须确保 `columns` 来自可信来源（硬编码或经 `sql_safety::validate_identifier`
333    /// 校验）。若列名可能来自不可信输入，请使用 [`QueryBuilder::select_quoted`]。
334    ///
335    /// 本方法保留原行为以兼容复杂表达式（如 `COUNT(*)`、`users.id AS uid`）。
336    pub fn select(mut self, columns: Vec<&str>) -> Self {
337        self.select_columns = columns.into_iter().map(|s| s.to_string()).collect();
338        self
339    }
340
341    /// M-3 修复：安全的 SELECT 列设置，自动校验每个列名并 quote。
342    ///
343    /// 每个 `column` 必须通过 `sql_safety::validate_identifier` 校验
344    /// （仅允许 ASCII 字母数字 + 下划线，不以数字开头，长度 1-63）。
345    /// 校验失败时返回 `DbError::InvalidInput`。
346    ///
347    /// 对于复杂表达式（如 `COUNT(*)`、`users.id AS uid`），请使用 [`QueryBuilder::select`]
348    /// 并自行确保安全。
349    pub fn select_quoted(mut self, columns: Vec<&str>) -> Result<Self, crate::DbError> {
350        let mut quoted = Vec::with_capacity(columns.len());
351        for col in columns {
352            crate::sql_safety::validate_identifier(col, "select column")?;
353            quoted.push(self.dialect.quote(col));
354        }
355        self.select_columns = quoted;
356        Ok(self)
357    }
358
359    /// 添加原始字符串 WHERE 条件（AND 关系）。
360    ///
361    /// **⚠️ P0-2 安全警告（v1.3.0+）**：本方法直接拼接 `condition` 到 SQL，
362    /// **存在 SQL 注入风险**。仅在以下场景使用：
363    /// - 条件来自硬编码字符串（如 `where_cond("age > 18")`）
364    /// - 条件含复杂表达式（如 `where_cond("status = 'active' AND role = 'admin'")`）
365    ///
366    /// **禁止**将用户输入拼接到 `condition` 中。若值来自不可信来源，
367    /// 必须使用参数化方法：[`where_eq`](Self::where_eq) / [`where_ne`](Self::where_ne) /
368    /// [`where_gt`](Self::where_gt) / [`where_lt`](Self::where_lt) / [`where_like`](Self::where_like)。
369    ///
370    /// # 推荐迁移
371    ///
372    /// ```ignore
373    /// // ❌ 危险：字符串拼接
374    /// builder.where_cond(format!("name = '{}'", user_input));
375    ///
376    /// // ✅ 安全：参数化绑定
377    /// builder.where_eq("name", Value::String(user_input.to_string()));
378    /// ```
379    #[deprecated(
380        since = "1.3.0",
381        note = "P0-2: 字符串拼接存在 SQL 注入风险，请使用 where_eq/where_ne/where_gt/where_lt/where_like 等参数化方法"
382    )]
383    pub fn where_cond(mut self, condition: impl Into<String>) -> Self {
384        self.where_conditions
385            .push(WhereCondition::And(condition.into()));
386        self
387    }
388
389    /// 添加原始字符串 WHERE 条件（OR 关系）。
390    ///
391    /// **⚠️ P0-2 安全警告**：同 [`where_cond`](Self::where_cond)，存在注入风险。
392    #[deprecated(
393        since = "1.3.0",
394        note = "P0-2: 字符串拼接存在 SQL 注入风险，请使用参数化方法"
395    )]
396    pub fn or_where(mut self, condition: impl Into<String>) -> Self {
397        self.where_conditions
398            .push(WhereCondition::Or(condition.into()));
399        self
400    }
401
402    /// P0-2：参数化等值条件 `field = ?`（AND 关系）。
403    ///
404    /// 值通过 `?` 占位符绑定，杜绝 SQL 注入。
405    ///
406    /// # 示例
407    ///
408    /// ```ignore
409    /// use sz_orm_core::Value;
410    ///
411    /// builder
412    ///     .where_eq("status", Value::String("active".into()))
413    ///     .where_eq("tenant_id", Value::I64(42));
414    /// ```
415    pub fn where_eq(mut self, field: impl Into<String>, value: Value) -> Self {
416        self.where_conditions
417            .push(WhereCondition::Eq(field.into(), value));
418        self
419    }
420
421    /// P0-2：参数化不等条件 `field != ?`（AND 关系）。
422    pub fn where_ne(mut self, field: impl Into<String>, value: Value) -> Self {
423        self.where_conditions
424            .push(WhereCondition::Ne(field.into(), value));
425        self
426    }
427
428    /// P0-2：参数化大于条件 `field > ?`（AND 关系）。
429    pub fn where_gt(mut self, field: impl Into<String>, value: Value) -> Self {
430        self.where_conditions
431            .push(WhereCondition::Gt(field.into(), value));
432        self
433    }
434
435    /// P0-2：参数化大于等于条件 `field >= ?`（AND 关系）。
436    pub fn where_ge(mut self, field: impl Into<String>, value: Value) -> Self {
437        self.where_conditions
438            .push(WhereCondition::Ge(field.into(), value));
439        self
440    }
441
442    /// P0-2：参数化小于条件 `field < ?`（AND 关系）。
443    pub fn where_lt(mut self, field: impl Into<String>, value: Value) -> Self {
444        self.where_conditions
445            .push(WhereCondition::Lt(field.into(), value));
446        self
447    }
448
449    /// P0-2：参数化小于等于条件 `field <= ?`（AND 关系）。
450    pub fn where_le(mut self, field: impl Into<String>, value: Value) -> Self {
451        self.where_conditions
452            .push(WhereCondition::Le(field.into(), value));
453        self
454    }
455
456    /// P0-2：参数化 LIKE 条件 `field LIKE ?`（AND 关系）。
457    ///
458    /// 调用方负责在 `pattern` 中包含 `%` 通配符。
459    ///
460    /// # 示例
461    ///
462    /// ```ignore
463    /// use sz_orm_core::Value;
464    ///
465    /// builder.where_like("name", Value::String("%alice%".into()));
466    /// ```
467    pub fn where_like(mut self, field: impl Into<String>, pattern: Value) -> Self {
468        self.where_conditions
469            .push(WhereCondition::Like(field.into(), pattern));
470        self
471    }
472
473    /// P0-2：参数化 OR 等值条件 `OR field = ?`。
474    ///
475    /// 值通过 `?` 占位符绑定，杜绝 SQL 注入。OR 条件会与相邻的 OR 条件组合成 `(cond1 OR cond2)` 形式。
476    pub fn or_where_eq(mut self, field: impl Into<String>, value: Value) -> Self {
477        self.where_conditions
478            .push(WhereCondition::OrEq(field.into(), value));
479        self
480    }
481
482    /// P0-2：参数化 OR 不等条件 `OR field != ?`。
483    pub fn or_where_ne(mut self, field: impl Into<String>, value: Value) -> Self {
484        self.where_conditions
485            .push(WhereCondition::OrNe(field.into(), value));
486        self
487    }
488
489    /// P0-2：参数化 OR 大于条件 `OR field > ?`。
490    pub fn or_where_gt(mut self, field: impl Into<String>, value: Value) -> Self {
491        self.where_conditions
492            .push(WhereCondition::OrGt(field.into(), value));
493        self
494    }
495
496    /// P0-2：参数化 OR 大于等于条件 `OR field >= ?`。
497    pub fn or_where_ge(mut self, field: impl Into<String>, value: Value) -> Self {
498        self.where_conditions
499            .push(WhereCondition::OrGe(field.into(), value));
500        self
501    }
502
503    /// P0-2：参数化 OR 小于条件 `OR field < ?`。
504    pub fn or_where_lt(mut self, field: impl Into<String>, value: Value) -> Self {
505        self.where_conditions
506            .push(WhereCondition::OrLt(field.into(), value));
507        self
508    }
509
510    /// P0-2：参数化 OR 小于等于条件 `OR field <= ?`。
511    pub fn or_where_le(mut self, field: impl Into<String>, value: Value) -> Self {
512        self.where_conditions
513            .push(WhereCondition::OrLe(field.into(), value));
514        self
515    }
516
517    /// P0-2：参数化 OR LIKE 条件 `OR field LIKE ?`。
518    pub fn or_where_like(mut self, field: impl Into<String>, pattern: Value) -> Self {
519        self.where_conditions
520            .push(WhereCondition::OrLike(field.into(), pattern));
521        self
522    }
523
524    pub fn where_in(mut self, field: impl Into<String>, values: Vec<Value>) -> Self {
525        self.where_conditions
526            .push(WhereCondition::In(field.into(), values));
527        self
528    }
529
530    pub fn where_not_in(mut self, field: impl Into<String>, values: Vec<Value>) -> Self {
531        self.where_conditions
532            .push(WhereCondition::NotIn(field.into(), values));
533        self
534    }
535
536    pub fn where_between(mut self, field: impl Into<String>, start: Value, end: Value) -> Self {
537        self.where_conditions
538            .push(WhereCondition::Between(field.into(), start, end));
539        self
540    }
541
542    pub fn where_not_between(mut self, field: impl Into<String>, start: Value, end: Value) -> Self {
543        self.where_conditions
544            .push(WhereCondition::NotBetween(field.into(), start, end));
545        self
546    }
547
548    pub fn where_null(mut self, field: impl Into<String>) -> Self {
549        self.where_conditions
550            .push(WhereCondition::Null(field.into()));
551        self
552    }
553
554    pub fn where_not_null(mut self, field: impl Into<String>) -> Self {
555        self.where_conditions
556            .push(WhereCondition::NotNull(field.into()));
557        self
558    }
559
560    pub fn order_by(mut self, field: impl Into<String>) -> Self {
561        self.order_by.push(OrderClause {
562            field: field.into(),
563            direction: OrderDirection::Asc,
564        });
565        self
566    }
567
568    pub fn order_desc(mut self, field: impl Into<String>) -> Self {
569        self.order_by.push(OrderClause {
570            field: field.into(),
571            direction: OrderDirection::Desc,
572        });
573        self
574    }
575
576    pub fn group_by(mut self, field: impl Into<String>) -> Self {
577        self.group_by.push(field.into());
578        self
579    }
580
581    // -----------------------------------------------------------------------
582    // 类型安全列引用（TypedColumn）变体
583    // 以下方法接受实现 TypedColumn 的零大小标记类型，在编译期确保列名
584    // 与表归属正确，避免字符串列名拼写错误逃逸到运行时。
585    // -----------------------------------------------------------------------
586
587    /// 类型安全 `where_eq`：通过 [`TypedColumn`] 标记类型指定列。
588    ///
589    /// # 示例
590    ///
591    /// ```ignore
592    /// use sz_orm_core::typed::TypedColumn;
593    /// // 假设 typed_query! 生成了 users::col_id
594    /// QueryBuilder::<User>::new()
595    ///     .where_eq_typed::<users::col_id>(Value::from(1i64))
596    ///     .build();
597    /// // 生成：WHERE `id` = ?
598    /// ```
599    pub fn where_eq_typed<C: TypedColumn>(mut self, value: Value) -> Self {
600        self.where_conditions
601            .push(WhereCondition::Eq(C::NAME.to_string(), value));
602        self
603    }
604
605    /// 类型安全 `where_ne`。
606    pub fn where_ne_typed<C: TypedColumn>(mut self, value: Value) -> Self {
607        self.where_conditions
608            .push(WhereCondition::Ne(C::NAME.to_string(), value));
609        self
610    }
611
612    /// 类型安全 `where_gt`。
613    pub fn where_gt_typed<C: TypedColumn>(mut self, value: Value) -> Self {
614        self.where_conditions
615            .push(WhereCondition::Gt(C::NAME.to_string(), value));
616        self
617    }
618
619    /// 类型安全 `where_ge`。
620    pub fn where_ge_typed<C: TypedColumn>(mut self, value: Value) -> Self {
621        self.where_conditions
622            .push(WhereCondition::Ge(C::NAME.to_string(), value));
623        self
624    }
625
626    /// 类型安全 `where_lt`。
627    pub fn where_lt_typed<C: TypedColumn>(mut self, value: Value) -> Self {
628        self.where_conditions
629            .push(WhereCondition::Lt(C::NAME.to_string(), value));
630        self
631    }
632
633    /// 类型安全 `where_le`。
634    pub fn where_le_typed<C: TypedColumn>(mut self, value: Value) -> Self {
635        self.where_conditions
636            .push(WhereCondition::Le(C::NAME.to_string(), value));
637        self
638    }
639
640    /// 类型安全 `where_null`。
641    pub fn where_null_typed<C: TypedColumn>(mut self) -> Self {
642        self.where_conditions
643            .push(WhereCondition::Null(C::NAME.to_string()));
644        self
645    }
646
647    /// 类型安全 `where_not_null`。
648    pub fn where_not_null_typed<C: TypedColumn>(mut self) -> Self {
649        self.where_conditions
650            .push(WhereCondition::NotNull(C::NAME.to_string()));
651        self
652    }
653
654    /// 类型安全 `order_by`（升序）。
655    pub fn order_by_typed<C: TypedColumn>(mut self) -> Self {
656        self.order_by.push(OrderClause {
657            field: C::NAME.to_string(),
658            direction: OrderDirection::Asc,
659        });
660        self
661    }
662
663    /// 类型安全 `order_desc`。
664    pub fn order_desc_typed<C: TypedColumn>(mut self) -> Self {
665        self.order_by.push(OrderClause {
666            field: C::NAME.to_string(),
667            direction: OrderDirection::Desc,
668        });
669        self
670    }
671
672    /// 类型安全 `group_by`。
673    pub fn group_by_typed<C: TypedColumn>(mut self) -> Self {
674        self.group_by.push(C::NAME.to_string());
675        self
676    }
677
678    /// 类型安全 `select`：添加单个列到 SELECT 列表。
679    ///
680    /// 多次调用追加列；与 `select`/`select_quoted` 混用时注意顺序。
681    pub fn select_typed<C: TypedColumn>(mut self) -> Self {
682        self.select_columns.push(C::NAME.to_string());
683        self
684    }
685
686    /// 类型安全 `select`：一次性添加多个列。
687    pub fn select_typed_cols<C: TypedColumn, const N: usize>(mut self) -> Self {
688        // 此方法用于固定数量列的场景；泛型 C 作为占位，实际列名由宏展开填充
689        // 对于多列场景，推荐链式调用 select_typed::<Col1>().select_typed::<Col2>()
690        self.select_columns.push(C::NAME.to_string());
691        self
692    }
693
694    pub fn having(mut self, condition: impl Into<String>) -> Self {
695        self.having_conditions
696            .push(WhereCondition::And(condition.into()));
697        self
698    }
699
700    pub fn limit(mut self, limit: usize) -> Self {
701        self.limit_value = Some(limit);
702        self
703    }
704
705    pub fn offset(mut self, offset: usize) -> Self {
706        self.offset_value = Some(offset);
707        self
708    }
709
710    pub fn page(mut self, page: usize, page_size: usize) -> Self {
711        self.limit_value = Some(page_size);
712        self.offset_value = Some((page.saturating_sub(1)) * page_size);
713        self
714    }
715
716    /// P2-5：基于游标的 Keyset 分页 — 查询指定字段值之后的记录（下一页）
717    ///
718    /// 生成 `WHERE {field} > ? ORDER BY {field} ASC LIMIT {page_size}`。
719    /// 适用于按主键或时间戳递增遍历大表的场景，性能不受数据插入/删除影响。
720    ///
721    /// # 参数
722    ///
723    /// - `field`：排序字段（通常是主键或索引列，如 `id`、`created_at`）
724    /// - `cursor_value`：当前页最后一条记录的该字段值
725    /// - `page_size`：每页大小
726    ///
727    /// # 示例
728    ///
729    /// ```
730    /// use sz_orm_core::{QueryBuilder, DbType, dialect::get_dialect, Value};
731    /// # use sz_orm_core::{Model, ModelExt};
732    /// # #[derive(Clone, Debug)]
733    /// # struct User { id: i64 }
734    /// # impl Model for User {
735    /// #     type PrimaryKey = i64;
736    /// #     fn table_name() -> &'static str { "users" }
737    /// #     fn pk(&self) -> i64 { self.id }
738    /// #     fn set_pk(&mut self, pk: i64) { self.id = pk; }
739    /// # }
740    /// # impl ModelExt for User {
741    /// #     fn columns() -> Vec<&'static str> { vec!["id"] }
742    /// #     fn fillable() -> Vec<&'static str> { vec![] }
743    /// #     fn guarded() -> Vec<&'static str> { vec!["id"] }
744    /// #     fn hidden() -> Vec<&'static str> { vec![] }
745    /// #     fn relations() -> std::collections::HashMap<&'static str, sz_orm_core::Relation> { Default::default() }
746    /// #     fn fill(&mut self, _: std::collections::HashMap<String, Value>) {}
747    /// #     fn to_json(&self) -> serde_json::Value { serde_json::json!({}) }
748    /// # }
749    /// let dialect = get_dialect(DbType::MySQL).unwrap();
750    /// let builder = QueryBuilder::<User>::new(dialect)
751    ///     .keyset_after("id", Value::I64(100), 20);
752    /// let (sql, params) = builder.build_select_with_params();
753    /// // 方言会引用字段名（如 MySQL 的 `id`），去除引号后检查
754    /// let sql_clean = sql.replace('`', "").replace('"', "");
755    /// assert!(sql_clean.contains("id > ?"));
756    /// assert!(sql.to_uppercase().contains("ORDER BY"));
757    /// assert!(sql.to_uppercase().contains("ASC"));
758    /// assert!(sql.contains("LIMIT 20"));
759    /// assert_eq!(params, vec![Value::I64(100)]);
760    /// ```
761    pub fn keyset_after(
762        mut self,
763        field: impl Into<String>,
764        cursor_value: Value,
765        page_size: usize,
766    ) -> Self {
767        let field_str = field.into();
768        // 自动设置 ORDER BY ASC（若字段已存在则更新方向为 ASC，保证 keyset 语义一致）
769        if let Some(existing) = self.order_by.iter_mut().find(|o| o.field == field_str) {
770            existing.direction = OrderDirection::Asc;
771        } else {
772            self.order_by.push(OrderClause {
773                field: field_str.clone(),
774                direction: OrderDirection::Asc,
775            });
776        }
777        self.limit_value = Some(page_size);
778        // 清除 offset（keyset 与 offset 互斥）
779        self.offset_value = None;
780        self.keyset_cursor = Some(KeysetCursor {
781            field: field_str,
782            value: cursor_value,
783            direction: KeysetDirection::After,
784        });
785        self
786    }
787
788    /// P2-5：基于游标的 Keyset 分页 — 查询指定字段值之前的记录（上一页）
789    ///
790    /// 生成 `WHERE {field} < ? ORDER BY {field} DESC LIMIT {page_size}`。
791    /// 适用于反向遍历场景。
792    ///
793    /// # 参数
794    ///
795    /// - `field`：排序字段
796    /// - `cursor_value`：当前页第一条记录的该字段值
797    /// - `page_size`：每页大小
798    ///
799    /// # 示例
800    ///
801    /// ```
802    /// use sz_orm_core::{QueryBuilder, DbType, dialect::get_dialect, Value};
803    /// # use sz_orm_core::{Model, ModelExt};
804    /// # #[derive(Clone, Debug)]
805    /// # struct User { id: i64 }
806    /// # impl Model for User {
807    /// #     type PrimaryKey = i64;
808    /// #     fn table_name() -> &'static str { "users" }
809    /// #     fn pk(&self) -> i64 { self.id }
810    /// #     fn set_pk(&mut self, pk: i64) { self.id = pk; }
811    /// # }
812    /// # impl ModelExt for User {
813    /// #     fn columns() -> Vec<&'static str> { vec!["id"] }
814    /// #     fn fillable() -> Vec<&'static str> { vec![] }
815    /// #     fn guarded() -> Vec<&'static str> { vec!["id"] }
816    /// #     fn hidden() -> Vec<&'static str> { vec![] }
817    /// #     fn relations() -> std::collections::HashMap<&'static str, sz_orm_core::Relation> { Default::default() }
818    /// #     fn fill(&mut self, _: std::collections::HashMap<String, Value>) {}
819    /// #     fn to_json(&self) -> serde_json::Value { serde_json::json!({}) }
820    /// # }
821    /// let dialect = get_dialect(DbType::MySQL).unwrap();
822    /// let builder = QueryBuilder::<User>::new(dialect)
823    ///     .keyset_before("id", Value::I64(100), 20);
824    /// let (sql, params) = builder.build_select_with_params();
825    /// // 方言会引用字段名（如 MySQL 的 `id`），去除引号后检查
826    /// let sql_clean = sql.replace('`', "").replace('"', "");
827    /// assert!(sql_clean.contains("id < ?"));
828    /// assert!(sql.to_uppercase().contains("ORDER BY"));
829    /// assert!(sql.to_uppercase().contains("DESC"));
830    /// assert!(sql.contains("LIMIT 20"));
831    /// assert_eq!(params, vec![Value::I64(100)]);
832    /// ```
833    pub fn keyset_before(
834        mut self,
835        field: impl Into<String>,
836        cursor_value: Value,
837        page_size: usize,
838    ) -> Self {
839        let field_str = field.into();
840        // 自动设置 ORDER BY DESC（若字段已存在则更新方向为 DESC，保证 keyset 语义一致）
841        if let Some(existing) = self.order_by.iter_mut().find(|o| o.field == field_str) {
842            existing.direction = OrderDirection::Desc;
843        } else {
844            self.order_by.push(OrderClause {
845                field: field_str.clone(),
846                direction: OrderDirection::Desc,
847            });
848        }
849        self.limit_value = Some(page_size);
850        self.offset_value = None;
851        self.keyset_cursor = Some(KeysetCursor {
852            field: field_str,
853            value: cursor_value,
854            direction: KeysetDirection::Before,
855        });
856        self
857    }
858
859    pub fn join_inner(
860        mut self,
861        table: impl Into<String>,
862        on_left: impl Into<String>,
863        on_right: impl Into<String>,
864    ) -> Self {
865        self.joins.push(JoinClause::Inner(
866            table.into(),
867            on_left.into(),
868            on_right.into(),
869        ));
870        self
871    }
872
873    pub fn join_left(
874        mut self,
875        table: impl Into<String>,
876        on_left: impl Into<String>,
877        on_right: impl Into<String>,
878    ) -> Self {
879        self.joins.push(JoinClause::Left(
880            table.into(),
881            on_left.into(),
882            on_right.into(),
883        ));
884        self
885    }
886
887    pub fn join_right(
888        mut self,
889        table: impl Into<String>,
890        on_left: impl Into<String>,
891        on_right: impl Into<String>,
892    ) -> Self {
893        self.joins.push(JoinClause::Right(
894            table.into(),
895            on_left.into(),
896            on_right.into(),
897        ));
898        self
899    }
900
901    /// 构建 SELECT SQL 语句
902    ///
903    /// L-5 修复：补充示例文档
904    ///
905    /// 根据 `table`、`select_columns`、`where_conditions`、`joins`、`order_by`、
906    /// `group_by`、`having`、`limit`、`offset` 等条件拼装最终 SQL。
907    /// 若未通过 `table()` 指定表名，则使用 `M::table_name()`。
908    ///
909    /// # 示例
910    ///
911    /// ```ignore
912    /// use sz_orm_core::query::QueryBuilder;
913    /// use sz_orm_core::dialect::MySqlDialect;
914    /// use sz_orm_core::model::Model;
915    ///
916    /// #[derive(Default)]
917    /// struct User;
918    /// impl Model for User {
919    ///     type PrimaryKey = i64;
920    ///     fn table_name() -> &'static str { "users" }
921    ///     fn pk(&self) -> Self::PrimaryKey { 0 }
922    ///     fn set_pk(&mut self, _: Self::PrimaryKey) {}
923    /// }
924    ///
925    /// let sql = QueryBuilder::<User>::new(Box::new(MySqlDialect))
926    ///     .select(vec!["id", "name"])
927    ///     .where_cond("age > 18")
928    ///     .order_by("id DESC")
929    ///     .limit(10)
930    ///     .build_select();
931    /// // sql => "SELECT id, name FROM `users` WHERE age > 18 ORDER BY id DESC LIMIT 10"
932    /// ```
933    #[tracing::instrument(skip(self), fields(op = "select"))]
934    pub fn build_select(&self) -> String {
935        let table = self
936            .table
937            .clone()
938            .unwrap_or_else(|| M::table_name().to_string());
939
940        let columns = if self.select_columns.is_empty() {
941            "*".to_string()
942        } else {
943            self.select_columns.join(", ")
944        };
945
946        let mut sql = format!("SELECT {} FROM {}", columns, self.dialect.quote(&table));
947
948        for join in &self.joins {
949            match join {
950                JoinClause::Inner(t, l, r) => {
951                    sql.push_str(&format!(
952                        " INNER JOIN {} ON {} = {}",
953                        self.dialect.quote(t),
954                        self.dialect.quote(l),
955                        self.dialect.quote(r)
956                    ));
957                }
958                JoinClause::Left(t, l, r) => {
959                    sql.push_str(&format!(
960                        " LEFT JOIN {} ON {} = {}",
961                        self.dialect.quote(t),
962                        self.dialect.quote(l),
963                        self.dialect.quote(r)
964                    ));
965                }
966                JoinClause::Right(t, l, r) => {
967                    sql.push_str(&format!(
968                        " RIGHT JOIN {} ON {} = {}",
969                        self.dialect.quote(t),
970                        self.dialect.quote(l),
971                        self.dialect.quote(r)
972                    ));
973                }
974                JoinClause::Cross(t, on) => {
975                    sql.push_str(&format!(
976                        " CROSS JOIN {} ON {}",
977                        self.dialect.quote(t),
978                        self.dialect.quote(on)
979                    ));
980                }
981            }
982        }
983
984        // P0-1：build_where_clause 内部已处理软删除条件，即使 where_conditions 为空也可能返回非空
985        let where_clause = self.build_where_clause();
986        if !where_clause.is_empty() {
987            sql.push_str(&where_clause);
988        }
989
990        if !self.group_by.is_empty() {
991            let cols: Vec<String> = self
992                .group_by
993                .iter()
994                .map(|c| self.dialect.quote(c))
995                .collect();
996            sql.push_str(" GROUP BY ");
997            sql.push_str(&cols.join(", "));
998        }
999
1000        if !self.having_conditions.is_empty() {
1001            sql.push_str(" HAVING ");
1002            for (i, cond) in self.having_conditions.iter().enumerate() {
1003                if i > 0 {
1004                    sql.push_str(" AND ");
1005                }
1006                if let WhereCondition::And(c) = cond {
1007                    sql.push_str(c);
1008                }
1009            }
1010        }
1011
1012        if !self.order_by.is_empty() {
1013            let order_cols: Vec<String> = self
1014                .order_by
1015                .iter()
1016                .map(|o| {
1017                    let dir = match o.direction {
1018                        OrderDirection::Asc => " ASC",
1019                        OrderDirection::Desc => " DESC",
1020                    };
1021                    format!("{}{}", self.dialect.quote(&o.field), dir)
1022                })
1023                .collect();
1024            sql.push_str(" ORDER BY ");
1025            sql.push_str(&order_cols.join(", "));
1026        }
1027
1028        if let Some(limit) = self.limit_value {
1029            sql.push_str(&format!(" LIMIT {}", limit));
1030        }
1031
1032        if let Some(offset) = self.offset_value {
1033            sql.push_str(&format!(" OFFSET {}", offset));
1034        }
1035
1036        sql
1037    }
1038
1039    /// 构建 WHERE 子句（处理所有条件类型：And/Or/In/NotIn/Between/Null/Eq/Ne/Gt/Lt/Like 等）
1040    ///
1041    /// P0-1：自动追加软删除过滤条件（`AND {soft_delete_field} IS NULL`）
1042    ///
1043    /// 返回空字符串表示无 WHERE 子句
1044    fn build_where_clause(&self) -> String {
1045        self.build_where_clause_with_options(true)
1046    }
1047
1048    /// 构建 WHERE 子句（可控制是否追加软删除过滤）。
1049    ///
1050    /// `include_soft_delete = true`：追加 `AND {soft_delete_field} IS NULL`（默认行为）
1051    /// `include_soft_delete = false`：不追加软删除过滤（用于 `build_force_delete`）
1052    ///
1053    /// P0-3：租户条件总是追加（若启用），不受 `include_soft_delete` 控制。
1054    /// 物理删除也应受租户隔离约束，跨租户操作需显式 `without_tenant()`。
1055    fn build_where_clause_with_options(&self, include_soft_delete: bool) -> String {
1056        // P0-1：构造软删除条件（若有且启用）
1057        let soft_delete_cond = if include_soft_delete {
1058            self.build_soft_delete_condition()
1059        } else {
1060            None
1061        };
1062
1063        // P0-3：构造租户条件（若有且启用）— 无参数版本内嵌转义值
1064        let tenant_cond = self.build_tenant_condition().map(|(sql, value)| {
1065            // sql 形如 "`tenant_id` = ?"，将 ? 替换为内嵌值
1066            sql.replacen('?', &value.to_param_with_dialect(&*self.dialect), 1)
1067        });
1068
1069        // 无用户条件且无软删除条件且无租户条件且无 keyset 游标 → 空 WHERE
1070        if self.where_conditions.is_empty()
1071            && soft_delete_cond.is_none()
1072            && tenant_cond.is_none()
1073            && self.keyset_cursor.is_none()
1074        {
1075            return String::new();
1076        }
1077
1078        // 将每个条件转换为字符串，OR 条件标记前缀
1079        let mut conditions: Vec<String> = self
1080            .where_conditions
1081            .iter()
1082            .map(|cond| match cond {
1083                WhereCondition::And(c) => c.clone(),
1084                WhereCondition::Or(c) => format!("OR {}", c),
1085                // P0-2：参数化条件在无参数版本中直接 inline 值（用于 build_select 等无参数绑定场景）
1086                WhereCondition::Eq(f, v) => format!(
1087                    "{} = {}",
1088                    self.dialect.quote(f),
1089                    v.to_param_with_dialect(&*self.dialect)
1090                ),
1091                WhereCondition::Ne(f, v) => format!(
1092                    "{} != {}",
1093                    self.dialect.quote(f),
1094                    v.to_param_with_dialect(&*self.dialect)
1095                ),
1096                WhereCondition::Gt(f, v) => format!(
1097                    "{} > {}",
1098                    self.dialect.quote(f),
1099                    v.to_param_with_dialect(&*self.dialect)
1100                ),
1101                WhereCondition::Ge(f, v) => format!(
1102                    "{} >= {}",
1103                    self.dialect.quote(f),
1104                    v.to_param_with_dialect(&*self.dialect)
1105                ),
1106                WhereCondition::Lt(f, v) => format!(
1107                    "{} < {}",
1108                    self.dialect.quote(f),
1109                    v.to_param_with_dialect(&*self.dialect)
1110                ),
1111                WhereCondition::Le(f, v) => format!(
1112                    "{} <= {}",
1113                    self.dialect.quote(f),
1114                    v.to_param_with_dialect(&*self.dialect)
1115                ),
1116                WhereCondition::Like(f, v) => format!(
1117                    "{} LIKE {}",
1118                    self.dialect.quote(f),
1119                    v.to_param_with_dialect(&*self.dialect)
1120                ),
1121                WhereCondition::OrEq(f, v) => format!(
1122                    "OR {} = {}",
1123                    self.dialect.quote(f),
1124                    v.to_param_with_dialect(&*self.dialect)
1125                ),
1126                WhereCondition::OrNe(f, v) => format!(
1127                    "OR {} != {}",
1128                    self.dialect.quote(f),
1129                    v.to_param_with_dialect(&*self.dialect)
1130                ),
1131                WhereCondition::OrGt(f, v) => format!(
1132                    "OR {} > {}",
1133                    self.dialect.quote(f),
1134                    v.to_param_with_dialect(&*self.dialect)
1135                ),
1136                WhereCondition::OrGe(f, v) => format!(
1137                    "OR {} >= {}",
1138                    self.dialect.quote(f),
1139                    v.to_param_with_dialect(&*self.dialect)
1140                ),
1141                WhereCondition::OrLt(f, v) => format!(
1142                    "OR {} < {}",
1143                    self.dialect.quote(f),
1144                    v.to_param_with_dialect(&*self.dialect)
1145                ),
1146                WhereCondition::OrLe(f, v) => format!(
1147                    "OR {} <= {}",
1148                    self.dialect.quote(f),
1149                    v.to_param_with_dialect(&*self.dialect)
1150                ),
1151                WhereCondition::OrLike(f, v) => format!(
1152                    "OR {} LIKE {}",
1153                    self.dialect.quote(f),
1154                    v.to_param_with_dialect(&*self.dialect)
1155                ),
1156                WhereCondition::In(f, vals) => {
1157                    // v0.2.2 修复 H-1：使用方言感知的转义
1158                    let vals_str: Vec<String> = vals
1159                        .iter()
1160                        .map(|v| v.to_param_with_dialect(&*self.dialect).to_string())
1161                        .collect();
1162                    format!("{} IN ({})", self.dialect.quote(f), vals_str.join(", "))
1163                }
1164                WhereCondition::NotIn(f, vals) => {
1165                    let vals_str: Vec<String> = vals
1166                        .iter()
1167                        .map(|v| v.to_param_with_dialect(&*self.dialect).to_string())
1168                        .collect();
1169                    format!("{} NOT IN ({})", self.dialect.quote(f), vals_str.join(", "))
1170                }
1171                WhereCondition::Between(f, start, end) => {
1172                    format!(
1173                        "{} BETWEEN {} AND {}",
1174                        self.dialect.quote(f),
1175                        start.to_param_with_dialect(&*self.dialect),
1176                        end.to_param_with_dialect(&*self.dialect)
1177                    )
1178                }
1179                WhereCondition::NotBetween(f, start, end) => {
1180                    format!(
1181                        "{} NOT BETWEEN {} AND {}",
1182                        self.dialect.quote(f),
1183                        start.to_param_with_dialect(&*self.dialect),
1184                        end.to_param_with_dialect(&*self.dialect)
1185                    )
1186                }
1187                WhereCondition::Null(f) => format!("{} IS NULL", self.dialect.quote(f)),
1188                WhereCondition::NotNull(f) => format!("{} IS NOT NULL", self.dialect.quote(f)),
1189                WhereCondition::Exists(s) => format!("EXISTS ({})", s),
1190                WhereCondition::NotExists(s) => format!("NOT EXISTS ({})", s),
1191            })
1192            .collect();
1193
1194        // P0-1：追加软删除条件（作为最后一个 AND 条件）
1195        if let Some(sd_cond) = soft_delete_cond {
1196            conditions.push(sd_cond);
1197        }
1198
1199        // P0-3：追加租户条件（在软删除之后，作为 AND 条件）
1200        if let Some(t_cond) = tenant_cond {
1201            conditions.push(t_cond);
1202        }
1203
1204        // P2-5：追加 keyset 游标条件（在租户条件之后，内嵌转义值）
1205        if let Some(ref cursor) = self.keyset_cursor {
1206            let op = match cursor.direction {
1207                KeysetDirection::After => ">",
1208                KeysetDirection::Before => "<",
1209            };
1210            conditions.push(format!(
1211                "{} {} {}",
1212                self.dialect.quote(&cursor.field),
1213                op,
1214                cursor.value.to_param_with_dialect(&*self.dialect)
1215            ));
1216        }
1217
1218        if conditions.is_empty() {
1219            return String::new();
1220        }
1221
1222        // OR 分组逻辑：将相邻的 OR 条件组合成 (cond1 OR cond2) 形式
1223        // 边界处理：如果第一个条件就是 OR（不合理但需防御），当作 AND 处理
1224        let mut groups: Vec<Vec<String>> = Vec::new();
1225        let mut current_group: Vec<String> = Vec::new();
1226        for cond in conditions.iter() {
1227            if let Some(stripped) = cond.strip_prefix("OR ") {
1228                // OR 条件：无论是否首个，都把 OR 前缀去掉当作普通条件加入当前组
1229                current_group.push(stripped.to_string());
1230            } else {
1231                // AND 条件：如果当前组非空，先保存
1232                if !current_group.is_empty() {
1233                    groups.push(std::mem::take(&mut current_group));
1234                }
1235                current_group.push(cond.clone());
1236            }
1237        }
1238        if !current_group.is_empty() {
1239            groups.push(current_group);
1240        }
1241
1242        let group_strs: Vec<String> = groups
1243            .iter()
1244            .map(|g| {
1245                if g.len() == 1 {
1246                    g[0].clone()
1247                } else {
1248                    format!("({})", g.join(" OR "))
1249                }
1250            })
1251            .collect();
1252
1253        // SAFETY: group_strs 来自 WhereCondition::Eq/Gt 等参数化变体渲染，值已绑定为 ? 占位符或内联常量
1254        format!(" WHERE {}", group_strs.join(" AND "))
1255    }
1256
1257    #[tracing::instrument(skip(self, data), fields(op = "insert"))]
1258    pub fn build_insert(&self, data: &std::collections::HashMap<String, Value>) -> String {
1259        let table = self
1260            .table
1261            .clone()
1262            .unwrap_or_else(|| M::table_name().to_string());
1263
1264        if data.is_empty() {
1265            return String::new();
1266        }
1267
1268        let columns: Vec<String> = data.keys().map(|k| self.dialect.quote(k)).collect();
1269        // v0.2.2 修复 H-1：使用方言感知的转义
1270        let values: Vec<String> = data
1271            .values()
1272            .map(|v| v.to_param_with_dialect(&*self.dialect).to_string())
1273            .collect();
1274
1275        format!(
1276            "INSERT INTO {} ({}) VALUES ({})",
1277            self.dialect.quote(&table),
1278            columns.join(", "),
1279            values.join(", ")
1280        )
1281    }
1282
1283    #[tracing::instrument(skip(self, data), fields(op = "update"))]
1284    pub fn build_update(&self, data: &std::collections::HashMap<String, Value>) -> String {
1285        let table = self
1286            .table
1287            .clone()
1288            .unwrap_or_else(|| M::table_name().to_string());
1289
1290        if data.is_empty() {
1291            return String::new();
1292        }
1293
1294        let set_clauses: Vec<String> = data
1295            .iter()
1296            .map(|(k, v)| {
1297                format!(
1298                    "{} = {}",
1299                    self.dialect.quote(k),
1300                    v.to_param_with_dialect(&*self.dialect)
1301                )
1302            })
1303            .collect();
1304
1305        let mut sql = format!(
1306            "UPDATE {} SET {}",
1307            self.dialect.quote(&table),
1308            set_clauses.join(", ")
1309        );
1310
1311        sql.push_str(&self.build_where_clause());
1312        sql
1313    }
1314
1315    /// 构建 DELETE SQL 语句。
1316    ///
1317    /// **P0-1 软删除集成（v1.3.0+）**：当 `M: Model` 实现了 `soft_delete_field()`
1318    /// 返回 `Some(field)` 且未调用 `without_soft_delete()` 时，本方法自动生成
1319    /// `UPDATE {table} SET {field} = NOW() WHERE ...` 而非 `DELETE FROM ...`。
1320    ///
1321    /// 这与 SeaORM 的 `ActiveModelBehavior::after_delete` + `ActiveValue::Set`
1322    /// 行为对齐：删除操作实际是软删除 UPDATE。
1323    ///
1324    /// 若需物理删除，请使用 [`build_force_delete`](Self::build_force_delete)。
1325    #[tracing::instrument(skip(self), fields(op = "delete"))]
1326    pub fn build_delete(&self) -> String {
1327        let table = self
1328            .table
1329            .clone()
1330            .unwrap_or_else(|| M::table_name().to_string());
1331
1332        // P0-1：软删除启用时转为 UPDATE SET {field} = NOW()
1333        if let Some(field) = self.soft_delete_field() {
1334            let where_clause = self.build_where_clause();
1335            return format!(
1336                "UPDATE {} SET {} = NOW(){}",
1337                self.dialect.quote(&table),
1338                self.dialect.quote(field),
1339                where_clause
1340            );
1341        }
1342
1343        let mut sql = format!("DELETE FROM {}", self.dialect.quote(&table));
1344        sql.push_str(&self.build_where_clause());
1345        sql
1346    }
1347
1348    /// 构建物理 DELETE SQL 语句（绕过软删除）。
1349    ///
1350    /// 即使 Model 实现了 `soft_delete_field()`，也生成 `DELETE FROM ...`，
1351    /// 且**不追加** `WHERE deleted_at IS NULL` 过滤（保留用户指定的 WHERE 条件）。
1352    /// 用于管理员强制清除场景。
1353    ///
1354    /// # 安全警告
1355    ///
1356    /// 物理删除不可恢复，请谨慎使用。
1357    pub fn build_force_delete(&self) -> String {
1358        let table = self
1359            .table
1360            .clone()
1361            .unwrap_or_else(|| M::table_name().to_string());
1362
1363        let mut sql = format!("DELETE FROM {}", self.dialect.quote(&table));
1364        // P0-1：物理删除不追加软删除过滤（include_soft_delete = false）
1365        sql.push_str(&self.build_where_clause_with_options(false));
1366        sql
1367    }
1368
1369    // ===================== 参数绑定版本（v1.1.0 新增） =====================
1370
1371    /// 构建 WHERE 子句（参数绑定版本）。
1372    ///
1373    /// P0-1：自动追加软删除过滤条件（`AND {soft_delete_field} IS NULL`，无参数）
1374    ///
1375    /// 将 `In`/`NotIn`/`Between`/`NotBetween`/`Eq`/`Ne`/`Gt`/`Ge`/`Lt`/`Le`/`Like`
1376    /// 条件中的值替换为 `?` 占位符，值收集到 `params` 向量中。
1377    /// `And`/`Or`/`Exists` 等原始字符串条件不提取参数（调用方负责安全）。
1378    fn build_where_clause_with_params(&self) -> (String, Vec<Value>) {
1379        // 默认包含软删除条件
1380        self.build_where_clause_with_params_options(true)
1381    }
1382
1383    /// 构建参数化 WHERE 子句（可控是否包含软删除条件）
1384    ///
1385    /// # 参数
1386    ///
1387    /// - `include_soft_delete`：true 时追加软删除条件；false 时跳过（用于物理删除等场景）
1388    ///
1389    /// P0-3：租户条件总是追加（若启用），不受 `include_soft_delete` 控制。
1390    /// 租户值通过 `?` 占位符绑定，加入 `params` 列表末尾。
1391    fn build_where_clause_with_params_options(
1392        &self,
1393        include_soft_delete: bool,
1394    ) -> (String, Vec<Value>) {
1395        // P0-1：构造软删除条件（若有且启用，无参数）
1396        let soft_delete_cond = if include_soft_delete {
1397            self.build_soft_delete_condition()
1398        } else {
1399            None
1400        };
1401
1402        // P0-3：构造租户条件（若有且启用）— 参数化版本保留 (sql, value)
1403        let tenant_cond = self.build_tenant_condition();
1404
1405        // 无用户条件且无软删除条件且无租户条件且无 keyset 游标 → 空 WHERE
1406        if self.where_conditions.is_empty()
1407            && soft_delete_cond.is_none()
1408            && tenant_cond.is_none()
1409            && self.keyset_cursor.is_none()
1410        {
1411            return (String::new(), Vec::new());
1412        }
1413
1414        let mut params = Vec::new();
1415
1416        let mut conditions: Vec<String> = self
1417            .where_conditions
1418            .iter()
1419            .map(|cond| match cond {
1420                WhereCondition::And(c) => c.clone(),
1421                WhereCondition::Or(c) => format!("OR {}", c),
1422                // P0-2：参数化条件使用 `?` 占位符
1423                WhereCondition::Eq(f, v) => {
1424                    params.push(v.clone());
1425                    format!("{} = ?", self.dialect.quote(f))
1426                }
1427                WhereCondition::Ne(f, v) => {
1428                    params.push(v.clone());
1429                    format!("{} != ?", self.dialect.quote(f))
1430                }
1431                WhereCondition::Gt(f, v) => {
1432                    params.push(v.clone());
1433                    format!("{} > ?", self.dialect.quote(f))
1434                }
1435                WhereCondition::Ge(f, v) => {
1436                    params.push(v.clone());
1437                    format!("{} >= ?", self.dialect.quote(f))
1438                }
1439                WhereCondition::Lt(f, v) => {
1440                    params.push(v.clone());
1441                    format!("{} < ?", self.dialect.quote(f))
1442                }
1443                WhereCondition::Le(f, v) => {
1444                    params.push(v.clone());
1445                    format!("{} <= ?", self.dialect.quote(f))
1446                }
1447                WhereCondition::Like(f, v) => {
1448                    params.push(v.clone());
1449                    format!("{} LIKE ?", self.dialect.quote(f))
1450                }
1451                WhereCondition::OrEq(f, v) => {
1452                    params.push(v.clone());
1453                    format!("OR {} = ?", self.dialect.quote(f))
1454                }
1455                WhereCondition::OrNe(f, v) => {
1456                    params.push(v.clone());
1457                    format!("OR {} != ?", self.dialect.quote(f))
1458                }
1459                WhereCondition::OrGt(f, v) => {
1460                    params.push(v.clone());
1461                    format!("OR {} > ?", self.dialect.quote(f))
1462                }
1463                WhereCondition::OrGe(f, v) => {
1464                    params.push(v.clone());
1465                    format!("OR {} >= ?", self.dialect.quote(f))
1466                }
1467                WhereCondition::OrLt(f, v) => {
1468                    params.push(v.clone());
1469                    format!("OR {} < ?", self.dialect.quote(f))
1470                }
1471                WhereCondition::OrLe(f, v) => {
1472                    params.push(v.clone());
1473                    format!("OR {} <= ?", self.dialect.quote(f))
1474                }
1475                WhereCondition::OrLike(f, v) => {
1476                    params.push(v.clone());
1477                    format!("OR {} LIKE ?", self.dialect.quote(f))
1478                }
1479                WhereCondition::In(f, vals) => {
1480                    let placeholders: Vec<&str> = vals.iter().map(|_| "?").collect();
1481                    params.extend(vals.iter().cloned());
1482                    format!("{} IN ({})", self.dialect.quote(f), placeholders.join(", "))
1483                }
1484                WhereCondition::NotIn(f, vals) => {
1485                    let placeholders: Vec<&str> = vals.iter().map(|_| "?").collect();
1486                    params.extend(vals.iter().cloned());
1487                    format!(
1488                        "{} NOT IN ({})",
1489                        self.dialect.quote(f),
1490                        placeholders.join(", ")
1491                    )
1492                }
1493                WhereCondition::Between(f, start, end) => {
1494                    params.push(start.clone());
1495                    params.push(end.clone());
1496                    format!("{} BETWEEN ? AND ?", self.dialect.quote(f))
1497                }
1498                WhereCondition::NotBetween(f, start, end) => {
1499                    params.push(start.clone());
1500                    params.push(end.clone());
1501                    format!("{} NOT BETWEEN ? AND ?", self.dialect.quote(f))
1502                }
1503                WhereCondition::Null(f) => format!("{} IS NULL", self.dialect.quote(f)),
1504                WhereCondition::NotNull(f) => format!("{} IS NOT NULL", self.dialect.quote(f)),
1505                WhereCondition::Exists(s) => format!("EXISTS ({})", s),
1506                WhereCondition::NotExists(s) => format!("NOT EXISTS ({})", s),
1507            })
1508            .collect();
1509
1510        // P0-1：追加软删除条件（作为最后一个 AND 条件，无参数）
1511        if let Some(sd_cond) = soft_delete_cond {
1512            conditions.push(sd_cond);
1513        }
1514
1515        // P0-3：追加租户条件（在软删除之后，参数化绑定）
1516        if let Some((t_sql, t_value)) = tenant_cond {
1517            conditions.push(t_sql);
1518            params.push(t_value);
1519        }
1520
1521        // P2-5：追加 keyset 游标条件（在租户条件之后，参数化绑定）
1522        if let Some(ref cursor) = self.keyset_cursor {
1523            let op = match cursor.direction {
1524                KeysetDirection::After => ">",
1525                KeysetDirection::Before => "<",
1526            };
1527            conditions.push(format!("{} {} ?", self.dialect.quote(&cursor.field), op));
1528            params.push(cursor.value.clone());
1529        }
1530
1531        if conditions.is_empty() {
1532            return (String::new(), params);
1533        }
1534
1535        // OR 分组逻辑：与 build_where_clause 相同
1536        let mut groups: Vec<Vec<String>> = Vec::new();
1537        let mut current_group: Vec<String> = Vec::new();
1538        for cond in conditions.iter() {
1539            if let Some(stripped) = cond.strip_prefix("OR ") {
1540                current_group.push(stripped.to_string());
1541            } else {
1542                if !current_group.is_empty() {
1543                    groups.push(std::mem::take(&mut current_group));
1544                }
1545                current_group.push(cond.clone());
1546            }
1547        }
1548        if !current_group.is_empty() {
1549            groups.push(current_group);
1550        }
1551
1552        let group_strs: Vec<String> = groups
1553            .iter()
1554            .map(|g| {
1555                if g.len() == 1 {
1556                    g[0].clone()
1557                } else {
1558                    format!("({})", g.join(" OR "))
1559                }
1560            })
1561            .collect();
1562
1563        // SAFETY: group_strs 来自 WhereCondition::Eq/Gt 等参数化变体渲染，值已绑定为 ? 占位符
1564        (format!(" WHERE {}", group_strs.join(" AND ")), params)
1565    }
1566
1567    /// 构建 SELECT SQL（参数绑定版本）。
1568    ///
1569    /// WHERE 子句中的值使用 `?` 占位符，值通过 `params` 返回。
1570    /// 适用于 `Connection::query_with_params()`。
1571    pub fn build_select_with_params(&self) -> (String, Vec<Value>) {
1572        let table = self
1573            .table
1574            .clone()
1575            .unwrap_or_else(|| M::table_name().to_string());
1576        let columns = if self.select_columns.is_empty() {
1577            "*".to_string()
1578        } else {
1579            self.select_columns.join(", ")
1580        };
1581
1582        let mut sql = format!("SELECT {} FROM {}", columns, self.dialect.quote(&table));
1583
1584        for join in &self.joins {
1585            match join {
1586                JoinClause::Inner(t, l, r) => {
1587                    sql.push_str(&format!(
1588                        " INNER JOIN {} ON {} = {}",
1589                        self.dialect.quote(t),
1590                        self.dialect.quote(l),
1591                        self.dialect.quote(r)
1592                    ));
1593                }
1594                JoinClause::Left(t, l, r) => {
1595                    sql.push_str(&format!(
1596                        " LEFT JOIN {} ON {} = {}",
1597                        self.dialect.quote(t),
1598                        self.dialect.quote(l),
1599                        self.dialect.quote(r)
1600                    ));
1601                }
1602                JoinClause::Right(t, l, r) => {
1603                    sql.push_str(&format!(
1604                        " RIGHT JOIN {} ON {} = {}",
1605                        self.dialect.quote(t),
1606                        self.dialect.quote(l),
1607                        self.dialect.quote(r)
1608                    ));
1609                }
1610                JoinClause::Cross(t, on) => {
1611                    sql.push_str(&format!(
1612                        " CROSS JOIN {} ON {}",
1613                        self.dialect.quote(t),
1614                        self.dialect.quote(on)
1615                    ));
1616                }
1617            }
1618        }
1619
1620        let mut params = Vec::new();
1621        // P0-1：build_where_clause_with_params 内部已处理软删除条件
1622        let (where_clause, where_params) = self.build_where_clause_with_params();
1623        if !where_clause.is_empty() {
1624            sql.push_str(&where_clause);
1625            params = where_params;
1626        }
1627
1628        if !self.group_by.is_empty() {
1629            let cols: Vec<String> = self
1630                .group_by
1631                .iter()
1632                .map(|c| self.dialect.quote(c))
1633                .collect();
1634            sql.push_str(" GROUP BY ");
1635            sql.push_str(&cols.join(", "));
1636        }
1637
1638        if !self.having_conditions.is_empty() {
1639            sql.push_str(" HAVING ");
1640            for (i, cond) in self.having_conditions.iter().enumerate() {
1641                if i > 0 {
1642                    sql.push_str(" AND ");
1643                }
1644                if let WhereCondition::And(c) = cond {
1645                    sql.push_str(c);
1646                }
1647            }
1648        }
1649
1650        if !self.order_by.is_empty() {
1651            let order_cols: Vec<String> = self
1652                .order_by
1653                .iter()
1654                .map(|o| {
1655                    let dir = match o.direction {
1656                        OrderDirection::Asc => " ASC",
1657                        OrderDirection::Desc => " DESC",
1658                    };
1659                    format!("{}{}", self.dialect.quote(&o.field), dir)
1660                })
1661                .collect();
1662            sql.push_str(" ORDER BY ");
1663            sql.push_str(&order_cols.join(", "));
1664        }
1665
1666        if let Some(limit) = self.limit_value {
1667            sql.push_str(&format!(" LIMIT {}", limit));
1668        }
1669        if let Some(offset) = self.offset_value {
1670            sql.push_str(&format!(" OFFSET {}", offset));
1671        }
1672
1673        (sql, params)
1674    }
1675
1676    /// 构建 INSERT SQL（参数绑定版本）。
1677    pub fn build_insert_with_params(
1678        &self,
1679        data: &std::collections::HashMap<String, Value>,
1680    ) -> (String, Vec<Value>) {
1681        let table = self
1682            .table
1683            .clone()
1684            .unwrap_or_else(|| M::table_name().to_string());
1685        if data.is_empty() {
1686            return (String::new(), Vec::new());
1687        }
1688
1689        let mut columns = Vec::with_capacity(data.len());
1690        let mut params = Vec::with_capacity(data.len());
1691        let placeholders: Vec<&str> = data.iter().map(|_| "?").collect();
1692        for (k, v) in data.iter() {
1693            columns.push(self.dialect.quote(k));
1694            params.push(v.clone());
1695        }
1696
1697        let sql = format!(
1698            "INSERT INTO {} ({}) VALUES ({})",
1699            self.dialect.quote(&table),
1700            columns.join(", "),
1701            placeholders.join(", ")
1702        );
1703        (sql, params)
1704    }
1705
1706    /// P2-6：构建批量 INSERT SQL（参数绑定版本）。
1707    ///
1708    /// 生成 `INSERT INTO t (c1, c2) VALUES (?, ?), (?, ?), ...` 形式的多行插入 SQL。
1709    /// 所有行的列必须一致（取第一行的列顺序）；空行列表返回空 SQL。
1710    ///
1711    /// **L3 实现深度**：使用参数化占位符 `?`，所有值通过 `params` 绑定，杜绝 SQL 注入。
1712    pub fn build_batch_insert_with_params(
1713        &self,
1714        rows: &[std::collections::HashMap<String, Value>],
1715    ) -> (String, Vec<Value>) {
1716        let table = self
1717            .table
1718            .clone()
1719            .unwrap_or_else(|| M::table_name().to_string());
1720        if rows.is_empty() {
1721            return (String::new(), Vec::new());
1722        }
1723
1724        // 取第一行的列作为列顺序（所有行必须一致）
1725        let first_row = &rows[0];
1726        let columns: Vec<String> = first_row.keys().cloned().collect();
1727        let quoted_columns: Vec<String> = columns.iter().map(|c| self.dialect.quote(c)).collect();
1728
1729        let mut params = Vec::with_capacity(rows.len() * columns.len());
1730        let mut value_groups: Vec<String> = Vec::with_capacity(rows.len());
1731        let is_pg = self.dialect.db_type() == DbType::PostgreSQL;
1732        let mut param_idx = 1usize;
1733        for row in rows {
1734            let placeholders: Vec<String> = columns
1735                .iter()
1736                .map(|col| match row.get(col) {
1737                    Some(v) => {
1738                        params.push(v.clone());
1739                        if is_pg {
1740                            let p = format!("${}", param_idx);
1741                            param_idx += 1;
1742                            p
1743                        } else {
1744                            "?".to_string()
1745                        }
1746                    }
1747                    None => "NULL".to_string(),
1748                })
1749                .collect();
1750            value_groups.push(format!("({})", placeholders.join(", ")));
1751        }
1752
1753        let sql = format!(
1754            "INSERT INTO {} ({}) VALUES {}",
1755            self.dialect.quote(&table),
1756            quoted_columns.join(", "),
1757            value_groups.join(", ")
1758        );
1759        (sql, params)
1760    }
1761
1762    /// P2-6：构建批量 Upsert SQL（参数绑定版本）。
1763    ///
1764    /// 在 `build_batch_insert_with_params` 基础上追加冲突处理子句：
1765    /// - MySQL: `ON DUPLICATE KEY UPDATE col=VALUES(col), ...`
1766    /// - PostgreSQL/SQLite: `ON CONFLICT (conflict_cols) DO UPDATE SET col=EXCLUDED.col, ...`
1767    /// - Oracle/SQL Server/ClickHouse/Db2: 返回 `Err(DbError::InvalidInput)`（不支持）
1768    ///
1769    /// # 参数
1770    /// - `rows`: 批量数据行（所有行的列必须一致）
1771    /// - `conflict_columns`: 冲突检测列（主键/唯一键）；MySQL 自动检测可传空
1772    /// - `update_columns`: 冲突时更新的列；空切片表示更新所有非冲突列
1773    ///
1774    /// # 返回
1775    /// - `Ok((sql, params))`: 生成的 SQL 和参数列表
1776    /// - `Err(DbError::InvalidInput)`: 方言不支持 upsert 或 rows 为空
1777    ///
1778    /// **L3 实现深度**：
1779    /// 1. SQL 下推：冲突处理由数据库执行，非内存判断
1780    /// 2. 参数化：所有值通过 `?` 占位符绑定，不拼接用户值
1781    /// 3. 实际执行：生成标准 INSERT...ON CONFLICT/ON DUPLICATE KEY SQL
1782    pub fn build_batch_upsert_with_params(
1783        &self,
1784        rows: &[std::collections::HashMap<String, Value>],
1785        conflict_columns: &[&str],
1786        update_columns: &[&str],
1787    ) -> Result<(String, Vec<Value>), crate::DbError> {
1788        if rows.is_empty() {
1789            return Err(crate::DbError::InvalidInput(
1790                "build_batch_upsert_with_params: rows cannot be empty".to_string(),
1791            ));
1792        }
1793
1794        // 构建批量 INSERT 部分
1795        let (insert_sql, params) = self.build_batch_insert_with_params(rows);
1796        if insert_sql.is_empty() {
1797            return Err(crate::DbError::InvalidInput(
1798                "build_batch_upsert_with_params: failed to build INSERT part".to_string(),
1799            ));
1800        }
1801
1802        // 取所有列名（原始未 quote）
1803        let all_columns: Vec<String> = rows[0].keys().cloned().collect();
1804
1805        // 调用方言生成冲突处理子句
1806        let conflict_clause = self
1807            .dialect
1808            .build_upsert_on_conflict(conflict_columns, update_columns, &all_columns)
1809            .ok_or_else(|| {
1810                crate::DbError::InvalidInput(format!(
1811                    "build_batch_upsert_with_params: dialect {:?} does not support upsert (ON CONFLICT / ON DUPLICATE KEY UPDATE). Consider using MERGE statement or individual upserts instead.",
1812                    self.dialect.db_type()
1813                ))
1814            })?;
1815
1816        let sql = format!("{} {}", insert_sql, conflict_clause);
1817        Ok((sql, params))
1818    }
1819
1820    /// 构建 UPDATE SQL（参数绑定版本）。
1821    /// 参数顺序：SET 参数在前，WHERE 参数在后。
1822    pub fn build_update_with_params(
1823        &self,
1824        data: &std::collections::HashMap<String, Value>,
1825    ) -> (String, Vec<Value>) {
1826        let table = self
1827            .table
1828            .clone()
1829            .unwrap_or_else(|| M::table_name().to_string());
1830        if data.is_empty() {
1831            return (String::new(), Vec::new());
1832        }
1833
1834        let mut set_clauses = Vec::with_capacity(data.len());
1835        let mut params = Vec::with_capacity(data.len());
1836        for (k, v) in data.iter() {
1837            set_clauses.push(format!("{} = ?", self.dialect.quote(k)));
1838            params.push(v.clone());
1839        }
1840
1841        let mut sql = format!(
1842            "UPDATE {} SET {}",
1843            self.dialect.quote(&table),
1844            set_clauses.join(", ")
1845        );
1846
1847        // P0-1：build_where_clause_with_params 内部已处理软删除条件
1848        let (where_clause, where_params) = self.build_where_clause_with_params();
1849        if !where_clause.is_empty() {
1850            sql.push_str(&where_clause);
1851            params.extend(where_params);
1852        }
1853
1854        (sql, params)
1855    }
1856
1857    /// 构建 DELETE SQL（参数绑定版本）。
1858    ///
1859    /// **P0-1 软删除集成（v1.3.0+）**：当 Model 启用软删除时，自动生成
1860    /// `UPDATE {table} SET {field} = NOW() WHERE ...` 而非 `DELETE FROM ...`。
1861    /// 参数列表为空（NOW() 由数据库填充）。
1862    pub fn build_delete_with_params(&self) -> (String, Vec<Value>) {
1863        let table = self
1864            .table
1865            .clone()
1866            .unwrap_or_else(|| M::table_name().to_string());
1867
1868        // P0-1：软删除启用时转为 UPDATE SET {field} = NOW()
1869        if let Some(field) = self.soft_delete_field() {
1870            let (where_clause, where_params) = self.build_where_clause_with_params();
1871            let sql = format!(
1872                "UPDATE {} SET {} = NOW(){}",
1873                self.dialect.quote(&table),
1874                self.dialect.quote(field),
1875                where_clause
1876            );
1877            return (sql, where_params);
1878        }
1879
1880        let mut sql = format!("DELETE FROM {}", self.dialect.quote(&table));
1881        let mut params = Vec::new();
1882
1883        let (where_clause, where_params) = self.build_where_clause_with_params();
1884        if !where_clause.is_empty() {
1885            sql.push_str(&where_clause);
1886            params = where_params;
1887        }
1888
1889        (sql, params)
1890    }
1891
1892    /// 构建物理 DELETE SQL（参数绑定版本，绕过软删除）。
1893    ///
1894    /// 即使 Model 启用软删除，也生成 `DELETE FROM ...`，且不追加软删除过滤。
1895    pub fn build_force_delete_with_params(&self) -> (String, Vec<Value>) {
1896        let table = self
1897            .table
1898            .clone()
1899            .unwrap_or_else(|| M::table_name().to_string());
1900
1901        let mut sql = format!("DELETE FROM {}", self.dialect.quote(&table));
1902        let mut params = Vec::new();
1903
1904        // P0-1：物理删除不追加软删除过滤，使用 build_where_clause_with_params_no_soft_delete
1905        let (where_clause, where_params) = self.build_where_clause_with_params_options(false);
1906        if !where_clause.is_empty() {
1907            sql.push_str(&where_clause);
1908            params = where_params;
1909        }
1910
1911        (sql, params)
1912    }
1913
1914    pub fn build_count(&self) -> String {
1915        let table = self
1916            .table
1917            .clone()
1918            .unwrap_or_else(|| M::table_name().to_string());
1919
1920        let mut sql = format!(
1921            "SELECT COUNT(*) as total FROM {}",
1922            self.dialect.quote(&table)
1923        );
1924        sql.push_str(&self.build_where_clause());
1925        sql
1926    }
1927
1928    pub fn build_exists(&self) -> String {
1929        let table = self
1930            .table
1931            .clone()
1932            .unwrap_or_else(|| M::table_name().to_string());
1933
1934        let mut sql = format!("SELECT 1 FROM {}", self.dialect.quote(&table));
1935        sql.push_str(&self.build_where_clause());
1936        sql.push_str(" LIMIT 1");
1937        format!("SELECT EXISTS({})", sql)
1938    }
1939
1940    pub fn build_max(&self, field: &str) -> String {
1941        let table = self
1942            .table
1943            .clone()
1944            .unwrap_or_else(|| M::table_name().to_string());
1945
1946        let mut sql = format!(
1947            "SELECT MAX({}) as max_val FROM {}",
1948            self.dialect.quote(field),
1949            self.dialect.quote(&table)
1950        );
1951        sql.push_str(&self.build_where_clause());
1952        sql
1953    }
1954
1955    pub fn build_min(&self, field: &str) -> String {
1956        let table = self
1957            .table
1958            .clone()
1959            .unwrap_or_else(|| M::table_name().to_string());
1960
1961        let mut sql = format!(
1962            "SELECT MIN({}) as min_val FROM {}",
1963            self.dialect.quote(field),
1964            self.dialect.quote(&table)
1965        );
1966        sql.push_str(&self.build_where_clause());
1967        sql
1968    }
1969
1970    pub fn build_sum(&self, field: &str) -> String {
1971        let table = self
1972            .table
1973            .clone()
1974            .unwrap_or_else(|| M::table_name().to_string());
1975
1976        let mut sql = format!(
1977            "SELECT SUM({}) as sum_val FROM {}",
1978            self.dialect.quote(field),
1979            self.dialect.quote(&table)
1980        );
1981        sql.push_str(&self.build_where_clause());
1982        sql
1983    }
1984
1985    pub fn build_avg(&self, field: &str) -> String {
1986        let table = self
1987            .table
1988            .clone()
1989            .unwrap_or_else(|| M::table_name().to_string());
1990
1991        let mut sql = format!(
1992            "SELECT AVG({}) as avg_val FROM {}",
1993            self.dialect.quote(field),
1994            self.dialect.quote(&table)
1995        );
1996        sql.push_str(&self.build_where_clause());
1997        sql
1998    }
1999
2000    /// 校验生成的 SELECT SQL 语句
2001    /// 检查 SQL 语法、JOIN 列名、表名合法性
2002    pub fn validate(&self) -> Result<(), Vec<sz_orm_sql_validator::SqlValidationError>> {
2003        let sql = self.build_select();
2004        let mut errors = Vec::new();
2005
2006        if let Err(e) = sz_orm_sql_validator::validate_select(&sql) {
2007            errors.push(e);
2008        }
2009
2010        // 校验 JOIN 子句产生的 SQL 是否合法
2011        if !self.joins.is_empty() {
2012            for join in &self.joins {
2013                match join {
2014                    JoinClause::Inner(_, left, right)
2015                    | JoinClause::Left(_, left, right)
2016                    | JoinClause::Right(_, left, right) => {
2017                        if let Err(e) = sz_orm_sql_validator::validate_column_name(left) {
2018                            errors.push(e);
2019                        }
2020                        if let Err(e) = sz_orm_sql_validator::validate_column_name(right) {
2021                            errors.push(e);
2022                        }
2023                    }
2024                    _ => {}
2025                }
2026            }
2027        }
2028
2029        // 校验表名合法性
2030        let table = self
2031            .table
2032            .clone()
2033            .unwrap_or_else(|| M::table_name().to_string());
2034        if let Err(e) = sz_orm_sql_validator::validate_table_name(&table) {
2035            errors.push(e);
2036        }
2037
2038        if errors.is_empty() {
2039            Ok(())
2040        } else {
2041            Err(errors)
2042        }
2043    }
2044
2045    /// 校验生成的 INSERT SQL 语句
2046    /// 含空数据检测（EmptyInsertData 错误）
2047    pub fn validate_insert(
2048        &self,
2049        data: &std::collections::HashMap<String, Value>,
2050    ) -> Result<(), Vec<sz_orm_sql_validator::SqlValidationError>> {
2051        let sql = self.build_insert(data);
2052        let mut errors = Vec::new();
2053
2054        if sql.is_empty() {
2055            errors.push(sz_orm_sql_validator::SqlValidationError::EmptyInsertData);
2056            return Err(errors);
2057        }
2058
2059        if let Err(e) = sz_orm_sql_validator::validate_insert(&sql) {
2060            errors.push(e);
2061        }
2062
2063        if errors.is_empty() {
2064            Ok(())
2065        } else {
2066            Err(errors)
2067        }
2068    }
2069
2070    /// 校验生成的 UPDATE SQL 语句
2071    /// 含空数据检测（EmptyUpdateData 错误）
2072    pub fn validate_update(
2073        &self,
2074        data: &std::collections::HashMap<String, Value>,
2075    ) -> Result<(), Vec<sz_orm_sql_validator::SqlValidationError>> {
2076        let sql = self.build_update(data);
2077        let mut errors = Vec::new();
2078
2079        if sql.is_empty() {
2080            errors.push(sz_orm_sql_validator::SqlValidationError::EmptyUpdateData);
2081            return Err(errors);
2082        }
2083
2084        if let Err(e) = sz_orm_sql_validator::validate_update(&sql) {
2085            errors.push(e);
2086        }
2087
2088        if errors.is_empty() {
2089            Ok(())
2090        } else {
2091            Err(errors)
2092        }
2093    }
2094
2095    /// 校验生成的 DELETE SQL 语句
2096    pub fn validate_delete(&self) -> Result<(), Vec<sz_orm_sql_validator::SqlValidationError>> {
2097        let sql = self.build_delete();
2098        let mut errors = Vec::new();
2099
2100        if let Err(e) = sz_orm_sql_validator::validate_delete(&sql) {
2101            errors.push(e);
2102        }
2103
2104        if errors.is_empty() {
2105            Ok(())
2106        } else {
2107            Err(errors)
2108        }
2109    }
2110}
2111
2112impl<M: Model> fmt::Debug for QueryBuilder<M> {
2113    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
2114        f.debug_struct("QueryBuilder")
2115            .field("table", &self.table)
2116            .field("select_columns", &self.select_columns)
2117            .field("where_conditions", &self.where_conditions.len())
2118            .field("limit", &self.limit_value)
2119            .finish()
2120    }
2121}
2122
2123#[cfg(test)]
2124#[allow(deprecated)]
2125mod tests {
2126    use super::*;
2127    use crate::db_type::DbType;
2128    use crate::dialect::get_dialect;
2129
2130    struct TestModel;
2131    impl Model for TestModel {
2132        type PrimaryKey = i64;
2133
2134        fn table_name() -> &'static str {
2135            "test_models"
2136        }
2137
2138        fn pk(&self) -> Self::PrimaryKey {
2139            1
2140        }
2141
2142        fn set_pk(&mut self, _pk: Self::PrimaryKey) {}
2143    }
2144
2145    #[test]
2146    fn test_query_builder_select() -> Result<(), crate::DbError> {
2147        let dialect = get_dialect(DbType::MySQL)?;
2148        let builder = QueryBuilder::<TestModel>::new(dialect);
2149
2150        let sql = builder
2151            .table("users")
2152            .select(vec!["id", "name"])
2153            .build_select();
2154        assert!(sql.contains("SELECT id, name FROM"));
2155        assert!(sql.contains("`users`"));
2156        Ok(())
2157    }
2158
2159    #[test]
2160    fn test_query_builder_where() -> Result<(), crate::DbError> {
2161        let dialect = get_dialect(DbType::MySQL)?;
2162        let builder = QueryBuilder::<TestModel>::new(dialect);
2163
2164        let sql = builder
2165            .table("users")
2166            .where_eq("status", crate::value::Value::String("active".into()))
2167            .where_gt("age", crate::value::Value::I64(18))
2168            .build_select();
2169
2170        assert!(sql.contains("WHERE"));
2171        assert!(sql.contains("`status` = 'active'"));
2172        assert!(sql.contains("`age` > 18"));
2173        Ok(())
2174    }
2175
2176    #[test]
2177    fn test_query_builder_order_by() -> Result<(), crate::DbError> {
2178        let dialect = get_dialect(DbType::MySQL)?;
2179        let builder = QueryBuilder::<TestModel>::new(dialect);
2180
2181        let sql = builder
2182            .table("users")
2183            .order_by("created_at")
2184            .order_desc("id")
2185            .build_select();
2186
2187        assert!(sql.contains("ORDER BY"));
2188        assert!(sql.contains("`created_at` ASC"));
2189        assert!(sql.contains("`id` DESC"));
2190        Ok(())
2191    }
2192
2193    #[test]
2194    fn test_query_builder_limit_offset() -> Result<(), crate::DbError> {
2195        let dialect = get_dialect(DbType::MySQL)?;
2196        let builder = QueryBuilder::<TestModel>::new(dialect);
2197
2198        let sql = builder.table("users").limit(10).offset(20).build_select();
2199
2200        assert!(sql.contains("LIMIT 10"));
2201        assert!(sql.contains("OFFSET 20"));
2202        Ok(())
2203    }
2204
2205    #[test]
2206    fn test_query_builder_page() -> Result<(), crate::DbError> {
2207        let dialect = get_dialect(DbType::MySQL)?;
2208        let builder = QueryBuilder::<TestModel>::new(dialect);
2209
2210        let sql = builder.table("users").page(3, 20).build_select();
2211
2212        assert!(sql.contains("LIMIT 20"));
2213        assert!(sql.contains("OFFSET 40"));
2214        Ok(())
2215    }
2216
2217    #[test]
2218    fn test_query_builder_insert() -> Result<(), crate::DbError> {
2219        let dialect = get_dialect(DbType::MySQL)?;
2220        let builder = QueryBuilder::<TestModel>::new(dialect);
2221
2222        let mut data = std::collections::HashMap::new();
2223        data.insert("name".to_string(), Value::String("test".to_string()));
2224        data.insert("age".to_string(), Value::I64(25));
2225
2226        let sql = builder.table("users").build_insert(&data);
2227
2228        assert!(sql.contains("INSERT INTO"));
2229        assert!(sql.contains("`name`"));
2230        assert!(sql.contains("'test'"));
2231        Ok(())
2232    }
2233
2234    #[test]
2235    fn test_query_builder_update() -> Result<(), crate::DbError> {
2236        let dialect = get_dialect(DbType::MySQL)?;
2237        let builder = QueryBuilder::<TestModel>::new(dialect);
2238
2239        let mut data = std::collections::HashMap::new();
2240        data.insert("name".to_string(), Value::String("updated".to_string()));
2241
2242        let sql = builder
2243            .table("users")
2244            .where_eq("id", Value::I64(1))
2245            .build_update(&data);
2246
2247        assert!(sql.contains("UPDATE"));
2248        assert!(sql.contains("`name` = 'updated'"));
2249        assert!(sql.contains("WHERE"));
2250        Ok(())
2251    }
2252
2253    #[test]
2254    fn test_query_builder_delete() -> Result<(), crate::DbError> {
2255        let dialect = get_dialect(DbType::MySQL)?;
2256        let builder = QueryBuilder::<TestModel>::new(dialect);
2257
2258        let sql = builder
2259            .table("users")
2260            .where_eq("id", Value::I64(1))
2261            .build_delete();
2262
2263        assert!(sql.contains("DELETE FROM"));
2264        assert!(sql.contains("WHERE"));
2265        Ok(())
2266    }
2267
2268    #[test]
2269    fn test_query_builder_count() -> Result<(), crate::DbError> {
2270        let dialect = get_dialect(DbType::MySQL)?;
2271        let builder = QueryBuilder::<TestModel>::new(dialect);
2272
2273        let sql = builder.table("users").build_count();
2274
2275        assert!(sql.contains("SELECT COUNT(*)"));
2276        assert!(sql.contains("FROM"));
2277        Ok(())
2278    }
2279
2280    #[test]
2281    fn test_query_builder_where_in() -> Result<(), crate::DbError> {
2282        let dialect = get_dialect(DbType::MySQL)?;
2283        let builder = QueryBuilder::<TestModel>::new(dialect);
2284
2285        let sql = builder
2286            .table("users")
2287            .where_in("id", vec![Value::I64(1), Value::I64(2), Value::I64(3)])
2288            .build_select();
2289
2290        assert!(sql.contains("IN ("));
2291        Ok(())
2292    }
2293
2294    #[test]
2295    fn test_query_builder_where_between() -> Result<(), crate::DbError> {
2296        let dialect = get_dialect(DbType::MySQL)?;
2297        let builder = QueryBuilder::<TestModel>::new(dialect);
2298
2299        let sql = builder
2300            .table("users")
2301            .where_between("age", Value::I64(18), Value::I64(30))
2302            .build_select();
2303
2304        assert!(sql.contains("BETWEEN"));
2305        Ok(())
2306    }
2307
2308    #[test]
2309    fn test_query_builder_where_null() -> Result<(), crate::DbError> {
2310        let dialect = get_dialect(DbType::MySQL)?;
2311        let builder = QueryBuilder::<TestModel>::new(dialect);
2312
2313        let sql = builder
2314            .table("users")
2315            .where_null("deleted_at")
2316            .build_select();
2317
2318        assert!(sql.contains("IS NULL"));
2319        Ok(())
2320    }
2321
2322    #[test]
2323    fn test_query_builder_join() -> Result<(), crate::DbError> {
2324        let dialect = get_dialect(DbType::MySQL)?;
2325        let builder = QueryBuilder::<TestModel>::new(dialect);
2326
2327        let sql = builder
2328            .table("users")
2329            .join_inner("posts", "users.id", "posts.user_id")
2330            .build_select();
2331
2332        assert!(sql.contains("INNER JOIN"));
2333        assert!(sql.contains("`posts`"));
2334        Ok(())
2335    }
2336
2337    #[test]
2338    fn test_query_builder_group_by() -> Result<(), crate::DbError> {
2339        let dialect = get_dialect(DbType::MySQL)?;
2340        let builder = QueryBuilder::<TestModel>::new(dialect);
2341
2342        let sql = builder.table("users").group_by("status").build_select();
2343
2344        assert!(sql.contains("GROUP BY"));
2345        assert!(sql.contains("`status`"));
2346        Ok(())
2347    }
2348
2349    #[test]
2350    fn test_query_builder_max() -> Result<(), crate::DbError> {
2351        let dialect = get_dialect(DbType::MySQL)?;
2352        let builder = QueryBuilder::<TestModel>::new(dialect);
2353
2354        let sql = builder.table("users").build_max("score");
2355
2356        assert!(sql.contains("MAX("));
2357        assert!(sql.contains("`score`"));
2358        Ok(())
2359    }
2360
2361    #[test]
2362    fn test_query_builder_min() -> Result<(), crate::DbError> {
2363        let dialect = get_dialect(DbType::MySQL)?;
2364        let builder = QueryBuilder::<TestModel>::new(dialect);
2365
2366        let sql = builder.table("users").build_min("price");
2367
2368        assert!(sql.contains("MIN("));
2369        assert!(sql.contains("`price`"));
2370        Ok(())
2371    }
2372
2373    #[test]
2374    fn test_query_builder_sum() -> Result<(), crate::DbError> {
2375        let dialect = get_dialect(DbType::MySQL)?;
2376        let builder = QueryBuilder::<TestModel>::new(dialect);
2377
2378        let sql = builder.table("orders").build_sum("amount");
2379
2380        assert!(sql.contains("SUM("));
2381        assert!(sql.contains("`amount`"));
2382        Ok(())
2383    }
2384
2385    #[test]
2386    fn test_query_builder_avg() -> Result<(), crate::DbError> {
2387        let dialect = get_dialect(DbType::MySQL)?;
2388        let builder = QueryBuilder::<TestModel>::new(dialect);
2389
2390        let sql = builder.table("scores").build_avg("value");
2391
2392        assert!(sql.contains("AVG("));
2393        assert!(sql.contains("`value`"));
2394        Ok(())
2395    }
2396
2397    #[test]
2398    fn test_validator_select() -> Result<(), crate::DbError> {
2399        let dialect = get_dialect(DbType::MySQL)?;
2400        let builder = QueryBuilder::<TestModel>::new(dialect);
2401
2402        let result = builder.table("users").select(vec!["id", "name"]).validate();
2403        assert!(result.is_ok());
2404        Ok(())
2405    }
2406
2407    #[test]
2408    fn test_validator_select_with_join() -> Result<(), crate::DbError> {
2409        let dialect = get_dialect(DbType::MySQL)?;
2410        let builder = QueryBuilder::<TestModel>::new(dialect);
2411
2412        let result = builder
2413            .table("users")
2414            .join_inner("posts", "users.id", "posts.user_id")
2415            .validate();
2416        assert!(result.is_ok());
2417        Ok(())
2418    }
2419
2420    #[test]
2421    fn test_validator_insert() -> Result<(), crate::DbError> {
2422        let dialect = get_dialect(DbType::MySQL)?;
2423        let builder = QueryBuilder::<TestModel>::new(dialect);
2424
2425        let mut data = std::collections::HashMap::new();
2426        data.insert("name".to_string(), Value::String("test".to_string()));
2427
2428        let result = builder.table("users").validate_insert(&data);
2429        assert!(result.is_ok());
2430        Ok(())
2431    }
2432
2433    #[test]
2434    fn test_validator_insert_empty_data() -> Result<(), crate::DbError> {
2435        let dialect = get_dialect(DbType::MySQL)?;
2436        let builder = QueryBuilder::<TestModel>::new(dialect);
2437
2438        let data = std::collections::HashMap::new();
2439        let result = builder.table("users").validate_insert(&data);
2440        assert!(result.is_err());
2441        Ok(())
2442    }
2443
2444    #[test]
2445    fn test_validator_update() -> Result<(), crate::DbError> {
2446        let dialect = get_dialect(DbType::MySQL)?;
2447        let builder = QueryBuilder::<TestModel>::new(dialect);
2448
2449        let mut data = std::collections::HashMap::new();
2450        data.insert("name".to_string(), Value::String("updated".to_string()));
2451
2452        let result = builder.table("users").validate_update(&data);
2453        assert!(result.is_ok());
2454        Ok(())
2455    }
2456
2457    #[test]
2458    fn test_validator_update_empty_data() -> Result<(), crate::DbError> {
2459        let dialect = get_dialect(DbType::MySQL)?;
2460        let builder = QueryBuilder::<TestModel>::new(dialect);
2461
2462        let data = std::collections::HashMap::new();
2463        let result = builder.table("users").validate_update(&data);
2464        assert!(result.is_err());
2465        Ok(())
2466    }
2467
2468    #[test]
2469    fn test_validator_delete() -> Result<(), crate::DbError> {
2470        let dialect = get_dialect(DbType::MySQL)?;
2471        let builder = QueryBuilder::<TestModel>::new(dialect);
2472
2473        let result = builder
2474            .table("users")
2475            .where_eq("id", Value::I64(1))
2476            .validate_delete();
2477        assert!(result.is_ok());
2478        Ok(())
2479    }
2480
2481    #[test]
2482    fn test_validator_delete_no_where() -> Result<(), crate::DbError> {
2483        let dialect = get_dialect(DbType::MySQL)?;
2484        let builder = QueryBuilder::<TestModel>::new(dialect);
2485
2486        // DELETE without WHERE still produces valid SQL (just no filter)
2487        let result = builder.table("users").validate_delete();
2488        assert!(result.is_ok());
2489        Ok(())
2490    }
2491
2492    // ==================== M-3 select_quoted 测试 ====================
2493
2494    #[test]
2495    fn test_m3_select_quoted_valid_columns() -> Result<(), crate::DbError> {
2496        let dialect = get_dialect(DbType::MySQL)?;
2497        let builder = QueryBuilder::<TestModel>::new(dialect);
2498        let builder = builder.table("users").select_quoted(vec!["id", "name"])?;
2499        let sql = builder.build_select();
2500        // 应自动 quote 列名
2501        assert!(sql.contains("SELECT `id`, `name` FROM"));
2502        assert!(sql.contains("`users`"));
2503        Ok(())
2504    }
2505
2506    #[test]
2507    fn test_m3_select_quoted_rejects_sql_injection() -> Result<(), crate::DbError> {
2508        let dialect = get_dialect(DbType::MySQL)?;
2509        let builder = QueryBuilder::<TestModel>::new(dialect);
2510
2511        // SQL 注入尝试：分号 + DROP TABLE
2512        let result = builder
2513            .table("users")
2514            .select_quoted(vec!["id; DROP TABLE users"]);
2515        assert!(result.is_err());
2516
2517        // 含引号
2518        let dialect = get_dialect(DbType::MySQL)?;
2519        let builder = QueryBuilder::<TestModel>::new(dialect);
2520        let result = builder.table("users").select_quoted(vec!["name'"]);
2521        assert!(result.is_err());
2522
2523        // 数字开头
2524        let dialect = get_dialect(DbType::MySQL)?;
2525        let builder = QueryBuilder::<TestModel>::new(dialect);
2526        let result = builder.table("users").select_quoted(vec!["1col"]);
2527        assert!(result.is_err());
2528
2529        // 含空格
2530        let dialect = get_dialect(DbType::MySQL)?;
2531        let builder = QueryBuilder::<TestModel>::new(dialect);
2532        let result = builder.table("users").select_quoted(vec!["col name"]);
2533        assert!(result.is_err());
2534        Ok(())
2535    }
2536
2537    #[test]
2538    fn test_m3_select_quoted_postgresql_dialect() -> Result<(), crate::DbError> {
2539        let dialect = get_dialect(DbType::PostgreSQL)?;
2540        let builder = QueryBuilder::<TestModel>::new(dialect);
2541        let builder = builder.table("users").select_quoted(vec!["id", "name"])?;
2542        let sql = builder.build_select();
2543        // PostgreSQL 使用双引号
2544        assert!(sql.contains("SELECT \"id\", \"name\" FROM"));
2545        assert!(sql.contains("\"users\""));
2546        Ok(())
2547    }
2548
2549    // ==================== P0-1 软删除集成行为测试 ====================
2550
2551    /// 软删除测试模型：实现 soft_delete_field() 返回 "deleted_at"
2552    struct SoftDeleteModel;
2553    impl Model for SoftDeleteModel {
2554        type PrimaryKey = i64;
2555
2556        fn table_name() -> &'static str {
2557            "soft_users"
2558        }
2559
2560        fn pk(&self) -> Self::PrimaryKey {
2561            1
2562        }
2563
2564        fn set_pk(&mut self, _pk: Self::PrimaryKey) {}
2565
2566        fn soft_delete_field() -> Option<&'static str> {
2567            Some("deleted_at")
2568        }
2569    }
2570
2571    /// 行为级测试 L3-1：软删除模型 build_select 自动追加 `WHERE deleted_at IS NULL`
2572    ///
2573    /// 用户视角：查询软删除模型时，自动过滤已删除记录，无需手动写条件。
2574    #[test]
2575    fn test_p01_soft_delete_select_auto_filter() -> Result<(), crate::DbError> {
2576        let dialect = get_dialect(DbType::MySQL)?;
2577        let builder = QueryBuilder::<SoftDeleteModel>::new(dialect);
2578        let sql = builder.table("soft_users").build_select();
2579        // 必须自动追加软删除过滤
2580        assert!(
2581            sql.contains("`deleted_at` IS NULL"),
2582            "软删除模型 SELECT 必须自动追加 `deleted_at` IS NULL，实际: {}",
2583            sql
2584        );
2585        Ok(())
2586    }
2587
2588    /// 行为级测试 L3-2：软删除模型 + 用户 WHERE 条件，软删除条件以 AND 追加
2589    #[test]
2590    fn test_p01_soft_delete_select_with_user_where() -> Result<(), crate::DbError> {
2591        let dialect = get_dialect(DbType::MySQL)?;
2592        let sql = QueryBuilder::<SoftDeleteModel>::new(dialect)
2593            .table("soft_users")
2594            .where_eq("status", Value::String("active".into()))
2595            .build_select();
2596        // 用户条件 + 软删除条件 同时存在
2597        assert!(sql.contains("`status` = "), "用户条件应保留: {}", sql);
2598        assert!(
2599            sql.contains("`deleted_at` IS NULL"),
2600            "软删除条件应自动追加: {}",
2601            sql
2602        );
2603        Ok(())
2604    }
2605
2606    /// 行为级测试 L3-3：without_soft_delete() 临时禁用软删除过滤
2607    ///
2608    /// 用户视角：管理员查询已删除记录时，可禁用自动过滤。
2609    #[test]
2610    fn test_p01_soft_delete_without_soft_delete() -> Result<(), crate::DbError> {
2611        let dialect = get_dialect(DbType::MySQL)?;
2612        let sql = QueryBuilder::<SoftDeleteModel>::new(dialect)
2613            .table("soft_users")
2614            .without_soft_delete()
2615            .build_select();
2616        // 不应包含软删除过滤
2617        assert!(
2618            !sql.contains("`deleted_at` IS NULL"),
2619            "without_soft_delete 应禁用过滤，实际: {}",
2620            sql
2621        );
2622        // 也应无 WHERE 子句（因为用户未提供任何条件）
2623        assert!(
2624            !sql.contains("WHERE"),
2625            "无用户条件 + 禁用软删除应无 WHERE 子句: {}",
2626            sql
2627        );
2628        Ok(())
2629    }
2630
2631    /// 行为级测试 L3-4：软删除模型 build_delete 自动转为 UPDATE
2632    ///
2633    /// 用户视角：调用 delete 实际是软删除 UPDATE，不是物理 DELETE。
2634    #[test]
2635    fn test_p01_soft_delete_delete_becomes_update() -> Result<(), crate::DbError> {
2636        let dialect = get_dialect(DbType::MySQL)?;
2637        let sql = QueryBuilder::<SoftDeleteModel>::new(dialect)
2638            .table("soft_users")
2639            .where_eq("id", Value::I64(42))
2640            .build_delete();
2641        // 应生成 UPDATE 而非 DELETE
2642        assert!(
2643            sql.starts_with("UPDATE"),
2644            "软删除模型的 build_delete 应生成 UPDATE，实际: {}",
2645            sql
2646        );
2647        assert!(
2648            !sql.contains("DELETE FROM"),
2649            "不应生成 DELETE FROM: {}",
2650            sql
2651        );
2652        assert!(
2653            sql.contains("`deleted_at` = NOW()"),
2654            "应设置 deleted_at = NOW(): {}",
2655            sql
2656        );
2657        // 软删除条件应自动追加，防止更新已删除记录
2658        assert!(
2659            sql.contains("`deleted_at` IS NULL"),
2660            "软删除 UPDATE 应追加 deleted_at IS NULL 防止重复删除: {}",
2661            sql
2662        );
2663        Ok(())
2664    }
2665
2666    /// 行为级测试 L3-5：build_force_delete 物理删除，不追加软删除过滤
2667    ///
2668    /// 用户视角：管理员强制清除时使用 build_force_delete。
2669    #[test]
2670    fn test_p01_soft_delete_force_delete() -> Result<(), crate::DbError> {
2671        let dialect = get_dialect(DbType::MySQL)?;
2672        let sql = QueryBuilder::<SoftDeleteModel>::new(dialect)
2673            .table("soft_users")
2674            .where_eq("id", Value::I64(99))
2675            .build_force_delete();
2676        // 应生成 DELETE FROM
2677        assert!(
2678            sql.starts_with("DELETE FROM"),
2679            "build_force_delete 应生成 DELETE FROM，实际: {}",
2680            sql
2681        );
2682        // 不应追加软删除过滤
2683        assert!(
2684            !sql.contains("`deleted_at` IS NULL"),
2685            "物理删除不应追加软删除过滤: {}",
2686            sql
2687        );
2688        Ok(())
2689    }
2690
2691    /// 行为级测试 L3-6：build_select_with_params 自动追加软删除条件（参数化版本）
2692    #[test]
2693    fn test_p01_soft_delete_select_with_params() -> Result<(), crate::DbError> {
2694        let dialect = get_dialect(DbType::MySQL)?;
2695        let (sql, params) = QueryBuilder::<SoftDeleteModel>::new(dialect)
2696            .table("soft_users")
2697            .where_eq("id", Value::I64(1))
2698            .build_select_with_params();
2699        assert!(
2700            sql.contains("`deleted_at` IS NULL"),
2701            "参数化版本也应自动追加软删除: {}",
2702            sql
2703        );
2704        assert_eq!(params.len(), 1, "参数应为 1 个（用户 where_eq 的值）");
2705        assert_eq!(params[0], Value::I64(1));
2706        Ok(())
2707    }
2708
2709    /// 行为级测试 L3-7：build_delete_with_params 自动转为 UPDATE
2710    #[test]
2711    fn test_p01_soft_delete_delete_with_params_becomes_update() -> Result<(), crate::DbError> {
2712        let dialect = get_dialect(DbType::MySQL)?;
2713        let (sql, params) = QueryBuilder::<SoftDeleteModel>::new(dialect)
2714            .table("soft_users")
2715            .where_eq("id", Value::I64(7))
2716            .build_delete_with_params();
2717        assert!(sql.starts_with("UPDATE"), "应生成 UPDATE: {}", sql);
2718        assert!(
2719            sql.contains("`deleted_at` = NOW()"),
2720            "应设置 NOW(): {}",
2721            sql
2722        );
2723        assert_eq!(params.len(), 1, "参数应为 1 个（WHERE 的值）");
2724        Ok(())
2725    }
2726
2727    /// 行为级测试 L3-8：build_force_delete_with_params 物理删除（参数化版本）
2728    #[test]
2729    fn test_p01_soft_delete_force_delete_with_params() -> Result<(), crate::DbError> {
2730        let dialect = get_dialect(DbType::MySQL)?;
2731        let (sql, params) = QueryBuilder::<SoftDeleteModel>::new(dialect)
2732            .table("soft_users")
2733            .where_eq("id", Value::I64(11))
2734            .build_force_delete_with_params();
2735        assert!(sql.starts_with("DELETE FROM"), "应生成 DELETE: {}", sql);
2736        assert!(
2737            !sql.contains("`deleted_at` IS NULL"),
2738            "不应追加软删除过滤: {}",
2739            sql
2740        );
2741        assert_eq!(params.len(), 1);
2742        Ok(())
2743    }
2744
2745    /// 行为级测试 L3-9：非软删除模型 TestModel 不追加软删除条件
2746    ///
2747    /// 用户视角：未启用软删除的模型行为不变。
2748    #[test]
2749    fn test_p01_non_soft_delete_model_unchanged() -> Result<(), crate::DbError> {
2750        let dialect = get_dialect(DbType::MySQL)?;
2751        let sql = QueryBuilder::<TestModel>::new(dialect)
2752            .table("users")
2753            .where_eq("id", Value::I64(1))
2754            .build_select();
2755        assert!(
2756            !sql.contains("deleted_at"),
2757            "非软删除模型不应追加 deleted_at: {}",
2758            sql
2759        );
2760        // build_delete 仍生成 DELETE FROM
2761        let dialect = get_dialect(DbType::MySQL)?;
2762        let del_sql = QueryBuilder::<TestModel>::new(dialect)
2763            .table("users")
2764            .where_eq("id", Value::I64(1))
2765            .build_delete();
2766        assert!(
2767            del_sql.starts_with("DELETE FROM"),
2768            "非软删除模型 build_delete 应生成 DELETE: {}",
2769            del_sql
2770        );
2771        Ok(())
2772    }
2773
2774    /// 行为级测试 L3-10：build_count 也应自动追加软删除条件
2775    #[test]
2776    fn test_p01_soft_delete_count_auto_filter() -> Result<(), crate::DbError> {
2777        let dialect = get_dialect(DbType::MySQL)?;
2778        let sql = QueryBuilder::<SoftDeleteModel>::new(dialect)
2779            .table("soft_users")
2780            .build_count();
2781        assert!(
2782            sql.contains("`deleted_at` IS NULL"),
2783            "build_count 也应追加软删除过滤: {}",
2784            sql
2785        );
2786        Ok(())
2787    }
2788
2789    // ==================== P0-2 参数化查询注入防护测试 ====================
2790
2791    /// 行为级测试 L3-11：where_eq 使用 `?` 占位符，值收集到 params
2792    ///
2793    /// 用户视角：参数化查询杜绝 SQL 注入。
2794    #[test]
2795    fn test_p02_where_eq_uses_placeholder() -> Result<(), crate::DbError> {
2796        let dialect = get_dialect(DbType::MySQL)?;
2797        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2798            .table("users")
2799            .where_eq("name", Value::String("alice".into()))
2800            .build_select_with_params();
2801        // SQL 中应含 `?` 占位符，不应内嵌值
2802        assert!(sql.contains("`name` = ?"), "应使用 ? 占位符: {}", sql);
2803        assert!(!sql.contains("'alice'"), "不应内嵌值到 SQL: {}", sql);
2804        assert_eq!(params.len(), 1);
2805        assert_eq!(params[0], Value::String("alice".into()));
2806        Ok(())
2807    }
2808
2809    /// 行为级测试 L3-12：where_like 使用 `?` 占位符
2810    #[test]
2811    fn test_p02_where_like_uses_placeholder() -> Result<(), crate::DbError> {
2812        let dialect = get_dialect(DbType::MySQL)?;
2813        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2814            .table("users")
2815            .where_like("name", Value::String("%alice%".into()))
2816            .build_select_with_params();
2817        assert!(sql.contains("`name` LIKE ?"), "应使用 LIKE ?: {}", sql);
2818        assert!(!sql.contains("%alice%"), "不应内嵌 pattern: {}", sql);
2819        assert_eq!(params.len(), 1);
2820        Ok(())
2821    }
2822
2823    /// 行为级测试 L3-12a：where_ne 使用 `?` 占位符
2824    ///
2825    /// 验证 P0-2 参数化 API where_ne 生成 `field != ?` 且值不内嵌。
2826    #[test]
2827    fn test_p02_where_ne_uses_placeholder() -> Result<(), crate::DbError> {
2828        let dialect = get_dialect(DbType::MySQL)?;
2829        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2830            .table("users")
2831            .where_ne("status", Value::I64(0))
2832            .build_select_with_params();
2833        assert!(sql.contains("`status` != ?"), "应使用 != ?: {}", sql);
2834        assert!(!sql.contains("!= 0"), "不应内嵌值: {}", sql);
2835        assert_eq!(params.len(), 1);
2836        assert_eq!(params[0], Value::I64(0));
2837        Ok(())
2838    }
2839
2840    /// 行为级测试 L3-12b：where_ge 使用 `?` 占位符
2841    ///
2842    /// 验证 P0-2 参数化 API where_ge 生成 `field >= ?` 且值不内嵌。
2843    #[test]
2844    fn test_p02_where_ge_uses_placeholder() -> Result<(), crate::DbError> {
2845        let dialect = get_dialect(DbType::MySQL)?;
2846        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2847            .table("users")
2848            .where_ge("age", Value::I64(18))
2849            .build_select_with_params();
2850        assert!(sql.contains("`age` >= ?"), "应使用 >= ?: {}", sql);
2851        assert!(!sql.contains(">= 18"), "不应内嵌值: {}", sql);
2852        assert_eq!(params.len(), 1);
2853        assert_eq!(params[0], Value::I64(18));
2854        Ok(())
2855    }
2856
2857    /// 行为级测试 L3-12c：where_lt 使用 `?` 占位符
2858    ///
2859    /// 验证 P0-2 参数化 API where_lt 生成 `field < ?` 且值不内嵌。
2860    #[test]
2861    fn test_p02_where_lt_uses_placeholder() -> Result<(), crate::DbError> {
2862        let dialect = get_dialect(DbType::MySQL)?;
2863        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2864            .table("users")
2865            .where_lt("score", Value::F64(60.0))
2866            .build_select_with_params();
2867        assert!(sql.contains("`score` < ?"), "应使用 < ?: {}", sql);
2868        assert!(!sql.contains("< 60"), "不应内嵌值: {}", sql);
2869        assert_eq!(params.len(), 1);
2870        assert_eq!(params[0], Value::F64(60.0));
2871        Ok(())
2872    }
2873
2874    /// 行为级测试 L3-13：注入攻击防护 - 值含 SQL 关键字也不会被解释执行
2875    ///
2876    /// 用户视角：即使用户输入 `'; DROP TABLE users; --`，也不会造成注入。
2877    #[test]
2878    fn test_p02_injection_protection_drop_table() -> Result<(), crate::DbError> {
2879        let dialect = get_dialect(DbType::MySQL)?;
2880        let evil_input = "'; DROP TABLE users; --".to_string();
2881        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2882            .table("users")
2883            .where_eq("name", Value::String(evil_input.clone()))
2884            .build_select_with_params();
2885        // SQL 中不应出现 DROP TABLE
2886        assert!(!sql.contains("DROP TABLE"), "SQL 注入未防护: {}", sql);
2887        // 整个恶意字符串应作为单一参数传递
2888        assert_eq!(params.len(), 1);
2889        assert_eq!(params[0], Value::String(evil_input));
2890        // SQL 中只有 1 个 `?`
2891        assert_eq!(sql.matches('?').count(), 1);
2892        Ok(())
2893    }
2894
2895    /// 行为级测试 L3-14：注入攻击防护 - OR 1=1 经典攻击
2896    #[test]
2897    fn test_p02_injection_protection_or_one_equals_one() -> Result<(), crate::DbError> {
2898        let dialect = get_dialect(DbType::MySQL)?;
2899        let evil = "' OR '1'='1".to_string();
2900        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2901            .table("users")
2902            .where_eq("name", Value::String(evil.clone()))
2903            .build_select_with_params();
2904        assert!(!sql.contains("OR '1'='1'"), "OR 1=1 注入未防护: {}", sql);
2905        assert_eq!(params.len(), 1);
2906        assert_eq!(params[0], Value::String(evil));
2907        Ok(())
2908    }
2909
2910    /// 行为级测试 L3-15：多参数顺序正确（WHERE a = ? AND b = ?）
2911    #[test]
2912    fn test_p02_multiple_params_order() -> Result<(), crate::DbError> {
2913        let dialect = get_dialect(DbType::MySQL)?;
2914        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2915            .table("users")
2916            .where_eq("name", Value::String("alice".into()))
2917            .where_gt("age", Value::I64(18))
2918            .where_le("score", Value::F64(99.5))
2919            .build_select_with_params();
2920        assert_eq!(sql.matches('?').count(), 3, "应有 3 个占位符: {}", sql);
2921        assert_eq!(params.len(), 3);
2922        // 参数顺序应与 WHERE 子句出现顺序一致
2923        assert_eq!(params[0], Value::String("alice".into()));
2924        assert_eq!(params[1], Value::I64(18));
2925        assert_eq!(params[2], Value::F64(99.5));
2926        Ok(())
2927    }
2928
2929    /// 行为级测试 L3-16：where_in 参数化
2930    #[test]
2931    fn test_p02_where_in_uses_placeholders() -> Result<(), crate::DbError> {
2932        let dialect = get_dialect(DbType::MySQL)?;
2933        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2934            .table("users")
2935            .where_in("id", vec![Value::I64(1), Value::I64(2), Value::I64(3)])
2936            .build_select_with_params();
2937        assert!(
2938            sql.contains("`id` IN (?, ?, ?)"),
2939            "应使用 3 个占位符: {}",
2940            sql
2941        );
2942        assert_eq!(params.len(), 3);
2943        Ok(())
2944    }
2945
2946    /// 行为级测试 L3-17：where_between 参数化
2947    #[test]
2948    fn test_p02_where_between_uses_placeholders() -> Result<(), crate::DbError> {
2949        let dialect = get_dialect(DbType::MySQL)?;
2950        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2951            .table("users")
2952            .where_between("age", Value::I64(18), Value::I64(65))
2953            .build_select_with_params();
2954        assert!(
2955            sql.contains("`age` BETWEEN ? AND ?"),
2956            "应使用 2 个占位符: {}",
2957            sql
2958        );
2959        assert_eq!(params.len(), 2);
2960        assert_eq!(params[0], Value::I64(18));
2961        assert_eq!(params[1], Value::I64(65));
2962        Ok(())
2963    }
2964
2965    /// 行为级测试 L3-18：UPDATE 参数化版本 - SET 参数在前，WHERE 参数在后
2966    #[test]
2967    fn test_p02_update_params_order_set_before_where() -> Result<(), crate::DbError> {
2968        let dialect = get_dialect(DbType::MySQL)?;
2969        let mut data = std::collections::HashMap::new();
2970        data.insert("name".to_string(), Value::String("bob".into()));
2971        data.insert("age".to_string(), Value::I64(30));
2972        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
2973            .table("users")
2974            .where_eq("id", Value::I64(99))
2975            .build_update_with_params(&data);
2976        // SET 子句应有 2 个占位符，WHERE 子句 1 个，共 3 个
2977        assert_eq!(sql.matches('?').count(), 3, "应有 3 个 ?: {}", sql);
2978        assert_eq!(params.len(), 3);
2979        // 前 2 个为 SET 参数，最后 1 个为 WHERE 参数
2980        // 注意：HashMap 迭代顺序未指定，仅校验 WHERE 参数在最后
2981        assert_eq!(params[2], Value::I64(99));
2982        Ok(())
2983    }
2984
2985    /// 行为级测试 L3-19：build_where_clause（无参数版本）参数化条件内嵌值
2986    ///
2987    /// 验证无参数版本（build_select）对参数化条件的处理：直接内嵌转义值。
2988    #[test]
2989    fn test_p02_build_where_clause_inlines_value() -> Result<(), crate::DbError> {
2990        let dialect = get_dialect(DbType::MySQL)?;
2991        let sql = QueryBuilder::<TestModel>::new(dialect)
2992            .table("users")
2993            .where_eq("name", Value::String("alice".into()))
2994            .build_select();
2995        // 无参数版本应内嵌值（依赖 to_param_with_dialect 转义）
2996        assert!(
2997            sql.contains("`name` = "),
2998            "无参数版本应含 WHERE 条件: {}",
2999            sql
3000        );
3001        // 不应含 `?`（无参数版本）
3002        assert!(
3003            !sql.contains("`name` = ?"),
3004            "无参数版本不应使用 ? 占位符: {}",
3005            sql
3006        );
3007        Ok(())
3008    }
3009
3010    /// 行为级测试 L3-20：is_soft_delete_disabled 反映状态
3011    #[test]
3012    fn test_p01_is_soft_delete_disabled_flag() -> Result<(), crate::DbError> {
3013        let dialect = get_dialect(DbType::MySQL)?;
3014        let builder = QueryBuilder::<SoftDeleteModel>::new(dialect);
3015        assert!(!builder.is_soft_delete_disabled(), "默认应启用软删除过滤");
3016        let builder =
3017            QueryBuilder::<SoftDeleteModel>::new(get_dialect(DbType::MySQL)?).without_soft_delete();
3018        assert!(
3019            builder.is_soft_delete_disabled(),
3020            "without_soft_delete 后应反映禁用状态"
3021        );
3022        Ok(())
3023    }
3024
3025    // ==================== P0-3 多租户自动过滤行为测试 ====================
3026
3027    /// 多租户测试模型：实现 tenant_field() 返回 "tenant_id"
3028    struct TenantModel;
3029    impl Model for TenantModel {
3030        type PrimaryKey = i64;
3031
3032        fn table_name() -> &'static str {
3033            "orders"
3034        }
3035
3036        fn pk(&self) -> Self::PrimaryKey {
3037            1
3038        }
3039
3040        fn set_pk(&mut self, _pk: Self::PrimaryKey) {}
3041
3042        fn tenant_field() -> Option<&'static str> {
3043            Some("tenant_id")
3044        }
3045    }
3046
3047    /// 同时实现软删除 + 多租户的模型
3048    struct SoftDeleteAndTenantModel;
3049    impl Model for SoftDeleteAndTenantModel {
3050        type PrimaryKey = i64;
3051
3052        fn table_name() -> &'static str {
3053            "documents"
3054        }
3055
3056        fn pk(&self) -> Self::PrimaryKey {
3057            1
3058        }
3059
3060        fn set_pk(&mut self, _pk: Self::PrimaryKey) {}
3061
3062        fn soft_delete_field() -> Option<&'static str> {
3063            Some("deleted_at")
3064        }
3065
3066        fn tenant_field() -> Option<&'static str> {
3067            Some("tenant_id")
3068        }
3069    }
3070
3071    /// 行为级测试 L3-21：多租户模型 + with_tenant_id 自动追加 WHERE tenant_id = ?
3072    ///
3073    /// 用户视角：设置租户 ID 后，查询自动过滤当前租户数据。
3074    #[test]
3075    fn test_p03_tenant_select_auto_filter() -> Result<(), crate::DbError> {
3076        let dialect = get_dialect(DbType::MySQL)?;
3077        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3078            .table("orders")
3079            .with_tenant_id(42)
3080            .build_select_with_params();
3081        assert!(
3082            sql.contains("`tenant_id` = ?"),
3083            "多租户模型应自动追加 tenant_id = ?: {}",
3084            sql
3085        );
3086        assert_eq!(params.len(), 1, "应有 1 个参数（tenant_id 值）");
3087        assert_eq!(params[0], Value::I64(42));
3088        Ok(())
3089    }
3090
3091    /// 行为级测试 L3-22：多租户模型 + 用户 WHERE 条件 + 租户条件
3092    #[test]
3093    fn test_p03_tenant_select_with_user_where() -> Result<(), crate::DbError> {
3094        let dialect = get_dialect(DbType::MySQL)?;
3095        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3096            .table("orders")
3097            .with_tenant_id(7)
3098            .where_eq("status", Value::String("active".into()))
3099            .build_select_with_params();
3100        assert!(sql.contains("`status` = ?"), "用户条件应保留: {}", sql);
3101        assert!(
3102            sql.contains("`tenant_id` = ?"),
3103            "租户条件应自动追加: {}",
3104            sql
3105        );
3106        assert_eq!(params.len(), 2, "应有 2 个参数");
3107        // 第 1 个为用户 where_eq 的值，第 2 个为 tenant_id
3108        assert_eq!(params[0], Value::String("active".into()));
3109        assert_eq!(params[1], Value::I64(7));
3110        Ok(())
3111    }
3112
3113    /// 行为级测试 L3-23：without_tenant() 临时禁用租户过滤
3114    ///
3115    /// 用户视角：管理员跨租户查询时禁用自动过滤。
3116    #[test]
3117    fn test_p03_tenant_without_tenant() -> Result<(), crate::DbError> {
3118        let dialect = get_dialect(DbType::MySQL)?;
3119        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3120            .table("orders")
3121            .with_tenant_id(42)
3122            .without_tenant()
3123            .build_select_with_params();
3124        assert!(
3125            !sql.contains("`tenant_id` = ?"),
3126            "without_tenant 应禁用过滤: {}",
3127            sql
3128        );
3129        assert_eq!(params.len(), 0, "不应有租户参数");
3130        Ok(())
3131    }
3132
3133    /// 行为级测试 L3-24：多租户模型 build_delete 自动追加租户条件
3134    ///
3135    /// 用户视角：删除操作自动限定在当前租户，防止跨租户删除。
3136    #[test]
3137    fn test_p03_tenant_delete_auto_filter() -> Result<(), crate::DbError> {
3138        let dialect = get_dialect(DbType::MySQL)?;
3139        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3140            .table("orders")
3141            .with_tenant_id(99)
3142            .where_eq("id", Value::I64(1))
3143            .build_delete_with_params();
3144        assert!(
3145            sql.contains("`tenant_id` = ?"),
3146            "删除应自动追加租户条件: {}",
3147            sql
3148        );
3149        // 2 个参数：where_eq(id=1) + tenant_id=99
3150        assert_eq!(params.len(), 2);
3151        assert_eq!(params[0], Value::I64(1));
3152        assert_eq!(params[1], Value::I64(99));
3153        Ok(())
3154    }
3155
3156    /// 行为级测试 L3-25：多租户模型 build_update 自动追加租户条件
3157    #[test]
3158    fn test_p03_tenant_update_auto_filter() -> Result<(), crate::DbError> {
3159        let dialect = get_dialect(DbType::MySQL)?;
3160        let mut data = std::collections::HashMap::new();
3161        data.insert("status".to_string(), Value::String("shipped".into()));
3162        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3163            .table("orders")
3164            .with_tenant_id(5)
3165            .where_eq("id", Value::I64(10))
3166            .build_update_with_params(&data);
3167        assert!(
3168            sql.contains("`tenant_id` = ?"),
3169            "更新应自动追加租户条件: {}",
3170            sql
3171        );
3172        // 3 个参数：SET status + WHERE id + tenant_id
3173        assert_eq!(params.len(), 3);
3174        // 最后一个应为 tenant_id
3175        assert_eq!(params[2], Value::I64(5));
3176        Ok(())
3177    }
3178
3179    /// 行为级测试 L3-26：多租户模型 build_count 自动追加租户条件
3180    #[test]
3181    fn test_p03_tenant_count_auto_filter() -> Result<(), crate::DbError> {
3182        let dialect = get_dialect(DbType::MySQL)?;
3183        let sql = QueryBuilder::<TenantModel>::new(dialect)
3184            .table("orders")
3185            .with_tenant_id(42)
3186            .build_count();
3187        assert!(
3188            sql.contains("`tenant_id` = 42"),
3189            "build_count 应追加租户条件（无参数版本内嵌值）: {}",
3190            sql
3191        );
3192        Ok(())
3193    }
3194
3195    /// 行为级测试 L3-27：非多租户模型 TestModel 不追加租户条件
3196    ///
3197    /// 用户视角：未启用多租户的模型行为不变。
3198    #[test]
3199    fn test_p03_non_tenant_model_unchanged() -> Result<(), crate::DbError> {
3200        let dialect = get_dialect(DbType::MySQL)?;
3201        // 即使设置了 with_tenant_id，非多租户模型也不应追加
3202        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
3203            .table("users")
3204            .with_tenant_id(42)
3205            .build_select_with_params();
3206        assert!(
3207            !sql.contains("tenant_id"),
3208            "非多租户模型不应追加 tenant_id: {}",
3209            sql
3210        );
3211        assert_eq!(params.len(), 0);
3212        Ok(())
3213    }
3214
3215    /// 行为级测试 L3-28：多租户模型未设置 tenant_id 时不追加条件
3216    ///
3217    /// 用户视角：未设置租户 ID 时，查询不追加租户过滤（允许跨租户，需调用方保证安全）。
3218    #[test]
3219    fn test_p03_tenant_no_id_no_filter() -> Result<(), crate::DbError> {
3220        let dialect = get_dialect(DbType::MySQL)?;
3221        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3222            .table("orders")
3223            .build_select_with_params();
3224        assert!(
3225            !sql.contains("tenant_id"),
3226            "未设置 tenant_id 时不应追加过滤: {}",
3227            sql
3228        );
3229        assert_eq!(params.len(), 0);
3230        Ok(())
3231    }
3232
3233    /// 行为级测试 L3-29：软删除 + 多租户组合，两个条件同时追加
3234    ///
3235    /// 用户视角：同时启用软删除和多租户时，查询自动追加两个条件。
3236    #[test]
3237    fn test_p03_soft_delete_and_tenant_combined() -> Result<(), crate::DbError> {
3238        let dialect = get_dialect(DbType::MySQL)?;
3239        let (sql, params) = QueryBuilder::<SoftDeleteAndTenantModel>::new(dialect)
3240            .table("documents")
3241            .with_tenant_id(100)
3242            .where_eq("title", Value::String("report".into()))
3243            .build_select_with_params();
3244        // 软删除条件
3245        assert!(
3246            sql.contains("`deleted_at` IS NULL"),
3247            "应追加软删除条件: {}",
3248            sql
3249        );
3250        // 租户条件
3251        assert!(sql.contains("`tenant_id` = ?"), "应追加租户条件: {}", sql);
3252        // 用户条件
3253        assert!(sql.contains("`title` = ?"), "用户条件应保留: {}", sql);
3254        // 2 个参数：where_eq(title) + tenant_id（软删除 IS NULL 无参数）
3255        assert_eq!(params.len(), 2);
3256        assert_eq!(params[0], Value::String("report".into()));
3257        assert_eq!(params[1], Value::I64(100));
3258        Ok(())
3259    }
3260
3261    /// 行为级测试 L3-30：without_tenant + without_soft_delete 同时禁用
3262    #[test]
3263    fn test_p03_without_tenant_and_soft_delete() -> Result<(), crate::DbError> {
3264        let dialect = get_dialect(DbType::MySQL)?;
3265        let (sql, params) = QueryBuilder::<SoftDeleteAndTenantModel>::new(dialect)
3266            .table("documents")
3267            .with_tenant_id(100)
3268            .without_tenant()
3269            .without_soft_delete()
3270            .build_select_with_params();
3271        assert!(
3272            !sql.contains("`deleted_at` IS NULL"),
3273            "应禁用软删除: {}",
3274            sql
3275        );
3276        assert!(!sql.contains("`tenant_id` = ?"), "应禁用租户: {}", sql);
3277        assert_eq!(params.len(), 0);
3278        Ok(())
3279    }
3280
3281    /// 行为级测试 L3-31：is_tenant_disabled 反映状态
3282    #[test]
3283    fn test_p03_is_tenant_disabled_flag() -> Result<(), crate::DbError> {
3284        let dialect = get_dialect(DbType::MySQL)?;
3285        let builder = QueryBuilder::<TenantModel>::new(dialect);
3286        assert!(!builder.is_tenant_disabled(), "默认应启用租户过滤");
3287        let builder = QueryBuilder::<TenantModel>::new(get_dialect(DbType::MySQL)?)
3288            .with_tenant_id(1)
3289            .without_tenant();
3290        assert!(
3291            builder.is_tenant_disabled(),
3292            "without_tenant 后应反映禁用状态"
3293        );
3294        Ok(())
3295    }
3296
3297    /// 行为级测试 L3-32：build_force_delete 保留租户条件（防止跨租户物理删除）
3298    ///
3299    /// 用户视角：物理删除也应受租户隔离约束，跨租户操作需显式 without_tenant()。
3300    #[test]
3301    fn test_p03_tenant_force_delete_keeps_tenant_filter() -> Result<(), crate::DbError> {
3302        let dialect = get_dialect(DbType::MySQL)?;
3303        let (sql, params) = QueryBuilder::<TenantModel>::new(dialect)
3304            .table("orders")
3305            .with_tenant_id(42)
3306            .where_eq("id", Value::I64(999))
3307            .build_force_delete_with_params();
3308        // 物理删除不应追加软删除（TenantModel 未实现软删除，无影响）
3309        // 但应保留租户条件
3310        assert!(
3311            sql.contains("`tenant_id` = ?"),
3312            "物理删除应保留租户条件: {}",
3313            sql
3314        );
3315        assert_eq!(params.len(), 2);
3316        assert_eq!(params[0], Value::I64(999));
3317        assert_eq!(params[1], Value::I64(42));
3318        Ok(())
3319    }
3320
3321    // ---- TypedColumn 类型安全方法测试 ----
3322
3323    struct TcUsersTable;
3324    impl crate::typed::TypedTable for TcUsersTable {
3325        const NAME: &'static str = "users";
3326    }
3327    struct TcColId;
3328    impl crate::typed::TypedColumn for TcColId {
3329        const NAME: &'static str = "id";
3330        type Table = TcUsersTable;
3331        type RustType = i64;
3332        type SqlType = crate::typed_ast::Untyped;
3333    }
3334    struct TcColName;
3335    impl crate::typed::TypedColumn for TcColName {
3336        const NAME: &'static str = "name";
3337        type Table = TcUsersTable;
3338        type RustType = String;
3339        type SqlType = crate::typed_ast::Untyped;
3340    }
3341
3342    #[test]
3343    fn test_where_eq_typed() -> Result<(), crate::DbError> {
3344        let dialect = get_dialect(DbType::MySQL)?;
3345        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
3346            .where_eq_typed::<TcColId>(Value::I64(42))
3347            .build_select_with_params();
3348        assert!(sql.contains("`id` = ?"));
3349        assert_eq!(params[0], Value::I64(42));
3350        Ok(())
3351    }
3352
3353    #[test]
3354    fn test_order_by_typed() -> Result<(), crate::DbError> {
3355        let dialect = get_dialect(DbType::MySQL)?;
3356        let (sql, _) = QueryBuilder::<TestModel>::new(dialect)
3357            .order_by_typed::<TcColName>()
3358            .build_select_with_params();
3359        assert!(sql.contains("ORDER BY"));
3360        assert!(sql.contains("`name`"));
3361        Ok(())
3362    }
3363
3364    #[test]
3365    fn test_select_typed() -> Result<(), crate::DbError> {
3366        let dialect = get_dialect(DbType::MySQL)?;
3367        let (sql, _) = QueryBuilder::<TestModel>::new(dialect)
3368            .select_typed::<TcColId>()
3369            .select_typed::<TcColName>()
3370            .build_select_with_params();
3371        assert!(sql.contains("SELECT"));
3372        // select_columns are joined as-is (not quoted by build_select_with_params)
3373        assert!(sql.contains("id"));
3374        assert!(sql.contains("name"));
3375        Ok(())
3376    }
3377
3378    #[test]
3379    fn test_where_null_typed() -> Result<(), crate::DbError> {
3380        let dialect = get_dialect(DbType::MySQL)?;
3381        let (sql, _) = QueryBuilder::<TestModel>::new(dialect)
3382            .where_null_typed::<TcColName>()
3383            .build_select_with_params();
3384        assert!(sql.contains("`name` IS NULL"));
3385        Ok(())
3386    }
3387
3388    #[test]
3389    fn test_where_not_null_typed() -> Result<(), crate::DbError> {
3390        let dialect = get_dialect(DbType::MySQL)?;
3391        let (sql, _) = QueryBuilder::<TestModel>::new(dialect)
3392            .where_not_null_typed::<TcColName>()
3393            .build_select_with_params();
3394        assert!(sql.contains("`name` IS NOT NULL"));
3395        Ok(())
3396    }
3397
3398    #[test]
3399    fn test_group_by_typed() -> Result<(), crate::DbError> {
3400        let dialect = get_dialect(DbType::MySQL)?;
3401        let (sql, _) = QueryBuilder::<TestModel>::new(dialect)
3402            .group_by_typed::<TcColName>()
3403            .build_select_with_params();
3404        assert!(sql.contains("GROUP BY"));
3405        assert!(sql.contains("`name`"));
3406        Ok(())
3407    }
3408
3409    #[test]
3410    fn test_where_gt_typed() -> Result<(), crate::DbError> {
3411        let dialect = get_dialect(DbType::MySQL)?;
3412        let (sql, params) = QueryBuilder::<TestModel>::new(dialect)
3413            .where_gt_typed::<TcColId>(Value::I64(10))
3414            .build_select_with_params();
3415        assert!(sql.contains("`id` > ?"));
3416        assert_eq!(params[0], Value::I64(10));
3417        Ok(())
3418    }
3419}