import tel.egram.kuery.*

object Organizations : Table("organizations") {
	val id = Column("id")
	val name = Column("name")
}

object Employees : Table("employees") {
	val id = Column("id")
	val name = Column("name")
	val organizationId = Column("organization_id")
}

import tel.egram.kuery.*
import tel.egram.kuery.sqlite.*

val statement = from(Employees).where { e -> e.id eq 1 }.select { e -> e.name }
val sql = statement.toString(SQLiteDialect)
print(sql) // SELECT "name" FROM "employees" WHERE "id" = 1

import tel.egram.kuery.*
import tel.egram.kuery.sqlite.*

// CREATE TABLE "organizations" ...
over(Organizations)
    .create {
        integer(it.id).primaryKey(autoIncrement = true)..
        text(it.name).unique().notNull()
    }
    
// CREATE TABLE "employees" ...
over(Employees)
    .create {
        integer(it.id).primaryKey(autoIncrement = true)..
        text(it.name).unique().notNull()..
        integer(it.organizationId).foreignKey(references = Organizations.id)
    }

// DROP TABLE "employees"
over(Employees).drop()

// INSERT INTO "employees"("name", "organization_id") VALUES("John Doe", 1)
into(Employees)
    .insert { e -> e.name("John Doe") .. e.organizationId(1) }

// SELECT "id", "name" FROM "organizations" WHERE ...
from(Employees)
    .where { e -> (e.organizationId ne null) and (e.name eq "John Doe") }
    .groupBy { e -> e.name }
    .having { e -> e.id ne null }
    .orderBy { e -> e.name.asc .. e.id.desc }
    .limit { 10 }
    .offset { 10 }
    .select { e -> e.id .. e.name }

// SELECT ... FROM "organizations" JOIN "employees" ON ...
from(Organizations)
    .join(Employees).on { o, e -> o.id eq e.organizationId }
    .select { o, e -> o.name .. e.name }

// UPDATE "organizations" SET "name" = 'John Doe' WHERE "id" = 1
from(Organizations)
    .where { o -> o.id eq 1 }
    .update { o -> o.name("John Doe") }

// DELETE FROM "organizations" WHERE "id" = 0
from(Organizations)
    .where { o -> o.id eq 0 }
    .delete()