package utils
import "strings"
// Placeholders generates SQL placeholder string for IN clauses (e.g. "?, ?, ?")
func Placeholders(n int) string {
	if n == 0 {
		return ""
	}
	ph := make([]string, n)
	for i := range ph {
		ph[i] = "?"
	}
	return strings.Join(ph, ", ")
}

	"skraak/db"

	query := `SELECT label FROM species WHERE label IN (` + db.Placeholders(len(speciesLabels)) + `) AND active = true`

	query := `SELECT label FROM species WHERE label IN (` + Placeholders(len(speciesLabels)) + `) AND active = true`

			WHERE s.label = ? AND ct.label IN (` + db.Placeholders(len(ctLabels)) + `) AND ct.active = true`

			WHERE s.label = ? AND ct.label IN (` + Placeholders(len(ctLabels)) + `) AND ct.active = true`

	Exec(query string, args ...any) (sql.Result, error)

package utils
import (
	"go/parser"
	"go/token"
	"os/exec"
	"strings"
	"testing"
)
// TestPackageDependencies enforces the project's package dependency rules
// defined in CLAUDE.md. Packages may only import packages below them:
//
//	cmd    → tools, tui, utils, db
//	tools  → utils, db
//	tui    → tools, utils
//	utils  → (nothing — leaf package)
//	db     → utils
//
// Note: This test lives in utils/ only because it needs to be in some
// package. It validates rules for ALL project packages.
func TestPackageDependencies(t *testing.T) {
	rules := map[string]map[string]bool{
		// pkg → set of allowed skraak-internal imports
		"skraak/cmd":   {"skraak/tools": true, "skraak/tui": true, "skraak/utils": true, "skraak/db": true},
		"skraak/tools": {"skraak/utils": true, "skraak/db": true},
		"skraak/tui":   {"skraak/tools": true, "skraak/utils": true},
		"skraak/utils": {}, // leaf package — no skraak imports allowed
		"skraak/db":    {"skraak/utils": true},
	}
	for pkg, allowed := range rules {
		imports := getInternalImports(t, pkg)
		for _, imp := range imports {
			if !allowed[imp] {
				t.Errorf("%s imports %s — not allowed by dependency rules", pkg, imp)
			}
		}
	}
}
// TestNoDirectDBImportInUtils checks that no file in utils/ imports skraak/db.
// This is a fast source-level check that doesn't require `go list`.
func TestNoDirectDBImportInUtils(t *testing.T) {
	fset := token.NewFileSet()
	pkgs, err := parser.ParseDir(fset, ".", nil, parser.ImportsOnly)
	if err != nil {
		t.Fatalf("parse utils/: %v", err)
	}
	for _, pkg := range pkgs {
		for filename, file := range pkg.Files {
			for _, imp := range file.Imports {
				path := strings.Trim(imp.Path.Value, `"`)
				if path == "skraak/db" {
					t.Errorf("%s: forbidden import of skraak/db (utils is the leaf package)", filename)
				}
			}
		}
	}
}
// getInternalImports returns the skraak-internal imports for a package using `go list`.
func getInternalImports(t *testing.T, pkg string) []string {
	t.Helper()
	out, err := exec.Command("go", "list", "-f", "{{range .Imports}}{{.}}\n{{end}}", pkg).Output()
	if err != nil {
		t.Fatalf("go list %s: %v", pkg, err)
	}
	var internal []string
	for line := range strings.SplitSeq(string(out), "\n") {
		line = strings.TrimSpace(line)
		if strings.HasPrefix(line, "skraak/") || line == "skraak" {
			internal = append(internal, line)
		}
	}
	return internal
}

	"skraak/db"

// ImportCluster imports all WAV files from a folder into a cluster

// ImportCluster imports all WAV files from a folder into a cluster.
// The caller must provide an open transaction via tx; this function does NOT
// commit or rollback — the caller owns the transaction lifecycle.

//  5. Batch insert in single transaction:

//  5. Batch insert using the provided transaction:

//     - All-or-nothing commit

//
// Transaction behavior: ALL files succeed or ALL rollback
// This preserves cluster integrity (cluster = complete recording session)

	database *sql.DB,

	database DB,
	tx *sql.Tx,

	// Batch insert into database

	// Batch insert into database using the provided transaction

		database,

		tx,

func GetLocationData(database *sql.DB, locationID string) (*LocationData, error) {

func GetLocationData(database DB, locationID string) (*LocationData, error) {

func EnsureClusterPath(database *sql.DB, clusterID, folderPath string) error {

func EnsureClusterPath(database DB, clusterID, folderPath string) error {

	tx *db.LoggedTx,

	tx *sql.Tx,

	fileStmt, datasetStmt, mothStmt *db.LoggedStmt,

	fileStmt, datasetStmt, mothStmt *sql.Stmt,

	fileStmt    *db.LoggedStmt
	datasetStmt *db.LoggedStmt
	mothStmt    *db.LoggedStmt

	fileStmt    *sql.Stmt
	datasetStmt *sql.Stmt
	mothStmt    *sql.Stmt

func prepareClusterStmts(ctx context.Context, tx *db.LoggedTx) (*clusterStmts, error) {

func prepareClusterStmts(ctx context.Context, tx *sql.Tx) (*clusterStmts, error) {

// insertClusterFiles inserts all file data into database in a single transaction

// insertClusterFiles inserts all file data into database using the provided transaction.
// The caller is responsible for committing or rolling back the transaction.

	database *sql.DB,

	tx *sql.Tx,

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

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

	"skraak/db"

	if result.Gain != db.GainMedium {
		t.Errorf("Gain incorrect: got %s, want %s", result.Gain, db.GainMedium)

	if result.Gain != GainMedium {
		t.Errorf("Gain incorrect: got %s, want %s", result.Gain, GainMedium)

	if result.Gain != db.GainHigh {
		t.Errorf("Gain incorrect: got %s, want %s", result.Gain, db.GainHigh)

	if result.Gain != GainHigh {
		t.Errorf("Gain incorrect: got %s, want %s", result.Gain, GainHigh)

		expected db.GainLevel

		expected GainLevel

		{"low", db.GainLow},
		{"low-medium", db.GainLowMedium},
		{"medium", db.GainMedium},
		{"medium-high", db.GainMediumHigh},
		{"high", db.GainHigh},

		{"low", GainLow},
		{"low-medium", GainLowMedium},
		{"medium", GainMedium},
		{"medium-high", GainMediumHigh},
		{"high", GainHigh},

		expected db.GainLevel

		expected GainLevel

		{"low", db.GainLow, false},
		{"LOW", db.GainLow, false},
		{"  low  ", db.GainLow, false},
		{"low-medium", db.GainLowMedium, false},
		{"medium", db.GainMedium, false},
		{"medium-high", db.GainMediumHigh, false},
		{"high", db.GainHigh, false},

		{"low", GainLow, false},
		{"LOW", GainLow, false},
		{"  low  ", GainLow, false},
		{"low-medium", GainLowMedium, false},
		{"medium", GainMedium, false},
		{"medium-high", GainMediumHigh, false},
		{"high", GainHigh, false},

			if result.Gain != db.GainMedium {
				t.Errorf("Gain should be normalized: got %s, want %s", result.Gain, db.GainMedium)

			if result.Gain != GainMedium {
				t.Errorf("Gain should be normalized: got %s, want %s", result.Gain, GainMedium)

)
// GainLevel represents the gain_level enum for AudioMoth recordings
type GainLevel string

	"skraak/db"

// AudioMoth gain level enum constants
const (
	GainLow        GainLevel = "low"
	GainLowMedium  GainLevel = "low-medium"
	GainMedium     GainLevel = "medium"
	GainMediumHigh GainLevel = "medium-high"
	GainHigh       GainLevel = "high"

	Gain       db.GainLevel

	Gain       GainLevel

func parseGainLevel(gainStr string) (db.GainLevel, error) {

func parseGainLevel(gainStr string) (GainLevel, error) {

		return db.GainLow, nil

		return GainLow, nil

		return db.GainLowMedium, nil

		return GainLowMedium, nil

		return db.GainMedium, nil

		return GainMedium, nil

		return db.GainMediumHigh, nil

		return GainMediumHigh, nil

		return db.GainHigh, nil

		return GainHigh, nil

	clusterOutput, err := utils.ImportCluster(database, utils.ClusterImportInput{

	tx, err := db.BeginLoggedTx(ctx, database, "import_audio_files")
	if err != nil {
		return output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	clusterOutput, err := utils.ImportCluster(database, tx.UnderlyingTx(), utils.ClusterImportInput{

		tx.Rollback()

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

	clusterOutput, err := utils.ImportCluster(database, utils.ClusterImportInput{

	ctx := context.Background()
	tx, err := db.BeginLoggedTx(ctx, database, "import_audio_files")
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	clusterOutput, err := utils.ImportCluster(database, tx.UnderlyingTx(), utils.ClusterImportInput{

		tx.Rollback()

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

import "strings"

import (
	"skraak/utils"
)

// Placeholders generates SQL placeholder string for IN clauses

// Placeholders generates SQL placeholder string for IN clauses.
// Delegates to utils.Placeholders.

	if n == 0 {
		return ""
	}
	ph := make([]string, n)
	for i := range ph {
		ph[i] = "?"
	}
	return strings.Join(ph, ", ")

	return utils.Placeholders(n)

	"skraak/utils"

// GainLevel represents the gain_level enum for AudioMoth recordings
type GainLevel string

// GainLevel is re-exported from utils for backward compatibility.
type GainLevel = utils.GainLevel

// AudioMoth gain level enum constants

// Gain level constants re-exported from utils.

	GainLow        GainLevel = "low"
	GainLowMedium  GainLevel = "low-medium"
	GainMedium     GainLevel = "medium"
	GainMediumHigh GainLevel = "medium-high"
	GainHigh       GainLevel = "high"

	GainLow        = utils.GainLow
	GainLowMedium  = utils.GainLowMedium
	GainMedium     = utils.GainMedium
	GainMediumHigh = utils.GainMediumHigh
	GainHigh       = utils.GainHigh

// UnderlyingTx returns the underlying *sql.Tx for use by packages that
// should not import db (e.g. utils). Prefer using LoggedTx methods directly
// when event logging is desired.
func (l *LoggedTx) UnderlyingTx() *sql.Tx {
	return l.tx
}

**Simple rule:** If called by `cmd/`, it goes in `tools/`. If called by `tools/`, it goes in `utils/`.

**Dependency rule:** packages may only import packages below them in this list:

- **`utils/`** - Reusable helpers (no MCP types, no `*Input`/`*Output` structs)

- **`cmd/*.go`** - CLI commands (parse flags, call tools, print JSON)

- **`cmd/*.go`** - CLI commands (parse flags, call tools, print JSON)

- **`tui/`** - TUI (interactive classify UI)
- **`utils/`** - Reusable helpers (no MCP types, no `*Input`/`*Output` structs, no `db` import)
- **`db/`** - Database connection, types, transactions (may import `utils/`)
`utils/` is the leaf package — it must not import `cmd/`, `tools/`, `tui/`, or `db/`.

utils/*.go       → Reusable helpers
db/              → Database connection + types

tui/             → Interactive classify UI
db/              → Database connection + types + transactions
utils/*.go       → Reusable helpers (leaf package, no db import)

## [2026-05-07] Remove utils → db package dependency
Three changes to eliminate the `utils → db` import edge, restoring clean layering
(`cmd → tools → utils` and `cmd → db`, with no upward dependency from utils):
- **Moved `Placeholders()` from `db/` to `utils/`**: It's a pure string function
  (generates `?, ?, ?` for SQL IN clauses) with no database dependency. `db.Placeholders`
  now delegates to `utils.Placeholders` for backward compatibility.

- **Moved `GainLevel` type and constants from `db/` to `utils/`**: It's a string enum
  that naturally belongs next to `AudioMothData` in the audiomoth parser. `db.GainLevel`
  is now a type alias (`type GainLevel = utils.GainLevel`) and the constants are
  re-exported for backward compatibility.
- **Refactored `ImportCluster` to accept `*sql.Tx` instead of managing its own
  transaction**: The caller (tools/) now opens the `LoggedTx`, passes the underlying
  `*sql.Tx` via `UnderlyingTx()`, and manages commit/rollback. This removes
  `db.BeginLoggedTx`, `db.LoggedTx`, and `db.LoggedStmt` from utils entirely.
  Added `UnderlyingTx()` method to `db.LoggedTx` to expose the raw `*sql.Tx`.
- **Extended `DB` interface** with `Exec()`: `EnsureClusterPath` needed `Exec` which
  was missing from the original `DB` interface. Both `*sql.DB` and `*sql.Tx` satisfy
  the updated interface.
Result: `utils/` no longer imports `skraak/db`. The dependency now flows correctly:
`db → utils` (for Placeholders and GainLevel re-exports).