package z1mongo

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"strconv"
	"strings"

	"gitee.com/myzero1/gotool/z1err"
	"github.com/blastrain/vitess-sqlparser/sqlparser"
	"go.mongodb.org/mongo-driver/bson"
)

// https://github.com/shan-chen/sql2mongo/blob/master/parse.go
func Sql2Mongo(sql string, z1count bool, ret ...interface{}) (result []bson.M, total int64, action string, err error) {
	defer z1err.Handle(&err)

	sqlInfo, err := ParsingSQL(sql)
	z1err.Check(err)

	// log.Println(`-----Sql2Mongo-------`, sqlInfo)

	switch sqlInfo[`action`] {
	case `insert`:
		action = `insert`
		collection := DB.Collection(sqlInfo[`insert_table`])
		var rows []interface{}

		err := json.Unmarshal([]byte(sqlInfo[`insert_rows`]), &rows)
		z1err.Check(err)

		{
			for i, row := range rows {
				rowrowMap := row.(map[string]interface{})
				for k, v := range rowrowMap {
					v1 := fmt.Sprintf(`%v`, v)
					if strings.HasPrefix(v1, `'`) {
						rowrowMap[k] = strings.Trim(v1, `'`)
					} else {
						if strings.Contains(v1, `.`) {
							i, err := strconv.ParseFloat(v1, 64)
							z1err.Check(err)
							rowrowMap[k] = i
						} else {
							i, err := strconv.ParseInt(v1, 10, 64)
							z1err.Check(err)
							rowrowMap[k] = i
						}
					}
				}
				rows[i] = rowrowMap
			}
		}

		insertManyResult, err := collection.InsertMany(context.TODO(), rows)
		z1err.Check(err)
		total = int64(len(insertManyResult.InsertedIDs))

	case `delete`:
		action = `delete`
		collection := DB.Collection(sqlInfo[`delete_table`])
		var where interface{}
		// err := json.Unmarshal([]byte(sqlInfo[`delete_where`]), &where)
		// z1err.Check(err)
		decoder := json.NewDecoder(strings.NewReader(sqlInfo[`delete_where`]))
		decoder.UseNumber()
		err = decoder.Decode(&where)
		z1err.Check(err)

		deleteResult, err := collection.DeleteMany(context.Background(), where)
		z1err.Check(err)
		total = deleteResult.DeletedCount
	case `drop`:
		action = `drop`
		collection := DB.Collection(sqlInfo[`drop_table`])
		err = collection.Drop(context.Background())
	case `update`:
		action = `update`
		collection := DB.Collection(sqlInfo[`update_table`])

		var where interface{}
		// err := json.Unmarshal([]byte(sqlInfo[`update_where`]), &where)
		// z1err.Check(err)
		decoder := json.NewDecoder(strings.NewReader(sqlInfo[`update_where`]))
		decoder.UseNumber()
		err = decoder.Decode(&where)
		z1err.Check(err)

		var set bson.D
		err = bson.UnmarshalExtJSON([]byte(sqlInfo[`update_set`]), false, &set)
		z1err.Check(err)

		{
			for i, v := range set {
				v1 := fmt.Sprintf(`%v`, v.Value)
				if strings.HasPrefix(v1, `'`) {
					v.Value = strings.Trim(v1, `'`)
				} else {
					if strings.Contains(v1, `.`) {
						i, err := strconv.ParseFloat(v1, 64)
						z1err.Check(err)
						v.Value = i
					} else {
						i, err := strconv.ParseInt(v1, 10, 64)
						z1err.Check(err)
						v.Value = i
					}
				}

				set[i] = v
			}
		}

		setD := bson.D{
			{
				"$set",
				set,
			},
		}

		updateResult, err := collection.UpdateMany(context.Background(), where, setD)
		z1err.Check(err)

		total = updateResult.ModifiedCount

	case `select`:
		action = `select`
		var pipeline []bson.D

		left := sqlInfo[`left`]
		_, ok := sqlInfo[`right`]
		// join
		{

			if ok {
				joinCount, err := CheckJoin(sqlInfo[`on_str`], sqlInfo[`left`])
				z1err.Check(err)

				onStrInfo := strings.Split(sqlInfo[`on_str`], ` | `)
				onInfo := strings.Split(sqlInfo[`on`], `|z1@#$FGHke467on|`)
				rightInfo := strings.Split(sqlInfo[`right`], `,`)
				joins := strings.Split(sqlInfo[`join`], `,`)

				for i := 0; i < joinCount; i++ {
					rightFlagField := `just_place_holder`

					onStr := onStrInfo[i]
					let := bson.D{}
					m := map[string]string{}

					info := strings.Split(onStr, ` `)
					for _, v := range info {
						v = strings.Trim(v, `()`)
						flag := left + `.`
						if strings.HasPrefix(v, flag) {
							_, ok := m[v]
							if !ok {
								m[v] = ""
								v = strings.ReplaceAll(v, flag, ``)
								let = append(let, bson.E{Key: v, Value: `$` + v})
							}
						} else {

							if rightFlagField == `just_place_holder` && strings.HasPrefix(v, rightInfo[i]+`.`) {
								rightFlagField = v
							}
						}
					}

					on := onInfo[i]
					onTmp := strings.ReplaceAll(on, left+`.`, `$$`)
					onTmp = strings.ReplaceAll(onTmp, rightInfo[i]+`.`, `$`)
					var onD bson.D
					err := bson.UnmarshalExtJSON([]byte(onTmp), true, &onD)
					// log.Println(`z1log----------onTmp-------`, onTmp, err)
					// time.Sleep(time.Millisecond * 3)
					z1err.Check(err)

					pipelineTmp := []bson.D{
						bson.D{{
							"$match",
							bson.D{{
								"$expr",
								onD,
							}},
						}},
					}

					lookup := bson.D{
						{
							"$lookup",
							bson.D{
								{
									"from",
									rightInfo[i],
								},
								{
									"let",
									let,
								},
								{
									"as",
									rightInfo[i],
								},
								{
									"pipeline",
									pipelineTmp,
								},
							},
						},
					}
					pipeline = append(pipeline, lookup)

					{
						unwindD := bson.D{
							{
								"$unwind",
								bson.D{
									{
										"path",
										`$` + rightInfo[i],
									},
									{
										"preserveNullAndEmptyArrays",
										joins[i] == `left join`,
									},
								},
							},
						}
						pipeline = append(pipeline, unwindD)
					}
				}
			}
		}

		// where
		{
			whereStr, ok := sqlInfo[`where`]
			whereStr = strings.ReplaceAll(whereStr, sqlInfo[`left`]+`.`, ``)

			if ok {
				var whereD bson.D
				err := bson.UnmarshalExtJSON([]byte(whereStr), true, &whereD)
				// log.Println(`z1log----------whereStr-------`, whereStr, err)
				// time.Sleep(time.Millisecond * 3)
				z1err.Check(err)

				whereMatch := bson.D{
					{
						"$match",
						whereD,
					},
				}

				pipeline = append(pipeline, whereMatch)
			}
		}

		if z1count {
			// count
			{
				countD := bson.D{
					{
						"$group",
						bson.D{
							{
								"_id",
								nil,
							},
							{
								"count",
								bson.D{
									{
										"$sum",
										1,
									},
								},
							},
						},
					},
				}
				pipeline = append(pipeline, countD)

			}

		} else {

			// fileds
			r, ok := sqlInfo[`fields`]
			if ok && r != `` {
				var fieldsD bson.D
				var fieldsD2 bson.D
				fieldsDMap := make(map[string]bson.E, 0)
				fieldsD2Map := make(map[string]bson.E, 0)

				fields := sqlInfo[`fields`]
				fields = strings.ReplaceAll(fields, left+`.`, ``)
				info := strings.Split(fields, `,`)
				fields_aliased := make(map[string]string, 0)
				fields_aliased_tmp, ok := sqlInfo[`fields_aliased`]
				if ok {
					err := json.Unmarshal([]byte(fields_aliased_tmp), &fields_aliased)
					// log.Println(`z1log----------fields_aliased_tmp-------`, fields_aliased_tmp, err)
					// time.Sleep(time.Millisecond * 3)
					z1err.Check(err)

				}

				for _, v := range info {
					// key := strings.Replace(v, `.`, `__`, 1)

					info := strings.Split(v, `.`)
					key := info[len(info)-1]

					keyTmp, ok := fields_aliased[v]
					if ok {
						if keyTmp != `` {
							key = keyTmp
						}
					}

					tmp := bson.E{
						Key:   key,
						Value: `$` + v,
					}
					// fieldsD = append(fieldsD, tmp)
					fieldsDMap[key] = tmp

					tmp2 := bson.E{
						Key:   key,
						Value: 1,
					}

					fieldsD2Map[key] = tmp2
					// fieldsD2 = append(fieldsD2, tmp2)
				}

				for _, v := range fieldsDMap {
					fieldsD = append(fieldsD, v)
				}

				for _, v := range fieldsD2Map {
					fieldsD2 = append(fieldsD2, v)
				}

				fieldsD2 = append(fieldsD2, bson.E{
					Key:   `_id`,
					Value: 0,
				})

				replaceRoot := bson.D{
					{
						"$replaceRoot",
						bson.D{
							{
								"newRoot",
								bson.D{
									{
										"$mergeObjects",
										// fieldsD,
										[]interface{}{
											"$$ROOT",
											fieldsD,
										},
									},
								},
							},
						},
					},
				}
				pipeline = append(pipeline, replaceRoot)

				_ = fieldsD2
				project := bson.D{
					{
						"$project",
						fieldsD2,
					},
				}
				pipeline = append(pipeline, project)
			}

			// order
			{
				sortStr, ok := sqlInfo[`sort`]
				if ok {
					sortStr = strings.ReplaceAll(sortStr, `"asc"`, `1`)
					sortStr = strings.ReplaceAll(sortStr, `"desc"`, `-1`)
					sortStr = `{"$sort": ` + sortStr + `}`

					var sortD bson.D
					err := bson.UnmarshalExtJSON([]byte(sortStr), true, &sortD)
					// log.Println(`z1log----------sortStr-------`, sortStr, err)
					// time.Sleep(time.Millisecond * 3)
					z1err.Check(err)

					pipeline = append(pipeline, sortD)
				}

			}

			// limit
			{
				limit, ok := sqlInfo[`limit`]
				skip := sqlInfo[`skip`]
				if ok {
					limit2, err := strconv.ParseInt(limit, 10, 64)
					z1err.Check(err)
					skip2, err := strconv.ParseInt(skip, 10, 64)
					z1err.Check(err)

					skipD := bson.D{{
						"$skip",
						skip2,
					}}
					pipeline = append(pipeline, skipD)

					limitD := bson.D{
						{
							"$limit",
							limit2,
						},
					}
					pipeline = append(pipeline, limitD)
				}
			}
		}

		// debug
		if `debug1` == `debug` {
			// log.Println(pipeline)

			var pip []string
			for _, v := range pipeline {
				t, err := bson.MarshalExtJSON(v, false, true)
				z1err.Check(err)
				// log.Println(err, t, string(t))
				pip = append(pip, string(t))
			}

			pipStr := fmt.Sprintf(`db.getCollection("%v").aggregate([%s])`, left, strings.Join(pip, `,`))

			log.Println(`---pipStr---`, pipStr, `--------`)
		}

		// return

		collection := DB.Collection(left)

		showInfoCursor, err := collection.Aggregate(context.TODO(), pipeline)
		defer showInfoCursor.Close(context.TODO())

		if len(ret) > 0 && !z1count {
			r := ret[0]

			b, err := json.Marshal(r)
			// log.Println(`z1log---------- 1 -------`, string(b), err)
			// time.Sleep(time.Millisecond * 3)
			z1err.Check(err)
			destStr := string(b)

			if strings.HasPrefix(destStr, `[`) {
				// tmp := []map[string]interface{}{}
				// err := showInfoCursor.All(context.Background(), &tmp)
				// z1err.Check(err)

				// {
				// 	b, err := json.MarshalIndent(tmp, ``, `  `)
				// 	z1err.Check(err)

				// 	err = json.Unmarshal(b, r)
				// 	z1err.Check(err)

				// 	// log.Println(`------tmp-------`, tmp, string(b))
				// }

				err := showInfoCursor.All(context.Background(), r)
				// log.Println(`z1log---------- 2 -------`, err)
				// time.Sleep(time.Millisecond * 3)
				z1err.Check(err)
			} else {
				if showInfoCursor.Next(context.TODO()) {
					// tmp := map[string]interface{}{}
					// err := showInfoCursor.Decode(&tmp)
					// z1err.Check(err)

					// {
					// 	b, err := json.MarshalIndent(tmp, ``, `  `)
					// 	z1err.Check(err)

					// 	err = json.Unmarshal(b, r)
					// 	z1err.Check(err)

					// 	// log.Println(`------tmp-------`, tmp, string(b))
					// }

					err := showInfoCursor.Decode(r)
					// log.Println(`z1log---------- 3 -------`, err)
					// time.Sleep(time.Millisecond * 3)
					z1err.Check(err)
				}
			}
		} else {
			var objs []bson.M
			err = showInfoCursor.All(context.TODO(), &objs)
			// log.Println(`z1log---------- 4 -------`, err)
			z1err.Check(err)

			if z1count {
				if len(objs) > 0 {
					tmp := objs[0]
					t := tmp[`count`]
					total = int64(t.(int32))
				} else {
					total = 0
				}
			}

			// debug
			if `debug1` == `debug` {
				log.Println(`-------`, showInfoCursor, err, `--------`, objs, total)
			}

			result = objs
		}
	}

	return
}

func ParsingSQL(sql string) (ret map[string]string, err error) {
	defer z1err.Handle(&err)

	{
		sql = strings.ReplaceAll(sql, ` IS NULL`, ` = '___empty_str___'`)
		sql = strings.ReplaceAll(sql, ` IS NOT NULL`, ` != '___empty_str___'`)

		relaceStrs := map[string]string{
			`NULL`: `'___empty_str___'`,
			`''`:   `'___empty_str___'`,
		}
		for k, v := range relaceStrs {
			sql = strings.ReplaceAll(sql, k, v)
		}
		// log.Println(sql)
	}

	// log.Println(`--------sql-----`, sql)
	stmt, err := sqlparser.Parse(sql)
	z1err.Check(err)

	switch stmt := stmt.(type) {
	case *sqlparser.Select:
		ret, err = ParsingSelectSQL(stmt)
	case *sqlparser.Insert:
		ret, err = ParsingInsertSQL(stmt)
	case *sqlparser.Delete:
		ret, err = ParsingDeleteSQL(stmt)
	case *sqlparser.Update:
		ret, err = ParsingUpdateSQL(stmt)
	case *sqlparser.DDL:
		ret, err = ParsingDDLSQL(stmt)
	}

	{
		relaceStrs := map[string]string{
			`___empty_str___`: ``,
		}

		_, ok := ret[`join`]
		if !ok {
			flag := ret[`left`] + `.`
			relaceStrs[flag] = ``
		}

		for k, v := range ret {
			for k1, v1 := range relaceStrs {
				v = strings.ReplaceAll(v, k1, v1)
			}
			ret[k] = v
		}
	}

	return
}

func ParsingInsertSQL(stmt sqlparser.Statement) (ret map[string]string, err error) {
	z1err.Handle(&err)

	switch stmt := stmt.(type) {
	case *sqlparser.Insert:
		ret = make(map[string]string, 0)

		if `new` == `new` {
			var insert_rows []map[string]interface{}

			{
				columns := strings.Split(
					strings.Trim(
						sqlparser.String(
							stmt.Columns,
						),
						`()`,
					),
					`, `,
				)

				rowStr := sqlparser.String(stmt.Rows)
				rowStr = strings.TrimPrefix(rowStr, `values `)
				rowStr = strings.Trim(rowStr, `()`)
				rows := strings.Split(rowStr, `), (`)

				for _, v := range rows {
					row := strings.Split(v, `, `)
					tmp := make(map[string]interface{}, 0)
					for i1, v1 := range row {
						tmp[columns[i1]] = v1
					}
					insert_rows = append(insert_rows, tmp)
				}
			}

			rowsByte, err := json.Marshal(insert_rows)
			z1err.Check(err)
			ret[`insert_rows`] = string(rowsByte)
		}

		ret[`action`] = stmt.Action
		ret[`insert_table`] = fmt.Sprintf(`%v`, stmt.Table.Name)
		ret[`left`] = ret[`insert_table`]
	}

	return
}

func ParsingDeleteSQL(stmt sqlparser.Statement) (ret map[string]string, err error) {
	z1err.Handle(&err)

	switch stmt := stmt.(type) {
	case *sqlparser.Delete:
		ret = make(map[string]string, 0)
		ret[`action`] = `delete`

		// table
		{
			var TableExprsTmp = stmt.TableExprs
			buffer := sqlparser.NewTrackedBuffer(nil)
			TableExprsTmp.Format(buffer)
			// fmt.Println(`-------left-----`, buffer)
			ret[`delete_table`] = fmt.Sprintf(`%v`, buffer)
			ret[`left`] = ret[`delete_table`]
		}

		// where
		{
			var rootParent sqlparser.Expr
			selectorStr, err := handleSelectWhere(&stmt.Where.Expr, true, &rootParent)
			z1err.Check(err)
			// log.Println(selectorStr)
			ret[`delete_where`] = selectorStr
		}
	}

	return
}

func ParsingUpdateSQL(stmt sqlparser.Statement) (ret map[string]string, err error) {
	z1err.Handle(&err)

	switch stmt := stmt.(type) {
	case *sqlparser.Update:
		ret = make(map[string]string, 0)
		ret[`action`] = `update`

		// table
		{
			var TableExprsTmp = stmt.TableExprs
			buffer := sqlparser.NewTrackedBuffer(nil)
			TableExprsTmp.Format(buffer)
			// fmt.Println(`-------left-----`, buffer)
			ret[`update_table`] = fmt.Sprintf(`%v`, buffer)
			ret[`left`] = ret[`update_table`]
		}

		// where
		{
			var rootParent sqlparser.Expr
			selectorStr, err := handleSelectWhere(&stmt.Where.Expr, true, &rootParent)
			z1err.Check(err)
			// log.Println(selectorStr)
			ret[`update_where`] = selectorStr
		}

		// update
		if `new` == `new` {
			setStr := sqlparser.String(stmt.Exprs)
			setStr = strings.ReplaceAll(setStr, ` = `, `":"`)
			setStr = strings.ReplaceAll(setStr, `, `, `","`)
			setStr = `{"` + setStr + `"}`
			ret[`update_set`] = setStr
		}
	}

	return
}

func ParsingDDLSQL(stmt sqlparser.Statement) (ret map[string]string, err error) {
	z1err.Handle(&err)

	switch stmt := stmt.(type) {
	case *sqlparser.DDL:
		ret = make(map[string]string, 0)
		ret[`action`] = stmt.Action
		if stmt.Action == `drop` {
			ret[`action`] = `drop`
			ret[`drop_table`] = fmt.Sprintf(`%v`, stmt.Table.Name)
			ret[`left`] = ret[`drop_table`]
		}

	}

	return
}

func ParsingSelectSQL(stmt sqlparser.Statement) (ret map[string]string, err error) {
	z1err.Handle(&err)

	switch stmt := stmt.(type) {
	case *sqlparser.Select:
		var Select = stmt
		ret = make(map[string]string, 0)
		ret["action"] = `select`

		// fields
		{
			fields := []string{}
			fieldsAliased := map[string]string{}
			for _, v := range Select.SelectExprs {
				var col = v
				switch colType := col.(type) {
				case *sqlparser.AliasedExpr:
					fields = append(fields, sqlparser.String(colType.Expr))
					fieldsAliased[sqlparser.String(colType.Expr)] = sqlparser.String(colType.As)
				}
			}
			ret[`fields`] = strings.Join(fields, `,`)
			b, err := json.Marshal(fieldsAliased)
			z1err.Check(err)
			ret[`fields_aliased`] = string(b)
		}

		// join
		if `new` == `new` {
			var left = Select.From[0]

			flag := true
			for flag {
				switch expr := left.(type) {
				case *sqlparser.AliasedTableExpr:
					Expr := expr.Expr
					buffer := sqlparser.NewTrackedBuffer(nil)
					Expr.Format(buffer)
					ret[`left`] = fmt.Sprintf(`%v`, buffer)

					// log.Println(`----AliasedTableExpr------`, buffer)

					flag = false
				case *sqlparser.JoinTableExpr:
					Expr := expr.RightExpr
					buffer := sqlparser.NewTrackedBuffer(nil)
					Expr.Format(buffer)
					// log.Println(`----JoinTableExpr RightExpr------`, buffer)
					_, ok := ret[`right`]
					if !ok {
						ret[`right`] = fmt.Sprintf(`%v`, buffer)
					} else {
						ret[`right`] = fmt.Sprintf(`%v,%v`, ret[`right`], buffer)
					}

					// join
					{
						// ret[`join`] = fmt.Sprintf(`%v`, TableExprsTypeTmp.Join)
						_, ok := ret[`join`]
						if !ok {
							ret[`join`] = fmt.Sprintf(`%v`, expr.Join)
						} else {
							ret[`join`] = fmt.Sprintf(`%v,%v`, ret[`join`], expr.Join)
						}
					}

					// on_str
					buffer = sqlparser.NewTrackedBuffer(nil)
					expr.On.Format(buffer)
					// ret[`on_str`] = fmt.Sprintf(`%v`, buffer)
					_, ok = ret[`on_str`]
					if !ok {
						ret[`on_str`] = fmt.Sprintf(`%v`, buffer)
					} else {
						ret[`on_str`] = fmt.Sprintf(`%v | %v`, ret[`on_str`], buffer)
					}
					// log.Println(`z1log-----------------ret["on_str"]-----`, sqlparser.String(expr.On), ret[`on_str`])
					// time.Sleep(time.Millisecond * 3)

					var rootParent sqlparser.Expr
					handleSelectWhereComparisonExprJoin = true
					selectorStr, err := handleSelectWhere(&expr.On, true, &rootParent)
					handleSelectWhereComparisonExprJoin = false
					z1err.Check(err)
					// log.Println(`----JoinTableExpr On------`, selectorStr)
					_, ok = ret[`on`]
					if !ok {
						ret[`on`] = fmt.Sprintf(`%v`, selectorStr)
					} else {
						ret[`on`] = fmt.Sprintf(`%v|z1@#$FGHke467on|%v`, ret[`on`], selectorStr)
					}

					left = expr.LeftExpr
				}
			}
		}

		if `old1` == `old` {
			var TableExprs = Select.From[0]
			switch TableExprsType := TableExprs.(type) {
			case *sqlparser.JoinTableExpr:
				var TableExprsTypeTmp = TableExprsType

				{
					var left = TableExprsTypeTmp.LeftExpr
					switch expr := left.(type) {
					case *sqlparser.AliasedTableExpr:
						Expr := expr.Expr
						buffer := sqlparser.NewTrackedBuffer(nil)
						Expr.Format(buffer)
						ret[`left`] = fmt.Sprintf(`%v`, buffer)
					}
				}

				{
					var right = TableExprsTypeTmp.RightExpr
					switch expr := right.(type) {
					case *sqlparser.AliasedTableExpr:
						Expr := expr.Expr
						buffer := sqlparser.NewTrackedBuffer(nil)
						Expr.Format(buffer)
						ret[`right`] = fmt.Sprintf(`%v`, buffer)
					}
				}

				{
					ret[`join`] = fmt.Sprintf(`%v`, TableExprsTypeTmp.Join)
				}

				{
					buffer := sqlparser.NewTrackedBuffer(nil)
					TableExprsTypeTmp.On.Format(buffer)
					ret[`on_str`] = fmt.Sprintf(`%v`, buffer)

					var rootParent sqlparser.Expr
					handleSelectWhereComparisonExprJoin = true
					selectorStr, err := handleSelectWhere(&TableExprsTypeTmp.On, true, &rootParent)
					handleSelectWhereComparisonExprJoin = false
					z1err.Check(err)
					ret[`on`] = selectorStr
				}

			case *sqlparser.AliasedTableExpr:
				var TableExprsTmp = TableExprs
				buffer := sqlparser.NewTrackedBuffer(nil)
				TableExprsTmp.Format(buffer)
				// fmt.Println(`-------left-----`, buffer)
				ret[`left`] = fmt.Sprintf(`%v`, buffer)
			}
		}

		// where
		{
			if Select.Where != nil {
				var rootParent sqlparser.Expr
				selectorStr, err := handleSelectWhere(&Select.Where.Expr, true, &rootParent)
				z1err.Check(err)
				ret[`where`] = selectorStr
			}
		}

		// order
		{
			var orderByArr []string
			for _, orderByExpr := range Select.OrderBy {
				orderByStr := fmt.Sprintf(`"%v": "%v"`, strings.Replace(sqlparser.String(orderByExpr.Expr), "`", "", -1), orderByExpr.Direction)
				orderByArr = append(orderByArr, orderByStr)
			}
			if len(orderByArr) > 0 {
				ret["sort"] = fmt.Sprintf("{%v}", strings.Join(orderByArr, ","))
			}
		}

		// limit
		{
			if Select.Limit != nil {
				skip := "0"
				if Select.Limit.Offset != nil {
					skip = sqlparser.String(Select.Limit.Offset)
				}
				limit := sqlparser.String(Select.Limit.Rowcount)
				ret["skip"] = skip
				ret["limit"] = limit
			}
		}
	}

	return
}

func Parse(sql string) (string, string, error) {
	stmt, err := sqlparser.Parse(sql)

	if err != nil {
		fmt.Println(err)
	}

	var docSQL string
	var tableName string
	switch stmt.(type) {
	case *sqlparser.Select:
		docSQL, tableName, err = handleSelect(stmt.(*sqlparser.Select))
	case *sqlparser.Update, *sqlparser.Insert, *sqlparser.Delete:
		return "", "", errors.New("action type is not supported")
	}

	if err != nil {
		return "", "", nil
	}
	return docSQL, tableName, nil
}

func handleSelect(sel *sqlparser.Select) (string, string, error) {
	var rootParent sqlparser.Expr
	if len(sel.From) != 1 {
		return "", "", errors.New("does not support multiple from")
	}

	tableName := strings.Replace(sqlparser.String(sel.From), "`", "", -1)
	resultMap := make(map[string]interface{})

	// where
	if sel.Where != nil {
		selectorStr, err := handleSelectWhere(&sel.Where.Expr, true, &rootParent)
		if err != nil {
			return "", tableName, err
		}
		resultMap["selector"] = selectorStr
	}

	// limit
	if sel.Limit != nil {
		skip := "0"
		if sel.Limit.Offset != nil {
			skip = sqlparser.String(sel.Limit.Offset)
		}
		limit := sqlparser.String(sel.Limit.Rowcount)
		resultMap["skip"] = skip
		resultMap["limit"] = limit
	}

	// order
	var orderByArr []string
	for _, orderByExpr := range sel.OrderBy {
		orderByStr := fmt.Sprintf(`{"%v": "%v"}`, strings.Replace(sqlparser.String(orderByExpr.Expr), "`", "", -1), orderByExpr.Direction)
		orderByArr = append(orderByArr, orderByStr)
	}
	if len(orderByArr) > 0 {
		resultMap["sort"] = fmt.Sprintf("[%v]", strings.Join(orderByArr, ","))
	}

	filterKeys := []string{"selector", "sort", "skip", "limit"}
	resultArr := make([]string, 0)
	for _, key := range filterKeys {
		if v, ok := resultMap[key]; ok {
			resultArr = append(resultArr, fmt.Sprintf("%v:%v", key, v))
		}
	}
	return fmt.Sprintf("{%v}", strings.Join(resultArr, ",")), tableName, nil
}

func handleSelectWhere(expr *sqlparser.Expr, topLevel bool, parent *sqlparser.Expr) (string, error) {
	if expr == nil {
		return "", errors.New("error expression cannot be nil here")
	}

	switch (*expr).(type) {
	case *sqlparser.AndExpr:
		return handleSelectWhereAndExpr(expr, topLevel, parent)

	case *sqlparser.OrExpr:
		return handleSelectWhereOrExpr(expr, topLevel, parent)
	case *sqlparser.ComparisonExpr:
		return handleSelectWhereComparisonExpr(expr, topLevel, parent)

	case *sqlparser.RangeCond:
		//TODO 支持between
		// between a and b
		// the meaning is equal to range query
		/*
			rangeCond := (*expr).(*sqlparser.RangeCond)
			colName, ok := rangeCond.Left.(*sqlparser.ColName)

			if !ok {
				return "", errors.New("elasticsql: range column name missing")
			}

			colNameStr := sqlparser.String(colName)
			fromStr := strings.Trim(sqlparser.String(rangeCond.From), `'`)
			toStr := strings.Trim(sqlparser.String(rangeCond.To), `'`)

			resultStr := fmt.Sprintf(`{"range" : {"%v" : {"from" : "%v", "to" : "%v"}}}`, colNameStr, fromStr, toStr)
			if topLevel {
				resultStr = fmt.Sprintf(`{"bool" : {"must" : [%v]}}`, resultStr)
			}

			return resultStr, nil
		*/

	case *sqlparser.ParenExpr:
		parentBoolExpr := (*expr).(*sqlparser.ParenExpr)
		boolExpr := parentBoolExpr.Expr

		// if paren is the top level, bool must is needed
		var isThisTopLevel = false
		if topLevel {
			isThisTopLevel = true
		}
		return handleSelectWhere(&boolExpr, isThisTopLevel, parent)

	default:
		return "", errors.New("grammer is not supported")
	}
	return "", nil
}

func handleSelectWhereAndExpr(expr *sqlparser.Expr, topLevel bool, parent *sqlparser.Expr) (string, error) {
	andExpr := (*expr).(*sqlparser.AndExpr)
	leftExpr := andExpr.Left
	rightExpr := andExpr.Right
	leftStr, err := handleSelectWhere(&leftExpr, false, expr)
	if err != nil {
		return "", err
	}
	rightStr, err := handleSelectWhere(&rightExpr, false, expr)
	if err != nil {
		return "", err
	}

	var resultStr string
	if leftStr == "" || rightStr == "" {
		resultStr = leftStr + rightStr
	} else {
		resultStr = leftStr + `,` + rightStr
	}

	if _, ok := (*parent).(*sqlparser.AndExpr); ok {
		return resultStr, nil
	}
	return fmt.Sprintf(`{"$and": [%v]}`, resultStr), nil
}

func handleSelectWhereOrExpr(expr *sqlparser.Expr, topLevel bool, parent *sqlparser.Expr) (string, error) {
	orExpr := (*expr).(*sqlparser.OrExpr)
	leftExpr := orExpr.Left
	rightExpr := orExpr.Right

	leftStr, err := handleSelectWhere(&leftExpr, false, expr)
	if err != nil {
		return "", err
	}

	rightStr, err := handleSelectWhere(&rightExpr, false, expr)
	if err != nil {
		return "", err
	}

	var resultStr string
	if leftStr == "" || rightStr == "" {
		resultStr = leftStr + rightStr
	} else {
		resultStr = leftStr + `,` + rightStr
	}

	if _, ok := (*parent).(*sqlparser.OrExpr); ok {
		return resultStr, nil
	}

	return fmt.Sprintf(`{"$or": [%v]}`, resultStr), nil
}

var handleSelectWhereComparisonExprJoin = false

func handleSelectWhereComparisonExpr(expr *sqlparser.Expr, topLevel bool, parent *sqlparser.Expr) (string, error) {
	comparisonExpr := (*expr).(*sqlparser.ComparisonExpr)
	colName, ok := comparisonExpr.Left.(*sqlparser.ColName)

	if !ok {
		return "", errors.New("invalid comparison expression, the left must be a column name")
	}

	colNameStr := sqlparser.String(colName)
	colNameStr = strings.Replace(colNameStr, "`", "", -1)
	rightStr, missingCheck, err := buildComparisonExprRightStr(comparisonExpr.Right)

	if handleSelectWhereComparisonExprJoin {
		rightStr = fmt.Sprintf(`"%s"`, strings.Trim(rightStr, `'`))
	} else {
		expr2 := comparisonExpr.Right
		switch exprType := expr2.(type) {
		case *sqlparser.SQLVal:
			switch exprType.Type {
			case sqlparser.IntVal:
				rightStr = strings.Trim(rightStr, `'`)
			default:
				rightStr = fmt.Sprintf(`"%s"`, strings.Trim(rightStr, `'`))
			}
		}
	}

	if err != nil {
		return "", err
	}
	if missingCheck {
		return "", errors.New("sql missing field")
	}
	resultStr := ""

	if handleSelectWhereComparisonExprJoin {
		switch comparisonExpr.Operator {
		case ">=":
			resultStr = fmt.Sprintf(`{"$gte" : ["%v" , %v]}`, colNameStr, rightStr)
		case "<=":
			resultStr = fmt.Sprintf(`{"$lte" : ["%v" , %v]}`, colNameStr, rightStr)
		case "=":
			resultStr = fmt.Sprintf(`{"$eq" : ["%v" , %v]}`, colNameStr, rightStr)
		case ">":
			resultStr = fmt.Sprintf(`{"$gt" : ["%v" , %v]}`, colNameStr, rightStr)
		case "<":
			resultStr = fmt.Sprintf(`{"$lt" : ["%v" , %v]}`, colNameStr, rightStr)
		case "!=":
			resultStr = fmt.Sprintf(`{"$ne" : ["%v" , %v]}`, colNameStr, rightStr)
		// case "in":
		// 	// the default valTuple is ('1', '2', '3') like
		// 	rightStr = strings.Replace(rightStr, `'`, `"`, -1)
		// 	rightStr = strings.Trim(rightStr, "(")
		// 	rightStr = strings.Trim(rightStr, ")")
		// 	resultStr = fmt.Sprintf(`{"%v" : {"$in" : [%v]}}`, colNameStr, rightStr)
		// case "not in":
		// 	rightStr = strings.Replace(rightStr, `'`, `"`, -1)
		// 	rightStr = strings.Trim(rightStr, "(")
		// 	rightStr = strings.Trim(rightStr, ")")
		// 	resultStr = fmt.Sprintf(`{"$not":{"%v" : {"$in" : [%v]}}`, colNameStr, rightStr)
		case "like":
			rightStr = fmt.Sprintf(`"^%v$"`, strings.Trim(rightStr, `"`))
			rightStr = strings.Replace(rightStr, `%`, `.*`, -1)
			resultStr = fmt.Sprintf(`{"$regex" : ["%v" , %v]}`, colNameStr, rightStr)
		case "not like":
			rightStr = fmt.Sprintf(`"^%v$"`, strings.Trim(rightStr, `"`))
			rightStr = strings.Replace(rightStr, `%`, `.*`, -1)
			resultStr = fmt.Sprintf(`{"not" : {"$regex" : ["%v" , %v]}`, colNameStr, rightStr)
		}
	} else {
		switch comparisonExpr.Operator {
		case ">=":
			resultStr = fmt.Sprintf(`{"%v" : {"$gte" : %v}}`, colNameStr, rightStr)
		case "<=":
			resultStr = fmt.Sprintf(`{"%v" : {"$lte" : %v}}`, colNameStr, rightStr)
		case "=":
			resultStr = fmt.Sprintf(`{"%v": {"$eq" : %v}}`, colNameStr, rightStr)
		case ">":
			resultStr = fmt.Sprintf(`{"%v" : {"$gt" : %v}}`, colNameStr, rightStr)
		case "<":
			resultStr = fmt.Sprintf(`{"%v" : {"$lt" : %v}}`, colNameStr, rightStr)
		case "!=":
			resultStr = fmt.Sprintf(`{"%v" : {"$ne" : %v}}`, colNameStr, rightStr)
		case "in":
			// the default valTuple is ('1', '2', '3') like
			rightStr = strings.Replace(rightStr, `'`, `"`, -1)
			rightStr = strings.Trim(rightStr, "(")
			rightStr = strings.Trim(rightStr, ")")
			resultStr = fmt.Sprintf(`{"%v" : {"$in" : [%v]}}`, colNameStr, rightStr)
		case "not in":
			rightStr = strings.Replace(rightStr, `'`, `"`, -1)
			rightStr = strings.Trim(rightStr, "(")
			rightStr = strings.Trim(rightStr, ")")
			resultStr = fmt.Sprintf(`{"$not":{"%v" : {"$in" : [%v]}}`, colNameStr, rightStr)
		case "like":
			rightStr = fmt.Sprintf(`"^%v$"`, strings.Trim(rightStr, `"`))
			rightStr = strings.Replace(rightStr, `%`, `.*`, -1)
			resultStr = fmt.Sprintf(`{"%v" : {"$regex" : %v}}`, colNameStr, rightStr)
		case "not like":
			rightStr = fmt.Sprintf(`"^%v$"`, strings.Trim(rightStr, `"`))
			rightStr = strings.Replace(rightStr, `%`, `.*`, -1)
			resultStr = fmt.Sprintf(`{"not" : {"%v" : {"$regex" : %v}}`, colNameStr, rightStr)
		}
	}

	// log.Println(`====`, resultStr)

	return resultStr, nil
}

func buildComparisonExprRightStr(expr sqlparser.Expr) (string, bool, error) {
	var rightStr string
	var err error
	missingCheck := false
	switch expr.(type) {
	case *sqlparser.SQLVal:
		rightStr = sqlparser.String(expr)
		rightStr = strings.Trim(rightStr, `'`)
	case *sqlparser.GroupConcatExpr:
		return "", false, errors.New("does not support group_concat")
	case *sqlparser.FuncExpr:
		// parse nested
		//funcExpr := expr.(*sqlparser.FuncExpr)
		//rightStr, err = buildNestedFuncStrValue(funcExpr)
		//if err != nil {
		//	return "", missingCheck, err
		//}
		return "", false, errors.New("does not support nested")

	case *sqlparser.ColName:
		if sqlparser.String(expr) == "missing" {
			missingCheck = true
			return "", missingCheck, nil
		}
		// return "", missingCheck, errors.New("column name on the right side of compare operator is not supported")

		rightStr = sqlparser.String(expr)
		rightStr = strings.Trim(rightStr, `'`)
		return rightStr, missingCheck, nil
	case sqlparser.ValTuple:
		rightStr = sqlparser.String(expr)
	default:
		// cannot reach here
	}
	return rightStr, missingCheck, err
}

func CheckJoin(on_str, mainTable string) (join int, err error) {
	c := strings.Count(on_str, ` | `)
	join = c + 1

	if c > 1 {
		err = errors.New(`This join type is not supported`)
		return
	} else if c == 1 {
		info := strings.Split(on_str, `|`)
		for _, v := range info {
			t := strings.Count(v, `.`)
			m := strings.Count(v, mainTable+`.`)

			if t != 2*m {
				err = errors.New(`This join type is not supported`)
				return
			}
		}
	}

	return
}