fletch-orm 1.0.0

//! Type-safe SQL query builder with dialect support.
//!
//! The [`QueryBuilder`] generates parameterized SQL statements for SELECT,
//! INSERT, UPDATE, and DELETE operations across SQLite, PostgreSQL, and MySQL.

use crate::built_query::BuiltQuery;
use crate::dialect::Dialect;
use crate::filter::{Filter, Op};
use crate::value::Value;

/// The sort direction for ORDER BY clauses.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Order {
    Asc,
    Desc,
}

/// Builds parameterized SQL statements for a specific dialect.
///
/// # Usage
///
/// ```
/// use fletch_orm::query_builder::{QueryBuilder, Order};
/// use fletch_orm::dialect::PostgresDialect;
/// use fletch_orm::filter::{Filter, Op};
///
/// let dialect = PostgresDialect;
/// let query = QueryBuilder::select(&dialect, "users")
///     .columns(&["id", "name", "email"])
///     .filter(Filter::new("name", Op::Eq, "Alice"))
///     .order_by("id", Order::Asc)
///     .limit(10)
///     .build();
///
/// assert_eq!(
///     query.sql,
///     "SELECT \"id\", \"name\", \"email\" FROM \"users\" WHERE \"name\" = $1 ORDER BY \"id\" ASC LIMIT 10"
/// );
/// ```
pub struct QueryBuilder<'d> {
    dialect: &'d dyn Dialect,
    table: String,
    kind: QueryKind,
    columns: Vec<String>,
    filters: Vec<Filter>,
    order_by: Vec<(String, Order)>,
    limit: Option<u64>,
    offset: Option<u64>,
    /// Column-value pairs for INSERT/UPDATE SET clauses.
    set_values: Vec<(String, Value)>,
    /// Columns to return (for RETURNING clause).
    returning: Vec<String>,
}

#[derive(Debug, Clone, Copy)]
enum QueryKind {
    Select,
    Insert,
    Update,
    Delete,
}

impl<'d> QueryBuilder<'d> {
    /// Begin a SELECT query.
    pub fn select(dialect: &'d dyn Dialect, table: &str) -> Self {
        Self::new(dialect, table, QueryKind::Select)
    }

    /// Begin an INSERT query.
    pub fn insert(dialect: &'d dyn Dialect, table: &str) -> Self {
        Self::new(dialect, table, QueryKind::Insert)
    }

    /// Begin an UPDATE query.
    pub fn update(dialect: &'d dyn Dialect, table: &str) -> Self {
        Self::new(dialect, table, QueryKind::Update)
    }

    /// Begin a DELETE query.
    pub fn delete(dialect: &'d dyn Dialect, table: &str) -> Self {
        Self::new(dialect, table, QueryKind::Delete)
    }

    fn new(dialect: &'d dyn Dialect, table: &str, kind: QueryKind) -> Self {
        Self {
            dialect,
            table: table.to_owned(),
            kind,
            columns: Vec::new(),
            filters: Vec::new(),
            order_by: Vec::new(),
            limit: None,
            offset: None,
            set_values: Vec::new(),
            returning: Vec::new(),
        }
    }

    /// Set the columns to select (SELECT) or insert into (INSERT).
    pub fn columns(mut self, cols: &[&str]) -> Self {
        self.columns = cols.iter().map(|c| (*c).to_owned()).collect();
        self
    }

    /// Add a filter condition to the WHERE clause.
    pub fn filter(mut self, f: Filter) -> Self {
        self.filters.push(f);
        self
    }

    /// Add an ORDER BY clause.
    pub fn order_by(mut self, column: &str, order: Order) -> Self {
        self.order_by.push((column.to_owned(), 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
    }

    /// Set a column value for INSERT or UPDATE operations.
    pub fn set(mut self, column: &str, value: impl Into<Value>) -> Self {
        self.set_values.push((column.to_owned(), value.into()));
        self
    }

    /// Specify columns to return via `RETURNING` (Postgres/SQLite).
    /// On MySQL, this is ignored; use `LAST_INSERT_ID()` instead.
    pub fn returning(mut self, cols: &[&str]) -> Self {
        self.returning = cols.iter().map(|c| (*c).to_owned()).collect();
        self
    }

    /// Build the SQL query and return the SQL string with bind values.
    pub fn build(self) -> BuiltQuery {
        match self.kind {
            QueryKind::Select => self.build_select(),
            QueryKind::Insert => self.build_insert(),
            QueryKind::Update => self.build_update(),
            QueryKind::Delete => self.build_delete(),
        }
    }

    fn build_select(self) -> BuiltQuery {
        let mut values = Vec::new();
        let mut param_index = 1usize;

        let cols = if self.columns.is_empty() {
            "*".to_owned()
        } else {
            self.columns
                .iter()
                .map(|c| self.dialect.quote_identifier(c))
                .collect::<Vec<_>>()
                .join(", ")
        };

        let table = self.dialect.quote_identifier(&self.table);
        let mut sql = format!("SELECT {cols} FROM {table}");

        let where_clause = self.build_where(&mut values, &mut param_index);
        if !where_clause.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&where_clause);
        }

        if !self.order_by.is_empty() {
            sql.push_str(" ORDER BY ");
            let orders: Vec<String> = self
                .order_by
                .iter()
                .map(|(col, ord)| {
                    let dir = match ord {
                        Order::Asc => "ASC",
                        Order::Desc => "DESC",
                    };
                    format!("{} {}", self.dialect.quote_identifier(col), dir)
                })
                .collect();
            sql.push_str(&orders.join(", "));
        }

        if let Some(limit) = self.limit {
            use std::fmt::Write;
            let _ = write!(sql, " LIMIT {limit}");
        }

        if let Some(offset) = self.offset {
            use std::fmt::Write;
            let _ = write!(sql, " OFFSET {offset}");
        }

        BuiltQuery { sql, values }
    }

    fn build_insert(self) -> BuiltQuery {
        let mut values = Vec::new();

        let table = self.dialect.quote_identifier(&self.table);

        let mut col_names = Vec::new();
        let mut placeholders = Vec::new();

        for (param_index, (col, val)) in (1usize..).zip(self.set_values.iter()) {
            col_names.push(self.dialect.quote_identifier(col));
            placeholders.push(self.dialect.bind_parameter(param_index));
            values.push(val.clone());
        }

        let mut sql = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            table,
            col_names.join(", "),
            placeholders.join(", "),
        );

        if self.dialect.supports_returning() && !self.returning.is_empty() {
            let ret_cols: Vec<String> = self
                .returning
                .iter()
                .map(|c| self.dialect.quote_identifier(c))
                .collect();
            sql.push_str(" RETURNING ");
            sql.push_str(&ret_cols.join(", "));
        }

        BuiltQuery { sql, values }
    }

    fn build_update(self) -> BuiltQuery {
        let mut values = Vec::new();
        let mut param_index = 1usize;

        let table = self.dialect.quote_identifier(&self.table);

        let mut assignments = Vec::new();
        for (col, val) in &self.set_values {
            let placeholder = self.dialect.bind_parameter(param_index);
            assignments.push(format!(
                "{} = {}",
                self.dialect.quote_identifier(col),
                placeholder
            ));
            values.push(val.clone());
            param_index += 1;
        }

        let mut sql = format!("UPDATE {} SET {}", table, assignments.join(", "));

        let where_clause = self.build_where(&mut values, &mut param_index);
        if !where_clause.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&where_clause);
        }

        if self.dialect.supports_returning() && !self.returning.is_empty() {
            let ret_cols: Vec<String> = self
                .returning
                .iter()
                .map(|c| self.dialect.quote_identifier(c))
                .collect();
            sql.push_str(" RETURNING ");
            sql.push_str(&ret_cols.join(", "));
        }

        BuiltQuery { sql, values }
    }

    fn build_delete(self) -> BuiltQuery {
        let mut values = Vec::new();
        let mut param_index = 1usize;

        let table = self.dialect.quote_identifier(&self.table);
        let mut sql = format!("DELETE FROM {table}");

        let where_clause = self.build_where(&mut values, &mut param_index);
        if !where_clause.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&where_clause);
        }

        if self.dialect.supports_returning() && !self.returning.is_empty() {
            let ret_cols: Vec<String> = self
                .returning
                .iter()
                .map(|c| self.dialect.quote_identifier(c))
                .collect();
            sql.push_str(" RETURNING ");
            sql.push_str(&ret_cols.join(", "));
        }

        BuiltQuery { sql, values }
    }

    /// Build the WHERE clause fragments from filters, appending values and
    /// advancing `param_index`. Returns the clause string (without "WHERE").
    #[expect(
        clippy::unreachable,
        reason = "In/NotIn/IsNull/IsNotNull variants are handled by arms above; remaining ops are exhaustive"
    )]
    fn build_where(&self, values: &mut Vec<Value>, param_index: &mut usize) -> String {
        if self.filters.is_empty() {
            return String::new();
        }

        let mut parts = Vec::new();

        for filter in &self.filters {
            let quoted_col = self.dialect.quote_identifier(&filter.column);

            let part = match filter.op {
                Op::IsNull => format!("{quoted_col} IS NULL"),
                Op::IsNotNull => format!("{quoted_col} IS NOT NULL"),
                Op::In => {
                    let placeholders: Vec<String> = filter
                        .values
                        .iter()
                        .map(|v| {
                            let p = self.dialect.bind_parameter(*param_index);
                            values.push(v.clone());
                            *param_index += 1;
                            p
                        })
                        .collect();
                    format!("{} IN ({})", quoted_col, placeholders.join(", "))
                }
                op => {
                    let sql_op = match op {
                        Op::Eq => "=",
                        Op::Ne => "<>",
                        Op::Lt => "<",
                        Op::Le => "<=",
                        Op::Gt => ">",
                        Op::Ge => ">=",
                        Op::Like => "LIKE",
                        _ => unreachable!(),
                    };
                    let placeholder = self.dialect.bind_parameter(*param_index);
                    if let Some(v) = filter.values.first() {
                        values.push(v.clone());
                    }
                    *param_index += 1;
                    format!("{quoted_col} {sql_op} {placeholder}")
                }
            };

            parts.push(part);
        }

        parts.join(" AND ")
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::dialect::{MySqlDialect, PostgresDialect, SqliteDialect};

    // ─── SELECT ──────────────────────────────────────────────

    #[test]
    fn select_all_columns() {
        let d = SqliteDialect;
        let q = QueryBuilder::select(&d, "users").build();
        assert_eq!(q.sql, "SELECT * FROM \"users\"");
        assert!(q.values.is_empty());
    }

    #[test]
    fn select_specific_columns() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .columns(&["id", "name"])
            .build();
        assert_eq!(q.sql, "SELECT \"id\", \"name\" FROM \"users\"");
    }

    #[test]
    fn select_with_eq_filter_postgres() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .columns(&["id", "name"])
            .filter(Filter::new("name", Op::Eq, "Alice"))
            .build();
        assert_eq!(
            q.sql,
            "SELECT \"id\", \"name\" FROM \"users\" WHERE \"name\" = $1"
        );
        assert_eq!(q.values, vec![Value::Text("Alice".into())]);
    }

    #[test]
    fn select_with_eq_filter_sqlite() {
        let d = SqliteDialect;
        let q = QueryBuilder::select(&d, "users")
            .columns(&["id", "name"])
            .filter(Filter::new("name", Op::Eq, "Alice"))
            .build();
        assert_eq!(
            q.sql,
            "SELECT \"id\", \"name\" FROM \"users\" WHERE \"name\" = ?"
        );
        assert_eq!(q.values, vec![Value::Text("Alice".into())]);
    }

    #[test]
    fn select_with_eq_filter_mysql() {
        let d = MySqlDialect;
        let q = QueryBuilder::select(&d, "users")
            .columns(&["id", "name"])
            .filter(Filter::new("name", Op::Eq, "Alice"))
            .build();
        assert_eq!(q.sql, "SELECT `id`, `name` FROM `users` WHERE `name` = ?");
    }

    #[test]
    fn select_with_multiple_filters() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::new("age", Op::Ge, 18))
            .filter(Filter::new("active", Op::Eq, true))
            .build();
        assert_eq!(
            q.sql,
            "SELECT * FROM \"users\" WHERE \"age\" >= $1 AND \"active\" = $2"
        );
        assert_eq!(q.values, vec![Value::I32(18), Value::Bool(true)]);
    }

    #[test]
    fn select_with_in_filter() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::in_list(
                "id",
                vec![Value::I64(1), Value::I64(2), Value::I64(3)],
            ))
            .build();
        assert_eq!(
            q.sql,
            "SELECT * FROM \"users\" WHERE \"id\" IN ($1, $2, $3)"
        );
        assert_eq!(q.values, vec![Value::I64(1), Value::I64(2), Value::I64(3)]);
    }

    #[test]
    fn select_with_is_null() {
        let d = SqliteDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::is_null("deleted_at"))
            .build();
        assert_eq!(
            q.sql,
            "SELECT * FROM \"users\" WHERE \"deleted_at\" IS NULL"
        );
        assert!(q.values.is_empty());
    }

    #[test]
    fn select_with_is_not_null() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::is_not_null("email"))
            .build();
        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"email\" IS NOT NULL");
    }

    #[test]
    fn select_with_like() {
        let d = SqliteDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::new("name", Op::Like, "%alice%"))
            .build();
        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"name\" LIKE ?");
        assert_eq!(q.values, vec![Value::Text("%alice%".into())]);
    }

    #[test]
    fn select_with_order_by() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .order_by("name", Order::Asc)
            .order_by("id", Order::Desc)
            .build();
        assert_eq!(
            q.sql,
            "SELECT * FROM \"users\" ORDER BY \"name\" ASC, \"id\" DESC"
        );
    }

    #[test]
    fn select_with_limit_offset() {
        let d = SqliteDialect;
        let q = QueryBuilder::select(&d, "posts")
            .limit(10)
            .offset(20)
            .build();
        assert_eq!(q.sql, "SELECT * FROM \"posts\" LIMIT 10 OFFSET 20");
    }

    #[test]
    fn select_full_query_postgres() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .columns(&["id", "name", "email"])
            .filter(Filter::new("active", Op::Eq, true))
            .filter(Filter::new("age", Op::Gt, 18))
            .order_by("name", Order::Asc)
            .limit(25)
            .offset(50)
            .build();
        assert_eq!(
            q.sql,
            "SELECT \"id\", \"name\", \"email\" FROM \"users\" WHERE \"active\" = $1 AND \"age\" > $2 ORDER BY \"name\" ASC LIMIT 25 OFFSET 50"
        );
        assert_eq!(q.values, vec![Value::Bool(true), Value::I32(18)]);
    }

    // ─── INSERT ──────────────────────────────────────────────

    #[test]
    fn insert_sqlite() {
        let d = SqliteDialect;
        let q = QueryBuilder::insert(&d, "users")
            .set("name", "Alice")
            .set("email", "alice@example.com")
            .returning(&["id"])
            .build();
        assert_eq!(
            q.sql,
            "INSERT INTO \"users\" (\"name\", \"email\") VALUES (?, ?) RETURNING \"id\""
        );
        assert_eq!(
            q.values,
            vec![
                Value::Text("Alice".into()),
                Value::Text("alice@example.com".into()),
            ]
        );
    }

    #[test]
    fn insert_postgres() {
        let d = PostgresDialect;
        let q = QueryBuilder::insert(&d, "users")
            .set("name", "Bob")
            .set("age", 30)
            .returning(&["id"])
            .build();
        assert_eq!(
            q.sql,
            "INSERT INTO \"users\" (\"name\", \"age\") VALUES ($1, $2) RETURNING \"id\""
        );
        assert_eq!(q.values, vec![Value::Text("Bob".into()), Value::I32(30)]);
    }

    #[test]
    fn insert_mysql_no_returning() {
        let d = MySqlDialect;
        let q = QueryBuilder::insert(&d, "users")
            .set("name", "Charlie")
            .returning(&["id"])
            .build();
        // MySQL does not support RETURNING
        assert_eq!(q.sql, "INSERT INTO `users` (`name`) VALUES (?)");
        assert_eq!(q.values, vec![Value::Text("Charlie".into())]);
    }

    // ─── UPDATE ──────────────────────────────────────────────

    #[test]
    fn update_postgres() {
        let d = PostgresDialect;
        let q = QueryBuilder::update(&d, "users")
            .set("name", "Updated")
            .set("age", 25)
            .filter(Filter::new("id", Op::Eq, 1i64))
            .build();
        assert_eq!(
            q.sql,
            "UPDATE \"users\" SET \"name\" = $1, \"age\" = $2 WHERE \"id\" = $3"
        );
        assert_eq!(
            q.values,
            vec![Value::Text("Updated".into()), Value::I32(25), Value::I64(1),]
        );
    }

    #[test]
    fn update_sqlite() {
        let d = SqliteDialect;
        let q = QueryBuilder::update(&d, "users")
            .set("name", "Updated")
            .filter(Filter::new("id", Op::Eq, 1i64))
            .returning(&["id", "name"])
            .build();
        assert_eq!(
            q.sql,
            "UPDATE \"users\" SET \"name\" = ? WHERE \"id\" = ? RETURNING \"id\", \"name\""
        );
    }

    #[test]
    fn update_mysql() {
        let d = MySqlDialect;
        let q = QueryBuilder::update(&d, "users")
            .set("active", false)
            .filter(Filter::new("id", Op::Eq, 42i64))
            .build();
        assert_eq!(q.sql, "UPDATE `users` SET `active` = ? WHERE `id` = ?");
        assert_eq!(q.values, vec![Value::Bool(false), Value::I64(42)]);
    }

    // ─── DELETE ──────────────────────────────────────────────

    #[test]
    fn delete_postgres() {
        let d = PostgresDialect;
        let q = QueryBuilder::delete(&d, "users")
            .filter(Filter::new("id", Op::Eq, 1i64))
            .build();
        assert_eq!(q.sql, "DELETE FROM \"users\" WHERE \"id\" = $1");
        assert_eq!(q.values, vec![Value::I64(1)]);
    }

    #[test]
    fn delete_sqlite() {
        let d = SqliteDialect;
        let q = QueryBuilder::delete(&d, "posts")
            .filter(Filter::new("id", Op::Eq, 5i64))
            .returning(&["id"])
            .build();
        assert_eq!(
            q.sql,
            "DELETE FROM \"posts\" WHERE \"id\" = ? RETURNING \"id\""
        );
    }

    #[test]
    fn delete_mysql_no_returning() {
        let d = MySqlDialect;
        let q = QueryBuilder::delete(&d, "posts")
            .filter(Filter::new("id", Op::Eq, 5i64))
            .returning(&["id"])
            .build();
        // MySQL doesn't support RETURNING
        assert_eq!(q.sql, "DELETE FROM `posts` WHERE `id` = ?");
    }

    #[test]
    fn delete_all_rows() {
        let d = SqliteDialect;
        let q = QueryBuilder::delete(&d, "logs").build();
        assert_eq!(q.sql, "DELETE FROM \"logs\"");
        assert!(q.values.is_empty());
    }

    // ─── BIND PARAMETER NUMBERING ───────────────────────────

    #[test]
    fn postgres_bind_params_numbered_correctly() {
        let d = PostgresDialect;
        let q = QueryBuilder::update(&d, "users")
            .set("name", "A")
            .set("email", "a@b.com")
            .set("age", 30)
            .filter(Filter::new("id", Op::Eq, 1i64))
            .filter(Filter::new("active", Op::Eq, true))
            .build();
        assert_eq!(
            q.sql,
            "UPDATE \"users\" SET \"name\" = $1, \"email\" = $2, \"age\" = $3 WHERE \"id\" = $4 AND \"active\" = $5"
        );
        assert_eq!(q.values.len(), 5);
    }

    // ─── COMPARISON OPERATORS ───────────────────────────────

    #[test]
    fn ne_operator() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "users")
            .filter(Filter::new("status", Op::Ne, "banned"))
            .build();
        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"status\" <> $1");
    }

    #[test]
    fn lt_le_gt_ge_operators() {
        let d = PostgresDialect;
        let q = QueryBuilder::select(&d, "products")
            .filter(Filter::new("price", Op::Lt, 100i32))
            .filter(Filter::new("stock", Op::Le, 50i32))
            .filter(Filter::new("rating", Op::Gt, 3i32))
            .filter(Filter::new("reviews", Op::Ge, 10i32))
            .build();
        assert_eq!(
            q.sql,
            "SELECT * FROM \"products\" WHERE \"price\" < $1 AND \"stock\" <= $2 AND \"rating\" > $3 AND \"reviews\" >= $4"
        );
    }
}