package tools

import (
	"context"
	"os"
	"testing"

	"skraak/db"
)

// setupTestDB creates a temporary database with schema for testing
func setupTestDB(t *testing.T) (string, func()) {
	t.Helper()

	// Create temp file path (but don't create the file - DuckDB will create it)
	tmpFile, err := os.CreateTemp("", "skraak_update_test_*.duckdb")
	if err != nil {
		t.Fatalf("Failed to create temp file: %v", err)
	}
	tmpPath := tmpFile.Name()
	tmpFile.Close()
	os.Remove(tmpPath) // Remove the empty file so DuckDB can create it fresh

	// Open database and run schema
	database, err := db.OpenWriteableDB(tmpPath)
	if err != nil {
		t.Fatalf("Failed to open database: %v", err)
	}

	// Read and execute schema
	schema, err := db.ReadSchemaSQL()
	if err != nil {
		database.Close()
		os.Remove(tmpPath)
		t.Fatalf("Failed to read schema: %v", err)
	}

	statements := db.ExtractDDLStatements(schema)
	for _, stmt := range statements {
		// Skip CREATE TABLE AS (ebird_taxonomy_v2024 was removed)
		if stmt.Type == "CREATE_TABLE_AS" {
			continue
		}
		_, err := database.Exec(stmt.SQL)
		if err != nil {
			database.Close()
			os.Remove(tmpPath)
			t.Fatalf("Failed to execute DDL: %v\nSQL: %s", err, stmt.SQL)
		}
	}

	database.Close()

	cleanup := func() {
		os.Remove(tmpPath)
	}

	return tmpPath, cleanup
}

// TestDatasetUpdatePreservesUnsetFields tests that update only modifies provided fields
func TestDatasetUpdatePreservesUnsetFields(t *testing.T) {
	dbPath, cleanup := setupTestDB(t)
	defer cleanup()

	SetDBPath(dbPath)

	// Create a dataset with all fields
	name := "Test Dataset"
	dsType := "train"
	description := "Original description"
	createInput := DatasetInput{
		Name:        &name,
		Type:        &dsType,
		Description: &description,
	}

	ctx := context.Background()
	created, err := CreateOrUpdateDataset(ctx, createInput)
	if err != nil {
		t.Fatalf("Failed to create dataset: %v", err)
	}

	// Verify initial values
	if created.Dataset.Name != "Test Dataset" {
		t.Errorf("Expected name 'Test Dataset', got '%s'", created.Dataset.Name)
	}
	if created.Dataset.Type != "train" {
		t.Errorf("Expected type 'train', got '%s'", created.Dataset.Type)
	}
	if created.Dataset.Description == nil || *created.Dataset.Description != "Original description" {
		t.Errorf("Expected description 'Original description', got '%v'", created.Dataset.Description)
	}

	// Update only the description (nil for other fields)
	newDesc := "Updated description only"
	updateInput := DatasetInput{
		ID:          &created.Dataset.ID,
		Description: &newDesc,
		// Name and Type are nil - should be preserved
	}

	updated, err := CreateOrUpdateDataset(ctx, updateInput)
	if err != nil {
		t.Fatalf("Failed to update dataset: %v", err)
	}

	// Verify only description changed
	if updated.Dataset.Name != "Test Dataset" {
		t.Errorf("Name should be preserved, got '%s'", updated.Dataset.Name)
	}
	if updated.Dataset.Type != "train" {
		t.Errorf("Type should be preserved, got '%s'", updated.Dataset.Type)
	}
	if updated.Dataset.Description == nil || *updated.Dataset.Description != "Updated description only" {
		t.Errorf("Description should be updated, got '%v'", updated.Dataset.Description)
	}
}

// TestLocationUpdatePreservesUnsetFields tests that update only modifies provided fields
func TestLocationUpdatePreservesUnsetFields(t *testing.T) {
	dbPath, cleanup := setupTestDB(t)
	defer cleanup()

	SetDBPath(dbPath)

	// Create a dataset first
	dsName := "Test Dataset"
	dsCreated, err := CreateOrUpdateDataset(context.Background(), DatasetInput{Name: &dsName})
	if err != nil {
		t.Fatalf("Failed to create dataset: %v", err)
	}

	// Create a location with all fields
	name := "Test Location"
	lat := -36.85
	lon := 174.76
	tz := "Pacific/Auckland"
	description := "Original description"
	createInput := LocationInput{
		DatasetID:   &dsCreated.Dataset.ID,
		Name:        &name,
		Latitude:    &lat,
		Longitude:   &lon,
		TimezoneID:  &tz,
		Description: &description,
	}

	ctx := context.Background()
	created, err := CreateOrUpdateLocation(ctx, createInput)
	if err != nil {
		t.Fatalf("Failed to create location: %v", err)
	}

	// Verify initial values
	if created.Location.Name != "Test Location" {
		t.Errorf("Expected name 'Test Location', got '%s'", created.Location.Name)
	}
	if created.Location.TimezoneID != "Pacific/Auckland" {
		t.Errorf("Expected timezone 'Pacific/Auckland', got '%s'", created.Location.TimezoneID)
	}

	// Update only the description (nil for other fields)
	newDesc := "Updated description only"
	updateInput := LocationInput{
		ID:          &created.Location.ID,
		Description: &newDesc,
		// Name, Latitude, Longitude, TimezoneID are nil - should be preserved
	}

	updated, err := CreateOrUpdateLocation(ctx, updateInput)
	if err != nil {
		t.Fatalf("Failed to update location: %v", err)
	}

	// Verify only description changed
	if updated.Location.Name != "Test Location" {
		t.Errorf("Name should be preserved, got '%s'", updated.Location.Name)
	}
	if updated.Location.Latitude != -36.85 {
		t.Errorf("Latitude should be preserved, got %f", updated.Location.Latitude)
	}
	if updated.Location.Longitude != 174.76 {
		t.Errorf("Longitude should be preserved, got %f", updated.Location.Longitude)
	}
	if updated.Location.TimezoneID != "Pacific/Auckland" {
		t.Errorf("TimezoneID should be preserved, got '%s'", updated.Location.TimezoneID)
	}
	if updated.Location.Description == nil || *updated.Location.Description != "Updated description only" {
		t.Errorf("Description should be updated, got '%v'", updated.Location.Description)
	}
}

// TestClusterUpdatePreservesUnsetFields tests that update only modifies provided fields
func TestClusterUpdatePreservesUnsetFields(t *testing.T) {
	dbPath, cleanup := setupTestDB(t)
	defer cleanup()

	SetDBPath(dbPath)

	// Create dataset and location
	dsName := "Test Dataset"
	dsCreated, err := CreateOrUpdateDataset(context.Background(), DatasetInput{Name: &dsName})
	if err != nil {
		t.Fatalf("Failed to create dataset: %v", err)
	}

	locName := "Test Location"
	lat, lon := -36.85, 174.76
	tz := "Pacific/Auckland"
	locCreated, err := CreateOrUpdateLocation(context.Background(), LocationInput{
		DatasetID:  &dsCreated.Dataset.ID,
		Name:       &locName,
		Latitude:   &lat,
		Longitude:  &lon,
		TimezoneID: &tz,
	})
	if err != nil {
		t.Fatalf("Failed to create location: %v", err)
	}

	// Create a cluster with all fields
	name := "Test Cluster"
	sampleRate := 250000
	description := "Original description"
	createInput := ClusterInput{
		DatasetID:   &dsCreated.Dataset.ID,
		LocationID:  &locCreated.Location.ID,
		Name:        &name,
		SampleRate:  &sampleRate,
		Description: &description,
	}

	ctx := context.Background()
	created, err := CreateOrUpdateCluster(ctx, createInput)
	if err != nil {
		t.Fatalf("Failed to create cluster: %v", err)
	}

	// Update only the description (nil for other fields)
	newDesc := "Updated description only"
	updateInput := ClusterInput{
		ID:          &created.Cluster.ID,
		Description: &newDesc,
		// Name, SampleRate are nil - should be preserved
	}

	updated, err := CreateOrUpdateCluster(ctx, updateInput)
	if err != nil {
		t.Fatalf("Failed to update cluster: %v", err)
	}

	// Verify only description changed
	if updated.Cluster.Name != "Test Cluster" {
		t.Errorf("Name should be preserved, got '%s'", updated.Cluster.Name)
	}
	if updated.Cluster.SampleRate != 250000 {
		t.Errorf("SampleRate should be preserved, got %d", updated.Cluster.SampleRate)
	}
	if updated.Cluster.Description == nil || *updated.Cluster.Description != "Updated description only" {
		t.Errorf("Description should be updated, got '%v'", updated.Cluster.Description)
	}
}

// TestPatternUpdatePreservesUnsetFields tests that update only modifies provided fields
func TestPatternUpdatePreservesUnsetFields(t *testing.T) {
	dbPath, cleanup := setupTestDB(t)
	defer cleanup()

	SetDBPath(dbPath)

	// Create a pattern
	recordSeconds := 60
	sleepSeconds := 1740
	createInput := PatternInput{
		RecordSeconds: &recordSeconds,
		SleepSeconds:  &sleepSeconds,
	}

	ctx := context.Background()
	created, err := CreateOrUpdatePattern(ctx, createInput)
	if err != nil {
		t.Fatalf("Failed to create pattern: %v", err)
	}

	// Verify initial values
	if created.Pattern.RecordS != 60 {
		t.Errorf("Expected record_s 60, got %d", created.Pattern.RecordS)
	}
	if created.Pattern.SleepS != 1740 {
		t.Errorf("Expected sleep_s 1740, got %d", created.Pattern.SleepS)
	}

	// Update only the record seconds
	newRecord := 30
	updateInput := PatternInput{
		ID:            &created.Pattern.ID,
		RecordSeconds: &newRecord,
		// SleepSeconds is nil - should be preserved
	}

	updated, err := CreateOrUpdatePattern(ctx, updateInput)
	if err != nil {
		t.Fatalf("Failed to update pattern: %v", err)
	}

	// Verify only record changed
	if updated.Pattern.RecordS != 30 {
		t.Errorf("RecordS should be updated to 30, got %d", updated.Pattern.RecordS)
	}
	if updated.Pattern.SleepS != 1740 {
		t.Errorf("SleepS should be preserved at 1740, got %d", updated.Pattern.SleepS)
	}
}

// TestDatasetUpdateNoFieldsError tests that update with no fields returns error
func TestDatasetUpdateNoFieldsError(t *testing.T) {
	dbPath, cleanup := setupTestDB(t)
	defer cleanup()

	SetDBPath(dbPath)

	// Create a dataset
	name := "Test Dataset"
	created, err := CreateOrUpdateDataset(context.Background(), DatasetInput{Name: &name})
	if err != nil {
		t.Fatalf("Failed to create dataset: %v", err)
	}

	// Update with no fields should error
	updateInput := DatasetInput{
		ID: &created.Dataset.ID,
		// All other fields are nil
	}

	_, err = CreateOrUpdateDataset(context.Background(), updateInput)
	if err == nil {
		t.Error("Expected error when no fields provided to update")
	}
}