package imp

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"testing"

	"skraak/datafile"
	"skraak/db"
	"skraak/utils"
)

// setupImportTestDB creates an in-memory DuckDB with the full schema and test data.
//
// Test data:
//   - Structured dataset:  dstest000001
//   - Unstructured dataset: dstest000002
//   - Location (active):    loctest00001 (in dstest000001)
//   - Location (inactive):  loctest00002 (in dstest000001)
//   - Cluster (active):     cltest000001 (in loctest00001)
//   - Cluster (inactive):   cltest000002 (in loctest00001)
//   - Species: Kiwi (sptest000001), Roroa (sptest000002)
//   - Calltypes: Kiwi/song (cttest000001), Kiwi/duet (cttest000002)
//   - Filters: kiwi.txt (fitest000001), test.txt (fitest000002)
func setupImportTestDB(t *testing.T) *sql.DB {
	t.Helper()
	database := db.SetupTestDB(t)

	// Datasets
	db.InsertTestDatasetWithType(t, database, "dstest000001", "Test Structured", "structured")
	db.InsertTestDatasetWithType(t, database, "dstest000002", "Test Unstructured", "unstructured")

	// Locations
	db.InsertTestLocation(t, database, "loctest00001", "dstest000001", "Test Location Active")
	db.InsertTestLocation(t, database, "loctest00002", "dstest000001", "Test Location Inactive")
	mustExec(t, database, "UPDATE location SET active = false WHERE id = 'loctest00002'")

	// Clusters
	db.InsertTestCluster(t, database, "cltest000001", "dstest000001", "loctest00001", "Test Cluster Active")
	db.InsertTestCluster(t, database, "cltest000002", "dstest000001", "loctest00001", "Test Cluster Inactive")
	mustExec(t, database, "UPDATE cluster SET active = false WHERE id = 'cltest000002'")

	// Species
	db.InsertTestSpecies(t, database, "sptest000001", "Kiwi")
	db.InsertTestSpecies(t, database, "sptest000002", "Roroa")

	// Calltypes
	db.InsertTestCallType(t, database, "cttest000001", "sptest000001", "song")
	db.InsertTestCallType(t, database, "cttest000002", "sptest000001", "duet")

	// Filters
	db.InsertTestFilter(t, database, "fitest000001", "kiwi.txt")
	db.InsertTestFilter(t, database, "fitest000002", "test.txt")

	return database
}

// setupFileBasedTestDB creates a file-based DuckDB for tests that need to
// open multiple connections to the same database (e.g., ImportAudioFiles).
// Returns the path to the database file. The database is closed after setup.
func setupFileBasedTestDB(t *testing.T) string {
	t.Helper()

	// Create temp file for database
	tmpDir := t.TempDir()
	dbPath := filepath.Join(tmpDir, "test.duckdb")

	// Open database
	database, err := sql.Open("duckdb", dbPath)
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	// Apply schema
	schema, err := db.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)
	}

	// Insert test data - same as setupImportTestDB
	db.InsertTestDatasetWithType(t, database, "dstest000001", "Test Structured", "structured")
	db.InsertTestDatasetWithType(t, database, "dstest000002", "Test Unstructured", "unstructured")

	db.InsertTestLocation(t, database, "loctest00001", "dstest000001", "Test Location Active")
	db.InsertTestLocation(t, database, "loctest00002", "dstest000001", "Test Location Inactive")
	mustExec(t, database, "UPDATE location SET active = false WHERE id = 'loctest00002'")

	db.InsertTestCluster(t, database, "cltest000001", "dstest000001", "loctest00001", "Test Cluster Active")
	db.InsertTestCluster(t, database, "cltest000002", "dstest000001", "loctest00001", "Test Cluster Inactive")
	mustExec(t, database, "UPDATE cluster SET active = false WHERE id = 'cltest000002'")

	db.InsertTestSpecies(t, database, "sptest000001", "Kiwi")
	db.InsertTestSpecies(t, database, "sptest000002", "Roroa")

	db.InsertTestCallType(t, database, "cttest000001", "sptest000001", "song")
	db.InsertTestCallType(t, database, "cttest000002", "sptest000001", "duet")

	db.InsertTestFilter(t, database, "fitest000001", "kiwi.txt")
	db.InsertTestFilter(t, database, "fitest000002", "test.txt")

	// Close the database so tests can open their own connections
	database.Close()

	return dbPath
}

// mustExec executes a SQL statement, failing the test on error.
func mustExec(t *testing.T, database *sql.DB, query string, args ...any) {
	t.Helper()
	if _, err := database.Exec(query, args...); err != nil {
		t.Fatalf("exec: %v", err)
	}
}

// createTestWAV creates a minimal valid WAV file at the given path.
// Returns the XXH64 hash of the file.
func createTestWAV(t *testing.T, path string) string {
	t.Helper()

	// Create a 1-second WAV file at 16kHz mono 16-bit
	// 44-byte header + 32000 bytes of data (16000 samples * 2 bytes)
	const sampleRate = 16000
	const numSamples = sampleRate   // 1 second
	const dataSize = numSamples * 2 // 2 bytes per sample
	const fileSize = 44 + dataSize - 8

	data := make([]byte, 44+dataSize)

	// RIFF header
	copy(data[0:4], "RIFF")
	data[4] = byte(fileSize & 0xFF)
	data[5] = byte((fileSize >> 8) & 0xFF)
	data[6] = byte((fileSize >> 16) & 0xFF)
	data[7] = byte((fileSize >> 24) & 0xFF)
	copy(data[8:12], "WAVE")

	// fmt chunk
	copy(data[12:16], "fmt ")
	data[16] = 16 // fmt chunk size
	data[20] = 1  // PCM format
	data[22] = 1  // mono
	data[24] = byte(sampleRate & 0xFF)
	data[25] = byte((sampleRate >> 8) & 0xFF)
	const byteRate = sampleRate * 2
	data[28] = byte(byteRate & 0xFF)
	data[29] = byte((byteRate >> 8) & 0xFF)
	data[32] = 2  // block align
	data[34] = 16 // bits per sample

	// data chunk
	copy(data[36:40], "data")
	data[40] = byte(dataSize & 0xFF)
	data[41] = byte((dataSize >> 8) & 0xFF)
	data[42] = byte((dataSize >> 16) & 0xFF)
	data[43] = byte((dataSize >> 24) & 0xFF)

	// Audio data is already zeros

	if err := os.WriteFile(path, data, 0644); err != nil {
		t.Fatalf("failed to create test WAV: %v", err)
	}

	hash, err := utils.ComputeXXH64(path)
	if err != nil {
		t.Fatalf("failed to compute hash: %v", err)
	}

	return hash
}

// createTestDataFile creates a minimal .data file with the given segments.
func createTestDataFile(t *testing.T, wavPath string, segments []*datafile.Segment) string {
	t.Helper()

	dataPath := wavPath + ".data"

	df := &datafile.DataFile{
		Meta: &datafile.DataMeta{
			Operator: "test",
			Duration: 0.0005,
		},
		Segments: segments,
	}

	if err := df.Write(dataPath); err != nil {
		t.Fatalf("failed to write test .data file: %v", err)
	}

	return dataPath
}

// createTestMappingFile creates a minimal mapping.json file.
func createTestMappingFile(t *testing.T, dir string) string {
	t.Helper()

	mapping := map[string]any{
		"Kiwi": map[string]any{
			"species": "Kiwi",
			"calltypes": map[string]string{
				"song": "song",
				"duet": "duet",
			},
		},
		"Roroa": map[string]any{
			"species": "Roroa",
		},
	}

	data, err := json.Marshal(mapping)
	if err != nil {
		t.Fatalf("failed to marshal mapping: %v", err)
	}

	path := filepath.Join(dir, "mapping.json")
	if err := os.WriteFile(path, data, 0644); err != nil {
		t.Fatalf("failed to write mapping file: %v", err)
	}

	return path
}

// createTestCSVFile creates a CSV file for bulk import testing.
// Columns: location_name, location_id, directory_path, date_range, sample_rate, file_count
func createTestCSVFile(t *testing.T, dir string, rows [][]string) string {
	t.Helper()

	path := filepath.Join(dir, "import.csv")
	file, err := os.Create(path)
	if err != nil {
		t.Fatalf("failed to create CSV: %v", err)
	}
	defer func() { _ = file.Close() }()

	// Write header
	if _, err := file.WriteString("location_name,location_id,directory_path,date_range,sample_rate,file_count\n"); err != nil {
		t.Fatalf("failed to write CSV header: %v", err)
	}

	// Write rows
	for _, row := range rows {
		line := fmt.Sprintf("%s,%s,%s,%s,%s,%s\n", row[0], row[1], row[2], row[3], row[4], row[5])
		if _, err := file.WriteString(line); err != nil {
			t.Fatalf("failed to write CSV row: %v", err)
		}
	}

	return path
}

// createTestLogFile creates a log file path for bulk import testing.
func createTestLogFile(t *testing.T, dir string) string {
	t.Helper()
	path := filepath.Join(dir, "import.log")
	// Create empty file
	if err := os.WriteFile(path, []byte{}, 0644); err != nil {
		t.Fatalf("failed to create log file: %v", err)
	}
	return path
}

// beginTestTx begins a logged transaction for testing.
func beginTestTx(t *testing.T, ctx context.Context, database *sql.DB) *db.LoggedTx {
	t.Helper()
	tx, err := db.BeginLoggedTx(ctx, database, "test")
	if err != nil {
		t.Fatalf("failed to begin transaction: %v", err)
	}
	return tx
}