package imp

import (
	"database/sql"
	"slices"
	"strings"
	"testing"

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

// setupMappingTestDB creates an in-memory DB with schema + test species/calltypes.
// Species: Kiwi (sp_kiwi000000), Roroa (sp_roroa00000)
// Calltypes: Kiwi/song (ct_kiwi000001), Kiwi/duet (ct_kiwi000002), Roroa/brrr (ct_roroa00001)
func setupMappingTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("duckdb", ":memory:")
	if err != nil {
		t.Fatalf("open: %v", err)
	}

	// Create minimal tables needed by mapping validation queries
	mustExecMapping(t, db, `CREATE TABLE species (
		id VARCHAR(12) PRIMARY KEY,
		label VARCHAR(100) UNIQUE NOT NULL,
		active BOOLEAN DEFAULT TRUE
	)`)
	mustExecMapping(t, db, `CREATE TABLE call_type (
		id VARCHAR(12) PRIMARY KEY,
		species_id VARCHAR(12) NOT NULL,
		label VARCHAR(100) NOT NULL,
		active BOOLEAN DEFAULT TRUE
	)`)

	// Insert test species
	mustExecMapping(t, db, "INSERT INTO species (id, label, active) VALUES ('sp_kiwi000000', 'Kiwi', true)")
	mustExecMapping(t, db, "INSERT INTO species (id, label, active) VALUES ('sp_roroa00000', 'Roroa', true)")
	mustExecMapping(t, db, "INSERT INTO species (id, label, active) VALUES ('sp_tui0000000', 'Tui', false)") // inactive

	// Insert test calltypes
	mustExecMapping(t, db, "INSERT INTO call_type (id, species_id, label, active) VALUES ('ct_kiwi000001', 'sp_kiwi000000', 'song', true)")
	mustExecMapping(t, db, "INSERT INTO call_type (id, species_id, label, active) VALUES ('ct_kiwi000002', 'sp_kiwi000000', 'duet', true)")
	mustExecMapping(t, db, "INSERT INTO call_type (id, species_id, label, active) VALUES ('ct_roroa00001', 'sp_roroa00000', 'brrr', true)")

	return db
}

// assertStringSlice checks that got matches want (order-insensitive).
func assertStringSlice(t *testing.T, label string, got, want []string) {
	t.Helper()
	if len(want) == 0 && len(got) == 0 {
		return
	}
	if len(got) != len(want) {
		t.Errorf("%s: got %v, want %v", label, got, want)
		return
	}
	for _, w := range want {
		found := slices.Contains(got, w)
		if !found {
			t.Errorf("%s: missing %q in %v", label, w, got)
		}
	}
}

func mustExecMapping(t *testing.T, db *sql.DB, query string) {
	t.Helper()
	if _, err := db.Exec(query); err != nil {
		t.Fatalf("exec: %v", err)
	}
}

// --- collectMappedLabels ---

func TestCollectMappedLabels(t *testing.T) {
	mapping := MappingFile{
		"GSK":   {Species: "Roroa", Calltypes: map[string]string{"brrr": "brrr"}},
		"K-M":   {Species: "Kiwi"},
		"noise": {Species: MappingNegative},
	}
	dataCalltypes := map[string]map[string]bool{
		"GSK": {"brrr": true},
		"K-M": {"song": true, "duet": true},
	}

	speciesSet, calltypes := collectMappedLabels(mapping, dataCalltypes)

	if !speciesSet["Roroa"] || !speciesSet["Kiwi"] {
		t.Errorf("speciesSet=%v, want Kiwi and Roroa", speciesSet)
	}
	if speciesSet[MappingNegative] {
		t.Error("sentinel species should be excluded")
	}

	// Roroa has explicit calltype mapping
	if calltypes["Roroa"]["brrr"] != "brrr" {
		t.Errorf("Roroa calltypes=%v", calltypes["Roroa"])
	}
	// Kiwi has no calltype mapping, so data calltypes pass through
	if calltypes["Kiwi"]["song"] != "song" || calltypes["Kiwi"]["duet"] != "duet" {
		t.Errorf("Kiwi calltypes=%v", calltypes["Kiwi"])
	}
}

// --- collectUnmappedCalltypes ---

func TestCollectUnmappedCalltypes(t *testing.T) {
	mapping := MappingFile{
		"GSK": {Species: "Roroa", Calltypes: map[string]string{"Male": "brrr"}},
	}
	dataCalltypes := map[string]map[string]bool{
		"GSK": {"Male": true, "Female": true},
	}

	mappedCalltypes := make(map[string]map[string]string)
	collectUnmappedCalltypes(mapping, dataCalltypes, mappedCalltypes)

	// Male maps to brrr
	if mappedCalltypes["Roroa"]["brrr"] != "Male" {
		t.Errorf("mapped Male->brrr: %v", mappedCalltypes["Roroa"])
	}
	// Female has no mapping entry, passes through as-is
	if mappedCalltypes["Roroa"]["Female"] != "Female" {
		t.Errorf("unmapped Female passthrough: %v", mappedCalltypes["Roroa"])
	}
}

// --- validateMappedSpecies ---

func TestValidateMappedSpecies(t *testing.T) {
	db := setupMappingTestDB(t)
	defer db.Close()

	t.Run("all species exist in DB", func(t *testing.T) {
		result := &MappingValidationResult{MissingDBSpecies: make([]string, 0)}
		err := validateMappedSpecies(db, map[string]bool{"Kiwi": true, "Roroa": true}, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingDBSpecies) > 0 {
			t.Errorf("missing species: %v", result.MissingDBSpecies)
		}
	})

	t.Run("species not in DB reported", func(t *testing.T) {
		result := &MappingValidationResult{MissingDBSpecies: make([]string, 0)}
		err := validateMappedSpecies(db, map[string]bool{"Phantom": true}, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingDBSpecies) != 1 || result.MissingDBSpecies[0] != "Phantom" {
			t.Errorf("expected [Phantom], got %v", result.MissingDBSpecies)
		}
	})

	t.Run("inactive species not found", func(t *testing.T) {
		result := &MappingValidationResult{MissingDBSpecies: make([]string, 0)}
		err := validateMappedSpecies(db, map[string]bool{"Tui": true}, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingDBSpecies) != 1 {
			t.Errorf("inactive species should be missing, got %v", result.MissingDBSpecies)
		}
	})

	t.Run("empty set is no-op", func(t *testing.T) {
		result := &MappingValidationResult{MissingDBSpecies: make([]string, 0)}
		err := validateMappedSpecies(db, map[string]bool{}, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingDBSpecies) != 0 {
			t.Errorf("expected no missing, got %v", result.MissingDBSpecies)
		}
	})
}

// --- validateMappedCalltypes ---

func TestValidateMappedCalltypes(t *testing.T) {
	db := setupMappingTestDB(t)
	defer db.Close()

	t.Run("all calltypes exist", func(t *testing.T) {
		result := &MappingValidationResult{MissingCalltypes: make(map[string]string)}
		ctMap := map[string]map[string]string{
			"Kiwi": {"song": "data-song", "duet": "data-duet"},
		}
		err := validateMappedCalltypes(db, ctMap, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingCalltypes) > 0 {
			t.Errorf("missing calltypes: %v", result.MissingCalltypes)
		}
	})

	t.Run("missing calltype reported", func(t *testing.T) {
		result := &MappingValidationResult{MissingCalltypes: make(map[string]string)}
		ctMap := map[string]map[string]string{
			"Kiwi": {"phantom": "data-phantom"},
		}
		err := validateMappedCalltypes(db, ctMap, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingCalltypes) != 1 {
			t.Errorf("expected 1 missing, got %v", result.MissingCalltypes)
		}
	})

	t.Run("empty calltype map skips species", func(t *testing.T) {
		result := &MappingValidationResult{MissingCalltypes: make(map[string]string)}
		ctMap := map[string]map[string]string{
			"Kiwi": {},
		}
		err := validateMappedCalltypes(db, ctMap, result)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if len(result.MissingCalltypes) != 0 {
			t.Errorf("expected none missing, got %v", result.MissingCalltypes)
		}
	})
}

// --- ValidateMappingAgainstDB (integration of all above) ---

func TestValidateMappingAgainstDB(t *testing.T) {
	db := setupMappingTestDB(t)
	defer db.Close()

	tests := []struct {
		name              string
		mapping           MappingFile
		dataSpecies       map[string]bool
		dataCT            map[string]map[string]bool
		hasErrors         bool
		missingSpecies    []string
		missingDBSpecies  []string
		missingCalltypeCT string // substring expected in MissingCalltypes key
		errorContains     string // substring expected in result.Error()
	}{
		{
			name: "valid mapping - no errors",
			mapping: MappingFile{
				"GSK": {Species: "Roroa", Calltypes: map[string]string{"brrr": "brrr"}},
				"K-M": {Species: "Kiwi"},
			},
			dataSpecies: map[string]bool{"GSK": true, "K-M": true},
			dataCT:      map[string]map[string]bool{"GSK": {"brrr": true}, "K-M": {"song": true}},
		},
		{
			name:           "missing species in mapping",
			mapping:        MappingFile{"GSK": {Species: "Roroa"}},
			dataSpecies:    map[string]bool{"GSK": true, "K-M": true},
			hasErrors:      true,
			missingSpecies: []string{"K-M"},
		},
		{
			name:             "mapped species not in DB",
			mapping:          MappingFile{"PHANTOM": {Species: "Phantom"}},
			dataSpecies:      map[string]bool{"PHANTOM": true},
			hasErrors:        true,
			missingDBSpecies: []string{"Phantom"},
		},
		{
			name:        "sentinel species excluded from DB check",
			mapping:     MappingFile{"noise": {Species: MappingNegative}, "ignore": {Species: MappingIgnore}},
			dataSpecies: map[string]bool{"noise": true, "ignore": true},
		},
		{
			name: "missing calltype in DB",
			mapping: MappingFile{
				"K-M": {Species: "Kiwi", Calltypes: map[string]string{"song": "song", "phantom": "phantom"}},
			},
			dataSpecies:       map[string]bool{"K-M": true},
			dataCT:            map[string]map[string]bool{"K-M": {"song": true, "phantom": true}},
			hasErrors:         true,
			missingCalltypeCT: "phantom",
			errorContains:     "phantom",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result, err := ValidateMappingAgainstDB(db, tt.mapping, tt.dataSpecies, tt.dataCT)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if result.HasErrors() != tt.hasErrors {
				t.Errorf("HasErrors()=%v, want %v", result.HasErrors(), tt.hasErrors)
			}
			assertStringSlice(t, "MissingSpecies", result.MissingSpecies, tt.missingSpecies)
			assertStringSlice(t, "MissingDBSpecies", result.MissingDBSpecies, tt.missingDBSpecies)
			if tt.missingCalltypeCT != "" && len(result.MissingCalltypes) == 0 {
				t.Error("expected missing calltype")
			}
			if tt.errorContains != "" && !strings.Contains(result.Error(), tt.errorContains) {
				t.Errorf("error should contain %q: %s", tt.errorContains, result.Error())
			}
		})
	}
}