unistore-sqlite 0.1.0

//! 查询构建器
//!
//! 职责：提供流畅的 SQL 查询构建 API

use crate::connection::Connection;
use crate::error::SqliteError;
use crate::types::{Param, Row, Rows};

/// SELECT 查询构建器
pub struct SelectBuilder<'a> {
    conn: &'a Connection,
    table: String,
    columns: Vec<String>,
    where_clause: Option<String>,
    where_params: Vec<Param>,
    order_by: Option<String>,
    limit: Option<usize>,
    offset: Option<usize>,
}

impl<'a> SelectBuilder<'a> {
    /// 创建新的 SELECT 构建器
    pub fn new(conn: &'a Connection, table: impl Into<String>) -> Self {
        Self {
            conn,
            table: table.into(),
            columns: vec!["*".to_string()],
            where_clause: None,
            where_params: Vec::new(),
            order_by: None,
            limit: None,
            offset: None,
        }
    }

    /// 指定要查询的列
    pub fn columns(mut self, cols: &[&str]) -> Self {
        self.columns = cols.iter().map(|s| s.to_string()).collect();
        self
    }

    /// 添加 WHERE 条件
    pub fn filter(mut self, condition: &str, params: impl IntoIterator<Item = Param>) -> Self {
        self.where_clause = Some(condition.to_string());
        self.where_params = params.into_iter().collect();
        self
    }

    /// 添加 WHERE 条件（单参数便捷方法）
    pub fn filter_eq(self, column: &str, value: impl Into<Param>) -> Self {
        self.filter(&format!("{} = ?", column), [value.into()])
    }

    /// 添加 ORDER BY
    pub fn order_by(mut self, column: &str, desc: bool) -> Self {
        let direction = if desc { "DESC" } else { "ASC" };
        self.order_by = Some(format!("{} {}", column, direction));
        self
    }

    /// 设置 LIMIT
    pub fn limit(mut self, limit: usize) -> Self {
        self.limit = Some(limit);
        self
    }

    /// 设置 OFFSET
    pub fn offset(mut self, offset: usize) -> Self {
        self.offset = Some(offset);
        self
    }

    /// 构建 SQL 语句
    pub fn build(&self) -> (String, Vec<Param>) {
        let mut sql = format!("SELECT {} FROM {}", self.columns.join(", "), self.table);

        let params = self.where_params.clone();

        if let Some(ref where_clause) = self.where_clause {
            sql.push_str(" WHERE ");
            sql.push_str(where_clause);
        }

        if let Some(ref order) = self.order_by {
            sql.push_str(" ORDER BY ");
            sql.push_str(order);
        }

        if let Some(limit) = self.limit {
            sql.push_str(&format!(" LIMIT {}", limit));
        }

        if let Some(offset) = self.offset {
            sql.push_str(&format!(" OFFSET {}", offset));
        }

        (sql, params)
    }

    /// 执行查询，返回所有行
    pub fn fetch_all(self) -> Result<Rows, SqliteError> {
        let (sql, params) = self.build();
        self.conn.query(&sql, &params)
    }

    /// 执行查询，返回第一行
    pub fn fetch_one(self) -> Result<Option<Row>, SqliteError> {
        let (sql, params) = self.build();
        self.conn.query_row(&sql, &params)
    }

    /// 执行查询，返回行数
    pub fn count(self) -> Result<i64, SqliteError> {
        let sql = format!(
            "SELECT COUNT(*) as cnt FROM {}{}",
            self.table,
            self.where_clause
                .as_ref()
                .map(|w| format!(" WHERE {}", w))
                .unwrap_or_default()
        );
        let row = self.conn.query_row(&sql, &self.where_params)?;
        Ok(row.and_then(|r| r.get_i64("cnt")).unwrap_or(0))
    }
}

/// INSERT 查询构建器
pub struct InsertBuilder<'a> {
    conn: &'a Connection,
    table: String,
    columns: Vec<String>,
    values: Vec<Param>,
}

impl<'a> InsertBuilder<'a> {
    /// 创建新的 INSERT 构建器
    pub fn new(conn: &'a Connection, table: impl Into<String>) -> Self {
        Self {
            conn,
            table: table.into(),
            columns: Vec::new(),
            values: Vec::new(),
        }
    }

    /// 设置列值
    pub fn set(mut self, column: &str, value: impl Into<Param>) -> Self {
        self.columns.push(column.to_string());
        self.values.push(value.into());
        self
    }

    /// 构建 SQL 语句
    pub fn build(&self) -> (String, Vec<Param>) {
        let placeholders = vec!["?"; self.columns.len()].join(", ");
        let sql = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            self.table,
            self.columns.join(", "),
            placeholders
        );
        (sql, self.values.clone())
    }

    /// 执行插入，返回最后插入的行 ID
    pub fn execute(self) -> Result<i64, SqliteError> {
        let (sql, params) = self.build();
        self.conn.execute(&sql, &params)?;
        self.conn.last_insert_rowid()
    }
}

/// UPDATE 查询构建器
pub struct UpdateBuilder<'a> {
    conn: &'a Connection,
    table: String,
    sets: Vec<(String, Param)>,
    where_clause: Option<String>,
    where_params: Vec<Param>,
}

impl<'a> UpdateBuilder<'a> {
    /// 创建新的 UPDATE 构建器
    pub fn new(conn: &'a Connection, table: impl Into<String>) -> Self {
        Self {
            conn,
            table: table.into(),
            sets: Vec::new(),
            where_clause: None,
            where_params: Vec::new(),
        }
    }

    /// 设置列值
    pub fn set(mut self, column: &str, value: impl Into<Param>) -> Self {
        self.sets.push((column.to_string(), value.into()));
        self
    }

    /// 添加 WHERE 条件
    pub fn filter(mut self, condition: &str, params: impl IntoIterator<Item = Param>) -> Self {
        self.where_clause = Some(condition.to_string());
        self.where_params = params.into_iter().collect();
        self
    }

    /// 添加 WHERE 条件（单参数便捷方法）
    pub fn filter_eq(self, column: &str, value: impl Into<Param>) -> Self {
        self.filter(&format!("{} = ?", column), [value.into()])
    }

    /// 构建 SQL 语句
    pub fn build(&self) -> (String, Vec<Param>) {
        let set_clause: Vec<String> = self.sets.iter().map(|(col, _)| format!("{} = ?", col)).collect();

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

        let mut params: Vec<Param> = self.sets.iter().map(|(_, v)| v.clone()).collect();

        if let Some(ref where_clause) = self.where_clause {
            sql.push_str(" WHERE ");
            sql.push_str(where_clause);
            params.extend(self.where_params.clone());
        }

        (sql, params)
    }

    /// 执行更新，返回影响的行数
    pub fn execute(self) -> Result<usize, SqliteError> {
        let (sql, params) = self.build();
        self.conn.execute(&sql, &params)
    }
}

/// DELETE 查询构建器
pub struct DeleteBuilder<'a> {
    conn: &'a Connection,
    table: String,
    where_clause: Option<String>,
    where_params: Vec<Param>,
}

impl<'a> DeleteBuilder<'a> {
    /// 创建新的 DELETE 构建器
    pub fn new(conn: &'a Connection, table: impl Into<String>) -> Self {
        Self {
            conn,
            table: table.into(),
            where_clause: None,
            where_params: Vec::new(),
        }
    }

    /// 添加 WHERE 条件
    pub fn filter(mut self, condition: &str, params: impl IntoIterator<Item = Param>) -> Self {
        self.where_clause = Some(condition.to_string());
        self.where_params = params.into_iter().collect();
        self
    }

    /// 添加 WHERE 条件（单参数便捷方法）
    pub fn filter_eq(self, column: &str, value: impl Into<Param>) -> Self {
        self.filter(&format!("{} = ?", column), [value.into()])
    }

    /// 构建 SQL 语句
    pub fn build(&self) -> (String, Vec<Param>) {
        let mut sql = format!("DELETE FROM {}", self.table);

        if let Some(ref where_clause) = self.where_clause {
            sql.push_str(" WHERE ");
            sql.push_str(where_clause);
        }

        (sql, self.where_params.clone())
    }

    /// 执行删除，返回影响的行数
    pub fn execute(self) -> Result<usize, SqliteError> {
        let (sql, params) = self.build();
        self.conn.execute(&sql, &params)
    }
}

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

    fn setup_test_db() -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch(
            "CREATE TABLE users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                age INTEGER
            )",
        )
        .unwrap();
        conn
    }

    #[test]
    fn test_insert() {
        let conn = setup_test_db();

        let id = InsertBuilder::new(&conn, "users")
            .set("name", "Alice")
            .set("age", 30)
            .execute()
            .unwrap();

        assert_eq!(id, 1);
    }

    #[test]
    fn test_select() {
        let conn = setup_test_db();

        InsertBuilder::new(&conn, "users")
            .set("name", "Alice")
            .set("age", 30)
            .execute()
            .unwrap();

        InsertBuilder::new(&conn, "users")
            .set("name", "Bob")
            .set("age", 25)
            .execute()
            .unwrap();

        // 查询所有
        let rows = SelectBuilder::new(&conn, "users").fetch_all().unwrap();
        assert_eq!(rows.len(), 2);

        // 条件查询
        let rows = SelectBuilder::new(&conn, "users")
            .filter_eq("name", "Alice")
            .fetch_all()
            .unwrap();
        assert_eq!(rows.len(), 1);

        // 排序和限制
        let rows = SelectBuilder::new(&conn, "users")
            .order_by("age", false)
            .limit(1)
            .fetch_all()
            .unwrap();
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0].get_str("name"), Some("Bob"));
    }

    #[test]
    fn test_update() {
        let conn = setup_test_db();

        InsertBuilder::new(&conn, "users")
            .set("name", "Alice")
            .set("age", 30)
            .execute()
            .unwrap();

        let affected = UpdateBuilder::new(&conn, "users")
            .set("age", 31)
            .filter_eq("name", "Alice")
            .execute()
            .unwrap();

        assert_eq!(affected, 1);

        let row = SelectBuilder::new(&conn, "users")
            .filter_eq("name", "Alice")
            .fetch_one()
            .unwrap()
            .unwrap();

        assert_eq!(row.get_i64("age"), Some(31));
    }

    #[test]
    fn test_delete() {
        let conn = setup_test_db();

        InsertBuilder::new(&conn, "users")
            .set("name", "Alice")
            .set("age", 30)
            .execute()
            .unwrap();

        InsertBuilder::new(&conn, "users")
            .set("name", "Bob")
            .set("age", 25)
            .execute()
            .unwrap();

        let affected = DeleteBuilder::new(&conn, "users")
            .filter_eq("name", "Alice")
            .execute()
            .unwrap();

        assert_eq!(affected, 1);

        let count = SelectBuilder::new(&conn, "users").count().unwrap();
        assert_eq!(count, 1);
    }

    #[test]
    fn test_count() {
        let conn = setup_test_db();

        for i in 0..5 {
            InsertBuilder::new(&conn, "users")
                .set("name", format!("User{}", i))
                .set("age", 20 + i)
                .execute()
                .unwrap();
        }

        let count = SelectBuilder::new(&conn, "users").count().unwrap();
        assert_eq!(count, 5);

        let count = SelectBuilder::new(&conn, "users")
            .filter("age >= ?", [22i32.into()])
            .count()
            .unwrap();
        assert_eq!(count, 3);
    }
}