internal/db/queries.go (view raw)
1package db
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "strings"
8 "time"
9)
10
11// ─────────────────────────── Users ───────────────────────────
12
13// CreateUser inserts a new user and returns it with the assigned ID.
14func (d *DB) CreateUser(ctx context.Context, username, passwordHash string, isAdmin bool) (*User, error) {
15 admin := 0
16 if isAdmin {
17 admin = 1
18 }
19 t := now()
20 res, err := d.db.ExecContext(ctx,
21 `INSERT INTO users (username, password_hash, is_admin, created_at, updated_at)
22 VALUES (?, ?, ?, ?, ?)`,
23 username, passwordHash, admin, t, t,
24 )
25 if err != nil {
26 return nil, fmt.Errorf("create user: %w", err)
27 }
28 id, err := res.LastInsertId()
29 if err != nil {
30 return nil, fmt.Errorf("create user last insert id: %w", err)
31 }
32 return d.GetUserByID(ctx, id)
33}
34
35// GetUserByID returns a single user by primary key.
36func (d *DB) GetUserByID(ctx context.Context, id int64) (*User, error) {
37 row := d.db.QueryRowContext(ctx,
38 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
39 FROM users WHERE id = ?`, id)
40 return scanUser(row)
41}
42
43// SetUserAvatar updates the avatar_path for a user. Pass nil to clear.
44func (d *DB) SetUserAvatar(ctx context.Context, id int64, avatarPath *string) error {
45 _, err := d.db.ExecContext(ctx,
46 `UPDATE users SET avatar_path = ?, updated_at = ? WHERE id = ?`,
47 avatarPath, now(), id)
48 return err
49}
50
51// GetUserByUsername returns a single user by username.
52func (d *DB) GetUserByUsername(ctx context.Context, username string) (*User, error) {
53 row := d.db.QueryRowContext(ctx,
54 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
55 FROM users WHERE username = ?`, username)
56 return scanUser(row)
57}
58
59// ListUsers returns all users ordered by username.
60func (d *DB) ListUsers(ctx context.Context) ([]User, error) {
61 rows, err := d.db.QueryContext(ctx,
62 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
63 FROM users ORDER BY username`)
64 if err != nil {
65 return nil, fmt.Errorf("list users: %w", err)
66 }
67 defer rows.Close()
68
69 users := make([]User, 0)
70 for rows.Next() {
71 u, err := scanUser(rows)
72 if err != nil {
73 return nil, err
74 }
75 users = append(users, *u)
76 }
77 return users, rows.Err()
78}
79
80// DeleteUser removes a user by ID. Returns sql.ErrNoRows if not found.
81func (d *DB) DeleteUser(ctx context.Context, id int64) error {
82 res, err := d.db.ExecContext(ctx, `DELETE FROM users WHERE id = ?`, id)
83 if err != nil {
84 return fmt.Errorf("delete user: %w", err)
85 }
86 n, _ := res.RowsAffected()
87 if n == 0 {
88 return sql.ErrNoRows
89 }
90 return nil
91}
92
93// UpdateUserPassword sets a new password hash for the user.
94func (d *DB) UpdateUserPassword(ctx context.Context, id int64, passwordHash string) error {
95 res, err := d.db.ExecContext(ctx,
96 `UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?`,
97 passwordHash, now(), id)
98 if err != nil {
99 return fmt.Errorf("update password: %w", err)
100 }
101 n, _ := res.RowsAffected()
102 if n == 0 {
103 return sql.ErrNoRows
104 }
105 return nil
106}
107
108type userScanner interface {
109 Scan(dest ...any) error
110}
111
112func scanUser(s userScanner) (*User, error) {
113 var u User
114 var isAdmin int
115 var createdAt, updatedAt string
116 var avatarPath *string
117 if err := s.Scan(&u.ID, &u.Username, &u.PasswordHash, &isAdmin, &createdAt, &updatedAt, &avatarPath); err != nil {
118 return nil, err
119 }
120 u.IsAdmin = isAdmin == 1
121 u.CreatedAt, _ = scanTime(createdAt)
122 u.UpdatedAt, _ = scanTime(updatedAt)
123 u.AvatarPath = avatarPath
124 return &u, nil
125}
126
127// ─────────────────────────── Sessions ───────────────────────────
128
129// CreateSession inserts a new session and returns it.
130func (d *DB) CreateSession(ctx context.Context, userID int64, token string, expiresAt time.Time) (*Session, error) {
131 t := now()
132 res, err := d.db.ExecContext(ctx,
133 `INSERT INTO sessions (user_id, token, expires_at, created_at)
134 VALUES (?, ?, ?, ?)`,
135 userID, token, expiresAt.UTC().Format(time.RFC3339), t,
136 )
137 if err != nil {
138 return nil, fmt.Errorf("create session: %w", err)
139 }
140 id, err := res.LastInsertId()
141 if err != nil {
142 return nil, fmt.Errorf("create session last insert id: %w", err)
143 }
144
145 return &Session{
146 ID: id,
147 UserID: userID,
148 Token: token,
149 ExpiresAt: expiresAt,
150 }, nil
151}
152
153// GetSessionByToken retrieves a session by its token.
154func (d *DB) GetSessionByToken(ctx context.Context, token string) (*Session, error) {
155 row := d.db.QueryRowContext(ctx,
156 `SELECT id, user_id, token, expires_at, created_at
157 FROM sessions WHERE token = ?`, token)
158 return scanSession(row)
159}
160
161// DeleteSession removes a session by token.
162func (d *DB) DeleteSession(ctx context.Context, token string) error {
163 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE token = ?`, token)
164 return err
165}
166
167// DeleteUserSessions removes all sessions for a given user.
168func (d *DB) DeleteUserSessions(ctx context.Context, userID int64) error {
169 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id = ?`, userID)
170 return err
171}
172
173// ExtendSession sets a new expiration time on the session.
174func (d *DB) ExtendSession(ctx context.Context, token string, expiresAt time.Time) error {
175 _, err := d.db.ExecContext(ctx,
176 `UPDATE sessions SET expires_at = ? WHERE token = ?`,
177 expiresAt.UTC().Format(time.RFC3339), token)
178 return err
179}
180
181type sessionScanner interface {
182 Scan(dest ...any) error
183}
184
185func scanSession(s sessionScanner) (*Session, error) {
186 var sess Session
187 var createdAt, expiresAt string
188 if err := s.Scan(&sess.ID, &sess.UserID, &sess.Token, &expiresAt, &createdAt); err != nil {
189 return nil, err
190 }
191 sess.ExpiresAt, _ = scanTime(expiresAt)
192 sess.CreatedAt, _ = scanTime(createdAt)
193 return &sess, nil
194}
195
196// ─────────────────────────── Images ───────────────────────────
197
198// CreateImage inserts a new image record.
199func (d *DB) CreateImage(ctx context.Context, img *Image) (*Image, error) {
200 t := now()
201 res, err := d.db.ExecContext(ctx,
202 `INSERT INTO images (filename, storage_path, thumbnail_path, uploaded_by,
203 file_size, width, height, mime_type, created_at, updated_at)
204 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
205 img.Filename, img.StoragePath, img.ThumbnailPath, img.UploadedBy,
206 img.FileSize, img.Width, img.Height, img.MimeType, t, t,
207 )
208 if err != nil {
209 return nil, fmt.Errorf("create image: %w", err)
210 }
211 id, err := res.LastInsertId()
212 if err != nil {
213 return nil, fmt.Errorf("create image last insert id: %w", err)
214 }
215 return d.GetImageByID(ctx, id)
216}
217
218// GetImageByID returns a single image by primary key.
219func (d *DB) GetImageByID(ctx context.Context, id int64) (*Image, error) {
220 row := d.db.QueryRowContext(ctx,
221 `SELECT id, filename, storage_path, thumbnail_path,
222 uploaded_by, file_size, width, height, mime_type,
223 created_at, updated_at
224 FROM images WHERE id = ?`, id)
225 img, err := scanImage(row)
226 if err != nil {
227 return nil, err
228 }
229
230 // Load tags for this image.
231 tags, err := d.GetImageTags(ctx, id)
232 if err != nil {
233 return nil, err
234 }
235 img.Tags = tags
236 return img, nil
237}
238
239// ListImages returns a paginated list of images, optionally filtered by tags
240// and/or uploader username. Multiple tags are AND-ed (image must have all
241// specified tags). Order is newest-first by created_at.
242func (d *DB) ListImages(ctx context.Context, filterTags []string, filterUploader string, limit, offset int) (*ImageList, error) {
243 // Count query
244 countQuery := `SELECT COUNT(DISTINCT i.id) FROM images i`
245 // Data query
246 dataQuery := `SELECT i.id, i.filename, i.storage_path, i.thumbnail_path,
247 i.uploaded_by, i.file_size, i.width, i.height, i.mime_type,
248 i.created_at, i.updated_at
249 FROM images i`
250
251 var args []any
252 var tagJoin string
253 var uploaderWhere string
254 var tagWhere string
255
256 if filterUploader != "" {
257 uploaderWhere = `i.uploaded_by = (SELECT id FROM users WHERE username = ?)`
258 args = append(args, filterUploader)
259 }
260
261 if len(filterTags) > 0 {
262 placeholders := make([]string, len(filterTags))
263 for j, t := range filterTags {
264 placeholders[j] = "?"
265 args = append(args, t)
266 }
267 tagJoin = ` JOIN image_tags it ON i.id = it.image_id`
268 tagWhere = `it.tag IN (` + strings.Join(placeholders, ",") + `)`
269 }
270
271 // Build WHERE clause.
272 var whereClause string
273 switch {
274 case tagWhere != "" && uploaderWhere != "":
275 whereClause = ` WHERE ` + tagWhere + ` AND ` + uploaderWhere
276 case tagWhere != "":
277 whereClause = ` WHERE ` + tagWhere
278 case uploaderWhere != "":
279 whereClause = ` WHERE ` + uploaderWhere
280 }
281
282 countQuery += tagJoin + whereClause
283 dataQuery += tagJoin + whereClause
284
285 if len(filterTags) > 0 {
286 dataQuery += ` GROUP BY i.id HAVING COUNT(DISTINCT it.tag) = ` + fmt.Sprintf("%d", len(filterTags))
287 }
288
289 dataQuery += ` ORDER BY i.created_at DESC LIMIT ? OFFSET ?`
290 argsData := make([]any, len(args))
291 copy(argsData, args)
292 argsData = append(argsData, limit, offset)
293 args = append(args, limit, offset)
294
295 // Execute count.
296 var total int
297 if err := d.db.QueryRowContext(ctx, countQuery, argsData[:len(argsData)-2]...).Scan(&total); err != nil {
298 return nil, fmt.Errorf("list images count: %w", err)
299 }
300
301 // Execute data.
302 rows, err := d.db.QueryContext(ctx, dataQuery, argsData...)
303 if err != nil {
304 return nil, fmt.Errorf("list images: %w", err)
305 }
306 defer rows.Close()
307
308 images := make([]Image, 0)
309 for rows.Next() {
310 img, err := scanImage(rows)
311 if err != nil {
312 return nil, err
313 }
314 images = append(images, *img)
315 }
316 if err := rows.Err(); err != nil {
317 return nil, err
318 }
319
320 return &ImageList{Images: images, Total: total}, nil
321}
322
323// DeleteImage removes an image record (tags cleaned via ON DELETE CASCADE).
324func (d *DB) DeleteImage(ctx context.Context, id int64) error {
325 // First decrement usage counts for associated tags.
326 tags, err := d.GetImageTags(ctx, id)
327 if err != nil {
328 return err
329 }
330 for _, tag := range tags {
331 if err := d.decrementTagUsage(ctx, tag); err != nil {
332 return err
333 }
334 }
335
336 res, err := d.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
337 if err != nil {
338 return fmt.Errorf("delete image: %w", err)
339 }
340 n, _ := res.RowsAffected()
341 if n == 0 {
342 return sql.ErrNoRows
343 }
344 return nil
345}
346
347type imageScanner interface {
348 Scan(dest ...any) error
349}
350
351func scanImage(s imageScanner) (*Image, error) {
352 var img Image
353 var thumbnailPath, createdAt, updatedAt sql.NullString
354 if err := s.Scan(
355 &img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
356 &img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
357 &createdAt, &updatedAt,
358 ); err != nil {
359 return nil, err
360 }
361 if thumbnailPath.Valid {
362 img.ThumbnailPath = &thumbnailPath.String
363 }
364 if createdAt.Valid {
365 img.CreatedAt, _ = scanTime(createdAt.String)
366 }
367 if updatedAt.Valid {
368 img.UpdatedAt, _ = scanTime(updatedAt.String)
369 }
370 return &img, nil
371}
372
373// ─────────────────────────── Tags ───────────────────────────
374
375// SetImageTags replaces all tags for an image and updates usage counts.
376func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
377 // Get existing tags to decrement counts.
378 oldTags, err := d.GetImageTags(ctx, imageID)
379 if err != nil {
380 return err
381 }
382
383 // Remove old associations.
384 if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
385 return fmt.Errorf("clear image tags: %w", err)
386 }
387
388 for _, t := range oldTags {
389 if err := d.decrementTagUsage(ctx, t); err != nil {
390 return err
391 }
392 }
393
394 // Insert new tags.
395 for _, t := range tags {
396 tag := normalizeTag(t)
397 if tag == "" {
398 continue
399 }
400 if _, err := d.db.ExecContext(ctx,
401 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
402 ); err != nil {
403 return fmt.Errorf("insert image tag: %w", err)
404 }
405 if err := d.incrementTagUsage(ctx, tag); err != nil {
406 return err
407 }
408 }
409
410 return nil
411}
412
413// GetImageTags returns the tag strings for a given image.
414func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
415 rows, err := d.db.QueryContext(ctx,
416 `SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
417 if err != nil {
418 return nil, fmt.Errorf("get image tags: %w", err)
419 }
420 defer rows.Close()
421
422 tags := make([]string, 0)
423 for rows.Next() {
424 var t string
425 if err := rows.Scan(&t); err != nil {
426 return nil, err
427 }
428 tags = append(tags, t)
429 }
430 return tags, rows.Err()
431}
432
433// GetImageTagsWithCounts returns tags and their usage counts for a given image,
434// ordered by most frequent first.
435func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
436 rows, err := d.db.QueryContext(ctx,
437 `SELECT t.tag, t.usage_count
438 FROM tags t
439 JOIN image_tags it ON it.tag = t.tag
440 WHERE it.image_id = ?
441 ORDER BY t.usage_count DESC, t.tag`, imageID)
442 if err != nil {
443 return nil, fmt.Errorf("get image tags with counts: %w", err)
444 }
445 defer rows.Close()
446
447 tags := make([]Tag, 0)
448 for rows.Next() {
449 var t Tag
450 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
451 return nil, err
452 }
453 tags = append(tags, t)
454 }
455 return tags, rows.Err()
456}
457
458// SuggestTags returns tags matching a prefix, ordered by usage count descending.
459func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
460 rows, err := d.db.QueryContext(ctx,
461 `SELECT tag, usage_count FROM tags WHERE tag LIKE ? ORDER BY usage_count DESC LIMIT ?`,
462 prefix+"%", limit)
463 if err != nil {
464 return nil, fmt.Errorf("suggest tags: %w", err)
465 }
466 defer rows.Close()
467
468 tags := make([]Tag, 0)
469 for rows.Next() {
470 var t Tag
471 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
472 return nil, err
473 }
474 tags = append(tags, t)
475 }
476 return tags, rows.Err()
477}
478
479func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
480 _, err := d.db.ExecContext(ctx,
481 `INSERT INTO tags (tag, usage_count) VALUES (?, 1)
482 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
483 tag)
484 return err
485}
486
487func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
488 _, err := d.db.ExecContext(ctx,
489 `UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
490 tag)
491 if err != nil {
492 return err
493 }
494 // Clean up tags with zero usage.
495 _, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
496 return err
497}
498
499// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
500func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
501 for _, t := range tags {
502 tag := normalizeTag(t)
503 if tag == "" {
504 continue
505 }
506 // Check if tag already exists for this image.
507 var count int
508 if err := d.db.QueryRowContext(ctx,
509 `SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
510 imageID, tag).Scan(&count); err != nil {
511 return fmt.Errorf("check existing tag: %w", err)
512 }
513 if count > 0 {
514 continue
515 }
516 if _, err := d.db.ExecContext(ctx,
517 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
518 ); err != nil {
519 return fmt.Errorf("insert image tag: %w", err)
520 }
521 if err := d.incrementTagUsage(ctx, tag); err != nil {
522 return err
523 }
524 }
525 return nil
526}
527
528// RemoveImageTags removes specific tags from an image and decrements usage counts.
529func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
530 for _, t := range tags {
531 tag := normalizeTag(t)
532 if tag == "" {
533 continue
534 }
535 res, err := d.db.ExecContext(ctx,
536 `DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
537 if err != nil {
538 return fmt.Errorf("remove image tag: %w", err)
539 }
540 n, _ := res.RowsAffected()
541 if n > 0 {
542 if err := d.decrementTagUsage(ctx, tag); err != nil {
543 return err
544 }
545 }
546 }
547 return nil
548}
549
550// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
551func normalizeTag(tag string) string {
552 return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
553}