quietlight/skraak_mcp - Change 4VO5HC4R37PDGNAN6SX24X2M5G2V4RXIX2WCTFXDOH2TPRZA7DZQC

refactor of cluster import and file import

Created by quietlight on February 6, 2026

4VO5HC4R37PDGNAN6SX24X2M5G2V4RXIX2WCTFXDOH2TPRZA7DZQC

Dependencies

In channels

main

Change contents

File addition: cluster_import.go (----------)

[3.1]

package utils
import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"time"
)
// FileImportError records errors encountered during file processing
type FileImportError struct {
	FileName string `json:"file_name"`
	Error    string `json:"error"`
	Stage    string `json:"stage"` // "scan", "hash", "parse", "validate", "insert"
}
// ClusterImportInput defines parameters for importing one cluster
type ClusterImportInput struct {
	FolderPath string // Absolute path to folder with WAV files
	DatasetID  string // 12-char dataset ID
	LocationID string // 12-char location ID
	ClusterID  string // 12-char cluster ID
	Recursive  bool   // Scan subfolders?
}
// ClusterImportOutput provides results and statistics
type ClusterImportOutput struct {
	TotalFiles     int
	ImportedFiles  int
	SkippedFiles   int // Duplicates
	FailedFiles    int
	AudioMothFiles int
	TotalDuration  float64
	ProcessingTime string
	Errors         []FileImportError
}
// locationData holds location information needed for processing
type locationData struct {
	Latitude   float64
	Longitude  float64
	TimezoneID string
}
// fileData holds all data for a single file to be imported
type fileData struct {
	FileName       string
	Hash           string
	Duration       float64
	SampleRate     int
	TimestampLocal time.Time
	IsAudioMoth    bool
	MothData       *AudioMothData
	AstroData      AstronomicalData
}
// ImportCluster imports all WAV files from a folder into a cluster
//
// This is the canonical cluster import logic used by both:
//   - import_files.go (single cluster)
//   - bulk_file_import.go (multiple clusters)
//
// Steps:
//  1. Validate folder exists
//  2. Get location metadata (lat/lon/timezone) from database
//  3. Scan folder for WAV files (recursive or not)
//  4. Batch process all files:
//     - Parse WAV headers
//     - Batch parse filename timestamps (variance-based)
//     - Resolve timestamps (AudioMoth → filename)
//     - Calculate hashes
//     - Calculate astronomical data
//  5. Batch insert in single transaction:
//     - Check duplicates
//     - INSERT INTO file
//     - INSERT INTO file_dataset (ALWAYS)
//     - INSERT INTO moth_metadata (if AudioMoth)
//     - All-or-nothing commit
//  6. Return summary statistics
//
// Transaction behavior: ALL files succeed or ALL rollback
// This preserves cluster integrity (cluster = complete recording session)
func ImportCluster(
	database *sql.DB,
	input ClusterImportInput,
) (*ClusterImportOutput, error) {
	startTime := time.Now()
	// Validate folder exists
	info, err := os.Stat(input.FolderPath)
	if err != nil {
		return nil, fmt.Errorf("folder not accessible: %w", err)
	}
	if !info.IsDir() {
		return nil, fmt.Errorf("path is not a directory: %s", input.FolderPath)
	}
	// Get location data for astronomical calculations
	locationData, err := getLocationData(database, input.LocationID)
	if err != nil {
		return nil, fmt.Errorf("failed to get location data: %w", err)
	}
	// Scan folder for WAV files
	wavFiles, err := scanClusterFiles(input.FolderPath, input.Recursive)
	if err != nil {
		return nil, fmt.Errorf("failed to scan folder: %w", err)
	}
	// If no files, return early
	if len(wavFiles) == 0 {
		return &ClusterImportOutput{
			TotalFiles:     0,
			ProcessingTime: time.Since(startTime).String(),
			Errors:         []FileImportError{},
		}, nil
	}
	// Batch process all files
	filesData, processErrors := batchProcessFiles(wavFiles, locationData)
	// Batch insert into database
	imported, skipped, insertErrors, err := insertClusterFiles(
		database,
		filesData,
		input.DatasetID,
		input.ClusterID,
		input.LocationID,
	)
	if err != nil {
		return nil, fmt.Errorf("database insertion failed: %w", err)
	}
	// Combine all errors
	allErrors := append(processErrors, insertErrors...)
	// Calculate summary statistics
	audiomothCount := 0
	totalDuration := 0.0
	for _, fd := range filesData {
		if fd.IsAudioMoth {
			audiomothCount++
		}
		totalDuration += fd.Duration
	}
	return &ClusterImportOutput{
		TotalFiles:     len(wavFiles),
		ImportedFiles:  imported,
		SkippedFiles:   skipped,
		FailedFiles:    len(allErrors),
		AudioMothFiles: audiomothCount,
		TotalDuration:  totalDuration,
		ProcessingTime: time.Since(startTime).String(),
		Errors:         allErrors,
	}, nil
}
// getLocationData retrieves location coordinates and timezone
func getLocationData(database *sql.DB, locationID string) (*locationData, error) {
	var loc locationData
	err := database.QueryRow(
		"SELECT latitude, longitude, timezone_id FROM location WHERE id = ?",
		locationID,
	).Scan(&loc.Latitude, &loc.Longitude, &loc.TimezoneID)
	if err != nil {
		return nil, fmt.Errorf("failed to query location data: %w", err)
	}
	return &loc, nil
}
// EnsureClusterPath sets the cluster's path field if it's currently empty
func EnsureClusterPath(database *sql.DB, clusterID, folderPath string) error {
	// Check if cluster already has a path
	var currentPath sql.NullString
	err := database.QueryRow("SELECT path FROM cluster WHERE id = ?", clusterID).Scan(&currentPath)
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}
	// If path is already set, skip
	if currentPath.Valid && currentPath.String != "" {
		return nil
	}
	// Normalize folder path
	normalizedPath := NormalizeFolderPath(folderPath)
	// Update cluster with normalized path
	_, err = database.Exec(
		"UPDATE cluster SET path = ?, last_modified = now() WHERE id = ?",
		normalizedPath,
		clusterID,
	)
	if err != nil {
		return fmt.Errorf("failed to update cluster path: %w", err)
	}
	return nil
}
// scanClusterFiles recursively scans a folder for WAV files, excluding Clips_* subfolders
func scanClusterFiles(rootPath string, recursive bool) ([]string, error) {
	var wavFiles []string
	if recursive {
		err := filepath.Walk(rootPath, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}
			// Skip "Clips_*" directories
			if info.IsDir() && strings.HasPrefix(info.Name(), "Clips_") {
				return filepath.SkipDir
			}
			// Check for WAV files
			if !info.IsDir() {
				ext := strings.ToLower(filepath.Ext(path))
				if ext == ".wav" && info.Size() > 0 {
					wavFiles = append(wavFiles, path)
				}
			}
			return nil
		})
		if err != nil {
			return nil, err
		}
	} else {
		// Non-recursive: scan only top level
		entries, err := os.ReadDir(rootPath)
		if err != nil {
			return nil, err
		}
		for _, entry := range entries {
			if !entry.IsDir() {
				name := entry.Name()
				ext := strings.ToLower(filepath.Ext(name))
				if ext == ".wav" {
					path := filepath.Join(rootPath, name)
					if info, err := os.Stat(path); err == nil && info.Size() > 0 {
						wavFiles = append(wavFiles, path)
					}
				}
			}
		}
	}
	// Sort for consistent processing order
	sort.Strings(wavFiles)
	return wavFiles, nil
}
// batchProcessFiles extracts metadata and calculates hashes for all files
func batchProcessFiles(wavFiles []string, location *locationData) ([]*fileData, []FileImportError) {
	var filesData []*fileData
	var errors []FileImportError
	// Step 1: Extract WAV metadata from all files
	type wavInfo struct {
		path     string
		metadata *WAVMetadata
		err      error
	}
	wavInfos := make([]wavInfo, len(wavFiles))
	for i, path := range wavFiles {
		metadata, err := ParseWAVHeader(path)
		wavInfos[i] = wavInfo{path: path, metadata: metadata, err: err}
	}
	// Step 2: Collect filenames for batch timestamp parsing
	var filenamesForParsing []string
	var filenameIndices []int
	for i, info := range wavInfos {
		if info.err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    info.err.Error(),
				Stage:    "parse",
			})
			continue
		}
		// Check if file has timestamp filename format
		if HasTimestampFilename(info.path) {
			filenamesForParsing = append(filenamesForParsing, filepath.Base(info.path))
			filenameIndices = append(filenameIndices, i)
		}
	}
	// Step 3: Parse filename timestamps in batch (if any)
	filenameTimestampMap := make(map[int]time.Time) // Maps file index to timestamp
	if len(filenamesForParsing) > 0 {
		filenameTimestamps, err := ParseFilenameTimestamps(filenamesForParsing)
		if err != nil {
			// If batch parsing fails, record error for all files
			for _, idx := range filenameIndices {
				errors = append(errors, FileImportError{
					FileName: filepath.Base(wavInfos[idx].path),
					Error:    fmt.Sprintf("filename timestamp parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		} else {
			// Apply timezone offset
			adjustedTimestamps, err := ApplyTimezoneOffset(filenameTimestamps, location.TimezoneID)
			if err != nil {
				for _, idx := range filenameIndices {
					errors = append(errors, FileImportError{
						FileName: filepath.Base(wavInfos[idx].path),
						Error:    fmt.Sprintf("timezone offset failed: %v", err),
						Stage:    "parse",
					})
				}
			} else {
				// Build map from file index to timestamp
				for j, idx := range filenameIndices {
					filenameTimestampMap[idx] = adjustedTimestamps[j]
				}
			}
		}
	}
	// Step 4: Process each file
	for i, info := range wavInfos {
		if info.err != nil {
			continue // Already recorded error
		}
		// Calculate hash
		hash, err := ComputeXXH64(info.path)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    fmt.Sprintf("hash calculation failed: %v", err),
				Stage:    "hash",
			})
			continue
		}
		// Determine timestamp
		var timestampLocal time.Time
		var isAudioMoth bool
		var mothData *AudioMothData
		// Try AudioMoth comment first
		if IsAudioMoth(info.metadata.Comment, info.metadata.Artist) {
			isAudioMoth = true
			mothData, err = ParseAudioMothComment(info.metadata.Comment)
			if err == nil {
				timestampLocal = mothData.Timestamp
			} else {
				// AudioMoth detected but parsing failed - try filename
				errors = append(errors, FileImportError{
					FileName: filepath.Base(info.path),
					Error:    fmt.Sprintf("AudioMoth comment parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		}
		// If no AudioMoth timestamp, use filename timestamp
		if timestampLocal.IsZero() {
			if ts, ok := filenameTimestampMap[i]; ok {
				timestampLocal = ts
			}
		}
		// If still no timestamp, skip file
		if timestampLocal.IsZero() {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    "no timestamp available (not AudioMoth and filename not parseable)",
				Stage:    "parse",
			})
			continue
		}
		// Calculate astronomical data
		astroData := CalculateAstronomicalData(
			timestampLocal.UTC(),
			info.metadata.Duration,
			location.Latitude,
			location.Longitude,
		)
		// Add to results
		filesData = append(filesData, &fileData{
			FileName:       filepath.Base(info.path),
			Hash:           hash,
			Duration:       info.metadata.Duration,
			SampleRate:     info.metadata.SampleRate,
			TimestampLocal: timestampLocal,
			IsAudioMoth:    isAudioMoth,
			MothData:       mothData,
			AstroData:      astroData,
		})
	}
	return filesData, errors
}
// insertClusterFiles inserts all file data into database in a single transaction
func insertClusterFiles(
	database *sql.DB,
	filesData []*fileData,
	datasetID, clusterID, locationID string,
) (imported, skipped int, errors []FileImportError, err error) {
	// Begin transaction
	ctx := context.Background()
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() // Rollback if not committed
	// Prepare statements
	fileStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file (
			id, file_name, xxh64_hash, location_id, timestamp_local,
			cluster_id, duration, sample_rate, maybe_solar_night, maybe_civil_night,
			moon_phase, created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare file statement: %w", err)
	}
	defer fileStmt.Close()
	datasetStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file_dataset (file_id, dataset_id, created_at, last_modified)
		VALUES (?, ?, now(), now())
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare dataset statement: %w", err)
	}
	defer datasetStmt.Close()
	mothStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO moth_metadata (
			file_id, timestamp, recorder_id, gain, battery_v, temp_c,
			created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare moth statement: %w", err)
	}
	defer mothStmt.Close()
	// Insert each file
	for _, fd := range filesData {
		// Check for duplicate hash
		var exists bool
		err = tx.QueryRowContext(ctx,
			"SELECT EXISTS(SELECT 1 FROM file WHERE xxh64_hash = ?)",
			fd.Hash,
		).Scan(&exists)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("duplicate check failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		if exists {
			skipped++
			continue
		}
		// Generate file ID
		fileID, err := GenerateLongID()
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("ID generation failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file record
		_, err = fileStmt.ExecContext(ctx,
			fileID, fd.FileName, fd.Hash, locationID,
			fd.TimestampLocal, clusterID, fd.Duration, fd.SampleRate,
			fd.AstroData.SolarNight, fd.AstroData.CivilNight, fd.AstroData.MoonPhase,
		)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file_dataset junction (ALWAYS)
		_, err = datasetStmt.ExecContext(ctx, fileID, datasetID)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file_dataset insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// If AudioMoth, insert moth_metadata
		if fd.IsAudioMoth && fd.MothData != nil {
			_, err = mothStmt.ExecContext(ctx,
				fileID,
				fd.MothData.Timestamp,
				&fd.MothData.RecorderID,
				&fd.MothData.Gain,
				&fd.MothData.BatteryV,
				&fd.MothData.TempC,
			)
			if err != nil {
				errors = append(errors, FileImportError{
					FileName: fd.FileName,
					Error:    fmt.Sprintf("moth_metadata insert failed: %v", err),
					Stage:    "insert",
				})
				continue
			}
		}
		imported++
	}
	// Commit transaction
	err = tx.Commit()
	if err != nil {
		return 0, 0, errors, fmt.Errorf("transaction commit failed: %w", err)
	}
	return imported, skipped, errors, nil
}

Deletion in tools/import_files.go at line 8 [3.50297]
B:BD[3.50362] → [3.50362:50398]
```
	"path/filepath"
	"sort"
	"strings"
```

Replacement in tools/import_files.go at line 27 [3.50297]

B:BD[3.51265] → [2.4491:4795]

	Summary ImportSummary     `json:"summary" jsonschema:"Import summary with counts and statistics"`
	FileIDs []string          `json:"file_ids" jsonschema:"List of successfully imported file IDs"`
	Errors  []FileImportError `json:"errors,omitempty" jsonschema:"Errors encountered during import (if any)"`

[3.51265]

[3.51575]

	Summary ImportSummary          `json:"summary" jsonschema:"Import summary with counts and statistics"`
	FileIDs []string               `json:"file_ids" jsonschema:"List of successfully imported file IDs"`
	Errors  []utils.FileImportError `json:"errors,omitempty" jsonschema:"Errors encountered during import (if any)"`

Deletion in tools/import_files.go at line 41 [3.50297]

B:BD[3.52031] → [3.52031:52391]

B:BD[3.52414] → [3.52414:52604]

}
// FileImportError records errors encountered during file processing
type FileImportError struct {
	FileName string `json:"file_name"`
	Error    string `json:"error"`
	Stage    string `json:"stage"` // "scan", "hash", "parse", "validate", "insert"
}
// fileData holds all data for a single file to be imported
type fileData struct {
	FileName       string
	Hash           string
	Duration       float64
	SampleRate     int
	TimestampLocal time.Time
	IsAudioMoth    bool
	MothData       *utils.AudioMothData
	AstroData      utils.AstronomicalData

Replacement in tools/import_files.go at line 58 [3.50297]
B:BD[3.53003] → [3.53003:53031]
```
	// Phase 1: Validate input
```
[3.53003]
[3.53031]
```
	// Validate database hierarchy (dataset → location → cluster)
```

Replacement in tools/import_files.go at line 63 [3.50297]

B:BD[3.53158] → [3.53158:53257]

	// Phase 2: Scan folder for WAV files
	wavFiles, err := scanWAVFiles(input.FolderPath, recursive)

[3.53158]

[3.53257]

	// Open database
	database, err := db.OpenWriteableDB(dbPath)

Replacement in tools/import_files.go at line 66 [3.50297]

B:BD[3.53274] → [3.53274:53618]

		return nil, output, fmt.Errorf("failed to scan folder: %w", err)
	}
	if len(wavFiles) == 0 {
		output = ImportAudioFilesOutput{
			Summary: ImportSummary{
				TotalFiles:     0,
				ProcessingTime: time.Since(startTime).String(),
			},
			FileIDs: []string{},
			Errors:  []FileImportError{},
		}
		return &mcp.CallToolResult{}, output, nil

[3.53274]

[3.53618]

		return nil, output, fmt.Errorf("failed to open database: %w", err)

Insertion in tools/import_files.go at line 68 [3.50297]
[3.53621]
[3.53621]
```
	defer database.Close()
```

Replacement in tools/import_files.go at line 70 [3.50297]

B:BD[3.53622] → [3.53622:53747]

	// Phase 3: Get location data for astronomical calculations
	locationData, err := getLocationData(dbPath, input.LocationID)

[3.53622]

[3.53747]

	// Set cluster path if empty
	err = utils.EnsureClusterPath(database, input.ClusterID, input.FolderPath)

Replacement in tools/import_files.go at line 73 [3.50297]

B:BD[3.53764] → [3.53764:53837]

		return nil, output, fmt.Errorf("failed to get location data: %w", err)

[3.53764]

[3.53837]

		return nil, output, fmt.Errorf("failed to set cluster path: %w", err)

Replacement in tools/import_files.go at line 76 [3.50297]

B:BD[3.53841] → [4.8036:8145]

	// Phase 3.5: Set cluster path if empty
	err = ensureClusterPath(dbPath, input.ClusterID, input.FolderPath)

[3.53841]

[4.8145]

	// Import the cluster (ALL THE LOGIC IS HERE)
	clusterOutput, err := utils.ImportCluster(database, utils.ClusterImportInput{
		FolderPath: input.FolderPath,
		DatasetID:  input.DatasetID,
		LocationID: input.LocationID,
		ClusterID:  input.ClusterID,
		Recursive:  recursive,
	})

Replacement in tools/import_files.go at line 85 [3.50297]

B:BD[4.8162] → [4.8162:8234]

		return nil, output, fmt.Errorf("failed to set cluster path: %w", err)

[4.8162]

[4.8234]

		return nil, output, fmt.Errorf("cluster import failed: %w", err)

Replacement in tools/import_files.go at line 88 [3.50297]

∅:D[4.8238] → [3.53841:54887]

B:BD[3.53841] → [3.53841:54887]

	// Phase 4: Process all files (extract metadata, calculate hashes, etc.)
	filesData, errors := processFiles(wavFiles, locationData)
	// Phase 5: Insert into database
	importedFiles, skippedFiles, insertErrors := insertFilesIntoDB(
		dbPath,
		filesData,
		input.DatasetID,
		input.ClusterID,
		input.LocationID,
	)
	// Combine all errors
	allErrors := append(errors, insertErrors...)
	// Calculate summary
	audiomothCount := 0
	totalDuration := 0.0
	for _, fd := range filesData {
		if fd.IsAudioMoth {
			audiomothCount++
		}
		totalDuration += fd.Duration
	}
	summary := ImportSummary{
		TotalFiles:     len(wavFiles),
		ImportedFiles:  importedFiles,
		SkippedFiles:   skippedFiles,
		FailedFiles:    len(allErrors),
		AudioMothFiles: audiomothCount,
		TotalDuration:  totalDuration,
		ProcessingTime: time.Since(startTime).String(),
	}
	// Collect file IDs
	fileIDs := make([]string, 0, len(filesData))
	// Note: File IDs are generated during insert, not tracked here
	// This would require refactoring insertFilesIntoDB to return IDs

[4.8238]

[3.54887]

	// Map to MCP output format

Replacement in tools/import_files.go at line 90 [3.50297]

B:BD[3.54921] → [3.54921:54983]

		Summary: summary,
		FileIDs: fileIDs,
		Errors:  allErrors,

[3.54921]

[3.54983]

		Summary: ImportSummary{
			TotalFiles:     clusterOutput.TotalFiles,
			ImportedFiles:  clusterOutput.ImportedFiles,
			SkippedFiles:   clusterOutput.SkippedFiles,
			FailedFiles:    clusterOutput.FailedFiles,
			AudioMothFiles: clusterOutput.AudioMothFiles,
			TotalDuration:  clusterOutput.TotalDuration,
			ProcessingTime: time.Since(startTime).String(),
		},
		FileIDs: []string{}, // File IDs not tracked currently
		Errors:  clusterOutput.Errors,

Deletion in tools/import_files.go at line 161 [3.50297]

B:BD[3.57065] → [3.57065:57762]

B:BD[3.57762] → [4.8239:9226]

∅:D[4.9226] → [3.57762:63077]

B:BD[3.57762] → [3.57762:63077]

B:BD[3.63107] → [3.63107:63381]

}
// locationData holds location information needed for processing
type locationData struct {
	Latitude   float64
	Longitude  float64
	TimezoneID string
}
// getLocationData retrieves location coordinates and timezone
func getLocationData(dbPath, locationID string) (*locationData, error) {
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return nil, err
	}
	defer database.Close()
	var loc locationData
	err = database.QueryRow(
		"SELECT latitude, longitude, timezone_id FROM location WHERE id = ?",
		locationID,
	).Scan(&loc.Latitude, &loc.Longitude, &loc.TimezoneID)
	if err != nil {
		return nil, fmt.Errorf("failed to query location data: %w", err)
	}
	return &loc, nil
}
// ensureClusterPath sets the cluster's path field if it's currently empty
func ensureClusterPath(dbPath, clusterID, folderPath string) error {
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	// Check if cluster already has a path
	var currentPath sql.NullString
	err = database.QueryRow("SELECT path FROM cluster WHERE id = ?", clusterID).Scan(&currentPath)
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}
	// If path is already set, skip
	if currentPath.Valid && currentPath.String != "" {
		return nil
	}
	// Normalize folder path
	normalizedPath := utils.NormalizeFolderPath(folderPath)
	// Update cluster with normalized path
	_, err = database.Exec(
		"UPDATE cluster SET path = ?, last_modified = now() WHERE id = ?",
		normalizedPath,
		clusterID,
	)
	if err != nil {
		return fmt.Errorf("failed to update cluster path: %w", err)
	}
	return nil
}
// scanWAVFiles recursively scans a folder for WAV files, excluding Clips_* subfolders
func scanWAVFiles(rootPath string, recursive bool) ([]string, error) {
	var wavFiles []string
	if recursive {
		err := filepath.Walk(rootPath, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}
			// Skip "Clips_*" directories
			if info.IsDir() && strings.HasPrefix(info.Name(), "Clips_") {
				return filepath.SkipDir
			}
			// Check for WAV files
			if !info.IsDir() {
				ext := strings.ToLower(filepath.Ext(path))
				if ext == ".wav" && info.Size() > 0 {
					wavFiles = append(wavFiles, path)
				}
			}
			return nil
		})
		if err != nil {
			return nil, err
		}
	} else {
		// Non-recursive: scan only top level
		entries, err := os.ReadDir(rootPath)
		if err != nil {
			return nil, err
		}
		for _, entry := range entries {
			if !entry.IsDir() {
				name := entry.Name()
				ext := strings.ToLower(filepath.Ext(name))
				if ext == ".wav" {
					path := filepath.Join(rootPath, name)
					if info, err := os.Stat(path); err == nil && info.Size() > 0 {
						wavFiles = append(wavFiles, path)
					}
				}
			}
		}
	}
	// Sort for consistent processing order
	sort.Strings(wavFiles)
	return wavFiles, nil
}
// processFiles extracts metadata and calculates hashes for all files
func processFiles(wavFiles []string, location *locationData) ([]*fileData, []FileImportError) {
	var filesData []*fileData
	var errors []FileImportError
	// Step 1: Extract WAV metadata from all files
	type wavInfo struct {
		path     string
		metadata *utils.WAVMetadata
		err      error
	}
	wavInfos := make([]wavInfo, len(wavFiles))
	for i, path := range wavFiles {
		metadata, err := utils.ParseWAVHeader(path)
		wavInfos[i] = wavInfo{path: path, metadata: metadata, err: err}
	}
	// Step 2: Collect filenames for batch timestamp parsing
	var filenamesForParsing []string
	var filenameIndices []int
	for i, info := range wavInfos {
		if info.err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    info.err.Error(),
				Stage:    "parse",
			})
			continue
		}
		// Check if file has timestamp filename format
		if utils.HasTimestampFilename(info.path) {
			filenamesForParsing = append(filenamesForParsing, filepath.Base(info.path))
			filenameIndices = append(filenameIndices, i)
		}
	}
	// Step 3: Parse filename timestamps in batch (if any)
	filenameTimestampMap := make(map[int]time.Time) // Maps file index to timestamp
	if len(filenamesForParsing) > 0 {
		filenameTimestamps, err := utils.ParseFilenameTimestamps(filenamesForParsing)
		if err != nil {
			// If batch parsing fails, record error for all files
			for _, idx := range filenameIndices {
				errors = append(errors, FileImportError{
					FileName: filepath.Base(wavInfos[idx].path),
					Error:    fmt.Sprintf("filename timestamp parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		} else {
			// Apply timezone offset
			adjustedTimestamps, err := utils.ApplyTimezoneOffset(filenameTimestamps, location.TimezoneID)
			if err != nil {
				for _, idx := range filenameIndices {
					errors = append(errors, FileImportError{
						FileName: filepath.Base(wavInfos[idx].path),
						Error:    fmt.Sprintf("timezone offset failed: %v", err),
						Stage:    "parse",
					})
				}
			} else {
				// Build map from file index to timestamp
				for j, idx := range filenameIndices {
					filenameTimestampMap[idx] = adjustedTimestamps[j]
				}
			}
		}
	}
	// Step 4: Process each file
	for i, info := range wavInfos {
		if info.err != nil {
			continue // Already recorded error
		}
		// Calculate hash
		hash, err := utils.ComputeXXH64(info.path)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    fmt.Sprintf("hash calculation failed: %v", err),
				Stage:    "hash",
			})
			continue
		}
		// Determine timestamp
		var timestampLocal time.Time
		var isAudioMoth bool
		var mothData *utils.AudioMothData
		// Try AudioMoth comment first
		if utils.IsAudioMoth(info.metadata.Comment, info.metadata.Artist) {
			isAudioMoth = true
			mothData, err = utils.ParseAudioMothComment(info.metadata.Comment)
			if err == nil {
				timestampLocal = mothData.Timestamp
			} else {
				// AudioMoth detected but parsing failed - try filename
				errors = append(errors, FileImportError{
					FileName: filepath.Base(info.path),
					Error:    fmt.Sprintf("AudioMoth comment parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		}
		// If no AudioMoth timestamp, use filename timestamp
		if timestampLocal.IsZero() {
			if ts, ok := filenameTimestampMap[i]; ok {
				timestampLocal = ts
			}
		}
		// If still no timestamp, skip file
		if timestampLocal.IsZero() {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    "no timestamp available (not AudioMoth and filename not parseable)",
				Stage:    "parse",
			})
			continue
		}
		// Calculate astronomical data
		astroData := utils.CalculateAstronomicalData(
			timestampLocal.UTC(),
			info.metadata.Duration,
			location.Latitude,
			location.Longitude,
		)
		// Add to results
		filesData = append(filesData, &fileData{
			FileName:       filepath.Base(info.path),
			Hash:           hash,
			Duration:       info.metadata.Duration,
			SampleRate:     info.metadata.SampleRate,
			TimestampLocal: timestampLocal,
			IsAudioMoth:    isAudioMoth,
			MothData:       mothData,
			AstroData:      astroData,
		})
	}
	return filesData, errors

Deletion in tools/import_files.go at line 162 [3.50297]

B:BD[3.63383] → [3.63383:64338]

B:BD[3.64338] → [4.9227:9287]

∅:D[4.9287] → [3.64404:64529]

B:BD[3.64404] → [3.64404:64529]

B:BD[3.64529] → [4.9288:9353]

∅:D[4.9353] → [3.64617:64969]

∅:D[5.63026] → [3.64617:64969]

B:BD[3.64617] → [3.64617:64969]

B:BD[3.64969] → [5.63027:63057]

∅:D[5.63057] → [3.65019:65426]

B:BD[3.65019] → [3.65019:65426]

B:BD[3.65426] → [5.63058:63108]

∅:D[5.63108] → [3.65496:66215]

B:BD[3.65496] → [3.65496:66215]

B:BD[3.66215] → [2.4858:4898]

∅:D[2.4898] → [3.66320:66577]

∅:D[6.8852] → [3.66320:66577]

B:BD[3.66320] → [3.66320:66577]

B:BD[3.66577] → [4.9354:9399]

∅:D[4.9399] → [3.66635:68040]

B:BD[3.66635] → [3.66635:68040]


// insertFilesIntoDB inserts all file data into database in a single transaction
func insertFilesIntoDB(
	dbPath string,
	filesData []*fileData,
	datasetID, clusterID, locationID string,
) (imported, skipped int, errors []FileImportError) {
	// Open writable database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to open database: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer database.Close()
	// Begin transaction
	ctx := context.Background()
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to begin transaction: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer tx.Rollback() // Rollback if not committed
	// Prepare statements
	fileStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file (
			id, file_name, xxh64_hash, location_id, timestamp_local,
			cluster_id, duration, sample_rate, maybe_solar_night, maybe_civil_night,
			moon_phase, created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare file statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer fileStmt.Close()
	datasetStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file_dataset (file_id, dataset_id, created_at, last_modified)
		VALUES (?, ?, now(), now())
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare dataset statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer datasetStmt.Close()
	mothStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO moth_metadata (
			file_id, timestamp, recorder_id, gain, battery_v, temp_c,
			created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare moth statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer mothStmt.Close()
	// Insert each file
	for _, fd := range filesData {
		// Check for duplicate hash
		var exists bool
		err = tx.QueryRowContext(ctx,
			"SELECT EXISTS(SELECT 1 FROM file WHERE xxh64_hash = ?)",
			fd.Hash,
		).Scan(&exists)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("duplicate check failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		if exists {
			skipped++
			continue
		}
		// Generate file ID
		fileID, err := utils.GenerateLongID()
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("ID generation failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file record
		_, err = fileStmt.ExecContext(ctx,
			fileID, fd.FileName, fd.Hash, locationID,
			fd.TimestampLocal, clusterID, fd.Duration, fd.SampleRate,
			fd.AstroData.SolarNight, fd.AstroData.CivilNight, fd.AstroData.MoonPhase,
		)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file_dataset junction
		_, err = datasetStmt.ExecContext(ctx, fileID, datasetID)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file_dataset insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// If AudioMoth, insert moth_metadata
		if fd.IsAudioMoth && fd.MothData != nil {
			_, err = mothStmt.ExecContext(ctx,
				fileID,
				fd.MothData.Timestamp,
				&fd.MothData.RecorderID,
				&fd.MothData.Gain,
				&fd.MothData.BatteryV,
				&fd.MothData.TempC,
			)
			if err != nil {
				errors = append(errors, FileImportError{
					FileName: fd.FileName,
					Error:    fmt.Sprintf("moth_metadata insert failed: %v", err),
					Stage:    "insert",
				})
				continue
			}
		}
		imported++
	}
	// Commit transaction
	err = tx.Commit()
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("transaction commit failed: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	return imported, skipped, errors
}

Insertion in tools/import_file.go at line 37 [7.1]

[7.1609]

}
// locationData holds location information (local wrapper around utils types)
type locationData struct {
	Latitude   float64
	Longitude  float64
	TimezoneID string
}
// fileData holds file metadata (local wrapper around utils types)
type fileData struct {
	FileName       string
	Hash           string
	Duration       float64
	SampleRate     int
	TimestampLocal time.Time
	IsAudioMoth    bool
	MothData       *utils.AudioMothData
	AstroData      utils.AstronomicalData

Insertion in tools/import_file.go at line 273 [7.1]

[7.10192]


// getLocationData retrieves location coordinates and timezone from database
func getLocationData(dbPath, locationID string) (*locationData, error) {
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return nil, err
	}
	defer database.Close()
	var loc locationData
	err = database.QueryRow(
		"SELECT latitude, longitude, timezone_id FROM location WHERE id = ?",
		locationID,
	).Scan(&loc.Latitude, &loc.Longitude, &loc.TimezoneID)
	if err != nil {
		return nil, fmt.Errorf("failed to query location data: %w", err)
	}
	return &loc, nil
}
// ensureClusterPath sets the cluster's path field if it's currently empty
func ensureClusterPath(dbPath, clusterID, folderPath string) error {
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	return utils.EnsureClusterPath(database, clusterID, folderPath)
}

Replacement in tools/bulk_file_import.go at line 358 [8.1]

B:BD[8.11482] → [8.11482:11800]

	// Get location data
	var latitude, longitude float64
	var timezoneID string
	err := database.QueryRow(`
		SELECT latitude, longitude, timezone_id
		FROM location
		WHERE id = ?
	`, locationID).Scan(&latitude, &longitude, &timezoneID)
	if err != nil {
		return nil, fmt.Errorf("failed to get location data: %v", err)

[8.11482]

[8.11800]

	// Check if directory exists
	if _, err := os.Stat(folderPath); os.IsNotExist(err) {
		logger.Log("  WARNING: Directory not found, skipping")
		return stats, nil

Replacement in tools/bulk_file_import.go at line 364 [8.1]

B:BD[8.11804] → [8.11804:12120]

	// Find all WAV files recursively
	var wavFiles []string
	err = filepath.Walk(folderPath, func(path string, info os.FileInfo, err error) error {
		if err != nil {
			return err
		}
		if !info.IsDir() && strings.HasSuffix(strings.ToLower(info.Name()), ".wav") {
			wavFiles = append(wavFiles, path)
		}
		return nil

[8.11804]

[8.12120]

	// Import the cluster (SAME LOGIC AS import_files.go)
	logger.Log("  Importing cluster...")
	clusterOutput, err := utils.ImportCluster(database, utils.ClusterImportInput{
		FolderPath: folderPath,
		DatasetID:  datasetID,
		LocationID: locationID,
		ClusterID:  clusterID,
		Recursive:  true,

Replacement in tools/bulk_file_import.go at line 374 [8.1]
B:BD[8.12141] → [8.12141:12203]
```
		return nil, fmt.Errorf("failed to scan directory: %v", err)
```
[8.12141]
[8.12203]
```
		return nil, err
```

Replacement in tools/bulk_file_import.go at line 377 [8.1]

B:BD[8.12207] → [8.12207:12292]

	stats.TotalFiles = len(wavFiles)
	if stats.TotalFiles == 0 {
		return stats, nil
	}

[8.12207]

[8.12292]

	// Map to bulk import stats
	stats.TotalFiles = clusterOutput.TotalFiles
	stats.ImportedFiles = clusterOutput.ImportedFiles
	stats.DuplicateFiles = clusterOutput.SkippedFiles
	stats.ErrorFiles = clusterOutput.FailedFiles

Replacement in tools/bulk_file_import.go at line 383 [8.1]

B:BD[8.12293] → [8.12293:12471]

	// Process each file
	for i, filePath := range wavFiles {
		// Log progress periodically
		if (i+1)%100 == 0 {
			logger.Log("    Processing file %d/%d...", i+1, len(wavFiles))

[8.12293]

[8.12471]

	// Log errors
	for i, fileErr := range clusterOutput.Errors {
		if i < 5 { // Log first 5
			logger.Log("    ERROR: %s: %s", fileErr.FileName, fileErr.Error)

Insertion in tools/bulk_file_import.go at line 388 [8.1]
[8.12475]
[8.12475]
```
	}
```

Replacement in tools/bulk_file_import.go at line 390 [8.1]

B:BD[8.12476] → [8.12476:12904]

		err := bulkImportSingleFile(database, filePath, datasetID, locationID, clusterID, latitude, longitude, timezoneID)
		if err != nil {
			if strings.Contains(err.Error(), "duplicate") {
				stats.DuplicateFiles++
			} else {
				stats.ErrorFiles++
				// Log first few errors
				if stats.ErrorFiles <= 5 {
					logger.Log("    ERROR: %s: %v", filepath.Base(filePath), err)
				}
			}
		} else {
			stats.ImportedFiles++
		}
	}

[8.12476]

[8.12904]

	logger.Log("  Complete: %d imported, %d duplicates, %d errors", stats.ImportedFiles, stats.DuplicateFiles, stats.ErrorFiles)

Deletion in tools/bulk_file_import.go at line 393 [8.1]

B:BD[8.12924] → [8.12924:13146]

B:BD[8.13146] → [6.9448:9611]

∅:D[6.9611] → [8.13208:13225]

B:BD[8.13208] → [8.13208:13225]

B:BD[8.13225] → [6.9612:9625]

∅:D[6.9625] → [8.13281:13309]

B:BD[8.13281] → [8.13281:13309]

B:BD[8.13309] → [6.9626:9692]

∅:D[6.9692] → [8.13682:13699]

B:BD[8.13682] → [8.13682:13699]

B:BD[8.13699] → [6.9693:9706]

∅:D[6.9706] → [8.13762:13765]

B:BD[8.13762] → [8.13762:13765]

B:BD[8.13765] → [6.9707:9752]

∅:D[6.9752] → [8.14511:14515]

B:BD[8.14511] → [8.14511:14515]

B:BD[8.14646] → [8.14646:14667]

B:BD[8.14667] → [2.5584:5623]

∅:D[2.5623] → [8.14771:15244]

∅:D[6.9792] → [8.14771:15244]

B:BD[8.14771] → [8.14771:15244]

B:BD[8.15244] → [6.9793:9974]

∅:D[6.9974] → [8.15383:15484]

B:BD[8.15383] → [8.15383:15484]

}
// bulkImportSingleFile imports a single WAV file into the database
func bulkImportSingleFile(database *sql.DB, filePath, datasetID, locationID, clusterID string, latitude, longitude float64, timezoneID string) error {
	// Process file: WAV parse → hash → timestamp → astronomical data
	result, err := utils.ProcessSingleFile(filePath, latitude, longitude, timezoneID, false)
	if err != nil {
		return err
	}
	// Check for duplicate
	_, isDup, err := utils.CheckDuplicateHash(database, result.Hash)
	if err != nil {
		return err
	}
	if isDup {
		return fmt.Errorf("duplicate")
	}
	// Generate file ID
	fileID, err := utils.GenerateLongID()
	if err != nil {
		return fmt.Errorf("ID generation failed: %v", err)
	}
	// Insert into database
	now := time.Now().UTC()
	_, err = database.Exec(`
		INSERT INTO file (
			id, location_id, cluster_id,
			file_name, xxh64_hash, duration, sample_rate,
			timestamp_local, maybe_solar_night, maybe_civil_night,
			moon_phase,
			active, created_at, last_modified
		) VALUES (
			?, ?, ?,
			?, ?, ?, ?,
			?, ?, ?,
			?,
			true, ?, ?
		)
	`, fileID, locationID, clusterID,
		result.FileName, result.Hash, result.Duration, result.SampleRate,
		result.TimestampLocal, result.AstroData.SolarNight, result.AstroData.CivilNight,
		result.AstroData.MoonPhase,
		now, now)
	if err != nil {
		return fmt.Errorf("database insert failed: %v", err)
	}
	return nil