mod user {
    use sea_orm::entity::prelude::*;

    #[sea_orm::model]
    #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
    #[sea_orm(table_name = "user")]
    pub struct Model {
        #[sea_orm(primary_key)]
        pub id: i32,
        pub name: String,
        #[sea_orm(unique)]
        pub email: String,
        #[sea_orm(has_one)]
        pub profile: HasOne<super::profile::Entity>,
        #[sea_orm(has_many)]
        pub posts: HasMany<super::post::Entity>,
    }
}
mod post {
    use sea_orm::entity::prelude::*;

    #[sea_orm::model]
    #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
    #[sea_orm(table_name = "post")]
    pub struct Model {
        #[sea_orm(primary_key)]
        pub id: i32,
        pub user_id: i32,
        pub title: String,
        #[sea_orm(belongs_to, from = "user_id", to = "id")]
        pub author: HasOne<super::user::Entity>,
        #[sea_orm(has_many, via = "post_tag")] // M-N relation with junction
        pub tags: HasMany<super::tag::Entity>,
    }
}

// join paths:
// user -> profile
// user -> post
//         post -> post_tag -> tag
let smart_user = user::Entity::load()
    .filter_by_id(42) // shorthand for .filter(user::COLUMN.id.eq(42))
    .with(profile::Entity) // 1-1 uses join
    .with((post::Entity, tag::Entity)) // 1-N uses data loader
    .one(db)
    .await?
    .unwrap();

// 3 queries are executed under the hood:
// 1. SELECT FROM user JOIN profile WHERE id = $
// 2. SELECT FROM post WHERE user_id IN (..)
// 3. SELECT FROM tag JOIN post_tag WHERE post_id IN (..)

smart_user
    == user::ModelEx {
        id: 42,
        name: "Bob".into(),
        email: "bob@sea-ql.org".into(),
        profile: HasOne::Loaded(
            profile::ModelEx {
                picture: "image.jpg".into(),
            }
            .into(),
        ),
        posts: HasMany::Loaded(vec![post::ModelEx {
            title: "Nice weather".into(),
            tags: HasMany::Loaded(vec![tag::ModelEx {
                tag: "sunny".into(),
            }]),
        }]),
    };

// this creates the nested object as shown above:
let user = user::ActiveModel::builder()
    .set_name("Bob")
    .set_email("bob@sea-ql.org")
    .set_profile(profile::ActiveModel::builder().set_picture("image.jpg"))
    .add_post(
        post::ActiveModel::builder()
            .set_title("Nice weather")
            .add_tag(tag::ActiveModel::builder().set_tag("sunny")),
    )
    .save(db)
    .await?;

// SeaORM resolves foreign key dependencies and creates the tables in topological order.
// Requires the `entity-registry` and `schema-sync` feature flags.
db.get_schema_registry("my_crate::entity::*").sync(db).await;

let user = Item { name: "Bob" }; // nested parameter access
let ids = [2, 3, 4]; // expanded by the `..` operator

let user: Option<user::Model> = user::Entity::find()
    .from_raw_sql(raw_sql!(
        Sqlite,
        r#"SELECT "id", "name" FROM "user"
           WHERE "name" LIKE {user.name}
           AND "id" in ({..ids})
        "#
    ))
    .one(db)
    .await?;

// find all models
let cakes: Vec<cake::Model> = Cake::find().all(db).await?;

// find and filter
let chocolate: Vec<cake::Model> = Cake::find()
    .filter(Cake::COLUMN.name.contains("chocolate"))
    .all(db)
    .await?;

// find one model
let cheese: Option<cake::Model> = Cake::find_by_id(1).one(db).await?;
let cheese: cake::Model = cheese.unwrap();

// find related models (lazy)
let fruit: Option<fruit::Model> = cheese.find_related(Fruit).one(db).await?;

// find related models (eager): for 1-1 relations
let cake_with_fruit: Vec<(cake::Model, Option<fruit::Model>)> =
    Cake::find().find_also_related(Fruit).all(db).await?;

// find related models (eager): works for both 1-N and M-N relations
let cake_with_fillings: Vec<(cake::Model, Vec<filling::Model>)> = Cake::find()
    .find_with_related(Filling) // for M-N relations, two joins are performed
    .all(db) // rows are automatically consolidated by left entity
    .await?;

use sea_orm::DerivePartialModel;

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity")]
struct CakeWithFruit {
    id: i32,
    name: String,
    #[sea_orm(nested)]
    fruit: Option<fruit::Model>, // this can be a regular or another partial model
}

let cakes: Vec<CakeWithFruit> = Cake::find()
    .left_join(fruit::Entity) // no need to specify join condition
    .into_partial_model() // only the columns in the partial model will be selected
    .all(db)
    .await?;

let apple = fruit::ActiveModel {
    name: Set("Apple".to_owned()),
    ..Default::default() // no need to set primary key
};

let pear = fruit::ActiveModel {
    name: Set("Pear".to_owned()),
    ..Default::default()
};

// insert one: Active Record style
let apple = apple.insert(db).await?;
apple.id == 1;

// insert one: repository style
let result = Fruit::insert(apple).exec(db).await?;
result.last_insert_id == 1;

// insert many returning last insert id
let result = Fruit::insert_many([apple, pear]).exec(db).await?;
result.last_insert_id == Some(2);

// insert many with returning (if supported by database)
let models: Vec<fruit::Model> = Fruit::insert_many([apple, pear])
    .exec_with_returning(db)
    .await?;
models[0]
    == fruit::Model {
        id: 1, // database assigned value
        name: "Apple".to_owned(),
        cake_id: None,
    };

// insert with ON CONFLICT on primary key do nothing, with MySQL specific polyfill
let result = Fruit::insert_many([apple, pear])
    .on_conflict_do_nothing()
    .exec(db)
    .await?;

matches!(result, TryInsertResult::Conflicted);

use sea_orm::sea_query::{Expr, Value};

let pear: Option<fruit::Model> = Fruit::find_by_id(1).one(db).await?;
let mut pear: fruit::ActiveModel = pear.unwrap().into();

pear.name = Set("Sweet pear".to_owned()); // update value of a single field

// update one: only changed columns will be updated
let pear: fruit::Model = pear.update(db).await?;

// update many: UPDATE "fruit" SET "cake_id" = "cake_id" + 2
//               WHERE "fruit"."name" LIKE '%Apple%'
Fruit::update_many()
    .col_expr(fruit::COLUMN.cake_id, fruit::COLUMN.cake_id.add(2))
    .filter(fruit::COLUMN.name.contains("Apple"))
    .exec(db)
    .await?;

let banana = fruit::ActiveModel {
    id: NotSet,
    name: Set("Banana".to_owned()),
    ..Default::default()
};

// create, because primary key `id` is `NotSet`
let mut banana = banana.save(db).await?;

banana.id == Unchanged(2);
banana.name = Set("Banana Mongo".to_owned());

// update, because primary key `id` is present
let banana = banana.save(db).await?;

// delete one: Active Record style
let orange: Option<fruit::Model> = Fruit::find_by_id(1).one(db).await?;
let orange: fruit::Model = orange.unwrap();
orange.delete(db).await?;

// delete one: repository style
let orange = fruit::ActiveModel {
    id: Set(2),
    ..Default::default()
};
fruit::Entity::delete(orange).exec(db).await?;

// delete many: DELETE FROM "fruit" WHERE "fruit"."name" LIKE '%Orange%'
fruit::Entity::delete_many()
    .filter(fruit::COLUMN.name.contains("Orange"))
    .exec(db)
    .await?;

#[derive(FromQueryResult)]
struct CakeWithBakery {
    name: String,
    #[sea_orm(nested)]
    bakery: Option<Bakery>,
}

#[derive(FromQueryResult)]
struct Bakery {
    #[sea_orm(alias = "bakery_name")]
    name: String,
}

let cake_ids = [2, 3, 4]; // expanded by the `..` operator

// can use many APIs with raw SQL, including nested select
let cake: Option<CakeWithBakery> = CakeWithBakery::find_by_statement(raw_sql!(
    Sqlite,
    r#"SELECT "cake"."name", "bakery"."name" AS "bakery_name"
       FROM "cake"
       LEFT JOIN "bakery" ON "cake"."bakery_id" = "bakery"."id"
       WHERE "cake"."id" IN ({..cake_ids})"#
))
.one(db)
.await?;