package nstore

import (
	"database/sql"
	"fmt"
	"iter"
	"log"
	"reflect"
	"strings"

	_ "github.com/mattn/go-sqlite3"
)

type Database struct {
	conn      *sql.DB
	recordIDs map[any]int64
	debug     bool
}

func Open(path string) (*Database, error) {
	if path == ":memory:" {
		path = "file::memory:?cache=shared"
	}
	db, err := sql.Open("sqlite3", path)
	return &Database{conn: db, recordIDs: make(map[any]int64)}, err
}

func OpenDebug(path string) (*Database, error) {
	if path == ":memory:" {
		path = "file::memory:?cache=shared"
	}
	db, err := sql.Open("sqlite3", path)
	return &Database{conn: db, recordIDs: make(map[any]int64), debug: true}, err
}

func OpenWithDB(db *sql.DB) *Database {
	return &Database{conn: db, recordIDs: make(map[any]int64)}
}

func (d *Database) Close() error {
	return d.conn.Close()
}

func (d *Database) ID(value any) (int64, error) {
	if d.recordIDs[value] != 0 {
		return d.recordIDs[value], nil
	}
	if d.recordIDs[&value] != 0 {
		return d.recordIDs[value], nil
	}
	return 0, errNotExists
}

func (d *Database) Save(values ...any) ([]int64, error) {
	if len(values) == 0 {
		return []int64{}, nil
	}

	tx, err := d.conn.Begin()
	if err != nil {
		return nil, err
	}

	IDs := make([]int64, 0)
	for _, value := range values {
		rValue, err := reflectStructValue(value)
		if err != nil {
			return nil, err
		}
		ID, err := d.saveStructDepth(tx, rValue, 0)
		if err != nil {
			tx.Rollback()
			return nil, err
		}
		IDs = append(IDs, ID)
	}

	return IDs, tx.Commit()
}

func (d *Database) Delete(values ...any) error {
	if len(values) == 0 {
		return nil
	}

	tx, err := d.conn.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	for _, value := range values {
		ID, err := d.ID(value)
		if err != nil {
			return err
		}
		rType, err := reflectStructType(value)
		if err != nil {
			return err
		}
		if err := d.deleteStruct(tx, ID, rType); err != nil {
			return err
		}
	}

	return tx.Commit()
}

func (d *Database) FetchQuery(typeValue any, query string, args ...any) (iter.Seq2[int64, any], error) {
	var err error
	rType, err := reflectStructType(typeValue)
	if err != nil {
		return nil, err
	}
	d.debugQuery(query, args...)

	return func(yield func(int64, any) bool) {
		var stmt *sql.Stmt
		var rows *sql.Rows
		var rowValue reflect.Value
		var rowID int64

		stmt, err = d.conn.Prepare(query)
		if err != nil {
			return
		}
		defer stmt.Close()

		rows, err = stmt.Query(args...)
		if err != nil {
			return
		}
		defer rows.Close()

		for rows.Next() {
			rowID, rowValue, err = d.rowToValue(rows, rType)
			if err != nil {
				log.Println("fetch row error:", err)
				return
			}
			if !yield(rowID, rowValue.Addr().Interface()) {
				return
			}
		}

	}, err
}

func (d *Database) Fetch(typeValue any, IDs ...int64) (iter.Seq2[int64, any], error) {
	rType, err := reflectStructType(typeValue)
	if err != nil {
		return nil, err
	}
	tableName := structTypeTableName(rType)

	args := make([]any, 0)
	for _, ID := range IDs {
		args = append(args, ID)
	}

	var query strings.Builder
	fmt.Fprintf(&query, `SELECT * FROM "%s"`, tableName)
	if len(IDs) > 0 {
		fmt.Fprintf(&query, ` WHERE _id IN (%s)`, strings.TrimSuffix(strings.Repeat("?,", len(IDs)), ","))
	}

	return func(yield func(int64, any) bool) {
		var stmt *sql.Stmt
		var rows *sql.Rows
		var rowValue reflect.Value
		var rowID int64

		d.debugQuery(query.String(), args...)
		stmt, err = d.conn.Prepare(query.String())
		if err != nil {
			return
		}
		defer stmt.Close()

		rows, err = stmt.Query(args...)
		if err != nil {
			return
		}
		defer rows.Close()

		for rows.Next() {
			rowID, rowValue, err = d.rowToValue(rows, rType)
			if err != nil {
				d.logWarning(err)
				return
			}
			if !yield(rowID, rowValue.Addr().Interface()) {
				return
			}
		}

	}, err
}

func (d *Database) FetchOne(typeValue any, ID int64) (any, error) {
	res, err := d.Fetch(typeValue, ID)
	if err != nil {
		return nil, err
	}
	for _, value := range res {
		return value, nil
	}
	return nil, errNotExists
}

func (d *Database) FetchAll(typeValue any) (iter.Seq2[int64, any], error) {
	rType, err := reflectStructType(typeValue)
	if err != nil {
		return nil, err
	}
	tableName := structTypeTableName(rType)

	return d.FetchQuery(
		typeValue,
		fmt.Sprintf(`SELECT * FROM "%s"`, tableName),
	)
}