database

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

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))
	WaitForBackgroundOperations()
	TrackBackgroundOperation()
	BackgroundOperationDone()

	InvalidateCountCache() error

	// Slug resolution cache methods
	GetCachedSlugResolution(
		ctx context.Context, systemID, slug string, tagFilters []TagFilter,
	) (int64, string, bool)
	SetCachedSlugResolution(
		ctx context.Context, systemID, slug string, tagFilters []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)

	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 []TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySecondarySlug(
		ctx context.Context, systemID string, secondarySlug string, tags []TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySlugPrefix(
		ctx context.Context, systemID string, slugPrefix string, tags []TagFilter,
	) ([]SearchResultWithCursor, error)
	SearchMediaBySlugIn(
		ctx context.Context, systemID string, slugs []string, tags []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
	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)
	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)

	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)

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

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 MediaQuery struct {
	PathGlob   string      `json:"pathGlob,omitempty"`
	PathPrefix string      `json:"pathPrefix,omitempty"`
	Systems    []string    `json:"systems,omitempty"`
	Tags       []TagFilter `json:"tags,omitempty"`
}

type MediaTag struct {
	DBID      int64
	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
	TagTypeIDs    map[string]int
	TagIDs        map[string]int
	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    []TagFilter         `json:"tags,omitempty"`
	Limit   int                 `json:"limit"`
}

type SearchResult struct {
	SystemID string
	Name     string
	Path     string
}

type SearchResultWithCursor struct {
	Year     *string
	SystemID string
	Name     string
	Path     string
	Tags     []TagInfo
	MediaID  int64
}

type System struct {
	SystemID string
	Name     string
	DBID     int64
}

type Tag struct {
	Tag      string
	DBID     int64
	TypeDBID int64
}

type TagFilter struct {
	Type     string      // Tag type (e.g., "lang", "year", "players")
	Value    string      // Tag value (e.g., "en", "1991", "2")
	Operator TagOperator // Operator: AND (default), NOT (-), OR (~)
}

type TagInfo struct {
	Tag  string `json:"tag"`
	Type string `json:"type"`
}

type TagOperator string

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 int) ([]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(lastID, limit int) ([]MediaHistoryEntry, 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)
}