package infra

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/tidbcloud/core-svc/idl/pbgen/proto/core"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

var (
	fakeDBSession *gorm.DB
)

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

// NOTE: all users/roles are omitted host info(e.g. '@%', '@localhost')
const (
	// SqlCreateTokenBasedUser
	// CREATE USER 'user@pingcap.com' IDENTIFIED WITH tidb_auth_token REQUIRE TOKEN_ISSUER 'pingcap.com' ATTRIBUTE '{"email": "user@pingcap.com"}';
	SqlCreateTokenBasedUser = `CREATE USER IF NOT EXISTS @username IDENTIFIED WITH tidb_auth_token REQUIRE TOKEN_ISSUER @token_issuer ATTRIBUTE @attribute;`
	// SqlCreatePasswordBasedUser sql for create an user defined user
	// CREATE USER 'user' IDENTIFIED BY 'password';
	SqlCreatePasswordBasedUser = `CREATE USER IF NOT EXISTS @username IDENTIFIED BY @password;`
	// SqlDropUser sql for drop an user
	// DROP USER 'user';
	SqlDropUser = `DROP USER IF EXISTS @username;`

	// SqlCreateRole
	// CREATE ROLE 'role_readonly';
	SqlCreateRole = `CREATE ROLE IF NOT EXISTS @role_name;`

	// SqlGrantUserRoles sql for grant some roles to user
	//GRANT 'app_read', 'app_write' TO 'rw_user1';
	SqlGrantUserRoles = `GRANT @roles TO @username;`
	SqlGrantUserRole  = `GRANT @role TO @username;`
	// SqlRevokeUserRoles sql for revoke some roles from user
	// REVOKE 'app_read', 'app_write' FROM 'rw_user1';
	SqlRevokeUserRoles = `REVOKE @roles FROM @username;`
	SqlRevokeUserRole  = `REVOKE @role FROM @username;`
	// SqlEnableAllRole sql for enable all role for user
	// SET DEFAULT ROLE ALL TO 'dev1';
	SqlEnableAllRole = `SET DEFAULT ROLE ALL TO @username;`
	// SqlShowRoles
	// show grants for 'rw_user2';
	SqlShowRoles = `SHOW GRANTS FOR @username;`

	// query  users
	//SqlQueryUsers = `SELECT USER, PLUGIN,ACCOUNT_LOCKED FROM MYSQL.USER  WHERE PLUGIN='mysql_native_password' AND PASSWORD_EXPIRED='N' AND ACCOUNT_LOCKED='N' AND USER NOT IN @usernames;`
	SqlQueryUsers            = `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;`

	SqlChangePassword = `ALTER USER @username @'%' IDENTIFIED BY @password;`
	SqlQueryUserRoles = `SELECT FROM_USER, TO_USER FROM MYSQL.ROLE_EDGES WHERE TO_USER IN @usernames ORDER BY 1;`
)

func buildCreateTokenBasedUserSql(username, attribute, tokenIssuer string) string {
	return buildSql(SqlCreateTokenBasedUser, sql.Named("username", username), sql.Named("attribute", attribute), sql.Named("token_issuer", tokenIssuer))
}

func buildCreatePasswordBasedUserSql(username, password string) string {
	return buildSql(SqlCreatePasswordBasedUser, sql.Named("username", username), sql.Named("password", password))
}

func buildChangePasswordUserSql(username, password string) string {
	return buildSql(SqlChangePassword, sql.Named("username", username), sql.Named("password", password))
}

func buildDropUserSql(username string) string {
	return buildSql(SqlDropUser, sql.Named("username", username))
}

func buildCreateRoleSql(roleName string) string {
	return buildSql(SqlCreateRole, sql.Named("role_name", roleName))
}

func buildGrantUserRolesSql(username string, roles []string) []string {
	if len(roles) == 0 {
		return nil
	}
	// NOTE: since 'GRANT @roles TO @user' with roles=[]string{"role1","role2"}, user="user1"
	// will generate statement "GRANT ('role1','role2') TO 'user1'", but it is invalid.
	//return buildSql(SqlGrantUserRoles, sql.Named("username", username), sql.Named("roles", roles))

	out := make([]string, 0, len(roles))
	for _, role := range roles {
		out = append(out, buildSql(SqlGrantUserRole, sql.Named("username", username), sql.Named("role", role)))
	}
	return out
}

func buildRevokeUserRolesSql(username string, roles []string) []string {
	if len(roles) == 0 {
		return nil
	}
	//return buildSql(SqlRevokeUserRoles, sql.Named("username", username), sql.Named("roles", roles))

	out := make([]string, 0, len(roles))
	for _, role := range roles {
		out = append(out, buildSql(SqlRevokeUserRole, sql.Named("username", username), sql.Named("role", role)))
	}
	return out
}

func buildEnableAllRoleSql(username string) string {
	return buildSql(SqlEnableAllRole, sql.Named("username", username))
}

func buildShowRolesSql(username string) string {
	return buildSql(SqlShowRoles, sql.Named("username", username))
}

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

func buildQueryUsersSql(ctx context.Context, param *core.ListDBUsersFilter) string {

	SQL := SqlQueryUsers
	if param != nil {
		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
}