fraiseql-core 2.15.0

//! SELECT, GROUP BY, and aggregate expression SQL generation.

use super::{
    AggregateExpression, AggregateFunction, AggregationSqlGenerator, DatabaseType,
    GroupByExpression, OrderByClause, OrderDirection, Result, TemporalBucket,
};

impl AggregationSqlGenerator {
    /// Build SELECT clause.
    ///
    /// # Errors
    ///
    /// Returns [`FraiseQLError::Validation`] if an unrecognised aggregate function
    /// or GROUP BY expression cannot be converted to SQL.
    pub(super) fn build_select_clause(
        &self,
        group_by_expressions: &[GroupByExpression],
        aggregate_expressions: &[AggregateExpression],
    ) -> Result<String> {
        let mut columns = Vec::new();

        // Add GROUP BY columns to SELECT
        for expr in group_by_expressions {
            let column = self.group_by_expression_to_sql(expr)?;
            let alias = match expr {
                GroupByExpression::JsonbPath { alias, .. }
                | GroupByExpression::TemporalBucket { alias, .. }
                | GroupByExpression::CalendarPath { alias, .. }
                | GroupByExpression::NativeColumn { alias, .. } => alias,
            };
            columns.push(format!("{} AS {}", column, alias));
        }

        // Add aggregate columns to SELECT
        for expr in aggregate_expressions {
            let column = self.aggregate_expression_to_sql(expr)?;
            let alias = match expr {
                AggregateExpression::Count { alias }
                | AggregateExpression::CountDistinct { alias, .. }
                | AggregateExpression::MeasureAggregate { alias, .. }
                | AggregateExpression::AdvancedAggregate { alias, .. }
                | AggregateExpression::BoolAggregate { alias, .. } => alias,
            };
            columns.push(format!("{} AS {}", column, alias));
        }

        Ok(format!("SELECT\n  {}", columns.join(",\n  ")))
    }

    /// Convert GROUP BY expression to SQL.
    ///
    /// # Errors
    ///
    /// Currently infallible; reserved for future expression types that may
    /// require validation (e.g., computed GROUP BY expressions).
    pub(super) fn group_by_expression_to_sql(&self, expr: &GroupByExpression) -> Result<String> {
        match expr {
            GroupByExpression::JsonbPath {
                jsonb_column, path, ..
            } => Ok(self.jsonb_extract_sql(jsonb_column, path)),
            GroupByExpression::TemporalBucket { column, bucket, .. } => {
                Ok(self.temporal_bucket_sql(column, *bucket))
            },
            GroupByExpression::CalendarPath {
                calendar_column,
                json_key,
                ..
            } => {
                // Calendar dimension: reuse JSONB extraction for all 4 databases
                Ok(self.jsonb_extract_sql(calendar_column, json_key))
            },
            GroupByExpression::NativeColumn { column, .. } => {
                // Direct column reference, dialect-quoted to handle reserved words
                Ok(self.quote_identifier(column))
            },
        }
    }

    /// Generate temporal bucket SQL
    pub(super) fn temporal_bucket_sql(&self, column: &str, bucket: TemporalBucket) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => {
                format!("DATE_TRUNC('{}', {})", bucket.postgres_arg(), column)
            },
        }
    }

    /// Convert aggregate expression to SQL.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` when an advanced aggregate function
    /// (e.g., `STRING_AGG`) is not supported by the target database dialect.
    pub(super) fn aggregate_expression_to_sql(&self, expr: &AggregateExpression) -> Result<String> {
        match expr {
            AggregateExpression::Count { .. } => Ok("COUNT(*)".to_string()),
            AggregateExpression::CountDistinct { column, .. } => {
                Ok(format!("COUNT(DISTINCT {})", column))
            },
            AggregateExpression::MeasureAggregate {
                column,
                function,
                native,
                ..
            } => {
                #[allow(clippy::enum_glob_use)]
                // Reason: glob import reduces noise in exhaustive match arms
                use AggregateFunction::*;
                // Native measure columns use quoted identifiers (no ::numeric cast needed)
                let col_ref = if *native {
                    self.quote_identifier(column)
                } else {
                    column.clone()
                };
                // Handle statistical functions with database-specific SQL
                match function {
                    Stddev => Ok(self.generate_stddev_sql(&col_ref)),
                    Variance => Ok(self.generate_variance_sql(&col_ref)),
                    _ => Ok(format!("{}({})", function.sql_name(), col_ref)),
                }
            },
            AggregateExpression::AdvancedAggregate {
                column,
                function,
                delimiter,
                order_by,
                ..
            } => self.advanced_aggregate_to_sql(
                column,
                *function,
                delimiter.as_deref(),
                order_by.as_ref(),
            ),
            AggregateExpression::BoolAggregate {
                column, function, ..
            } => Ok(self.generate_bool_agg_sql(column, *function)),
        }
    }

    /// Generate SQL for advanced aggregates
    pub(super) fn advanced_aggregate_to_sql(
        &self,
        column: &str,
        function: AggregateFunction,
        delimiter: Option<&str>,
        order_by: Option<&Vec<OrderByClause>>,
    ) -> Result<String> {
        #[allow(clippy::enum_glob_use)]
        // Reason: glob import reduces noise in exhaustive match arms
        use AggregateFunction::*;

        match function {
            ArrayAgg => Ok(self.generate_array_agg_sql(column, order_by)),
            JsonAgg => Ok(self.generate_json_agg_sql(column, order_by)),
            JsonbAgg => Ok(self.generate_jsonb_agg_sql(column, order_by)),
            StringAgg => {
                Ok(self.generate_string_agg_sql(column, delimiter.unwrap_or(","), order_by))
            },
            _ => Ok(format!("{}({})", function.sql_name(), column)),
        }
    }

    /// Generate `ARRAY_AGG` SQL
    pub(super) fn generate_array_agg_sql(
        &self,
        column: &str,
        order_by: Option<&Vec<OrderByClause>>,
    ) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => {
                if let Some(order) = order_by {
                    format!("ARRAY_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
                } else {
                    format!("ARRAY_AGG({})", column)
                }
            },
        }
    }

    /// Generate `JSON_AGG` SQL
    pub(super) fn generate_json_agg_sql(
        &self,
        column: &str,
        order_by: Option<&Vec<OrderByClause>>,
    ) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => {
                if let Some(order) = order_by {
                    format!("JSON_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
                } else {
                    format!("JSON_AGG({})", column)
                }
            },
        }
    }

    /// Generate `JSONB_AGG` SQL (PostgreSQL-specific)
    pub(super) fn generate_jsonb_agg_sql(
        &self,
        column: &str,
        order_by: Option<&Vec<OrderByClause>>,
    ) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => {
                if let Some(order) = order_by {
                    format!("JSONB_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
                } else {
                    format!("JSONB_AGG({})", column)
                }
            },
        }
    }

    /// Generate `STRING_AGG` SQL
    pub(super) fn generate_string_agg_sql(
        &self,
        column: &str,
        delimiter: &str,
        order_by: Option<&Vec<OrderByClause>>,
    ) -> String {
        // Escape the delimiter to prevent SQL injection via single-quote sequences.
        // A delimiter like "O'Reilly" would otherwise break out of the string literal.
        let safe_delimiter = self.escape_sql_string(delimiter);
        match self.database_type {
            DatabaseType::PostgreSQL => {
                if let Some(order) = order_by {
                    format!(
                        "STRING_AGG({}, '{}' ORDER BY {})",
                        column,
                        safe_delimiter,
                        self.order_by_to_sql(order)
                    )
                } else {
                    format!("STRING_AGG({}, '{}')", column, safe_delimiter)
                }
            },
        }
    }

    /// Convert ORDER BY clauses to SQL
    pub(super) fn order_by_to_sql(&self, order_by: &[OrderByClause]) -> String {
        order_by
            .iter()
            .map(|clause| {
                #[allow(clippy::match_same_arms)]
                // Reason: non_exhaustive enum requires catch-all; explicit Asc arm documents intent
                let direction = match clause.direction {
                    OrderDirection::Asc => "ASC",
                    OrderDirection::Desc => "DESC",
                    _ => "ASC",
                };
                format!("{} {}", self.quote_identifier(&clause.field), direction)
            })
            .collect::<Vec<_>>()
            .join(", ")
    }

    /// Generate STDDEV SQL (database-specific)
    ///
    /// Database support:
    /// - PostgreSQL: `STDDEV_SAMP()` (default), `STDDEV_POP()` also available
    /// - MySQL: `STDDEV_SAMP()` or `STD()`
    /// - SQLite: Not natively supported (returns NULL or use custom function)
    /// - SQL Server: `STDEV()`
    pub(super) fn generate_stddev_sql(&self, column: &str) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => format!("STDDEV_SAMP({})", column),
        }
    }

    /// Generate VARIANCE SQL (database-specific)
    ///
    /// Database support:
    /// - PostgreSQL: `VAR_SAMP()` (default), `VAR_POP()` also available
    /// - MySQL: `VAR_SAMP()` or `VARIANCE()`
    /// - SQLite: Not natively supported (returns NULL or use custom function)
    /// - SQL Server: `VAR()`
    pub(super) fn generate_variance_sql(&self, column: &str) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => format!("VAR_SAMP({})", column),
        }
    }

    /// Generate `BOOL_AND/BOOL_OR` SQL
    pub(super) fn generate_bool_agg_sql(
        &self,
        column: &str,
        function: crate::compiler::aggregate_types::BoolAggregateFunction,
    ) -> String {
        match self.database_type {
            DatabaseType::PostgreSQL => {
                // PostgreSQL has native BOOL_AND/BOOL_OR
                format!("{}({})", function.sql_name(), column)
            },
        }
    }

    /// Build GROUP BY clause.
    ///
    /// # Errors
    ///
    /// Returns [`FraiseQLError::Validation`] if any GROUP BY expression cannot
    /// be converted to SQL (e.g., unsupported temporal bucket on the current dialect).
    pub(super) fn build_group_by_clause(
        &self,
        group_by_expressions: &[GroupByExpression],
    ) -> Result<String> {
        let mut columns = Vec::new();

        for expr in group_by_expressions {
            let column = self.group_by_expression_to_sql(expr)?;
            columns.push(column);
        }

        Ok(format!("GROUP BY {}", columns.join(", ")))
    }

    /// Build ORDER BY clause.
    ///
    /// All ORDER BY fields are quoted via `quote_identifier`, which produces the correct
    /// dialect syntax (double-quotes, backticks, or brackets).  For native SQL columns,
    /// this quoted identifier resolves to the SELECT alias emitted by `build_select_clause`,
    /// so no JSONB path is ever generated in ORDER BY.
    ///
    /// The `_native_aliases` parameter is accepted for documentation purposes: its presence
    /// makes explicit that native-column aliases are handled correctly by the existing
    /// `quote_identifier` path, and guards against future refactors accidentally introducing
    /// JSONB path generation for ORDER BY fields.
    ///
    /// # Errors
    ///
    /// Currently infallible; reserved for future validation of column references
    /// against the aggregation plan.
    pub(super) fn build_order_by_clause(
        &self,
        order_by: &[OrderByClause],
        _native_aliases: &std::collections::HashSet<&str>,
    ) -> Result<String> {
        let clauses: Vec<String> = order_by
            .iter()
            .map(|clause| {
                #[allow(clippy::match_same_arms)]
                // Reason: non_exhaustive enum requires catch-all; explicit Asc arm documents intent
                let direction = match clause.direction {
                    OrderDirection::Asc => "ASC",
                    OrderDirection::Desc => "DESC",
                    _ => "ASC",
                };
                format!("{} {}", self.quote_identifier(&clause.field), direction)
            })
            .collect();

        Ok(format!("ORDER BY {}", clauses.join(", ")))
    }
}