floz-orm 0.1.2

//! SELECT query builder.
//!
//! ```ignore
//! let (sql, params) = SelectQuery::new("users")
//!     .where_(col_age.gt(25))
//!     .order_by(col_name.asc())
//!     .limit(10)
//!     .to_sql();
//! // "SELECT * FROM users WHERE age > $1 ORDER BY name ASC LIMIT 10"
//! ```

use std::fmt::Write;

use crate::expr::{Expr, OrderExpr};
use crate::value::Value;
use super::quote_table;

/// A SELECT query builder.
///
/// Supports: WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, column selection.
#[derive(Debug, Clone)]
pub struct SelectQuery {
    table: String,
    columns: Option<Vec<String>>,
    where_clause: Option<Expr>,
    order_by: Vec<OrderExpr>,
    limit: Option<u64>,
    offset: Option<u64>,
    distinct: bool,
}

impl SelectQuery {
    /// Create a new SELECT query for the given table.
    pub fn new(table: &str) -> Self {
        Self {
            table: table.to_string(),
            columns: None,
            where_clause: None,
            order_by: Vec::new(),
            limit: None,
            offset: None,
            distinct: false,
        }
    }

    /// Select specific columns instead of `*`.
    pub fn cols(mut self, columns: &[&str]) -> Self {
        self.columns = Some(columns.iter().map(|c| c.to_string()).collect());
        self
    }

    /// Add a WHERE clause. Multiple calls replace (not append).
    /// Use `.and()` / `.or()` on `Expr` to compose filters.
    pub fn where_(mut self, expr: Expr) -> Self {
        self.where_clause = Some(expr);
        self
    }

    /// Add an ORDER BY clause. Multiple calls append ordering.
    pub fn order_by(mut self, order: OrderExpr) -> Self {
        self.order_by.push(order);
        self
    }

    /// Set the LIMIT.
    pub fn limit(mut self, n: u64) -> Self {
        self.limit = Some(n);
        self
    }

    /// Set the OFFSET.
    pub fn offset(mut self, n: u64) -> Self {
        self.offset = Some(n);
        self
    }

    /// Add DISTINCT.
    pub fn distinct(mut self) -> Self {
        self.distinct = true;
        self
    }

    /// Render to SQL string + parameters.
    pub fn to_sql(&self) -> (String, Vec<Value>) {
        let mut sql = String::with_capacity(128);
        let mut params = Vec::new();
        let mut idx = 0usize;

        // SELECT [DISTINCT]
        sql.push_str("SELECT ");
        if self.distinct {
            sql.push_str("DISTINCT ");
        }

        // Columns
        match &self.columns {
            Some(cols) => {
                for (i, col) in cols.iter().enumerate() {
                    if i > 0 { sql.push_str(", "); }
                    sql.push_str(col);
                }
            }
            None => sql.push('*'),
        }

        // FROM
        write!(sql, " FROM {}", quote_table(&self.table)).unwrap();

        // WHERE
        if let Some(expr) = &self.where_clause {
            sql.push_str(" WHERE ");
            expr.to_sql(&mut sql, &mut params, &mut idx);
        }

        // ORDER BY
        if !self.order_by.is_empty() {
            sql.push_str(" ORDER BY ");
            for (i, order) in self.order_by.iter().enumerate() {
                if i > 0 { sql.push_str(", "); }
                order.to_sql(&mut sql);
            }
        }

        // LIMIT
        if let Some(n) = self.limit {
            write!(sql, " LIMIT {}", n).unwrap();
        }

        // OFFSET
        if let Some(n) = self.offset {
            write!(sql, " OFFSET {}", n).unwrap();
        }

        (sql, params)
    }
}

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

    fn col_id() -> Column<i32> { Column::new("id", "users") }
    fn col_age() -> Column<i32> { Column::new("age", "users") }
    fn col_name() -> Column<String> { Column::new("name", "users") }
    fn col_email() -> Column<String> { Column::new("email", "users") }

    #[test]
    fn select_star() {
        let (sql, params) = SelectQuery::new("users").to_sql();
        assert_eq!(sql, "SELECT * FROM users");
        assert!(params.is_empty());
    }

    #[test]
    fn select_columns() {
        let (sql, _) = SelectQuery::new("users").cols(&["name", "age"]).to_sql();
        assert_eq!(sql, "SELECT name, age FROM users");
    }

    #[test]
    fn select_where() {
        let (sql, params) = SelectQuery::new("users")
            .where_(col_age().gt(25))
            .to_sql();
        assert_eq!(sql, "SELECT * FROM users WHERE age > $1");
        assert_eq!(params, vec![Value::Int(25)]);
    }

    #[test]
    fn select_where_complex() {
        let (sql, params) = SelectQuery::new("users")
            .where_(
                col_age().between(18, 65)
                    .and(col_email().is_not_null())
                    .or(col_name().eq("admin")),
            )
            .to_sql();
        assert_eq!(
            sql,
            "SELECT * FROM users WHERE ((age BETWEEN $1 AND $2 AND email IS NOT NULL) OR name = $3)"
        );
        assert_eq!(params.len(), 3);
    }

    #[test]
    fn select_order_by() {
        let (sql, _) = SelectQuery::new("users")
            .order_by(col_name().asc())
            .to_sql();
        assert_eq!(sql, "SELECT * FROM users ORDER BY name ASC");
    }

    #[test]
    fn select_multiple_order() {
        let (sql, _) = SelectQuery::new("users")
            .order_by(col_name().asc())
            .order_by(col_age().desc())
            .to_sql();
        assert_eq!(sql, "SELECT * FROM users ORDER BY name ASC, age DESC");
    }

    #[test]
    fn select_limit() {
        let (sql, _) = SelectQuery::new("users").limit(10).to_sql();
        assert_eq!(sql, "SELECT * FROM users LIMIT 10");
    }

    #[test]
    fn select_offset() {
        let (sql, _) = SelectQuery::new("users").offset(20).to_sql();
        assert_eq!(sql, "SELECT * FROM users OFFSET 20");
    }

    #[test]
    fn select_limit_offset() {
        let (sql, _) = SelectQuery::new("users")
            .order_by(col_id().asc())
            .limit(20)
            .offset(40)
            .to_sql();
        assert_eq!(sql, "SELECT * FROM users ORDER BY id ASC LIMIT 20 OFFSET 40");
    }

    #[test]
    fn select_distinct() {
        let (sql, _) = SelectQuery::new("users")
            .cols(&["name"])
            .distinct()
            .to_sql();
        assert_eq!(sql, "SELECT DISTINCT name FROM users");
    }

    #[test]
    fn select_full_query() {
        let (sql, params) = SelectQuery::new("users")
            .cols(&["id", "name", "age"])
            .where_(col_age().gt(18).and(col_email().is_not_null()))
            .order_by(col_name().asc())
            .limit(50)
            .offset(100)
            .distinct()
            .to_sql();
        assert_eq!(
            sql,
            "SELECT DISTINCT id, name, age FROM users WHERE (age > $1 AND email IS NOT NULL) ORDER BY name ASC LIMIT 50 OFFSET 100"
        );
        assert_eq!(params, vec![Value::Int(18)]);
    }

    #[test]
    fn select_schema_qualified() {
        let (sql, _) = SelectQuery::new("auth.users").to_sql();
        assert_eq!(sql, r#"SELECT * FROM "auth"."users""#);
    }
}