package utils
import (
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"sort"
	"strings"
)
// SpeciesMapping maps .data species/calltype names to DB labels
type SpeciesMapping struct {
	Species   string            `json:"species"`
	Calltypes map[string]string `json:"calltypes,omitempty"`
}
// MappingFile represents the complete mapping file structure
// Key is the .data file species name
type MappingFile map[string]SpeciesMapping
// LoadMappingFile loads and parses a mapping JSON file
func LoadMappingFile(path string) (MappingFile, error) {
	data, err := os.ReadFile(path)
	if err != nil {
		return nil, fmt.Errorf("failed to read mapping file: %w", err)
	}
	var mapping MappingFile
	if err := json.Unmarshal(data, &mapping); err != nil {
		return nil, fmt.Errorf("failed to parse mapping JSON: %w", err)
	}
	// Validate non-empty
	if len(mapping) == 0 {
		return nil, fmt.Errorf("mapping file is empty")
	}
	// Validate each entry has species
	for dataSpecies, sm := range mapping {
		if sm.Species == "" {
			return nil, fmt.Errorf("mapping entry '%s' has empty species field", dataSpecies)
		}
	}
	return mapping, nil
}
// MappingValidationResult contains validation errors for a mapping
type MappingValidationResult struct {
	MissingSpecies   []string          // .data species not in mapping
	MissingDBSpecies []string          // mapped species not in DB
	MissingCalltypes map[string]string // "dataSpecies/dataCalltype" -> "dbSpecies/dbCalltype"
}
// HasErrors returns true if any validation errors exist
func (r MappingValidationResult) HasErrors() bool {
	return len(r.MissingSpecies) > 0 ||
		len(r.MissingDBSpecies) > 0 ||
		len(r.MissingCalltypes) > 0
}
// Error returns a formatted error message
func (r MappingValidationResult) Error() string {
	var parts []string
	if len(r.MissingSpecies) > 0 {
		parts = append(parts, fmt.Sprintf("species in .data but not in mapping: [%s]",
			strings.Join(r.MissingSpecies, ", ")))
	}
	if len(r.MissingDBSpecies) > 0 {
		parts = append(parts, fmt.Sprintf("mapped species not found in DB: [%s]",
			strings.Join(r.MissingDBSpecies, ", ")))
	}
	if len(r.MissingCalltypes) > 0 {
		var ctErrors []string
		for k, v := range r.MissingCalltypes {
			ctErrors = append(ctErrors, fmt.Sprintf("%s->%s", k, v))
		}
		sort.Strings(ctErrors)
		parts = append(parts, fmt.Sprintf("calltypes not found in DB: [%s]",
			strings.Join(ctErrors, ", ")))
	}
	return strings.Join(parts, "; ")
}
// ValidateMappingAgainstDB validates that all mapped species and calltypes exist in the database
// Also validates that the mapping covers all species/calltypes found in .data files
func ValidateMappingAgainstDB(
	db *sql.DB,
	mapping MappingFile,
	dataSpeciesSet map[string]bool,
	dataCalltypes map[string]map[string]bool, // species -> calltype -> true
) (MappingValidationResult, error) {
	result := MappingValidationResult{
		MissingSpecies:   make([]string, 0),
		MissingDBSpecies: make([]string, 0),
		MissingCalltypes: make(map[string]string),
	}
	// Check all .data species are in mapping
	for species := range dataSpeciesSet {
		if _, exists := mapping[species]; !exists {
			result.MissingSpecies = append(result.MissingSpecies, species)
		}
	}
	sort.Strings(result.MissingSpecies)
	// Collect all mapped species and calltypes
	mappedSpeciesSet := make(map[string]bool)
	mappedCalltypes := make(map[string]map[string]string) // dbSpecies -> dbCalltype -> dataCalltype
	for _, sm := range mapping {
		mappedSpeciesSet[sm.Species] = true
		// Track calltype mappings
		if len(sm.Calltypes) > 0 {
			if mappedCalltypes[sm.Species] == nil {
				mappedCalltypes[sm.Species] = make(map[string]string)
			}
			for dataCT, dbCT := range sm.Calltypes {
				mappedCalltypes[sm.Species][dbCT] = dataCT
			}
		}
	}
	// Also collect unmapped calltypes (where .data calltype = DB calltype)
	for dataSpecies, ctSet := range dataCalltypes {
		sm, exists := mapping[dataSpecies]
		if !exists {
			continue // Already reported as missing species
		}
		dbSpecies := sm.Species
		for dataCT := range ctSet {
			// If no explicit mapping, assume dataCT == dbCT
			dbCT := dataCT
			if sm.Calltypes != nil {
				if mapped, ok := sm.Calltypes[dataCT]; ok {
					dbCT = mapped
				}
			}
			if mappedCalltypes[dbSpecies] == nil {
				mappedCalltypes[dbSpecies] = make(map[string]string)
			}
			mappedCalltypes[dbSpecies][dbCT] = dataCT
		}
	}
	// Validate species exist in DB
	speciesLabels := make([]string, 0, len(mappedSpeciesSet))
	for s := range mappedSpeciesSet {
		speciesLabels = append(speciesLabels, s)
	}
	sort.Strings(speciesLabels)
	if len(speciesLabels) > 0 {
		query := `SELECT label FROM species WHERE label IN (` + placeholders(len(speciesLabels)) + `) AND active = true`
		args := make([]any, len(speciesLabels))
		for i, s := range speciesLabels {
			args[i] = s
		}
		rows, err := db.Query(query, args...)
		if err != nil {
			return result, fmt.Errorf("failed to query species: %w", err)
		}
		defer rows.Close()
		foundSpecies := make(map[string]bool)
		for rows.Next() {
			var label string
			if err := rows.Scan(&label); err == nil {
				foundSpecies[label] = true
			}
		}
		for _, s := range speciesLabels {
			if !foundSpecies[s] {
				result.MissingDBSpecies = append(result.MissingDBSpecies, s)
			}
		}
	}
	// Validate calltypes exist in DB
	for dbSpecies, ctMap := range mappedCalltypes {
		if len(ctMap) == 0 {
			continue
		}
		ctLabels := make([]string, 0, len(ctMap))
		for dbCT := range ctMap {
			ctLabels = append(ctLabels, dbCT)
		}
		sort.Strings(ctLabels)
		query := `
			SELECT ct.label
			FROM call_type ct
			JOIN species s ON ct.species_id = s.id
			WHERE s.label = ? AND ct.label IN (` + placeholders(len(ctLabels)) + `) AND ct.active = true`
		args := make([]any, 1+len(ctLabels))
		args[0] = dbSpecies
		for i, ct := range ctLabels {
			args[1+i] = ct
		}
		rows, err := db.Query(query, args...)
		if err != nil {
			return result, fmt.Errorf("failed to query calltypes for species %s: %w", dbSpecies, err)
		}
		defer rows.Close()
		foundCT := make(map[string]bool)
		for rows.Next() {
			var label string
			if err := rows.Scan(&label); err == nil {
				foundCT[label] = true
			}
		}
		for dbCT, dataCT := range ctMap {
			if !foundCT[dbCT] {
				key := fmt.Sprintf("%s/%s", dbSpecies, dataCT)
				value := fmt.Sprintf("%s/%s", dbSpecies, dbCT)
				result.MissingCalltypes[key] = value
			}
		}
	}
	return result, nil
}
// GetDBSpecies returns the DB species label for a .data species
func (m MappingFile) GetDBSpecies(dataSpecies string) (string, bool) {
	sm, exists := m[dataSpecies]
	if !exists {
		return "", false
	}
	return sm.Species, true
}
// GetDBCalltype returns the DB calltype label for a .data species/calltype
// Returns the dataCalltype unchanged if no mapping exists
func (m MappingFile) GetDBCalltype(dataSpecies, dataCalltype string) string {
	sm, exists := m[dataSpecies]
	if !exists || sm.Calltypes == nil {
		return dataCalltype
	}
	if dbCT, ok := sm.Calltypes[dataCalltype]; ok {
		return dbCT
	}
	return dataCalltype
}
// placeholders generates SQL placeholder string for IN clauses
func placeholders(n int) string {
	if n == 0 {
		return ""
	}
	ph := make([]string, n)
	for i := range ph {
		ph[i] = "?"
	}
	return strings.Join(ph, ", ")
}

package tools
import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"
	"skraak/db"
	"skraak/utils"
)
// ImportSegmentsInput defines the input parameters for the import_segments tool
type ImportSegmentsInput struct {
	Folder          string `json:"folder" jsonschema:"required,Path to folder containing .data files"`
	Mapping         string `json:"mapping" jsonschema:"required,Path to mapping JSON file"`
	DatasetID       string `json:"dataset_id" jsonschema:"required,Dataset ID (12-character nanoid)"`
	LocationID      string `json:"location_id" jsonschema:"required,Location ID (12-character nanoid)"`
	ClusterID       string `json:"cluster_id" jsonschema:"required,Cluster ID (12-character nanoid)"`
	ProgressHandler func(processed, total int, message string)
}
// ImportSegmentsOutput defines the output structure for the import_segments tool
type ImportSegmentsOutput struct {
	Summary  ImportSegmentsSummary `json:"summary"`
	Segments []SegmentImport       `json:"segments"`
	Errors   []ImportSegmentError  `json:"errors,omitempty"`
}
// ImportSegmentsSummary provides summary statistics for the import operation
type ImportSegmentsSummary struct {
	DataFilesFound     int   `json:"data_files_found"`
	DataFilesProcessed int   `json:"data_files_processed"`
	TotalSegments      int   `json:"total_segments"`
	ImportedSegments   int   `json:"imported_segments"`
	ImportedLabels     int   `json:"imported_labels"`
	ImportedSubtypes   int   `json:"imported_subtypes"`
	SkippedBookmarks   int   `json:"skipped_bookmarks"`
	ProcessingTimeMs   int64 `json:"processing_time_ms"`
}
// SegmentImport represents an imported segment in the output
type SegmentImport struct {
	SegmentID  string         `json:"segment_id"`
	FileName   string         `json:"file_name"`
	StartTime  float64        `json:"start_time"`
	EndTime    float64        `json:"end_time"`
	FreqLow    float64        `json:"freq_low"`
	FreqHigh   float64        `json:"freq_high"`
	Labels     []LabelImport  `json:"labels"`
}
// LabelImport represents an imported label in the output
type LabelImport struct {
	LabelID   string  `json:"label_id"`
	Species   string  `json:"species"`
	CallType  string  `json:"calltype,omitempty"`
	Filter    string  `json:"filter"`
	Certainty int     `json:"certainty"`
	Comment   string  `json:"comment,omitempty"`
}
// ImportSegmentError records errors encountered during segment import
type ImportSegmentError struct {
	File    string `json:"file,omitempty"`
	Stage   string `json:"stage"` // "validation", "hash", "import"
	Message string `json:"message"`
}
// scannedDataFile holds parsed data for a .data file
type scannedDataFile struct {
	DataPath  string
	WavPath   string
	WavHash   string
	FileID    string
	Duration  float64
	Segments  []*utils.Segment
}
// ImportSegments imports segments from AviaNZ .data files into the database
func ImportSegments(ctx context.Context, input ImportSegmentsInput) (ImportSegmentsOutput, error) {
	startTime := time.Now()
	var output ImportSegmentsOutput
	output.Segments = make([]SegmentImport, 0)
	output.Errors = make([]ImportSegmentError, 0)
	// Phase A: Input Validation
	if err := validateSegmentImportInput(input); err != nil {
		return output, err
	}
	// Load mapping file
	mapping, err := utils.LoadMappingFile(input.Mapping)
	if err != nil {
		return output, fmt.Errorf("failed to load mapping file: %w", err)
	}
	// Find .data files
	dataFiles, err := utils.FindDataFiles(input.Folder)
	if err != nil {
		return output, fmt.Errorf("failed to find .data files: %w", err)
	}
	output.Summary.DataFilesFound = len(dataFiles)
	if len(dataFiles) == 0 {
		return output, fmt.Errorf("no .data files found in folder: %s", input.Folder)
	}
	// Phase B: Parse all .data files and collect unique values
	scannedFiles, parseErrors, uniqueFilters, uniqueSpecies, uniqueCalltypes := scanAllDataFiles(dataFiles, input.Folder)
	output.Errors = append(output.Errors, parseErrors...)
	if len(scannedFiles) == 0 {
		output.Summary.ProcessingTimeMs = time.Since(startTime).Milliseconds()
		return output, nil
	}
	// Phase C: Pre-Import Validation
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	// Validate dataset/location/cluster hierarchy
	if err := validateSegmentHierarchy(database, input.DatasetID, input.LocationID, input.ClusterID); err != nil {
		return output, err
	}
	// Validate all filters exist
	filterIDMap, err := validateFiltersExist(database, uniqueFilters)
	if err != nil {
		return output, fmt.Errorf("filter validation failed: %w", err)
	}
	// Validate mapping covers all species/calltypes and they exist in DB
	validationResult, err := utils.ValidateMappingAgainstDB(database, mapping, uniqueSpecies, uniqueCalltypes)
	if err != nil {
		return output, fmt.Errorf("mapping validation failed: %w", err)
	}
	if validationResult.HasErrors() {
		return output, fmt.Errorf("mapping validation failed: %s", validationResult.Error())
	}
	// Load species and calltype ID maps
	speciesIDMap, calltypeIDMap, err := loadSpeciesCalltypeIDs(database, mapping, uniqueSpecies, uniqueCalltypes)
	if err != nil {
		return output, fmt.Errorf("failed to load species/calltype IDs: %w", err)
	}
	// Validate files: hash exists, no existing labels
	fileIDMap, hashErrors := validateAndMapFiles(database, scannedFiles, input.ClusterID)
	output.Errors = append(output.Errors, hashErrors...)
	if len(fileIDMap) == 0 && len(scannedFiles) > 0 {
		output.Summary.ProcessingTimeMs = time.Since(startTime).Milliseconds()
		return output, nil
	}
	// Phase D: Transactional Import
	importedSegments, importedLabels, importedSubtypes, skippedBookmarks, importErrors := importSegmentsIntoDB(
		ctx, database, fileIDMap, scannedFiles, mapping, filterIDMap, speciesIDMap, calltypeIDMap, input.DatasetID, input.ProgressHandler,
	)
	output.Errors = append(output.Errors, importErrors...)
	// Build output segments
	for _, seg := range importedSegments {
		output.Segments = append(output.Segments, seg)
	}
	output.Summary.DataFilesProcessed = len(fileIDMap)
	output.Summary.TotalSegments = countTotalSegments(scannedFiles)
	output.Summary.ImportedSegments = len(importedSegments)
	output.Summary.ImportedLabels = importedLabels
	output.Summary.ImportedSubtypes = importedSubtypes
	output.Summary.SkippedBookmarks = skippedBookmarks
	output.Summary.ProcessingTimeMs = time.Since(startTime).Milliseconds()
	return output, nil
}
// validateSegmentImportInput validates input parameters
func validateSegmentImportInput(input ImportSegmentsInput) error {
	// Validate folder exists
	if info, err := os.Stat(input.Folder); err != nil {
		return fmt.Errorf("folder does not exist: %s", input.Folder)
	} else if !info.IsDir() {
		return fmt.Errorf("path is not a folder: %s", input.Folder)
	}
	// Validate mapping file exists
	if _, err := os.Stat(input.Mapping); err != nil {
		return fmt.Errorf("mapping file does not exist: %s", input.Mapping)
	}
	// Validate IDs
	if err := utils.ValidateShortID(input.DatasetID, "dataset_id"); err != nil {
		return err
	}
	if err := utils.ValidateShortID(input.LocationID, "location_id"); err != nil {
		return err
	}
	if err := utils.ValidateShortID(input.ClusterID, "cluster_id"); err != nil {
		return err
	}
	return nil
}
// validateSegmentHierarchy validates dataset/location/cluster relationships
func validateSegmentHierarchy(dbConn *sql.DB, datasetID, locationID, clusterID string) error {
	// Validate dataset exists and is structured
	var datasetType string
	err := dbConn.QueryRow(`SELECT type FROM dataset WHERE id = ? AND active = true`, datasetID).Scan(&datasetType)
	if err == sql.ErrNoRows {
		return fmt.Errorf("dataset not found: %s", datasetID)
	}
	if err != nil {
		return fmt.Errorf("failed to query dataset: %w", err)
	}
	if datasetType != "structured" {
		return fmt.Errorf("dataset must be 'structured' type, got: %s", datasetType)
	}
	// Validate location belongs to dataset
	var locationExists bool
	err = dbConn.QueryRow(`
		SELECT EXISTS(SELECT 1 FROM location WHERE id = ? AND dataset_id = ? AND active = true)
	`, locationID, datasetID).Scan(&locationExists)
	if err != nil {
		return fmt.Errorf("failed to query location: %w", err)
	}
	if !locationExists {
		return fmt.Errorf("location not found or not linked to dataset: %s", locationID)
	}
	// Validate cluster belongs to location
	var clusterExists bool
	err = dbConn.QueryRow(`
		SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ? AND location_id = ? AND active = true)
	`, clusterID, locationID).Scan(&clusterExists)
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}
	if !clusterExists {
		return fmt.Errorf("cluster not found or not linked to location: %s", clusterID)
	}
	return nil
}
// scanAllDataFiles parses all .data files and collects unique values
func scanAllDataFiles(dataFiles []string, folder string) (
	[]scannedDataFile,
	[]ImportSegmentError,
	map[string]bool,
	map[string]bool,
	map[string]map[string]bool,
) {
	var scanned []scannedDataFile
	var errors []ImportSegmentError
	uniqueFilters := make(map[string]bool)
	uniqueSpecies := make(map[string]bool)
	uniqueCalltypes := make(map[string]map[string]bool) // species -> calltype -> true
	for _, dataPath := range dataFiles {
		// Find corresponding WAV file
		wavPath := strings.TrimSuffix(dataPath, ".data")
		if _, err := os.Stat(wavPath); err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(dataPath),
				Stage:   "validation",
				Message: fmt.Sprintf("corresponding WAV file not found: %s", filepath.Base(wavPath)),
			})
			continue
		}
		// Parse .data file
		df, err := utils.ParseDataFile(dataPath)
		if err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(dataPath),
				Stage:   "validation",
				Message: fmt.Sprintf("failed to parse .data file: %v", err),
			})
			continue
		}
		// Collect unique filters, species, calltypes
		for _, seg := range df.Segments {
			for _, label := range seg.Labels {
				uniqueFilters[label.Filter] = true
				uniqueSpecies[label.Species] = true
				if label.CallType != "" {
					if uniqueCalltypes[label.Species] == nil {
						uniqueCalltypes[label.Species] = make(map[string]bool)
					}
					uniqueCalltypes[label.Species][label.CallType] = true
				}
			}
		}
		scanned = append(scanned, scannedDataFile{
			DataPath: dataPath,
			WavPath:  wavPath,
			Duration: df.Meta.Duration,
			Segments: df.Segments,
		})
	}
	return scanned, errors, uniqueFilters, uniqueSpecies, uniqueCalltypes
}
// validateFiltersExist checks all filters exist in DB and returns ID map
func validateFiltersExist(dbConn *sql.DB, filterNames map[string]bool) (map[string]string, error) {
	filterIDMap := make(map[string]string)
	if len(filterNames) == 0 {
		return filterIDMap, nil
	}
	names := make([]string, 0, len(filterNames))
	for name := range filterNames {
		names = append(names, name)
	}
	query := `SELECT id, name FROM filter WHERE name IN (` + placeholders(len(names)) + `) AND active = true`
	args := make([]any, len(names))
	for i, name := range names {
		args[i] = name
	}
	rows, err := dbConn.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query filters: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var id, name string
		if err := rows.Scan(&id, &name); err == nil {
			filterIDMap[name] = id
		}
	}
	// Check for missing filters
	var missing []string
	for name := range filterNames {
		if _, exists := filterIDMap[name]; !exists {
			missing = append(missing, name)
		}
	}
	if len(missing) > 0 {
		return nil, fmt.Errorf("filters not found in database: [%s]", strings.Join(missing, ", "))
	}
	return filterIDMap, nil
}
// loadSpeciesCalltypeIDs loads species and calltype ID maps
func loadSpeciesCalltypeIDs(
	dbConn *sql.DB,
	mapping utils.MappingFile,
	uniqueSpecies map[string]bool,
	uniqueCalltypes map[string]map[string]bool,
) (map[string]string, map[string]map[string]string, error) {
	speciesIDMap := make(map[string]string)
	calltypeIDMap := make(map[string]map[string]string) // (dbSpecies, dbCalltype) -> calltype_id
	// Collect all DB species labels from mapping
	dbSpeciesSet := make(map[string]bool)
	for dataSpecies := range uniqueSpecies {
		if dbSpecies, ok := mapping.GetDBSpecies(dataSpecies); ok {
			dbSpeciesSet[dbSpecies] = true
		}
	}
	// Load species IDs
	if len(dbSpeciesSet) > 0 {
		dbSpeciesList := make([]string, 0, len(dbSpeciesSet))
		for s := range dbSpeciesSet {
			dbSpeciesList = append(dbSpeciesList, s)
		}
		query := `SELECT id, label FROM species WHERE label IN (` + placeholders(len(dbSpeciesList)) + `) AND active = true`
		args := make([]any, len(dbSpeciesList))
		for i, s := range dbSpeciesList {
			args[i] = s
		}
		rows, err := dbConn.Query(query, args...)
		if err != nil {
			return nil, nil, fmt.Errorf("failed to query species: %w", err)
		}
		defer rows.Close()
		for rows.Next() {
			var id, label string
			if err := rows.Scan(&id, &label); err == nil {
				speciesIDMap[label] = id
			}
		}
	}
	// Load calltype IDs
	for dataSpecies, ctSet := range uniqueCalltypes {
		dbSpecies, ok := mapping.GetDBSpecies(dataSpecies)
		if !ok {
			continue
		}
		if calltypeIDMap[dbSpecies] == nil {
			calltypeIDMap[dbSpecies] = make(map[string]string)
		}
		for dataCalltype := range ctSet {
			dbCalltype := mapping.GetDBCalltype(dataSpecies, dataCalltype)
			// Query calltype ID
			var calltypeID string
			err := dbConn.QueryRow(`
				SELECT ct.id
				FROM call_type ct
				JOIN species s ON ct.species_id = s.id
				WHERE s.label = ? AND ct.label = ? AND ct.active = true
			`, dbSpecies, dbCalltype).Scan(&calltypeID)
			if err == nil {
				calltypeIDMap[dbSpecies][dbCalltype] = calltypeID
			}
		}
	}
	return speciesIDMap, calltypeIDMap, nil
}
// validateAndMapFiles validates files exist by hash and have no existing labels
func validateAndMapFiles(
	dbConn *sql.DB,
	scannedFiles []scannedDataFile,
	clusterID string,
) (map[string]scannedDataFile, []ImportSegmentError) {
	fileIDMap := make(map[string]scannedDataFile)
	var errors []ImportSegmentError
	for _, sf := range scannedFiles {
		// Compute hash
		hash, err := utils.ComputeXXH64(sf.WavPath)
		if err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.WavPath),
				Stage:   "hash",
				Message: fmt.Sprintf("failed to compute hash: %v", err),
			})
			continue
		}
		sf.WavHash = hash
		// Find file by hash in cluster
		var fileID string
		var duration float64
		err = dbConn.QueryRow(`
			SELECT id, duration FROM file WHERE xxh64_hash = ? AND cluster_id = ? AND active = true
		`, hash, clusterID).Scan(&fileID, &duration)
		if err == sql.ErrNoRows {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.WavPath),
				Stage:   "validation",
				Message: fmt.Sprintf("file hash not found in database for cluster (hash: %s)", hash),
			})
			continue
		}
		if err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.WavPath),
				Stage:   "validation",
				Message: fmt.Sprintf("failed to query file: %v", err),
			})
			continue
		}
		sf.FileID = fileID
		sf.Duration = duration
		// Check no existing labels for this file
		var labelCount int
		err = dbConn.QueryRow(`
			SELECT COUNT(*) FROM label l
			JOIN segment s ON l.segment_id = s.id
			WHERE s.file_id = ? AND l.active = true
		`, fileID).Scan(&labelCount)
		if err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.WavPath),
				Stage:   "validation",
				Message: fmt.Sprintf("failed to check existing labels: %v", err),
			})
			continue
		}
		if labelCount > 0 {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.WavPath),
				Stage:   "validation",
				Message: fmt.Sprintf("file already has %d label(s) - fresh imports only", labelCount),
			})
			continue
		}
		fileIDMap[fileID] = sf
	}
	return fileIDMap, errors
}
// importSegmentsIntoDB performs the transactional import
func importSegmentsIntoDB(
	ctx context.Context,
	database *sql.DB,
	fileIDMap map[string]scannedDataFile,
	scannedFiles []scannedDataFile,
	mapping utils.MappingFile,
	filterIDMap map[string]string,
	speciesIDMap map[string]string,
	calltypeIDMap map[string]map[string]string,
	datasetID string,
	progressHandler func(processed, total int, message string),
) ([]SegmentImport, int, int, int, []ImportSegmentError) {
	var importedSegments []SegmentImport
	var errors []ImportSegmentError
	importedLabels := 0
	importedSubtypes := 0
	skippedBookmarks := 0
	// Begin transaction
	tx, err := db.BeginLoggedTx(ctx, database, "import_segments")
	if err != nil {
		errors = append(errors, ImportSegmentError{
			Stage:   "import",
			Message: fmt.Sprintf("failed to begin transaction: %v", err),
		})
		return nil, 0, 0, 0, errors
	}
	defer tx.Rollback()
	// Process each file
	totalFiles := len(scannedFiles)
	processedFiles := 0
	for _, sf := range scannedFiles {
		if sf.FileID == "" {
			continue // Was filtered out during validation
		}
		processedFiles++
		if progressHandler != nil {
			progressHandler(processedFiles, totalFiles, filepath.Base(sf.DataPath))
		}
		// Update file_metadata with skraak_hash
		_, err = tx.ExecContext(ctx, `
			INSERT INTO file_metadata (file_id, json, created_at, last_modified, active)
			VALUES (?, json('{"skraak_hash": "' || ? || '"}'), now(), now(), true)
			ON CONFLICT (file_id) DO UPDATE SET
				json = json_set(COALESCE(json, '{}'), '$.skraak_hash', ?),
				last_modified = now(),
				active = true
		`, sf.FileID, sf.WavHash, sf.WavHash)
		if err != nil {
			errors = append(errors, ImportSegmentError{
				File:    filepath.Base(sf.DataPath),
				Stage:   "import",
				Message: fmt.Sprintf("failed to update file_metadata: %v", err),
			})
			continue
		}
		// Process segments
		for _, seg := range sf.Segments {
			// Check for bookmarks - skip entire segment if any label is bookmarked
			hasBookmark := false
			for _, label := range seg.Labels {
				if label.Bookmark {
					hasBookmark = true
					break
				}
			}
			if hasBookmark {
				skippedBookmarks++
				continue
			}
			// Validate segment bounds
			if seg.StartTime >= seg.EndTime {
				errors = append(errors, ImportSegmentError{
					File:    filepath.Base(sf.DataPath),
					Stage:   "import",
					Message: fmt.Sprintf("invalid segment bounds: start=%.2f >= end=%.2f", seg.StartTime, seg.EndTime),
				})
				continue
			}
			if seg.EndTime > sf.Duration {
				errors = append(errors, ImportSegmentError{
					File:    filepath.Base(sf.DataPath),
					Stage:   "import",
					Message: fmt.Sprintf("segment end time (%.2f) exceeds file duration (%.2f)", seg.EndTime, sf.Duration),
				})
				continue
			}
			// Insert segment
			segmentID, err := utils.GenerateLongID()
			if err != nil {
				errors = append(errors, ImportSegmentError{
					File:    filepath.Base(sf.DataPath),
					Stage:   "import",
					Message: fmt.Sprintf("failed to generate segment ID: %v", err),
				})
				continue
			}
			_, err = tx.ExecContext(ctx, `
				INSERT INTO segment (id, file_id, dataset_id, start_time, end_time, freq_low, freq_high, created_at, last_modified, active)
				VALUES (?, ?, ?, ?, ?, ?, ?, now(), now(), true)
			`, segmentID, sf.FileID, datasetID, seg.StartTime, seg.EndTime, seg.FreqLow, seg.FreqHigh)
			if err != nil {
				errors = append(errors, ImportSegmentError{
					File:    filepath.Base(sf.DataPath),
					Stage:   "import",
					Message: fmt.Sprintf("failed to insert segment: %v", err),
				})
				continue
			}
			// Process labels
			var segmentImport SegmentImport
			segmentImport.SegmentID = segmentID
			segmentImport.FileName = filepath.Base(sf.WavPath)
			segmentImport.StartTime = seg.StartTime
			segmentImport.EndTime = seg.EndTime
			segmentImport.FreqLow = seg.FreqLow
			segmentImport.FreqHigh = seg.FreqHigh
			segmentImport.Labels = make([]LabelImport, 0)
			for _, label := range seg.Labels {
				// Get DB species and calltype
				dbSpecies, ok := mapping.GetDBSpecies(label.Species)
				if !ok {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("species not found in mapping: %s", label.Species),
					})
					continue
				}
				speciesID, ok := speciesIDMap[dbSpecies]
				if !ok {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("species ID not found: %s", dbSpecies),
					})
					continue
				}
				filterID, ok := filterIDMap[label.Filter]
				if !ok {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("filter ID not found: %s", label.Filter),
					})
					continue
				}
				// Insert label
				labelID, err := utils.GenerateLongID()
				if err != nil {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("failed to generate label ID: %v", err),
					})
					continue
				}
				_, err = tx.ExecContext(ctx, `
					INSERT INTO label (id, segment_id, species_id, filter_id, certainty, created_at, last_modified, active)
					VALUES (?, ?, ?, ?, ?, now(), now(), true)
				`, labelID, segmentID, speciesID, filterID, label.Certainty)
				if err != nil {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("failed to insert label: %v", err),
					})
					continue
				}
				importedLabels++
				// Insert label_metadata
				metadataJSON := fmt.Sprintf(`{"skraak_label_id": "%s"`, labelID)
				if label.Comment != "" {
					// Escape quotes in comment
					escapedComment := strings.ReplaceAll(label.Comment, `"`, `\"`)
					metadataJSON += fmt.Sprintf(`, "comment": "%s"`, escapedComment)
				}
				metadataJSON += "}"
				_, err = tx.ExecContext(ctx, `
					INSERT INTO label_metadata (label_id, json, created_at, last_modified, active)
					VALUES (?, ?, now(), now(), true)
				`, labelID, metadataJSON)
				if err != nil {
					errors = append(errors, ImportSegmentError{
						File:    filepath.Base(sf.DataPath),
						Stage:   "import",
						Message: fmt.Sprintf("failed to insert label_metadata: %v", err),
					})
					continue
				}
				// Build label import for output
				labelImport := LabelImport{
					LabelID:   labelID,
					Species:   dbSpecies,
					Filter:    label.Filter,
					Certainty: label.Certainty,
				}
				if label.Comment != "" {
					labelImport.Comment = label.Comment
				}
				// Insert label_subtype if calltype exists
				if label.CallType != "" {
					dbCalltype := mapping.GetDBCalltype(label.Species, label.CallType)
					calltypeID := ""
					if calltypeIDMap[dbSpecies] != nil {
						calltypeID = calltypeIDMap[dbSpecies][dbCalltype]
					}
					if calltypeID == "" {
						errors = append(errors, ImportSegmentError{
							File:    filepath.Base(sf.DataPath),
							Stage:   "import",
							Message: fmt.Sprintf("calltype ID not found: %s/%s", dbSpecies, dbCalltype),
						})
						continue
					}
					subtypeID, err := utils.GenerateLongID()
					if err != nil {
						errors = append(errors, ImportSegmentError{
							File:    filepath.Base(sf.DataPath),
							Stage:   "import",
							Message: fmt.Sprintf("failed to generate label_subtype ID: %v", err),
						})
						continue
					}
					_, err = tx.ExecContext(ctx, `
						INSERT INTO label_subtype (id, label_id, calltype_id, filter_id, certainty, created_at, last_modified, active)
						VALUES (?, ?, ?, ?, ?, now(), now(), true)
					`, subtypeID, labelID, calltypeID, filterID, label.Certainty)
					if err != nil {
						errors = append(errors, ImportSegmentError{
							File:    filepath.Base(sf.DataPath),
							Stage:   "import",
							Message: fmt.Sprintf("failed to insert label_subtype: %v", err),
						})
						continue
					}
					importedSubtypes++
					labelImport.CallType = dbCalltype
				}
				segmentImport.Labels = append(segmentImport.Labels, labelImport)
			}
			if len(segmentImport.Labels) > 0 {
				importedSegments = append(importedSegments, segmentImport)
			}
		}
	}
	// Commit transaction
	if err := tx.Commit(); err != nil {
		errors = append(errors, ImportSegmentError{
			Stage:   "import",
			Message: fmt.Sprintf("failed to commit transaction: %v", err),
		})
		return nil, 0, 0, skippedBookmarks, errors
	}
	return importedSegments, importedLabels, importedSubtypes, skippedBookmarks, errors
}
// countTotalSegments counts total non-bookmarked segments
func countTotalSegments(scannedFiles []scannedDataFile) int {
	count := 0
	for _, sf := range scannedFiles {
		for _, seg := range sf.Segments {
			hasBookmark := false
			for _, label := range seg.Labels {
				if label.Bookmark {
					hasBookmark = true
					break
				}
			}
			if !hasBookmark {
				count++
			}
		}
	}
	return count
}

# prepare .data files for import skill
get summary
look at species and call types
check they match db
generate a dated json call mapping

PLAN MODE::

skraak import segments --db <> --folder <>  --mapping <> --dataset <> --location <> --cluster <>
imports .data segments from 1 file into the skraak.duckdb database
reuse code from import selections tool (extract to util first) 
Note import selections tool is deprecated and will be deleted in future, but it does something similar to this cli tool
see $ skraak calls summarise cmd in code, reuse where possible, extract to util first
see .claude/skills/skraak-data-mapping
see db/avianz_data_schema.json and add file and label metadata ie skraak_hash, skraak_label_id
see db/avianz_file_format_specification.md
see db/schema.sql for relationships. segment/label/label_subtype/label_metadata
see db/mapping_schema.json json schema

operates on 1 folder
get list of files with segments
check segments are valid
apply call mapping
hash files using existing util 
label id

example .data file:
[
  {
    "Duration": 900,
    "Reviewer": "None",
    "Operator": "Auto"
    ///// write skraak_hash
  },
  [
    165,
    185,
    0,
    32000,
    [
      {
        "calltype": "Male",
        "filter": "opensoundscape-kiwi-1.2",
        "certainty": 70,
        "species": "GSK"
        //// write skraak_label_id
      }
    ]
  ],
  [
    165,
    182.5,
    0,
    32000,
    [
      {
        "calltype": "Male",
        "filter": "opensoundscape-kiwi-1.5",
        "certainty": 70,
        "species": "GSK"
      }
    ]
  ],
  [
    782.5,
    787.5,
    0,
    32000,
    [
      {
        "filter": "opensoundscape-kiwi-1.2",
        "certainty": 70,
        "species": "Don't Know"
      }
    ]
  ],
  [
    895,
    900,
    0,
    32000,
    [
      {
        "filter": "opensoundscape-kiwi-1.5",
        "certainty": 70,
        "species": "Don't Know"
      }
    ]
  ]
]

mapping_2026-03-14.json
{
  "Don't Know": {
    "species": "Don't Know"
  },
  "GSK": {
    "species": "Roroa",
    "calltypes": {
      "Male": "Male - Solo",
      "Female": "Female - Solo"
    }
  }
}
skraak import segments --db <> --folder <>  --mapping <> --dataset <> --location <> --cluster <>
database --db <> (required)
operates on 1 folder --folder <> (required)
get list of files with segments
get call mapping file --mapping <> (required)
check .data segments are valid
check call mapping is complete and up to date for all calls and db
apply call mapping
hash wav files with .data metadata using existing util, hash must already exist in db
label id using existing util

ignore bookmarks in .data files (bookmark: true => ignore)
comment => label_metadata json {"comment":"............"}

- all certainty scores (for label label_subtype) must not be null

- all certainty scores (for label label_subtype) must be transferred over
- all filters must already be in the skraak db

- All included files must have no existing labels in db
- All labels and label_subtypes must already exist in species and call_type

- All included files must have no existing labels in db (fresh imports only, no mutations allowed)
- All labels and label_subtypes must map to already existing species and call_type in the db

Questions?
::PLAN MODE

we need to write a new skill. in order to import .data files from a folder
  into the skraak db, we need to make sure the species and calltype map to
  label and label_subtype in the db (ie a valid species and for that species
  calltype). this involves running $skraak calls summarise --folder
  ./recordings | jq 'del(.segments)' then mapping existing specier and call
  types to db species and call types, outputting json, with user input as
  required. As below. ie existing => db mapping
{
    "Morepork": {
      "name": "morepo1"
    },
    "Kiwi": {
      "name": "sobkiwi1",
      "calltypes": {
        "M": "Male",
        "F": "Female",
        "D": "Duet"
      }
    }
  }

PLAN MODE::
WAV files are in the same folder as .data files
Yes freq_low and freq_high from .data segments should be stored in the segment table, if absent clamp to sample rate, commonly 0, 8000 or 0, 16000 or 0, 32000
A .data segment can have multiple labels, yes each Should get its own label record
 pointing to the same segment_id (Yes, per schema design)
 Progress reporting: Should we use the same ProgressHandler pattern as
 other import tools? Yes
Error handling: Yes Fail-fast on any error, should insert into db in a transaction
Note: be very careful of DB relations. read @db/schema.sql again. ebird_taxonomy, species, call_type, segment, label, label_subtype, label_metadata, filter, all theese tables are related
::PLAN MODE

	case "segments":
		runImportSegments(args[1:])

	fmt.Fprintf(os.Stderr, "  segments     Import segments from AviaNZ .data files (structured datasets)\n")

	fmt.Fprintf(os.Stderr, "  skraak import segments --db ./db/skraak.duckdb --dataset abc123 --location loc456 --cluster clust789 --folder /path/to/folder --mapping mapping.json\n")

func runImportSegments(args []string) {
	fs := flag.NewFlagSet("import segments", flag.ExitOnError)
	dbPath := fs.String("db", "", "Path to DuckDB database (required)")
	datasetID := fs.String("dataset", "", "Dataset ID (required)")
	locationID := fs.String("location", "", "Location ID (required)")
	clusterID := fs.String("cluster", "", "Cluster ID (required)")
	folderPath := fs.String("folder", "", "Path to folder containing .data files (required)")
	mappingPath := fs.String("mapping", "", "Path to mapping JSON file (required)")
	fs.Usage = func() {
		fmt.Fprintf(os.Stderr, "Usage: skraak import segments [options]\n\n")
		fmt.Fprintf(os.Stderr, "Import segments from AviaNZ .data files into the database.\n")
		fmt.Fprintf(os.Stderr, "Applies species/calltype mapping from JSON file.\n\n")
		fmt.Fprintf(os.Stderr, "Options:\n")
		fs.PrintDefaults()
		fmt.Fprintf(os.Stderr, "\nMapping file format:\n")
		fmt.Fprintf(os.Stderr, "  {\n")
		fmt.Fprintf(os.Stderr, "    \"GSK\": {\"species\": \"Roroa\", \"calltypes\": {\"Male\": \"Male - Solo\"}},\n")
		fmt.Fprintf(os.Stderr, "    \"Don't Know\": {\"species\": \"Don't Know\"}\n")
		fmt.Fprintf(os.Stderr, "  }\n")
		fmt.Fprintf(os.Stderr, "\nInvariants:\n")
		fmt.Fprintf(os.Stderr, "  - All file hashes must already exist in database for the cluster\n")
		fmt.Fprintf(os.Stderr, "  - All files must have no existing labels (fresh imports only)\n")
		fmt.Fprintf(os.Stderr, "  - All filters, species, and calltypes must exist in database\n")
		fmt.Fprintf(os.Stderr, "  - Segments with bookmark=true labels are skipped\n")
		fmt.Fprintf(os.Stderr, "\nExamples:\n")
		fmt.Fprintf(os.Stderr, "  skraak import segments --db ./db/skraak.duckdb --dataset abc123 --location loc456 --cluster clust789 --folder /path/to/data --mapping mapping.json\n")
	}
	if err := fs.Parse(args); err != nil {
		os.Exit(1)
	}
	// Validate required flags
	missing := []string{}
	if *dbPath == "" {
		missing = append(missing, "--db")
	}
	if *datasetID == "" {
		missing = append(missing, "--dataset")
	}
	if *locationID == "" {
		missing = append(missing, "--location")
	}
	if *clusterID == "" {
		missing = append(missing, "--cluster")
	}
	if *folderPath == "" {
		missing = append(missing, "--folder")
	}
	if *mappingPath == "" {
		missing = append(missing, "--mapping")
	}
	if len(missing) > 0 {
		fmt.Fprintf(os.Stderr, "Error: missing required flags: %v\n\n", missing)
		fs.Usage()
		os.Exit(1)
	}
	tools.SetDBPath(*dbPath)
	// Initialize event log
	eventLogPath := *dbPath + ".events.jsonl"
	db.SetEventLogConfig(db.EventLogConfig{
		Enabled: true,
		Path:    eventLogPath,
	})
	defer db.CloseEventLog()
	input := tools.ImportSegmentsInput{
		Folder:     *folderPath,
		Mapping:    *mappingPath,
		DatasetID:  *datasetID,
		LocationID: *locationID,
		ClusterID:  *clusterID,
		ProgressHandler: func(processed, total int, message string) {
			if total > 0 {
				percent := float64(processed) / float64(total) * 100
				fmt.Fprintf(os.Stderr, "\rProcessing .data files: %d/%d (%.0f%%) - %s", processed, total, percent, message)
				if processed == total {
					fmt.Fprintf(os.Stderr, "\n")
				}
			}
		},
	}
	fmt.Fprintf(os.Stderr, "Importing segments from: %s\n", *folderPath)
	fmt.Fprintf(os.Stderr, "Using mapping: %s\n", *mappingPath)
	output, err := tools.ImportSegments(context.Background(), input)
	if err != nil {
		fmt.Fprintf(os.Stderr, "\nError: %v\n", err)
		// Still print partial results if available
		if len(output.Segments) > 0 || len(output.Errors) > 0 {
			printJSON(output)
		}
		os.Exit(1)
	}
	fmt.Fprintf(os.Stderr, "\nImport complete:\n")
	fmt.Fprintf(os.Stderr, "  Data files processed: %d\n", output.Summary.DataFilesProcessed)
	fmt.Fprintf(os.Stderr, "  Segments imported: %d\n", output.Summary.ImportedSegments)
	fmt.Fprintf(os.Stderr, "  Labels imported: %d\n", output.Summary.ImportedLabels)
	fmt.Fprintf(os.Stderr, "  Subtypes imported: %d\n", output.Summary.ImportedSubtypes)
	fmt.Fprintf(os.Stderr, "  Bookmarks skipped: %d\n", output.Summary.SkippedBookmarks)
	printJSON(output)
}

# Plan: `skraak import segments` Command
## Overview
Import segments from AviaNZ `.data` files into the skraak database, applying a species/calltype mapping file.
## CLI Usage
```bash
skraak import segments \
  --db ./db/skraak.duckdb \
  --folder /path/to/folder \
  --mapping /path/to/mapping_2026-03-14.json \
  --dataset abc123 \
  --location loc456 \
  --cluster clust789
```
## Database Relationships (Critical for Insert Order)
```
dataset
  └── location (FK: dataset_id)
        └── cluster (FK: location_id, dataset_id)
              └── file (FK: cluster_id, location_id)
                    └── file_dataset (FK: file_id, dataset_id)
                    └── file_metadata (FK: file_id)
                    └── segment (FK: file_id, dataset_id, file_dataset)
                          └── label (FK: segment_id, species_id, filter_id)
                                └── label_metadata (FK: label_id)
                                └── label_subtype (FK: label_id, calltype_id, filter_id)
species
  └── call_type (FK: species_id)
```
## Insert Order (Per Segment)
1. **segment** → needs file_id (from DB by hash), dataset_id
2. **label** → needs segment_id (just inserted), species_id (from mapping), filter_id (from DB), certainty
3. **label_metadata** → needs label_id, stores `{"skraak_label_id": "...", "comment": "..."}`
4. **label_subtype** → needs label_id, calltype_id (from mapping), filter_id, certainty
## Files to Create/Modify
### 1. NEW: `utils/mapping.go`
Mapping file utilities:
- `SpeciesMapping` struct
- `LoadMappingFile(path string) (map[string]SpeciesMapping, error)`
- `ValidateMappingAgainstDB(db *sql.DB, mapping map[string]SpeciesMapping) error`
### 2. NEW: `tools/import_segments.go`
Core tool logic with types:
- `ImportSegmentsInput`
- `ImportSegmentsOutput`
- `ImportSummary`
- `SegmentImport`
- `LabelImport`
- `ImportError`
- `ImportSegments()` function
### 3. MODIFY: `cmd/import.go`
Add `segments` subcommand to switch statement and `runImportSegments()` handler.
## Detailed Algorithm
### Phase A: Input Validation
1. Validate folder exists and contains .data files
2. Validate dataset_id, location_id, cluster_id format (12-char nanoid)
3. Validate cluster belongs to location, location belongs to dataset
4. Validate dataset type is 'structured'
5. Load mapping file and validate JSON schema
### Phase B: Pre-Import Validation (Fail-Fast)
1. Parse all .data files, collect:
   - Unique filter names
   - Unique species names (from labels)
   - Unique calltype names per species
2. Validate all filters exist in DB:
   ```sql
   SELECT id FROM filter WHERE name IN (?) AND active = true
   ```
3. Validate mapping covers all species found in .data files:
   - For each species in .data: must exist as key in mapping
   - mapping[species].species must exist in species table
4. Validate mapping covers all calltypes:
   - For each (species, calltype) in .data:
     - If mapping[species].calltypes exists: calltype must map to existing call_type.label
     - Else: calltype must exist as-is in call_type table for that species
5. For each .data file:
   - Find corresponding WAV file (same folder, strip .data extension)
   - Hash the WAV file
   - Verify hash exists in file table for this cluster
   - Verify no existing labels for this file (fresh imports only)
6. Validate segment bounds:
   - start_time < end_time
   - end_time <= file.duration
### Phase C: Transactional Import
```sql
BEGIN TRANSACTION;
-- Pre-load all ID lookups into memory
-- filterIDMap: filter_name -> filter_id
-- speciesIDMap: species_label -> species_id
-- calltypeIDMap: (species_label, calltype_label) -> calltype_id
-- fileIDMap: xxh64_hash -> file_id
FOR each .data file:
    wavPath = dataPath without .data extension
    hash = ComputeXXH64(wavPath)
    fileID = fileIDMap[hash]
    -- Update file_metadata with skraak_hash
    INSERT OR REPLACE INTO file_metadata (file_id, json, active)
    VALUES (fileID, JSON_SET(COALESCE(json, '{}'), '$.skraak_hash', ?), true)
    FOR each segment in .data:
        IF segment has any label with bookmark=true: CONTINUE
        segmentID = GenerateLongID()
        -- Clamp freq_low/freq_high (default 0, sample_rate/2)
        INSERT INTO segment (id, file_id, dataset_id, start_time, end_time,
                            freq_low, freq_high, created_at, last_modified, active)
        VALUES (segmentID, fileID, datasetID, start, end, freqLow, freqHigh, now(), now(), true)
        FOR each label in segment:
            labelID = GenerateLongID()
            dbSpecies = mapping[label.Species].species
            speciesID = speciesIDMap[dbSpecies]
            filterID = filterIDMap[label.Filter]
            certainty = label.Certainty
            INSERT INTO label (id, segment_id, species_id, filter_id, certainty,
                              created_at, last_modified, active)
            VALUES (labelID, segmentID, speciesID, filterID, certainty, now(), now(), true)
            -- Build label_metadata JSON
            metadata := {"skraak_label_id": labelID}
            IF label.Comment != "": metadata["comment"] = label.Comment
            INSERT INTO label_metadata (label_id, json, created_at, last_modified, active)
            VALUES (labelID, metadata, now(), now(), true)
            IF label.CallType != "":
                subtypeID = GenerateLongID()
                dbCalltype = mapping[label.Species].calltypes[label.CallType]
                    OR label.CallType if no mapping entry
                calltypeID = calltypeIDMap[(dbSpecies, dbCalltype)]
                INSERT INTO label_subtype (id, label_id, calltype_id, filter_id, certainty,
                                          created_at, last_modified, active)
                VALUES (subtypeID, labelID, calltypeID, filterID, certainty, now(), now(), true)
COMMIT;
```
## Mapping File Format
```json
{
  "Don't Know": {
    "species": "Don't Know"
  },
  "GSK": {
    "species": "Roroa",
    "calltypes": {
      "Male": "Male - Solo",
      "Female": "Female - Solo"
    }
  }
}
```
## Output JSON
```json
{
  "summary": {
    "data_files_found": 42,
    "data_files_processed": 42,
    "total_segments": 342,
    "imported_segments": 342,
    "imported_labels": 356,
    "imported_subtypes": 280,
    "processing_time_ms": 1234
  },
  "segments": [
    {
      "segment_id": "abc123...",
      "file_name": "recording.wav",
      "start_time": 165.0,
      "end_time": 185.0,
      "labels": [
        {
          "label_id": "def456...",
          "species": "Roroa",
          "calltype": "Male - Solo",
          "filter": "opensoundscape-kiwi-1.2",
          "certainty": 70
        }
      ]
    }
  ],
  "errors": []
}
```
## Invariants
| Invariant | How Enforced |
|-----------|--------------|
| Operates on 1 folder | CLI flag validation |
| All certainty scores transferred | Direct copy from .data to DB |
| All filters exist in DB | Phase B validation |
| Mapping is complete | Phase B validation |
| File hashes exist in DB | Phase B - lookup by xxh64_hash |
| No existing labels | Phase B - query before import |
| Species/calltypes exist | Phase B validation |
| Fail-fast on error | All validation before transaction |
| Single transaction | `db.BeginLoggedTx()` with rollback |
## Key Code Patterns to Reuse
- `utils.ParseDataFile()` - Parse .data files
- `utils.FindDataFiles()` - Find .data files in folder
- `utils.ComputeXXH64()` - Hash WAV files
- `utils.GenerateLongID()` - 21-char nanoid for segment/label IDs
- `utils.ValidateShortID()` - Validate 12-char IDs
- `db.BeginLoggedTx()` - Transaction with mutation logging
- `utils.CheckDuplicateHash()` - Check if hash exists (adapt for this use)
## Decisions Made
1. ✅ WAV files same folder as .data files
2. ✅ freq_low/freq_high stored in segment table (clamp to sample_rate if needed)
3. ✅ Multiple labels per segment → multiple label records
4. ✅ ProgressHandler pattern for progress reporting
5. ✅ Fail-fast + single transaction