package storage import ( "context" "database/sql" "encoding/json" "errors" "fmt" "strings" "time" "github.com/uptrace/bun" "git.kmalloc.org/squid/sonda/internal/catalog" "git.kmalloc.org/squid/sonda/internal/mixes" "git.kmalloc.org/squid/sonda/internal/smart" ) // SmartRepo stores smart playlists and reads the songs their rules choose from. It implements smart.Store. type SmartRepo struct { db *bun.DB mix *MixRepo // reads the genres, the history and the songs, as the mixes do } // NewSmartRepo builds a SmartRepo. func NewSmartRepo(db *bun.DB) *SmartRepo { return &SmartRepo{db: db, mix: NewMixRepo(db)} } const smartColumns = "id, name, description, rules, sort, max_tracks, created_at, updated_at" func scanSmart(rows interface{ Scan(...any) error }) (smart.SmartPlaylist, error) { var ( p smart.SmartPlaylist rules, sort string created, updated int64 ) if err := rows.Scan(&p.ID, &p.Name, &p.Description, &rules, &sort, &p.Limit, &created, &updated); err != nil { return smart.SmartPlaylist{}, err } if err := json.Unmarshal([]byte(rules), &p.Rules); err != nil { return smart.SmartPlaylist{}, fmt.Errorf("read the rules of smart playlist %d: %w", p.ID, err) } p.Sort = smart.Sort(sort) p.CreatedAt, p.UpdatedAt = time.Unix(created, 0), time.Unix(updated, 0) return p, nil } func (r *SmartRepo) get(ctx context.Context, db bun.IDB, userID, id int64) (smart.SmartPlaylist, error) { p, err := scanSmart(db.QueryRowContext(ctx, "SELECT "+smartColumns+" FROM smart_playlists WHERE id = ? AND user_id = ?", id, userID)) if errors.Is(err, sql.ErrNoRows) { return smart.SmartPlaylist{}, smart.ErrNotFound } if err != nil { return smart.SmartPlaylist{}, fmt.Errorf("read smart playlist: %w", err) } return p, nil } // Create keeps a smart playlist. func (r *SmartRepo) Create(ctx context.Context, userID int64, p smart.SmartPlaylist, now time.Time) (smart.SmartPlaylist, error) { rules, err := json.Marshal(p.Rules) if err != nil { return smart.SmartPlaylist{}, fmt.Errorf("write the rules: %w", err) } var out smart.SmartPlaylist 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 smart.ErrNotFound // the account is gone } var have int if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM smart_playlists WHERE user_id = ?", userID).Scan(&have); err != nil { return fmt.Errorf("count smart playlists: %w", err) } if have >= smart.MaxPlaylists { return fmt.Errorf("%w: a user can have %d smart playlists", smart.ErrTooMany, smart.MaxPlaylists) } var id int64 err = tx.QueryRowContext(ctx, `INSERT INTO smart_playlists (user_id, name, description, rules, sort, max_tracks, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING id`, userID, p.Name, p.Description, string(rules), string(p.Sort), p.Limit, now.Unix(), now.Unix()).Scan(&id) if err != nil { return fmt.Errorf("insert smart playlist: %w", err) } out, err = r.get(ctx, tx, userID, id) return err }) return out, err } // List returns a page of the user's smart playlists, the one changed last first. func (r *SmartRepo) List(ctx context.Context, userID int64, limit, offset int) ([]smart.SmartPlaylist, int, error) { var total int if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM smart_playlists WHERE user_id = ?", userID).Scan(&total); err != nil { return nil, 0, fmt.Errorf("count smart playlists: %w", err) } rows, err := r.db.QueryContext(ctx, "SELECT "+smartColumns+" FROM smart_playlists WHERE user_id = ? ORDER BY updated_at DESC, id DESC LIMIT ? OFFSET ?", userID, limit, offset) if err != nil { return nil, 0, fmt.Errorf("list smart playlists: %w", err) } defer rows.Close() out := []smart.SmartPlaylist{} for rows.Next() { p, err := scanSmart(rows) if err != nil { return nil, 0, err } out = append(out, p) } return out, total, rows.Err() } // Get returns a smart playlist of the user. func (r *SmartRepo) Get(ctx context.Context, userID, id int64) (smart.SmartPlaylist, error) { return r.get(ctx, r.db, userID, id) } // Update replaces what a smart playlist says. func (r *SmartRepo) Update(ctx context.Context, userID, id int64, p smart.SmartPlaylist, now time.Time) (smart.SmartPlaylist, error) { rules, err := json.Marshal(p.Rules) if err != nil { return smart.SmartPlaylist{}, fmt.Errorf("write the rules: %w", err) } var out smart.SmartPlaylist err = r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { res, err := tx.ExecContext(ctx, "UPDATE smart_playlists SET name = ?, description = ?, rules = ?, sort = ?, max_tracks = ?, updated_at = ? WHERE id = ? AND user_id = ?", p.Name, p.Description, string(rules), string(p.Sort), p.Limit, now.Unix(), id, userID) if err := affectedOrNotFoundSmart(res, err); err != nil { return err } out, err = r.get(ctx, tx, userID, id) return err }) return out, err } func affectedOrNotFoundSmart(res sql.Result, err error) error { if err != nil { return fmt.Errorf("change smart playlist: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return smart.ErrNotFound } return nil } // Delete removes a smart playlist of the user. func (r *SmartRepo) Delete(ctx context.Context, userID, id int64) error { res, err := r.db.ExecContext(ctx, "DELETE FROM smart_playlists WHERE id = ? AND user_id = ?", id, userID) return affectedOrNotFoundSmart(res, err) } // Genres returns every genre of the playable songs as the files spell it. func (r *SmartRepo) Genres(ctx context.Context) ([]mixes.Genre, error) { return r.mix.Genres(ctx) } // History returns what the user did with each song they did anything with. func (r *SmartRepo) History(ctx context.Context, userID int64, minListen time.Duration) (map[int64]smart.History, error) { return r.mix.History(ctx, userID, minListen) } // Plays returns how many listens the user has of each song since a moment (a play counts as a listen when it was // completed or lasted minListen), for the songs they listened to in that time. func (r *SmartRepo) Plays(ctx context.Context, userID int64, minListen time.Duration, since time.Time) (map[int64]int, error) { ms := minListen.Milliseconds() rows, err := r.db.QueryContext(ctx, `SELECT track_id, COUNT(*) FROM listening_events WHERE user_id = ? AND occurred_at >= ? AND (event_type = 'complete' OR duration_played_ms >= ?) GROUP BY track_id`, userID, since.Unix(), ms) if err != nil { return nil, fmt.Errorf("read plays: %w", err) } defer rows.Close() out := map[int64]int{} for rows.Next() { var ( id int64 n int ) if err := rows.Scan(&id, &n); err != nil { return nil, err } out[id] = n } return out, rows.Err() } // AlbumCovers returns, for the songs in the order given, the albums that have a cover, each once, up to limit. func (r *SmartRepo) AlbumCovers(ctx context.Context, trackIDs []int64, limit int) ([]int64, error) { out := []int64{} if len(trackIDs) == 0 || limit <= 0 { return out, nil } rows, err := r.db.QueryContext(ctx, `SELECT t.id, t.album_id FROM tracks t JOIN images im ON im.kind = 'album' AND im.owner_id = t.album_id AND im.status = 'ok' WHERE t.id IN (?)`, bun.In(trackIDs)) if err != nil { return nil, fmt.Errorf("read the covers of songs: %w", err) } defer rows.Close() albumOf := map[int64]int64{} for rows.Next() { var track, album int64 if err := rows.Scan(&track, &album); err != nil { return nil, err } albumOf[track] = album } if err := rows.Err(); err != nil { return nil, err } seen := map[int64]bool{} for _, id := range trackIDs { if a, ok := albumOf[id]; ok && !seen[a] && len(out) < limit { seen[a] = true out = append(out, a) } } return out, nil } // Tracks returns the playable songs among ids, in the order given. func (r *SmartRepo) Tracks(ctx context.Context, ids []int64) ([]catalog.Track, error) { return r.mix.Tracks(ctx, ids) } // Candidates returns the playable songs that the filter keeps, with their main artist (the one with the // lowest id when there are several), the key of the title, the length and the date of the newest file. func (r *SmartRepo) Candidates(ctx context.Context, f smart.Filter) ([]smart.Candidate, error) { vis, _ := visible("t", 0) conds := []string{vis} var args []any add := func(cond string, a ...any) { conds = append(conds, cond) args = append(args, a...) } const genre = "EXISTS (SELECT 1 FROM track_genres g WHERE g.track_id = t.id AND g.genre IN (?))" const artist = "EXISTS (SELECT 1 FROM track_artists x WHERE x.track_id = t.id AND x.role = 'main' AND x.artist_id IN (?))" if len(f.Genres) > 0 { add(genre, bun.In(f.Genres)) } if len(f.NotGenres) > 0 { add("NOT "+genre, bun.In(f.NotGenres)) } if len(f.ArtistIDs) > 0 { add(artist, bun.In(f.ArtistIDs)) } if len(f.NotArtistIDs) > 0 { add("NOT "+artist, bun.In(f.NotArtistIDs)) } if len(f.AlbumIDs) > 0 { add("t.album_id IN (?)", bun.In(f.AlbumIDs)) } if len(f.NotAlbumIDs) > 0 { add("(t.album_id IS NULL OR t.album_id NOT IN (?))", bun.In(f.NotAlbumIDs)) } if f.MinYear > 0 { add("t.year >= ?", f.MinYear) } if f.MaxYear > 0 { add("t.year <= ?", f.MaxYear) } if len(f.Formats) > 0 || f.MinBitrateKbps > 0 { file := "EXISTS (SELECT 1 FROM audio_files f WHERE f.track_id = t.id AND f.missing_at IS NULL" var fargs []any if len(f.Formats) > 0 { file += " AND f.format IN (?)" fargs = append(fargs, bun.In(f.Formats)) } if f.MinBitrateKbps > 0 { file += " AND f.bitrate >= ?" fargs = append(fargs, f.MinBitrateKbps) } add(file+")", fargs...) } rows, err := r.db.QueryContext(ctx, `SELECT t.id, COALESCE((SELECT MIN(ta.artist_id) FROM track_artists ta WHERE ta.track_id = t.id AND ta.role = 'main'), 0), t.title_key, COALESCE(t.duration_ms, 0), COALESCE((SELECT MAX(f.mtime_unix) FROM audio_files f WHERE f.track_id = t.id AND f.missing_at IS NULL), 0) FROM tracks t WHERE `+strings.Join(conds, " AND ")+` ORDER BY t.id`, args...) if err != nil { return nil, fmt.Errorf("read the songs: %w", err) } defer rows.Close() out := []smart.Candidate{} for rows.Next() { var ( c smart.Candidate added int64 ) if err := rows.Scan(&c.TrackID, &c.ArtistID, &c.Title, &c.DurationMs, &added); err != nil { return nil, err } if added > 0 { c.AddedAt = time.Unix(added, 0) } out = append(out, c) } return out, rows.Err() }