"time"

// createTestWAVWithMetadata creates a WAV file and inserts it into the database.
// Returns the file ID and hash.
func createTestWAVWithMetadata(t *testing.T, database *sql.DB, clusterID, locationID, filename string) (fileID, hash string) {
	t.Helper()

	// Create temp file
	tmpDir := t.TempDir()
	wavPath := filepath.Join(tmpDir, filename)
	hash = createTestWAV(t, wavPath)
	// Generate file ID
	fileID, err := utils.GenerateLongID()
	if err != nil {
		t.Fatalf("failed to generate file ID: %v", err)
	}
	// Insert file record
	_, err = database.ExecContext(context.Background(), `
		INSERT INTO file (id, file_name, xxh64_hash, location_id, cluster_id, timestamp_local, duration, sample_rate, active)
		VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, 0.0005, 16000, true)
	`, fileID, filename, hash, locationID, clusterID)
	if err != nil {
		t.Fatalf("failed to insert file: %v", err)
	}
	return fileID, hash
}

	defer file.Close()

	defer func() { _ = file.Close() }()

}
// assertFileCount queries the database and asserts the expected number of files.
func assertFileCount(t *testing.T, database *sql.DB, expected int) {
	t.Helper()
	var count int
	if err := database.QueryRow("SELECT COUNT(*) FROM file WHERE active = true").Scan(&count); err != nil {
		t.Fatalf("failed to count files: %v", err)
	}
	if count != expected {
		t.Errorf("expected %d files, got %d", expected, count)
	}

// assertSegmentCount queries the database and asserts the expected number of segments.
func assertSegmentCount(t *testing.T, database *sql.DB, expected int) {
	t.Helper()
	var count int
	if err := database.QueryRow("SELECT COUNT(*) FROM segment WHERE active = true").Scan(&count); err != nil {
		t.Fatalf("failed to count segments: %v", err)
	}
	if count != expected {
		t.Errorf("expected %d segments, got %d", expected, count)
	}
}
// assertLabelCount queries the database and asserts the expected number of labels.
func assertLabelCount(t *testing.T, database *sql.DB, expected int) {
	t.Helper()
	var count int
	if err := database.QueryRow("SELECT COUNT(*) FROM label WHERE active = true").Scan(&count); err != nil {
		t.Fatalf("failed to count labels: %v", err)
	}
	if count != expected {
		t.Errorf("expected %d labels, got %d", expected, count)
	}
}
// getTestLocationData returns location data for testing.
func getTestLocationData(t *testing.T, database *sql.DB, locationID string) *LocationData {
	t.Helper()
	data, err := GetLocationData(database, locationID)
	if err != nil {
		t.Fatalf("failed to get location data: %v", err)
	}
	return data
}

// waitForAsync waits for a short duration to allow async operations to complete.
func waitForAsync() {
	time.Sleep(100 * time.Millisecond)
}

	if recursive {
		return scanWavFilesRecursive(folderPath)
	}
	return scanWavFilesFlat(folderPath)
}
// scanWavFilesRecursive walks the directory tree recursively.
func scanWavFilesRecursive(folderPath string) ([]string, []FileImportError) {

	walkFunc := func(path string, d fs.DirEntry, err error) error {

	err := filepath.WalkDir(folderPath, func(path string, d fs.DirEntry, err error) error {

		// Skip directories if not recursive

			if !recursive && path != folderPath {
				return fs.SkipDir
			}

		// Check for .wav extension (case-insensitive)
		if strings.HasSuffix(strings.ToLower(d.Name()), ".wav") {

		if isWavFile(d.Name()) {

	})
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: folderPath,
			Error:    err.Error(),
			Stage:    StageScan,
		})

	return files, errors
}

	if recursive {
		if err := filepath.WalkDir(folderPath, walkFunc); err != nil {
			errors = append(errors, FileImportError{
				FileName: folderPath,
				Error:    err.Error(),
				Stage:    StageScan,
			})
		}
	} else {
		// Non-recursive: only scan top-level
		entries, err := os.ReadDir(folderPath)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: folderPath,
				Error:    err.Error(),
				Stage:    StageScan,
			})
			return nil, errors
		}

// scanWavFilesFlat scans only the top-level directory.
func scanWavFilesFlat(folderPath string) ([]string, []FileImportError) {
	var files []string
	var errors []FileImportError
	entries, err := os.ReadDir(folderPath)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: folderPath,
			Error:    err.Error(),
			Stage:    StageScan,
		})
		return nil, errors
	}

		for _, entry := range entries {
			if !entry.IsDir() && strings.HasSuffix(strings.ToLower(entry.Name()), ".wav") {
				files = append(files, filepath.Join(folderPath, entry.Name()))
			}

	for _, entry := range entries {
		if !entry.IsDir() && isWavFile(entry.Name()) {
			files = append(files, filepath.Join(folderPath, entry.Name()))

	return files, errors
}

	return files, errors

// isWavFile returns true if the filename has a .wav extension (case-insensitive).
func isWavFile(name string) bool {
	return strings.HasSuffix(strings.ToLower(name), ".wav")

}
// resolvedLabelIDs holds the resolved database IDs for a label.
type resolvedLabelIDs struct {
	speciesID string
	filterID  string
	labelID   string
	dbSpecies string

// importSingleLabel inserts a single label and its metadata/subtype into the DB.
func importSingleLabel(
	ctx context.Context,
	tx *db.LoggedTx,

// resolveLabelIDs looks up species and filter IDs, generates a label ID.
// Returns an error if any lookup fails.
func resolveLabelIDs(

	segmentID string,
	segIdx, labelIdx int,

	calltypeIDMap map[string]map[string]string,
) importLabelResult {

) (resolvedLabelIDs, error) {

		return importLabelResult{err: ImportSegmentError{
			File: filepath.Base(sf.DataPath), Stage: StageImport,
			Message: fmt.Sprintf("species not found in mapping: %s", label.Species),
		}, hasError: true}

		return resolvedLabelIDs{}, fmt.Errorf("species not found in mapping: %s", label.Species)

		return importLabelResult{err: ImportSegmentError{
			File: filepath.Base(sf.DataPath), Stage: StageImport,
			Message: fmt.Sprintf("species ID not found: %s", dbSpecies),
		}, hasError: true}

		return resolvedLabelIDs{}, fmt.Errorf("species ID not found: %s", dbSpecies)

		return importLabelResult{err: ImportSegmentError{
			File: filepath.Base(sf.DataPath), Stage: StageImport,
			Message: fmt.Sprintf("filter ID not found: %s", label.Filter),
		}, hasError: true}

		return resolvedLabelIDs{}, fmt.Errorf("filter ID not found: %s", label.Filter)

	if err != nil {
		return resolvedLabelIDs{}, fmt.Errorf("failed to generate label ID: %w", err)
	}
	return resolvedLabelIDs{
		speciesID: speciesID,
		filterID:  filterID,
		labelID:   labelID,
		dbSpecies: dbSpecies,
	}, nil
}
// importSingleLabel inserts a single label and its metadata/subtype into the DB.
func importSingleLabel(
	ctx context.Context,
	tx *db.LoggedTx,
	label *datafile.Label,
	segmentID string,
	segIdx, labelIdx int,
	sf scannedDataFile,
	mapping MappingFile,
	filterIDMap map[string]string,
	speciesIDMap map[string]string,
	calltypeIDMap map[string]map[string]string,
) importLabelResult {
	// Resolve all IDs first
	ids, err := resolveLabelIDs(label, sf, mapping, filterIDMap, speciesIDMap)

			Message: fmt.Sprintf("failed to generate label ID: %v", err),

			Message: err.Error(),

	_, 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 {

	// Insert the label
	if err := insertLabel(ctx, tx, ids, segmentID, label); err != nil {

			Message: fmt.Sprintf("failed to insert label: %v", err),

			Message: err.Error(),

		escapedComment := strings.ReplaceAll(label.Comment, `"`, `\"`)
		metadataJSON := fmt.Sprintf(`{"comment": "%s"}`, escapedComment)
		if _, err := tx.ExecContext(ctx, `
			INSERT INTO label_metadata (label_id, json, created_at, last_modified, active)
			VALUES (?, ?, now(), now(), true)
		`, labelID, metadataJSON); err != nil {

		if err := insertLabelMetadata(ctx, tx, ids.labelID, label.Comment); err != nil {

				Message: fmt.Sprintf("failed to insert label_metadata: %v", err),

				Message: err.Error(),

		LabelID:   labelID,
		Species:   dbSpecies,

		LabelID:   ids.labelID,
		Species:   ids.dbSpecies,

		if err := importCalltype(ctx, tx, labelID, label, dbSpecies, filterID, mapping, calltypeIDMap, sf); err != nil {
			return importLabelResult{err: *err, hasError: true}

		if ctErr := importCalltype(ctx, tx, ids.labelID, label, ids.dbSpecies, ids.filterID, mapping, calltypeIDMap, sf); ctErr != nil {
			return importLabelResult{err: *ctErr, hasError: true}

		return importLabelResult{labelImport: labelImport, labelID: labelID, subtypesImported: 1}

		return importLabelResult{labelImport: labelImport, labelID: ids.labelID, subtypesImported: 1}

	return importLabelResult{labelImport: labelImport, labelID: ids.labelID}
}

	return importLabelResult{labelImport: labelImport, labelID: labelID}

// insertLabel inserts a label row into the database.
func insertLabel(ctx context.Context, tx *db.LoggedTx, ids resolvedLabelIDs, segmentID string, label *datafile.Label) error {
	_, err := tx.ExecContext(ctx, `
		INSERT INTO label (id, segment_id, species_id, filter_id, certainty, created_at, last_modified, active)
		VALUES (?, ?, ?, ?, ?, now(), now(), true)
	`, ids.labelID, segmentID, ids.speciesID, ids.filterID, label.Certainty)
	if err != nil {
		return fmt.Errorf("failed to insert label: %w", err)
	}
	return nil

// insertLabelMetadata inserts a label_metadata row for a comment.
func insertLabelMetadata(ctx context.Context, tx *db.LoggedTx, labelID, comment string) error {
	escapedComment := strings.ReplaceAll(comment, `"`, `\\"`)
	metadataJSON := fmt.Sprintf(`{"comment": "%s"}`, escapedComment)
	_, 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 {
		return fmt.Errorf("failed to insert label_metadata: %w", err)
	}
	return nil
}

// boolPtr returns a pointer to the bool value.
//
//go:fix inline
func boolPtr(v bool) *bool {
	return new(v)
}

	// Validate file exists
	if _, err := os.Stat(input.DataFilePath); os.IsNotExist(err) {
		output.Error = fmt.Sprintf("File not found: %s", input.DataFilePath)

	// Validate input and get data file
	dataFile, wavPath, err := validateAndParseShowImagesInput(input)
	if err != nil {
		output.Error = err.Error()
		return output, err
	}
	output.WavFile = wavPath
	if len(dataFile.Segments) == 0 {
		output.Error = "No segments found in .data file"

	// Derive WAV file path (strip .data suffix)
	wavPath := strings.TrimSuffix(input.DataFilePath, ".data")
	output.WavFile = wavPath

	// Resolve image size and protocol
	imgSize := resolveImageSize(input.ImageSize)
	protocol := resolveGraphicsProtocol(input)

	// Check WAV file exists

	// Generate and display spectrograms
	segmentsShown, err := displaySegmentSpectrograms(input.DataFilePath, dataFile.Segments, input.Color, imgSize, protocol)
	if err != nil {
		output.Error = err.Error()
		return output, err
	}
	output.SegmentsShown = segmentsShown
	return output, nil
}
// validateAndParseShowImagesInput validates input files and parses the .data file.
func validateAndParseShowImagesInput(input CallsShowImagesInput) (*datafile.DataFile, string, error) {
	if _, err := os.Stat(input.DataFilePath); os.IsNotExist(err) {
		return nil, "", fmt.Errorf("file not found: %s", input.DataFilePath)
	}
	wavPath := strings.TrimSuffix(input.DataFilePath, ".data")

		output.Error = fmt.Sprintf("WAV file not found: %s", wavPath)
		return output, fmt.Errorf("%s", output.Error)

		return nil, "", fmt.Errorf("WAV file not found: %s", wavPath)

	// Parse .data file (includes labels for future filtering)

		output.Error = err.Error()
		return output, fmt.Errorf("%s", output.Error)

		return nil, "", err

	if len(dataFile.Segments) == 0 {
		output.Error = "No segments found in .data file"
		return output, fmt.Errorf("%s", output.Error)
	}

	return dataFile, wavPath, nil
}

	// Resolve image size
	imgSize := input.ImageSize
	if imgSize == 0 {
		imgSize = spectrogram.SpectrogramDisplaySize

// resolveImageSize returns the image size, using default if zero.
func resolveImageSize(size int) int {
	if size == 0 {
		return spectrogram.SpectrogramDisplaySize

	return size
}

	// Select graphics protocol
	protocol := spectrogram.ProtocolKitty

// resolveGraphicsProtocol selects the terminal graphics protocol.
func resolveGraphicsProtocol(input CallsShowImagesInput) spectrogram.ImageProtocol {

		protocol = spectrogram.ProtocolITerm
	} else if input.Sixel {
		protocol = spectrogram.ProtocolSixel

		return spectrogram.ProtocolITerm

	if input.Sixel {
		return spectrogram.ProtocolSixel
	}
	return spectrogram.ProtocolKitty
}

	// Generate spectrogram for each segment and output
	for i, seg := range dataFile.Segments {
		// Generate spectrogram image
		img, err := spectrogram.GenerateSegmentSpectrogram(input.DataFilePath, seg.StartTime, seg.EndTime, input.Color, imgSize)

// displaySegmentSpectrograms generates and displays spectrograms for each segment.
func displaySegmentSpectrograms(dataFilePath string, segments []*datafile.Segment, color bool, imgSize int, protocol spectrogram.ImageProtocol) (int, error) {
	shown := 0
	for i, seg := range segments {
		img, err := spectrogram.GenerateSegmentSpectrogram(dataFilePath, seg.StartTime, seg.EndTime, color, imgSize)

		// Print segment info

		// Write to stdout via terminal graphics protocol

			output.Error = fmt.Sprintf("Failed to write image: %v", err)
			return output, fmt.Errorf("%s", output.Error)

			return shown, fmt.Errorf("failed to write image: %w", err)

		fmt.Println() // Newline after image

		fmt.Println()
		shown++

	output.SegmentsShown = len(dataFile.Segments)
	return output, nil

	return shown, nil

	matches := filterLabelsBySpecies(segment.Labels, species, callType, filter)
	if len(matches) == 0 {
		return nil, ""
	}
	if callType == "" && len(matches) > 1 {
		return nil, formatAmbiguousCalltypeError(species, filter, matches)
	}
	return matches, ""
}
// filterLabelsBySpecies returns labels matching the given species, calltype, and filter.
func filterLabelsBySpecies(labels []*datafile.Label, species, callType, filter string) []*datafile.Label {

	for _, label := range segment.Labels {

	for _, label := range labels {

	}
	if len(matches) == 0 {
		return nil, ""

	return matches
}

	if callType == "" && len(matches) > 1 {
		var callTypes []string
		for _, l := range matches {
			ct := l.CallType
			if ct == "" {
				ct = "(none)"
			}
			callTypes = append(callTypes, ct)

// formatAmbiguousCalltypeError creates an error message for ambiguous calltype matches.
func formatAmbiguousCalltypeError(species, filter string, matches []*datafile.Label) string {
	var callTypes []string
	for _, l := range matches {
		ct := l.CallType
		if ct == "" {
			ct = "(none)"

		return nil, fmt.Sprintf("multiple labels match species '%s' with filter '%s', specify --calltype to disambiguate (calltypes: %v)", species, filter, callTypes)

		callTypes = append(callTypes, ct)

	return matches, ""

	return fmt.Sprintf("multiple labels match species '%s' with filter '%s', specify --calltype to disambiguate (calltypes: %v)", species, filter, callTypes)

	// If segment has no labels left, remove it
	if len(targetSegment.Labels) == 0 {
		removeSegmentFromDataFile(dataFile, targetSegment)
		output.Removed = "segment"

	// Handle the result of label removal
	output.Removed = handleRemovalResult(dataFile, targetSegment, input.File)

		// If no segments left, remove the .data file
		if len(dataFile.Segments) == 0 {
			if err := os.Remove(input.File); err != nil {
				return removeOutputError(&output, fmt.Sprintf("failed to remove file: %v", err))
			}
			output.Removed = "file"
			return output, nil
		}
	} else {
		output.Removed = "label"

	// Handle file removal case
	if output.Removed == "file" {
		return output, nil

}
// handleRemovalResult handles segment/file removal after labels are removed.
// Returns "label", "segment", or "file" depending on what was removed.
func handleRemovalResult(dataFile *datafile.DataFile, targetSegment *datafile.Segment, filePath string) string {
	if len(targetSegment.Labels) > 0 {
		return "label"
	}
	removeSegmentFromDataFile(dataFile, targetSegment)
	if len(dataFile.Segments) > 0 {
		return "segment"
	}
	// No segments left, remove the .data file
	os.Remove(filePath)
	return "file"

	}
	idx := detectBirdaColumns(header)
	if err := validateBirdaColumns(idx); err != nil {
		return birdaColumnIndices{}, err

	return idx, nil
}

// detectBirdaColumns scans the header row and returns column indices.
func detectBirdaColumns(header []string) birdaColumnIndices {

	columnMap := map[string]*int{
		"Start (s)":   &idx.startIdx,
		"End (s)":     &idx.endIdx,
		"Common name": &idx.commonNameIdx,
		"Confidence":  &idx.confidenceIdx,
		"File":        &idx.fileIdx,
	}

		switch col {
		case "Start (s)":
			idx.startIdx = i
		case "End (s)":
			idx.endIdx = i
		case "Common name":
			idx.commonNameIdx = i
		case "Confidence":
			idx.confidenceIdx = i
		case "File":
			idx.fileIdx = i

		if ptr, ok := columnMap[col]; ok {
			*ptr = i

	return idx
}

// validateBirdaColumns checks that required columns were found.
func validateBirdaColumns(idx birdaColumnIndices) error {

		return birdaColumnIndices{}, fmt.Errorf("missing required columns in BirdNET file")

		return fmt.Errorf("missing required columns in BirdNET file")

	return idx, nil

	return nil