lume 0.13.1

#![warn(missing_docs)]

//! # Query Module
//!
//! This module provides type-safe query building and execution functionality.
//! It includes the `Query<T>` struct for building and executing database queries.

use std::{fmt::Debug, marker::PhantomData, sync::Arc};

#[cfg(feature = "mysql")]
use sqlx::MySqlPool;
#[cfg(feature = "postgres")]
use sqlx::PgPool;
#[cfg(feature = "sqlite")]
use sqlx::SqlitePool;

use crate::dialects::get_dialect;
use crate::filter::{Filter, Filtered};
use crate::helpers::{StartingSql, bind_value, build_filter_expr, get_starting_sql};
use crate::schema::{ColumnInfo, Select, Value};
use crate::{database::error::DatabaseError, row::Row, schema::Schema};

/// A type-safe query builder for database operations.
///
/// The `Query<T, S>` struct provides a fluent interface for building and executing
/// database queries with compile-time type safety.
///
/// # Type Parameters
///
/// - `T`: The schema type to query (must implement `Schema + Debug`)
/// - `S`: The selection type for column specification (must implement `Select + Debug`)
///
/// # Features
///
/// - **Type Safety**: Compile-time type checking for all query operations
/// - **Fluent Interface**: Chainable methods for building complex queries
/// - **Filtering**: Support for WHERE clause conditions
/// - **MySQL Integration**: Built-in support for MySQL database operations
///
/// # Example
///
/// ```no_run
/// use lume::define_schema;
/// use lume::database::Database;
/// use lume::filter::Filter;
/// use lume::schema::{Schema, ColumnInfo};
/// use lume::filter::eq_value;
///
/// define_schema! {
///     User {
///         id: i32 [primary_key()],
///         name: String [not_null()],
///         age: i32,
///     }
/// }
///
/// #[tokio::main]
/// async fn main() -> Result<(), lume::database::error::DatabaseError> {
///     let db = Database::connect("mysql://...").await?;
///     let users = db.query::<User, SelectUser>()
///         .filter(eq_value(User::name(), Value::String("John".to_string())))
///         .execute()
///         .await?;
///     Ok(())
/// }
/// ```
#[derive(Debug)]
pub struct Query<T, S> {
    /// Phantom data to maintain schema type information
    pub(crate) table: PhantomData<T>,
    /// List of filters to apply to the query
    pub(crate) filters: Vec<Box<dyn Filtered>>,

    #[cfg(feature = "mysql")]
    /// Database connection pool
    pub(crate) conn: Arc<MySqlPool>,

    #[cfg(feature = "postgres")]
    /// Database connection pool
    pub(crate) conn: Arc<PgPool>,

    #[cfg(feature = "sqlite")]
    /// Database connection pool
    pub(crate) conn: Arc<SqlitePool>,

    pub(crate) select: Option<S>,
    pub(crate) distinct: bool,

    pub(crate) joins: Vec<JoinInfo>,

    pub(crate) limit: Option<u64>,
    pub(crate) offset: Option<u64>,
}

/// Information about a join operation
#[derive(Debug)]
pub(crate) struct JoinInfo {
    /// The table to join
    pub(crate) table_name: String,
    /// The join condition (column-to-column comparison)
    pub(crate) condition: Filter,

    pub(crate) join_type: JoinType,

    pub(crate) columns: Vec<ColumnInfo<'static>>,

    pub(crate) selected_columns: Vec<&'static str>,
}

#[derive(Debug, PartialEq)]
pub(crate) enum JoinType {
    Left,
    Inner,
    #[cfg(not(feature = "sqlite"))]
    Right,
    #[cfg(feature = "postgres")]
    Full,
    Cross,
}

impl<T: Schema + Debug, S: Select + Debug> Query<T, S> {
    #[cfg(feature = "mysql")]
    /// Creates a new query builder for the specified schema type.
    ///
    /// # Arguments
    ///
    /// - `conn`: The database connection pool
    ///
    /// # Returns
    ///
    /// A new `Query<T>` instance ready for building queries
    pub(crate) fn new(conn: Arc<MySqlPool>) -> Self {
        Self {
            table: PhantomData,
            filters: Vec::new(),
            select: None,
            distinct: false,
            limit: None,
            offset: None,
            joins: Vec::new(),
            conn,
        }
    }

    #[cfg(feature = "postgres")]
    /// Creates a new query builder for the specified schema type.
    ///
    /// # Arguments
    ///
    /// - `conn`: The database connection pool
    ///
    /// # Returns
    ///
    /// A new `Query<T>` instance ready for building queries
    pub(crate) fn new(conn: Arc<PgPool>) -> Self {
        Self {
            table: PhantomData,
            filters: Vec::new(),
            select: None,
            distinct: false,
            limit: None,
            offset: None,
            joins: Vec::new(),
            conn,
        }
    }

    #[cfg(feature = "sqlite")]
    /// Creates a new query builder for the specified schema type.
    ///
    /// # Arguments
    ///
    /// - `conn`: The database connection pool
    ///
    /// # Returns
    ///
    /// A new `Query<T>` instance ready for building queries
    pub(crate) fn new(conn: Arc<SqlitePool>) -> Self {
        Self {
            table: PhantomData,
            filters: Vec::new(),
            select: None,
            distinct: false,
            limit: None,
            offset: None,
            joins: Vec::new(),
            conn,
        }
    }

    /// Adds a filter condition to the query.
    ///
    /// This method allows chaining multiple filter conditions to build
    /// complex WHERE clauses. All filters are combined with AND logic.
    ///
    /// # Arguments
    ///
    /// - `filter`: The filter condition to add
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_value;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///         age: i32,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let query = db.query::<User, SelectUser>()
    ///         .filter(eq_value(User::name(), Value::String("John".to_string())));
    ///     Ok(())
    /// }
    /// ```
    pub fn filter<F>(mut self, filter: F) -> Self
    where
        F: Filtered + 'static,
    {
        self.filters.push(Box::new(filter));
        self
    }

    /// Adds a limit to the query.
    ///
    /// This method adds a LIMIT clause to the SQL query, limiting the number of rows returned.
    ///
    /// # Arguments
    ///
    /// * `limit` - The maximum number of rows to return.
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining.
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_value;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let query = db.query::<User, SelectUser>().limit(10);
    ///     Ok(())
    /// }
    /// ```
    pub fn limit(mut self, limit: u64) -> Self {
        self.limit = Some(limit);
        self
    }

    /// Adds an offset to the query.
    ///
    /// This method adds an OFFSET clause to the SQL query, skipping the specified number of rows before starting to return rows.
    ///
    /// # Arguments
    ///
    /// * `offset` - The number of rows to skip before starting to return rows.
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining.
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let query = db.query::<User, SelectUser>().offset(20);
    ///     Ok(())
    /// }
    /// ```
    pub fn offset(mut self, offset: u64) -> Self {
        self.offset = Some(offset);
        self
    }

    /// Specifies which columns to select in the query.
    ///
    /// This method accepts a selection schema that determines which columns
    /// will be included in the SELECT clause of the SQL query.
    ///
    /// # Arguments
    ///
    /// - `select_schema`: The selection schema specifying which columns to include
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining

    pub fn select(mut self, select_schema: S) -> Self {
        self.select = Some(select_schema);
        self
    }

    /// Specifies that the query should select only distinct rows for the given columns.
    ///
    /// This method works like [`select`](Self::select), but adds a `DISTINCT` clause to the SQL query,
    /// ensuring that duplicate rows are removed from the result set.
    ///
    /// # Arguments
    ///
    /// - `select_schema`: The selection schema specifying which columns to include in the DISTINCT selection.
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining.
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     // Selects only unique user names
    ///     let query = db.query::<User, SelectUser>().select_distinct(SelectUser::selected().name());
    ///     Ok(())
    /// }
    /// ```
    pub fn select_distinct(mut self, select_schema: S) -> Self {
        self.select = Some(select_schema);
        self.distinct = true;
        self
    }

    /// Adds a left join to the query.
    ///
    /// This method joins the specified schema table to the current query using a LEFT JOIN.
    /// All records from the left table (current query) are returned, along with matching
    /// records from the right table (joined table).
    ///
    /// # Arguments
    ///
    /// - `filter`: The join condition specifying how tables should be joined
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_column;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    ///
    ///     Post {
    ///         id: i32 [primary_key()],
    ///         user_id: i32,
    ///         title: String,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let results = db.query::<User, SelectUser>()
    ///         .left_join::<Post, SelectPost>(eq_column(User::id(), Post::user_id()), SelectPost { title: true, ..Default::default() })
    ///         .execute()
    ///         .await?;
    ///     Ok(())
    /// }
    /// ```
    pub fn left_join<LeftJoinSchema: Schema + Debug, LeftJoinSchemaSelect: Select + Debug>(
        mut self,
        filter: Filter,
        select_schema: LeftJoinSchemaSelect,
    ) -> Self {
        self.joins.push(JoinInfo {
            table_name: LeftJoinSchema::table_name().to_string(),
            condition: filter,
            join_type: JoinType::Left,
            columns: LeftJoinSchema::get_all_columns(),
            selected_columns: select_schema.get_selected(),
        });

        self
    }

    /// Adds an inner join to the query.
    ///
    /// This method joins the specified schema table to the current query using an INNER JOIN.
    /// Only records that have matching values in both tables are returned.
    ///
    /// # Arguments
    ///
    /// - `filter`: The join condition specifying how tables should be joined
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_column;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    ///
    ///     Post {
    ///         id: i32 [primary_key()],
    ///         user_id: i32,
    ///         title: String,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let results = db.query::<User, SelectUser>()
    ///         .inner_join::<Post, SelectPost>(eq_column(User::id(), Post::user_id()), SelectPost { title: true, ..Default::default() })
    ///         .execute()
    ///         .await?;
    ///     Ok(())
    /// }
    /// ```
    pub fn inner_join<InnerJoinSchema: Schema + Debug, InnerJoinSchemaSelect: Select + Debug>(
        mut self,
        filter: Filter,
        select_schema: InnerJoinSchemaSelect,
    ) -> Self {
        self.joins.push(JoinInfo {
            table_name: InnerJoinSchema::table_name().to_string(),
            condition: filter,
            join_type: JoinType::Inner,
            columns: InnerJoinSchema::get_all_columns(),
            selected_columns: select_schema.get_selected(),
        });

        self
    }

    #[cfg(not(feature = "sqlite"))]
    /// Adds a right join to the query.
    ///
    /// This method joins the specified schema table to the current query using a RIGHT JOIN.
    /// All records from the right table (joined table) are returned, along with matching
    /// records from the left table (current query).
    ///
    /// # Arguments
    ///
    /// - `filter`: The join condition specifying how tables should be joined
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_column;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    ///
    ///     Post {
    ///         id: i32 [primary_key()],
    ///         user_id: i32,
    ///         title: String,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let results = db.query::<User, SelectUser>()
    ///         .right_join::<Post, SelectPost>(eq_column(User::id(), Post::user_id()), SelectPost { title: true, ..Default::default() })
    ///         .execute()
    ///         .await?;
    ///     Ok(())
    /// }
    /// ```
    pub fn right_join<RightJoinSchema: Schema + Debug, RightJoinSchemaSelect: Select + Debug>(
        mut self,
        filter: Filter,
        select_schema: RightJoinSchemaSelect,
    ) -> Self {
        self.joins.push(JoinInfo {
            table_name: RightJoinSchema::table_name().to_string(),
            condition: filter,
            join_type: JoinType::Right,
            columns: RightJoinSchema::get_all_columns(),
            selected_columns: select_schema.get_selected(),
        });

        self
    }

    #[cfg(feature = "postgres")]
    /// Adds a full outer join to the query.
    ///
    /// This method joins the specified schema table to the current query using a FULL OUTER JOIN.
    /// All records from both tables are returned, with NULL values for non-matching records.
    ///
    /// # Arguments
    ///
    /// - `filter`: The join condition specifying how tables should be joined
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::{database::Database, define_schema, filter::eq_column};
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    ///
    ///     Post {
    ///         id: i32 [primary_key()],
    ///         user_id: i32,
    ///         title: String,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let results = db
    ///         .query::<User, SelectUser>()
    ///         .full_join::<Post, SelectPost>(
    ///             eq_column(User::id(), Post::user_id()),
    ///             SelectPost::selected(),
    ///         )
    ///         .execute()
    ///         .await?;
    ///     Ok(())
    /// }
    /// ```
    pub fn full_join<FullJoinSchema: Schema + Debug, FullJoinSchemaSelect: Select + Debug>(
        mut self,
        filter: Filter,
        select_schema: FullJoinSchemaSelect,
    ) -> Self {
        self.joins.push(JoinInfo {
            table_name: FullJoinSchema::table_name().to_string(),
            condition: filter,
            join_type: JoinType::Full,
            columns: FullJoinSchema::get_all_columns(),
            selected_columns: select_schema.get_selected(),
        });

        self
    }

    /// Adds a cross join to the query.
    ///
    /// This method joins the specified schema table to the current query using a CROSS JOIN.
    /// This produces a Cartesian product of all records from both tables.
    ///
    /// # Arguments
    ///
    /// - `filter`: The join condition (note: cross joins typically don't use conditions)
    ///
    /// # Returns
    ///
    /// The query builder instance for method chaining
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    ///
    ///     Post {
    ///         id: i32 [primary_key()],
    ///         user_id: i32,
    ///         title: String,
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let results = db.query::<User, SelectUser>()
    ///         .cross_join::<Post, SelectPost>(SelectPost { title: true, ..Default::default() })
    ///         .execute()
    ///         .await?;
    ///     Ok(())
    /// }
    /// ```
    pub fn cross_join<CrossJoinSchema: Schema + Debug, CrossJoinSchemaSelect: Select + Debug>(
        mut self,
        select_schema: CrossJoinSchemaSelect,
    ) -> Self {
        self.joins.push(JoinInfo {
            table_name: CrossJoinSchema::table_name().to_string(),
            condition: Filter::default(),
            join_type: JoinType::Cross,
            columns: CrossJoinSchema::get_all_columns(),
            selected_columns: select_schema.get_selected(),
        });

        self
    }

    /// Executes the query and returns the results.
    ///
    /// This method builds and executes the SQL query, returning type-safe
    /// row objects that can be used to access column values.
    ///
    /// # Returns
    ///
    /// - `Ok(Vec<Row<T>>)`: A vector of type-safe row objects
    /// - `Err(DatabaseError)`: If there was an error executing the query
    ///
    /// # Example
    ///
    /// ```no_run
    /// use lume::define_schema;
    /// use lume::database::Database;
    /// use lume::filter::Filter;
    /// use lume::schema::{Schema, ColumnInfo};
    /// use lume::filter::eq_value;
    ///
    /// define_schema! {
    ///     User {
    ///         id: i32 [primary_key()],
    ///         name: String [not_null()],
    ///     }
    /// }
    ///
    /// #[tokio::main]
    /// async fn main() -> Result<(), lume::database::error::DatabaseError> {
    ///     let db = Database::connect("mysql://...").await?;
    ///     let users = db.query::<User, SelectUser>()
    ///         .filter(eq_value(User::name(), Value::String("John".to_string())))
    ///         .execute()
    ///         .await?;
    ///
    ///     for user in users {
    ///         let name: Option<String> = user.get(User::name());
    ///         println!("User: {:?}", name);
    ///     }
    ///     Ok(())
    /// }
    /// ```
    pub async fn execute(self) -> Result<Vec<Row<T>>, DatabaseError> {
        let mut sql = get_starting_sql(StartingSql::Select, T::table_name());

        if self.distinct {
            sql.push_str(" DISTINCT ");
        }

        let sql = Self::select_sql(sql, self.select, T::table_name(), &self.joins);
        let sql = Self::joins_sql(sql, &self.joins);
        let mut params: Vec<Value> = Vec::new();
        let mut sql = Self::filter_sql(sql, self.filters, &mut params);

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

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

        let mut conn = self
            .conn
            .acquire()
            .await
            .map_err(DatabaseError::ConnectionError)?;

        let mut query = sqlx::query(&sql);
        for v in params {
            query = bind_value(query, v);
        }

        let data = query
            .fetch_all(&mut *conn)
            .await
            .map_err(|e| DatabaseError::QueryError(e.to_string()))?;

        #[cfg(feature = "mysql")]
        let rows = Row::from_mysql_row(data, Some(&self.joins));

        #[cfg(feature = "postgres")]
        let rows = Row::from_postgres_row(data, Some(&self.joins));

        #[cfg(feature = "sqlite")]
        let rows = Row::from_sqlite_row(data, Some(&self.joins));

        Ok(rows)
    }

    pub(crate) fn select_sql(
        mut sql: String,
        select: Option<S>,
        table_name: &str,
        joins: &Vec<JoinInfo>,
    ) -> String {
        if let Some(selection) = select {
            sql.push_str(&selection.get_selected().join(", "));
        } else {
            // Default to the base table only (avoid pulling join columns twice)
            let dialect = get_dialect();
            sql.push_str(&format!("{}.*", dialect.quote_identifier(table_name)));
        }

        if !joins.is_empty() {
            for join in joins {
                for column in &join.selected_columns {
                    sql.push_str(&format!(", {}", column));
                }
            }
        }

        sql.push_str(format!(" FROM {}", get_dialect().quote_identifier(table_name)).as_str());
        sql
    }

    pub(crate) fn joins_sql(mut sql: String, joins: &Vec<JoinInfo>) -> String {
        if joins.is_empty() {
            return sql;
        }

        for join in joins {
            let join_type = match join.join_type {
                JoinType::Left => "LEFT JOIN",
                JoinType::Inner => "INNER JOIN",
                #[cfg(not(feature = "sqlite"))]
                JoinType::Right => "RIGHT JOIN",
                #[cfg(feature = "postgres")]
                JoinType::Full => "FULL JOIN",
                JoinType::Cross => "CROSS JOIN",
            };

            let join_table = &join.table_name;

            if join_type == "CROSS JOIN" {
                sql.push_str(&format!(" {} {}", join_type, join_table,));
            } else {
                sql.push_str(&format!(
                    " {} {} ON {}.{} = {}.{}",
                    join_type,
                    join_table,
                    join.condition.column_one.0,
                    join.condition.column_one.1,
                    join.condition.column_two.as_ref().unwrap().0,
                    join.condition.column_two.as_ref().unwrap().1
                ));
            }
        }

        sql
    }
    pub(crate) fn filter_sql(
        mut sql: String,
        filters: Vec<Box<dyn Filtered>>,
        params: &mut Vec<Value>,
    ) -> String {
        if filters.is_empty() {
            return sql;
        }

        sql.push_str(" WHERE ");
        let mut parts: Vec<String> = Vec::with_capacity(filters.len());
        for filter in &filters {
            parts.push(build_filter_expr(filter.as_ref(), params));
        }
        sql.push_str(&parts.join(" AND "));

        sql
    }
}