package sqluser

import (
	"bytes"
	"context"
	"database/sql"
	"encoding/base64"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"sync"

	"github.com/tidbcloud/account/commons/database"
	apikey2 "github.com/tidbcloud/account/internel/svc/apikey"
	"github.com/tidbcloud/account/internel/svc/authcenter"
	"github.com/tidbcloud/account/internel/svc/dbuser"
	rbac2 "github.com/tidbcloud/account/internel/svc/rbac"

	"gorm.io/driver/mysql"

	"github.com/tidbcloud/account/central/utils"

	"github.com/tidbcloud/account/internel/dao"
	"github.com/tidbcloud/account/internel/dao/entity"
	"github.com/tidbcloud/pkgv2/clusterproxy"
	"gorm.io/gorm"

	"github.com/tidbcloud/account/thirdparty/infra"
	"github.com/tidbcloud/core-svc/idl/pbgen/proto/core"
	"github.com/tidbcloud/tiinf/errs"
	"github.com/tidbcloud/tiinf/monitor/tilog"
	"go.uber.org/zap"
)

const (
	sqlQueryDbUsers          = `SELECT USER, PLUGIN, ACCOUNT_LOCKED, PASSWORD_EXPIRED FROM MYSQL.USER WHERE 1=1`
	conditionUserName        = `AND USER IN @username`
	conditionPlugin          = `AND PLUGIN IN @plugin`
	conditionPasswordExpired = `AND PASSWORD_EXPIRED IN @passwordExpired`
	conditionAccountLocked   = `AND ACCOUNT_LOCKED IN @accountLocked`
	conditionIgnoreUser      = `AND USER NOT IN @ignoreUsers`
	sqlOrderBy               = `ORDER BY 1;`

	sqlQueryUserRoles = `SELECT FROM_USER, TO_USER FROM MYSQL.ROLE_EDGES WHERE TO_USER IN @usernames ORDER BY 1;`

	builtinRoleNameAdmin = "role_admin"

	emailToken = "@"
)

var (
	fakeDBSession *gorm.DB
)

func init() {
	fakeDB, _ := gorm.Open(mysql.Open(""), &gorm.Config{DisableAutomaticPing: true})
	fakeDBSession = fakeDB.Session(&gorm.Session{DryRun: true})
}

type DbUserWatchDogSvcIface interface {
	ListDbUsers(ctx context.Context, req *ListDbUsersReq) (*ListDbUsersRsp, error)
	TestDbUser(ctx context.Context, req *TestDbUserReq) error
	TestDbUserJwt(ctx context.Context, req *TestDbUserJwtReq) (string, error)
	GetClusterOperator(ctx context.Context, req *GetClusterOperatorReq) (*clusterproxy.ClusterOperator, *core.Cluster, error)
}

var (
	dbUserWatchDogSvcInitOnce sync.Once
	dbUserWatchDogSvcInstance DbUserWatchDogSvcIface
)

func GetDbUserWatchDogSvc() (DbUserWatchDogSvcIface, error) {
	dbUserWatchDogSvcInitOnce.Do(func() {
		dbUserWatchDogSvcInstance = NewDbUserWatchDogSvc(database.DBConn.DB, dao.GetDaoHub())
	})
	return dbUserWatchDogSvcInstance, nil
}

type DbUserWatchDogSvc struct {
	db  *gorm.DB
	hub *dao.DaoHub
}

func NewDbUserWatchDogSvc(db *gorm.DB, hub *dao.DaoHub) DbUserWatchDogSvcIface {
	return &DbUserWatchDogSvc{
		db:  db,
		hub: hub,
	}
}

type ListDbUsersReq struct {
	ClusterID       uint64
	Plugin          []string
	PasswordExpired []string
	AccountLocked   []string
	IgnoreUsers     []string
	Users           []string
	ListDbUserType  string
}

type ListDbUsersRsp struct {
	DbUsers          []DbUserDisplay
	InClusterDbUsers []DbUserDisplay
}

type DbUserDisplay struct {
	TargetType  string
	TargetValue string
	UserName    string
	BuiltinRole string
	CustomRoles []string
	AuthMethod  string
}

type GetClusterOperatorReq struct {
	ClusterID   uint64
	TargetValue string
	TargetType  string
}
type TestDbUserJwtReq struct {
	ClusterID uint64
	UserName  string
	Jwt       string
	QuerySQL  string
}
type TestDbUserReq struct {
	ClusterID      uint64
	TargetValue    string
	TargetType     string
	QuerySQL       string
	TestDbUserType string
}

func (s *DbUserWatchDogSvc) ListDbUsers(ctx context.Context, req *ListDbUsersReq) (*ListDbUsersRsp, error) {
	tilog.Info(ctx, "ListDbUsers request", zap.Any("req", req))
	// get cluster
	cluster, err := infra.GetHandler().GetClusterById(ctx, req.ClusterID, false)
	if err != nil {
		tilog.Error(ctx, "ListDbUsers getCluster error", zap.Any("err", err))
		return nil, err
	}
	if cluster == nil {
		return nil, errs.NewNotFound("cluster not exist", nil)
	}

	if req.ListDbUserType != "" {
		return s.listDbUsersByDbUserType(ctx, req, cluster)
	}

	if !infra.IsSupportDBUser(ctx, cluster) {
		return nil, errs.NewBadRequest("this cluster version not support db User", nil)
	}

	needCreateDbUsers, err := s.listClusterNeedCreateDbUsers(ctx, cluster)
	if err != nil {
		tilog.Error(ctx, "ListDbUsers listClusterNeedCreateDbUsers error", zap.Any("err", err))
		return nil, err
	}

	req, err = convertReqUsers2DbUsers(ctx, req, cluster)
	if err != nil {
		tilog.Error(ctx, "ListDbUsers replaceFilterUsers2DbUsers error", zap.Any("err", err))
		return nil, err
	}
	createdDbUsers, err := s.listClusterCreatedDbUsers(ctx, cluster, req)
	if err != nil {
		tilog.Error(ctx, "ListDbUsers getClusterCreatedDbUsers error", zap.Any("err", err))
		return nil, err
	}

	resultDbUsers := generateResultDbUsers(ctx, needCreateDbUsers, createdDbUsers, req)

	rsp := ListDbUsersRsp{
		DbUsers:          resultDbUsers,
		InClusterDbUsers: createdDbUsers,
	}

	tilog.Info(ctx, "ListDbUsers response", zap.Any("rsp", rsp))
	return &rsp, nil
}

func (s *DbUserWatchDogSvc) listDbUsersByDbUserType(ctx context.Context, req *ListDbUsersReq, cluster *core.Cluster) (*ListDbUsersRsp, error) {
	if req.ListDbUserType == "direct_mysql_user" {
		createdDbUsers, err := s.listClusterCreatedDbUsers(ctx, cluster, req)
		if err != nil {
			tilog.Error(ctx, "ListDbUsers listDbUsersByDbUserType listClusterCreatedDbUsers error", zap.Any("err", err))
			return nil, err
		}
		rsp := ListDbUsersRsp{
			InClusterDbUsers: createdDbUsers,
		}
		tilog.Info(ctx, "ListDbUsers list created db users", zap.Any("rsp", rsp))
		return &rsp, nil
	}

	if req.ListDbUserType == "need_create_dbuser" {
		needCreateDbUsers, err := s.listClusterNeedCreateDbUsers(ctx, cluster)
		if err != nil {
			tilog.Error(ctx, "ListDbUsers listDbUsersByDbUserType listClusterNeedCreateDbUsers error", zap.Any("err", err))
			return nil, err
		}
		rsp := ListDbUsersRsp{
			DbUsers: needCreateDbUsers,
		}
		tilog.Info(ctx, "ListDbUsers list need create db users", zap.Any("rsp", rsp))
		return &rsp, nil
	}

	return nil, errs.NewBadRequest("invalid_list_dbuser_type", nil)
}

func generateResultDbUsers(ctx context.Context, needCreateDbUsers, createdDbUsers []DbUserDisplay, req *ListDbUsersReq) []DbUserDisplay {
	createdDbUserMap := map[string]DbUserDisplay{}
	for _, dbUser := range createdDbUsers {
		createdDbUserMap[dbUser.UserName] = dbUser
	}

	reqIgnoreUserMap := map[string]struct{}{}
	reqSearchUserMap := map[string]struct{}{}
	if len(req.IgnoreUsers) > 0 {
		for _, iu := range req.IgnoreUsers {
			reqIgnoreUserMap[iu] = struct{}{}
		}
	}
	if len(req.Users) > 0 {
		for _, u := range req.Users {
			reqSearchUserMap[u] = struct{}{}
		}
	}

	var resultDbUsers []DbUserDisplay
	for i := 0; i < len(needCreateDbUsers); i++ {
		needCreateDbUser := needCreateDbUsers[i]
		if _, ok := reqIgnoreUserMap[needCreateDbUser.UserName]; ok {
			continue
		}
		_, ok := reqSearchUserMap[needCreateDbUser.UserName]
		if len(reqSearchUserMap) > 0 && !ok {
			continue
		}
		createdDbUser, ok := createdDbUserMap[needCreateDbUser.UserName]
		tilog.Info(ctx, "assemble need create db user", zap.Any("needCreateDbUser", needCreateDbUser), zap.Any("createdDbUser", createdDbUser))

		retU := DbUserDisplay{
			TargetType:  needCreateDbUser.TargetType,
			TargetValue: needCreateDbUser.TargetValue,
		}

		if ok {
			retU.UserName = createdDbUser.UserName
			retU.BuiltinRole = createdDbUser.BuiltinRole
			retU.CustomRoles = createdDbUser.CustomRoles
			retU.AuthMethod = createdDbUser.AuthMethod
		}
		resultDbUsers = append(resultDbUsers, retU)
	}

	return resultDbUsers
}

func convertReqUsers2DbUsers(ctx context.Context, req *ListDbUsersReq, cluster *core.Cluster) (*ListDbUsersReq, error) {
	if len(req.Users) > 0 {
		var dbUsers []string
		for _, user := range req.Users {
			dbUser, err := getDbUser(ctx, user, cluster)
			if err != nil {
				tilog.Error(ctx, "convert user to dbUser error", zap.Any("user", user), zap.Any("err", err))
				continue
			}
			dbUsers = append(dbUsers, dbUser)
		}
		req.Users = dbUsers
	}
	if len(req.IgnoreUsers) > 0 {
		var dbUsers []string
		for _, user := range req.IgnoreUsers {
			dbUser, err := getDbUser(ctx, user, cluster)
			if err != nil {
				tilog.Error(ctx, "convert user to dbUser error", zap.Any("user", user), zap.Any("err", err))
				continue
			}
			dbUsers = append(dbUsers, dbUser)
		}
		req.IgnoreUsers = dbUsers
	}
	return req, nil
}

func getDbUser(ctx context.Context, user string, cluster *core.Cluster) (string, error) {
	isDedicated := !cluster.IsDevTier
	var req utils.GetDBUserReq
	// it means email
	if strings.Contains(user, emailToken) {
		req = utils.GetDBUserReq{
			Email:           user,
			IsDedicated:     &isDedicated,
			ServerlessToken: cluster.ServerlessToken,
			ClusterId:       &cluster.Id,
		}
	} else {
		// it means access key
		req = utils.GetDBUserReq{
			Email:           user,
			IsDedicated:     &isDedicated,
			ServerlessToken: cluster.ServerlessToken,
			ClusterId:       &cluster.Id,
			TargetType:      entity.RoleBindingTargetAPIKey.String(),
		}
	}

	dbUser, err := utils.GetDBUser(ctx, req)
	if err != nil {
		return "", err
	}
	return *dbUser, nil

}

func (s *DbUserWatchDogSvc) listClusterCreatedDbUsers(ctx context.Context, cluster *core.Cluster, req *ListDbUsersReq) ([]DbUserDisplay, error) {
	// get operator
	//operator, err := infra.GetClusterCloudAdmin(ctx, cluster)
	clusterUserSvc, _ := authcenter.GetClusterUserSvc()
	operator, err := clusterUserSvc.GetClusterCloudAdmin(ctx, cluster.Id, 0)
	if err != nil {
		tilog.Error(ctx, "listClusterCreatedDbUsers getClusterCloudAdmin error", zap.Any("err", err))
		return nil, err
	}
	resultMap, err := selectDbUsers(ctx, cluster, operator, req)
	if err != nil {
		tilog.Error(ctx, "ListDbUsers selectDbUsers error", zap.Any("err", err))
		return nil, err
	}
	if len(resultMap) == 0 || resultMap["USER"] == nil || len(resultMap["USER"].Values) == 0 {
		return []DbUserDisplay{}, nil
	}

	users := resultMap["USER"].Values
	plugins := resultMap["PLUGIN"].Values
	tilog.Info(ctx, "selectDbUserRoles, get users plugins", zap.Any("users", users), zap.Any("plugins", plugins))

	userRoleMap, err := selectDbUserRoles(ctx, cluster, operator, users)
	if err != nil {
		return nil, err
	}
	tilog.Info(ctx, "selectDbUserRoles, get user role map", zap.Any("userRoleMap", userRoleMap))

	var dbUsers []DbUserDisplay

	for i, u := range users {
		out := DbUserDisplay{
			UserName:   u,
			AuthMethod: plugins[i],
		}
		roles := userRoleMap[u]
		if roles != nil {
			builtinRole, customRoles := parseRoles(roles)
			out.BuiltinRole = builtinRole
			out.CustomRoles = customRoles
		}
		//tilog.Info(ctx, "assemble created db user", zap.Any("user", out))

		dbUsers = append(dbUsers, out)
	}

	tilog.Info(ctx, "selectDbUserRoles, get cluster created db users", zap.Any("clusterID", cluster.Id), zap.Any("dbUsers", dbUsers))

	return dbUsers, nil
}

func parseRoles(roles []string) (builtinRole string, customRoles []string) {
	for _, each := range roles {
		if each == builtinRoleNameAdmin {
			builtinRole = each
			continue
		}
		customRoles = append(customRoles, each)
	}
	return
}

func selectDbUserRoles(ctx context.Context, cluster *core.Cluster, operator *clusterproxy.ClusterOperator, usernames []string) (map[string][]string, error) {
	userRoleMap := map[string][]string{}
	if len(usernames) == 0 {
		return userRoleMap, nil
	}

	querySql := buildSelectUserRolesSql(usernames)
	//tilog.Info(ctx, "selectDbUserRoles, query user roles sql", zap.Any("sql", querySql))

	result, err := infra.ExecQuerySql(ctx, cluster, operator, querySql)
	if err != nil {
		return nil, err
	}
	sqlResultMap := map[string][]string{}
	if err = json.Unmarshal([]byte(result), &sqlResultMap); err != nil {
		return nil, err
	}
	userNamesArr := sqlResultMap["TO_USER"]
	rolesArr := sqlResultMap["FROM_USER"]

	for i, user := range userNamesArr {
		role := rolesArr[i]
		roleByte, err := base64.StdEncoding.DecodeString(role)
		if err != nil {
			return nil, errs.NewInternalUnknown(fmt.Sprintf("base64 decode %s failed", role), err)
		}
		role = string(roleByte)
		userByte, err := base64.StdEncoding.DecodeString(user)
		if err != nil {
			return nil, errs.NewInternalUnknown(fmt.Sprintf("base64 decode %s failed", user), err)
		}
		user = string(userByte)

		userRoleMap[user] = append(userRoleMap[user], role)
	}
	return userRoleMap, nil
}

func selectDbUsers(ctx context.Context, cluster *core.Cluster, operator *clusterproxy.ClusterOperator, req *ListDbUsersReq) (map[string]*core.SqlValues, error) {
	querySql := buildSelectUsersSql(req)
	//tilog.Info(ctx, "selectDbUsers, query mysql.user sql", zap.Any("sql", querySql))

	result, err := infra.ExecQuerySql(ctx, cluster, operator, querySql)
	if err != nil {
		return nil, err
	}

	sqlResultMap := map[string][]string{}
	if err = json.Unmarshal([]byte(result), &sqlResultMap); err != nil {
		return nil, err
	}
	responseMap := make(map[string]*core.SqlValues)
	for k := range sqlResultMap {
		values, err := getKeyResult(sqlResultMap, k)
		if err != nil {
			return nil, err
		}
		listDBUsersValues := &core.SqlValues{
			Values: values,
		}
		responseMap[k] = listDBUsersValues
	}

	return responseMap, nil

}

func getKeyResult(grants map[string][]string, key string) ([]string, error) {
	var results []string
	for _, line := range grants[key] {
		value, err := base64.StdEncoding.DecodeString(line)
		if err != nil {
			return nil, err
		}
		results = append(results, string(value))

	}
	return results, nil
}

func buildSelectUserRolesSql(usernames []string) string {
	return buildSql(sqlQueryUserRoles, sql.Named("usernames", usernames))
}

func buildSelectUsersSql(param *ListDbUsersReq) string {
	SQL := sqlQueryDbUsers
	if len(param.Plugin) > 0 {
		SQL = fmt.Sprintf("%s %s", SQL, conditionPlugin)
		SQL = buildSql(SQL, sql.Named("plugin", param.Plugin))
	}
	if len(param.PasswordExpired) > 0 {
		SQL = fmt.Sprintf("%s %s", SQL, conditionPasswordExpired)
		SQL = buildSql(SQL, sql.Named("passwordExpired", param.PasswordExpired))
	}
	if len(param.AccountLocked) > 0 {
		SQL = fmt.Sprintf("%s %s", SQL, conditionAccountLocked)
		SQL = buildSql(SQL, sql.Named("accountLocked", param.AccountLocked))
	}
	if len(param.IgnoreUsers) > 0 {
		SQL = fmt.Sprintf("%s %s", SQL, conditionIgnoreUser)
		SQL = buildSql(SQL, sql.Named("ignoreUsers", param.IgnoreUsers))
	}

	if len(param.Users) > 0 {
		SQL = fmt.Sprintf("%s %s", SQL, conditionUserName)
		SQL = buildSql(SQL, sql.Named("username", param.Users))
	}

	SQL = fmt.Sprintf("%s %s", SQL, sqlOrderBy)

	return SQL

}

func buildSql(sqlTemplate string, args ...interface{}) string {
	sqlStr := fakeDBSession.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Raw(sqlTemplate, args...)
	})
	return sqlStr
}

func (s *DbUserWatchDogSvc) listClusterNeedCreateDbUsers(ctx context.Context, cluster *core.Cluster) ([]DbUserDisplay, error) {
	isDedicated := !cluster.IsDevTier

	// user
	rbacUserSvc, _ := rbac2.GetRbacUserSvc()
	orgUsers, err := rbacUserSvc.ListUsersByScope(ctx, entity.RoleBindingScopeOrg, cluster.TenantId)
	if err != nil {
		return nil, err
	}
	projectUsers, err := rbacUserSvc.ListUsersByScope(ctx, entity.RoleBindingScopeProject, cluster.ProjectId)
	if err != nil {
		return nil, err
	}
	var existUserIds = map[uint64]struct{}{}
	var userNeedCreateDbUsers []rbac2.RbacUser
	for _, orgUser := range orgUsers {
		if orgUser.RoleBindingStatus != entity.RoleBindingStatusActive {
			continue
		}
		if orgUser.RbacRole != entity.RoleOrgOwner {
			continue
		}
		userNeedCreateDbUsers = append(userNeedCreateDbUsers, orgUser)
		existUserIds[orgUser.User.ID.Uint64()] = struct{}{}
	}
	for _, projectUser := range projectUsers {
		if projectUser.RoleBindingStatus != entity.RoleBindingStatusActive {
			continue
		}
		if _, ok := existUserIds[projectUser.User.ID.Uint64()]; !ok {
			userNeedCreateDbUsers = append(userNeedCreateDbUsers, projectUser)
		}
	}

	// api key
	apiKeySvc, err := apikey2.GetApiKeySvc()
	if err != nil {
		return nil, err
	}
	apiKeys, err := apiKeySvc.ListApiKeysByResourceID(ctx, &apikey2.ListKeysByResourceIDParam{
		ScopeType:  entity.RoleBindingScopeOrg.String(),
		ResourceId: cluster.TenantId,
	})
	if err != nil {
		return nil, err
	}
	accessKeysMap := map[string]struct{}{}
	for _, key := range apiKeys.Items {
		accessKeysMap[key.AccessKey] = struct{}{}
	}
	var keyNeedCreateDbUsers []string
	for ak := range accessKeysMap {
		keyNeedCreateDbUsers = append(keyNeedCreateDbUsers, ak)
	}

	var needCreateDbUsers []DbUserDisplay

	for _, rbacUser := range userNeedCreateDbUsers {
		user := rbacUser.User
		dbRole, ok := entity.RoleBindingRole2DbRoleMap[rbacUser.RbacRole]
		if !ok {
			continue
		}

		var builtinRole = ""
		var customRoles []string
		if dbRole == entity.DbRoleAdmin {
			builtinRole = entity.DbRoleAdmin
		} else {
			createDbRole := dbRole
			if cluster.IsDevTier {
				createDbRole = *cluster.ServerlessToken + "." + dbRole
			}
			customRoles = append(customRoles, createDbRole)

		}
		getDBUserReq := utils.GetDBUserReq{
			Email:           user.Email,
			IsDedicated:     &isDedicated,
			ServerlessToken: cluster.ServerlessToken,
		}
		username, err := utils.GetDBUser(ctx, getDBUserReq)
		if err != nil {
			tilog.Info(ctx, "user GetDBUser error", zap.Any("email", user.Email), zap.Any("err", err))
			continue
		}

		u := DbUserDisplay{
			TargetType:  entity.RoleBindingTargetUser.String(),
			TargetValue: user.Email,
			UserName:    *username,
			BuiltinRole: builtinRole,
			CustomRoles: customRoles,
		}

		needCreateDbUsers = append(needCreateDbUsers, u)
	}

	for _, key := range keyNeedCreateDbUsers {
		dbUserReq := utils.GetDBUserReq{
			Email:           key,
			IsDedicated:     &isDedicated,
			ServerlessToken: cluster.ServerlessToken,
			TargetType:      entity.RoleBindingTargetAPIKey.String(),
		}
		dbUser, err := utils.GetDBUser(ctx, dbUserReq)
		if err != nil {
			tilog.Info(ctx, "key GetDBUser error", zap.Any("apikey", key), zap.Any("err", err))
			continue
		}

		u := DbUserDisplay{
			TargetType:  entity.RoleBindingTargetAPIKey.String(),
			TargetValue: key,
			UserName:    *dbUser,
			BuiltinRole: entity.DbRoleAdmin,
			CustomRoles: []string{},
		}

		needCreateDbUsers = append(needCreateDbUsers, u)
	}

	return needCreateDbUsers, nil

}

func (s *DbUserWatchDogSvc) TestDbUser(ctx context.Context, req *TestDbUserReq) error {
	tilog.Info(ctx, "TestDbUser request", zap.Any("req", req))

	if req.TestDbUserType == "test_db_user" {
		param := &GetClusterOperatorReq{ClusterID: req.ClusterID, TargetType: req.TargetType, TargetValue: req.TargetValue}
		operator, cluster, err := s.GetClusterOperator(ctx, param)
		if err != nil {
			tilog.Error(ctx, "TestDbUser getClusterOperator error", zap.Any("err", err))
			return err
		}

		result, err := infra.ExecQuerySql(ctx, cluster, operator, req.QuerySQL)
		if err != nil {
			tilog.Error(ctx, "TestDbUser executeQuerySql error", zap.Any("err", err))
			return err
		}
		tilog.Info(ctx, "TestDbUser success", zap.Any("req", req), zap.Any("rsp", result))
	}

	if req.TestDbUserType == "create_db_user_use_target" {
		tilog.Info(ctx, "TestCreateDbUserWithTarget start", zap.Any("req", req))
		param := &GetClusterOperatorReq{ClusterID: req.ClusterID, TargetType: req.TargetType, TargetValue: req.TargetValue}
		operator, cluster, err := s.GetClusterOperator(ctx, param)
		if err != nil {
			tilog.Error(ctx, "TestDbUser getClusterOperator error", zap.Any("err", err))
			return err
		}
		needCreateDbUsers, err := s.listClusterNeedCreateDbUsers(ctx, cluster)
		if err != nil {
			tilog.Error(ctx, "TestCreateDbUserWithTarget listClusterNeedCreateDbUsers error", zap.Any("req", req), zap.Any("err", err))
			return err
		}
		tilog.Info(ctx, "TestCreateDbUserWithTarget needCreateDbUsers count", zap.Any("count", len(needCreateDbUsers)))

		for _, toCreateUser := range needCreateDbUsers {
			dbRole := ""
			if toCreateUser.BuiltinRole != "" {
				dbRole = toCreateUser.BuiltinRole
			} else if len(toCreateUser.CustomRoles) > 0 {
				dbRole = toCreateUser.CustomRoles[0]
			}
			tilog.Info(ctx, fmt.Sprintf("TestCreateDbUserWithTarget to create user %s", toCreateUser.TargetValue), zap.Any("dbUser", toCreateUser.UserName), zap.Any("dbRole", dbRole))

			status := infra.GetHandler().CreateDBUser(ctx, cluster.Id, operator.Username, clusterproxy.AuthMethodToken, operator.Credential,
				toCreateUser.TargetValue, toCreateUser.UserName, true, &entity.CommandDBUserCluster{}, dbRole)
			if status == entity.CommandStatusComplete {
				tilog.Info(ctx, fmt.Sprintf("TestCreateDbUserWithTarget create user success %s", toCreateUser.TargetValue), zap.Any("dbUser", toCreateUser.UserName))
			} else {
				tilog.Info(ctx, "TestCreateDbUserWithTarget create user fail", zap.Any("toCreateUser", toCreateUser))
			}
		}
		return nil
	}

	if req.TestDbUserType == "create_db_user_use_cloud_admin" {
		cluster, err := infra.GetHandler().GetClusterById(ctx, req.ClusterID, false)
		if err != nil {
			tilog.Error(ctx, "TestDbUser getCluster error", zap.Any("err", err))
			return err
		}
		if cluster == nil {
			return errs.NewNotFound("cluster not exist", nil)
		}

		svc, _ := authcenter.GetClusterUserSvc()
		cloudAdmin, err := svc.GetClusterCloudAdmin(ctx, req.ClusterID, 0)
		if err != nil {
			tilog.Error(ctx, "TestCreateDbUserWithCloudAdmin GetClusterCloudAdmin error", zap.Any("clusterId", req.ClusterID), zap.Any("err", err))
			return err
		}
		proxy, err := infra.GetClusterProxy(ctx, cluster)
		if err != nil {
			tilog.Error(ctx, "TestCreateDbUserWithCloudAdmin GetClusterProxy error", zap.Any("clusterId", req.ClusterID), zap.Any("err", err))
			return err
		}

		privNFields, err := getCloudAdminPrivNFields(ctx, cloudAdmin, proxy, cluster)
		if err != nil {
			tilog.Error(ctx, "TestCreateDbUserWithCloudAdmin getCloudAdminPrivNFields error", zap.Any("clusterId", req.ClusterID), zap.Any("err", err))
			return err
		}
		tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin toChangePrivFields", zap.Any("clusterId", req.ClusterID), zap.Any("privNFields", privNFields))
		if len(privNFields) > 0 {
			var buffer bytes.Buffer
			for i, f := range privNFields {
				if i != 0 {
					buffer.WriteString(", ")
				}
				buffer.WriteString(fmt.Sprintf("%s = '%s'", f, "Y"))
			}
			changePrivFields := buffer.String()

			changeCloudAdminGrantSql := fmt.Sprintf("update mysql.user set %s where User = '%s'", changePrivFields, cloudAdmin.Username)
			rsp, err := proxy.ExecuteMultiSQLWithSqlError(ctx, cloudAdmin, []string{changeCloudAdminGrantSql})
			tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin update cloud admin priv result", zap.Any("sql", changeCloudAdminGrantSql), zap.Any("rsp", rsp), zap.Any("err", err))
			if err != nil || rsp.ErrNumber != 0 {
				tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin update cloud admin priv error", zap.Any("cloud_admin", cloudAdmin.Username), zap.Any("rsp", rsp), zap.Any("err", err))
				return errors.New("change cloud_admin priv error")
			}

		}

		rsp, err := proxy.ExecuteSQLWithSqlError(ctx, cloudAdmin, "FLUSH PRIVILEGES;")
		tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin flush priv result", zap.Any("sql", "FLUSH PRIVILEGES;"), zap.Any("rsp", rsp), zap.Any("err", err))
		if err != nil || rsp.ErrNumber != 0 {
			tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin flush priv error", zap.Any("cloud_admin", cloudAdmin.Username), zap.Any("rsp", rsp), zap.Any("err", err))
			return errors.New("flush priv error")
		}

		createRoleAdminSqls := []string{
			"CREATE ROLE IF NOT EXISTS 'role_admin';",
			"GRANT ALL ON *.* TO 'role_admin' WITH GRANT OPTION;",
			"REVOKE SYSTEM_USER ON *.* FROM 'role_admin';",
			"REVOKE SHUTDOWN, CONFIG ON *.* FROM 'role_admin';",
			"GRANT ROLE_ADMIN ON *.* to 'role_admin';",
		}
		rsp, err = proxy.ExecuteMultiSQLWithSqlError(ctx, cloudAdmin, createRoleAdminSqls)
		tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin create role admin result", zap.Any("sql", createRoleAdminSqls), zap.Any("rsp", rsp), zap.Any("err", err))
		if err != nil || rsp.ErrNumber != 0 {
			tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin create role admin error", zap.Any("cloud_admin", cloudAdmin.Username), zap.Any("rsp", rsp), zap.Any("err", err))
			return errors.New("create role_admin error")
		}

		needCreateDbUsers, err := s.listClusterNeedCreateDbUsers(ctx, cluster)
		if err != nil {
			tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin listClusterNeedCreateDbUsers error", zap.Any("req", req), zap.Any("err", err))
			return err
		}
		tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin needCreateDbUsers count", zap.Any("count", len(needCreateDbUsers)))

		for _, toCreateUser := range needCreateDbUsers {
			dbRole := ""
			if toCreateUser.BuiltinRole != "" {
				dbRole = toCreateUser.BuiltinRole
			} else if len(toCreateUser.CustomRoles) > 0 {
				dbRole = toCreateUser.CustomRoles[0]
			}
			tilog.Info(ctx, fmt.Sprintf("TestCreateDbUserWithCloudAdmin to create user %s", toCreateUser.TargetValue), zap.Any("dbUser", toCreateUser.UserName), zap.Any("dbRole", dbRole))

			status := infra.GetHandler().CreateDBUser(ctx, cluster.Id, cloudAdmin.Username, cloudAdmin.AuthMethod, cloudAdmin.Credential,
				toCreateUser.TargetValue, toCreateUser.UserName, true, &entity.CommandDBUserCluster{}, dbRole)
			if status == entity.CommandStatusComplete {
				tilog.Info(ctx, fmt.Sprintf("TestCreateDbUserWithCloudAdmin create user success %s", toCreateUser.TargetValue), zap.Any("dbUser", toCreateUser.UserName))
			} else {
				tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin create user fail", zap.Any("toCreateUser", toCreateUser))
			}
		}

		if len(privNFields) > 0 {
			var buffer bytes.Buffer
			for i, f := range privNFields {
				if i != 0 {
					buffer.WriteString(", ")
				}
				buffer.WriteString(fmt.Sprintf("%s = '%s'", f, "N"))
			}
			changePrivFields := buffer.String()

			changeCloudAdminGrantSql := fmt.Sprintf("update mysql.user set %s where User = '%s'", changePrivFields, cloudAdmin.Username)
			rsp, err := proxy.ExecuteMultiSQLWithSqlError(ctx, cloudAdmin, []string{changeCloudAdminGrantSql})
			tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin update cloud amdin priv result", zap.Any("sql", changeCloudAdminGrantSql), zap.Any("rsp", rsp), zap.Any("err", err))
			if err != nil || rsp.ErrNumber != 0 {
				tilog.Warn(ctx, "TestCreateDbUserWithCloudAdmin update cloud_admin priv error", zap.Any("cloud_admin", cloudAdmin.Username), zap.Any("rsp", rsp), zap.Any("err", err))
				return errors.New("recover cloud_admin priv error")
			}
		}

		return nil

	}

	return errors.New("invalid test_db_user_type")
}

func getCloudAdminPrivNFields(ctx context.Context, cloudAdmin *clusterproxy.ClusterOperator, proxy clusterproxy.ClusterProxy, cluster *core.Cluster) ([]string, error) {
	cloudAdminPrivFields := []string{
		"Select_priv",
		"Insert_priv",
		"Update_priv",
		"Delete_priv",
		"Create_priv",
		"Drop_priv",
		"Process_priv",
		"Grant_priv",
		"References_priv",
		"Alter_priv",
		"Show_db_priv",
		"Super_priv",
		"Create_tmp_table_priv",
		"Lock_tables_priv",
		"Execute_priv",
		"Create_view_priv",
		"Show_view_priv",
		"Create_routine_priv",
		"Alter_routine_priv",
		"Index_priv",
		"Create_user_priv",
		"Event_priv",
		"Repl_slave_priv",
		"Repl_client_priv",
		"Trigger_priv",
		"Create_role_priv",
		"Drop_role_priv",
		"Shutdown_priv",
		"Reload_priv",
		"FILE_priv",
		"Config_priv",
		"Create_Tablespace_Priv",
	}
	privFields := strings.Join(cloudAdminPrivFields, ", ")

	selectCloudAdminSql := fmt.Sprintf("select %s from mysql.user where User = '%s'", privFields, cloudAdmin.Username)
	rsp, err := proxy.QuerySQLWithSqlError(ctx, cloudAdmin, selectCloudAdminSql)
	tilog.Info(ctx, "TestCreateDbUserWithCloudAdmin select cloud admin priv result", zap.Any("sql", selectCloudAdminSql), zap.Any("rsp", rsp), zap.Any("err", err))
	if err != nil || rsp.ErrNumber != 0 {
		return nil, err
	}
	sqlResultMap := map[string][]string{}
	if err := json.Unmarshal(rsp.Result, &sqlResultMap); err != nil {
		return nil, errs.NewInternalUnknown("unmarshal sql result error", err)
	}

	resultMap := make(map[string]*core.SqlValues)
	for k := range sqlResultMap {
		values, err := getKeyResult(sqlResultMap, k)
		if err != nil {
			return nil, err
		}
		listDBUsersValues := &core.SqlValues{
			Values: values,
		}
		resultMap[k] = listDBUsersValues
	}

	var privNFields []string
	for privF := range resultMap {
		v := resultMap[privF]
		if v == nil {
			return nil, fmt.Errorf("not found %s field in result", privF)
		}
		fv := v.Values[0]
		tilog.Info(ctx, fmt.Sprintf("TestCreateDbUserWithCloudAdmin priv field value: %s %s", privF, fv))
		if fv == "N" {
			privNFields = append(privNFields, privF)
		}

	}
	return privNFields, nil
}
func (s *DbUserWatchDogSvc) TestDbUserJwt(ctx context.Context, req *TestDbUserJwtReq) (string, error) {
	tilog.Info(ctx, "TestJwt request", zap.Any("req", req))

	cluster, err := infra.GetHandler().GetClusterById(ctx, req.ClusterID, false)
	if err != nil {
		tilog.Error(ctx, "TestJwt getCluster error", zap.Any("err", err))
		return "", err
	}
	if cluster == nil {
		return "", errs.NewNotFound("cluster not exist", nil)
	}
	if !infra.IsSupportDBUser(ctx, cluster) {
		return "", errs.NewBadRequest("this cluster version not support db User", nil)
	}
	operator := &clusterproxy.ClusterOperator{
		Username:   req.UserName,
		AuthMethod: clusterproxy.AuthMethodToken,
		Credential: req.Jwt,
	}

	return infra.ExecQuerySql(ctx, cluster, operator, req.QuerySQL)

}

func (s *DbUserWatchDogSvc) GetClusterOperator(ctx context.Context, req *GetClusterOperatorReq) (*clusterproxy.ClusterOperator, *core.Cluster, error) {
	cluster, err := infra.GetHandler().GetClusterById(ctx, req.ClusterID, false)
	if err != nil {
		tilog.Error(ctx, "TestDbUser getCluster error", zap.Any("err", err))
		return nil, nil, err
	}
	if cluster == nil {
		return nil, nil, errs.NewNotFound("cluster not exist", nil)
	}
	if !infra.IsSupportDBUser(ctx, cluster) {
		return nil, nil, errs.NewBadRequest("this cluster version not support db User", nil)
	}

	targetType := strings.ToUpper(req.TargetType)
	dbUserSvc, _ := dbuser.GetDBUserSvc()

	if targetType == string(entity.RoleBindingTargetUser) {
		user, err := s.hub.User.GetUserByEmail(ctx, s.db, req.TargetValue)
		if err != nil {
			if errors.Is(err, gorm.ErrRecordNotFound) {
				return nil, nil, errs.NewBadRequest("user not found", nil)
			}
			return nil, nil, err
		}
		dbUser, jwt, err := dbUserSvc.GetOperatorDBuser(ctx, cluster.TenantId, cluster, user.ID.Uint64())
		if err != nil {
			return nil, nil, err
		}
		operator := clusterproxy.ClusterOperator{
			Username:   *dbUser,
			AuthMethod: clusterproxy.AuthMethodToken,
			Credential: *jwt,
		}
		return &operator, cluster, nil
	}

	if targetType == string(entity.RoleBindingTargetAPIKey) {
		key, err := s.hub.ApiKey.GetApiKeyByAk(ctx, s.db, req.TargetValue)
		if err != nil {
			return nil, nil, err
		}
		if key == nil {
			return nil, nil, errs.NewBadRequest("api key not found", nil)
		}
		dbUser, jwt, err := dbUserSvc.GetApiKeyOperatorDBuser(ctx, cluster.TenantId, cluster, key.ID.Uint64())
		if err != nil {
			return nil, nil, err
		}
		operator := clusterproxy.ClusterOperator{
			Username:   *dbUser,
			AuthMethod: clusterproxy.AuthMethodToken,
			Credential: *jwt,
		}
		return &operator, cluster, nil

	}

	return nil, nil, errs.NewBadRequest("target_type invalid", nil)
}