package database

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"gitee.com/ccait-dev/fast-api/fast/database/entity"
	"gitee.com/ccait-dev/fast-api/fast/database/express"
	"gitee.com/ccait-dev/fast-api/fast/types"
	"gitee.com/ccait-dev/fast-api/fast/utils/jsonUtil"
	"gorm.io/gorm"
	"log"
	"math"
	"reflect"
	"strings"
	"time"
)

var (
	MAX_DATA int64
)

type DBHelper struct {
	table string
}

type IDBHelper interface {
	Insert(db *gorm.DB, model interface{}) *gorm.DB
	Delete(db *gorm.DB, model interface{}) *gorm.DB
	Update(db *gorm.DB, id string, model interface{}) *gorm.DB
	Save(db *gorm.DB, model interface{}) *gorm.DB
	Find(db *gorm.DB, query interface{}) entity.Result
	First(db *gorm.DB, query interface{}) entity.Result
	Last(db *gorm.DB, query interface{}) entity.Result
	Count(db *gorm.DB, query interface{}) int64
	Page(db *gorm.DB, query interface{}, page entity.Page) entity.Result
	QueryDict(db *gorm.DB, columns types.ColumnInfos) map[string]map[interface{}]interface{}
}

func init() {
	MAX_DATA = 1000000
}

func NewDBHelper() *DBHelper {
	return &DBHelper{table: ``}
}

func NewDBHelperByTable(table string) *DBHelper {
	return &DBHelper{table: jsonUtil.Camel2Case(table)}
}

func (helper *DBHelper) Insert(db *gorm.DB, model interface{}) *gorm.DB {
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	return db.Create(model)
}

func (helper *DBHelper) Delete(db *gorm.DB, model interface{}) *gorm.DB {
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	var result interface{}
	return db.Where(model).Delete(result)
}

func (helper *DBHelper) Save(db *gorm.DB, model interface{}) *gorm.DB {
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	return db.Save(model)
}

func (helper *DBHelper) Update(db *gorm.DB, id any, model interface{}) *gorm.DB {
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	return db.Where("id=?", id).Omit("id").Updates(model)
}

func (helper *DBHelper) Find(db *gorm.DB, query interface{}, orderBy interface{}, selects interface{}) entity.Result {
	var err error
	var model map[string]interface{}
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	rows, err := db.Where(whereWarp(query)).Order(orderWarp(orderBy)).Select(selectWarp(selects)).Find(&model).Rows()
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	return entity.Result{
		Data: getRowsData(db, rows),
	}
}

func (helper *DBHelper) First(db *gorm.DB, query interface{}, orderBy interface{}, selects interface{}) entity.Result {
	var err error
	var model map[string]interface{}
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	rows, err := db.Where(whereWarp(query)).Order(orderWarp(orderBy)).Select(selectWarp(selects)).First(&model).Rows()
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	return entity.Result{
		Data: getRowsData(db, rows),
	}
}

func (helper *DBHelper) Last(db *gorm.DB, query interface{}, orderBy interface{}, selects interface{}) entity.Result {
	var err error
	var model map[string]interface{}
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	rows, err := db.Where(whereWarp(query)).Order(orderWarp(orderBy)).Select(selectWarp(selects)).Last(&model).Rows()
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	return entity.Result{
		Data: getRowsData(db, rows),
	}
}

func (helper *DBHelper) Count(db *gorm.DB, query interface{}) int64 {
	var count int64
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	db.Where(query).Count(&count)
	return count
}

func (helper *DBHelper) Page(db *gorm.DB, query interface{}, page entity.Page, orderBy interface{}, selects interface{}) entity.Result {
	// 分页查询
	offset := (page.PageIndex - 1) * page.PageSize
	var err error
	var model map[string]interface{}
	if strings.TrimSpace(helper.table) != "" {
		db = db.Table(helper.table)
	}
	rows, err := db.Where(whereWarp(query)).Order(orderWarp(orderBy)).Select(selectWarp(selects)).Offset(offset).Limit(page.PageSize).Find(&model).Rows()
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	data := getRowsData(db, rows)
	var count int64
	db.Where(whereWarp(query)).Order(orderWarp(orderBy)).Select(selectWarp(selects)).Offset(offset).Limit(page.PageSize).Count(&count)
	page.TotalPages = int(math.Ceil(float64(count) / float64(page.PageSize)))
	return entity.Result{
		Page:  page,
		Data:  data,
		Total: count,
	}
}

func (helper *DBHelper) QueryDict(db *gorm.DB, columns types.ColumnInfos) map[string]map[interface{}]interface{} {
	dict := make(map[string]map[interface{}]interface{})
	for _, column := range columns {
		if column.IgnoreField {
			continue
		}
		if column.DictQuery.Empty() {
			continue
		}
		table := jsonUtil.Camel2Case(express.ReplaceInjectChar(column.DictQuery.Table))
		nameFields := make([]string, 0)
		column.DictQuery.NameField = strings.TrimSpace(column.DictQuery.NameField)
		if strings.HasPrefix(column.DictQuery.NameField, "[") &&
			strings.HasSuffix(column.DictQuery.NameField, "]") {
			var result []string
			err := json.Unmarshal([]byte(column.DictQuery.NameField), &result)
			if err != nil {
				log.Fatalf("JSON Unmarshal failed: %v", err)
				continue
			} else {
				for _, s := range result {
					if s != "" {
						nameFields = append(nameFields, fmt.Sprintf("%s.%s", table, jsonUtil.Camel2Case(strings.TrimSpace(s))))
					}
				}
			}
		}
		tbl := db.Table(table)
		if len(nameFields) > 1 {
			first := nameFields[0]
			var result = make([]string, 0)
			for i, field := range nameFields {
				if i == 0 {
					continue
				}
				if field != "" {
					result = append(result, field)
				}
			}
			if db.Name() == "mysql" || db.Name() == "sqlserver" {
				tbl = tbl.Select(
					fmt.Sprintf("%s.%s as id, CONCAT(%s, '(', %s, ')') as name", column.DictQuery.Table,
						jsonUtil.Camel2Case(column.DictQuery.IdField),
						first,
						strings.Join(result, ", ',', ")))
			} else {
				tbl = tbl.Select(
					fmt.Sprintf("%s.%s as id, %s || '(', %s, ')' as name", column.DictQuery.Table,
						jsonUtil.Camel2Case(column.DictQuery.IdField),
						first,
						strings.Join(result, " || ',' || ")))
			}

		} else {
			tbl = tbl.Select(
				fmt.Sprintf("%s.%s as id, %s.%s as name", column.DictQuery.Table,
					jsonUtil.Camel2Case(column.DictQuery.IdField),
					table,
					jsonUtil.Camel2Case(column.DictQuery.NameField)))
		}

		for _, condition := range column.DictQuery.Conditions {
			if strings.TrimSpace(condition.Name) == "" ||
				strings.TrimSpace(condition.Algorithm) == "" ||
				condition.Value == nil {
				continue
			}
			if condition.Algorithm == "CONTAINS" {
				tbl = tbl.Where(fmt.Sprintf("`%s` like '%s?%s'", jsonUtil.Camel2Case(condition.Name), "%", "%"), condition.Value)
			} else if condition.Algorithm == "BEGIN" {
				tbl = tbl.Where(fmt.Sprintf("`%s` like '?%s'", jsonUtil.Camel2Case(condition.Name), "%"), condition.Value)
			} else if condition.Algorithm == "END" {
				tbl = tbl.Where(fmt.Sprintf("`%s` like '?%s'", jsonUtil.Camel2Case(condition.Name), "%"), condition.Value)
			} else {
				if express.GetAlgorithm(condition.Algorithm) == "" {
					continue
				}
				tbl = tbl.Where(fmt.Sprintf("`%s`%s?", jsonUtil.Camel2Case(condition.Name), condition.Algorithm), condition.Value)
			}
		}
		for _, fieldGroup := range column.DictQuery.GroupByFields {
			if strings.TrimSpace(fieldGroup) == "" {
				continue
			}
			tbl = tbl.Group(jsonUtil.Camel2Case(fieldGroup))
		}
		var list []map[string]interface{}
		tbl.Find(&list)
		row := make(map[interface{}]interface{})
		for _, m := range list {
			row[strings.ToLower(m["name"].(string))] = m["id"]
		}
		dict[column.DictQuery.Table] = row
	}
	return dict
}

func getRowsData(db *gorm.DB, rows *sql.Rows) any {
	var result []map[string]interface{}
	for rows.Next() {
		err2 := db.ScanRows(rows, &result)
		if err2 != nil {
			fmt.Println(err2)
		}
		if !rows.NextResultSet() {
			break
		}
	}

	return result
}

func whereWarp(param interface{}) any {
	var arr = make([]string, 0)
	if reflect.TypeOf(param).Kind() == reflect.Slice ||
		reflect.TypeOf(param).Kind() == reflect.Array {
		for _, item := range param.([]map[string]interface{}) {
			if item != nil {
				var arr2 = make([]string, 0)
				for key, value := range item {
					key = express.FieldCaseTo(key)
					if value == nil {
						arr2 = append(arr2, fmt.Sprintf("%s is null", key))
					} else {
						if reflect.TypeOf(value).Kind() == reflect.String && strings.HasPrefix(key, "$") {
							value := express.ParseElExpr(key, value.(string))
							if len(value) == 0 {
								arr2 = append(arr2, fmt.Sprintf("%s='%s'", key, express.ReplaceInjectChar(value)))
							} else {
								arr2 = append(arr2, value)
							}
						} else if reflect.TypeOf(value).Kind() == reflect.Struct && reflect.TypeOf(value) == reflect.TypeOf(time.Time{}) {
							arr2 = append(arr2, fmt.Sprintf("%s='%s'", key, value))
						} else {
							arr2 = append(arr2, fmt.Sprintf("%s='%s'", key, value))
						}
					}
				}
				arr = append(arr, strings.Join(arr2, " AND "))
			}
		}
		if len(arr) == 0 {
			return "1=1"
		}
		return strings.Join(arr, " OR ")
	} else {
		return param
	}
}

func orderWarp(param interface{}) any {
	var arr = make([]string, 0)
	if param == nil {
		return "id"
	}
	for key, value := range param.(map[string]string) {
		key = express.FieldCaseTo(key)
		if len(strings.TrimSpace(value)) == 0 ||
			value == "desc" || value == "Desc" || value == "DESC" {
			value = "desc"
		} else {
			value = "asc"
		}
		arr = append(arr, fmt.Sprintf("%s %s", key, express.ReplaceInjectChar(value)))
	}
	return strings.Join(arr, ",")
}

func selectWarp(param interface{}) any {
	var arr = make([]string, 0)
	if param == nil {
		return "*"
	}
	for _, key := range param.([]string) {
		arr = append(arr, express.FieldCaseTo(express.ReplaceInjectChar(key)))
	}
	return strings.Join(arr, ",")
}