package sqldir

import (
	"os"
	"path/filepath"
	"testing"

	"git.warky.dev/wdevs/relspecgo/pkg/readers"
)

func TestReader_ReadDatabase(t *testing.T) {
	// Create temporary test directory
	tempDir, err := os.MkdirTemp("", "sqldir-test-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	// Create test SQL files with both underscore and hyphen separators
	testFiles := map[string]string{
		"1_001_create_users.sql":  "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);",
		"1_002_create_posts.sql":  "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INT);",
		"2_001_add_indexes.sql":   "CREATE INDEX idx_posts_user_id ON posts(user_id);",
		"1_003_seed_data.pgsql":   "INSERT INTO users (name) VALUES ('Alice'), ('Bob');",
		"10-10-create-newid.pgsql": "CREATE TABLE newid (id SERIAL PRIMARY KEY);",
		"2-005-add-column.sql":    "ALTER TABLE users ADD COLUMN email TEXT;",
	}

	for filename, content := range testFiles {
		filePath := filepath.Join(tempDir, filename)
		if err := os.WriteFile(filePath, []byte(content), 0644); err != nil {
			t.Fatalf("Failed to create test file %s: %v", filename, err)
		}
	}

	// Create subdirectory with additional script
	subDir := filepath.Join(tempDir, "migrations")
	if err := os.MkdirAll(subDir, 0755); err != nil {
		t.Fatalf("Failed to create subdirectory: %v", err)
	}
	subFile := filepath.Join(subDir, "3_001_add_column.sql")
	if err := os.WriteFile(subFile, []byte("ALTER TABLE users ADD COLUMN email TEXT;"), 0644); err != nil {
		t.Fatalf("Failed to create subdirectory file: %v", err)
	}

	// Create reader
	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
		Metadata: map[string]any{
			"schema_name":   "test_schema",
			"database_name": "test_db",
		},
	})

	// Read database
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase failed: %v", err)
	}

	// Verify database
	if db.Name != "test_db" {
		t.Errorf("Expected database name 'test_db', got '%s'", db.Name)
	}

	if len(db.Schemas) != 1 {
		t.Fatalf("Expected 1 schema, got %d", len(db.Schemas))
	}

	schema := db.Schemas[0]
	if schema.Name != "test_schema" {
		t.Errorf("Expected schema name 'test_schema', got '%s'", schema.Name)
	}

	// Verify scripts (should be 7 total: 4 underscore + 2 hyphen + 1 subdirectory)
	if len(schema.Scripts) != 7 {
		t.Fatalf("Expected 7 scripts, got %d", len(schema.Scripts))
	}

	// Verify script details
	expectedScripts := []struct {
		name     string
		priority int
		sequence uint
	}{
		{"create_users", 1, 1},
		{"create_posts", 1, 2},
		{"seed_data", 1, 3},
		{"add_indexes", 2, 1},
		{"add-column", 2, 5},
		{"add_column", 3, 1},
		{"create-newid", 10, 10},
	}

	scriptMap := make(map[string]*struct {
		priority int
		sequence uint
		sql      string
	})
	for _, script := range schema.Scripts {
		scriptMap[script.Name] = &struct {
			priority int
			sequence uint
			sql      string
		}{
			priority: script.Priority,
			sequence: script.Sequence,
			sql:      script.SQL,
		}
	}

	for _, expected := range expectedScripts {
		script, exists := scriptMap[expected.name]
		if !exists {
			t.Errorf("Expected script '%s' not found", expected.name)
			continue
		}
		if script.priority != expected.priority {
			t.Errorf("Script '%s': expected priority %d, got %d",
				expected.name, expected.priority, script.priority)
		}
		if script.sequence != expected.sequence {
			t.Errorf("Script '%s': expected sequence %d, got %d",
				expected.name, expected.sequence, script.sequence)
		}
		if script.sql == "" {
			t.Errorf("Script '%s': SQL content is empty", expected.name)
		}
	}
}

func TestReader_ReadSchema(t *testing.T) {
	// Create temporary test directory
	tempDir, err := os.MkdirTemp("", "sqldir-test-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	// Create test SQL file
	testFile := filepath.Join(tempDir, "1_001_test.sql")
	if err := os.WriteFile(testFile, []byte("SELECT 1;"), 0644); err != nil {
		t.Fatalf("Failed to create test file: %v", err)
	}

	// Create reader
	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
	})

	// Read schema
	schema, err := reader.ReadSchema()
	if err != nil {
		t.Fatalf("ReadSchema failed: %v", err)
	}

	// Verify schema
	if schema.Name != "public" {
		t.Errorf("Expected default schema name 'public', got '%s'", schema.Name)
	}

	if len(schema.Scripts) != 1 {
		t.Fatalf("Expected 1 script, got %d", len(schema.Scripts))
	}
}

func TestReader_InvalidDirectory(t *testing.T) {
	reader := NewReader(&readers.ReaderOptions{
		FilePath: "/nonexistent/directory",
	})

	_, err := reader.ReadDatabase()
	if err == nil {
		t.Error("Expected error for nonexistent directory, got nil")
	}
}

func TestReader_EmptyDirectory(t *testing.T) {
	// Create temporary empty directory
	tempDir, err := os.MkdirTemp("", "sqldir-test-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
	})

	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase failed: %v", err)
	}

	if len(db.Schemas[0].Scripts) != 0 {
		t.Errorf("Expected 0 scripts in empty directory, got %d", len(db.Schemas[0].Scripts))
	}
}

func TestReader_InvalidFilename(t *testing.T) {
	// Create temporary test directory
	tempDir, err := os.MkdirTemp("", "sqldir-test-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	// Create files with various invalid patterns
	invalidFiles := []string{
		"invalid.sql",           // No priority/sequence
		"1_test.sql",            // Missing sequence
		"test_1_2.sql",          // Wrong order
		"a_001_test.sql",        // Non-numeric priority
		"1_abc_test.sql",        // Non-numeric sequence
		"1_001_test.txt",        // Wrong extension
		"1_001_test.sql.backup", // Wrong extension
	}

	for _, filename := range invalidFiles {
		filePath := filepath.Join(tempDir, filename)
		if err := os.WriteFile(filePath, []byte("SELECT 1;"), 0644); err != nil {
			t.Fatalf("Failed to create test file %s: %v", filename, err)
		}
	}

	// Create one valid file
	validFile := filepath.Join(tempDir, "1_001_valid.sql")
	if err := os.WriteFile(validFile, []byte("SELECT 1;"), 0644); err != nil {
		t.Fatalf("Failed to create valid file: %v", err)
	}

	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
	})

	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase failed: %v", err)
	}

	// Should only have the valid file
	if len(db.Schemas[0].Scripts) != 1 {
		t.Errorf("Expected 1 script (invalid files should be skipped), got %d", len(db.Schemas[0].Scripts))
	}

	if db.Schemas[0].Scripts[0].Name != "valid" {
		t.Errorf("Expected script name 'valid', got '%s'", db.Schemas[0].Scripts[0].Name)
	}
}

func TestReader_ReadTable(t *testing.T) {
	reader := NewReader(&readers.ReaderOptions{})

	_, err := reader.ReadTable()
	if err == nil {
		t.Error("Expected error for ReadTable (not supported), got nil")
	}
}

func TestReader_HyphenFormat(t *testing.T) {
	// Create temporary test directory
	tempDir, err := os.MkdirTemp("", "sqldir-test-hyphen-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	// Create test files with hyphen separators
	testFiles := map[string]string{
		"1-001-create-table.sql":  "CREATE TABLE test (id INT);",
		"1-002-insert-data.pgsql": "INSERT INTO test VALUES (1);",
		"10-10-create-newid.pgsql": "CREATE TABLE newid (id SERIAL);",
		"2-005-add-index.sql":     "CREATE INDEX idx_test ON test(id);",
	}

	for filename, content := range testFiles {
		filePath := filepath.Join(tempDir, filename)
		if err := os.WriteFile(filePath, []byte(content), 0644); err != nil {
			t.Fatalf("Failed to create test file %s: %v", filename, err)
		}
	}

	// Create reader
	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
	})

	// Read database
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase failed: %v", err)
	}

	schema := db.Schemas[0]
	if len(schema.Scripts) != 4 {
		t.Fatalf("Expected 4 scripts, got %d", len(schema.Scripts))
	}

	// Verify specific hyphen-formatted scripts
	expectedScripts := map[string]struct {
		priority int
		sequence uint
	}{
		"create-table":  {1, 1},
		"insert-data":   {1, 2},
		"add-index":     {2, 5},
		"create-newid":  {10, 10},
	}

	for _, script := range schema.Scripts {
		expected, exists := expectedScripts[script.Name]
		if !exists {
			t.Errorf("Unexpected script: %s", script.Name)
			continue
		}
		if script.Priority != expected.priority {
			t.Errorf("Script '%s': expected priority %d, got %d",
				script.Name, expected.priority, script.Priority)
		}
		if script.Sequence != expected.sequence {
			t.Errorf("Script '%s': expected sequence %d, got %d",
				script.Name, expected.sequence, script.Sequence)
		}
	}
}

func TestReader_MixedFormat(t *testing.T) {
	// Test that both underscore and hyphen formats can be mixed
	tempDir, err := os.MkdirTemp("", "sqldir-test-mixed-*")
	if err != nil {
		t.Fatalf("Failed to create temp directory: %v", err)
	}
	defer os.RemoveAll(tempDir)

	testFiles := map[string]string{
		"1_001_underscore.sql": "SELECT 1;",
		"1-002-hyphen.sql":     "SELECT 2;",
		"2_003_underscore.sql": "SELECT 3;",
		"2-004-hyphen.sql":     "SELECT 4;",
	}

	for filename, content := range testFiles {
		filePath := filepath.Join(tempDir, filename)
		if err := os.WriteFile(filePath, []byte(content), 0644); err != nil {
			t.Fatalf("Failed to create test file %s: %v", filename, err)
		}
	}

	reader := NewReader(&readers.ReaderOptions{
		FilePath: tempDir,
	})

	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase failed: %v", err)
	}

	schema := db.Schemas[0]
	if len(schema.Scripts) != 4 {
		t.Fatalf("Expected 4 scripts (mixed format), got %d", len(schema.Scripts))
	}

	// Verify both formats are parsed correctly
	names := make(map[string]bool)
	for _, script := range schema.Scripts {
		names[script.Name] = true
	}

	expectedNames := []string{"underscore", "hyphen", "underscore", "hyphen"}
	for _, name := range expectedNames {
		if !names[name] {
			t.Errorf("Expected script name '%s' not found", name)
		}
	}
}