polyglot_sql/dialects/

snowflake.rs

//! Snowflake Dialect
//!
//! Snowflake-specific transformations based on sqlglot patterns.
//! Key differences:
//! - TRY_ prefix for safe operations (TRY_CAST, TRY_TO_NUMBER)
//! - FLATTEN for unnesting arrays
//! - QUALIFY clause support
//! - ARRAY_CONSTRUCT, OBJECT_CONSTRUCT for arrays/objects
//! - Variant type handling
//! - Default case-insensitive identifiers (unquoted)

use super::{DialectImpl, DialectType};
#[cfg(feature = "transpile")]
use crate::error::Result;
#[cfg(feature = "transpile")]
use crate::expressions::{
    AggFunc, BinaryOp, Cast, CeilFunc, DataType, Expression, Function, IntervalUnit, ListAggFunc,
    Literal, UnaryFunc, VarArgFunc,
};
#[cfg(feature = "generate")]
use crate::generator::GeneratorConfig;
use crate::tokens::TokenizerConfig;

/// Convert IntervalUnit to string for Snowflake syntax
#[cfg(feature = "transpile")]
fn interval_unit_to_str(unit: &IntervalUnit) -> String {
    match unit {
        IntervalUnit::Year => "YEAR".to_string(),
        IntervalUnit::Quarter => "QUARTER".to_string(),
        IntervalUnit::Month => "MONTH".to_string(),
        IntervalUnit::Week => "WEEK".to_string(),
        IntervalUnit::Day => "DAY".to_string(),
        IntervalUnit::Hour => "HOUR".to_string(),
        IntervalUnit::Minute => "MINUTE".to_string(),
        IntervalUnit::Second => "SECOND".to_string(),
        IntervalUnit::Millisecond => "MILLISECOND".to_string(),
        IntervalUnit::Microsecond => "MICROSECOND".to_string(),
        IntervalUnit::Nanosecond => "NANOSECOND".to_string(),
    }
}

/// Snowflake dialect
pub struct SnowflakeDialect;

impl DialectImpl for SnowflakeDialect {
    fn dialect_type(&self) -> DialectType {
        DialectType::Snowflake
    }

    fn tokenizer_config(&self) -> TokenizerConfig {
        let mut config = TokenizerConfig::default();
        // Snowflake uses double quotes for identifiers
        config.identifiers.insert('"', '"');
        // Snowflake supports $$ string literals
        config.quotes.insert("$$".to_string(), "$$".to_string());
        // Snowflake accepts both doubled quotes and backslash escape sequences.
        config.string_escapes.push('\\');
        // Unknown backslash sequences discard the backslash. Listing the supported
        // followers also prevents MySQL-only escapes from being decoded here.
        config.escape_follow_chars = vec![
            '\'', '"', '\\', 'b', 'f', 'n', 'r', 't', '0', '1', '2', '3', '4', '5', '6', '7', 'x',
            'u',
        ];
        // Snowflake does NOT support nested comments (per Python sqlglot)
        config.nested_comments = false;
        // Snowflake supports // as single-line comments (in addition to --)
        config.comments.insert("//".to_string(), None);
        config
    }

    #[cfg(feature = "generate")]

    fn generator_config(&self) -> GeneratorConfig {
        use crate::generator::IdentifierQuoteStyle;
        GeneratorConfig {
            identifier_quote: '"',
            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
            dialect: Some(DialectType::Snowflake),
            // Snowflake-specific settings from Python sqlglot
            parameter_token: "$",
            matched_by_source: false,
            single_string_interval: true,
            join_hints: false,
            table_hints: false,
            query_hints: false,
            aggregate_filter_supported: false,
            supports_table_copy: false,
            collate_is_func: true,
            limit_only_literals: true,
            json_key_value_pair_sep: ",",
            insert_overwrite: " OVERWRITE INTO",
            struct_delimiter: ("(", ")"),
            copy_params_are_wrapped: false,
            copy_params_eq_required: true,
            star_except: "EXCLUDE",
            supports_exploding_projections: false,
            array_concat_is_var_len: false,
            supports_convert_timezone: true,
            except_intersect_support_all_clause: false,
            supports_median: true,
            array_size_name: "ARRAY_SIZE",
            supports_decode_case: true,
            is_bool_allowed: false,
            // Snowflake supports TRY_ prefix operations
            try_supported: true,
            // Snowflake supports NVL2
            nvl2_supported: true,
            // Snowflake uses FLATTEN for unnest
            unnest_with_ordinality: false,
            // Snowflake uses space before paren: ALL (subquery)
            quantified_no_paren_space: false,
            // Snowflake uses bracket-only array syntax: [1, 2, 3]
            array_bracket_only: true,
            ..Default::default()
        }
    }

    #[cfg(feature = "transpile")]

    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
        match expr {
            // ===== Data Type Mappings =====
            Expression::DataType(dt) => self.transform_data_type(dt),

            // ===== NOT IN transformation =====
            // Snowflake treats `value NOT IN (subquery)` as `VALUE <> ALL (subquery)`
            // See: https://docs.snowflake.com/en/sql-reference/functions/in
            Expression::In(in_expr) if in_expr.not && in_expr.query.is_some() => {
                // Transform NOT IN (subquery) -> <> ALL (subquery)
                let inner = in_expr.query.unwrap();
                // Wrap in Subquery so generator outputs ALL (subquery) with space
                let subquery = Expression::Subquery(Box::new(crate::expressions::Subquery {
                    this: inner,
                    alias: None,
                    column_aliases: Vec::new(),
                    alias_explicit_as: false,
                    alias_keyword: None,
                    order_by: None,
                    limit: None,
                    offset: None,
                    distribute_by: None,
                    sort_by: None,
                    cluster_by: None,
                    lateral: false,
                    modifiers_inside: false,
                    trailing_comments: Vec::new(),
                    inferred_type: None,
                }));
                Ok(Expression::All(Box::new(
                    crate::expressions::QuantifiedExpr {
                        this: in_expr.this,
                        subquery,
                        op: Some(crate::expressions::QuantifiedOp::Neq),
                    },
                )))
            }

            // NOT IN (values) -> NOT x IN (values)
            Expression::In(in_expr) if in_expr.not => {
                // Transform NOT x IN (values) by wrapping the In expression with not=false inside a Not
                let in_without_not = crate::expressions::In {
                    this: in_expr.this,
                    expressions: in_expr.expressions,
                    query: in_expr.query,
                    not: false,
                    global: in_expr.global,
                    unnest: in_expr.unnest,
                    is_field: in_expr.is_field,
                };
                Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
                    this: Expression::In(Box::new(in_without_not)),
                    inferred_type: None,
                })))
            }

            // ===== Interval unit expansion =====
            // Expand abbreviated units in interval string values (e.g., '1 w' -> '1 WEEK')
            Expression::Interval(interval) => self.transform_interval(*interval),

            // ===== Null handling =====
            // IFNULL -> COALESCE (both work in Snowflake, but COALESCE is standard)
            Expression::IfNull(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
                original_name: None,
                expressions: vec![f.this, f.expression],
                inferred_type: None,
            }))),

            // NVL -> COALESCE (both work in Snowflake, but COALESCE is standard)
            Expression::Nvl(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
                original_name: None,
                expressions: vec![f.this, f.expression],
                inferred_type: None,
            }))),

            // Coalesce with original_name (e.g., IFNULL parsed as Coalesce) -> clear original_name
            Expression::Coalesce(mut f) => {
                f.original_name = None;
                Ok(Expression::Coalesce(f))
            }

            // GROUP_CONCAT -> LISTAGG in Snowflake
            Expression::GroupConcat(f) => Ok(Expression::ListAgg(Box::new(ListAggFunc {
                source_dialect: None,
                this: f.this,
                separator: f.separator,
                on_overflow: None,
                max_length: None,
                order_by: f.order_by,
                distinct: f.distinct,
                filter: f.filter,
                inferred_type: None,
            }))),

            // ===== Cast operations =====
            // CAST(x AS GEOGRAPHY) -> TO_GEOGRAPHY(x)
            // CAST(x AS GEOMETRY) -> TO_GEOMETRY(x)
            Expression::Cast(c) => {
                use crate::expressions::DataType;
                // First, recursively transform the inner expression
                let transformed_this = self.transform_expr(c.this)?;
                match &c.to {
                    DataType::Geography { .. } => Ok(Expression::Function(Box::new(
                        Function::new("TO_GEOGRAPHY".to_string(), vec![transformed_this]),
                    ))),
                    DataType::Geometry { .. } => Ok(Expression::Function(Box::new(Function::new(
                        "TO_GEOMETRY".to_string(),
                        vec![transformed_this],
                    )))),
                    _ => {
                        // Transform the data type
                        let transformed_dt = match self.transform_data_type(c.to.clone())? {
                            Expression::DataType(dt) => dt,
                            _ => c.to.clone(),
                        };
                        Ok(Expression::Cast(Box::new(Cast {
                            this: transformed_this,
                            to: transformed_dt,
                            double_colon_syntax: false, // Normalize :: to CAST()
                            trailing_comments: c.trailing_comments,
                            format: c.format,
                            default: c.default,
                            inferred_type: None,
                        })))
                    }
                }
            }

            // TryCast stays as TryCast (Snowflake supports TRY_CAST)
            // Recursively transform the inner expression
            Expression::TryCast(c) => {
                let transformed_this = self.transform_expr(c.this)?;
                Ok(Expression::TryCast(Box::new(Cast {
                    this: transformed_this,
                    to: c.to,
                    double_colon_syntax: false, // Normalize :: to CAST()
                    trailing_comments: c.trailing_comments,
                    format: c.format,
                    default: c.default,
                    inferred_type: None,
                })))
            }

            // SafeCast -> Cast in Snowflake (Snowflake CAST is safe by default)
            // Also convert TIMESTAMP to TIMESTAMPTZ (BigQuery TIMESTAMP = tz-aware)
            Expression::SafeCast(c) => {
                let to = match c.to {
                    DataType::Timestamp { .. } => DataType::Custom {
                        name: "TIMESTAMPTZ".to_string(),
                    },
                    DataType::Custom { name } if name.eq_ignore_ascii_case("TIMESTAMP") => {
                        DataType::Custom {
                            name: "TIMESTAMPTZ".to_string(),
                        }
                    }
                    other => other,
                };
                let transformed_this = self.transform_expr(c.this)?;
                Ok(Expression::Cast(Box::new(Cast {
                    this: transformed_this,
                    to,
                    double_colon_syntax: c.double_colon_syntax,
                    trailing_comments: c.trailing_comments,
                    format: c.format,
                    default: c.default,
                    inferred_type: None,
                })))
            }

            // ===== Typed Literals -> CAST =====
            // TIMESTAMP '...' -> CAST('...' AS TIMESTAMP)
            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Timestamp(_)) => {
                let Literal::Timestamp(s) = lit.as_ref() else {
                    unreachable!()
                };
                Ok(Expression::Cast(Box::new(Cast {
                    this: Expression::Literal(Box::new(Literal::String(s.clone()))),
                    to: DataType::Timestamp {
                        precision: None,
                        timezone: false,
                    },
                    double_colon_syntax: false,
                    trailing_comments: Vec::new(),
                    format: None,
                    default: None,
                    inferred_type: None,
                })))
            }

            // DATE '...' -> CAST('...' AS DATE)
            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Date(_)) => {
                let Literal::Date(s) = lit.as_ref() else {
                    unreachable!()
                };
                Ok(Expression::Cast(Box::new(Cast {
                    this: Expression::Literal(Box::new(Literal::String(s.clone()))),
                    to: DataType::Date,
                    double_colon_syntax: false,
                    trailing_comments: Vec::new(),
                    format: None,
                    default: None,
                    inferred_type: None,
                })))
            }

            // TIME '...' -> CAST('...' AS TIME)
            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Time(_)) => {
                let Literal::Time(s) = lit.as_ref() else {
                    unreachable!()
                };
                Ok(Expression::Cast(Box::new(Cast {
                    this: Expression::Literal(Box::new(Literal::String(s.clone()))),
                    to: DataType::Time {
                        precision: None,
                        timezone: false,
                    },
                    double_colon_syntax: false,
                    trailing_comments: Vec::new(),
                    format: None,
                    default: None,
                    inferred_type: None,
                })))
            }

            // DATETIME '...' -> CAST('...' AS DATETIME)
            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Datetime(_)) => {
                let Literal::Datetime(s) = lit.as_ref() else {
                    unreachable!()
                };
                Ok(Expression::Cast(Box::new(Cast {
                    this: Expression::Literal(Box::new(Literal::String(s.clone()))),
                    to: DataType::Custom {
                        name: "DATETIME".to_string(),
                    },
                    double_colon_syntax: false,
                    trailing_comments: Vec::new(),
                    format: None,
                    default: None,
                    inferred_type: None,
                })))
            }

            // ===== Pattern matching =====
            // ILIKE is native to Snowflake (no transformation needed)
            Expression::ILike(op) => Ok(Expression::ILike(op)),

            // ===== Array operations =====
            // EXPLODE -> FLATTEN in Snowflake
            Expression::Explode(f) => Ok(Expression::Function(Box::new(Function::new(
                "FLATTEN".to_string(),
                vec![f.this],
            )))),

            // ExplodeOuter -> FLATTEN with OUTER => TRUE
            Expression::ExplodeOuter(f) => Ok(Expression::Function(Box::new(Function::new(
                "FLATTEN".to_string(),
                vec![f.this],
            )))),

            // UNNEST -> TABLE(FLATTEN(INPUT => x)) AS _t0(seq, key, path, index, value, this)
            Expression::Unnest(f) => {
                // Create INPUT => x named argument
                let input_arg =
                    Expression::NamedArgument(Box::new(crate::expressions::NamedArgument {
                        name: crate::expressions::Identifier::new("INPUT"),
                        value: f.this,
                        separator: crate::expressions::NamedArgSeparator::DArrow,
                    }));

                // Create FLATTEN(INPUT => x)
                let flatten = Expression::Function(Box::new(Function::new(
                    "FLATTEN".to_string(),
                    vec![input_arg],
                )));

                // Wrap in TABLE(...)
                let table_func =
                    Expression::TableFromRows(Box::new(crate::expressions::TableFromRows {
                        this: Box::new(flatten),
                        alias: None,
                        joins: vec![],
                        pivots: None,
                        sample: None,
                    }));

                // Add alias _t0(seq, key, path, index, value, this)
                Ok(Expression::Alias(Box::new(crate::expressions::Alias {
                    this: table_func,
                    alias: crate::expressions::Identifier::new("_t0"),
                    column_aliases: vec![
                        crate::expressions::Identifier::new("seq"),
                        crate::expressions::Identifier::new("key"),
                        crate::expressions::Identifier::new("path"),
                        crate::expressions::Identifier::new("index"),
                        crate::expressions::Identifier::new("value"),
                        crate::expressions::Identifier::new("this"),
                    ],
                    alias_explicit_as: false,
                    alias_keyword: None,
                    pre_alias_comments: vec![],
                    trailing_comments: vec![],
                    inferred_type: None,
                })))
            }

            // Array constructor:
            // - If bracket notation ([1, 2, 3]), preserve it in Snowflake
            // - If ARRAY[...] syntax, convert to ARRAY_CONSTRUCT
            Expression::ArrayFunc(arr) => {
                if arr.bracket_notation {
                    // Keep bracket notation in Snowflake
                    Ok(Expression::ArrayFunc(arr))
                } else {
                    // Convert ARRAY[...] to ARRAY_CONSTRUCT
                    Ok(Expression::Function(Box::new(Function::new(
                        "ARRAY_CONSTRUCT".to_string(),
                        arr.expressions,
                    ))))
                }
            }

            // ArrayConcat -> ARRAY_CAT
            Expression::ArrayConcat(f) => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_CAT".to_string(),
                f.expressions,
            )))),

            // ArrayConcatAgg -> ARRAY_FLATTEN
            Expression::ArrayConcatAgg(f) => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_FLATTEN".to_string(),
                vec![f.this],
            )))),

            // ArrayContains -> ARRAY_CONTAINS
            Expression::ArrayContains(f) => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_CONTAINS".to_string(),
                vec![f.this, f.expression],
            )))),

            // ArrayIntersect -> ARRAY_INTERSECTION
            Expression::ArrayIntersect(f) => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_INTERSECTION".to_string(),
                f.expressions,
            )))),

            // SortArray -> ARRAY_SORT
            Expression::ArraySort(f) => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_SORT".to_string(),
                vec![f.this],
            )))),

            // StringToArray -> STRTOK_TO_ARRAY
            Expression::StringToArray(f) => {
                let mut args = vec![*f.this];
                if let Some(expr) = f.expression {
                    args.push(*expr);
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "STRTOK_TO_ARRAY".to_string(),
                    args,
                ))))
            }

            // ===== Bitwise operations =====
            // BitwiseOr -> BITOR
            Expression::BitwiseOr(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITOR".to_string(),
                vec![f.left, f.right],
            )))),

            // BitwiseXor -> BITXOR
            Expression::BitwiseXor(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITXOR".to_string(),
                vec![f.left, f.right],
            )))),

            // BitwiseAnd -> BITAND
            Expression::BitwiseAnd(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITAND".to_string(),
                vec![f.left, f.right],
            )))),

            // BitwiseNot -> BITNOT
            Expression::BitwiseNot(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITNOT".to_string(),
                vec![f.this],
            )))),

            // BitwiseLeftShift -> BITSHIFTLEFT
            Expression::BitwiseLeftShift(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITSHIFTLEFT".to_string(),
                vec![f.left, f.right],
            )))),

            // BitwiseRightShift -> BITSHIFTRIGHT
            Expression::BitwiseRightShift(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITSHIFTRIGHT".to_string(),
                vec![f.left, f.right],
            )))),

            // BitwiseAndAgg -> BITAND_AGG
            Expression::BitwiseAndAgg(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITAND_AGG".to_string(),
                vec![f.this],
            )))),

            // BitwiseOrAgg -> BITOR_AGG
            Expression::BitwiseOrAgg(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITOR_AGG".to_string(),
                vec![f.this],
            )))),

            // BitwiseXorAgg -> BITXOR_AGG
            Expression::BitwiseXorAgg(f) => Ok(Expression::Function(Box::new(Function::new(
                "BITXOR_AGG".to_string(),
                vec![f.this],
            )))),

            // ===== Boolean aggregates =====
            // LogicalAnd -> BOOLAND_AGG
            Expression::LogicalAnd(f) => Ok(Expression::Function(Box::new(Function::new(
                "BOOLAND_AGG".to_string(),
                vec![f.this],
            )))),

            // LogicalOr -> BOOLOR_AGG
            Expression::LogicalOr(f) => Ok(Expression::Function(Box::new(Function::new(
                "BOOLOR_AGG".to_string(),
                vec![f.this],
            )))),

            // Booland -> BOOLAND
            Expression::Booland(f) => Ok(Expression::Function(Box::new(Function::new(
                "BOOLAND".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // Boolor -> BOOLOR
            Expression::Boolor(f) => Ok(Expression::Function(Box::new(Function::new(
                "BOOLOR".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // Xor -> BOOLXOR
            Expression::Xor(f) => {
                let mut args = Vec::new();
                if let Some(this) = f.this {
                    args.push(*this);
                }
                if let Some(expr) = f.expression {
                    args.push(*expr);
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "BOOLXOR".to_string(),
                    args,
                ))))
            }

            // ===== Date/time functions =====
            // DayOfMonth -> DAYOFMONTH
            Expression::DayOfMonth(f) => Ok(Expression::Function(Box::new(Function::new(
                "DAYOFMONTH".to_string(),
                vec![f.this],
            )))),

            // DayOfWeek -> DAYOFWEEK
            Expression::DayOfWeek(f) => Ok(Expression::Function(Box::new(Function::new(
                "DAYOFWEEK".to_string(),
                vec![f.this],
            )))),

            // DayOfWeekIso -> DAYOFWEEKISO
            Expression::DayOfWeekIso(f) => Ok(Expression::Function(Box::new(Function::new(
                "DAYOFWEEKISO".to_string(),
                vec![f.this],
            )))),

            // DayOfYear -> DAYOFYEAR
            Expression::DayOfYear(f) => Ok(Expression::Function(Box::new(Function::new(
                "DAYOFYEAR".to_string(),
                vec![f.this],
            )))),

            // WeekOfYear -> WEEK (Snowflake native function)
            Expression::WeekOfYear(f) => Ok(Expression::Function(Box::new(Function::new(
                "WEEK".to_string(),
                vec![f.this],
            )))),

            // YearOfWeek -> YEAROFWEEK
            Expression::YearOfWeek(f) => Ok(Expression::Function(Box::new(Function::new(
                "YEAROFWEEK".to_string(),
                vec![f.this],
            )))),

            // YearOfWeekIso -> YEAROFWEEKISO
            Expression::YearOfWeekIso(f) => Ok(Expression::Function(Box::new(Function::new(
                "YEAROFWEEKISO".to_string(),
                vec![f.this],
            )))),

            // ByteLength -> OCTET_LENGTH
            Expression::ByteLength(f) => Ok(Expression::Function(Box::new(Function::new(
                "OCTET_LENGTH".to_string(),
                vec![f.this],
            )))),

            // TimestampDiff -> TIMESTAMPDIFF
            Expression::TimestampDiff(f) => {
                let mut args = vec![];
                // If unit is set (from cross-dialect normalize), use unit as first arg, this as second, expression as third
                if let Some(ref unit_str) = f.unit {
                    args.push(Expression::Identifier(crate::expressions::Identifier::new(
                        unit_str.clone(),
                    )));
                    args.push(*f.this);
                    args.push(*f.expression);
                } else {
                    args.push(*f.this);
                    args.push(*f.expression);
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TIMESTAMPDIFF".to_string(),
                    args,
                ))))
            }

            // TimestampAdd -> TIMESTAMPADD
            Expression::TimestampAdd(f) => {
                let mut args = vec![];
                if let Some(ref unit_str) = f.unit {
                    args.push(Expression::Identifier(crate::expressions::Identifier::new(
                        unit_str.clone(),
                    )));
                    args.push(*f.this);
                    args.push(*f.expression);
                } else {
                    args.push(*f.this);
                    args.push(*f.expression);
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TIMESTAMPADD".to_string(),
                    args,
                ))))
            }

            // ToArray -> TO_ARRAY
            Expression::ToArray(f) => Ok(Expression::Function(Box::new(Function::new(
                "TO_ARRAY".to_string(),
                vec![f.this],
            )))),

            // DateAdd -> DATEADD (with unit, amount, date order)
            Expression::DateAdd(f) => {
                let unit_str = interval_unit_to_str(&f.unit);
                let unit = Expression::Identifier(crate::expressions::Identifier {
                    name: unit_str,
                    quoted: false,
                    trailing_comments: Vec::new(),
                    span: None,
                });
                Ok(Expression::Function(Box::new(Function::new(
                    "DATEADD".to_string(),
                    vec![unit, f.interval, f.this],
                ))))
            }

            // DateSub -> DATEADD with negated amount: val * -1
            Expression::DateSub(f) => {
                let unit_str = interval_unit_to_str(&f.unit);
                let unit = Expression::Identifier(crate::expressions::Identifier {
                    name: unit_str,
                    quoted: false,
                    trailing_comments: Vec::new(),
                    span: None,
                });
                // Negate using val * -1 format (matching Python sqlglot output)
                let neg_expr = Expression::Mul(Box::new(crate::expressions::BinaryOp::new(
                    f.interval,
                    Expression::Neg(Box::new(crate::expressions::UnaryOp {
                        this: Expression::number(1),
                        inferred_type: None,
                    })),
                )));
                Ok(Expression::Function(Box::new(Function::new(
                    "DATEADD".to_string(),
                    vec![unit, neg_expr, f.this],
                ))))
            }

            // DateDiff -> DATEDIFF
            Expression::DateDiff(f) => {
                let unit_str =
                    interval_unit_to_str(&f.unit.unwrap_or(crate::expressions::IntervalUnit::Day));
                let unit = Expression::Identifier(crate::expressions::Identifier {
                    name: unit_str,
                    quoted: false,
                    trailing_comments: Vec::new(),
                    span: None,
                });
                Ok(Expression::Function(Box::new(Function::new(
                    "DATEDIFF".to_string(),
                    vec![unit, f.expression, f.this],
                ))))
            }

            // ===== String functions =====
            // StringAgg -> LISTAGG in Snowflake
            Expression::StringAgg(f) => {
                let mut args = vec![f.this.clone()];
                if let Some(separator) = &f.separator {
                    args.push(separator.clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "LISTAGG".to_string(),
                    args,
                ))))
            }

            // StartsWith -> STARTSWITH
            Expression::StartsWith(f) => Ok(Expression::Function(Box::new(Function::new(
                "STARTSWITH".to_string(),
                vec![f.this, f.expression],
            )))),

            // EndsWith -> keep as EndsWith AST node; generator outputs per-dialect
            Expression::EndsWith(f) => Ok(Expression::EndsWith(f)),

            // Stuff -> INSERT
            Expression::Stuff(f) => {
                let mut args = vec![*f.this];
                if let Some(start) = f.start {
                    args.push(*start);
                }
                if let Some(length) = f.length {
                    args.push(Expression::number(length));
                }
                args.push(*f.expression);
                Ok(Expression::Function(Box::new(Function::new(
                    "INSERT".to_string(),
                    args,
                ))))
            }

            // ===== Hash functions =====
            // SHA -> SHA1
            Expression::SHA(f) => Ok(Expression::Function(Box::new(Function::new(
                "SHA1".to_string(),
                vec![f.this],
            )))),

            // SHA1Digest -> SHA1_BINARY
            Expression::SHA1Digest(f) => Ok(Expression::Function(Box::new(Function::new(
                "SHA1_BINARY".to_string(),
                vec![f.this],
            )))),

            // SHA2Digest -> SHA2_BINARY
            Expression::SHA2Digest(f) => Ok(Expression::Function(Box::new(Function::new(
                "SHA2_BINARY".to_string(),
                vec![*f.this],
            )))),

            // MD5Digest -> MD5_BINARY
            Expression::MD5Digest(f) => Ok(Expression::Function(Box::new(Function::new(
                "MD5_BINARY".to_string(),
                vec![*f.this],
            )))),

            // MD5NumberLower64 -> MD5_NUMBER_LOWER64
            Expression::MD5NumberLower64(f) => Ok(Expression::Function(Box::new(Function::new(
                "MD5_NUMBER_LOWER64".to_string(),
                vec![f.this],
            )))),

            // MD5NumberUpper64 -> MD5_NUMBER_UPPER64
            Expression::MD5NumberUpper64(f) => Ok(Expression::Function(Box::new(Function::new(
                "MD5_NUMBER_UPPER64".to_string(),
                vec![f.this],
            )))),

            // ===== Vector functions =====
            // CosineDistance -> VECTOR_COSINE_SIMILARITY
            Expression::CosineDistance(f) => Ok(Expression::Function(Box::new(Function::new(
                "VECTOR_COSINE_SIMILARITY".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // DotProduct -> VECTOR_INNER_PRODUCT
            Expression::DotProduct(f) => Ok(Expression::Function(Box::new(Function::new(
                "VECTOR_INNER_PRODUCT".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // EuclideanDistance -> VECTOR_L2_DISTANCE
            Expression::EuclideanDistance(f) => Ok(Expression::Function(Box::new(Function::new(
                "VECTOR_L2_DISTANCE".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // ManhattanDistance -> VECTOR_L1_DISTANCE
            Expression::ManhattanDistance(f) => Ok(Expression::Function(Box::new(Function::new(
                "VECTOR_L1_DISTANCE".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // ===== JSON/Struct functions =====
            // JSONFormat -> TO_JSON
            Expression::JSONFormat(f) => {
                let mut args = Vec::new();
                if let Some(this) = f.this {
                    args.push(*this);
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_JSON".to_string(),
                    args,
                ))))
            }

            // JSONKeys -> OBJECT_KEYS
            Expression::JSONKeys(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_KEYS".to_string(),
                vec![*f.this],
            )))),

            // GetExtract -> GET
            Expression::GetExtract(f) => Ok(Expression::Function(Box::new(Function::new(
                "GET".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // StarMap -> OBJECT_CONSTRUCT
            Expression::StarMap(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT".to_string(),
                vec![f.this, f.expression],
            )))),

            // LowerHex -> TO_CHAR
            Expression::LowerHex(f) => Ok(Expression::Function(Box::new(Function::new(
                "TO_CHAR".to_string(),
                vec![f.this],
            )))),

            // Skewness -> SKEW
            Expression::Skewness(f) => Ok(Expression::Function(Box::new(Function::new(
                "SKEW".to_string(),
                vec![f.this],
            )))),

            // StPoint -> ST_MAKEPOINT
            Expression::StPoint(f) => Ok(Expression::Function(Box::new(Function::new(
                "ST_MAKEPOINT".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // FromTimeZone -> CONVERT_TIMEZONE
            Expression::FromTimeZone(f) => Ok(Expression::Function(Box::new(Function::new(
                "CONVERT_TIMEZONE".to_string(),
                vec![*f.this],
            )))),

            // ===== Conversion functions =====
            // Unhex -> HEX_DECODE_BINARY
            Expression::Unhex(f) => Ok(Expression::Function(Box::new(Function::new(
                "HEX_DECODE_BINARY".to_string(),
                vec![*f.this],
            )))),

            // UnixToTime -> TO_TIMESTAMP
            Expression::UnixToTime(f) => {
                let mut args = vec![*f.this];
                if let Some(scale) = f.scale {
                    args.push(Expression::number(scale));
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_TIMESTAMP".to_string(),
                    args,
                ))))
            }

            // ===== Conditional =====
            // IfFunc -> keep as IfFunc with IFF name for Snowflake
            Expression::IfFunc(f) => Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                condition: f.condition,
                true_value: f.true_value,
                false_value: Some(
                    f.false_value
                        .unwrap_or(Expression::Null(crate::expressions::Null)),
                ),
                original_name: Some("IFF".to_string()),
                inferred_type: None,
            }))),

            // ===== Aggregate functions =====
            // ApproxDistinct -> APPROX_COUNT_DISTINCT
            Expression::ApproxDistinct(f) => Ok(Expression::Function(Box::new(Function::new(
                "APPROX_COUNT_DISTINCT".to_string(),
                vec![f.this],
            )))),

            // ArgMax -> MAX_BY
            Expression::ArgMax(f) => Ok(Expression::Function(Box::new(Function::new(
                "MAX_BY".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // ArgMin -> MIN_BY
            Expression::ArgMin(f) => Ok(Expression::Function(Box::new(Function::new(
                "MIN_BY".to_string(),
                vec![*f.this, *f.expression],
            )))),

            // ===== Random =====
            // RANDOM is native to Snowflake - keep as-is
            Expression::Random(_) => Ok(Expression::Random(crate::expressions::Random)),

            // Rand - keep as-is (generator outputs RANDOM for Snowflake)
            Expression::Rand(r) => Ok(Expression::Rand(r)),

            // ===== UUID =====
            // Uuid -> keep as Uuid node; generator will output UUID_STRING for Snowflake
            Expression::Uuid(u) => Ok(Expression::Uuid(u)),

            // ===== Map/Object =====
            // Map -> OBJECT_CONSTRUCT
            Expression::Map(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT".to_string(),
                f.keys
                    .into_iter()
                    .zip(f.values.into_iter())
                    .flat_map(|(k, v)| vec![k, v])
                    .collect(),
            )))),

            // MapFunc (curly brace syntax) -> OBJECT_CONSTRUCT
            Expression::MapFunc(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT".to_string(),
                f.keys
                    .into_iter()
                    .zip(f.values.into_iter())
                    .flat_map(|(k, v)| vec![k, v])
                    .collect(),
            )))),

            // VarMap -> OBJECT_CONSTRUCT
            Expression::VarMap(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT".to_string(),
                f.keys
                    .into_iter()
                    .zip(f.values.into_iter())
                    .flat_map(|(k, v)| vec![k, v])
                    .collect(),
            )))),

            // ===== JSON =====
            // JSONObject -> OBJECT_CONSTRUCT_KEEP_NULL
            Expression::JsonObject(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT_KEEP_NULL".to_string(),
                f.pairs.into_iter().flat_map(|(k, v)| vec![k, v]).collect(),
            )))),

            // JSONExtractScalar -> JSON_EXTRACT_PATH_TEXT
            Expression::JsonExtractScalar(f) => Ok(Expression::Function(Box::new(Function::new(
                "JSON_EXTRACT_PATH_TEXT".to_string(),
                vec![f.this, f.path],
            )))),

            // ===== Struct =====
            // Struct -> OBJECT_CONSTRUCT
            Expression::Struct(f) => Ok(Expression::Function(Box::new(Function::new(
                "OBJECT_CONSTRUCT".to_string(),
                f.fields
                    .into_iter()
                    .flat_map(|(name, expr)| {
                        let key = match name {
                            Some(n) => Expression::string(n),
                            None => Expression::Null(crate::expressions::Null),
                        };
                        vec![key, expr]
                    })
                    .collect(),
            )))),

            // ===== JSON Path =====
            // JSONPathRoot -> empty string ($ is implicit in Snowflake)
            Expression::JSONPathRoot(_) => Ok(Expression::Literal(Box::new(
                crate::expressions::Literal::String(String::new()),
            ))),

            // ===== VarSamp -> VARIANCE (Snowflake) =====
            // Snowflake uses VARIANCE instead of VAR_SAMP
            Expression::VarSamp(agg) => Ok(Expression::Variance(agg)),

            // ===== VarPop -> keep as VarPop =====
            // The generator handles dialect-specific naming (VARIANCE_POP for Snowflake)
            Expression::VarPop(agg) => Ok(Expression::VarPop(agg)),

            // ===== EXTRACT -> DATE_PART =====
            // Snowflake uses DATE_PART instead of EXTRACT
            Expression::Extract(f) => {
                use crate::expressions::DateTimeField;
                // Recursively transform the inner expression (e.g., CAST(... AS TIMESTAMP_NTZ) -> CAST(... AS TIMESTAMPNTZ))
                let transformed_this = self.transform_expr(f.this)?;
                let field_name = match &f.field {
                    DateTimeField::Year => "YEAR",
                    DateTimeField::Month => "MONTH",
                    DateTimeField::Day => "DAY",
                    DateTimeField::Hour => "HOUR",
                    DateTimeField::Minute => "MINUTE",
                    DateTimeField::Second => "SECOND",
                    DateTimeField::Millisecond => "MILLISECOND",
                    DateTimeField::Microsecond => "MICROSECOND",
                    DateTimeField::Week => "WEEK",
                    DateTimeField::WeekWithModifier(m) => {
                        return Ok(Expression::Function(Box::new(Function::new(
                            "DATE_PART".to_string(),
                            vec![
                                Expression::Identifier(crate::expressions::Identifier {
                                    name: format!("WEEK({})", m),
                                    quoted: false,
                                    trailing_comments: Vec::new(),
                                    span: None,
                                }),
                                transformed_this,
                            ],
                        ))))
                    }
                    DateTimeField::DayOfWeek => "DAYOFWEEK",
                    DateTimeField::DayOfYear => "DAYOFYEAR",
                    DateTimeField::Quarter => "QUARTER",
                    DateTimeField::Epoch => "EPOCH",
                    DateTimeField::Timezone => "TIMEZONE",
                    DateTimeField::TimezoneHour => "TIMEZONE_HOUR",
                    DateTimeField::TimezoneMinute => "TIMEZONE_MINUTE",
                    DateTimeField::Date => "DATE",
                    DateTimeField::Time => "TIME",
                    DateTimeField::Custom(s) => {
                        // Map common EXTRACT field names to Snowflake DATE_PART names
                        match s.to_uppercase().as_str() {
                            "DAYOFMONTH" => "DAY",
                            "DOW" => "DAYOFWEEK",
                            "DOY" => "DAYOFYEAR",
                            "ISODOW" => "DAYOFWEEKISO",
                            "EPOCH_SECOND" | "EPOCH_SECONDS" => "EPOCH_SECOND",
                            "EPOCH_MILLISECOND" | "EPOCH_MILLISECONDS" => "EPOCH_MILLISECOND",
                            "EPOCH_MICROSECOND" | "EPOCH_MICROSECONDS" => "EPOCH_MICROSECOND",
                            "EPOCH_NANOSECOND" | "EPOCH_NANOSECONDS" => "EPOCH_NANOSECOND",
                            _ => {
                                return {
                                    let field_ident =
                                        Expression::Identifier(crate::expressions::Identifier {
                                            name: s.to_string(),
                                            quoted: false,
                                            trailing_comments: Vec::new(),
                                            span: None,
                                        });
                                    Ok(Expression::Function(Box::new(Function::new(
                                        "DATE_PART".to_string(),
                                        vec![field_ident, transformed_this],
                                    ))))
                                }
                            }
                        }
                    }
                };
                let field_ident = Expression::Identifier(crate::expressions::Identifier {
                    name: field_name.to_string(),
                    quoted: false,
                    trailing_comments: Vec::new(),
                    span: None,
                });
                Ok(Expression::Function(Box::new(Function::new(
                    "DATE_PART".to_string(),
                    vec![field_ident, transformed_this],
                ))))
            }

            // Generic function transformations
            Expression::Function(f) => self.transform_function(*f),

            // SUM - recursively transform inner expression
            Expression::Sum(mut agg) => {
                agg.this = self.transform_expr(agg.this)?;
                Ok(Expression::Sum(agg))
            }

            // Generic aggregate function transformations
            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),

            // Handle NamedArgument - recursively transform the value
            Expression::NamedArgument(na) => {
                let transformed_value = self.transform_expr(na.value)?;
                Ok(Expression::NamedArgument(Box::new(
                    crate::expressions::NamedArgument {
                        name: na.name,
                        value: transformed_value,
                        separator: na.separator,
                    },
                )))
            }

            // Handle CreateTable - transform column data types and default/computed expressions
            Expression::CreateTable(mut ct) => {
                for col in &mut ct.columns {
                    if let Expression::DataType(new_dt) =
                        self.transform_data_type(col.data_type.clone())?
                    {
                        col.data_type = new_dt;
                    }
                    // Also transform computed/default expressions (e.g., AS (parse_json(x):COL3::number))
                    if let Some(default_expr) = col.default.take() {
                        col.default = Some(self.transform_expr(default_expr)?);
                    }
                    // Transform expressions in column constraints (computed columns)
                    for constraint in &mut col.constraints {
                        if let crate::expressions::ColumnConstraint::ComputedColumn(cc) = constraint
                        {
                            let transformed = self.transform_expr(*cc.expression.clone())?;
                            cc.expression = Box::new(transformed);
                        }
                    }
                }

                // For EXTERNAL tables, convert with_properties to Raw properties
                // with proper Snowflake formatting (no WITH wrapper, specific key casing)
                if ct.table_modifier.as_deref() == Some("EXTERNAL")
                    && !ct.with_properties.is_empty()
                {
                    for (key, value) in ct.with_properties.drain(..) {
                        let formatted = Self::format_external_table_property(&key, &value);
                        ct.properties
                            .push(Expression::Raw(crate::expressions::Raw { sql: formatted }));
                    }
                }

                Ok(Expression::CreateTable(ct))
            }

            // Handle AlterTable - transform column data types in ADD operations
            Expression::AlterTable(mut at) => {
                for action in &mut at.actions {
                    if let crate::expressions::AlterTableAction::AddColumn { column, .. } = action {
                        if let Expression::DataType(new_dt) =
                            self.transform_data_type(column.data_type.clone())?
                        {
                            column.data_type = new_dt;
                        }
                    }
                }
                Ok(Expression::AlterTable(at))
            }

            // Handle Table reference - transform HistoricalData (AT/BEFORE time travel clauses)
            Expression::Table(mut t) => {
                if let Some(when) = t.when.take() {
                    // Recursively transform the expression inside HistoricalData
                    let transformed_expr = self.transform_expr(*when.expression)?;
                    t.when = Some(Box::new(crate::expressions::HistoricalData {
                        this: when.this,
                        kind: when.kind,
                        expression: Box::new(transformed_expr),
                    }));
                }
                Ok(Expression::Table(t))
            }

            // Handle Subscript - recursively transform inner expression
            Expression::Subscript(s) => {
                let transformed_this = self.transform_expr(s.this)?;
                let transformed_index = self.transform_expr(s.index)?;
                Ok(Expression::Subscript(Box::new(
                    crate::expressions::Subscript {
                        this: transformed_this,
                        index: transformed_index,
                    },
                )))
            }

            // Recursively transform parenthesized expressions
            Expression::Paren(p) => {
                let transformed = self.transform_expr(p.this)?;
                Ok(Expression::Paren(Box::new(crate::expressions::Paren {
                    this: transformed,
                    trailing_comments: p.trailing_comments,
                })))
            }

            // Implicit NULL ordering is handled by cross-dialect normalization,
            // not here: this transform also runs on Snowflake targets and identity
            // queries, where missing clauses must retain server-dependent semantics.

            // LATERAL FLATTEN: add default column aliases (SEQ, KEY, PATH, INDEX, VALUE, THIS)
            Expression::Lateral(mut lat) => {
                // Check if the inner expression is a FLATTEN function
                let is_flatten = match lat.this.as_ref() {
                    Expression::Function(f) => f.name.to_uppercase() == "FLATTEN",
                    _ => false,
                };
                if is_flatten && lat.column_aliases.is_empty() {
                    // Add default column aliases
                    lat.column_aliases = vec![
                        "SEQ".to_string(),
                        "KEY".to_string(),
                        "PATH".to_string(),
                        "INDEX".to_string(),
                        "VALUE".to_string(),
                        "THIS".to_string(),
                    ];
                    // If no alias, add _flattened
                    if lat.alias.is_none() {
                        lat.alias = Some("_flattened".to_string());
                    }
                }
                Ok(Expression::Lateral(lat))
            }

            // Pass through everything else
            _ => Ok(expr),
        }
    }
}

#[cfg(feature = "transpile")]
impl SnowflakeDialect {
    /// Format a Snowflake external table property for output.
    /// Some properties like LOCATION and FILE_FORMAT are uppercased keywords.
    fn format_external_table_property(key: &str, value: &str) -> String {
        let lower_key = key.to_lowercase();
        match lower_key.as_str() {
            "location" => format!("LOCATION={}", value),
            "file_format" => {
                // Format file_format value: remove spaces around =, uppercase booleans
                let formatted_value = Self::format_file_format_value(value);
                format!("FILE_FORMAT={}", formatted_value)
            }
            _ => format!("{}={}", key, value),
        }
    }

    /// Format file_format property value:
    /// - Remove spaces around = signs
    /// - Uppercase boolean values (false -> FALSE, true -> TRUE)
    fn format_file_format_value(value: &str) -> String {
        if !value.starts_with('(') {
            return value.to_string();
        }
        // Strip outer parens, process inner key=value pairs
        let inner = value[1..value.len() - 1].trim();
        // Parse space-separated key=value pairs (may have spaces around =)
        let mut result = String::from("(");
        let mut parts: Vec<String> = Vec::new();
        // Split by whitespace and reconstruct key=value pairs
        let tokens: Vec<&str> = inner.split_whitespace().collect();
        let mut i = 0;
        while i < tokens.len() {
            let token = tokens[i];
            if i + 2 < tokens.len() && tokens[i + 1] == "=" {
                // key = value pattern
                let val = Self::format_property_value(tokens[i + 2]);
                parts.push(format!("{}={}", token, val));
                i += 3;
            } else if token.contains('=') {
                // key=value already joined
                let eq_pos = token.find('=').unwrap();
                let k = &token[..eq_pos];
                let v = Self::format_property_value(&token[eq_pos + 1..]);
                parts.push(format!("{}={}", k, v));
                i += 1;
            } else {
                parts.push(token.to_string());
                i += 1;
            }
        }
        result.push_str(&parts.join(" "));
        result.push(')');
        result
    }

    /// Format a property value - uppercase boolean literals
    fn format_property_value(value: &str) -> String {
        match value.to_lowercase().as_str() {
            "true" => "TRUE".to_string(),
            "false" => "FALSE".to_string(),
            _ => value.to_string(),
        }
    }

    /// Transform data types according to Snowflake TYPE_MAPPING
    fn transform_data_type(&self, dt: crate::expressions::DataType) -> Result<Expression> {
        use crate::expressions::DataType;
        let transformed = match dt {
            // TEXT -> VARCHAR
            DataType::Text => DataType::VarChar {
                length: None,
                parenthesized_length: false,
            },
            // STRUCT -> OBJECT
            DataType::Struct { fields, .. } => {
                // Snowflake uses OBJECT for struct types
                let _ = fields; // Snowflake OBJECT doesn't preserve field names in the same way
                DataType::Custom {
                    name: "OBJECT".to_string(),
                }
            }
            // Custom type transformations
            DataType::Custom { name } => {
                let upper_name = name.to_uppercase();
                match upper_name.as_str() {
                    // NVARCHAR -> VARCHAR (SQL Server type)
                    "NVARCHAR" | "NCHAR" | "NATIONAL CHARACTER VARYING" | "NATIONAL CHAR" => {
                        DataType::VarChar {
                            length: None,
                            parenthesized_length: false,
                        }
                    }
                    // STRING -> VARCHAR (Snowflake accepts both, but normalizes to VARCHAR)
                    "STRING" => DataType::VarChar {
                        length: None,
                        parenthesized_length: false,
                    },
                    // BIGDECIMAL -> DOUBLE
                    "BIGDECIMAL" => DataType::Double {
                        precision: None,
                        scale: None,
                    },
                    // NESTED -> OBJECT
                    "NESTED" => DataType::Custom {
                        name: "OBJECT".to_string(),
                    },
                    // BYTEINT -> INT
                    "BYTEINT" => DataType::Int {
                        length: None,
                        integer_spelling: false,
                    },
                    // CHAR VARYING -> VARCHAR
                    "CHAR VARYING" | "CHARACTER VARYING" => DataType::VarChar {
                        length: None,
                        parenthesized_length: false,
                    },
                    // SQL_DOUBLE -> DOUBLE
                    "SQL_DOUBLE" => DataType::Double {
                        precision: None,
                        scale: None,
                    },
                    // SQL_VARCHAR -> VARCHAR
                    "SQL_VARCHAR" => DataType::VarChar {
                        length: None,
                        parenthesized_length: false,
                    },
                    // TIMESTAMP_NTZ -> TIMESTAMPNTZ (normalize underscore form)
                    "TIMESTAMP_NTZ" => DataType::Custom {
                        name: "TIMESTAMPNTZ".to_string(),
                    },
                    // TIMESTAMP_LTZ -> TIMESTAMPLTZ (normalize underscore form)
                    "TIMESTAMP_LTZ" => DataType::Custom {
                        name: "TIMESTAMPLTZ".to_string(),
                    },
                    // TIMESTAMP_TZ -> TIMESTAMPTZ (normalize underscore form)
                    "TIMESTAMP_TZ" => DataType::Custom {
                        name: "TIMESTAMPTZ".to_string(),
                    },
                    // NCHAR VARYING -> VARCHAR
                    "NCHAR VARYING" => DataType::VarChar {
                        length: None,
                        parenthesized_length: false,
                    },
                    // NUMBER -> DECIMAL(38, 0) (Snowflake's default NUMBER is DECIMAL(38, 0))
                    "NUMBER" => DataType::Decimal {
                        precision: Some(38),
                        scale: Some(0),
                    },
                    _ if name.starts_with("NUMBER(") => {
                        // NUMBER(precision, scale) -> DECIMAL(precision, scale)
                        // Parse: "NUMBER(38, 0)" -> precision=38, scale=0
                        let inner = &name[7..name.len() - 1]; // strip "NUMBER(" and ")"
                        let parts: Vec<&str> = inner.split(',').map(|s| s.trim()).collect();
                        let precision = parts.first().and_then(|p| p.parse::<u32>().ok());
                        let scale = parts.get(1).and_then(|s| s.parse::<u32>().ok());
                        DataType::Decimal { precision, scale }
                    }
                    _ => DataType::Custom { name },
                }
            }
            // DECIMAL without precision -> DECIMAL(38, 0) (Snowflake default)
            DataType::Decimal {
                precision: None,
                scale: None,
            } => DataType::Decimal {
                precision: Some(38),
                scale: Some(0),
            },
            // FLOAT -> DOUBLE (Snowflake FLOAT is actually 64-bit DOUBLE)
            DataType::Float { .. } => DataType::Double {
                precision: None,
                scale: None,
            },
            // Keep all other types as-is (Snowflake is quite flexible)
            other => other,
        };
        Ok(Expression::DataType(transformed))
    }

    /// Map date part abbreviation to canonical form (from Python SQLGlot DATE_PART_MAPPING)
    fn map_date_part(abbr: &str) -> Option<&'static str> {
        match abbr.to_uppercase().as_str() {
            // Year
            "Y" | "YY" | "YYY" | "YYYY" | "YR" | "YEARS" | "YRS" => Some("YEAR"),
            // Month
            "MM" | "MON" | "MONS" | "MONTHS" => Some("MONTH"),
            // Day
            "D" | "DD" | "DAYS" | "DAYOFMONTH" => Some("DAY"),
            // Day of week
            "DAY OF WEEK" | "WEEKDAY" | "DOW" | "DW" => Some("DAYOFWEEK"),
            "WEEKDAY_ISO" | "DOW_ISO" | "DW_ISO" | "DAYOFWEEK_ISO" => Some("DAYOFWEEKISO"),
            // Day of year
            "DAY OF YEAR" | "DOY" | "DY" => Some("DAYOFYEAR"),
            // Week
            "W" | "WK" | "WEEKOFYEAR" | "WOY" | "WY" => Some("WEEK"),
            "WEEK_ISO" | "WEEKOFYEARISO" | "WEEKOFYEAR_ISO" => Some("WEEKISO"),
            // Quarter
            "Q" | "QTR" | "QTRS" | "QUARTERS" => Some("QUARTER"),
            // Hour
            "H" | "HH" | "HR" | "HOURS" | "HRS" => Some("HOUR"),
            // Minute (note: 'M' could be minute in some contexts, but we keep it simple)
            "MI" | "MIN" | "MINUTES" | "MINS" => Some("MINUTE"),
            // Second
            "S" | "SEC" | "SECONDS" | "SECS" => Some("SECOND"),
            // Millisecond
            "MS" | "MSEC" | "MSECS" | "MSECOND" | "MSECONDS" | "MILLISEC" | "MILLISECS"
            | "MILLISECON" | "MILLISECONDS" => Some("MILLISECOND"),
            // Microsecond
            "US" | "USEC" | "USECS" | "MICROSEC" | "MICROSECS" | "USECOND" | "USECONDS"
            | "MICROSECONDS" => Some("MICROSECOND"),
            // Nanosecond
            "NS" | "NSEC" | "NANOSEC" | "NSECOND" | "NSECONDS" | "NANOSECS" => Some("NANOSECOND"),
            // Epoch variants
            "EPOCH_SECOND" | "EPOCH_SECONDS" => Some("EPOCH_SECOND"),
            "EPOCH_MILLISECOND" | "EPOCH_MILLISECONDS" => Some("EPOCH_MILLISECOND"),
            "EPOCH_MICROSECOND" | "EPOCH_MICROSECONDS" => Some("EPOCH_MICROSECOND"),
            "EPOCH_NANOSECOND" | "EPOCH_NANOSECONDS" => Some("EPOCH_NANOSECOND"),
            // Timezone
            "TZH" => Some("TIMEZONE_HOUR"),
            "TZM" => Some("TIMEZONE_MINUTE"),
            // Decade
            "DEC" | "DECS" | "DECADES" => Some("DECADE"),
            // Millennium
            "MIL" | "MILS" | "MILLENIA" => Some("MILLENNIUM"),
            // Century
            "C" | "CENT" | "CENTS" | "CENTURIES" => Some("CENTURY"),
            // No mapping needed (already canonical or unknown)
            _ => None,
        }
    }

    /// Transform a date part identifier/expression using the mapping
    fn transform_date_part_arg(&self, expr: Expression) -> Expression {
        match &expr {
            // Handle string literal: 'minute' -> minute (unquoted identifier, preserving case)
            Expression::Literal(lit)
                if matches!(lit.as_ref(), crate::expressions::Literal::String(_)) =>
            {
                let crate::expressions::Literal::String(s) = lit.as_ref() else {
                    unreachable!()
                };
                Expression::Identifier(crate::expressions::Identifier {
                    name: s.clone(),
                    quoted: false,
                    trailing_comments: Vec::new(),
                    span: None,
                })
            }
            // Handle Identifier (rare case)
            Expression::Identifier(id) => {
                if let Some(canonical) = Self::map_date_part(&id.name) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    // No mapping needed, keep original (Python sqlglot preserves case)
                    expr
                }
            }
            Expression::Var(v) => {
                if let Some(canonical) = Self::map_date_part(&v.this) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    expr
                }
            }
            // Handle Column (more common - parser treats unqualified names as columns)
            Expression::Column(col) if col.table.is_none() => {
                if let Some(canonical) = Self::map_date_part(&col.name.name) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    // No mapping needed, keep original (Python sqlglot preserves case)
                    expr
                }
            }
            _ => expr,
        }
    }

    /// Like transform_date_part_arg but only handles Identifier/Column, never String literals.
    /// Used for native Snowflake DATE_PART where string args should stay as strings.
    fn transform_date_part_arg_identifiers_only(&self, expr: Expression) -> Expression {
        match &expr {
            Expression::Identifier(id) => {
                if let Some(canonical) = Self::map_date_part(&id.name) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    expr
                }
            }
            Expression::Var(v) => {
                if let Some(canonical) = Self::map_date_part(&v.this) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    expr
                }
            }
            Expression::Column(col) if col.table.is_none() => {
                if let Some(canonical) = Self::map_date_part(&col.name.name) {
                    Expression::Identifier(crate::expressions::Identifier {
                        name: canonical.to_string(),
                        quoted: false,
                        trailing_comments: Vec::new(),
                        span: None,
                    })
                } else {
                    expr
                }
            }
            _ => expr,
        }
    }

    /// Transform JSON path for Snowflake GET_PATH function
    /// - Convert colon notation to dot notation (y[0]:z -> y[0].z)
    /// - Wrap unsafe keys in brackets ($id -> ["$id"])
    fn transform_json_path(path: &str) -> String {
        // Check if path is just a single key that needs bracket wrapping
        // A safe identifier is alphanumeric + underscore, starting with letter/underscore
        fn is_safe_identifier(s: &str) -> bool {
            if s.is_empty() {
                return false;
            }
            let mut chars = s.chars();
            match chars.next() {
                Some(c) if c.is_ascii_alphabetic() || c == '_' => {}
                _ => return false,
            }
            chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
        }

        // Simple path: just a key like "$id" or "field"
        // If no dots, brackets, or colons, it's a simple key
        if !path.contains('.') && !path.contains('[') && !path.contains(':') {
            if is_safe_identifier(path) {
                return path.to_string();
            } else {
                // Wrap unsafe key in bracket notation
                return format!("[\"{}\"]", path);
            }
        }

        // Complex path: replace colons with dots
        // e.g., y[0]:z -> y[0].z
        let result = path.replace(':', ".");
        result
    }

    /// Transform interval to expand abbreviated units (e.g., 'w' -> 'WEEK')
    fn transform_interval(&self, interval: crate::expressions::Interval) -> Result<Expression> {
        use crate::expressions::{Interval, Literal};

        // Unit abbreviation mapping (from Python SQLGlot UNABBREVIATED_UNIT_NAME)
        fn expand_unit(abbr: &str) -> &'static str {
            match abbr.to_uppercase().as_str() {
                "D" => "DAY",
                "H" => "HOUR",
                "M" => "MINUTE",
                "MS" => "MILLISECOND",
                "NS" => "NANOSECOND",
                "Q" => "QUARTER",
                "S" => "SECOND",
                "US" => "MICROSECOND",
                "W" => "WEEK",
                "Y" => "YEAR",
                // Full forms (normalize to singular, uppercase)
                "WEEK" | "WEEKS" => "WEEK",
                "DAY" | "DAYS" => "DAY",
                "HOUR" | "HOURS" => "HOUR",
                "MINUTE" | "MINUTES" => "MINUTE",
                "SECOND" | "SECONDS" => "SECOND",
                "MONTH" | "MONTHS" => "MONTH",
                "YEAR" | "YEARS" => "YEAR",
                "QUARTER" | "QUARTERS" => "QUARTER",
                "MILLISECOND" | "MILLISECONDS" => "MILLISECOND",
                "MICROSECOND" | "MICROSECONDS" => "MICROSECOND",
                "NANOSECOND" | "NANOSECONDS" => "NANOSECOND",
                _ => "", // Unknown unit, return empty to indicate no match
            }
        }

        /// Parse an interval string like "1 w" into (value, unit)
        fn parse_interval_string(s: &str) -> Option<(&str, &str)> {
            let s = s.trim();

            // Find where the number ends and the unit begins
            // Number can be: optional -, digits, optional decimal point, more digits
            let mut num_end = 0;
            let mut chars = s.chars().peekable();

            // Skip leading minus
            if chars.peek() == Some(&'-') {
                chars.next();
                num_end += 1;
            }

            // Skip digits
            while let Some(&c) = chars.peek() {
                if c.is_ascii_digit() {
                    chars.next();
                    num_end += 1;
                } else {
                    break;
                }
            }

            // Skip optional decimal point and more digits
            if chars.peek() == Some(&'.') {
                chars.next();
                num_end += 1;
                while let Some(&c) = chars.peek() {
                    if c.is_ascii_digit() {
                        chars.next();
                        num_end += 1;
                    } else {
                        break;
                    }
                }
            }

            if num_end == 0 || (num_end == 1 && s.starts_with('-')) {
                return None; // No number found
            }

            let value = &s[..num_end];
            let rest = s[num_end..].trim();

            // Rest should be alphabetic (the unit)
            if rest.is_empty() || !rest.chars().all(|c| c.is_ascii_alphabetic()) {
                return None;
            }

            Some((value, rest))
        }

        // Check if the interval value is a string literal with embedded value+unit
        if let Some(Expression::Literal(ref lit)) = interval.this {
            if let Literal::String(ref s) = lit.as_ref() {
                if let Some((value, unit)) = parse_interval_string(s) {
                    let expanded = expand_unit(unit);
                    if !expanded.is_empty() {
                        // Construct new string with expanded unit
                        let new_value = format!("{} {}", value, expanded);

                        return Ok(Expression::Interval(Box::new(Interval {
                            this: Some(Expression::Literal(Box::new(Literal::String(new_value)))),
                            unit: None, // Unit is now part of the string (SINGLE_STRING_INTERVAL style)
                        })));
                    }
                }
            }
        }

        // No transformation needed
        Ok(Expression::Interval(Box::new(interval)))
    }

    fn transform_function(&self, f: Function) -> Result<Expression> {
        // First, recursively transform all function arguments
        let transformed_args: Vec<Expression> = f
            .args
            .into_iter()
            .map(|arg| self.transform_expr(arg))
            .collect::<Result<Vec<_>>>()?;

        let f = Function {
            on_error: None,
            qualified_name: Vec::new(),
            source_dialect: None,

            name: f.name,
            args: transformed_args,
            distinct: f.distinct,
            trailing_comments: f.trailing_comments,
            use_bracket_syntax: f.use_bracket_syntax,
            no_parens: f.no_parens,
            quoted: f.quoted,
            span: None,
            inferred_type: None,
        };

        let name_upper = f.name.to_uppercase();
        match name_upper.as_str() {
            // IFNULL -> COALESCE (standardize to COALESCE)
            "IFNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
                original_name: None,
                expressions: f.args,
                inferred_type: None,
            }))),

            // NVL -> COALESCE (both work in Snowflake, but COALESCE is standard per SQLGlot)
            "NVL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
                original_name: None,
                expressions: f.args,
                inferred_type: None,
            }))),

            // NVL2 is native to Snowflake
            "NVL2" => Ok(Expression::Function(Box::new(f))),

            // GROUP_CONCAT -> LISTAGG in Snowflake
            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
                Function::new("LISTAGG".to_string(), f.args),
            ))),

            // STRING_AGG -> LISTAGG in Snowflake
            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
                Function::new("LISTAGG".to_string(), f.args),
            ))),

            // SUBSTR -> SUBSTRING (both work in Snowflake)
            "SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
                "SUBSTRING".to_string(),
                f.args,
            )))),

            // UNNEST -> FLATTEN
            "UNNEST" => Ok(Expression::Function(Box::new(Function::new(
                "FLATTEN".to_string(),
                f.args,
            )))),

            // EXPLODE -> FLATTEN
            "EXPLODE" => Ok(Expression::Function(Box::new(Function::new(
                "FLATTEN".to_string(),
                f.args,
            )))),

            // CURRENT_DATE is native
            "CURRENT_DATE" => Ok(Expression::CurrentDate(crate::expressions::CurrentDate)),

            // NOW -> CURRENT_TIMESTAMP (preserving parens style)
            "NOW" => Ok(Expression::Function(Box::new(Function {
                on_error: None,
                qualified_name: Vec::new(),
                source_dialect: None,

                name: "CURRENT_TIMESTAMP".to_string(),
                args: f.args,
                distinct: false,
                trailing_comments: Vec::new(),
                use_bracket_syntax: false,
                no_parens: f.no_parens,
                quoted: false,
                span: None,
                inferred_type: None,
            }))),

            // GETDATE -> CURRENT_TIMESTAMP (preserving parens style)
            "GETDATE" => Ok(Expression::Function(Box::new(Function {
                on_error: None,
                qualified_name: Vec::new(),
                source_dialect: None,

                name: "CURRENT_TIMESTAMP".to_string(),
                args: f.args,
                distinct: false,
                trailing_comments: Vec::new(),
                use_bracket_syntax: false,
                no_parens: f.no_parens,
                quoted: false,
                span: None,
                inferred_type: None,
            }))),

            // CURRENT_TIMESTAMP - always output with parens in Snowflake
            // Note: LOCALTIMESTAMP converts to CURRENT_TIMESTAMP without parens,
            // but explicit CURRENT_TIMESTAMP calls should have parens
            "CURRENT_TIMESTAMP" if f.args.is_empty() => {
                Ok(Expression::Function(Box::new(Function {
                    on_error: None,
                    qualified_name: Vec::new(),
                    source_dialect: None,

                    name: "CURRENT_TIMESTAMP".to_string(),
                    args: Vec::new(),
                    distinct: false,
                    trailing_comments: Vec::new(),
                    use_bracket_syntax: false,
                    no_parens: false, // Always output with parens
                    quoted: false,
                    span: None,
                    inferred_type: None,
                })))
            }

            // TO_DATE with single string arg that looks like a date -> CAST(arg AS DATE)
            // Per Python SQLGlot: TO_DATE('2013-04-05') -> CAST('2013-04-05' AS DATE)
            // But TO_DATE('12345') stays as is (doesn't look like a date)
            "TO_DATE" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(s) = lit.as_ref() {
                            // Check if the string looks like a date (contains dashes like 2013-04-05)
                            if s.contains('-') && s.len() >= 8 && s.len() <= 12 {
                                return Ok(Expression::Cast(Box::new(Cast {
                                    this: f.args.into_iter().next().unwrap(),
                                    to: crate::expressions::DataType::Date,
                                    double_colon_syntax: false,
                                    trailing_comments: Vec::new(),
                                    format: None,
                                    default: None,
                                    inferred_type: None,
                                })));
                            }
                        }
                    }
                }
                // Normalize format string (2nd arg) if present
                let mut args = f.args;
                if args.len() >= 2 {
                    args[1] = Self::normalize_format_arg(args[1].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_DATE".to_string(),
                    args,
                ))))
            }

            // TO_TIME with single string arg -> CAST(arg AS TIME)
            "TO_TIME" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(_) = lit.as_ref() {
                            return Ok(Expression::Cast(Box::new(Cast {
                                this: f.args.into_iter().next().unwrap(),
                                to: crate::expressions::DataType::Time {
                                    precision: None,
                                    timezone: false,
                                },
                                double_colon_syntax: false,
                                trailing_comments: Vec::new(),
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                    }
                }
                // Normalize format string (2nd arg) if present
                let mut args = f.args;
                if args.len() >= 2 {
                    args[1] = Self::normalize_format_arg(args[1].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_TIME".to_string(),
                    args,
                ))))
            }

            // TO_TIMESTAMP: Snowflake has multiple forms:
            // 1. TO_TIMESTAMP('datetime_string') -> CAST('...' AS TIMESTAMP)
            // 2. TO_TIMESTAMP('epoch_string') -> UnixToTime(epoch_string)
            // 3. TO_TIMESTAMP(number) -> UnixToTime(number)
            // 4. TO_TIMESTAMP(number, scale) where scale is int -> UnixToTime(number, scale)
            // 5. TO_TIMESTAMP(string, format) where format is string -> StrToTime(string, format)
            "TO_TIMESTAMP" => {
                let args = f.args;
                if args.len() == 1 {
                    let arg = &args[0];
                    match arg {
                        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(s) if Self::looks_like_datetime(s)) =>
                        {
                            let Literal::String(_) = lit.as_ref() else {
                                unreachable!()
                            };
                            // Case 1: datetime string -> CAST AS TIMESTAMP
                            return Ok(Expression::Cast(Box::new(Cast {
                                this: args.into_iter().next().unwrap(),
                                to: DataType::Timestamp {
                                    precision: None,
                                    timezone: false,
                                },
                                double_colon_syntax: false,
                                trailing_comments: vec![],
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(s) if Self::looks_like_epoch(s)) =>
                        {
                            let Literal::String(_) = lit.as_ref() else {
                                unreachable!()
                            };
                            // Case 2: epoch number as string -> UnixToTime
                            return Ok(Expression::UnixToTime(Box::new(
                                crate::expressions::UnixToTime {
                                    this: Box::new(args.into_iter().next().unwrap()),
                                    scale: None,
                                    zone: None,
                                    hours: None,
                                    minutes: None,
                                    format: None,
                                    target_type: None,
                                },
                            )));
                        }
                        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
                            // Case 3: number -> UnixToTime
                            return Ok(Expression::UnixToTime(Box::new(
                                crate::expressions::UnixToTime {
                                    this: Box::new(args.into_iter().next().unwrap()),
                                    scale: None,
                                    zone: None,
                                    hours: None,
                                    minutes: None,
                                    format: None,
                                    target_type: None,
                                },
                            )));
                        }
                        Expression::Neg(_) => {
                            // Case 3: number -> UnixToTime
                            return Ok(Expression::UnixToTime(Box::new(
                                crate::expressions::UnixToTime {
                                    this: Box::new(args.into_iter().next().unwrap()),
                                    scale: None,
                                    zone: None,
                                    hours: None,
                                    minutes: None,
                                    format: None,
                                    target_type: None,
                                },
                            )));
                        }
                        _ => {
                            // Unknown single arg, keep as function
                            return Ok(Expression::Function(Box::new(Function::new(
                                "TO_TIMESTAMP".to_string(),
                                args,
                            ))));
                        }
                    }
                } else if args.len() == 2 {
                    let second_arg = &args[1];
                    // Check if second arg is an integer (scale) or a format string
                    let is_int_scale = match second_arg {
                        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
                            let Literal::Number(n) = lit.as_ref() else {
                                unreachable!()
                            };
                            n.parse::<i64>().is_ok()
                        }
                        _ => false,
                    };

                    if is_int_scale {
                        // Case 4: TO_TIMESTAMP(number, scale) -> UnixToTime
                        let mut args_iter = args.into_iter();
                        let value = args_iter.next().unwrap();
                        let scale_expr = args_iter.next().unwrap();
                        let scale = if let Expression::Literal(lit) = &scale_expr {
                            if let Literal::Number(n) = lit.as_ref() {
                                n.parse::<i64>().ok()
                            } else {
                                None
                            }
                        } else {
                            None
                        };
                        return Ok(Expression::UnixToTime(Box::new(
                            crate::expressions::UnixToTime {
                                this: Box::new(value),
                                scale,
                                zone: None,
                                hours: None,
                                minutes: None,
                                format: None,
                                target_type: None,
                            },
                        )));
                    } else {
                        // Case 5: TO_TIMESTAMP(string, format) -> StrToTime
                        let mut args_iter = args.into_iter();
                        let value = args_iter.next().unwrap();
                        let format_expr = args_iter.next().unwrap();
                        let format_str = match &format_expr {
                            Expression::Literal(lit)
                                if matches!(lit.as_ref(), Literal::String(_)) =>
                            {
                                let Literal::String(s) = lit.as_ref() else {
                                    unreachable!()
                                };
                                s.clone()
                            }
                            _ => {
                                // Non-string format, keep as function
                                return Ok(Expression::Function(Box::new(Function::new(
                                    "TO_TIMESTAMP".to_string(),
                                    vec![value, format_expr],
                                ))));
                            }
                        };
                        // Normalize Snowflake format to target-neutral
                        let normalized_format = Self::normalize_snowflake_format(&format_str);
                        return Ok(Expression::StrToTime(Box::new(
                            crate::expressions::StrToTime {
                                this: Box::new(value),
                                format: normalized_format,
                                zone: None,
                                safe: None,
                                target_type: None,
                            },
                        )));
                    }
                }
                // More than 2 args or other cases, keep as function
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_TIMESTAMP".to_string(),
                    args,
                ))))
            }

            // Canonicalize temporal TO_CHAR so target generators can translate the
            // format model. Numeric/binary TO_CHAR overloads stay source-specific.
            "TO_CHAR"
                if f.args.len() == 2
                    && Self::is_temporal_expr(&f.args[0])
                    && matches!(&f.args[1], Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_))) =>
            {
                let mut args = f.args;
                let this = args.remove(0);
                let Literal::String(format) = (match args.remove(0) {
                    Expression::Literal(lit) => *lit,
                    _ => unreachable!(),
                }) else {
                    unreachable!()
                };

                Ok(Expression::TimeToStr(Box::new(
                    crate::expressions::TimeToStr {
                        this: Box::new(this),
                        format: Self::normalize_snowflake_format(&format),
                        culture: None,
                        zone: None,
                    },
                )))
            }

            // Other TO_CHAR overloads are native to Snowflake.
            "TO_CHAR" => Ok(Expression::Function(Box::new(f))),

            // ROUND with named args: ROUND(EXPR => x, SCALE => y, ROUNDING_MODE => z)
            // -> ROUND(x, y) or ROUND(x, y, z)
            "ROUND"
                if f.args
                    .iter()
                    .any(|a| matches!(a, Expression::NamedArgument(_))) =>
            {
                let mut expr_val = None;
                let mut scale_val = None;
                let mut rounding_mode_val = None;
                for arg in &f.args {
                    if let Expression::NamedArgument(na) = arg {
                        match na.name.name.to_uppercase().as_str() {
                            "EXPR" => expr_val = Some(na.value.clone()),
                            "SCALE" => scale_val = Some(na.value.clone()),
                            "ROUNDING_MODE" => rounding_mode_val = Some(na.value.clone()),
                            _ => {}
                        }
                    }
                }
                if let Some(expr) = expr_val {
                    let mut args = vec![expr];
                    if let Some(scale) = scale_val {
                        args.push(scale);
                    }
                    if let Some(mode) = rounding_mode_val {
                        args.push(mode);
                    }
                    Ok(Expression::Function(Box::new(Function::new(
                        "ROUND".to_string(),
                        args,
                    ))))
                } else {
                    Ok(Expression::Function(Box::new(f)))
                }
            }

            // DATE_FORMAT -> TO_CHAR in Snowflake
            // Also converts strftime format to Snowflake format and wraps first arg in CAST AS TIMESTAMP
            "DATE_FORMAT" => {
                let mut args = f.args;
                // Wrap first arg in CAST AS TIMESTAMP if it's a string literal
                if !args.is_empty() {
                    if matches!(&args[0], Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)))
                    {
                        args[0] = Expression::Cast(Box::new(crate::expressions::Cast {
                            this: args[0].clone(),
                            to: DataType::Timestamp {
                                precision: None,
                                timezone: false,
                            },
                            trailing_comments: Vec::new(),
                            double_colon_syntax: false,
                            format: None,
                            default: None,
                            inferred_type: None,
                        }));
                    }
                }
                // Convert strftime format to Snowflake format
                if args.len() >= 2 {
                    if let Expression::Literal(ref lit) = args[1] {
                        if let Literal::String(ref fmt) = lit.as_ref() {
                            let sf_fmt = strftime_to_snowflake_format(fmt);
                            args[1] = Expression::Literal(Box::new(Literal::String(sf_fmt)));
                        }
                    }
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_CHAR".to_string(),
                    args,
                ))))
            }

            // ARRAY -> ARRAY_CONSTRUCT
            "ARRAY" => Ok(Expression::Function(Box::new(Function::new(
                "ARRAY_CONSTRUCT".to_string(),
                f.args,
            )))),

            // STRUCT -> OBJECT_CONSTRUCT
            // Convert STRUCT(value AS name, ...) to OBJECT_CONSTRUCT('name', value, ...)
            "STRUCT" => {
                let mut oc_args = Vec::new();
                for arg in f.args {
                    match arg {
                        Expression::Alias(a) => {
                            // Named field: value AS name -> 'name', value
                            oc_args.push(Expression::Literal(Box::new(
                                crate::expressions::Literal::String(a.alias.name.clone()),
                            )));
                            oc_args.push(a.this);
                        }
                        other => {
                            // Unnamed field: just pass through
                            oc_args.push(other);
                        }
                    }
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "OBJECT_CONSTRUCT".to_string(),
                    oc_args,
                ))))
            }

            // JSON_EXTRACT -> GET_PATH or GET in Snowflake
            "JSON_EXTRACT" => Ok(Expression::Function(Box::new(Function::new(
                "GET_PATH".to_string(),
                f.args,
            )))),

            // JSON_EXTRACT_SCALAR -> JSON_EXTRACT_PATH_TEXT
            "JSON_EXTRACT_SCALAR" => Ok(Expression::Function(Box::new(Function::new(
                "JSON_EXTRACT_PATH_TEXT".to_string(),
                f.args,
            )))),

            // LEN -> LENGTH
            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
                f.args.into_iter().next().unwrap(),
            )))),

            // CEILING -> CEIL (both work)
            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
                this: f.args.into_iter().next().unwrap(),
                decimals: None,
                to: None,
            }))),

            // CHARINDEX -> POSITION or CHARINDEX (native)
            "CHARINDEX" => Ok(Expression::Function(Box::new(f))),

            // SPLIT is native to Snowflake - keep as-is
            "SPLIT" => Ok(Expression::Function(Box::new(f))),

            // ARRAY_AGG is native to Snowflake
            "ARRAY_AGG" => Ok(Expression::Function(Box::new(f))),

            // PARSE_JSON for JSON parsing
            "JSON_PARSE" | "PARSE_JSON" => Ok(Expression::Function(Box::new(Function::new(
                "PARSE_JSON".to_string(),
                f.args,
            )))),

            // RAND -> Rand (to use RANDOM in Snowflake)
            "RAND" => {
                let seed = f.args.first().cloned().map(Box::new);
                Ok(Expression::Rand(Box::new(crate::expressions::Rand {
                    seed,
                    lower: None,
                    upper: None,
                })))
            }

            // SHA -> SHA1
            "SHA" => Ok(Expression::Function(Box::new(Function::new(
                "SHA1".to_string(),
                f.args,
            )))),

            // APPROX_COUNT_DISTINCT is native
            "APPROX_DISTINCT" => Ok(Expression::Function(Box::new(Function::new(
                "APPROX_COUNT_DISTINCT".to_string(),
                f.args,
            )))),

            // GEN_RANDOM_UUID/UUID -> Uuid AST node
            "GEN_RANDOM_UUID" | "UUID" => {
                Ok(Expression::Uuid(Box::new(crate::expressions::Uuid {
                    this: None,
                    name: None,
                    is_string: None,
                })))
            }

            // NEWID -> Uuid AST node
            "NEWID" => Ok(Expression::Uuid(Box::new(crate::expressions::Uuid {
                this: None,
                name: None,
                is_string: None,
            }))),

            // UUID_STRING -> Uuid AST node (without args only; with args keep as Function for identity)
            "UUID_STRING" => {
                if f.args.is_empty() {
                    Ok(Expression::Uuid(Box::new(crate::expressions::Uuid {
                        this: None,
                        name: None,
                        is_string: None,
                    })))
                } else {
                    Ok(Expression::Function(Box::new(Function::new(
                        "UUID_STRING".to_string(),
                        f.args,
                    ))))
                }
            }

            // IF -> IFF (convert to IfFunc AST node)
            "IF" if f.args.len() >= 2 => {
                let mut args = f.args;
                let condition = args.remove(0);
                let true_val = args.remove(0);
                let false_val = if !args.is_empty() {
                    Some(args.remove(0))
                } else {
                    None
                };
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition,
                    true_value: true_val,
                    false_value: Some(
                        false_val.unwrap_or(Expression::Null(crate::expressions::Null)),
                    ),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // SQUARE(x) -> POWER(x, 2)
            "SQUARE" if f.args.len() == 1 => {
                let x = f.args.into_iter().next().unwrap();
                Ok(Expression::Power(Box::new(
                    crate::expressions::BinaryFunc {
                        original_name: None,
                        this: x,
                        expression: Expression::number(2),
                        inferred_type: None,
                    },
                )))
            }

            // POW(x, y) -> POWER(x, y)
            "POW" if f.args.len() == 2 => {
                let mut args = f.args.into_iter();
                let x = args.next().unwrap();
                let y = args.next().unwrap();
                Ok(Expression::Power(Box::new(
                    crate::expressions::BinaryFunc {
                        original_name: None,
                        this: x,
                        expression: y,
                        inferred_type: None,
                    },
                )))
            }

            // MOD(x, y) -> x % y (modulo operator)
            "MOD" if f.args.len() == 2 => {
                let mut args = f.args.into_iter();
                let x = args.next().unwrap();
                let y = args.next().unwrap();
                Ok(Expression::Mod(Box::new(crate::expressions::BinaryOp {
                    left: x,
                    right: y,
                    left_comments: Vec::new(),
                    operator_comments: Vec::new(),
                    trailing_comments: Vec::new(),
                    inferred_type: None,
                })))
            }

            // APPROXIMATE_JACCARD_INDEX -> APPROXIMATE_SIMILARITY
            "APPROXIMATE_JACCARD_INDEX" => Ok(Expression::Function(Box::new(Function::new(
                "APPROXIMATE_SIMILARITY".to_string(),
                f.args,
            )))),

            // ARRAY_CONSTRUCT -> Array with bracket notation in Snowflake
            "ARRAY_CONSTRUCT" => Ok(Expression::ArrayFunc(Box::new(
                crate::expressions::ArrayConstructor {
                    expressions: f.args,
                    bracket_notation: true,
                    use_list_keyword: false,
                    inferred_type: None,
                },
            ))),

            // APPROX_TOP_K - add default k=1 if not provided
            "APPROX_TOP_K" if f.args.len() == 1 => {
                let mut args = f.args;
                args.push(Expression::number(1));
                Ok(Expression::Function(Box::new(Function::new(
                    "APPROX_TOP_K".to_string(),
                    args,
                ))))
            }

            // TO_DECIMAL, TO_NUMERIC -> TO_NUMBER
            "TO_DECIMAL" | "TO_NUMERIC" => Ok(Expression::Function(Box::new(Function::new(
                "TO_NUMBER".to_string(),
                f.args,
            )))),

            // TRY_TO_DECIMAL, TRY_TO_NUMERIC -> TRY_TO_NUMBER
            "TRY_TO_DECIMAL" | "TRY_TO_NUMERIC" => Ok(Expression::Function(Box::new(
                Function::new("TRY_TO_NUMBER".to_string(), f.args),
            ))),

            // STDDEV_SAMP -> STDDEV
            "STDDEV_SAMP" => Ok(Expression::Function(Box::new(Function::new(
                "STDDEV".to_string(),
                f.args,
            )))),

            // STRTOK -> SPLIT_PART (with default delimiter and position)
            "STRTOK" if f.args.len() >= 1 => {
                let mut args = f.args;
                // Add default delimiter (space) if missing
                if args.len() == 1 {
                    args.push(Expression::string(" ".to_string()));
                }
                // Add default position (1) if missing
                if args.len() == 2 {
                    args.push(Expression::number(1));
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "STRTOK".to_string(),
                    args,
                ))))
            }

            "STRTOK_TO_ARRAY" if f.args.len() == 1 => {
                let mut args = f.args;
                args.push(Expression::string(" ".to_string()));
                Ok(Expression::Function(Box::new(Function::new(
                    "STRTOK_TO_ARRAY".to_string(),
                    args,
                ))))
            }

            // WEEKOFYEAR -> WEEK
            "WEEKOFYEAR" => Ok(Expression::Function(Box::new(Function::new(
                "WEEK".to_string(),
                f.args,
            )))),

            // LIKE(col, pattern, escape) -> col LIKE pattern ESCAPE escape
            "LIKE" if f.args.len() >= 2 => {
                let mut args = f.args.into_iter();
                let left = args.next().unwrap();
                let right = args.next().unwrap();
                let escape = args.next();
                Ok(Expression::Like(Box::new(crate::expressions::LikeOp {
                    left,
                    right,
                    escape,
                    quantifier: None,
                    inferred_type: None,
                })))
            }

            // ILIKE(col, pattern, escape) -> col ILIKE pattern ESCAPE escape
            "ILIKE" if f.args.len() >= 2 => {
                let mut args = f.args.into_iter();
                let left = args.next().unwrap();
                let right = args.next().unwrap();
                let escape = args.next();
                Ok(Expression::ILike(Box::new(crate::expressions::LikeOp {
                    left,
                    right,
                    escape,
                    quantifier: None,
                    inferred_type: None,
                })))
            }

            // RLIKE -> REGEXP_LIKE
            "RLIKE" if f.args.len() >= 2 => {
                let mut args = f.args.into_iter();
                let left = args.next().unwrap();
                let pattern = args.next().unwrap();
                let flags = args.next();
                Ok(Expression::RegexpLike(Box::new(
                    crate::expressions::RegexpFunc {
                        options: None,

                        this: left,
                        pattern,
                        flags,
                    },
                )))
            }

            // IFF -> convert to IfFunc AST node for proper cross-dialect handling
            "IFF" if f.args.len() >= 2 => {
                let mut args = f.args;
                let condition = args.remove(0);
                let true_value = args.remove(0);
                let false_value = if !args.is_empty() {
                    Some(args.remove(0))
                } else {
                    None
                };
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition,
                    true_value,
                    false_value,
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // TIMESTAMP_NTZ_FROM_PARTS, TIMESTAMPFROMPARTS, TIMESTAMPNTZFROMPARTS -> TIMESTAMP_FROM_PARTS
            "TIMESTAMP_NTZ_FROM_PARTS" | "TIMESTAMPFROMPARTS" | "TIMESTAMPNTZFROMPARTS" => {
                Ok(Expression::Function(Box::new(Function::new(
                    "TIMESTAMP_FROM_PARTS".to_string(),
                    f.args,
                ))))
            }

            // TIMESTAMPLTZFROMPARTS -> TIMESTAMP_LTZ_FROM_PARTS
            "TIMESTAMPLTZFROMPARTS" => Ok(Expression::Function(Box::new(Function::new(
                "TIMESTAMP_LTZ_FROM_PARTS".to_string(),
                f.args,
            )))),

            // TIMESTAMPTZFROMPARTS -> TIMESTAMP_TZ_FROM_PARTS
            "TIMESTAMPTZFROMPARTS" => Ok(Expression::Function(Box::new(Function::new(
                "TIMESTAMP_TZ_FROM_PARTS".to_string(),
                f.args,
            )))),

            // DATEADD with 3 args - transform the unit (first arg) using date part mapping
            "DATEADD" if f.args.len() >= 1 => {
                let mut args = f.args;
                args[0] = self.transform_date_part_arg(args[0].clone());
                Ok(Expression::Function(Box::new(Function::new(
                    "DATEADD".to_string(),
                    args,
                ))))
            }

            // DATEDIFF with 3 args - transform the unit (first arg) using date part mapping
            // Also convert _POLYGLOT_TO_DATE back to TO_DATE (from cross-dialect normalize)
            "DATEDIFF" if f.args.len() >= 1 => {
                let mut args = f.args;
                args[0] = self.transform_date_part_arg(args[0].clone());
                // Convert _POLYGLOT_TO_DATE back to TO_DATE for date args
                // (_POLYGLOT_TO_DATE is an internal marker from cross-dialect normalize)
                for i in 1..args.len() {
                    if let Expression::Function(ref func) = args[i] {
                        if func.name == "_POLYGLOT_TO_DATE" {
                            let inner_args = func.args.clone();
                            args[i] = Expression::Function(Box::new(Function::new(
                                "TO_DATE".to_string(),
                                inner_args,
                            )));
                        }
                    }
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "DATEDIFF".to_string(),
                    args,
                ))))
            }

            // TIMEDIFF -> DATEDIFF
            "TIMEDIFF" => Ok(Expression::Function(Box::new(Function::new(
                "DATEDIFF".to_string(),
                f.args,
            )))),

            // TIMESTAMPDIFF -> DATEDIFF
            "TIMESTAMPDIFF" => Ok(Expression::Function(Box::new(Function::new(
                "DATEDIFF".to_string(),
                f.args,
            )))),

            // TIMESTAMPADD -> DATEADD
            "TIMESTAMPADD" => Ok(Expression::Function(Box::new(Function::new(
                "DATEADD".to_string(),
                f.args,
            )))),

            // TIMEADD -> preserve it
            "TIMEADD" => Ok(Expression::Function(Box::new(f))),

            // DATE_FROM_PARTS, DATEFROMPARTS -> DATE_FROM_PARTS
            "DATEFROMPARTS" => Ok(Expression::Function(Box::new(Function::new(
                "DATE_FROM_PARTS".to_string(),
                f.args,
            )))),

            // TIME_FROM_PARTS, TIMEFROMPARTS -> TIME_FROM_PARTS
            "TIMEFROMPARTS" => Ok(Expression::Function(Box::new(Function::new(
                "TIME_FROM_PARTS".to_string(),
                f.args,
            )))),

            // DAYOFWEEK -> DAYOFWEEK (preserve)
            "DAYOFWEEK" => Ok(Expression::Function(Box::new(f))),

            // DAYOFMONTH -> DAYOFMONTH (preserve)
            "DAYOFMONTH" => Ok(Expression::Function(Box::new(f))),

            // DAYOFYEAR -> DAYOFYEAR (preserve)
            "DAYOFYEAR" => Ok(Expression::Function(Box::new(f))),

            // MONTHNAME -> Monthname AST node (abbreviated=true for Snowflake)
            // Target dialects can then convert to their native form
            "MONTHNAME" if f.args.len() == 1 => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Monthname(Box::new(
                    crate::expressions::Monthname {
                        this: Box::new(arg),
                        abbreviated: Some(Box::new(Expression::Literal(Box::new(
                            Literal::String("true".to_string()),
                        )))),
                    },
                )))
            }

            // DAYNAME -> Dayname AST node (abbreviated=true for Snowflake)
            // Target dialects can then convert to their native form
            "DAYNAME" if f.args.len() == 1 => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Dayname(Box::new(crate::expressions::Dayname {
                    this: Box::new(arg),
                    abbreviated: Some(Box::new(Expression::Literal(Box::new(Literal::String(
                        "true".to_string(),
                    ))))),
                })))
            }

            // BOOLAND_AGG/BOOL_AND/LOGICAL_AND -> LogicalAnd AST node
            "BOOLAND_AGG" | "BOOL_AND" | "LOGICAL_AND" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::LogicalAnd(Box::new(AggFunc {
                    this: arg,
                    distinct: false,
                    filter: None,
                    order_by: Vec::new(),
                    name: Some("BOOLAND_AGG".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // BOOLOR_AGG/BOOL_OR/LOGICAL_OR -> LogicalOr AST node
            "BOOLOR_AGG" | "BOOL_OR" | "LOGICAL_OR" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::LogicalOr(Box::new(AggFunc {
                    this: arg,
                    distinct: false,
                    filter: None,
                    order_by: Vec::new(),
                    name: Some("BOOLOR_AGG".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // SKEW -> Skewness AST node for proper cross-dialect handling
            "SKEW" | "SKEWNESS" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Skewness(Box::new(AggFunc {
                    this: arg,
                    distinct: false,
                    filter: None,
                    order_by: Vec::new(),
                    name: Some("SKEW".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // VAR_SAMP -> VARIANCE (Snowflake uses VARIANCE for sample variance)
            "VAR_SAMP" => Ok(Expression::Function(Box::new(Function::new(
                "VARIANCE".to_string(),
                f.args,
            )))),

            // VAR_POP -> VARIANCE_POP
            "VAR_POP" => Ok(Expression::Function(Box::new(Function::new(
                "VARIANCE_POP".to_string(),
                f.args,
            )))),

            // DATE(str) -> TO_DATE(str) (single-arg form)
            "DATE" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
                "TO_DATE".to_string(),
                f.args,
            )))),
            // DATE(str, format) -> TO_DATE(str, normalized_format)
            // Python SQLGlot normalizes DATE(...) to TO_DATE(...) for formatted variants.
            // But _POLYGLOT_DATE(str, format) stays as DATE() (from BigQuery PARSE_DATE conversion)
            "DATE" if f.args.len() >= 2 => {
                let mut args = f.args;
                args[1] = Self::normalize_format_arg(args[1].clone());
                Ok(Expression::Function(Box::new(Function::new(
                    "TO_DATE".to_string(),
                    args,
                ))))
            }
            // Internal marker from BigQuery PARSE_DATE -> Snowflake conversion
            // _POLYGLOT_DATE stays as DATE() (not converted to TO_DATE)
            "_POLYGLOT_DATE" if f.args.len() >= 2 => {
                let mut args = f.args;
                args[1] = Self::normalize_format_arg(args[1].clone());
                Ok(Expression::Function(Box::new(Function::new(
                    "DATE".to_string(),
                    args,
                ))))
            }

            // DESCRIBE/DESC normalization
            "DESCRIBE" => Ok(Expression::Function(Box::new(f))),

            // MD5 -> MD5 (preserve) but MD5_HEX -> MD5
            "MD5_HEX" => Ok(Expression::Function(Box::new(Function::new(
                "MD5".to_string(),
                f.args,
            )))),

            // SHA1_HEX -> SHA1
            "SHA1_HEX" => Ok(Expression::Function(Box::new(Function::new(
                "SHA1".to_string(),
                f.args,
            )))),

            // SHA2_HEX -> SHA2
            "SHA2_HEX" => Ok(Expression::Function(Box::new(Function::new(
                "SHA2".to_string(),
                f.args,
            )))),

            // EDITDISTANCE -> EDITDISTANCE (preserve Snowflake name)
            "LEVENSHTEIN" => Ok(Expression::Function(Box::new(Function::new(
                "EDITDISTANCE".to_string(),
                f.args,
            )))),

            // BIT_NOT -> BITNOT
            "BIT_NOT" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
                "BITNOT".to_string(),
                f.args,
            )))),

            // BIT_AND -> BITAND
            "BIT_AND" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
                "BITAND".to_string(),
                f.args,
            )))),

            // BIT_OR -> BITOR
            "BIT_OR" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
                "BITOR".to_string(),
                f.args,
            )))),

            // BIT_XOR -> BITXOR
            "BIT_XOR" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
                "BITXOR".to_string(),
                f.args,
            )))),

            // BIT_SHIFTLEFT -> BITSHIFTLEFT
            "BIT_SHIFTLEFT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
                Function::new("BITSHIFTLEFT".to_string(), f.args),
            ))),

            // BIT_SHIFTRIGHT -> BITSHIFTRIGHT
            "BIT_SHIFTRIGHT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
                Function::new("BITSHIFTRIGHT".to_string(), f.args),
            ))),

            // SYSTIMESTAMP -> CURRENT_TIMESTAMP (preserving parens style)
            "SYSTIMESTAMP" => Ok(Expression::Function(Box::new(Function {
                on_error: None,
                qualified_name: Vec::new(),
                source_dialect: None,

                name: "CURRENT_TIMESTAMP".to_string(),
                args: f.args,
                distinct: false,
                trailing_comments: Vec::new(),
                use_bracket_syntax: false,
                no_parens: f.no_parens,
                quoted: false,
                span: None,
                inferred_type: None,
            }))),

            // LOCALTIMESTAMP -> CURRENT_TIMESTAMP (preserving parens style)
            "LOCALTIMESTAMP" => Ok(Expression::Function(Box::new(Function {
                on_error: None,
                qualified_name: Vec::new(),
                source_dialect: None,

                name: "CURRENT_TIMESTAMP".to_string(),
                args: f.args,
                distinct: false,
                trailing_comments: Vec::new(),
                use_bracket_syntax: false,
                no_parens: f.no_parens,
                quoted: false,
                span: None,
                inferred_type: None,
            }))),

            // SPACE(n) -> REPEAT(' ', n) in Snowflake
            "SPACE" if f.args.len() == 1 => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Function(Box::new(Function::new(
                    "REPEAT".to_string(),
                    vec![
                        Expression::Literal(Box::new(Literal::String(" ".to_string()))),
                        arg,
                    ],
                ))))
            }

            // CEILING -> CEIL
            "CEILING" => Ok(Expression::Function(Box::new(Function::new(
                "CEIL".to_string(),
                f.args,
            )))),

            // LOG without base -> LN
            "LOG" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
                "LN".to_string(),
                f.args,
            )))),

            // REGEXP_SUBSTR_ALL is native to Snowflake
            "REGEXP_SUBSTR_ALL" => Ok(Expression::Function(Box::new(f))),

            // GET_PATH - transform path argument:
            // - Convert colon notation to dot notation (y[0]:z -> y[0].z)
            // - Wrap unsafe keys in brackets ($id -> ["$id"])
            "GET_PATH" if f.args.len() >= 2 => {
                let mut args = f.args;
                // Transform the path argument (second argument)
                if let Expression::Literal(lit) = &args[1] {
                    if let crate::expressions::Literal::String(path) = lit.as_ref() {
                        let transformed = Self::transform_json_path(path);
                        args[1] = Expression::Literal(Box::new(
                            crate::expressions::Literal::String(transformed),
                        ));
                    }
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "GET_PATH".to_string(),
                    args,
                ))))
            }
            "GET_PATH" => Ok(Expression::Function(Box::new(f))),

            // FLATTEN is native to Snowflake
            "FLATTEN" => Ok(Expression::Function(Box::new(f))),

            // DATE_TRUNC - transform unit to quoted string
            // DATE_TRUNC(yr, x) -> DATE_TRUNC('YEAR', x)
            "DATE_TRUNC" if f.args.len() >= 1 => {
                let mut args = f.args;
                // Transform the unit to canonical form and convert to string literal
                let unit_name = match &args[0] {
                    Expression::Identifier(id) => Some(id.name.as_str()),
                    Expression::Var(v) => Some(v.this.as_str()),
                    Expression::Column(col) if col.table.is_none() => Some(col.name.name.as_str()),
                    _ => None,
                };
                if let Some(name) = unit_name {
                    let canonical = Self::map_date_part(name).unwrap_or(name);
                    args[0] = Expression::Literal(Box::new(crate::expressions::Literal::String(
                        canonical.to_uppercase(),
                    )));
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "DATE_TRUNC".to_string(),
                    args,
                ))))
            }

            // DATE_PART - transform unit argument
            // DATE_PART(yyy, x) -> DATE_PART(YEAR, x)
            // Only convert string literals to identifiers when the second arg is a typed literal
            // (e.g., TIMESTAMP '...', DATE '...'), indicating the function came from another dialect.
            // For native Snowflake DATE_PART('month', CAST(...)), preserve the string as-is.
            "DATE_PART" if f.args.len() >= 1 => {
                let mut args = f.args;
                let from_typed_literal = args.len() >= 2
                    && matches!(
                        &args[1],
                        Expression::Literal(lit) if matches!(lit.as_ref(),
                            crate::expressions::Literal::Timestamp(_)
                            | crate::expressions::Literal::Date(_)
                            | crate::expressions::Literal::Time(_)
                            | crate::expressions::Literal::Datetime(_)
                        )
                    );
                if from_typed_literal {
                    args[0] = self.transform_date_part_arg(args[0].clone());
                } else {
                    // For non-typed-literal cases, only normalize identifiers/columns
                    // (don't convert string literals to identifiers)
                    args[0] = self.transform_date_part_arg_identifiers_only(args[0].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "DATE_PART".to_string(),
                    args,
                ))))
            }

            // OBJECT_CONSTRUCT is native to Snowflake
            "OBJECT_CONSTRUCT" => Ok(Expression::Function(Box::new(f))),

            // OBJECT_CONSTRUCT_KEEP_NULL is native to Snowflake
            "OBJECT_CONSTRUCT_KEEP_NULL" => Ok(Expression::Function(Box::new(f))),

            // DESC -> DESCRIBE
            "DESC" => Ok(Expression::Function(Box::new(Function::new(
                "DESCRIBE".to_string(),
                f.args,
            )))),

            // RLIKE -> REGEXP_LIKE
            "RLIKE" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
                "REGEXP_LIKE".to_string(),
                f.args,
            )))),

            // TRANSFORM function - handle typed lambda parameters
            // For typed lambdas like `a int -> a + 1`, we need to:
            // 1. Remove the type annotation from the parameter
            // 2. Wrap all references to the parameter in the body with CAST(param AS type)
            "TRANSFORM" => {
                let transformed_args: Vec<Expression> = f
                    .args
                    .into_iter()
                    .map(|arg| {
                        if let Expression::Lambda(lambda) = arg {
                            self.transform_typed_lambda(*lambda)
                        } else {
                            arg
                        }
                    })
                    .collect();
                Ok(Expression::Function(Box::new(Function::new(
                    "TRANSFORM".to_string(),
                    transformed_args,
                ))))
            }

            // SEARCH function - convert to Search expression with canonical parameter ordering
            "SEARCH" if f.args.len() >= 2 => {
                let mut args = f.args.into_iter();
                let this = Box::new(args.next().unwrap());
                let expression = Box::new(args.next().unwrap());

                let mut analyzer: Option<Box<Expression>> = None;
                let mut search_mode: Option<Box<Expression>> = None;

                // Parse remaining named arguments
                for arg in args {
                    if let Expression::NamedArgument(na) = &arg {
                        let name_upper = na.name.name.to_uppercase();
                        match name_upper.as_str() {
                            "ANALYZER" => analyzer = Some(Box::new(arg)),
                            "SEARCH_MODE" => search_mode = Some(Box::new(arg)),
                            _ => {}
                        }
                    }
                }

                Ok(Expression::Search(Box::new(crate::expressions::Search {
                    this,
                    expression,
                    json_scope: None,
                    analyzer,
                    analyzer_options: None,
                    search_mode,
                })))
            }

            // ODBC CONVERT function: CONVERT(value, SQL_TYPE) -> CAST(value AS TYPE)
            // This handles the { fn CONVERT(...) } ODBC escape sequence syntax
            "CONVERT" if f.args.len() == 2 => {
                let value = f.args.get(0).cloned().unwrap();
                let type_arg = f.args.get(1).cloned().unwrap();

                // Check if second argument is a SQL_ type identifier
                if let Expression::Column(col) = &type_arg {
                    let type_name = col.name.name.to_uppercase();
                    let data_type = match type_name.as_str() {
                        "SQL_DOUBLE" => Some(DataType::Double {
                            precision: None,
                            scale: None,
                        }),
                        "SQL_VARCHAR" => Some(DataType::VarChar {
                            length: None,
                            parenthesized_length: false,
                        }),
                        "SQL_INTEGER" | "SQL_INT" => Some(DataType::Int {
                            length: None,
                            integer_spelling: false,
                        }),
                        "SQL_BIGINT" => Some(DataType::BigInt { length: None }),
                        "SQL_SMALLINT" => Some(DataType::SmallInt { length: None }),
                        "SQL_FLOAT" => Some(DataType::Float {
                            precision: None,
                            scale: None,
                            real_spelling: false,
                        }),
                        "SQL_REAL" => Some(DataType::Float {
                            precision: None,
                            scale: None,
                            real_spelling: true,
                        }),
                        "SQL_DECIMAL" => Some(DataType::Decimal {
                            precision: None,
                            scale: None,
                        }),
                        "SQL_DATE" => Some(DataType::Date),
                        "SQL_TIME" => Some(DataType::Time {
                            precision: None,
                            timezone: false,
                        }),
                        "SQL_TIMESTAMP" => Some(DataType::Timestamp {
                            precision: None,
                            timezone: false,
                        }),
                        _ => None,
                    };

                    if let Some(dt) = data_type {
                        return Ok(Expression::Cast(Box::new(Cast {
                            this: value,
                            to: dt,
                            double_colon_syntax: false,
                            trailing_comments: vec![],
                            format: None,
                            default: None,
                            inferred_type: None,
                        })));
                    }
                }
                // If not a SQL_ type, keep as regular CONVERT function
                Ok(Expression::Function(Box::new(f)))
            }

            // TO_TIMESTAMP_TZ: single string arg -> CAST(... AS TIMESTAMPTZ), otherwise keep as function
            // Per Python sqlglot: _build_datetime converts TO_TIMESTAMP_TZ('string') to CAST('string' AS TIMESTAMPTZ)
            "TO_TIMESTAMP_TZ" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(_) = lit.as_ref() {
                            return Ok(Expression::Cast(Box::new(Cast {
                                this: f.args.into_iter().next().unwrap(),
                                to: DataType::Custom {
                                    name: "TIMESTAMPTZ".to_string(),
                                },
                                double_colon_syntax: false,
                                trailing_comments: vec![],
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                    }
                }
                Ok(Expression::Function(Box::new(f)))
            }

            // TO_TIMESTAMP_NTZ: single string arg -> CAST(... AS TIMESTAMPNTZ), otherwise keep as function
            "TO_TIMESTAMP_NTZ" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(_) = lit.as_ref() {
                            return Ok(Expression::Cast(Box::new(Cast {
                                this: f.args.into_iter().next().unwrap(),
                                to: DataType::Custom {
                                    name: "TIMESTAMPNTZ".to_string(),
                                },
                                double_colon_syntax: false,
                                trailing_comments: vec![],
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                    }
                }
                Ok(Expression::Function(Box::new(f)))
            }

            // TO_TIMESTAMP_LTZ: single string arg -> CAST(... AS TIMESTAMPLTZ), otherwise keep as function
            "TO_TIMESTAMP_LTZ" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(_) = lit.as_ref() {
                            return Ok(Expression::Cast(Box::new(Cast {
                                this: f.args.into_iter().next().unwrap(),
                                to: DataType::Custom {
                                    name: "TIMESTAMPLTZ".to_string(),
                                },
                                double_colon_syntax: false,
                                trailing_comments: vec![],
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                    }
                }
                Ok(Expression::Function(Box::new(f)))
            }

            // UNIFORM -> keep as-is (Snowflake-specific)
            "UNIFORM" => Ok(Expression::Function(Box::new(f))),

            // REPLACE with 2 args -> add empty string 3rd arg
            "REPLACE" if f.args.len() == 2 => {
                let mut args = f.args;
                args.push(Expression::Literal(Box::new(
                    crate::expressions::Literal::String(String::new()),
                )));
                Ok(Expression::Function(Box::new(Function::new(
                    "REPLACE".to_string(),
                    args,
                ))))
            }

            // ARBITRARY -> ANY_VALUE in Snowflake
            "ARBITRARY" => Ok(Expression::Function(Box::new(Function::new(
                "ANY_VALUE".to_string(),
                f.args,
            )))),

            // SAFE_DIVIDE(x, y) -> IFF(y <> 0, x / y, NULL)
            "SAFE_DIVIDE" if f.args.len() == 2 => {
                let mut args = f.args;
                let x = args.remove(0);
                let y = args.remove(0);
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition: Expression::Neq(Box::new(BinaryOp {
                        left: y.clone(),
                        right: Expression::number(0),
                        left_comments: Vec::new(),
                        operator_comments: Vec::new(),
                        trailing_comments: Vec::new(),
                        inferred_type: None,
                    })),
                    true_value: Expression::Div(Box::new(BinaryOp {
                        left: x,
                        right: y,
                        left_comments: Vec::new(),
                        operator_comments: Vec::new(),
                        trailing_comments: Vec::new(),
                        inferred_type: None,
                    })),
                    false_value: Some(Expression::Null(crate::expressions::Null)),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // TIMESTAMP(x) -> CAST(x AS TIMESTAMPTZ) in Snowflake
            "TIMESTAMP" if f.args.len() == 1 => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Cast(Box::new(Cast {
                    this: arg,
                    to: DataType::Custom {
                        name: "TIMESTAMPTZ".to_string(),
                    },
                    trailing_comments: Vec::new(),
                    double_colon_syntax: false,
                    format: None,
                    default: None,
                    inferred_type: None,
                })))
            }

            // TIMESTAMP(x, tz) -> CONVERT_TIMEZONE(tz, CAST(x AS TIMESTAMP)) in Snowflake
            "TIMESTAMP" if f.args.len() == 2 => {
                let mut args = f.args;
                let value = args.remove(0);
                let tz = args.remove(0);
                Ok(Expression::Function(Box::new(Function::new(
                    "CONVERT_TIMEZONE".to_string(),
                    vec![
                        tz,
                        Expression::Cast(Box::new(Cast {
                            this: value,
                            to: DataType::Timestamp {
                                precision: None,
                                timezone: false,
                            },
                            trailing_comments: Vec::new(),
                            double_colon_syntax: false,
                            format: None,
                            default: None,
                            inferred_type: None,
                        })),
                    ],
                ))))
            }

            // TIME(h, m, s) -> TIME_FROM_PARTS(h, m, s) in Snowflake
            "TIME" if f.args.len() == 3 => Ok(Expression::Function(Box::new(Function::new(
                "TIME_FROM_PARTS".to_string(),
                f.args,
            )))),

            // DIV0(x, y) -> IFF(y = 0 AND NOT x IS NULL, 0, x / y)
            "DIV0" if f.args.len() == 2 => {
                let mut args = f.args;
                let x = args.remove(0);
                let y = args.remove(0);
                // Need parens around complex expressions
                let x_expr = Self::maybe_paren(x.clone());
                let y_expr = Self::maybe_paren(y.clone());
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition: Expression::And(Box::new(BinaryOp::new(
                        Expression::Eq(Box::new(BinaryOp::new(
                            y_expr.clone(),
                            Expression::number(0),
                        ))),
                        Expression::Not(Box::new(crate::expressions::UnaryOp {
                            this: Expression::IsNull(Box::new(crate::expressions::IsNull {
                                this: x_expr.clone(),
                                not: false,
                                postfix_form: false,
                            })),
                            inferred_type: None,
                        })),
                    ))),
                    true_value: Expression::number(0),
                    false_value: Some(Expression::Div(Box::new(BinaryOp::new(x_expr, y_expr)))),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // DIV0NULL(x, y) -> IFF(y = 0 OR y IS NULL, 0, x / y)
            "DIV0NULL" if f.args.len() == 2 => {
                let mut args = f.args;
                let x = args.remove(0);
                let y = args.remove(0);
                let x_expr = Self::maybe_paren(x.clone());
                let y_expr = Self::maybe_paren(y.clone());
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition: Expression::Or(Box::new(BinaryOp::new(
                        Expression::Eq(Box::new(BinaryOp::new(
                            y_expr.clone(),
                            Expression::number(0),
                        ))),
                        Expression::IsNull(Box::new(crate::expressions::IsNull {
                            this: y_expr.clone(),
                            not: false,
                            postfix_form: false,
                        })),
                    ))),
                    true_value: Expression::number(0),
                    false_value: Some(Expression::Div(Box::new(BinaryOp::new(x_expr, y_expr)))),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // ZEROIFNULL(x) -> IFF(x IS NULL, 0, x)
            "ZEROIFNULL" if f.args.len() == 1 => {
                let x = f.args.into_iter().next().unwrap();
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition: Expression::IsNull(Box::new(crate::expressions::IsNull {
                        this: x.clone(),
                        not: false,
                        postfix_form: false,
                    })),
                    true_value: Expression::number(0),
                    false_value: Some(x),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // NULLIFZERO(x) -> IFF(x = 0, NULL, x)
            "NULLIFZERO" if f.args.len() == 1 => {
                let x = f.args.into_iter().next().unwrap();
                Ok(Expression::IfFunc(Box::new(crate::expressions::IfFunc {
                    condition: Expression::Eq(Box::new(BinaryOp::new(
                        x.clone(),
                        Expression::number(0),
                    ))),
                    true_value: Expression::Null(crate::expressions::Null),
                    false_value: Some(x),
                    original_name: Some("IFF".to_string()),
                    inferred_type: None,
                })))
            }

            // TRY_TO_TIME('string') -> TRY_CAST('string' AS TIME) when single string arg
            "TRY_TO_TIME" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(_) = lit.as_ref() {
                            return Ok(Expression::TryCast(Box::new(Cast {
                                this: f.args.into_iter().next().unwrap(),
                                to: crate::expressions::DataType::Time {
                                    precision: None,
                                    timezone: false,
                                },
                                double_colon_syntax: false,
                                trailing_comments: Vec::new(),
                                format: None,
                                default: None,
                                inferred_type: None,
                            })));
                        }
                    }
                }
                // Normalize format string (2nd arg) if present
                let mut args = f.args;
                if args.len() >= 2 {
                    args[1] = Self::normalize_format_arg(args[1].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TRY_TO_TIME".to_string(),
                    args,
                ))))
            }

            // TRY_TO_TIMESTAMP('string') -> TRY_CAST('string' AS TIMESTAMP) when single string arg
            // Convert if the string is NOT a pure numeric/epoch value
            "TRY_TO_TIMESTAMP" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(s) = lit.as_ref() {
                            if !Self::looks_like_epoch(s) {
                                return Ok(Expression::TryCast(Box::new(Cast {
                                    this: f.args.into_iter().next().unwrap(),
                                    to: DataType::Timestamp {
                                        precision: None,
                                        timezone: false,
                                    },
                                    double_colon_syntax: false,
                                    trailing_comments: Vec::new(),
                                    format: None,
                                    default: None,
                                    inferred_type: None,
                                })));
                            }
                        }
                    }
                }
                // Normalize format string (2nd arg) if present
                let mut args = f.args;
                if args.len() >= 2 {
                    args[1] = Self::normalize_format_arg(args[1].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TRY_TO_TIMESTAMP".to_string(),
                    args,
                ))))
            }

            // TRY_TO_DATE('string') -> TRY_CAST('string' AS DATE) when single string arg
            "TRY_TO_DATE" => {
                if f.args.len() == 1 {
                    if let Expression::Literal(lit) = &f.args[0] {
                        if let crate::expressions::Literal::String(s) = lit.as_ref() {
                            // Only convert if the string looks like a date
                            if s.contains('-') && s.len() >= 8 && s.len() <= 12 {
                                return Ok(Expression::TryCast(Box::new(Cast {
                                    this: f.args.into_iter().next().unwrap(),
                                    to: crate::expressions::DataType::Date,
                                    double_colon_syntax: false,
                                    trailing_comments: Vec::new(),
                                    format: None,
                                    default: None,
                                    inferred_type: None,
                                })));
                            }
                        }
                    }
                }
                // Normalize format string (2nd arg) if present
                let mut args = f.args;
                if args.len() >= 2 {
                    args[1] = Self::normalize_format_arg(args[1].clone());
                }
                Ok(Expression::Function(Box::new(Function::new(
                    "TRY_TO_DATE".to_string(),
                    args,
                ))))
            }

            // Canonicalize TRY_TO_DOUBLE so targets with nullable conversion
            // functions can preserve Snowflake's NULL-on-failure behavior.
            "TRY_TO_DOUBLE" if f.args.len() == 1 => {
                let this = f.args.into_iter().next().unwrap();
                Ok(Expression::ToDouble(Box::new(
                    crate::expressions::ToDouble {
                        this: Box::new(this),
                        format: None,
                        safe: Some(Box::new(Expression::Boolean(
                            crate::expressions::BooleanLiteral { value: true },
                        ))),
                    },
                )))
            }
            "TRY_TO_DOUBLE"
                if f.args.len() == 2
                    && matches!(&f.args[1], Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_))) =>
            {
                let mut args = f.args;
                let this = args.remove(0);
                let Literal::String(format) = (match args.remove(0) {
                    Expression::Literal(lit) => *lit,
                    _ => unreachable!(),
                }) else {
                    unreachable!()
                };
                Ok(Expression::ToDouble(Box::new(
                    crate::expressions::ToDouble {
                        this: Box::new(this),
                        format: Some(format),
                        safe: Some(Box::new(Expression::Boolean(
                            crate::expressions::BooleanLiteral { value: true },
                        ))),
                    },
                )))
            }
            "TRY_TO_DOUBLE" => Ok(Expression::Function(Box::new(f))),

            // REGEXP_REPLACE with 2 args -> add empty string replacement
            "REGEXP_REPLACE" if f.args.len() == 2 => {
                let mut args = f.args;
                args.push(Expression::Literal(Box::new(
                    crate::expressions::Literal::String(String::new()),
                )));
                Ok(Expression::Function(Box::new(Function::new(
                    "REGEXP_REPLACE".to_string(),
                    args,
                ))))
            }

            // LAST_DAY(x, MONTH) -> LAST_DAY(x) in Snowflake (strip MONTH default)
            "LAST_DAY" if f.args.len() == 2 => {
                let mut args = f.args;
                let date = args.remove(0);
                let unit = args.remove(0);
                let unit_str = match &unit {
                    Expression::Column(c) => c.name.name.to_uppercase(),
                    Expression::Identifier(i) => i.name.to_uppercase(),
                    _ => String::new(),
                };
                if unit_str == "MONTH" {
                    Ok(Expression::Function(Box::new(Function::new(
                        "LAST_DAY".to_string(),
                        vec![date],
                    ))))
                } else {
                    Ok(Expression::Function(Box::new(Function::new(
                        "LAST_DAY".to_string(),
                        vec![date, unit],
                    ))))
                }
            }

            // EXTRACT('field', expr) function-call syntax -> DATE_PART('field', expr)
            "EXTRACT" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
                "DATE_PART".to_string(),
                f.args,
            )))),

            // ENDS_WITH/ENDSWITH -> EndsWith AST node
            "ENDS_WITH" | "ENDSWITH" if f.args.len() == 2 => {
                let mut args = f.args;
                let this = args.remove(0);
                let expr = args.remove(0);
                Ok(Expression::EndsWith(Box::new(
                    crate::expressions::BinaryFunc {
                        original_name: None,
                        this,
                        expression: expr,
                        inferred_type: None,
                    },
                )))
            }

            // Pass through everything else
            _ => Ok(Expression::Function(Box::new(f))),
        }
    }

    /// Check if a string looks like a datetime (contains date separators, not just digits)
    fn looks_like_datetime(s: &str) -> bool {
        // A datetime string typically contains dashes, colons, or spaces
        // A numeric/epoch string is just digits (possibly with a dot)
        s.contains('-') || s.contains(':') || s.contains(' ') || s.contains('/')
    }

    /// Check if a string looks like an epoch number (only digits, possibly with a dot)
    fn looks_like_epoch(s: &str) -> bool {
        !s.is_empty() && s.chars().all(|c| c.is_ascii_digit() || c == '.')
    }

    /// Wrap an expression in parentheses if it's a complex expression (binary op, etc.)
    fn maybe_paren(expr: Expression) -> Expression {
        match &expr {
            Expression::Sub(_) | Expression::Add(_) | Expression::Mul(_) | Expression::Div(_) => {
                Expression::Paren(Box::new(crate::expressions::Paren {
                    this: expr,
                    trailing_comments: Vec::new(),
                }))
            }
            _ => expr,
        }
    }

    /// Normalize Snowflake date/time format strings to canonical lowercase form.
    /// YYYY -> yyyy, MM -> mm, DD -> DD (stays), HH24 -> hh24, HH12 -> hh12,
    /// MI -> mi, SS -> ss, FF -> ff, AM/PM -> pm, quoted "T" -> T
    fn normalize_snowflake_format(format: &str) -> String {
        let mut result = String::new();
        let mut cursor = crate::format_tokens::FormatTokenCursor::new(format);
        while let Some(ch) = cursor.peek() {
            // Quoted literals are copied without recognizing format tokens.
            if cursor.consume_prefix("\"", false) {
                while let Some(literal) = cursor.next_char() {
                    if literal == '"' {
                        break;
                    }
                    result.push(literal);
                }
                continue;
            }

            // Multi-char patterns (check longest first)
            if cursor.consume_prefix("YYYY", true) {
                result.push_str("yyyy");
            } else if cursor.consume_prefix("YY", true) {
                result.push_str("yy");
            } else if cursor.consume_prefix("MMMM", true) {
                result.push_str("mmmm");
            } else if cursor.consume_prefix("MON", true) {
                result.push_str("mon");
            } else if cursor.consume_prefix("MM", true) {
                result.push_str("mm");
            } else if cursor.consume_prefix("DD", true) {
                result.push_str("DD");
            } else if cursor.consume_prefix("DY", true) {
                result.push_str("dy");
            } else if cursor.consume_prefix("HH24", true) {
                result.push_str("hh24");
            } else if cursor.consume_prefix("HH12", true) {
                result.push_str("hh12");
            } else if cursor.consume_prefix("HH", true) {
                result.push_str("hh");
            } else if cursor.consume_prefix("MISS", true) {
                // MISS = MI + SS
                result.push_str("miss");
            } else if cursor.consume_prefix("MI", true) {
                result.push_str("mi");
            } else if cursor.consume_prefix("SS", true) {
                result.push_str("ss");
            } else if cursor.consume_prefix("FF", true) {
                if let Some(digit) = cursor.peek().filter(|c| c.is_ascii_digit()) {
                    result.push_str("ff");
                    result.push(digit);
                    cursor.next_char();
                } else {
                    // Plain FF normalizes to nanosecond precision.
                    result.push_str("ff9");
                }
            } else if cursor.consume_prefix("AM", true) || cursor.consume_prefix("PM", true) {
                result.push_str("pm");
            } else if cursor.consume_prefix("TZH", true) {
                result.push_str("tzh");
            } else if cursor.consume_prefix("TZM", true) {
                result.push_str("tzm");
            } else {
                // Keep separators and other characters as-is
                result.push(ch);
                cursor.next_char();
            }
        }
        result
    }

    fn is_temporal_expr(expr: &Expression) -> bool {
        match expr {
            Expression::CurrentDate(_)
            | Expression::CurrentTime(_)
            | Expression::CurrentTimestamp(_)
            | Expression::CurrentTimestampLTZ(_)
            | Expression::CurrentDatetime(_)
            | Expression::Localtime(_)
            | Expression::Localtimestamp(_)
            | Expression::Systimestamp(_)
            | Expression::UtcTime(_)
            | Expression::UtcTimestamp(_)
            | Expression::Date(_)
            | Expression::Time(_)
            | Expression::ToDate(_)
            | Expression::ToTimestamp(_)
            | Expression::DateStrToDate(_)
            | Expression::TimeStrToTime(_)
            | Expression::StrToTime(_) => true,
            Expression::Cast(cast) | Expression::TryCast(cast) | Expression::SafeCast(cast) => {
                matches!(
                    cast.to,
                    DataType::Date | DataType::Time { .. } | DataType::Timestamp { .. }
                )
            }
            Expression::Paren(paren) => Self::is_temporal_expr(&paren.this),
            other => matches!(
                other.inferred_type(),
                Some(DataType::Date | DataType::Time { .. } | DataType::Timestamp { .. })
            ),
        }
    }

    /// Normalize format string argument if it's a string literal
    fn normalize_format_arg(expr: Expression) -> Expression {
        if let Expression::Literal(lit) = &expr {
            if let crate::expressions::Literal::String(s) = lit.as_ref() {
                let normalized = Self::normalize_snowflake_format(s);
                Expression::Literal(Box::new(crate::expressions::Literal::String(normalized)))
            } else {
                expr.clone()
            }
        } else {
            expr
        }
    }

    /// Transform a lambda with typed parameters for Snowflake
    /// For `a int -> a + a + 1`, transforms to `a -> CAST(a AS INT) + CAST(a AS INT) + 1`
    fn transform_typed_lambda(&self, lambda: crate::expressions::LambdaExpr) -> Expression {
        use crate::expressions::{DataType, LambdaExpr};
        use std::collections::HashMap;

        // Build mapping of parameter names to their types
        let mut param_types: HashMap<String, DataType> = HashMap::new();
        for (i, param) in lambda.parameters.iter().enumerate() {
            if let Some(Some(dt)) = lambda.parameter_types.get(i) {
                param_types.insert(param.name.to_uppercase(), dt.clone());
            }
        }

        // If no typed parameters, return lambda unchanged
        if param_types.is_empty() {
            return Expression::Lambda(Box::new(lambda));
        }

        // Transform the body by replacing parameter references with CAST expressions
        let transformed_body = self.replace_lambda_params_with_cast(lambda.body, &param_types);

        // Return new lambda without type annotations (they're now embedded in CAST)
        Expression::Lambda(Box::new(LambdaExpr {
            parameters: lambda.parameters,
            body: transformed_body,
            colon: lambda.colon,
            parameter_types: Vec::new(), // Clear type annotations
        }))
    }

    /// Recursively replace column/identifier references to typed lambda parameters with CAST expressions
    fn replace_lambda_params_with_cast(
        &self,
        expr: Expression,
        param_types: &std::collections::HashMap<String, crate::expressions::DataType>,
    ) -> Expression {
        use crate::expressions::{BinaryOp, Cast, Paren};

        match expr {
            // Column reference - check if it matches a typed parameter
            Expression::Column(col) if col.table.is_none() => {
                let name_upper = col.name.name.to_uppercase();
                if let Some(dt) = param_types.get(&name_upper) {
                    // Wrap in CAST
                    Expression::Cast(Box::new(Cast {
                        this: Expression::Column(col),
                        to: dt.clone(),
                        double_colon_syntax: false,
                        trailing_comments: Vec::new(),
                        format: None,
                        default: None,
                        inferred_type: None,
                    }))
                } else {
                    Expression::Column(col)
                }
            }

            // Identifier reference - check if it matches a typed parameter
            Expression::Identifier(id) => {
                let name_upper = id.name.to_uppercase();
                if let Some(dt) = param_types.get(&name_upper) {
                    // Wrap in CAST
                    Expression::Cast(Box::new(Cast {
                        this: Expression::Identifier(id),
                        to: dt.clone(),
                        double_colon_syntax: false,
                        trailing_comments: Vec::new(),
                        format: None,
                        default: None,
                        inferred_type: None,
                    }))
                } else {
                    Expression::Identifier(id)
                }
            }

            // Binary operations - recursively transform both sides
            Expression::Add(op) => Expression::Add(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Sub(op) => Expression::Sub(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Mul(op) => Expression::Mul(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Div(op) => Expression::Div(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Mod(op) => Expression::Mod(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),

            // Parenthesized expression
            Expression::Paren(p) => Expression::Paren(Box::new(Paren {
                this: self.replace_lambda_params_with_cast(p.this, param_types),
                trailing_comments: p.trailing_comments,
            })),

            // Function calls - transform arguments
            Expression::Function(mut f) => {
                f.args = f
                    .args
                    .into_iter()
                    .map(|arg| self.replace_lambda_params_with_cast(arg, param_types))
                    .collect();
                Expression::Function(f)
            }

            // Comparison operators
            Expression::Eq(op) => Expression::Eq(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Neq(op) => Expression::Neq(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Lt(op) => Expression::Lt(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Lte(op) => Expression::Lte(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Gt(op) => Expression::Gt(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Gte(op) => Expression::Gte(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),

            // And/Or
            Expression::And(op) => Expression::And(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),
            Expression::Or(op) => Expression::Or(Box::new(BinaryOp::new(
                self.replace_lambda_params_with_cast(op.left, param_types),
                self.replace_lambda_params_with_cast(op.right, param_types),
            ))),

            // Other expressions - return unchanged
            other => other,
        }
    }

    fn transform_aggregate_function(
        &self,
        f: Box<crate::expressions::AggregateFunction>,
    ) -> Result<Expression> {
        let name_upper = f.name.to_uppercase();
        match name_upper.as_str() {
            // GROUP_CONCAT -> LISTAGG
            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
                Function::new("LISTAGG".to_string(), f.args),
            ))),

            // STRING_AGG -> LISTAGG
            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
                Function::new("LISTAGG".to_string(), f.args),
            ))),

            // APPROX_DISTINCT -> APPROX_COUNT_DISTINCT
            "APPROX_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
                Function::new("APPROX_COUNT_DISTINCT".to_string(), f.args),
            ))),

            // BIT_AND -> BITAND_AGG
            "BIT_AND" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
                "BITAND_AGG".to_string(),
                f.args,
            )))),

            // BIT_OR -> BITOR_AGG
            "BIT_OR" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
                "BITOR_AGG".to_string(),
                f.args,
            )))),

            // BIT_XOR -> BITXOR_AGG
            "BIT_XOR" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
                "BITXOR_AGG".to_string(),
                f.args,
            )))),

            // BOOL_AND/BOOLAND_AGG/LOGICAL_AND -> LogicalAnd AST node
            "BOOL_AND" | "LOGICAL_AND" | "BOOLAND_AGG" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::LogicalAnd(Box::new(AggFunc {
                    this: arg,
                    distinct: f.distinct,
                    filter: f.filter,
                    order_by: Vec::new(),
                    name: Some("BOOLAND_AGG".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // BOOL_OR/BOOLOR_AGG/LOGICAL_OR -> LogicalOr AST node
            "BOOL_OR" | "LOGICAL_OR" | "BOOLOR_AGG" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::LogicalOr(Box::new(AggFunc {
                    this: arg,
                    distinct: f.distinct,
                    filter: f.filter,
                    order_by: Vec::new(),
                    name: Some("BOOLOR_AGG".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // APPROX_TOP_K - add default k=1 if only one argument
            "APPROX_TOP_K" if f.args.len() == 1 => {
                let mut args = f.args;
                args.push(Expression::number(1));
                Ok(Expression::AggregateFunction(Box::new(
                    crate::expressions::AggregateFunction {
                        source_dialect: None,

                        name: "APPROX_TOP_K".to_string(),
                        args,
                        distinct: f.distinct,
                        filter: f.filter,
                        order_by: Vec::new(),
                        limit: None,
                        ignore_nulls: None,
                        inferred_type: None,
                    },
                )))
            }

            // SKEW/SKEWNESS -> Skewness AST node
            "SKEW" | "SKEWNESS" if !f.args.is_empty() => {
                let arg = f.args.into_iter().next().unwrap();
                Ok(Expression::Skewness(Box::new(AggFunc {
                    this: arg,
                    distinct: f.distinct,
                    filter: f.filter,
                    order_by: Vec::new(),
                    name: Some("SKEW".to_string()),
                    ignore_nulls: None,
                    having_max: None,
                    limit: None,
                    inferred_type: None,
                })))
            }

            // Pass through everything else
            _ => Ok(Expression::AggregateFunction(f)),
        }
    }
}

/// Convert strftime format specifiers to Snowflake format specifiers
#[cfg(feature = "transpile")]
fn strftime_to_snowflake_format(fmt: &str) -> String {
    let mut result = String::new();
    let chars: Vec<char> = fmt.chars().collect();
    let mut i = 0;
    while i < chars.len() {
        if chars[i] == '%' && i + 1 < chars.len() {
            match chars[i + 1] {
                'Y' => {
                    result.push_str("yyyy");
                    i += 2;
                }
                'y' => {
                    result.push_str("yy");
                    i += 2;
                }
                'm' => {
                    result.push_str("mm");
                    i += 2;
                }
                'd' => {
                    result.push_str("DD");
                    i += 2;
                }
                'H' => {
                    result.push_str("hh24");
                    i += 2;
                }
                'M' => {
                    result.push_str("mmmm");
                    i += 2;
                } // %M = full month name
                'i' => {
                    result.push_str("mi");
                    i += 2;
                }
                'S' | 's' => {
                    result.push_str("ss");
                    i += 2;
                }
                'f' => {
                    result.push_str("ff");
                    i += 2;
                }
                'w' => {
                    result.push_str("dy");
                    i += 2;
                } // day of week number
                'a' => {
                    result.push_str("DY");
                    i += 2;
                } // abbreviated day name
                'b' => {
                    result.push_str("mon");
                    i += 2;
                } // abbreviated month name
                'T' => {
                    result.push_str("hh24:mi:ss");
                    i += 2;
                } // time shorthand
                _ => {
                    result.push(chars[i]);
                    result.push(chars[i + 1]);
                    i += 2;
                }
            }
        } else {
            result.push(chars[i]);
            i += 1;
        }
    }
    result
}

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

    #[test]
    fn test_date_time_format_normalization_preserves_unicode() {
        for literal in ["é", "年", "🦀", "e\u{301}", "ß", "ſ"] {
            assert_eq!(
                SnowflakeDialect::normalize_snowflake_format(literal),
                literal
            );
            assert_eq!(
                SnowflakeDialect::normalize_snowflake_format(&format!(
                    "{literal}YYYY{literal}mM{literal}"
                )),
                format!("{literal}yyyy{literal}mm{literal}")
            );
        }
        for (input, expected) in [
            ("", ""),
            (
                "YYYY YY MMMM MON MM DD DY HH24 HH12 HH MISS MI SS AM PM TZH TZM",
                "yyyy yy mmmm mon mm DD dy hh24 hh12 hh miss mi ss pm pm tzh tzm",
            ),
            (
                "FF FF0 FF1 FF6 FF7 FF9 FF69",
                "ff9 ff0 ff1 ff6 ff7 ff9 ff69",
            ),
            ("éFF9年YYYY", "éff9年yyyy"),
            ("\"éYYYY🦀\"YYYY", "éYYYY🦀yyyy"),
            ("YYYY\"年", "yyyy年"),
            ("\"\"YYYY\"T\"HH24", "yyyyThh24"),
            ("q %-q %", "q %-q %"),
        ] {
            assert_eq!(
                SnowflakeDialect::normalize_snowflake_format(input),
                expected,
                "{input}"
            );
        }
    }

    fn transpile_to_snowflake(sql: &str) -> String {
        let dialect = Dialect::get(DialectType::Generic);
        let result = dialect
            .transpile(sql, DialectType::Snowflake)
            .expect("Transpile failed");
        result[0].clone()
    }

    #[test]
    fn test_ifnull_to_coalesce() {
        let result = transpile_to_snowflake("SELECT IFNULL(a, b)");
        assert!(
            result.contains("COALESCE"),
            "Expected COALESCE, got: {}",
            result
        );
    }

    #[test]
    fn test_basic_select() {
        let result = transpile_to_snowflake("SELECT a, b FROM users WHERE id = 1");
        assert!(result.contains("SELECT"));
        assert!(result.contains("FROM users"));
    }

    #[test]
    fn test_extract_string_comma_syntax_remains_date_part() {
        assert_eq!(
            transpile_to_snowflake("SELECT EXTRACT('month', a)"),
            "SELECT DATE_PART('month', a)"
        );
    }

    #[test]
    fn test_snowflake_scripting_cursor_declare_block_roundtrip() {
        let sql = "DECLARE
  emp CURSOR FOR SELECT salary FROM employees;
BEGIN
  RETURN 1;
END";

        let dialect = Dialect::get(DialectType::Snowflake);
        let ast = dialect.parse(sql).expect("Parse failed");
        let output = dialect.generate(&ast[0]).expect("Generate failed");

        assert_eq!(output, sql);
    }

    #[test]
    fn test_snowflake_scripting_cursor_return_table_roundtrip() {
        let sql = "DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END";

        let dialect = Dialect::get(DialectType::Snowflake);
        let ast = dialect.parse(sql).expect("Parse failed");
        let output = dialect.generate(&ast[0]).expect("Generate failed");

        assert_eq!(output, sql);
    }

    #[test]
    fn test_group_concat_to_listagg() {
        let result = transpile_to_snowflake("SELECT GROUP_CONCAT(name)");
        assert!(
            result.contains("LISTAGG"),
            "Expected LISTAGG, got: {}",
            result
        );
    }

    #[test]
    fn test_string_agg_to_listagg() {
        let result = transpile_to_snowflake("SELECT STRING_AGG(name)");
        assert!(
            result.contains("LISTAGG"),
            "Expected LISTAGG, got: {}",
            result
        );
    }

    #[test]
    fn test_array_to_array_construct() {
        let result = transpile_to_snowflake("SELECT ARRAY(1, 2, 3)");
        // ARRAY(1, 2, 3) from Generic -> Snowflake uses [] bracket notation
        assert!(
            result.contains("[1, 2, 3]"),
            "Expected [1, 2, 3], got: {}",
            result
        );
    }

    #[test]
    fn test_double_quote_identifiers() {
        // Snowflake uses double quotes for identifiers
        let dialect = SnowflakeDialect;
        let config = dialect.generator_config();
        assert_eq!(config.identifier_quote, '"');
    }
}