package persistence

import (
	"context"
	"errors"
	"fmt"
	"reflect"
	"time"

	"github.com/Masterminds/squirrel"
	"github.com/deluan/rest"
	"github.com/navidrome/navidrome/conf"
	"github.com/navidrome/navidrome/conf/configtest"
	"github.com/navidrome/navidrome/log"
	"github.com/navidrome/navidrome/model"
	"github.com/navidrome/navidrome/model/criteria"
	"github.com/navidrome/navidrome/model/id"
	"github.com/navidrome/navidrome/model/request"
	"github.com/navidrome/navidrome/utils/slice"
	. "github.com/onsi/ginkgo/v2"
	. "github.com/onsi/gomega"
	"github.com/pocketbase/dbx"
)

var _ = Describe("MediaRepository", func() {
	var mr model.MediaFileRepository
	var ctx context.Context

	BeforeEach(func() {
		ctx = request.WithUser(log.NewContext(GinkgoT().Context()), model.User{ID: "userid"})
		mr = NewMediaFileRepository(GetDBXBuilder())
	})

	Describe("GetAlbumIDsByFolder", func() {
		var lib model.Library
		var albumRoot, disc1, sibling *model.Folder

		BeforeEach(func() {
			ctx := request.WithUser(log.NewContext(context.TODO()), model.User{ID: "userid"})
			libPtr, err := NewLibraryRepository(GetDBXBuilder()).Get(ctx, 1)
			Expect(err).ToNot(HaveOccurred())
			lib = *libPtr

			folderRepo := newFolderRepository(GetDBXBuilder())
			albumRoot = model.NewFolder(lib, "ByFolder/Album")
			disc1 = model.NewFolder(lib, "ByFolder/Album/CD1")
			sibling = model.NewFolder(lib, "ByFolder/Other")
			for _, f := range []*model.Folder{albumRoot, disc1, sibling} {
				Expect(folderRepo.Put(ctx, f)).To(Succeed())
			}
			// Tracks live in the disc subfolder; the sibling album is the negative control.
			Expect(mr.Put(ctx, &model.MediaFile{ID: "fol-mf-1", LibraryID: 1, AlbumID: "fol-al-1", FolderID: disc1.ID, Path: "t/1.mp3"})).To(Succeed())
			Expect(mr.Put(ctx, &model.MediaFile{ID: "fol-mf-2", LibraryID: 1, AlbumID: "fol-al-1", FolderID: disc1.ID, Path: "t/2.mp3"})).To(Succeed())
			Expect(mr.Put(ctx, &model.MediaFile{ID: "fol-mf-3", LibraryID: 1, AlbumID: "fol-al-2", FolderID: sibling.ID, Path: "t/3.mp3"})).To(Succeed())
			Expect(mr.Put(ctx, &model.MediaFile{ID: "fol-mf-4", LibraryID: 1, AlbumID: "fol-al-3", FolderID: disc1.ID, Path: "t/4.mp3", Missing: true})).To(Succeed())
			DeferCleanup(func() {
				_, _ = GetDBXBuilder().NewQuery("DELETE FROM media_file WHERE id LIKE 'fol-mf-%'").Execute()
				_, _ = GetDBXBuilder().NewQuery("DELETE FROM folder WHERE path LIKE 'ByFolder%' OR name = 'ByFolder'").Execute()
			})
		})

		It("returns the distinct album IDs of non-missing tracks in the folder", func() {
			ids, err := mr.GetAlbumIDsByFolder(ctx, lib, disc1.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(ids).To(ConsistOf("fol-al-1"))
		})

		It("also matches albums whose tracks are in a direct child of the folder", func() {
			// A cover in the album root must reach the album whose tracks sit in CD1
			ids, err := mr.GetAlbumIDsByFolder(ctx, lib, albumRoot.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(ids).To(ConsistOf("fol-al-1"))
		})

		It("does not match albums outside the folder", func() {
			ids, err := mr.GetAlbumIDsByFolder(ctx, lib, albumRoot.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(ids).ToNot(ContainElement("fol-al-2"))
		})
	})

	Describe("GetCursor", func() {
		It("yields the same media files as GetAll", func() {
			opts := model.QueryOptions{Sort: "title"}
			want, err := mr.GetAll(ctx, opts)
			Expect(err).ToNot(HaveOccurred())
			Expect(collectCursor(mr.GetCursor(ctx, opts))).To(Equal([]model.MediaFile(want)))
		})

		It("honors Max/Offset like GetAll", func() {
			opts := model.QueryOptions{Sort: "title", Max: 2, Offset: 1}
			want, err := mr.GetAll(ctx, opts)
			Expect(err).ToNot(HaveOccurred())
			Expect(collectCursor(mr.GetCursor(ctx, opts))).To(Equal([]model.MediaFile(want)))
		})
	})

	It("gets mediafile from the DB", func() {
		actual, err := mr.Get(ctx, "1004")
		Expect(err).ToNot(HaveOccurred())
		actual.CreatedAt = time.Time{}
		Expect(actual).To(Equal(&songAntenna))
	})

	It("returns ErrNotFound", func() {
		_, err := mr.Get(ctx, "56")
		Expect(err).To(MatchError(model.ErrNotFound))
	})

	It("counts the number of mediafiles in the DB", func() {
		Expect(mr.CountAll(ctx)).To(Equal(int64(13)))
	})

	Describe("CountAll annotation-join gating", func() {
		var adminRepo model.MediaFileRepository
		var adminCtx context.Context

		BeforeEach(func() {
			adminCtx = request.WithUser(ctx, model.User{ID: "userid", IsAdmin: true})
			adminRepo = NewMediaFileRepository(GetDBXBuilder())
		})

		It("counts starred songs when an annotation filter is present", func() {
			// Come Together (id 1002) is starred for the admin user in the seed data
			count, err := adminRepo.CountAll(adminCtx, model.QueryOptions{
				Filters: annotationBoolFilter("starred")("starred", "true"),
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(count).To(Equal(int64(1)))
		})

		It("counts with starred=false without a 'no such column' error (join kept)", func() {
			count, err := adminRepo.CountAll(adminCtx, model.QueryOptions{
				Filters: annotationBoolFilter("starred")("starred", "false"),
			})
			Expect(err).ToNot(HaveOccurred())
			// All songs except the one starred one
			Expect(count).To(Equal(int64(12)))
		})

		It("counts unfiltered with the join dropped", func() {
			Expect(adminRepo.CountAll(adminCtx)).To(Equal(int64(13)))
		})
	})

	Describe("CountBySuffix", func() {
		var mp3File, flacFile1, flacFile2, flacUpperFile model.MediaFile

		BeforeEach(func() {
			mp3File = model.MediaFile{ID: "suffix-mp3", LibraryID: 1, Suffix: "mp3", Path: "test/file.mp3"}
			flacFile1 = model.MediaFile{ID: "suffix-flac1", LibraryID: 1, Suffix: "flac", Path: "test/file1.flac"}
			flacFile2 = model.MediaFile{ID: "suffix-flac2", LibraryID: 1, Suffix: "flac", Path: "test/file2.flac"}
			flacUpperFile = model.MediaFile{ID: "suffix-FLAC", LibraryID: 1, Suffix: "FLAC", Path: "test/file.FLAC"}

			Expect(mr.Put(ctx, &mp3File)).To(Succeed())
			Expect(mr.Put(ctx, &flacFile1)).To(Succeed())
			Expect(mr.Put(ctx, &flacFile2)).To(Succeed())
			Expect(mr.Put(ctx, &flacUpperFile)).To(Succeed())
		})

		AfterEach(func() {
			_ = mr.Delete(ctx, mp3File.ID)
			_ = mr.Delete(ctx, flacFile1.ID)
			_ = mr.Delete(ctx, flacFile2.ID)
			_ = mr.Delete(ctx, flacUpperFile.ID)
		})

		It("counts media files grouped by suffix with lowercase normalization", func() {
			counts, err := mr.CountBySuffix(ctx)
			Expect(err).ToNot(HaveOccurred())

			// Should have lowercase keys only
			Expect(counts).To(HaveKey("mp3"))
			Expect(counts).To(HaveKey("flac"))
			Expect(counts).ToNot(HaveKey("FLAC"))

			// mp3: 1 file
			Expect(counts["mp3"]).To(Equal(int64(1)))
			// flac: 3 files (2 lowercase + 1 uppercase normalized)
			Expect(counts["flac"]).To(Equal(int64(3)))
		})
	})

	It("returns songs ordered by lyrics with a specific title/artist", func() {
		// attempt to mimic filters.SongsByArtistTitleWithLyricsFirst, except we want all items
		results, err := mr.GetAll(ctx, model.QueryOptions{
			Sort:  "lyrics, updated_at",
			Order: "desc",
			Filters: squirrel.And{
				squirrel.Eq{"title": "Antenna"},
				squirrel.Or{
					Exists("json_tree(participants, '$.albumartist')", squirrel.Eq{"value": "Kraftwerk"}),
					Exists("json_tree(participants, '$.artist')", squirrel.Eq{"value": "Kraftwerk"}),
				},
			},
		})

		Expect(err).To(BeNil())
		Expect(results).To(HaveLen(3))
		Expect(results[0].Lyrics).To(Equal(`[{"lang":"xxx","line":[{"value":"This is a set of lyrics"}],"synced":false}]`))
		for _, item := range results[1:] {
			Expect(item.Lyrics).To(Equal("[]"))
			Expect(item.Title).To(Equal("Antenna"))
			Expect(item.Participants[model.RoleArtist][0].Name).To(Equal("Kraftwerk"))
		}
	})

	Describe("GetRandom", func() {
		It("returns the requested number of distinct, fully-hydrated media files", func() {
			results, err := mr.GetRandom(ctx, model.QueryOptions{Max: 5})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(5))

			// Each returned row must match its GetAll counterpart exactly — proves Phase 2
			// hydrates full rows (not bare rowids) — and ids must be distinct.
			byID := map[string]model.MediaFile{}
			all, err := mr.GetAll(ctx)
			Expect(err).ToNot(HaveOccurred())
			for _, mf := range all {
				byID[mf.ID] = mf
			}
			seen := map[string]bool{}
			for _, mf := range results {
				expected, ok := byID[mf.ID]
				Expect(ok).To(BeTrue(), "returned id must be a real media file")
				Expect(mf.Title).To(Equal(expected.Title), "row must be fully hydrated")
				Expect(seen[mf.ID]).To(BeFalse(), "no duplicate rows")
				seen[mf.ID] = true
			}
		})

		It("returns all matching files when Max exceeds the total", func() {
			results, err := mr.GetRandom(ctx, model.QueryOptions{Max: 1000})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(13))
		})

		It("honors filters", func() {
			results, err := mr.GetRandom(ctx, model.QueryOptions{
				Max:     10,
				Filters: squirrel.Eq{"media_file.title": "Antenna"},
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).ToNot(BeEmpty())
			for _, mf := range results {
				Expect(mf.Title).To(Equal("Antenna"))
			}
		})

		It("returns varying results across calls", func() {
			// Retry a few times: two random draws of 5 from 13 rows differ with near-certainty.
			first, err := mr.GetRandom(ctx, model.QueryOptions{Max: 5})
			Expect(err).ToNot(HaveOccurred())
			firstIDs := func() []string {
				ids := make([]string, len(first))
				for i, mf := range first {
					ids[i] = mf.ID
				}
				return ids
			}()
			differed := false
			for range 10 {
				next, err := mr.GetRandom(ctx, model.QueryOptions{Max: 5})
				Expect(err).ToNot(HaveOccurred())
				nextIDs := make([]string, len(next))
				for i, mf := range next {
					nextIDs[i] = mf.ID
				}
				if !reflect.DeepEqual(firstIDs, nextIDs) {
					differed = true
					break
				}
			}
			Expect(differed).To(BeTrue(), "GetRandom should not return an identical set every call")
		})

		It("randomizes order even when Max exceeds the total", func() {
			// Same set of rows every time (all 13), but the order must still be shuffled —
			// guards against Phase 2's `rowid IN (...)` returning rows in rowid order.
			first, err := mr.GetRandom(ctx, model.QueryOptions{Max: 100})
			Expect(err).ToNot(HaveOccurred())
			Expect(first).To(HaveLen(13))
			firstIDs := make([]string, len(first))
			for i, mf := range first {
				firstIDs[i] = mf.ID
			}
			differed := false
			for range 10 {
				next, err := mr.GetRandom(ctx, model.QueryOptions{Max: 100})
				Expect(err).ToNot(HaveOccurred())
				nextIDs := make([]string, len(next))
				for i, mf := range next {
					nextIDs[i] = mf.ID
				}
				if !reflect.DeepEqual(firstIDs, nextIDs) {
					differed = true
					break
				}
			}
			Expect(differed).To(BeTrue(), "order must vary even when returning all rows")
		})
	})

	Describe("Put CreatedAt behavior (#5050)", func() {
		It("sets CreatedAt to now when inserting a new file with zero CreatedAt", func() {
			before := time.Now().Add(-time.Second)
			newFile := model.MediaFile{ID: id.NewRandom(), LibraryID: 1, Path: "test/created-at-zero.mp3"}
			Expect(mr.Put(ctx, &newFile)).To(Succeed())

			retrieved, err := mr.Get(ctx, newFile.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(retrieved.CreatedAt).To(BeTemporally(">", before))

			_ = mr.Delete(ctx, newFile.ID)
		})

		It("preserves CreatedAt when inserting a new file with non-zero CreatedAt", func() {
			originalTime := time.Date(2020, 3, 15, 10, 30, 0, 0, time.UTC)
			newFile := model.MediaFile{
				ID:        id.NewRandom(),
				LibraryID: 1,
				Path:      "test/created-at-preserved.mp3",
				CreatedAt: originalTime,
			}
			Expect(mr.Put(ctx, &newFile)).To(Succeed())

			retrieved, err := mr.Get(ctx, newFile.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(retrieved.CreatedAt).To(BeTemporally("~", originalTime, time.Second))

			_ = mr.Delete(ctx, newFile.ID)
		})

		It("does not reset CreatedAt when updating an existing file", func() {
			originalTime := time.Date(2019, 6, 1, 12, 0, 0, 0, time.UTC)
			fileID := id.NewRandom()
			newFile := model.MediaFile{
				ID:        fileID,
				LibraryID: 1,
				Path:      "test/created-at-update.mp3",
				Title:     "Original Title",
				CreatedAt: originalTime,
			}
			Expect(mr.Put(ctx, &newFile)).To(Succeed())

			// Update the file with a new title but zero CreatedAt
			updatedFile := model.MediaFile{
				ID:        fileID,
				LibraryID: 1,
				Path:      "test/created-at-update.mp3",
				Title:     "Updated Title",
				// CreatedAt is zero - should NOT overwrite the stored value
			}
			Expect(mr.Put(ctx, &updatedFile)).To(Succeed())

			retrieved, err := mr.Get(ctx, fileID)
			Expect(err).ToNot(HaveOccurred())
			Expect(retrieved.Title).To(Equal("Updated Title"))
			// CreatedAt should still be the original time (not reset)
			Expect(retrieved.CreatedAt).To(BeTemporally("~", originalTime, time.Second))

			_ = mr.Delete(ctx, fileID)
		})
	})

	It("checks existence of mediafiles in the DB", func() {
		Expect(mr.Exists(ctx, songAntenna.ID)).To(BeTrue())
		Expect(mr.Exists(ctx, "666")).To(BeFalse())
	})

	It("delete tracks by id", func() {
		newID := id.NewRandom()
		Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: newID})).To(Succeed())

		Expect(mr.Delete(ctx, newID)).To(Succeed())

		_, err := mr.Get(ctx, newID)
		Expect(err).To(MatchError(model.ErrNotFound))
	})

	It("deletes all missing files", func() {
		new1 := model.MediaFile{ID: id.NewRandom(), LibraryID: 1}
		new2 := model.MediaFile{ID: id.NewRandom(), LibraryID: 1}
		Expect(mr.Put(ctx, &new1)).To(Succeed())
		Expect(mr.Put(ctx, &new2)).To(Succeed())
		Expect(mr.MarkMissing(ctx, true, &new1, &new2)).To(Succeed())

		adminCtx := request.WithUser(log.NewContext(context.TODO()), model.User{ID: "userid", IsAdmin: true})
		adminRepo := NewMediaFileRepository(GetDBXBuilder())

		// Ensure the files are marked as missing and we have 2 of them
		count, err := adminRepo.CountAll(adminCtx, model.QueryOptions{Filters: squirrel.Eq{"missing": true}})
		Expect(count).To(BeNumerically("==", 2))
		Expect(err).ToNot(HaveOccurred())

		count, err = adminRepo.DeleteAllMissing(adminCtx)
		Expect(err).ToNot(HaveOccurred())
		Expect(count).To(BeNumerically("==", 2))

		_, err = mr.Get(ctx, new1.ID)
		Expect(err).To(MatchError(model.ErrNotFound))
		_, err = mr.Get(ctx, new2.ID)
		Expect(err).To(MatchError(model.ErrNotFound))
	})

	Context("Annotations", func() {
		It("increments play count when the tracks does not have annotations", func() {
			id := "incplay.firsttime"
			Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: id})).To(BeNil())
			playDate := time.Now()
			Expect(mr.IncPlayCount(ctx, id, playDate)).To(BeNil())

			mf, err := mr.Get(ctx, id)
			Expect(err).To(BeNil())

			Expect(mf.PlayDate.Unix()).To(Equal(playDate.Unix()))
			Expect(mf.PlayCount).To(Equal(int64(1)))
		})

		Describe("AverageRating", func() {
			var raw *mediaFileRepository

			BeforeEach(func() {
				raw = mr.(*mediaFileRepository)
			})

			It("returns 0 when no ratings exist", func() {
				newID := id.NewRandom()
				Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: newID, Path: "test/no-rating.mp3"})).To(Succeed())

				mf, err := mr.Get(ctx, newID)
				Expect(err).ToNot(HaveOccurred())
				Expect(mf.AverageRating).To(Equal(0.0))

				_, _ = raw.executeSQL(ctx, squirrel.Delete("media_file").Where(squirrel.Eq{"id": newID}))
			})

			It("returns the user's rating as average when only one user rated", func() {
				newID := id.NewRandom()
				Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: newID, Path: "test/single-rating.mp3"})).To(Succeed())
				Expect(mr.SetRating(ctx, 5, newID)).To(Succeed())

				mf, err := mr.Get(ctx, newID)
				Expect(err).ToNot(HaveOccurred())
				Expect(mf.AverageRating).To(Equal(5.0))

				_, _ = raw.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": newID}))
				_, _ = raw.executeSQL(ctx, squirrel.Delete("media_file").Where(squirrel.Eq{"id": newID}))
			})

			It("calculates average across multiple users", func() {
				newID := id.NewRandom()
				Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: newID, Path: "test/multi-rating.mp3"})).To(Succeed())

				Expect(mr.SetRating(ctx, 3, newID)).To(Succeed())

				user2Ctx := request.WithUser(GinkgoT().Context(), regularUser)
				user2Repo := NewMediaFileRepository(GetDBXBuilder())
				Expect(user2Repo.SetRating(user2Ctx, 5, newID)).To(Succeed())

				mf, err := mr.Get(ctx, newID)
				Expect(err).ToNot(HaveOccurred())
				Expect(mf.AverageRating).To(Equal(4.0))

				_, _ = raw.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": newID}))
				_, _ = raw.executeSQL(ctx, squirrel.Delete("media_file").Where(squirrel.Eq{"id": newID}))
			})

			It("excludes zero ratings from average calculation", func() {
				newID := id.NewRandom()
				Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: newID, Path: "test/zero-excluded.mp3"})).To(Succeed())

				Expect(mr.SetRating(ctx, 4, newID)).To(Succeed())

				user2Ctx := request.WithUser(GinkgoT().Context(), regularUser)
				user2Repo := NewMediaFileRepository(GetDBXBuilder())
				Expect(user2Repo.SetRating(user2Ctx, 0, newID)).To(Succeed())

				mf, err := mr.Get(ctx, newID)
				Expect(err).ToNot(HaveOccurred())
				Expect(mf.AverageRating).To(Equal(4.0))

				_, _ = raw.executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": newID}))
				_, _ = raw.executeSQL(ctx, squirrel.Delete("media_file").Where(squirrel.Eq{"id": newID}))
			})
		})

		It("preserves play date if and only if provided date is older", func() {
			id := "incplay.playdate"
			Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: id})).To(BeNil())
			playDate := time.Now()
			Expect(mr.IncPlayCount(ctx, id, playDate)).To(BeNil())
			mf, err := mr.Get(ctx, id)
			Expect(err).To(BeNil())
			Expect(mf.PlayDate.Unix()).To(Equal(playDate.Unix()))
			Expect(mf.PlayCount).To(Equal(int64(1)))

			playDateLate := playDate.AddDate(0, 0, 1)
			Expect(mr.IncPlayCount(ctx, id, playDateLate)).To(BeNil())
			mf, err = mr.Get(ctx, id)
			Expect(err).To(BeNil())
			Expect(mf.PlayDate.Unix()).To(Equal(playDateLate.Unix()))
			Expect(mf.PlayCount).To(Equal(int64(2)))

			playDateEarly := playDate.AddDate(0, 0, -1)
			Expect(mr.IncPlayCount(ctx, id, playDateEarly)).To(BeNil())
			mf, err = mr.Get(ctx, id)
			Expect(err).To(BeNil())
			Expect(mf.PlayDate.Unix()).To(Equal(playDateLate.Unix()))
			Expect(mf.PlayCount).To(Equal(int64(3)))
		})

		It("increments play count on newly starred items", func() {
			id := "star.incplay"
			Expect(mr.Put(ctx, &model.MediaFile{LibraryID: 1, ID: id})).To(BeNil())
			Expect(mr.SetStar(ctx, true, id)).To(BeNil())
			playDate := time.Now()
			Expect(mr.IncPlayCount(ctx, id, playDate)).To(BeNil())

			mf, err := mr.Get(ctx, id)
			Expect(err).To(BeNil())

			Expect(mf.PlayDate.Unix()).To(Equal(playDate.Unix()))
			Expect(mf.PlayCount).To(Equal(int64(1)))
		})
	})

	Context("Sort options", func() {
		Context("recently_added sort", func() {
			var testMediaFiles []model.MediaFile

			BeforeEach(func() {
				DeferCleanup(configtest.SetupConfig())

				// Create test media files with specific timestamps
				testMediaFiles = []model.MediaFile{
					{
						ID:        id.NewRandom(),
						LibraryID: 1,
						Title:     "Old Song",
						Path:      "test/old.mp3",
					},
					{
						ID:        id.NewRandom(),
						LibraryID: 1,
						Title:     "Middle Song",
						Path:      "test/middle.mp3",
					},
					{
						ID:        id.NewRandom(),
						LibraryID: 1,
						Title:     "New Song",
						Path:      "test/new.mp3",
					},
				}

				// Insert test data first
				for i := range testMediaFiles {
					Expect(mr.Put(ctx, &testMediaFiles[i])).To(Succeed())
				}

				// Then manually update timestamps using direct SQL to bypass the repository logic
				db := GetDBXBuilder()

				// Set specific timestamps for testing
				oldTime := time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC)
				middleTime := time.Date(2021, 1, 1, 0, 0, 0, 0, time.UTC)
				newTime := time.Date(2022, 1, 1, 0, 0, 0, 0, time.UTC)

				// Update "Old Song": created long ago, updated recently
				_, err := db.Update("media_file",
					map[string]any{
						"created_at": oldTime,
						"updated_at": newTime,
					},
					dbx.HashExp{"id": testMediaFiles[0].ID}).Execute()
				Expect(err).ToNot(HaveOccurred())

				// Update "Middle Song": created and updated at the same middle time
				_, err = db.Update("media_file",
					map[string]any{
						"created_at": middleTime,
						"updated_at": middleTime,
					},
					dbx.HashExp{"id": testMediaFiles[1].ID}).Execute()
				Expect(err).ToNot(HaveOccurred())

				// Update "New Song": created recently, updated long ago
				_, err = db.Update("media_file",
					map[string]any{
						"created_at": newTime,
						"updated_at": oldTime,
					},
					dbx.HashExp{"id": testMediaFiles[2].ID}).Execute()
				Expect(err).ToNot(HaveOccurred())
			})

			AfterEach(func() {
				// Clean up test data
				for _, mf := range testMediaFiles {
					_ = mr.Delete(ctx, mf.ID)
				}
			})

			When("RecentlyAddedByModTime is false", func() {
				var testRepo model.MediaFileRepository

				BeforeEach(func() {
					conf.Server.RecentlyAddedByModTime = false
					// Create repository AFTER setting config
					testRepo = NewMediaFileRepository(GetDBXBuilder())
				})

				It("sorts by created_at", func() {
					// Get results sorted by recently_added (should use created_at)
					results, err := testRepo.GetAll(ctx, model.QueryOptions{
						Sort:    "recently_added",
						Order:   "desc",
						Filters: squirrel.Eq{"media_file.id": []string{testMediaFiles[0].ID, testMediaFiles[1].ID, testMediaFiles[2].ID}},
					})
					Expect(err).ToNot(HaveOccurred())
					Expect(results).To(HaveLen(3))

					// Verify sorting by created_at (newest first in descending order)
					Expect(results[0].Title).To(Equal("New Song"))    // created 2022
					Expect(results[1].Title).To(Equal("Middle Song")) // created 2021
					Expect(results[2].Title).To(Equal("Old Song"))    // created 2020
				})

				It("sorts in ascending order when specified", func() {
					// Get results sorted by recently_added in ascending order
					results, err := testRepo.GetAll(ctx, model.QueryOptions{
						Sort:    "recently_added",
						Order:   "asc",
						Filters: squirrel.Eq{"media_file.id": []string{testMediaFiles[0].ID, testMediaFiles[1].ID, testMediaFiles[2].ID}},
					})
					Expect(err).ToNot(HaveOccurred())
					Expect(results).To(HaveLen(3))

					// Verify sorting by created_at (oldest first)
					Expect(results[0].Title).To(Equal("Old Song"))    // created 2020
					Expect(results[1].Title).To(Equal("Middle Song")) // created 2021
					Expect(results[2].Title).To(Equal("New Song"))    // created 2022
				})
			})

			When("RecentlyAddedByModTime is true", func() {
				var testRepo model.MediaFileRepository

				BeforeEach(func() {
					conf.Server.RecentlyAddedByModTime = true
					// Create repository AFTER setting config
					testRepo = NewMediaFileRepository(GetDBXBuilder())
				})

				It("sorts by updated_at", func() {
					// Get results sorted by recently_added (should use updated_at)
					results, err := testRepo.GetAll(ctx, model.QueryOptions{
						Sort:    "recently_added",
						Order:   "desc",
						Filters: squirrel.Eq{"media_file.id": []string{testMediaFiles[0].ID, testMediaFiles[1].ID, testMediaFiles[2].ID}},
					})
					Expect(err).ToNot(HaveOccurred())
					Expect(results).To(HaveLen(3))

					// Verify sorting by updated_at (newest first in descending order)
					Expect(results[0].Title).To(Equal("Old Song"))    // updated 2022
					Expect(results[1].Title).To(Equal("Middle Song")) // updated 2021
					Expect(results[2].Title).To(Equal("New Song"))    // updated 2020
				})
			})

			It("breaks ties deterministically when files share the same created_at", func() {
				conf.Server.RecentlyAddedByModTime = false
				ctx := log.NewContext(GinkgoT().Context())
				ctx = request.WithUser(ctx, model.User{ID: "userid"})
				repo := NewMediaFileRepository(GetDBXBuilder())

				ids := []string{testMediaFiles[0].ID, testMediaFiles[1].ID, testMediaFiles[2].ID}
				sameTime := time.Date(2024, 3, 1, 0, 0, 0, 0, time.UTC)
				_, err := GetDBXBuilder().Update("media_file",
					dbx.Params{"created_at": sameTime},
					dbx.In("id", ids[0], ids[1], ids[2])).Execute()
				Expect(err).ToNot(HaveOccurred())

				order := func() []string {
					res, err := repo.GetAll(ctx, model.QueryOptions{
						Sort: "recently_added", Order: "desc",
						Filters: squirrel.Eq{"media_file.id": ids}})
					Expect(err).ToNot(HaveOccurred())
					out := make([]string, len(res))
					for i, mf := range res {
						out[i] = mf.ID
					}
					return out
				}
				// Stable across repeated queries (no query-plan-dependent reordering).
				Expect(order()).To(Equal(order()))
			})

		})
	})

	Context("Filters", func() {
		var mfWithoutAnnotation model.MediaFile

		BeforeEach(func() {
			mfWithoutAnnotation = model.MediaFile{ID: "no-annotation-file", LibraryID: 1, Path: "test/no-annotation.mp3", Title: "No Annotation"}
			Expect(mr.Put(ctx, &mfWithoutAnnotation)).To(Succeed())
		})

		AfterEach(func() {
			_ = mr.Delete(ctx, mfWithoutAnnotation.ID)
		})

		Describe("starred", func() {
			It("false includes items without annotations", func() {
				res, err := mr.ReadAll(ctx, rest.QueryOptions{
					Filters: map[string]any{"starred": "false"},
				})
				Expect(err).ToNot(HaveOccurred())
				files := res

				var found bool
				for _, f := range files {
					if f.ID == mfWithoutAnnotation.ID {
						found = true
						break
					}
				}
				Expect(found).To(BeTrue(), "MediaFile without annotation should be included in starred=false filter")
			})

			It("true excludes items without annotations", func() {
				res, err := mr.ReadAll(ctx, rest.QueryOptions{
					Filters: map[string]any{"starred": "true"},
				})
				Expect(err).ToNot(HaveOccurred())
				files := res

				for _, f := range files {
					Expect(f.ID).ToNot(Equal(mfWithoutAnnotation.ID))
				}
			})
		})

		Describe("path", func() {
			It("matches files whose path starts with the given prefix", func() {
				res, err := mr.ReadAll(ctx, rest.QueryOptions{
					Filters: map[string]any{"path": "test/"},
				})
				Expect(err).ToNot(HaveOccurred())
				files := res

				var found bool
				for _, f := range files {
					Expect(f.Path).To(HavePrefix("test/"))
					if f.ID == mfWithoutAnnotation.ID {
						found = true
					}
				}
				Expect(found).To(BeTrue(), "MediaFile with matching path prefix should be included")
			})

			It("excludes files whose path does not start with the given prefix", func() {
				res, err := mr.ReadAll(ctx, rest.QueryOptions{
					Filters: map[string]any{"path": "no-such-prefix/"},
				})
				Expect(err).ToNot(HaveOccurred())
				files := res
				Expect(files).To(BeEmpty())
			})
		})
	})

	Describe("Search", func() {
		Context("text search", func() {
			It("finds media files by title", func() {
				results, err := mr.Search(ctx, "Antenna", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(3)) // songAntenna, songAntennaWithLyrics, songAntenna2
				for _, result := range results {
					Expect(result.Title).To(Equal("Antenna"))
				}
			})

			It("finds media files case insensitively", func() {
				results, err := mr.Search(ctx, "antenna", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(3))
				for _, result := range results {
					Expect(result.Title).To(Equal("Antenna"))
				}
			})

			It("returns empty result when no matches found", func() {
				results, err := mr.Search(ctx, "nonexistent", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty())
			})
		})

		Context("MBID search", func() {
			var mediaFileWithMBID model.MediaFile
			var raw *mediaFileRepository

			BeforeEach(func() {
				raw = mr.(*mediaFileRepository)
				// Create a test media file with MBID
				mediaFileWithMBID = model.MediaFile{
					ID:                "test-mbid-mediafile",
					Title:             "Test MBID MediaFile",
					MbzRecordingID:    "550e8400-e29b-41d4-a716-446655440020", // Valid UUID v4
					MbzReleaseTrackID: "550e8400-e29b-41d4-a716-446655440021", // Valid UUID v4
					LibraryID:         1,
					Path:              "test/path/test.mp3",
				}

				// Insert the test media file into the database
				err := mr.Put(ctx, &mediaFileWithMBID)
				Expect(err).ToNot(HaveOccurred())
			})

			AfterEach(func() {
				// Clean up test data using direct SQL
				_, _ = raw.executeSQL(ctx, squirrel.Delete(raw.tableName).Where(squirrel.Eq{"id": mediaFileWithMBID.ID}))
			})

			It("finds media file by mbz_recording_id", func() {
				results, err := mr.Search(ctx, "550e8400-e29b-41d4-a716-446655440020", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(1))
				Expect(results[0].ID).To(Equal("test-mbid-mediafile"))
				Expect(results[0].Title).To(Equal("Test MBID MediaFile"))
			})

			It("finds media file by mbz_release_track_id", func() {
				results, err := mr.Search(ctx, "550e8400-e29b-41d4-a716-446655440021", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(1))
				Expect(results[0].ID).To(Equal("test-mbid-mediafile"))
				Expect(results[0].Title).To(Equal("Test MBID MediaFile"))
			})

			It("returns empty result when MBID is not found", func() {
				results, err := mr.Search(ctx, "550e8400-e29b-41d4-a716-446655440099", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty())
			})

			It("missing media files are never returned by search", func() {
				// Create a missing media file with MBID
				missingMediaFile := model.MediaFile{
					ID:             "test-missing-mbid-mediafile",
					Title:          "Test Missing MBID MediaFile",
					MbzRecordingID: "550e8400-e29b-41d4-a716-446655440022",
					LibraryID:      1,
					Path:           "test/path/missing.mp3",
					Missing:        true,
				}

				err := mr.Put(ctx, &missingMediaFile)
				Expect(err).ToNot(HaveOccurred())

				// Search never returns missing media files (hardcoded behavior)
				results, err := mr.Search(ctx, "550e8400-e29b-41d4-a716-446655440022", model.QueryOptions{Max: 10})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty())

				// Clean up
				_, _ = raw.executeSQL(ctx, squirrel.Delete(raw.tableName).Where(squirrel.Eq{"id": missingMediaFile.ID}))
			})
		})

		Context("empty query (natural order pagination)", func() {
			It("returns all non-missing files in natural order", func() {
				results, err := mr.Search(ctx, "", model.QueryOptions{Max: 1000})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).ToNot(BeEmpty())
				for _, result := range results {
					Expect(result.Missing).To(BeFalse())
				}
			})

			It(`treats quoted empty query ("") the same as empty`, func() {
				all, err := mr.Search(ctx, "", model.QueryOptions{Max: 1000})
				Expect(err).ToNot(HaveOccurred())
				quoted, err := mr.Search(ctx, `""`, model.QueryOptions{Max: 1000})
				Expect(err).ToNot(HaveOccurred())
				Expect(quoted).To(HaveLen(len(all)))
			})

			It("paginates without overlaps or gaps", func() {
				all, err := mr.Search(ctx, "", model.QueryOptions{Max: 1000})
				Expect(err).ToNot(HaveOccurred())
				Expect(len(all)).To(BeNumerically(">", 3))

				var paged model.MediaFiles
				pageSize := 3
				for offset := 0; offset < len(all); offset += pageSize {
					page, err := mr.Search(ctx, "", model.QueryOptions{Max: pageSize, Offset: offset})
					Expect(err).ToNot(HaveOccurred())
					paged = append(paged, page...)
				}
				Expect(paged).To(HaveLen(len(all)))
				for i := range all {
					Expect(paged[i].ID).To(Equal(all[i].ID), fmt.Sprintf("row %d differs", i))
				}
			})

			It("returns empty page when offset is beyond the total", func() {
				results, err := mr.Search(ctx, "", model.QueryOptions{Max: 10, Offset: 100000})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty())
			})
		})
	})

	Describe("ReassignReferences", func() {
		var prev, next model.MediaFile
		var pr model.PlaylistRepository
		var pls model.Playlist

		BeforeEach(func() {
			ctx := request.WithUser(log.NewContext(context.TODO()), model.User{ID: "userid"})
			pr = NewPlaylistRepository(GetDBXBuilder())
			prev = model.MediaFile{ID: "reassign-prev", LibraryID: 1, Path: "reassign/prev.mp3", Title: "Prev"}
			next = model.MediaFile{ID: "reassign-next", LibraryID: 1, Path: "reassign/next.mp3", Title: "Next"}
			Expect(mr.Put(ctx, &prev)).To(Succeed())
			Expect(mr.Put(ctx, &next)).To(Succeed())
			pls = model.Playlist{Name: "Reassign", OwnerID: "userid"}
			pls.AddMediaFilesByID([]string{prev.ID})
			Expect(pr.Put(ctx, &pls)).To(Succeed())
		})

		AfterEach(func() {
			_ = pr.Delete(ctx, pls.ID)
			_ = mr.Delete(ctx, prev.ID)
			_ = mr.Delete(ctx, next.ID)
			_, _ = mr.(*mediaFileRepository).executeSQL(ctx, squirrel.Delete("annotation").Where(squirrel.Eq{"item_id": []string{prev.ID, next.ID}}))
			_, _ = mr.(*mediaFileRepository).executeSQL(ctx, squirrel.Delete("bookmark").Where(squirrel.Eq{"item_id": []string{prev.ID, next.ID}}))
			_, _ = mr.(*mediaFileRepository).executeSQL(ctx, squirrel.Delete("scrobbles").Where(squirrel.Eq{"media_file_id": []string{prev.ID, next.ID}}))
			_, _ = mr.(*mediaFileRepository).executeSQL(ctx, squirrel.Delete("scrobble_buffer").Where(squirrel.Eq{"media_file_id": []string{prev.ID, next.ID}}))
		})

		It("moves annotations, bookmarks and playlist entries onto the new id", func() {
			Expect(mr.SetRating(ctx, 5, prev.ID)).To(Succeed())
			Expect(mr.AddBookmark(ctx, prev.ID, "here", 42)).To(Succeed())

			Expect(mr.ReassignReferences(ctx, prev.ID, next.ID)).To(Succeed())

			got, err := mr.Get(ctx, next.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(got.Rating).To(Equal(5))

			bookmarks, err := mr.GetBookmarks(ctx)
			Expect(err).ToNot(HaveOccurred())
			Expect(bookmarks).To(ContainElement(HaveField("Item.ID", next.ID)))

			withTracks, err := pr.GetWithTracks(ctx, pls.ID, false, false)
			Expect(err).ToNot(HaveOccurred())
			Expect(withTracks.Tracks).To(HaveLen(1))
			Expect(withTracks.Tracks[0].MediaFileID).To(Equal(next.ID))
		})

		It("moves scrobbles and buffered scrobbles onto the new id", func() {
			ctx := request.WithUser(log.NewContext(context.TODO()), model.User{ID: "userid"})
			scrobbles := NewScrobbleRepository(GetDBXBuilder())
			buffer := NewScrobbleBufferRepository(GetDBXBuilder())
			Expect(scrobbles.RecordScrobble(ctx, prev.ID, time.Now())).To(Succeed())
			Expect(buffer.Enqueue(ctx, "lastfm", "userid", prev.ID, time.Now())).To(Succeed())

			Expect(mr.ReassignReferences(ctx, prev.ID, next.ID)).To(Succeed())
			Expect(mr.Delete(ctx, prev.ID)).To(Succeed())

			all, err := scrobbles.GetAll(ctx)
			Expect(err).ToNot(HaveOccurred())
			mine := slice.Map(slice.Filter(all, func(sc model.Scrobble) bool {
				return sc.MediaFileID == prev.ID || sc.MediaFileID == next.ID
			}), func(sc model.Scrobble) string { return sc.MediaFileID })
			Expect(mine).To(ConsistOf(next.ID))

			entry, err := buffer.Next(ctx, "lastfm", "userid")
			Expect(err).ToNot(HaveOccurred())
			Expect(entry).ToNot(BeNil())
			Expect(entry.MediaFile.ID).To(Equal(next.ID))
		})

		It("recomputes the average rating after merging another user's annotation", func() {
			otherCtx := request.WithUser(ctx, model.User{ID: "2222"})
			other := NewMediaFileRepository(GetDBXBuilder())
			Expect(mr.SetRating(ctx, 5, next.ID)).To(Succeed())
			Expect(other.SetRating(otherCtx, 3, prev.ID)).To(Succeed())

			Expect(mr.ReassignReferences(ctx, prev.ID, next.ID)).To(Succeed())

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

		It("keeps the new id's own annotation and bookmark when both exist", func() {
			Expect(mr.SetRating(ctx, 5, prev.ID)).To(Succeed())
			Expect(mr.SetRating(ctx, 1, next.ID)).To(Succeed())
			Expect(mr.AddBookmark(ctx, prev.ID, "prev", 42)).To(Succeed())
			Expect(mr.AddBookmark(ctx, next.ID, "next", 7)).To(Succeed())

			Expect(mr.ReassignReferences(ctx, prev.ID, next.ID)).To(Succeed())

			got, err := mr.Get(ctx, next.ID)
			Expect(err).ToNot(HaveOccurred())
			Expect(got.Rating).To(Equal(1))
			bookmarks, err := mr.GetBookmarks(ctx)
			Expect(err).ToNot(HaveOccurred())
			Expect(bookmarks).To(ContainElement(SatisfyAll(HaveField("Item.ID", next.ID), HaveField("Comment", "next"))))
		})
	})

	Describe("FindByPaths", func() {
		// Test fixtures for Unicode and case-sensitivity tests
		var testFiles []model.MediaFile

		BeforeEach(func() {
			testFiles = []model.MediaFile{
				{ID: "findpath-1", LibraryID: 1, Path: "artist/Album/track.mp3", Title: "Track"},
				{ID: "findpath-2", LibraryID: 1, Path: "artist/Album/UPPER.mp3", Title: "Upper"},
				// Fullwidth uppercase: ＡＣＲＯＳＳ (U+FF21 U+FF23 U+FF32 U+FF2F U+FF33 U+FF33)
				{ID: "findpath-3", LibraryID: 1, Path: "plex/02 - ＡＣＲＯＳＳ.flac", Title: "Fullwidth"},
				// French diacritic: è (U+00E8, can decompose to e + combining grave)
				{ID: "findpath-4", LibraryID: 1, Path: "artist/Michèle/song.mp3", Title: "French"},
				{ID: "findpath-5", LibraryID: 1, Path: "Bach: Goldberg Variations/01.mp3", Title: "Colon"},
				{ID: "findpath-6", LibraryID: 1, Path: "1999: A Different Life/01.mp3", Title: "Numeric colon"},
			}
			for _, mf := range testFiles {
				Expect(mr.Put(ctx, &mf)).To(Succeed())
			}
		})

		AfterEach(func() {
			for _, mf := range testFiles {
				_ = mr.Delete(ctx, mf.ID)
			}
		})

		It("treats a path whose prefix is not a library id as unqualified", func() {
			results, err := mr.FindByPaths(ctx, []string{"Bach: Goldberg Variations/01.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-5"))
		})

		It("finds a plain path whose colon prefix looks like a library id", func() {
			results, err := mr.FindByPaths(ctx, []string{"1999: A Different Life/01.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-6"))
		})

		It("splits only the first colon of a library-qualified path", func() {
			results, err := mr.FindByPaths(ctx, []string{"1:Bach: Goldberg Variations/01.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-5"))
		})

		It("finds files by exact path", func() {
			results, err := mr.FindByPaths(ctx, []string{"1:artist/Album/track.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-1"))
		})

		It("finds files case-insensitively for ASCII characters (NOCASE)", func() {
			// SQLite's COLLATE NOCASE handles ASCII case-insensitivity
			results, err := mr.FindByPaths(ctx, []string{"1:ARTIST/ALBUM/TRACK.MP3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-1"))
		})

		It("finds fullwidth characters only with exact case match (SQLite NOCASE limitation)", func() {
			// SQLite's NOCASE does NOT handle fullwidth uppercase/lowercase equivalence
			// The DB has fullwidth uppercase ＡＣＲＯＳＳ, searching with exact match should work
			results, err := mr.FindByPaths(ctx, []string{"1:plex/02 - ＡＣＲＯＳＳ.flac"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))
			Expect(results[0].ID).To(Equal("findpath-3"))

			// Searching with fullwidth lowercase ａｃｒｏｓｓ should NOT match
			// (this is the SQLite limitation that requires exact matching for non-ASCII)
			results, err = mr.FindByPaths(ctx, []string{"1:plex/02 - ａｃｒｏｓｓ.flac"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(BeEmpty())
		})

		It("returns multiple files when querying multiple paths", func() {
			results, err := mr.FindByPaths(ctx, []string{
				"1:artist/Album/track.mp3",
				"1:artist/Album/UPPER.mp3",
			})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(2))
		})

		It("returns empty slice for non-existent paths", func() {
			results, err := mr.FindByPaths(ctx, []string{"1:nonexistent/path.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(BeEmpty())
		})

		It("returns empty slice for empty input", func() {
			results, err := mr.FindByPaths(ctx, []string{})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(BeEmpty())
		})

		It("handles library-qualified paths correctly", func() {
			// Library 1 should find the file
			results, err := mr.FindByPaths(ctx, []string{"1:artist/Album/track.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(HaveLen(1))

			// Library 2 should NOT find it (file is in library 1)
			results, err = mr.FindByPaths(ctx, []string{"2:artist/Album/track.mp3"})
			Expect(err).ToNot(HaveOccurred())
			Expect(results).To(BeEmpty())
		})

		Context("when the user has restricted library access", func() {
			var otherLib model.Library
			var restrictedUser model.User

			BeforeEach(func() {
				adminCtx := request.WithUser(GinkgoT().Context(), adminUser)
				lr := NewLibraryRepository(GetDBXBuilder())

				// A second library the restricted user has no access to
				otherLib = model.Library{ID: 0, Name: "Other Library", Path: "/other/lib"}
				Expect(lr.Put(adminCtx, &otherLib)).To(Succeed())

				// A track that lives only in the other library (created as admin)
				adminMr := NewMediaFileRepository(GetDBXBuilder())
				Expect(adminMr.Put(adminCtx, &model.MediaFile{
					ID: "otherlib-track", LibraryID: otherLib.ID,
					Path: "hidden/test.mp3", Title: "Hidden",
				})).To(Succeed())

				// Non-admin user with access to library 1 ONLY
				restrictedUser = createUserWithLibraries("restricted-finder", []int{1})
				ur := NewUserRepository(GetDBXBuilder())
				Expect(ur.Put(adminCtx, &restrictedUser)).To(Succeed())
				Expect(ur.SetUserLibraries(adminCtx, restrictedUser.ID, []int{1})).To(Succeed())
			})

			AfterEach(func() {
				adminCtx := request.WithUser(GinkgoT().Context(), adminUser)
				_ = NewMediaFileRepository(GetDBXBuilder()).Delete(adminCtx, "otherlib-track")
				lr := NewLibraryRepository(GetDBXBuilder()).(*libraryRepository)
				_ = lr.delete(adminCtx, squirrel.Eq{"id": otherLib.ID})
				_ = NewUserRepository(GetDBXBuilder()).Delete(adminCtx, restrictedUser.ID)
			})

			It("does not resolve paths in libraries the user cannot access", func() {
				userCtx := request.WithUser(ctx, restrictedUser)
				userMr := NewMediaFileRepository(GetDBXBuilder())
				qualified := fmt.Sprintf("%d:hidden/test.mp3", otherLib.ID)
				results, err := userMr.FindByPaths(userCtx, []string{qualified})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty(), "a track outside the user's libraries must not be resolvable")
			})

			It("still resolves the path for an admin", func() {
				adminCtx := request.WithUser(ctx, adminUser)
				adminMr := NewMediaFileRepository(GetDBXBuilder())
				qualified := fmt.Sprintf("%d:hidden/test.mp3", otherLib.ID)
				results, err := adminMr.FindByPaths(adminCtx, []string{qualified})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(1))
				Expect(results[0].ID).To(Equal("otherlib-track"))
			})

			It("resolves paths from multiple libraries in a single call", func() {
				adminCtx := request.WithUser(ctx, adminUser)
				adminMr := NewMediaFileRepository(GetDBXBuilder())
				results, err := adminMr.FindByPaths(adminCtx, []string{
					"1:artist/Album/track.mp3",
					fmt.Sprintf("%d:hidden/test.mp3", otherLib.ID),
				})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(HaveLen(2))
				Expect([]string{results[0].ID, results[1].ID}).To(ConsistOf("findpath-1", "otherlib-track"))
			})

			It("keeps each path scoped to its own library when several are queried", func() {
				adminCtx := request.WithUser(ctx, adminUser)
				adminMr := NewMediaFileRepository(GetDBXBuilder())
				// Each path exists, but under the other library's ID, so neither must match.
				results, err := adminMr.FindByPaths(adminCtx, []string{
					fmt.Sprintf("%d:artist/Album/track.mp3", otherLib.ID),
					"1:hidden/test.mp3",
				})
				Expect(err).ToNot(HaveOccurred())
				Expect(results).To(BeEmpty())
			})
		})
	})

	Describe("dbMediaFile.String", func() {
		It("does not dereference a nil MediaFile", func() {
			Expect(fmt.Sprint(dbMediaFile{})).To(Equal("<nil>"))
			Expect(fmt.Sprint(&dbMediaFile{})).To(Equal("<nil>"))
		})
	})

	Describe("wrapMediaFileCursor", func() {
		It("does not panic when the cursor yields a dbMediaFile with nil MediaFile", func() {
			// Simulate what queryWithStableResults does on the rows.Err() path:
			// it yields a zero-value dbMediaFile (where MediaFile is nil) with an error.
			dbErr := fmt.Errorf("database is locked")
			cursor := func(yield func(dbMediaFile, error) bool) {
				var empty dbMediaFile // MediaFile pointer is nil
				yield(empty, dbErr)
			}

			// wrapMediaFileCursor should handle the nil MediaFile without panicking
			wrappedCursor := wrapMediaFileCursor(cursor)
			var gotErr error
			Expect(func() {
				for _, err := range wrappedCursor {
					gotErr = err
				}
			}).ToNot(Panic())
			Expect(gotErr).To(HaveOccurred())
			Expect(gotErr.Error()).To(ContainSubstring("unexpected nil model.MediaFile"))
			Expect(errors.Is(gotErr, dbErr)).To(BeTrue(), "should wrap the original cursor error")
		})

		It("yields mediafiles from a valid cursor", func() {
			mf := &model.MediaFile{ID: "mf1", Title: "Test"}
			cursor := func(yield func(dbMediaFile, error) bool) {
				yield(dbMediaFile{MediaFile: mf}, nil)
			}

			wrappedCursor := wrapMediaFileCursor(cursor)
			var mediafiles []model.MediaFile
			for m, err := range wrappedCursor {
				Expect(err).ToNot(HaveOccurred())
				mediafiles = append(mediafiles, m)
			}
			Expect(mediafiles).To(HaveLen(1))
			Expect(mediafiles[0].ID).To(Equal("mf1"))
		})
	})

	Describe("BPM and BitDepth nullable round-trip", func() {
		It("stores nil BPM and BitDepth as NULL and retrieves them as nil", func() {
			newID := id.NewRandom()
			mf := model.MediaFile{LibraryID: 1, ID: newID, Path: "test/bpm-nil.mp3"}
			Expect(mr.Put(ctx, &mf)).To(Succeed())

			retrieved, err := mr.Get(ctx, newID)
			Expect(err).ToNot(HaveOccurred())
			Expect(retrieved.BPM).To(BeNil())
			Expect(retrieved.BitDepth).To(BeNil())

			// Also verify via raw SQL that the columns are truly NULL (not 0)
			db := GetDBXBuilder()
			var row struct {
				BPM      *int `db:"bpm"`
				BitDepth *int `db:"bit_depth"`
			}
			err = db.NewQuery("SELECT bpm, bit_depth FROM media_file WHERE id={:id}").
				Bind(dbx.Params{"id": newID}).
				One(&row)
			Expect(err).ToNot(HaveOccurred())
			Expect(row.BPM).To(BeNil(), "bpm should be stored as NULL in the database")
			Expect(row.BitDepth).To(BeNil(), "bit_depth should be stored as NULL in the database")

			_ = mr.Delete(ctx, newID)
		})

		It("stores non-nil BPM and BitDepth and retrieves correct values", func() {
			newID := id.NewRandom()
			bpm := 120
			bitDepth := 24
			mf := model.MediaFile{LibraryID: 1, ID: newID, Path: "test/bpm-set.mp3", BPM: &bpm, BitDepth: &bitDepth}
			Expect(mr.Put(ctx, &mf)).To(Succeed())

			retrieved, err := mr.Get(ctx, newID)
			Expect(err).ToNot(HaveOccurred())
			Expect(retrieved.BPM).ToNot(BeNil())
			Expect(*retrieved.BPM).To(Equal(120))
			Expect(retrieved.BitDepth).ToNot(BeNil())
			Expect(*retrieved.BitDepth).To(Equal(24))

			_ = mr.Delete(ctx, newID)
		})
	})

	Describe("AlbumImage hydration", func() {
		It("carries the parent album's artwork state onto each track", func() {
			mfs := model.MediaFiles{{ID: "mf-1", AlbumID: "al-1"}}
			infos := map[string]model.ItemArtworkInfo{
				"al-1": {ItemID: "al-1", Hash: "0123456789abcdef", BlurHash: "LEHV6nWB2yk8"},
			}
			applyItemImage(infos, mfs[0].AlbumID, &mfs[0].AlbumImage)

			Expect(mfs[0].AlbumImage.ImageHash).To(Equal("0123456789abcdef"))
			Expect(mfs[0].AlbumImage.BlurHash).To(Equal("LEHV6nWB2yk8"))
			Expect(mfs[0].AlbumImage.ImageAbsent).To(BeFalse())
		})

		It("keeps the album state independent of the track's own art", func() {
			mf := model.MediaFile{ID: "mf-2", AlbumID: "al-2"}
			mf.ImageHash = "aaaaaaaaaaaaaaaa" // the track's own art
			applyItemImage(
				map[string]model.ItemArtworkInfo{"al-2": {ItemID: "al-2", Hash: "bbbbbbbbbbbbbbbb"}},
				mf.AlbumID, &mf.AlbumImage,
			)
			Expect(mf.ImageHash).To(Equal("aaaaaaaaaaaaaaaa"))
			Expect(mf.AlbumImage.ImageHash).To(Equal("bbbbbbbbbbbbbbbb"))
		})
	})

	// Exists must apply the same library filter as Get/GetAll/CountAll.
	Describe("Exists library visibility", func() {
		It("hides a track the user has no library access to", func() {
			restricted := model.User{ID: "restricted_mf_user", UserName: "rm", Name: "RM", Email: "rm@t.com"}
			rctx := request.WithUser(GinkgoT().Context(), restricted)

			Expect(mr.Exists(ctx, songAntenna.ID)).To(BeTrue(), "admin sees it")
			Expect(NewMediaFileRepository(GetDBXBuilder()).Exists(rctx, songAntenna.ID)).To(BeFalse())
		})
	})

	Describe("MatchesCriteria", func() {
		It("returns true when the track matches", func() {
			c := criteria.Criteria{Expression: criteria.All{criteria.Contains{"title": "Day"}}}
			match, err := mr.MatchesCriteria(ctx, songDayInALife.ID, c)
			Expect(err).ToNot(HaveOccurred())
			Expect(match).To(BeTrue())
		})
		It("returns false when the track does not match", func() {
			c := criteria.Criteria{Expression: criteria.All{criteria.Contains{"title": "Nickelback"}}}
			match, err := mr.MatchesCriteria(ctx, songDayInALife.ID, c)
			Expect(err).ToNot(HaveOccurred())
			Expect(match).To(BeFalse())
		})
		It("treats missing annotations as their COALESCE default", func() {
			// unrated track: rating coalesces to 0, so "rating < 4" matches
			c := criteria.Criteria{Expression: criteria.All{criteria.Lt{"rating": 4}}}
			match, err := mr.MatchesCriteria(ctx, songDayInALife.ID, c)
			Expect(err).ToNot(HaveOccurred())
			Expect(match).To(BeTrue())
		})
		It("returns an error for an invalid field", func() {
			c := criteria.Criteria{Expression: criteria.All{criteria.Is{"bogusfield": 1}}}
			_, err := mr.MatchesCriteria(ctx, songDayInALife.ID, c)
			Expect(err).To(HaveOccurred())
		})
	})
})