database

type BackupInfo struct {
	CreatedAt  time.Time `json:"createdAt"`
	Name       string    `json:"name"`
	Path       string    `json:"path"`
	QuickCheck string    `json:"quickCheck"`
	Reason     string    `json:"reason,omitempty"`
	Size       int64     `json:"size"`
	Valid      bool      `json:"valid"`
	Manual     bool      `json:"manual"`
}

type BrowseCursor struct {
	SortValue  string
	SortMode   string
	Phase      string
	DirName    string
	LastID     int64
	TotalFiles int
	TotalDirs  int
}

type BrowseDirCountOptions struct {
	PathPrefix string
	Systems    []systemdefs.System
}

type BrowseDirectoriesOptions struct {
	PathPrefix string
	AfterName  string
	Systems    []systemdefs.System
	Limit      int
}

type BrowseDirectoryResult struct {
	Name      string
	SystemIDs []string
	FileCount int
}

type BrowseFileCountOptions struct {
	Letter     *string
	PathPrefix string
	Systems    []systemdefs.System
}

type BrowseFilesOptions struct {
	Cursor     *BrowseCursor
	Letter     *string
	PathPrefix string
	Sort       string
	Systems    []systemdefs.System
	Limit      int
}

type BrowseIndexBucket struct {
	Key       string
	SortValue string
	LastID    int64
	Count     int
	Offset    int
	AtStart   bool
}

type BrowseIndexOptions struct {
	PathPrefix string
	Sort       string
	Systems    []systemdefs.System
}

type BrowseIndexResult struct {
	Scheme     string
	SortMode   string
	Buckets    []BrowseIndexBucket
	TotalFiles int
}

type BrowseRouteCount struct {
	Path         string
	SystemIDs    []string
	FileCount    int
	CountUnknown bool
}

type BrowseRouteCountsOptions struct {
	Systems []systemdefs.System
	Routes  []string
}

type BrowseSystemRootCandidates struct {
	Children map[string][]string
	HasMedia map[string]bool
}

type BrowseSystemRootCandidatesOptions struct {
	Roots   []string
	Systems []systemdefs.System
}

type BrowseVirtualScheme struct {
	Scheme    string
	SystemIDs []string
	FileCount int
}

type BrowseVirtualSchemesOptions struct {
	Systems []systemdefs.System
}

type Client struct {
	ClientID   string `json:"clientId"`
	ClientName string `json:"clientName"`
	AuthToken  string `json:"-"`
	// Role is the client's permission role ("admin" or "member"), chosen
	// at pairing approval. See pkg/api/permissions.
	Role       string `json:"role"`
	PairingKey []byte `json:"-"`
	DBID       int64  `json:"-"`
	CreatedAt  int64  `json:"createdAt"`
	LastSeenAt int64  `json:"lastSeenAt"`
}

type Conn struct {
	// contains filtered or unexported fields
}

type Database struct {
	UserDB  UserDBI
	MediaDB MediaDBI
}

type FileInfo struct {
	SystemID string
	Path     string
	Name     string
}

type GenericDBI interface {
	Open() error
	UnsafeGetSQLDb() *sql.DB
	Truncate() error
	Allocate() error
	MigrateUp() error
	Vacuum() error
	Close() error
	GetDBPath() string
}

type HistoryEntry struct {
	Time           time.Time  `json:"time"`
	CreatedAt      time.Time  `json:"createdAt,omitempty"`
	SyncedAt       *time.Time `json:"syncedAt,omitempty"`
	DeviceID       *string    `json:"deviceId,omitempty"`
	BootUUID       string     `json:"bootUuid,omitempty"`
	ID             string     `json:"uuid,omitempty"`
	TokenData      string     `json:"tokenData"`
	TokenValue     string     `json:"tokenValue"`
	TokenID        string     `json:"tokenId"`
	Type           string     `json:"type"`
	DBID           int64      `db:"DBID" json:"id"`
	MonotonicStart int64      `json:"monotonicStart,omitempty"`
	Success        bool       `json:"success"`
	ClockReliable  bool       `json:"clockReliable"`
	IsDeleted      bool       `json:"isDeleted,omitempty"`
}

type InboxMessage struct {
	CreatedAt time.Time `json:"createdAt"`
	Title     string    `json:"title"`
	Body      string    `json:"body,omitempty"`
	Category  string    `json:"category,omitempty"`
	DBID      int64     `json:"id"`
	Severity  int       `json:"severity"`
	ProfileID int64     `json:"profileId"`
}

type JournalMode string

type Mapping struct {
	Label    string `json:"label"`
	Type     string `json:"type"`
	Match    string `json:"match"`
	Pattern  string `json:"pattern"`
	Override string `json:"override"`
	DBID     int64
	Added    int64 `json:"added"`
	Enabled  bool  `json:"enabled"`
}

type Media struct {
	Path           string
	ParentDir      string
	SortName       string // write-once copy of MediaTitles.Name; titles never update so no propagation is needed
	DBID           int64
	MediaTitleDBID int64
	SystemDBID     int64
	IsMissing      bool
}

type MediaBlob struct {
	Hash        string
	ContentType string
	Data        []byte
	DBID        int64
}

type MediaDBI interface {
	GenericDBI
	BeginTransaction(batchEnabled bool) error
	CommitTransaction() error
	CommitTransactionWithOptions(options TransactionOptions) error
	FlushBatchInserters() error
	RollbackTransaction() error
	Exists() bool
	UpdateLastGenerated() error
	GetLastGenerated() (time.Time, error)

	SetOptimizationStatus(status string) error
	GetOptimizationStatus() (string, error)
	SetOptimizationStep(step string) error
	GetOptimizationStep() (string, error)
	IsOptimizing() bool
	BeginBrowseCacheRebuild()
	EndBrowseCacheRebuild()
	RunBackgroundOptimization(statusCallback func(optimizing bool), pauser *syncutil.Pauser)
	WaitForBackgroundOperations()
	BeginRecovery()
	EndRecovery()
	TrackBackgroundOperation()
	HasBackgroundOperations() bool
	SetIndexingConnBoost(active bool)
	BackgroundOperationDone()

	InvalidateCountCache() error
	RebuildSlugSearchCache() error
	RebuildTagCache() error
	WALCheckpoint() error
	QuickCheck() (bool, error)
	IntegrityReport() []string
	MarkCorrupt(reason string)
	IsMarkedCorrupt() bool
	ClearCorruptMarker() error
	NoteCorruption(err error) bool
	Recreate(keepBackup bool) error

	// On-disk persistence for the rebuilt caches. Persist* writes the
	// current in-memory cache atomically; LoadCached* reads it back at
	// startup and returns (false, nil) on missing/stale/version-mismatch
	// so the caller can fall through to a SQL rebuild.
	PersistTagCache() error
	LoadCachedTagCache() (bool, error)
	PersistSlugSearchCache() error
	LoadCachedSlugSearchCache() (bool, error)

	// IndexGeneration is bumped at the end of every successful indexing
	// run. Persisted cache files embed the value they were built against
	// so a stale cache from a previous run is rejected on next load.
	IndexGeneration() (int64, error)
	BumpIndexGeneration() (int64, error)

	// Slug resolution cache methods
	GetCachedSlugResolution(
		ctx context.Context, systemID, slug string, tagFilters []zapscript.TagFilter,
	) (int64, string, bool)
	SetCachedSlugResolution(
		ctx context.Context, systemID, slug string, tagFilters []zapscript.TagFilter, mediaDBID int64, strategy string,
	) error
	InvalidateSlugCache(ctx context.Context) error
	InvalidateSlugCacheForSystems(ctx context.Context, systemIDs []string) error
	GetMediaByDBID(ctx context.Context, mediaDBID int64) (SearchResultWithCursor, error)
	GetZapScriptTagsBySystemAndPath(ctx context.Context, systemID, path string) ([]TagInfo, error)

	SetIndexingCacheSize(enable bool)
	DropSecondaryIndexes() error
	CreateSecondaryIndexes() error
	SetIndexingStatus(status string) error
	GetIndexingStatus() (string, error)
	GetIndexResumeAttempts() (int, error)
	IncrementIndexResumeAttempts() (int, error)
	ResetIndexResumeAttempts() error
	GetIndexResumeCheckpoint() (string, error)
	SetIndexResumeCheckpoint(checkpoint string) error
	SetScrapingStatus(status string) error
	GetScrapingStatus() (string, error)
	SetScrapingOperation(operation ScrapingOperation) error
	GetScrapingOperation() (ScrapingOperation, bool, error)
	ClearScrapingOperation() error
	SetLastIndexedSystem(systemID string) error
	GetLastIndexedSystem() (string, error)
	SetIndexingSystems(systemIDs []string) error
	GetIndexingSystems() ([]string, error)
	TruncateSystems(systemIDs []string) error

	// Scanner staging: files are streamed into staging tables inside the open
	// batch transaction, then folded into the media tables with set-based SQL
	// so indexing memory does not scale with database size.
	StageScannedMedia(media *ScanStagedMedia) error
	ReconcileStagedSystem(ctx context.Context, systemID string, opts ScanReconcileOpts) (ScanReconcileStats, error)
	ClearScanStage() error
	SeedCanonicalTagDefinitions(ctx context.Context) error

	SearchMediaPathExact(ctx context.Context, systems []systemdefs.System, query string) ([]SearchResult, error)
	SearchMediaWithFilters(ctx context.Context, filters *SearchFilters) ([]SearchResultWithCursor, error)
	SearchMediaBySlug(
		ctx context.Context, systemID string, slug string, tags []zapscript.TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySecondarySlug(
		ctx context.Context, systemID string, secondarySlug string, tags []zapscript.TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySlugPrefix(
		ctx context.Context, systemID string, slugPrefix string, tags []zapscript.TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySlugIn(
		ctx context.Context, systemID string, slugs []string, tags []zapscript.TagFilter,
	) ([]SearchResultWithCursor, error)
	GetTitlesWithPreFilter(
		ctx context.Context, systemID string, minLength, maxLength, minWordCount, maxWordCount int,
	) ([]MediaTitle, error)
	GetLaunchCommandForMedia(ctx context.Context, systemID, path string) (string, error)
	// SearchMediaByProperty finds media by a stored property value. systemID is an optional scope.
	SearchMediaByProperty(ctx context.Context, systemID, property, value string) ([]SearchResult, error)
	// HasMediaPropertyForPath reports whether indexed media already has a stored property.
	HasMediaPropertyForPath(ctx context.Context, systemID, path, property string) (bool, error)
	GetTags(ctx context.Context, systems []systemdefs.System) ([]TagInfo, error)
	GetAllUsedTags(ctx context.Context) ([]TagInfo, error)
	PopulateSystemTagsCache(ctx context.Context) error
	PopulateSystemTagsCacheForSystems(ctx context.Context, systems []systemdefs.System) error
	AnalyzeApproximate() error
	RefreshSlugSearchCacheForSystems(ctx context.Context, systemIDs []string) error
	GetSystemTagsCached(ctx context.Context, systems []systemdefs.System) ([]TagInfo, error)
	InvalidateSystemTagsCache(ctx context.Context, systems []systemdefs.System) error
	SearchMediaPathGlob(systems []systemdefs.System, query string) ([]SearchResult, error)

	// Browse methods for directory-style navigation of indexed content
	BrowseDirectories(ctx context.Context, opts BrowseDirectoriesOptions) ([]BrowseDirectoryResult, error)
	BrowseDirCount(ctx context.Context, opts BrowseDirCountOptions) (int, error)
	BrowseFiles(ctx context.Context, opts *BrowseFilesOptions) ([]SearchResultWithCursor, error)
	BrowseFileCount(ctx context.Context, opts BrowseFileCountOptions) (int, error)
	BrowseIndex(ctx context.Context, opts BrowseIndexOptions) (BrowseIndexResult, error)
	BrowseVirtualSchemes(ctx context.Context, opts BrowseVirtualSchemesOptions) ([]BrowseVirtualScheme, error)
	BrowseRootCounts(ctx context.Context, rootDirs []string) (map[string]*int, error)
	BrowseRouteCounts(ctx context.Context, opts BrowseRouteCountsOptions) (map[string]BrowseRouteCount, error)
	BrowseSystemRootCandidates(
		ctx context.Context, opts BrowseSystemRootCandidatesOptions,
	) (result BrowseSystemRootCandidates, cacheReady bool, err error)
	PopulateBrowseCache(ctx context.Context) error
	PopulateBrowseCacheForSystems(ctx context.Context, systemIDs []string) error
	BrowseCacheNeedsRebuild(ctx context.Context) (bool, error)

	IndexedSystems() ([]string, error)
	SystemIndexed(system *systemdefs.System) bool
	RandomGame(ctx context.Context, systems []systemdefs.System) (SearchResult, error)
	RandomGameWithQuery(ctx context.Context, query *MediaQuery) (SearchResult, error)
	GetTotalMediaCount() (int, error)
	HasAnyMedia() (bool, error)
	GetMissingMediaCount() (int, error)
	GetScrapedMediaCount(ctx context.Context, scraperID string) (int, error)
	GetTotalScrapedMediaCount(ctx context.Context) (int, error)

	FindSystem(row System) (System, error)
	FindSystemBySystemID(systemID string) (System, error)
	InsertSystem(row System) (System, error)
	FindOrInsertSystem(row System) (System, error)

	FindMediaTitle(row *MediaTitle) (MediaTitle, error)
	InsertMediaTitle(row *MediaTitle) (MediaTitle, error)
	FindOrInsertMediaTitle(row *MediaTitle) (MediaTitle, error)

	FindMedia(row Media) (Media, error)
	InsertMedia(row Media) (Media, error)
	FindOrInsertMedia(row Media) (Media, error)
	DeleteMediaTag(mediaDBID, tagDBID int64) error
	TemporaryRepairJobsPending(ctx context.Context) (bool, error)

	FindTagType(row TagType) (TagType, error)
	InsertTagType(row TagType) (TagType, error)
	FindOrInsertTagType(row TagType) (TagType, error)

	FindTag(row Tag) (Tag, error)
	InsertTag(row Tag) (Tag, error)
	FindOrInsertTag(row Tag) (Tag, error)

	FindMediaTag(row MediaTag) (MediaTag, error)
	InsertMediaTag(row MediaTag) (MediaTag, error)
	FindOrInsertMediaTag(row MediaTag) (MediaTag, error)

	// CleanMediaOrphans removes Media rows where IsMissing=1 together with
	// their associated MediaTags and MediaProperties.  MediaTitles that are
	// no longer referenced by any Media row are also removed (including their
	// MediaTitleTags and MediaTitleProperties).  Tags unreferenced by any join
	// table are pruned.  A VACUUM is issued on success.
	//
	// Returns the count of Media rows deleted, or one of ErrIndexingInProgress,
	// ErrOptimizationInProgress, or ErrTransactionActive when the operation
	// cannot safely run.
	CleanMediaOrphans(ctx context.Context) (int64, error)

	GetAllSystems() ([]System, error)
	// GetExistingMediaUserData returns user-authored data (favourites, launcher
	// overrides) already stored in media.db, for the one-time UserDB backfill.
	GetExistingMediaUserData(ctx context.Context) ([]MediaUserData, error)

	// Per-system query methods for scrapers
	GetTitlesBySystemID(systemID string) ([]TitleWithSystem, error)
	GetMediaBySystemID(systemID string) ([]MediaWithFullPath, error)

	// FindMediaBySystemAndPath returns the Media row matching systemDBID and path,
	// or nil, nil when no row is found.
	FindMediaBySystemAndPath(ctx context.Context, systemDBID int64, path string) (*Media, error)
	FindMediaBySystemAndPaths(ctx context.Context, systemDBID int64, paths []string) (map[string]Media, error)
	// FindMediaIDsByPaths returns the system ID, path, and DBID of every Media
	// row whose Path is in paths, in a single query across all systems.
	FindMediaIDsByPaths(ctx context.Context, paths []string) ([]MediaPathID, error)
	// FindSingleContainerLaunchMedia returns the one logical launch target in the
	// direct contents of containerPath for systemDBID, or nil, nil when the
	// container is empty, nested-only, or ambiguous.
	FindSingleContainerLaunchMedia(ctx context.Context, systemDBID int64, containerPath string) (*Media, error)
	// ResolveSingletonContainerAliases resolves the given candidate child
	// directories for systemDBID in a single batch query, returning one
	// SingletonContainerAlias per candidate that collapses to a single launch
	// target. Candidates with nested subdirs (recursive FileCount exceeding
	// their direct media rows) or ambiguous contents are omitted.
	// ZapScriptTags are populated via in-memory disambiguation (same approach
	// as the search path) and will be empty for unambiguous titles.
	ResolveSingletonContainerAliases(
		ctx context.Context, systemDBID int64, candidates []SingletonAliasCandidate,
	) ([]SingletonContainerAlias, error)

	// FindMediaBySystemAndPathFold returns the Media row matching systemDBID and
	// path using a case-insensitive path comparison, or nil, nil when no row is
	// found. Intended for scrapers where the incoming path casing may differ from
	// what the indexer recorded (e.g. Windows filesystem with mixed-case system
	// directory names).
	FindMediaBySystemAndPathFold(ctx context.Context, systemDBID int64, path string) (*Media, error)

	// FindMediaBySystemAndPathSuffix returns all Media rows for the given system
	// whose Path ends with "/" + filename. Used when companion XML child paths are
	// root-relative (./file.rom) and the indexed path may be in any subdirectory.
	FindMediaBySystemAndPathSuffix(ctx context.Context, systemDBID int64, filename string) ([]Media, error)

	// MediaHasTag returns true when the Media row has a tag whose full string
	// (type:value) equals tagValue.
	MediaHasTag(ctx context.Context, mediaDBID int64, tagValue string) (bool, error)

	// GetScrapedMediaIDs returns media DBIDs in a system already marked as scraped
	// by scraperID.
	GetScrapedMediaIDs(ctx context.Context, scraperID string, systemDBID int64) (map[int64]struct{}, error)

	// GetScrapeRunMediaIDs returns media DBIDs in a system completed during a
	// specific scraper run.
	GetScrapeRunMediaIDs(ctx context.Context, scraperID, runID string, systemDBID int64) (map[int64]struct{}, error)

	// ClearScrapeRunMarkers removes per-run completion markers after a scraper
	// operation reaches a terminal state.
	ClearScrapeRunMarkers(ctx context.Context, scraperID, runID string) error

	// UpsertMediaTags writes tags to MediaTags for a specific Media row.
	// Exclusive types (TagTypes.IsExclusive=1) delete existing tags of that type
	// for the entity before inserting; additive types use INSERT OR IGNORE.
	UpsertMediaTags(ctx context.Context, mediaDBID int64, tags []TagInfo) error

	// UpsertMediaTitleTags writes tags to MediaTitleTags for a specific MediaTitle row.
	// Exclusive/additive behaviour is identical to UpsertMediaTags.
	UpsertMediaTitleTags(ctx context.Context, mediaTitleDBID int64, tags []TagInfo) error

	// RecomputeTitleDisambiguation recomputes the stored disambiguating tag types
	// for the given MediaTitle DBIDs. Called after writes that change a title's
	// media or tags so reads can rely on the stored, title-global value.
	RecomputeTitleDisambiguation(ctx context.Context, titleDBIDs []int64) error

	// RecomputeSystemDisambiguation recomputes the stored disambiguating tag types
	// for every MediaTitle belonging to the given system DBIDs. Used at index time.
	RecomputeSystemDisambiguation(ctx context.Context, systemDBIDs []int64) error

	// UpsertMediaTitleProperties upserts properties into MediaTitleProperties.
	// Conflicts on (MediaTitleDBID, TypeTagDBID) update data columns; DBID is preserved.
	UpsertMediaTitleProperties(ctx context.Context, mediaTitleDBID int64, props []MediaProperty) error

	// UpsertMediaProperties upserts properties into MediaProperties.
	// Conflicts on (MediaDBID, TypeTagDBID) update data columns; DBID is preserved.
	UpsertMediaProperties(ctx context.Context, mediaDBID int64, props []MediaProperty) error

	// ApplyScrapeResult atomically writes all scraper metadata for a Media row and
	// writes the sentinel tag last.
	ApplyScrapeResult(ctx context.Context, mediaDBID, mediaTitleDBID int64, write *ScrapeWrite) error

	// ConsumeScrapeImageChanges returns and clears systems with materially changed
	// image properties. all is true when tracking could not resolve a safe targeted
	// set and callers must conservatively invalidate the full cache.
	ConsumeScrapeImageChanges() (systems []string, all bool)

	// FindMediaTitlesWithoutSentinel returns MediaTitle rows for the given system
	// that have no Media row with the given sentinel tag value.
	FindMediaTitlesWithoutSentinel(ctx context.Context, systemDBID int64, sentinelTag string) ([]MediaTitle, error)

	// FindMediaTitleByDBID returns the MediaTitle with the given DBID,
	// or nil, nil when no row is found.
	FindMediaTitleByDBID(ctx context.Context, dbid int64) (*MediaTitle, error)

	// FindMediaTitleBySystemAndSlug returns the MediaTitle matching systemDBID and
	// slug, or nil, nil when no row is found.
	FindMediaTitleBySystemAndSlug(ctx context.Context, systemDBID int64, slug string) (*MediaTitle, error)

	// GetMediaTitleProperties returns all properties for a MediaTitle row,
	// with TypeTagDBID resolved to the tag value string. Binary blobs are capped
	// at MaxMediaPropertyBinaryBytes; larger blobs populate BlobSize but not Binary.
	GetMediaTitleProperties(ctx context.Context, mediaTitleDBID int64) ([]MediaProperty, error)
	GetMediaTitlePropertiesByMediaTitleDBIDs(
		ctx context.Context, mediaTitleDBIDs []int64,
	) (map[int64][]MediaProperty, error)
	GetMediaTitlePropertyMetadata(ctx context.Context, mediaTitleDBID int64) ([]MediaProperty, error)
	GetMediaTitlePropertyMetadataByMediaTitleDBIDs(
		ctx context.Context, mediaTitleDBIDs []int64,
	) (map[int64][]MediaProperty, error)

	// GetMediaProperties returns all properties for a Media row,
	// with TypeTagDBID resolved to the tag value string. Binary blobs are capped
	// at MaxMediaPropertyBinaryBytes; larger blobs populate BlobSize but not Binary.
	GetMediaProperties(ctx context.Context, mediaDBID int64) ([]MediaProperty, error)
	GetMediaPropertiesByMediaDBIDs(ctx context.Context, mediaDBIDs []int64) (map[int64][]MediaProperty, error)
	GetMediaPropertyMetadata(ctx context.Context, mediaDBID int64) ([]MediaProperty, error)
	GetMediaPropertyMetadataByMediaDBIDs(ctx context.Context, mediaDBIDs []int64) (map[int64][]MediaProperty, error)

	// DeleteMediaTitleProperty removes a single property row from MediaTitleProperties
	// identified by (mediaTitleDBID, typeTagDBID). A no-op if the row does not exist.
	DeleteMediaTitleProperty(ctx context.Context, mediaTitleDBID int64, typeTagDBID int64) error

	// DeleteMediaProperty removes a single property row from MediaProperties
	// identified by (mediaDBID, typeTagDBID). A no-op if the row does not exist.
	DeleteMediaProperty(ctx context.Context, mediaDBID int64, typeTagDBID int64) error

	// GetMediaWithTitleAndSystem fetches a Media record together with its parent
	// MediaTitle and System via a single JOIN query. Returns nil, nil when no
	// Media row with the given DBID exists. IsMissing is NOT filtered — metadata
	// remains accessible for missing files.
	GetMediaWithTitleAndSystem(ctx context.Context, mediaDBID int64) (*MediaFullRow, error)
	GetMediaWithTitleAndSystemByIDs(ctx context.Context, mediaDBIDs []int64) (map[int64]MediaFullRow, error)

	// GetMediaTagsByMediaDBID returns the file-level tags (MediaTags) for a
	// single Media row. Does not include title-level tags.
	GetMediaTagsByMediaDBID(ctx context.Context, mediaDBID int64) ([]TagInfo, error)
	GetMediaTagsByMediaDBIDs(ctx context.Context, mediaDBIDs []int64) (map[int64][]TagInfo, error)

	// GetMediaTitleTagsByMediaTitleDBID returns the title-level tags
	// (MediaTitleTags) for a single MediaTitle row.
	GetMediaTitleTagsByMediaTitleDBID(ctx context.Context, mediaTitleDBID int64) ([]TagInfo, error)
	GetMediaTitleTagsByMediaTitleDBIDs(ctx context.Context, mediaTitleDBIDs []int64) (map[int64][]TagInfo, error)

	// UpsertMediaBlob inserts a blob into MediaBlobs when no row with the same
	// SHA-256 hash of framed content type and bytes already exists, then returns its DBID.
	// Hash computation is performed internally; callers supply only contentType and raw data.
	UpsertMediaBlob(ctx context.Context, contentType string, data []byte) (int64, error)

	// GetMediaBlob returns the MediaBlob row for the given DBID,
	// or nil, nil when not found.
	GetMediaBlob(ctx context.Context, blobDBID int64) (*MediaBlob, error)
	GetMediaBlobDataCapped(ctx context.Context, blobDBID int64, maxBytes int64) ([]byte, string, error)

	// PruneOrphanedBlobs deletes MediaBlobs rows that are not referenced by
	// any MediaTitleProperties or MediaProperties row. Returns the count of
	// rows deleted. Safe to call from CleanMediaOrphans.
	PruneOrphanedBlobs(ctx context.Context) (int64, error)
}

type MediaFullRow struct {
	System System
	Media
	Title MediaTitle
}

type MediaHistoryEntry struct {
	StartTime      time.Time  `json:"startTime"`
	UpdatedAt      time.Time  `json:"updatedAt,omitempty"`
	CreatedAt      time.Time  `json:"createdAt,omitempty"`
	EndTime        *time.Time `json:"endTime,omitempty"`
	SyncedAt       *time.Time `json:"syncedAt,omitempty"`
	DeviceID       *string    `json:"deviceId,omitempty"`
	ProfileID      *string    `json:"profileId,omitempty"`
	BootUUID       string     `json:"bootUuid,omitempty"`
	ClockSource    string     `json:"clockSource,omitempty"`
	SystemID       string     `json:"systemId"`
	ID             string     `json:"uuid,omitempty"`
	LauncherID     string     `json:"launcherId"`
	SystemName     string     `json:"systemName"`
	MediaPath      string     `json:"mediaPath"`
	MediaName      string     `json:"mediaName"`
	Tags           []string   `json:"tags,omitempty"`
	DBID           int64      `db:"DBID" json:"id"`
	WallDuration   int        `json:"wallDuration"`
	DurationSec    int        `json:"durationSec"`
	MonotonicStart int64      `json:"monotonicStart,omitempty"`
	PlayTime       int        `json:"playTime"`
	TimeSkewFlag   bool       `json:"timeSkewFlag"`
	ClockReliable  bool       `json:"clockReliable"`
	IsDeleted      bool       `json:"isDeleted,omitempty"`
}

type MediaHistorySyncRef struct {
	UpdatedAt time.Time
	DBID      int64
}

type MediaHistoryTopEntry struct {
	LastPlayedAt  time.Time
	SystemID      string
	SystemName    string
	MediaName     string
	MediaPath     string
	TotalPlayTime int
	SessionCount  int
}

type MediaIdentity struct {
	Name string
	Tags []string
}

type MediaPathID struct {
	SystemID string
	Path     string
	DBID     int64
}

type MediaProperty struct {
	BlobDBID    *int64
	TypeTag     string
	Text        string
	ContentType string
	Binary      []byte
	TypeTagDBID int64
	BlobSize    int64
}

type MediaQuery struct {
	PathGlob   string                `json:"pathGlob,omitempty"`
	PathPrefix string                `json:"pathPrefix,omitempty"`
	Systems    []string              `json:"systems,omitempty"`
	Tags       []zapscript.TagFilter `json:"tags,omitempty"`
}

type MediaTag struct {
	DBID      int64
	MediaDBID int64
	TagDBID   int64
}

type MediaTagLink struct {
	MediaDBID int64
	TagDBID   int64
}

type MediaTitle struct {
	Slug string
	Name string
	// DisambiguationTypes is the title's stored comma-separated set of tag types
	// whose values differ across its non-missing media (see RecomputeTitleDisambiguation).
	DisambiguationTypes string
	SecondarySlug       sql.NullString
	DBID                int64
	SystemDBID          int64
	SlugLength          int
	SlugWordCount       int
}

type MediaUserData struct {
	SystemID         string
	Path             string
	LauncherOverride string
	// MediaName and Tags snapshot the scanner's identity for this path at
	// write time (display name + disambiguating type:value tags), so the
	// row stays matchable to a canonical game after MediaDB is rebuilt or
	// the file disappears. Empty when no scanner entry existed.
	MediaName  string
	Tags       []string
	DBID       int64
	CreatedAt  int64
	UpdatedAt  int64
	IsFavorite bool
}

type MediaWithFullPath struct {
	Path           string
	ParentDir      string
	TitleSlug      string
	SystemID       string
	SortName       string
	DBID           int64
	MediaTitleDBID int64
	IsMissing      bool
}

type Profile struct {
	LimitsEnabled *bool   `json:"limitsEnabled,omitempty"`
	DailyLimit    *string `json:"dailyLimit,omitempty"`
	SessionLimit  *string `json:"sessionLimit,omitempty"`
	LastUsedAt    *int64  `json:"lastUsedAt,omitempty"`
	ProfileID     string  `json:"profileId"`
	Name          string  `json:"name"`
	Role          string  `json:"role"`
	SwitchID      string  `json:"switchId"`
	PINHash       string  `json:"-"`
	DBID          int64   `json:"-"`
	CreatedAt     int64   `json:"createdAt"`
	UpdatedAt     int64   `json:"updatedAt"`
}

type RestoreInfo struct {
	PreRestoreBackup *BackupInfo `json:"preRestoreBackup,omitempty"`
	RestoredFrom     BackupInfo  `json:"restoredFrom"`
}

type ScanReconcileOpts struct {
	// IncompleteScan means file collection for this system hit errors (an
	// unreadable path, a failed launcher scanner), so the staged set may be a
	// subset of what actually exists. Staged files are still upserted and
	// re-found rows still clear their missing flag, but media absent from the
	// stage keep their current missing state instead of being flagged missing.
	IncompleteScan bool
}

type ScanReconcileStats struct {
	SystemDBID      int64
	TitlesInserted  int64
	TitlesRenamed   int64
	MediaUpserted   int64
	MediaMissing    int64
	TagsInserted    int64
	TagLinksAdded   int64
	TagLinksDeleted int64
	TouchedTitles   int64
	// SystemKnown is false when the system has no DB row and nothing was staged,
	// meaning the reconcile was a no-op and no Systems row was created.
	SystemKnown bool
}

type ScanStagedMedia struct {
	Path          string
	ParentDir     string
	Slug          string
	TitleName     string
	SortName      string
	SecondarySlug string
	Tags          []ScanStagedTag
	Properties    []ScanStagedProperty
	SlugLength    int
	SlugWordCount int
}

type ScanStagedProperty struct {
	Type string
	Name string
	Text string
}

type ScanStagedTag struct {
	Type  string
	Value string
}

type ScrapeResultBatchApplier interface {
	ApplyScrapeResults(ctx context.Context, targets []ScrapeWriteTarget) error
}

type ScrapeWrite struct {
	Sentinel   TagInfo
	MediaTags  []TagInfo
	TitleTags  []TagInfo
	TitleProps []MediaProperty
	MediaProps []MediaProperty
}

type ScrapeWriteTarget struct {
	Write          *ScrapeWrite
	MediaDBID      int64
	MediaTitleDBID int64
}

type ScrapingOperation struct {
	ScraperID string   `json:"scraperId"`
	RunID     string   `json:"runId,omitempty"`
	Systems   []string `json:"systems"`
	Force     bool     `json:"force"`
}

type SearchFilters struct {
	Cursor  *int64                `json:"cursor,omitempty"`
	Letter  *string               `json:"letter,omitempty"`
	Query   string                `json:"query"`
	Systems []systemdefs.System   `json:"systems,omitempty"`
	Tags    []zapscript.TagFilter `json:"tags,omitempty"`
	Limit   int                   `json:"limit"`
}

type SearchResult struct {
	SystemID string
	Name     string
	Path     string
	MediaID  int64
}

type SearchResultWithCursor struct {
	SystemID string
	Name     string
	Path     string
	// DisambiguationTypes is the title's stored comma-separated set of tag types
	// that distinguish its variants (see RecomputeTitleDisambiguation). Empty for
	// titles with no variants, which lets the read path skip the tag lookup.
	DisambiguationTypes string
	SortValue           string
	SortMode            string
	Tags                []TagInfo
	ZapScriptTags       []TagInfo
	MediaID             int64
	MediaTitleID        int64 `json:"-"`
	HasCover            bool
}

type SingletonAliasCandidate struct {
	ChildDir  string
	FileCount int
}

type SingletonContainerAlias struct {
	ChildDir      string
	Tags          []TagInfo
	ZapScriptTags []TagInfo
	Row           MediaFullRow
	HasCover      bool
}

type System struct {
	SystemID string
	Name     string
	DBID     int64
}

type Tag struct {
	Tag         string
	DisplayName string
	DBID        int64
	TypeDBID    int64
}

type TagInfo struct {
	Tag   string `json:"tag"`
	Type  string `json:"type"`
	Label string `json:"label,omitempty"`
	Count int64  `json:"count,omitempty"`
}

type TagType struct {
	Type        string
	DBID        int64
	IsExclusive bool
}

type TitleWithSystem struct {
	Slug       string
	Name       string
	SystemID   string
	DBID       int64
	SystemDBID int64
}

type TransactionOptions struct {
	WALCheckpoint WALCheckpointMode
}

type UserDBI interface {
	GenericDBI
	AddHistory(entry *HistoryEntry) error
	GetHistory(lastID int64) ([]HistoryEntry, error)
	CleanupHistory(retentionDays int) (int64, error)
	AddMediaHistory(entry *MediaHistoryEntry) (int64, error)
	UpdateMediaHistoryTime(dbid int64, playTime int) error
	UpdateMediaHistoryIdentity(dbid int64, identity MediaIdentity) error
	CloseMediaHistory(dbid int64, endTime time.Time, playTime int) error
	GetMediaHistory(systemIDs []string, lastID int64, limit int) ([]MediaHistoryEntry, error)
	GetLatestMediaHistory() (MediaHistoryEntry, bool, error)
	GetMediaHistoryTop(systemIDs []string, since *time.Time, limit int) ([]MediaHistoryTopEntry, error)
	CloseHangingMediaHistory() error
	CleanupMediaHistory(retentionDays int, requireSynced bool) (int64, error)
	BackfillMediaHistoryUUIDs() (int64, error)
	ResetMediaHistorySyncAfter(watermark *time.Time) error
	GetMediaHistorySyncBatch(after time.Time, afterDBID int64, limit int) ([]MediaHistoryEntry, error)
	MarkMediaHistorySynced(refs []MediaHistorySyncRef, syncedAt time.Time) error
	HealTimestamps(bootUUID string, trueBootTime time.Time) (int64, error)
	SumMediaPlayTimeForDay(dayStart time.Time) (int64, error)
	SumMediaPlayTimeForDayByProfile(dayStart time.Time, profileID string) (int64, error)
	AddMapping(m *Mapping) error
	GetMapping(id int64) (Mapping, error)
	DeleteMapping(id int64) error
	UpdateMapping(id int64, m *Mapping) error
	GetAllMappings() ([]Mapping, error)
	GetEnabledMappings() ([]Mapping, error)
	GetMediaUserData(systemID, path string) (MediaUserData, bool, error)
	SetMediaUserFavorite(systemID, path string, favorite bool) error
	SetMediaUserLauncherOverride(systemID, path, launcherID string) error
	SetMediaUserSnapshot(systemID, path, mediaName string, tags []string) error
	UpsertMediaUserData(data *MediaUserData) error
	DeleteMediaUserData(systemID, path string) error
	ListMediaUserData() ([]MediaUserData, error)
	UpdateZapLinkHost(host string, zapscript int) error
	GetZapLinkHost(host string) (bool, bool, error)
	GetSupportedZapLinkHosts() ([]string, error)
	PruneExpiredZapLinkHosts(olderThan time.Duration) (int64, error)
	UpdateZapLinkCache(url string, zapscript string) error
	GetZapLinkCache(url string) (string, error)
	AddInboxMessage(msg *InboxMessage) (*InboxMessage, error)
	GetInboxMessages() ([]InboxMessage, error)
	DeleteInboxMessage(id int64) error
	DeleteAllInboxMessages() (int64, error)
	CreateClient(c *Client) error
	GetClientByToken(authToken string) (*Client, error)
	ListClients() ([]Client, error)
	ReplaceAllClients(clients []Client) error
	DeleteClient(clientID string) error
	UpdateClientLastSeen(authToken string, lastSeenAt int64) error
	CountClients() (int, error)
	CreateProfile(p *Profile) error
	GetProfile(profileID string) (*Profile, error)
	GetProfileBySwitchID(switchID string) (*Profile, error)
	ListProfiles() ([]Profile, error)
	UpdateProfile(p *Profile) error
	ActivateProfile(profileID string, lastUsedAt int64) error
	DeleteProfile(profileID string) error
	SetDeviceState(key, value string) error
	GetDeviceState(key string) (string, bool, error)
	DeleteDeviceState(key string) error
	Backup(reason string, manual bool) (BackupInfo, error)
	BackupForTransfer(ctx context.Context, reason string) (BackupInfo, func() error, error)
	EnsureRecentBackup(maxAge time.Duration) (BackupInfo, bool, error)
	ListBackups() ([]BackupInfo, error)
	RestoreBackup(name string) (RestoreInfo, error)
	IntegrityReport() []string
	MarkCorrupt(reason string)
	IsMarkedCorrupt() bool
	ClearCorruptMarker() error
	NoteCorruption(err error) bool
	RecoverFromCorruption() (RestoreInfo, error)
}

type WALCheckpointMode int