ferriorm_codegen/

model.rs

//! Generates per-model Rust modules (struct, filters, data inputs, ordering, CRUD).
//!
//! For each model in the schema, this module produces:
//!
//! - A **data struct** (e.g., `User`) with `sqlx::FromRow` and serde derives.
//! - A **filter submodule** with `WhereInput` and `WhereUniqueInput` types.
//! - A **data submodule** with `CreateInput` and `UpdateInput` types.
//! - An **order submodule** with `OrderByInput`.
//! - An **`Actions` struct** exposing `create`, `find_unique`, `find_many`,
//!   `update`, `delete`, `upsert`, and batch operations.
//! - **Query builder structs** that chain filters, ordering, pagination, and
//!   include clauses before calling `.exec()`.

use ferriorm_core::schema::{Field, FieldKind, Model};
use ferriorm_core::types::ScalarType;
use ferriorm_core::utils::{to_pascal_case, to_snake_case};
use proc_macro2::TokenStream;
use quote::{format_ident, quote};

use crate::rust_type::{ModuleDepth, filter_type_tokens, rust_type_tokens};

/// Generate the complete module for a single model.
#[must_use]
pub fn generate_model_module(model: &Model) -> TokenStream {
    let scalar_fields: Vec<&Field> = model.fields.iter().filter(|f| f.is_scalar()).collect();

    let data_struct = gen_data_struct(model, &scalar_fields);
    let filter_module = gen_filter_module(model, &scalar_fields);
    let data_module = gen_data_module(model, &scalar_fields);
    let order_module = gen_order_module(model, &scalar_fields);
    let actions_struct = gen_actions(model, &scalar_fields);
    let query_builders = gen_query_builders(model, &scalar_fields);
    let aggregate_types = gen_aggregate_types(model, &scalar_fields);
    let groupby_types = gen_groupby_types(model, &scalar_fields);
    let select_types = gen_select_types(model, &scalar_fields);

    quote! {
        #![allow(unused_imports, dead_code, unused_variables, clippy::all, clippy::pedantic, clippy::nursery)]

        use serde::{Deserialize, Serialize};
        use ferriorm_runtime::prelude::*;
        use ferriorm_runtime::prelude::sqlx;
        use ferriorm_runtime::prelude::chrono;
        use ferriorm_runtime::prelude::uuid;

        #data_struct
        #filter_module
        #data_module
        #order_module
        #actions_struct
        #query_builders
        #aggregate_types
        #groupby_types
        #select_types
    }
}

// ─── Data Struct ──────────────────────────────────────────────

fn gen_data_struct(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let struct_name = format_ident!("{}", model.name);
    let table_name = &model.db_name;

    let fields: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let ty = rust_type_tokens(f, ModuleDepth::TopLevel);
            let db_name = &f.db_name;
            if db_name == &to_snake_case(&f.name) {
                quote! { pub #name: #ty }
            } else {
                quote! { #[sqlx(rename = #db_name)] pub #name: #ty }
            }
        })
        .collect();

    quote! {
        #[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
        #[sqlx(rename_all = "snake_case")]
        pub struct #struct_name {
            #(#fields),*
        }

        impl #struct_name {
            pub const TABLE_NAME: &'static str = #table_name;
        }
    }
}

// ─── Filter Module ────────────────────────────────────────────

#[allow(clippy::too_many_lines)]
fn gen_filter_module(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let where_input = format_ident!("{}WhereInput", model.name);
    let where_unique = format_ident!("{}WhereUniqueInput", model.name);

    let where_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .filter_map(|f| {
            let filter_ty = filter_type_tokens(f, ModuleDepth::Nested)?;
            let name = format_ident!("{}", to_snake_case(&f.name));
            Some(quote! { pub #name: Option<#filter_ty> })
        })
        .collect();

    let single_unique_variants: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| f.is_id || f.is_unique)
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let ty = rust_type_tokens(f, ModuleDepth::Nested);
            quote! { #variant(#ty) }
        })
        .collect();

    let compound_unique_variants: Vec<TokenStream> = model
        .unique_constraints
        .iter()
        .map(|uc| {
            let variant = format_ident!("{}", compound_variant_name(&uc.fields));
            let struct_fields = compound_variant_fields(model, &uc.fields);
            quote! { #variant { #(#struct_fields),* } }
        })
        .collect();

    let unique_variants: Vec<TokenStream> = single_unique_variants
        .into_iter()
        .chain(compound_unique_variants)
        .collect();

    // Generate build_where for WhereInput
    let db_bounds = collect_db_bounds(scalar_fields);
    let where_arms = gen_where_arms(scalar_fields);
    let unique_arms = gen_unique_where_arms(model, scalar_fields);
    let conflict_target_arms = gen_conflict_target_arms(model, scalar_fields);
    let first_conflict_col_arms = gen_first_conflict_col_arms(model, scalar_fields);

    quote! {
        pub mod filter {
            use ferriorm_runtime::prelude::*;

            #[derive(Debug, Clone, Default)]
            pub struct #where_input {
                #(#where_fields,)*
                pub and: Option<Vec<#where_input>>,
                pub or: Option<Vec<#where_input>>,
                pub not: Option<Box<#where_input>>,
            }

            #[derive(Debug, Clone)]
            pub enum #where_unique {
                #(#unique_variants),*
            }

            impl #where_input {
                pub(crate) fn build_where<'args, DB: sqlx::Database>(
                    &self,
                    qb: &mut sqlx::QueryBuilder<'args, DB>,
                )
                where
                    #(#db_bounds,)*
                {
                    #(#where_arms)*

                    if let Some(conditions) = &self.and {
                        for c in conditions {
                            c.build_where(qb);
                        }
                    }
                    if let Some(conditions) = &self.or {
                        if !conditions.is_empty() {
                            qb.push(" AND (");
                            for (i, c) in conditions.iter().enumerate() {
                                if i > 0 { qb.push(" OR "); }
                                qb.push("(1=1");
                                c.build_where(qb);
                                qb.push(")");
                            }
                            qb.push(")");
                        }
                    }
                    if let Some(c) = &self.not {
                        qb.push(" AND NOT (1=1");
                        c.build_where(qb);
                        qb.push(")");
                    }
                }
            }

            impl #where_unique {
                pub(crate) fn build_where<'args, DB: sqlx::Database>(
                    &self,
                    qb: &mut sqlx::QueryBuilder<'args, DB>,
                )
                where
                    #(#db_bounds,)*
                {
                    match self {
                        #(#unique_arms)*
                    }
                }
            }

            impl #where_unique {
                #[allow(dead_code)]
                pub(crate) fn conflict_target(&self) -> &'static str {
                    match self {
                        #(#conflict_target_arms)*
                    }
                }

                #[allow(dead_code)]
                pub(crate) fn first_conflict_col(&self) -> &'static str {
                    match self {
                        #(#first_conflict_col_arms)*
                    }
                }
            }
        }
    }
}

/// Collect the sqlx type bounds needed for all scalar types used by the model.
fn collect_db_bounds(scalar_fields: &[&Field]) -> Vec<TokenStream> {
    let mut seen = std::collections::HashSet::new();
    let mut bounds = Vec::new();

    // Always need i64 for LIMIT/OFFSET
    seen.insert("i64");
    bounds.push(quote! { i64: sqlx::Type<DB> + for<'e> sqlx::Encode<'e, DB> });

    for f in scalar_fields {
        match &f.field_type {
            FieldKind::Scalar(scalar) => {
                let key = scalar.rust_type();
                if seen.insert(key)
                    && let Some(ty) = scalar_bound_tokens(scalar)
                {
                    bounds.push(quote! { #ty: sqlx::Type<DB> + for<'e> sqlx::Encode<'e, DB> });
                    // Also add Option<T> bound for nullable field support
                    bounds.push(
                        quote! { Option<#ty>: sqlx::Type<DB> + for<'e> sqlx::Encode<'e, DB> },
                    );
                }
            }
            FieldKind::Enum(_) | FieldKind::Model(_) => {}
        }
    }

    bounds
}

fn scalar_bound_tokens(scalar: &ScalarType) -> Option<TokenStream> {
    match scalar {
        ScalarType::String => Some(quote! { String }),
        ScalarType::Int => Some(quote! { i32 }),
        ScalarType::BigInt => Some(quote! { i64 }),
        ScalarType::Float => Some(quote! { f64 }),
        ScalarType::Boolean => Some(quote! { bool }),
        ScalarType::DateTime => Some(quote! { chrono::DateTime<chrono::Utc> }),
        ScalarType::Bytes => Some(quote! { Vec<u8> }),
        ScalarType::Json | ScalarType::Decimal => None,
    }
}

/// Generate where-clause arms for each filterable scalar field.
fn gen_where_arms(scalar_fields: &[&Field]) -> Vec<TokenStream> {
    scalar_fields
        .iter()
        .filter_map(|f| {
            // Only generate filter arms for scalar types (skip enums for now)
            if !matches!(&f.field_type, FieldKind::Scalar(_)) {
                return None;
            }
            let field_ident = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            let is_string = matches!(&f.field_type, FieldKind::Scalar(ScalarType::String));
            let is_comparable = matches!(
                &f.field_type,
                FieldKind::Scalar(
                    ScalarType::Int | ScalarType::BigInt | ScalarType::Float | ScalarType::DateTime
                )
            );

            let mut arms = vec![];

            if f.is_optional {
                // Nullable filter: `equals`/`not` are `Option<Option<T>>`.
                // `Some(None)` means IS NULL / IS NOT NULL; `Some(Some(v))`
                // is the ordinary `= ?` / `!= ?` comparison.
                arms.push(quote! {
                    if let Some(v) = &filter.equals {
                        match v {
                            None => {
                                qb.push(concat!(" AND \"", #db_name, "\" IS NULL"));
                            }
                            Some(inner) => {
                                qb.push(concat!(" AND \"", #db_name, "\" = "));
                                qb.push_bind(inner.clone());
                            }
                        }
                    }
                    if let Some(v) = &filter.not {
                        match v {
                            None => {
                                qb.push(concat!(" AND \"", #db_name, "\" IS NOT NULL"));
                            }
                            Some(inner) => {
                                qb.push(concat!(" AND \"", #db_name, "\" != "));
                                qb.push_bind(inner.clone());
                            }
                        }
                    }
                });
            } else {
                arms.push(quote! {
                    if let Some(v) = &filter.equals {
                        qb.push(concat!(" AND \"", #db_name, "\" = "));
                        qb.push_bind(v.clone());
                    }
                    if let Some(v) = &filter.not {
                        qb.push(concat!(" AND \"", #db_name, "\" != "));
                        qb.push_bind(v.clone());
                    }
                });
            }

            if is_string {
                // `like_escape` quotes %, _, and \ in user input so they
                // match themselves; `ESCAPE '\\'` tells the DB to treat
                // backslash as the escape character. Without this the
                // query `contains: "100%_safe"` would also match
                // arbitrary strings like `100Xsafe`.
                arms.push(quote! {
                    if let Some(v) = &filter.contains {
                        qb.push(concat!(" AND \"", #db_name, "\" LIKE "));
                        qb.push_bind(format!("%{}%", ferriorm_runtime::filter::like_escape(v)));
                        qb.push(" ESCAPE '\\'");
                    }
                    if let Some(v) = &filter.starts_with {
                        qb.push(concat!(" AND \"", #db_name, "\" LIKE "));
                        qb.push_bind(format!("{}%", ferriorm_runtime::filter::like_escape(v)));
                        qb.push(" ESCAPE '\\'");
                    }
                    if let Some(v) = &filter.ends_with {
                        qb.push(concat!(" AND \"", #db_name, "\" LIKE "));
                        qb.push_bind(format!("%{}", ferriorm_runtime::filter::like_escape(v)));
                        qb.push(" ESCAPE '\\'");
                    }
                });
            }

            if is_comparable {
                arms.push(quote! {
                    if let Some(v) = &filter.gt {
                        qb.push(concat!(" AND \"", #db_name, "\" > "));
                        qb.push_bind(v.clone());
                    }
                    if let Some(v) = &filter.gte {
                        qb.push(concat!(" AND \"", #db_name, "\" >= "));
                        qb.push_bind(v.clone());
                    }
                    if let Some(v) = &filter.lt {
                        qb.push(concat!(" AND \"", #db_name, "\" < "));
                        qb.push_bind(v.clone());
                    }
                    if let Some(v) = &filter.lte {
                        qb.push(concat!(" AND \"", #db_name, "\" <= "));
                        qb.push_bind(v.clone());
                    }
                });
            }

            Some(quote! {
                if let Some(filter) = &self.#field_ident {
                    #(#arms)*
                }
            })
        })
        .collect()
}

fn gen_unique_where_arms(model: &Model, scalar_fields: &[&Field]) -> Vec<TokenStream> {
    let mut arms: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| f.is_id || f.is_unique)
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                Self::#variant(v) => {
                    qb.push(concat!(" AND \"", #db_name, "\" = "));
                    qb.push_bind(v.clone());
                }
            }
        })
        .collect();

    for uc in &model.unique_constraints {
        let variant = format_ident!("{}", compound_variant_name(&uc.fields));
        let idents: Vec<_> = uc
            .fields
            .iter()
            .map(|name| format_ident!("{}", to_snake_case(name)))
            .collect();
        let binds: Vec<TokenStream> = uc
            .fields
            .iter()
            .map(|name| {
                let ident = format_ident!("{}", to_snake_case(name));
                let db_name = resolve_db_name(model, name);
                quote! {
                    qb.push(concat!(" AND \"", #db_name, "\" = "));
                    qb.push_bind(#ident.clone());
                }
            })
            .collect();
        arms.push(quote! {
            Self::#variant { #(#idents),* } => {
                #(#binds)*
            }
        });
    }

    arms
}

fn gen_conflict_target_arms(model: &Model, scalar_fields: &[&Field]) -> Vec<TokenStream> {
    let mut arms: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| f.is_id || f.is_unique)
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let target = format!("(\"{}\")", f.db_name);
            quote! { Self::#variant(_) => #target, }
        })
        .collect();

    for uc in &model.unique_constraints {
        let variant = format_ident!("{}", compound_variant_name(&uc.fields));
        let cols: Vec<String> = uc
            .fields
            .iter()
            .map(|n| format!("\"{}\"", resolve_db_name(model, n)))
            .collect();
        let target = format!("({})", cols.join(", "));
        arms.push(quote! { Self::#variant { .. } => #target, });
    }

    arms
}

fn gen_first_conflict_col_arms(model: &Model, scalar_fields: &[&Field]) -> Vec<TokenStream> {
    let mut arms: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| f.is_id || f.is_unique)
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let col = format!("\"{}\"", f.db_name);
            quote! { Self::#variant(_) => #col, }
        })
        .collect();

    for uc in &model.unique_constraints {
        let variant = format_ident!("{}", compound_variant_name(&uc.fields));
        let first = uc
            .fields
            .first()
            .map_or_else(String::new, |n| resolve_db_name(model, n));
        let col = format!("\"{first}\"");
        arms.push(quote! { Self::#variant { .. } => #col, });
    }

    arms
}

/// `PascalCase` concatenation of the field names.
fn compound_variant_name(fields: &[String]) -> String {
    fields.iter().map(|f| to_pascal_case(f)).collect()
}

/// Struct-field tokens for a compound variant: `ident: Ty` per field.
/// Enum struct-variant fields inherit the enum's visibility; no `pub` here.
fn compound_variant_fields(model: &Model, fields: &[String]) -> Vec<TokenStream> {
    fields
        .iter()
        .filter_map(|field_name| {
            let field = model.fields.iter().find(|f| f.name == *field_name)?;
            let ident = format_ident!("{}", to_snake_case(field_name));
            let ty = rust_type_tokens(field, ModuleDepth::Nested);
            Some(quote! { #ident: #ty })
        })
        .collect()
}

/// Resolve a schema field name to its `db_name`, falling back to `snake_case`.
fn resolve_db_name(model: &Model, field_name: &str) -> String {
    model
        .fields
        .iter()
        .find(|f| f.name == field_name)
        .map_or_else(|| to_snake_case(field_name), |f| f.db_name.clone())
}

// ─── Data Module ──────────────────────────────────────────────

fn gen_data_module(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let create_name = format_ident!("{}CreateInput", model.name);
    let update_name = format_ident!("{}UpdateInput", model.name);

    let required_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| !f.has_default() && !f.is_updated_at)
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let ty = rust_type_tokens(f, ModuleDepth::Nested);
            quote! { pub #name: #ty }
        })
        .collect();

    let optional_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| f.has_default() && !f.is_updated_at)
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let base_ty = rust_type_tokens(f, ModuleDepth::Nested);
            quote! { pub #name: Option<#base_ty> }
        })
        .collect();

    let update_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .filter(|f| !f.is_id && !f.is_updated_at)
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let ty = rust_type_tokens(f, ModuleDepth::Nested);
            quote! { pub #name: Option<SetValue<#ty>> }
        })
        .collect();

    quote! {
        pub mod data {
            use ferriorm_runtime::prelude::*;

            #[derive(Debug, Clone)]
            pub struct #create_name {
                #(#required_fields,)*
                #(#optional_fields,)*
            }

            /// Update payload. Each field is `Option<SetValue<T>>`:
            /// `None` leaves the column untouched (omitted from the SET clause),
            /// `Some(SetValue::Set(v))` writes `v`.
            #[derive(Debug, Clone, Default)]
            pub struct #update_name {
                #(#update_fields,)*
            }
        }
    }
}

// ─── Order Module ─────────────────────────────────────────────

fn gen_order_module(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let order_name = format_ident!("{}OrderByInput", model.name);

    let variants: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            quote! { #variant(SortOrder) }
        })
        .collect();

    let order_arms: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                Self::#variant(order) => {
                    qb.push(concat!("\"", #db_name, "\" "));
                    qb.push(order.as_sql());
                }
            }
        })
        .collect();

    quote! {
        pub mod order {
            use ferriorm_runtime::prelude::*;

            #[derive(Debug, Clone)]
            pub enum #order_name {
                #(#variants),*
            }

            impl #order_name {
                pub(crate) fn build_order_by<'args, DB: sqlx::Database>(
                    &self,
                    qb: &mut sqlx::QueryBuilder<'args, DB>,
                ) {
                    match self {
                        #(#order_arms)*
                    }
                }
            }
        }
    }
}

// ─── Actions ──────────────────────────────────────────────────

fn gen_actions(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let _model_ident = format_ident!("{}", model.name);
    let actions_name = format_ident!("{}Actions", model.name);
    let where_input = format_ident!("{}WhereInput", model.name);
    let where_unique = format_ident!("{}WhereUniqueInput", model.name);
    let create_input = format_ident!("{}CreateInput", model.name);
    let update_input = format_ident!("{}UpdateInput", model.name);
    let _order_by = format_ident!("{}OrderByInput", model.name);

    // Only generate aggregate() if there are aggregatable fields
    let has_agg_fields = scalar_fields.iter().any(|f| {
        matches!(
            &f.field_type,
            FieldKind::Scalar(
                ScalarType::Int | ScalarType::BigInt | ScalarType::Float | ScalarType::DateTime
            )
        )
    });
    let aggregate_method = if has_agg_fields {
        quote! {
            pub fn aggregate(&self, r#where: filter::#where_input) -> AggregateQuery<'a> {
                AggregateQuery { client: self.client, r#where, ops: vec![] }
            }
        }
    } else {
        quote! {}
    };

    // Only generate group_by() if there are groupable fields
    let has_group_fields = scalar_fields.iter().any(|f| is_groupable(f));
    let groupby_field_name = format_ident!("{}GroupByField", model.name);
    let group_by_method = if has_group_fields {
        quote! {
            pub fn group_by(&self, keys: Vec<#groupby_field_name>) -> GroupByQuery<'a> {
                GroupByQuery {
                    client: self.client,
                    r#where: filter::#where_input::default(),
                    group_keys: keys,
                    agg_ops: vec![],
                    count: false,
                    having: None,
                }
            }
        }
    } else {
        quote! {}
    };

    quote! {
        pub struct #actions_name<'a> {
            client: &'a DatabaseClient,
        }

        impl<'a> #actions_name<'a> {
            pub fn new(client: &'a DatabaseClient) -> Self { Self { client } }

            pub fn find_unique(&self, r#where: filter::#where_unique) -> FindUniqueQuery<'a> {
                FindUniqueQuery { client: self.client, r#where }
            }

            pub fn find_first(&self, r#where: filter::#where_input) -> FindFirstQuery<'a> {
                FindFirstQuery { client: self.client, r#where, order_by: vec![] }
            }

            pub fn find_many(&self, r#where: filter::#where_input) -> FindManyQuery<'a> {
                FindManyQuery { client: self.client, r#where, order_by: vec![], skip: None, take: None }
            }

            pub fn create(&self, data: data::#create_input) -> CreateQuery<'a> {
                CreateQuery { client: self.client, data }
            }

            pub fn update(&self, r#where: filter::#where_unique, data: data::#update_input) -> UpdateQuery<'a> {
                UpdateQuery { client: self.client, r#where, data }
            }

            /// Like [`update`], but accepts a full `WhereInput` so additional
            /// predicates (e.g., `status = 'pending'`) can be used for
            /// compare-and-swap updates. Returns `Ok(None)` if no row matched.
            pub fn update_first(&self, r#where: filter::#where_input, data: data::#update_input) -> UpdateFirstQuery<'a> {
                UpdateFirstQuery { client: self.client, r#where, data }
            }

            pub fn delete(&self, r#where: filter::#where_unique) -> DeleteQuery<'a> {
                DeleteQuery { client: self.client, r#where }
            }

            pub fn count(&self, r#where: filter::#where_input) -> CountQuery<'a> {
                CountQuery { client: self.client, r#where }
            }

            pub fn create_many(&self, data: Vec<data::#create_input>) -> CreateManyQuery<'a> {
                CreateManyQuery { client: self.client, data }
            }

            pub fn update_many(&self, r#where: filter::#where_input, data: data::#update_input) -> UpdateManyQuery<'a> {
                UpdateManyQuery { client: self.client, r#where, data }
            }

            pub fn delete_many(&self, r#where: filter::#where_input) -> DeleteManyQuery<'a> {
                DeleteManyQuery { client: self.client, r#where }
            }

            pub fn upsert(
                &self,
                r#where: filter::#where_unique,
                create: data::#create_input,
                update: data::#update_input,
            ) -> UpsertQuery<'a> {
                UpsertQuery { client: self.client, r#where, create, update }
            }

            #aggregate_method

            #group_by_method
        }
    }
}

// ─── Query Builders with exec() ──────────────────────────────

#[allow(clippy::too_many_lines)]
fn gen_query_builders(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let model_ident = format_ident!("{}", model.name);
    let table_name = &model.db_name;
    let _where_input = format_ident!("{}WhereInput", model.name);
    let _where_unique = format_ident!("{}WhereUniqueInput", model.name);
    let _create_input = format_ident!("{}CreateInput", model.name);
    let _update_input = format_ident!("{}UpdateInput", model.name);
    let order_by = format_ident!("{}OrderByInput", model.name);
    let _select_struct = format_ident!("{}Select", model.name);
    let _partial_struct = format_ident!("{}Partial", model.name);
    let _aggregate_result = format_ident!("{}AggregateResult", model.name);
    let _aggregate_field = format_ident!("{}AggregateField", model.name);
    let db_bounds = collect_db_bounds(scalar_fields);

    let select_sql = format!(r#"SELECT * FROM "{table_name}" WHERE 1=1"#);
    let count_sql = format!(r#"SELECT COUNT(*) as "count" FROM "{table_name}" WHERE 1=1"#);
    let delete_sql = format!(r#"DELETE FROM "{table_name}" WHERE 1=1"#);

    let insert_code = gen_insert_code(model, scalar_fields, table_name);
    let insert_ignore_code = gen_insert_ignore_code(model, scalar_fields, table_name);
    let update_code = gen_update_code(model, scalar_fields, table_name);
    let update_first_code = gen_update_first_code(model, scalar_fields, table_name);
    let update_many_code = gen_update_many_code(model, scalar_fields, table_name);
    let upsert_code = gen_upsert_code(model, scalar_fields, table_name);

    quote! {
        // ── Generic helper: build ORDER BY clause ──────────────
        fn build_order_by<'args, DB: sqlx::Database>(
            orders: &[order::#order_by],
            qb: &mut sqlx::QueryBuilder<'args, DB>,
        ) {
            if !orders.is_empty() {
                qb.push(" ORDER BY ");
                for (i, ob) in orders.iter().enumerate() {
                    if i > 0 { qb.push(", "); }
                    ob.build_order_by(qb);
                }
            }
        }

        // ── Generic helper: build a SELECT query ───────────────
        fn build_select_query<'args, DB: sqlx::Database>(
            base_sql: &str,
            where_input: &filter::#_where_input,
            orders: &[order::#order_by],
            take: Option<i64>,
            skip: Option<i64>,
        ) -> sqlx::QueryBuilder<'args, DB>
        where
            #(#db_bounds,)*
        {
            let mut qb = sqlx::QueryBuilder::<DB>::new(base_sql);
            where_input.build_where(&mut qb);
            build_order_by(orders, &mut qb);
            if let Some(take) = take {
                qb.push(" LIMIT ");
                qb.push_bind(take);
            }
            if let Some(skip) = skip {
                qb.push(" OFFSET ");
                qb.push_bind(skip);
            }
            qb
        }

        // ── Generic helper: build a SELECT query for unique lookup ──
        fn build_unique_select_query<'args, DB: sqlx::Database>(
            base_sql: &str,
            where_unique: &filter::#_where_unique,
        ) -> sqlx::QueryBuilder<'args, DB>
        where
            #(#db_bounds,)*
        {
            let mut qb = sqlx::QueryBuilder::<DB>::new(base_sql);
            where_unique.build_where(&mut qb);
            qb.push(" LIMIT 1");
            qb
        }

        // ── Generic helper: build a DELETE-returning query ─────
        fn build_delete_query<'args, DB: sqlx::Database>(
            base_sql: &str,
            where_unique: &filter::#_where_unique,
        ) -> sqlx::QueryBuilder<'args, DB>
        where
            #(#db_bounds,)*
        {
            let mut qb = sqlx::QueryBuilder::<DB>::new(base_sql);
            where_unique.build_where(&mut qb);
            qb.push(" RETURNING *");
            qb
        }

        // ── Generic helper: build a COUNT query ────────────────
        fn build_count_query<'args, DB: sqlx::Database>(
            base_sql: &str,
            where_input: &filter::#_where_input,
        ) -> sqlx::QueryBuilder<'args, DB>
        where
            #(#db_bounds,)*
        {
            let mut qb = sqlx::QueryBuilder::<DB>::new(base_sql);
            where_input.build_where(&mut qb);
            qb
        }

        // ── Generic helper: build a DELETE-many query ──────────
        fn build_delete_many_query<'args, DB: sqlx::Database>(
            base_sql: &str,
            where_input: &filter::#_where_input,
        ) -> sqlx::QueryBuilder<'args, DB>
        where
            #(#db_bounds,)*
        {
            let mut qb = sqlx::QueryBuilder::<DB>::new(base_sql);
            where_input.build_where(&mut qb);
            qb
        }

        pub struct FindUniqueQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_unique,
        }

        impl<'a> FindUniqueQuery<'a> {
            pub fn select(self, select: #_select_struct) -> FindUniqueSelectQuery<'a> {
                FindUniqueSelectQuery { client: self.client, r#where: self.r#where, select }
            }

            pub async fn exec(self) -> Result<Option<#model_ident>, FerriormError> {
                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_unique_select_query::<sqlx::Postgres>(#select_sql, &self.r#where);
                        self.client.fetch_optional_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_unique_select_query::<sqlx::Sqlite>(#select_sql, &self.r#where);
                        self.client.fetch_optional_sqlite(qb).await
                    }
                }
            }
        }

        pub struct FindFirstQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            order_by: Vec<order::#order_by>,
        }

        impl<'a> FindFirstQuery<'a> {
            pub fn order_by(mut self, order: order::#order_by) -> Self {
                self.order_by.push(order);
                self
            }

            pub fn select(self, select: #_select_struct) -> FindFirstSelectQuery<'a> {
                FindFirstSelectQuery { client: self.client, r#where: self.r#where, order_by: self.order_by, select }
            }

            pub async fn exec(self) -> Result<Option<#model_ident>, FerriormError> {
                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_select_query::<sqlx::Postgres>(#select_sql, &self.r#where, &self.order_by, Some(1), None);
                        self.client.fetch_optional_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_select_query::<sqlx::Sqlite>(#select_sql, &self.r#where, &self.order_by, Some(1), None);
                        self.client.fetch_optional_sqlite(qb).await
                    }
                }
            }
        }

        pub struct FindManyQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            order_by: Vec<order::#order_by>,
            skip: Option<i64>,
            take: Option<i64>,
        }

        impl<'a> FindManyQuery<'a> {
            pub fn order_by(mut self, order: order::#order_by) -> Self {
                self.order_by.push(order);
                self
            }

            pub fn skip(mut self, n: i64) -> Self {
                self.skip = Some(n);
                self
            }

            pub fn take(mut self, n: i64) -> Self {
                self.take = Some(n);
                self
            }

            pub fn select(self, select: #_select_struct) -> FindManySelectQuery<'a> {
                FindManySelectQuery {
                    client: self.client,
                    r#where: self.r#where,
                    order_by: self.order_by,
                    skip: self.skip,
                    take: self.take,
                    select,
                }
            }

            pub async fn exec(self) -> Result<Vec<#model_ident>, FerriormError> {
                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_select_query::<sqlx::Postgres>(#select_sql, &self.r#where, &self.order_by, self.take, self.skip);
                        self.client.fetch_all_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_select_query::<sqlx::Sqlite>(#select_sql, &self.r#where, &self.order_by, self.take, self.skip);
                        self.client.fetch_all_sqlite(qb).await
                    }
                }
            }
        }

        pub struct CreateQuery<'a> {
            client: &'a DatabaseClient,
            data: data::#_create_input,
        }

        impl<'a> CreateQuery<'a> {
            pub async fn exec(self) -> Result<#model_ident, FerriormError> {
                let client = self.client;
                #insert_code
            }

            /// Switch the insert into "ignore on conflict" mode:
            /// PostgreSQL uses `ON CONFLICT DO NOTHING`, SQLite uses `INSERT OR IGNORE`.
            /// Returns `Ok(None)` when a conflict suppressed the insert.
            pub fn on_conflict_ignore(self) -> CreateIgnoreQuery<'a> {
                CreateIgnoreQuery { client: self.client, data: self.data }
            }
        }

        pub struct CreateIgnoreQuery<'a> {
            client: &'a DatabaseClient,
            data: data::#_create_input,
        }

        impl<'a> CreateIgnoreQuery<'a> {
            pub async fn exec(self) -> Result<Option<#model_ident>, FerriormError> {
                let client = self.client;
                #insert_ignore_code
            }
        }

        pub struct UpdateQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_unique,
            data: data::#_update_input,
        }

        impl<'a> UpdateQuery<'a> {
            pub async fn exec(self) -> Result<#model_ident, FerriormError> {
                let client = self.client;
                #update_code
            }
        }

        pub struct UpdateFirstQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            data: data::#_update_input,
        }

        impl<'a> UpdateFirstQuery<'a> {
            pub async fn exec(self) -> Result<Option<#model_ident>, FerriormError> {
                let client = self.client;
                #update_first_code
            }
        }

        pub struct DeleteQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_unique,
        }

        impl<'a> DeleteQuery<'a> {
            pub async fn exec(self) -> Result<#model_ident, FerriormError> {
                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_delete_query::<sqlx::Postgres>(#delete_sql, &self.r#where);
                        self.client.fetch_one_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_delete_query::<sqlx::Sqlite>(#delete_sql, &self.r#where);
                        self.client.fetch_one_sqlite(qb).await
                    }
                }
            }
        }

        #[derive(sqlx::FromRow)]
        struct CountResult { count: i64 }

        pub struct CountQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
        }

        impl<'a> CountQuery<'a> {
            pub async fn exec(self) -> Result<i64, FerriormError> {
                let row: CountResult = match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_count_query::<sqlx::Postgres>(#count_sql, &self.r#where);
                        self.client.fetch_one_pg(qb).await?
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_count_query::<sqlx::Sqlite>(#count_sql, &self.r#where);
                        self.client.fetch_one_sqlite(qb).await?
                    }
                };
                Ok(row.count)
            }
        }

        pub struct CreateManyQuery<'a> {
            client: &'a DatabaseClient,
            data: Vec<data::#_create_input>,
        }

        impl<'a> CreateManyQuery<'a> {
            pub async fn exec(self) -> Result<u64, FerriormError> {
                if self.data.is_empty() { return Ok(0); }
                let count = self.data.len() as u64;
                for item in self.data {
                    CreateQuery { client: self.client, data: item }.exec().await?;
                }
                Ok(count)
            }
        }

        pub struct UpdateManyQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            data: data::#_update_input,
        }

        impl<'a> UpdateManyQuery<'a> {
            pub async fn exec(self) -> Result<u64, FerriormError> {
                let client = self.client;
                #update_many_code
            }
        }

        pub struct UpsertQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_unique,
            create: data::#_create_input,
            update: data::#_update_input,
        }

        impl<'a> UpsertQuery<'a> {
            pub async fn exec(self) -> Result<#model_ident, FerriormError> {
                let client = self.client;
                #upsert_code
            }
        }

        pub struct DeleteManyQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
        }

        impl<'a> DeleteManyQuery<'a> {
            pub async fn exec(self) -> Result<u64, FerriormError> {
                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_delete_many_query::<sqlx::Postgres>(#delete_sql, &self.r#where);
                        self.client.execute_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_delete_many_query::<sqlx::Sqlite>(#delete_sql, &self.r#where);
                        self.client.execute_sqlite(qb).await
                    }
                }
            }
        }
    }
}

// ─── INSERT code generation ───────────────────────────────────

fn gen_insert_code(model: &Model, scalar_fields: &[&Field], table_name: &str) -> TokenStream {
    let _model_ident = format_ident!("{}", model.name);

    // Required columns: scalar, no default, not @updatedAt
    let required: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.has_default() && !f.is_updated_at)
        .collect();

    // Optional columns: have default (can be overridden), not @updatedAt
    let optional: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.has_default() && !f.is_updated_at)
        .collect();

    // @updatedAt columns: always set to now()
    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    // Build column names and bind values
    let mut col_pushes = vec![];
    let mut val_pushes = vec![];

    // Required fields — always included
    for f in &required {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(self.data.#field_ident); });
    }

    // Optional fields — resolve defaults in Rust
    for f in &optional {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        if is_autoincrement(f) {
            // Autoincrement: if caller passed None, omit the column entirely
            // so the DB assigns the next sequence value. Binding a literal 0
            // would collide on the second insert.
            col_pushes.push(quote! {
                if self.data.#field_ident.is_some() { cols.push(#db_name); }
            });
            val_pushes.push(quote! {
                if let Some(val) = self.data.#field_ident {
                    sep.push_bind(val);
                }
            });
        } else {
            let default_expr = gen_default_expr(f, &f.field_type);
            col_pushes.push(quote! { cols.push(#db_name); });
            val_pushes.push(quote! {
                let val = self.data.#field_ident.unwrap_or_else(|| #default_expr);
                sep.push_bind(val);
            });
        }
    }

    // @updatedAt fields
    for f in &updated_at {
        let db_name = &f.db_name;
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(chrono::Utc::now()); });
    }

    let insert_start = format!(r#"INSERT INTO "{table_name}""#);

    // The insert_body macro avoids duplicating the column/value building logic
    // for each database backend. It captures `self` by reference.
    quote! {
        // Helper to build the INSERT query for any DB backend
        macro_rules! build_insert {
            ($qb_type:ty) => {{
                let mut cols: Vec<&str> = Vec::new();
                #(#col_pushes)*

                let mut qb = sqlx::QueryBuilder::<$qb_type>::new(#insert_start);
                qb.push(" (");
                for (i, col) in cols.iter().enumerate() {
                    if i > 0 { qb.push(", "); }
                    qb.push("\"");
                    qb.push(*col);
                    qb.push("\"");
                }
                qb.push(") VALUES (");
                {
                    let mut sep = qb.separated(", ");
                    #(#val_pushes)*
                }
                qb.push(") RETURNING *");
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_insert!(sqlx::Postgres);
                client.fetch_one_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_insert!(sqlx::Sqlite);
                client.fetch_one_sqlite(qb).await
            }
        }
    }
}

fn gen_insert_ignore_code(
    _model: &Model,
    scalar_fields: &[&Field],
    table_name: &str,
) -> TokenStream {
    let required: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.has_default() && !f.is_updated_at)
        .collect();
    let optional: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.has_default() && !f.is_updated_at)
        .collect();
    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    let mut col_pushes = vec![];
    let mut val_pushes = vec![];

    for f in &required {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(self.data.#field_ident); });
    }
    for f in &optional {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        if is_autoincrement(f) {
            col_pushes.push(quote! {
                if self.data.#field_ident.is_some() { cols.push(#db_name); }
            });
            val_pushes.push(quote! {
                if let Some(val) = self.data.#field_ident {
                    sep.push_bind(val);
                }
            });
        } else {
            let default_expr = gen_default_expr(f, &f.field_type);
            col_pushes.push(quote! { cols.push(#db_name); });
            val_pushes.push(quote! {
                let val = self.data.#field_ident.unwrap_or_else(|| #default_expr);
                sep.push_bind(val);
            });
        }
    }
    for f in &updated_at {
        let db_name = &f.db_name;
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(chrono::Utc::now()); });
    }

    let pg_insert_start = format!(r#"INSERT INTO "{table_name}""#);
    let sqlite_insert_start = format!(r#"INSERT OR IGNORE INTO "{table_name}""#);

    quote! {
        macro_rules! build_insert_ignore {
            ($qb_type:ty, $head:expr, $tail:expr) => {{
                let mut cols: Vec<&str> = Vec::new();
                #(#col_pushes)*

                let mut qb = sqlx::QueryBuilder::<$qb_type>::new($head);
                qb.push(" (");
                for (i, col) in cols.iter().enumerate() {
                    if i > 0 { qb.push(", "); }
                    qb.push("\"");
                    qb.push(*col);
                    qb.push("\"");
                }
                qb.push(") VALUES (");
                {
                    let mut sep = qb.separated(", ");
                    #(#val_pushes)*
                }
                qb.push(")");
                qb.push($tail);
                qb.push(" RETURNING *");
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_insert_ignore!(sqlx::Postgres, #pg_insert_start, " ON CONFLICT DO NOTHING");
                client.fetch_optional_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_insert_ignore!(sqlx::Sqlite, #sqlite_insert_start, "");
                client.fetch_optional_sqlite(qb).await
            }
        }
    }
}

/// True if the field is declared with `@default(autoincrement())`.
/// Such columns must be omitted from the INSERT when the caller passes `None`,
/// otherwise we'd bind a literal 0 and collide on the second insert.
fn is_autoincrement(field: &Field) -> bool {
    matches!(
        field.default,
        Some(ferriorm_core::ast::DefaultValue::AutoIncrement)
    )
}

/// Generate a Rust expression for a field's @default value.
fn gen_default_expr(field: &Field, field_type: &FieldKind) -> TokenStream {
    use ferriorm_core::ast::DefaultValue;

    match &field.default {
        Some(DefaultValue::Uuid | DefaultValue::Cuid) => {
            quote! { uuid::Uuid::new_v4().to_string() }
        }
        Some(DefaultValue::Now) => quote! { chrono::Utc::now() },
        // Autoincrement is handled specially by the INSERT/UPSERT generators
        // (see `is_autoincrement`): the column is omitted when the caller passes
        // `None`, so this arm is unreachable in practice. It stays as a safe
        // fallback only for match exhaustiveness.
        Some(DefaultValue::AutoIncrement) => quote! { 0i32 },
        Some(DefaultValue::Literal(lit)) => {
            use ferriorm_core::ast::LiteralValue;
            match lit {
                LiteralValue::String(s) => quote! { #s.to_string() },
                LiteralValue::Int(i) => {
                    // Cast the integer literal to the correct Rust type based on the field's scalar type.
                    #[allow(clippy::cast_precision_loss, clippy::cast_possible_truncation)]
                    match field_type {
                        FieldKind::Scalar(ScalarType::Float) => {
                            let val = *i as f64;
                            quote! { #val }
                        }
                        FieldKind::Scalar(ScalarType::BigInt) => quote! { #i },
                        // `@db.BigInt` on an `Int` widens the literal to i64 too.
                        FieldKind::Scalar(ScalarType::Int)
                            if field.db_type.as_ref().is_some_and(|(ty, _)| ty == "BigInt") =>
                        {
                            quote! { #i }
                        }
                        _ => {
                            // Default to i32 for Int and other types
                            let val = *i as i32;
                            quote! { #val }
                        }
                    }
                }
                LiteralValue::Float(f) => quote! { #f },
                LiteralValue::Bool(b) => quote! { #b },
            }
        }
        Some(DefaultValue::EnumVariant(v)) => {
            // Reference the enum variant — insert code runs at model module level
            let variant = format_ident!("{}", v);
            if let FieldKind::Enum(enum_name) = &field.field_type {
                let enum_ident = format_ident!("{}", enum_name);
                quote! { super::enums::#enum_ident::#variant }
            } else {
                quote! { Default::default() }
            }
        }
        None => quote! { Default::default() },
    }
}

// ─── UPDATE code generation ───────────────────────────────────

fn gen_update_code(model: &Model, scalar_fields: &[&Field], table_name: &str) -> TokenStream {
    let _model_ident = format_ident!("{}", model.name);

    // Updatable fields: non-id, non-updatedAt scalar fields
    let updatable: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.is_id && !f.is_updated_at)
        .collect();

    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    let update_start = format!(r#"UPDATE "{table_name}" SET "#);

    // Generate SET clause arms
    let set_arms: Vec<TokenStream> = updatable
        .iter()
        .map(|f| {
            let field_ident = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                if let Some(SetValue::Set(v)) = self.data.#field_ident {
                    if !first_set { qb.push(", "); }
                    first_set = false;
                    qb.push(concat!("\"", #db_name, "\" = "));
                    qb.push_bind(v);
                }
            }
        })
        .collect();

    let updated_at_arms: Vec<TokenStream> = updated_at
        .iter()
        .map(|f| {
            let db_name = &f.db_name;
            quote! {
                if !first_set { qb.push(", "); }
                first_set = false;
                qb.push(concat!("\"", #db_name, "\" = "));
                qb.push_bind(chrono::Utc::now());
            }
        })
        .collect();

    // The build_update macro avoids duplicating the SET clause building logic
    // for each database backend.
    quote! {
        macro_rules! build_update {
            ($qb_type:ty) => {{
                let mut qb = sqlx::QueryBuilder::<$qb_type>::new(#update_start);
                let mut first_set = true;
                #(#set_arms)*
                #(#updated_at_arms)*

                if first_set {
                    return Err(FerriormError::Query("No fields to update".into()));
                }

                qb.push(" WHERE 1=1");
                self.r#where.build_where(&mut qb);
                qb.push(" RETURNING *");
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_update!(sqlx::Postgres);
                client.fetch_one_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_update!(sqlx::Sqlite);
                client.fetch_one_sqlite(qb).await
            }
        }
    }
}

// ─── UPDATE FIRST (CAS) code generation ──────────────────────

fn gen_update_first_code(
    _model: &Model,
    scalar_fields: &[&Field],
    table_name: &str,
) -> TokenStream {
    let updatable: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.is_id && !f.is_updated_at)
        .collect();

    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    let update_start = format!(r#"UPDATE "{table_name}" SET "#);

    let set_arms: Vec<TokenStream> = updatable
        .iter()
        .map(|f| {
            let field_ident = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                if let Some(SetValue::Set(v)) = self.data.#field_ident {
                    if !first_set { qb.push(", "); }
                    first_set = false;
                    qb.push(concat!("\"", #db_name, "\" = "));
                    qb.push_bind(v);
                }
            }
        })
        .collect();

    let updated_at_arms: Vec<TokenStream> = updated_at
        .iter()
        .map(|f| {
            let db_name = &f.db_name;
            quote! {
                if !first_set { qb.push(", "); }
                first_set = false;
                qb.push(concat!("\"", #db_name, "\" = "));
                qb.push_bind(chrono::Utc::now());
            }
        })
        .collect();

    quote! {
        macro_rules! build_update_first {
            ($qb_type:ty) => {{
                let mut qb = sqlx::QueryBuilder::<$qb_type>::new(#update_start);
                let mut first_set = true;
                #(#set_arms)*
                #(#updated_at_arms)*

                if first_set {
                    return Err(FerriormError::Query("No fields to update".into()));
                }

                qb.push(" WHERE 1=1");
                self.r#where.build_where(&mut qb);
                qb.push(" RETURNING *");
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_update_first!(sqlx::Postgres);
                client.fetch_optional_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_update_first!(sqlx::Sqlite);
                client.fetch_optional_sqlite(qb).await
            }
        }
    }
}

// ─── UPDATE MANY code generation ──────────────────────────────

fn gen_update_many_code(_model: &Model, scalar_fields: &[&Field], table_name: &str) -> TokenStream {
    // Updatable fields: non-id, non-updatedAt scalar fields
    let updatable: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.is_id && !f.is_updated_at)
        .collect();

    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    let update_start = format!(r#"UPDATE "{table_name}" SET "#);

    // Generate SET clause arms
    let set_arms: Vec<TokenStream> = updatable
        .iter()
        .map(|f| {
            let field_ident = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                if let Some(SetValue::Set(v)) = self.data.#field_ident {
                    if !first_set { qb.push(", "); }
                    first_set = false;
                    qb.push(concat!("\"", #db_name, "\" = "));
                    qb.push_bind(v);
                }
            }
        })
        .collect();

    let updated_at_arms: Vec<TokenStream> = updated_at
        .iter()
        .map(|f| {
            let db_name = &f.db_name;
            quote! {
                if !first_set { qb.push(", "); }
                first_set = false;
                qb.push(concat!("\"", #db_name, "\" = "));
                qb.push_bind(chrono::Utc::now());
            }
        })
        .collect();

    quote! {
        macro_rules! build_update_many {
            ($qb_type:ty) => {{
                let mut qb = sqlx::QueryBuilder::<$qb_type>::new(#update_start);
                let mut first_set = true;
                #(#set_arms)*
                #(#updated_at_arms)*

                if first_set {
                    return Ok(0);
                }

                qb.push(" WHERE 1=1");
                self.r#where.build_where(&mut qb);
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_update_many!(sqlx::Postgres);
                client.execute_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_update_many!(sqlx::Sqlite);
                client.execute_sqlite(qb).await
            }
        }
    }
}

// ─── Aggregate Types ──────────────────────────────────────────

/// Identifies which fields are aggregatable and what operations they support.
enum AggregateKind {
    /// Numeric fields: avg, sum, min, max
    Numeric,
    /// `DateTime` fields: min, max only
    DateTime,
}

// ─── UPSERT code generation ──────────────────────────────────

#[allow(clippy::too_many_lines)]
fn gen_upsert_code(_model: &Model, scalar_fields: &[&Field], table_name: &str) -> TokenStream {
    // Required + optional + updatedAt fields for the INSERT part (same as gen_insert_code)
    let required: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.has_default() && !f.is_updated_at)
        .collect();
    let optional: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.has_default() && !f.is_updated_at)
        .collect();
    let updated_at: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| f.is_updated_at)
        .collect();

    let mut col_pushes = vec![];
    let mut val_pushes = vec![];

    for f in &required {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(self.create.#field_ident); });
    }
    for f in &optional {
        let db_name = &f.db_name;
        let field_ident = format_ident!("{}", to_snake_case(&f.name));
        if is_autoincrement(f) {
            col_pushes.push(quote! {
                if self.create.#field_ident.is_some() { cols.push(#db_name); }
            });
            val_pushes.push(quote! {
                if let Some(val) = self.create.#field_ident {
                    sep.push_bind(val);
                }
            });
        } else {
            let default_expr = gen_default_expr(f, &f.field_type);
            col_pushes.push(quote! { cols.push(#db_name); });
            val_pushes.push(quote! {
                let val = self.create.#field_ident.unwrap_or_else(|| #default_expr);
                sep.push_bind(val);
            });
        }
    }
    for f in &updated_at {
        let db_name = &f.db_name;
        col_pushes.push(quote! { cols.push(#db_name); });
        val_pushes.push(quote! { sep.push_bind(chrono::Utc::now()); });
    }

    // Updatable fields for the DO UPDATE SET part
    let updatable: Vec<&Field> = scalar_fields
        .iter()
        .copied()
        .filter(|f| !f.is_id && !f.is_updated_at)
        .collect();

    let set_arms: Vec<TokenStream> = updatable
        .iter()
        .map(|f| {
            let field_ident = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            quote! {
                if let Some(SetValue::Set(v)) = self.update.#field_ident {
                    if !first_set { qb.push(", "); }
                    first_set = false;
                    qb.push(concat!("\"", #db_name, "\" = "));
                    qb.push_bind(v);
                }
            }
        })
        .collect();

    let updated_at_set: Vec<TokenStream> = updated_at
        .iter()
        .map(|f| {
            let db_name = &f.db_name;
            quote! {
                if !first_set { qb.push(", "); }
                first_set = false;
                qb.push(concat!("\"", #db_name, "\" = "));
                qb.push_bind(chrono::Utc::now());
            }
        })
        .collect();

    let insert_start = format!(r#"INSERT INTO "{table_name}""#);

    quote! {
        let conflict_target = self.r#where.conflict_target();
        let first_conflict_col = self.r#where.first_conflict_col();

        macro_rules! build_upsert {
            ($qb_type:ty) => {{
                let mut cols: Vec<&str> = Vec::new();
                #(#col_pushes)*

                let mut qb = sqlx::QueryBuilder::<$qb_type>::new(#insert_start);
                qb.push(" (");
                for (i, col) in cols.iter().enumerate() {
                    if i > 0 { qb.push(", "); }
                    qb.push("\"");
                    qb.push(*col);
                    qb.push("\"");
                }
                qb.push(") VALUES (");
                {
                    let mut sep = qb.separated(", ");
                    #(#val_pushes)*
                }
                qb.push(")");
                qb.push(" ON CONFLICT ");
                qb.push(conflict_target);
                qb.push(" DO UPDATE SET ");

                let mut first_set = true;
                #(#set_arms)*
                #(#updated_at_set)*

                if first_set {
                    // No update fields specified — use a no-op update on the first
                    // conflict-target column so RETURNING * still yields the row.
                    qb.push(first_conflict_col);
                    qb.push(" = ");
                    qb.push(first_conflict_col);
                }

                qb.push(" RETURNING *");
                qb
            }};
        }

        match client {
            DatabaseClient::Postgres(_) => {
                let qb = build_upsert!(sqlx::Postgres);
                client.fetch_one_pg(qb).await
            }
            DatabaseClient::Sqlite(_) => {
                let qb = build_upsert!(sqlx::Sqlite);
                client.fetch_one_sqlite(qb).await
            }
        }
    }
}

#[allow(clippy::too_many_lines)]
fn gen_aggregate_types(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let aggregate_field_name = format_ident!("{}AggregateField", model.name);
    let aggregate_result_name = format_ident!("{}AggregateResult", model.name);
    let _where_input = format_ident!("{}WhereInput", model.name);
    let table_name = &model.db_name;

    // Collect aggregatable fields with their kind
    let agg_fields: Vec<(&Field, AggregateKind)> = scalar_fields
        .iter()
        .filter_map(|f| match &f.field_type {
            FieldKind::Scalar(ScalarType::Int | ScalarType::BigInt | ScalarType::Float) => {
                Some((*f, AggregateKind::Numeric))
            }
            FieldKind::Scalar(ScalarType::DateTime) => Some((*f, AggregateKind::DateTime)),
            _ => None,
        })
        .collect();

    if agg_fields.is_empty() {
        return quote! {};
    }

    // Generate enum variants
    let enum_variants: Vec<TokenStream> = agg_fields
        .iter()
        .map(|(f, _)| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            quote! { #variant }
        })
        .collect();

    // Generate db_name match arms
    let db_name_arms: Vec<TokenStream> = agg_fields
        .iter()
        .map(|(f, _)| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let db_name = &f.db_name;
            quote! { Self::#variant => #db_name }
        })
        .collect();

    // Generate AggregateResult fields
    let mut result_fields = Vec::new();
    for (f, kind) in &agg_fields {
        let snake = to_snake_case(&f.name);
        let orig_ty = rust_type_tokens(
            &Field {
                is_optional: false,
                ..(*f).clone()
            },
            ModuleDepth::TopLevel,
        );

        match kind {
            AggregateKind::Numeric => {
                let avg_name = format_ident!("avg_{}", snake);
                let sum_name = format_ident!("sum_{}", snake);
                let min_name = format_ident!("min_{}", snake);
                let max_name = format_ident!("max_{}", snake);
                result_fields.push(quote! { #[sqlx(default)] pub #avg_name: Option<f64> });
                result_fields.push(quote! { #[sqlx(default)] pub #sum_name: Option<f64> });
                result_fields.push(quote! { #[sqlx(default)] pub #min_name: Option<#orig_ty> });
                result_fields.push(quote! { #[sqlx(default)] pub #max_name: Option<#orig_ty> });
            }
            AggregateKind::DateTime => {
                let min_name = format_ident!("min_{}", snake);
                let max_name = format_ident!("max_{}", snake);
                result_fields.push(quote! { #[sqlx(default)] pub #min_name: Option<#orig_ty> });
                result_fields.push(quote! { #[sqlx(default)] pub #max_name: Option<#orig_ty> });
            }
        }
    }

    // Generate the is_numeric check for avg/sum validation
    let numeric_arms: Vec<TokenStream> = agg_fields
        .iter()
        .filter(|(_, kind)| matches!(kind, AggregateKind::Numeric))
        .map(|(f, _)| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            quote! { Self::#variant => true }
        })
        .collect();

    let has_numeric = !numeric_arms.is_empty();
    let is_numeric_method = if has_numeric {
        quote! {
            fn is_numeric(&self) -> bool {
                match self {
                    #(#numeric_arms,)*
                    #[allow(unreachable_patterns)]
                    _ => false,
                }
            }
        }
    } else {
        quote! {
            fn is_numeric(&self) -> bool { false }
        }
    };

    // Generate alias match arms for each (prefix, field) combination
    let mut alias_arms = Vec::new();
    for (f, kind) in &agg_fields {
        let variant = format_ident!("{}", to_pascal_case(&f.name));
        let snake = to_snake_case(&f.name);
        let prefixes = match kind {
            AggregateKind::Numeric => vec!["avg", "sum", "min", "max"],
            AggregateKind::DateTime => vec!["min", "max"],
        };
        for prefix in prefixes {
            let alias_str = format!("{prefix}_{snake}");
            alias_arms.push(quote! { (#prefix, Self::#variant) => #alias_str });
        }
    }

    let agg_select_base = format!(r#"SELECT {{}} FROM "{table_name}" WHERE 1=1"#);

    quote! {
        #[derive(Debug, Clone, Copy)]
        pub enum #aggregate_field_name {
            #(#enum_variants),*
        }

        impl #aggregate_field_name {
            pub fn db_name(&self) -> &'static str {
                match self {
                    #(#db_name_arms,)*
                }
            }

            fn alias(&self, prefix: &'static str) -> &'static str {
                match (prefix, self) {
                    #(#alias_arms,)*
                    _ => unreachable!(),
                }
            }

            #is_numeric_method
        }

        #[derive(Debug, Clone, sqlx::FromRow, Serialize, Deserialize)]
        pub struct #aggregate_result_name {
            #(#result_fields,)*
        }

        pub struct AggregateQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            ops: Vec<(&'static str, &'static str, &'static str)>,
        }

        impl<'a> AggregateQuery<'a> {
            pub fn avg(mut self, field: #aggregate_field_name) -> Self {
                assert!(field.is_numeric(), "avg() is only supported on numeric fields");
                let db_name = field.db_name();
                let alias = field.alias("avg");
                self.ops.push(("AVG", db_name, alias));
                self
            }

            pub fn sum(mut self, field: #aggregate_field_name) -> Self {
                assert!(field.is_numeric(), "sum() is only supported on numeric fields");
                let db_name = field.db_name();
                let alias = field.alias("sum");
                self.ops.push(("SUM", db_name, alias));
                self
            }

            pub fn min(mut self, field: #aggregate_field_name) -> Self {
                let db_name = field.db_name();
                let alias = field.alias("min");
                self.ops.push(("MIN", db_name, alias));
                self
            }

            pub fn max(mut self, field: #aggregate_field_name) -> Self {
                let db_name = field.db_name();
                let alias = field.alias("max");
                self.ops.push(("MAX", db_name, alias));
                self
            }

            pub async fn exec(self) -> Result<#aggregate_result_name, FerriormError> {
                if self.ops.is_empty() {
                    return Err(FerriormError::Query("No aggregate operations specified".into()));
                }

                let selections: Vec<String> = self.ops.iter()
                    .map(|(func, col, alias)| format!(r#"{}("{}") as "{}""#, func, col, alias))
                    .collect();
                let select_clause = selections.join(", ");
                let base_sql = format!(#agg_select_base, select_clause);

                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let mut qb = sqlx::QueryBuilder::<sqlx::Postgres>::new(&base_sql);
                        self.r#where.build_where(&mut qb);
                        self.client.fetch_one_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let mut qb = sqlx::QueryBuilder::<sqlx::Sqlite>::new(&base_sql);
                        self.r#where.build_where(&mut qb);
                        self.client.fetch_one_sqlite(qb).await
                    }
                }
            }
        }
    }
}

// ─── GroupBy Types ────────────────────────────────────────────

/// Generate the six standard-comparable HAVING arms (`equals`/`not`/`gt`/
/// `gte`/`lt`/`lte`) for one aggregate field. `lhs` is the SQL expression on
/// the left-hand side of the operator (e.g. `AVG("age")`, `MIN("created_at")`).
fn gen_having_comparable_arms(field_ident: &proc_macro2::Ident, lhs: &str) -> TokenStream {
    let eq = format!(" AND {lhs} = ");
    let ne = format!(" AND {lhs} != ");
    let gt = format!(" AND {lhs} > ");
    let gte = format!(" AND {lhs} >= ");
    let lt = format!(" AND {lhs} < ");
    let lte = format!(" AND {lhs} <= ");
    quote! {
        if let Some(filter) = &self.#field_ident {
            if let Some(v) = &filter.equals { qb.push(#eq); qb.push_bind(v.clone()); }
            if let Some(v) = &filter.not    { qb.push(#ne); qb.push_bind(v.clone()); }
            if let Some(v) = &filter.gt     { qb.push(#gt); qb.push_bind(v.clone()); }
            if let Some(v) = &filter.gte    { qb.push(#gte); qb.push_bind(v.clone()); }
            if let Some(v) = &filter.lt     { qb.push(#lt); qb.push_bind(v.clone()); }
            if let Some(v) = &filter.lte    { qb.push(#lte); qb.push_bind(v.clone()); }
        }
    }
}

/// True for fields that can appear in a `GROUP BY` clause: any scalar except
/// `Json`/`Bytes`/`Decimal` (which are not orderable / hashable in SQL), plus
/// enums. Optional fields are still groupable -- `NULL` becomes its own
/// bucket.
fn is_groupable(field: &Field) -> bool {
    match &field.field_type {
        FieldKind::Scalar(
            ScalarType::String
            | ScalarType::Int
            | ScalarType::BigInt
            | ScalarType::Float
            | ScalarType::Boolean
            | ScalarType::DateTime,
        )
        | FieldKind::Enum(_) => true,
        FieldKind::Scalar(ScalarType::Json | ScalarType::Bytes | ScalarType::Decimal)
        | FieldKind::Model(_) => false,
    }
}

#[allow(clippy::too_many_lines)]
fn gen_groupby_types(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let groupby_field_name = format_ident!("{}GroupByField", model.name);
    let groupby_result_name = format_ident!("{}GroupByResult", model.name);
    let having_input_name = format_ident!("{}HavingInput", model.name);
    let aggregate_field_name = format_ident!("{}AggregateField", model.name);
    let where_input = format_ident!("{}WhereInput", model.name);
    let table_name = &model.db_name;

    // Reuse the same aggregate-field collection as gen_aggregate_types so the
    // result struct columns and HAVING surface stay consistent.
    let agg_fields: Vec<(&Field, AggregateKind)> = scalar_fields
        .iter()
        .filter_map(|f| match &f.field_type {
            FieldKind::Scalar(ScalarType::Int | ScalarType::BigInt | ScalarType::Float) => {
                Some((*f, AggregateKind::Numeric))
            }
            FieldKind::Scalar(ScalarType::DateTime) => Some((*f, AggregateKind::DateTime)),
            _ => None,
        })
        .collect();

    let group_fields: Vec<&Field> = scalar_fields
        .iter()
        .filter(|f| is_groupable(f))
        .copied()
        .collect();

    if group_fields.is_empty() {
        return quote! {};
    }

    // ── <Model>GroupByField enum ──────────────────────────────
    let groupby_variants: Vec<TokenStream> = group_fields
        .iter()
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            quote! { #variant }
        })
        .collect();

    let groupby_db_arms: Vec<TokenStream> = group_fields
        .iter()
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let db_name = &f.db_name;
            quote! { Self::#variant => #db_name }
        })
        .collect();

    let groupby_alias_arms: Vec<TokenStream> = group_fields
        .iter()
        .map(|f| {
            let variant = format_ident!("{}", to_pascal_case(&f.name));
            let alias = to_snake_case(&f.name);
            quote! { Self::#variant => #alias }
        })
        .collect();

    // ── <Model>GroupByResult fields ───────────────────────────
    // One Option<T> per groupable field (only filled when that field is in
    // the active group key set), then count, then the same avg/sum/min/max
    // columns that gen_aggregate_types emits.
    let mut result_fields: Vec<TokenStream> = Vec::new();
    for f in &group_fields {
        let snake = to_snake_case(&f.name);
        let name = format_ident!("{}", snake);
        // Always wrap in Option so the same struct serves every group_by call.
        let base_ty = rust_type_tokens(
            &Field {
                is_optional: false,
                ..(*f).clone()
            },
            ModuleDepth::TopLevel,
        );
        result_fields.push(quote! { #[sqlx(default)] pub #name: Option<#base_ty> });
    }
    result_fields.push(quote! { #[sqlx(default)] pub count: Option<i64> });
    for (f, kind) in &agg_fields {
        let snake = to_snake_case(&f.name);
        let orig_ty = rust_type_tokens(
            &Field {
                is_optional: false,
                ..(*f).clone()
            },
            ModuleDepth::TopLevel,
        );
        match kind {
            AggregateKind::Numeric => {
                let avg_name = format_ident!("avg_{}", snake);
                let sum_name = format_ident!("sum_{}", snake);
                let min_name = format_ident!("min_{}", snake);
                let max_name = format_ident!("max_{}", snake);
                result_fields.push(quote! { #[sqlx(default)] pub #avg_name: Option<f64> });
                result_fields.push(quote! { #[sqlx(default)] pub #sum_name: Option<f64> });
                result_fields.push(quote! { #[sqlx(default)] pub #min_name: Option<#orig_ty> });
                result_fields.push(quote! { #[sqlx(default)] pub #max_name: Option<#orig_ty> });
            }
            AggregateKind::DateTime => {
                let min_name = format_ident!("min_{}", snake);
                let max_name = format_ident!("max_{}", snake);
                result_fields.push(quote! { #[sqlx(default)] pub #min_name: Option<#orig_ty> });
                result_fields.push(quote! { #[sqlx(default)] pub #max_name: Option<#orig_ty> });
            }
        }
    }

    // ── <Model>HavingInput fields ─────────────────────────────
    // Filtering on aggregate expressions: COUNT(*), AVG/SUM/MIN/MAX of each
    // aggregatable column. RHS reuses the same scalar filter types as
    // WhereInput.
    let mut having_fields: Vec<TokenStream> = Vec::new();
    // COUNT(*) returns BIGINT in both Postgres and SQLite -> BigIntFilter.
    having_fields.push(quote! { pub count: Option<ferriorm_runtime::filter::BigIntFilter> });
    for (f, kind) in &agg_fields {
        let snake = to_snake_case(&f.name);
        let avg_name = format_ident!("avg_{}", snake);
        let sum_name = format_ident!("sum_{}", snake);
        let min_name = format_ident!("min_{}", snake);
        let max_name = format_ident!("max_{}", snake);
        let column_filter = filter_type_tokens(
            &Field {
                is_optional: false,
                ..(*f).clone()
            },
            ModuleDepth::TopLevel,
        )
        .unwrap_or_else(|| quote! { ferriorm_runtime::filter::BigIntFilter });
        match kind {
            AggregateKind::Numeric => {
                having_fields
                    .push(quote! { pub #avg_name: Option<ferriorm_runtime::filter::FloatFilter> });
                having_fields
                    .push(quote! { pub #sum_name: Option<ferriorm_runtime::filter::FloatFilter> });
                having_fields.push(quote! { pub #min_name: Option<#column_filter> });
                having_fields.push(quote! { pub #max_name: Option<#column_filter> });
            }
            AggregateKind::DateTime => {
                having_fields.push(quote! { pub #min_name: Option<#column_filter> });
                having_fields.push(quote! { pub #max_name: Option<#column_filter> });
            }
        }
    }

    // ── build_having arms ─────────────────────────────────────
    // Mirrors gen_where_arms but the LHS is the aggregate expression
    // (`AVG("col")`, `COUNT(*)`, ...) instead of a bare column reference.
    // Aggregate results are never NULL semantically except for empty inputs,
    // so we don't need IS NULL handling here.
    let mut having_arms: Vec<TokenStream> = Vec::new();
    // count: BigIntFilter on COUNT(*)
    having_arms.push(quote! {
        if let Some(filter) = &self.count {
            if let Some(v) = &filter.equals { qb.push(" AND COUNT(*) = "); qb.push_bind(*v); }
            if let Some(v) = &filter.not    { qb.push(" AND COUNT(*) != "); qb.push_bind(*v); }
            if let Some(v) = &filter.gt     { qb.push(" AND COUNT(*) > "); qb.push_bind(*v); }
            if let Some(v) = &filter.gte    { qb.push(" AND COUNT(*) >= "); qb.push_bind(*v); }
            if let Some(v) = &filter.lt     { qb.push(" AND COUNT(*) < "); qb.push_bind(*v); }
            if let Some(v) = &filter.lte    { qb.push(" AND COUNT(*) <= "); qb.push_bind(*v); }
        }
    });

    for (f, kind) in &agg_fields {
        let snake = to_snake_case(&f.name);
        let db_name = &f.db_name;
        let avg_ident = format_ident!("avg_{}", snake);
        let sum_ident = format_ident!("sum_{}", snake);
        let min_ident = format_ident!("min_{}", snake);
        let max_ident = format_ident!("max_{}", snake);
        match kind {
            AggregateKind::Numeric => {
                let avg_lhs = format!(r#"AVG("{db_name}")"#);
                let sum_lhs = format!(r#"SUM("{db_name}")"#);
                let min_lhs = format!(r#"MIN("{db_name}")"#);
                let max_lhs = format!(r#"MAX("{db_name}")"#);
                having_arms.push(gen_having_comparable_arms(&avg_ident, &avg_lhs));
                having_arms.push(gen_having_comparable_arms(&sum_ident, &sum_lhs));
                having_arms.push(gen_having_comparable_arms(&min_ident, &min_lhs));
                having_arms.push(gen_having_comparable_arms(&max_ident, &max_lhs));
            }
            AggregateKind::DateTime => {
                let min_lhs = format!(r#"MIN("{db_name}")"#);
                let max_lhs = format!(r#"MAX("{db_name}")"#);
                having_arms.push(gen_having_comparable_arms(&min_ident, &min_lhs));
                having_arms.push(gen_having_comparable_arms(&max_ident, &max_lhs));
            }
        }
    }

    // build_having binds the RHS of `AVG(col) op ?` (always f64) and
    // `COUNT(*) op ?` (always i64) regardless of which scalar types appear
    // in the model. Reuse collect_db_bounds for the column-type bounds
    // (needed by min/max filters), then top up with f64.
    let mut db_bounds = collect_db_bounds(scalar_fields);
    if !scalar_fields
        .iter()
        .any(|f| matches!(&f.field_type, FieldKind::Scalar(ScalarType::Float)))
    {
        db_bounds.push(quote! { f64: sqlx::Type<DB> + for<'e> sqlx::Encode<'e, DB> });
    }

    // ── is_numeric reuse: AggregateField is the canonical enum, but if no
    //    aggregatable fields exist we still want a typed group_by query --
    //    just without the agg ops. In that case AggregateField may not have
    //    been emitted at all, so skip avg/sum/min/max methods.
    let has_agg_fields = !agg_fields.is_empty();

    let agg_methods = if has_agg_fields {
        quote! {
            pub fn count(mut self) -> Self {
                self.count = true;
                self
            }

            pub fn avg(mut self, field: #aggregate_field_name) -> Self {
                assert!(field.is_numeric(), "avg() is only supported on numeric fields");
                let db_name = field.db_name();
                let alias = field.alias("avg");
                self.agg_ops.push(("AVG", db_name, alias));
                self
            }

            pub fn sum(mut self, field: #aggregate_field_name) -> Self {
                assert!(field.is_numeric(), "sum() is only supported on numeric fields");
                let db_name = field.db_name();
                let alias = field.alias("sum");
                self.agg_ops.push(("SUM", db_name, alias));
                self
            }

            pub fn min(mut self, field: #aggregate_field_name) -> Self {
                let db_name = field.db_name();
                let alias = field.alias("min");
                self.agg_ops.push(("MIN", db_name, alias));
                self
            }

            pub fn max(mut self, field: #aggregate_field_name) -> Self {
                let db_name = field.db_name();
                let alias = field.alias("max");
                self.agg_ops.push(("MAX", db_name, alias));
                self
            }
        }
    } else {
        quote! {
            pub fn count(mut self) -> Self {
                self.count = true;
                self
            }
        }
    };

    quote! {
        #[derive(Debug, Clone, Copy)]
        pub enum #groupby_field_name {
            #(#groupby_variants),*
        }

        impl #groupby_field_name {
            pub fn db_name(&self) -> &'static str {
                match self {
                    #(#groupby_db_arms,)*
                }
            }

            fn alias(&self) -> &'static str {
                match self {
                    #(#groupby_alias_arms,)*
                }
            }
        }

        #[derive(Debug, Clone, sqlx::FromRow, Serialize, Deserialize)]
        pub struct #groupby_result_name {
            #(#result_fields,)*
        }

        #[derive(Debug, Clone, Default)]
        pub struct #having_input_name {
            #(#having_fields,)*
            pub and: Option<Vec<#having_input_name>>,
            pub or: Option<Vec<#having_input_name>>,
            pub not: Option<Box<#having_input_name>>,
        }

        impl #having_input_name {
            pub(crate) fn build_having<'args, DB: sqlx::Database>(
                &self,
                qb: &mut sqlx::QueryBuilder<'args, DB>,
            )
            where
                #(#db_bounds,)*
            {
                #(#having_arms)*

                if let Some(conditions) = &self.and {
                    for c in conditions {
                        c.build_having(qb);
                    }
                }
                if let Some(conditions) = &self.or {
                    if !conditions.is_empty() {
                        qb.push(" AND (");
                        for (i, c) in conditions.iter().enumerate() {
                            if i > 0 { qb.push(" OR "); }
                            qb.push("(1=1");
                            c.build_having(qb);
                            qb.push(")");
                        }
                        qb.push(")");
                    }
                }
                if let Some(c) = &self.not {
                    qb.push(" AND NOT (1=1");
                    c.build_having(qb);
                    qb.push(")");
                }
            }
        }

        pub struct GroupByQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#where_input,
            group_keys: Vec<#groupby_field_name>,
            agg_ops: Vec<(&'static str, &'static str, &'static str)>,
            count: bool,
            having: Option<#having_input_name>,
        }

        impl<'a> GroupByQuery<'a> {
            pub fn r#where(mut self, r#where: filter::#where_input) -> Self {
                self.r#where = r#where;
                self
            }

            #agg_methods

            pub fn having(mut self, having: #having_input_name) -> Self {
                self.having = Some(having);
                self
            }

            pub async fn exec(self) -> Result<Vec<#groupby_result_name>, FerriormError> {
                if self.group_keys.is_empty() {
                    return Err(FerriormError::Query(
                        "group_by() requires at least one group key".into(),
                    ));
                }

                let mut selections: Vec<String> = self.group_keys
                    .iter()
                    .map(|k| format!(r#""{}" as "{}""#, k.db_name(), k.alias()))
                    .collect();
                if self.count {
                    selections.push(r#"COUNT(*) as "count""#.to_string());
                }
                for (func, col, alias) in &self.agg_ops {
                    selections.push(format!(r#"{}("{}") as "{}""#, func, col, alias));
                }

                let group_by_clause: Vec<String> = self.group_keys
                    .iter()
                    .map(|k| format!(r#""{}""#, k.db_name()))
                    .collect();

                let base_sql = format!(
                    r#"SELECT {} FROM "{}" WHERE 1=1"#,
                    selections.join(", "),
                    #table_name,
                );

                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let mut qb = sqlx::QueryBuilder::<sqlx::Postgres>::new(&base_sql);
                        self.r#where.build_where(&mut qb);
                        qb.push(format!(" GROUP BY {}", group_by_clause.join(", ")));
                        if let Some(h) = &self.having {
                            qb.push(" HAVING 1=1");
                            h.build_having(&mut qb);
                        }
                        self.client.fetch_all_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let mut qb = sqlx::QueryBuilder::<sqlx::Sqlite>::new(&base_sql);
                        self.r#where.build_where(&mut qb);
                        qb.push(format!(" GROUP BY {}", group_by_clause.join(", ")));
                        if let Some(h) = &self.having {
                            qb.push(" HAVING 1=1");
                            h.build_having(&mut qb);
                        }
                        self.client.fetch_all_sqlite(qb).await
                    }
                }
            }
        }
    }
}

// ─── Select Types ─────────────────────────────────────────────

#[allow(clippy::too_many_lines)]
fn gen_select_types(model: &Model, scalar_fields: &[&Field]) -> TokenStream {
    let select_name = format_ident!("{}Select", model.name);
    let partial_name = format_ident!("{}Partial", model.name);
    let _where_input = format_ident!("{}WhereInput", model.name);
    let _where_unique = format_ident!("{}WhereUniqueInput", model.name);
    let order_by_name = format_ident!("{}OrderByInput", model.name);
    let table_name = &model.db_name;

    // Select struct fields: all bool, default false
    let select_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            quote! { pub #name: bool }
        })
        .collect();

    // Partial struct fields: all Option<T> with #[sqlx(default)]
    // For already-optional fields, don't double-wrap in Option
    let partial_fields: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            // Get the base type (non-optional version)
            let base_ty = rust_type_tokens(
                &Field {
                    is_optional: false,
                    ..(*f).clone()
                },
                ModuleDepth::TopLevel,
            );
            let rename = if db_name == &to_snake_case(&f.name) {
                quote! {}
            } else {
                quote! { #[sqlx(rename = #db_name)] }
            };
            // Always wrap in Option<T>, regardless of whether field was originally optional
            quote! { #[sqlx(default)] #rename pub #name: Option<#base_ty> }
        })
        .collect();

    // build_select_columns: maps Select bools to column names
    let select_col_arms: Vec<TokenStream> = scalar_fields
        .iter()
        .map(|f| {
            let name = format_ident!("{}", to_snake_case(&f.name));
            let db_name = &f.db_name;
            let col_expr = format!(r#""{db_name}""#);
            quote! {
                if select.#name { cols.push(#col_expr); }
            }
        })
        .collect();

    let select_sql_prefix = format!(r#"SELECT {{}} FROM "{table_name}" WHERE 1=1"#);

    quote! {
        #[derive(Debug, Clone, Default)]
        pub struct #select_name {
            #(#select_fields,)*
        }

        #[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
        #[sqlx(rename_all = "snake_case")]
        pub struct #partial_name {
            #(#partial_fields,)*
        }

        fn build_select_columns(select: &#select_name) -> String {
            let mut cols = Vec::new();
            #(#select_col_arms)*
            if cols.is_empty() {
                "*".to_string()
            } else {
                cols.join(", ")
            }
        }

        // ── FindManySelectQuery ──────────────────────────────────

        pub struct FindManySelectQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            order_by: Vec<order::#order_by_name>,
            skip: Option<i64>,
            take: Option<i64>,
            select: #select_name,
        }

        impl<'a> FindManySelectQuery<'a> {
            pub fn order_by(mut self, order: order::#order_by_name) -> Self {
                self.order_by.push(order);
                self
            }

            pub fn skip(mut self, n: i64) -> Self {
                self.skip = Some(n);
                self
            }

            pub fn take(mut self, n: i64) -> Self {
                self.take = Some(n);
                self
            }

            pub async fn exec(self) -> Result<Vec<#partial_name>, FerriormError> {
                let cols = build_select_columns(&self.select);
                let base_sql = format!(#select_sql_prefix, cols);

                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_select_query::<sqlx::Postgres>(
                            &base_sql, &self.r#where, &self.order_by, self.take, self.skip,
                        );
                        self.client.fetch_all_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_select_query::<sqlx::Sqlite>(
                            &base_sql, &self.r#where, &self.order_by, self.take, self.skip,
                        );
                        self.client.fetch_all_sqlite(qb).await
                    }
                }
            }
        }

        // ── FindUniqueSelectQuery ────────────────────────────────

        pub struct FindUniqueSelectQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_unique,
            select: #select_name,
        }

        impl<'a> FindUniqueSelectQuery<'a> {
            pub async fn exec(self) -> Result<Option<#partial_name>, FerriormError> {
                let cols = build_select_columns(&self.select);
                let base_sql = format!(#select_sql_prefix, cols);

                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_unique_select_query::<sqlx::Postgres>(
                            &base_sql, &self.r#where,
                        );
                        self.client.fetch_optional_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_unique_select_query::<sqlx::Sqlite>(
                            &base_sql, &self.r#where,
                        );
                        self.client.fetch_optional_sqlite(qb).await
                    }
                }
            }
        }

        // ── FindFirstSelectQuery ─────────────────────────────────

        pub struct FindFirstSelectQuery<'a> {
            client: &'a DatabaseClient,
            r#where: filter::#_where_input,
            order_by: Vec<order::#order_by_name>,
            select: #select_name,
        }

        impl<'a> FindFirstSelectQuery<'a> {
            pub fn order_by(mut self, order: order::#order_by_name) -> Self {
                self.order_by.push(order);
                self
            }

            pub async fn exec(self) -> Result<Option<#partial_name>, FerriormError> {
                let cols = build_select_columns(&self.select);
                let base_sql = format!(#select_sql_prefix, cols);

                match self.client {
                    DatabaseClient::Postgres(_) => {
                        let qb = build_select_query::<sqlx::Postgres>(
                            &base_sql, &self.r#where, &self.order_by, Some(1), None,
                        );
                        self.client.fetch_optional_pg(qb).await
                    }
                    DatabaseClient::Sqlite(_) => {
                        let qb = build_select_query::<sqlx::Sqlite>(
                            &base_sql, &self.r#where, &self.order_by, Some(1), None,
                        );
                        self.client.fetch_optional_sqlite(qb).await
                    }
                }
            }
        }
    }
}