fraiseql-db 2.2.0

//! SQL Projection Query Generator
//!
//! Generates database-specific SQL for field projection optimization.
//!
//! # Overview
//!
//! When a schema type has a `SqlProjectionHint`, this module generates the actual SQL
//! to project only requested fields at the database level, reducing network payload
//! and JSON deserialization overhead.
//!
//! # Supported Databases
//!
//! - PostgreSQL: Uses `jsonb_build_object()` for efficient field selection
//! - MySQL, SQLite, SQL Server: Multi-database support
//!
//! # Example
//!
//! ```rust
//! use fraiseql_db::projection_generator::PostgresProjectionGenerator;
//! # use fraiseql_error::Result;
//! # fn example() -> Result<()> {
//! let generator = PostgresProjectionGenerator::new();
//! let fields = vec!["id".to_string(), "name".to_string(), "email".to_string()];
//! let sql = generator.generate_projection_sql(&fields)?;
//! assert!(sql.contains("jsonb_build_object"));
//! # Ok(())
//! # }
//! ```

use fraiseql_error::{FraiseQLError, Result};

/// The semantic kind of a projection field, determining which JSONB extraction
/// operator to use in generated SQL.
///
/// - `Text` → `->>` (extracts as text — for String and ID scalars)
/// - `Native` → `->` (preserves native JSON type — Int, Float, Boolean, DateTime, etc.)
/// - `Composite` → `->` (preserves full JSONB structure)
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FieldKind {
    /// Text scalar — extracted with `->>` (String, ID).
    Text,
    /// Native JSON scalar — extracted with `->` to preserve type (Int, Float, Boolean, DateTime,
    /// etc.).
    Native,
    /// Object or list — extracted with `->` to preserve JSONB structure.
    Composite,
}

/// A field in a SQL projection with type information.
///
/// Used by typed projection generators to choose the correct JSONB extraction
/// operator based on [`FieldKind`]: `->` (preserves JSONB) for composites and
/// native scalars, `->>` (text) for text scalars (String, ID).
///
/// When `sub_fields` is populated on a composite field, `generate_typed_projection_sql`
/// will recurse and emit a nested `jsonb_build_object(...)` instead of returning the full
/// composite blob.  Leave `sub_fields` as `None` to get the existing `data->'field'`
/// behaviour (full blob, no sub-selection).
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ProjectionField {
    /// GraphQL field name (camelCase).
    pub name: String,

    /// Semantic kind of the field, controlling the JSONB extraction operator.
    pub kind: FieldKind,

    /// Sub-fields to project for composite (Object) types.
    ///
    /// When `Some` and non-empty, the generator recurses and produces a nested
    /// `jsonb_build_object` instead of returning the entire composite blob.
    /// Set to `None` (or `Some([])`) to fall back to `data->'field'`.
    /// List fields should always use `None` — sub-projection inside aggregated
    /// JSONB arrays is out of scope for this first iteration.
    pub sub_fields: Option<Vec<ProjectionField>>,
}

impl ProjectionField {
    /// Create a text scalar projection field (uses `->>` text extraction).
    ///
    /// Use for String and ID fields only. Other scalars (Int, Float, Boolean,
    /// DateTime, etc.) should use [`Self::native`].
    #[must_use]
    pub fn scalar(name: impl Into<String>) -> Self {
        Self {
            name:       name.into(),
            kind:       FieldKind::Text,
            sub_fields: None,
        }
    }

    /// Create a native JSON scalar projection field (uses `->` to preserve type).
    ///
    /// Use for Int, Float, Boolean, DateTime, Date, Time, Decimal, Vector, and
    /// other non-text scalars. `->>` would coerce these to strings inside
    /// `jsonb_build_object`, losing type information.
    #[must_use]
    pub fn native(name: impl Into<String>) -> Self {
        Self {
            name:       name.into(),
            kind:       FieldKind::Native,
            sub_fields: None,
        }
    }

    /// Create a composite (object/list) projection field (uses `->` JSONB extraction).
    #[must_use]
    pub fn composite(name: impl Into<String>) -> Self {
        Self {
            name:       name.into(),
            kind:       FieldKind::Composite,
            sub_fields: None,
        }
    }

    /// Create a composite projection field with known sub-fields.
    ///
    /// The generator will recurse into `sub_fields` and emit a nested
    /// `jsonb_build_object(...)` rather than returning the full composite blob.
    #[must_use]
    pub fn composite_with_sub_fields(name: impl Into<String>, sub_fields: Vec<Self>) -> Self {
        Self {
            name:       name.into(),
            kind:       FieldKind::Composite,
            sub_fields: Some(sub_fields),
        }
    }

    /// Whether this field is a composite type (Object or List).
    #[must_use]
    pub const fn is_composite(&self) -> bool {
        matches!(self.kind, FieldKind::Composite)
    }
}

impl From<String> for ProjectionField {
    fn from(name: String) -> Self {
        Self::scalar(name)
    }
}

/// Validate that a GraphQL field name contains only characters that are safe
/// for use in SQL projections (alphanumeric characters and underscores only).
///
/// GraphQL field names in FraiseQL are either snake_case (schema definitions)
/// or camelCase (after the compiler's automatic conversion). Both forms are
/// subsets of `[a-zA-Z_][a-zA-Z0-9_]*`, so this function rejects any name
/// that falls outside that alphabet.
///
/// # Errors
///
/// Returns `FraiseQLError::Validation` if `field` contains a character outside
/// `[a-zA-Z0-9_]`.
fn validate_field_name(field: &str) -> Result<()> {
    if field.chars().all(|c| c.is_ascii_alphanumeric() || c == '_') {
        Ok(())
    } else {
        Err(FraiseQLError::Validation {
            message: format!(
                "field name '{}' contains characters that cannot be safely projected; \
                 only ASCII alphanumeric characters and underscores are allowed",
                field
            ),
            path:    None,
        })
    }
}

use crate::utils::to_snake_case;

/// Maximum nesting depth for recursive JSONB projection.
///
/// Prevents pathological schemas from producing unbounded SQL. Fields at depth ≥ this
/// value fall back to `data->'field'` (full composite blob), matching the pre-recursion
/// behaviour.
const MAX_PROJECTION_DEPTH: usize = 4;

/// PostgreSQL SQL projection generator using jsonb_build_object.
///
/// Generates efficient PostgreSQL SQL that projects only requested JSONB fields,
/// reducing payload size and JSON deserialization time.
pub struct PostgresProjectionGenerator {
    /// JSONB column name (typically "data")
    jsonb_column: String,
}

impl PostgresProjectionGenerator {
    /// Create new PostgreSQL projection generator with default JSONB column name.
    ///
    /// Default JSONB column: "data"
    #[must_use]
    pub fn new() -> Self {
        Self::with_column("data")
    }

    /// Create projection generator with custom JSONB column name.
    ///
    /// # Arguments
    ///
    /// * `jsonb_column` - Name of the JSONB column in the database table
    #[must_use]
    pub fn with_column(jsonb_column: &str) -> Self {
        Self {
            jsonb_column: jsonb_column.to_string(),
        }
    }

    /// Generate PostgreSQL projection SQL for specified fields.
    ///
    /// Generates a `jsonb_build_object()` call that selects only the requested fields
    /// from the JSONB column, drastically reducing payload size.
    ///
    /// # Arguments
    ///
    /// * `fields` - GraphQL field names to project from JSONB
    ///
    /// # Returns
    ///
    /// SQL fragment that can be used in a SELECT clause, e.g.:
    /// `jsonb_build_object('id', data->>'id', 'email', data->>'email')`
    ///
    /// # Example
    ///
    /// ```rust
    /// use fraiseql_db::projection_generator::PostgresProjectionGenerator;
    /// # use fraiseql_error::Result;
    /// # fn example() -> Result<()> {
    /// let generator = PostgresProjectionGenerator::new();
    /// let fields = vec!["id".to_string(), "email".to_string()];
    /// let sql = generator.generate_projection_sql(&fields)?;
    /// // Returns:
    /// // jsonb_build_object('id', data->>'id', 'email', data->>'email')
    /// assert!(sql.contains("jsonb_build_object"));
    /// # Ok(())
    /// # }
    /// ```
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name contains characters
    /// that cannot be safely included in a SQL projection.
    pub fn generate_projection_sql(&self, fields: &[String]) -> Result<String> {
        if fields.is_empty() {
            // No fields to project, return pass-through
            return Ok(format!("\"{}\"", self.jsonb_column));
        }

        // Validate all field names before generating any SQL.
        for field in fields {
            validate_field_name(field)?;
        }

        // Build the jsonb_build_object() call with all requested fields
        let field_pairs: Vec<String> = fields
            .iter()
            .map(|field| {
                // Response key uses the GraphQL field name (camelCase).
                // Used as a SQL *string literal* key (inside single-quotes): escape ' → ''.
                let safe_field = Self::escape_sql_string(field);
                // JSONB key uses the original schema field name (snake_case).
                let jsonb_key = to_snake_case(field);
                let safe_jsonb_key = Self::escape_sql_string(&jsonb_key);
                format!("'{}', \"{}\"->>'{}' ", safe_field, self.jsonb_column, safe_jsonb_key)
            })
            .collect();

        // Format: jsonb_build_object('field1', data->>'field1', 'field2', data->>'field2', ...)
        Ok(format!("jsonb_build_object({})", field_pairs.join(",")))
    }

    /// Generate type-aware PostgreSQL projection SQL.
    ///
    /// Uses `->` (JSONB extraction) for composite fields (objects, lists) and
    /// `->>` (text extraction) for scalar fields. This avoids the unnecessary
    /// text→JSON round-trip that occurs when `->>` is used for nested objects.
    ///
    /// When a composite field carries `sub_fields`, the generator recurses and
    /// emits a nested `jsonb_build_object(...)` that selects only the requested
    /// sub-fields rather than returning the entire blob.  Recursion is capped at
    /// [`MAX_PROJECTION_DEPTH`] levels; deeper fields fall back to `data->'field'`.
    ///
    /// # Arguments
    ///
    /// * `fields` - Projection fields with type information
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name contains characters
    /// that cannot be safely included in a SQL projection.
    pub fn generate_typed_projection_sql(&self, fields: &[ProjectionField]) -> Result<String> {
        if fields.is_empty() {
            return Ok(format!("\"{}\"", self.jsonb_column));
        }

        let path = format!("\"{}\"", self.jsonb_column);
        let field_pairs = fields
            .iter()
            .map(|field| Self::render_field(field, &path, 0))
            .collect::<Result<Vec<_>>>()?;

        Ok(format!("jsonb_build_object({})", field_pairs.join(",")))
    }

    /// Recursively render one projection field as a `'key', <expr>` pair for
    /// `jsonb_build_object`.
    ///
    /// * `field` — field to render
    /// * `path`  — JSONB path prefix built so far (e.g. `"data"` at depth 0, `"data"->'author'` at
    ///   depth 1)
    /// * `depth` — current recursion depth (capped at [`MAX_PROJECTION_DEPTH`])
    fn render_field(field: &ProjectionField, path: &str, depth: usize) -> Result<String> {
        let resp_key = Self::escape_sql_string(&field.name);
        let jsonb_key = to_snake_case(&field.name);
        let safe_jsonb_key = Self::escape_sql_string(&jsonb_key);

        // Recurse into Object sub-fields when available and within depth limit.
        if depth < MAX_PROJECTION_DEPTH {
            if let Some(subs) = &field.sub_fields {
                if !subs.is_empty() {
                    let nested_path = format!("{}->'{}'", path, safe_jsonb_key);
                    let inner = subs
                        .iter()
                        .map(|sf| Self::render_field(sf, &nested_path, depth + 1))
                        .collect::<Result<Vec<_>>>()?;
                    return Ok(format!("'{}', jsonb_build_object({})", resp_key, inner.join(",")));
                }
            }
        }

        // Text: ->> (text cast, for String/ID).
        // Native / Composite: -> (preserves native JSONB type).
        let op = if field.kind == FieldKind::Text {
            "->>"
        } else {
            "->"
        };
        Ok(format!("'{}', {}{}'{}'", resp_key, path, op, safe_jsonb_key))
    }

    /// Generate complete SELECT clause with projection for a table.
    ///
    /// # Arguments
    ///
    /// * `table_alias` - Table alias or name in the FROM clause
    /// * `fields` - Fields to project
    ///
    /// # Returns
    ///
    /// Complete SELECT clause, e.g.: `SELECT jsonb_build_object(...) as data`
    ///
    /// # Example
    ///
    /// ```rust
    /// use fraiseql_db::projection_generator::PostgresProjectionGenerator;
    ///
    /// let generator = PostgresProjectionGenerator::new();
    /// let fields = vec!["id".to_string(), "name".to_string()];
    /// let sql = generator.generate_select_clause("t", &fields).unwrap();
    /// assert!(sql.contains("SELECT"));
    /// ```
    ///
    /// # Errors
    ///
    /// Propagates any error from [`Self::generate_projection_sql`].
    pub fn generate_select_clause(&self, table_alias: &str, fields: &[String]) -> Result<String> {
        let projection = self.generate_projection_sql(fields)?;
        Ok(format!(
            "SELECT {} as \"{}\" FROM \"{}\" ",
            projection, self.jsonb_column, table_alias
        ))
    }

    /// Escape a value for use as a SQL *string literal* (inside single quotes).
    ///
    /// Doubles any embedded single-quote (`'` → `''`) to prevent SQL injection
    /// when the field name is embedded as a string literal key, e.g. in
    /// `jsonb_build_object('key', ...)` or `data->>'key'`.
    fn escape_sql_string(s: &str) -> String {
        s.replace('\'', "''")
    }

    /// Escape a SQL identifier using PostgreSQL double-quote quoting.
    ///
    /// Double-quote delimiters prevent identifier injection: any `"` within
    /// the identifier is doubled (`""`), and the whole name is wrapped in `"`.
    /// Use this when the name appears in an *identifier* position (column name,
    /// table alias) rather than as a string literal.
    #[allow(dead_code)] // Reason: available for callers embedding names as SQL identifiers
    fn escape_identifier(field: &str) -> String {
        format!("\"{}\"", field.replace('"', "\"\""))
    }
}

impl Default for PostgresProjectionGenerator {
    fn default() -> Self {
        Self::new()
    }
}

/// MySQL SQL projection generator.
///
/// MySQL uses `JSON_OBJECT()` for field projection, similar to PostgreSQL's `jsonb_build_object()`.
/// Generates efficient SQL that projects only requested JSON fields.
///
/// # Example
///
/// ```
/// use fraiseql_db::projection_generator::MySqlProjectionGenerator;
///
/// let generator = MySqlProjectionGenerator::new();
/// let fields = vec!["id".to_string(), "name".to_string()];
/// let sql = generator.generate_projection_sql(&fields).unwrap();
/// assert!(sql.contains("JSON_OBJECT"));
/// ```
pub struct MySqlProjectionGenerator {
    json_column: String,
}

impl MySqlProjectionGenerator {
    /// Create new MySQL projection generator with default JSON column name.
    ///
    /// Default JSON column: "data"
    #[must_use]
    pub fn new() -> Self {
        Self::with_column("data")
    }

    /// Create projection generator with custom JSON column name.
    ///
    /// # Arguments
    ///
    /// * `json_column` - Name of the JSON column in the database table
    #[must_use]
    pub fn with_column(json_column: &str) -> Self {
        Self {
            json_column: json_column.to_string(),
        }
    }

    /// Generate MySQL projection SQL for specified fields.
    ///
    /// Generates a `JSON_OBJECT()` call that selects only the requested fields
    /// from the JSON column.
    ///
    /// # Arguments
    ///
    /// * `fields` - JSON field names to project
    ///
    /// # Returns
    ///
    /// SQL fragment that can be used in a SELECT clause
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name cannot be safely projected.
    pub fn generate_projection_sql(&self, fields: &[String]) -> Result<String> {
        if fields.is_empty() {
            return Ok(format!("`{}`", self.json_column));
        }

        // Validate all field names before generating any SQL.
        for field in fields {
            validate_field_name(field)?;
        }

        let field_pairs: Vec<String> = fields
            .iter()
            .map(|field| {
                // Response key used as SQL string literal key — escape ' → ''.
                let safe_field = Self::escape_sql_string(field);
                // JSON key uses the original schema field name (snake_case).
                let json_key = to_snake_case(field);
                format!("'{}', JSON_EXTRACT(`{}`, '$.{}')", safe_field, self.json_column, json_key)
            })
            .collect();

        Ok(format!("JSON_OBJECT({})", field_pairs.join(",")))
    }

    /// Escape a value for use as a SQL *string literal* (inside single quotes).
    fn escape_sql_string(s: &str) -> String {
        s.replace('\'', "''")
    }

    /// Escape a SQL identifier using MySQL backtick quoting.
    ///
    /// Use this when the name appears in an *identifier* position (column name,
    /// table alias), not as a string literal.
    #[allow(dead_code)] // Reason: available for callers embedding names as SQL identifiers
    fn escape_identifier(field: &str) -> String {
        format!("`{}`", field.replace('`', "``"))
    }
}

impl Default for MySqlProjectionGenerator {
    fn default() -> Self {
        Self::new()
    }
}

/// SQLite SQL projection generator.
///
/// SQLite's JSON support is more limited than PostgreSQL and MySQL.
/// Uses `json_object()` with `json_extract()` to project fields.
///
/// # Example
///
/// ```
/// use fraiseql_db::projection_generator::SqliteProjectionGenerator;
///
/// let generator = SqliteProjectionGenerator::new();
/// let fields = vec!["id".to_string(), "name".to_string()];
/// let sql = generator.generate_projection_sql(&fields).unwrap();
/// assert!(sql.contains("json_object"));
/// ```
pub struct SqliteProjectionGenerator {
    json_column: String,
}

impl SqliteProjectionGenerator {
    /// Create new SQLite projection generator with default JSON column name.
    ///
    /// Default JSON column: "data"
    #[must_use]
    pub fn new() -> Self {
        Self::with_column("data")
    }

    /// Create projection generator with custom JSON column name.
    ///
    /// # Arguments
    ///
    /// * `json_column` - Name of the JSON column in the database table
    #[must_use]
    pub fn with_column(json_column: &str) -> Self {
        Self {
            json_column: json_column.to_string(),
        }
    }

    /// Generate SQLite projection SQL for specified fields.
    ///
    /// Generates a `json_object()` call that selects only the requested fields.
    ///
    /// # Arguments
    ///
    /// * `fields` - JSON field names to project
    ///
    /// # Returns
    ///
    /// SQL fragment that can be used in a SELECT clause
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name cannot be safely projected.
    pub fn generate_projection_sql(&self, fields: &[String]) -> Result<String> {
        if fields.is_empty() {
            return Ok(format!("\"{}\"", self.json_column));
        }

        // Validate all field names before generating any SQL.
        for field in fields {
            validate_field_name(field)?;
        }

        let field_pairs: Vec<String> = fields
            .iter()
            .map(|field| {
                // Response key used as SQL string literal key — escape ' → ''.
                let safe_field = Self::escape_sql_string(field);
                // JSON key uses the original schema field name (snake_case).
                let json_key = to_snake_case(field);
                format!(
                    "'{}', json_extract(\"{}\", '$.{}')",
                    safe_field, self.json_column, json_key
                )
            })
            .collect();

        Ok(format!("json_object({})", field_pairs.join(",")))
    }

    /// Escape a value for use as a SQL *string literal* (inside single quotes).
    fn escape_sql_string(s: &str) -> String {
        s.replace('\'', "''")
    }

    /// Escape a SQL identifier using SQLite double-quote quoting.
    ///
    /// Double-quote delimiters prevent identifier injection: any `"` within
    /// the identifier is doubled (`""`), and the whole name is wrapped in `"`.
    /// Use this when the name appears in an *identifier* position (column name,
    /// table alias), not as a string literal.
    #[allow(dead_code)] // Reason: available for callers that embed field names as identifiers
    fn escape_identifier(field: &str) -> String {
        format!("\"{}\"", field.replace('"', "\"\""))
    }
}

impl Default for SqliteProjectionGenerator {
    fn default() -> Self {
        Self::new()
    }
}

#[cfg(test)]
#[allow(clippy::unwrap_used)] // Reason: test code, panics are acceptable
mod tests {
    use super::*;

    #[test]
    fn test_postgres_projection_single_field() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "jsonb_build_object('id', \"data\"->>'id' )");
    }

    #[test]
    fn test_postgres_projection_multiple_fields() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["id".to_string(), "name".to_string(), "email".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert!(sql.contains("jsonb_build_object("));
        assert!(sql.contains("'id', \"data\"->>'id'"));
        assert!(sql.contains("'name', \"data\"->>'name'"));
        assert!(sql.contains("'email', \"data\"->>'email'"));
    }

    #[test]
    fn test_postgres_projection_empty_fields() {
        let generator = PostgresProjectionGenerator::new();
        let fields: Vec<String> = vec![];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        // Empty projection should pass through the JSONB column
        assert_eq!(sql, "\"data\"");
    }

    #[test]
    fn test_postgres_projection_custom_column() {
        let generator = PostgresProjectionGenerator::with_column("metadata");
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "jsonb_build_object('id', \"metadata\"->>'id' )");
    }

    #[test]
    fn test_postgres_select_clause() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["id".to_string(), "name".to_string()];

        let sql = generator.generate_select_clause("users", &fields).unwrap();
        assert!(sql.starts_with("SELECT jsonb_build_object("));
        assert!(sql.contains("as \"data\""));
        assert!(sql.contains("FROM \"users\""));
    }

    #[test]
    fn test_escape_identifier_quoting() {
        // Simple identifiers are wrapped in double-quotes.
        assert_eq!(PostgresProjectionGenerator::escape_identifier("id"), "\"id\"");
        assert_eq!(PostgresProjectionGenerator::escape_identifier("user_id"), "\"user_id\"");
        // Special chars (hyphens, dots) are safe inside quotes.
        assert_eq!(PostgresProjectionGenerator::escape_identifier("field-name"), "\"field-name\"");
        assert_eq!(PostgresProjectionGenerator::escape_identifier("field.name"), "\"field.name\"");
        // Double-quote chars inside the name are doubled.
        assert_eq!(
            PostgresProjectionGenerator::escape_identifier("col\"inject"),
            "\"col\"\"inject\""
        );
    }

    // MySQL Projection Generator Tests
    #[test]
    fn test_mysql_projection_single_field() {
        let generator = MySqlProjectionGenerator::new();
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "JSON_OBJECT('id', JSON_EXTRACT(`data`, '$.id'))");
    }

    #[test]
    fn test_mysql_projection_multiple_fields() {
        let generator = MySqlProjectionGenerator::new();
        let fields = vec!["id".to_string(), "name".to_string(), "email".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert!(sql.contains("JSON_OBJECT("));
        assert!(sql.contains("'id', JSON_EXTRACT(`data`, '$.id')"));
        assert!(sql.contains("'name', JSON_EXTRACT(`data`, '$.name')"));
        assert!(sql.contains("'email', JSON_EXTRACT(`data`, '$.email')"));
    }

    #[test]
    fn test_mysql_projection_empty_fields() {
        let generator = MySqlProjectionGenerator::new();
        let fields: Vec<String> = vec![];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "`data`");
    }

    #[test]
    fn test_mysql_projection_custom_column() {
        let generator = MySqlProjectionGenerator::with_column("metadata");
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "JSON_OBJECT('id', JSON_EXTRACT(`metadata`, '$.id'))");
    }

    // SQLite Projection Generator Tests
    #[test]
    fn test_sqlite_projection_single_field() {
        let generator = SqliteProjectionGenerator::new();
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "json_object('id', json_extract(\"data\", '$.id'))");
    }

    #[test]
    fn test_sqlite_projection_multiple_fields() {
        let generator = SqliteProjectionGenerator::new();
        let fields = vec!["id".to_string(), "name".to_string(), "email".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert!(sql.contains("json_object("));
        assert!(sql.contains("'id', json_extract(\"data\", '$.id')"));
        assert!(sql.contains("'name', json_extract(\"data\", '$.name')"));
        assert!(sql.contains("'email', json_extract(\"data\", '$.email')"));
    }

    #[test]
    fn test_sqlite_projection_empty_fields() {
        let generator = SqliteProjectionGenerator::new();
        let fields: Vec<String> = vec![];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "\"data\"");
    }

    #[test]
    fn test_sqlite_projection_custom_column() {
        let generator = SqliteProjectionGenerator::with_column("metadata");
        let fields = vec!["id".to_string()];

        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert_eq!(sql, "json_object('id', json_extract(\"metadata\", '$.id'))");
    }

    // ========================================================================
    // Issue #269: JSONB field extraction with snake_case/camelCase mapping
    // ========================================================================

    #[test]
    fn test_to_snake_case_conversion() {
        // Test camelCase to snake_case conversion
        assert_eq!(super::to_snake_case("id"), "id");
        assert_eq!(super::to_snake_case("firstName"), "first_name");
        assert_eq!(super::to_snake_case("createdAt"), "created_at");
        assert_eq!(super::to_snake_case("userId"), "user_id");
        assert_eq!(super::to_snake_case("updatedAtTimestamp"), "updated_at_timestamp");
    }

    #[test]
    fn test_postgres_projection_with_field_mapping_snake_case() {
        // Problem: GraphQL converts field names to camelCase (first_name → firstName)
        // But JSONB stores them in snake_case (first_name).
        // When generating JSONB extraction SQL, we must use the original snake_case key,
        // not the camelCase GraphQL name.

        let generator = PostgresProjectionGenerator::new();

        // Simulate what happens when fields come from GraphQL query
        // These are camelCase field names (what GraphQL expects in response)
        let graphql_fields = vec![
            "id".to_string(),
            "firstName".to_string(),
            "createdAt".to_string(),
        ];

        let sql = generator.generate_projection_sql(&graphql_fields).unwrap();

        eprintln!("Generated SQL: {}", sql);

        // Current broken behavior generates:
        // jsonb_build_object('id', data->>'id', 'firstName', data->>'firstName', 'createdAt',
        // data->>'createdAt')
        //
        // This fails because JSONB has snake_case keys: first_name, created_at
        // Result: data->>'firstName' returns NULL (key not found)

        // Regression guard: SQL must use snake_case keys for JSONB access.
        // camelCase field names in the schema (firstName, createdAt) must be
        // mapped to snake_case in generated SQL (first_name, created_at) because
        // PostgreSQL stores JSONB keys verbatim and FraiseQL always writes snake_case.
        assert!(
            !sql.contains("->>'firstName'") && !sql.contains("->>'createdAt'"),
            "Regression: SQL is using camelCase keys for JSONB access. \
             JSONB has snake_case keys ('first_name', 'created_at'). SQL: {}",
            sql
        );
    }

    // =========================================================================
    // Additional projection_generator.rs tests
    // =========================================================================

    #[test]
    fn test_postgres_projection_sql_injection_in_field_name() {
        // A field name containing a single quote is rejected by the validator — it is
        // not a valid GraphQL / FraiseQL field identifier and must never reach SQL.
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["user'name".to_string()];
        let result = generator.generate_projection_sql(&fields);
        assert!(result.is_err(), "Field name with single quote must be rejected");
    }

    #[test]
    fn test_postgres_projection_rejects_field_with_semicolon() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["id; DROP TABLE users--".to_string()];
        let result = generator.generate_projection_sql(&fields);
        assert!(result.is_err(), "Field name with SQL injection characters must be rejected");
    }

    #[test]
    fn test_mysql_projection_rejects_unsafe_field_name() {
        let generator = MySqlProjectionGenerator::new();
        let fields = vec!["field`hack".to_string()];
        let result = generator.generate_projection_sql(&fields);
        assert!(result.is_err(), "Field name with backtick must be rejected");
    }

    #[test]
    fn test_sqlite_projection_rejects_unsafe_field_name() {
        let generator = SqliteProjectionGenerator::new();
        let fields = vec!["field\"inject".to_string()];
        let result = generator.generate_projection_sql(&fields);
        assert!(result.is_err(), "Field name with double-quote must be rejected");
    }

    #[test]
    fn test_validate_field_name_accepts_valid_names() {
        assert!(super::validate_field_name("id").is_ok());
        assert!(super::validate_field_name("user_id").is_ok());
        assert!(super::validate_field_name("firstName").is_ok());
        assert!(super::validate_field_name("createdAt").is_ok());
        assert!(super::validate_field_name("field123").is_ok());
        assert!(super::validate_field_name("_private").is_ok());
    }

    #[test]
    fn test_validate_field_name_rejects_unsafe_chars() {
        assert!(super::validate_field_name("user'name").is_err());
        assert!(super::validate_field_name("field-name").is_err());
        assert!(super::validate_field_name("field.name").is_err());
        assert!(super::validate_field_name("field;inject").is_err());
        assert!(super::validate_field_name("field\"inject").is_err());
        assert!(super::validate_field_name("field`hack").is_err());
    }

    #[test]
    fn test_mysql_projection_sql_contains_json_object() {
        let generator = MySqlProjectionGenerator::new();
        let fields = vec!["email".to_string(), "name".to_string()];
        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert!(sql.starts_with("JSON_OBJECT("), "MySQL projection must start with JSON_OBJECT");
    }

    #[test]
    fn test_sqlite_projection_custom_column_appears_in_sql() {
        let generator = SqliteProjectionGenerator::with_column("payload");
        let fields = vec!["id".to_string()];
        let sql = generator.generate_projection_sql(&fields).unwrap();
        assert!(sql.contains("\"payload\""), "Custom column name must appear in SQLite SQL");
    }

    #[test]
    fn test_postgres_projection_camel_to_snake_in_jsonb_key() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["updatedAt".to_string()];
        let sql = generator.generate_projection_sql(&fields).unwrap();
        // The JSONB extraction key should be snake_case
        assert!(
            sql.contains("'updated_at'"),
            "updatedAt must be mapped to updated_at for JSONB key"
        );
        // The response key in jsonb_build_object should be the original camelCase
        assert!(sql.contains("'updatedAt'"), "Response key must remain camelCase");
    }

    #[test]
    fn test_postgres_select_clause_contains_from() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec!["id".to_string()];
        let sql = generator.generate_select_clause("orders", &fields).unwrap();
        assert!(
            sql.contains("FROM \"orders\""),
            "SELECT clause must include FROM clause with table name"
        );
        assert!(sql.contains("SELECT"), "SELECT clause must start with SELECT");
    }

    // ── generate_typed_projection_sql tests (C12) ─────────────────────────

    #[test]
    fn test_typed_projection_empty_fields_returns_data_column() {
        let generator = PostgresProjectionGenerator::new();
        let result = generator.generate_typed_projection_sql(&[]).unwrap();
        assert_eq!(result, "\"data\"");
    }

    #[test]
    fn test_typed_projection_text_field_uses_text_extraction() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![ProjectionField::scalar("name")];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Text fields use ->> (text extraction)
        assert!(sql.contains("->>'name'"), "text field must use ->> operator, got: {sql}");
        assert!(!sql.contains("->'name'"), "text field must NOT use -> operator, got: {sql}");
    }

    #[test]
    fn test_typed_projection_composite_field_uses_jsonb_extraction() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![ProjectionField::composite("address")];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Composite fields with no sub_fields use -> (full JSONB blob)
        assert!(sql.contains("->'address'"), "composite field must use -> operator, got: {sql}");
    }

    #[test]
    fn test_typed_projection_mixed_text_native_and_composite() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![
            ProjectionField::scalar("id"),
            ProjectionField::native("age"),
            ProjectionField::composite("address"),
            ProjectionField::composite("tags"),
            ProjectionField::scalar("email"),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();

        // Text scalars use ->>
        assert!(sql.contains("->>'id'"), "id (text) must use ->>, got: {sql}");
        assert!(sql.contains("->>'email'"), "email (text) must use ->>, got: {sql}");

        // Native scalars use ->
        assert!(sql.contains("->'age'"), "age (native) must use ->, got: {sql}");

        // Composites use ->
        assert!(sql.contains("->'address'"), "address (composite) must use ->, got: {sql}");
        assert!(sql.contains("->'tags'"), "tags (composite) must use ->, got: {sql}");

        // Must be wrapped in jsonb_build_object
        assert!(
            sql.starts_with("jsonb_build_object("),
            "must wrap in jsonb_build_object, got: {sql}"
        );
    }

    #[test]
    fn test_typed_projection_camel_case_maps_to_snake_case_jsonb_key() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![ProjectionField::scalar("firstName")];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Response key is camelCase, JSONB key is snake_case
        assert!(
            sql.contains("'firstName'"),
            "response key must be camelCase 'firstName', got: {sql}"
        );
        assert!(
            sql.contains("->>'first_name'"),
            "JSONB key must be snake_case 'first_name', got: {sql}"
        );
    }

    #[test]
    fn test_typed_projection_single_quote_in_field_name_escaped() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![ProjectionField::scalar("it's")];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Single quotes must be doubled for SQL safety
        assert!(
            sql.contains("'it''s'"),
            "single quote in field name must be escaped, got: {sql}"
        );
    }

    // ── Native field extraction tests (issue #197 / #202) ──────────────────────

    #[test]
    fn test_native_field_uses_jsonb_extraction() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![
            ProjectionField::native("isActive"),
            ProjectionField::scalar("name"),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Native: -> (not ->>) to preserve native JSON type (boolean, int, etc.)
        assert!(sql.contains("->'is_active'"), "native field must use -> operator, got: {sql}");
        // Text scalar still uses ->>
        assert!(sql.contains("->>'name'"), "text scalar field must use ->> operator, got: {sql}");
    }

    #[test]
    fn test_native_field_mixed_with_composite() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![
            ProjectionField::native("isActive"),
            ProjectionField::composite("address"),
            ProjectionField::scalar("email"),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        assert!(sql.contains("->'is_active'"), "native uses ->, got: {sql}");
        assert!(sql.contains("->'address'"), "composite uses ->, got: {sql}");
        assert!(sql.contains("->>'email'"), "text scalar uses ->>, got: {sql}");
    }

    #[test]
    fn test_native_int_and_float_use_jsonb_extraction() {
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![
            ProjectionField::native("age"),
            ProjectionField::native("price"),
            ProjectionField::scalar("name"),
            ProjectionField::scalar("id"),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // Native scalars (Int, Float) use ->
        assert!(sql.contains("->'age'"), "int (native) must use ->, got: {sql}");
        assert!(sql.contains("->'price'"), "float (native) must use ->, got: {sql}");
        // Text scalars (String, ID) use ->>
        assert!(sql.contains("->>'name'"), "string (text) must use ->>, got: {sql}");
        assert!(sql.contains("->>'id'"), "id (text) must use ->>, got: {sql}");
    }

    // ── Deep nested projection tests (issue #189) ─────────────────────────────

    #[test]
    fn test_typed_projection_nested_sub_fields_generate_nested_jsonb_build_object() {
        let generator = PostgresProjectionGenerator::new();
        // Simulate: comments { id content author { id username fullName } }
        let fields = vec![
            ProjectionField::scalar("id"),
            ProjectionField::scalar("content"),
            ProjectionField::composite_with_sub_fields(
                "author",
                vec![
                    ProjectionField::scalar("id"),
                    ProjectionField::scalar("username"),
                    ProjectionField::scalar("fullName"),
                ],
            ),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        // author must use a nested jsonb_build_object instead of the full blob
        assert!(
            sql.contains("'author', jsonb_build_object("),
            "author must produce nested jsonb_build_object, got: {sql}"
        );
        // Nested scalars must use path operator with full path prefix
        assert!(
            sql.contains("'author'->>'id'"),
            "nested 'id' must use path \"data\"->'author'->>'id', got: {sql}"
        );
        assert!(
            sql.contains("'author'->>'username'"),
            "nested 'username' must use correct path, got: {sql}"
        );
        // camelCase sub-field must map to snake_case key
        assert!(
            sql.contains("->>'full_name'"),
            "fullName sub-field must map to snake_case 'full_name', got: {sql}"
        );
        // Root scalars still use top-level path
        assert!(
            sql.contains("\"data\"->>'id'"),
            "root id must use top-level data path, got: {sql}"
        );
    }

    #[test]
    fn test_typed_projection_composite_without_sub_fields_returns_full_blob() {
        // When sub_fields is None, fall back to data->'field' (no regression)
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![ProjectionField::composite("author")];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        assert!(
            sql.contains("\"data\"->'author'"),
            "composite without sub_fields must return full blob, got: {sql}"
        );
        // The outer jsonb_build_object wraps all fields — that's expected.
        // What must NOT appear is a *nested* jsonb_build_object as the value for 'author'.
        assert!(
            !sql.contains("'author', jsonb_build_object("),
            "must NOT produce nested jsonb_build_object for author when sub_fields is None, got: {sql}"
        );
    }

    #[test]
    fn test_typed_projection_depth_2_recursion() {
        // Three levels: post → author → profile
        let generator = PostgresProjectionGenerator::new();
        let fields = vec![
            ProjectionField::scalar("id"),
            ProjectionField::composite_with_sub_fields(
                "author",
                vec![
                    ProjectionField::scalar("id"),
                    ProjectionField::composite_with_sub_fields(
                        "profile",
                        vec![ProjectionField::scalar("bio")],
                    ),
                ],
            ),
        ];
        let sql = generator.generate_typed_projection_sql(&fields).unwrap();
        assert!(
            sql.contains("'author', jsonb_build_object("),
            "author must be nested, got: {sql}"
        );
        assert!(
            sql.contains("'profile', jsonb_build_object("),
            "profile must be nested inside author, got: {sql}"
        );
        assert!(sql.contains("'profile'->>'bio'"), "bio must use depth-2 path, got: {sql}");
    }
}