Add playback repository cleanup queries

1 files changed, 55 insertions, 0 deletions

diff --git a/internal/repository/playback_progress.go b/internal/repository/playback_progress.go
index 046598a..a2f753e 100644
--- a/internal/repository/playback_progress.go
+++ b/internal/repository/playback_progress.go
@@ -4,6 +4,7 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
+	"strings"
 
 	"codeberg.org/snonux/player/internal/model"
 )
@@ -35,6 +36,27 @@ func (s *SQLite) GetProgress(ctx context.Context, userID, mediaID int64) (*model
 	return &p, nil
 }
 
+// DeleteProgress removes playback progress for a user and media.
+func (s *SQLite) DeleteProgress(ctx context.Context, userID, mediaID int64) error {
+	_, err := s.db.ExecContext(ctx, `DELETE FROM playback_progress WHERE user_id = ? AND media_id = ?`, userID, mediaID)
+	if err != nil {
+		return fmt.Errorf("delete progress: %w", err)
+	}
+	return nil
+}
+
+// MarkFinished marks playback progress finished for a user and media.
+func (s *SQLite) MarkFinished(ctx context.Context, userID, mediaID int64) error {
+	_, err := s.db.ExecContext(ctx,
+		`UPDATE playback_progress SET finished = 1, updated_at = CURRENT_TIMESTAMP WHERE user_id = ? AND media_id = ?`,
+		userID, mediaID,
+	)
+	if err != nil {
+		return fmt.Errorf("mark finished: %w", err)
+	}
+	return nil
+}
+
 // ListProgressByUser returns all progress records for a user.
 func (s *SQLite) ListProgressByUser(ctx context.Context, userID int64) ([]model.PlaybackProgress, error) {
 	rows, err := s.db.QueryContext(ctx,
@@ -53,3 +75,36 @@ func (s *SQLite) ListProgressByUser(ctx context.Context, userID int64) ([]model.
 	}
 	return pp, rows.Err()
 }
+
+// ListInProgressMedia returns unfinished, non-deleted media with saved progress for a user.
+func (s *SQLite) ListInProgressMedia(ctx context.Context, userID int64, filter MediaFilter) ([]model.Media, error) {
+	args := []any{userID}
+	conds := []string{`pp.user_id = ?`, `pp.finished = 0`, `media.deleted_at IS NULL`}
+	query := `SELECT media.id, media.set_id, media.rel_path, media.file_name, media.abs_path, media.type, media.duration, media.codec, media.resolution, media.bitrate, media.file_size_bytes, media.width, media.height, media.exif_camera, media.exif_lens, media.exif_date, media.exif_iso, media.exif_f_number, media.exif_exposure, media.exif_focal_length, media.thumbnail_path, media.play_count, media.deleted_at, media.created_at FROM playback_progress pp INNER JOIN media ON media.id = pp.media_id`
+
+	if len(filter.AllowedSetIDs) > 0 {
+		conds = append(conds, "media.set_id IN ("+placeholders(len(filter.AllowedSetIDs))+")")
+		for _, id := range filter.AllowedSetIDs {
+			args = append(args, id)
+		}
+	}
+
+	query += " WHERE " + strings.Join(conds, " AND ")
+	query += " ORDER BY pp.updated_at DESC"
+
+	rows, err := s.db.QueryContext(ctx, query, args...)
+	if err != nil {
+		return nil, fmt.Errorf("list in-progress media: %w", err)
+	}
+	defer rows.Close()
+
+	var media []model.Media
+	for rows.Next() {
+		m, err := scanMedia(rows)
+		if err != nil {
+			return nil, err
+		}
+		media = append(media, *m)
+	}
+	return media, rows.Err()
+}