plumber/internal/store/user.go

package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgconn"

	"plumber/internal/store/sqlc"
)

var (
	// ErrLastAdmin is returned when demoting the only remaining admin.
	ErrLastAdmin = errors.New("cannot demote the last admin")
	// ErrDuplicateUsername is returned when inserting an existing username.
	ErrDuplicateUsername = errors.New("username taken")
	// ErrDuplicateEmail is returned when inserting or updating an existing email.
	ErrDuplicateEmail = errors.New("email taken")
)

// Role is a user privilege level stored in users.role.
type Role string

const (
	RoleUser  Role = "user"
	RoleAdmin Role = "admin"
)

// User is an account row. Methods run SQL against db via sqlc.
type User struct {
	ID           string
	Username     string
	Name         string
	Role         Role
	Email        string
	AvatarURL    string
	State        string
	CreatedAt    string
	PasswordHash string
	db           *sql.DB
}

// NewUser returns a User bound to db (not yet inserted).
func NewUser(db *sql.DB) *User {
	return &User{db: db}
}

func (u *User) Admin() bool {
	return u != nil && u.Role == RoleAdmin
}

func NormalizeUsername(s string) string {
	return strings.ToLower(strings.TrimSpace(s))
}

func mapUniqueViolation(err error) error {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && pgErr.Code == "23505" {
		if strings.Contains(strings.ToLower(pgErr.ConstraintName), "email") {
			return ErrDuplicateEmail
		}
		return ErrDuplicateUsername
	}
	return err
}

func toUser(db *sql.DB, id, username, name, role, email, avatarURL, state, createdAt, passwordHash string) *User {
	return &User{
		ID:           id,
		Username:     username,
		Name:         name,
		Role:         Role(role),
		Email:        email,
		AvatarURL:    avatarURL,
		State:        state,
		CreatedAt:    createdAt,
		PasswordHash: passwordHash,
		db:           db,
	}
}

// Create inserts the user. Sets ID, Name, and CreatedAt when empty.
func (u *User) Create(ctx context.Context) error {
	if u == nil || u.db == nil {
		return fmt.Errorf("user: no database")
	}
	if u.Role != RoleUser && u.Role != RoleAdmin {
		return fmt.Errorf("invalid role")
	}
	u.Username = NormalizeUsername(u.Username)
	u.Email = NormalizeEmail(u.Email)
	if u.ID == "" {
		u.ID = uuid.NewString()
	}
	if u.Name == "" {
		u.Name = u.Username
	}
	if u.CreatedAt == "" {
		u.CreatedAt = time.Now().UTC().Format(time.RFC3339)
	}
	return mapUniqueViolation(sqlc.New(u.db).CreateUser(ctx, sqlc.CreateUserParams{
		ID:           u.ID,
		Username:     u.Username,
		Name:         u.Name,
		PasswordHash: u.PasswordHash,
		Role:         string(u.Role),
		Email:        u.Email,
		CreatedAt:    u.CreatedAt,
	}))
}

// adminRoleLockKey serializes SetRole so concurrent demotions cannot bypass the
// last-admin guard under READ COMMITTED.
const adminRoleLockKey int64 = 0x706c756d5f61646d // "plum_adm"

// SetRole updates this user's role (last-admin safe).
func (u *User) SetRole(ctx context.Context, role Role) error {
	if u == nil || u.db == nil {
		return fmt.Errorf("user: no database")
	}
	if role != RoleUser && role != RoleAdmin {
		return fmt.Errorf("invalid role")
	}
	tx, err := u.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.ExecContext(ctx, `SELECT pg_advisory_xact_lock($1)`, adminRoleLockKey); err != nil {
		return err
	}

	q := sqlc.New(tx)
	current, err := q.GetUserRole(ctx, u.ID)
	if err != nil {
		return err
	}
	if Role(current) == RoleAdmin && role == RoleUser {
		n, err := q.CountAdmins(ctx, string(RoleAdmin))
		if err != nil {
			return err
		}
		if n <= 1 {
			return ErrLastAdmin
		}
	}
	res, err := q.UpdateUserRole(ctx, sqlc.UpdateUserRoleParams{
		Role: string(role),
		ID:   u.ID,
	})
	if err != nil {
		return err
	}
	aff, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if aff == 0 {
		return sql.ErrNoRows
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	u.Role = role
	return nil
}

// SaveProfile writes Email, State, and optionally AvatarURL.
func (u *User) SaveProfile(ctx context.Context) error {
	if u == nil || u.db == nil {
		return fmt.Errorf("user: no database")
	}
	u.State = strings.TrimSpace(u.State)
	u.Email = NormalizeEmail(u.Email)
	q := sqlc.New(u.db)
	if u.AvatarURL == "" {
		return mapUniqueViolation(q.UpdateUserProfile(ctx, sqlc.UpdateUserProfileParams{
			State: u.State,
			Email: u.Email,
			ID:    u.ID,
		}))
	}
	return mapUniqueViolation(q.UpdateUserProfileAndAvatar(ctx, sqlc.UpdateUserProfileAndAvatarParams{
		State:     u.State,
		Email:     u.Email,
		AvatarUrl: u.AvatarURL,
		ID:        u.ID,
	}))
}

func CountAdmins(ctx context.Context, db *sql.DB) (int, error) {
	n, err := sqlc.New(db).CountAdmins(ctx, string(RoleAdmin))
	return int(n), err
}

func ListUsers(ctx context.Context, db *sql.DB, q ListUsersQuery) ([]User, string, string, error) {
	limit := q.Limit
	if limit <= 0 {
		limit = AdminUsersLimit
	}
	rows, err := sqlc.New(db).ListUsers(ctx, sqlc.ListUsersParams{
		Search:        q.Search,
		CursorCreated: q.CursorCreated,
		CursorID:      q.CursorID,
		RowLimit:      int32(limit + 1),
	})
	if err != nil {
		return nil, "", "", err
	}
	out := make([]User, 0, len(rows))
	for _, r := range rows {
		u := toUser(db, r.ID, r.Username, r.Name, r.Role, r.Email, r.AvatarUrl, r.State, r.CreatedAt, "")
		out = append(out, *u)
	}
	var nextCreated, nextID string
	if len(out) > limit {
		last := out[limit-1]
		nextCreated, nextID = last.CreatedAt, last.ID
		out = out[:limit]
	}
	return out, nextCreated, nextID, nil
}

func UserByID(ctx context.Context, db *sql.DB, id string) (*User, error) {
	r, err := sqlc.New(db).GetUserByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return toUser(db, r.ID, r.Username, r.Name, r.Role, r.Email, r.AvatarUrl, r.State, r.CreatedAt, ""), nil
}

func UserByUsername(ctx context.Context, db *sql.DB, username string) (*User, error) {
	r, err := sqlc.New(db).GetUserByUsername(ctx, NormalizeUsername(username))
	if err != nil {
		return nil, err
	}
	return toUser(db, r.ID, r.Username, r.Name, r.Role, r.Email, r.AvatarUrl, r.State, r.CreatedAt, r.PasswordHash), nil
}