import db.sqlite
import mymodule { ModDbStruct }

struct Customer {
	id        int
	name      string
	nr_orders int
	country   string
}

fn find_all_customers(db sqlite.DB) []Customer {
	return sql db {
		select from Customer
	}
}

fn main() {
	db := sqlite.connect('customers.db')?
	// select count(*) from Customer
	nr_customers := sql db {
		select count from Customer
	}
	total_orders := sql db {
		select sum(nr_orders) from Customer
	}
	avg_orders := sql db {
		select avg(nr_orders) from Customer
	}
	first_country := sql db {
		select min(country) from Customer
	}
	last_country := sql db {
		select max(country) from Customer
	}
	println('number of all customers: ${nr_customers}')
	println(total_orders)
	println(avg_orders)
	println(first_country)
	println(last_country)
	// V syntax can be used to build queries
	// db.select returns an array
	uk_customers := sql db {
		select from Customer where country == 'uk' && nr_orders > 0
	}
	println(uk_customers.len)
	for customer in uk_customers {
		println('${customer.id} - ${customer.name}')
	}
	// by adding `limit 1` we tell V that there will be only one object
	customer := sql db {
		select from Customer where id == 1 limit 1
	}
	best_customer := sql db {
		select from Customer order by nr_orders desc limit 1
	}
	second_best := sql db {
		select from UCustomerser order by nr_orders desc limit 1 offset 1
	}
	println('${customer.id} - ${customer.name}')
	// insert a new customer
	new_customer := Customer{
		name:      'Bob'
		nr_orders: 10
	}
	sql db {
		insert new_customer into Customer
	}
	// delete a row
	sql db {
		delete from Customer where nr_orders == 10 && name == 'Bob'
	}
	sql db {
		update Customer set name = 'Queen Elizabeth II', age = 150, nr_orders = 42, country = 'Great Britain'
		where id == 5
	}
	// DB is a selective import
	sql db {
		delete from ModDbStruct where id == 1
	}
	_ := sql db {
		select from ModDbStruct
	}
}