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