package utils

import (
	"database/sql"
	"testing"

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

func TestCheckDuplicateHash_NoRows(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	// No rows exist — should return not-duplicate
	id, dup, err := CheckDuplicateHash(db, "abcdef0123456789")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if dup {
		t.Error("expected isDuplicate=false when no rows")
	}
	if id != "" {
		t.Errorf("expected empty id, got %q", id)
	}
}

func TestCheckDuplicateHash_FoundDuplicate(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	// Insert a file with known hash
	hash := "deadbeef12345678"
	fileID := "test_file_id_123"
	_, err := db.Exec(`INSERT INTO file (id, path, dataset_id, xxh64_hash, active)
		VALUES (?, '/test/file.wav', 'ds1', ?, true)`, fileID, hash)
	if err != nil {
		t.Fatalf("insert: %v", err)
	}

	id, dup, err := CheckDuplicateHash(db, hash)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if !dup {
		t.Error("expected isDuplicate=true")
	}
	if id != fileID {
		t.Errorf("expected id=%q, got %q", fileID, id)
	}
}

func TestCheckDuplicateHash_InactiveNotDuplicate(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	// Insert an INACTIVE file with known hash
	hash := "cafebeef12345678"
	fileID := "inactive_file_id"
	_, err := db.Exec(`INSERT INTO file (id, path, dataset_id, xxh64_hash, active)
		VALUES (?, '/test/old.wav', 'ds1', ?, false)`, fileID, hash)
	if err != nil {
		t.Fatalf("insert: %v", err)
	}

	// Inactive files should NOT be considered duplicates
	id, dup, err := CheckDuplicateHash(db, hash)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if dup {
		t.Error("expected isDuplicate=false for inactive file")
	}
	if id != "" {
		t.Errorf("expected empty id, got %q", id)
	}
}

func TestCheckDuplicateHash_DifferentHashNoDuplicate(t *testing.T) {
	db := openTestDB(t)
	defer db.Close()

	// Insert file with hash A
	_, err := db.Exec(`INSERT INTO file (id, path, dataset_id, xxh64_hash, active)
		VALUES ('id1', '/test/a.wav', 'ds1', 'hash_aaaa', true)`)
	if err != nil {
		t.Fatalf("insert: %v", err)
	}

	// Query for hash B — no duplicate
	id, dup, err := CheckDuplicateHash(db, "hash_bbbb")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if dup {
		t.Error("expected isDuplicate=false for different hash")
	}
	if id != "" {
		t.Errorf("expected empty id, got %q", id)
	}
}

// openTestDB creates a DuckDB in-memory database with the minimal schema
// needed for the file table.
func openTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("duckdb", "")
	if err != nil {
		t.Fatalf("open duckdb: %v", err)
	}

	_, err = db.Exec(`
		CREATE TABLE file (
			id VARCHAR PRIMARY KEY,
			path VARCHAR,
			dataset_id VARCHAR,
			xxh64_hash VARCHAR,
			active BOOLEAN DEFAULT true
		)
	`)
	if err != nil {
		db.Close()
		t.Fatalf("create table: %v", err)
	}

	return db
}