//! SQL query builder for constructing dynamic queries.

use rusqlite::ToSql;

/// A simple query builder for constructing dynamic SQL queries.
///
/// This builder helps reduce boilerplate when building queries with optional
/// WHERE conditions. It automatically handles parameter indexing and SQL construction.
///
/// # Safety
///
/// **Column names are interpolated directly into SQL strings.** Only use string
/// literals for column names - never pass user input. Values are properly
/// parameterized and safe from SQL injection.
///
/// # Example
/// ```ignore
/// let mut qb = QueryBuilder::new("SELECT * FROM events");
/// qb.and_eq("session_id", session_id);  // "session_id" is a literal - OK
/// qb.and_eq_upper("event_type", event_type);
/// qb.order_by("ts DESC");
/// qb.limit(100);
/// let (sql, params) = qb.build();
/// ```
pub(crate) struct QueryBuilder {
    select: &'static str,
    conditions: Vec<String>,
    params: Vec<Box<dyn ToSql>>,
    order_by: Option<&'static str>,
    limit_clause: Option<String>,
}

impl QueryBuilder {
    /// Create a new query builder with the given SELECT clause.
    pub(crate) fn new(select: &'static str) -> Self {
        Self {
            select,
            conditions: Vec::new(),
            params: Vec::new(),
            order_by: None,
            limit_clause: None,
        }
    }

    /// Add an equality condition: `column = ?N`
    pub(crate) fn and_eq(&mut self, column: &str, value: impl ToSql + 'static) -> &mut Self {
        let idx = self.params.len() + 1;
        self.conditions.push(format!("{column} = ?{idx}"));
        self.params.push(Box::new(value));
        self
    }

    /// Add a case-insensitive equality condition: `UPPER(column) = UPPER(?N)`
    pub(crate) fn and_eq_upper(&mut self, column: &str, value: impl ToSql + 'static) -> &mut Self {
        let idx = self.params.len() + 1;
        self.conditions
            .push(format!("UPPER({column}) = UPPER(?{idx})"));
        self.params.push(Box::new(value));
        self
    }

    /// Add a case-insensitive not-equal condition: `UPPER(column) != UPPER(?N)`
    pub(crate) fn and_neq_upper(&mut self, column: &str, value: impl ToSql + 'static) -> &mut Self {
        let idx = self.params.len() + 1;
        self.conditions
            .push(format!("UPPER({column}) != UPPER(?{idx})"));
        self.params.push(Box::new(value));
        self
    }

    /// Add a greater-than condition: `column > ?N`
    pub(crate) fn and_gt(&mut self, column: &str, value: impl ToSql + 'static) -> &mut Self {
        let idx = self.params.len() + 1;
        self.conditions.push(format!("{column} > ?{idx}"));
        self.params.push(Box::new(value));
        self
    }

    /// Add a less-than condition: `column < ?N`
    pub(crate) fn and_lt(&mut self, column: &str, value: impl ToSql + 'static) -> &mut Self {
        let idx = self.params.len() + 1;
        self.conditions.push(format!("{column} < ?{idx}"));
        self.params.push(Box::new(value));
        self
    }

    /// Set the ORDER BY clause.
    pub(crate) fn order_by(&mut self, clause: &'static str) -> &mut Self {
        self.order_by = Some(clause);
        self
    }

    /// Set the LIMIT value. This adds the limit to the params vector.
    pub(crate) fn limit(&mut self, n: usize) -> &mut Self {
        let idx = self.params.len() + 1;
        self.limit_clause = Some(format!("LIMIT ?{idx}"));
        self.params.push(Box::new(n as i64));
        self
    }

    /// Build the final SQL query and parameter references.
    pub(crate) fn build(&self) -> (String, Vec<&dyn ToSql>) {
        let mut sql = String::from(self.select);

        if !self.conditions.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&self.conditions.join(" AND "));
        }

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

        if let Some(ref limit) = self.limit_clause {
            sql.push(' ');
            sql.push_str(limit);
        }

        let params_refs: Vec<&dyn ToSql> = self.params.iter().map(|p| p.as_ref()).collect();
        (sql, params_refs)
    }
}

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

    #[test]
    fn test_query_builder_no_conditions() {
        let qb = QueryBuilder::new("SELECT * FROM events");
        let (sql, params) = qb.build();
        assert_eq!(sql, "SELECT * FROM events");
        assert!(params.is_empty());
    }

    #[test]
    fn test_query_builder_single_condition() {
        let mut qb = QueryBuilder::new("SELECT * FROM events");
        qb.and_eq("session_id", "test123".to_string());
        let (sql, params) = qb.build();
        assert_eq!(sql, "SELECT * FROM events WHERE session_id = ?1");
        assert_eq!(params.len(), 1);
    }

    #[test]
    fn test_query_builder_multiple_conditions() {
        let mut qb = QueryBuilder::new("SELECT * FROM events");
        qb.and_eq("session_id", "test123".to_string());
        qb.and_eq_upper("event_type", "SessionStart".to_string());
        qb.and_gt("id", 100i64);
        let (sql, params) = qb.build();
        assert_eq!(
            sql,
            "SELECT * FROM events WHERE session_id = ?1 AND UPPER(event_type) = UPPER(?2) AND id > ?3"
        );
        assert_eq!(params.len(), 3);
    }

    #[test]
    fn test_query_builder_with_order_and_limit() {
        let mut qb = QueryBuilder::new("SELECT * FROM events");
        qb.and_eq("session_id", "test".to_string());
        qb.order_by("ts DESC").limit(100);
        let (sql, params) = qb.build();
        assert_eq!(
            sql,
            "SELECT * FROM events WHERE session_id = ?1 ORDER BY ts DESC LIMIT ?2"
        );
        assert_eq!(params.len(), 2);
    }

    #[test]
    fn test_query_builder_order_and_limit_without_conditions() {
        let mut qb = QueryBuilder::new("SELECT * FROM events");
        qb.order_by("id ASC").limit(50);
        let (sql, params) = qb.build();
        assert_eq!(sql, "SELECT * FROM events ORDER BY id ASC LIMIT ?1");
        assert_eq!(params.len(), 1);
    }
}
