user_db.go (7439B)
1 package internal 2 3 import ( 4 "context" 5 "crypto/rand" 6 "database/sql" 7 "encoding/base64" 8 "fmt" 9 "time" 10 11 "golang.org/x/crypto/bcrypt" 12 ) 13 14 // CreateUser inserts a new user with a bcrypt-hashed password and returns the new ID. 15 func (db *DB) CreateUser(ctx context.Context, username, password string) (int64, error) { 16 if err := ValidateUsername(username); err != nil { 17 return 0, err 18 } 19 hash, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost) 20 if err != nil { 21 return 0, fmt.Errorf("hash password: %w", err) 22 } 23 // Generate an API token on creation 24 b := make([]byte, 32) 25 if _, err := rand.Read(b); err != nil { 26 return 0, fmt.Errorf("rand: %w", err) 27 } 28 token := base64.RawURLEncoding.EncodeToString(b) 29 now := time.Now().UTC().Format(time.RFC3339Nano) 30 res, err := db.sql.ExecContext(ctx, ` 31 INSERT INTO users(username, encrypted_password, api_token, created_at, updated_at) 32 VALUES(?, ?, ?, ?, ?) 33 `, username, string(hash), token, now, now) 34 if err != nil { 35 return 0, fmt.Errorf("insert user: %w", err) 36 } 37 id, err := res.LastInsertId() 38 if err != nil { 39 return 0, fmt.Errorf("last id: %w", err) 40 } 41 // Backfill: assign any orphan bookmarks (pre-user era) to the first created user 42 if _, err := db.sql.ExecContext(ctx, `UPDATE bookmarks SET user_id = ? WHERE user_id IS NULL`, id); err != nil { 43 return 0, fmt.Errorf("backfill bookmarks: %w", err) 44 } 45 return id, nil 46 } 47 48 // VerifyUserPassword checks credentials and returns (userID, ok, err). 49 func (db *DB) VerifyUserPassword(ctx context.Context, username, password string) (int64, bool, error) { 50 var id int64 51 var hash string 52 err := db.sql.QueryRowContext(ctx, `SELECT id, encrypted_password FROM users WHERE username = ?`, username).Scan(&id, &hash) 53 if err != nil { 54 if err == sql.ErrNoRows { 55 return 0, false, nil 56 } 57 return 0, false, fmt.Errorf("lookup user: %w", err) 58 } 59 if bcrypt.CompareHashAndPassword([]byte(hash), []byte(password)) != nil { 60 return 0, false, nil 61 } 62 return id, true, nil 63 } 64 65 func (db *DB) GetUserByID(ctx context.Context, id int64) (*User, error) { 66 var u User 67 var apiToken sql.NullString 68 if err := db.sql.QueryRowContext(ctx, ` 69 SELECT id, username, encrypted_password, api_token FROM users WHERE id = ? 70 `, id).Scan(&u.ID, &u.Username, &u.EncryptedPassword, &apiToken); err != nil { 71 if err == sql.ErrNoRows { 72 return nil, nil 73 } 74 return nil, fmt.Errorf("get user: %w", err) 75 } 76 if apiToken.Valid { 77 s := apiToken.String 78 u.APIToken = &s 79 } 80 return &u, nil 81 } 82 83 func (db *DB) GetUserByUsername(ctx context.Context, username string) (*User, error) { 84 var u User 85 var apiToken sql.NullString 86 if err := db.sql.QueryRowContext(ctx, ` 87 SELECT id, username, encrypted_password, api_token FROM users WHERE username = ? 88 `, username).Scan(&u.ID, &u.Username, &u.EncryptedPassword, &apiToken); err != nil { 89 if err == sql.ErrNoRows { 90 return nil, nil 91 } 92 return nil, fmt.Errorf("get user by username: %w", err) 93 } 94 if apiToken.Valid { 95 s := apiToken.String 96 u.APIToken = &s 97 } 98 return &u, nil 99 } 100 101 // ListPublicBookmarks lists only public bookmarks for a given user. 102 func (db *DB) ListPublicBookmarks(ctx context.Context, userID int64, limit, offset int) ([]Bookmark, error) { 103 if limit <= 0 { 104 limit = 50 105 } 106 rows, err := db.sql.QueryContext(ctx, ` 107 SELECT id, url, title, description, referrer, public, user_id, created_at, updated_at 108 FROM bookmarks WHERE user_id = ? AND public = 1 109 ORDER BY updated_at DESC 110 LIMIT ? OFFSET ? 111 `, userID, limit, offset) 112 if err != nil { 113 return nil, fmt.Errorf("query public bookmarks: %w", err) 114 } 115 defer rows.Close() 116 out := make([]Bookmark, 0, limit) 117 for rows.Next() { 118 var b Bookmark 119 var created, updated string 120 if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil { 121 return nil, fmt.Errorf("scan: %w", err) 122 } 123 var e error 124 if b.CreatedAt, e = time.Parse(time.RFC3339Nano, created); e != nil { 125 return nil, e 126 } 127 if b.UpdatedAt, e = time.Parse(time.RFC3339Nano, updated); e != nil { 128 return nil, e 129 } 130 out = append(out, b) 131 } 132 if err := rows.Err(); err != nil { 133 return nil, err 134 } 135 return out, nil 136 } 137 138 // CountPublicBookmarks returns the total number of public bookmarks for a user. 139 func (db *DB) CountPublicBookmarks(ctx context.Context, userID int64) (int, error) { 140 var n int 141 if err := db.sql.QueryRowContext(ctx, `SELECT COUNT(1) FROM bookmarks WHERE user_id = ? AND public = 1`, userID).Scan(&n); err != nil { 142 return 0, fmt.Errorf("count public bookmarks: %w", err) 143 } 144 return n, nil 145 } 146 147 // ListPrivateBookmarks lists only private bookmarks (public = 0) for a given user. 148 func (db *DB) ListPrivateBookmarks(ctx context.Context, userID int64, limit, offset int) ([]Bookmark, error) { 149 if limit <= 0 { 150 limit = 50 151 } 152 rows, err := db.sql.QueryContext(ctx, ` 153 SELECT id, url, title, description, referrer, public, user_id, created_at, updated_at 154 FROM bookmarks WHERE user_id = ? AND public = 0 155 ORDER BY updated_at DESC 156 LIMIT ? OFFSET ? 157 `, userID, limit, offset) 158 if err != nil { 159 return nil, fmt.Errorf("query private bookmarks: %w", err) 160 } 161 defer rows.Close() 162 out := make([]Bookmark, 0, limit) 163 for rows.Next() { 164 var b Bookmark 165 var created, updated string 166 if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil { 167 return nil, fmt.Errorf("scan: %w", err) 168 } 169 var e error 170 if b.CreatedAt, e = time.Parse(time.RFC3339Nano, created); e != nil { 171 return nil, e 172 } 173 if b.UpdatedAt, e = time.Parse(time.RFC3339Nano, updated); e != nil { 174 return nil, e 175 } 176 out = append(out, b) 177 } 178 if err := rows.Err(); err != nil { 179 return nil, err 180 } 181 return out, nil 182 } 183 184 // CountPrivateBookmarks returns the total number of private bookmarks for a user. 185 func (db *DB) CountPrivateBookmarks(ctx context.Context, userID int64) (int, error) { 186 var n int 187 if err := db.sql.QueryRowContext(ctx, `SELECT COUNT(1) FROM bookmarks WHERE user_id = ? AND public = 0`, userID).Scan(&n); err != nil { 188 return 0, fmt.Errorf("count private bookmarks: %w", err) 189 } 190 return n, nil 191 } 192 193 // GetAPIToken returns the user's API token if set. 194 func (db *DB) GetAPIToken(ctx context.Context, userID int64) (string, bool, error) { 195 var tok sql.NullString 196 if err := db.sql.QueryRowContext(ctx, `SELECT api_token FROM users WHERE id = ?`, userID).Scan(&tok); err != nil { 197 if err == sql.ErrNoRows { 198 return "", false, nil 199 } 200 return "", false, fmt.Errorf("get api token: %w", err) 201 } 202 if tok.Valid && tok.String != "" { 203 return tok.String, true, nil 204 } 205 return "", false, nil 206 } 207 208 // RegenerateAPIToken generates a new unique API token for the user and stores it. 209 func (db *DB) RegenerateAPIToken(ctx context.Context, userID int64) (string, error) { 210 // Try a few times in case of uniqueness collision 211 for i := 0; i < 5; i++ { 212 b := make([]byte, 32) 213 if _, err := rand.Read(b); err != nil { 214 return "", fmt.Errorf("rand: %w", err) 215 } 216 token := base64.RawURLEncoding.EncodeToString(b) 217 now := time.Now().UTC().Format(time.RFC3339Nano) 218 _, err := db.sql.ExecContext(ctx, `UPDATE users SET api_token = ?, updated_at = ? WHERE id = ?`, token, now, userID) 219 if err == nil { 220 return token, nil 221 } 222 // If unique conflict, retry 223 } 224 return "", fmt.Errorf("failed to generate unique api token after retries") 225 }