package adm

import (
	"fmt"
//    "reflect"
	"strings"
	"errors"
)

type QueryType int8

const (
	MARRY_AND = iota
	MARRY_OR
)

const (
	QUERY_SELECT QueryType = iota
	QUERY_INSERT
	QUERY_UPDATE
	QUERY_DELETE
)

const (
	SQL_AND = "AND"
	SQL_OR = "OR"
	SQL_ASC = "ASC"
	SQL_DESC = "DESC"
	SQL_AS = "AS"
	SQL_NONE = ""
	SQL_QUERY = "?"
	SQL_COL_SPR = ","
	SQL_QUERY_HOLDER = SQL_COL_SPR + SQL_QUERY
	SQL_IN = "IN"
	SQL_NOT_IN = "NOT IN"
)

type QueryBuilder struct {
	query           QueryType
	//////////////////////////////////////////
	// 关联的字段，只在Select和Insert中有效
	Columns         []string
	// 要操作的目标的表
	// select时，为查询的表，不包含join的表
	// insert, update, delete为操作的主表
	TargetTable     string
	// 查询条件
	Conditions      *Conditions
	// limit， 0 -> offset, 1 -> limit
	LimitOffset     [2]int
	// 排序字段
	OrderColumns    []string
	// GROUP BY
	GroupColumn     string
	//////////////////////////////////////////
	// Insert
	InsertData      [][]interface{}
	insertRows      int
	//////////////////////////////////////////
	// Update
	UpdateData      map[string]interface{}
}

type Conditions struct {
	SQL         string
	Params      []interface{}
}


func as(field string, as string, spr string) (string) {
	return field + " " + spr + " " + as
}

func Asc(field string) (string) {
	return as(field, SQL_ASC, SQL_NONE)
}

func Desc(field string) (string) {
	return as(field, SQL_DESC, SQL_NONE)
}

// Col("name") => "name"
// Col("name", "f1") => "name as f1"
func Col(args ...string) (string) {
	num := len(args)
	if num > 1 {
		return as(args[0], args[1], SQL_AS)
	}
	if num == 1 {
		return args[0]
	}
	return ""
}

func Marry(marry int) (string) {
	if marry == MARRY_OR {
		return SQL_OR
	}
	return SQL_AND
}

func InToSql(field string, len int, negative bool) (string) {
	in := SQL_IN
	if negative {
		in = SQL_NOT_IN
	}
	return field + " " + in + " (" + string([]byte(strings.Repeat(SQL_QUERY_HOLDER, len))[1:]) + ")"
}

func MkQueryHolder(size int) string {
	if size <= 0 {
		return ""
	}
	return string([]byte(strings.Repeat(SQL_QUERY_HOLDER, size))[1:])
}

/////////////////////////////////////////////////////////////////////////
// SQLStatement
/////////////////////////////////////////////////////////////////////////

// 构造一个select查询
// Select还是恢复查询字段的参数模式
func Select(columns ...string) (*QueryBuilder) {
	return &QueryBuilder{query: QUERY_SELECT, Columns: columns }
}

func Insert(table string) (*QueryBuilder) {
	return &QueryBuilder{query: QUERY_INSERT, TargetTable: table }
}

func Update(table string) (*QueryBuilder) {
	return &QueryBuilder{query: QUERY_UPDATE, TargetTable: table }
}

func Delete(table string) (*QueryBuilder) {
	return &QueryBuilder{query: QUERY_DELETE, TargetTable: table }
}

// 将FindOne绑定到QueryBuilder，这样更符合语义化
// adm.Select().Where("id > 100").FindOne()
func (this *QueryBuilder) FindOne(obj Model) *ResultSet {
	rs := obj.Conn().Find(this.Table(obj.Table()).Limit(1))
	if rs.Count() > 0 {
		rs.Row(0).Fetch(obj)
	}
	return rs
}

func (this *QueryBuilder) Find(obj Model) *ResultSet {
	if len(this.TargetTable) <= 0 {
		this.Table(obj.Table())
	}
	return obj.Conn().Find(this)
}

//func (this *QueryBuilder) Exec(obj Model) *ExecResult {
//    if len(this.TargetTable) <= 0 {
//        this.Table(obj.Table())
//    }
//    return obj.Conn().execQuery(this)
//}

/////////////////////////////////////////////////////////////////////////
// Select构造
/////////////////////////////////////////////////////////////////////////

// 追加字段
func (this *QueryBuilder) Cols(columns...string) (*QueryBuilder) {
	this.Columns = append(this.Columns, columns...)
	return this
}

// 直接覆盖现有的所有字段
func (this *QueryBuilder) SetCols(columns...string) (*QueryBuilder) {
	this.Columns = columns
	return this
}

// 修改目标表
func (this *QueryBuilder) Table(args ...string) (*QueryBuilder) {
	num := len(args)
	if num > 1 {
		this.TargetTable = as(args[0], args[1], SQL_AS)
	}
	if num == 1 {
		this.TargetTable = args[0]
	}
	return this
}

func (this *QueryBuilder) getOrCreateConditions() (*Conditions) {
	if this.Conditions == nil {
		this.Conditions = &Conditions{}
	}
	return this.Conditions
}

func (this *QueryBuilder) Where(sql string, params ...interface {}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_AND, sql, params...)
	return this
}

func (this *QueryBuilder) OrWhere(sql string, params ...interface{}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_OR, sql, params...)
	return this
}

func (this *QueryBuilder) In(field string, values ...interface{}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_AND, InToSql(field, len(values), false), values...)
	return this
}

func (this *QueryBuilder) OrIn(field string, values ...interface{}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_OR, InToSql(field, len(values), false), values...)
	return this
}

func (this *QueryBuilder) NotIn(field string, values ...interface{}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_AND, InToSql(field, len(values), true), values...)
	return this
}

func (this *QueryBuilder) OrNotIn(field string, values ...interface{}) (*QueryBuilder) {
	this.getOrCreateConditions().Push(MARRY_OR, InToSql(field, len(values), true), values...)
	return this
}

func (this *QueryBuilder) Order(args ...string) (*QueryBuilder) {
	this.OrderColumns = append(this.OrderColumns, args...)
	return this
}

// 暂时只支持一个字段的Group
func (this *QueryBuilder) Group(column string) (*QueryBuilder) {
	this.GroupColumn = column
	return this
}

func (this *QueryBuilder) Limit(limit int) (*QueryBuilder) {
	if limit < 0 {
		limit = 0
	}
	this.LimitOffset[0] = 0
	this.LimitOffset[1] = limit
	return this
}

func (this *QueryBuilder) Offset(offset int, limit int) (*QueryBuilder) {
	if limit < 0 {
		limit = 0
	}
	if offset < 0 {
		offset = 0
	}
	this.LimitOffset[0] = offset
	this.LimitOffset[1] = limit
	return this
}

func (this *QueryBuilder) Values(values...interface {}) (*QueryBuilder) {
	this.InsertData = append(this.InsertData, values)
	this.insertRows += 1
	return this
}

func (this *QueryBuilder) ClearValues() (*QueryBuilder) {
	this.InsertData = make([][]interface{}, 0)
	this.insertRows = 0
	return this
}

func (this *QueryBuilder) GetInsertSize() int {
	return this.insertRows
}

func (this *QueryBuilder) getOrCreateUpdateData(key string, value interface{}) map[string]interface{} {
	if this.UpdateData == nil {
		this.UpdateData = map[string]interface{}{key: value }
		// 这里还是有待优化的，不断的添加key value需要不断的动态分配新的内存
	} else {
		this.UpdateData[key] = value
	}
	return this.UpdateData
}

func (this *QueryBuilder) Set(key string, value interface{}) (*QueryBuilder) {
	this.getOrCreateUpdateData(key, value);
	return this
}

func (this *QueryBuilder) SetMap(values map[string]interface{}) (*QueryBuilder) {
	this.UpdateData = values
	return this
}

func (this *QueryBuilder) GetQueryName() string {
	switch this.query {
		case QUERY_SELECT :
		return "SELECT"
		case QUERY_INSERT :
		return "INSERT"
		case QUERY_UPDATE :
		return "UPDATE"
		case QUERY_DELETE :
		return "DELETE"
	}
	return ""
}

func (this *QueryBuilder) GetQueryType() QueryType {
	return this.query
}

func (this *QueryBuilder) HasConds() bool {
	return this.Conditions != nil && !this.Conditions.IsEmpty()
}

func (this *QueryBuilder) Verify() error {
	name := this.GetQueryName()
	if len(this.TargetTable) <= 0 {
		return errors.New(fmt.Sprintf("Unset table or table is empty str in %s query", name))
	}
	switch this.query {
		case QUERY_SELECT :
		//        if len(this.Columns) <= 0 {
		//            return errors.New(fmt.Sprintf("Columns can't be empty in %s query", name))
		//        }
		case QUERY_INSERT :
		if len(this.Columns) <= 0 {
			return errors.New(fmt.Sprintf("Columns can't be empty in %s query", name))
		}
		if this.insertRows <= 0 {
			return errors.New(fmt.Sprintf("Unset insert values in %s query", name))
		}
		case QUERY_UPDATE :
		if len(this.UpdateData) <= 0 {
			return errors.New(fmt.Sprintf("Unset update values in %s query", name))
		}
		case QUERY_DELETE :
	}
	return nil
}

func (this *QueryBuilder) GetJoinColumns() string {
	if len(this.Columns) <= 0 {
		return "*"
	}
	// 暂时先直接join，不过滤重复字段，请使用者自己控制字段不重复
	return strings.Join(this.Columns, SQL_COL_SPR)
}

func (this *QueryBuilder) GetSQL() string {
	switch this.query {
		case QUERY_SELECT :
		return this.buildSelectSQL()
		case QUERY_INSERT :
		return this.buildInsertSQL()
		case QUERY_UPDATE :
		return this.buildUpdateSQL()
		case QUERY_DELETE :
		return this.buildDeleteSQL()
	}
	return ""
}

func (this *QueryBuilder) buildSelectSQL() string {
	sql := "SELECT " + this.GetJoinColumns() + " FROM " + this.TargetTable
	if this.HasConds() {
		sql += " WHERE " + this.Conditions.SQL
	}
	if len(this.GroupColumn) > 0 {
		sql += " GROUP BY " + this.GroupColumn
	}
	orderLen := len(this.OrderColumns)
	if orderLen > 0 {
		sql += " ORDER BY " + strings.Join(this.OrderColumns, SQL_COL_SPR)
	}
	if this.LimitOffset[1] > 0 {
		if this.LimitOffset[0] > 0 {
			sql += fmt.Sprintf(" LIMIT %d,%d", this.LimitOffset[0], this.LimitOffset[1])
		} else {
			sql += fmt.Sprintf(" LIMIT %d", this.LimitOffset[1])
		}
	}
	return sql
}

func (this *QueryBuilder) buildInsertSQL() string {
	size := len(this.Columns)
	rows := len(this.InsertData)

	sql := "INSERT INTO " + this.TargetTable
	if len(this.Columns) > 0 {
		sql += "(" + this.GetJoinColumns() + ")"
	}
	sql += " VALUES "

	if size <= 0 {
		return sql;
	}

	holder := "(" + string([]byte(strings.Repeat(SQL_QUERY_HOLDER, size))[1:]) + ")"
	vHolder := ""
	VIndex := 0
	for i := 0; i < rows; i++ {
		if VIndex > 0 {
			vHolder += SQL_COL_SPR
		}
		vHolder += holder
		VIndex++
	}
	return sql + vHolder
}

func (this *QueryBuilder) buildUpdateSQL() string {
	sql := "UPDATE " + this.TargetTable
	if this.UpdateData != nil {
		//        sql += " SET "
		i, tempHolders := 0, make([]string, len(this.UpdateData))
		for key, _ := range this.UpdateData {
			tempHolders[i] = key + " = ?"
			i++
		}
		if len(tempHolders) > 0 {
			sql += " SET " + strings.Join(tempHolders, SQL_COL_SPR)
		}
	}
	if this.HasConds() {
		sql += " WHERE " + this.Conditions.SQL
	}
	return sql
}

func (this *QueryBuilder) buildDeleteSQL() string {
	sql := "DELETE FROM " + this.TargetTable
	if this.HasConds() {
		sql += " WHERE " + this.Conditions.SQL
	}
	return sql;
}

// go 1.4.2 这里返回一个nil会自动转为一个 [] 空数组
func (this *QueryBuilder) GetParams() []interface {} {
	switch this.query {
		case QUERY_SELECT :
		return this.getSelectParams()
		case QUERY_INSERT :
		return this.getInsertParams()
		case QUERY_UPDATE :
		return this.getUpdateParams()
		case QUERY_DELETE :
		return this.getDeleteParams()
	}
	return nil
}

func (this *QueryBuilder) getSelectParams() []interface {} {
	if this.Conditions == nil {
		return nil
	}
	return this.Conditions.Params
}

func (this *QueryBuilder) getInsertParams() []interface {} {
	rows := len(this.InsertData)
	size := len(this.Columns)
	if rows <= 0 || size <= 0 {
		return nil
	}
	params := make([]interface{}, size * rows)
	for i := 0; i < rows; i++ {
		vSize := len(this.InsertData[i])
		for j := 0; j < size; j++ {
			index := i * size + j
			if j > vSize - 1 {
				params[index] = nil
			} else {
				params[index] = this.InsertData[i][j]
			}
		}
	}
	return params
}

func (this *QueryBuilder) getUpdateParams() []interface {} {
	// 先要拿到总长度
	dataSize := 0
	condSize := 0
	if this.UpdateData != nil {
		dataSize += len(this.UpdateData)
	}
	if this.HasConds() {
		condSize = len(this.Conditions.Params)
	}
	// 构建一个总索引和切片
	index, params := 0, make([]interface{}, dataSize + condSize)
	if dataSize > 0 {
		for _, value := range this.UpdateData {
			params[index] = value
			index++
		}
	}
	// 查询在后
	if condSize > 0 {
		for i := 0; i < condSize; i++ {
			params[index] = this.Conditions.Params[i]
			index++
		}
	}
	// 1.到底是先分配了长度，循环放入，性能高呢？
	// 2.还是先拿到updateData的数据，再append性能高呢？
	// 初步觉得，1.方法性能会高，因为切片的长度在初始化赋值的时候就给了，2.需要修改切片长度，2.会相对优雅
	return params
}

func (this *QueryBuilder) getDeleteParams() []interface {} {
	if this.Conditions == nil {
		return nil
	}
	return this.Conditions.Params
}

func (this *QueryBuilder) GetExecSQLParams() (string, []interface{}) {
	switch this.query {
		case QUERY_SELECT :
		return this.buildSelectSQL(), this.getSelectParams()
		case QUERY_INSERT :
		return this.buildInsertSQL(), this.getInsertParams()
		case QUERY_UPDATE :
		return this.getUpdateExecSQLParams()
		case QUERY_DELETE :
		return this.buildDeleteSQL(), this.getDeleteParams()
	}
	return "", nil
}

func (this *QueryBuilder) getUpdateExecSQLParams() (string, []interface{}) {

	sql := "UPDATE " + this.TargetTable

	dataSize := 0
	condSize := 0
	if this.UpdateData != nil {
		dataSize += len(this.UpdateData)
	}
	if this.HasConds() {
		condSize = len(this.Conditions.Params)
	}
	index, params := 0, make([]interface{}, dataSize + condSize)

	if dataSize > 0 {
		tempHolders := make([]string, len(this.UpdateData))
		for key, value := range this.UpdateData {
			tempHolders[index] = key + " = ?"
			params[index] = value
			index++
		}

		if len(tempHolders) > 0 {
			sql += " SET " + strings.Join(tempHolders, SQL_COL_SPR)
		}
	}
	if condSize > 0 {
		sql += " WHERE " + this.Conditions.SQL
		for i := 0; i < condSize; i++ {
			params[index] = this.Conditions.Params[i]
			index++
		}
	}
	return sql, params
}


/////////////////////////////////////////////////////////////////////////
// Conditions
/////////////////////////////////////////////////////////////////////////

func (this *Conditions) And(sql string, params ...interface{}) (*Conditions) {
	return this
}

func (this *Conditions) Push(marry int, sql string, params ...interface{}) (*Conditions) {
	return this.PushSQL(marry, sql).PushParams(params...)
}

func (this *Conditions) PushSQL(marry int, sql string) (*Conditions) {
	if len(sql) > 0 {
		if len(this.SQL) > 0 {
			this.SQL += " " + Marry(marry) + " " + sql
		} else {
			this.SQL = sql
		}
	}
	return this
}

func (this *Conditions) PushParams(params ...interface{}) (*Conditions) {
	if len(params) > 0 {
		if len(this.Params) > 0 {
			this.Params = append(this.Params, params...)
		} else {
			this.Params = params
		}
	}
	return this
}

func (this *Conditions) IsEmpty() (bool) {
	return len(this.SQL) <= 0
}