// vtest retry: 2
import orm
import db.sqlite
import time

@[table: 'sys_users']
@[index: 'name, age']
struct User {
	id            int    @[primary; serial]
	name          string @[index]
	age           int
	role          string @[index]
	status        int    @[index]
	level         UserLevel
	salary        int
	title         string
	score         int
	created_at    ?time.Time @[sql_type: 'TIMESTAMP']
	updated_at    time.Time  @[sql_type: 'TIMESTAMP']
	type_i8       i8
	type_i16      i16
	type_int      int
	type_i64      i64
	type_u8       u8
	type_u16      u16
	type_u32      u32
	type_u64      u64
	type_f32      f32
	type_f64      f64
	type_bool     bool
	type_string   string
	option_i8     ?i8
	option_i16    ?i16
	option_int    ?int
	option_i64    ?i64
	option_u8     ?u8
	option_u16    ?u16
	option_u32    ?u32
	option_u64    ?u64
	option_f32    ?f32
	option_f64    ?f64
	option_bool   ?bool
	option_string ?string
}

enum UserLevel {
	beginner
	intermediate
	advanced
}

// UserPart is part of User, so we can access only part of the `sys_users` table
// note: for test, we modify `created_at` field from option to require
// a `null` value in database, will map to default value of the require field in struct
@[table: 'sys_users']
struct UserPart {
	id            int @[primary; serial]
	name          string
	created_at    time.Time @[sql_type: 'TIMESTAMP']
	updated_at    time.Time @[sql_type: 'TIMESTAMP']
	option_i8     ?i8     = 13 // option with default test
	option_string ?string = 'this is not none'
}

fn test_orm_func_where() {
	mut db := sqlite.connect(':memory:')!
	defer { db.close() or {} }
	mut qb := orm.new_query[User](db)

	// single_condition
	qb.reset()
	qb.where('age > ?', 25)!
	assert qb.where.fields == ['age']
	assert qb.where.kinds == [.gt]
	assert qb.where.data == [25]

	// chain_condition
	qb.reset()
	qb.where('age > ?', 25)!.where('salary < ?', 1000)!
	assert qb.where.fields == ['age', 'salary']
	assert qb.where.kinds == [.gt, .lt]
	assert qb.where.data == [25, 1000]

	// and_or_combination
	qb.reset()
	qb.where('name = ? AND status = ? OR role = ? || id = ? && title = ?', 'Alice', 1, 'admin', 1,
		'st')!
	assert qb.where.fields == ['name', 'status', 'role', 'id', 'title']
	assert qb.where.kinds == [.eq, .eq, .eq, .eq, .eq]
	assert qb.where.is_and == [true, false, false, true]

	// nested_parentheses
	qb.reset()
	qb.where('(salary >= ? AND (age <= ? OR title LIKE ?))', 50000, 35, '%Manager%')!
	assert qb.where.parentheses == [[1, 2], [0, 2]]

	// test lowercase `like`
	qb.reset()
	qb.where('(salary >= ? AND (age <= ? OR title like ?))', 50000, 35, '%Manager%')!
	assert qb.where.parentheses == [[1, 2], [0, 2]]
	assert qb.where.kinds == [.ge, .le, .orm_like]

	// complex_nesting
	qb.reset()
	qb.where('((age = ? OR (salary > ? AND id < ?)) AND (name LIKE ?))', 1, 2, 3, '%test%')!
	assert qb.where.parentheses == [[1, 2], [0, 2], [3, 3], [0, 3]]

	// in and not in
	qb.reset()
	qb.where('name IN ? AND age NOT IN ?', ['Tom'], [2])!
	assert qb.where.fields == ['name', 'age']
	assert qb.where.kinds == [.in, .not_in]

	// lowercase in and not in
	qb.reset()
	qb.where('name in ? AND age not in ?', ['Tom'], [2])!
	assert qb.where.fields == ['name', 'age']
	assert qb.where.kinds == [.in, .not_in]

	// variable arrays for in and not in
	names := ['Tom']
	ages := [2]
	qb.reset()
	qb.where('name IN ? AND age NOT IN ?', names, ages)!
	assert qb.where.fields == ['name', 'age']
	assert qb.where.kinds == [.in, .not_in]
	assert qb.where.data[0] is []orm.Primitive
	assert qb.where.data[1] is []orm.Primitive
	name_params := qb.where.data[0] as []orm.Primitive
	age_params := qb.where.data[1] as []orm.Primitive
	assert name_params == [orm.Primitive('Tom')]
	assert age_params == [orm.Primitive(2)]
}

fn test_orm_func_stmts() {
	users := [
		User{
			name:          'Tom'
			age:           30
			role:          'admin'
			status:        1
			salary:        5000
			title:         'manager'
			score:         90
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'Alice'
			age:           20
			role:          'employee'
			status:        2
			salary:        2000
			title:         'doctor'
			score:         95
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'Mars'
			age:           40
			role:          'employer'
			status:        3
			salary:        1000
			title:         'doctor'
			score:         85
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'Kitty'
			age:           18
			role:          'employer'
			status:        1
			salary:        1500
			title:         'doctor'
			score:         87
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:        'Silly'
			age:         27
			role:        'employer'
			status:      5
			level:       .intermediate
			salary:      2500
			title:       'doctor'
			score:       81
			updated_at:  time.now()
			type_i8:     1
			type_i16:    2
			type_int:    3
			type_i64:    4
			type_u8:     5
			type_u16:    6
			type_u32:    7
			type_u64:    8
			type_f32:    1.1
			type_f64:    2.2
			type_bool:   true
			type_string: 'hello'
			option_i8:   1
			option_i16:  2
			option_int:  3
			option_i64:  4
			option_u8:   5
			option_u16:  6
			option_u32:  7
			option_u64:  8
			option_f32:  1.1
			option_f64:  2.2
			option_bool: true
			// option_string: 'hello'	// option with default test
		},
		User{
			name:          'Smith'
			age:           37
			role:          'employer'
			status:        1
			salary:        4500
			title:         'doctor'
			score:         89
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'Bob'
			age:           26
			role:          'employer'
			status:        2
			salary:        6500
			title:         'doctor'
			score:         81
			created_at:    time.now()
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:        'Peter'
			age:         29
			role:        'employer'
			status:      1
			salary:      3500
			title:       'doctor'
			score:       80
			created_at:  time.now()
			updated_at:  time.now()
			type_i8:     1
			type_i16:    2
			type_int:    3
			type_i64:    4
			type_u8:     5
			type_u16:    6
			type_u32:    7
			type_u64:    8
			type_f32:    1.1
			type_f64:    2.2
			type_bool:   true
			type_string: 'hello'
			// option_i8:     1	// option with default test
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'See'
			age:           45
			role:          'employer'
			status:        2
			salary:        8500
			title:         'doctor'
			score:         82
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
		User{
			name:          'John'
			age:           42
			role:          'employer'
			status:        1
			salary:        10000
			title:         'doctor'
			score:         88
			updated_at:    time.now()
			type_i8:       1
			type_i16:      2
			type_int:      3
			type_i64:      4
			type_u8:       5
			type_u16:      6
			type_u32:      7
			type_u64:      8
			type_f32:      1.1
			type_f64:      2.2
			type_bool:     true
			type_string:   'hello'
			option_i8:     1
			option_i16:    2
			option_int:    3
			option_i64:    4
			option_u8:     5
			option_u16:    6
			option_u32:    7
			option_u64:    8
			option_f32:    1.1
			option_f64:    2.2
			option_bool:   true
			option_string: 'hello'
		},
	]
	mut db := sqlite.connect(':memory:')!
	defer { db.close() or {} }
	mut qb := orm.new_query[User](db)

	// create table
	qb.create()!

	// insert many records
	qb.insert_many(users)!

	// select count(*)
	mut count := qb.count()!

	// last_id
	mut last_id := qb.last_id()
	assert count == last_id
	assert count == users.len

	// insert a single record
	qb.insert(users[0])!

	// select * from table
	all_users := qb.query()!
	assert all_users.len == users.len + 1

	// select `name` from table
	only_names := qb.select('name')!.query()!
	assert only_names[0].name != ''
	assert only_names[0].id == 0
	assert only_names[0].age == 0
	assert only_names[0].role == ''
	assert only_names[0].status == 0
	assert only_names[0].salary == 0
	assert only_names[0].title == ''
	assert only_names[0].score == 0
	assert only_names[0].created_at == none

	// select distinct `role` from table
	distinct_roles := qb.select('role')!.distinct()!.order(.asc, 'role')!.query()!
	assert distinct_roles.len == 3
	assert distinct_roles.map(it.role) == ['admin', 'employee', 'employer']
	assert distinct_roles[0].id == 0
	assert distinct_roles[0].name == ''

	// update with single `set()`
	qb.set('age = ?, title = ?', 71, 'boss')!.where('name = ?', 'John')!.update()!
	john := qb.where('name = ?', 'John')!.query()!
	assert john[0].name == 'John'
	assert john[0].age == 71
	assert john[0].title == 'boss'

	// update with multiple `set()`

	qb.set('age = ?', 51)!
		.set('title = ?', 'employee')!
		.where('name = ?', 'John')!.update()!
	john2 := qb.where('name = ?', 'John')!.query()!
	assert john2[0].name == 'John'
	assert john2[0].age == 51
	assert john2[0].title == 'employee'

	// delete
	qb.where('name = ?', 'John')!.delete()!
	no_john := qb.where('name = ?', 'John')!.query()!
	assert no_john.len == 0

	// complex select
	selected_users := qb.where('created_at IS NULL && ((salary > ? && age < ?) || (role LIKE ?))',
		2000, 30, '%employee%')!.query()!
	assert selected_users[0].name == 'Silly'
	// Check enum
	assert selected_users[0].level == .intermediate
	assert selected_users.len == 1

	// complex select with lowercase `is null` and `like`
	selected_users1 := qb.where('created_at is null && ((salary > ? && age < ?) || (role like ?))',
		2000, 30, '%employee%')!.query()!
	assert selected_users1[0].name == 'Silly'
	assert selected_users1.len == 1

	// chain where
	and_where := qb.where('salary > ?', 2000)!.where('age > ?', 40)!.query()!
	assert and_where.len == 1
	or_where := qb.where('salary > ?', 2000)!.or_where('age > ? OR score > ?', 40, 85)!.query()!
	assert or_where.len == 9

	// chain calls
	final_users := qb
		.drop()!
		.create()!
		.insert_many(users)!
		.set('name = ?', 'haha')!.where('name = ?', 'Tom')!.update()!
		.where('age >= ?', 30)!.delete()!
		.order(.asc, 'age')!
		.limit(100)!
		.query()!
	assert final_users.len == 5
	assert final_users[0].age == 18

	// access only part of the table
	mut part := orm.new_query[UserPart](db)
	part_user := part.query()!
	// a `null` value in database, will map to default value of the require field in struct
	assert part_user.filter(it.name == 'Silly')[0].created_at == time.Time{}
	assert part_user.len == 5
}

struct Person {
	age_f32 f32
	age_f64 f64
}

fn test_orm_func_f32_f64() {
	p := Person{
		age_f32: 10.33
		age_f64: 10.343
	}

	db := sqlite.connect(':memory:')!

	mut qb := orm.new_query[Person](db)

	data := qb
		.create()!
		.insert(p)!
		.query()!
		.first()

	assert data.age_f32 == p.age_f32
	assert data.age_f64 == p.age_f64
}

@[index: 'age_f33, age_f64']
struct InvalidIndexFieldName1 {
	age_f32 f32
	age_f64 f64
}

fn test_orm_func_invalid_index_field_name1() {
	p := InvalidIndexFieldName1{
		age_f32: 10.33
		age_f64: 10.343
	}

	db := sqlite.connect(':memory:')!

	mut qb := orm.new_query[InvalidIndexFieldName1](db)

	qb.create() or {
		assert err.msg() == "table `invalidindexfieldname1` has no field's name: `age_f33`"
		return
	}
	assert false, 'should not be here'
}

@[index: 'age_f32, age_f64']
struct InvalidIndexFieldName2 {
	age_f32 f32 @[sql: abc]
	age_f64 f64
}

fn test_orm_func_invalid_index_field_name2() {
	p := InvalidIndexFieldName2{
		age_f32: 10.33
		age_f64: 10.343
	}

	db := sqlite.connect(':memory:')!

	mut qb := orm.new_query[InvalidIndexFieldName2](db)

	qb.create() or {
		assert err.msg() == "table `invalidindexfieldname2` has no field's name: `age_f32`"
		return
	}
	assert false, 'should not be here'
}

fn test_orm_func_update_many() {
	mut db := sqlite.connect(':memory:')!
	defer { db.close() or {} }
	mut qb := orm.new_query[User](db)

	qb.create()!

	// Insert test records
	users := [
		User{
			name: 'Alice'
			age:  25
			role: 'developer'
		},
		User{
			name: 'Bob'
			age:  30
			role: 'manager'
		},
		User{
			name: 'Carol'
			age:  35
			role: 'designer'
		},
	]
	qb.insert_many(users)!

	// Verify initial data
	all_users := qb.query()!
	assert all_users.len == 3
	assert all_users[0].name == 'Alice'
	assert all_users[1].name == 'Bob'
	assert all_users[2].name == 'Carol'

	// Batch update names by id
	orm.update_many[User](mut db, [
		User{
			id:   1
			name: 'Alice_updated'
			age:  26
		},
		User{
			id:   2
			name: 'Bob_updated'
			age:  31
		},
	], 'id', 'name', 'age')!

	// Verify updated data
	updated_users := qb.query()!
	assert updated_users.len == 3
	for u in updated_users {
		match u.id {
			1 {
				assert u.name == 'Alice_updated'
				assert u.age == 26
				assert u.role == 'developer'
			}
			2 {
				assert u.name == 'Bob_updated'
				assert u.age == 31
				assert u.role == 'manager'
			}
			3 {
				assert u.name == 'Carol'
				assert u.age == 35
				assert u.role == 'designer'
			}
			else {
				assert false
			}
		}
	}

	// Test update_many with single record
	orm.update_many[User](mut db, [
		User{
			id:   3
			name: 'Carol_updated'
			age:  36
		},
	], 'id', 'name', 'age')!

	single_result := qb.where('id = ?', 3)!.query()!
	assert single_result.len == 1
	assert single_result[0].name == 'Carol_updated'
	assert single_result[0].age == 36
	assert single_result[0].role == 'designer'

	// Test update_many with empty values
	orm.update_many[User](mut db, []User{}, 'id', 'name') or {
		assert err.msg().contains('need at least one record')
		return
	}
	assert false, 'should not be here'
}