package starrocks

import (
	"database/sql"
	"fmt"
	"github.com/zeromicro/go-zero/core/collection"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/domain"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/infrastructure/utils"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/log"
	"gorm.io/gorm"
	"reflect"
	"strings"
)

var AssertString = utils.AssertString

func Query(params QueryOptions, queryFunc func(params QueryOptions) (*sql.Rows, error)) (*domain.DataTable, error) {
	rows, err := queryFunc(params)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	dataTable := &domain.DataTable{}
	dataTable.Data, err = ScanRows(rows)

	//rows.Columns()
	return dataTable, err
}

type QueryOptions struct {
	Table     *domain.Table
	TableName string
	Select    []*domain.Field
	Where     []Condition
	Offset    int
	Limit     int
	Context   *domain.Context
}

func (o *QueryOptions) SetOffsetLimit(pageNumber, pageSize int) {
	if pageNumber == 0 {
		pageNumber = 1
	}
	if pageSize == 0 {
		pageSize = 20
	}
	o.Offset = (pageNumber - 1) * pageSize
	o.Limit = pageSize
}

func (o *QueryOptions) SetCondition(conditions []domain.Condition) *QueryOptions {
	for _, c := range conditions {
		o.Where = append(o.Where, Condition{
			Condition: c,
		})
	}
	return o
}

func (o *QueryOptions) SetDefaultOrder() *QueryOptions {
	hasOrder := false
	for _, c := range o.Where {
		if len(c.Order) > 0 {
			hasOrder = true
		}
	}
	// 没有排序的加一个排序，才能分页
	if !hasOrder {
		if o.Table != nil {
			if o.Table.PK == nil {
				//o.Where = append(o.Where, Condition{
				//	Condition: domain.Condition{
				//		Field: o.Table.DataFields[0],
				//		Order: "ASC",
				//	},
				//})
				return o
			}
			o.Where = append(o.Where, Condition{
				Condition: domain.Condition{
					Field: o.Table.PK,
					Order: "ASC",
				},
			})
			return o
		}
		o.Where = append(o.Where, Condition{
			Condition: domain.Condition{
				Field: domain.PK(),
				Order: "ASC",
			},
		})
	}
	return o
}

func (o *QueryOptions) AdditionOptionsByTable(table *domain.Table) *QueryOptions {
	if table.TableType != domain.ObjectDBTable {
		return o
	}
	switch table.TableId {
	case domain.DBTableTableOperateLog.ToInt():
		o.SetCondition([]domain.Condition{{
			Field: &domain.Field{
				SQLName: "log_type",
				SQLType: domain.String.ToString(),
			},
			In: []interface{}{domain.CommonLog.ToString()},
		}})
	case domain.DBTableQuerySetLog.ToInt():
		o.SetCondition([]domain.Condition{{
			Field: &domain.Field{
				SQLName: "log_type",
				SQLType: domain.String.ToString(),
			},
			In: []interface{}{domain.QuerySetLog.ToString()},
		}})
	case domain.DBTableFormulaLog.ToInt():
		o.SetCondition([]domain.Condition{{
			Field: &domain.Field{
				SQLName: "log_type",
				SQLType: domain.String.ToString(),
			},
			In: []interface{}{domain.FormulaLog.ToString()},
		}})
	}
	return o
}

type Condition struct {
	params QueryOptions
	domain.Condition
	Distinct      bool
	DisableFormat bool
}

func (c Condition) SetWhere(params QueryOptions, q *gorm.DB) {
	c.SetParams(params)
	if len(c.Like) > 0 {
		q.Where(fmt.Sprintf("%v like '%%%v%%'", c.FormatIfNull(params, c.Field), c.Like))
	}
	if len(c.In) > 0 {
		if c.Field.SQLType == domain.Float.ToString() {
			if hasEmpty(c.In) {
				q.Where(fmt.Sprintf("((%v in %v) or (%v is null))", c.CastType(c.Field.SQLName, domain.DECIMALV2.ToString()), c.InArgs(c.In), c.Field.SQLName))
			} else {
				q.Where(fmt.Sprintf("%v in %v", c.CastType(c.Field.SQLName, domain.DECIMALV2.ToString()), c.InArgs(c.In)))
			}
		} else if c.Field.SQLType == domain.Date.ToString() {
			q.Where(fmt.Sprintf("%v in %v", c.CastType(c.FormatIfNull(params, c.Field), "datetime"), c.InArgs(c.In)))
		} else {
			q.Where(fmt.Sprintf("%v in %v", c.CastType(c.FormatIfNull(params, c.Field), "string"), c.InArgs(c.In)))
		}
	}
	if len(c.Ex) > 0 {
		in := c.InArgs(c.Ex)
		q.Where(fmt.Sprintf("%v not in %v", c.FormatIfNull(params, c.Field), in))
	}
	if len(c.Range) > 0 {
		for _, item := range c.Range {
			if item.Op == "" {
				continue
			}
			opVal, ok := opMap[item.Op]
			if !ok {
				continue
			}
			val, err := domain.ValueToType(AssertString(item.Val), c.Field.SQLType)
			if err != nil {
				log.Logger.Error(err.Error())
				continue
			}
			q.Where(fmt.Sprintf("%s %s %s",
				c.FormatIfNull(params, c.Field),
				opVal,
				c.formatByOp(item.Op, val),
			))
		}
	}
	if c.Distinct {
		// 需要优化
		q.Distinct(c.FormatFiled(c.Field))
	}
	if len(c.Order) > 0 {
		q.Order(fmt.Sprintf("%v %v", c.Field.SQLName, c.Order))
	}
}

func (c *Condition) SetParams(params QueryOptions) {
	c.params = params
}

func (c Condition) FormatIfNull(params QueryOptions, f *domain.Field) string {
	if params.Table != nil && params.Table.TableType == domain.ObjectDBTable {
		return f.SQLName
	}
	if domain.SQLType(f.SQLType).IsString() {
		return fmt.Sprintf("ifnull(%s,'')", f.SQLName)
	}
	return f.SQLName
}

func (c Condition) FormatFiled(f *domain.Field) string {
	return formatFiled(c.Field)
}

func (c Condition) CastType(sql, t string) string {
	if c.params.Table != nil && c.params.Table.TableType == domain.ObjectDBTable {
		return sql
	}
	return castType(sql, t)
}

func (c Condition) CastTypeByField(f *domain.Field, t string) string {
	if c.params.Table != nil && c.params.Table.TableType == domain.ObjectDBTable {
		return f.SQLName
	}
	if f.SQLType == domain.Float.ToString() || f.SQLType == domain.DECIMAL279.ToString() {
		return castTypeAlias(f.SQLName, domain.DECIMALV2.ToString())
	}
	return castType(f.SQLName, t)
}

func formatFiled(f *domain.Field) string {
	//if f.SQLType == domain.Float.ToString() || f.SQLType == domain.DECIMAL279.ToString() {
	//	return castTypeAlias(f.SQLName, domain.DECIMALV2.ToString())
	//}
	return f.SQLName
}

func hasEmpty(in []interface{}) bool {
	for _, arg := range in {
		if v, ok := arg.(string); ok && len(v) == 0 {
			return true
		}
	}
	return false
}

func castType(sql, t string) string {
	return fmt.Sprintf("cast(%v as %v)", sql, t)
}

func castTypeAlias(sql, t string) string {
	return fmt.Sprintf("cast(%v as %v) %v", sql, t, sql)
}

var opMap = map[string]string{
	"=":        "=",
	">":        ">",
	"<":        "<",
	">=":       ">=",
	"<=":       "<=",
	"<>":       "<>",
	"like":     "like",
	"not like": "not like",
}

func (c Condition) formatByOp(op string, val interface{}) string {
	if op == "like" || op == "not like" {
		return fmt.Sprintf("'%%%s%%'", AssertString(val))
	}
	return c.Arg(val)
}

func (c Condition) InArgs(args interface{}) string {
	bytes := make([]byte, 0)
	bytes = appendIn(bytes, reflect.ValueOf(args))
	return string(bytes)
}

func (c Condition) Arg(args interface{}) string {
	bytes := make([]byte, 0)
	v := reflect.ValueOf(args)
	bytes = appendValue(bytes, v)
	return string(bytes)
}

func appendIn(b []byte, slice reflect.Value) []byte {
	sliceLen := slice.Len()
	b = append(b, '(')
	for i := 0; i < sliceLen; i++ {
		if i > 0 {
			b = append(b, ',')
		}

		elem := slice.Index(i)
		if elem.Kind() == reflect.Interface {
			elem = elem.Elem()
		}
		if elem.Kind() == reflect.Slice {
			//b = appendIn(b, elem)
		} else {
			b = appendValue(b, elem)
		}
	}
	b = append(b, ')')
	return b
}

func appendValue(b []byte, v reflect.Value) []byte {
	if v.Kind() == reflect.Ptr && v.IsNil() {

		return append(b, "NULL"...)
	}
	if v.Kind() == reflect.Int || v.Kind() == reflect.Int64 || v.Kind() == reflect.Float64 {
		return append(b, []byte(AssertString(v.Interface()))...)
	}
	b = append(b, []byte("'")...)
	b = append(b, []byte(AssertString(v.Interface()))...)
	b = append(b, []byte("'")...)
	return b
}

func DefaultQueryFunc(params QueryOptions) (*sql.Rows, error) {
	query := DB.Table(params.TableName)
	rows, err := query.Rows()
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func WrapQueryFuncWithDB(db *gorm.DB) func(QueryOptions) (*sql.Rows, error) {
	return func(params QueryOptions) (*sql.Rows, error) {
		query := db.Table(params.TableName)
		queryWithoutLimitOffset(query, params)
		if params.Offset > 0 {
			query.Offset(params.Offset)
		}
		if params.Limit > 0 {
			query.Limit(params.Limit)
		}
		if params.Context != nil {
			query.Where(fmt.Sprintf("context->>'companyId'='%v'", params.Context.CompanyId))
		}
		rows, err := query.Rows()
		if err != nil {
			return nil, err
		}
		return rows, nil
	}
}

func WrapDeleteFuncWithDB(db *gorm.DB) func(QueryOptions) (int64, error) {
	return func(params QueryOptions) (int64, error) {
		query := db.Table(params.TableName)
		queryWithoutLimitOffset(query, params)
		//if params.Offset > 0 {
		//	query.Offset(params.Offset)
		//}
		//if params.Limit > 0 {
		//	query.Limit(params.Limit)
		//}
		//if params.Context != nil {
		//	query.Where(fmt.Sprintf("context->>'companyId'='%v'", params.Context.CompanyId))
		//}
		rows, err := query.Rows()
		defer rows.Close()
		if err != nil {
			return 0, err
		}
		dataTable := &domain.DataTable{}
		dataTable.Data, err = ScanRows(rows)
		idList := make([]string, 0)
		for _, row := range dataTable.Data {
			if len(row) == 0 {
				continue
			}
			idList = append(idList, row[0])
			if len(idList) > 5000 {
				c := Condition{}
				sql := fmt.Sprintf("delete from %v where id in %v", params.TableName, c.InArgs(idList))
				query = db.Exec(sql)
				idList = make([]string, 0)
			}
		}
		if len(idList) == 0 {
			return 0, nil
		}
		c := Condition{}
		sql := fmt.Sprintf("delete from %v where id in %v", params.TableName, c.InArgs(idList))
		query = db.Exec(sql)
		return int64(len(idList)), query.Error
	}
}

func SetTable(query *gorm.DB, tableName string) {
	query.Statement.Table = tableName
}

func queryWithoutLimitOffset(query *gorm.DB, params QueryOptions) {
	if len(params.Select) > 0 {
		fields := make([]string, 0)
		for _, f := range params.Select {
			if f.Flag == domain.ManualField {
				fields = append(fields, "'' "+f.SQLName)
				continue
			}
			fields = append(fields, formatFiled(f))
		}
		query.Select(strings.Join(fields, ","))
	}
	if len(params.Where) > 0 {
		for _, w := range params.Where {
			if w.Field != nil && w.Field.Flag == domain.ManualField {
				continue
			}
			w.SetWhere(params, query)
		}
	}
}

func QueryCount(params QueryOptions) (int64, error) {
	var total int64
	query := DB.Table(params.TableName)
	queryWithoutLimitOffset(query, params)
	query.Count(&total)
	return total, query.Error
}

func WrapQueryCountWithDB(params QueryOptions, db *gorm.DB) func() (int64, error) {
	return func() (int64, error) {
		var total int64
		query := db.Table(params.TableName)
		queryWithoutLimitOffset(query, params)
		if params.Context != nil {
			query.Where(fmt.Sprintf("context->>'companyId'='%v'", params.Context.CompanyId))
		}
		query.Count(&total)
		return total, query.Error
	}
}

func ArrayInterfaceToString(args []interface{}) []string {
	result := make([]string, 0)
	for _, arg := range args {
		result = append(result, AssertString(arg))
	}
	return result
}

// WrapQueryHasDuplicateRowWithDB query table view has duplicate row
// result 1 represent is true other is false
func WrapQueryHasDuplicateRowWithDB(params QueryOptions, db *gorm.DB) func() (int64, int64, error) {
	return func() (int64, int64, error) {
		var total int64
		var duplicateTotal int64
		query := db.Table(params.Table.SQLName)
		fieldNames := make([]string, 0)
		for _, f := range params.Table.DataFields {
			fieldNames = append(fieldNames, fmt.Sprintf("ifnull(%s,'')", f.SQLName))
		}
		query.Select(fmt.Sprintf("count(0) c1,count(distinct %s) c2", strings.Join(fieldNames, ",")))
		row := query.Row()
		if row.Err() != nil {
			return total, duplicateTotal, row.Err()
		}
		if err := row.Scan(&total, &duplicateTotal); err != nil {
			return total, duplicateTotal, err
		}
		if total == duplicateTotal {
			return total, duplicateTotal, nil
		}
		return total, duplicateTotal, nil
	}
}

func WrapQueryHasDuplicateRowByIDWithDB(params QueryOptions, db *gorm.DB) func() (int64, int64, error) {
	return func() (int64, int64, error) {
		var total int64
		var duplicateTotal int64
		query := db.Table(params.Table.SQLName)
		fieldNames := make([]string, 0)
		fieldNames = append(fieldNames, "id")
		for _, f := range params.Table.DataFields {
			fieldNames = append(fieldNames, fmt.Sprintf("ifnull(%s,'')", f.SQLName))
		}
		query.Select(fmt.Sprintf("count(0) c1,count(distinct %s) c2", strings.Join(fieldNames, ",")))
		row := query.Row()
		if row.Err() != nil {
			return total, duplicateTotal, row.Err()
		}
		if err := row.Scan(&total, &duplicateTotal); err != nil {
			return total, duplicateTotal, err
		}
		if total == duplicateTotal {
			return total, duplicateTotal, nil
		}
		return total, duplicateTotal, nil
	}
}

func WrapQueryHasDuplicateRowBySql(sql string, db *gorm.DB) func() (int64, int64, error) {
	return func() (int64, int64, error) {
		var total int64
		var duplicateTotal int64
		query := db.Raw(sql)
		row := query.Row()
		if row.Err() != nil {
			return total, duplicateTotal, row.Err()
		}
		if err := row.Scan(&total, &duplicateTotal); err != nil {
			return total, duplicateTotal, err
		}
		if total == duplicateTotal {
			return total, duplicateTotal, nil
		}
		return total, duplicateTotal, nil
	}
}

func WrapQueryHasDuplicateRowBySqlParam1(sql string, db *gorm.DB, result1 interface{}) error {
	query := db.Raw(sql)
	row := query.Row()
	if row.Err() != nil {
		return row.Err()
	}
	if err := row.Scan(result1); err != nil {
		return err
	}
	return nil
}

func CalculateItemValue(db *gorm.DB, fieldExpr *domain.FieldExpr) string {
	var value string
	sql := CalculateItemViewSql(fieldExpr)
	WrapQueryHasDuplicateRowBySqlParam1(sql, db, &value)
	return value
}

func CalculateItemViewSql(fieldExpr *domain.FieldExpr) string {
	sql := "select " + fieldExpr.ExprSql
	tables := collection.NewSet()
	for _, f := range fieldExpr.TableFields {
		if len(f.TableSqlName) == 0 {
			continue
		}
		tables.AddStr(f.TableSqlName)
	}
	if len(tables.KeysStr()) > 0 {
		sql += fmt.Sprintf(" from %v", strings.Join(tables.KeysStr(), ","))
	}
	return sql
}

func CalculateTableViewSql(table string, aggregation *domain.Aggregation) string {

	columns := make([]string, 0)
	groups := make([]string, 0)
	orders := make([]string, 0)
	for _, f := range aggregation.RowFields {
		columns = append(columns, f.Expr.ExprSql)
		if f.Order != "" {
			orders = append(orders, fmt.Sprintf("%v %v", f.Field.SQLName, f.Order))
		}
		groups = append(groups, f.Field.SQLName)
	}
	for _, f := range aggregation.ValueFields {
		columns = append(columns, f.Expr.ExprSql)
		if f.Order != "" {
			orders = append(orders, fmt.Sprintf("%v %v", f.Field.SQLName, f.Order))
		}
	}
	sql := "select " + strings.Join(columns, ",")
	sql += fmt.Sprintf("\nfrom %v", table)
	if len(groups) > 0 {
		sql += "\ngroup by " + strings.Join(groups, ",")
	}
	if len(orders) > 0 {
		sql += "\norder by " + strings.Join(orders, ",")
	}
	return sql
}