package db

import (
	"code.hoteas.com/golang/hotime/cache"
	. "code.hoteas.com/golang/hotime/common"
	"database/sql"
	"encoding/json"
	"errors"
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/mattn/go-sqlite3"
	"github.com/sirupsen/logrus"
	"os"
	"reflect"
	"sort"
	"strings"
)

type HoTimeDB struct {
	*sql.DB
	ContextBase
	DBName string
	*cache.HoTimeCache
	Log         *logrus.Logger
	Type        string
	Prefix      string
	LastQuery   string
	LastData    []interface{}
	ConnectFunc func(err ...*Error) (*sql.DB, *sql.DB)
	LastErr     *Error
	limit       Slice
	*sql.Tx     //事务对象
	SlaveDB     *sql.DB
	Mode        int //mode为0生产模式,1、为测试模式、2为开发模式
}

type HotimeDBBuilder struct {
	HoTimeDB  *HoTimeDB
	table     string
	selects   []interface{}
	join      Slice
	where     Map
	lastWhere Map
	page      int
	pageRow   int
}

func (that *HoTimeDB) Table(table string) *HotimeDBBuilder {

	return &HotimeDBBuilder{HoTimeDB: that, table: table, where: Map{}}
}

func (that *HotimeDBBuilder) Get(qu ...interface{}) Map {

	return that.HoTimeDB.Get(that.table, that.join, qu, that.where)
}

func (that *HotimeDBBuilder) Count() int {

	return that.HoTimeDB.Count(that.table, that.join, that.where)
}
func (that *HotimeDBBuilder) Page(page, pageRow int) *HotimeDBBuilder {
	that.page = page
	that.pageRow = pageRow
	return that
}
func (that *HotimeDBBuilder) Select(qu ...interface{}) []Map {
	if that.page != 0 {
		return that.HoTimeDB.Page(that.page, that.pageRow).PageSelect(that.table, that.join, qu, that.where)
	}
	return that.HoTimeDB.Select(that.table, that.join, qu, that.where)
}

func (that *HotimeDBBuilder) Update(qu ...interface{}) int64 {
	lth := len(qu)
	if lth == 0 {
		return 0
	}
	data := Map{}
	if lth == 1 {
		data = ObjToMap(qu[0])
	}
	if lth > 1 {
		for k := 1; k < lth; k++ {
			data[ObjToStr(qu[k-1])] = qu[k]
			k++
		}
	}

	return that.HoTimeDB.Update(that.table, data, that.where)
}

func (that *HotimeDBBuilder) Delete() int64 {

	return that.HoTimeDB.Delete(that.table, that.where)
}

func (that *HotimeDBBuilder) LeftJoin(table, joinStr string) *HotimeDBBuilder {
	that.Join(Map{"[>]" + table: joinStr})
	return that

}
func (that *HotimeDBBuilder) RightJoin(table, joinStr string) *HotimeDBBuilder {
	that.Join(Map{"[<]" + table: joinStr})
	return that

}
func (that *HotimeDBBuilder) InnerJoin(table, joinStr string) *HotimeDBBuilder {
	that.Join(Map{"[><]" + table: joinStr})
	return that

}

func (that *HotimeDBBuilder) FullJoin(table, joinStr string) *HotimeDBBuilder {
	that.Join(Map{"[<>]" + table: joinStr})
	return that
}

func (that *HotimeDBBuilder) Join(qu ...interface{}) *HotimeDBBuilder {
	lth := len(qu)
	if lth == 0 {
		return that
	}
	data := Map{}
	if lth == 1 {
		data = ObjToMap(qu[0])
	}
	if lth > 1 {
		for k := 1; k < lth; k++ {
			data[ObjToStr(qu[k-1])] = qu[k]
			k++
		}
	}

	if that.join == nil {
		that.join = Slice{}
	}
	if data == nil {
		return that
	}

	that.join = append(that.join, data)
	return that
}

func (that *HotimeDBBuilder) And(qu ...interface{}) *HotimeDBBuilder {

	lth := len(qu)
	if lth == 0 {
		return that
	}
	var where Map
	if lth == 1 {
		where = ObjToMap(qu[0])
	}
	if lth > 1 {
		where = Map{}
		for k := 1; k < lth; k++ {
			where[ObjToStr(qu[k-1])] = qu[k]
			k++
		}
	}

	if where == nil {
		return that
	}

	if that.lastWhere != nil {
		that.lastWhere["AND"] = where
		that.lastWhere = where
		return that
	}
	that.lastWhere = where
	that.where = Map{"AND": where}

	return that
}

func (that *HotimeDBBuilder) Or(qu ...interface{}) *HotimeDBBuilder {
	lth := len(qu)
	if lth == 0 {
		return that
	}
	var where Map
	if lth == 1 {
		where = ObjToMap(qu[0])
	}
	if lth > 1 {
		where = Map{}
		for k := 1; k < lth; k++ {
			where[ObjToStr(qu[k-1])] = qu[k]
			k++
		}
	}
	if where == nil {
		return that
	}

	if that.lastWhere != nil {
		that.lastWhere["OR"] = where
		that.lastWhere = where
		return that
	}
	that.lastWhere = where
	that.where = Map{"Or": where}

	return that
}
func (that *HotimeDBBuilder) Where(qu ...interface{}) *HotimeDBBuilder {

	lth := len(qu)
	if lth == 0 {
		return that
	}
	var where Map
	if lth == 1 {
		where = ObjToMap(qu[0])
	}
	if lth > 1 {
		where = Map{}
		for k := 1; k < lth; k++ {
			where[ObjToStr(qu[k-1])] = qu[k]
			k++
		}
	}

	if where == nil {
		return that
	}

	if that.lastWhere != nil {
		that.lastWhere["AND"] = where
		that.lastWhere = where
		return that
	}
	that.lastWhere = where
	that.where = Map{"AND": that.lastWhere}

	return that
}

func (that *HotimeDBBuilder) From(table string) *HotimeDBBuilder {
	that.table = table
	return that
}

func (that *HotimeDBBuilder) Order(qu ...interface{}) *HotimeDBBuilder {
	that.where["ORDER"] = ObjToSlice(qu)
	return that
}
func (that *HotimeDBBuilder) Limit(qu ...interface{}) *HotimeDBBuilder {
	that.where["LIMIT"] = ObjToSlice(qu)
	return that
}

func (that *HotimeDBBuilder) Group(qu ...interface{}) *HotimeDBBuilder {
	that.where["GROUP"] = ObjToSlice(qu)
	return that
}

// SetConnect 设置数据库配置连接
func (that *HoTimeDB) SetConnect(connect func(err ...*Error) (master, slave *sql.DB), err ...*Error) {
	that.ConnectFunc = connect
	_ = that.InitDb(err...)

}

// GetType 设置数据库配置连接
func (that *HoTimeDB) GetType() string {
	return that.Type
}

// Action 事务，如果action返回true则执行成功；false则回滚
func (that *HoTimeDB) Action(action func(db HoTimeDB) (isSuccess bool)) (isSuccess bool) {
	db := HoTimeDB{that.DB, that.ContextBase, that.DBName,
		that.HoTimeCache, that.Log, that.Type,
		that.Prefix, that.LastQuery, that.LastData,
		that.ConnectFunc, that.LastErr, that.limit, that.Tx,
		that.SlaveDB, that.Mode}
	tx, err := db.Begin()
	if err != nil {
		that.LastErr.SetError(err)
		return isSuccess
	}

	db.Tx = tx

	isSuccess = action(db)

	if !isSuccess {
		err = db.Tx.Rollback()
		if err != nil {
			that.LastErr.SetError(err)
			return isSuccess
		}
		return isSuccess
	}
	err = db.Tx.Commit()
	if err != nil {
		that.LastErr.SetError(err)
		return false
	}
	return true
}

func (that *HoTimeDB) InitDb(err ...*Error) *Error {
	if len(err) != 0 {
		that.LastErr = err[0]
	}
	that.DB, that.SlaveDB = that.ConnectFunc(that.LastErr)
	if that.DB == nil {
		return that.LastErr
	}
	e := that.DB.Ping()

	that.LastErr.SetError(e)

	if that.SlaveDB != nil {
		e := that.SlaveDB.Ping()
		that.LastErr.SetError(e)
	}
	//that.DB.SetConnMaxLifetime(time.Second)
	return that.LastErr
}

func (that *HoTimeDB) Page(page, pageRow int) *HoTimeDB {
	page = (page - 1) * pageRow
	if page < 0 {
		page = 1
	}

	that.limit = Slice{page, pageRow}
	return that
}

func (that *HoTimeDB) PageSelect(table string, qu ...interface{}) []Map {

	if len(qu) == 1 {
		qu = append(qu, Map{"LIMIT": that.limit})
	}
	if len(qu) == 2 {
		temp := DeepCopyMap(qu[1]).(Map)
		temp["LIMIT"] = that.limit
		qu[1] = temp
	}
	if len(qu) == 3 {
		temp := DeepCopyMap(qu[2]).(Map)
		temp["LIMIT"] = that.limit
		qu[2] = temp
	}
	//fmt.Println(qu)
	data := that.Select(table, qu...)

	return data
}

// Row 数据库数据解析
func (that *HoTimeDB) Row(resl *sql.Rows) []Map {
	dest := make([]Map, 0)
	strs, _ := resl.Columns()

	for i := 0; resl.Next(); i++ {
		lis := make(Map, 0)
		a := make([]interface{}, len(strs))

		b := make([]interface{}, len(a))
		for j := 0; j < len(a); j++ {
			b[j] = &a[j]
		}
		err := resl.Scan(b...)
		if err != nil {
			that.LastErr.SetError(err)
			return nil
		}
		for j := 0; j < len(a); j++ {
			//fmt.Println(reflect.ValueOf(a[j]).Type().String() )
			if a[j] != nil && reflect.ValueOf(a[j]).Type().String() == "[]uint8" {
				lis[strs[j]] = string(a[j].([]byte))
			} else {
				lis[strs[j]] = a[j] //取实际类型
			}

		}
		//防止int被误读为float64
		//jlis, e := json.Marshal(lis)
		//if e != nil {
		//	that.LastErr.SetError(e)
		//} else {
		//	lis.JsonToMap(string(jlis), that.LastErr)
		//}

		dest = append(dest, lis)

	}

	return dest
}

//
////code=0,1,2 0 backup all,1 backup data,2 backup ddl
//func (that *HoTimeDB) Backup(path string, code int) {
//	var cmd *exec.Cmd
//	switch code {
//	case 0:cmd= exec.Command("mysqldump","-h"+ObjToStr(Config["dbHost"]), "-P"+ObjToStr(Config["dbPort"]),"-u"+ObjToStr(Config["dbUser"]), "-p"+ObjToStr(Config["dbPwd"]),ObjToStr(Config["dbName"]))
//	case 1:cmd= exec.Command("mysqldump","-h"+ObjToStr(Config["dbHost"]), "-P"+ObjToStr(Config["dbPort"]),"-u"+ObjToStr(Config["dbUser"]), "-p"+ObjToStr(Config["dbPwd"]), ObjToStr(Config["dbName"]))
//	case 2:cmd= exec.Command("mysqldump","--no-data","-h"+ObjToStr(Config["dbHost"]), "-P"+ObjToStr(Config["dbPort"]),"-u"+ObjToStr(Config["dbUser"]), "-p"+ObjToStr(Config["dbPwd"]),ObjToStr(Config["dbName"]))
//	}
//
//
//
//	stdout, err := cmd.StdoutPipe()
//	if err != nil {
//		log.Println(err)
//	}
//
//	if err := cmd.Start(); err != nil {
//		log.Println(err)
//	}
//
//	bytes, err := ioutil.ReadAll(stdout)
//	if err != nil {
//		log.Println(err)
//	}
//	err = ioutil.WriteFile(path, bytes, 0644)
//	if err != nil {
//		panic(err)
//	}
//	return ;
//	//
//	//db := ``
//	//fmt.Println(db)
//	//
//	//tables := that.Query("show tables")
//	//lth := len(tables)
//	//if lth == 0 {
//	//	return
//	//}
//	//for k, _ := range tables[0] {
//	//	db = Substr(k, 10, len(k))
//	//}
//	//
//	//fd, _ := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_APPEND, 0644)
//	//fd.Write([]byte("/*datetime " + time.Now().Format("2006-01-02 15:04:05") + "  */ \r\n"))
//	//fd.Close()
//	//
//	//for i := 0; i < lth; i++ {
//	//	tt := tables[i]["Tables_in_"+db].(string)
//	//	that.backupSave(path, tt, code)
//	//	debug.FreeOSMemory()
//	//}
//
//}

func (that *HoTimeDB) backupSave(path string, tt string, code int) {
	fd, _ := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_APPEND, 0644)
	defer fd.Close()

	str := "\r\n"
	if code == 0 || code == 2 {
		str += that.backupDdl(tt)
	}

	if code == 0 || code == 1 {
		str += "insert  into `" + tt + "`\r\n\r\n("
		str += that.backupCol(tt)
	}

	_, _ = fd.Write([]byte(str))
}
func (that *HoTimeDB) backupDdl(tt string) string {

	data := that.Query("show create table " + tt)
	if len(data) == 0 {
		return ""
	}

	return ObjToStr(data[0]["Create Table"]) + ";\r\n\r\n"
}

func (that *HoTimeDB) backupCol(tt string) string {
	str := ""
	data := that.Select(tt, "*")

	lthData := len(data)

	if lthData == 0 {
		return str
	}
	lthCol := len(data[0])
	col := make([]string, lthCol)
	tempLthData := 0
	for k := range data[0] {

		if tempLthData == lthCol-1 {
			str += "`" + k + "`) "
		} else {
			str += "`" + k + "`,"
		}
		col[tempLthData] = k
		tempLthData++
	}

	str += " values"

	for j := 0; j < lthData; j++ {

		for m := 0; m < lthCol; m++ {

			if m == 0 {
				str += "("
			}

			v := "NULL"
			if data[j][col[m]] != nil {
				v = "'" + strings.Replace(ObjToStr(data[j][col[m]]), "'", `\'`, -1) + "'"
			}

			if m == lthCol-1 {
				str += v + ") "

			} else {
				str += v + ","
			}
		}
		if j == lthData-1 {
			str += ";\r\n\r\n"
		} else {
			str += ",\r\n\r\n"
		}
	}
	return str
}

func (that *HoTimeDB) md5(query string, args ...interface{}) string {
	strByte, _ := json.Marshal(args)
	str := Md5(query + ":" + string(strByte))
	return str
}

func (that *HoTimeDB) Query(query string, args ...interface{}) []Map {
	defer func() {
		if that.Mode != 0 {
			that.Log.Info("SQL:"+that.LastQuery, " DATA:", that.LastData, " ERROR:", that.LastErr.GetError())
		}
	}()
	//fmt.Println(query)
	var err error
	var resl *sql.Rows

	that.LastQuery = query
	that.LastData = args
	//主从数据库切换，只有select语句有从数据库
	db := that.DB
	if that.SlaveDB != nil {
		db = that.SlaveDB
	}

	if db == nil {
		err = errors.New("没有初始化数据库")
		that.LastErr.SetError(err)
		return nil
	}

	if that.Tx != nil {
		resl, err = that.Tx.Query(query, args...)
	} else {
		resl, err = db.Query(query, args...)
	}

	if err != nil && that.LastErr.GetError() != nil &&
		that.LastErr.GetError().Error() == err.Error() {
		return nil
	}

	that.LastErr.SetError(err)
	if err != nil {

		if err = db.Ping(); err == nil {
			return that.Query(query, args...)
		}
		that.LastErr.SetError(err)
		return nil
	}

	return that.Row(resl)
}

func (that *HoTimeDB) Exec(query string, args ...interface{}) (sql.Result, *Error) {
	defer func() {
		if that.Mode != 0 {
			that.Log.Info("SQL: "+that.LastQuery, " DATA: ", that.LastData, " ERROR: ", that.LastErr.GetError())
		}
	}()
	that.LastQuery = query
	that.LastData = args
	var e error
	var resl sql.Result

	if that.DB == nil {
		err := errors.New("没有初始化数据库")
		that.LastErr.SetError(err)
		return nil, that.LastErr
	}

	if that.Tx != nil {
		resl, e = that.Tx.Exec(query, args...)
	} else {
		resl, e = that.DB.Exec(query, args...)
	}

	if e != nil && that.LastErr.GetError() != nil &&
		that.LastErr.GetError().Error() == e.Error() {
		return resl, that.LastErr
	}
	that.LastErr.SetError(e)
	//判断是否连接断开了
	if e != nil {

		if e = that.DB.Ping(); e == nil {
			return that.Exec(query, args...)
		}
		that.LastErr.SetError(e)
		return resl, that.LastErr
	}

	return resl, that.LastErr
}

//func (that *HoTimeDB)copy(data []Map)[]Map{
//	if data==nil{
//		return nil
//	}
//
//	lth:=len(data)
//
//	res:=make([]Map,lth)
//
//
//	for i:=0;i<lth;i++{
//
//		res[i]=DeepCopy(data[i]).(Map)
//	}
//
//	return res
//
//}

func (that *HoTimeDB) Select(table string, qu ...interface{}) []Map {

	query := "SELECT"
	where := Map{}
	qs := make([]interface{}, 0)
	intQs, intWhere := 0, 1
	join := false
	if len(qu) == 3 {
		intQs = 1
		intWhere = 2
		join = true

	}

	if len(qu) > 0 {
		if reflect.ValueOf(qu[intQs]).Type().String() == "string" {
			query += " " + qu[intQs].(string)
		} else {
			data := ObjToSlice(qu[intQs])
			for i := 0; i < len(data); i++ {
				k := data.GetString(i)
				if strings.Contains(k, " AS ") ||
					strings.Contains(k, ".") {

					query += " " + k + " "

				} else {

					query += " `" + k + "` "
				}

				if i+1 != len(data) {
					query = query + ", "
				}

			}
		}

	} else {
		query += " *"
	}
	if !strings.Contains(table, ".") && !strings.Contains(table, " AS ") {
		query += " FROM `" + that.Prefix + table + "` "
	} else {
		query += " FROM " + that.Prefix + table + " "
	}

	if join {
		var testQu = []string{}
		testQuData := Map{}
		if reflect.ValueOf(qu[0]).Type().String() == "common.Map" {
			testQuData = qu[0].(Map)
			for key, _ := range testQuData {
				//fmt.Println(key, ":", value)
				testQu = append(testQu, key)
			}
		}

		if reflect.ValueOf(qu[0]).Type().String() == "common.Slice" || strings.Contains(reflect.ValueOf(qu[0]).Type().String(), "[]") {
			qu0 := ObjToSlice(qu[0])
			for key, _ := range qu0 {
				v := qu0.GetMap(key)
				for k1, v1 := range v {
					testQu = append(testQu, k1)
					testQuData[k1] = v1
				}
			}
		}

		sort.Strings(testQu)

		for _, k := range testQu {
			v := testQuData[k]
			switch Substr(k, 0, 3) {
			case "[>]":
				func() {
					table := Substr(k, 3, len(k)-3)
					if !strings.Contains(table, " ") {
						table = "`" + table + "`"
					}
					query += " LEFT JOIN " + table + " ON " + v.(string) + " "
				}()
			case "[<]":
				func() {
					table := Substr(k, 3, len(k)-3)
					if !strings.Contains(table, " ") {
						table = "`" + table + "`"
					}
					query += " RIGHT JOIN " + table + " ON " + v.(string) + " "
				}()

			}
			switch Substr(k, 0, 4) {
			case "[<>]":
				func() {
					table := Substr(k, 4, len(k)-4)
					if !strings.Contains(table, " ") {
						table = "`" + table + "`"
					}
					query += " FULL JOIN " + table + " ON " + v.(string) + " "
				}()

			case "[><]":
				func() {
					table := Substr(k, 4, len(k)-4)
					if !strings.Contains(table, " ") {
						table = "`" + table + "`"
					}
					query += " INNER JOIN " + table + " ON " + v.(string) + " "
				}()

			}
		}
	}

	if len(qu) > 1 {
		where = qu[intWhere].(Map)
	}

	temp, resWhere := that.where(where)

	query += temp + ";"
	qs = append(qs, resWhere...)
	md5 := that.md5(query, qs...)

	if that.HoTimeCache != nil && table != "cached" {
		//如果缓存有则从缓存取
		cacheData := that.HoTimeCache.Db(table + ":" + md5)

		if cacheData != nil && cacheData.Data != nil {
			return cacheData.ToMapArray()
		}
	}

	//无缓存则数据库取
	res := that.Query(query, qs...)

	if res == nil {
		res = []Map{}
	}

	//缓存
	if that.HoTimeCache != nil && table != "cached" {

		_ = that.HoTimeCache.Db(table+":"+md5, res)
	}

	return res

}

func (that *HoTimeDB) Get(table string, qu ...interface{}) Map {
	//fmt.Println(qu)
	if len(qu) == 1 {
		qu = append(qu, Map{"LIMIT": 1})
	}
	if len(qu) == 2 {
		temp := qu[1].(Map)
		temp["LIMIT"] = 1
		qu[1] = temp
	}
	if len(qu) == 3 {
		temp := qu[2].(Map)
		temp["LIMIT"] = 1
		qu[2] = temp
	}
	//fmt.Println(qu)
	data := that.Select(table, qu...)
	if len(data) == 0 {
		return nil
	}
	return data[0]
}
func (that *HoTimeDB) GetPrefix() string {
	return that.Prefix
}

// Count 计数
func (that *HoTimeDB) Count(table string, qu ...interface{}) int {
	var req = []interface{}{}
	if len(qu) == 2 {
		req = append(req, qu[0])
		req = append(req, "COUNT(*)")
		req = append(req, qu[1])
	} else {
		req = append(req, "COUNT(*)")
		req = append(req, qu...)
	}

	//req=append(req,qu...)
	data := that.Select(table, req...)
	//fmt.Println(data)
	if len(data) == 0 {
		return 0
	}
	//res,_:=StrToInt(data[0]["COUNT(*)"].(string))
	res := ObjToStr(data[0]["COUNT(*)"])
	count, _ := StrToInt(res)
	return count

}

var condition = []string{"AND", "OR"}
var vcond = []string{"GROUP", "ORDER", "LIMIT"}

// Count 计数
func (that *HoTimeDB) Sum(table string, column string, qu ...interface{}) float64 {
	var req = []interface{}{}
	if len(qu) == 2 {
		req = append(req, qu[0])
		req = append(req, "SUM("+column+")")
		req = append(req, qu[1])
	} else {
		req = append(req, "SUM("+column+")")
		req = append(req, qu...)
	}

	//req=append(req,qu...)
	data := that.Select(table, req...)
	//fmt.Println(data)
	if len(data) == 0 {
		return 0
	}
	//res,_:=StrToInt(data[0]["COUNT(*)"].(string))
	res := ObjToStr(data[0]["SUM("+column+")"])
	count := ObjToFloat64(res)
	return count

}

//where语句解析
func (that *HoTimeDB) where(data Map) (string, []interface{}) {

	where := ""

	res := make([]interface{}, 0)
	//AND OR判断
	testQu := []string{}
	//testQuData:= qu[0].(Map)
	for key, _ := range data {
		//fmt.Println(key, ":", value)
		testQu = append(testQu, key)
	}
	sort.Strings(testQu)
	for _, k := range testQu {
		v := data[k]
		x := 0
		for i := 0; i < len(condition); i++ {

			if condition[i] == k {

				tw, ts := that.cond(k, v.(Map))

				where += tw
				res = append(res, ts...)
				break
			}
			x++
		}

		y := 0
		for j := 0; j < len(vcond); j++ {

			if vcond[j] == k {
				break
			}
			y++
		}
		if x == len(condition) && y == len(vcond) {

			if v != nil && reflect.ValueOf(v).Type().String() == "common.Slice" && len(v.(Slice)) == 0 {
				continue
			}
			if v != nil && strings.Contains(reflect.ValueOf(v).Type().String(), "[]") && len(ObjToSlice(v)) == 0 {
				continue
			}

			tv, vv := that.varCond(k, v)

			where += tv
			res = append(res, vv...)
		}

	}

	if len(where) != 0 {
		hasWhere := true
		for _, v := range vcond {
			if strings.Index(where, v) == 0 {
				hasWhere = false
			}
		}

		if hasWhere {
			where = " WHERE " + where + " "
		}
	}

	//特殊字符
	for j := 0; j < len(vcond); j++ {
		testQu := []string{}
		//testQuData:= qu[0].(Map)
		for key, _ := range data {
			//fmt.Println(key, ":", value)
			testQu = append(testQu, key)
		}
		sort.Strings(testQu)
		for _, k := range testQu {
			v := data[k]
			if vcond[j] == k {
				if k == "ORDER" {
					where += k + " BY "
					//fmt.Println(reflect.ValueOf(v).Type())

					//break
				} else if k == "GROUP" {

					where += k + " BY "
				} else {

					where += k
				}

				if reflect.ValueOf(v).Type().String() == "common.Slice" || strings.Contains(reflect.ValueOf(v).Type().String(), "[]") {
					vs := ObjToSlice(v)
					for i := 0; i < len(vs); i++ {
						where += " " + vs.GetString(i) + " "

						if len(vs) != i+1 {
							where += ", "
						}

					}

				} else {
					//fmt.Println(v)
					where += " " + ObjToStr(v) + " "
				}

				break
			}
		}

	}

	return where, res

}

func (that *HoTimeDB) varCond(k string, v interface{}) (string, []interface{}) {

	where := ""
	res := make([]interface{}, 0)
	length := len(k)
	if k == "[#]" {
		k = strings.Replace(k, "[#]", "", -1)
		where += " " + ObjToStr(v) + " "
	} else if length > 0 && strings.Contains(k, "[") && k[length-1] == ']' {
		def := false

		switch Substr(k, length-3, 3) {
		case "[>]":
			k = strings.Replace(k, "[>]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + ">? "
			res = append(res, v)
		case "[<]":
			k = strings.Replace(k, "[<]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + "<? "
			res = append(res, v)
		case "[!]":
			k = strings.Replace(k, "[!]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where, res = that.notIn(k, v, where, res)
		case "[#]":
			k = strings.Replace(k, "[#]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += " " + k + "=" + ObjToStr(v) + " "
		case "[##]": //直接添加value到sql，需要考虑防注入，value比如："a>b"

			where += " " + ObjToStr(v)
		case "[#!]":
			k = strings.Replace(k, "[#!]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += " " + k + "!=" + ObjToStr(v) + " "
		case "[!#]":
			k = strings.Replace(k, "[!#]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += " " + k + "!=" + ObjToStr(v) + " "
		case "[~]":
			k = strings.Replace(k, "[~]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + " LIKE ? "
			v = "%" + ObjToStr(v) + "%"
			res = append(res, v)
		case "[!~]": //左边任意
			k = strings.Replace(k, "[!~]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + " LIKE ? "
			v = "%" + ObjToStr(v) + ""
			res = append(res, v)
		case "[~!]": //右边任意
			k = strings.Replace(k, "[~!]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + " LIKE ? "
			v = ObjToStr(v) + "%"
			res = append(res, v)
		case "[~~]": //手动任意
			k = strings.Replace(k, "[~]", "", -1)
			if !strings.Contains(k, ".") {
				k = "`" + k + "` "
			}
			where += k + " LIKE ? "
			//v = ObjToStr(v)
			res = append(res, v)
		default:
			def = true

		}
		if def {
			switch Substr(k, length-4, 4) {
			case "[>=]":
				k = strings.Replace(k, "[>=]", "", -1)
				if !strings.Contains(k, ".") {
					k = "`" + k + "` "
				}
				where += k + ">=? "
				res = append(res, v)
			case "[<=]":
				k = strings.Replace(k, "[<=]", "", -1)
				if !strings.Contains(k, ".") {
					k = "`" + k + "` "
				}
				where += k + "<=? "
				res = append(res, v)
			case "[><]":
				k = strings.Replace(k, "[><]", "", -1)
				if !strings.Contains(k, ".") {
					k = "`" + k + "` "
				}
				where += k + " NOT BETWEEN ? AND ? "
				vs := ObjToSlice(v)
				res = append(res, vs[0])
				res = append(res, vs[1])
			case "[<>]":
				k = strings.Replace(k, "[<>]", "", -1)
				if !strings.Contains(k, ".") {
					k = "`" + k + "` "
				}
				where += k + " BETWEEN ? AND ? "
				vs := ObjToSlice(v)
				res = append(res, vs[0])
				res = append(res, vs[1])
			default:
				if !strings.Contains(k, ".") {
					k = "`" + k + "` "
				}
				if reflect.ValueOf(v).Type().String() == "common.Slice" || strings.Contains(reflect.ValueOf(v).Type().String(), "[]") {
					vs := ObjToSlice(v)
					if len(vs) == 0 {
						return where, res
					}

					if len(vs) == 1 {
						where += k + "=? "
						res = append(res, vs[0])
						return where, res
					}

					min := int64(0)
					isMin := true
					IsRange := true
					num := int64(0)
					isNum := true

					where1 := ""
					res1 := Slice{}
					where2 := k + " IN ("
					res2 := Slice{}
					for kvs := 0; kvs <= len(vs); kvs++ {
						vsv := int64(0)
						if kvs < len(vs) {
							vsv = vs.GetCeilInt64(kvs)
							//确保是全部是int类型
							if ObjToStr(vsv) != vs.GetString(kvs) {
								IsRange = false
								break
							}
						}

						if isNum {
							isNum = false
							num = vsv
						} else {
							num++
						}
						if isMin {
							isMin = false
							min = vsv
						}
						//不等于则到了分路口
						if num != vsv {
							//between
							if num-min > 1 {
								if where1 != "" {
									where1 += " OR " + k + " BETWEEN ? AND ? "
								} else {
									where1 += k + " BETWEEN ? AND ? "
								}
								res1 = append(res1, min)
								res1 = append(res1, num-1)
							} else {
								//如果就前进一步就用OR
								where2 += "?,"
								res2 = append(res2, min)
							}
							min = vsv
							num = vsv
						}

					}

					if IsRange {

						where3 := ""

						if where1 != "" {
							where3 += where1
							res = append(res, res1...)
						}

						if len(res2) == 1 {
							if where3 == "" {
								where3 += k + " = ? "
							} else {
								where3 += " OR " + k + " = ? "
							}
							res = append(res, res2...)
						} else if len(res2) > 1 {
							where2 = where2[:len(where2)-1]
							if where3 == "" {
								where3 += where2 + ")"
							} else {
								where3 += " OR " + where2 + ")"
							}
							res = append(res, res2...)
						}

						if where3 != "" {
							where += "(" + where3 + ")"
						}

						return where, res
					}

					where += k + " IN ("
					res = append(res, vs...)

					for i := 0; i < len(vs); i++ {
						if i+1 != len(vs) {
							where += "?,"
						} else {
							where += "?) "
						}
						//res=append(res,(v.(Slice))[i])
					}

				} else {
					where += k + "=? "
					res = append(res, v)
				}

			}
		}

	} else {
		//fmt.Println(reflect.ValueOf(v).Type().String())
		if !strings.Contains(k, ".") {
			k = "`" + k + "` "
		}
		if v == nil {
			where += k + " IS NULL "
		} else if reflect.ValueOf(v).Type().String() == "common.Slice" || strings.Contains(reflect.ValueOf(v).Type().String(), "[]") {
			vs := ObjToSlice(v)
			//fmt.Println(v)
			if len(vs) == 0 {
				return where, res
			}

			if len(vs) == 1 {
				where += k + "=? "
				res = append(res, vs[0])
				return where, res
			}

			//In转betwin和IN，提升部分性能
			min := int64(0)
			isMin := true
			IsRange := true
			num := int64(0)
			isNum := true

			where1 := ""
			res1 := Slice{}
			where2 := k + " IN ("
			res2 := Slice{}
			for kvs := 0; kvs <= len(vs); kvs++ {
				vsv := int64(0)
				if kvs < len(vs) {
					vsv = vs.GetCeilInt64(kvs)
					//确保是全部是int类型
					if ObjToStr(vsv) != vs.GetString(kvs) {
						IsRange = false
						break
					}
				}

				if isNum {
					isNum = false
					num = vsv
				} else {
					num++
				}
				if isMin {
					isMin = false
					min = vsv
				}
				//不等于则到了分路口
				if num != vsv {
					//between
					if num-min > 1 {
						if where1 != "" {
							where1 += " OR " + k + " BETWEEN ? AND ? "
						} else {
							where1 += k + " BETWEEN ? AND ? "
						}
						res1 = append(res1, min)
						res1 = append(res1, num-1)
					} else {
						//如果就前进一步就用OR
						where2 += "?,"
						res2 = append(res2, min)
					}
					min = vsv
					num = vsv
				}

			}

			if IsRange {

				where3 := ""

				if where1 != "" {
					where3 += where1
					res = append(res, res1...)
				}

				if len(res2) == 1 {
					if where3 == "" {
						where3 += k + " = ? "
					} else {
						where3 += " OR " + k + " = ? "
					}
					res = append(res, res2...)
				} else if len(res2) > 1 {
					where2 = where2[:len(where2)-1]
					if where3 == "" {
						where3 += where2 + ")"
					} else {
						where3 += " OR " + where2 + ")"
					}
					res = append(res, res2...)
				}

				if where3 != "" {
					where += "(" + where3 + ")"
				}

				return where, res
			}

			where += k + " IN ("
			res = append(res, vs...)

			for i := 0; i < len(vs); i++ {
				if i+1 != len(vs) {
					where += "?,"
				} else {
					where += "?) "
				}
				//res=append(res,(v.(Slice))[i])
			}

		} else {

			where += k + "=? "
			res = append(res, v)

		}
	}

	return where, res
}

//	that.Db.Update("user",hotime.Map{"ustate":"1"},hotime.Map{"AND":hotime.Map{"OR":hotime.Map{"uid":4,"uname":"dasda"}},"ustate":1})
func (that *HoTimeDB) notIn(k string, v interface{}, where string, res []interface{}) (string, []interface{}) {
	//where:=""
	//fmt.Println(reflect.ValueOf(v).Type().String())
	if v == nil {

		where += k + " IS NOT NULL "

	} else if reflect.ValueOf(v).Type().String() == "common.Slice" || strings.Contains(reflect.ValueOf(v).Type().String(), "[]") {
		vs := ObjToSlice(v)
		if len(vs) == 0 {
			return where, res
		}
		where += k + " NOT IN ("
		res = append(res, vs...)

		for i := 0; i < len(vs); i++ {
			if i+1 != len(vs) {
				where += "?,"
			} else {
				where += "?) "
			}
			//res=append(res,(v.(Slice))[i])
		}

	} else {

		where += k + " !=? "
		res = append(res, v)

	}

	return where, res
}

func (that *HoTimeDB) cond(tag string, data Map) (string, []interface{}) {
	where := " "
	res := make([]interface{}, 0)
	lens := len(data)
	//fmt.Println(lens)
	testQu := []string{}
	//testQuData:= qu[0].(Map)
	for key, _ := range data {
		//fmt.Println(key, ":", value)
		testQu = append(testQu, key)
	}
	sort.Strings(testQu)
	for _, k := range testQu {
		v := data[k]
		x := 0
		for i := 0; i < len(condition); i++ {

			if condition[i] == k {

				tw, ts := that.cond(k, v.(Map))
				if lens--; lens <= 0 {
					//fmt.Println(lens)
					where += "(" + tw + ") "
				} else {
					where += "(" + tw + ") " + tag + " "
				}

				res = append(res, ts...)
				break
			}
			x++
		}

		if x == len(condition) {

			tv, vv := that.varCond(k, v)
			if tv == "" {
				lens--
				continue
			}
			res = append(res, vv...)
			if lens--; lens <= 0 {
				where += tv + ""
			} else {
				where += tv + " " + tag + " "
			}
		}

	}

	return where, res
}

// Update 更新数据
func (that *HoTimeDB) Update(table string, data Map, where Map) int64 {

	query := "UPDATE `" + that.Prefix + table + "` SET "
	//UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
	qs := make([]interface{}, 0)
	tp := len(data)

	for k, v := range data {
		vstr := "?"
		if Substr(k, len(k)-3, 3) == "[#]" {
			k = strings.Replace(k, "[#]", "", -1)

			vstr = ObjToStr(v)
		} else {
			qs = append(qs, v)
		}
		query += "`" + k + "`=" + vstr + " "
		if tp--; tp != 0 {
			query += ", "
		}
	}

	temp, resWhere := that.where(where)
	//fmt.Println(resWhere)

	query += temp + ";"
	qs = append(qs, resWhere...)

	res, err := that.Exec(query, qs...)

	rows := int64(0)
	if err.GetError() == nil && res != nil {
		rows, _ = res.RowsAffected()
	}

	//如果更新成功，则删除缓存
	if rows != 0 {
		if that.HoTimeCache != nil && table != "cached" {
			_ = that.HoTimeCache.Db(table+"*", nil)
		}
	}

	return rows
}

func (that *HoTimeDB) Delete(table string, data map[string]interface{}) int64 {

	query := "DELETE FROM " + that.Prefix + table + " "

	temp, resWhere := that.where(data)
	query += temp + ";"

	res, err := that.Exec(query, resWhere...)
	rows := int64(0)
	if err.GetError() == nil && res != nil {
		rows, _ = res.RowsAffected()
	}

	//如果删除成功,删除对应缓存
	if rows != 0 {
		if that.HoTimeCache != nil && table != "cached" {
			_ = that.HoTimeCache.Db(table+"*", nil)
		}
	}
	//return 0

	return rows
}

// Insert 插入新数据
func (that *HoTimeDB) Insert(table string, data map[string]interface{}) int64 {

	values := make([]interface{}, 0)
	queryString := " ("
	valueString := " ("

	lens := len(data)
	tempLen := 0
	for k, v := range data {
		tempLen++

		vstr := "?"
		if Substr(k, len(k)-3, 3) == "[#]" {
			k = strings.Replace(k, "[#]", "", -1)

			vstr = ObjToStr(v)
			if tempLen < lens {
				queryString += "`" + k + "`,"
				valueString += vstr + ","
			} else {
				queryString += "`" + k + "`) "
				valueString += vstr + ");"
			}
		} else {

			values = append(values, v)
			if tempLen < lens {
				queryString += "`" + k + "`,"
				valueString += "?,"
			} else {
				queryString += "`" + k + "`) "
				valueString += "?);"
			}
		}

	}
	query := "INSERT INTO `" + that.Prefix + table + "` " + queryString + "VALUES" + valueString

	res, err := that.Exec(query, values...)

	id := int64(0)
	if err.GetError() == nil && res != nil {
		id1, err := res.LastInsertId()
		that.LastErr.SetError(err)
		id = id1
	}

	//如果插入成功，删除缓存
	if id != 0 {
		if that.HoTimeCache != nil && table != "cached" {
			_ = that.HoTimeCache.Db(table+"*", nil)
		}
	}

	//fmt.Println(id)
	return id
}