use std::borrow::Cow;
use sqlx::types::time::PrimitiveDateTime;
use time::macros::datetime;
use taitan_orm::database::sqlite::prelude::*;
use taitan_orm::prelude::*;
#[derive(Debug, Schema, Clone)]
#[table(user)]
#[unique(uk_name=(name))]
#[index(idx_hello=(age, birthday))]
#[primary(id)]
pub struct User {
 id: i32,
 name: String,
 age: Option<i32>,
 birthday: Option<PrimitiveDateTime>,
}
#[tokio::main]
async fn main() -> taitan_orm::result::Result<()> {
    tracing_subscriber::fmt()
            .with_max_level(tracing::Level::TRACE)
            .init();
 
    // setup database
    // refer to docs/setup.md for database setup
    let db = ...;
    db.execute_plain("DROP TABLE IF EXISTS `user`").await?;
    db.execute_plain(
     "CREATE TABLE IF NOT EXISTS `user`(`id` INT PRIMARY KEY, `age` INT, `name` VARCHAR(64), `birthday` DATETIME)",
    )
            .await?;
    db.execute_plain("CREATE UNIQUE INDEX `uk_name` ON `user` (`name`);")
            .await?;
    db.execute_plain("CREATE INDEX `idx_age_birthday` ON `user` (`age`, `birthday`);")
            .await?;
   
    // 1. insert entity
    let entity = User {
     id: 1,
     name: "Allen".to_string(),
     age: Option::Some(23),
     birthday: Option::Some(datetime!(2019-01-01 0:00)),
    };
    let result = db.insert(&entity).await?;
   
    // 2. update
    let mutation = UserMutation {
     name: None,
     age: Some(Some(24)),
     birthday: None,
    };
    let primary = UserPrimary { id: 1 };
    let result = db.update(&mutation, &primary).await?;
    assert_eq!(result, true);
   
    // 3. select
    let selection = UserSelected::default();
    let entity: Option<UserSelected> = db.select(&selection, &primary).await?;
    assert!(entity.is_some());
   
    // 4. select by unique
    let uk = UserUniqueUkName {
     name: "Allen".to_string(),
    };
    let unique_entity: Option<UserSelected> = db.select(&selection, &uk).await?;
    assert!(unique_entity.is_some());
   
    // 5. search by index
    let index = UserIndexIdxHello::AgeBirthday {
     age: Expr::from("=", 24)?,
     birthday: Expr::from("=", datetime!(2019-01-01 0:00))?,
    };
    let pagination = Pagination::new(10, 0);
    let order_by = UserOrderBy::build(vec!["id"]).unwrap();
    let index_entities: Vec<UserSelected> = db
            .search::<UserSelected>(&selection, &index, &order_by, &pagination)
            .await?;
    assert_eq!(index_entities.len(), 1);
   
    // 6. search
    let selection = UserSelected::default();
    let location = UserLocation::Id(Expr {
     val: Some(1),
     cmp: Cmp::Eq,
    });
    let entities: Vec<UserSelected> = db
            .search(&selection, &location, &order_by, &pagination)
            .await?;
    assert_eq!(entities.len(), 1);
   
    // 7. delete
    let result = db.delete(&primary).await?;
    assert_eq!(result, true);
   
    let entity: Option<UserSelected> = db.select(&selection, &primary).await?;
    assert!(entity.is_none());
   
    println!("crud success!");
    Ok(())
}

#[derive(Debug, Schema, Clone)]
#[table(user)]
#[unique(uk_name=(name))]
#[index(idx_hello=(age, birthday))]
#[primary(id)]
pub struct User {
    id: i32,
    name: String,
    age: Option<i32>,
    birthday: Option<PrimitiveDateTime>,
}
// struct for primary key 
pub struct UserPrimary {
    id: i32
}
// struct for update
// None: skip this field
// Some(None): null
// Some(Some(23)): actual set value
// before 0.1.9, there is a special enum Optional to support null expression
// 0.1.10 remove Optional, use Option<Option<T>> instead.
pub struct UserMutation {
    name: Option<Option<String>>,
    age: Option<Option<i32>>,
    birthday: Option<Option<PrimitiveDateTime>>
}
// struct for unique key
pub struct UserNameUnique { 
    name: String 
}
// struct for index_hello, designed for index prefix matching
// age is allowed,
// age, birthday is allowed
// birthday is not allowed
pub enum UserIndexIdxHello {
    Age { 
        age: LocationExpr<i32> 
    },
    AgeBirthday{ 
        age: LocationExpr<i32>, 
        birthday: LocationExpr<PrimitiveDateTime> 
    }
}
// struct to generate where condition 
pub enum UserLocation {
    Id(LocationExpr<i32>),
    Name(LocationExpr<String>),
    Age(LocationExpr<i32>),
    Birthday(LocationExpr<PrimitiveDateTime>)
}
// struct to select field and recv result from database 
pub struct UserSelected {
    id: Option<Option<i32>>,
    name: Option<Option<String>>,
    age: Option<Option<i32>>,
    birthday: Option<Option<PrimitiveDateTime>>
}

#[derive(Template, Debug)]
#[template(
 source = "UPDATE `user` SET name = :{name} WHERE `id` = :{id}",
 ext = "txt"
)]
pub struct UserUpdateTemplate {
 id: i32,
 name: String,
}
#[derive(Template, Debug)]
#[template(
 source = "select `id`, `name`, `age` FROM `user` where `id` >= :{id}",
 ext = "txt"
)]
pub struct UserSelectTemplate {
 id: i32,
}
#[derive(Template, Debug)]
#[template(
 source = "select `id`, `name`, `age` FROM `user` where {% if age.is_some() %} age >= :{age} AND {% endif %} `name` = :{name}",
 ext = "txt"
)]
pub struct UserCustomTemplate {
 name: String,
 age: Option<i32>,
}

async fn trx_insert_user(
    db: &mut SqliteDatabase,
    user1: &User,
    user2: &User,
) -> taitan_orm::result::Result<()> {
    let mut trx = db.transaction().await?; // create a transaction, trx
    trx.insert(user1).await?;              // same api as database
    trx.insert(user2).await?;              // rollback if there is any error
    trx.commit().await?;                   // commit it
    Ok(())                                 // when trx drop, if not commit, rollback 
}