package edb

import (
	"bytes"
	"fmt"
	"reflect"
	"strconv"
)

// mysql源码(EscapeBytesBackslash)只对[]byte转义,这里是对已经拼接好的sql转义
func escapeString(sql string) string {
	srcLen := len(sql)
	desCapacity := srcLen * 2
	desBuf := make([]byte, desCapacity)
	srcBuf := []byte(sql)

	index := 0
	for i := 0; i < srcLen; i++ {
		c := srcBuf[i]
		switch c {
		case '\x00':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = '0'
				index++
			}
		case '\n':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = 'n'
				index++
			}
		case '\r':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = 'r'
				index++
			}
		case '\x1a':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = 'Z'
				index++
			}
		case '\'':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = '\''
				index++
			}
		case '"':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = '"'
				index++
			}
		case '\\':
			{
				desBuf[index] = '\\'
				index++
				desBuf[index] = '\\'
				index++
			}
		default:
			{
				desBuf[index] = c
				index++
			}
		}
	}
	return string(desBuf[:index])
}

func addSingleQuotesString(buf *bytes.Buffer, field string) {
	buf.WriteString("'")
	buf.WriteString(field)
	buf.WriteString("'")
}

func AsSqlString(src interface{}) string {
	switch v := src.(type) {
	case string:
		var buf bytes.Buffer
		escape_string_sql := escapeString(v)
		addSingleQuotesString(&buf, escape_string_sql)
		return buf.String()
	case []byte:
		if v == nil {
			return "''"
		}
		var buf bytes.Buffer
		addSingleQuotesString(&buf, string(v))
		return buf.String()
	}

	rv := reflect.ValueOf(src)
	switch rv.Kind() {
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		return strconv.FormatInt(rv.Int(), 10)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
		return strconv.FormatUint(rv.Uint(), 10)
	case reflect.Float64:
		return strconv.FormatFloat(rv.Float(), 'g', -1, 64)
	case reflect.Float32:
		return strconv.FormatFloat(rv.Float(), 'g', -1, 32)
	case reflect.Bool:
		return strconv.FormatBool(rv.Bool())
	}
	return fmt.Sprintf("%v", src)
}

type DBField struct {
	Fileds []string
}

func NewDBField() *DBField {
	return &DBField{
		Fileds: make([]string, 0),
	}
}

func (d *DBField) Add(filed string) {
	d.Fileds = append(d.Fileds, filed)
}

type DBFieldPair struct {
	Key   string
	Value string
}

func NewDBFieldPair(key string, value interface{}) *DBFieldPair {
	str := AsSqlString(value)
	dbFieldPair := &DBFieldPair{
		Key:   key,
		Value: str,
	}
	return dbFieldPair
}

type DBFieldPairs struct {
	Pairs []*DBFieldPair
}

func NewDBFieldPairs() *DBFieldPairs {
	return &DBFieldPairs{
		Pairs: make([]*DBFieldPair, 0),
	}
}

func (d *DBFieldPairs) Add(pair *DBFieldPair) {
	d.Pairs = append(d.Pairs, pair)
}

// ---------------------------------------------------------------------------
func GetSelectSQL(tbl string, selects *DBField, wheres *DBFieldPairs) string {
	var buf bytes.Buffer

	buf.WriteString("SELECT ")

	if selects == nil {
		buf.WriteString(" * ")
	} else {
		for index, name := range selects.Fileds {
			if index != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(name)
		}
	}

	buf.WriteString(" FROM ")
	buf.WriteString(tbl)

	if wheres != nil {
		buf.WriteString(" WHERE ")
		for index, pair := range wheres.Pairs {
			if index != 0 {
				buf.WriteString(" AND ")
			}
			buf.WriteString(pair.Key)
			buf.WriteString("=")
			buf.WriteString(pair.Value)
		}
	}
	buf.WriteString(";")

	return buf.String()
}

func GetInsertSQL(tbl string, inserts *DBFieldPairs) string {
	var buf bytes.Buffer

	buf.WriteString("INSERT INTO ")
	buf.WriteString(tbl)
	buf.WriteString(" (")

	if inserts != nil {
		var values []string
		for index, pair := range inserts.Pairs {
			if index != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(pair.Key)
			values = append(values, pair.Value)
		}
		buf.WriteString(") VALUES (")

		for i := 0; i < len(values); i++ {
			if i != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(values[i])
		}
	}
	buf.WriteString(");")

	return buf.String()
}

func GetUpdateSQL(tbl string, updates *DBFieldPairs, wheres *DBFieldPairs) string {
	var buf bytes.Buffer

	buf.WriteString("UPDATE ")
	buf.WriteString(tbl)
	buf.WriteString(" SET ")

	if updates != nil {
		for index, pair := range updates.Pairs {
			if index != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(pair.Key)
			buf.WriteString("=")
			buf.WriteString(pair.Value)
		}
	}

	if wheres != nil {
		buf.WriteString(" WHERE ")
		for index, pair := range wheres.Pairs {
			if index != 0 {
				buf.WriteString(" AND ")
			}
			buf.WriteString(pair.Key)
			buf.WriteString("=")
			buf.WriteString(pair.Value)
		}
	}
	buf.WriteString(";")
	return buf.String()
}

func GetDeleteSQL(tbl string, wheres *DBFieldPairs) string {
	var buf bytes.Buffer

	buf.WriteString("DELETE FROM ")
	buf.WriteString(tbl)

	if wheres != nil {
		buf.WriteString(" WHERE ")
		for index, pair := range wheres.Pairs {
			if index != 0 {
				buf.WriteString(" AND ")
			}
			buf.WriteString(pair.Key)
			buf.WriteString("=")
			buf.WriteString(pair.Value)
		}
	}
	buf.WriteString(";")
	return buf.String()
}

func GetInsertOrUpdateSQL(tbl string, updates *DBFieldPairs, keys *DBField) string {
	var buf bytes.Buffer

	buf.WriteString("INSERT INTO ")
	buf.WriteString(tbl)
	buf.WriteString(" ( ")

	// upate list enum.
	if updates != nil {
		var values []string
		for index, pair := range updates.Pairs { // key
			if index != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(pair.Key)
			values = append(values, pair.Value)
		}

		buf.WriteString(") VALUES (") // value

		for i := 0; i < len(values); i++ {
			if i != 0 {
				buf.WriteString(",")
			}
			buf.WriteString(values[i])
		}
		buf.WriteString(") ON DUPLICATE KEY UPDATE ")
	}

	if keys != nil { // exclude key value.
		firstflag := true
		for _, pair := range updates.Pairs {
			key_exist_flag := false
			for key_index := 0; key_index < len(keys.Fileds); key_index++ {
				if keys.Fileds[key_index] == pair.Key {
					key_exist_flag = true
				}
			}

			if key_exist_flag {
				continue
			}

			if !firstflag {
				buf.WriteString(",")
			}
			firstflag = false

			buf.WriteString(pair.Key)
			buf.WriteString("=")
			buf.WriteString(pair.Value)
		}
	}

	buf.WriteString(";")
	return buf.String()
}

// --------------------------------------------------------------------------------------
func BuildSelectSQL(tbl string, selects []string, wheres []*DBFieldPair) string {
	var selectfields *DBField
	if selects != nil {
		selectfields = NewDBField()
		for _, name := range selects {
			selectfields.Add(name)
		}
	} else {
		selectfields = nil
	}

	var wheresmap *DBFieldPairs
	if wheres != nil {
		wheresmap = NewDBFieldPairs()
		wheresmap.Pairs = append(wheresmap.Pairs, wheres...)
	} else {
		wheresmap = nil
	}
	return GetSelectSQL(tbl, selectfields, wheresmap)
}

func BuildInsertSQL(tbl string, inserts []*DBFieldPair) string {
	insertmap := NewDBFieldPairs()
	insertmap.Pairs = append(insertmap.Pairs, inserts...)
	return GetInsertSQL(tbl, insertmap)
}

func BuildUpdateSQL(tbl string, updates []*DBFieldPair, wheres []*DBFieldPair) string {
	updatemap := NewDBFieldPairs()
	updatemap.Pairs = append(updatemap.Pairs, updates...)

	var wheresmap *DBFieldPairs
	if wheres != nil {
		wheresmap = NewDBFieldPairs()
		wheresmap.Pairs = append(wheresmap.Pairs, wheres...)
	} else {
		wheresmap = nil
	}
	return GetUpdateSQL(tbl, updatemap, wheresmap)
}

func BuildDeleteSQL(tbl string, wheres []*DBFieldPair) string {
	wheremap := NewDBFieldPairs()
	if len(wheres) > 0 {
		wheremap.Pairs = append(wheremap.Pairs, wheres...)
	} else {
		wheremap = nil
	}
	return GetDeleteSQL(tbl, wheremap)
}

func BuildInsertOrUpdateSQL(tbl string, updates []*DBFieldPair, keys []string) string {
	updatemap := NewDBFieldPairs()
	updatemap.Pairs = append(updatemap.Pairs, updates...)

	fields := NewDBField()
	for _, name := range keys {
		fields.Add(name)
	}

	return GetInsertOrUpdateSQL(tbl, updatemap, fields)
}