package storage import ( "context" "database/sql" "fmt" "sort" "github.com/uptrace/bun" "git.kmalloc.org/squid/sonda/internal/stats" ) // StatsRepo reads the counts behind the statistics page. Whatever is counted in "the library" is what can be played: // a song with a file that is not missing. A listen is a complete event, or a play of at least minMs. type StatsRepo struct{ db *bun.DB } // NewStatsRepo returns the repo. func NewStatsRepo(db *bun.DB) *StatsRepo { return &StatsRepo{db: db} } var _ stats.Store = (*StatsRepo)(nil) // Totals counts what the library holds that can be played, and the space its files take. func (r *StatsRepo) Totals(ctx context.Context) (stats.Totals, error) { vis, _ := visible("t", 0) var out stats.Totals if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*), COALESCE(SUM(t.duration_ms), 0) FROM tracks t WHERE "+vis).Scan(&out.Songs, &out.DurationMs); err != nil { return out, fmt.Errorf("count songs: %w", err) } if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM artists a WHERE "+playableArtist("a.id")).Scan(&out.Artists); err != nil { return out, fmt.Errorf("count artists: %w", err) } if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM albums al WHERE EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND "+vis+")").Scan(&out.Albums); err != nil { return out, fmt.Errorf("count albums: %w", err) } if err := r.db.QueryRowContext(ctx, "SELECT COALESCE(SUM(size_bytes), 0) FROM audio_files WHERE missing_at IS NULL").Scan(&out.SizeBytes); err != nil { return out, fmt.Errorf("sum sizes: %w", err) } return out, nil } // Formats counts the files that are not missing by format. func (r *StatsRepo) Formats(ctx context.Context) ([]stats.FormatCount, error) { rows, err := r.db.QueryContext(ctx, "SELECT LOWER(format), COUNT(*), COALESCE(SUM(size_bytes), 0) FROM audio_files WHERE missing_at IS NULL GROUP BY LOWER(format)") if err != nil { return nil, fmt.Errorf("count formats: %w", err) } defer rows.Close() var out []stats.FormatCount for rows.Next() { var f stats.FormatCount if err := rows.Scan(&f.Format, &f.Files, &f.Bytes); err != nil { return nil, err } out = append(out, f) } return out, rows.Err() } // weightedGenres adds up, for each genre, the items that have it. An item with several genres weighs 1/n for each. // source is the FROM and WHERE of a query whose rows are items with the genre table joined as g and the count of genres // of the item as c.n. func (r *StatsRepo) weightedGenres(ctx context.Context, source string, args []any) ([]stats.GenreWeight, error) { rows, err := r.db.QueryContext(ctx, "SELECT g.genre, COUNT(*), SUM(1.0 / c.n) "+source+" GROUP BY g.genre", args...) if err != nil { return nil, fmt.Errorf("weigh genres: %w", err) } defer rows.Close() var out []stats.GenreWeight for rows.Next() { var g stats.GenreWeight if err := rows.Scan(&g.Genre, &g.Songs, &g.Weight); err != nil { return nil, err } out = append(out, g) } return out, rows.Err() } const genresPerTrack = "(SELECT track_id, COUNT(*) AS n FROM track_genres GROUP BY track_id)" // Genres weighs the genres of the songs that can be played, and counts the songs that have one. func (r *StatsRepo) Genres(ctx context.Context) ([]stats.GenreWeight, int, error) { vis, _ := visible("t", 0) out, err := r.weightedGenres(ctx, "FROM track_genres g JOIN "+genresPerTrack+" c ON c.track_id = g.track_id JOIN tracks t ON t.id = g.track_id WHERE "+vis, nil) if err != nil { return nil, 0, err } var tagged int if err := r.db.QueryRowContext(ctx, "SELECT COUNT(DISTINCT g.track_id) FROM track_genres g JOIN tracks t ON t.id = g.track_id WHERE "+vis).Scan(&tagged); err != nil { return nil, 0, fmt.Errorf("count tagged songs: %w", err) } return out, tagged, nil } // Years counts the playable songs by the year in their tags. func (r *StatsRepo) Years(ctx context.Context) ([]stats.YearCount, error) { vis, _ := visible("t", 0) rows, err := r.db.QueryContext(ctx, "SELECT t.year, COUNT(*) FROM tracks t WHERE t.year IS NOT NULL AND "+vis+" GROUP BY t.year") if err != nil { return nil, fmt.Errorf("count years: %w", err) } defer rows.Close() var out []stats.YearCount for rows.Next() { var y stats.YearCount if err := rows.Scan(&y.Year, &y.Songs); err != nil { return nil, err } out = append(out, y) } return out, rows.Err() } // listensWhere is the condition for the listens of a user in a period. func listensWhere(userID, minMs int64, rng stats.Range) (string, []any) { where := "e.user_id = ? AND " + counted args := []any{userID, minMs} if !rng.From.IsZero() { where += " AND e.occurred_at >= ?" args = append(args, rng.From.Unix()) } if !rng.Until.IsZero() { where += " AND e.occurred_at < ?" args = append(args, rng.Until.Unix()) } return where, args } // ListenTotals sums up the listens of a user in a period. func (r *StatsRepo) ListenTotals(ctx context.Context, userID, minMs int64, rng stats.Range) (stats.ListenTotals, error) { where, args := listensWhere(userID, minMs, rng) var out stats.ListenTotals if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*), COALESCE(SUM(e.duration_played_ms), 0), COUNT(DISTINCT e.track_id), COALESCE(MIN(e.occurred_at), 0) FROM listening_events e WHERE "+where, args...).Scan(&out.Listens, &out.PlayedMs, &out.Songs, &out.First); err != nil { return out, fmt.Errorf("sum listens: %w", err) } if err := r.db.QueryRowContext(ctx, "SELECT COUNT(DISTINCT ta.artist_id) FROM listening_events e JOIN track_artists ta ON ta.track_id = e.track_id WHERE "+where, args...).Scan(&out.Artists); err != nil { return out, fmt.Errorf("count artists listened to: %w", err) } return out, nil } // ListenTimes gives the moment of each listen. The hour, weekday and year are worked out by the caller, in the time // zone of the listener, so that both databases give the same answer. func (r *StatsRepo) ListenTimes(ctx context.Context, userID, minMs int64, rng stats.Range) ([]int64, error) { where, args := listensWhere(userID, minMs, rng) rows, err := r.db.QueryContext(ctx, "SELECT e.occurred_at FROM listening_events e WHERE "+where, args...) if err != nil { return nil, fmt.Errorf("read listens: %w", err) } defer rows.Close() var out []int64 for rows.Next() { var at int64 if err := rows.Scan(&at); err != nil { return nil, err } out = append(out, at) } return out, rows.Err() } // TopArtists ranks the artists by listens, a listen counting once for each artist credited on the song. func (r *StatsRepo) TopArtists(ctx context.Context, userID, minMs int64, rng stats.Range, limit int) ([]stats.Ranked, error) { where, args := listensWhere(userID, minMs, rng) rows, err := r.db.QueryContext(ctx, `SELECT a.id, a.name, COUNT(DISTINCT e.id) AS n FROM listening_events e JOIN track_artists ta ON ta.track_id = e.track_id JOIN artists a ON a.id = ta.artist_id WHERE `+where+` GROUP BY a.id, a.name ORDER BY n DESC, a.name ASC, a.id ASC LIMIT ?`, append(args, limit)...) if err != nil { return nil, fmt.Errorf("rank artists: %w", err) } defer rows.Close() var out []stats.Ranked for rows.Next() { var x stats.Ranked if err := rows.Scan(&x.ID, &x.Name, &x.Listens); err != nil { return nil, err } out = append(out, x) } return out, rows.Err() } // TopSongs ranks the songs by listens, and names their artists. func (r *StatsRepo) TopSongs(ctx context.Context, userID, minMs int64, rng stats.Range, limit int) ([]stats.Ranked, error) { where, args := listensWhere(userID, minMs, rng) rows, err := r.db.QueryContext(ctx, `SELECT t.id, t.title, COUNT(*) AS n FROM listening_events e JOIN tracks t ON t.id = e.track_id WHERE `+where+` GROUP BY t.id, t.title ORDER BY n DESC, t.title ASC, t.id ASC LIMIT ?`, append(args, limit)...) if err != nil { return nil, fmt.Errorf("rank songs: %w", err) } defer rows.Close() var out []stats.Ranked var ids []int64 for rows.Next() { var x stats.Ranked if err := rows.Scan(&x.ID, &x.Name, &x.Listens); err != nil { return nil, err } out = append(out, x) ids = append(ids, x.ID) } if err := rows.Err(); err != nil { return nil, err } if len(ids) == 0 { return out, nil } names, err := r.db.QueryContext(ctx, "SELECT ta.track_id, a.name FROM track_artists ta JOIN artists a ON a.id = ta.artist_id WHERE ta.track_id IN (?) ORDER BY ta.track_id, a.name", bun.In(ids)) if err != nil { return nil, fmt.Errorf("name the artists: %w", err) } defer names.Close() byTrack := map[int64][]string{} for names.Next() { var id int64 var name string if err := names.Scan(&id, &name); err != nil { return nil, err } if list := byTrack[id]; len(list) == 0 || list[len(list)-1] != name { // a role listed twice is one artist byTrack[id] = append(list, name) } } for i := range out { out[i].Artists = byTrack[out[i].ID] } return out, names.Err() } // ListenGenres weighs the genres of the listens, and counts the listens whose song has a genre. func (r *StatsRepo) ListenGenres(ctx context.Context, userID, minMs int64, rng stats.Range) ([]stats.GenreWeight, int, error) { where, args := listensWhere(userID, minMs, rng) out, err := r.weightedGenres(ctx, "FROM listening_events e JOIN track_genres g ON g.track_id = e.track_id JOIN "+genresPerTrack+" c ON c.track_id = e.track_id WHERE "+where, args) if err != nil { return nil, 0, err } var tagged int if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM listening_events e WHERE "+where+" AND EXISTS (SELECT 1 FROM track_genres g WHERE g.track_id = e.track_id)", args...).Scan(&tagged); err != nil { return nil, 0, fmt.Errorf("count tagged listens: %w", err) } sort.Slice(out, func(i, j int) bool { return out[i].Genre < out[j].Genre }) return out, tagged, nil } // ListenGenreTimes gives every listen with the genres of its song: one row for each genre, weighing 1/n for a song with // n, or one row with no genre for a song that has none. The listens of a year or a month, and the share of each genre in // them, are worked out by the caller in the time zone of the listener. func (r *StatsRepo) ListenGenreTimes(ctx context.Context, userID, minMs int64, rng stats.Range) ([]stats.GenreListen, error) { where, args := listensWhere(userID, minMs, rng) rows, err := r.db.QueryContext(ctx, "SELECT e.occurred_at, g.genre, 1.0 / c.n FROM listening_events e LEFT JOIN track_genres g ON g.track_id = e.track_id "+ "LEFT JOIN "+genresPerTrack+" c ON c.track_id = e.track_id WHERE "+where, args...) if err != nil { return nil, fmt.Errorf("read the genres of the listens: %w", err) } defer rows.Close() var out []stats.GenreListen for rows.Next() { var at int64 var genre sql.NullString var weight sql.NullFloat64 if err := rows.Scan(&at, &genre, &weight); err != nil { return nil, err } g := stats.GenreListen{At: at, Genre: genre.String, Weight: 1} if genre.Valid { g.Weight = weight.Float64 } out = append(out, g) } return out, rows.Err() }