internal/db/queries.go (view raw)
1package db
2
3import (
4 "context"
5 "database/sql"
6 "encoding/json"
7 "fmt"
8 "strings"
9 "time"
10)
11
12// ─────────────────────────── Users ───────────────────────────
13
14// CreateUser inserts a new user and returns it with the assigned ID.
15func (d *DB) CreateUser(ctx context.Context, username, passwordHash string, isAdmin bool) (*User, error) {
16 admin := 0
17 if isAdmin {
18 admin = 1
19 }
20 t := now()
21 res, err := d.db.ExecContext(ctx,
22 `INSERT INTO users (username, password_hash, is_admin, created_at, updated_at)
23 VALUES (?, ?, ?, ?, ?)`,
24 username, passwordHash, admin, t, t,
25 )
26 if err != nil {
27 return nil, fmt.Errorf("create user: %w", err)
28 }
29 id, err := res.LastInsertId()
30 if err != nil {
31 return nil, fmt.Errorf("create user last insert id: %w", err)
32 }
33 return d.GetUserByID(ctx, id)
34}
35
36// GetUserByID returns a single user by primary key.
37func (d *DB) GetUserByID(ctx context.Context, id int64) (*User, error) {
38 row := d.db.QueryRowContext(ctx,
39 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
40 FROM users WHERE id = ?`, id)
41 return scanUser(row)
42}
43
44// SetUserAvatar updates the avatar_path for a user. Pass nil to clear.
45func (d *DB) SetUserAvatar(ctx context.Context, id int64, avatarPath *string) error {
46 _, err := d.db.ExecContext(ctx,
47 `UPDATE users SET avatar_path = ?, updated_at = ? WHERE id = ?`,
48 avatarPath, now(), id)
49 return err
50}
51
52// GetUserByUsername returns a single user by username.
53func (d *DB) GetUserByUsername(ctx context.Context, username string) (*User, error) {
54 row := d.db.QueryRowContext(ctx,
55 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
56 FROM users WHERE username = ?`, username)
57 return scanUser(row)
58}
59
60// ListUsers returns all users ordered by username.
61func (d *DB) ListUsers(ctx context.Context) ([]User, error) {
62 rows, err := d.db.QueryContext(ctx,
63 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
64 FROM users ORDER BY username`)
65 if err != nil {
66 return nil, fmt.Errorf("list users: %w", err)
67 }
68 defer rows.Close()
69
70 users := make([]User, 0)
71 for rows.Next() {
72 u, err := scanUser(rows)
73 if err != nil {
74 return nil, err
75 }
76 users = append(users, *u)
77 }
78 return users, rows.Err()
79}
80
81// DeleteUser removes a user by ID. Returns sql.ErrNoRows if not found.
82func (d *DB) DeleteUser(ctx context.Context, id int64) error {
83 res, err := d.db.ExecContext(ctx, `DELETE FROM users WHERE id = ?`, id)
84 if err != nil {
85 return fmt.Errorf("delete user: %w", err)
86 }
87 n, _ := res.RowsAffected()
88 if n == 0 {
89 return sql.ErrNoRows
90 }
91 return nil
92}
93
94// UpdateUserPassword sets a new password hash for the user.
95func (d *DB) UpdateUserPassword(ctx context.Context, id int64, passwordHash string) error {
96 res, err := d.db.ExecContext(ctx,
97 `UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?`,
98 passwordHash, now(), id)
99 if err != nil {
100 return fmt.Errorf("update password: %w", err)
101 }
102 n, _ := res.RowsAffected()
103 if n == 0 {
104 return sql.ErrNoRows
105 }
106 return nil
107}
108
109type userScanner interface {
110 Scan(dest ...any) error
111}
112
113func scanUser(s userScanner) (*User, error) {
114 var u User
115 var isAdmin int
116 var createdAt, updatedAt string
117 var avatarPath *string
118 if err := s.Scan(&u.ID, &u.Username, &u.PasswordHash, &isAdmin, &createdAt, &updatedAt, &avatarPath); err != nil {
119 return nil, err
120 }
121 u.IsAdmin = isAdmin == 1
122 u.CreatedAt, _ = scanTime(createdAt)
123 u.UpdatedAt, _ = scanTime(updatedAt)
124 u.AvatarPath = avatarPath
125 return &u, nil
126}
127
128// ─────────────────────────── Sessions ───────────────────────────
129
130// CreateSession inserts a new session and returns it.
131func (d *DB) CreateSession(ctx context.Context, userID int64, token string, expiresAt time.Time) (*Session, error) {
132 t := now()
133 res, err := d.db.ExecContext(ctx,
134 `INSERT INTO sessions (user_id, token, expires_at, created_at)
135 VALUES (?, ?, ?, ?)`,
136 userID, token, expiresAt.UTC().Format(time.RFC3339), t,
137 )
138 if err != nil {
139 return nil, fmt.Errorf("create session: %w", err)
140 }
141 id, err := res.LastInsertId()
142 if err != nil {
143 return nil, fmt.Errorf("create session last insert id: %w", err)
144 }
145
146 return &Session{
147 ID: id,
148 UserID: userID,
149 Token: token,
150 ExpiresAt: expiresAt,
151 }, nil
152}
153
154// GetSessionByToken retrieves a session by its token.
155func (d *DB) GetSessionByToken(ctx context.Context, token string) (*Session, error) {
156 row := d.db.QueryRowContext(ctx,
157 `SELECT id, user_id, token, expires_at, created_at
158 FROM sessions WHERE token = ?`, token)
159 return scanSession(row)
160}
161
162// DeleteSession removes a session by token.
163func (d *DB) DeleteSession(ctx context.Context, token string) error {
164 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE token = ?`, token)
165 return err
166}
167
168// DeleteUserSessions removes all sessions for a given user.
169func (d *DB) DeleteUserSessions(ctx context.Context, userID int64) error {
170 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id = ?`, userID)
171 return err
172}
173
174// ExtendSession sets a new expiration time on the session.
175func (d *DB) ExtendSession(ctx context.Context, token string, expiresAt time.Time) error {
176 _, err := d.db.ExecContext(ctx,
177 `UPDATE sessions SET expires_at = ? WHERE token = ?`,
178 expiresAt.UTC().Format(time.RFC3339), token)
179 return err
180}
181
182type sessionScanner interface {
183 Scan(dest ...any) error
184}
185
186func scanSession(s sessionScanner) (*Session, error) {
187 var sess Session
188 var createdAt, expiresAt string
189 if err := s.Scan(&sess.ID, &sess.UserID, &sess.Token, &expiresAt, &createdAt); err != nil {
190 return nil, err
191 }
192 sess.ExpiresAt, _ = scanTime(expiresAt)
193 sess.CreatedAt, _ = scanTime(createdAt)
194 return &sess, nil
195}
196
197// ─────────────────────────── Images ───────────────────────────
198
199// CreateImage inserts a new image record.
200func (d *DB) CreateImage(ctx context.Context, img *Image) (*Image, error) {
201 t := now()
202
203 innateTagsJSON := marshalStrings(img.InnateTags)
204
205 res, err := d.db.ExecContext(ctx,
206 `INSERT INTO images (filename, storage_path, thumbnail_path, uploaded_by,
207 file_size, width, height, mime_type, innate_tags, duration, gem_count, created_at, updated_at)
208 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
209 img.Filename, img.StoragePath, img.ThumbnailPath, img.UploadedBy,
210 img.FileSize, img.Width, img.Height, img.MimeType, innateTagsJSON, img.Duration, 0, t, t,
211 )
212 if err != nil {
213 return nil, fmt.Errorf("create image: %w", err)
214 }
215 id, err := res.LastInsertId()
216 if err != nil {
217 return nil, fmt.Errorf("create image last insert id: %w", err)
218 }
219 return d.GetImageByID(ctx, id)
220}
221
222// GetImageByID returns a single image by primary key.
223func (d *DB) GetImageByID(ctx context.Context, id int64) (*Image, error) {
224 row := d.db.QueryRowContext(ctx,
225 `SELECT id, filename, storage_path, thumbnail_path,
226 uploaded_by, file_size, width, height, mime_type,
227 innate_tags, duration, gem_count, created_at, updated_at
228 FROM images WHERE id = ?`, id)
229 img, err := scanImage(row)
230 if err != nil {
231 return nil, err
232 }
233
234 // Load tags for this image.
235 tags, err := d.GetImageTags(ctx, id)
236 if err != nil {
237 return nil, err
238 }
239 img.Tags = tags
240 return img, nil
241}
242
243// ListImages returns a paginated list of images, optionally filtered by tags
244// and/or uploader username. Multiple tags are AND-ed (image must have all
245// specified tags). Tag filtering also matches innate tags stored in the
246// innate_tags JSON column. If sortGems is true, results are ordered by
247// gem_count DESC then created_at DESC. If filterGems is true, only images
248// with gem_count > 0 are returned.
249func (d *DB) ListImages(ctx context.Context, filterTags []string, filterUploader string, limit, offset int, sortGems, filterGems bool) (*ImageList, error) {
250 // Count query
251 countQuery := `SELECT COUNT(DISTINCT i.id) FROM images i`
252 // Data query
253 dataQuery := `SELECT i.id, i.filename, i.storage_path, i.thumbnail_path,
254 i.uploaded_by, i.file_size, i.width, i.height, i.mime_type,
255 i.innate_tags, i.duration, i.gem_count, i.created_at, i.updated_at
256 FROM images i`
257
258 var args []any
259 var tagJoin string
260 var conditions []string
261
262 if len(filterTags) > 0 {
263 placeholders := make([]string, len(filterTags))
264 for j, t := range filterTags {
265 placeholders[j] = "?"
266 args = append(args, t)
267 }
268 // Union image_tags and innate_tags so both are searchable via tag filter.
269 tagJoin = ` JOIN (
270 SELECT image_id, tag FROM image_tags
271 UNION ALL
272 SELECT i.id, j.value FROM images i, json_each(COALESCE(i.innate_tags, '[]')) j
273 ) all_tags ON i.id = all_tags.image_id`
274 conditions = append(conditions, `all_tags.tag IN (`+strings.Join(placeholders, ",")+`)`)
275 }
276
277 if filterUploader != "" {
278 conditions = append(conditions, `i.uploaded_by = (SELECT id FROM users WHERE username = ?)`)
279 args = append(args, filterUploader)
280 }
281
282 if filterGems {
283 conditions = append(conditions, `i.gem_count > 0`)
284 }
285
286 // Build WHERE clause.
287 whereClause := strings.Join(conditions, " AND ")
288 if whereClause != "" {
289 whereClause = ` WHERE ` + whereClause
290 }
291
292 countQuery += tagJoin + whereClause
293 dataQuery += tagJoin + whereClause
294
295 if len(filterTags) > 0 {
296 dataQuery += ` GROUP BY i.id HAVING COUNT(DISTINCT all_tags.tag) = ` + fmt.Sprintf("%d", len(filterTags))
297 }
298
299 // ORDER BY.
300 if sortGems {
301 dataQuery += ` ORDER BY i.gem_count DESC, i.created_at DESC`
302 } else {
303 dataQuery += ` ORDER BY i.created_at DESC`
304 }
305
306 dataQuery += ` LIMIT ? OFFSET ?`
307 argsData := make([]any, len(args))
308 copy(argsData, args)
309 argsData = append(argsData, limit, offset)
310 args = append(args, limit, offset)
311
312 // Execute count.
313 var total int
314 if err := d.db.QueryRowContext(ctx, countQuery, argsData[:len(argsData)-2]...).Scan(&total); err != nil {
315 return nil, fmt.Errorf("list images count: %w", err)
316 }
317
318 // Execute data.
319 rows, err := d.db.QueryContext(ctx, dataQuery, argsData...)
320 if err != nil {
321 return nil, fmt.Errorf("list images: %w", err)
322 }
323 defer rows.Close()
324
325 images := make([]Image, 0)
326 for rows.Next() {
327 img, err := scanImage(rows)
328 if err != nil {
329 return nil, err
330 }
331 images = append(images, *img)
332 }
333 if err := rows.Err(); err != nil {
334 return nil, err
335 }
336
337 return &ImageList{Images: images, Total: total}, nil
338}
339
340// DeleteImage removes an image record (tags cleaned via ON DELETE CASCADE).
341func (d *DB) DeleteImage(ctx context.Context, id int64) error {
342 // First decrement usage counts for associated tags.
343 tags, err := d.GetImageTags(ctx, id)
344 if err != nil {
345 return err
346 }
347 for _, tag := range tags {
348 if err := d.decrementTagUsage(ctx, tag); err != nil {
349 return err
350 }
351 }
352
353 res, err := d.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
354 if err != nil {
355 return fmt.Errorf("delete image: %w", err)
356 }
357 n, _ := res.RowsAffected()
358 if n == 0 {
359 return sql.ErrNoRows
360 }
361 return nil
362}
363
364// ListAllImages returns all images ordered by id ascending, without tag/uploader
365// filtering. Used by CLI commands that need to iterate over every record.
366func (d *DB) ListAllImages(ctx context.Context) ([]Image, error) {
367 rows, err := d.db.QueryContext(ctx,
368 `SELECT id, filename, storage_path, thumbnail_path,
369 uploaded_by, file_size, width, height, mime_type,
370 innate_tags, duration, gem_count, created_at, updated_at
371 FROM images ORDER BY id`)
372 if err != nil {
373 return nil, fmt.Errorf("list all images: %w", err)
374 }
375 defer rows.Close()
376
377 images := make([]Image, 0)
378 for rows.Next() {
379 img, err := scanImage(rows)
380 if err != nil {
381 return nil, err
382 }
383 images = append(images, *img)
384 }
385 return images, rows.Err()
386}
387
388// UpdateImageInnateTags sets the innate_tags column for a single image.
389func (d *DB) UpdateImageInnateTags(ctx context.Context, id int64, tags []string) error {
390 _, err := d.db.ExecContext(ctx,
391 `UPDATE images SET innate_tags = ?, updated_at = ? WHERE id = ?`,
392 marshalStrings(tags), now(), id)
393 return err
394}
395
396// UpdateImageVideoMetadata updates the width, height, and duration columns for
397// a single image. Used by CLI repair commands to backfill metadata for videos
398// that were uploaded before video dimension/duration probing was added.
399func (d *DB) UpdateImageVideoMetadata(ctx context.Context, id int64, width, height int, duration float64) error {
400 _, err := d.db.ExecContext(ctx,
401 `UPDATE images SET width = ?, height = ?, duration = ?, updated_at = ? WHERE id = ?`,
402 width, height, duration, now(), id)
403 return err
404}
405
406// ─────────────────────────── Gems ───────────────────────────
407
408// ToggleGem adds or removes a gem from an image for a user.
409// Returns the new gem status (true=gemmed) and updated gem count.
410func (d *DB) ToggleGem(ctx context.Context, imageID, userID int64) (bool, int, error) {
411 var count int
412 err := d.db.QueryRowContext(ctx,
413 `SELECT COUNT(*) FROM image_gems WHERE image_id = ? AND user_id = ?`,
414 imageID, userID).Scan(&count)
415 if err != nil {
416 return false, 0, fmt.Errorf("check gem: %w", err)
417 }
418
419 if count > 0 {
420 _, err = d.db.ExecContext(ctx,
421 `DELETE FROM image_gems WHERE image_id = ? AND user_id = ?`,
422 imageID, userID)
423 if err != nil {
424 return false, 0, fmt.Errorf("remove gem: %w", err)
425 }
426 _, err = d.db.ExecContext(ctx,
427 `UPDATE images SET gem_count = MAX(0, gem_count - 1), updated_at = ? WHERE id = ?`,
428 now(), imageID)
429 if err != nil {
430 return false, 0, fmt.Errorf("decrement gem count: %w", err)
431 }
432 } else {
433 _, err = d.db.ExecContext(ctx,
434 `INSERT INTO image_gems (image_id, user_id, created_at) VALUES (?, ?, ?)`,
435 imageID, userID, now())
436 if err != nil {
437 return false, 0, fmt.Errorf("add gem: %w", err)
438 }
439 _, err = d.db.ExecContext(ctx,
440 `UPDATE images SET gem_count = gem_count + 1, updated_at = ? WHERE id = ?`,
441 now(), imageID)
442 if err != nil {
443 return false, 0, fmt.Errorf("increment gem count: %w", err)
444 }
445 }
446
447 var gemCount int
448 d.db.QueryRowContext(ctx, `SELECT gem_count FROM images WHERE id = ?`, imageID).Scan(&gemCount)
449 return count == 0, gemCount, nil
450}
451
452// GetUserGemStatus returns true if the user has gemmed the specified image.
453func (d *DB) GetUserGemStatus(ctx context.Context, imageID, userID int64) (bool, error) {
454 var count int
455 err := d.db.QueryRowContext(ctx,
456 `SELECT COUNT(*) FROM image_gems WHERE image_id = ? AND user_id = ?`,
457 imageID, userID).Scan(&count)
458 return count > 0, err
459}
460
461type imageScanner interface {
462 Scan(dest ...any) error
463}
464
465func scanImage(s imageScanner) (*Image, error) {
466 var img Image
467 var thumbnailPath, innateTags, createdAt, updatedAt sql.NullString
468 var duration sql.NullFloat64
469 if err := s.Scan(
470 &img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
471 &img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
472 &innateTags, &duration, &img.GemCount, &createdAt, &updatedAt,
473 ); err != nil {
474 return nil, err
475 }
476 if thumbnailPath.Valid {
477 img.ThumbnailPath = &thumbnailPath.String
478 }
479 if innateTags.Valid {
480 img.InnateTags = unmarshalStrings(innateTags.String)
481 }
482 if duration.Valid {
483 img.Duration = duration.Float64
484 }
485 if createdAt.Valid {
486 img.CreatedAt, _ = scanTime(createdAt.String)
487 }
488 if updatedAt.Valid {
489 img.UpdatedAt, _ = scanTime(updatedAt.String)
490 }
491 return &img, nil
492}
493
494// ─────────────────────────── Tags ───────────────────────────
495
496// SetImageTags replaces all tags for an image and updates usage counts.
497func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
498 // Get existing tags to decrement counts.
499 oldTags, err := d.GetImageTags(ctx, imageID)
500 if err != nil {
501 return err
502 }
503
504 // Remove old associations.
505 if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
506 return fmt.Errorf("clear image tags: %w", err)
507 }
508
509 for _, t := range oldTags {
510 if err := d.decrementTagUsage(ctx, t); err != nil {
511 return err
512 }
513 }
514
515 // Insert new tags.
516 for _, t := range tags {
517 tag := normalizeTag(t)
518 if tag == "" {
519 continue
520 }
521 if _, err := d.db.ExecContext(ctx,
522 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
523 ); err != nil {
524 return fmt.Errorf("insert image tag: %w", err)
525 }
526 if err := d.incrementTagUsage(ctx, tag); err != nil {
527 return err
528 }
529 }
530
531 return nil
532}
533
534// GetImageTags returns the tag strings for a given image.
535func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
536 rows, err := d.db.QueryContext(ctx,
537 `SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
538 if err != nil {
539 return nil, fmt.Errorf("get image tags: %w", err)
540 }
541 defer rows.Close()
542
543 tags := make([]string, 0)
544 for rows.Next() {
545 var t string
546 if err := rows.Scan(&t); err != nil {
547 return nil, err
548 }
549 tags = append(tags, t)
550 }
551 return tags, rows.Err()
552}
553
554// GetImageTagsWithCounts returns tags and their usage counts for a given image,
555// ordered by most frequent first.
556func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
557 rows, err := d.db.QueryContext(ctx,
558 `SELECT t.tag, t.usage_count
559 FROM tags t
560 JOIN image_tags it ON it.tag = t.tag
561 WHERE it.image_id = ?
562 ORDER BY t.usage_count DESC, t.tag`, imageID)
563 if err != nil {
564 return nil, fmt.Errorf("get image tags with counts: %w", err)
565 }
566 defer rows.Close()
567
568 tags := make([]Tag, 0)
569 for rows.Next() {
570 var t Tag
571 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
572 return nil, err
573 }
574 tags = append(tags, t)
575 }
576 return tags, rows.Err()
577}
578
579// SuggestTags returns tags matching a prefix, ordered by usage count descending.
580// SuggestUploaders returns usernames matching a prefix, ordered by upload count descending.
581func (d *DB) SuggestUploaders(ctx context.Context, prefix string, limit int) ([]Tag, error) {
582 rows, err := d.db.QueryContext(ctx,
583 `SELECT u.username, COUNT(i.id) AS cnt
584 FROM users u
585 LEFT JOIN images i ON i.uploaded_by = u.id
586 WHERE u.username LIKE ?
587 GROUP BY u.id, u.username
588 ORDER BY cnt DESC, u.username
589 LIMIT ?`,
590 prefix+"%", limit)
591 if err != nil {
592 return nil, fmt.Errorf("suggest uploaders: %w", err)
593 }
594 defer rows.Close()
595
596 users := make([]Tag, 0)
597 for rows.Next() {
598 var t Tag
599 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
600 return nil, err
601 }
602 users = append(users, t)
603 }
604 return users, rows.Err()
605}
606
607func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
608 rows, err := d.db.QueryContext(ctx,
609 `SELECT tag, usage_count FROM (
610 SELECT tag, usage_count FROM tags WHERE tag LIKE ?
611 UNION ALL
612 SELECT j.value AS tag, COUNT(*) AS usage_count
613 FROM images i, json_each(COALESCE(i.innate_tags, '[]')) j
614 WHERE j.value LIKE ?
615 GROUP BY j.value
616 )
617 ORDER BY usage_count DESC, tag
618 LIMIT ?`,
619 prefix+"%", prefix+"%", limit)
620 if err != nil {
621 return nil, fmt.Errorf("suggest tags: %w", err)
622 }
623 defer rows.Close()
624
625 tags := make([]Tag, 0)
626 for rows.Next() {
627 var t Tag
628 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
629 return nil, err
630 }
631 tags = append(tags, t)
632 }
633 return tags, rows.Err()
634}
635
636func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
637 _, err := d.db.ExecContext(ctx,
638 `INSERT INTO tags (tag, usage_count) VALUES (?, 1)
639 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
640 tag)
641 return err
642}
643
644func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
645 _, err := d.db.ExecContext(ctx,
646 `UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
647 tag)
648 if err != nil {
649 return err
650 }
651 // Clean up tags with zero usage.
652 _, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
653 return err
654}
655
656// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
657func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
658 for _, t := range tags {
659 tag := normalizeTag(t)
660 if tag == "" {
661 continue
662 }
663 // Check if tag already exists for this image.
664 var count int
665 if err := d.db.QueryRowContext(ctx,
666 `SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
667 imageID, tag).Scan(&count); err != nil {
668 return fmt.Errorf("check existing tag: %w", err)
669 }
670 if count > 0 {
671 continue
672 }
673 if _, err := d.db.ExecContext(ctx,
674 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
675 ); err != nil {
676 return fmt.Errorf("insert image tag: %w", err)
677 }
678 if err := d.incrementTagUsage(ctx, tag); err != nil {
679 return err
680 }
681 }
682 return nil
683}
684
685// RemoveImageTags removes specific tags from an image and decrements usage counts.
686func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
687 for _, t := range tags {
688 tag := normalizeTag(t)
689 if tag == "" {
690 continue
691 }
692 res, err := d.db.ExecContext(ctx,
693 `DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
694 if err != nil {
695 return fmt.Errorf("remove image tag: %w", err)
696 }
697 n, _ := res.RowsAffected()
698 if n > 0 {
699 if err := d.decrementTagUsage(ctx, tag); err != nil {
700 return err
701 }
702 }
703 }
704 return nil
705}
706
707// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
708func normalizeTag(tag string) string {
709 return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
710}
711
712// marshalStrings marshals a string slice to a JSON array for storage in TEXT columns.
713func marshalStrings(s []string) string {
714 if len(s) == 0 {
715 return "[]"
716 }
717 b, _ := json.Marshal(s)
718 return string(b)
719}
720
721// unmarshalStrings unmarshals a JSON array from a TEXT column back to a string slice.
722func unmarshalStrings(s string) []string {
723 if s == "" {
724 return nil
725 }
726 var result []string
727 json.Unmarshal([]byte(s), &result)
728 return result
729}