database

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

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

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
}

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:"-"`
	PairingKey []byte `json:"-"`
	DBID       int64  `json:"-"`
	CreatedAt  int64  `json:"createdAt"`
	LastSeenAt int64  `json:"lastSeenAt"`
}

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)
	RunBackgroundOptimization(statusCallback func(optimizing bool), pauser *syncutil.Pauser)
	WaitForBackgroundOperations()
	TrackBackgroundOperation()
	BackgroundOperationDone()

	InvalidateCountCache() error
	RebuildSlugSearchCache() error
	RebuildTagCache() error
	WALCheckpoint() 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)
	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

	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)
	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)
	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

	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)
	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)
	UpdateMediaTitle(mediaDBID, mediaTitleDBID int64, sortName string) error
	UpdateMediaTitleName(titleDBID int64, name string) error
	UpdateMediaParentDir(mediaDBID int64, parentDir string) error
	DeleteMediaTags(mediaDBID int64) error
	DeleteMediaTag(mediaDBID, tagDBID int64) error
	DeleteMediaTagsByTagIDs(mediaDBID int64, tagDBIDs []int) 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)

	// Missing media methods for persistent indexing
	BulkSetMediaMissing(dbids map[int]struct{}) error
	ResetMissingFlags(systemDBIDs []int) 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)

	// GetMax*ID methods for resume functionality
	GetMaxSystemID() (int64, error)
	GetMaxTitleID() (int64, error)
	GetMaxMediaID() (int64, error)
	GetMaxTagTypeID() (int64, error)
	GetMaxTagID() (int64, error)
	GetMaxMediaTagID() (int64, error)

	// GetAll* methods for populating scan state maps
	GetAllSystems() ([]System, error)
	GetAllMediaTitles() ([]MediaTitle, error)
	GetAllMedia() ([]Media, error)
	GetAllTags() ([]Tag, error)
	GetAllTagTypes() ([]TagType, error)

	// Optimized JOIN query methods for populating scan state
	GetTitlesWithSystems() ([]TitleWithSystem, error)
	GetMediaWithFullPath() ([]MediaWithFullPath, error)

	// Optimized JOIN query methods for selective indexing (excluding specified systems)
	GetSystemsExcluding(excludeSystemIDs []string) ([]System, error)
	GetTitlesWithSystemsExcluding(excludeSystemIDs []string) ([]TitleWithSystem, error)
	GetMediaWithFullPathExcluding(excludeSystemIDs []string) ([]MediaWithFullPath, error)

	// Per-system query methods for lazy loading during resume
	GetTitlesBySystemID(systemID string) ([]TitleWithSystem, error)
	GetMediaBySystemID(systemID string) ([]MediaWithFullPath, error)
	GetMediaWithTagsBySystemID(systemID string, loadMediaTags bool) ([]MediaWithFullPath, error)
	GetMediaTagsBySystemID(systemID string) ([]MediaTagLink, error)
	GetScannerMediaTagsBySystemID(systemID string) ([]MediaTagLink, 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

	// 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"`
	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"`
	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 MediaHistoryTopEntry struct {
	LastPlayedAt  time.Time
	SystemID      string
	SystemName    string
	MediaName     string
	MediaPath     string
	TotalPlayTime int
	SessionCount  int
}

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 MediaWithFullPath struct {
	Path           string
	ParentDir      string
	TitleSlug      string
	SystemID       string
	SortName       string
	TagIDs         []int
	DBID           int64
	MediaTitleDBID int64
}

type ScanState struct {
	SystemIDs          map[string]int
	TitleIDs           map[string]int
	TitleNames         map[int]string
	MediaIDs           map[string]int
	MediaTitleIDs      map[int]int      // Existing media DBID -> MediaTitleDBID for persistent reconciliation
	MediaNeedsSortName map[int]struct{} // Media DBIDs with SortName=” needing a write on next title update
	// Existing media DBID -> per-file display/sort title for persistent reconciliation.
	MediaSortNames  map[int]string
	MediaParentDirs map[int]string
	MediaTagIDs     map[int]map[int]struct{}
	TagTypeIDs      map[string]int
	TagIDs          map[string]int
	UserOwnedTagIDs map[int]bool
	MissingMedia    map[int]struct{} // DBIDs of media not yet re-found during scan
	SystemsIndex    int
	TitlesIndex     int
	MediaIndex      int
	TagTypesIndex   int
	TagsIndex       int
}

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
	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) (int64, error)
	HealTimestamps(bootUUID string, trueBootTime time.Time) (int64, error)
	SumMediaPlayTimeForDay(dayStart time.Time) (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)
	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)
	DeleteClient(clientID string) error
	UpdateClientLastSeen(authToken string, lastSeenAt int64) error
	CountClients() (int, error)
}

type WALCheckpointMode int