package executor

import (
	"context"
	"testing"

	"github.com/everything-on-mysql/cassandra-on-mysql/pkg/cql/parser"
	"github.com/everything-on-mysql/cassandra-on-mysql/pkg/cql/systemtables"
)

// TestSystemTableIntegration tests the integration between executor and systemtables
func TestSystemTableIntegration(t *testing.T) {
	// Create a mock executor with system table support
	executor := &Executor{
		systemExecutor: systemtables.NewSystemTableExecutor(systemtables.NewRegistry()),
	}

	ctx := context.Background()

	// Test 1: Query system.local through executor
	t.Run("system_local_via_executor", func(t *testing.T) {
		cql := "SELECT * FROM system.local"
		stmt, err := parser.NewParser(cql).Parse()
		if err != nil {
			t.Fatalf("Parse failed: %v", err)
		}

		selectStmt, ok := stmt.(*parser.SelectStatement)
		if !ok {
			t.Fatal("Expected SelectStatement")
		}

		// Check if it's detected as system table query
		if !systemtables.IsSystemTableQuery(selectStmt) {
			t.Fatal("Should be detected as system table query")
		}

		// Execute through system executor
		result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
		if err != nil {
			t.Fatalf("Execute failed: %v", err)
		}

		// Convert to executor.QueryResult
		converted := convertQueryResult(result)

		if len(converted.Columns) == 0 {
			t.Fatal("Expected columns")
		}
		if len(converted.Rows) == 0 {
			t.Fatal("Expected rows")
		}

		t.Logf("Columns: %v", converted.Columns)
		t.Logf("Rows: %d", len(converted.Rows))
	})

	// Test 2: Query system_schema.keyspaces
	t.Run("system_schema_keyspaces_via_executor", func(t *testing.T) {
		cql := "SELECT * FROM system_schema.keyspaces"
		stmt, err := parser.NewParser(cql).Parse()
		if err != nil {
			t.Fatalf("Parse failed: %v", err)
		}

		selectStmt, ok := stmt.(*parser.SelectStatement)
		if !ok {
			t.Fatal("Expected SelectStatement")
		}

		if !systemtables.IsSystemTableQuery(selectStmt) {
			t.Fatal("Should be detected as system table query")
		}

		result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
		if err != nil {
			t.Fatalf("Execute failed: %v", err)
		}

		converted := convertQueryResult(result)

		if len(converted.Rows) == 0 {
			t.Fatal("Expected rows")
		}

		t.Logf("Keyspaces: %d rows", len(converted.Rows))
	})

	// Test 3: Query system_auth.roles
	t.Run("system_auth_roles_via_executor", func(t *testing.T) {
		cql := "SELECT * FROM system_auth.roles"
		stmt, err := parser.NewParser(cql).Parse()
		if err != nil {
			t.Fatalf("Parse failed: %v", err)
		}

		selectStmt, ok := stmt.(*parser.SelectStatement)
		if !ok {
			t.Fatal("Expected SelectStatement")
		}

		if !systemtables.IsSystemTableQuery(selectStmt) {
			t.Fatal("Should be detected as system table query")
		}

		result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
		if err != nil {
			t.Fatalf("Execute failed: %v", err)
		}

		converted := convertQueryResult(result)

		if len(converted.Columns) == 0 {
			t.Fatal("Expected columns")
		}

		t.Logf("Roles: %d rows", len(converted.Rows))
	})

	// Test 4: Test convertQueryResult with nil
	t.Run("convert_nil", func(t *testing.T) {
		result := convertQueryResult(nil)
		if result == nil {
			t.Fatal("Expected non-nil result")
		}
		if result.Columns == nil {
			t.Error("Expected non-nil columns")
		}
		if result.Rows == nil {
			t.Error("Expected non-nil rows")
		}
	})
}

// TestSystemTableQueryRouting tests the full Execute flow with system tables
func TestSystemTableQueryRouting(t *testing.T) {
	// This test verifies that the Execute method correctly routes system table queries
	// Note: We can't fully test this without a real poolManager, but we can verify
	// the routing logic works

	// Create a minimal executor
	executor := &Executor{
		systemExecutor: systemtables.NewSystemTableExecutor(systemtables.NewRegistry()),
	}

	ctx := context.Background()

	// Test that system table queries are detected
	queries := []string{
		"SELECT * FROM system.local",
		"SELECT * FROM system.peers",
		"SELECT * FROM system_schema.keyspaces",
		"SELECT * FROM system_distributed.repair_history",
		"SELECT * FROM system_auth.roles",
		"SELECT * FROM system_traces.sessions",
		"SELECT * FROM dse_security.role_options",
	}

	for _, cql := range queries {
		t.Run(cql, func(t *testing.T) {
			stmt, err := parser.NewParser(cql).Parse()
			if err != nil {
				t.Fatalf("Parse failed: %v", err)
			}

			selectStmt, ok := stmt.(*parser.SelectStatement)
			if !ok {
				t.Fatal("Expected SelectStatement")
			}

			// Verify detection
			if !systemtables.IsSystemTableQuery(selectStmt) {
				t.Fatalf("Should detect system table query: %s", cql)
			}

			// Execute and verify result
			result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
			if err != nil {
				t.Fatalf("Execute failed: %v", err)
			}

			converted := convertQueryResult(result)
			if converted == nil {
				t.Fatal("Expected non-nil result")
			}

			t.Logf("Query: %s -> %d rows", cql, len(converted.Rows))
		})
	}
}

// TestSystemTableWithFiltering tests system tables with WHERE clauses
func TestSystemTableWithFiltering(t *testing.T) {
	executor := &Executor{
		systemExecutor: systemtables.NewSystemTableExecutor(systemtables.NewRegistry()),
	}

	ctx := context.Background()

	tests := []struct {
		name       string
		query      string
		expectRows int
	}{
		{
			name:       "system.local with key filter",
			query:      "SELECT * FROM system.local WHERE key = 'local'",
			expectRows: 1,
		},
		{
			name:       "system_schema.keyspaces with name filter",
			query:      "SELECT * FROM system_schema.keyspaces WHERE keyspace_name = 'system_schema'",
			expectRows: 1,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			stmt, err := parser.NewParser(tt.query).Parse()
			if err != nil {
				t.Fatalf("Parse failed: %v", err)
			}

			selectStmt := stmt.(*parser.SelectStatement)
			result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
			if err != nil {
				t.Fatalf("Execute failed: %v", err)
			}

			converted := convertQueryResult(result)
			if len(converted.Rows) != tt.expectRows {
				t.Errorf("Expected %d rows, got %d", tt.expectRows, len(converted.Rows))
			}
		})
	}
}

// TestSystemTableWithLimit tests system tables with LIMIT clauses
func TestSystemTableWithLimit(t *testing.T) {
	executor := &Executor{
		systemExecutor: systemtables.NewSystemTableExecutor(systemtables.NewRegistry()),
	}

	ctx := context.Background()

	cql := "SELECT * FROM system_schema.tables LIMIT 2"
	stmt, err := parser.NewParser(cql).Parse()
	if err != nil {
		t.Fatalf("Parse failed: %v", err)
	}

	selectStmt := stmt.(*parser.SelectStatement)
	result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
	if err != nil {
		t.Fatalf("Execute failed: %v", err)
	}

	converted := convertQueryResult(result)
	if len(converted.Rows) != 2 {
		t.Errorf("Expected 2 rows with LIMIT 2, got %d", len(converted.Rows))
	}
}

// TestSystemTableColumnProjection tests column projection
func TestSystemTableColumnProjection(t *testing.T) {
	executor := &Executor{
		systemExecutor: systemtables.NewSystemTableExecutor(systemtables.NewRegistry()),
	}

	ctx := context.Background()

	cql := "SELECT keyspace_name, table_name FROM system_schema.tables"
	stmt, err := parser.NewParser(cql).Parse()
	if err != nil {
		t.Fatalf("Parse failed: %v", err)
	}

	selectStmt := stmt.(*parser.SelectStatement)
	result, err := executor.systemExecutor.Execute(ctx, selectStmt, "test_user", "test_password")
	if err != nil {
		t.Fatalf("Execute failed: %v", err)
	}

	converted := convertQueryResult(result)
	if len(converted.Columns) != 2 {
		t.Errorf("Expected 2 columns, got %d", len(converted.Columns))
	}
	if converted.Columns[0].Name != "keyspace_name" || converted.Columns[1].Name != "table_name" {
		t.Errorf("Column names incorrect: %v", converted.Columns)
	}
}