Spanglish/photo-api/internal/store/photos.go

package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

const maxAttempts = 5

type Photo struct {
	ID               string
	GalleryID        string
	Position         int
	OriginalKey      string
	OriginalFilename string
	ContentType      string
	SizeBytes        int64
	Width            int
	Height           int
	ThumbKey         string
	PreviewKey       string
	// PreviewSizeBytes is the size of the preview object, 0 when it has not
	// been measured yet (rows written before the column existed).
	PreviewSizeBytes int64
	TakenAt          time.Time
	Status           string
	Attempts         int
	NextAttemptAt    time.Time
	LastError        string
	CreatedAt        time.Time
	UpdatedAt        time.Time
	// Checksum is the sha256 of the original bytes, empty when the row has
	// not been hashed yet (uploaded before duplicate detection existed).
	Checksum string
}

const photoColumns = `id, gallery_id, position, original_key, original_filename, content_type,
	size_bytes, width, height, thumb_key, preview_key, preview_size_bytes, taken_at, status,
	attempts, next_attempt_at, last_error, created_at, updated_at, checksum`

func scanPhoto(s scanner) (Photo, error) {
	var v [20]any
	dest := make([]any, len(v))
	for i := range v {
		dest[i] = &v[i]
	}
	if err := s.Scan(dest...); err != nil {
		return Photo{}, err
	}
	return Photo{
		ID:               asString(v[0]),
		GalleryID:        asString(v[1]),
		Position:         int(asInt(v[2])),
		OriginalKey:      asString(v[3]),
		OriginalFilename: asString(v[4]),
		ContentType:      asString(v[5]),
		SizeBytes:        asInt(v[6]),
		Width:            int(asInt(v[7])),
		Height:           int(asInt(v[8])),
		ThumbKey:         asString(v[9]),
		PreviewKey:       asString(v[10]),
		PreviewSizeBytes: asInt(v[11]),
		TakenAt:          asTime(v[12]),
		Status:           asString(v[13]),
		Attempts:         int(asInt(v[14])),
		NextAttemptAt:    asTime(v[15]),
		LastError:        asString(v[16]),
		CreatedAt:        asTime(v[17]),
		UpdatedAt:        asTime(v[18]),
		Checksum:         asString(v[19]),
	}, nil
}

// InsertPhoto returns an error satisfying IsUniqueViolation when the gallery
// already holds a photo with the same checksum; callers treat that as a
// duplicate rather than a failure.
func (db *DB) InsertPhoto(ctx context.Context, p Photo) error {
	_, err := db.ExecContext(ctx, db.Rebind(`
		INSERT INTO photos_photos
			(id, gallery_id, position, original_key, original_filename, content_type, size_bytes,
			 status, attempts, next_attempt_at, created_at, updated_at, checksum)
		VALUES (?, ?, ?, ?, ?, ?, ?, 'queued', 0, ?, ?, ?, ?)`),
		p.ID, p.GalleryID, p.Position, p.OriginalKey, nullable(p.OriginalFilename), p.ContentType,
		p.SizeBytes, db.TimeArg(p.NextAttemptAt), db.TimeArg(p.CreatedAt), db.TimeArg(p.UpdatedAt),
		nullable(p.Checksum))
	return err
}

// FindPhotoByChecksum looks for an existing photo with the same content in one
// gallery — the duplicate check the upload path runs before storing anything.
// Duplicate scope is per gallery: the same image in another gallery keeps its
// own row and its own stored object.
func (db *DB) FindPhotoByChecksum(ctx context.Context, galleryID, checksum string) (Photo, error) {
	if checksum == "" {
		return Photo{}, ErrNotFound // never match the not-yet-hashed rows
	}
	row := db.QueryRowContext(ctx, db.Rebind(
		"SELECT "+photoColumns+" FROM photos_photos WHERE gallery_id = ? AND checksum = ?"),
		galleryID, checksum)
	p, err := scanPhoto(row)
	if errors.Is(err, sql.ErrNoRows) {
		return Photo{}, ErrNotFound
	}
	return p, err
}

// SetPhotoChecksum fills in the hash of an already-stored photo. Used by the
// backfill; returns a unique-violation error when the row turns out to
// duplicate one already hashed in the same gallery.
func (db *DB) SetPhotoChecksum(ctx context.Context, id, checksum string) error {
	res, err := db.ExecContext(ctx, db.Rebind(
		"UPDATE photos_photos SET checksum = ? WHERE id = ?"), checksum, id)
	if err != nil {
		return err
	}
	return errIfNoRows(res)
}

// PhotosMissingChecksum lists rows still lacking a hash (optionally of one
// gallery only), oldest first so backfill keeps the earliest upload of a
// duplicate pair as the one that gets the checksum.
func (db *DB) PhotosMissingChecksum(ctx context.Context, galleryID string) ([]Photo, error) {
	q := "SELECT " + photoColumns + " FROM photos_photos WHERE checksum IS NULL"
	var args []any
	if galleryID != "" {
		q += " AND gallery_id = ?"
		args = append(args, galleryID)
	}
	q += " ORDER BY gallery_id, position, created_at"
	rows, err := db.QueryContext(ctx, db.Rebind(q), args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	photos := []Photo{}
	for rows.Next() {
		p, err := scanPhoto(rows)
		if err != nil {
			return nil, err
		}
		photos = append(photos, p)
	}
	return photos, rows.Err()
}

func (db *DB) GetPhoto(ctx context.Context, id string) (Photo, error) {
	row := db.QueryRowContext(ctx,
		db.Rebind("SELECT "+photoColumns+" FROM photos_photos WHERE id = ?"), id)
	p, err := scanPhoto(row)
	if errors.Is(err, sql.ErrNoRows) {
		return Photo{}, ErrNotFound
	}
	return p, err
}

// ListPhotos returns a gallery's photos in position order. When readyOnly is
// set (public callers), photos still processing or failed are omitted.
func (db *DB) ListPhotos(ctx context.Context, galleryID string, readyOnly bool) ([]Photo, error) {
	q := "SELECT " + photoColumns + " FROM photos_photos WHERE gallery_id = ?"
	if readyOnly {
		q += " AND status = 'ready'"
	}
	q += " ORDER BY position, created_at"
	rows, err := db.QueryContext(ctx, db.Rebind(q), galleryID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	photos := []Photo{}
	for rows.Next() {
		p, err := scanPhoto(rows)
		if err != nil {
			return nil, err
		}
		photos = append(photos, p)
	}
	return photos, rows.Err()
}

func (db *DB) NextPosition(ctx context.Context, galleryID string) (int, error) {
	var v any
	err := db.QueryRowContext(ctx,
		db.Rebind("SELECT COALESCE(MAX(position), -1) + 1 FROM photos_photos WHERE gallery_id = ?"),
		galleryID).Scan(&v)
	return int(asInt(v)), err
}

// ReorderPhotos rewrites positions to match ids order, in one transaction.
// ids must be exactly the gallery's photo ids (validated by the handler).
func (db *DB) ReorderPhotos(ctx context.Context, galleryID string, ids []string) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	q := db.Rebind("UPDATE photos_photos SET position = ?, updated_at = ? WHERE id = ? AND gallery_id = ?")
	now := db.TimeArg(time.Now())
	for i, id := range ids {
		if _, err := tx.ExecContext(ctx, q, i, now, id, galleryID); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (db *DB) DeletePhoto(ctx context.Context, id string) error {
	res, err := db.ExecContext(ctx, db.Rebind("DELETE FROM photos_photos WHERE id = ?"), id)
	if err != nil {
		return err
	}
	return errIfNoRows(res)
}

// PhotoKeys returns every storage key of a gallery, for object cleanup
// before the rows cascade away.
func (db *DB) PhotoKeys(ctx context.Context, galleryID string) ([]string, error) {
	rows, err := db.QueryContext(ctx, db.Rebind(
		"SELECT original_key, thumb_key, preview_key FROM photos_photos WHERE gallery_id = ?"), galleryID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var keys []string
	for rows.Next() {
		var a, b, c any
		if err := rows.Scan(&a, &b, &c); err != nil {
			return nil, err
		}
		for _, k := range []string{asString(a), asString(b), asString(c)} {
			if k != "" {
				keys = append(keys, k)
			}
		}
	}
	return keys, rows.Err()
}

// PhotoObject is one stored object: a photo variant plus the content type it
// should be written with. Used by the storage sync, which treats the rows as
// the inventory of what exists.
type PhotoObject struct {
	PhotoID     string
	GalleryID   string
	Variant     string // original | thumb | preview
	Key         string
	ContentType string
}

// AllPhotoObjects lists every object of every photo (optionally of one
// gallery only), oldest first. Variant keys are empty until the worker has
// processed the photo; those are left out.
func (db *DB) AllPhotoObjects(ctx context.Context, galleryID string) ([]PhotoObject, error) {
	q := `SELECT id, gallery_id, content_type, original_key, thumb_key, preview_key
		FROM photos_photos`
	var args []any
	if galleryID != "" {
		q += " WHERE gallery_id = ?"
		args = append(args, galleryID)
	}
	q += " ORDER BY gallery_id, position, created_at"
	rows, err := db.QueryContext(ctx, db.Rebind(q), args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	objects := []PhotoObject{}
	for rows.Next() {
		var id, galID, ctype, orig, thumb, preview any
		if err := rows.Scan(&id, &galID, &ctype, &orig, &thumb, &preview); err != nil {
			return nil, err
		}
		variants := []struct {
			name, key, contentType string
		}{
			{"original", asString(orig), asString(ctype)},
			{"thumb", asString(thumb), "image/jpeg"},
			{"preview", asString(preview), "image/jpeg"},
		}
		for _, v := range variants {
			if v.key == "" {
				continue
			}
			objects = append(objects, PhotoObject{
				PhotoID:     asString(id),
				GalleryID:   asString(galID),
				Variant:     v.name,
				Key:         v.key,
				ContentType: v.contentType,
			})
		}
	}
	return objects, rows.Err()
}

// ClaimNextPhoto picks the oldest due queued/failed photo and marks it
// processing. Optimistic claim (RowsAffected check) works identically on
// Postgres and SQLite; returns ErrNotFound when the queue is empty.
func (db *DB) ClaimNextPhoto(ctx context.Context) (Photo, error) {
	now := time.Now()
	var idRaw any
	err := db.QueryRowContext(ctx, db.Rebind(`
		SELECT id FROM photos_photos
		WHERE status IN ('queued','failed') AND attempts < ? AND next_attempt_at <= ?
		ORDER BY created_at LIMIT 1`),
		maxAttempts, db.TimeArg(now)).Scan(&idRaw)
	if errors.Is(err, sql.ErrNoRows) {
		return Photo{}, ErrNotFound
	}
	if err != nil {
		return Photo{}, err
	}
	id := asString(idRaw)
	res, err := db.ExecContext(ctx, db.Rebind(`
		UPDATE photos_photos SET status = 'processing', attempts = attempts + 1, updated_at = ?
		WHERE id = ? AND status IN ('queued','failed')`),
		db.TimeArg(now), id)
	if err != nil {
		return Photo{}, err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return Photo{}, ErrNotFound // lost the race; caller loops again
	}
	return db.GetPhoto(ctx, id)
}

func (db *DB) MarkPhotoReady(ctx context.Context, id, thumbKey, previewKey string, previewSize int64, width, height int, takenAt time.Time) error {
	var takenArg any
	if !takenAt.IsZero() {
		takenArg = db.TimeArg(takenAt)
	}
	_, err := db.ExecContext(ctx, db.Rebind(`
		UPDATE photos_photos
		SET status = 'ready', thumb_key = ?, preview_key = ?, preview_size_bytes = ?, width = ?,
			height = ?, taken_at = ?, last_error = NULL, updated_at = ?
		WHERE id = ?`),
		thumbKey, previewKey, previewSize, width, height, takenArg, db.TimeArg(time.Now()), id)
	return err
}

// SetPreviewSize records the measured size of an already-generated preview.
// Used to fill in rows processed before the column existed; failure to write
// is not worth failing a read over, so callers may ignore the error.
func (db *DB) SetPreviewSize(ctx context.Context, id string, size int64) error {
	_, err := db.ExecContext(ctx, db.Rebind(
		"UPDATE photos_photos SET preview_size_bytes = ? WHERE id = ?"), size, id)
	return err
}

func (db *DB) MarkPhotoFailed(ctx context.Context, id string, attempts int, cause error) error {
	backoff := time.Duration(1<<min(attempts, 6)) * time.Minute
	msg := fmt.Sprintf("%v", cause)
	if len(msg) > 1000 {
		msg = msg[:1000]
	}
	_, err := db.ExecContext(ctx, db.Rebind(`
		UPDATE photos_photos SET status = 'failed', last_error = ?, next_attempt_at = ?, updated_at = ?
		WHERE id = ?`),
		msg, db.TimeArg(time.Now().Add(backoff)), db.TimeArg(time.Now()), id)
	return err
}

// RequeuePhoto resets a failed photo for a fresh round of attempts.
func (db *DB) RequeuePhoto(ctx context.Context, id string) error {
	res, err := db.ExecContext(ctx, db.Rebind(`
		UPDATE photos_photos SET status = 'queued', attempts = 0, next_attempt_at = ?, updated_at = ?
		WHERE id = ? AND status IN ('failed','queued')`),
		db.TimeArg(time.Now()), db.TimeArg(time.Now()), id)
	if err != nil {
		return err
	}
	return errIfNoRows(res)
}

// RecoverStuckProcessing requeues photos left in 'processing' by a crash.
func (db *DB) RecoverStuckProcessing(ctx context.Context, olderThan time.Duration) (int64, error) {
	res, err := db.ExecContext(ctx, db.Rebind(`
		UPDATE photos_photos SET status = 'queued', updated_at = ?
		WHERE status = 'processing' AND updated_at < ?`),
		db.TimeArg(time.Now()), db.TimeArg(time.Now().Add(-olderThan)))
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// GalleryCoverKeys returns thumb keys for cover selection: the explicit
// cover photo's thumb when set and ready, else the first ready photo's.
func (db *DB) GalleryCoverKey(ctx context.Context, g Gallery) (photoID, thumbKey string) {
	if g.CoverPhotoID != "" {
		if p, err := db.GetPhoto(ctx, g.CoverPhotoID); err == nil && p.Status == "ready" && p.GalleryID == g.ID {
			return p.ID, p.ThumbKey
		}
	}
	row := db.QueryRowContext(ctx, db.Rebind(`
		SELECT id, thumb_key FROM photos_photos
		WHERE gallery_id = ? AND status = 'ready'
		ORDER BY position, created_at LIMIT 1`), g.ID)
	var idRaw, keyRaw any
	if err := row.Scan(&idRaw, &keyRaw); err != nil {
		return "", ""
	}
	return asString(idRaw), asString(keyRaw)
}