mediascanner.go

// Zaparoo Core
// Copyright (c) 2026 The Zaparoo Project Contributors.
// SPDX-License-Identifier: GPL-3.0-or-later
//
// This file is part of Zaparoo Core.
//
// Zaparoo Core is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Zaparoo Core is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Zaparoo Core.  If not, see <http://www.gnu.org/licenses/>.

package mediascanner

import (
	"context"
	"errors"
	"fmt"
	"io/fs"
	"path/filepath"
	"regexp"
	"runtime"
	"sort"
	"strings"
	"sync/atomic"
	"time"

	"github.com/ZaparooProject/zaparoo-core/v2/pkg/config"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/database"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/database/mediadb"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/database/systemdefs"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/helpers"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/helpers/syncutil"
	"github.com/ZaparooProject/zaparoo-core/v2/pkg/platforms"
	"github.com/charlievieth/fastwalk"
	sqlite3 "github.com/mattn/go-sqlite3"
	"github.com/rs/zerolog/log"
)

// Batch configuration for transaction optimization
const (
	maxFilesPerTransaction   = 10000
	maxSystemsPerTransaction = 10
)

// listNumberingRegex matches file list numbering patterns like "1. ", "01 - ", "42. "
var listNumberingRegex = regexp.MustCompile(`^\d{1,3}[.\s\-]+`)

// detectNumberingPattern returns true if a significant portion of files match list numbering pattern.
// This heuristic detects directory-wide list numbering (e.g., "1. Game.zip", "2. Game.zip")
// to distinguish from legitimate title numbers (e.g., "1942.zip", "007.zip").
//
// Parameters:
//   - files: slice of scan results to analyze
//   - threshold: minimum ratio of matching files (0.0-1.0) to trigger stripping
//   - minFiles: minimum number of files required to apply heuristic
//
// Returns true if >threshold% of files match AND file count >= minFiles.
func detectNumberingPattern(files []platforms.ScanResult, threshold float64, minFiles int) bool {
	if len(files) < minFiles {
		return false // Don't apply heuristic to small file sets
	}

	matchCount := 0
	for _, file := range files {
		filename := filepath.Base(file.Path)
		if listNumberingRegex.MatchString(filename) {
			matchCount++
		}
	}

	return float64(matchCount)/float64(len(files)) > threshold
}

type PathResult struct {
	Path   string
	System systemdefs.System
}

// FindPath case-insensitively finds a file/folder at a path and returns the actual filesystem case.
// On case-insensitive filesystems (Windows, macOS), this ensures we get the real case from the filesystem
// rather than preserving the input case, which prevents case-mismatch issues during string comparisons.
//
// This function recursively normalizes the entire path from root to leaf to ensure all components
// match the actual filesystem case.
//
// Special handling:
//   - Linux: Prefers exact match before case-insensitive match (handles File.txt vs file.txt)
//   - Windows: Handles 8.3 short names (PROGRA~1) via fallback to os.Stat
//   - All platforms: Works with symlinks, UNC paths, network drives
func FindPath(ctx context.Context, path string) (string, error) {
	// Check if path exists first
	if _, err := statWithContext(ctx, path); err != nil {
		return "", fmt.Errorf("path does not exist: %s", path)
	}

	// Get absolute path to ensure we have a complete path
	absPath, err := filepath.Abs(path)
	if err != nil {
		return "", fmt.Errorf("failed to get absolute path: %w", err)
	}

	// Extract volume (Windows: "C:", UNC: "\\server\share", Unix: "")
	volume := filepath.VolumeName(absPath)

	// Start building result from volume/root
	currentPath := volume
	if currentPath == "" {
		// Unix absolute path - start from root
		if filepath.IsAbs(absPath) {
			currentPath = string(filepath.Separator)
		}
	} else {
		// Windows: ensure volume ends with separator (C: -> C:\)
		// filepath.Join("C:", "Users") -> "C:Users" (relative, wrong!)
		// filepath.Join("C:\", "Users") -> "C:\Users" (absolute, correct!)
		if !strings.HasSuffix(currentPath, string(filepath.Separator)) {
			currentPath += string(filepath.Separator)
		}
	}

	// Get relative path (everything after volume)
	relPath := absPath[len(volume):]

	// Clean leading separators to ensure clean split
	// Handles both "\" from "C:\Users" and "/" from mixed-slash paths
	relPath = strings.TrimLeft(relPath, "/\\")

	// Split into components and normalize each
	parts := strings.Split(relPath, string(filepath.Separator))

	for _, part := range parts {
		select {
		case <-ctx.Done():
			return "", ctx.Err()
		default:
		}

		if part == "" || part == "." {
			continue
		}

		entries, err := readDirWithContext(ctx, currentPath)
		if err != nil {
			return "", fmt.Errorf("failed to read directory %s: %w", currentPath, err)
		}

		found := false

		// On case-sensitive filesystems (Linux), prefer exact match first
		// This prevents File.txt and file.txt ambiguity
		if runtime.GOOS == "linux" {
			for _, entry := range entries {
				if entry.Name() == part {
					currentPath = filepath.Join(currentPath, entry.Name())
					found = true
					break
				}
			}
		}

		// Fallback to case-insensitive match (or first attempt on Windows/macOS)
		if !found {
			for _, entry := range entries {
				if strings.EqualFold(entry.Name(), part) {
					currentPath = filepath.Join(currentPath, entry.Name())
					found = true
					break
				}
			}
		}

		// Handle 8.3 short names on Windows (PROGRA~1)
		// If component not found via ReadDir but path exists via Stat,
		// it might be a short name or special filesystem entry
		if !found {
			targetCheck := filepath.Join(currentPath, part)
			if _, err := statWithContext(ctx, targetCheck); err == nil {
				// Path exists but wasn't found in directory listing
				// Accept the component as-is (likely 8.3 short name)
				currentPath = targetCheck
				found = true
			}
		}

		if !found {
			return "", fmt.Errorf("component %s not found in %s", part, currentPath)
		}
	}

	return currentPath, nil
}

func GetSystemPaths(
	ctx context.Context,
	_ *config.Instance,
	_ platforms.Platform,
	rootFolders []string,
	systems []systemdefs.System,
) []PathResult {
	var matches []PathResult

	log.Info().
		Int("rootFolders", len(rootFolders)).
		Int("systems", len(systems)).
		Msg("starting path discovery")

	// Validate root folders ONCE before iterating systems
	// This prevents logging the same error 200+ times (once per system)
	validRootFolders := make([]string, 0, len(rootFolders))
	for _, folder := range rootFolders {
		gf, err := FindPath(ctx, folder)
		if err != nil {
			switch {
			case errors.Is(err, ErrFsTimeout):
				log.Warn().Str("path", folder).Dur("timeout", defaultFsTimeout).
					Msg("root folder timed out (possible stale mount)")
			case ctx.Err() != nil:
				log.Info().Msg("path discovery cancelled")
				return matches
			default:
				log.Debug().Err(err).Str("path", folder).Msg("skipping root folder - not found or inaccessible")
			}
			continue
		}
		validRootFolders = append(validRootFolders, gf)
	}

	log.Info().
		Int("validRoots", len(validRootFolders)).
		Int("totalRoots", len(rootFolders)).
		Msg("root folder validation complete")

	for _, system := range systems {
		select {
		case <-ctx.Done():
			log.Info().Msg("path discovery cancelled")
			return matches
		default:
		}

		// GlobalLauncherCache is assumed to be read-only after initialization
		launchers := helpers.GlobalLauncherCache.GetLaunchersBySystem(system.ID)

		var folders []string
		for j := range launchers {
			// Skip filesystem scanning for launchers that don't need it
			if launchers[j].SkipFilesystemScan {
				log.Trace().
					Str("launcher", launchers[j].ID).
					Str("system", system.ID).
					Msg("skipping filesystem scan for launcher")
				continue
			}
			for _, folder := range launchers[j].Folders {
				if !helpers.Contains(folders, folder) {
					folders = append(folders, folder)
				}
			}
		}

		log.Trace().
			Str("system", system.ID).
			Int("launchers", len(launchers)).
			Strs("folders", folders).
			Strs("rootFolders", validRootFolders).
			Msg("resolving system paths")

		for _, gf := range validRootFolders {
			for _, folder := range folders {
				if filepath.IsAbs(folder) {
					continue // handled separately below
				}

				systemFolder := filepath.Join(gf, folder)
				path, err := FindPath(ctx, systemFolder)
				if err != nil {
					if ctx.Err() != nil {
						return matches
					}
					continue
				}

				matches = append(matches, PathResult{
					System: system,
					Path:   path,
				})
			}
		}

		for _, folder := range folders {
			if !filepath.IsAbs(folder) {
				continue
			}

			path, err := FindPath(ctx, folder)
			if err != nil {
				if ctx.Err() != nil {
					return matches
				}
				continue
			}
			matches = append(matches, PathResult{
				System: system,
				Path:   path,
			})
		}
	}

	// Deduplicate by (SystemID, resolved Path) to prevent UNIQUE constraint
	// failures when multiple root folders resolve to the same directory
	seen := make(map[string]bool)
	deduplicated := make([]PathResult, 0, len(matches))
	for _, m := range matches {
		key := m.System.ID + ":" + m.Path
		if !seen[key] {
			seen[key] = true
			deduplicated = append(deduplicated, m)
		}
	}

	log.Info().
		Int("matches", len(deduplicated)).
		Int("duplicatesRemoved", len(matches)-len(deduplicated)).
		Msg("path discovery complete")

	return deduplicated
}

// GetFiles searches for all valid games in a given path and returns a list of
// files. Uses fastwalk for parallel directory traversal with built-in symlink
// cycle detection. Deep searches .zip files when ZipsAsDirs is enabled.
func GetFiles(
	ctx context.Context,
	cfg *config.Instance,
	platform platforms.Platform,
	systemID string,
	path string,
) ([]string, error) {
	system, err := systemdefs.GetSystem(systemID)
	if err != nil {
		return nil, fmt.Errorf("failed to get system %s: %w", systemID, err)
	}

	var entriesScanned atomic.Int64
	walkStartTime := time.Now()

	var mu syncutil.Mutex
	var results []string

	conf := &fastwalk.Config{
		Follow: true,
	}

	log.Debug().Str("system", systemID).Str("path", path).Msg("starting directory walk")
	err = fastwalk.Walk(conf, path, func(p string, d fs.DirEntry, err error) error {
		if err != nil {
			log.Warn().Err(err).Str("path", p).Msg("walk error")
			return nil
		}

		select {
		case <-ctx.Done():
			return ctx.Err()
		default:
		}

		n := entriesScanned.Add(1)
		if n%5000 == 0 {
			log.Debug().
				Str("system", systemID).
				Str("path", p).
				Int64("entriesScanned", n).
				Dur("elapsed", time.Since(walkStartTime)).
				Msg("directory walk progress")
		}

		if d.IsDir() {
			markerPath := filepath.Join(p, ".zaparooignore")
			if _, statErr := statWithContext(ctx, markerPath); statErr == nil {
				log.Info().Str("path", p).Msg("skipping directory with .zaparooignore marker")
				return filepath.SkipDir
			}
			return nil
		}

		if helpers.IsZip(p) && platform.Settings().ZipsAsDirs {
			log.Trace().Str("path", p).Msg("opening zip file for indexing")
			zipFiles, zipErr := helpers.ListZip(p)
			if zipErr != nil {
				log.Warn().Err(zipErr).Msgf("error listing zip: %s", p)
				return nil
			}

			mu.Lock()
			for i := range zipFiles {
				abs := filepath.Join(p, zipFiles[i])
				if helpers.MatchSystemFile(cfg, platform, system.ID, abs) {
					results = append(results, abs)
				}
			}
			mu.Unlock()
		} else if helpers.MatchSystemFile(cfg, platform, system.ID, p) {
			mu.Lock()
			results = append(results, p)
			mu.Unlock()
		}

		return nil
	})
	if err != nil {
		return nil, fmt.Errorf("failed to walk directory %s: %w", path, err)
	}

	scanned := entriesScanned.Load()
	walkElapsed := time.Since(walkStartTime)

	log.Debug().
		Str("system", systemID).
		Str("path", path).
		Int64("entriesScanned", scanned).
		Int("filesFound", len(results)).
		Dur("elapsed", walkElapsed).
		Msg("completed directory walk")

	if scanned > 0 && len(results) == 0 {
		log.Info().
			Str("system", systemID).
			Str("path", path).
			Int64("entriesScanned", scanned).
			Msg("directory walk found entries but no files matched any launcher")
	}

	if walkElapsed > 15*time.Second {
		log.Warn().
			Str("system", systemID).
			Str("path", path).
			Int64("entriesScanned", scanned).
			Dur("elapsed", walkElapsed).
			Msg("directory walk took longer than expected - large directory or slow storage")
	}

	return results, nil
}

// handleCancellation performs cleanup when media indexing is cancelled
func handleCancellation(ctx context.Context, db database.MediaDBI, message string) (int, error) {
	log.Info().Msg(message)
	if setErr := db.SetIndexingStatus(mediadb.IndexingStatusCancelled); setErr != nil {
		log.Error().Err(setErr).Msg("failed to set indexing status to cancelled")
	}
	return 0, ctx.Err()
}

// handleCancellationWithRollback performs cleanup when media indexing is cancelled after transaction begins
func handleCancellationWithRollback(ctx context.Context, db database.MediaDBI, message string) (int, error) {
	log.Info().Msg(message)
	if rbErr := db.RollbackTransaction(); rbErr != nil {
		log.Error().Err(rbErr).Msg("failed to rollback transaction after cancellation")
	}
	if setErr := db.SetIndexingStatus(mediadb.IndexingStatusCancelled); setErr != nil {
		log.Error().Err(setErr).Msg("failed to set indexing status to cancelled")
	}
	return 0, ctx.Err()
}

const (
	PhaseDiscovering  = "discovering"
	PhaseInitializing = "initializing"
)

type IndexStatus struct {
	SystemID string
	Phase    string // PhaseDiscovering, PhaseInitializing, or empty during indexing
	Total    int
	Step     int
	Files    int
}

// NewNamesIndex takes a list of systems, indexes all valid game files on disk
// and writes a name index to the DB.
//
// Overwrites any existing names index but does not clean up old missing files.
//
// Takes a function which will be called with the current status of the index
// during key steps.
//
// Returns the total number of files indexed.
func NewNamesIndex(
	ctx context.Context,
	platform platforms.Platform,
	cfg *config.Instance,
	systems []systemdefs.System,
	fdb *database.Database,
	update func(IndexStatus),
) (int, error) {
	db := fdb.MediaDB
	indexStartTime := time.Now()

	log.Info().
		Int("systemCount", len(systems)).
		Msg("starting media indexing")

	var indexedFiles int
	var err error

	// Create list of system IDs for storage
	currentSystemIDs := make([]string, 0, len(systems))
	systemIDMap := make(map[string]bool, len(systems))
	for _, sys := range systems {
		currentSystemIDs = append(currentSystemIDs, sys.ID)
		systemIDMap[sys.ID] = true
	}

	// 1. Check for database locks or issues before starting
	log.Info().Msg("checking database readiness for indexing")
	// Quick database health check - try to read a simple value
	_, checkErr := db.GetIndexingStatus()
	if checkErr != nil {
		return 0, fmt.Errorf("database not ready for indexing (possible lock or corruption): %w", checkErr)
	}

	// 2. Determine resume state
	log.Info().Msg("determining indexing resume state")
	indexingStatus, getStatusErr := db.GetIndexingStatus()
	if getStatusErr != nil {
		return 0, fmt.Errorf("failed to get indexing status: %w", getStatusErr)
	}

	lastIndexedSystemID := ""
	shouldResume := false

	switch indexingStatus {
	case "":
		log.Info().Msg("starting fresh indexing (no previous indexing status)")
	case mediadb.IndexingStatusRunning, mediadb.IndexingStatusPending:
		log.Warn().Str("status", indexingStatus).
			Msg("previous indexing was interrupted, attempting to resume")
		var getSystemErr error
		lastIndexedSystemID, getSystemErr = db.GetLastIndexedSystem()
		if getSystemErr != nil {
			return 0, fmt.Errorf("failed to get last indexed system during resume: %w", getSystemErr)
		} else if lastIndexedSystemID != "" {
			// Validate that we can resume with the current configuration
			// Always check if we're indexing the same systems
			storedSystems, getStoredErr := db.GetIndexingSystems()

			switch {
			case getStoredErr != nil:
				log.Warn().Err(getStoredErr).Msg("failed to get stored indexing configuration, assuming fresh start")
			case !helpers.EqualStringSlices(storedSystems, currentSystemIDs):
				log.Warn().Msg("system list changed from previous indexing, reverting to fresh index")
			default:
				log.Info().Msgf("previous indexing interrupted. attempting to resume from system: %s",
					lastIndexedSystemID)
				shouldResume = true
			}
		}
	case mediadb.IndexingStatusFailed:
		log.Info().Msg("previous indexing run failed, starting fresh index")
		// Explicitly clear status for a fresh start after a failure
		if setErr := db.SetLastIndexedSystem(""); setErr != nil {
			log.Error().Err(setErr).Msg("failed to clear last indexed system after failed run")
		}
		if setErr := db.SetIndexingStatus(""); setErr != nil {
			log.Error().Err(setErr).Msg("failed to clear indexing status after failed run")
		}
	}

	// Get the ordered list of systems for this run (deterministic by ID)
	update(IndexStatus{Phase: PhaseDiscovering})
	systemPaths := make(map[string][]string)
	for _, v := range GetSystemPaths(ctx, cfg, platform, platform.RootDirs(cfg), systems) {
		systemPaths[v.System.ID] = append(systemPaths[v.System.ID], v.Path)
	}
	sysPathIDs := helpers.AlphaMapKeys(systemPaths)

	update(IndexStatus{Phase: PhaseInitializing})

	// Check for cancellation
	select {
	case <-ctx.Done():
		return handleCancellation(ctx, db, "Media indexing cancelled during initialization")
	default:
	}

	// Validate resume point against current system list
	if shouldResume && lastIndexedSystemID != "" {
		foundLastIndexed := false
		// Check against the provided systems list, not just sysPathIDs (which depends on paths)
		for _, system := range systems {
			if system.ID == lastIndexedSystemID {
				foundLastIndexed = true
				break
			}
		}
		if !foundLastIndexed {
			log.Warn().Msgf("last indexed system '%s' not found in current system list, reverting to full re-index",
				lastIndexedSystemID)
			shouldResume = false // Cannot resume reliably, force full re-index
			// Clear state for a fresh start
			if setErr := db.SetLastIndexedSystem(""); setErr != nil {
				log.Error().Err(setErr).Msg("failed to clear last indexed system after unresumable state")
			}
			if setErr := db.SetIndexingStatus(""); setErr != nil {
				log.Error().Err(setErr).Msg("failed to clear indexing status after unresumable state")
			}
		}
	}

	// Initialize scan state
	scanState := database.ScanState{
		SystemsIndex:  0,
		SystemIDs:     make(map[string]int),
		TitlesIndex:   0,
		TitleIDs:      make(map[string]int),
		MediaIndex:    0,
		MediaIDs:      make(map[string]int),
		TagTypesIndex: 0,
		TagTypeIDs:    make(map[string]int),
		TagsIndex:     0,
		TagIDs:        make(map[string]int),
	}

	// 3. Truncate and Initial Status Set
	if !shouldResume {
		log.Info().Msg("preparing database for fresh indexing")
		// Set indexing systems before truncating
		if setErr := db.SetIndexingSystems(currentSystemIDs); setErr != nil {
			return 0, fmt.Errorf("failed to set indexing systems: %w", setErr)
		}

		// Clear data for the specified systems (smart truncation)
		log.Info().Msgf("starting indexing for systems: %v", currentSystemIDs)

		// Use smart truncation - if indexing all systems, use full truncate for performance
		allSystems := systemdefs.AllSystems()
		allSystemIDs := make([]string, len(allSystems))
		for i, sys := range allSystems {
			allSystemIDs[i] = sys.ID
		}

		// Sort currentSystemIDs to ensure order-insensitive comparison
		sort.Strings(currentSystemIDs)
		// Sort allSystemIDs as well to ensure consistent comparison
		sort.Strings(allSystemIDs)

		if len(currentSystemIDs) == len(allSystemIDs) && helpers.EqualStringSlices(currentSystemIDs, allSystemIDs) {
			log.Info().Msg("checkpointing WAL before full database truncation")
			if sqlDB := db.UnsafeGetSQLDb(); sqlDB != nil {
				_, walErr := sqlDB.ExecContext(ctx, "PRAGMA wal_checkpoint(TRUNCATE);")
				if walErr != nil {
					log.Warn().Err(walErr).Msg("WAL checkpoint failed, continuing anyway")
				}
			}

			/*
				// Full truncate - foreign keys not needed since we're deleting everything
				log.Info().Msgf("performing full database truncation (indexing %d systems)", len(currentSystemIDs))
				err = db.Truncate()
				if err != nil {
					return 0, fmt.Errorf("failed to truncate database: %w", err)
				}
				log.Info().Msg("database truncation completed")*/
			err := db.MarkAllMediaMissing()
			if err != nil {
				return 0, fmt.Errorf("failed to mark all media is missing %v: %w", currentSystemIDs, err)
			}
			log.Info().Msg("all media marked missing for rescan")
		} else {
			// Selective indexing
			// DELETE mode disables FKs for performance, but TruncateSystems() relies on CASCADE
			// to properly delete Media/MediaTitles/MediaTags when a System is deleted
			/*log.Info().Msgf(
				"performing selective truncation for systems: %v",
				currentSystemIDs,
			)
			err = db.TruncateSystems(currentSystemIDs)
			if err != nil {
				return 0, fmt.Errorf("failed to truncate systems %v: %w", currentSystemIDs, err)
			}
			log.Info().Msg("selective truncation completed")*/

			err := db.MarkSystemsMediaMissing(currentSystemIDs)
			if err != nil {
				return 0, fmt.Errorf("failed to mark systems media is missing %v: %w", currentSystemIDs, err)
			}
			log.Info().Msg("systems media marked missing for rescan")

		}

		// For rescan indexing, populate scan state with max IDs, global data, and
		// maps for systems NOT being reindexed to avoid conflicts with existing data
		log.Info().Msgf(
			"Populating scan state for selective indexing (excluding systems: %v)", currentSystemIDs,
		)
		if err = PopulateScanStateForSelectiveIndexing(ctx, db, &scanState, currentSystemIDs); err != nil {
			// Check if this is a cancellation error
			if errors.Is(err, context.Canceled) {
				return handleCancellation(
					ctx, db, "Media indexing cancelled during selective scan state population",
				)
			}
			log.Error().Err(err).Msg("failed to populate scan state for selective indexing")
			return 0, fmt.Errorf("failed to populate scan state for selective indexing: %w", err)
		}
		log.Info().Msg("successfully populated scan state for selective indexing")

		if setErr := db.SetIndexingStatus(mediadb.IndexingStatusRunning); setErr != nil {
			log.Error().Err(setErr).Msg("failed to set indexing status to running on fresh start")
		}
		if setErr := db.SetLastIndexedSystem(""); setErr != nil {
			log.Error().Err(setErr).Msg("failed to clear last indexed system on fresh start")
		}

		// Selective indexing already seeds tags via PopulateScanStateForSelectiveIndexing;
		// only seed here for full truncate where TagTypesIndex is still 0.

		if scanState.TagTypesIndex == 0 {
			log.Info().Msg("seeding known tags for fresh indexing")
			// SeedCanonicalTags runs in its own non-batch transaction for safety.
			err = SeedCanonicalTags(db, &scanState)
			if err != nil {
				return 0, fmt.Errorf("failed to seed known tags: %w", err)
			}
			log.Info().Msg("successfully seeded known tags")
		}
	} else {
		// If resuming, ensure status is "running" and populate existing scan state indexes
		if setErr := db.SetIndexingStatus(mediadb.IndexingStatusRunning); setErr != nil {
			log.Error().Err(setErr).Msg("failed to set indexing status to running during resume")
		}

		// Check if we're resuming a full index (all systems) - if so, we need to restore WAL mode on completion
		// This handles the case where a full index was interrupted after switching to DELETE mode
		allSystems := systemdefs.AllSystems()
		allSystemIDs := make([]string, len(allSystems))
		for i, sys := range allSystems {
			allSystemIDs[i] = sys.ID
		}
		sortedCurrent := make([]string, len(currentSystemIDs))
		copy(sortedCurrent, currentSystemIDs)
		sort.Strings(sortedCurrent)
		sortedAll := make([]string, len(allSystemIDs))
		copy(sortedAll, allSystemIDs)
		sort.Strings(sortedAll)

		if len(sortedCurrent) == len(sortedAll) && helpers.EqualStringSlices(sortedCurrent, sortedAll) {
			log.Info().Msg("resuming full index")
		}

		// When resuming, we need to populate the scan state with existing data
		// to avoid ID conflicts and continue from where we left off
		log.Info().Msg("populating scan state from existing database for resume")
		err = PopulateScanStateFromDB(ctx, db, &scanState)
		if err != nil {
			// Check if this is a cancellation error
			if errors.Is(err, context.Canceled) {
				return handleCancellation(ctx, db, "Media indexing cancelled during scan state population")
			}
			log.Error().Err(err).Msg("failed to populate scan state from database")
			return 0, fmt.Errorf("failed to populate scan state from database: %w", err)
		}
		log.Info().Msg("successfully populated scan state for resume")
	}

	// Ensure transaction cleanup and status update on completion or error
	defer func() {
		// Always attempt to rollback any dangling transaction, whether success or failure
		// On success, this should be a no-op (tx == nil), but ensures cleanup if
		// the last transaction was never committed due to batchStarted being false
		if rbErr := db.RollbackTransaction(); rbErr != nil {
			if err != nil {
				log.Error().Err(rbErr).Msg("failed to rollback transaction after error")
			} else {
				log.Debug().Err(rbErr).Msg("no transaction to rollback (expected)")
			}
		}

		if err != nil {
			// Mark indexing as failed on error
			if setErr := db.SetIndexingStatus(mediadb.IndexingStatusFailed); setErr != nil {
				log.Error().Err(setErr).Msg("failed to set indexing status to failed after error")
			}
		}
	}()

	status := IndexStatus{
		Total: len(sysPathIDs) + 1, // +1 for final "Writing database" step
		Step:  0,
	}

	// Track UNIQUE constraint failures across all systems
	var uniqueConstraintFailures int

	// Track which launchers have already been scanned to prevent double-execution
	scannedLaunchers := make(map[string]bool)
	// This map tracks systems that have been fully processed and committed
	completedSystems := make(map[string]bool)

	// Populate completedSystems if resuming
	if shouldResume {
		for _, k := range sysPathIDs {
			if k == lastIndexedSystemID {
				// DO NOT mark the last indexed system as completed - we need to resume from it
				// Only mark systems BEFORE the last indexed system as completed
				break
			}
			completedSystems[k] = true // Mark all systems before the resume point as completed
		}
	}

	scannedSystems := make(map[string]bool)
	for _, s := range systemdefs.AllSystems() {
		scannedSystems[s.ID] = false
	}

	// Batch tracking variables for adaptive transaction management
	filesInBatch := 0
	systemsInBatch := 0
	batchStarted := false

	// Main loop for systems
	for _, k := range sysPathIDs {
		// Check for cancellation
		select {
		case <-ctx.Done():
			return handleCancellationWithRollback(ctx, db, "Media indexing cancelled")
		default:
		}

		systemID := k

		if completedSystems[systemID] {
			log.Debug().Msgf("skipping already indexed system: %s", systemID)
			status.Step++
			update(status)
			continue // Skip this system if it was already completed in a previous run
		}

		// Lazy load this system's existing data for resume
		if shouldResume {
			log.Debug().Str("system", systemID).Msg("loading existing data for system resume")
			if loadErr := PopulateScanStateForSystem(ctx, db, &scanState, systemID); loadErr != nil {
				// Check if this is a cancellation error
				if errors.Is(loadErr, context.Canceled) {
					return handleCancellation(ctx, db, "Media indexing cancelled during system data loading")
				}
				return 0, fmt.Errorf("failed to load system data for resume: %w", loadErr)
			}
		}

		files := make([]platforms.ScanResult, 0)
		systemStartTime := time.Now()

		status.SystemID = systemID
		status.Step++
		update(status)

		pathCount := len(systemPaths[k])
		log.Info().
			Str("system", systemID).
			Int("step", status.Step).
			Int("total", status.Total).
			Int("paths", pathCount).
			Msg("indexing system")

		// scan using standard folder and extensions
		for _, systemPath := range systemPaths[k] {
			pathFiles, pathErr := GetFiles(ctx, cfg, platform, k, systemPath)
			if pathErr != nil {
				// Check if this is a cancellation error
				if errors.Is(pathErr, context.Canceled) {
					return handleCancellationWithRollback(ctx, db, "Media indexing cancelled during file scanning")
				}
				log.Error().Err(pathErr).Msgf("error getting files for system: %s", systemID)
				continue
			}
			for _, f := range pathFiles {
				files = append(files, platforms.ScanResult{Path: f})
			}
		}

		// Run each system launcher's custom scanner if one exists.
		//
		// SkipFilesystemScan launchers (e.g. RetroBat, Kodi) generate results
		// independently — they don't filter/enrich the shared file list, so they
		// receive empty input and their results are *appended* to files. This
		// prevents an independent scanner that ignores its input from wiping out
		// files discovered by other launchers or the filesystem walk.
		//
		// Non-skip launchers (e.g. Batocera gamelist.xml enrichment) act as a
		// pipeline: they receive the current files and their output *replaces*
		// files, allowing them to filter, reorder, or add metadata.
		launchers := helpers.GlobalLauncherCache.GetLaunchersBySystem(k)
		for i := range launchers {
			l := &launchers[i]
			if l.Scanner != nil {
				log.Debug().Msgf("running %s scanner for system: %s", l.ID, systemID)
				var scanErr error
				if l.SkipFilesystemScan {
					// Isolated: scanner gets empty input, results accumulated
					var independent []platforms.ScanResult
					independent, scanErr = l.Scanner(ctx, cfg, systemID, nil)
					if scanErr == nil {
						files = append(files, independent...)
					}
				} else {
					// Pipeline: scanner filters/enriches existing files
					files, scanErr = l.Scanner(ctx, cfg, systemID, files)
				}
				if scanErr != nil {
					if errors.Is(scanErr, context.Canceled) {
						return handleCancellationWithRollback(ctx, db, "Media indexing cancelled during custom scanner")
					}
					log.Error().Err(scanErr).Msgf("error running %s scanner for system: %s", l.ID, systemID)
					continue
				}
				// Mark launcher as scanned to prevent double-execution
				scannedLaunchers[l.ID] = true
			}
		}

		if len(files) == 0 {
			log.Debug().Msgf("no files found for system: %s", systemID)
		} else {
			status.Files += len(files)
			log.Debug().Msgf("scanned %d files for system: %s", len(files), systemID)
		}

		scannedSystems[systemID] = true

		// Group files by directory and determine stripping policy per directory
		filesByDir := make(map[string][]platforms.ScanResult)
		for _, file := range files {
			dir := filepath.Dir(file.Path)
			filesByDir[dir] = append(filesByDir[dir], file)
		}

		stripPolicyByDir := make(map[string]bool)
		for dir, dirFiles := range filesByDir {
			// Use 50% threshold and require at least 5 files to apply heuristic
			stripPolicyByDir[dir] = detectNumberingPattern(dirFiles, 0.5, 5)
		}

		for _, file := range files {
			// Check for cancellation between file processing
			select {
			case <-ctx.Done():
				return handleCancellationWithRollback(ctx, db, "Media indexing cancelled during file processing")
			default:
			}

			// Start transaction if needed (at start of system OR after mid-system commit)
			if !batchStarted {
				if beginErr := db.BeginTransaction(true); beginErr != nil {
					return 0, fmt.Errorf("failed to begin new transaction: %w", beginErr)
				}
				batchStarted = true
			}

			// Look up stripping policy for this file's directory
			dir := filepath.Dir(file.Path)
			shouldStrip := stripPolicyByDir[dir]

			_, _, addErr := AddMediaPath(db, &scanState, systemID, file.Path, file.NoExt, shouldStrip, cfg)
			if addErr != nil {
				var sqliteErr sqlite3.Error
				if errors.As(addErr, &sqliteErr) && sqliteErr.ExtendedCode == sqlite3.ErrConstraintUnique {
					uniqueConstraintFailures++
					log.Debug().Err(addErr).Str("path", file.Path).Msg("skipping duplicate media entry")
					continue
				}
				return 0, fmt.Errorf("unrecoverable error adding media path %q: %w", file.Path, addErr)
			}
			filesInBatch++

			// Commit if we hit file limit (memory safety - even mid-system)
			if filesInBatch >= maxFilesPerTransaction {
				commitStart := time.Now()
				if commitErr := db.CommitTransaction(); commitErr != nil {
					return 0, fmt.Errorf("failed to commit batch transaction (file limit): %w", commitErr)
				}
				commitElapsed := time.Since(commitStart)
				log.Debug().
					Str("system", systemID).
					Int("files", filesInBatch).
					Dur("commitTime", commitElapsed).
					Msg("committed batch (file limit)")
				if commitElapsed > 5*time.Second {
					log.Warn().
						Int("files", filesInBatch).
						Dur("commitTime", commitElapsed).
						Msg("database commit took longer than expected")
				}
				// Update progress after successful commit
				if setErr := db.SetLastIndexedSystem(systemID); setErr != nil {
					log.Error().Err(setErr).Msgf(
						"failed to set last indexed system to %s after file limit commit", systemID)
				}
				FlushScanStateMaps(&scanState)
				filesInBatch = 0
				systemsInBatch = 0
				batchStarted = false
			}
		}

		// Only count system in batch if we have pending uncommitted work
		if batchStarted {
			systemsInBatch++
		}

		// Mark system as processed (even if split across multiple commits)
		completedSystems[systemID] = true

		systemElapsed := time.Since(systemStartTime)
		log.Info().
			Str("system", systemID).
			Int("files", len(files)).
			Dur("elapsed", systemElapsed).
			Msg("completed system indexing")

		if systemElapsed > 30*time.Second {
			log.Warn().
				Str("system", systemID).
				Int("files", len(files)).
				Dur("elapsed", systemElapsed).
				Msg("system indexing took longer than expected - check for slow storage or large directories")
		}

		// Commit after N small systems OR when file limit forces it
		if batchStarted && systemsInBatch >= maxSystemsPerTransaction {
			commitStart := time.Now()
			if commitErr := db.CommitTransaction(); commitErr != nil {
				return 0, fmt.Errorf("failed to commit batch transaction (system limit): %w", commitErr)
			}
			commitElapsed := time.Since(commitStart)
			log.Debug().
				Int("systems", systemsInBatch).
				Int("files", filesInBatch).
				Dur("commitTime", commitElapsed).
				Msg("committed batch (system limit)")
			if commitElapsed > 5*time.Second {
				log.Warn().
					Int("files", filesInBatch).
					Dur("commitTime", commitElapsed).
					Msg("database commit took longer than expected")
			}
			// Update progress after successful commit
			if setErr := db.SetLastIndexedSystem(systemID); setErr != nil {
				log.Error().Err(setErr).Msgf(
					"failed to set last indexed system to %s after system limit commit", systemID)
			}
			FlushScanStateMaps(&scanState)
			filesInBatch = 0
			systemsInBatch = 0
			batchStarted = false
		}
	}

	// Commit any remaining uncommitted data from main loop
	if batchStarted && filesInBatch > 0 {
		if commitErr := db.CommitTransaction(); commitErr != nil {
			return 0, fmt.Errorf("failed to commit final batch transaction: %w", commitErr)
		}
		// Update progress with last processed system
		lastSystem := ""
		if len(sysPathIDs) > 0 {
			lastSystem = sysPathIDs[len(sysPathIDs)-1]
		}
		if setErr := db.SetLastIndexedSystem(lastSystem); setErr != nil {
			log.Error().Err(setErr).Msg("failed to set last indexed system after final commit")
		}
		FlushScanStateMaps(&scanState)
		batchStarted = false
		filesInBatch = 0
		systemsInBatch = 0
	}

	// Check for cancellation before custom scanners
	select {
	case <-ctx.Done():
		return handleCancellationWithRollback(ctx, db, "Media indexing cancelled before custom scanners")
	default:
	}

	// run each custom scanner at least once, even if there are no paths
	// defined or results from a regular index
	launchers := helpers.GlobalLauncherCache.GetAllLaunchers()
	log.Debug().Msgf("checking %d launchers for custom scanners", len(launchers))
	for i := range launchers {
		l := &launchers[i]
		systemID := l.SystemID
		log.Debug().Msgf("launcher %s for system %s: scanner=%v scanned=%v",
			l.ID, systemID, l.Scanner != nil, scannedLaunchers[l.ID])

		// Only run custom scanners for systems in the current indexing operation
		if !scannedLaunchers[l.ID] && l.Scanner != nil && systemID != "" {
			// Check if this system is part of the current indexing operation
			if !systemIDMap[systemID] {
				log.Debug().Msgf("skipping %s scanner for system %s (not in current index)", l.ID, systemID)
				continue
			}

			log.Debug().Msgf("running %s scanner for system: %s", l.ID, systemID)
			results, scanErr := l.Scanner(ctx, cfg, systemID, []platforms.ScanResult{})
			if scanErr != nil {
				// Check if this is a cancellation error
				if errors.Is(scanErr, context.Canceled) {
					return handleCancellationWithRollback(ctx, db,
						"Media indexing cancelled during second round custom scanner")
				}
				log.Warn().Err(scanErr).Msgf("error running %s scanner for system: %s", l.ID, systemID)
				continue
			}

			log.Debug().Msgf("scanned %d files for system: %s", len(results), systemID)

			if len(results) > 0 {
				status.Files += len(results)

				for _, result := range results {
					// Check for cancellation between file processing
					select {
					case <-ctx.Done():
						return handleCancellationWithRollback(ctx, db,
							"Media indexing cancelled during custom scanner file processing")
					default:
					}

					// Start transaction if needed (at start OR after mid-system commit)
					if !batchStarted {
						if beginErr := db.BeginTransaction(true); beginErr != nil {
							return 0, fmt.Errorf("failed to begin new transaction for custom scanner: %w", beginErr)
						}
						batchStarted = true
					}

					// Custom scanner files: don't apply number stripping heuristic (false)
					_, _, addErr := AddMediaPath(db, &scanState, systemID, result.Path, result.NoExt, false, cfg)
					if addErr != nil {
						var sqliteErr sqlite3.Error
						if errors.As(addErr, &sqliteErr) && sqliteErr.ExtendedCode == sqlite3.ErrConstraintUnique {
							uniqueConstraintFailures++
							log.Debug().Err(addErr).Str("path", result.Path).Msg("skipping duplicate media entry")
							continue
						}
						return 0, fmt.Errorf(
							"unrecoverable error adding custom scanner path %q: %w",
							result.Path,
							addErr,
						)
					}
					filesInBatch++

					// Commit if we hit file limit (memory safety)
					if filesInBatch >= maxFilesPerTransaction {
						if commitErr := db.CommitTransaction(); commitErr != nil {
							return 0, fmt.Errorf(
								"failed to commit batch transaction for custom scanner (file limit): %w", commitErr)
						}
						FlushScanStateMaps(&scanState)
						filesInBatch = 0
						systemsInBatch = 0
						batchStarted = false
					}
				}

				// Update system count if we have uncommitted work
				if batchStarted {
					systemsInBatch++
				}

				// Commit after N systems OR when file limit forces it
				if batchStarted && systemsInBatch >= maxSystemsPerTransaction {
					if commitErr := db.CommitTransaction(); commitErr != nil {
						return 0, fmt.Errorf(
							"failed to commit batch transaction for custom scanner (system limit): %w", commitErr)
					}
					FlushScanStateMaps(&scanState)
					filesInBatch = 0
					systemsInBatch = 0
					batchStarted = false
				}
			}
			scannedSystems[systemID] = true
			scannedLaunchers[l.ID] = true // Mark this specific launcher as run
		}
	}

	// launcher scanners with no system defined are run against every system
	var anyScanners []platforms.Launcher
	allLaunchers := helpers.GlobalLauncherCache.GetAllLaunchers()
	for i := range allLaunchers {
		if allLaunchers[i].SystemID == "" && allLaunchers[i].Scanner != nil {
			anyScanners = append(anyScanners, allLaunchers[i])
		}
	}

	for i := range anyScanners {
		l := &anyScanners[i]
		for _, s := range systems {
			systemID := s.ID
			if completedSystems[systemID] {
				log.Debug().Msgf("skipping 'any' scanner for already completed system: %s", systemID)
				continue // Skip if system already fully processed
			}

			log.Debug().Msgf("running %s 'any' scanner for system: %s", l.ID, s.ID)
			results, scanErr := l.Scanner(ctx, cfg, s.ID, []platforms.ScanResult{})
			if scanErr != nil {
				// Check if this is a cancellation error
				if errors.Is(scanErr, context.Canceled) {
					return handleCancellationWithRollback(ctx, db, "Media indexing cancelled during 'any' scanner")
				}
				log.Error().Err(scanErr).Msgf("error running %s 'any' scanner for system: %s", l.ID, s.ID)
				continue
			}

			log.Debug().Msgf("scanned %d files for system: %s", len(results), s.ID)

			if len(results) > 0 {
				// Dynamically expand total and report progress for systems with results
				status.Total++
				status.Step++
				status.SystemID = systemID
				update(status)

				status.Files += len(results)

				for _, scanResult := range results {
					// Check for cancellation between file processing
					select {
					case <-ctx.Done():
						return handleCancellationWithRollback(ctx, db,
							"Media indexing cancelled during 'any' scanner file processing")
					default:
					}

					// Start transaction if needed (at start OR after mid-system commit)
					if !batchStarted {
						if beginErr := db.BeginTransaction(true); beginErr != nil {
							return 0, fmt.Errorf("failed to begin new transaction for 'any' scanner: %w", beginErr)
						}
						batchStarted = true
					}

					// 'Any' scanner files: don't apply number stripping heuristic (false)
					_, _, addErr := AddMediaPath(
						db, &scanState, systemID, scanResult.Path, scanResult.NoExt, false, cfg,
					)
					if addErr != nil {
						var sqliteErr sqlite3.Error
						if errors.As(addErr, &sqliteErr) && sqliteErr.ExtendedCode == sqlite3.ErrConstraintUnique {
							uniqueConstraintFailures++
							log.Debug().Err(addErr).Str("path", scanResult.Path).Msg("skipping duplicate media entry")
							continue
						}
						return 0, fmt.Errorf(
							"unrecoverable error adding 'any' scanner path %q: %w",
							scanResult.Path,
							addErr,
						)
					}
					filesInBatch++

					// Commit if we hit file limit (memory safety)
					if filesInBatch >= maxFilesPerTransaction {
						if commitErr := db.CommitTransaction(); commitErr != nil {
							return 0, fmt.Errorf(
								"failed to commit batch transaction for 'any' scanner (file limit): %w", commitErr)
						}
						FlushScanStateMaps(&scanState)
						filesInBatch = 0
						systemsInBatch = 0
						batchStarted = false
					}
				}

				// Update system count if we have uncommitted work
				if batchStarted {
					systemsInBatch++
				}

				// Commit after N systems OR when file limit forces it
				if batchStarted && systemsInBatch >= maxSystemsPerTransaction {
					if commitErr := db.CommitTransaction(); commitErr != nil {
						return 0, fmt.Errorf(
							"failed to commit batch transaction for 'any' scanner (system limit): %w", commitErr)
					}
					FlushScanStateMaps(&scanState)
					filesInBatch = 0
					systemsInBatch = 0
					batchStarted = false
				}
			}
			scannedSystems[systemID] = true
		}
	}

	// Commit any remaining uncommitted data from all scanner phases
	if batchStarted && filesInBatch > 0 {
		if commitErr := db.CommitTransaction(); commitErr != nil {
			return 0, fmt.Errorf("failed to commit final scanner batch transaction: %w", commitErr)
		}
		FlushScanStateMaps(&scanState)
		batchStarted = false
	}

	status.Step++
	status.SystemID = ""
	update(status)

	scanState.TagIDs = make(map[string]int)

	// Phase 1: Complete data operations (foreground) - commit all data
	// Note: We may not have an active transaction here due to batching
	if batchStarted {
		err = db.CommitTransaction()
		if err != nil {
			return 0, fmt.Errorf("failed to commit final transaction: %w", err)
		}
	}

	// Mark database as complete and ready for use
	err = db.UpdateLastGenerated()
	if err != nil {
		return 0, fmt.Errorf("failed to update last generated timestamp: %w", err)
	}

	// Mark indexing as completed and clear indexing metadata
	if setErr := db.SetIndexingStatus(mediadb.IndexingStatusCompleted); setErr != nil {
		log.Error().Err(setErr).Msg("failed to set indexing status to completed")
	}
	if setErr := db.SetLastIndexedSystem(""); setErr != nil {
		log.Error().Err(setErr).Msg("failed to clear last indexed system on completion")
	}
	if setErr := db.SetIndexingSystems(nil); setErr != nil {
		log.Error().Err(setErr).Msg("failed to clear indexing systems on completion")
	}

	// Invalidate media count cache after successful indexing
	if cacheErr := db.InvalidateCountCache(); cacheErr != nil {
		log.Error().Err(cacheErr).Msg("failed to invalidate media count cache after indexing")
	}

	// Populate system tags cache for fast tag lookups
	log.Info().Msg("populating system tags cache after indexing completion")
	if cacheErr := db.PopulateSystemTagsCache(ctx); cacheErr != nil {
		log.Error().Err(cacheErr).Msg("failed to populate system tags cache after indexing")
	} else {
		log.Info().Msg("successfully populated system tags cache")
	}

	// Force WAL checkpoint to ensure all pending writes are flushed to disk
	// This prevents stale locks from persisting if the process is interrupted
	log.Info().Msg("forcing WAL checkpoint after indexing completion")
	if sqlDB := db.UnsafeGetSQLDb(); sqlDB != nil {
		if _, walErr := sqlDB.ExecContext(ctx, "PRAGMA wal_checkpoint(TRUNCATE);"); walErr != nil {
			log.Warn().Err(walErr).Msg("failed to checkpoint WAL after indexing")
		} else {
			log.Info().Msg("WAL checkpoint completed successfully")
		}
	}

	// Mark optimization as pending
	err = db.SetOptimizationStatus("pending")
	if err != nil {
		err = fmt.Errorf("failed to set optimization status to pending: %w", err)
		log.Error().Err(err).Msg("failed to set optimization status to pending")
	}

	indexedSystems := make([]string, 0)
	log.Debug().Msgf("processed systems: %v", completedSystems)
	for k, v := range completedSystems {
		if v {
			indexedSystems = append(indexedSystems, k)
		}
	}
	log.Debug().Msgf("indexed systems: %v", indexedSystems)

	if uniqueConstraintFailures > 0 {
		log.Warn().Int("count", uniqueConstraintFailures).
			Msg("UNIQUE constraint failures during indexing (possible duplicate paths)")
	}

	indexedFiles = status.Files
	indexElapsed := time.Since(indexStartTime)

	if err != nil {
		log.Error().
			Err(err).
			Int("files", indexedFiles).
			Int("systemsCompleted", len(indexedSystems)).
			Dur("elapsed", indexElapsed).
			Msg("media indexing completed with error")
	} else {
		log.Info().
			Int("files", indexedFiles).
			Int("systemsCompleted", len(indexedSystems)).
			Dur("elapsed", indexElapsed).
			Msg("media indexing completed successfully")
	}

	return indexedFiles, err
}