package stardb

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

func (star *StarRows) Orm(ins interface{}) error {
	//check if is slice
	if !star.parsed {
		if err := star.parserows(); err != nil {
			return err
		}
	}
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() != reflect.Ptr {
		return errors.New("interface not writable")
	}
	//now convert to slice
	t = t.Elem()
	v = v.Elem()
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		//get type of slice
		sigType := t.Elem()
		var result reflect.Value
		result = reflect.New(t).Elem()
		if star.Length == 0 {
			v.Set(result)
			return nil
		}
		for i := 0; i < star.Length; i++ {
			val := reflect.New(sigType)
			star.setAllRefValue(val.Interface(), "db", i)
			result = reflect.Append(result, val.Elem())
		}
		v.Set(result)
		return nil
	}
	if star.Length == 0 {
		return nil
	}
	return star.setAllRefValue(ins, "db", 0)
}

func (star *StarDB) queryX(ctx context.Context, ins interface{}, args ...interface{}) (*StarRows, error) {
	kvMap, err := getAllRefValue(ins, "db")
	if err != nil {
		return nil, err
	}
	for k, v := range args {
		if k == 0 {
			continue
		}
		switch v.(type) {
		case string:
			str := v.(string)
			if strings.Index(str, ":") == 0 {
				if _, ok := kvMap[str[1:]]; ok {
					args[k] = kvMap[str[1:]]
				} else {
					args[k] = ""
				}
				continue
			}
			if strings.Index(str, `\:`) == 0 {
				args[k] = kvMap[str[1:]]
			}
		}
	}
	return star.query(ctx, args...)
}
func (star *StarDB) QueryX(ins interface{}, args ...interface{}) (*StarRows, error) {
	return star.queryX(nil, ins, args)
}
func (star *StarDB) QueryXS(ins interface{}, args ...interface{}) ([]*StarRows, error) {
	var starRes []*StarRows
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.queryX(nil, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.queryX(nil, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarDB) ExecXS(ins interface{}, args ...interface{}) ([]sql.Result, error) {
	var starRes []sql.Result
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.execX(nil, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.execX(nil, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarDB) ExecX(ins interface{}, args ...interface{}) (sql.Result, error) {
	return star.execX(nil, ins, args...)
}

func getUpdateSentence(ins interface{}, sheetName string, primaryKey ...string) (string, []string, error) {
	Keys, err := getAllRefKey(ins, "db")
	if err != nil {
		return "", []string{}, err
	}
	var mystr string
	for k, v := range Keys {
		mystr += fmt.Sprintf("%s=? ", v)
		Keys[k] = ":" + v
	}
	mystr = fmt.Sprintf("update %s set %s where ", sheetName, mystr)
	var whereSlice []string
	for _, v := range primaryKey {
		whereSlice = append(whereSlice, v+"=?")
		Keys = append(Keys, ":"+v)
	}
	mystr += strings.Join(whereSlice, " and ")
	return mystr, Keys, nil
}

func getInsertSentence(ins interface{}, sheetName string, autoIncrease ...string) (string, []string, error) {
	Keys, err := getAllRefKey(ins, "db")
	if err != nil {
		return "", []string{}, err
	}
	var mystr, rps string
	var rtnKeys []string
cns:
	for _, v := range Keys {
		for _, vs := range autoIncrease {
			if v == vs {
				rps += "null,"
				continue cns
			}
		}
		rtnKeys = append(rtnKeys, ":"+v)
		rps += "?,"
	}
	mystr = fmt.Sprintf("insert into %s (%s) values (%s) ", sheetName, strings.Join(Keys, ","), rps[:len(rps)-1])
	return mystr, rtnKeys, nil
}

func (star *StarDB) execX(ctx context.Context, ins interface{}, args ...interface{}) (sql.Result, error) {
	kvMap, err := getAllRefValue(ins, "db")
	if err != nil {
		return nil, err
	}
	for k, v := range args {
		if k == 0 {
			continue
		}
		switch v.(type) {
		case string:
			str := v.(string)
			if strings.Index(str, ":") == 0 {
				if _, ok := kvMap[str[1:]]; ok {
					args[k] = kvMap[str[1:]]
				} else {
					args[k] = ""
				}
				continue
			}
			if strings.Index(str, `\:`) == 0 {
				args[k] = kvMap[str[1:]]
			}
		}
	}
	return star.exec(ctx, args...)
}

func (star *StarDB) Update(ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	return star.updateinsert(nil, true, ins, sheetName, primaryKey...)
}

func (star *StarDB) UpdateContext(ctx context.Context, ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	return star.updateinsert(ctx, true, ins, sheetName, primaryKey...)
}

func (star *StarDB) Insert(ins interface{}, sheetName string, autoCreaseKey ...string) (sql.Result, error) {
	return star.updateinsert(nil, false, ins, sheetName, autoCreaseKey...)
}

func (star *StarDB) InsertContext(ctx context.Context, ins interface{}, sheetName string, autoCreaseKey ...string) (sql.Result, error) {
	return star.updateinsert(ctx, false, ins, sheetName, autoCreaseKey...)
}

func (star *StarDB) updateinsert(ctx context.Context, isUpdate bool, ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	var sqlStr string
	var para []string
	var err error
	if isUpdate {
		sqlStr, para, err = getUpdateSentence(ins, sheetName, primaryKey...)
	} else {
		sqlStr, para, err = getInsertSentence(ins, sheetName, primaryKey...)
	}
	if err != nil {
		return nil, err
	}
	tmpStr := append([]interface{}{}, sqlStr)
	for _, v := range para {
		tmpStr = append(tmpStr, v)
	}
	return star.execX(ctx, ins, tmpStr...)
}
func (star *StarDB) QueryXContext(ctx context.Context, ins interface{}, args ...interface{}) (*StarRows, error) {
	return star.queryX(ctx, ins, args)
}
func (star *StarDB) QueryXSContext(ctx context.Context, ins interface{}, args ...interface{}) ([]*StarRows, error) {
	var starRes []*StarRows
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.queryX(ctx, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.queryX(ctx, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarDB) ExecXSContext(ctx context.Context, ins interface{}, args ...interface{}) ([]sql.Result, error) {
	var starRes []sql.Result
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.execX(ctx, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.execX(ctx, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarDB) ExecXContext(ctx context.Context, ins interface{}, args ...interface{}) (sql.Result, error) {
	return star.execX(ctx, ins, args...)
}

func (star *StarTx) queryX(ctx context.Context, ins interface{}, args ...interface{}) (*StarRows, error) {
	kvMap, err := getAllRefValue(ins, "db")
	if err != nil {
		return nil, err
	}
	for k, v := range args {
		if k == 0 {
			continue
		}
		switch v.(type) {
		case string:
			str := v.(string)
			if strings.Index(str, ":") == 0 {
				if _, ok := kvMap[str[1:]]; ok {
					args[k] = kvMap[str[1:]]
				} else {
					args[k] = ""
				}
				continue
			}
			if strings.Index(str, `\:`) == 0 {
				args[k] = kvMap[str[1:]]
			}
		}
	}
	return star.query(ctx, args...)
}
func (star *StarTx) QueryX(ins interface{}, args ...interface{}) (*StarRows, error) {
	return star.queryX(nil, ins, args)
}

func (star *StarTx) QueryXS(ins interface{}, args ...interface{}) ([]*StarRows, error) {
	var starRes []*StarRows
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.queryX(nil, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.queryX(nil, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}
func (star *StarTx) Update(ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	return star.updateinsert(nil, true, ins, sheetName, primaryKey...)
}

func (star *StarTx) UpdateContext(ctx context.Context, ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	return star.updateinsert(ctx, true, ins, sheetName, primaryKey...)
}

func (star *StarTx) Insert(ins interface{}, sheetName string, autoCreaseKey ...string) (sql.Result, error) {
	return star.updateinsert(nil, false, ins, sheetName, autoCreaseKey...)
}

func (star *StarTx) InsertContext(ctx context.Context, ins interface{}, sheetName string, autoCreaseKey ...string) (sql.Result, error) {
	return star.updateinsert(ctx, false, ins, sheetName, autoCreaseKey...)
}

func (star *StarTx) updateinsert(ctx context.Context, isUpdate bool, ins interface{}, sheetName string, primaryKey ...string) (sql.Result, error) {
	var sqlStr string
	var para []string
	var err error
	if isUpdate {
		sqlStr, para, err = getUpdateSentence(ins, sheetName, primaryKey...)
	} else {
		sqlStr, para, err = getInsertSentence(ins, sheetName, primaryKey...)
	}
	if err != nil {
		return nil, err
	}
	tmpStr := append([]interface{}{}, sqlStr)
	for _, v := range para {
		tmpStr = append(tmpStr, v)
	}
	return star.execX(ctx, ins, tmpStr...)
}
func (star *StarTx) ExecXS(ins interface{}, args ...interface{}) ([]sql.Result, error) {
	var starRes []sql.Result
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.execX(nil, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.execX(nil, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarTx) ExecX(ins interface{}, args ...interface{}) (sql.Result, error) {
	return star.execX(nil, ins, args...)
}
func (star *StarTx) execX(ctx context.Context, ins interface{}, args ...interface{}) (sql.Result, error) {
	kvMap, err := getAllRefValue(ins, "db")
	if err != nil {
		return nil, err
	}
	for k, v := range args {
		if k == 0 {
			continue
		}
		switch v.(type) {
		case string:
			str := v.(string)
			if strings.Index(str, ":") == 0 {
				if _, ok := kvMap[str[1:]]; ok {
					args[k] = kvMap[str[1:]]
				} else {
					args[k] = ""
				}
				continue
			}
			if strings.Index(str, `\:`) == 0 {
				args[k] = kvMap[str[1:]]
			}
		}
	}
	return star.exec(ctx, args...)
}

func (star *StarTx) QueryXContext(ctx context.Context, ins interface{}, args ...interface{}) (*StarRows, error) {
	return star.queryX(ctx, ins, args)
}
func (star *StarTx) QueryXSContext(ctx context.Context, ins interface{}, args ...interface{}) ([]*StarRows, error) {
	var starRes []*StarRows
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.queryX(ctx, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.queryX(ctx, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarTx) ExecXSContext(ctx context.Context, ins interface{}, args ...interface{}) ([]sql.Result, error) {
	var starRes []sql.Result
	t := reflect.TypeOf(ins)
	v := reflect.ValueOf(ins)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
		v = v.Elem()
	}
	//now convert to slice
	if v.Kind() == reflect.Slice || v.Kind() == reflect.Array {
		for i := 0; i < v.Len(); i++ {
			result, err := star.execX(ctx, v.Index(i).Interface(), args...)
			if err != nil {
				return starRes, err
			}
			starRes = append(starRes, result)
		}
	} else {
		result, err := star.execX(ctx, ins, args...)
		if err != nil {
			return starRes, err
		}
		starRes = append(starRes, result)
	}
	return starRes, nil
}

func (star *StarTx) ExecXContext(ctx context.Context, ins interface{}, args ...interface{}) (sql.Result, error) {
	return star.execX(ctx, ins, args...)
}