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// Multiple tags are AND-ed (image must have all specified tags).
241// Order is newest-first by created_at.
242func (d *DB) ListImages(ctx context.Context, filterTags []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
253 if len(filterTags) > 0 {
254 placeholders := make([]string, len(filterTags))
255 for j, t := range filterTags {
256 placeholders[j] = "?"
257 args = append(args, t)
258 }
259 having := fmt.Sprintf("HAVING COUNT(DISTINCT it.tag) = %d", len(filterTags))
260
261 join := ` JOIN image_tags it ON i.id = it.image_id WHERE it.tag IN (` +
262 strings.Join(placeholders, ",") + `)`
263 countQuery += join
264 dataQuery += join + ` GROUP BY i.id ` + having
265 }
266
267 dataQuery += ` ORDER BY i.created_at DESC LIMIT ? OFFSET ?`
268 argsData := make([]any, len(args))
269 copy(argsData, args)
270 argsData = append(argsData, limit, offset)
271 args = append(args, limit, offset)
272
273 // Execute count.
274 var total int
275 if err := d.db.QueryRowContext(ctx, countQuery, argsData[:len(argsData)-2]...).Scan(&total); err != nil {
276 return nil, fmt.Errorf("list images count: %w", err)
277 }
278
279 // Execute data.
280 rows, err := d.db.QueryContext(ctx, dataQuery, argsData...)
281 if err != nil {
282 return nil, fmt.Errorf("list images: %w", err)
283 }
284 defer rows.Close()
285
286 images := make([]Image, 0)
287 for rows.Next() {
288 img, err := scanImage(rows)
289 if err != nil {
290 return nil, err
291 }
292 images = append(images, *img)
293 }
294 if err := rows.Err(); err != nil {
295 return nil, err
296 }
297
298 return &ImageList{Images: images, Total: total}, nil
299}
300
301// DeleteImage removes an image record (tags cleaned via ON DELETE CASCADE).
302func (d *DB) DeleteImage(ctx context.Context, id int64) error {
303 // First decrement usage counts for associated tags.
304 tags, err := d.GetImageTags(ctx, id)
305 if err != nil {
306 return err
307 }
308 for _, tag := range tags {
309 if err := d.decrementTagUsage(ctx, tag); err != nil {
310 return err
311 }
312 }
313
314 res, err := d.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
315 if err != nil {
316 return fmt.Errorf("delete image: %w", err)
317 }
318 n, _ := res.RowsAffected()
319 if n == 0 {
320 return sql.ErrNoRows
321 }
322 return nil
323}
324
325type imageScanner interface {
326 Scan(dest ...any) error
327}
328
329func scanImage(s imageScanner) (*Image, error) {
330 var img Image
331 var thumbnailPath, createdAt, updatedAt sql.NullString
332 if err := s.Scan(
333 &img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
334 &img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
335 &createdAt, &updatedAt,
336 ); err != nil {
337 return nil, err
338 }
339 if thumbnailPath.Valid {
340 img.ThumbnailPath = &thumbnailPath.String
341 }
342 if createdAt.Valid {
343 img.CreatedAt, _ = scanTime(createdAt.String)
344 }
345 if updatedAt.Valid {
346 img.UpdatedAt, _ = scanTime(updatedAt.String)
347 }
348 return &img, nil
349}
350
351// ─────────────────────────── Tags ───────────────────────────
352
353// SetImageTags replaces all tags for an image and updates usage counts.
354func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
355 // Get existing tags to decrement counts.
356 oldTags, err := d.GetImageTags(ctx, imageID)
357 if err != nil {
358 return err
359 }
360
361 // Remove old associations.
362 if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
363 return fmt.Errorf("clear image tags: %w", err)
364 }
365
366 for _, t := range oldTags {
367 if err := d.decrementTagUsage(ctx, t); err != nil {
368 return err
369 }
370 }
371
372 // Insert new tags.
373 for _, t := range tags {
374 tag := normalizeTag(t)
375 if tag == "" {
376 continue
377 }
378 if _, err := d.db.ExecContext(ctx,
379 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
380 ); err != nil {
381 return fmt.Errorf("insert image tag: %w", err)
382 }
383 if err := d.incrementTagUsage(ctx, tag); err != nil {
384 return err
385 }
386 }
387
388 return nil
389}
390
391// GetImageTags returns the tag strings for a given image.
392func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
393 rows, err := d.db.QueryContext(ctx,
394 `SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
395 if err != nil {
396 return nil, fmt.Errorf("get image tags: %w", err)
397 }
398 defer rows.Close()
399
400 tags := make([]string, 0)
401 for rows.Next() {
402 var t string
403 if err := rows.Scan(&t); err != nil {
404 return nil, err
405 }
406 tags = append(tags, t)
407 }
408 return tags, rows.Err()
409}
410
411// GetImageTagsWithCounts returns tags and their usage counts for a given image,
412// ordered by most frequent first.
413func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
414 rows, err := d.db.QueryContext(ctx,
415 `SELECT t.tag, t.usage_count
416 FROM tags t
417 JOIN image_tags it ON it.tag = t.tag
418 WHERE it.image_id = ?
419 ORDER BY t.usage_count DESC, t.tag`, imageID)
420 if err != nil {
421 return nil, fmt.Errorf("get image tags with counts: %w", err)
422 }
423 defer rows.Close()
424
425 tags := make([]Tag, 0)
426 for rows.Next() {
427 var t Tag
428 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
429 return nil, err
430 }
431 tags = append(tags, t)
432 }
433 return tags, rows.Err()
434}
435
436// SuggestTags returns tags matching a prefix, ordered by usage count descending.
437func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
438 rows, err := d.db.QueryContext(ctx,
439 `SELECT tag, usage_count FROM tags WHERE tag LIKE ? ORDER BY usage_count DESC LIMIT ?`,
440 prefix+"%", limit)
441 if err != nil {
442 return nil, fmt.Errorf("suggest tags: %w", err)
443 }
444 defer rows.Close()
445
446 tags := make([]Tag, 0)
447 for rows.Next() {
448 var t Tag
449 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
450 return nil, err
451 }
452 tags = append(tags, t)
453 }
454 return tags, rows.Err()
455}
456
457func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
458 _, err := d.db.ExecContext(ctx,
459 `INSERT INTO tags (tag, usage_count) VALUES (?, 1)
460 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
461 tag)
462 return err
463}
464
465func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
466 _, err := d.db.ExecContext(ctx,
467 `UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
468 tag)
469 if err != nil {
470 return err
471 }
472 // Clean up tags with zero usage.
473 _, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
474 return err
475}
476
477// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
478func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
479 for _, t := range tags {
480 tag := normalizeTag(t)
481 if tag == "" {
482 continue
483 }
484 // Check if tag already exists for this image.
485 var count int
486 if err := d.db.QueryRowContext(ctx,
487 `SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
488 imageID, tag).Scan(&count); err != nil {
489 return fmt.Errorf("check existing tag: %w", err)
490 }
491 if count > 0 {
492 continue
493 }
494 if _, err := d.db.ExecContext(ctx,
495 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
496 ); err != nil {
497 return fmt.Errorf("insert image tag: %w", err)
498 }
499 if err := d.incrementTagUsage(ctx, tag); err != nil {
500 return err
501 }
502 }
503 return nil
504}
505
506// RemoveImageTags removes specific tags from an image and decrements usage counts.
507func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
508 for _, t := range tags {
509 tag := normalizeTag(t)
510 if tag == "" {
511 continue
512 }
513 res, err := d.db.ExecContext(ctx,
514 `DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
515 if err != nil {
516 return fmt.Errorf("remove image tag: %w", err)
517 }
518 n, _ := res.RowsAffected()
519 if n > 0 {
520 if err := d.decrementTagUsage(ctx, tag); err != nil {
521 return err
522 }
523 }
524 }
525 return nil
526}
527
528// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
529func normalizeTag(tag string) string {
530 return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
531}