database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return ExecuteSQLOutput{}, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()

	var output ExecuteSQLOutput
	err := db.WithReadDB(dbPath, func(database *sql.DB) error {
		rows, rerr := executeSQLQuery(ctx, database, query, input.Parameters)
		if rerr != nil {
			return rerr
		}
		defer rows.Close()

	rows, err := executeSQLQuery(ctx, database, query, input.Parameters)
	if err != nil {
		return ExecuteSQLOutput{}, err
	}
	defer rows.Close()

		columnInfo, columns, cerr := buildColumnInfo(rows)
		if cerr != nil {
			return cerr
		}

	columnInfo, columns, err := buildColumnInfo(rows)
	if err != nil {
		return ExecuteSQLOutput{}, err
	}

		results, serr := scanResultRows(rows, columns)
		if serr != nil {
			return serr
		}

	results, err := scanResultRows(rows, columns)
	if err != nil {
		return ExecuteSQLOutput{}, err
	}

		// Handle empty results (return empty array, not error)
		if results == nil {
			results = []map[string]any{}
		}

	// Handle empty results (return empty array, not error)
	if results == nil {
		results = []map[string]any{}
	}
	// Detect truncation: if we auto-added limit+1 and got more than limit rows
	limited := false
	if autoAddedLimit && len(results) > limit {
		limited = true
		results = results[:limit]
	}

		// Detect truncation: if we auto-added limit+1 and got more than limit rows
		limited := false
		if autoAddedLimit && len(results) > limit {
			limited = true
			results = results[:limit]
		}

	queryReported := buildQueryReported(input.Query, autoAddedLimit, limit)

		queryReported := buildQueryReported(input.Query, autoAddedLimit, limit)

	return ExecuteSQLOutput{
		Rows:     results,
		RowCount: len(results),
		Columns:  columnInfo,
		Limited:  limited,
		Query:    queryReported,
	}, nil

		output = ExecuteSQLOutput{
			Rows:     results,
			RowCount: len(results),
			Columns:  columnInfo,
			Limited:  limited,
			Query:    queryReported,
		}
		return nil
	})
	return output, err

	}
	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("database connection failed: %w", err)

	defer database.Close()

	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_pattern")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_pattern", func(database *sql.DB, tx *db.LoggedTx) error {
		// Check if pattern with same record_s/sleep_s already exists
		existing, found, ferr := findExistingPattern(ctx, tx, *input.RecordSeconds, *input.SleepSeconds)
		if ferr != nil {
			return fmt.Errorf("failed to check for existing pattern: %w", ferr)

	}()
	// Check if pattern with same record_s/sleep_s already exists
	if existing, found, err := findExistingPattern(ctx, tx, *input.RecordSeconds, *input.SleepSeconds); err != nil {
		return output, fmt.Errorf("failed to check for existing pattern: %w", err)
	} else if found {
		if err = tx.Commit(); err != nil {
			return output, fmt.Errorf("failed to commit transaction: %w", err)

		if found {
			output.Pattern = existing
			output.Message = fmt.Sprintf("Pattern already exists with ID %s (record %ds, sleep %ds) - returning existing pattern",
				existing.ID, existing.RecordS, existing.SleepS)
			return nil // commit transaction

		output.Pattern = existing
		output.Message = fmt.Sprintf("Pattern already exists with ID %s (record %ds, sleep %ds) - returning existing pattern",
			existing.ID, existing.RecordS, existing.SleepS)
		return output, nil
	}
	// Generate ID
	id, err := utils.GenerateShortID()
	if err != nil {
		return output, fmt.Errorf("failed to generate ID: %w", err)
	}
	// Insert pattern
	_, err = tx.ExecContext(ctx,
		"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, created_at, last_modified, active) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, *input.RecordSeconds, *input.SleepSeconds,
	)
	if err != nil {
		return output, fmt.Errorf("failed to create pattern: %w", err)
	}

	// Fetch the created pattern
	var pattern db.CyclicRecordingPattern
	err = tx.QueryRowContext(ctx,
		"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
		id,
	).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active)
	if err != nil {
		return output, fmt.Errorf("failed to fetch created pattern: %w", err)
	}

		// Generate ID
		id, gerr := utils.GenerateShortID()
		if gerr != nil {
			return fmt.Errorf("failed to generate ID: %w", gerr)
		}

	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

		// Insert pattern
		if _, err := tx.ExecContext(ctx,
			"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, created_at, last_modified, active) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
			id, *input.RecordSeconds, *input.SleepSeconds,
		); err != nil {
			return fmt.Errorf("failed to create pattern: %w", err)
		}

	output.Pattern = pattern
	output.Message = fmt.Sprintf("Successfully created cyclic recording pattern with ID %s (record %ds, sleep %ds)",
		pattern.ID, pattern.RecordS, pattern.SleepS)

		// Fetch the created pattern
		var pattern db.CyclicRecordingPattern
		if err := tx.QueryRowContext(ctx,
			"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
			id,
		).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active); err != nil {
			return fmt.Errorf("failed to fetch created pattern: %w", err)
		}

	return output, nil

		output.Pattern = pattern
		output.Message = fmt.Sprintf("Successfully created cyclic recording pattern with ID %s (record %ds, sleep %ds)",
			pattern.ID, pattern.RecordS, pattern.SleepS)
		return nil
	})
	return output, err

func updatePattern(ctx context.Context, input PatternInput) (PatternOutput, error) {
	var output PatternOutput
	if err := validateUpdatePatternInput(input); err != nil {
		return output, err
	}
	// Open writable database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	// Verify pattern exists and check active status

// verifyPatternExistsAndActive checks that a pattern exists and is active.
func verifyPatternExistsAndActive(database *sql.DB, patternID string) error {

	err = database.QueryRow(

	err := database.QueryRow(

		*input.ID, *input.ID,

		patternID, patternID,

		return output, fmt.Errorf("failed to query pattern: %w", err)

		return fmt.Errorf("failed to query pattern: %w", err)

		return output, fmt.Errorf("pattern not found: %s", *input.ID)

		return fmt.Errorf("pattern not found: %s", patternID)

		return output, fmt.Errorf("pattern '%s' is not active (cannot update inactive patterns)", *input.ID)

		return fmt.Errorf("pattern '%s' is not active (cannot update inactive patterns)", patternID)

	return nil
}

	// Build dynamic UPDATE query

// buildPatternUpdateQuery builds the dynamic UPDATE query and args for pattern fields.
func buildPatternUpdateQuery(input PatternInput) (string, []any, error) {

		return output, fmt.Errorf("no fields provided to update")

		return "", nil, fmt.Errorf("no fields provided to update")

	// Always update last_modified

	return query, args, nil
}

	// Begin logged transaction for update
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_pattern")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)

func updatePattern(ctx context.Context, input PatternInput) (PatternOutput, error) {
	var output PatternOutput
	if err := validateUpdatePatternInput(input); err != nil {
		return output, err

	defer func() {
		if err != nil {
			tx.Rollback()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_pattern", func(database *sql.DB, tx *db.LoggedTx) error {
		if err := verifyPatternExistsAndActive(database, *input.ID); err != nil {
			return err

	}()

	_, err = tx.Exec(query, args...)
	if err != nil {
		return output, fmt.Errorf("failed to update pattern: %w", err)
	}

		query, args, qerr := buildPatternUpdateQuery(input)
		if qerr != nil {
			return qerr
		}

	// Fetch the updated pattern
	var pattern db.CyclicRecordingPattern
	err = tx.QueryRow(
		"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
		*input.ID,
	).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active)
	if err != nil {
		return output, fmt.Errorf("failed to fetch updated pattern: %w", err)
	}

		if _, err := tx.Exec(query, args...); err != nil {
			return fmt.Errorf("failed to update pattern: %w", err)
		}

	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

		// Fetch the updated pattern
		var pattern db.CyclicRecordingPattern
		if err := tx.QueryRow(
			"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
			*input.ID,
		).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active); err != nil {
			return fmt.Errorf("failed to fetch updated pattern: %w", err)
		}

	output.Pattern = pattern
	output.Message = fmt.Sprintf("Successfully updated pattern (ID: %s, record %ds, sleep %ds)",
		pattern.ID, pattern.RecordS, pattern.SleepS)
	return output, nil

		output.Pattern = pattern
		output.Message = fmt.Sprintf("Successfully updated pattern (ID: %s, record %ds, sleep %ds)",
			pattern.ID, pattern.RecordS, pattern.SleepS)
		return nil
	})
	return output, err

	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_location")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_location", func(database *sql.DB, tx *db.LoggedTx) error {
		if err := verifyDatasetExistsAndActive(ctx, tx, *input.DatasetID); err != nil {
			return err

	}()

	if err := verifyDatasetExistsAndActive(ctx, tx, *input.DatasetID); err != nil {
		return output, err
	}
	// Check for existing location with same name in dataset (UNIQUE constraint)
	var existingID string
	err = tx.QueryRowContext(ctx,
		"SELECT id FROM location WHERE dataset_id = ? AND name = ? AND active = true",
		*input.DatasetID, *input.Name,
	).Scan(&existingID)
	if err == nil {
		return returnExistingLocation(ctx, tx, existingID, output)
	}

		// Check for existing location with same name in dataset (UNIQUE constraint)
		var existingID string
		qerr := tx.QueryRowContext(ctx,
			"SELECT id FROM location WHERE dataset_id = ? AND name = ? AND active = true",
			*input.DatasetID, *input.Name,
		).Scan(&existingID)

	// Generate ID
	id, err := utils.GenerateShortID()
	if err != nil {
		return output, fmt.Errorf("failed to generate ID: %w", err)
	}

		if qerr == nil {
			result, rerr := returnExistingLocation(ctx, tx, existingID)
			if rerr != nil {
				return rerr
			}
			output = result
			return nil
		}

	// Insert location
	_, err = tx.ExecContext(ctx,
		"INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, *input.DatasetID, *input.Name, *input.Latitude, *input.Longitude, *input.TimezoneID, input.Description,
	)
	if err != nil {
		return output, fmt.Errorf("failed to create location: %w", err)
	}

		// Generate ID
		id, gerr := utils.GenerateShortID()
		if gerr != nil {
			return fmt.Errorf("failed to generate ID: %w", gerr)
		}

	// Fetch the created location
	location, err := fetchLocationByID(ctx, tx, id)
	if err != nil {
		return output, fmt.Errorf("failed to fetch created location: %w", err)
	}

		// Insert location
		if _, err := tx.ExecContext(ctx,
			"INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
			id, *input.DatasetID, *input.Name, *input.Latitude, *input.Longitude, *input.TimezoneID, input.Description,
		); err != nil {
			return fmt.Errorf("failed to create location: %w", err)
		}

	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

		// Fetch the created location
		location, ferr := fetchLocationByID(ctx, tx, id)
		if ferr != nil {
			return fmt.Errorf("failed to fetch created location: %w", ferr)
		}

	output.Location = location
	output.Message = fmt.Sprintf("Successfully created location '%s' with ID %s (%.6f, %.6f, %s)",
		location.Name, location.ID, location.Latitude, location.Longitude, location.TimezoneID)
	return output, nil

		output.Location = location
		output.Message = fmt.Sprintf("Successfully created location '%s' with ID %s (%.6f, %.6f, %s)",
			location.Name, location.ID, location.Latitude, location.Longitude, location.TimezoneID)
		return nil
	})
	return output, err

func returnExistingLocation(ctx context.Context, tx *db.LoggedTx, existingID string, output LocationOutput) (LocationOutput, error) {

// Caller is responsible for committing the transaction.
func returnExistingLocation(ctx context.Context, tx *db.LoggedTx, existingID string) (LocationOutput, error) {
	var output LocationOutput

	}
	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)

func verifyDatasetExistsAndActive(ctx context.Context, queryer interface {
	QueryRowContext(context.Context, string, ...any) *sql.Row
}, datasetID string) error {
	var exists, active bool
	err := queryer.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false)",
		datasetID, datasetID,
	).Scan(&exists, &active)
	if err != nil {
		return fmt.Errorf("failed to verify dataset: %w", err)
	}
	if !exists {
		return fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
	}
	if !active {
		return fmt.Errorf("dataset (ID: %s) is not active", datasetID)
	}
	return nil

func verifyDatasetExistsAndActive(ctx context.Context, q db.Querier, datasetID string) error {
	_, err := db.DatasetExistsAndActive(q, datasetID)
	return err

	// Open writable database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_location", func(database *sql.DB, tx *db.LoggedTx) error {
		if err := verifyLocationExistsAndActive(database, locationID); err != nil {
			return err
		}

	if err := verifyLocationExistsAndActive(database, locationID); err != nil {
		return output, err
	}

		// Verify dataset exists if DatasetID provided (relationship consistency)
		if input.DatasetID != nil {
			if err := verifyDatasetExistsAndActive(ctx, database, *input.DatasetID); err != nil {
				return err
			}
		}

	// Verify dataset exists if DatasetID provided (relationship consistency)
	if input.DatasetID != nil {
		if err := verifyDatasetExistsAndActive(context.Background(), database, *input.DatasetID); err != nil {
			return output, err

		updates, args, uerr := buildLocationUpdates(input, locationID)
		if uerr != nil {
			return uerr

	}
	updates, args, err := buildLocationUpdates(input, locationID)
	if err != nil {
		return output, err
	}
	query := fmt.Sprintf("UPDATE location SET %s WHERE id = ?", strings.Join(updates, ", "))

		query := fmt.Sprintf("UPDATE location SET %s WHERE id = ?", strings.Join(updates, ", "))

	// Begin logged transaction for update
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_location")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

		if _, err := tx.ExecContext(ctx, query, args...); err != nil {
			return fmt.Errorf("failed to update location: %w", err)

	}()
	_, err = tx.ExecContext(ctx, query, args...)
	if err != nil {
		return output, fmt.Errorf("failed to update location: %w", err)
	}
	// Fetch the updated location
	location, err := fetchLocationByID(ctx, tx, locationID)
	if err != nil {
		return output, fmt.Errorf("failed to fetch updated location: %w", err)
	}

	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

		// Fetch the updated location
		location, ferr := fetchLocationByID(ctx, tx, locationID)
		if ferr != nil {
			return fmt.Errorf("failed to fetch updated location: %w", ferr)
		}

	output.Location = location
	output.Message = fmt.Sprintf("Successfully updated location '%s' (ID: %s)", location.Name, location.ID)
	return output, nil

		output.Location = location
		output.Message = fmt.Sprintf("Successfully updated location '%s' (ID: %s)", location.Name, location.ID)
		return nil
	})
	return output, err

	"database/sql"

	// Open database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	// Scan for WAV files

	// Scan for WAV files (no DB needed)

	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "import_unstructured")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	err := db.WithWriteTx(ctx, dbPath, "import_unstructured", func(database *sql.DB, tx *db.LoggedTx) error {
		// Process each file
		for _, filePath := range files {
			fileResult, procErr := processUnstructuredFile(tx, filePath, input.DatasetID)

	// Process each file
	for _, filePath := range files {
		fileResult, procErr := processUnstructuredFile(tx, filePath, input.DatasetID)

			if procErr != nil {
				output.FailedFiles++
				output.Errors = append(output.Errors, utils.FileImportError{
					FileName: filepath.Base(filePath),
					Error:    procErr.Error(),
					Stage:    utils.StageProcess,
				})
				continue
			}

		if procErr != nil {
			output.FailedFiles++
			output.Errors = append(output.Errors, utils.FileImportError{
				FileName: filepath.Base(filePath),
				Error:    procErr.Error(),
				Stage:    utils.StageProcess,
			})
			continue

			if fileResult.Skipped {
				output.SkippedFiles++
			} else {
				output.ImportedFiles++
				output.TotalDuration += fileResult.Duration
			}

		if fileResult.Skipped {
			output.SkippedFiles++
		} else {
			output.ImportedFiles++
			output.TotalDuration += fileResult.Duration
		}

		return nil
	})
	if err != nil {
		return output, err

	// Commit transaction
	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

	}
	// Open database for validation
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)

	defer database.Close()

	// Verify dataset exists and is active
	var datasetExists bool
	err = database.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)",
		input.DatasetID,
	).Scan(&datasetExists)
	if err != nil {
		return fmt.Errorf("failed to query dataset: %w", err)
	}
	if !datasetExists {
		return fmt.Errorf("dataset not found or inactive: %s", input.DatasetID)
	}

	return db.WithReadDB(dbPath, func(database *sql.DB) error {
		// Verify dataset exists and is active
		if _, err := db.DatasetExistsAndActive(database, input.DatasetID); err != nil {
			return err
		}

	// Verify dataset is 'unstructured' type
	if err := db.ValidateDatasetTypeUnstructured(database, input.DatasetID); err != nil {
		return err
	}

		// Verify dataset is 'unstructured' type
		if err := db.ValidateDatasetTypeUnstructured(database, input.DatasetID); err != nil {
			return err
		}

	return nil

		return nil
	})

	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 err := db.ValidateDatasetTypeForImport(dbConn, datasetID); err != nil {
		return err

	if datasetType != "structured" {
		return fmt.Errorf("dataset must be 'structured' type, got: %s", datasetType)
	}

	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 err := db.ValidateLocationBelongsToDataset(dbConn, locationID, datasetID); err != nil {
		return err

	if !locationExists {
		return fmt.Errorf("location not found or not linked to dataset: %s", locationID)
	}

	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)

	if err := db.ClusterBelongsToLocation(dbConn, clusterID, locationID); err != nil {
		return err

	// Open database for validation queries
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()

	return db.WithReadDB(dbPath, func(database *sql.DB) error {
		// Verify dataset exists, is active, and is 'structured' type
		if err := db.ValidateDatasetTypeForImport(database, datasetID); err != nil {
			return err
		}

	// Verify dataset exists and is active
	var datasetExists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)", datasetID).Scan(&datasetExists)
	if err != nil {
		return fmt.Errorf("failed to query dataset: %w", err)
	}
	if !datasetExists {
		return fmt.Errorf("dataset not found or inactive: %s", datasetID)
	}

		// Verify location exists and belongs to dataset
		if err := db.ValidateLocationBelongsToDataset(database, locationID, datasetID); err != nil {
			return err
		}

	// Verify dataset is 'structured' type (file imports only support structured datasets)
	if err := db.ValidateDatasetTypeForImport(database, datasetID); err != nil {
		return err
	}
	// Verify location exists and belongs to dataset
	var locationDatasetID string
	err = database.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)
	if err == sql.ErrNoRows {
		return fmt.Errorf("location not found or inactive: %s", locationID)
	}
	if err != nil {
		return fmt.Errorf("failed to query location: %w", err)
	}
	if locationDatasetID != datasetID {
		return fmt.Errorf("location %s does not belong to dataset %s", locationID, datasetID)
	}
	// Verify cluster exists and belongs to location
	var clusterLocationID string
	err = database.QueryRow("SELECT location_id FROM cluster WHERE id = ? AND active = true", clusterID).Scan(&clusterLocationID)
	if err == sql.ErrNoRows {
		return fmt.Errorf("cluster not found or inactive: %s", clusterID)
	}
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}
	if clusterLocationID != locationID {
		return fmt.Errorf("cluster %s does not belong to location %s", clusterID, locationID)
	}

		// Verify cluster exists and belongs to location
		if err := db.ClusterBelongsToLocation(database, clusterID, locationID); err != nil {
			return err
		}

	return nil

		return nil
	})

	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()

	err = db.WithWriteTx(ctx, dbPath, "create_or_update_dataset", func(database *sql.DB, tx *db.LoggedTx) error {
		// Check for existing dataset with same name (UNIQUE constraint)
		var existingID string
		qerr := tx.QueryRowContext(ctx,
			"SELECT id FROM dataset WHERE name = ? AND active = true",
			*input.Name,
		).Scan(&existingID)

	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_dataset")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

		if qerr == nil {
			result, herr := handleExistingDataset(ctx, tx, existingID)
			if herr != nil {
				return herr
			}
			output = result
			return nil

	}()

	// Check for existing dataset with same name (UNIQUE constraint)
	var existingID string
	err = tx.QueryRowContext(ctx,
		"SELECT id FROM dataset WHERE name = ? AND active = true",
		*input.Name,
	).Scan(&existingID)
	if err == nil {
		return handleExistingDataset(ctx, tx, existingID)
	}
	return insertNewDataset(ctx, tx, *input.Name, input.Description, datasetType)

		result, insErr := insertNewDataset(ctx, tx, *input.Name, input.Description, datasetType)
		if insErr != nil {
			return insErr
		}
		output = result
		return nil
	})
	return output, err

// Caller is responsible for committing the transaction.

	}
	if err = tx.Commit(); err != nil {
		return DatasetOutput{}, fmt.Errorf("failed to commit transaction: %w", err)

// Caller is responsible for committing the transaction.

	}
	if err = tx.Commit(); err != nil {
		return DatasetOutput{}, fmt.Errorf("failed to commit transaction: %w", err)

	// Open writable database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_dataset", func(database *sql.DB, tx *db.LoggedTx) error {
		// Verify dataset exists and check active status
		if err := verifyDatasetActive(database, datasetID); err != nil {
			return err
		}

	// Verify dataset exists and check active status
	if err := verifyDatasetActive(database, datasetID); err != nil {
		return output, err
	}

		// Build dynamic UPDATE query
		query, args, qerr := buildUpdateQuery(input, datasetID)
		if qerr != nil {
			return qerr
		}

	// Build dynamic UPDATE query
	query, args, err := buildUpdateQuery(input, datasetID)
	if err != nil {
		return output, err
	}

		if _, err := tx.Exec(query, args...); err != nil {
			return fmt.Errorf("failed to update dataset: %w", err)
		}

	// Begin logged transaction for update
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_dataset")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {

		// Fetch the updated dataset
		var dataset db.Dataset
		err := tx.QueryRow(
			"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
			datasetID,
		).Scan(&dataset.ID, &dataset.Name, &dataset.Description, &dataset.CreatedAt, &dataset.LastModified, &dataset.Active, &dataset.Type)

			tx.Rollback()

			return fmt.Errorf("failed to fetch updated dataset: %w", err)

	}()
	_, err = tx.Exec(query, args...)
	if err != nil {
		return output, fmt.Errorf("failed to update dataset: %w", err)
	}
	// Fetch the updated dataset
	var dataset db.Dataset
	err = tx.QueryRow(
		"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
		datasetID,
	).Scan(&dataset.ID, &dataset.Name, &dataset.Description, &dataset.CreatedAt, &dataset.LastModified, &dataset.Active, &dataset.Type)
	if err != nil {
		return output, fmt.Errorf("failed to fetch updated dataset: %w", err)
	}
	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}
	output.Dataset = dataset
	output.Message = fmt.Sprintf("Successfully updated dataset '%s' (ID: %s)", dataset.Name, dataset.ID)

	return output, nil

		output.Dataset = dataset
		output.Message = fmt.Sprintf("Successfully updated dataset '%s' (ID: %s)", dataset.Name, dataset.ID)
		return nil
	})
	return output, err

	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_cluster")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

	err := db.WithWriteTx(ctx, dbPath, "create_or_update_cluster", func(database *sql.DB, tx *db.LoggedTx) error {
		// Verify parent references exist and are active
		datasetName, locationName, verr := verifyClusterParentRefs(ctx, tx, input)
		if verr != nil {
			return verr

	}()

	// Verify parent references exist and are active
	datasetName, locationName, err := verifyClusterParentRefs(ctx, tx, input)
	if err != nil {
		return output, err
	}

		// Check for existing cluster with same name in location (UNIQUE constraint)
		existing, findErr := findExistingClusterInLocation(ctx, tx, *input.LocationID, *input.Name)
		if findErr == nil {
			output.Cluster = existing
			output.Message = fmt.Sprintf("Cluster '%s' already exists in location '%s' (ID: %s) - returning existing cluster", existing.Name, locationName, existing.ID)
			return nil // commit transaction
		}

	// Check for existing cluster with same name in location (UNIQUE constraint)
	existing, err := findExistingClusterInLocation(ctx, tx, *input.LocationID, *input.Name)
	if err == nil {
		if err = tx.Commit(); err != nil {
			return output, fmt.Errorf("failed to commit transaction: %w", err)

		result, insErr := insertNewCluster(ctx, tx, input, datasetName, locationName)
		if insErr != nil {
			return insErr

		output.Cluster = existing
		output.Message = fmt.Sprintf("Cluster '%s' already exists in location '%s' (ID: %s) - returning existing cluster", existing.Name, locationName, existing.ID)
		return output, nil
	}
	return insertNewCluster(ctx, tx, input, datasetName, locationName)

		output = result
		return nil // commit transaction
	})
	return output, err

	datasetName, err := verifyDatasetForCluster(ctx, tx, *input.DatasetID)

	datasetName, err := db.DatasetExistsAndActive(tx, *input.DatasetID)

	locationName, err := verifyLocationForCluster(ctx, tx, *input.LocationID, *input.DatasetID, datasetName)

	locationName, err := db.LocationBelongsToDataset(tx, *input.LocationID, *input.DatasetID)

// Caller is responsible for committing the transaction.

	}
	if err = tx.Commit(); err != nil {
		return ClusterOutput{}, fmt.Errorf("failed to commit transaction: %w", err)

// verifyDatasetForCluster verifies dataset exists and is active within a transaction
func verifyDatasetForCluster(ctx context.Context, tx *db.LoggedTx, datasetID string) (string, error) {
	var exists, active bool
	var name string
	err := tx.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false), COALESCE((SELECT name FROM dataset WHERE id = ?), '')",
		datasetID, datasetID, datasetID,
	).Scan(&exists, &active, &name)
	if err != nil {
		return "", fmt.Errorf("failed to verify dataset: %w", err)
	}
	if !exists {
		return "", fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
	}
	if !active {
		return "", fmt.Errorf("dataset '%s' (ID: %s) is not active", name, datasetID)
	}
	return name, nil
}
// verifyLocationForCluster verifies location exists, is active, and belongs to the dataset
func verifyLocationForCluster(ctx context.Context, tx *db.LoggedTx, locationID, datasetID, datasetName string) (string, error) {
	var exists, active bool
	var name, locDatasetID string
	err := tx.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), COALESCE((SELECT active FROM location WHERE id = ?), false), COALESCE((SELECT name FROM location WHERE id = ?), ''), COALESCE((SELECT dataset_id FROM location WHERE id = ?), '')",
		locationID, locationID, locationID, locationID,
	).Scan(&exists, &active, &name, &locDatasetID)
	if err != nil {
		return "", fmt.Errorf("failed to verify location: %w", err)
	}
	if !exists {
		return "", fmt.Errorf("location with ID '%s' does not exist", locationID)
	}
	if !active {
		return "", fmt.Errorf("location '%s' (ID: %s) is not active", name, locationID)
	}
	if locDatasetID != datasetID {
		return "", fmt.Errorf("location '%s' (ID: %s) does not belong to dataset '%s' (ID: %s) - it belongs to dataset ID '%s'",
			name, locationID, datasetName, datasetID, locDatasetID)
	}
	return name, nil
}

	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	if err := validateClusterActive(database, clusterID); err != nil {
		return output, err
	}
	if err := validateClusterCyclicPattern(database, input); err != nil {
		return output, err
	}
	query, args, err := buildClusterUpdateQuery(input, clusterID)
	if err != nil {
		return output, err
	}

	err = db.WithWriteTx(ctx, dbPath, "create_or_update_cluster", func(database *sql.DB, tx *db.LoggedTx) error {
		if err := validateClusterActive(database, clusterID); err != nil {
			return err
		}

	tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_cluster")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()

		if err := validateClusterCyclicPattern(database, input); err != nil {
			return err

	}()

	if _, err = tx.Exec(query, args...); err != nil {
		return output, fmt.Errorf("failed to update cluster: %w", err)
	}

		query, args, qerr := buildClusterUpdateQuery(input, clusterID)
		if qerr != nil {
			return qerr
		}

	cluster, err := fetchClusterByID(ctx, tx, clusterID)
	if err != nil {
		return output, fmt.Errorf("failed to fetch updated cluster: %w", err)
	}

		if _, err := tx.Exec(query, args...); err != nil {
			return fmt.Errorf("failed to update cluster: %w", err)
		}

	if err = tx.Commit(); err != nil {
		return output, fmt.Errorf("failed to commit transaction: %w", err)
	}

		cluster, ferr := fetchClusterByID(ctx, tx, clusterID)
		if ferr != nil {
			return fmt.Errorf("failed to fetch updated cluster: %w", ferr)
		}

	output.Cluster = cluster
	output.Message = fmt.Sprintf("Successfully updated cluster '%s' (ID: %s)", cluster.Name, cluster.ID)
	return output, nil

		output.Cluster = cluster
		output.Message = fmt.Sprintf("Successfully updated cluster '%s' (ID: %s)", cluster.Name, cluster.ID)
		return nil
	})
	return output, err

	"sort"

	"strconv"

	"sync"
	"sync/atomic"

}
// RavenSelection represents a single Raven selection
type RavenSelection struct {
	StartTime float64
	EndTime   float64
	FreqLow   float64
	FreqHigh  float64
	Species   string
}
// ravenJob represents a single Raven file to process
type ravenJob struct {
	ravenFile string
}
// ravenResult represents the result of processing a single Raven file
type ravenResult struct {
	ravenFile string
	calls     []ClusteredCall
	written   bool
	skipped   bool
	err       error
}
func (r ravenResult) filePath() string          { return r.ravenFile }
func (r ravenResult) getCalls() []ClusteredCall { return r.calls }
func (r ravenResult) wasWritten() bool          { return r.written }
func (r ravenResult) wasSkipped() bool          { return r.skipped }
func (r ravenResult) getError() error           { return r.err }
// CallsFromRaven processes Raven selection files and writes .data files
func CallsFromRaven(input CallsFromRavenInput) (CallsFromRavenOutput, error) {
	var output CallsFromRavenOutput
	output.Filter = "Raven"
	// Collect Raven files to process
	var ravenFiles []string
	if input.File != "" {
		ravenFiles = []string{input.File}
	} else if input.Folder != "" {
		var err error
		ravenFiles, err = findRavenFiles(input.Folder)
		if err != nil {
			errMsg := fmt.Sprintf("Failed to find Raven files: %v", err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
	} else {
		errMsg := "Either --folder or --file must be specified"
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}
	if len(ravenFiles) == 0 {
		errMsg := "No Raven files found"
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}
	// Single file or small batch: process sequentially (avoid goroutine overhead)
	if len(ravenFiles) < 10 {
		return callsFromRavenSequential(input, ravenFiles)
	}
	// Large batch: parallel processing with DirCache
	return callsFromRavenParallel(input, ravenFiles)

// callsFromRavenSequential processes Raven files one at a time (for small batches)
func callsFromRavenSequential(input CallsFromRavenInput, ravenFiles []string) (CallsFromRavenOutput, error) {
	var output CallsFromRavenOutput
	output.Filter = "Raven"
	// Build DirCache once for the folder (even sequential benefits from avoiding repeated dir scans)
	dirCaches := make(map[string]*DirCache)
	if input.Folder != "" {
		dirCaches[input.Folder] = NewDirCache(input.Folder)
	}
	speciesCount := make(map[string]int)
	var allCalls []ClusteredCall
	dataFilesWritten := 0
	dataFilesSkipped := 0
	filesProcessed := 0
	filesDeleted := 0
	for _, ravenFile := range ravenFiles {
		dir := filepath.Dir(ravenFile)
		cache := dirCaches[dir]
		if cache == nil {
			cache = NewDirCache(dir)
			dirCaches[dir] = cache
		}
		calls, written, skipped, err := processRavenFileCached(ravenFile, cache)
		if err != nil {
			errMsg := fmt.Sprintf("Error processing %s: %v", ravenFile, err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
		if written {
			dataFilesWritten++
		}
		if skipped {
			dataFilesSkipped++
		}

		for _, call := range calls {
			allCalls = append(allCalls, call)
			speciesCount[call.EbirdCode]++
		}

// ravenSource implements CallSource for Raven selection files
type ravenSource struct{}

		filesProcessed++

func (ravenSource) Name() string { return "Raven" }

		// Delete if requested and successfully processed
		if input.Delete && written {
			if err := os.Remove(ravenFile); err != nil {
				errMsg := fmt.Sprintf("Failed to delete %s: %v", ravenFile, err)
				output.Error = &errMsg
				return output, fmt.Errorf("%s", errMsg)
			}
			filesDeleted++
		}
		if input.ProgressHandler != nil {
			input.ProgressHandler(filesProcessed, len(ravenFiles), filepath.Base(ravenFile))
		}
	}
	// Sort all calls by file, then start time
	sort.Slice(allCalls, func(i, j int) bool {
		if allCalls[i].File != allCalls[j].File {
			return allCalls[i].File < allCalls[j].File
		}
		return allCalls[i].StartTime < allCalls[j].StartTime
	})
	output.Calls = allCalls
	output.TotalCalls = len(allCalls)
	output.SpeciesCount = speciesCount
	output.DataFilesWritten = dataFilesWritten
	output.DataFilesSkipped = dataFilesSkipped
	output.FilesProcessed = filesProcessed
	output.FilesDeleted = filesDeleted
	return output, nil
}
// callsFromRavenParallel processes Raven files concurrently using a worker pool and DirCache
func callsFromRavenParallel(input CallsFromRavenInput, ravenFiles []string) (CallsFromRavenOutput, error) {
	var output CallsFromRavenOutput
	output.Filter = "Raven"
	total := len(ravenFiles)
	var processed atomic.Int32
	// Build DirCache for the folder
	dirCaches := &sync.Map{}
	if input.Folder != "" {
		cache := NewDirCache(input.Folder)
		dirCaches.Store(input.Folder, cache)
	}
	// Create job and result channels
	jobs := make(chan ravenJob, total)
	results := make(chan parallelResult, total)
	// Start workers
	var wg sync.WaitGroup
	for range DOT_DATA_WORKERS {
		wg.Add(1)
		go ravenWorker(dirCaches, jobs, results, &wg)
	}
	// Send jobs
	for _, ravenFile := range ravenFiles {
		jobs <- ravenJob{ravenFile: ravenFile}
	}
	close(jobs)
	// Wait for workers to finish, then close results
	go func() {
		wg.Wait()
		close(results)
	}()
	// Collect results with progress reporting
	stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)
	if stats.firstErr != nil {
		errMsg := stats.firstErr.Error()
		output.Error = &errMsg
		return output, stats.firstErr
	}
	sortCallsByFileAndTime(stats.calls)
	output.Calls = stats.calls
	output.TotalCalls = len(stats.calls)
	output.SpeciesCount = stats.speciesCount
	output.DataFilesWritten = stats.dataFilesWritten
	output.DataFilesSkipped = stats.dataFilesSkipped
	output.FilesProcessed = stats.filesProcessed
	output.FilesDeleted = stats.filesDeleted
	return output, nil
}
// ravenWorker processes Raven files from the jobs channel
func ravenWorker(dirCaches *sync.Map, jobs <-chan ravenJob, results chan<- parallelResult, wg *sync.WaitGroup) {
	defer wg.Done()
	for job := range jobs {
		dir := filepath.Dir(job.ravenFile)
		// Get or create DirCache for this directory
		var cache *DirCache
		if cached, ok := dirCaches.Load(dir); ok {
			cache = cached.(*DirCache)
		} else {
			cache = NewDirCache(dir)
			dirCaches.Store(dir, cache)
		}
		calls, written, skipped, err := processRavenFileCached(job.ravenFile, cache)
		results <- ravenResult{
			ravenFile: job.ravenFile,
			calls:     calls,
			written:   written,
			skipped:   skipped,
			err:       err,
		}
	}
}
// findRavenFiles finds all Raven selection files in a folder
func findRavenFiles(folder string) ([]string, error) {

func (ravenSource) FindFiles(folder string) ([]string, error) {

func (ravenSource) ProcessFile(ravenFile string, cache *DirCache) ([]ClusteredCall, bool, bool, error) {
	return processRavenFileCached(ravenFile, cache)
}
// CallsFromRaven processes Raven selection files and writes .data files
func CallsFromRaven(input CallsFromRavenInput) (CallsFromRavenOutput, error) {
	src := ravenSource{}
	commonInput := CallsFromSourceInput(input)

	commonOutput, err := callsFromSource(src, commonInput)
	// Convert to Raven-specific output type
	var output CallsFromRavenOutput
	output.Calls = commonOutput.Calls
	output.TotalCalls = commonOutput.TotalCalls
	output.SpeciesCount = commonOutput.SpeciesCount
	output.DataFilesWritten = commonOutput.DataFilesWritten
	output.DataFilesSkipped = commonOutput.DataFilesSkipped
	output.FilesProcessed = commonOutput.FilesProcessed
	output.FilesDeleted = commonOutput.FilesDeleted
	output.Filter = commonOutput.Filter
	output.Error = commonOutput.Error
	return output, err
}
// RavenSelection represents a single Raven selection
type RavenSelection struct {
	StartTime float64
	EndTime   float64
	FreqLow   float64
	FreqHigh  float64
	Species   string
}

	if _, err := fmt.Sscanf(fields[idx.beginTimeIdx], "%f", &sel.StartTime); err != nil {

	startTime, err := strconv.ParseFloat(fields[idx.beginTimeIdx], 64)
	if err != nil {

	if _, err := fmt.Sscanf(fields[idx.endTimeIdx], "%f", &sel.EndTime); err != nil {

	sel.StartTime = startTime
	endTime, err := strconv.ParseFloat(fields[idx.endTimeIdx], 64)
	if err != nil {

	sel.EndTime = endTime

		if _, err := fmt.Sscanf(fields[idx.lowFreqIdx], "%f", &sel.FreqLow); err != nil {

		freqLow, err := strconv.ParseFloat(fields[idx.lowFreqIdx], 64)
		if err != nil {

		sel.FreqLow = freqLow

		if _, err := fmt.Sscanf(fields[idx.highFreqIdx], "%f", &sel.FreqHigh); err != nil {

		freqHigh, err := strconv.ParseFloat(fields[idx.highFreqIdx], 64)
		if err != nil {

		sel.FreqHigh = freqHigh

}
// extractFilename extracts just the filename from a path
// "./C05/2025-11-08/20250518_210000.WAV" -> "20250518_210000.WAV"
func extractFilename(path string) string {
	return filepath.Base(path)

		filename := extractFilename(call.File)

		filename := filepath.Base(call.File)

package tools
import (
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"sync"
	"sync/atomic"
)
// CallsFromSourceInput defines the common input for calls-from-source tools
type CallsFromSourceInput struct {
	Folder          string          `json:"folder"`
	File            string          `json:"file"`
	Delete          bool            `json:"delete"`
	ProgressHandler ProgressHandler `json:"-"` // Optional progress callback
}
// CallsFromSourceOutput defines the common output for calls-from-source tools
type CallsFromSourceOutput struct {
	Calls            []ClusteredCall `json:"calls"`
	TotalCalls       int             `json:"total_calls"`
	SpeciesCount     map[string]int  `json:"species_count"`
	DataFilesWritten int             `json:"data_files_written"`
	DataFilesSkipped int             `json:"data_files_skipped"`
	FilesProcessed   int             `json:"files_processed"`
	FilesDeleted     int             `json:"files_deleted"`
	Filter           string          `json:"filter"`
	Error            *string         `json:"error,omitempty"`
}
// CallSource abstracts a source of bird call data (Raven, BirdNET, etc.)
type CallSource interface {
	// Name returns the display name (e.g. "Raven", "BirdNET")
	Name() string
	// FindFiles discovers source files in the given folder
	FindFiles(folder string) ([]string, error)
	// ProcessFile processes a single source file and returns calls, write/skip status
	ProcessFile(path string, cache *DirCache) (calls []ClusteredCall, written, skipped bool, err error)
}
// callsFromSource is the shared entry point for all call source tools.
func callsFromSource(src CallSource, input CallsFromSourceInput) (CallsFromSourceOutput, error) {
	var output CallsFromSourceOutput
	output.Filter = src.Name()
	// Collect source files to process
	var files []string
	if input.File != "" {
		files = []string{input.File}
	} else if input.Folder != "" {
		var err error
		files, err = src.FindFiles(input.Folder)
		if err != nil {
			errMsg := fmt.Sprintf("Failed to find %s files: %v", src.Name(), err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
	} else {
		errMsg := "Either --folder or --file must be specified"
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}
	if len(files) == 0 {
		errMsg := fmt.Sprintf("No %s files found", src.Name())
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}
	// Single file or small batch: process sequentially (avoid goroutine overhead)
	if len(files) < 10 {
		return callsFromSourceSequential(src, input, files)
	}
	// Large batch: parallel processing with DirCache
	return callsFromSourceParallel(src, input, files)
}
// callsFromSourceSequential processes source files one at a time (for small batches)
func callsFromSourceSequential(src CallSource, input CallsFromSourceInput, files []string) (CallsFromSourceOutput, error) {
	var output CallsFromSourceOutput
	output.Filter = src.Name()
	// Build DirCache once for the folder
	dirCaches := make(map[string]*DirCache)
	if input.Folder != "" {
		dirCaches[input.Folder] = NewDirCache(input.Folder)
	}
	speciesCount := make(map[string]int)
	var allCalls []ClusteredCall
	dataFilesWritten := 0
	dataFilesSkipped := 0
	filesProcessed := 0
	filesDeleted := 0
	for _, file := range files {
		dir := filepath.Dir(file)
		cache := dirCaches[dir]
		if cache == nil {
			cache = NewDirCache(dir)
			dirCaches[dir] = cache
		}
		calls, written, skipped, err := src.ProcessFile(file, cache)
		if err != nil {
			errMsg := fmt.Sprintf("Error processing %s: %v", file, err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
		if written {
			dataFilesWritten++
		}
		if skipped {
			dataFilesSkipped++
		}
		for _, call := range calls {
			allCalls = append(allCalls, call)
			speciesCount[call.EbirdCode]++
		}
		filesProcessed++
		// Delete if requested and successfully processed
		if input.Delete && written {
			if err := os.Remove(file); err != nil {
				errMsg := fmt.Sprintf("Failed to delete %s: %v", file, err)
				output.Error = &errMsg
				return output, fmt.Errorf("%s", errMsg)
			}
			filesDeleted++
		}
		if input.ProgressHandler != nil {
			input.ProgressHandler(filesProcessed, len(files), filepath.Base(file))
		}
	}
	// Sort all calls by file, then start time
	sort.Slice(allCalls, func(i, j int) bool {
		if allCalls[i].File != allCalls[j].File {
			return allCalls[i].File < allCalls[j].File
		}
		return allCalls[i].StartTime < allCalls[j].StartTime
	})
	output.Calls = allCalls
	output.TotalCalls = len(allCalls)
	output.SpeciesCount = speciesCount
	output.DataFilesWritten = dataFilesWritten
	output.DataFilesSkipped = dataFilesSkipped
	output.FilesProcessed = filesProcessed
	output.FilesDeleted = filesDeleted
	return output, nil
}
// sourceJob represents a single file to process (generic over CallSource)
type sourceJob struct {
	filePath string
}
// sourceResult represents the result of processing a single source file
type sourceResult struct {
	path    string
	calls   []ClusteredCall
	written bool
	skipped bool
	err     error
}
func (r sourceResult) filePath() string          { return r.path }
func (r sourceResult) getCalls() []ClusteredCall { return r.calls }
func (r sourceResult) wasWritten() bool          { return r.written }
func (r sourceResult) wasSkipped() bool          { return r.skipped }
func (r sourceResult) getError() error           { return r.err }
// callsFromSourceParallel processes source files concurrently using a worker pool and DirCache
func callsFromSourceParallel(src CallSource, input CallsFromSourceInput, files []string) (CallsFromSourceOutput, error) {
	var output CallsFromSourceOutput
	output.Filter = src.Name()
	total := len(files)
	var processed atomic.Int32
	// Build DirCache for the folder
	dirCaches := &sync.Map{}
	if input.Folder != "" {
		cache := NewDirCache(input.Folder)
		dirCaches.Store(input.Folder, cache)
	}
	// Create job and result channels
	jobs := make(chan sourceJob, total)
	results := make(chan parallelResult, total)
	// Start workers
	var wg sync.WaitGroup
	for range DOT_DATA_WORKERS {
		wg.Add(1)
		go sourceWorker(src, dirCaches, jobs, results, &wg)
	}
	// Send jobs
	for _, file := range files {
		jobs <- sourceJob{filePath: file}
	}
	close(jobs)
	// Wait for workers to finish, then close results
	go func() {
		wg.Wait()
		close(results)
	}()
	// Collect results with progress reporting
	stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)
	if stats.firstErr != nil {
		errMsg := stats.firstErr.Error()
		output.Error = &errMsg
		return output, stats.firstErr
	}
	sortCallsByFileAndTime(stats.calls)
	output.Calls = stats.calls
	output.TotalCalls = len(stats.calls)
	output.SpeciesCount = stats.speciesCount
	output.DataFilesWritten = stats.dataFilesWritten
	output.DataFilesSkipped = stats.dataFilesSkipped
	output.FilesProcessed = stats.filesProcessed
	output.FilesDeleted = stats.filesDeleted
	return output, nil
}
// sourceWorker processes source files from the jobs channel
func sourceWorker(src CallSource, dirCaches *sync.Map, jobs <-chan sourceJob, results chan<- parallelResult, wg *sync.WaitGroup) {
	defer wg.Done()
	for job := range jobs {
		dir := filepath.Dir(job.filePath)
		// Get or create DirCache for this directory
		var cache *DirCache
		if cached, ok := dirCaches.Load(dir); ok {
			cache = cached.(*DirCache)
		} else {
			cache = NewDirCache(dir)
			dirCaches.Store(dir, cache)
		}
		calls, written, skipped, err := src.ProcessFile(job.filePath, cache)
		results <- sourceResult{
			path:    job.filePath,
			calls:   calls,
			written: written,
			skipped: skipped,
			err:     err,
		}
	}
}

	"sort"

	"strconv"

	"sync"
	"sync/atomic"

}
// BirdNETDetection represents a single BirdNET detection
type BirdNETDetection struct {
	StartTime      float64
	EndTime        float64
	ScientificName string
	CommonName     string
	Confidence     float64
	WAVPath        string
}
// birdaJob represents a single BirdNET file to process
type birdaJob struct {
	birdaFile string
}
// birdaResult represents the result of processing a single BirdNET file
type birdaResult struct {
	birdaFile string
	calls     []ClusteredCall
	written   bool
	skipped   bool
	err       error

func (r birdaResult) filePath() string          { return r.birdaFile }
func (r birdaResult) getCalls() []ClusteredCall { return r.calls }
func (r birdaResult) wasWritten() bool          { return r.written }
func (r birdaResult) wasSkipped() bool          { return r.skipped }
func (r birdaResult) getError() error           { return r.err }
// CallsFromBirda processes BirdNET results files and writes .data files
func CallsFromBirda(input CallsFromBirdaInput) (CallsFromBirdaOutput, error) {
	var output CallsFromBirdaOutput
	output.Filter = "BirdNET"
	// Collect BirdNET files to process
	var birdaFiles []string
	if input.File != "" {
		birdaFiles = []string{input.File}
	} else if input.Folder != "" {
		var err error
		birdaFiles, err = findBirdaFiles(input.Folder)
		if err != nil {
			errMsg := fmt.Sprintf("Failed to find BirdNET files: %v", err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
	} else {
		errMsg := "Either --folder or --file must be specified"
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}

	if len(birdaFiles) == 0 {
		errMsg := "No BirdNET files found"
		output.Error = &errMsg
		return output, fmt.Errorf("%s", errMsg)
	}

// birdaSource implements CallSource for BirdNET results files
type birdaSource struct{}

	// Single file or small batch: process sequentially (avoid goroutine overhead)
	if len(birdaFiles) < 10 {
		return callsFromBirdaSequential(input, birdaFiles)
	}

func (birdaSource) Name() string { return "BirdNET" }

	// Large batch: parallel processing with DirCache
	return callsFromBirdaParallel(input, birdaFiles)
}
// callsFromBirdaSequential processes BirdNET files one at a time (for small batches)
func callsFromBirdaSequential(input CallsFromBirdaInput, birdaFiles []string) (CallsFromBirdaOutput, error) {
	var output CallsFromBirdaOutput
	output.Filter = "BirdNET"
	// Build DirCache once for the folder
	dirCaches := make(map[string]*DirCache)
	if input.Folder != "" {
		dirCaches[input.Folder] = NewDirCache(input.Folder)
	}
	speciesCount := make(map[string]int)
	var allCalls []ClusteredCall
	dataFilesWritten := 0
	dataFilesSkipped := 0
	filesProcessed := 0
	filesDeleted := 0
	for _, birdaFile := range birdaFiles {
		dir := filepath.Dir(birdaFile)
		cache := dirCaches[dir]
		if cache == nil {
			cache = NewDirCache(dir)
			dirCaches[dir] = cache
		}
		calls, written, skipped, err := processBirdaFileCached(birdaFile, cache)
		if err != nil {
			errMsg := fmt.Sprintf("Error processing %s: %v", birdaFile, err)
			output.Error = &errMsg
			return output, fmt.Errorf("%s", errMsg)
		}
		if written {
			dataFilesWritten++
		}
		if skipped {
			dataFilesSkipped++
		}
		for _, call := range calls {
			allCalls = append(allCalls, call)
			speciesCount[call.EbirdCode]++
		}
		filesProcessed++
		// Delete if requested and successfully processed
		if input.Delete && written {
			if err := os.Remove(birdaFile); err != nil {
				errMsg := fmt.Sprintf("Failed to delete %s: %v", birdaFile, err)
				output.Error = &errMsg
				return output, fmt.Errorf("%s", errMsg)
			}
			filesDeleted++
		}
		if input.ProgressHandler != nil {
			input.ProgressHandler(filesProcessed, len(birdaFiles), filepath.Base(birdaFile))
		}
	}
	// Sort all calls by file, then start time
	sort.Slice(allCalls, func(i, j int) bool {
		if allCalls[i].File != allCalls[j].File {
			return allCalls[i].File < allCalls[j].File
		}
		return allCalls[i].StartTime < allCalls[j].StartTime
	})
	output.Calls = allCalls
	output.TotalCalls = len(allCalls)
	output.SpeciesCount = speciesCount
	output.DataFilesWritten = dataFilesWritten
	output.DataFilesSkipped = dataFilesSkipped
	output.FilesProcessed = filesProcessed
	output.FilesDeleted = filesDeleted
	return output, nil
}
// callsFromBirdaParallel processes BirdNET files concurrently using a worker pool and DirCache
func callsFromBirdaParallel(input CallsFromBirdaInput, birdaFiles []string) (CallsFromBirdaOutput, error) {
	var output CallsFromBirdaOutput
	output.Filter = "BirdNET"
	total := len(birdaFiles)
	var processed atomic.Int32
	// Build DirCache for the folder
	dirCaches := &sync.Map{}
	if input.Folder != "" {
		cache := NewDirCache(input.Folder)
		dirCaches.Store(input.Folder, cache)
	}
	// Create job and result channels
	jobs := make(chan birdaJob, total)
	results := make(chan parallelResult, total)
	// Start workers
	var wg sync.WaitGroup
	for range DOT_DATA_WORKERS {
		wg.Add(1)
		go birdaWorker(dirCaches, jobs, results, &wg)
	}
	// Send jobs
	for _, birdaFile := range birdaFiles {
		jobs <- birdaJob{birdaFile: birdaFile}
	}
	close(jobs)
	// Wait for workers to finish, then close results
	go func() {
		wg.Wait()
		close(results)
	}()
	// Collect results with progress reporting
	stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)
	if stats.firstErr != nil {
		errMsg := stats.firstErr.Error()
		output.Error = &errMsg
		return output, stats.firstErr
	}
	sortCallsByFileAndTime(stats.calls)
	output.Calls = stats.calls
	output.TotalCalls = len(stats.calls)
	output.SpeciesCount = stats.speciesCount
	output.DataFilesWritten = stats.dataFilesWritten
	output.DataFilesSkipped = stats.dataFilesSkipped
	output.FilesProcessed = stats.filesProcessed
	output.FilesDeleted = stats.filesDeleted
	return output, nil
}
// birdaWorker processes BirdNET files from the jobs channel
func birdaWorker(dirCaches *sync.Map, jobs <-chan birdaJob, results chan<- parallelResult, wg *sync.WaitGroup) {
	defer wg.Done()
	for job := range jobs {
		dir := filepath.Dir(job.birdaFile)
		// Get or create DirCache for this directory
		var cache *DirCache
		if cached, ok := dirCaches.Load(dir); ok {
			cache = cached.(*DirCache)
		} else {
			cache = NewDirCache(dir)
			dirCaches.Store(dir, cache)
		}
		calls, written, skipped, err := processBirdaFileCached(job.birdaFile, cache)
		results <- birdaResult{
			birdaFile: job.birdaFile,
			calls:     calls,
			written:   written,
			skipped:   skipped,
			err:       err,
		}
	}
}
// findBirdaFiles finds all BirdNET results files in a folder
func findBirdaFiles(folder string) ([]string, error) {

func (birdaSource) FindFiles(folder string) ([]string, error) {

}
func (birdaSource) ProcessFile(birdaFile string, cache *DirCache) ([]ClusteredCall, bool, bool, error) {
	return processBirdaFileCached(birdaFile, cache)

// CallsFromBirda processes BirdNET results files and writes .data files
func CallsFromBirda(input CallsFromBirdaInput) (CallsFromBirdaOutput, error) {
	src := birdaSource{}
	commonInput := CallsFromSourceInput(input)

	commonOutput, err := callsFromSource(src, commonInput)
	// Convert to Birda-specific output type
	var output CallsFromBirdaOutput
	output.Calls = commonOutput.Calls
	output.TotalCalls = commonOutput.TotalCalls
	output.SpeciesCount = commonOutput.SpeciesCount
	output.DataFilesWritten = commonOutput.DataFilesWritten
	output.DataFilesSkipped = commonOutput.DataFilesSkipped
	output.FilesProcessed = commonOutput.FilesProcessed
	output.FilesDeleted = commonOutput.FilesDeleted
	output.Filter = commonOutput.Filter
	output.Error = commonOutput.Error
	return output, err
}
// BirdNETDetection represents a single BirdNET detection
type BirdNETDetection struct {
	StartTime      float64
	EndTime        float64
	ScientificName string
	CommonName     string
	Confidence     float64
	WAVPath        string
}

		if _, err := fmt.Sscanf(record[idx.startIdx], "%f", &det.StartTime); err != nil {
			return nil, fmt.Errorf("failed to parse start time %q: %w", record[idx.startIdx], err)

		startTime, perr := strconv.ParseFloat(record[idx.startIdx], 64)
		if perr != nil {
			return nil, fmt.Errorf("failed to parse start time %q: %w", record[idx.startIdx], perr)

		if _, err := fmt.Sscanf(record[idx.endIdx], "%f", &det.EndTime); err != nil {
			return nil, fmt.Errorf("failed to parse end time %q: %w", record[idx.endIdx], err)

		det.StartTime = startTime
		endTime, perr := strconv.ParseFloat(record[idx.endIdx], 64)
		if perr != nil {
			return nil, fmt.Errorf("failed to parse end time %q: %w", record[idx.endIdx], perr)

		det.EndTime = endTime

		if _, err := fmt.Sscanf(record[idx.confidenceIdx], "%f", &det.Confidence); err != nil {
			return nil, fmt.Errorf("failed to parse confidence %q: %w", record[idx.confidenceIdx], err)

		confidence, perr := strconv.ParseFloat(record[idx.confidenceIdx], 64)
		if perr != nil {
			return nil, fmt.Errorf("failed to parse confidence %q: %w", record[idx.confidenceIdx], perr)

		det.Confidence = confidence

	// Verify dataset exists and is active
	var datasetExists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)", input.DatasetID).Scan(&datasetExists)
	if err != nil {
		return fmt.Errorf("failed to query dataset: %w", err)
	}
	if !datasetExists {
		return fmt.Errorf("dataset not found or inactive: %s", input.DatasetID)
	}

	// Verify dataset is 'structured' type (file imports only support structured datasets)

	// Verify dataset exists and is structured

	"context"

// Querier is the common interface for *sql.DB and *LoggedTx query operations.
type Querier interface {
	QueryRow(query string, args ...any) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

func GetDatasetType(database *sql.DB, datasetID string) (string, bool, error) {

func GetDatasetType(q Querier, datasetID string) (string, bool, error) {

	err := database.QueryRow("SELECT type FROM dataset WHERE id = ?", datasetID).Scan(&datasetType)

	err := q.QueryRow("SELECT type FROM dataset WHERE id = ?", datasetID).Scan(&datasetType)

func ValidateDatasetTypeForImport(database *sql.DB, datasetID string) error {
	datasetType, exists, err := GetDatasetType(database, datasetID)

func ValidateDatasetTypeForImport(q Querier, datasetID string) error {
	datasetType, exists, err := GetDatasetType(q, datasetID)

func ValidateDatasetTypeUnstructured(database *sql.DB, datasetID string) error {
	datasetType, exists, err := GetDatasetType(database, datasetID)

func ValidateDatasetTypeUnstructured(q Querier, datasetID string) error {
	datasetType, exists, err := GetDatasetType(q, datasetID)

func ValidateLocationBelongsToDataset(database *sql.DB, locationID, datasetID string) error {

func ValidateLocationBelongsToDataset(q Querier, locationID, datasetID string) error {

	err := database.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)

	err := q.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)

// DatasetExistsAndActive checks that a dataset exists and is active.
// Returns the dataset name if found.
func DatasetExistsAndActive(q Querier, datasetID string) (name string, err error) {
	var exists, active bool
	err = q.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false), COALESCE((SELECT name FROM dataset WHERE id = ?), '')",
		datasetID, datasetID, datasetID,
	).Scan(&exists, &active, &name)
	if err != nil {
		return "", fmt.Errorf("failed to verify dataset: %w", err)
	}
	if !exists {
		return "", fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
	}
	if !active {
		return "", fmt.Errorf("dataset '%s' (ID: %s) is not active", name, datasetID)
	}
	return name, nil
}
// LocationBelongsToDataset checks that a location exists, is active, and belongs to the dataset.
// Returns the location name if found.
func LocationBelongsToDataset(q Querier, locationID, datasetID string) (name string, err error) {
	var exists, active bool
	var locDatasetID string
	err = q.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), COALESCE((SELECT active FROM location WHERE id = ?), false), COALESCE((SELECT name FROM location WHERE id = ?), ''), COALESCE((SELECT dataset_id FROM location WHERE id = ?), '')",
		locationID, locationID, locationID, locationID,
	).Scan(&exists, &active, &name, &locDatasetID)
	if err != nil {
		return "", fmt.Errorf("failed to verify location: %w", err)
	}
	if !exists {
		return "", fmt.Errorf("location with ID '%s' does not exist", locationID)
	}
	if !active {
		return "", fmt.Errorf("location '%s' (ID: %s) is not active", name, locationID)
	}
	if locDatasetID != datasetID {
		return "", fmt.Errorf("location '%s' (ID: %s) does not belong to dataset ID '%s'",
			name, locationID, locDatasetID)
	}
	return name, nil
}
// ClusterBelongsToLocation checks that a cluster exists, is active, and belongs to the location.
func ClusterBelongsToLocation(q Querier, clusterID, locationID string) error {
	var exists, active bool
	var clusterLocationID string
	err := q.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ?), COALESCE((SELECT active FROM cluster WHERE id = ?), false), COALESCE((SELECT location_id FROM cluster WHERE id = ?), '')",
		clusterID, clusterID, clusterID,
	).Scan(&exists, &active, &clusterLocationID)
	if err != nil {
		return fmt.Errorf("failed to verify cluster: %w", err)
	}
	if !exists {
		return fmt.Errorf("cluster with ID '%s' does not exist", clusterID)
	}
	if !active {
		return fmt.Errorf("cluster '%s' is not active", clusterID)
	}
	if clusterLocationID != locationID {
		return fmt.Errorf("cluster '%s' does not belong to location '%s'", clusterID, locationID)
	}
	return nil
}

	"context"

}
// WithReadDB opens a read-only DB connection, calls fn, and closes the connection.
// This is a convenience wrapper that ensures the connection is always closed.
func WithReadDB(dbPath string, fn func(*sql.DB) error) error {
	database, err := OpenReadOnlyDB(dbPath)
	if err != nil {
		return fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	return fn(database)

// WithWriteTx opens a writeable DB connection, begins a logged transaction, calls fn,
// and commits on success (or rollbacks on error). The connection is always closed.
// The fn callback receives both the *sql.DB (for pre-validation queries) and the
// *LoggedTx (for mutation operations).
func WithWriteTx(ctx context.Context, dbPath, toolName string, fn func(*sql.DB, *LoggedTx) error) error {
	database, err := OpenWriteableDB(dbPath)
	if err != nil {
		return fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	tx, err := BeginLoggedTx(ctx, database, toolName)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() // no-op after commit
	if err := fn(database, tx); err != nil {
		return err
	}
	return tx.Commit()
}

# Lines of code
make count
# Go unit tests
make unit 

```

# Shell script integration tests (make sure db/test.duckdb available and FK's applied)
make shell 

# Keep cyclomatic complexity low
gocyclo -over 10 .
```

## [2026-05-05] tools/ refactoring: WithWriteTx, CallSource interface, hierarchy primitives, strconv

Four refactoring changes in tools/ and db/:
- **Added `db.WithWriteTx` and `db.WithReadDB` helpers** (db/db.go): Extracted the
  open-DB→begin-tx→defer-rollback→commit→close-DB boilerplate that was repeated across
  14+ tool entry points. `WithWriteTx(ctx, dbPath, name, fn)` opens a writeable DB, begins
  a logged transaction, calls fn, and commits on success / rollbacks on error. `WithReadDB`
  does the same for read-only connections. Applied to all create/update functions in
  cluster, dataset, location, and pattern, plus import_unstructured, import_files (validation),
  sql, and bulk_file_import (validation). Eliminates inconsistent rollback handling
  (some used `defer tx.Rollback()`, others `defer func() { if err != nil { tx.Rollback() } }()`)
  and removes ~100 lines of boilerplate.
- **Introduced `CallSource` interface** (tools/calls_from_common.go): Extracted shared
  scaffolding from `calls_from_raven.go` and `calls_from_birda.go` into a `CallSource`
  interface with `Name()`, `FindFiles()`, and `ProcessFile()` methods. Both files now
  implement the interface and delegate to `callsFromSource()`, which handles the
  sequential/parallel dispatch, DirCache management, worker pool, and result aggregation.
  Public API (`CallsFromRaven`, `CallsFromBirda`) unchanged. ~100 lines saved.
- **Extracted hierarchy validation primitives** (db/validation.go): Added `Querier`
  interface, `DatasetExistsAndActive()`, `LocationBelongsToDataset()`, and
  `ClusterBelongsToLocation()` to db/validation.go. Replaced three near-duplicate
  functions: `validateSegmentHierarchy` (import_segments.go, 14→7 cyclomatic),
  `validateHierarchyIDs` (import_files.go, 14→7 cyclomatic), and
  `verifyClusterParentRefs` plus its helpers `verifyDatasetForCluster` and
  `verifyLocationForCluster` (cluster.go, deleted ~50 lines). Also replaced inline
  dataset-exists-and-active checks in bulk_file_import.go and import_unstructured.go.
- **`fmt.Sscanf("%f", ...)→ strconv.ParseFloat` and deleted `extractFilename`**: Replaced
  7 `fmt.Sscanf` calls in calls_from_raven.go and calls_from_birda.go with
  `strconv.ParseFloat` (faster, idiomatic). Deleted `extractFilename()` from
  calls_from_preds.go — it was a one-line wrapper over `filepath.Base`.
Functions at cyclomatic >13 reduced from 9 to 6.