module orm

import time
import strings.textscanner

const operators = ['=', '!=', '<>', '>=', '<=', '>', '<', 'LIKE', 'ILIKE', 'IS NULL', 'IS NOT NULL',
    'IN', 'NOT IN']!

pub struct AggregateValue {
pub:
    has_value bool
    value     Primitive = Null{}
}

@[heap]
pub struct QueryBuilder[T] {
pub mut:
    meta                  []TableField
    valid_sql_field_names []string
    conn                  Connection
    config                SelectConfig
    data                  QueryData
    where                 QueryData
}

// new_query create a new query object for struct `T`
pub fn new_query[T](conn Connection) &QueryBuilder[T] {
    meta := struct_meta[T]()
    return &QueryBuilder[T]{
        meta:                  meta
        valid_sql_field_names: meta.map(sql_field_name(it))
        conn:                  conn
        config:                SelectConfig{
            table: table_from_struct[T]()
        }
        data:                  QueryData{}
        where:                 QueryData{}
    }
}

// reset reset a query object, but keep the connection and table name
pub fn (qb_ &QueryBuilder[T]) reset() &QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    old_table := qb.config.table
    qb.config = SelectConfig{
        table: old_table
    }
    qb.data = QueryData{}
    qb.where = QueryData{}
    return qb
}

// where create a `where` clause, it will `AND` with previous `where` clause.
// valid token in the `condition` include: `field's names`, `operator`, `(`, `)`, `?`, `AND`, `OR`, `||`, `&&`,
// valid `operator` incldue: `=`, `!=`, `<>`, `>=`, `<=`, `>`, `<`, `LIKE`, `ILIKE`, `IS NULL`, `IS NOT NULL`, `IN`, `NOT IN`
// example: `where('(a > ? AND b <= ?) OR (c <> ? AND (x = ? OR y = ?))', a, b, c, x, y)`
pub fn (qb_ &QueryBuilder[T]) where(condition string, params ...Primitive) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if qb.where.fields.len > 0 {
        // skip first field
        qb.where.is_and << true // and
    }
    qb.parse_conditions(condition, normalize_primitive_arguments(params))!
    qb.config.has_where = true
    return qb
}

// or_where create a `where` clause, it will `OR` with previous `where` clause.
pub fn (qb_ &QueryBuilder[T]) or_where(condition string, params ...Primitive) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if qb.where.fields.len > 0 {
        // skip first field
        qb.where.is_and << false // or
    }
    qb.parse_conditions(condition, normalize_primitive_arguments(params))!
    qb.config.has_where = true
    return qb
}

fn normalize_primitive_arguments(params []Primitive) []Primitive {
    mut normalized := []Primitive{cap: params.len}
    for param in params {
        normalized << primitive_value(param)
    }
    return normalized
}

fn primitive_value(value Primitive) Primitive {
    return match value {
        []bool { primitive_array(value) }
        []f32 { primitive_array(value) }
        []f64 { primitive_array(value) }
        []i16 { primitive_array(value) }
        []i64 { primitive_array(value) }
        []i8 { primitive_array(value) }
        []int { primitive_array(value) }
        []string { primitive_array(value) }
        []time.Time { primitive_array(value) }
        []u16 { primitive_array(value) }
        []u32 { primitive_array(value) }
        []u64 { primitive_array(value) }
        []u8 { primitive_array(value) }
        []InfixType { primitive_array(value) }
        else { value }
    }
}

fn primitive_array[T](values []T) []Primitive {
    mut out := []Primitive{cap: values.len}
    for value in values {
        out << Primitive(value)
    }
    return out
}

fn parse_error(msg string, pos int, conds string) ! {
    mut m := msg + '\n' + '\t' + conds + '\n\t' + ' '.repeat(pos) + '^\n'
    return error(m)
}

enum ParserState {
    field
    op
    qm
}

struct MyTextScanner {
    textscanner.TextScanner
mut:
    last_tok_start int
}

// next_tok get next token from scanner, skip whitespace
fn (mut ss MyTextScanner) next_tok() string {
    mut ret := ''
    ss.skip_whitespace()
    ss.last_tok_start = ss.pos
    ss_upper := ss.input.to_upper()

    // check for longest token first
    if ss_upper[ss.pos..].starts_with('IS NOT NULL') {
        ss.pos += 11
        return 'IS NOT NULL'
    }
    if ss_upper[ss.pos..].starts_with('IS NULL') {
        ss.pos += 7
        return 'IS NULL'
    }
    if ss_upper[ss.pos..].starts_with('NOT IN') {
        ss.pos += 6
        return 'NOT IN'
    }
    if ss.remaining() >= 2 {
        two_chars := ss.input[ss.pos..ss.pos + 2]
        if two_chars in ['>=', '<=', '<>', '!=', '||', '&&', 'IN'] {
            ss.pos += 2
            return two_chars
        }
    }
    if ss.remaining() > 0 {
        c := ss.input[ss.pos]
        if c in [`>`, `<`, `=`] {
            ss.pos++
            return c.ascii_str()
        }
    }
    for ss.remaining() > 0 {
        c := u8(ss.next()) // only support ascii now
        if c.is_alnum() || c == `_` || c == `$` {
            ret += c.ascii_str()
        } else {
            if ret.len == 0 {
                ret = c.ascii_str()
            } else {
                // already contain a tok
                ss.back()
            }
            break
        }
    }
    return ret
}

// parse_conditions update `qb` by parsing the `conds` string
fn (qb_ &QueryBuilder[T]) parse_conditions(conds string, params []Primitive) ! {
    // conditions: '(a > ? AND b <= ?) OR (c <> ? AND (x = ? OR y = ?))'
    mut qb := unsafe { qb_ }
    if conds.len == 0 {
        return error('${@FN}(): empty condition')
    }
    required_params := conds.count('?')
    if required_params != params.len {
        parse_error('${@FN}(): condition requires `${required_params}` params but got `${params.len}`',
            0, conds)!
    }

    mut s := MyTextScanner{
        input: conds
        ilen:  conds.len
    }

    mut state := ParserState.field
    mut tok := ''
    mut current_field := ''
    mut current_op := OperationKind.eq
    mut current_is_and := true
    mut i := 0
    mut paren_stack := []int{}
    mut is_first_field := true
    for s.remaining() > 0 {
        tok = s.next_tok()
        match state {
            .field {
                // only support valid field names
                if tok in qb.valid_sql_field_names {
                    current_field = tok
                    state = .op
                } else if tok == '(' {
                    paren_stack << qb.where.fields.len
                } else if tok == ')' {
                    if paren_stack.len == 0 {
                        parse_error('${@FN}: unexpected `)`', s.last_tok_start, conds)!
                    }
                    start_pos := paren_stack.pop()
                    qb.where.parentheses << [start_pos, qb.where.fields.len - 1]
                } else {
                    parse_error("${@FN}: table `${qb.config.table}` has no field's name: `${tok}`",
                        s.last_tok_start, conds)!
                }
            }
            .op {
                current_op = match tok.to_upper() {
                    '=' {
                        OperationKind.eq
                    }
                    '<>' {
                        OperationKind.neq
                    }
                    '!=' {
                        OperationKind.neq
                    }
                    '>' {
                        OperationKind.gt
                    }
                    '<' {
                        OperationKind.lt
                    }
                    '>=' {
                        OperationKind.ge
                    }
                    '<=' {
                        OperationKind.le
                    }
                    'LIKE' {
                        OperationKind.orm_like
                    }
                    'ILIKE' {
                        OperationKind.orm_ilike
                    }
                    'IS NULL' {
                        OperationKind.is_null
                    }
                    'IS NOT NULL' {
                        OperationKind.is_not_null
                    }
                    'IN' {
                        OperationKind.in
                    }
                    'NOT IN' {
                        OperationKind.not_in
                    }
                    else {
                        parse_error('${@FN}(): unsupported operator: `${tok}`', s.last_tok_start,
                            conds)!
                        OperationKind.eq
                    }
                }

                if current_op in [.is_null, .is_not_null]! {
                    qb.where.fields << current_field
                    qb.where.kinds << current_op
                    if is_first_field {
                        is_first_field = false
                    } else {
                        // skip first field
                        qb.where.is_and << current_is_and
                    }
                }
                state = .qm
            }
            .qm {
                if tok == '?' {
                    // finish an expr, update `qb`
                    qb.where.fields << current_field
                    qb.where.data << params[i]
                    qb.where.kinds << current_op
                    if is_first_field {
                        is_first_field = false
                    } else {
                        // skip first field
                        qb.where.is_and << current_is_and
                    }
                    i++
                } else if tok == ')' {
                    if paren_stack.len == 0 {
                        parse_error('${@FN}: unexpected `)`', s.last_tok_start, conds)!
                    }
                    start_pos := paren_stack.pop()
                    qb.where.parentheses << [start_pos, qb.where.fields.len - 1]
                } else if tok == 'AND' {
                    current_is_and = true
                    state = .field
                } else if tok == 'OR' {
                    current_is_and = false
                    state = .field
                } else if tok == '&&' {
                    current_is_and = true
                    state = .field
                } else if tok == '||' {
                    current_is_and = false
                    state = .field
                } else {
                    parse_error('${@FN}(): unexpected `${tok}`, maybe `AND`,`OR`',
                        s.last_tok_start, conds)!
                }
            }
        }
    }
}

// order create a `order` clause
pub fn (qb_ &QueryBuilder[T]) order(order_type OrderType, field string) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if field in qb.valid_sql_field_names {
        qb.config.has_order = true
        qb.config.order = field
        qb.config.order_type = order_type
    } else {
        return error("${@FN}(): table `${qb.config.table}` has no field's name: `${field}`")
    }
    return qb
}

// limit create a `limit` clause
pub fn (qb_ &QueryBuilder[T]) limit(limit int) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if limit > 0 {
        qb.config.has_limit = true
        qb.data.data << Primitive(limit)
        qb.data.types << type_idx['int']
    } else {
        return error('${@FN}(): `limit` should be a positive integer')
    }
    return qb
}

// offset create a `offset` clause
pub fn (qb_ &QueryBuilder[T]) offset(offset int) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if offset >= 0 {
        qb.config.has_offset = true
        qb.data.data << Primitive(offset)
        qb.data.types << type_idx['int']
    } else {
        return error('${@FN}(): `offset` should be a integer > 0')
    }
    return qb
}

// select create a `select` clause
pub fn (qb_ &QueryBuilder[T]) select(fields ...string) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    for f in fields {
        if f !in qb.valid_sql_field_names {
            return error("${@FN}(): table `${qb.config.table}` has no field's name: `${f}`")
        }
    }
    qb.config.fields = fields
    return qb
}

// distinct marks the query as `SELECT DISTINCT`.
pub fn (qb_ &QueryBuilder[T]) distinct() !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    qb.config.has_distinct = true
    return qb
}

// set create a `set` clause for `update`
pub fn (qb_ &QueryBuilder[T]) set(assign string, values ...Primitive) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    if assign.len == 0 {
        return error('${@FN}(): empty `set`')
    }
    required_params := assign.count('?')
    if required_params != values.len {
        return error('${@FN}(): `set` requires `${required_params}` params but got `${values.len}`')
    }
    mut fields := []string{}
    assign_splits := assign.split_any(',')
    for assign_split in assign_splits {
        f := assign_split.split_any('=')
        if f.len != 2 {
            return error('${@FN}(): `set` syntax error, it should look like : `a=?,b=?`')
        }
        if f[1].trim_space() != '?' {
            return error('${@FN}(): `set` syntax error, it should look like : `a=?,b=?`')
        }
        field := f[0].trim_space()
        if field !in qb.valid_sql_field_names {
            return error("${@FN}(): table `${qb.config.table}` has no field's name: `${field}`")
        }
        fields << field
    }
    qb.data.fields << fields
    for v in values {
        qb.data.data << v
    }
    return qb
}

// table_from_struct get table from struct
fn table_from_struct[T]() Table {
    mut table_name := T.name
    // Strip generic parameters from type name (e.g., Message[Payload] -> Message)
    if bracket_pos := table_name.index('[') {
        table_name = table_name[..bracket_pos]
    }
    mut has_custom_table_name := false
    mut attrs := []VAttribute{}
    $for a in T.attributes {
        $if a.name == 'table' && a.has_arg {
            table_name = a.arg
            has_custom_table_name = true
        }
        attrs << a
    }
    if !has_custom_table_name {
        // Keep default ORM table names aligned with unquoted SQL identifiers across DB drivers.
        table_name = table_name.to_lower()
    }
    return Table{
        name:  table_name
        attrs: attrs
    }
}

// struct_meta return a struct's fields info
fn struct_meta[T]() []TableField {
    mut meta := []TableField{}
    $for field in T.fields {
        if !field.is_embed {
            mut attrs := []VAttribute{}
            mut is_skip := false
            for attr in field.attrs {
                f := attr.split_any(':')
                if f.len == 1 {
                    ff := f[0].trim_space()
                    if ff == 'skip' {
                        is_skip = true
                    }
                    attrs << VAttribute{
                        name: ff
                    }
                    continue
                }
                if f.len == 2 {
                    ff := f[1].trim_space()
                    if f[0].trim_space() == 'sql' && ff == '-' {
                        is_skip = true
                    }
                    mut kind := AttributeKind.plain
                    if ff == 'true' || ff == 'false' {
                        kind = .bool
                    } else if ff.starts_with('if ') {
                        kind = .comptime_define
                    } else if (ff.starts_with("'") && ff.ends_with("'"))
                        || (ff.starts_with('"') && ff.ends_with('"')) {
                        kind = .string
                    } else if ff.contains_only('0123456789') {
                        kind = .number
                    } else if ff !in ['serial', 'i8', 'i16', 'int', 'i64', 'u8', 'u16', 'u32',
                        'u64', 'f32', 'f64', 'bool', 'string'] {
                        // @[sql: data_type] need kind = .plain
                        // @[sql: column_name] need kind = .string
                        kind = .string
                    }
                    attrs << VAttribute{
                        name:    f[0].trim_space()
                        has_arg: true
                        arg:     ff
                        kind:    kind
                    }
                }
            }

            mut field_type := field.typ
            if typeof(field).name.contains('time.Time') {
                field_type = time_
            } else if field.is_struct {
                field_type = type_idx['int']
            } else if field.is_enum {
                field_type = enum_
            }

            if !is_skip {
                meta << TableField{
                    name:     field.name
                    typ:      field_type
                    nullable: field.is_option
                    attrs:    attrs
                }
            }
        }
    }
    return meta
}

// map_row map a row result into a struct
fn (qb &QueryBuilder[T]) map_row(row []Primitive) !T {
    mut instance := T{}

    $for field in T.fields {
        mut m := TableField{}
        mm := qb.meta.filter(it.name == field.name)
        if mm.len != 0 {
            m = mm[0]
            index := qb.config.fields.index(sql_field_name(m))
            if index >= 0 {
                value := row[index]

                $if field.typ is $option {
                    if value == Primitive(Null{}) {
                        instance.$(field.name) = none
                    }
                }
                if value != Primitive(Null{}) {
                    $if field.unaliased_typ is i8 || field.unaliased_typ is ?i8 {
                        instance.$(field.name) = match value {
                            i8 { i8(value) }
                            i16 { i8(value) }
                            int { i8(value) }
                            i64 { i8(value) }
                            u8 { i8(value) }
                            u16 { i8(value) }
                            u32 { i8(value) }
                            u64 { i8(value) }
                            bool { i8(value) }
                            f32 { i8(value) }
                            f64 { i8(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is i16 || field.unaliased_typ is ?i16 {
                        instance.$(field.name) = match value {
                            i8 { i16(value) }
                            i16 { i16(value) }
                            int { i16(value) }
                            i64 { i16(value) }
                            u8 { i16(value) }
                            u16 { i16(value) }
                            u32 { i16(value) }
                            u64 { i16(value) }
                            bool { i16(value) }
                            f32 { i16(value) }
                            f64 { i16(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is int || field.unaliased_typ is ?int {
                        instance.$(field.name) = match value {
                            i8 { int(value) }
                            i16 { int(value) }
                            int { int(value) }
                            i64 { int(value) }
                            u8 { int(value) }
                            u16 { int(value) }
                            u32 { int(value) }
                            u64 { int(value) }
                            bool { int(value) }
                            f32 { int(value) }
                            f64 { int(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is i64 || field.unaliased_typ is ?i64
                        || field.unaliased_typ is $enum {
                        instance.$(field.name) = match value {
                            i8 { i64(value) }
                            i16 { i64(value) }
                            int { i64(value) }
                            i64 { i64(value) }
                            u8 { i64(value) }
                            u16 { i64(value) }
                            u32 { i64(value) }
                            u64 { i64(value) }
                            bool { i64(value) }
                            f32 { i64(value) }
                            f64 { i64(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is u8 || field.unaliased_typ is ?u8 {
                        instance.$(field.name) = match value {
                            i8 { u8(value) }
                            i16 { u8(value) }
                            int { u8(value) }
                            i64 { u8(value) }
                            u8 { u8(value) }
                            u16 { u8(value) }
                            u32 { u8(value) }
                            u64 { u8(value) }
                            bool { u8(value) }
                            f32 { u8(value) }
                            f64 { u8(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is u16 || field.unaliased_typ is ?u16 {
                        instance.$(field.name) = match value {
                            i8 { u16(value) }
                            i16 { u16(value) }
                            int { u16(value) }
                            i64 { u16(value) }
                            u8 { u16(value) }
                            u16 { u16(value) }
                            u32 { u16(value) }
                            u64 { u16(value) }
                            bool { u16(value) }
                            f32 { u16(value) }
                            f64 { u16(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is u32 || field.unaliased_typ is ?u32 {
                        instance.$(field.name) = match value {
                            i8 { u32(value) }
                            i16 { u32(value) }
                            int { u32(value) }
                            i64 { u32(value) }
                            u8 { u32(value) }
                            u16 { u32(value) }
                            u32 { u32(value) }
                            u64 { u32(value) }
                            bool { u32(value) }
                            f32 { u32(value) }
                            f64 { u32(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is u64 || field.unaliased_typ is ?u64 {
                        instance.$(field.name) = match value {
                            i8 { u64(value) }
                            i16 { u64(value) }
                            int { u64(value) }
                            i64 { u64(value) }
                            u8 { u64(value) }
                            u16 { u64(value) }
                            u32 { u64(value) }
                            u64 { u64(value) }
                            bool { u64(value) }
                            f32 { u64(value) }
                            f64 { u64(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is f32 || field.unaliased_typ is ?f32 {
                        instance.$(field.name) = match value {
                            i8 { f32(value) }
                            i16 { f32(value) }
                            int { f32(value) }
                            i64 { f32(value) }
                            u8 { f32(value) }
                            u16 { f32(value) }
                            u32 { f32(value) }
                            u64 { f32(value) }
                            bool { f32(value) }
                            f32 { value }
                            f64 { f32(value) }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is f64 || field.unaliased_typ is ?f64 {
                        instance.$(field.name) = match value {
                            i8 { f64(value) }
                            i16 { f64(value) }
                            int { f64(value) }
                            i64 { f64(value) }
                            u8 { f64(value) }
                            u16 { f64(value) }
                            u32 { f64(value) }
                            u64 { f64(value) }
                            bool { f64(value) }
                            f32 { f64(value) }
                            f64 { value }
                            else { 0 }
                        }
                    } $else $if field.unaliased_typ is bool || field.unaliased_typ is ?bool {
                        instance.$(field.name) = match value {
                            i8 { value != 0 }
                            i16 { value != 0 }
                            int { value != 0 }
                            i64 { value != 0 }
                            u8 { value != 0 }
                            u16 { value != 0 }
                            u32 { value != 0 }
                            u64 { value != 0 }
                            bool { value }
                            f32 { value != 0 }
                            f64 { value != 0 }
                            else { false }
                        }
                    } $else $if field.unaliased_typ is string || field.unaliased_typ is ?string {
                        instance.$(field.name) = value as string
                    } $else $if field.unaliased_typ is time.Time
                        || field.unaliased_typ is ?time.Time {
                        if m.typ == time_ {
                            instance.$(field.name) = value as time.Time
                        } else if m.typ == type_string {
                            instance.$(field.name) = time.parse(value as string)!
                        }
                    }
                }
            }
        }
    }
    return instance
}

// prepare QueryBuilder, ready for gen SQL
fn (qb_ &QueryBuilder[T]) prepare() ! {
    mut qb := unsafe { qb_ }

    // check for mismatch `(` and `)`
    for p in qb.where.parentheses {
        if p[1] == -1 {
            return error('${@FN}(): missing `)`')
        }
    }

    // auto fill field's names if not set by `select`
    if qb.config.fields.len == 0 {
        qb.config.fields = qb.meta.map(sql_field_name(it))
    }

    if qb.config.select_exprs.len != qb.config.fields.len {
        mut select_exprs := []string{cap: qb.config.fields.len}
        for f in qb.config.fields {
            mut select_expr := f
            for ff in qb.meta {
                if sql_field_name(ff) == f {
                    select_expr = sql_field_select_expr(ff)
                    break
                }
            }
            select_exprs << select_expr
        }
        qb.config.select_exprs = select_exprs
    }

    if qb.config.types.len == 0 {
        // set field's types
        mut types := []int{cap: qb.config.fields.len}
        for f in qb.config.fields {
            for ff in qb.meta {
                if sql_field_name(ff) == f {
                    types << ff.typ
                }
            }
        }
        qb.config.types = types
    }
}

fn (qb &QueryBuilder[T]) get_meta_field_by_sql_name(field string) ?TableField {
    for meta_field in qb.meta {
        if sql_field_name(meta_field) == field {
            return meta_field
        }
    }
    return none
}

fn is_numeric_type_idx(typ int) bool {
    return typ in nums || typ in num64 || typ in float
}

fn is_min_max_supported_type_idx(typ int) bool {
    return is_numeric_type_idx(typ) || typ == type_string || typ == time_
}

fn (qb &QueryBuilder[T]) validate_aggregate_field(kind AggregateKind, field string) !TableField {
    meta_field := qb.get_meta_field_by_sql_name(field) or {
        return error("${@FN}(): table `${qb.config.table}` has no field's name: `${field}`")
    }
    match kind {
        .sum, .avg {
            if !is_numeric_type_idx(meta_field.typ) {
                msg := match kind {
                    .sum { '${@FN}(): `sum` requires a numeric field' }
                    .avg { '${@FN}(): `avg` requires a numeric field' }
                    else { '${@FN}(): aggregate requires a numeric field' }
                }

                return error(msg)
            }
        }
        .min, .max {
            if !is_min_max_supported_type_idx(meta_field.typ) {
                msg := match kind {
                    .min { '${@FN}(): `min` requires a numeric, string, or time.Time field' }
                    .max { '${@FN}(): `max` requires a numeric, string, or time.Time field' }
                    else { '${@FN}(): aggregate requires a numeric, string, or time.Time field' }
                }

                return error(msg)
            }
        }
        else {}
    }

    return meta_field
}

fn (qb &QueryBuilder[T]) build_aggregate_config(kind AggregateKind, field string) !SelectConfig {
    mut cfg := qb.config
    cfg.aggregate_kind = kind
    cfg.aggregate_field = ''
    cfg.fields = []
    cfg.types = []
    if kind == .count {
        cfg.types = [type_idx['int']]
        return cfg
    }

    meta_field := qb.validate_aggregate_field(kind, field)!
    cfg.aggregate_field = field
    cfg.fields = [field]
    cfg.types = [if kind == .avg { type_idx['f64'] } else { meta_field.typ }]
    return cfg
}

fn primitive_to_aggregate_value(value Primitive) AggregateValue {
    return if value == Primitive(Null{}) {
        AggregateValue{}
    } else {
        AggregateValue{
            has_value: true
            value:     value
        }
    }
}

// as_int returns the aggregate value as `int`, or `none` when it is null or not numeric.
pub fn (value AggregateValue) as_int() ?int {
    if !value.has_value {
        return none
    }
    return match value.value {
        i8 { int(value.value) }
        i16 { int(value.value) }
        int { value.value }
        i64 { int(value.value) }
        u8 { int(value.value) }
        u16 { int(value.value) }
        u32 { int(value.value) }
        u64 { int(value.value) }
        f32 { int(value.value) }
        f64 { int(value.value) }
        else { return none }
    }
}

// as_f64 returns the aggregate value as `f64`, or `none` when it is null or not numeric.
pub fn (value AggregateValue) as_f64() ?f64 {
    if !value.has_value {
        return none
    }
    return match value.value {
        i8 { f64(value.value) }
        i16 { f64(value.value) }
        int { f64(value.value) }
        i64 { f64(value.value) }
        u8 { f64(value.value) }
        u16 { f64(value.value) }
        u32 { f64(value.value) }
        u64 { f64(value.value) }
        f32 { f64(value.value) }
        f64 { value.value }
        else { return none }
    }
}

// as_string returns the aggregate value as `string`, or `none` when it is null or not a string.
pub fn (value AggregateValue) as_string() ?string {
    if !value.has_value {
        return none
    }
    return match value.value {
        string { value.value }
        else { return none }
    }
}

// as_time returns the aggregate value as `time.Time`, or `none` when it is null or not a time.
pub fn (value AggregateValue) as_time() ?time.Time {
    if !value.has_value {
        return none
    }
    return match value.value {
        time.Time { value.value }
        else { return none }
    }
}

// query start a query and return result in struct `T`
pub fn (qb_ &QueryBuilder[T]) query() ![]T {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    rows := qb.conn.select(qb.config, qb.data, qb.where)!
    mut result := []T{cap: rows.len}
    for row in rows {
        result << qb.map_row[T](row)!
    }
    return result
}

// count start a count query and return result
pub fn (qb_ &QueryBuilder[T]) count() !int {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    count_config := qb.build_aggregate_config(.count, '')!
    result := qb.conn.select(count_config, qb.data, qb.where)!

    if result.len == 0 || result[0].len == 0 {
        return 0
    }
    count_val := result[0][0]
    return match count_val {
        int { count_val }
        i64 { int(count_val) }
        u64 { int(count_val) }
        else { return error('${@FN}(): invalid count result type') }
    }
}

// sum returns the sum of the field values as an `AggregateValue`.
pub fn (qb_ &QueryBuilder[T]) sum(field string) !AggregateValue {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    qb.validate_aggregate_field(.sum, field)!
    cfg := qb.build_aggregate_config(.sum, field)!
    result := qb.conn.select(cfg, qb.data, qb.where)!
    if result.len == 0 || result[0].len == 0 {
        return AggregateValue{}
    }
    return primitive_to_aggregate_value(result[0][0])
}

// min returns the smallest field value as an `AggregateValue`.
pub fn (qb_ &QueryBuilder[T]) min(field string) !AggregateValue {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    qb.validate_aggregate_field(.min, field)!
    cfg := qb.build_aggregate_config(.min, field)!
    result := qb.conn.select(cfg, qb.data, qb.where)!
    if result.len == 0 || result[0].len == 0 {
        return AggregateValue{}
    }
    return primitive_to_aggregate_value(result[0][0])
}

// max returns the largest field value as an `AggregateValue`.
pub fn (qb_ &QueryBuilder[T]) max(field string) !AggregateValue {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    qb.validate_aggregate_field(.max, field)!
    cfg := qb.build_aggregate_config(.max, field)!
    result := qb.conn.select(cfg, qb.data, qb.where)!
    if result.len == 0 || result[0].len == 0 {
        return AggregateValue{}
    }
    return primitive_to_aggregate_value(result[0][0])
}

// avg returns the average field value as an `AggregateValue`.
pub fn (qb_ &QueryBuilder[T]) avg(field string) !AggregateValue {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    qb.validate_aggregate_field(.avg, field)!
    cfg := qb.build_aggregate_config(.avg, field)!
    result := qb.conn.select(cfg, qb.data, qb.where)!
    if result.len == 0 || result[0].len == 0 {
        return AggregateValue{}
    }
    return primitive_to_aggregate_value(result[0][0])
}

// insert insert a record into the database
pub fn (qb_ &QueryBuilder[T]) insert[T](value T) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.insert_many([value])!
    return qb
}

// insert_many insert records into the database
// Uses batch INSERT for efficiency when inserting multiple records.
pub fn (qb_ &QueryBuilder[T]) insert_many[T](values []T) !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    if values.len == 0 {
        return error('${@FN}(): `insert` need at least one record')
    }
    mut batch := fill_data_with_struct[T](values[0], qb.meta)
    batch.batch_rows = values.len
    for i in 1 .. values.len {
        next := fill_data_with_struct[T](values[i], qb.meta)
        for d in next.data {
            batch.data << d
        }
    }
    qb.conn.insert(qb.config.table, batch)!
    return qb
}

// save updates all mapped fields in `value` using the struct primary key or `id` field.
pub fn save[T](conn Connection, value T) ! {
    mut qb := new_query[T](conn)
    data, where := build_save_query_data[T](qb.meta, qb.config.table.name, value)!
    qb.conn.update(qb.config.table, data, where)!
}

fn build_save_query_data[T](meta []TableField, table_name string, value T) !(QueryData, QueryData) {
    data := fill_data_with_struct[T](value, meta)
    if data.fields.len != data.data.len {
        return error('${@FN}(): table `${table_name}` contains fields that `save` cannot map automatically')
    }
    primary_field_name := find_save_primary_field_name(meta) or {
        return error('${@FN}(): table `${table_name}` needs a primary key or `id` field to use `save`')
    }
    mut update_data := QueryData{}
    mut where_data := QueryData{
        kinds: [.eq]
    }
    for i, field_name in data.fields {
        if field_name == primary_field_name {
            where_data.fields << field_name
            where_data.data << data.data[i]
            continue
        }
        update_data.fields << field_name
        update_data.data << data.data[i]
    }
    if where_data.fields.len == 0 {
        return error('${@FN}(): struct value is missing the primary key field `${primary_field_name}`')
    }
    if update_data.fields.len == 0 {
        return error('${@FN}(): no updatable fields were found for table `${table_name}`')
    }
    return update_data, where_data
}

fn find_save_primary_field_name(meta []TableField) ?string {
    for field in meta {
        for attr in field.attrs {
            if attr_name_matches(attr.name, 'primary') {
                return sql_field_name(field)
            }
        }
    }
    for field in meta {
        field_name := sql_field_name(field)
        if field.name == 'id' || field_name == 'id' {
            return field_name
        }
    }
    return none
}

fn fill_data_with_struct[T](value T, meta []TableField) QueryData {
    mut qb := QueryData{}
    $for field in T.fields {
        sql_fields := meta.filter(it.name == field.name)
        if sql_fields.len == 1 {
            sql_f := sql_fields[0]
            sql_f_name := sql_field_name(sql_f)
            sql_f_type := sql_field_type(sql_f)

            if sql_f_type == serial {
                // `serial` should be auto field
                qb.auto_fields << qb.fields.len
            }
            qb.fields << sql_f_name

            $if field.unaliased_typ is bool {
                qb.data << bool_to_primitive(bool(value.$(field.name)))
            } $else $if field.unaliased_typ is ?bool {
                qb.data << option_bool_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is f32 {
                qb.data << f32_to_primitive(f32(value.$(field.name)))
            } $else $if field.unaliased_typ is ?f32 {
                qb.data << option_f32_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is f64 {
                qb.data << f64_to_primitive(f64(value.$(field.name)))
            } $else $if field.unaliased_typ is ?f64 {
                qb.data << option_f64_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is i8 {
                qb.data << i8_to_primitive(i8(value.$(field.name)))
            } $else $if field.unaliased_typ is ?i8 {
                qb.data << option_i8_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is i16 {
                qb.data << i16_to_primitive(i16(value.$(field.name)))
            } $else $if field.unaliased_typ is ?i16 {
                qb.data << option_i16_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is int {
                qb.data << int_to_primitive(int(value.$(field.name)))
            } $else $if field.unaliased_typ is ?int {
                qb.data << option_int_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is i64 {
                qb.data << i64_to_primitive(i64(value.$(field.name)))
            } $else $if field.unaliased_typ is ?i64 {
                qb.data << option_i64_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is u8 {
                qb.data << u8_to_primitive(u8(value.$(field.name)))
            } $else $if field.unaliased_typ is ?u8 {
                qb.data << option_u8_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is u16 {
                qb.data << u16_to_primitive(u16(value.$(field.name)))
            } $else $if field.unaliased_typ is ?u16 {
                qb.data << option_u16_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is u32 {
                qb.data << u32_to_primitive(u32(value.$(field.name)))
            } $else $if field.unaliased_typ is ?u32 {
                qb.data << option_u32_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is u64 {
                qb.data << u64_to_primitive(u64(value.$(field.name)))
            } $else $if field.unaliased_typ is ?u64 {
                qb.data << option_u64_to_primitive(value.$(field.name))
            }
            $if field.unaliased_typ is string {
                qb.data << string_to_primitive(string(value.$(field.name)))
            } $else $if field.unaliased_typ is ?string {
                qb.data << option_string_to_primitive(value.$(field.name))
            } $else $if field.unaliased_typ is time.Time {
                if sql_f_type == type_string {
                    qb.data << string_to_primitive(value.$(field.name).format_ss())
                } else {
                    qb.data << time_to_primitive(value.$(field.name))
                }
            } $else $if field.unaliased_typ is ?time.Time {
                if sql_f_type == type_string {
                    b := value.$(field.name)
                    if b_ := b {
                        qb.data << Primitive(b_.format_ss())
                    } else {
                        qb.data << null_primitive
                    }
                } else {
                    qb.data << option_time_to_primitive(value.$(field.name))
                }
            } $else $if field.unaliased_typ is $enum {
                qb.data << i64_to_primitive(i64(value.$(field.name)))
            }
        }
    }
    return qb
}

// update update record(s) in the database
pub fn (qb_ &QueryBuilder[T]) update() !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    if qb.data.fields.len == 0 {
        return error('${@FN}(): `update` need at least one `set` clause')
    }
    qb.conn.update(qb.config.table, qb.data, qb.where)!
    return qb
}

// update_many updates multiple records by a key field, using batch CASE WHEN for efficiency.
// key_field is the column used to match rows (e.g. 'id').
// field_names selects which columns to update; if empty, all struct fields except key_field are updated.
pub fn update_many[T](mut conn Connection, values []T, key_field string, field_names ...string) ! {
    if values.len == 0 {
        return error('${@FN}(): need at least one record')
    }
    mut qb := new_query[T](conn)

    // Build the field list from the first value
    first := fill_data_with_struct[T](values[0], qb.meta)
    mut key_index := -1
    mut value_fields := []string{}
    mut value_indexes := []int{}

    for i, field in first.fields {
        if field == key_field {
            key_index = i
        } else if field_names.len == 0 || field in field_names {
            value_fields << field
            value_indexes << i
        }
    }

    if key_index < 0 {
        return error('${@FN}(): key field `${key_field}` not found in table `${qb.config.table.name}`')
    }

    if value_fields.len == 0 {
        return error('${@FN}(): no updatable fields found for table `${qb.config.table.name}`')
    }

    mut update_data := QueryData{
        fields:     value_fields
        batch_rows: values.len
        batch_key:  key_field
    }

    // Build data: per value_field, per row: [key_value, value_field_value]
    rows := values.map(fill_data_with_struct[T](it, qb.meta))

    for fj in value_indexes {
        for row in rows {
            if key_index < row.data.len {
                update_data.data << row.data[key_index]
            }
            if fj < row.data.len {
                update_data.data << row.data[fj]
            }
        }
    }

    // Build WHERE clause using IN
    mut key_values := []Primitive{}
    for row in rows {
        if key_index < row.data.len {
            key_values << row.data[key_index]
        }
    }

    mut where_data := QueryData{
        fields: [key_field]
        data:   [Primitive(key_values)]
        kinds:  [.in]
    }

    conn.update(qb.config.table, update_data, where_data)!
}

// delete delete record(s) in the database
pub fn (qb_ &QueryBuilder[T]) delete() !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.prepare()!
    qb.conn.delete(qb.config.table, qb.where)!
    return qb
}

// create create a table
pub fn (qb_ &QueryBuilder[T]) create() !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.conn.create(qb.config.table, qb.meta)!
    return qb
}

// drop drop a table
pub fn (qb_ &QueryBuilder[T]) drop() !&QueryBuilder[T] {
    mut qb := unsafe { qb_ }
    defer {
        qb.reset()
    }
    qb.conn.drop(qb.config.table)!
    return qb
}

// last_id returns the last inserted id of the db
pub fn (qb_ &QueryBuilder[T]) last_id() int {
    mut qb := unsafe { qb_ }
    qb.reset()
    return qb.conn.last_id()
}