// Define a data class extending KPojo
@Table(name = "tb_user") data class User(
    @PrimaryKey(identity = true) val id: Long? = null,
    val username: String? = null,
    val age: Int? = null
) : KPojo

// We can use dataSource.table.createTable<User>() to create the table,
// or dataSource.table.sync<User>() to synchronize the table structure.
dataSource.table.createTable<User>()

// Insert a record
// we can use List<User>.insert().execute() to batch insert
User(id = 1, username = "test", age = 18).insert().execute()

// Query
val listOfUser = User(age = 18).select().queryList()

// Update
User(id = 1, age = 19).update().by { it.id }.execute()

// Delete
User(id = 1).delete().execute()

Kronos.dataSource = { KronosBasicWrapper(SomeDataSource()) }
// for example:
//Kronos.dataSource = { KronosBasicWrapper(MysqlDataSource("jdbc:mysql://localhost:3306/test", "root", "***")) }
//Kronos.dataSource = { KronosBasicWrapper(HikariDataSource().apply { jdbcUrl = "jdbc:mysql://localhost:3306/test" ... }) }
//Kronos.dataSource = { KronosBasicWrapper(BasicDataSource().apply { url = "jdbc:sqlite:path/to/db" ... }) }

@Table(name = "tb_movie")
@TableIndex("idx_name", ["name"], Mysql.KIndexType.UNIQUE, Mysql.KIndexMethod.BTREE)
data class Movie(
    @PrimaryKey(identity = true)
    val id: Long? = null, // primary key and auto increment
    val name: String? = null, // movie name
    val directorId: Long? = null, // director id
    @Cascade(["directorId"], ["id"])
    val director: Director? = null, // cascade table & one-to-many
    val relations: List<MovieActorRelation>? = null, // reference list & many-to-many
    @Serializable
    val type: List<String>? = null, // deserialize from string
    @Column("movie_summary")
    val summary: String? = null, // summary with column name
    @Version val version: Long? = null, // version for optimistic lock
    @LogicDelete val deleted: Boolean? = null, // logic delete
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @UpdateTime val updateTime: String? = null, // update time and date format
    @CreateTime val createTime: LocalDateTime? = null // create time
) : KPojo { // KPojo is a marker interface
    var actors: List<Actor>? by manyToMany(::relations) // many-to-many
}

val movie = Movie(1)
val dataMap: Map<String, Any?> = movie.toDataMap() // Map("id" = 1)
val movie2: Movie = dataMap.mapperTo<Movie>() // or dataMap.mapperTo(Movie::class)
val tableName = movie.kronosTable() // "tb_movie", NO REFLECTION used
val columns = movie.kronosColumns() // [Field(id), Field(name), ...], NO REFLECTION used

//is table exists
dataSource.table.exists<Movie>()

// create table
dataSource.table.createTable<Movie>()

// drop table
dataSource.table.dropTable<Movie>()
// or
dataSource.table.dropTable("tb_movie")

//sync table structure
dataSource.table.syncTable<Movie>()

// single query
val listOfUser: List<User> = User()
    .select { it.id + it.username }
    .where { it.id < 10 && it.age >= 18 }
    .distinct()
    .groupBy { it.id }
    .orderBy { it.username.desc() }
    .queryList()

// with Pagination
val (total, list) = User()
    .select { it.id + it.username }
    .where { it.id < 10 && it.username like "a%" }
    .distinct()
    .groupBy { it.id }
    .orderBy { it.username.desc() }
    .page(1, 10)
    .withTotal()
    .queryList()

// multi-table query
val listOfMap = User().join(UserRelation(), UserRole()) { user, relation, role ->
    on { user.id == relation.userId && user.id == role.userId }
    select {
        user.id + user.username + relation.id.as_("relationId") + 
                role.role + f.count(1).as_("count")
    }
    where { user.id < 10 }
}.query()

// Using Native SQL Queries with Named Parameters
val result: Map<String, Any> = dataSource.query("select * from tb_user where id = :id", mapOf("id" to 1))

// single insert
user.insert().execute()

// batch insert
listOfUser.insert().execute()

// update by some conditions use `set`
user.update()
    .set {
        it.username = "123"
        it.score += 10
    }
    .by { it.id }
    .execute()

// update by some conditions, data from record
user.update { it.username + it.gender }
    .by { it.id }
    .execute()

// upsert on some columns
user.upsert { it.username }
    .on { it.id }
//  .lock(PessimisticLock.X) // You can specify the type of lock, and pessimistic lock is used by default
    .execute()

// upsert on duplicate key
user.upsert { it.username }
    .onConfict() // We have achieved compatibility with different databases
    .execute()

// delete rows by some conditions
user.delete()
    .where { it.id == 1 }
    .execute()