navidrome/persistence/sql_annotations_test.go

package persistence

import (
	"context"

	"github.com/Masterminds/squirrel"
	"github.com/deluan/rest"
	"github.com/navidrome/navidrome/model"
	"github.com/navidrome/navidrome/model/request"
	. "github.com/onsi/ginkgo/v2"
	. "github.com/onsi/gomega"
)

var _ = Describe("Annotation Filters", func() {
	var (
		albumRepo              *albumRepository
		albumWithoutAnnotation model.Album
		ctx                    context.Context
	)

	BeforeEach(func() {
		ctx = request.WithUser(GinkgoT().Context(), model.User{ID: "userid", UserName: "johndoe"})
		albumRepo = NewAlbumRepository(GetDBXBuilder()).(*albumRepository)

		// Create album without any annotation (no star, no rating)
		albumWithoutAnnotation = model.Album{ID: "no-annotation-album", Name: "No Annotation", LibraryID: 1}
		Expect(albumRepo.Put(ctx, &albumWithoutAnnotation)).To(Succeed())
	})

	AfterEach(func() {
		_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("album").Where(squirrel.Eq{"id": albumWithoutAnnotation.ID}))
	})

	Describe("ReassignAnnotation", func() {
		var prev, next model.Album

		BeforeEach(func() {
			prev = model.Album{ID: "reassign-prev", Name: "Prev", LibraryID: 1}
			next = model.Album{ID: "reassign-next", Name: "Next", LibraryID: 1}
			Expect(albumRepo.Put(ctx, &prev)).To(Succeed())
			Expect(albumRepo.Put(ctx, &next)).To(Succeed())
		})

		AfterEach(func() {
			_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": []string{prev.ID, next.ID}}))
			_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("album").Where(squirrel.Eq{"id": []string{prev.ID, next.ID}}))
		})

		It("moves the annotation when the new item has none", func() {
			Expect(albumRepo.SetRating(ctx, 4, prev.ID)).To(Succeed())

			Expect(albumRepo.ReassignAnnotation(ctx, prev.ID, next.ID)).To(Succeed())

			got, err := albumRepo.Get(ctx, next.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(got.Rating).To(Equal(4))
		})

		It("recomputes the new item's cached average rating", func() {
			Expect(albumRepo.SetRating(ctx, 4, prev.ID)).To(Succeed())

			Expect(albumRepo.ReassignAnnotation(ctx, prev.ID, next.ID)).To(Succeed())

			got, err := albumRepo.Get(ctx, next.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(got.AverageRating).To(Equal(4.0))
		})

		It("keeps the new item's annotation when both exist", func() {
			Expect(albumRepo.SetRating(ctx, 4, prev.ID)).To(Succeed())
			Expect(albumRepo.SetRating(ctx, 2, next.ID)).To(Succeed())

			Expect(albumRepo.ReassignAnnotation(ctx, prev.ID, next.ID)).To(Succeed())

			got, err := albumRepo.Get(ctx, next.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(got.Rating).To(Equal(2))
		})
	})

	Describe("annotationBoolFilter", func() {
		DescribeTable("creates correct SQL expressions",
			func(field, value string, expectedSQL string, expectedArgs []any) {
				sqlizer := annotationBoolFilter(field)(field, value)
				sql, args, err := sqlizer.ToSql()
				Expect(err).ToNot(HaveOccurred())
				Expect(sql).To(Equal(expectedSQL))
				Expect(args).To(Equal(expectedArgs))
			},
			Entry("starred=true", "starred", "true", "COALESCE(starred, 0) > 0", []any(nil)),
			Entry("starred=false", "starred", "false", "COALESCE(starred, 0) = 0", []any(nil)),
			Entry("starred=True (case insensitive)", "starred", "True", "COALESCE(starred, 0) > 0", []any(nil)),
			Entry("rating=true", "rating", "true", "COALESCE(rating, 0) > 0", []any(nil)),
			Entry("play_count=true", "play_count", "true", "COALESCE(play_count, 0) > 0", []any(nil)),
			Entry("play_count=false", "play_count", "false", "COALESCE(play_count, 0) = 0", []any(nil)),
		)

		It("returns nil if value is not a string", func() {
			sqlizer := annotationBoolFilter("starred")("starred", 123)
			Expect(sqlizer).To(BeNil())
		})
	})

	Describe("starredFilter", func() {
		It("false includes items without annotations", func() {
			albums, err := albumRepo.GetAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("starred")("starred", "false"),
			})
			Expect(err).ToNot(HaveOccurred())

			var found bool
			for _, a := range albums {
				if a.ID == albumWithoutAnnotation.ID {
					found = true
					break
				}
			}
			Expect(found).To(BeTrue(), "Item without annotation should be included in starred=false filter")
		})

		It("true excludes items without annotations", func() {
			albums, err := albumRepo.GetAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("starred")("starred", "true"),
			})
			Expect(err).ToNot(HaveOccurred())

			for _, a := range albums {
				Expect(a.ID).ToNot(Equal(albumWithoutAnnotation.ID))
			}
		})
	})

	Describe("hasRatingFilter", func() {
		It("false includes items without annotations", func() {
			albums, err := albumRepo.GetAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("rating")("rating", "false"),
			})
			Expect(err).ToNot(HaveOccurred())

			var found bool
			for _, a := range albums {
				if a.ID == albumWithoutAnnotation.ID {
					found = true
					break
				}
			}
			Expect(found).To(BeTrue(), "Item without annotation should be included in has_rating=false filter")
		})

		It("true excludes items without annotations", func() {
			albums, err := albumRepo.GetAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("rating")("rating", "true"),
			})
			Expect(err).ToNot(HaveOccurred())

			for _, a := range albums {
				Expect(a.ID).ToNot(Equal(albumWithoutAnnotation.ID))
			}
		})

		It("true includes items with rating > 0", func() {
			// Create album with rating 1
			ratedAlbum := model.Album{ID: "rated-album", Name: "Rated Album", LibraryID: 1}
			Expect(albumRepo.Put(ctx, &ratedAlbum)).To(Succeed())
			Expect(albumRepo.SetRating(ctx, 1, ratedAlbum.ID)).To(Succeed())
			defer func() {
				_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": ratedAlbum.ID}))
				_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("album").Where(squirrel.Eq{"id": ratedAlbum.ID}))
			}()

			albums, err := albumRepo.GetAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("rating")("rating", "true"),
			})
			Expect(err).ToNot(HaveOccurred())

			var found bool
			for _, a := range albums {
				if a.ID == ratedAlbum.ID {
					found = true
					break
				}
			}
			Expect(found).To(BeTrue(), "Album with rating 5 should be included in has_rating=true filter")
		})
	})

	It("ignores invalid filter values (not strings)", func() {
		res, err := albumRepo.ReadAll(ctx, rest.QueryOptions{
			Filters: map[string]any{"starred": 123},
		})
		Expect(err).ToNot(HaveOccurred())
		albums := res

		var found bool
		for _, a := range albums {
			if a.ID == albumWithoutAnnotation.ID {
				found = true
				break
			}
		}
		Expect(found).To(BeTrue(), "Item without annotation should be included when filter is ignored")
	})

	Describe("annotationColumns", func() {
		It("derives the annotation join columns from model.Annotations, excluding average_rating", func() {
			cols := annotationColumns()
			Expect(cols).To(HaveKey("starred"))
			Expect(cols).To(HaveKey("starred_at"))
			Expect(cols).To(HaveKey("rating"))
			Expect(cols).To(HaveKey("rated_at"))
			Expect(cols).To(HaveKey("play_count"))
			Expect(cols).To(HaveKey("play_date"))
			Expect(cols).To(HaveLen(6), "expected exactly the 6 annotation-join columns")
			Expect(cols).ToNot(HaveKey("average_rating"), "average_rating lives on the base table, not the annotation join")
		})
	})

	Describe("filtersNeedAnnotation", func() {
		It("is true when the query references an annotation column", func() {
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Eq{"starred": true})
			Expect(filtersNeedAnnotation(q)).To(BeTrue())
		})

		It("is true for a raw expression referencing an annotation column", func() {
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Expr("rating > 0"))
			Expect(filtersNeedAnnotation(q)).To(BeTrue())
		})

		It("is false for a query that references no annotation column", func() {
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Eq{"missing": false})
			Expect(filtersNeedAnnotation(q)).To(BeFalse())
		})

		It("is false for a filter on average_rating (base-table column, not the annotation rating)", func() {
			// Regression: average_rating must not match the annotation column "rating".
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Gt{"average_rating": 3})
			Expect(filtersNeedAnnotation(q)).To(BeFalse())
		})

		It("is true when both average_rating and a real annotation column are referenced", func() {
			q := squirrel.Select("count(1)").From("media_file").
				Where(squirrel.Gt{"average_rating": 3}).
				Where(squirrel.Expr("COALESCE(rating, 0) > 0"))
			Expect(filtersNeedAnnotation(q)).To(BeTrue())
		})

		It("is true for uppercase/mixed-case annotation columns (SQLite is case-insensitive)", func() {
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Expr("RATING > 0"))
			Expect(filtersNeedAnnotation(q)).To(BeTrue())
		})

		It("is false for uppercase average_rating (still excluded case-insensitively)", func() {
			q := squirrel.Select("count(1)").From("media_file").Where(squirrel.Expr("AVERAGE_RATING > 3"))
			Expect(filtersNeedAnnotation(q)).To(BeFalse())
		})
	})

	Describe("CountAll annotation-join gating", func() {
		It("counts all items unfiltered (join dropped)", func() {
			total, err := albumRepo.CountAll(ctx)
			Expect(err).ToNot(HaveOccurred())
			Expect(total).To(BeNumerically(">=", int64(1)))

			filtered, err := albumRepo.CountAll(ctx, model.QueryOptions{
				Filters: squirrel.Eq{"album.id": albumWithoutAnnotation.ID},
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(filtered).To(Equal(int64(1)))
		})

		It("counts starred items correctly (named annotation filter keeps the join)", func() {
			starredAlbum := model.Album{ID: "counted-starred-album", Name: "Counted Starred", LibraryID: 1}
			Expect(albumRepo.Put(ctx, &starredAlbum)).To(Succeed())
			Expect(albumRepo.SetStar(ctx, true, starredAlbum.ID)).To(Succeed())
			defer func() {
				_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": starredAlbum.ID}))
				_, _ = albumRepo.executeSQL(ctx, squirrel.Delete("album").Where(squirrel.Eq{"id": starredAlbum.ID}))
			}()

			// Exactly two albums are starred for this user: the one created above and
			// albumRadioactivity (id 103) from the seed data.
			count, err := albumRepo.CountAll(ctx, model.QueryOptions{
				Filters: annotationBoolFilter("starred")("starred", "true"),
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(count).To(Equal(int64(2)))
		})

		It("counts via a raw annotation filter without a 'no such column' error", func() {
			count, err := albumRepo.CountAll(ctx, model.QueryOptions{
				Filters: squirrel.Expr("COALESCE(rating, 0) > 0"),
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(count).To(BeNumerically(">=", int64(0)))
		})
	})
})