package storage import ( "context" "database/sql" "errors" "fmt" "time" "github.com/uptrace/bun" "git.kmalloc.org/squid/sonda/internal/catalog" "git.kmalloc.org/squid/sonda/internal/playlists" ) type playlistRow struct { bun.BaseModel `bun:"table:playlists"` ID int64 `bun:"id,pk,autoincrement"` UserID int64 `bun:"user_id,notnull"` Name string `bun:"name,notnull"` Description string `bun:"description,notnull"` CreatedAt int64 `bun:"created_at,notnull"` UpdatedAt int64 `bun:"updated_at,notnull"` } type playlistItemRow struct { bun.BaseModel `bun:"table:playlist_items"` ID int64 `bun:"id,pk,autoincrement"` PlaylistID int64 `bun:"playlist_id,notnull"` TrackID int64 `bun:"track_id,notnull"` Position int `bun:"position,notnull"` AddedAt int64 `bun:"added_at,notnull"` } // PlaylistRepo stores playlists. It implements playlists.Store. type PlaylistRepo struct { db *bun.DB cat *CatalogRepo // reads the tracks of the items } // NewPlaylistRepo builds a PlaylistRepo. func NewPlaylistRepo(db *bun.DB) *PlaylistRepo { return &PlaylistRepo{db: db, cat: NewCatalogRepo(db)} } // summarySelect reads playlists with the number of their items and how long they are, over all // the items, available or not. const summarySelect = `SELECT p.id, p.name, p.description, p.created_at, p.updated_at, COUNT(i.id), COALESCE(SUM(t.duration_ms), 0) FROM playlists p LEFT JOIN playlist_items i ON i.playlist_id = p.id LEFT JOIN tracks t ON t.id = i.track_id` func scanSummary(rows interface{ Scan(...any) error }) (playlists.Playlist, error) { var ( p playlists.Playlist created, updated int64 count, durationSum int64 ) if err := rows.Scan(&p.ID, &p.Name, &p.Description, &created, &updated, &count, &durationSum); err != nil { return playlists.Playlist{}, err } p.CreatedAt, p.UpdatedAt = time.Unix(created, 0), time.Unix(updated, 0) p.TrackCount, p.DurationMs = int(count), durationSum return p, nil } // attachCovers gives each playlist the albums that stand for it: the first albums that have a cover, in the order they // appear in the playlist, each once, up to playlists.MaxCoverAlbums. Playlists with none get an empty list. func attachCovers(ctx context.Context, db bun.IDB, ps []playlists.Playlist) error { if len(ps) == 0 { return nil } index := map[int64]int{} ids := make([]int64, len(ps)) for i := range ps { ps[i].AlbumIDs = []int64{} index[ps[i].ID] = i ids[i] = ps[i].ID } rows, err := db.QueryContext(ctx, `SELECT playlist_id, album_id FROM ( SELECT g.playlist_id, g.album_id, ROW_NUMBER() OVER (PARTITION BY g.playlist_id ORDER BY g.first, g.album_id) AS n FROM ( SELECT i.playlist_id, t.album_id, MIN(i.position) AS first FROM playlist_items i JOIN tracks t ON t.id = i.track_id JOIN images im ON im.kind = 'album' AND im.owner_id = t.album_id AND im.status = 'ok' WHERE i.playlist_id IN (?) AND t.album_id IS NOT NULL GROUP BY i.playlist_id, t.album_id) g) r WHERE n <= ? ORDER BY playlist_id, n`, bun.In(ids), playlists.MaxCoverAlbums) if err != nil { return fmt.Errorf("read the albums of playlists: %w", err) } defer rows.Close() for rows.Next() { var playlist, album int64 if err := rows.Scan(&playlist, &album); err != nil { return err } ps[index[playlist]].AlbumIDs = append(ps[index[playlist]].AlbumIDs, album) } return rows.Err() } // summary reads one playlist of a user, or playlists.ErrNotFound. func summary(ctx context.Context, db bun.IDB, userID, id int64) (playlists.Playlist, error) { p, err := scanSummary(db.QueryRowContext(ctx, summarySelect+" WHERE p.id = ? AND p.user_id = ? GROUP BY p.id", id, userID)) if errors.Is(err, sql.ErrNoRows) { return playlists.Playlist{}, playlists.ErrNotFound } if err != nil { return playlists.Playlist{}, fmt.Errorf("read playlist: %w", err) } one := []playlists.Playlist{p} if err := attachCovers(ctx, db, one); err != nil { return playlists.Playlist{}, err } return one[0], nil } // touch sets the updated time of a playlist of the user and so takes its row lock, which is what // serialises two changes of the same playlist on PostgreSQL. It gives ErrNotFound when the // playlist is not the user's. func touch(ctx context.Context, tx bun.Tx, userID, id int64, now time.Time) error { res, err := tx.ExecContext(ctx, "UPDATE playlists SET updated_at = ? WHERE id = ? AND user_id = ?", now.Unix(), id, userID) if err != nil { return fmt.Errorf("lock playlist: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return playlists.ErrNotFound } return nil } func countItems(ctx context.Context, tx bun.Tx, id int64) (int, error) { var n int if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlist_items WHERE playlist_id = ?", id).Scan(&n); err != nil { return 0, fmt.Errorf("count items: %w", err) } return n, nil } // Create makes an empty playlist. func (r *PlaylistRepo) Create(ctx context.Context, userID int64, name, description string, now time.Time) (playlists.Playlist, error) { var out playlists.Playlist err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { // The row of the user is the lock that makes the count below hold for two requests at once. res, err := tx.ExecContext(ctx, "UPDATE users SET is_admin = is_admin WHERE id = ?", userID) if err != nil { return fmt.Errorf("lock user: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return playlists.ErrNotFound // the account is gone } var have int if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlists WHERE user_id = ?", userID).Scan(&have); err != nil { return fmt.Errorf("count playlists: %w", err) } if have >= playlists.MaxPlaylists { return fmt.Errorf("%w: a user can have %d playlists", playlists.ErrTooMany, playlists.MaxPlaylists) } row := &playlistRow{UserID: userID, Name: name, Description: description, CreatedAt: now.Unix(), UpdatedAt: now.Unix()} if _, err := tx.NewInsert().Model(row).Exec(ctx); err != nil { return fmt.Errorf("insert playlist: %w", err) } out, err = summary(ctx, tx, userID, row.ID) return err }) return out, err } // List returns a page of the user's playlists, the one changed last first. func (r *PlaylistRepo) List(ctx context.Context, userID int64, limit, offset int) ([]playlists.Playlist, int, error) { var total int if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlists WHERE user_id = ?", userID).Scan(&total); err != nil { return nil, 0, fmt.Errorf("count playlists: %w", err) } out := []playlists.Playlist{} if offset >= total { return out, total, nil } rows, err := r.db.QueryContext(ctx, summarySelect+" WHERE p.user_id = ? GROUP BY p.id ORDER BY p.updated_at DESC, p.id DESC LIMIT ? OFFSET ?", userID, limit, offset) if err != nil { return nil, 0, fmt.Errorf("list playlists: %w", err) } defer rows.Close() for rows.Next() { p, err := scanSummary(rows) if err != nil { return nil, 0, err } out = append(out, p) } if err := rows.Err(); err != nil { return nil, 0, err } rows.Close() if err := attachCovers(ctx, r.db, out); err != nil { return nil, 0, err } return out, total, nil } // Get returns a playlist of the user. func (r *PlaylistRepo) Get(ctx context.Context, userID, id int64) (playlists.Playlist, error) { return summary(ctx, r.db, userID, id) } // Update changes the name and/or the description that are not nil. func (r *PlaylistRepo) Update(ctx context.Context, userID, id int64, name, description *string, now time.Time) (playlists.Playlist, error) { var out playlists.Playlist err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { q := tx.NewUpdate().Model((*playlistRow)(nil)).Set("updated_at = ?", now.Unix()).Where("id = ?", id).Where("user_id = ?", userID) if name != nil { q = q.Set("name = ?", *name) } if description != nil { q = q.Set("description = ?", *description) } res, err := q.Exec(ctx) if err := affectedOrNotFoundPlaylist(res, err); err != nil { return err } out, err = summary(ctx, tx, userID, id) return err }) return out, err } func affectedOrNotFoundPlaylist(res sql.Result, err error) error { if err != nil { return fmt.Errorf("change playlist: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return playlists.ErrNotFound } return nil } // Delete removes a playlist and, through the foreign key, its items. func (r *PlaylistRepo) Delete(ctx context.Context, userID, id int64) error { res, err := r.db.ExecContext(ctx, "DELETE FROM playlists WHERE id = ? AND user_id = ?", id, userID) return affectedOrNotFoundPlaylist(res, err) } // Items returns a page of the playlist in order. The tracks are read with the same three queries // as any list of tracks, whatever the page size, and also when a track has no file to play. func (r *PlaylistRepo) Items(ctx context.Context, userID, id int64, limit, offset int) ([]playlists.Item, int, error) { p, err := summary(ctx, r.db, userID, id) if err != nil { return nil, 0, err } items := []playlists.Item{} if offset >= p.TrackCount { return items, p.TrackCount, nil } rows, err := r.db.QueryContext(ctx, `SELECT i.id, i.position, i.added_at, t.id, t.title, COALESCE(t.duration_ms, 0), COALESCE(t.track_no, 0), COALESCE(t.disc_no, 0), t.album_id, EXISTS (SELECT 1 FROM audio_files f WHERE f.track_id = t.id AND f.missing_at IS NULL) FROM playlist_items i JOIN tracks t ON t.id = i.track_id WHERE i.playlist_id = ? ORDER BY i.position, i.id LIMIT ? OFFSET ?`, id, limit, offset) if err != nil { return nil, 0, fmt.Errorf("list items: %w", err) } defer rows.Close() albumOf := map[int64]int64{} page := catalog.Page[catalog.Track]{} var trackIDs []int64 seen := map[int64]bool{} for rows.Next() { var ( it playlists.Item added int64 albumID sql.NullInt64 ) t := &it.Track if err := rows.Scan(&it.ID, &it.Position, &added, &t.ID, &t.Title, &t.DurationMs, &t.TrackNo, &t.DiscNo, &albumID, &it.Available); err != nil { return nil, 0, err } it.AddedAt = time.Unix(added, 0) t.Artists = []catalog.ArtistRef{} if albumID.Valid { albumOf[t.ID] = albumID.Int64 } if !seen[t.ID] { seen[t.ID] = true trackIDs = append(trackIDs, t.ID) } items = append(items, it) page.Items = append(page.Items, *t) } if err := rows.Err(); err != nil { return nil, 0, err } if err := r.cat.attachTrackDetails(ctx, &page, trackIDs, albumOf, 0); err != nil { return nil, 0, err } for i := range items { items[i].Track = page.Items[i] } return items, p.TrackCount, nil } // AddTracks puts the tracks at position (at the end when nil), in the order given. func (r *PlaylistRepo) AddTracks(ctx context.Context, userID, id int64, trackIDs []int64, position *int, now time.Time) (playlists.Playlist, error) { var out playlists.Playlist err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { if err := touch(ctx, tx, userID, id, now); err != nil { return err } have, err := countItems(ctx, tx, id) if err != nil { return err } at := have if position != nil { at = *position } if at > have { return fmt.Errorf("%w: the playlist has %d tracks", playlists.ErrPosition, have) } if have+len(trackIDs) > playlists.MaxItems { return fmt.Errorf("%w: a playlist can have %d tracks", playlists.ErrTooMany, playlists.MaxItems) } distinct := map[int64]bool{} var ids []int64 for _, t := range trackIDs { if !distinct[t] { distinct[t] = true ids = append(ids, t) } } var known int if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM tracks WHERE id IN (?)", bun.In(ids)).Scan(&known); err != nil { return fmt.Errorf("check tracks: %w", err) } if known != len(ids) { return playlists.ErrTrackNotFound } if at < have { if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = position + ? WHERE playlist_id = ? AND position >= ?", len(trackIDs), id, at); err != nil { return fmt.Errorf("make room: %w", err) } } rows := make([]playlistItemRow, len(trackIDs)) for i, t := range trackIDs { rows[i] = playlistItemRow{PlaylistID: id, TrackID: t, Position: at + i, AddedAt: now.Unix()} } if _, err := tx.NewInsert().Model(&rows).Exec(ctx); err != nil { return fmt.Errorf("insert items: %w", err) } out, err = summary(ctx, tx, userID, id) return err }) return out, err } // RemoveItem takes an item out and closes the gap. func (r *PlaylistRepo) RemoveItem(ctx context.Context, userID, id, itemID int64, now time.Time) error { return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { if err := touch(ctx, tx, userID, id, now); err != nil { return err } at, err := itemPosition(ctx, tx, id, itemID) if err != nil { return err } if _, err := tx.ExecContext(ctx, "DELETE FROM playlist_items WHERE id = ?", itemID); err != nil { return fmt.Errorf("delete item: %w", err) } if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = position - 1 WHERE playlist_id = ? AND position > ?", id, at); err != nil { return fmt.Errorf("close the gap: %w", err) } return nil }) } func itemPosition(ctx context.Context, tx bun.Tx, id, itemID int64) (int, error) { var at int err := tx.QueryRowContext(ctx, "SELECT position FROM playlist_items WHERE id = ? AND playlist_id = ?", itemID, id).Scan(&at) if errors.Is(err, sql.ErrNoRows) { return 0, playlists.ErrItemNotFound } if err != nil { return 0, fmt.Errorf("read item: %w", err) } return at, nil } // MoveItem puts an item at a position. func (r *PlaylistRepo) MoveItem(ctx context.Context, userID, id, itemID int64, position int, now time.Time) error { return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { if err := touch(ctx, tx, userID, id, now); err != nil { return err } from, err := itemPosition(ctx, tx, id, itemID) if err != nil { return err } have, err := countItems(ctx, tx, id) if err != nil { return err } if position >= have { return fmt.Errorf("%w: the playlist has %d tracks", playlists.ErrPosition, have) } switch { case from < position: _, err = tx.ExecContext(ctx, "UPDATE playlist_items SET position = position - 1 WHERE playlist_id = ? AND position > ? AND position <= ?", id, from, position) case from > position: _, err = tx.ExecContext(ctx, "UPDATE playlist_items SET position = position + 1 WHERE playlist_id = ? AND position >= ? AND position < ?", id, position, from) default: return nil } if err != nil { return fmt.Errorf("shift items: %w", err) } if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = ? WHERE id = ?", position, itemID); err != nil { return fmt.Errorf("move item: %w", err) } return nil }) }