housecarl/internal/store/users.go

package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"strings"
	"time"

	"housecarl/internal/model"
)

const userCols = `id, username, password_hash, admin, grants, disabled, comment, created_at, updated_at, last_login_at`

func scanUser(sc interface{ Scan(...any) error }) (*model.User, error) {
	u := &model.User{}
	var grants, ca, ua string
	var admin, disabled int
	var ll sql.NullString
	if err := sc.Scan(&u.ID, &u.Username, &u.PasswordHash, &admin, &grants, &disabled, &u.Comment, &ca, &ua, &ll); err != nil {
		return nil, err
	}
	u.Admin, u.Disabled = admin != 0, disabled != 0
	u.HasPassword = u.PasswordHash != ""
	if err := json.Unmarshal([]byte(grants), &u.Grants); err != nil {
		return nil, err
	}
	if u.Grants == nil {
		u.Grants = []model.Grant{}
	}
	u.CreatedAt, u.UpdatedAt, u.LastLoginAt = parseTS(ca), parseTS(ua), parseTSPtr(ll)
	return u, nil
}

// CreateUser inserts a user.
func (s *Store) CreateUser(ctx context.Context, u *model.User) error {
	now := time.Now()
	u.CreatedAt, u.UpdatedAt = now, now
	if u.Grants == nil {
		u.Grants = []model.Grant{}
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO users (username, password_hash, admin, grants, disabled, comment, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?)`,
		u.Username, u.PasswordHash, boolInt(u.Admin), jsonText(u.Grants), boolInt(u.Disabled), u.Comment, ts(now), ts(now))
	if isUnique(err) {
		return ErrConflict
	}
	if err != nil {
		return err
	}
	u.ID, err = res.LastInsertId()
	u.HasPassword = u.PasswordHash != ""
	return err
}

// UpdateUser saves a user.
func (s *Store) UpdateUser(ctx context.Context, u *model.User) error {
	u.UpdatedAt = time.Now()
	res, err := s.db.ExecContext(ctx, `UPDATE users SET username = ?, password_hash = ?, admin = ?, grants = ?, disabled = ?, comment = ?, updated_at = ? WHERE id = ?`,
		u.Username, u.PasswordHash, boolInt(u.Admin), jsonText(u.Grants), boolInt(u.Disabled), u.Comment, ts(u.UpdatedAt), u.ID)
	if isUnique(err) {
		return ErrConflict
	}
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	u.HasPassword = u.PasswordHash != ""
	return nil
}

// TouchLogin records a successful login.
func (s *Store) TouchLogin(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx, `UPDATE users SET last_login_at = ? WHERE id = ?`, ts(time.Now()), id)
	return err
}

// GetUser loads a user by ID.
func (s *Store) GetUser(ctx context.Context, id int64) (*model.User, error) {
	u, err := scanUser(s.db.QueryRowContext(ctx, `SELECT `+userCols+` FROM users WHERE id = ?`, id))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return u, err
}

// GetUserByName loads a user by name (case-insensitive).
func (s *Store) GetUserByName(ctx context.Context, name string) (*model.User, error) {
	u, err := scanUser(s.db.QueryRowContext(ctx, `SELECT `+userCols+` FROM users WHERE username = ?`, name))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return u, err
}

// ListUsers lists all users by name.
func (s *Store) ListUsers(ctx context.Context) ([]*model.User, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT `+userCols+` FROM users ORDER BY username COLLATE NOCASE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*model.User
	for rows.Next() {
		u, err := scanUser(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

// CountAdmins counts the enabled administrators.
func (s *Store) CountAdmins(ctx context.Context) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE admin = 1 AND disabled = 0`).Scan(&n)
	return n, err
}

// DeleteUser removes a user with their tokens and sessions.
func (s *Store) DeleteUser(ctx context.Context, id int64) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM users WHERE id = ?`, id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

const tokenCols = `t.id, t.user_id, u.username, t.name, t.public_id, t.secret_hash, t.grants, t.expires_at, t.created_at, t.last_used_at, t.last_used_ip, t.revoked_at, t.rotated_at, t.prev_secret_hash, t.prev_expires_at`

func scanToken(sc interface{ Scan(...any) error }) (*model.Token, error) {
	t := &model.Token{}
	var grants, exp, lu, rev, rot, pexp sql.NullString
	var ca string
	if err := sc.Scan(&t.ID, &t.UserID, &t.Username, &t.Name, &t.PublicID, &t.SecretHash, &grants, &exp, &ca, &lu, &t.LastUsedIP, &rev, &rot, &t.PrevSecretHash, &pexp); err != nil {
		return nil, err
	}
	if grants.Valid {
		if err := json.Unmarshal([]byte(grants.String), &t.Grants); err != nil {
			return nil, err
		}
		if t.Grants == nil {
			t.Grants = []model.Grant{}
		}
	}
	t.ExpiresAt, t.LastUsedAt, t.RevokedAt = parseTSPtr(exp), parseTSPtr(lu), parseTSPtr(rev)
	t.RotatedAt, t.PrevExpiresAt = parseTSPtr(rot), parseTSPtr(pexp)
	t.CreatedAt = parseTS(ca)
	return t, nil
}

// CreateToken inserts a token.
func (s *Store) CreateToken(ctx context.Context, t *model.Token) error {
	t.CreatedAt = time.Now()
	var grants any
	if t.Grants != nil {
		grants = jsonText(t.Grants)
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO tokens (user_id, name, public_id, secret_hash, grants, expires_at, created_at) VALUES (?,?,?,?,?,?,?)`,
		t.UserID, t.Name, t.PublicID, t.SecretHash, grants, tsPtr(t.ExpiresAt), ts(t.CreatedAt))
	if isUnique(err) {
		return ErrConflict
	}
	if err != nil {
		return err
	}
	t.ID, err = res.LastInsertId()
	return err
}

// GetTokenByPublicID loads a token by its public ID.
func (s *Store) GetTokenByPublicID(ctx context.Context, pid string) (*model.Token, error) {
	t, err := scanToken(s.db.QueryRowContext(ctx, `SELECT `+tokenCols+` FROM tokens t JOIN users u ON u.id = t.user_id WHERE t.public_id = ?`, pid))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return t, err
}

// GetToken loads a token by ID.
func (s *Store) GetToken(ctx context.Context, id int64) (*model.Token, error) {
	t, err := scanToken(s.db.QueryRowContext(ctx, `SELECT `+tokenCols+` FROM tokens t JOIN users u ON u.id = t.user_id WHERE t.id = ?`, id))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return t, err
}

// ListTokens lists the tokens of one user (userID > 0) or of all.
func (s *Store) ListTokens(ctx context.Context, userID int64) ([]*model.Token, error) {
	q := `SELECT ` + tokenCols + ` FROM tokens t JOIN users u ON u.id = t.user_id`
	var args []any
	if userID > 0 {
		q += ` WHERE t.user_id = ?`
		args = append(args, userID)
	}
	q += ` ORDER BY t.id`
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*model.Token
	for rows.Next() {
		t, err := scanToken(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// RevokeToken marks a token revoked.
func (s *Store) RevokeToken(ctx context.Context, id int64) error {
	res, err := s.db.ExecContext(ctx, `UPDATE tokens SET revoked_at = ? WHERE id = ? AND revoked_at IS NULL`, ts(time.Now()), id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// RotateToken gives a token a new secret and expiry. With prevUntil set,
// the old secret keeps working until then; otherwise it stops at once. A
// revoked token can't be rotated (ErrNotFound).
func (s *Store) RotateToken(ctx context.Context, id int64, secretHash string, expiresAt, prevUntil *time.Time) error {
	prevHash := `''`
	if prevUntil != nil {
		prevHash = `secret_hash`
	}
	res, err := s.db.ExecContext(ctx, `UPDATE tokens SET prev_secret_hash = `+prevHash+`, prev_expires_at = ?, secret_hash = ?, expires_at = ?, rotated_at = ? WHERE id = ? AND revoked_at IS NULL`,
		tsPtr(prevUntil), secretHash, tsPtr(expiresAt), ts(time.Now()), id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// TouchToken records a token's use.
func (s *Store) TouchToken(ctx context.Context, id int64, ip string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE tokens SET last_used_at = ?, last_used_ip = ? WHERE id = ?`, ts(time.Now()), ip, id)
	return err
}

// CreateSession stores a session.
func (s *Store) CreateSession(ctx context.Context, se *model.Session) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO sessions (id_hash, user_id, csrf_token, created_at, expires_at, last_seen_at, ip, user_agent) VALUES (?,?,?,?,?,?,?,?)`,
		se.IDHash, se.UserID, se.CSRFToken, ts(se.CreatedAt), ts(se.ExpiresAt), ts(se.LastSeenAt), se.IP, se.UserAgent)
	return err
}

// GetSession loads a session by the hash of its ID.
func (s *Store) GetSession(ctx context.Context, idHash string) (*model.Session, error) {
	se := &model.Session{}
	var ca, ea, ls string
	err := s.db.QueryRowContext(ctx, `SELECT id_hash, user_id, csrf_token, created_at, expires_at, last_seen_at, ip, user_agent FROM sessions WHERE id_hash = ?`, idHash).
		Scan(&se.IDHash, &se.UserID, &se.CSRFToken, &ca, &ea, &ls, &se.IP, &se.UserAgent)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	se.CreatedAt, se.ExpiresAt, se.LastSeenAt = parseTS(ca), parseTS(ea), parseTS(ls)
	return se, nil
}

// TouchSession updates a session's last-seen time.
func (s *Store) TouchSession(ctx context.Context, idHash string, at time.Time) error {
	_, err := s.db.ExecContext(ctx, `UPDATE sessions SET last_seen_at = ? WHERE id_hash = ?`, ts(at), idHash)
	return err
}

// DeleteSession removes a session.
func (s *Store) DeleteSession(ctx context.Context, idHash string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id_hash = ?`, idHash)
	return err
}

// DeleteUserSessions removes a user's sessions, except keep (if set).
func (s *Store) DeleteUserSessions(ctx context.Context, userID int64, keep string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id = ? AND id_hash != ?`, userID, keep)
	return err
}

// PurgeSessions removes expired sessions.
func (s *Store) PurgeSessions(ctx context.Context, now time.Time) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE expires_at < ?`, ts(now))
	return err
}

// AddAudit stores an audit event.
func (s *Store) AddAudit(ctx context.Context, ev *model.AuditEvent) error {
	if ev.Time.IsZero() {
		ev.Time = time.Now()
	}
	var uid, tid any
	if ev.UserID != 0 {
		uid = ev.UserID
	}
	if ev.TokenID != 0 {
		tid = ev.TokenID
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO audit (time, user_id, username, auth, token_id, ip, action, object, result, detail) VALUES (?,?,?,?,?,?,?,?,?,?)`,
		ts(ev.Time), uid, ev.Username, ev.Auth, tid, ev.IP, ev.Action, ev.Object, ev.Result, ev.Detail)
	if err != nil {
		return err
	}
	ev.ID, _ = res.LastInsertId()
	return nil
}

// AuditFilter selects audit events.
type AuditFilter struct {
	Username string
	Action   string // prefix: "auth." or "set.add"
	Result   string
	Search   string // in the object or the detail
	Before   int64  // IDs below this (paging)
	Limit    int
}

// ListAudit lists audit events, newest first.
func (s *Store) ListAudit(ctx context.Context, f AuditFilter) ([]*model.AuditEvent, error) {
	var where []string
	var args []any
	if f.Username != "" {
		where = append(where, "username = ? COLLATE NOCASE")
		args = append(args, f.Username)
	}
	if f.Action != "" {
		where = append(where, "action LIKE ? ESCAPE '\\'")
		args = append(args, likeEscape(f.Action)+"%")
	}
	if f.Result != "" {
		where = append(where, "result = ?")
		args = append(args, f.Result)
	}
	if f.Search != "" {
		where = append(where, "(object LIKE ? ESCAPE '\\' OR detail LIKE ? ESCAPE '\\')")
		p := "%" + likeEscape(f.Search) + "%"
		args = append(args, p, p)
	}
	if f.Before > 0 {
		where = append(where, "id < ?")
		args = append(args, f.Before)
	}
	q := `SELECT id, time, user_id, username, auth, token_id, ip, action, object, result, detail FROM audit`
	if len(where) > 0 {
		q += " WHERE " + strings.Join(where, " AND ")
	}
	limit := f.Limit
	if limit <= 0 || limit > 1000 {
		limit = 200
	}
	q += " ORDER BY id DESC LIMIT ?"
	args = append(args, limit)
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []*model.AuditEvent{}
	for rows.Next() {
		ev := &model.AuditEvent{}
		var t string
		var uid, tid sql.NullInt64
		if err := rows.Scan(&ev.ID, &t, &uid, &ev.Username, &ev.Auth, &tid, &ev.IP, &ev.Action, &ev.Object, &ev.Result, &ev.Detail); err != nil {
			return nil, err
		}
		ev.Time = parseTS(t)
		ev.UserID, ev.TokenID = uid.Int64, tid.Int64
		out = append(out, ev)
	}
	return out, rows.Err()
}

func likeEscape(s string) string {
	return strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`).Replace(s)
}