// vtest retry: 3
// vtest build: present_sqlite3?
import db.sqlite

@[table: 'upsert_users']
struct UpsertUser {
mut:
    id       int    @[primary; sql: serial]
    username string @[unique]
    age      int
}

@[table: 'upsert_configs']
@[unique_key: 'scope, key']
struct UpsertConfig {
mut:
    id    int @[primary; sql: serial]
    scope string
    key   string
    value string
}

@[table: 'ambiguous_upsert_users']
struct AmbiguousUpsertUser {
mut:
    id       int    @[primary]
    username string @[unique]
    note     string
}

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

    sql db {
        create table UpsertUser
    }!

    first := UpsertUser{
        username: 'alice'
        age:      30
    }
    second := UpsertUser{
        username: 'alice'
        age:      31
    }

    sql db {
        upsert first into UpsertUser
        upsert second into UpsertUser
    }!

    rows := sql db {
        select from UpsertUser where username == 'alice'
    }!

    assert rows.len == 1
    assert rows[0].username == 'alice'
    assert rows[0].age == 31
    assert rows[0].id == 1
}

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

    sql db {
        create table UpsertConfig
    }!

    first := UpsertConfig{
        scope: 'web'
        key:   'theme'
        value: 'light'
    }
    second := UpsertConfig{
        scope: 'web'
        key:   'theme'
        value: 'dark'
    }

    sql db {
        upsert first into UpsertConfig
        upsert second into UpsertConfig
    }!

    rows := sql db {
        select from UpsertConfig where scope == 'web' && key == 'theme'
    }!

    assert rows.len == 1
    assert rows[0].value == 'dark'
    assert rows[0].id == 1
}

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

    sql db {
        create table AmbiguousUpsertUser
    }!

    first := AmbiguousUpsertUser{
        id:       1
        username: 'alice'
        note:     'first'
    }
    second := AmbiguousUpsertUser{
        id:       2
        username: 'bob'
        note:     'second'
    }

    sql db {
        insert first into AmbiguousUpsertUser
        insert second into AmbiguousUpsertUser
    }!

    ambiguous := AmbiguousUpsertUser{
        id:       1
        username: 'bob'
        note:     'updated'
    }

    mut got_error := false
    sql db {
        upsert ambiguous into AmbiguousUpsertUser
    } or { got_error = true }

    assert got_error

    rows := sql db {
        select from AmbiguousUpsertUser order by id
    }!
    assert rows.len == 2
    assert rows[0].note == 'first'
    assert rows[1].note == 'second'
}