// vtest retry: 3
// vtest build: present_sqlite3? && !windows
// import db.mysql
// import db.pg
import orm
import time
import db.sqlite

const offset_const = 2

@[index: 'name, nr_downloads']
struct Module {
    id           int    @[primary; sql: serial]
    name         string @[index]
    nr_downloads int
    test_id      u64 @[index]
    user         ?User
    created      time.Time
}

@[table: 'userlist']
struct User {
    id              int @[primary; sql: serial]
    age             int
    name            string @[default: 'name'; sql: 'username']
    is_customer     bool
    skipped_string  string   @[skip]
    skipped_string2 string   @[sql: '-']
    skipped_array   []string @[skip]
    skipped_array2  []string @[sql: '-']
}

struct Foo {
    age int
}

struct TestTime {
    id     int @[primary; sql: serial]
    create time.Time
}

struct SelectTransformUser {
    id   int    @[primary; sql: serial]
    name string @[sql_select: 'upper(name)']
}

fn test_use_struct_field_as_limit() {
    db := sqlite.connect(':memory:') or { panic(err) }

    sql db {
        create table User
    }!

    foo := Foo{
        age: 10
    }

    sam := User{
        age:             29
        name:            'Sam'
        skipped_string2: 'this should be ignored'
        skipped_array:   ['ignored', 'array']
        skipped_array2:  ['another', 'ignored', 'array']
    }

    sql db {
        insert sam into User
    }!

    users := sql db {
        select from User limit foo.age
    }!

    assert users.len == 1
    assert users[0].name == 'Sam'
    assert users[0].age == 29
    assert users[0].skipped_string == ''
    assert users[0].skipped_string2 == ''
    assert users[0].skipped_array == [], 'skipped because of the @[skip] tag, used for both sql and json'
    assert users[0].skipped_array2 == [], "should be skipped, because of the sql specific @[sql: '-'] tag"
}

fn test_orm_sql_select_attribute() {
    mut db := sqlite.connect(':memory:') or { panic(err) }
    defer {
        db.close() or {}
    }

    sql db {
        create table SelectTransformUser
    }!

    alice := SelectTransformUser{
        name: 'Alice'
    }

    sql db {
        insert alice into SelectTransformUser
    }!

    rows := sql db {
        select from SelectTransformUser where id == 1
    }!

    assert rows.len == 1
    assert rows[0].name == 'ALICE'

    mut qb := orm.new_query[SelectTransformUser](db)
    qb_rows := qb.query()!

    assert qb_rows.len == 1
    assert qb_rows[0].name == 'ALICE'
}

fn test_orm_select_specific_fields() {
    mut db := sqlite.connect(':memory:') or { panic(err) }
    defer {
        db.close() or {}
    }

    sql db {
        create table User
        create table SelectTransformUser
    }!

    sam := User{
        age:  29
        name: 'Sam'
    }
    alice := SelectTransformUser{
        name: 'Alice'
    }

    sql db {
        insert sam into User
        insert alice into SelectTransformUser
    }!

    users := sql db {
        select id, name from User where name == 'Sam'
    }!

    assert users.len == 1
    assert users[0].id == 1
    assert users[0].name == 'Sam'
    assert users[0].age == 0
    assert users[0].is_customer == false

    transformed := sql db {
        select name from SelectTransformUser where id == 1
    }!

    assert transformed.len == 1
    assert transformed[0].name == 'ALICE'
    assert transformed[0].id == 0
}

fn test_orm_order_by_explicit_asc() {
    mut db := sqlite.connect(':memory:')!
    defer {
        db.close() or { panic(err) }
    }

    sql db {
        create table User
    }!

    users := [
        User{
            age:  31
            name: 'Alice'
        },
        User{
            age:  19
            name: 'Bob'
        },
        User{
            age:  44
            name: 'Charlie'
        },
    ]

    for user in users {
        sql db {
            insert user into User
        }!
    }

    // vfmt off
    rows := sql db {
        select from User order by age asc limit 2
    }!
    // vfmt on

    assert rows.len == 2
    assert rows[0].name == 'Bob'
    assert rows[0].age == 19
    assert rows[1].name == 'Alice'
    assert rows[1].age == 31
}

fn test_orm() {
    db := sqlite.connect(':memory:') or { panic(err) }

    sql db {
        create table Module
    }!
    sql db {
        create table User
    }!

    name := 'Peter'

    sam := User{
        age:  29
        name: 'Sam'
    }

    peter := User{
        age:  31
        name: 'Peter'
    }

    k := User{
        age:         30
        name:        'Kate'
        is_customer: true
    }

    sql db {
        insert sam into User
        insert peter into User
        insert k into User
    }!

    c := sql db {
        select count from User where id != 1
    }!
    assert c == 2

    nr_all_users := sql db {
        select count from User
    }!
    assert nr_all_users == 3

    nr_users1 := sql db {
        select count from User where id == 1
    }!
    assert nr_users1 == 1

    nr_peters := sql db {
        select count from User where id == 2 && name == 'Peter'
    }!
    assert nr_peters == 1

    nr_peters2 := sql db {
        select count from User where id == 2 && name == name
    }!
    assert nr_peters2 == 1

    nr_peters3 := sql db {
        select count from User where name == name
    }!
    assert nr_peters3 == 1

    peters := sql db {
        select from User where name == name
    }!
    assert peters.len == 1
    assert peters[0].name == 'Peter'

    mut users := sql db {
        select from User where name == name limit 1
    }!

    one_peter := users.first()
    assert one_peter.name == 'Peter'
    assert one_peter.id == 2

    users = sql db {
        select from User where id == 1
    }!

    user := users.first()
    assert user.name == 'Sam'
    assert user.id == 1
    assert user.age == 29

    users = sql db {
        select from User where id > 0
    }!
    assert users.len == 3
    assert users[0].name == 'Sam'
    assert users[1].name == 'Peter'
    assert users[1].age == 31

    users2 := sql db {
        select from User where id < 0
    }!
    assert users2.len == 0

    users3 := sql db {
        select from User where age == 29 || age == 31
    }!

    assert users3.len == 2
    assert users3[0].age == 29
    assert users3[1].age == 31

    new_user := User{
        name: 'New user'
        age:  30
    }
    sql db {
        insert new_user into User
    }!

    users = sql db {
        select from User where id == 4
    }!

    x := users.first()
    assert x.age == 30
    assert x.id == 4
    assert x.name == 'New user'

    users = sql db {
        select from User where id == 3
    }!

    kate := users.first()
    assert kate.is_customer == true

    users = sql db {
        select from User where is_customer == true limit 1
    }!

    customer := users.first()
    assert customer.is_customer == true
    assert customer.name == 'Kate'

    sql db {
        update User set age = 31 where name == 'Kate'
    }!

    users = sql db {
        select from User where id == 3
    }!
    kate2 := users.first()
    assert kate2.age == 31
    assert kate2.name == 'Kate'

    sql db {
        update User set age = 32, name = 'Kate N' where name == 'Kate'
    }!

    users = sql db {
        select from User where id == 3
    }!
    mut kate3 := users.first()
    assert kate3.age == 32
    assert kate3.name == 'Kate N'

    new_age := 33
    sql db {
        update User set age = new_age, name = 'Kate N' where id == 3
    }!

    users = sql db {
        select from User where id == 3
    }!

    kate3 = users.first()
    assert kate3.age == 33
    assert kate3.name == 'Kate N'

    foo := Foo{34}
    sql db {
        update User set age = foo.age, name = 'Kate N' where id == 3
    }!

    users = sql db {
        select from User where id == 3
    }!
    kate3 = users.first()
    assert kate3.age == 34
    assert kate3.name == 'Kate N'

    no_user := sql db {
        select from User where id == 30
    }!

    assert no_user.len == 0

    two_users := sql db {
        select from User limit 2
    }!
    assert two_users.len == 2
    assert two_users[0].id == 1

    y := sql db {
        select from User limit 2 offset 1
    }!
    assert y.len == 2
    assert y[0].id == 2

    z := sql db {
        select from User order by id limit 2 offset offset_const
    }!
    assert z.len == 2
    assert z[0].id == 3

    users = sql db {
        select from User order by age desc limit 1
    }!

    oldest := users.first()
    assert oldest.age == 34

    offs := 1
    users = sql db {
        select from User order by age desc limit 1 offset offs
    }!

    second_oldest := users.first()
    assert second_oldest.age == 31
    sql db {
        delete from User where age == 34
    }!

    users = sql db {
        select from User order by age desc limit 1
    }!
    updated_oldest := users.first()
    assert updated_oldest.age == 31

    // Remove this when pg is used
    // db.exec('insert into User (name, age) values (NULL, 31)')
    users = sql db {
        select from User where id == 5
    }!
    assert users.len == 0

    users = sql db {
        select from User where id == 1
    }!
    age_test := users.first()

    assert age_test.age == 29

    sql db {
        update User set age = age + 1 where id == 1
    }!

    users = sql db {
        select from User where id == 1
    }!

    mut first := users.first()
    assert first.age == 30

    sql db {
        update User set age = age * 2 where id == 1
    }!

    users = sql db {
        select from User where id == 1
    }!

    first = users.first()
    assert first.age == 60

    sql db {
        create table TestTime
    }!

    tnow := time.now()

    time_test := TestTime{
        create: tnow
    }

    sql db {
        insert time_test into TestTime
    }!

    data := sql db {
        select from TestTime where create == tnow
    }!

    assert data.len == 1
    assert tnow.unix() == data[0].create.unix()

    mod := Module{}

    sql db {
        insert mod into Module
    }!

    sql db {
        update Module set test_id = 11 where id == 1
    }!

    mut modules := sql db {
        select from Module where id == 1
    }!

    assert modules.first().test_id == 11

    t := time.now()
    sql db {
        update Module set created = t where id == 1
    }!

    modules = sql db {
        select from Module where id == 1
    }!

    // Note: usually updated_time_mod.created != t, because t has
    // its microseconds set, while the value retrieved from the DB
    // has them zeroed, because the db field resolution is seconds.
    // Note: the database also stores the time in UTC, so the
    // comparison must be done on the unix timestamp.
    assert modules.first().created.unix() == t.unix()

    users = sql db {
        select from User where (name == 'Sam' && is_customer == true) || id == 1
    }!

    assert users.first() == first

    sql db {
        drop table Module
        drop table TestTime
    }!
}

fn test_distinct() {
    db := sqlite.connect(':memory:') or { panic(err) }

    // Create table without unique constraints to allow true duplicates
    db.exec('CREATE TABLE items (name TEXT, category TEXT)')!
    db.exec("INSERT INTO items VALUES ('Apple', 'Fruit'), ('Apple', 'Fruit'), ('Banana', 'Fruit')")!

    without_distinct := db.exec('SELECT name, category FROM items')!
    assert without_distinct.len == 3

    with_distinct := db.exec('SELECT DISTINCT name, category FROM items')!
    assert with_distinct.len == 2
}