package dbUtils

import (
	"context"
	sql2 "database/sql"
	"errors"
	"fmt"
	"gitee.com/qq358678184_admin/gcore2/customTypes/dateTime"
	"gitee.com/qq358678184_admin/gcore2/helper"
	"gitee.com/qq358678184_admin/gcore2/logHelper"
	"strconv"
	"strings"
	"time"
)

type MsSqlQueryable2 struct {
	IQueryable2
	TableName string
	Prefix    string
	Dbkey     string
	WhereMap  map[string]interface{}
	OrderStr  strings.Builder
	takeSize  int32
	SetMap    map[string]interface{}
	//Session        *xorm.Session
	//engine   *xorm.Engine
	//useTransaction bool
	dao         *BaseDao2
	QueryFields string
}

func (queryable *MsSqlQueryable2) SelectFields(fields string) IQueryable2 {
	queryable.QueryFields = fields
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) GetWhereMap() map[string]interface{} {
	return queryable.WhereMap
}

func (queryable *MsSqlQueryable2) From(tableName string) IQueryable2 {
	queryable.TableName = tableName
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) Take(size int32) IQueryable2 {
	queryable.takeSize = size
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) convertArg(arg interface{}) interface{} {
	t, ok := arg.(time.Time)
	if ok {
		return t.Format(dateTime.TimeFormart)
	}
	t2, ok2 := arg.(*time.Time)
	if ok2 {
		return t2.Format(dateTime.TimeFormart)
	}
	return arg
}

func (queryable *MsSqlQueryable2) WhereIf(condition string, arg interface{}, condtion bool) IQueryable2 {
	if condtion {
		queryable.WhereMap[condition] = queryable.convertArg(arg)
	}
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) Where(condition string, arg interface{}) IQueryable2 {
	return queryable.WhereIf(condition, arg, true)
}

func (queryable *MsSqlQueryable2) WhereIn(fieldName string, args []interface{}, condition bool) IQueryable2 {
	if !condition {
		return queryable
	}

	builder := strings.Builder{}
	builder.WriteString(fmt.Sprintf(" and %s in(", fieldName))
	//inArgs := make([]interface{}, 0)
	for i, _ := range args {
		if i == 0 {
			builder.WriteString("?")
		} else {
			builder.WriteString(",?")
		}
	}
	builder.WriteString(")")

	return queryable.WhereIf(builder.String(), args, true)
	//return queryable.WhereIf(" and "+fieldName+" in "+builder.String(), nil, true)
}

func (queryable *MsSqlQueryable2) In(fieldName string, args ...interface{}) IQueryable2 {
	builder := strings.Builder{}
	builder.WriteString(fmt.Sprintf(" and %s in(", fieldName))
	inArgs := make([]interface{}, 0)
	for i, _ := range args {
		if i == 0 {
			builder.WriteString("?")
		} else {
			builder.WriteString(",?")
		}
	}
	builder.WriteString(")")
	inArgs = append(inArgs, args...)
	return queryable.WhereIf(builder.String(), inArgs, true)
}

func (queryable *MsSqlQueryable2) Order(orderField string) IQueryable2 {
	if queryable.OrderStr.Len() == 0 {
		queryable.OrderStr.WriteString(" order by " + orderField + " asc ")
	} else {
		queryable.OrderStr.WriteString(" , " + orderField + " asc ")
	}
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) OrderByDesc(orderField string) IQueryable2 {
	if queryable.OrderStr.Len() == 0 {
		queryable.OrderStr.WriteString(" order by " + orderField + " desc ")
	} else {
		queryable.OrderStr.WriteString(" , " + orderField + " desc ")
	}
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) Page(pageIndex int, pageSize int, beansPtr interface{}, ctx context.Context) *helper.QueryResult {
	if pageIndex <= 0 {
		panic(errors.New(fmt.Sprintf("pageIndex is %d", pageIndex)))
	}
	var res = new(helper.QueryResult)
	defer queryable.dao.Close()
	builder := strings.Builder{}
	countBuilder := strings.Builder{}
	countBuilder.WriteString(fmt.Sprintf("select count(0) as tableRowsCount from %s with(nolock)  ", queryable.TableName))
	builder.WriteString(fmt.Sprintf("select * from %s  with(nolock)  ", queryable.TableName))

	_, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()

	builder.WriteString(whereSql)
	builder.WriteString(queryable.OrderStr.String())
	builder.WriteString(fmt.Sprintf(" OFFSET　%d ROW  FETCH NEXT %d ROW ONLY", (pageIndex-1)*pageSize, pageSize))

	countBuilder.WriteString(whereSql)

	var err error
	err = queryable.dao.getDbSession(ctx).SQL(builder.String(), whereArgs...).Find(beansPtr)
	if err != nil {
		logHelper.Error(err)
	}
	var sqlOrArgs = make([]interface{}, 0)
	sqlOrArgs = append(sqlOrArgs, countBuilder.String())
	sqlOrArgs = append(sqlOrArgs, whereArgs...)

	var result []map[string][]byte
	result, _ = queryable.dao.getDbSession(ctx).Query(sqlOrArgs...)
	if result != nil {
		res.TotalRecords, _ = strconv.ParseInt(string(result[0]["tableRowsCount"]), 10, 32)
	}
	res.PageSize = pageSize
	res.PageIndex = pageIndex
	if res.TotalRecords%int64(pageSize) == 0 {
		res.TotalPage = res.TotalRecords / int64(pageSize)
	} else {
		res.TotalPage = res.TotalRecords/int64(pageSize) + 1
	}
	res.PageCount = res.TotalPage
	res.ResultList = beansPtr

	queryable.WhereMap = nil
	return res
}

func (queryable *MsSqlQueryable2) List(beansPtr interface{}, ctx context.Context) {
	defer queryable.dao.Close()
	builder := strings.Builder{}
	if queryable.takeSize == 0 {
		builder.WriteString(fmt.Sprintf("select * from %s with(nolock)  ", queryable.TableName))

	} else {
		builder.WriteString(fmt.Sprintf("select top %d * from %s with(nolock)  ", queryable.takeSize, queryable.TableName))
	}
	_, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()
	builder.WriteString(whereSql)
	builder.WriteString(queryable.OrderStr.String())
	var err error
	err = queryable.dao.getDbSession(ctx).SQL(builder.String(), whereArgs...).Find(beansPtr)
	if err != nil {
		logHelper.Error(err)
	}
}

func (queryable *MsSqlQueryable2) FirstOrDefault(beanPtr interface{}, ctx context.Context) {
	defer queryable.dao.Close()
	builder := strings.Builder{}
	builder.WriteString(fmt.Sprintf("select top 1 * from %s with(nolock) ", queryable.TableName))
	_, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()
	if whereSql != "" {
		builder.WriteString(whereSql)
	}
	if queryable.OrderStr.Len() > 0 {
		builder.WriteString(queryable.OrderStr.String())
	}
	var err error
	_, err = queryable.dao.getDbSession(ctx).SQL(builder.String(), whereArgs...).Get(beanPtr)
	if err != nil {
		logHelper.Error(err)
	}
}

func (queryable *MsSqlQueryable2) Delete(ctx context.Context) int64 {
	defer queryable.dao.Close()
	builder := strings.Builder{}
	builder.WriteString(fmt.Sprintf("delete from %s ", queryable.TableName))

	whereKeys, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()
	if whereSql != "" {
		builder.WriteString(whereSql)
	}
	var sqlOrArgs = make([]interface{}, 0)
	sqlOrArgs = append(sqlOrArgs, builder.String())
	if len(whereArgs) > 0 {
		sqlOrArgs = append(sqlOrArgs, whereArgs...)
	}
	var exec sql2.Result
	var err error
	exec, err = queryable.dao.getDbSession(ctx).Exec(sqlOrArgs...)
	if err != nil {
		return 0
	}
	var rows, _ = exec.RowsAffected()
	if queryable.dao.EnableSyncDb() {
		dbConfig := NewDbHelper().GetDbConfig(queryable.Dbkey)
		NewDbLogHelper().WriteDeletedLog(queryable.TableName, whereKeys, whereArgs, dbConfig.DataBase, queryable.Dbkey)
	}
	return rows
}

func (queryable *MsSqlQueryable2) Count(ctx context.Context) int64 {
	defer queryable.dao.Close()
	countBuilder := strings.Builder{}
	countBuilder.WriteString(fmt.Sprintf("select count(0) as tableRowsCount from %s ", queryable.TableName))

	_, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()
	if whereSql != "" {
		countBuilder.WriteString(whereSql)
	}
	var sqlOrArgs = make([]interface{}, 0)
	sqlOrArgs = append(sqlOrArgs, countBuilder.String())
	if len(whereArgs) > 0 {
		sqlOrArgs = append(sqlOrArgs, whereArgs...)
	}
	var err error
	var result []map[string][]byte
	result, err = queryable.dao.getDbSession(ctx).Query(sqlOrArgs...)
	if err != nil {
		logHelper.Error(err)
	}
	var totalRecords int64 = 0
	if result != nil {
		totalRecords, _ = strconv.ParseInt(string(result[0]["tableRowsCount"]), 10, 32)
	}
	queryable.WhereMap = nil
	return totalRecords
}

func (queryable *MsSqlQueryable2) Set(field string, arg interface{}) IQueryable2 {
	if queryable.SetMap == nil {
		queryable.SetMap = make(map[string]interface{})
	}
	queryable.SetMap[field] = arg
	return queryable.IQueryable2
}

func (queryable *MsSqlQueryable2) getWhereKeysAndArgsAndWhereSql() ([]string, []interface{}, string) {
	var whereArgs = make([]interface{}, 0)
	var whereKeys = make([]string, 0)
	if len(queryable.WhereMap) > 0 {
		for k := range queryable.WhereMap {
			whereKeys = append(whereKeys, k)
		}
	}
	for _, key := range whereKeys {
		elems := queryable.WhereMap[key]
		if elems != nil {
			if ids, ok := elems.([]interface{}); ok {
				for _, id := range ids {
					whereArgs = append(whereArgs, id)
				}
			} else if strArr, ok := elems.([]string); ok {
				for _, id := range strArr {
					whereArgs = append(whereArgs, id)
				}
			} else {
				whereArgs = append(whereArgs, elems)
			}
		}
	}
	whereSql := strings.Builder{}
	for index, key := range whereKeys {
		if index == 0 {
			whereSql.WriteString(fmt.Sprintf(" where 1=1 %s ", key))
		} else {
			whereSql.WriteString(fmt.Sprintf(" %s ", key))
		}
	}
	return whereKeys, whereArgs, whereSql.String()
}

func (queryable *MsSqlQueryable2) Update(ctx context.Context) bool {
	if queryable.SetMap == nil {
		return false
	}
	defer queryable.dao.Close()
	builder := strings.Builder{}
	builder.WriteString(fmt.Sprintf("update %s ", queryable.TableName))

	var setArgs = make([]interface{}, 0)
	var setKeys = make([]string, 0)
	if len(queryable.SetMap) > 0 {
		for k := range queryable.SetMap {
			setKeys = append(setKeys, k)
		}
	}

	for index, key := range setKeys {
		if index == 0 {
			builder.WriteString(fmt.Sprintf("set %s=?", key))
		} else {
			builder.WriteString(fmt.Sprintf(",%s=?", key))
		}
	}

	for _, key := range setKeys {
		if queryable.SetMap[key] != nil {
			setArgs = append(setArgs, queryable.SetMap[key])
		}
	}

	whereKeys, whereArgs, whereSql := queryable.getWhereKeysAndArgsAndWhereSql()
	if whereSql != "" {
		builder.WriteString(whereSql)
	}
	sql := builder.String()
	var sqlOrArgs = make([]interface{}, 0)
	sqlOrArgs = append(sqlOrArgs, sql)
	if len(setArgs) > 0 {
		sqlOrArgs = append(sqlOrArgs, setArgs...)
	}
	if len(whereArgs) > 0 {
		sqlOrArgs = append(sqlOrArgs, whereArgs...)
	}
	var exec sql2.Result
	var err error
	exec, err = queryable.dao.getDbSession(ctx).Exec(sqlOrArgs...)
	if err != nil {
		logHelper.Error(err)
		return false
	}
	var rows, _ = exec.RowsAffected()
	if rows > 0 {
		if queryable.dao.EnableSyncDb() {
			dbConfig := NewDbHelper().GetDbConfig(queryable.Dbkey)
			NewDbLogHelper().WriteUpdatedLog(queryable.TableName, setKeys, setArgs, whereKeys, whereArgs, dbConfig.DataBase, queryable.Dbkey)
		}
	}
	return rows > 0
}