import Lite4D from "lite4d/lib/database";

const DB = () => {
	//set the connection:D1Database
	return (new Lite4D()).connection(env.TEST)
}

//if you are using honojs
import { Context, Hono } from "hono";

const DB = (c: Context) => {
	return (new Lite4D()).connection(c.env.TEST)
}

(new Lite4D()).connection(c.env.TEST).query()
	//get a query builder
	(new Lite4D()).connection(c.env.TEST).table('student_test')
//get a builder and set the table

//object
await DB(c).table('student_test').insert({
	name: 'Joe',
	age: 16,
	score: 99,
	class_id: 1,
})
//map
await DB(c).table('student_test').insert(new Map(Object.entries({
	name: 'John',
	age: 26,
	score: 49,
	class_id: 5,
	email: '[email protected]'
})))
//array
await DB(c).table('student_test').insert([
	{
		name: 'Jeff',
		age: 18,
		score: 76,
		class_id: 3,
		email: '[email protected]'
	}, {
		name: 'Jean',
		age: 24,
		score: 88,
		class_id: 3,
		email: '[email protected]'
	}])

//first return null|Student
await DB(c).table('student_test').where('id', 1).first<Student>()
//if table has a id column as primary key
await DB(c).table('student_test').find<Student>(1)

await DB(c).table('student_test').where('id', '>', 1).get<Student>()

await DB(c).table('student_test').where('name', 'John').value<string>('email')
// john@uc.edu

await DB(c).table('student_test').where('name', 'Je').value<string>('email')
//null

await DB(c).table('student_test').select(['name', 'email', 'id']).get<{ name: string, email: string, id: number }>()
await DB(c).table('student_test').get<{ name: string, email: string, id: number }>(['name', 'email', 'id'])

await DB(c).table('student_test').where('score', '>', 60).count()
//3
await DB(c).table('student_test').where('score', '>', 60).count('id')
//3
await DB(c).table('student_test').where('class_id', 3).avg('score')
//82
await DB(c).table('student_test').max('score')
//99

await DB(c).table('student_test').select(['name', new Expression('email as school_email')]).where('id', 2).first<{ name: string, school_email: string }>()
//result 
{
	name:"John",
	school_email:"[email protected]"
}

DB(c).query().select("*").where([["age", ">", 18], ["name", "Jay"]]).from("users")
//select * from "users" where ("age" > ? and "name" = ?)

DB(c).query().select("*").where([["age", ">", 18], { name: "Will", location: "LA" }]).from("users")
//select * from "users" where ("age" > ? and "name" = ? and "location" = ?)

DB(c).query().select("*").where("id", 1).orWhere("age", 2).from("users")
//select * from "users" where "id" = ? or "age" = ?

DB(c).query().select("*").whereNot("id", 1).whereNot("id", "<>", 2).from("users")
//select * from "users" where not "id" = ? and not "id" <> ?
DB(c).query().select("*").whereNot("id", 1).orWhereNot("age", "<>", 2).from("users")
//select * from "users" where not "id" = ? or not "age" <> ?

DB(c).query().select("*").whereBetween("id", [1, 2]).from("users");
//select * from "users" where "id" between ? and ?

DB(c).query().select("*").whereBetweenColumns("id", ["users.created_at", "users.updated_at"]).from("users");
//select * from "users" where "id" between "users"."created_at" and "users"."updated_at"

DB(c).query().select("*").whereIn("id", [1, 3, 7]).orWhereIn("age", [2, 4, 6]).from("users");
//select * from "users" where "id" in (?, ?, ?) or "age" in (?, ?, ?)

DB(c).query().select("*").whereNull("id").from("users");
//select * from "users" where "id" is null

DB(c).query().select("*").whereColumn("first", "last").orWhereColumn("third", "middle").from("users");
//select * from "users" where "first" = "last" or "third" = "middle"

DB(c).query().select("*").where('age', '>', 30).where((b: Builder) => {
	return b.where("email", "like", "%@gmail").orWhere("name", "Walter");
}).from("users");
//select * from "users" where "age" > ? and ("email" like ? or "name" = ?)

DB(c).query().select("*").limit(5).offset(6).from("users");
//select * from "users" limit 5 offset 6

DB(c).query().select("*").take(0).skip(-6).from("users");
//select * from "users"
DB(c).query().select("*").from("users").forPage(2, 15);
//select * from "users" limit 15 offset 15
DB(c).query().select("*").from("users").paginate<T>(2, 15);
//returns a Paginator<T>

DB(c).query().select("*").orderBy("age").orderBy("rank", "desc").from("users");
//select * from "users" order by "age" asc, "rank" desc
DB(c).query().select("*").orderByRaw(`"age" ? desc`, ["foo"]).from("users");
//select * from "users" order by "age" ? desc
DB(c).query().select("*").orderBy((b: Builder): Builder => {
	return b.select("created_at").from("logins").whereColumn("user_id", "users.id").limit(1);
}).from("users");
//select * from "users" order by (select "created_at" from "logins" where "user_id" = "users"."id" limit 1)

let query = DB(c).query();

query.from("users").where("id", 1).update({
	email: "foo",
	name: "bar"
});
//update "users" set "email" = ?, "name" = ? where "id" = ?`

let query = DB(c).query();
query.from("users").where("id", 1).update({
	email: "foo",
	name: "bar",
	size: new Expression("size+1000"),
	meta: JSON.stringify(
		{
			type: "video/mp4",
			batch: 4,
			parts: [{ id: 1, etag: "etag1" }, { id: 2, etag: "etag2" }],
			key: "key",
			id: "id"
		})
});
//update "users" set "email" = ?, "name" = ?, "size" = size+1000, "meta" = ? where "id" = ?`, ["foo", "bar", `{"type":"video/mp4","batch":4,"parts":[{"id":1,"etag":"etag1"},{"id":2,"etag":"etag2"}],"key":"key","id":"id"}`, 1]));

let query = DB(c).query();
query.from("users").insert({ email: "[email protected]", name: "test insert" });
//insert into "users" ("email", "name") values (?, ?)
let query = DB(c).query();
query.from("users").insert(new Map(Object.entries({ email: "[email protected]", name: "test insert" })));
//insert into "users" ("email", "name") values (?, ?)
let query = DB(c).query();
query.from("users").insert([{ email: "[email protected]", name: "test insert" }, {
	email: "[email protected]",
	name: "test multiple insert"
}]);
//insert into "users" ("email", "name") values (?, ?), (?, ?)
let query = DB(c).query();
query.from("users").insert([new Map(Object.entries({
	email: "[email protected]",
	name: "test insert"
})), new Map(Object.entries({ email: "[email protected]", name: "test multiple insert" }))]);
//insert into "users" ("email", "name") values (?, ?), (?, ?)

let query = db.query();
query.from("users").where("id", "<", 10).delete();
//delete from "users" where "id" < ?
let query = db.query();
query.from("users").where("id", "<", 10).delete(1);
//delete from "users" where "id" < ? and "users"."id" = ?

await DB(c).table('student_test').pretend().where('id','<',10).delete()
//this query will not execute,instead it returns an object
{
	"query": "delete from \"student_test\" where \"id\" < ?",
	"bindings": [
	  10
    ]
}