orm_mysql 1.5.0

use orm_mysql::mysql::{OrmMySqlTrait, OrmMySqlTraitConn};
use orm_mysql::mysql_async::{self, prelude::*};
use orm_mysql::OrmMySql;

const DB_URL: &str = "mysql://name:pwd@ip:port/db_name";

#[tokio::main]
async fn main() -> common_uu::IResult {
    basic_users_temp_flow().await?;
    string_id_flow().await?;
    find_delete_i64_flow().await?;
    find_delete_string_flow().await?;
    transaction_find_delete_flow().await?;
    auto_increment_id_flow().await?;
    println!("All mysql_all scenarios finished successfully.");
    Ok(())
}

/// End-to-end CRUD on the users_temp table using numerical ids.
async fn basic_users_temp_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_users_temp(&mut conn).await?;

    let mut u1 = UserData::default();
    u1.user_id = 1;
    u1.username = "alpha".into();
    u1.username2 = Some("alpha-extra".into());

    let mut u2 = UserData::default();
    u2.user_id = 2;
    u2.username = "beta".into();
    u2.username2 = Some("beta-extra".into());

    conn.insert_arr(vec![u1.clone(), u2.clone()]).await?;
    let rows = UserData::query(&mut conn, "where 1=1 order by user_id", Some(10)).await?;
    assert_eq!(rows.len(), 2, "two users should exist after insert");

    u1.delete(&mut conn).await?;
    let deleted = UserData::query(&mut conn, "where user_id = 1", Some(1)).await?;
    assert!(deleted.is_empty(), "user_id=1 must be deleted");
    Ok(())
}

/// Basic CRUD on the users_str_temp table using string ids.
async fn string_id_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_users_str_temp(&mut conn).await?;

    let mut d1 = UserDataStr::default();
    d1.user_id = "s-1".into();
    d1.data = "payload-1".into();

    let mut d2 = UserDataStr::default();
    d2.user_id = "s-2".into();
    d2.data = "payload-2".into();

    conn.insert_arr(vec![d1.clone(), d2.clone()]).await?;
    let rows = UserDataStr::query(&mut conn, "where 1=1", Some(10)).await?;
    assert_eq!(rows.len(), 2, "string table should have two rows");

    d2.delete(&mut conn).await?;
    let remaining = UserDataStr::query(&mut conn, "where user_id = 's-2'", Some(1)).await?;
    assert!(remaining.is_empty(), "string user s-2 should be deleted");
    Ok(())
}

/// Mirrors the mysql_find_delete_by_id example for i64 ids.
async fn find_delete_i64_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_user_i64_table(&mut conn).await?;

    UserI64 { id: 1, name: "Alice".into() }.insert(&mut conn).await?;
    UserI64 { id: 2, name: "Bob".into() }.insert(&mut conn).await?;

    assert_eq!(UserI64::find_by_id(&mut conn, 1).await?.unwrap().name, "Alice");
    assert_eq!(UserI64::find_by_id(&mut conn, 2).await?.unwrap().name, "Bob");
    assert!(UserI64::find_by_id(&mut conn, 999).await?.is_none());

    let deleted = UserI64::delete_by_id(&mut conn, 1).await?;
    assert_eq!(deleted, 1, "delete_by_id should remove one row");
    assert!(UserI64::find_by_id(&mut conn, 1).await?.is_none());
    Ok(())
}

/// Mirrors mysql_find_delete_by_id example for string ids.
async fn find_delete_string_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_user_str_table(&mut conn).await?;

    UserStr { user_id: "uuid-001".into(), data: "first".into() }.insert(&mut conn).await?;
    UserStr { user_id: "uuid-002".into(), data: "second".into() }.insert(&mut conn).await?;

    assert_eq!(
        UserStr::find_by_id(&mut conn, "uuid-001".into()).await?.unwrap().data,
        "first"
    );
    assert_eq!(
        UserStr::find_by_id(&mut conn, "uuid-002".into()).await?.unwrap().data,
        "second"
    );
    assert!(UserStr::find_by_id(&mut conn, "not-exist".into()).await?.is_none());

    let deleted = UserStr::delete_by_id(&mut conn, "uuid-001".into()).await?;
    assert_eq!(deleted, 1);
    assert!(UserStr::find_by_id(&mut conn, "uuid-001".into()).await?.is_none());
    Ok(())
}

/// Shows transaction usage for find/delete helpers and ensures rollback works.
async fn transaction_find_delete_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_user_tx_table(&mut conn).await?;

    let mut tx = pool.start_transaction(mysql_async::TxOpts::new()).await?;
    UserTx { id: 100, name: "tx-user".into() }.insert(&mut tx).await?;

    let found = UserTx::find_by_id(&mut tx, 100).await?;
    assert_eq!(found.unwrap().name, "tx-user");

    UserTx::delete_by_id(&mut tx, 100).await?;
    assert!(UserTx::find_by_id(&mut tx, 100).await?.is_none());

    tx.rollback().await?;
    let persisted = UserTx::find_by_id(&mut conn, 100).await?;
    assert!(persisted.is_none(), "rollback should remove tx-user");
    Ok(())
}

/// Validates that inserts on an AUTO_INCREMENT table return the generated id.
async fn auto_increment_id_flow() -> common_uu::IResult {
    let pool = mysql_async::Pool::new(DB_URL);
    let mut conn = pool.get_conn().await?;
    recreate_users_auto_inc(&mut conn).await?;

    let mut user = UserAutoIncrement::default();
    user.user_id = 0;
    user.username = "auto-test".into();

    let inserted_id = user.insert(&mut conn).await?;
    assert!(inserted_id > 0, "insert should return auto-increment id");

    let fetched = UserAutoIncrement::find_by_id(&mut conn, inserted_id).await?;
    let fetched = fetched.expect("auto incremented user must exist");
    assert_eq!(fetched.username, "auto-test");
    Ok(())
}

async fn recreate_users_temp(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    r"DROP TABLE IF EXISTS users_temp".ignore(&mut *conn).await?;
    r"CREATE TABLE users_temp (
        user_id int   not null,
        username      varchar(128) null,
        username2      varchar(128) null,
        username3      varchar(128) null,
        username4      varchar(128) null,
        username5      varchar(128) null,
        username6      varchar(128) null,
        username7      varchar(128) null,
        username8      varchar(128) null,
        username9      varchar(128) null,
        username10      varchar(128) null,
        username11      varchar(128) null,
        username12      varchar(128) null,
        username13      varchar(128) null,
        username14      varchar(128) null,
        username15      varchar(128) null,
        float_v      float(8,2) null
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

async fn recreate_users_str_temp(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    r"DROP TABLE IF EXISTS users_str_temp".ignore(&mut *conn).await?;
    r"CREATE TABLE users_str_temp (
        user_id     varchar(128)  not null,
        data      varchar(128) null
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

async fn recreate_user_i64_table(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    "DROP TABLE IF EXISTS test_user_i64".ignore(&mut *conn).await?;
    "CREATE TABLE test_user_i64 (
        id BIGINT NOT NULL PRIMARY KEY,
        name VARCHAR(128) NOT NULL
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

async fn recreate_user_str_table(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    "DROP TABLE IF EXISTS test_user_str".ignore(&mut *conn).await?;
    "CREATE TABLE test_user_str (
        user_id VARCHAR(64) NOT NULL PRIMARY KEY,
        data VARCHAR(128) NOT NULL
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

async fn recreate_user_tx_table(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    r"DROP TABLE IF EXISTS test_user_tx".ignore(&mut *conn).await?;
    r"CREATE TABLE test_user_tx (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(128) NOT NULL
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

async fn recreate_users_auto_inc(conn: &mut mysql_async::Conn) -> common_uu::IResult {
    r"DROP TABLE IF EXISTS users_auto_inc".ignore(&mut *conn).await?;
    r"CREATE TABLE users_auto_inc (
        user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(128) NOT NULL
    )"
    .ignore(&mut *conn)
    .await?;
    Ok(())
}

// -----------------------------------------------------------------------------
//  Unit tests — each test is small, focused, and can run independently.
// -----------------------------------------------------------------------------

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

    #[tokio::test]
    async fn test_basic_users_temp_flow() -> common_uu::IResult {
        basic_users_temp_flow().await
    }

    #[tokio::test]
    async fn test_string_id_flow() -> common_uu::IResult {
        string_id_flow().await
    }

    #[tokio::test]
    async fn test_find_delete_i64_flow() -> common_uu::IResult {
        find_delete_i64_flow().await
    }

    #[tokio::test]
    async fn test_find_delete_string_flow() -> common_uu::IResult {
        find_delete_string_flow().await
    }

    #[tokio::test]
    async fn test_transaction_find_delete_flow() -> common_uu::IResult {
        transaction_find_delete_flow().await
    }

    #[tokio::test]
    async fn test_auto_increment_id_flow() -> common_uu::IResult {
        auto_increment_id_flow().await
    }
}

// --------------------------------- ORM structs ---------------------------------

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name=users_temp)]
struct UserData {
    #[orm_mysql(id)]
    user_id: i64,
    username: String,
    username2: Option<String>,
    username3: Option<String>,
    username4: Option<String>,
    username5: Option<String>,
    username6: Option<String>,
    username7: Option<String>,
    username8: Option<String>,
    username9: Option<String>,
    username10: Option<String>,
    username11: Option<String>,
    username12: Option<String>,
    username13: String,
    username14: String,
    username15: String,
    float_v: f64,
}

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name=users_str_temp)]
struct UserDataStr {
    #[orm_mysql(id)]
    user_id: String,
    data: String,
}

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name = test_user_i64)]
struct UserI64 {
    #[orm_mysql(id)]
    id: i64,
    name: String,
}

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name = test_user_str)]
struct UserStr {
    #[orm_mysql(id)]
    user_id: String,
    data: String,
}

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name = test_user_tx)]
struct UserTx {
    #[orm_mysql(id)]
    id: i32,
    name: String,
}

#[derive(OrmMySql, Default, Debug, Clone)]
#[orm_mysql(table_name = users_auto_inc)]
struct UserAutoIncrement {
    #[orm_mysql(id, auto_increment)]
    user_id: i64,
    username: String,
}