nusadb 0.1.0

//! A lightweight, native query-builder ORM over [`Connection`]. It emits NusaDB's own SQL
//! (double-quoted identifiers, `$n` parameters, constant `LIMIT`/`OFFSET`, `RETURNING *`) and decodes
//! rows into user types via [`FromRow`]. [`Select`] covers the SELECT surface: projection
//! (`columns`/`select_raw`/`distinct`), joins (`inner_join`/`left_join`/`right_join`/`full_join`/
//! `cross_join`), predicates (`filter`/`gt`/`gte`/`lt`/`lte`/`ne`/`like`/`ilike`/`between`/`is_null`/
//! `is_not_null`/`where_in`/`where_not_in`/`where_raw`), `group_by` + `having`, `order_by`,
//! `limit`/`offset`, set operations (`union`/`union_all`/`intersect`/`except`), and aggregate
//! terminals (`count`/`sum`/`avg`/`min`/`max`). It is a thin convenience layer — drop to raw SQL any
//! time with [`Connection::query`] / [`Connection::query_params`].

use crate::connection::{Connection, QueryResult, Row};
use crate::error::Result;
use crate::value::{ToSql, Value};

/// Convert common Rust values into a [`Value`] for the builders' parameter lists.
macro_rules! value_from {
    ($($t:ty => $variant:expr),* $(,)?) => {$(
        impl From<$t> for Value {
            fn from(v: $t) -> Self { $variant(v) }
        }
    )*};
}
value_from! {
    bool => Value::Bool,
    i64 => Value::Int,
    f64 => Value::Float,
    String => Value::Text,
}
impl From<i32> for Value {
    fn from(v: i32) -> Self {
        Self::Int(i64::from(v))
    }
}
impl From<&str> for Value {
    fn from(v: &str) -> Self {
        Self::Text(v.to_owned())
    }
}
impl<T: Into<Value>> From<Option<T>> for Value {
    fn from(v: Option<T>) -> Self {
        v.map_or(Self::Null, Into::into)
    }
}

/// Map a result [`Row`] into a Rust value. Implement this for your structs (or use
/// [`QueryResult`]/[`Row`] directly).
pub trait FromRow: Sized {
    /// Build `Self` from a row, or fail with a conversion error.
    fn from_row(row: &Row) -> Result<Self>;
}

/// Quote an SQL identifier (double-quote, doubling any embedded quote).
#[must_use]
pub fn quote_ident(name: &str) -> String {
    format!("\"{}\"", name.replace('"', "\"\""))
}

/// Coerce a scalar [`Value`] to `f64` for the numeric aggregate terminals — `avg` may come back as
/// `NUMERIC` (a string-backed value) rather than a native `FLOAT`, so fall back to parsing its text.
fn value_to_f64(v: &Value) -> Option<f64> {
    match v {
        Value::Float(n) => Some(*n),
        Value::Int(n) => Some(*n as f64),
        _ => v.as_str().and_then(|s| s.parse::<f64>().ok()),
    }
}

/// Shift every `$n` placeholder in `sql` up by `offset`, so a set-operation operand's parameters
/// can follow the outer query's in the combined statement.
fn shift_placeholders(sql: &str, offset: usize) -> String {
    if offset == 0 {
        return sql.to_owned();
    }
    let mut out = String::with_capacity(sql.len());
    let mut chars = sql.chars().peekable();
    while let Some(c) = chars.next() {
        if c == '$' && chars.peek().is_some_and(char::is_ascii_digit) {
            let mut num = String::new();
            while let Some(&d) = chars.peek() {
                if d.is_ascii_digit() {
                    num.push(d);
                    chars.next();
                } else {
                    break;
                }
            }
            let n: usize = num.parse().unwrap_or(0);
            out.push('$');
            out.push_str(&(n + offset).to_string());
        } else {
            out.push(c);
        }
    }
    out
}

fn run(conn: &mut Connection, sql: &str, params: &[Value]) -> Result<QueryResult> {
    if params.is_empty() {
        conn.query(sql)
    } else {
        let refs: Vec<&dyn ToSql> = params.iter().map(|v| v as &dyn ToSql).collect();
        conn.query_params(sql, &refs)
    }
}

/// `SELECT` builder.
pub struct Select {
    table: String,
    columns: Vec<String>,
    distinct: bool,
    joins: Vec<String>,
    wheres: Vec<String>,
    params: Vec<Value>,
    group_by: Vec<String>,
    having: Vec<String>,
    set_ops: Vec<(&'static str, Box<Select>)>,
    order: Vec<String>,
    limit: Option<i64>,
    offset: Option<i64>,
}

impl Select {
    /// Start a `SELECT * FROM table`.
    #[must_use]
    pub fn from(table: &str) -> Self {
        Self {
            table: table.to_owned(),
            columns: Vec::new(),
            distinct: false,
            joins: Vec::new(),
            wheres: Vec::new(),
            params: Vec::new(),
            group_by: Vec::new(),
            having: Vec::new(),
            set_ops: Vec::new(),
            order: Vec::new(),
            limit: None,
            offset: None,
        }
    }

    /// Restrict the projection to these columns (default `*`). Each name is quoted as an identifier.
    #[must_use]
    pub fn columns(mut self, cols: &[&str]) -> Self {
        self.columns = cols.iter().map(|c| quote_ident(c)).collect();
        self
    }

    /// Project raw SQL expressions verbatim (no quoting) — for aggregates and computed columns under
    /// `GROUP BY`, e.g. `.select_raw(&["category", "count(*) AS n"])`.
    #[must_use]
    pub fn select_raw(mut self, exprs: &[&str]) -> Self {
        self.columns = exprs.iter().map(|e| (*e).to_owned()).collect();
        self
    }

    /// Emit `SELECT DISTINCT` so duplicate result rows are collapsed.
    #[must_use]
    pub fn distinct(mut self) -> Self {
        self.distinct = true;
        self
    }

    /// Add an `INNER JOIN <table> ON <on>`. The `on` fragment is raw SQL — reference columns with
    /// table-qualified, quoted identifiers (e.g. `"orders"."user_id" = "users"."id"`) to avoid
    /// ambiguity, and pair with [`Select::columns`] to project specific columns when both sides
    /// share a name.
    #[must_use]
    pub fn inner_join(self, table: &str, on: &str) -> Self {
        self.join("INNER", table, on)
    }

    /// Add a `LEFT JOIN <table> ON <on>` (see [`Select::inner_join`] for the `on` form).
    #[must_use]
    pub fn left_join(self, table: &str, on: &str) -> Self {
        self.join("LEFT", table, on)
    }

    /// Add a `RIGHT JOIN <table> ON <on>` (see [`Select::inner_join`] for the `on` form).
    #[must_use]
    pub fn right_join(self, table: &str, on: &str) -> Self {
        self.join("RIGHT", table, on)
    }

    /// Add a `FULL JOIN <table> ON <on>` (see [`Select::inner_join`] for the `on` form).
    #[must_use]
    pub fn full_join(self, table: &str, on: &str) -> Self {
        self.join("FULL", table, on)
    }

    /// Add a `CROSS JOIN <table>` (cartesian product; no `ON` clause).
    #[must_use]
    pub fn cross_join(mut self, table: &str) -> Self {
        self.joins
            .push(format!("CROSS JOIN {}", quote_ident(table)));
        self
    }

    /// Shared renderer for the keyed join kinds (`INNER`/`LEFT`/`RIGHT`/`FULL`).
    #[must_use]
    fn join(mut self, kind: &str, table: &str, on: &str) -> Self {
        self.joins
            .push(format!("{kind} JOIN {} ON {on}", quote_ident(table)));
        self
    }

    /// Add a `column = value` predicate (ANDed).
    #[must_use]
    pub fn filter(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.params.push(value.into());
        self.wheres
            .push(format!("{} = ${}", quote_ident(column), self.params.len()));
        self
    }

    /// Add a raw predicate fragment with its own `$n`-numbered params appended after existing ones.
    /// You are responsible for numbering placeholders to follow the current parameter count.
    #[must_use]
    pub fn where_raw(mut self, fragment: &str, params: &[Value]) -> Self {
        self.wheres.push(fragment.to_owned());
        self.params.extend_from_slice(params);
        self
    }

    /// Add a `column <op> value` predicate (ANDed), binding `value` as a parameter. The shared
    /// engine of the comparison helpers ([`Select::gt`], [`Select::lte`], …).
    #[must_use]
    fn cmp(mut self, column: &str, op: &str, value: impl Into<Value>) -> Self {
        self.params.push(value.into());
        self.wheres.push(format!(
            "{} {op} ${}",
            quote_ident(column),
            self.params.len()
        ));
        self
    }

    /// `column > value` (ANDed).
    #[must_use]
    pub fn gt(self, column: &str, value: impl Into<Value>) -> Self {
        self.cmp(column, ">", value)
    }

    /// `column >= value` (ANDed).
    #[must_use]
    pub fn gte(self, column: &str, value: impl Into<Value>) -> Self {
        self.cmp(column, ">=", value)
    }

    /// `column < value` (ANDed).
    #[must_use]
    pub fn lt(self, column: &str, value: impl Into<Value>) -> Self {
        self.cmp(column, "<", value)
    }

    /// `column <= value` (ANDed).
    #[must_use]
    pub fn lte(self, column: &str, value: impl Into<Value>) -> Self {
        self.cmp(column, "<=", value)
    }

    /// `column <> value` (not equal, ANDed).
    #[must_use]
    pub fn ne(self, column: &str, value: impl Into<Value>) -> Self {
        self.cmp(column, "<>", value)
    }

    /// `column LIKE pattern` (ANDed).
    #[must_use]
    pub fn like(self, column: &str, pattern: impl Into<Value>) -> Self {
        self.cmp(column, "LIKE", pattern)
    }

    /// `column ILIKE pattern` (case-insensitive `LIKE`, ANDed).
    #[must_use]
    pub fn ilike(self, column: &str, pattern: impl Into<Value>) -> Self {
        self.cmp(column, "ILIKE", pattern)
    }

    /// `column BETWEEN low AND high` (inclusive, ANDed).
    #[must_use]
    pub fn between(mut self, column: &str, low: impl Into<Value>, high: impl Into<Value>) -> Self {
        self.params.push(low.into());
        let lo = self.params.len();
        self.params.push(high.into());
        let hi = self.params.len();
        self.wheres
            .push(format!("{} BETWEEN ${lo} AND ${hi}", quote_ident(column)));
        self
    }

    /// `column IS NULL` (ANDed).
    #[must_use]
    pub fn is_null(mut self, column: &str) -> Self {
        self.wheres.push(format!("{} IS NULL", quote_ident(column)));
        self
    }

    /// `column IS NOT NULL` (ANDed).
    #[must_use]
    pub fn is_not_null(mut self, column: &str) -> Self {
        self.wheres
            .push(format!("{} IS NOT NULL", quote_ident(column)));
        self
    }

    /// `column IN (values…)` (ANDed). An empty list matches nothing (renders `FALSE`).
    #[must_use]
    pub fn where_in<V: Into<Value> + Clone>(self, column: &str, values: &[V]) -> Self {
        self.in_list(column, "IN", "FALSE", values)
    }

    /// `column NOT IN (values…)` (ANDed). An empty list excludes nothing (renders `TRUE`).
    #[must_use]
    pub fn where_not_in<V: Into<Value> + Clone>(self, column: &str, values: &[V]) -> Self {
        self.in_list(column, "NOT IN", "TRUE", values)
    }

    /// Shared engine for [`Select::where_in`] / [`Select::where_not_in`].
    #[must_use]
    fn in_list<V: Into<Value> + Clone>(
        mut self,
        column: &str,
        op: &str,
        empty: &str,
        values: &[V],
    ) -> Self {
        if values.is_empty() {
            self.wheres.push(empty.to_owned());
            return self;
        }
        let mut placeholders = Vec::with_capacity(values.len());
        for v in values {
            self.params.push(v.clone().into());
            placeholders.push(format!("${}", self.params.len()));
        }
        self.wheres.push(format!(
            "{} {op} ({})",
            quote_ident(column),
            placeholders.join(", ")
        ));
        self
    }

    /// Order by a column (ascending unless `desc`).
    #[must_use]
    pub fn order_by(mut self, column: &str, desc: bool) -> Self {
        self.order.push(format!(
            "{} {}",
            quote_ident(column),
            if desc { "DESC" } else { "ASC" }
        ));
        self
    }

    /// Limit the result (inlined as a constant — NusaDB requires a constant `LIMIT`).
    #[must_use]
    pub fn limit(mut self, n: i64) -> Self {
        self.limit = Some(n);
        self
    }

    /// Skip the first `n` rows (inlined as a constant — NusaDB requires a constant `OFFSET`).
    #[must_use]
    pub fn offset(mut self, n: i64) -> Self {
        self.offset = Some(n);
        self
    }

    /// Group rows by these columns (`GROUP BY`). Each name is quoted; pair with [`Select::select_raw`]
    /// to project the grouping columns and their aggregates.
    #[must_use]
    pub fn group_by(mut self, cols: &[&str]) -> Self {
        self.group_by = cols.iter().map(|c| quote_ident(c)).collect();
        self
    }

    /// Add a `HAVING` predicate over the grouped rows (ANDed). The `fragment` is raw SQL (it usually
    /// references an aggregate, e.g. `count(*) > $1`); its own params are appended after the
    /// `WHERE` params, so call this after the `WHERE` builders.
    #[must_use]
    pub fn having(mut self, fragment: &str, params: &[Value]) -> Self {
        self.having.push(fragment.to_owned());
        self.params.extend_from_slice(params);
        self
    }

    /// Combine with another query via `UNION` (duplicate rows removed). `ORDER BY`/`LIMIT`/`OFFSET`
    /// on `self` apply to the whole combined result.
    #[must_use]
    pub fn union(self, other: Select) -> Self {
        self.set_op("UNION", other)
    }

    /// Combine with another query via `UNION ALL` (duplicates kept).
    #[must_use]
    pub fn union_all(self, other: Select) -> Self {
        self.set_op("UNION ALL", other)
    }

    /// Rows present in both queries (`INTERSECT`).
    #[must_use]
    pub fn intersect(self, other: Select) -> Self {
        self.set_op("INTERSECT", other)
    }

    /// Rows in `self` but not in `other` (`EXCEPT`).
    #[must_use]
    pub fn except(self, other: Select) -> Self {
        self.set_op("EXCEPT", other)
    }

    /// Shared engine for the set-operation combinators.
    #[must_use]
    fn set_op(mut self, op: &'static str, other: Select) -> Self {
        self.set_ops.push((op, Box::new(other)));
        self
    }

    /// Append ` FROM <table>`, every join, and the `WHERE` clause to `sql` — the shared prefix of
    /// both [`Select::build`] and the aggregate terminals.
    fn push_from_where(&self, sql: &mut String) {
        sql.push_str(" FROM ");
        sql.push_str(&quote_ident(&self.table));
        for join in &self.joins {
            sql.push(' ');
            sql.push_str(join);
        }
        if !self.wheres.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&self.wheres.join(" AND "));
        }
    }

    /// Render the core `SELECT … FROM … WHERE … GROUP BY … HAVING …` (no set-ops / `ORDER BY` /
    /// `LIMIT` / `OFFSET`) — the operand form used inside a set operation.
    #[must_use]
    fn build_core(&self) -> (String, Vec<Value>) {
        let cols = if self.columns.is_empty() {
            "*".to_owned()
        } else {
            self.columns.join(", ")
        };
        let mut sql = if self.distinct {
            format!("SELECT DISTINCT {cols}")
        } else {
            format!("SELECT {cols}")
        };
        self.push_from_where(&mut sql);
        if !self.group_by.is_empty() {
            sql.push_str(" GROUP BY ");
            sql.push_str(&self.group_by.join(", "));
        }
        if !self.having.is_empty() {
            sql.push_str(" HAVING ");
            sql.push_str(&self.having.join(" AND "));
        }
        (sql, self.params.clone())
    }

    /// Render to `(sql, params)`.
    #[must_use]
    pub fn build(&self) -> (String, Vec<Value>) {
        let (mut sql, mut params) = self.build_core();
        for (op, rhs) in &self.set_ops {
            let (rhs_sql, rhs_params) = rhs.build_core();
            sql.push(' ');
            sql.push_str(op);
            sql.push(' ');
            sql.push_str(&shift_placeholders(&rhs_sql, params.len()));
            params.extend(rhs_params);
        }
        if !self.order.is_empty() {
            sql.push_str(" ORDER BY ");
            sql.push_str(&self.order.join(", "));
        }
        if let Some(n) = self.limit {
            sql.push_str(&format!(" LIMIT {n}"));
        }
        if let Some(n) = self.offset {
            sql.push_str(&format!(" OFFSET {n}"));
        }
        (sql, params)
    }

    /// Execute and return the raw [`QueryResult`].
    pub fn fetch(&self, conn: &mut Connection) -> Result<QueryResult> {
        let (sql, params) = self.build();
        run(conn, &sql, &params)
    }

    /// Execute and map every row into `T`.
    pub fn fetch_as<T: FromRow>(&self, conn: &mut Connection) -> Result<Vec<T>> {
        let result = self.fetch(conn)?;
        result.rows.iter().map(T::from_row).collect()
    }

    /// Execute, returning the first row mapped into `T`, or `None`.
    pub fn fetch_one<T: FromRow>(&self, conn: &mut Connection) -> Result<Option<T>> {
        let result = self.fetch(conn)?;
        result.rows.first().map(T::from_row).transpose()
    }

    /// Run an aggregate `SELECT <expr> FROM ...` honouring `WHERE`/joins (but not `ORDER BY`/
    /// `LIMIT`/`OFFSET`), returning the single scalar cell — `None` when the result is `NULL` (e.g.
    /// `min`/`sum` over an empty set).
    fn aggregate(&self, expr: &str, conn: &mut Connection) -> Result<Option<Value>> {
        let mut sql = format!("SELECT {expr}");
        self.push_from_where(&mut sql);
        let result = run(conn, &sql, &self.params)?;
        Ok(result
            .rows
            .first()
            .and_then(|r| r.value(0).cloned())
            .filter(|v| !v.is_null()))
    }

    /// Count matching rows (`SELECT count(*)`, honouring `WHERE`/joins).
    pub fn count(&self, conn: &mut Connection) -> Result<i64> {
        Ok(self
            .aggregate("count(*)", conn)?
            .and_then(|v| v.as_i64())
            .unwrap_or(0))
    }

    /// Sum of `column` (`SELECT sum(column)`), or `None` when there are no matching rows.
    pub fn sum(&self, column: &str, conn: &mut Connection) -> Result<Option<Value>> {
        self.aggregate(&format!("sum({})", quote_ident(column)), conn)
    }

    /// Arithmetic mean of `column` (`SELECT avg(column)`), or `None` when there are no matching rows.
    pub fn avg(&self, column: &str, conn: &mut Connection) -> Result<Option<f64>> {
        Ok(self
            .aggregate(&format!("avg({})", quote_ident(column)), conn)?
            .and_then(|v| value_to_f64(&v)))
    }

    /// Minimum of `column` (`SELECT min(column)`), or `None` when there are no matching rows.
    pub fn min(&self, column: &str, conn: &mut Connection) -> Result<Option<Value>> {
        self.aggregate(&format!("min({})", quote_ident(column)), conn)
    }

    /// Maximum of `column` (`SELECT max(column)`), or `None` when there are no matching rows.
    pub fn max(&self, column: &str, conn: &mut Connection) -> Result<Option<Value>> {
        self.aggregate(&format!("max({})", quote_ident(column)), conn)
    }
}

/// `INSERT` builder.
pub struct Insert {
    table: String,
    columns: Vec<String>,
    values: Vec<Value>,
    returning: bool,
}

impl Insert {
    /// Start an `INSERT INTO table`.
    #[must_use]
    pub fn into(table: &str) -> Self {
        Self {
            table: table.to_owned(),
            columns: Vec::new(),
            values: Vec::new(),
            returning: false,
        }
    }

    /// Set a `column = value` for the inserted row.
    #[must_use]
    pub fn set(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.columns.push(quote_ident(column));
        self.values.push(value.into());
        self
    }

    /// Append `RETURNING *` so the inserted row comes back.
    #[must_use]
    pub fn returning(mut self) -> Self {
        self.returning = true;
        self
    }

    /// Render to `(sql, params)`.
    #[must_use]
    pub fn build(&self) -> (String, Vec<Value>) {
        let placeholders: Vec<String> = (1..=self.values.len()).map(|i| format!("${i}")).collect();
        let mut sql = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            quote_ident(&self.table),
            self.columns.join(", "),
            placeholders.join(", ")
        );
        if self.returning {
            sql.push_str(" RETURNING *");
        }
        (sql, self.values.clone())
    }

    /// Execute; returns the [`QueryResult`] (rows present when `RETURNING *` was set).
    pub fn run(&self, conn: &mut Connection) -> Result<QueryResult> {
        let (sql, params) = self.build();
        run(conn, &sql, &params)
    }
}

/// `UPDATE` builder.
pub struct Update {
    table: String,
    sets: Vec<String>,
    params: Vec<Value>,
    wheres: Vec<String>,
    returning: bool,
}

impl Update {
    /// Start an `UPDATE table`.
    #[must_use]
    pub fn table(table: &str) -> Self {
        Self {
            table: table.to_owned(),
            sets: Vec::new(),
            params: Vec::new(),
            wheres: Vec::new(),
            returning: false,
        }
    }

    /// Set `column = value`.
    #[must_use]
    pub fn set(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.params.push(value.into());
        self.sets
            .push(format!("{} = ${}", quote_ident(column), self.params.len()));
        self
    }

    /// Add a `column = value` predicate (ANDed).
    #[must_use]
    pub fn filter(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.params.push(value.into());
        self.wheres
            .push(format!("{} = ${}", quote_ident(column), self.params.len()));
        self
    }

    /// Append `RETURNING *`.
    #[must_use]
    pub fn returning(mut self) -> Self {
        self.returning = true;
        self
    }

    /// Render to `(sql, params)`.
    #[must_use]
    pub fn build(&self) -> (String, Vec<Value>) {
        let mut sql = format!(
            "UPDATE {} SET {}",
            quote_ident(&self.table),
            self.sets.join(", ")
        );
        if !self.wheres.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&self.wheres.join(" AND "));
        }
        if self.returning {
            sql.push_str(" RETURNING *");
        }
        (sql, self.params.clone())
    }

    /// Execute; returns the [`QueryResult`].
    pub fn run(&self, conn: &mut Connection) -> Result<QueryResult> {
        let (sql, params) = self.build();
        run(conn, &sql, &params)
    }
}

/// `DELETE` builder.
pub struct Delete {
    table: String,
    wheres: Vec<String>,
    params: Vec<Value>,
    returning: bool,
}

impl Delete {
    /// Start a `DELETE FROM table`.
    #[must_use]
    pub fn from(table: &str) -> Self {
        Self {
            table: table.to_owned(),
            wheres: Vec::new(),
            params: Vec::new(),
            returning: false,
        }
    }

    /// Add a `column = value` predicate (ANDed).
    #[must_use]
    pub fn filter(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.params.push(value.into());
        self.wheres
            .push(format!("{} = ${}", quote_ident(column), self.params.len()));
        self
    }

    /// Append `RETURNING *`.
    #[must_use]
    pub fn returning(mut self) -> Self {
        self.returning = true;
        self
    }

    /// Render to `(sql, params)`.
    #[must_use]
    pub fn build(&self) -> (String, Vec<Value>) {
        let mut sql = format!("DELETE FROM {}", quote_ident(&self.table));
        if !self.wheres.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&self.wheres.join(" AND "));
        }
        if self.returning {
            sql.push_str(" RETURNING *");
        }
        (sql, self.params.clone())
    }

    /// Execute; returns the [`QueryResult`].
    pub fn run(&self, conn: &mut Connection) -> Result<QueryResult> {
        let (sql, params) = self.build();
        run(conn, &sql, &params)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn select_renders_limit_then_offset() {
        let (sql, params) = Select::from("account")
            .filter("active", true)
            .order_by("balance", true)
            .limit(10)
            .offset(20)
            .build();
        assert_eq!(
            sql,
            "SELECT * FROM \"account\" WHERE \"active\" = $1 ORDER BY \"balance\" DESC LIMIT 10 OFFSET 20"
        );
        assert_eq!(params, vec![Value::Bool(true)]);
    }

    #[test]
    fn select_offset_without_limit() {
        let (sql, _) = Select::from("t").offset(5).build();
        assert_eq!(sql, "SELECT * FROM \"t\" OFFSET 5");
    }

    #[test]
    fn select_distinct_projection() {
        let (sql, _) = Select::from("t").distinct().columns(&["city"]).build();
        assert_eq!(sql, "SELECT DISTINCT \"city\" FROM \"t\"");
    }

    #[test]
    fn select_joins_render_between_from_and_where() {
        let (sql, params) = Select::from("orders")
            .columns(&["id"])
            .inner_join("users", "\"orders\".\"user_id\" = \"users\".\"id\"")
            .filter("status", "paid")
            .build();
        assert_eq!(
            sql,
            "SELECT \"id\" FROM \"orders\" INNER JOIN \"users\" ON \"orders\".\"user_id\" = \"users\".\"id\" WHERE \"status\" = $1"
        );
        assert_eq!(params, vec![Value::Text("paid".to_owned())]);
    }

    #[test]
    fn select_left_and_cross_join() {
        let (sql, _) = Select::from("a")
            .left_join("b", "\"a\".\"k\" = \"b\".\"k\"")
            .cross_join("c")
            .build();
        assert_eq!(
            sql,
            "SELECT * FROM \"a\" LEFT JOIN \"b\" ON \"a\".\"k\" = \"b\".\"k\" CROSS JOIN \"c\""
        );
    }

    #[test]
    fn select_comparison_operators_and_params() {
        let (sql, params) = Select::from("t")
            .gt("a", 1_i64)
            .lte("b", 2_i64)
            .ne("c", "x")
            .like("d", "p%")
            .build();
        assert_eq!(
            sql,
            "SELECT * FROM \"t\" WHERE \"a\" > $1 AND \"b\" <= $2 AND \"c\" <> $3 AND \"d\" LIKE $4"
        );
        assert_eq!(
            params,
            vec![
                Value::Int(1),
                Value::Int(2),
                Value::Text("x".to_owned()),
                Value::Text("p%".to_owned()),
            ]
        );
    }

    #[test]
    fn select_in_between_and_null() {
        let (sql, params) = Select::from("t")
            .where_in("id", &[1_i64, 2, 3])
            .between("age", 18_i64, 65)
            .is_null("deleted")
            .build();
        assert_eq!(
            sql,
            "SELECT * FROM \"t\" WHERE \"id\" IN ($1, $2, $3) AND \"age\" BETWEEN $4 AND $5 AND \"deleted\" IS NULL"
        );
        assert_eq!(params.len(), 5);
    }

    #[test]
    fn select_empty_in_lists() {
        let empty: [i64; 0] = [];
        let (sql_in, _) = Select::from("t").where_in("id", &empty).build();
        assert_eq!(sql_in, "SELECT * FROM \"t\" WHERE FALSE");
        let (sql_not, _) = Select::from("t").where_not_in("id", &empty).build();
        assert_eq!(sql_not, "SELECT * FROM \"t\" WHERE TRUE");
    }

    #[test]
    fn select_group_by_having_with_raw_projection() {
        let (sql, params) = Select::from("sales")
            .select_raw(&["region", "sum(amount) AS total"])
            .gt("amount", 0_i64)
            .group_by(&["region"])
            .having("sum(amount) > $2", &[Value::Int(1000)])
            .order_by("region", false)
            .build();
        assert_eq!(
            sql,
            "SELECT region, sum(amount) AS total FROM \"sales\" WHERE \"amount\" > $1 GROUP BY \"region\" HAVING sum(amount) > $2 ORDER BY \"region\" ASC"
        );
        assert_eq!(params, vec![Value::Int(0), Value::Int(1000)]);
    }

    #[test]
    fn select_union_renumbers_right_params() {
        let left = Select::from("a").columns(&["id"]).filter("kind", "x");
        let right = Select::from("b").columns(&["id"]).filter("kind", "y");
        let (sql, params) = left.union(right).order_by("id", false).build();
        assert_eq!(
            sql,
            "SELECT \"id\" FROM \"a\" WHERE \"kind\" = $1 UNION SELECT \"id\" FROM \"b\" WHERE \"kind\" = $2 ORDER BY \"id\" ASC"
        );
        assert_eq!(
            params,
            vec![Value::Text("x".to_owned()), Value::Text("y".to_owned())]
        );
    }

    #[test]
    fn shift_placeholders_only_touches_markers() {
        assert_eq!(
            shift_placeholders("a = $1 AND b = $2", 3),
            "a = $4 AND b = $5"
        );
        assert_eq!(shift_placeholders("no markers", 5), "no markers");
        assert_eq!(shift_placeholders("x = $1", 0), "x = $1");
    }
}