use crate::{
    ColumnDef, ColumnType, DbBackend, EntityName, Iden, IdenStatic, IntoSimpleExpr, Iterable,
};
use sea_query::{
    BinOper, DynIden, Expr, ExprTrait, IntoIden, IntoLikeExpr, SeaRc, SelectStatement, Value,
};
use std::{borrow::Cow, str::FromStr};

mod types;
pub use types::*;

pub(crate) mod macros {
    macro_rules! bind_oper {
        ($vis:vis $op:ident, $bin_op:ident) => {
            #[allow(missing_docs)]
            $vis fn $op<V>(&self, v: V) -> Expr
            where
                V: Into<Value>,
            {
                let expr = self.save_as(Expr::val(v));
                Expr::col(self.as_column_ref()).binary(BinOper::$bin_op, expr)
            }
        };
    }

    macro_rules! bind_func_no_params {
        ($vis:vis $func:ident) => {
            /// See also SeaQuery's method with same name.
            $vis fn $func(&self) -> Expr {
                Expr::col(self.as_column_ref()).$func()
            }
        };
    }

    macro_rules! bind_vec_func {
        ($vis:vis $func:ident) => {
            #[allow(missing_docs)]
            #[allow(clippy::wrong_self_convention)]
            $vis fn $func<V, I>(&self, v: I) -> Expr
            where
                V: Into<Value>,
                I: IntoIterator<Item = V>,
            {
                let v_with_enum_cast = v.into_iter().map(|v| self.save_as(Expr::val(v)));
                Expr::col(self.as_column_ref()).$func(v_with_enum_cast)
            }
        };
    }

    macro_rules! bind_subquery_func {
        ($vis:vis $func:ident) => {
            #[allow(clippy::wrong_self_convention)]
            #[allow(missing_docs)]
            $vis fn $func(&self, s: SelectStatement) -> Expr {
                Expr::col(self.as_column_ref()).$func(s)
            }
        };
    }

    macro_rules! bind_array_oper {
        ($vis:vis $op:ident, $oper:ident) => {
            #[cfg(feature = "postgres-array")]
            /// Array operator. Postgres only.
            $vis fn $op<V, I>(&self, v: I) -> Expr
            where
                V: Into<Value> + sea_query::ValueType + sea_query::postgres_array::NotU8,
                I: IntoIterator<Item = V>,
            {
                use sea_query::extension::postgres::PgBinOper;

                let vec: Vec<_> = v.into_iter().collect();
                Expr::col(self.as_column_ref()).binary(PgBinOper::$oper, self.save_as(Expr::val(vec)))
            }
        };
    }

    pub(crate) use bind_array_oper;
    pub(crate) use bind_func_no_params;
    pub(crate) use bind_oper;
    pub(crate) use bind_subquery_func;
    pub(crate) use bind_vec_func;
}

use macros::*;

/// Operations and comparisons available on every entity column.
///
/// Implemented by the generated `Column` enum for each entity, this is what
/// turns `entity::COLUMN.field.eq(42)` into a filter expression. Most methods
/// (`eq`, `lt`, `like`, `is_in`, …) mirror their counterparts on
/// [`sea_query::Expr`] but qualify the column with its table automatically.
pub trait ColumnTrait: IdenStatic + Iterable + FromStr {
    /// The entity this column belongs to.
    type EntityName: EntityName;

    /// SQL type and constraints attached to this column.
    fn def(&self) -> ColumnDef;

    /// If the column maps to a database `ENUM`, the enum's type name.
    /// Returns `None` for non-enum columns.
    fn enum_type_name(&self) -> Option<&'static str> {
        None
    }

    /// Table iden of the entity this column belongs to.
    fn entity_name(&self) -> DynIden {
        SeaRc::new(Self::EntityName::default())
    }

    /// Fully-qualified `(table, column)` reference, used when building
    /// expressions that need to disambiguate columns across joined tables.
    fn as_column_ref(&self) -> (DynIden, DynIden) {
        (self.entity_name(), SeaRc::new(*self))
    }

    /// Perform equality against a Value. `None` will be converted to `IS NULL`.
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::fruit};
    ///
    /// assert_eq!(
    ///     fruit::Entity::find()
    ///         .filter(fruit::COLUMN.cake_id.eq(2))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `fruit`.`cake_id` = 2"
    /// );
    /// assert_eq!(
    ///     fruit::Entity::find()
    ///         .filter(fruit::COLUMN.cake_id.eq(Option::<i32>::None))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `fruit`.`cake_id` IS NULL"
    /// );
    /// ```
    fn eq<V>(&self, v: V) -> Expr
    where
        V: Into<Value>,
    {
        let v = v.into();
        if v == v.as_null() {
            Expr::col(self.as_column_ref()).is_null()
        } else {
            let expr = self.save_as(Expr::val(v));
            Expr::col(self.as_column_ref()).eq(expr)
        }
    }

    /// Perform inequality against a Value. `None` will be converted to `IS NOT NULL`.
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::fruit};
    ///
    /// assert_eq!(
    ///     fruit::Entity::find()
    ///         .filter(fruit::COLUMN.cake_id.ne(2))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `fruit`.`cake_id` <> 2"
    /// );
    /// assert_eq!(
    ///     fruit::Entity::find()
    ///         .filter(fruit::COLUMN.cake_id.ne(Option::<i32>::None))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit` WHERE `fruit`.`cake_id` IS NOT NULL"
    /// );
    /// ```
    fn ne<V>(&self, v: V) -> Expr
    where
        V: Into<Value>,
    {
        let v = v.into();
        if v == v.as_null() {
            Expr::col(self.as_column_ref()).is_not_null()
        } else {
            let expr = self.save_as(Expr::val(v));
            Expr::col(self.as_column_ref()).ne(expr)
        }
    }

    bind_oper!(gt, GreaterThan);
    bind_oper!(gte, GreaterThanOrEqual);
    bind_oper!(lt, SmallerThan);
    bind_oper!(lte, SmallerThanOrEqual);

    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.between(2, 3))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` BETWEEN 2 AND 3"
    /// );
    /// ```
    fn between<V>(&self, a: V, b: V) -> Expr
    where
        V: Into<Value>,
    {
        Expr::col(self.as_column_ref()).between(a, b)
    }

    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.not_between(2, 3))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` NOT BETWEEN 2 AND 3"
    /// );
    /// ```
    fn not_between<V>(&self, a: V, b: V) -> Expr
    where
        V: Into<Value>,
    {
        Expr::col(self.as_column_ref()).not_between(a, b)
    }

    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.like("cheese"))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE 'cheese'"
    /// );
    /// ```
    fn like<T>(&self, s: T) -> Expr
    where
        T: IntoLikeExpr,
    {
        Expr::col(self.as_column_ref()).like(s)
    }

    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.not_like("cheese"))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` NOT LIKE 'cheese'"
    /// );
    /// ```
    fn not_like<T>(&self, s: T) -> Expr
    where
        T: IntoLikeExpr,
    {
        Expr::col(self.as_column_ref()).not_like(s)
    }

    /// Postgres Only.
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.ilike("cheese"))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."name" ILIKE 'cheese'"#
    /// );
    /// ```
    fn ilike<T>(&self, s: T) -> Expr
    where
        T: IntoLikeExpr,
    {
        use sea_query::extension::postgres::PgExpr;

        Expr::col(self.as_column_ref()).ilike(s)
    }

    /// Postgres Only.
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.not_ilike("cheese"))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."name" NOT ILIKE 'cheese'"#
    /// );
    /// ```
    fn not_ilike<T>(&self, s: T) -> Expr
    where
        T: IntoLikeExpr,
    {
        use sea_query::extension::postgres::PgExpr;

        Expr::col(self.as_column_ref()).not_ilike(s)
    }

    /// This is a simplified shorthand for a more general `like` method.
    /// Use `like` if you need something more complex, like specifying an escape character.
    ///
    /// ## Examples
    ///
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.starts_with("cheese"))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE 'cheese%'"
    /// );
    /// ```
    fn starts_with<T>(&self, s: T) -> Expr
    where
        T: Into<String>,
    {
        let pattern = format!("{}%", s.into());
        Expr::col(self.as_column_ref()).like(pattern)
    }

    /// This is a simplified shorthand for a more general `like` method.
    /// Use `like` if you need something more complex, like specifying an escape character.
    ///
    /// ## Examples
    ///
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.ends_with("cheese"))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese'"
    /// );
    /// ```
    fn ends_with<T>(&self, s: T) -> Expr
    where
        T: Into<String>,
    {
        let pattern = format!("%{}", s.into());
        Expr::col(self.as_column_ref()).like(pattern)
    }

    /// This is a simplified shorthand for a more general `like` method.
    /// Use `like` if you need something more complex, like specifying an escape character.
    ///
    /// ## Examples
    ///
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.contains("cheese"))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
    /// );
    /// ```
    fn contains<T>(&self, s: T) -> Expr
    where
        T: Into<String>,
    {
        let pattern = format!("%{}%", s.into());
        Expr::col(self.as_column_ref()).like(pattern)
    }

    bind_func_no_params!(max);
    bind_func_no_params!(min);
    bind_func_no_params!(sum);
    bind_func_no_params!(avg);
    bind_func_no_params!(count);
    bind_func_no_params!(is_null);
    bind_func_no_params!(is_not_null);

    /// Provide fallback value if the column is null (null coalescing)
    fn if_null<V>(&self, v: V) -> Expr
    where
        V: Into<Value>,
    {
        Expr::col(self.as_column_ref()).if_null(v)
    }

    bind_vec_func!(is_in);
    bind_vec_func!(is_not_in);

    /// Postgres only.
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// // Compare with MySQL
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.is_in(std::iter::empty::<i32>()))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE 1 = 2"
    /// );
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.is_in(vec![4, 5]))
    ///         .build(DbBackend::MySql)
    ///         .to_string(),
    ///     "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`id` IN (4, 5)"
    /// );
    /// // Postgres Array
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.eq_any(std::iter::empty::<i32>()))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE 1 = 2"#
    /// );
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.eq_any(vec![4, 5]))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."id" = ANY(ARRAY [4,5])"#
    /// );
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Name.eq_any(&["Apple".to_owned(), "Chocolate".to_owned()]))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."name" = ANY(ARRAY ['Apple','Chocolate'])"#
    /// );
    /// ```
    #[cfg(feature = "postgres-array")]
    fn eq_any<V, I>(&self, v: I) -> Expr
    where
        V: Into<Value> + sea_query::postgres_array::NotU8,
        I: IntoIterator<Item = V>,
    {
        use sea_query::extension::postgres::PgFunc;

        let values: Vec<Value> = v.into_iter().map(|v| v.into()).collect();

        if let Some(first) = values.first() {
            Expr::col(self.as_column_ref()).eq(PgFunc::any(Value::Array(
                first.array_type(),
                Some(Box::new(values)),
            )))
        } else {
            Expr::col(self.as_column_ref()).is_in(std::iter::empty::<V>())
        }
    }

    /// Postgres only. Opposite of `eq_any` (equivalent to `is_not_in`).
    /// ```
    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
    ///
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.ne_all(std::iter::empty::<i32>()))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE 1 = 1"#
    /// );
    /// assert_eq!(
    ///     cake::Entity::find()
    ///         .filter(cake::Column::Id.ne_all(vec![4, 5]))
    ///         .build(DbBackend::Postgres)
    ///         .to_string(),
    ///     r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "cake"."id" <> ALL(ARRAY [4,5])"#
    /// );
    /// ```
    #[cfg(feature = "postgres-array")]
    fn ne_all<V, I>(&self, v: I) -> Expr
    where
        V: Into<Value> + sea_query::postgres_array::NotU8,
        I: IntoIterator<Item = V>,
    {
        use sea_query::extension::postgres::PgFunc;

        let values: Vec<Value> = v.into_iter().map(|v| v.into()).collect();

        if let Some(first) = values.first() {
            Expr::col(self.as_column_ref()).ne(PgFunc::all(Value::Array(
                first.array_type(),
                Some(Box::new(values)),
            )))
        } else {
            Expr::col(self.as_column_ref()).is_not_in(std::iter::empty::<V>())
        }
    }

    bind_subquery_func!(in_subquery);
    bind_subquery_func!(not_in_subquery);

    bind_array_oper!(array_contains, Contains);
    bind_array_oper!(array_contained, Contained);
    bind_array_oper!(array_overlap, Overlap);

    /// Wrap the column in a plain [`Expr`], suitable for use anywhere a
    /// `sea_query` expression is expected.
    fn into_expr(self) -> Expr {
        self.into_simple_expr()
    }

    /// Wrap the column as the expression used inside a `RETURNING` clause
    /// for the given backend.
    #[allow(clippy::match_single_binding)]
    fn into_returning_expr(self, db_backend: DbBackend) -> Expr {
        match db_backend {
            _ => Expr::col(self),
        }
    }

    /// Apply the standard SELECT-side cast for this column. By default,
    /// enum columns are cast to text; non-enum columns are returned as-is.
    fn select_as(&self, expr: Expr) -> Expr {
        self.select_enum_as(expr)
    }

    /// Cast an enum column to text; no-op for non-enum columns.
    fn select_enum_as(&self, expr: Expr) -> Expr {
        cast_enum_as(expr, &self.def(), select_enum_as)
    }

    /// Apply the standard write-side cast: convert text into the database's
    /// enum type for enum columns, return as-is otherwise.
    fn save_as(&self, val: Expr) -> Expr {
        self.save_enum_as(val)
    }

    /// Cast a value into the column's enum type; no-op for non-enum columns.
    fn save_enum_as(&self, val: Expr) -> Expr {
        cast_enum_as(val, &self.def(), save_enum_as)
    }

    /// JSON key used for this column when (de)serializing the model.
    #[cfg(feature = "with-json")]
    fn json_key(&self) -> &'static str {
        self.as_str()
    }
}

/// Extension methods on [`ColumnType`] for building [`ColumnDef`]s and
/// inspecting database `ENUM` metadata.
pub trait ColumnTypeTrait {
    /// Wrap this [`ColumnType`] in a fresh [`ColumnDef`].
    fn def(self) -> ColumnDef;

    /// Name of the database `ENUM` type if this column is an enum,
    /// `None` otherwise.
    fn get_enum_name(&self) -> Option<&DynIden>;
}

impl ColumnTypeTrait for ColumnType {
    fn def(self) -> ColumnDef {
        ColumnDef {
            col_type: self,
            null: false,
            unique: false,
            indexed: false,
            default: None,
            comment: None,
            unique_key: None,
            renamed_from: None,
            extra: None,
            seaography: Default::default(),
        }
    }

    fn get_enum_name(&self) -> Option<&DynIden> {
        enum_name(self)
    }
}

impl ColumnTypeTrait for ColumnDef {
    fn def(self) -> ColumnDef {
        self
    }

    fn get_enum_name(&self) -> Option<&DynIden> {
        enum_name(&self.col_type)
    }
}

fn enum_name(col_type: &ColumnType) -> Option<&DynIden> {
    match col_type {
        ColumnType::Enum { name, .. } => Some(name),
        ColumnType::Array(col_type) => enum_name(col_type),
        _ => None,
    }
}

struct Text;
struct TextArray;

impl Iden for Text {
    fn quoted(&self) -> Cow<'static, str> {
        Cow::Borrowed("text")
    }

    fn unquoted(&self) -> &str {
        match self.quoted() {
            Cow::Borrowed(s) => s,
            _ => unreachable!(),
        }
    }
}

impl Iden for TextArray {
    fn quoted(&self) -> Cow<'static, str> {
        // This is Postgres only and it has a special handling for quoting this
        Cow::Borrowed("text[]")
    }

    fn unquoted(&self) -> &str {
        match self.quoted() {
            Cow::Borrowed(s) => s,
            _ => unreachable!(),
        }
    }
}

pub(crate) fn select_enum_as(col: Expr, _: DynIden, col_type: &ColumnType) -> Expr {
    let type_name = match col_type {
        ColumnType::Array(_) => TextArray.into_iden(),
        _ => Text.into_iden(),
    };
    col.as_enum(type_name)
}

pub(crate) fn save_enum_as(col: Expr, enum_name: DynIden, col_type: &ColumnType) -> Expr {
    if matches!(col, Expr::Value(Value::Enum(_))) {
        return col;
    }
    #[cfg(feature = "postgres-array")]
    if matches!(
        col,
        Expr::Value(Value::Array(sea_query::ArrayType::Enum(_), _))
    ) {
        return col;
    }

    let type_name = match col_type {
        ColumnType::Array(_) => format!("{enum_name}[]").into_iden(),
        _ => enum_name,
    };
    col.as_enum(type_name)
}

pub(crate) fn cast_enum_as<F>(expr: Expr, col_def: &ColumnDef, f: F) -> Expr
where
    F: Fn(Expr, DynIden, &ColumnType) -> Expr,
{
    let col_type = col_def.get_column_type();

    match col_type {
        #[cfg(all(feature = "with-json", feature = "postgres-array"))]
        ColumnType::Json | ColumnType::JsonBinary => {
            use sea_query::ArrayType;
            use serde_json::Value as Json;

            match expr {
                Expr::Value(Value::Array(ArrayType::Json, Some(json_vec))) => {
                    // flatten Array(Vec<Json>) into Json
                    let json_vec: Vec<Json> = json_vec
                        .into_iter()
                        .filter_map(|val| match val {
                            Value::Json(Some(json)) => Some(*json),
                            _ => None,
                        })
                        .collect();
                    Expr::Value(Value::Json(Some(Box::new(json_vec.into()))))
                }
                Expr::Value(Value::Array(ArrayType::Json, None)) => Expr::Value(Value::Json(None)),
                _ => expr,
            }
        }
        _ => match col_type.get_enum_name() {
            Some(enum_name) => f(expr, enum_name.clone(), col_type),
            None => expr,
        },
    }
}

#[cfg(test)]
mod tests {
    use crate::{
        ColumnTrait, Condition, DbBackend, EntityTrait, QueryFilter, QueryTrait, tests_cfg::*,
    };
    use sea_query::Query;

    #[test]
    fn test_in_subquery_1() {
        assert_eq!(
            cake::Entity::find()
                .filter(
                    Condition::any().add(
                        cake::Column::Id.in_subquery(
                            Query::select()
                                .expr(cake::Column::Id.max())
                                .from(cake::Entity)
                                .to_owned()
                        )
                    )
                )
                .build(DbBackend::MySql)
                .to_string(),
            [
                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
                "WHERE `cake`.`id` IN (SELECT MAX(`cake`.`id`) FROM `cake`)",
            ]
            .join(" ")
        );
    }

    #[test]
    fn test_in_subquery_2() {
        assert_eq!(
            cake::Entity::find()
                .filter(
                    Condition::any().add(
                        cake::Column::Id.in_subquery(
                            Query::select()
                                .column(cake_filling::Column::CakeId)
                                .from(cake_filling::Entity)
                                .to_owned()
                        )
                    )
                )
                .build(DbBackend::MySql)
                .to_string(),
            [
                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
                "WHERE `cake`.`id` IN (SELECT `cake_id` FROM `cake_filling`)",
            ]
            .join(" ")
        );
    }

    #[test]
    #[cfg(feature = "macros")]
    fn select_as_1() {
        use crate::{ActiveModelTrait, ActiveValue, Update};

        mod hello_expanded {
            use crate as sea_orm;
            use crate::entity::prelude::*;
            use crate::sea_query::{Expr, ExprTrait};

            #[derive(Copy, Clone, Default, Debug, DeriveEntity)]
            pub struct Entity;

            impl EntityName for Entity {
                fn table_name(&self) -> &'static str {
                    "hello"
                }
            }

            #[derive(Clone, Debug, PartialEq, Eq, DeriveModel, DeriveActiveModel)]
            pub struct Model {
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
            pub enum Column {
                Id,
                One1,
                Two,
                Three3,
            }

            impl ColumnTrait for Column {
                type EntityName = Entity;

                fn def(&self) -> ColumnDef {
                    match self {
                        Column::Id => ColumnType::Integer.def(),
                        Column::One1 => ColumnType::Integer.def(),
                        Column::Two => ColumnType::Integer.def(),
                        Column::Three3 => ColumnType::Integer.def(),
                    }
                }

                fn select_as(&self, expr: Expr) -> Expr {
                    match self {
                        Self::Two => expr.cast_as("integer"),
                        _ => self.select_enum_as(expr),
                    }
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DerivePrimaryKey)]
            pub enum PrimaryKey {
                Id,
            }

            impl PrimaryKeyTrait for PrimaryKey {
                type ValueType = i32;

                fn auto_increment() -> bool {
                    true
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        #[allow(clippy::enum_variant_names)]
        mod hello_compact {
            use crate as sea_orm;
            use crate::entity::prelude::*;

            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
            #[sea_orm(table_name = "hello")]
            pub struct Model {
                #[sea_orm(primary_key)]
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                #[sea_orm(select_as = "integer")]
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        fn assert_it<E, A>(active_model: A)
        where
            E: EntityTrait,
            A: ActiveModelTrait<Entity = E>,
        {
            assert_eq!(
                E::find().build(DbBackend::Postgres).to_string(),
                r#"SELECT "hello"."id", "hello"."one1", CAST("hello"."two" AS integer), "hello"."three3" FROM "hello""#,
            );
            assert_eq!(
                Update::one(active_model)
                    .validate()
                    .unwrap()
                    .build(DbBackend::Postgres)
                    .to_string(),
                r#"UPDATE "hello" SET "one1" = 1, "two" = 2, "three3" = 3 WHERE "hello"."id" = 1"#,
            );
        }

        assert_it(hello_expanded::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
        assert_it(hello_compact::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
    }

    #[test]
    #[cfg(feature = "macros")]
    fn save_as_1() {
        use crate::{ActiveModelTrait, ActiveValue, Update};

        mod hello_expanded {
            use crate as sea_orm;
            use crate::entity::prelude::*;
            use crate::sea_query::{Expr, ExprTrait};

            #[derive(Copy, Clone, Default, Debug, DeriveEntity)]
            pub struct Entity;

            impl EntityName for Entity {
                fn table_name(&self) -> &'static str {
                    "hello"
                }
            }

            #[derive(Clone, Debug, PartialEq, Eq, DeriveModel, DeriveActiveModel)]
            pub struct Model {
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
            pub enum Column {
                Id,
                One1,
                Two,
                Three3,
            }

            impl ColumnTrait for Column {
                type EntityName = Entity;

                fn def(&self) -> ColumnDef {
                    match self {
                        Column::Id => ColumnType::Integer.def(),
                        Column::One1 => ColumnType::Integer.def(),
                        Column::Two => ColumnType::Integer.def(),
                        Column::Three3 => ColumnType::Integer.def(),
                    }
                }

                fn save_as(&self, val: Expr) -> Expr {
                    match self {
                        Self::Two => val.cast_as("text"),
                        _ => self.save_enum_as(val),
                    }
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DerivePrimaryKey)]
            pub enum PrimaryKey {
                Id,
            }

            impl PrimaryKeyTrait for PrimaryKey {
                type ValueType = i32;

                fn auto_increment() -> bool {
                    true
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        #[allow(clippy::enum_variant_names)]
        mod hello_compact {
            use crate as sea_orm;
            use crate::entity::prelude::*;

            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
            #[sea_orm(table_name = "hello")]
            pub struct Model {
                #[sea_orm(primary_key)]
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                #[sea_orm(save_as = "text")]
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        fn assert_it<E, A>(active_model: A)
        where
            E: EntityTrait,
            A: ActiveModelTrait<Entity = E>,
        {
            assert_eq!(
                E::find().build(DbBackend::Postgres).to_string(),
                r#"SELECT "hello"."id", "hello"."one1", "hello"."two", "hello"."three3" FROM "hello""#,
            );
            assert_eq!(
                Update::one(active_model)
                    .validate()
                    .unwrap()
                    .build(DbBackend::Postgres)
                    .to_string(),
                r#"UPDATE "hello" SET "one1" = 1, "two" = CAST(2 AS text), "three3" = 3 WHERE "hello"."id" = 1"#,
            );
        }

        assert_it(hello_expanded::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
        assert_it(hello_compact::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
    }

    #[test]
    #[cfg(feature = "macros")]
    fn select_as_and_value_1() {
        use crate::{ActiveModelTrait, ActiveValue, Update};

        mod hello_expanded {
            use crate as sea_orm;
            use crate::entity::prelude::*;
            use crate::sea_query::{Expr, ExprTrait};

            #[derive(Copy, Clone, Default, Debug, DeriveEntity)]
            pub struct Entity;

            impl EntityName for Entity {
                fn table_name(&self) -> &'static str {
                    "hello"
                }
            }

            #[derive(Clone, Debug, PartialEq, Eq, DeriveModel, DeriveActiveModel)]
            pub struct Model {
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
            pub enum Column {
                Id,
                One1,
                Two,
                Three3,
            }

            impl ColumnTrait for Column {
                type EntityName = Entity;

                fn def(&self) -> ColumnDef {
                    match self {
                        Column::Id => ColumnType::Integer.def(),
                        Column::One1 => ColumnType::Integer.def(),
                        Column::Two => ColumnType::Integer.def(),
                        Column::Three3 => ColumnType::Integer.def(),
                    }
                }

                fn select_as(&self, expr: Expr) -> Expr {
                    match self {
                        Self::Two => expr.cast_as("integer"),
                        _ => self.select_enum_as(expr),
                    }
                }

                fn save_as(&self, val: Expr) -> Expr {
                    match self {
                        Self::Two => val.cast_as("text"),
                        _ => self.save_enum_as(val),
                    }
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DerivePrimaryKey)]
            pub enum PrimaryKey {
                Id,
            }

            impl PrimaryKeyTrait for PrimaryKey {
                type ValueType = i32;

                fn auto_increment() -> bool {
                    true
                }
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        #[allow(clippy::enum_variant_names)]
        mod hello_compact {
            use crate as sea_orm;
            use crate::entity::prelude::*;

            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
            #[sea_orm(table_name = "hello")]
            pub struct Model {
                #[sea_orm(primary_key)]
                pub id: i32,
                #[sea_orm(enum_name = "One1")]
                pub one: i32,
                #[sea_orm(select_as = "integer", save_as = "text")]
                pub two: i32,
                #[sea_orm(enum_name = "Three3")]
                pub three: i32,
            }

            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
            pub enum Relation {}

            impl ActiveModelBehavior for ActiveModel {}
        }

        fn assert_it<E, A>(active_model: A)
        where
            E: EntityTrait,
            A: ActiveModelTrait<Entity = E>,
        {
            assert_eq!(
                E::find().build(DbBackend::Postgres).to_string(),
                r#"SELECT "hello"."id", "hello"."one1", CAST("hello"."two" AS integer), "hello"."three3" FROM "hello""#,
            );
            assert_eq!(
                Update::one(active_model)
                    .validate()
                    .unwrap()
                    .build(DbBackend::Postgres)
                    .to_string(),
                r#"UPDATE "hello" SET "one1" = 1, "two" = CAST(2 AS text), "three3" = 3 WHERE "hello"."id" = 1"#,
            );
        }

        assert_it(hello_expanded::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
        assert_it(hello_compact::ActiveModel {
            id: ActiveValue::set(1),
            one: ActiveValue::set(1),
            two: ActiveValue::set(2),
            three: ActiveValue::set(3),
        });
    }
}
