// Package sqlx provides extra features for go's standard database/sql library.
package sqlx

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"iter"
	"reflect"
	"strings"
	"sync"
)

// IsolationLevel is the transaction isolation level.
type IsolationLevel = sql.IsolationLevel

// Isolation levels.
const (
	LevelDefault         = sql.LevelDefault
	LevelReadUncommitted = sql.LevelReadUncommitted
	LevelReadCommitted   = sql.LevelReadCommitted
	LevelWriteCommitted  = sql.LevelWriteCommitted
	LevelRepeatableRead  = sql.LevelRepeatableRead
	LevelSnapshot        = sql.LevelSnapshot
	LevelSerializable    = sql.LevelSerializable
	LevelLinearizable    = sql.LevelLinearizable
)

// Errors.
var (
	ErrNotFound  = errors.New("sqlx: result is not found")
	ErrNotSingle = errors.New("sqlx: result is not single")
)

var (
	structIndexes      = make(map[reflect.Type]map[string]int)
	structIndexesMutex sync.RWMutex
)

// Named creates a named argument.
func Named(name string, value any) sql.NamedArg {
	return sql.Named(name, value)
}

// QuerySingle executes a query that returns a single value.
func QuerySingle[T any](db *DB, cmd string, args ...any) (T, error) {
	return QuerySingleContext[T](context.Background(), db, cmd, args...)
}

// QuerySingleContext executes a query that returns a single value.
func QuerySingleContext[T any](ctx context.Context, db *DB, cmd string, args ...any) (T, error) {
	var result T
	done, err := false, ErrNotFound
	for result, err = range QueryContext[T](ctx, db, cmd, args...) {
		if err != nil {
			break
		}
		if done {
			err = ErrNotSingle
			break
		}
		done = true
	}
	return result, err
}

// Query executes a query that returns a sequence.
func Query[T any](db *DB, cmd string, args ...any) iter.Seq2[T, error] {
	return QueryContext[T](context.Background(), db, cmd, args...)
}

// QueryContext executes a query that returns a sequence.
func QueryContext[T any](ctx context.Context, db *DB, cmd string, args ...any) iter.Seq2[T, error] {
	return func(yield func(T, error) bool) {
		var zero T
		rows, err := db.QueryRowsContext(ctx, cmd, args...)
		if err != nil {
			yield(zero, err)
			return
		}
		defer rows.Close()

		for ev, err := range getValues(rows, reflect.TypeOf(zero)) {
			if err != nil {
				yield(zero, err)
				return
			}

			p := new(T) // Ensure addressable.
			v := reflect.ValueOf(p).Elem()
			v.Set(ev)
			if !yield(*p, nil) {
				return
			}
		}
	}
}

func fillSliceResult(rows *sql.Rows, result any) error {
	v := reflect.ValueOf(result)
	t := v.Type()
	if t.Kind() != reflect.Pointer {
		return fmt.Errorf("sqlx: unable to map the query results to non-pointer type: %s", t)
	}

	t = t.Elem()
	if t.Kind() != reflect.Slice {
		return fmt.Errorf("sqlx: unable to map the query results to non-slice type: %s", t)
	}
	v = v.Elem()
	et := t.Elem()

	evs := v
	for ev, err := range getValues(rows, et) {
		if err != nil {
			return err
		}
		evs = reflect.Append(evs, ev)
	}
	v.Set(evs)
	return nil
}

func fillSingleResult(rows *sql.Rows, result any) error {
	v := reflect.ValueOf(result)
	t := v.Type()
	if t.Kind() != reflect.Pointer {
		return fmt.Errorf("sqlx: unable to map the query results to non-pointer type: %s", t)
	}
	t = t.Elem()
	v = v.Elem()

	var ev reflect.Value
	done, err := false, ErrNotFound
	for ev, err = range getValues(rows, t) {
		if err != nil {
			break
		}
		if done {
			err = ErrNotSingle
			break
		}
		done = true
	}
	if err == nil {
		v.Set(ev)
	}
	return err
}

func getValues(rows *sql.Rows, t reflect.Type) iter.Seq2[reflect.Value, error] {
	return func(yield func(reflect.Value, error) bool) {
		columns, err := rows.Columns()
		if err != nil {
			yield(reflect.Value{}, err)
			return
		}
		if len(columns) < 1 {
			yield(reflect.Value{}, errors.New("sqlx: the query result has no columns"))
			return
		}
		fields := make([]any, len(columns))

		isPointer := t.Kind() == reflect.Pointer
		if isPointer {
			t = t.Elem()
		}
		isStruct := t.Kind() == reflect.Struct

		for rows.Next() {
			var err error
			v := reflect.New(t)
			if isStruct {
				err = scanStruct(rows, columns, fields, t, v.Elem())
			} else {
				err = rows.Scan(v.Interface())
			}
			if err != nil {
				yield(reflect.Value{}, err)
				return
			}

			if !isPointer {
				v = v.Elem()
			}
			if !yield(v, nil) {
				return
			}
		}
		if err := rows.Err(); err != nil {
			yield(reflect.Value{}, err)
		}
	}
}

func scanStruct(rows *sql.Rows, columns []string, fields []any, t reflect.Type, v reflect.Value) error {
	for i, column := range columns {
		fv, err := getStructFieldValue(t, v, column)
		if err != nil {
			return err
		}
		fields[i] = fv.Addr().Interface()
	}
	return rows.Scan(fields...)
}

func getStructFieldValue(t reflect.Type, v reflect.Value, name string) (reflect.Value, error) {
	indexes := getStructIndexes(t)
	i := strings.IndexByte(name, '_')
	key := name
	if i >= 0 {
		key = name[:i]
	}
	index, ok := indexes[key]
	if !ok {
		return reflect.Value{}, fmt.Errorf("sqlx: field `%s.%s` is not found", t, name)
	}

	fv := v.Field(index)
	if i < 0 {
		return fv, nil
	}

	ft := fv.Type()
	if ft.Kind() == reflect.Pointer {
		ft = ft.Elem()
		if fv.IsNil() {
			fv.Set(reflect.New(ft))
		}
		fv = fv.Elem()
	}

	if ft.Kind() != reflect.Struct {
		return reflect.Value{}, fmt.Errorf("sqlx: unsupported type `%s` for `%s`", ft, name)
	}
	return getStructFieldValue(ft, fv, name[i+1:])
}

func getStructIndexes(t reflect.Type) map[string]int {
	structIndexesMutex.RLock()
	indexes, ok := structIndexes[t]
	structIndexesMutex.RUnlock()
	if ok {
		return indexes
	}

	structIndexesMutex.Lock()
	defer structIndexesMutex.Unlock()

	if indexes, ok = structIndexes[t]; ok {
		return indexes
	}

	n := t.NumField()
	indexes = make(map[string]int, n)
	structIndexes[t] = indexes
	for i := range n {
		f := t.Field(i)
		if f.IsExported() {
			key := f.Name
			if tag, ok := f.Tag.Lookup("db"); ok {
				key = tag
			}
			indexes[key] = i
		}
	}
	return indexes
}