database

type BrowseCursor struct {
	SortValue string
	LastID    int64
}

type BrowseDirectoryResult struct {
	Name      string
	FileCount int
}

type BrowseFilesOptions struct {
	Cursor     *BrowseCursor
	Letter     *string
	PathPrefix string
	Sort       string
	Limit      int
}

type BrowseVirtualScheme struct {
	Scheme    string // e.g., "steam://"
	FileCount int
}

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
	DBID           int64
	MediaTitleDBID int64
	SystemDBID     int64
	IsMissing      bool
}

type MediaDBI interface {
	GenericDBI
	BeginTransaction(batchEnabled bool) error
	CommitTransaction() 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

	// 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)
	SetLastIndexedSystem(systemID string) error
	GetLastIndexedSystem() (string, error)
	SetIndexingSystems(systemIDs []string) error
	GetIndexingSystems() ([]string, error)
	TruncateSystems(systemIDs []string) error

	SearchMediaPathExact(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
	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, pathPrefix string) ([]BrowseDirectoryResult, error)
	BrowseFiles(ctx context.Context, opts *BrowseFilesOptions) ([]SearchResultWithCursor, error)
	BrowseFileCount(ctx context.Context, pathPrefix string, letter *string) (int, error)
	BrowseVirtualSchemes(ctx context.Context) ([]BrowseVirtualScheme, error)
	BrowseRootCounts(ctx context.Context, rootDirs []string) (map[string]*int, error)
	PopulateBrowseCache(ctx context.Context) error

	IndexedSystems() ([]string, error)
	SystemIndexed(system *systemdefs.System) bool
	RandomGame(systems []systemdefs.System) (SearchResult, error)
	RandomGameWithQuery(query *MediaQuery) (SearchResult, error)
	GetTotalMediaCount() (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) error
	DeleteMediaTags(mediaDBID int64) error
	DeleteMediaTag(mediaDBID, tagDBID int64) 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

	// 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)
	GetMediaTagsBySystemID(systemID string) ([]MediaTagLink, error)
}

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 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
	SecondarySlug sql.NullString
	DBID          int64
	SystemDBID    int64
	SlugLength    int
	SlugWordCount int
}

type MediaWithFullPath struct {
	Path           string
	TitleSlug      string
	SystemID       string
	DBID           int64
	MediaTitleDBID int64
}

type ScanState struct {
	SystemIDs     map[string]int
	TitleIDs      map[string]int
	MediaIDs      map[string]int
	MediaTitleIDs map[int]int // Existing media DBID -> MediaTitleDBID for persistent reconciliation
	MediaTagIDs   map[int]map[int]struct{}
	TagTypeIDs    map[string]int
	TagIDs        map[string]int
	MissingMedia  map[int]struct{} // DBIDs of media not yet re-found during scan
	SystemsIndex  int
	TitlesIndex   int
	MediaIndex    int
	TagTypesIndex int
	TagsIndex     int
}

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
}

type SearchResultWithCursor struct {
	SystemID      string
	Name          string
	Path          string
	Tags          []TagInfo
	ZapScriptTags []TagInfo // Disambiguating tags only (tags that differ across sibling variants)
	MediaID       int64
}

type System struct {
	SystemID string
	Name     string
	DBID     int64
}

type Tag struct {
	Tag      string
	DBID     int64
	TypeDBID int64
}

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

type TagType struct {
	Type string
	DBID int64
}

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

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