database/option.go

// Package database ...
//
// Description : mysql ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 2022-05-15 11:43
package database

import (
	"encoding/json"
	"errors"
	"fmt"
	"git.zhangdeman.cn/zhangdeman/consts"
	"git.zhangdeman.cn/zhangdeman/database/define"
	"git.zhangdeman.cn/zhangdeman/op_type"
	"git.zhangdeman.cn/zhangdeman/serialize"
	"reflect"
	"strings"
)

// WithModel ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 15:18 2024/1/15
func WithModel(model any) define.SetOption {
	return func(o *define.Option) {
		o.Model = model
	}
}

// WithTable 设置查询的表名
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 20:56 2023/3/22
func WithTable(tableName string) define.SetOption {
	return func(o *define.Option) {
		o.Table = tableName
	}
}

// WithWhere 设置where条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 12:17 2022/5/15
func WithWhere[T op_type.BaseType](where map[string]T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Where {
			o.Where = make(map[string]any)
		}
		for field, value := range where {
			o.Where[field] = value
		}
	}
}

// WithLimit 设置limit条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 12:00 2022/5/15
func WithLimit[T op_type.Int](limit T, offset T) define.SetOption {
	return func(o *define.Option) {
		o.Limit = int(limit)
		o.Offset = int(offset)
	}
}

// WithLimitByPageAndSize 通过page和size构建条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 21:42 2023/10/14
func WithLimitByPageAndSize[T op_type.Int](page T, size T) define.SetOption {
	return func(o *define.Option) {
		if size > 0 {
			o.Limit = int(size)
		}
		if page <= 0 {
			page = 1
		}
		o.Offset = int((page - 1) * size)
	}
}

// WithIn 设置in条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 12:23 2022/5/15
func WithIn[T op_type.Array](field string, value T) define.SetOption {
	return func(o *define.Option) {
		if nil == value {
			return
		}
		if len(value) == 0 {
			return
		}
		if nil == o.In {
			o.In = make(map[string]any)
		}
		o.In[field] = value
	}
}

// WithBatchIn ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 12:24 2022/5/15
func WithBatchIn[T op_type.Array](batchIn map[string]T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.In {
			o.In = make(map[string]any)
		}
		for field, value := range batchIn {
			WithIn(field, value)
		}
	}
}

// WithNotIn 设置 notin 条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 16:18 2022/5/15
func WithNotIn[T op_type.Array](field string, value T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotIn {
			o.NotIn = make(map[string]any)
		}
		if value == nil || len(value) == 0 {
			return
		}
		o.NotIn[field] = value
	}
}

// WithBatchNotIn 批量设置 NOT IN
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 16:23 2022/5/15
func WithBatchNotIn[T op_type.Array](data map[string]T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotIn {
			o.NotIn = make(map[string]any)
		}
		for field, value := range data {
			if value == nil || len(value) == 0 {
				continue
			}
			o.NotIn[field] = value
		}
	}
}

// WithStart >= 条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:01 2022/5/15
func WithStart(field string, value any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Start {
			o.Start = make(map[string]any)
		}
		o.Start[field] = value
	}
}

// WithBatchStart 批量设置起始条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:03 2022/5/15
func WithBatchStart(data map[string]any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Start {
			o.Start = make(map[string]any)
		}
		for field, value := range data {
			o.Start[field] = value
		}
	}
}

// WithEnd 设置 < 条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:07 2022/5/15
func WithEnd(field string, value any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.End {
			o.End = make(map[string]any)
		}
		o.End[field] = value
	}
}

// WithBatchEnd 批量设置 < 条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:10 2022/5/15
func WithBatchEnd(data map[string]any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.End {
			o.End = make(map[string]any)
		}
		for field, value := range data {
			o.End[field] = value
		}
	}
}

// WithLike 设置 like 查询条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:16 2022/5/15
func WithLike(field string, value string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Like {
			o.Like = make(map[string]string)
		}
		if len(value) == 0 {
			return
		}
		o.Like[field] = value
	}
}

// WithBatchLike 批量设置like条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:19 2022/5/15
func WithBatchLike(data map[string]string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Like {
			o.Like = make(map[string]string)
		}
		for field, value := range data {
			if len(value) == 0 {
				continue
			}
			o.Like[field] = value
		}
	}
}

// WithNotLike NOT LIKE 语句
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:50 2022/5/15
func WithNotLike(field string, value string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotLike {
			o.NotLike = make(map[string]string)
		}
		if len(value) == 0 {
			return
		}
		o.NotLike[field] = value
	}
}

// WithBatchNotLike ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:52 2022/5/15
func WithBatchNotLike(data map[string]string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotLike {
			o.NotLike = make(map[string]string)
		}
		for field, value := range data {
			if len(value) == 0 {
				continue
			}
			o.NotLike[field] = value
		}
	}
}

// WithNotEqual 设置不等于语句
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:31 2022/5/15
func WithNotEqual[T op_type.BaseType](field string, value T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotEqual {
			o.NotEqual = make(map[string]any)
		}
		o.NotEqual[field] = value
	}
}

// WithBatchNotEqual 批量设置不等于条件
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:33 2022/5/15
func WithBatchNotEqual[T op_type.BaseType](data map[string]T) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotEqual {
			o.NotEqual = make(map[string]any)
		}
		for field, value := range data {
			o.NotEqual[field] = value
		}
	}
}

// WithOR 设置OR语句
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 20:03 2022/7/23
func WithOR(orConditionList ...define.SetOption) define.SetOption {
	return func(o *define.Option) {
		if nil == o.OR {
			o.OR = make([][]define.SetOption, 0)
		}
		o.OR = append(o.OR, orConditionList)
	}
}

// WithOrder 设置排序规则
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 20:15 2022/10/20
func WithOrder(orderRuleList ...string) define.SetOption {
	return func(o *define.Option) {
		o.Order = orderRuleList
	}
}

// WithOrderDesc 降序排序
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 15:09 2023/4/3
func WithOrderDesc(field string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Order {
			o.Order = make([]string, 0)
		}
		o.Order = append(o.Order, field+" desc")
	}
}

// WithOrderAsc 升序排序
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 15:09 2023/4/3
func WithOrderAsc(field string) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Order {
			o.Order = make([]string, 0)
		}
		o.Order = append(o.Order, field+" asc")
	}
}

// newOption 生成新的option
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:46 2024/8/9
func newOption(setOptionList ...define.SetOption) *define.Option {
	o := &define.Option{}
	for _, item := range setOptionList {
		item(o)
	}
	return o
}

// optionToSql 基于 option 配置生成sql
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 17:46 2024/8/9
func optionToSql(o *define.Option) (sqlBuildResult string, bindValue []any) {
	bindValue = make([]any, 0)
	sqlBuildResultBlockList := make([]string, 0)
	// 设置where条件
	for fieldName, fieldValue := range o.Where {
		if nil == fieldValue {
			continue
		}
		if reflect.TypeOf(fieldValue).Kind() == reflect.Slice {
			// 传入数组, in语句
			placeholder, dataList := parseInSql(fieldValue)
			sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` IN ("+placeholder+")")
			bindValue = append(bindValue, dataList...)
		} else {
			sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` = ?")
			bindValue = append(bindValue, fieldValue)
		}
	}
	// 设置不等于
	for fieldName, fieldValue := range o.NotEqual {
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` != ?")
		bindValue = append(bindValue, fieldValue)
	}

	// in 语句
	// 传入数组, in语句
	for fieldName, fieldValue := range o.In {
		placeholder, dataList := parseInSql(fieldValue)
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` IN ("+placeholder+")")
		bindValue = append(bindValue, dataList...)
	}

	// not in 语句
	for fieldName, fieldValue := range o.NotIn {
		placeholder, dataList := parseInSql(fieldValue)
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` NOT IN ("+placeholder+")")
		bindValue = append(bindValue, dataList...)
	}

	// like 语句
	for fieldName, fieldValue := range o.Like {
		if len(fieldValue) == 0 {
			continue
		}
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` LIKE \"%?%\"")
		bindValue = append(bindValue, fieldValue)
	}

	// NOT LIKE 语句
	for fieldName, fieldValue := range o.NotLike {
		if len(fieldValue) == 0 {
			continue
		}
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` NOT LIKE \"%?%\"")
		bindValue = append(bindValue, fieldValue)
	}

	// >=
	for fieldName, fieldValue := range o.Start {
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` >= ?")
		bindValue = append(bindValue, fieldValue)
	}

	// <
	for fieldName, fieldValue := range o.End {
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+fieldName+"` < ?")
		bindValue = append(bindValue, fieldValue)
	}

	// between
	for field, betweenVal := range o.Between {
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+field+"` BETWEEN ? AND ?")
		bindValue = append(bindValue, betweenVal[0], betweenVal[1])
	}
	// not between
	for field, notBetweenVal := range o.NotBetween {
		sqlBuildResultBlockList = append(sqlBuildResultBlockList, "`"+field+"` NOT BETWEEN ? AND ?")
		bindValue = append(bindValue, notBetweenVal[0], notBetweenVal[1])
	}

	if len(bindValue) > 0 {
		sqlBuildResult = strings.Join(sqlBuildResultBlockList, " AND ")
	}
	return
}

// parseInSql 解析in语句需要绑定占位符以及数据
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 22:07 2024/8/9
func parseInSql(fieldValue any) (string, []any) {
	byteData, _ := json.Marshal(fieldValue)
	var dataList []any
	decoder := json.NewDecoder(strings.NewReader(string(byteData)))
	decoder.UseNumber()
	_ = decoder.Decode(&dataList)
	// 生成占位符
	placeholderList := make([]string, 0)
	for i := 0; i < len(dataList); i++ {
		placeholderList = append(placeholderList, "?")
	}
	return strings.Join(placeholderList, ","), dataList
}

// WithBetween between 语句
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 15:52 2024/8/23
func WithBetween(field string, left any, right any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.Between {
			o.Between = map[string][2]any{}
		}
		o.Between[field] = [2]any{left, right}
	}
}

// WithNotBetween not between 语句
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 15:52 2024/8/23
func WithNotBetween(field string, left any, right any) define.SetOption {
	return func(o *define.Option) {
		if nil == o.NotBetween {
			o.NotBetween = map[string][2]any{}
		}
		o.NotBetween[field] = [2]any{left, right}
	}
}

// WithAnyCondition 设置任意查询条件, 仅 where 子句 in / not in / like / not like / == / !=
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 14:48 2024/8/23
func WithAnyCondition(column string, operate string, value any) (define.SetOption, error) {
	if nil == value {
		return nil, errors.New("value is nil")
	}
	switch operate {
	case consts.WhereOperateEqual:
		return WithWhere(map[string]any{column: value}), nil
	case consts.WhereOperateNotEqual:
		return WithNotEqual(column, value), nil
	case consts.WhereOperateIn:
		var target []any
		if err := serialize.JSON.Transition(value, &target); nil != err {
			return nil, err
		}
		return WithIn[[]any](column, target), nil
	case consts.WhereOperateNotIn:
		var target []any
		if err := serialize.JSON.Transition(value, &target); nil != err {
			return nil, err
		}
		return WithNotIn[[]any](column, target), nil
	case consts.WhereOperateLike:
		return WithLike(column, fmt.Sprintf("%v", value)), nil
	case consts.WhereOperateNotLike:
		return WithNotLike(column, fmt.Sprintf("%v", value)), nil
	default:
		return nil, errors.New(operate + " : operate is not support")
	}
}