package sqlexec

import (
	"testing"

	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/writers"
)

func TestNewWriter(t *testing.T) {
	opts := &writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://localhost/test",
		},
	}

	writer := NewWriter(opts)
	if writer == nil {
		t.Fatal("Expected non-nil writer")
	}
	if writer.options != opts {
		t.Error("Writer options not set correctly")
	}
}

func TestWriter_WriteDatabase_NilDatabase(t *testing.T) {
	writer := NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://localhost/test",
		},
	})

	err := writer.WriteDatabase(nil)
	if err == nil {
		t.Error("Expected error for nil database, got nil")
	}
}

func TestWriter_WriteDatabase_MissingConnectionString(t *testing.T) {
	writer := NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{},
	})

	db := &models.Database{
		Name: "test",
		Schemas: []*models.Schema{
			{
				Name: "public",
				Scripts: []*models.Script{
					{Name: "test", SQL: "SELECT 1;"},
				},
			},
		},
	}

	err := writer.WriteDatabase(db)
	if err == nil {
		t.Error("Expected error for missing connection_string, got nil")
	}
}

func TestWriter_WriteSchema_NilSchema(t *testing.T) {
	writer := NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://localhost/test",
		},
	})

	err := writer.WriteSchema(nil)
	if err == nil {
		t.Error("Expected error for nil schema, got nil")
	}
}

func TestWriter_WriteSchema_MissingConnectionString(t *testing.T) {
	writer := NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{},
	})

	schema := &models.Schema{
		Name: "public",
		Scripts: []*models.Script{
			{Name: "test", SQL: "SELECT 1;"},
		},
	}

	err := writer.WriteSchema(schema)
	if err == nil {
		t.Error("Expected error for missing connection_string, got nil")
	}
}

func TestWriter_WriteTable(t *testing.T) {
	writer := NewWriter(&writers.WriterOptions{})

	err := writer.WriteTable(&models.Table{})
	if err == nil {
		t.Error("Expected error for WriteTable (not supported), got nil")
	}
}

// TestScriptSorting verifies that scripts are sorted correctly by Priority then Sequence
func TestScriptSorting(t *testing.T) {
	scripts := []*models.Script{
		{Name: "script1", Priority: 2, Sequence: 1, SQL: "SELECT 1;"},
		{Name: "script2", Priority: 1, Sequence: 3, SQL: "SELECT 2;"},
		{Name: "script3", Priority: 1, Sequence: 1, SQL: "SELECT 3;"},
		{Name: "script4", Priority: 1, Sequence: 2, SQL: "SELECT 4;"},
		{Name: "script5", Priority: 3, Sequence: 1, SQL: "SELECT 5;"},
		{Name: "script6", Priority: 2, Sequence: 2, SQL: "SELECT 6;"},
	}

	// Create a copy and sort it using the same logic as executeScripts
	sortedScripts := make([]*models.Script, len(scripts))
	copy(sortedScripts, scripts)

	// Use the same sorting logic from executeScripts
	for i := 0; i < len(sortedScripts)-1; i++ {
		for j := i + 1; j < len(sortedScripts); j++ {
			if sortedScripts[i].Priority > sortedScripts[j].Priority ||
				(sortedScripts[i].Priority == sortedScripts[j].Priority &&
					sortedScripts[i].Sequence > sortedScripts[j].Sequence) {
				sortedScripts[i], sortedScripts[j] = sortedScripts[j], sortedScripts[i]
			}
		}
	}

	// Expected order after sorting
	expectedOrder := []string{
		"script3", // Priority 1, Sequence 1
		"script4", // Priority 1, Sequence 2
		"script2", // Priority 1, Sequence 3
		"script1", // Priority 2, Sequence 1
		"script6", // Priority 2, Sequence 2
		"script5", // Priority 3, Sequence 1
	}

	for i, expected := range expectedOrder {
		if sortedScripts[i].Name != expected {
			t.Errorf("Position %d: expected %s, got %s", i, expected, sortedScripts[i].Name)
		}
	}

	// Verify priorities are ascending
	for i := 0; i < len(sortedScripts)-1; i++ {
		if sortedScripts[i].Priority > sortedScripts[i+1].Priority {
			t.Errorf("Priority not ascending at position %d: %d > %d",
				i, sortedScripts[i].Priority, sortedScripts[i+1].Priority)
		}
		// Within same priority, sequences should be ascending
		if sortedScripts[i].Priority == sortedScripts[i+1].Priority &&
			sortedScripts[i].Sequence > sortedScripts[i+1].Sequence {
			t.Errorf("Sequence not ascending at position %d with same priority %d: %d > %d",
				i, sortedScripts[i].Priority, sortedScripts[i].Sequence, sortedScripts[i+1].Sequence)
		}
	}
}

func TestWriter_WriteSchema_EmptyScripts(t *testing.T) {
	// This test verifies that writing an empty script list doesn't cause errors
	// even without a database connection (should return early)
	writer := NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://invalid/test",
		},
	})

	schema := &models.Schema{
		Name:    "public",
		Scripts: []*models.Script{},
	}

	// Note: This will try to connect even with empty scripts
	// In a real scenario, the executeScripts function returns early for empty scripts
	// but the connection is made before that. This test documents the behavior.
	err := writer.WriteSchema(schema)
	// We expect a connection error since we're using an invalid connection string
	if err == nil {
		t.Error("Expected connection error, got nil")
	}
}

// NOTE: Integration tests for actual database execution should be added separately
// Those tests would require:
// 1. A running PostgreSQL instance
// 2. Test database setup/teardown
// 3. Verification of actual script execution
// 4. Testing error handling during execution
// 5. Testing transaction behavior if added
//
// Example integration test structure:
// func TestWriter_Integration_ExecuteScripts(t *testing.T) {
//     if testing.Short() {
//         t.Skip("Skipping integration test")
//     }
//     // Setup test database
//     // Create test scripts
//     // Execute scripts
//     // Verify results
//     // Cleanup
// }