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

struct AggregateEntry {
    id      int @[primary; sql: serial]
    age     int
    score   f64
    label   string
    created time.Time
}

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

    sql db {
        create table AggregateEntry
    }!

    first_created := time.unix(1_700_000_000)
    second_created := time.unix(1_700_000_100)
    third_created := time.unix(1_700_000_200)
    entries := [
        AggregateEntry{
            age:     20
            score:   7.5
            label:   'bravo'
            created: second_created
        },
        AggregateEntry{
            age:     30
            score:   8.25
            label:   'alpha'
            created: first_created
        },
        AggregateEntry{
            age:     40
            score:   9.75
            label:   'charlie'
            created: third_created
        },
    ]

    for entry in entries {
        sql db {
            insert entry into AggregateEntry
        }!
    }

    total_age := sql db {
        select sum(age) from AggregateEntry
    }!
    if value := total_age {
        assert value == 90
    } else {
        assert false
    }

    average_age := sql db {
        select avg(age) from AggregateEntry where age >= 20
    }!
    if value := average_age {
        assert value == 30.0
    } else {
        assert false
    }

    min_label := sql db {
        select min(label) from AggregateEntry
    }!
    if value := min_label {
        assert value == 'alpha'
    } else {
        assert false
    }

    max_created := sql db {
        select max(created) from AggregateEntry
    }!
    if value := max_created {
        assert value == third_created
    } else {
        assert false
    }

    empty_sum := sql db {
        select sum(age) from AggregateEntry where age > 100
    }!
    assert empty_sum == none

    empty_avg := sql db {
        select avg(score) from AggregateEntry where age > 100
    }!
    assert empty_avg == none
}

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

    mut qb := orm.new_query[AggregateEntry](db)
    qb.create()!

    first_created := time.unix(1_800_000_000)
    second_created := time.unix(1_800_000_100)
    entries := [
        AggregateEntry{
            age:     10
            score:   1.5
            label:   'delta'
            created: second_created
        },
        AggregateEntry{
            age:     25
            score:   2.5
            label:   'beta'
            created: first_created
        },
    ]

    qb.insert_many(entries)!

    assert qb.count()! == 2

    sum_age := qb.sum('age')!
    assert sum_age.has_value
    assert sum_age.as_int()? == 35

    avg_score := qb.avg('score')!
    assert avg_score.has_value
    assert avg_score.as_f64()? == 2.0

    min_label := qb.min('label')!
    assert min_label.has_value
    assert min_label.as_string()? == 'beta'

    max_created := qb.max('created')!
    assert max_created.has_value
    assert max_created.as_time()? == second_created

    empty_max := qb.where('age > ?', 100)!.max('age')!
    assert !empty_max.has_value

    if _ := qb.sum('label') {
        assert false
    } else {
        assert err.msg().contains('requires a numeric field')
    }
}