package db

import (
	"database/sql"
	"testing"

	_ "github.com/duckdb/duckdb-go/v2"
)

// SetupTestDB creates an in-memory DuckDB with the full schema applied.
// The caller must defer db.Close().
func SetupTestDB(t *testing.T) *sql.DB {
	t.Helper()
	database, err := sql.Open("duckdb", ":memory:")
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}
	schema, err := ReadSchemaSQL()
	if err != nil {
		database.Close()
		t.Fatalf("failed to read schema: %v", err)
	}
	if _, err = database.Exec(schema); err != nil {
		database.Close()
		t.Fatalf("failed to create schema: %v", err)
	}
	return database
}

// InsertTestDataset inserts a structured dataset row and returns its ID.
func InsertTestDataset(t *testing.T, database *sql.DB, id, name string) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO dataset (id, name, type, active) VALUES (?, ?, 'structured', true)",
		id, name,
	)
	if err != nil {
		t.Fatalf("failed to insert dataset: %v", err)
	}
}

// InsertTestDatasetWithType inserts a dataset with the given type.
func InsertTestDatasetWithType(t *testing.T, database *sql.DB, id, name, datasetType string) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO dataset (id, name, type, active) VALUES (?, ?, ?, true)",
		id, name, datasetType,
	)
	if err != nil {
		t.Fatalf("failed to insert dataset: %v", err)
	}
}

// InsertTestLocation inserts a location row.
func InsertTestLocation(t *testing.T, database *sql.DB, id, datasetID, name string) {
	t.Helper()
	_, err := database.Exec(
		`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active)
		 VALUES (?, ?, ?, -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		id, datasetID, name,
	)
	if err != nil {
		t.Fatalf("failed to insert location: %v", err)
	}
}

// InsertTestCluster inserts a cluster row.
func InsertTestCluster(t *testing.T, database *sql.DB, id, datasetID, locationID, name string) {
	t.Helper()
	_, err := database.Exec(
		`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active)
		 VALUES (?, ?, ?, ?, 48000, true)`,
		id, datasetID, locationID, name,
	)
	if err != nil {
		t.Fatalf("failed to insert cluster: %v", err)
	}
}

// InsertTestFile inserts a file row.
func InsertTestFile(t *testing.T, database *sql.DB, id, hash, locationID string) {
	t.Helper()
	_, err := database.Exec(
		`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active)
		 VALUES (?, 'test.wav', ?, ?, CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		id, hash, locationID,
	)
	if err != nil {
		t.Fatalf("failed to insert file: %v", err)
	}
}

// InsertTestSpecies inserts a species row.
func InsertTestSpecies(t *testing.T, database *sql.DB, id, label string) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO species (id, label, active) VALUES (?, ?, true)",
		id, label,
	)
	if err != nil {
		t.Fatalf("failed to insert species: %v", err)
	}
}

// InsertTestCallType inserts a call_type row.
func InsertTestCallType(t *testing.T, database *sql.DB, id, speciesID, label string) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO call_type (id, species_id, label, active) VALUES (?, ?, ?, true)",
		id, speciesID, label,
	)
	if err != nil {
		t.Fatalf("failed to insert call_type: %v", err)
	}
}

// InsertTestPattern inserts a cyclic_recording_pattern row.
func InsertTestPattern(t *testing.T, database *sql.DB, id string, recordS, sleepS int) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, active) VALUES (?, ?, ?, true)",
		id, recordS, sleepS,
	)
	if err != nil {
		t.Fatalf("failed to insert pattern: %v", err)
	}
}

// InsertTestFilter inserts a filter row.
func InsertTestFilter(t *testing.T, database *sql.DB, id, name string) {
	t.Helper()
	_, err := database.Exec(
		"INSERT INTO filter (id, name, active) VALUES (?, ?, true)",
		id, name,
	)
	if err != nil {
		t.Fatalf("failed to insert filter: %v", err)
	}
}

// InsertTestFileForCluster inserts a file row linked to a cluster.
func InsertTestFileForCluster(t *testing.T, database *sql.DB, id, clusterID, locationID, fileName, hash string, duration float64) {
	t.Helper()
	_, err := database.Exec(
		`INSERT INTO file (id, file_name, xxh64_hash, location_id, cluster_id, timestamp_local, duration, sample_rate, active)
		 VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, 48000, true)`,
		id, fileName, hash, locationID, clusterID, duration,
	)
	if err != nil {
		t.Fatalf("failed to insert file: %v", err)
	}
}