package identity

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/authidentity"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/core/registration"
	"github.com/tidbcloud/AgentTeam/internal/core/secrets"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/pinyinsearch"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

// CreateRefreshToken inserts one refresh token record.
func (store *Store) CreateRefreshToken(ctx context.Context, userID string, tokenHash string, expiresAt time.Time) error {
	refreshTokenID, err := identifiers.NewEntityID("rft")
	if err != nil {
		return fmt.Errorf("generate refresh token id: %w", err)
	}
	now := time.Now().UTC()
	if _, err := store.db.Exec(ctx, `
INSERT INTO user_refresh_tokens (id, user_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, $4, $5)
`, refreshTokenID, userID, tokenHash, expiresAt, now); err != nil {
		return fmt.Errorf("insert refresh token: %w", err)
	}
	return nil
}

// CreateSessionAndRefreshToken creates a session and refresh token within the same transaction.
func (store *Store) CreateSessionAndRefreshToken(ctx context.Context, userID string, sessionTokenHash string, sessionExpiresAt time.Time, refreshTokenHash string, refreshExpiresAt time.Time) (model.UserSession, error) {
	sessionID, err := identifiers.NewEntityID("sess")
	if err != nil {
		return model.UserSession{}, fmt.Errorf("generate session id: %w", err)
	}
	refreshTokenID, err := identifiers.NewEntityID("rft")
	if err != nil {
		return model.UserSession{}, fmt.Errorf("generate refresh token id: %w", err)
	}

	return withTxValue(ctx, store.db, func(tx coredb.Tx) (model.UserSession, error) {
		now := time.Now().UTC()
		if _, err := tx.Exec(ctx, `
INSERT INTO user_sessions (id, user_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, $4, $5)
`, sessionID, userID, sessionTokenHash, sessionExpiresAt, now); err != nil {
			return model.UserSession{}, fmt.Errorf("insert session: %w", err)
		}

		if _, err := tx.Exec(ctx, `
INSERT INTO user_refresh_tokens (id, user_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, $4, $5)
`, refreshTokenID, userID, refreshTokenHash, refreshExpiresAt, now); err != nil {
			return model.UserSession{}, fmt.Errorf("insert refresh token: %w", err)
		}

		return model.UserSession{
			ID:        sessionID,
			UserID:    userID,
			ExpiresAt: sessionExpiresAt,
			CreatedAt: now,
		}, nil
	})
}

// RotateRefreshTokenAndCreateSession consumes the provided refresh token, mints a replacement, and creates a new session.
//
// The returned user ID is the owner of the rotated token.
func (store *Store) RotateRefreshTokenAndCreateSession(ctx context.Context, refreshToken string, nextTokenHash string, nextExpiresAt time.Time, sessionTokenHash string, sessionExpiresAt time.Time) (string, model.UserSession, error) {
	tokenHash := secrets.HashToken(refreshToken)

	return withTx2(ctx, store.db, func(tx coredb.Tx) (string, model.UserSession, error) {
		row := tx.QueryRow(ctx, `
SELECT r.id, r.user_id, r.session_family_id, r.expires_at, r.revoked_at
FROM user_refresh_tokens r
LEFT JOIN user_session_families f ON f.id = r.session_family_id
WHERE r.token_hash = $1
  AND (r.session_family_id IS NULL OR (f.id IS NOT NULL AND f.revoked_at IS NULL AND f.expires_at > NOW()))
FOR UPDATE
`, tokenHash)
		var refreshTokenID string
		var userID string
		var sessionFamilyID *string
		var expiresAt time.Time
		var revokedAt *time.Time
		if err := row.Scan(&refreshTokenID, &userID, &sessionFamilyID, &expiresAt, &revokedAt); err != nil {
			if coredb.IsNotFound(err) {
				return "", model.UserSession{}, ErrUnauthorized
			}
			return "", model.UserSession{}, fmt.Errorf("load refresh token: %w", err)
		}

		now := time.Now().UTC()
		if revokedAt != nil || now.After(expiresAt) {
			return "", model.UserSession{}, ErrUnauthorized
		}

		if _, err := tx.Exec(ctx, `
UPDATE user_refresh_tokens
SET revoked_at = $2
WHERE id = $1
`, refreshTokenID, now); err != nil {
			return "", model.UserSession{}, fmt.Errorf("revoke refresh token: %w", err)
		}
		if sessionFamilyID != nil {
			if _, err := tx.Exec(ctx, `
UPDATE user_session_families
SET expires_at = $2
WHERE id = $1 AND revoked_at IS NULL
`, *sessionFamilyID, nextExpiresAt); err != nil {
				return "", model.UserSession{}, fmt.Errorf("extend session family: %w", err)
			}
		}

		nextTokenID, err := identifiers.NewEntityID("rft")
		if err != nil {
			return "", model.UserSession{}, fmt.Errorf("generate next refresh token id: %w", err)
		}
		if _, err := tx.Exec(ctx, `
INSERT INTO user_refresh_tokens (id, user_id, session_family_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, $4, $5, $6)
`, nextTokenID, userID, sessionFamilyID, nextTokenHash, nextExpiresAt, now); err != nil {
			return "", model.UserSession{}, fmt.Errorf("insert next refresh token: %w", err)
		}

		sessionID, err := identifiers.NewEntityID("sess")
		if err != nil {
			return "", model.UserSession{}, fmt.Errorf("generate session id: %w", err)
		}
		if _, err := tx.Exec(ctx, `
INSERT INTO user_sessions (id, user_id, session_family_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, $4, $5, $6)
`, sessionID, userID, sessionFamilyID, sessionTokenHash, sessionExpiresAt, now); err != nil {
			return "", model.UserSession{}, fmt.Errorf("insert session: %w", err)
		}

		return userID, model.UserSession{
			ID:              sessionID,
			UserID:          userID,
			SessionFamilyID: sessionFamilyID,
			ExpiresAt:       sessionExpiresAt,
			CreatedAt:       now,
		}, nil
	})
}

// RevokeRefreshToken revokes one refresh token if it exists.
func (store *Store) RevokeRefreshToken(ctx context.Context, refreshToken string) error {
	tokenHash := secrets.HashToken(refreshToken)
	_, err := store.db.Exec(ctx, `
UPDATE user_refresh_tokens
SET revoked_at = NOW()
WHERE token_hash = $1 AND revoked_at IS NULL
`, tokenHash)
	if err != nil {
		return fmt.Errorf("revoke refresh token: %w", err)
	}
	return nil
}

// CreatePasswordResetToken creates one password reset token record.
func (store *Store) CreatePasswordResetToken(ctx context.Context, userID string, tokenHash string, expiresAt time.Time) error {
	_, err := store.db.Exec(ctx, `
INSERT INTO user_password_reset_tokens (user_id, token_hash, expires_at, created_at)
VALUES ($1, $2, $3, NOW())
`, userID, tokenHash, expiresAt)
	if err != nil {
		return fmt.Errorf("insert password reset token: %w", err)
	}
	return nil
}

// ResetPasswordWithToken consumes a reset token and replaces the stored password hash.
func (store *Store) ResetPasswordWithToken(ctx context.Context, token string, nextPasswordHash string) error {
	tokenHash := secrets.HashToken(token)

	return coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		row := tx.QueryRow(ctx, `
SELECT user_id, expires_at, consumed_at
FROM user_password_reset_tokens
WHERE token_hash = $1
FOR UPDATE
`, tokenHash)
		var userID string
		var expiresAt time.Time
		var consumedAt *time.Time
		if err := row.Scan(&userID, &expiresAt, &consumedAt); err != nil {
			if coredb.IsNotFound(err) {
				return badRequest("invalid password reset token")
			}
			return fmt.Errorf("load password reset token: %w", err)
		}

		now := time.Now().UTC()
		if consumedAt != nil {
			return badRequest("password reset token already consumed")
		}
		if now.After(expiresAt) {
			return badRequest("password reset token expired")
		}

		if _, err := tx.Exec(ctx, `
UPDATE users
SET password_hash = $2, updated_at = $3
WHERE id = $1
`, userID, nextPasswordHash, now); err != nil {
			return fmt.Errorf("update user password hash: %w", err)
		}

		if _, err := tx.Exec(ctx, `
UPDATE user_password_reset_tokens
SET consumed_at = $2
WHERE user_id = $1 AND token_hash = $3
`, userID, now, tokenHash); err != nil {
			return fmt.Errorf("consume password reset token: %w", err)
		}

		if _, err := tx.Exec(ctx, `
UPDATE user_sessions
SET revoked_at = $2
WHERE user_id = $1 AND revoked_at IS NULL
`, userID, now); err != nil {
			return fmt.Errorf("revoke user sessions: %w", err)
		}

		if _, err := tx.Exec(ctx, `
UPDATE user_refresh_tokens
SET revoked_at = $2
WHERE user_id = $1 AND revoked_at IS NULL
`, userID, now); err != nil {
			return fmt.Errorf("revoke user refresh tokens: %w", err)
		}

		return nil
	})
}

// UpdateUserAvatarURL updates one user profile avatar URL and returns the updated user.
func (store *Store) UpdateUserAvatarURL(ctx context.Context, userID string, avatarURL string) (model.User, error) {
	trimmedURL := strings.TrimSpace(avatarURL)
	return withTxValue(ctx, store.db, func(tx coredb.Tx) (model.User, error) {
		_, err := tx.Exec(ctx, `
UPDATE users
SET avatar_url = $2, updated_at = NOW()
WHERE id = $1
`, userID, trimmedURL)
		if err != nil {
			return model.User{}, fmt.Errorf("update user avatar url: %w", err)
		}

		user, err := loadUserWithoutPasswordHash(ctx, tx, userID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return model.User{}, ErrNotFound
			}
			return model.User{}, fmt.Errorf("update user avatar url: %w", err)
		}

		return user, nil
	})
}

// UpdateUserDisplayName updates one user profile display name and returns the updated user.
func (store *Store) UpdateUserDisplayName(ctx context.Context, userID string, displayName string) (model.User, error) {
	trimmedDisplayName := strings.TrimSpace(displayName)
	return withTxValue(ctx, store.db, func(tx coredb.Tx) (model.User, error) {
		previousUser, err := loadUserWithoutPasswordHash(ctx, tx, userID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return model.User{}, ErrNotFound
			}
			return model.User{}, fmt.Errorf("update user display name: %w", err)
		}

		if _, err := tx.Exec(ctx, `
UPDATE users
SET display_name = $2, updated_at = NOW()
WHERE id = $1
`, userID, trimmedDisplayName); err != nil {
			return model.User{}, fmt.Errorf("update user display name: %w", err)
		}

		if trimmedDisplayName != "" {
			pinyinFull, pinyinInitials := pinyinsearch.Variants(trimmedDisplayName)
			if _, err := tx.Exec(ctx, `
UPDATE members
SET display_name_pinyin = $2,
    display_name_pinyin_initials = $3,
    updated_at = NOW()
WHERE user_id = $1
`, userID, pinyinFull, pinyinInitials); err != nil {
				return model.User{}, fmt.Errorf("sync member display name pinyin: %w", err)
			}
		}

		nextWorkspaceName := defaultPersonalWorkspaceName(model.User{
			Email:       previousUser.Email,
			Name:        previousUser.Name,
			DisplayName: trimmedDisplayName,
		})
		previousWorkspaceName := defaultPersonalWorkspaceName(previousUser)
		if nextWorkspaceName != previousWorkspaceName {
			if _, err := tx.Exec(ctx, `
UPDATE workspaces
SET name = $2, updated_at = NOW()
WHERE name = $3
  AND id IN (
    SELECT os.default_workspace_id
    FROM organization_settings os
    JOIN organizations o ON o.id = os.organization_id
    JOIN organization_members om ON om.organization_id = o.id
    WHERE o.created_by_user_id = $1
      AND om.user_id = $1
      AND om.status = 'active'
  )
`, userID, nextWorkspaceName, previousWorkspaceName); err != nil {
				return model.User{}, fmt.Errorf("update personal workspace name: %w", err)
			}
		}

		user, err := loadUserWithoutPasswordHash(ctx, tx, userID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return model.User{}, ErrNotFound
			}
			return model.User{}, fmt.Errorf("update user display name: %w", err)
		}

		return user, nil
	})
}

func defaultPersonalWorkspaceName(user model.User) string {
	name := strings.TrimSpace(user.DisplayName)
	if name == "" {
		name = strings.TrimSpace(user.Name)
	}
	if name == "" && strings.Contains(user.Email, "@") {
		name = strings.TrimSpace(strings.SplitN(user.Email, "@", 2)[0])
	}
	if name == "" {
		name = "Personal"
	}
	return name + "'s Workspace"
}

// LoadUserPasswordHash returns the stored Argon2id password hash for one user.
//
// Most user-facing queries intentionally do not select `users.password_hash` to minimize
// sensitive field exposure. Only use this helper for password verification flows.
func (store *Store) LoadUserPasswordHash(ctx context.Context, userID string) (string, error) {
	row := store.db.QueryRow(ctx, `SELECT password_hash FROM users WHERE id = $1`, userID)
	var passwordHash string
	if err := row.Scan(&passwordHash); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load user password hash: %w", err)
	}
	return passwordHash, nil
}

// UpdateUserPasswordHash updates one user password hash and returns the updated user.
func (store *Store) UpdateUserPasswordHash(ctx context.Context, userID string, passwordHash string) (model.User, error) {
	return withTxValue(ctx, store.db, func(tx coredb.Tx) (model.User, error) {
		if _, err := tx.Exec(ctx, `
UPDATE users
SET password_hash = $2, updated_at = NOW()
WHERE id = $1
`, userID, passwordHash); err != nil {
			return model.User{}, fmt.Errorf("update user password hash: %w", err)
		}

		user, err := loadUserWithoutPasswordHash(ctx, tx, userID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return model.User{}, ErrNotFound
			}
			return model.User{}, fmt.Errorf("update user password hash: %w", err)
		}

		return user, nil
	})
}

// PingCAPAuthUserInfo represents user information from PingCAP User Center
type PingCAPAuthUserInfo struct {
	OpenID         string
	DisplayName    string
	Email          string
	EmailVerified  bool
	Mobile         string
	MobileVerified bool
	AvatarURL      string
}

const pingCAPAuthNameInsertMaxAttempts = 5

// FindOrCreateUserByPingCAPAuth finds an existing user by PingCAP User Center identity or creates a new one
func (store *Store) FindOrCreateUserByPingCAPAuth(ctx context.Context, userInfo PingCAPAuthUserInfo) (model.User, bool, error) {
	resolvedUserInfo, err := normalizePingCAPAuthUserInfo(userInfo)
	if err != nil {
		return model.User{}, false, err
	}
	userInfo = resolvedUserInfo

	return withTx2(ctx, store.db, func(tx coredb.Tx) (model.User, bool, error) {
		// First, try to find an existing user by pingcap_sub
		var user model.User
		row := tx.QueryRow(ctx, `
SELECT id, COALESCE(email, ''), COALESCE(mobile, ''), name, display_name, avatar_url, is_admin, email_verified_at, mobile_verified_at, created_at, updated_at
FROM users
WHERE pingcap_sub = $1
	`, userInfo.OpenID)
		err := row.Scan(&user.ID, &user.Email, &user.Mobile, &user.Name, &user.DisplayName, &user.AvatarURL, &user.IsAdmin, &user.EmailVerifiedAt, &user.MobileVerifiedAt, &user.CreatedAt, &user.UpdatedAt)
		if err == nil {
			// Found existing user by pingcap_sub
			// Update user info from PingCAP User Center
			now := time.Now().UTC()
			if err := updateUserFromPingCAPAuth(ctx, tx, user.ID, userInfo, now, false); err != nil {
				return model.User{}, false, err
			}
			user, err = loadUserWithoutPasswordHash(ctx, tx, user.ID)
			if err != nil {
				if coredb.IsNotFound(err) {
					return model.User{}, false, ErrNotFound
				}
				return model.User{}, false, fmt.Errorf("load user from pingcap auth update: %w", err)
			}

			return user, false, nil
		}
		if !coredb.IsNotFound(err) {
			return model.User{}, false, fmt.Errorf("find user by pingcap_sub: %w", err)
		}

		// No user found by pingcap_sub, check if there's an existing user with the same email
		if userInfo.Email != "" {
			row = tx.QueryRow(ctx, `
SELECT id, COALESCE(email, ''), COALESCE(mobile, ''), name, display_name, avatar_url, is_admin, email_verified_at, mobile_verified_at, created_at, updated_at
FROM users
WHERE email = $1
	`, userInfo.Email)
			err = row.Scan(&user.ID, &user.Email, &user.Mobile, &user.Name, &user.DisplayName, &user.AvatarURL, &user.IsAdmin, &user.EmailVerifiedAt, &user.MobileVerifiedAt, &user.CreatedAt, &user.UpdatedAt)
			if err == nil {
				// Found existing user by email, link the PingCAP User Center identity
				now := time.Now().UTC()
				if err := updateUserFromPingCAPAuth(ctx, tx, user.ID, userInfo, now, true); err != nil {
					return model.User{}, false, err
				}
				user, err = loadUserWithoutPasswordHash(ctx, tx, user.ID)
				if err != nil {
					if coredb.IsNotFound(err) {
						return model.User{}, false, ErrNotFound
					}
					return model.User{}, false, fmt.Errorf("load linked pingcap auth user: %w", err)
				}

				return user, false, nil
			}
			if !coredb.IsNotFound(err) {
				return model.User{}, false, fmt.Errorf("find user by email: %w", err)
			}
		}

		if userInfo.Mobile != "" {
			row = tx.QueryRow(ctx, `
SELECT id, COALESCE(email, ''), COALESCE(mobile, ''), name, display_name, avatar_url, is_admin, email_verified_at, mobile_verified_at, created_at, updated_at
FROM users
WHERE mobile = $1
	`, userInfo.Mobile)
			err = row.Scan(&user.ID, &user.Email, &user.Mobile, &user.Name, &user.DisplayName, &user.AvatarURL, &user.IsAdmin, &user.EmailVerifiedAt, &user.MobileVerifiedAt, &user.CreatedAt, &user.UpdatedAt)
			if err == nil {
				now := time.Now().UTC()
				if err := updateUserFromPingCAPAuth(ctx, tx, user.ID, userInfo, now, true); err != nil {
					return model.User{}, false, err
				}
				user, err = loadUserWithoutPasswordHash(ctx, tx, user.ID)
				if err != nil {
					if coredb.IsNotFound(err) {
						return model.User{}, false, ErrNotFound
					}
					return model.User{}, false, fmt.Errorf("load linked pingcap auth user by mobile: %w", err)
				}

				return user, false, nil
			}
			if !coredb.IsNotFound(err) {
				return model.User{}, false, fmt.Errorf("find user by mobile: %w", err)
			}
		}

		// No existing user found, create a new one
		userID, err := identifiers.NewEntityID("usr")
		if err != nil {
			return model.User{}, false, fmt.Errorf("generate user id: %w", err)
		}
		now := time.Now().UTC()

		// Generate a unique name from display name, email, or mobile.
		nameSeed := userInfo.Email
		if nameSeed == "" {
			nameSeed = userInfo.Mobile
		}
		emailVerifiedAt := registration.EmailVerifiedAt(userInfo.Email != "" && userInfo.EmailVerified, now)
		mobileVerifiedAt := registration.EmailVerifiedAt(userInfo.Mobile != "" && userInfo.MobileVerified, now)
		rejectedNames := make(map[string]struct{})

		for attempt := 0; attempt < pingCAPAuthNameInsertMaxAttempts; attempt++ {
			name, err := registration.NextUniqueName(ctx, registration.PingCAPNameSeed(userInfo.DisplayName, nameSeed), func(ctx context.Context, candidate string) (bool, error) {
				if _, rejected := rejectedNames[candidate]; rejected {
					return true, nil
				}
				var exists bool
				checkRow := tx.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM users WHERE name = $1)`, candidate)
				if err := checkRow.Scan(&exists); err != nil {
					return false, fmt.Errorf("check name uniqueness: %w", err)
				}
				return exists, nil
			})
			if err != nil {
				return model.User{}, false, err
			}

			if _, err := tx.Exec(ctx, `
INSERT INTO users (id, email, name, display_name, avatar_url, is_admin, password_hash, email_verified_at, mobile, mobile_verified_at, auth_provider, pingcap_sub, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, FALSE, '', $6, $7, $8, $9, $10, $11, $11)
	`, userID, nullableString(userInfo.Email), name, userInfo.DisplayName, userInfo.AvatarURL, emailVerifiedAt, nullableString(userInfo.Mobile), mobileVerifiedAt, "pingcap-auth", userInfo.OpenID, now); err != nil {
				if constraint, ok := uniqueViolationConstraint(err); ok {
					switch constraint {
					case "email", "users_email_key":
						return model.User{}, false, conflict("Email already registered. Try signing in instead.")
					case "users_mobile_key":
						return model.User{}, false, conflict("Mobile already registered. Try signing in instead.")
					case "name", "users_name_key":
						rejectedNames[name] = struct{}{}
						if attempt+1 < pingCAPAuthNameInsertMaxAttempts {
							continue
						}
						return model.User{}, false, conflict("Name already taken. Try another one.")
					default:
						return model.User{}, false, conflict("Account already exists.")
					}
				}
				return model.User{}, false, fmt.Errorf("insert user from pingcap auth: %w", err)
			}

			user = modelUserFromRegistration(registration.BuildUserRecord(registration.UserRecordParams{
				ID:               userID,
				Email:            userInfo.Email,
				Mobile:           userInfo.Mobile,
				Name:             name,
				DisplayName:      userInfo.DisplayName,
				AvatarURL:        userInfo.AvatarURL,
				EmailVerifiedAt:  emailVerifiedAt,
				MobileVerifiedAt: mobileVerifiedAt,
				Now:              now,
			}))

			return user, true, nil
		}

		return model.User{}, false, conflict("Name already taken. Try another one.")
	})
}

func normalizePingCAPAuthUserInfo(userInfo PingCAPAuthUserInfo) (PingCAPAuthUserInfo, error) {
	resolved := PingCAPAuthUserInfo{
		OpenID:         strings.TrimSpace(userInfo.OpenID),
		DisplayName:    strings.TrimSpace(userInfo.DisplayName),
		EmailVerified:  userInfo.EmailVerified,
		MobileVerified: userInfo.MobileVerified,
		AvatarURL:      strings.TrimSpace(userInfo.AvatarURL),
	}
	if resolved.OpenID == "" {
		return PingCAPAuthUserInfo{}, badRequest("PingCAP Auth subject is required")
	}

	trimmedEmail := strings.TrimSpace(userInfo.Email)
	if trimmedEmail != "" {
		email, err := authidentity.NormalizeEmail(trimmedEmail)
		if err != nil {
			return PingCAPAuthUserInfo{}, err
		}
		resolved.Email = email
	}

	trimmedMobile := strings.TrimSpace(userInfo.Mobile)
	if trimmedMobile != "" && userInfo.MobileVerified {
		mobile, err := authidentity.NormalizeMobile(trimmedMobile)
		if err != nil {
			return PingCAPAuthUserInfo{}, err
		}
		resolved.Mobile = mobile
	} else {
		resolved.MobileVerified = false
	}

	if resolved.Email == "" && resolved.Mobile == "" {
		return PingCAPAuthUserInfo{}, badRequest("PingCAP Auth user must include email or verified mobile")
	}
	return resolved, nil
}

func updateUserFromPingCAPAuth(ctx context.Context, tx coredb.Tx, userID string, userInfo PingCAPAuthUserInfo, now time.Time, link bool) error {
	emailVerifiedAt := registration.EmailVerifiedAt(userInfo.Email != "" && userInfo.EmailVerified, now)
	mobileVerifiedAt := registration.EmailVerifiedAt(userInfo.Mobile != "" && userInfo.MobileVerified, now)

	authProviderUpdate := ""
	args := []any{
		userID,
		userInfo.Email,
		emailVerifiedAt,
		userInfo.Mobile,
		mobileVerifiedAt,
		now,
	}
	if link {
		authProviderUpdate = `
	    auth_provider = $7,
	    pingcap_sub = $8,`
		args = append(args, "pingcap-auth", userInfo.OpenID)
	}

	query := `
	UPDATE users
	SET email = CASE
	        WHEN COALESCE(email, '') = '' THEN NULLIF($2, '')
	        ELSE email
	    END,
	    email_verified_at = CASE
	        WHEN NULLIF($2, '') IS NULL THEN email_verified_at
	        ELSE COALESCE(email_verified_at, $3)
	    END,
	    mobile = CASE
	        WHEN NULLIF($4, '') IS NULL THEN mobile
	        ELSE $4
	    END,
	    mobile_verified_at = CASE
	        WHEN NULLIF($4, '') IS NULL THEN mobile_verified_at
	        WHEN COALESCE(mobile, '') = $4 THEN COALESCE(mobile_verified_at, $5)
	        ELSE $5
	    END,` + authProviderUpdate + `
	    updated_at = $6
	WHERE id = $1
	`
	if _, err := tx.Exec(ctx, query, args...); err != nil {
		if constraint, ok := uniqueViolationConstraint(err); ok {
			switch constraint {
			case "email", "users_email_key":
				return conflict("Email already registered. Try signing in instead.")
			case "users_mobile_key":
				return conflict("Mobile already registered. Try signing in instead.")
			default:
				return conflict("Account already exists.")
			}
		}
		return fmt.Errorf("update user from pingcap auth: %w", err)
	}
	return nil
}

func nullableString(value string) any {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return nil
	}
	return trimmed
}