// vtest retry: 3
// vtest build: present_sqlite3? && !windows && !sanitize-memory-clang
import db.sqlite

// Basic embedded struct
struct Coordinates {
	latitude  f64
	longitude f64
}

// Struct with embedded Coordinates
struct Location {
	Coordinates
	id   int @[primary; sql: serial]
	name string
}

// Another embedded struct with different types
struct Metadata {
	created_by string
	version    int
	is_active  bool
}

// Struct with multiple fields and embedded struct
struct Document {
	Metadata
	id      int @[primary; sql: serial]
	title   string
	content string
}

// Struct embedding another struct that has various field types
struct PersonInfo {
	first_name string
	last_name  string
	age        int
}

struct Employee {
	PersonInfo
	id         int @[primary; sql: serial]
	department string
	salary     f64
}

// Test basic embedded struct insert and select
fn test_embedded_struct_insert_and_select() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	loc := Location{
		name:      'Paris'
		latitude:  48.8566
		longitude: 2.3522
	}

	sql db {
		insert loc into Location
	}!

	locations := sql db {
		select from Location
	}!

	assert locations.len == 1
	fetched := locations.first()
	assert fetched.name == 'Paris'
	assert fetched.latitude == 48.8566
	assert fetched.longitude == 2.3522
}

// Test multiple inserts with embedded struct
fn test_embedded_struct_multiple_inserts() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	locations_to_insert := [
		Location{
			name:      'New York'
			latitude:  40.7128
			longitude: -74.0060
		},
		Location{
			name:      'Tokyo'
			latitude:  35.6762
			longitude: 139.6503
		},
		Location{
			name:      'Sydney'
			latitude:  -33.8688
			longitude: 151.2093
		},
	]

	for loc in locations_to_insert {
		sql db {
			insert loc into Location
		}!
	}

	locations := sql db {
		select from Location
	}!

	assert locations.len == 3

	// Verify each location
	assert locations[0].name == 'New York'
	assert locations[0].latitude == 40.7128
	assert locations[0].longitude == -74.0060

	assert locations[1].name == 'Tokyo'
	assert locations[1].latitude == 35.6762
	assert locations[1].longitude == 139.6503

	assert locations[2].name == 'Sydney'
	assert locations[2].latitude == -33.8688
	assert locations[2].longitude == 151.2093
}

// Test embedded struct with different field types (string, int, bool)
fn test_embedded_struct_various_types() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Document
	}!

	doc := Document{
		title:      'Test Document'
		content:    'This is the content'
		created_by: 'admin'
		version:    1
		is_active:  true
	}

	sql db {
		insert doc into Document
	}!

	docs := sql db {
		select from Document
	}!

	assert docs.len == 1
	fetched := docs.first()
	assert fetched.title == 'Test Document'
	assert fetched.content == 'This is the content'
	assert fetched.created_by == 'admin'
	assert fetched.version == 1
	assert fetched.is_active == true
}

// Test embedded struct with bool false value
fn test_embedded_struct_bool_false() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Document
	}!

	doc := Document{
		title:      'Inactive Doc'
		content:    'Content here'
		created_by: 'user'
		version:    2
		is_active:  false
	}

	sql db {
		insert doc into Document
	}!

	docs := sql db {
		select from Document
	}!

	assert docs.len == 1
	assert docs.first().is_active == false
}

// Test employee with person info embedded
fn test_employee_with_embedded_person_info() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Employee
	}!

	emp := Employee{
		first_name: 'John'
		last_name:  'Doe'
		age:        30
		department: 'Engineering'
		salary:     75000.50
	}

	sql db {
		insert emp into Employee
	}!

	employees := sql db {
		select from Employee
	}!

	assert employees.len == 1
	fetched := employees.first()
	assert fetched.first_name == 'John'
	assert fetched.last_name == 'Doe'
	assert fetched.age == 30
	assert fetched.department == 'Engineering'
	assert fetched.salary == 75000.50
}

// Test selecting specific records with where clause on non-embedded field
fn test_embedded_struct_select_with_where() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Employee
	}!

	employees_to_insert := [
		Employee{
			first_name: 'Alice'
			last_name:  'Smith'
			age:        25
			department: 'HR'
			salary:     50000.0
		},
		Employee{
			first_name: 'Bob'
			last_name:  'Jones'
			age:        35
			department: 'Engineering'
			salary:     80000.0
		},
		Employee{
			first_name: 'Carol'
			last_name:  'Williams'
			age:        28
			department: 'Engineering'
			salary:     70000.0
		},
	]

	for emp in employees_to_insert {
		sql db {
			insert emp into Employee
		}!
	}

	// Select by department
	engineers := sql db {
		select from Employee where department == 'Engineering'
	}!

	assert engineers.len == 2
	assert engineers[0].first_name == 'Bob'
	assert engineers[1].first_name == 'Carol'

	// Verify all embedded fields are correctly fetched
	for eng in engineers {
		assert eng.last_name.len > 0
		assert eng.age > 0
		assert eng.salary > 0
	}
}

// Test that embedded struct fields are stored with correct column names
fn test_embedded_struct_field_values_integrity() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	// Insert with specific values that could be confused
	loc := Location{
		name:      'Test'
		latitude:  12.345
		longitude: 67.890
	}

	sql db {
		insert loc into Location
	}!

	locations := sql db {
		select from Location
	}!

	fetched := locations.first()

	// Ensure latitude and longitude are not swapped
	assert fetched.latitude == 12.345
	assert fetched.longitude == 67.890
	// Ensure they're not equal (which would indicate a bug)
	assert fetched.latitude != fetched.longitude
}

// Test update on non-embedded fields preserves embedded field values
fn test_update_preserves_embedded_fields() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	loc := Location{
		name:      'Original'
		latitude:  10.0
		longitude: 20.0
	}

	sql db {
		insert loc into Location
	}!

	// Update only the name
	sql db {
		update Location set name = 'Updated' where id == 1
	}!

	locations := sql db {
		select from Location where id == 1
	}!

	fetched := locations.first()
	assert fetched.name == 'Updated'
	// Embedded fields should be preserved
	assert fetched.latitude == 10.0
	assert fetched.longitude == 20.0
}

// Test delete with embedded struct
fn test_delete_with_embedded_struct() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	loc1 := Location{
		name:      'ToDelete'
		latitude:  1.0
		longitude: 2.0
	}

	loc2 := Location{
		name:      'ToKeep'
		latitude:  3.0
		longitude: 4.0
	}

	sql db {
		insert loc1 into Location
		insert loc2 into Location
	}!

	sql db {
		delete from Location where name == 'ToDelete'
	}!

	locations := sql db {
		select from Location
	}!

	assert locations.len == 1
	assert locations.first().name == 'ToKeep'
	assert locations.first().latitude == 3.0
	assert locations.first().longitude == 4.0
}

// Test empty/zero values in embedded struct
fn test_embedded_struct_zero_values() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Employee
	}!

	emp := Employee{
		first_name: ''
		last_name:  ''
		age:        0
		department: 'Test'
		salary:     0.0
	}

	sql db {
		insert emp into Employee
	}!

	employees := sql db {
		select from Employee
	}!

	assert employees.len == 1
	fetched := employees.first()
	assert fetched.first_name == ''
	assert fetched.last_name == ''
	assert fetched.age == 0
	assert fetched.salary == 0.0
}

// Test that the struct equality works correctly with embedded fields
fn test_embedded_struct_equality() {
	db := sqlite.connect(':memory:')!

	sql db {
		create table Location
	}!

	original := Location{
		name:      'TestCity'
		latitude:  45.0
		longitude: 90.0
	}

	sql db {
		insert original into Location
	}!

	locations := sql db {
		select from Location
	}!

	fetched := locations.first()

	// Compare all fields individually
	assert fetched.name == original.name
	assert fetched.latitude == original.latitude
	assert fetched.longitude == original.longitude
}