package mysql

import (
	"context"
	"fmt"
	"reflect"
	"testing"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/pov-service/internal/common/options"
	"github.com/tidbcloud/pov-service/internal/config"
	"github.com/tidbcloud/pov-service/internal/model"
	"github.com/tidbcloud/pov-service/internal/pkg/log"
	"github.com/tidbcloud/pov-service/internal/pkg/uuid"
	"go.uber.org/zap"
)

// setupTestDB creates a unique database for each test to ensure isolation
func setupTestDB(t *testing.T) (*sqlx.DB, func()) {
	// Generate unique database name for this test
	testDBName := fmt.Sprintf("tidbcloud_pov_test_%s_%d",
		uuid.NewStringShort(), time.Now().UnixNano())

	// Base configuration to connect to MySQL server (without specific database)
	baseCfg := config.DatabaseConfig{
		Host:         "xxx",
		Port:         4000,
		User:         "xxx.root",
		Password:     "xxx",
		TLSMode:      "skip-verify",
		Database:     "", // Connect without database first
		MaxOpenConns: 5,
		MaxIdleConns: 2,
	}

	// Connect to MySQL server to create test database
	baseDB, err := NewDB(baseCfg)
	if err != nil {
		t.Fatalf("Cannot connect to MySQL server: %v", err)
	}
	// Create unique test database
	_, err = baseDB.Exec(fmt.Sprintf("CREATE DATABASE IF NOT EXISTS `%s`", testDBName))
	if err != nil {
		_ = baseDB.Close()
		t.Fatalf("Failed to create test database: %v", err)
	}
	_ = baseDB.Close()

	// Now connect to the specific test database
	testCfg := baseCfg
	testCfg.Database = testDBName

	database, err := NewDB(testCfg)
	if err != nil {
		t.Fatalf("Cannot connect to test database: %v", err)
	}

	// Create a test logger
	zapLogger, _ := zap.NewDevelopment()
	testLogger := log.Logger{Sugar: zapLogger.Sugar()}

	// Run migrations to create test tables
	migrationManager := NewMigrationManager(database, testLogger)
	err = migrationManager.RunMigrations()
	if err != nil {
		_ = database.Close()
		t.Fatalf("Failed to run migrations: %v", err)
	}

	// Cleanup function that drops the entire test database
	cleanup := func() {
		_ = database.Close()

		// Connect to MySQL server again to drop the test database
		baseDB, err := NewDB(baseCfg)
		if err != nil {
			t.Logf("Warning: Could not connect to drop test database: %v", err)
			return
		}
		defer func() { _ = baseDB.Close() }()

		_, err = baseDB.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", testDBName))
		if err != nil {
			t.Logf("Warning: Could not drop test database %s: %v", testDBName, err)
		}
	}

	return database, cleanup
}

func TestWorkloadTestRepo_Create(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workload
	workload := model.NewWorkloadTest("Test Workload", 123, model.WorkloadParams{
		{Name: "param1", Value: "value1"},
		{Name: "param2", Value: "value2"},
	}, true, "test@example.com")
	workload.WorkloadName = "workload_name"

	// Test Create
	created, err := repo.Create(ctx, workload)
	require.NoError(t, err)
	assert.Equal(t, workload.ID, created.ID)
	assert.Equal(t, workload.Name, created.Name)
	assert.Equal(t, workload.WorkloadID, created.WorkloadID)
	assert.Equal(t, workload.WorkloadName, created.WorkloadName)
	assert.Equal(t, workload.Status, created.Status)
	assert.Equal(t, workload.Phase, created.Phase)
	assert.NotZero(t, created.CreatedAt)
	assert.NotNil(t, created.UpdatedAt)

	// Check parameters are correctly stored
	require.Len(t, created.Params, 2)
	assert.Equal(t, "param1", created.Params[0].Name)
	assert.Equal(t, "value1", created.Params[0].Value)
	assert.Equal(t, "param2", created.Params[1].Name)
	assert.Equal(t, "value2", created.Params[1].Value)

	// Test duplicate create should fail
	_, err = repo.Create(ctx, workload)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "already exists")
}

func TestWorkloadTestRepo_Get(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workload
	workload := model.NewWorkloadTest("Test Workload", 123, model.WorkloadParams{
		{Name: "param1", Value: "value1"},
	}, false, "test@example.com")

	// Add a task for testing
	now := time.Now().UTC()
	task := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypeCreateCluster,
			RelatedServiceID: "tcms-workflow-id",
			Phase:            model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				StartedAt: &now,
				Status:    model.ExecStatusPending,
				Message:   "test message",
			},
			Progress: 0.5,
		},
		Result: model.CreateClusterResult{
			Cluster: &model.TiDBCluster{
				TestbedID:           456,
				TestbedName:         "test-testbed",
				TestbedPlanExecID:   789,
				TiWorkloadClusterID: "workload-cluster-123",
				TiDBCloudOrgID:      "101112",
				TiDBCloudClusterID:  "test-org",
			},
		},
	}
	workload.Tasks = append(workload.Tasks, task)

	// Create workload
	_, err := repo.Create(ctx, workload)
	require.NoError(t, err)

	// Test Get
	retrieved, err := repo.Get(ctx, workload.ID)
	assert.NoError(t, err)
	assert.Equal(t, workload.ID, retrieved.ID)
	assert.Equal(t, workload.Name, retrieved.Name)
	assert.Equal(t, workload.WorkloadID, retrieved.WorkloadID)
	assert.Equal(t, workload.WorkloadName, retrieved.WorkloadName)
	assert.Equal(t, workload.Status, retrieved.Status)
	assert.Equal(t, workload.Phase, retrieved.Phase)
	assert.Equal(t, workload.StartedBy, retrieved.StartedBy)
	assert.NotZero(t, retrieved.CreatedAt)
	assert.NotNil(t, retrieved.UpdatedAt)

	// Check parameters are correctly retrieved
	require.Len(t, retrieved.Params, 1)
	assert.Equal(t, "param1", retrieved.Params[0].Name)
	assert.Equal(t, "value1", retrieved.Params[0].Value)

	// Check task details
	assert.Len(t, retrieved.Tasks, 1)
	assert.NotZero(t, retrieved.Tasks[0].ID)
	assert.Equal(t, model.WorkloadTaskTypeCreateCluster, retrieved.Tasks[0].Typ)
	assert.Equal(t, float64(0.5), retrieved.Tasks[0].Progress)
	assert.Equal(t, "tcms-workflow-id", retrieved.Tasks[0].RelatedServiceID)
	assert.Equal(t, model.ExecStatusPending, retrieved.Tasks[0].Status)
	assert.Equal(t, "test message", retrieved.Tasks[0].Message)

	// Check task result details
	if createClusterResult, ok := retrieved.Tasks[0].Result.(model.CreateClusterResult); ok {
		assert.NotNil(t, createClusterResult.Cluster)
		assert.Equal(t, int64(456), createClusterResult.Cluster.TestbedID)
		assert.Equal(t, "test-testbed", createClusterResult.Cluster.TestbedName)
		assert.Equal(t, int64(789), createClusterResult.Cluster.TestbedPlanExecID)
		assert.Equal(t, "workload-cluster-123", createClusterResult.Cluster.TiWorkloadClusterID)
		assert.Equal(t, "101112", createClusterResult.Cluster.TiDBCloudOrgID)
		assert.Equal(t, "test-org", createClusterResult.Cluster.TiDBCloudClusterID)
	} else {
		t.Error("Expected CreateClusterResult in task result")
	}

	// Test Get non-existent workload
	_, err = repo.Get(ctx, uuid.NewString())
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "not found")
}

func TestWorkloadTestRepo_Update(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workload
	workload := model.NewWorkloadTest("Test Workload", 123, model.WorkloadParams{
		{Name: "param1", Value: "value1"},
	}, false, "test@example.com")

	_, err := repo.Create(ctx, workload)
	require.NoError(t, err)

	// Update workload
	workload.Name = "Updated Workload"
	workload.Status = model.ExecStatusRunning
	workload.Phase = model.WorkloadTestPhaseCreateCluster
	workload.Progress = 0.8
	workload.Message = "update message"
	workload.WorkloadName = "updated-workload-name"

	// Add a task
	task := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypeCreateCluster,
			RelatedServiceID: "test-service",
			Phase:            model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				Status:    model.ExecStatusPending,
				CreatedAt: time.Now().UTC(),
			},
		},
		Result: model.CreateClusterResult{
			Cluster: &model.TiDBCluster{
				TestbedID:           456,
				TestbedName:         "test-testbed",
				TestbedPlanExecID:   789,
				TiWorkloadClusterID: "workload-cluster-123",
				TiDBCloudOrgID:      "101112",
				TiDBCloudClusterID:  "test-org",
			},
		},
	}
	workload.Tasks = append(workload.Tasks, task)

	// Test Update
	updated, err := repo.Update(ctx, workload)
	assert.NoError(t, err)
	assert.Equal(t, workload.ID, updated.ID)
	assert.Equal(t, "Updated Workload", updated.Name)
	assert.Equal(t, model.ExecStatusRunning, updated.Status)
	assert.Equal(t, model.WorkloadTestPhaseCreateCluster, updated.Phase)
	assert.Equal(t, workload.WorkloadID, updated.WorkloadID)
	assert.Equal(t, workload.WorkloadName, updated.WorkloadName)
	assert.Equal(t, "updated-workload-name", updated.WorkloadName)
	assert.NotZero(t, updated.CreatedAt)
	assert.NotNil(t, updated.UpdatedAt)
	assert.Equal(t, float64(0.8), updated.Progress)
	assert.Equal(t, "update message", updated.Message)

	// Check parameters are preserved during update
	require.Len(t, updated.Params, 1)
	assert.Equal(t, "param1", updated.Params[0].Name)
	assert.Equal(t, "value1", updated.Params[0].Value)

	// Check task was updated
	assert.Len(t, updated.Tasks, 1)
	assert.Equal(t, model.WorkloadTaskTypeCreateCluster, updated.Tasks[0].Typ)
	assert.Equal(t, model.ExecStatusPending, updated.Tasks[0].Status)
	assert.NotZero(t, updated.Tasks[0].ID)
	assert.NotZero(t, updated.Tasks[0].CreatedAt)
	assert.NotNil(t, updated.Tasks[0].UpdatedAt)
	assert.Equal(t, model.WorkloadTaskPhasePending, updated.Tasks[0].Phase)
	assert.Equal(t, "test-service", updated.Tasks[0].RelatedServiceID)
	assert.Equal(t, "101112", updated.Tasks[0].Result.(model.CreateClusterResult).Cluster.TiDBCloudOrgID)
	assert.Equal(t, "test-org", updated.Tasks[0].Result.(model.CreateClusterResult).Cluster.TiDBCloudClusterID)
	assert.Equal(t, "workload-cluster-123", updated.Tasks[0].Result.(model.CreateClusterResult).Cluster.TiWorkloadClusterID)

	// Verify the update by retrieving
	retrieved, err := repo.Get(ctx, workload.ID)
	assert.NoError(t, err)
	assert.Equal(t, workload.ID, retrieved.ID)
	assert.Equal(t, "Updated Workload", retrieved.Name)
	assert.Equal(t, model.ExecStatusRunning, retrieved.Status)
	assert.Equal(t, model.WorkloadTestPhaseCreateCluster, retrieved.Phase)
	assert.Equal(t, workload.WorkloadID, retrieved.WorkloadID)
	assert.Equal(t, workload.WorkloadName, retrieved.WorkloadName)

	// Check parameters are preserved in retrieved record
	require.Len(t, retrieved.Params, 1)
	assert.Equal(t, "param1", retrieved.Params[0].Name)
	assert.Equal(t, "value1", retrieved.Params[0].Value)

	assert.Len(t, retrieved.Tasks, 1)
	assert.Equal(t, model.WorkloadTaskTypeCreateCluster, retrieved.Tasks[0].Typ)
	assert.Equal(t, model.ExecStatusPending, retrieved.Tasks[0].Status)

	// Mark the first task as completed and append a new task
	firstTaskID := updated.Tasks[0].ID
	updated.Tasks[0].Status = model.ExecStatusCompleted
	updated.Tasks[0].Message = "Cluster created successfully"
	updated.Tasks[0].Progress = 1.0

	// Add a new PrepareData task
	prepareDataTask := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypePrepareData,
			RelatedServiceID: "prepare-service-123",
			Phase:            model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				Status:  model.ExecStatusPending,
				Message: "Preparing data...",
			},
			Progress: 0.0,
		},
		Result: model.PrepareDataResult{
			DataSize:               1000000,
			TableCount:             5,
			DBHost:                 "db.example.com",
			DBPort:                 4000,
			PDHost:                 "pd.example.com",
			PDPort:                 2379,
			TiWorkloadClusterID:    "tw-cluster-456",
			TiWorkloadWorkloadName: "benchmark-workload",
		},
	}
	updated.Tasks = append(updated.Tasks, prepareDataTask)

	// Update the workload with the modified tasks
	secondUpdate, err := repo.Update(ctx, updated)
	assert.NoError(t, err)

	// Verify the first task was marked as completed
	require.Len(t, secondUpdate.Tasks, 2)
	assert.Equal(t, firstTaskID, secondUpdate.Tasks[0].ID) // ID should be preserved
	assert.Equal(t, model.WorkloadTaskTypeCreateCluster, secondUpdate.Tasks[0].Typ)
	assert.Equal(t, model.ExecStatusCompleted, secondUpdate.Tasks[0].Status)
	assert.Equal(t, "Cluster created successfully", secondUpdate.Tasks[0].Message)
	assert.Equal(t, float64(1.0), secondUpdate.Tasks[0].Progress)

	// Verify the new PrepareData task was added
	assert.Equal(t, model.WorkloadTaskTypePrepareData, secondUpdate.Tasks[1].Typ)
	assert.Equal(t, model.ExecStatusPending, secondUpdate.Tasks[1].Status)
	assert.Equal(t, "Preparing data...", secondUpdate.Tasks[1].Message)
	assert.Equal(t, float64(0.0), secondUpdate.Tasks[1].Progress)
	assert.NotZero(t, secondUpdate.Tasks[1].ID) // Should have auto-generated ID
	assert.Equal(t, "prepare-service-123", secondUpdate.Tasks[1].RelatedServiceID)

	// Verify the PrepareData task result
	prepareResult, ok := secondUpdate.Tasks[1].Result.(model.PrepareDataResult)
	assert.True(t, ok, "Expected PrepareDataResult type")
	assert.Equal(t, int64(1000000), prepareResult.DataSize)
	assert.Equal(t, 5, prepareResult.TableCount)
	assert.Equal(t, "db.example.com", prepareResult.DBHost)
	assert.Equal(t, 4000, prepareResult.DBPort)
	assert.Equal(t, "pd.example.com", prepareResult.PDHost)
	assert.Equal(t, 2379, prepareResult.PDPort)
	assert.Equal(t, "tw-cluster-456", prepareResult.TiWorkloadClusterID)
	assert.Equal(t, "benchmark-workload", prepareResult.TiWorkloadWorkloadName)

	// Verify by retrieving again
	finalRetrieved, err := repo.Get(ctx, workload.ID)
	assert.NoError(t, err)
	require.Len(t, finalRetrieved.Tasks, 2)

	// Verify both tasks persist correctly in database
	assert.Equal(t, model.ExecStatusCompleted, finalRetrieved.Tasks[0].Status)
	assert.Equal(t, model.ExecStatusPending, finalRetrieved.Tasks[1].Status)
	assert.Equal(t, model.WorkloadTaskTypeCreateCluster, finalRetrieved.Tasks[0].Typ)
	assert.Equal(t, model.WorkloadTaskTypePrepareData, finalRetrieved.Tasks[1].Typ)

	// Test Update non-existent workload
	nonExistent := workload
	nonExistent.ID = uuid.NewString()
	_, err = repo.Update(ctx, nonExistent)
	assert.Error(t, err)
	assert.Contains(t, err.Error(), "not found")
}

func TestWorkloadTestRepo_ListActive(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workloads with different statuses
	workload1 := model.NewWorkloadTest("Active Workload 1", 123, nil, false, "user1@example.com")
	workload1.Status = model.ExecStatusRunning

	workload2 := model.NewWorkloadTest("Active Workload 2", 124, nil, false, "user2@example.com")
	now := time.Now().UTC()
	workload2.Tasks = append(workload2.Tasks, model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:   model.WorkloadTaskTypeCreateCluster,
			Phase: model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				Status:     model.ExecStatusRunning,
				CreatedAt:  now,
				UpdatedAt:  &now,
				StartedAt:  &now,
				FinishedAt: &now,
				Message:    "failed to create cluster",
			},
		},
		Result: model.CreateClusterResult{
			Cluster: &model.TiDBCluster{
				TestbedID:           125,
				TestbedName:         "test-testbed",
				TestbedPlanExecID:   789,
				TiWorkloadClusterID: "workload-cluster-123",
				TiDBCloudOrgID:      "101112",
				TiDBCloudClusterID:  "test-org",
			},
		},
	})
	workload2.Status = model.ExecStatusPending

	workload3 := model.NewWorkloadTest("Inactive Workload", 125, nil, false, "user3@example.com")

	workload3.Status = model.ExecStatusCompleted

	// Create workloads
	_, err := repo.Create(ctx, workload1)
	require.NoError(t, err)
	workload2, err = repo.Create(ctx, workload2)
	require.NoError(t, err)
	workload3, err = repo.Create(ctx, workload3)
	require.NoError(t, err)

	// Test ListActive
	active, err := repo.ListActive(ctx)
	assert.NoError(t, err)
	assert.Len(t, active, 2) // Only pending and running workloads

	// Verify the active workloads
	ids := make(map[string]bool)
	nameToWorkload := make(map[string]model.WorkloadTest)
	for _, w := range active {
		ids[w.ID] = true
		nameToWorkload[w.Name] = w
		assert.True(t, w.Status == model.ExecStatusPending || w.Status == model.ExecStatusRunning)
		// Check that all fields are properly populated for active workloads
		assert.NotEmpty(t, w.ID)
		assert.NotEmpty(t, w.Name)
		assert.NotZero(t, w.WorkloadID)
		assert.NotZero(t, w.CreatedAt)
		assert.NotNil(t, w.UpdatedAt)
	}
	assert.True(t, ids[workload1.ID])
	assert.True(t, ids[workload2.ID])
	assert.False(t, ids[workload3.ID])

	// Verify specific workload details
	if w1, exists := nameToWorkload["Active Workload 1"]; exists {
		assert.Equal(t, workload1.ID, w1.ID)
		assert.Equal(t, model.ExecStatusRunning, w1.Status)
		assert.Equal(t, int64(123), w1.WorkloadID)
		assert.Equal(t, "user1@example.com", w1.StartedBy)
	} else {
		t.Error("Active Workload 1 not found in active list")
	}

	if w2, exists := nameToWorkload["Active Workload 2"]; exists {
		assert.Equal(t, workload2.ID, w2.ID)
		assert.Equal(t, model.ExecStatusPending, w2.Status)
		assert.Equal(t, int64(124), w2.WorkloadID)
		tasks := w2.Tasks
		assert.Len(t, tasks, 1)
		assert.NotZero(t, tasks[0].ID)
		assert.Equal(t, model.WorkloadTaskTypeCreateCluster, tasks[0].Typ)
		assert.Equal(t, model.ExecStatusRunning, tasks[0].Status)
		assert.Equal(t, "failed to create cluster", tasks[0].Message)
		assert.NotNil(t, tasks[0].Result)
		assert.Equal(t, int64(125), tasks[0].Result.(model.CreateClusterResult).Cluster.TestbedID)
		assert.Equal(t, "test-testbed", tasks[0].Result.(model.CreateClusterResult).Cluster.TestbedName)
		assert.Equal(t, "user2@example.com", w2.StartedBy)
	} else {
		t.Error("Active Workload 2 not found in active list")
	}
}

func TestWorkloadTestRepo_ListWithOptions(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create a set of diverse workloads
	now := time.Now().UTC()
	workload1 := model.NewWorkloadTest("Workload A", 101, nil, false, "userA@example.com")
	workload1.Status = model.ExecStatusRunning // Active
	workload1.IsAuto = true
	workload1.WorkloadName = "workload-a"

	workload2 := model.NewWorkloadTest("Workload B", 102, nil, false, "userB@example.com")
	workload2.Status = model.ExecStatusCompleted // Completed
	workload2.CreatedAt = now.Add(-24 * time.Hour)
	workload2.UpdatedAt = &now
	workload2.WorkloadName = "completed-workload"

	workload3 := model.NewWorkloadTest("Workload C", 103, nil, false, "userC@example.com")
	workload3.Status = model.ExecStatusFailed // failed
	workload3.CreatedAt = now.Add(-48 * time.Hour)
	workload3.UpdatedAt = &now
	workload3.WorkloadName = "failed-workload"

	workload4 := model.NewWorkloadTest("Workload D", 104, nil, false, "userD@example.com")
	workload4.Status = model.ExecStatusPending // Active
	workload4.IsAuto = true
	workload4.WorkloadName = "pending-auto-workload"

	workload5 := model.NewWorkloadTest("Workload E", 105, nil, false, "userE@example.com")
	workload5.Status = model.ExecStatusRunning // Active
	workload5.WorkloadName = "running-manual-workload"

	// Create workloads
	var err error
	workload1, err = repo.Create(ctx, workload1)
	require.NoError(t, err)
	workload2, err = repo.Create(ctx, workload2)
	require.NoError(t, err)
	workload3, err = repo.Create(ctx, workload3)
	require.NoError(t, err)
	workload4, err = repo.Create(ctx, workload4)
	require.NoError(t, err)
	workload5, err = repo.Create(ctx, workload5)
	require.NoError(t, err)

	// Test case 1: List without any options (should return all)
	t.Run("List all workloads", func(t *testing.T) {
		lists, err := repo.List(
			ctx,
			options.ListOption[options.WorkloadTestRefOption]{})
		assert.NoError(t, err)
		assert.Len(t, lists, 5)
	})

	// Test case 2: List with ListCompletedWorkloads option
	t.Run("List with ListCompletedWorkloads option", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.CompletedStatus()))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Contains(t, []string{workload2.ID}, lists[0].ID)
	})

	// Test case 3: List with ListAutoWorkloads option
	t.Run("List with ListAutoWorkloads option", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.IsAuto()))
		assert.NoError(t, err)
		assert.Len(t, lists, 2)
		assert.Contains(t, []string{workload1.ID, workload4.ID}, lists[0].ID)
		assert.Contains(t, []string{workload1.ID, workload4.ID}, lists[1].ID)
		assert.Contains(t, []string{"userA@example.com", "userD@example.com"}, lists[0].StartedBy, lists[1].StartedBy)
	})

	// Test case 4: List with ListByWorkloadID option
	t.Run("List with ListByWorkloadID option", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadID(101)))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Equal(t, workload1.ID, lists[0].ID)
		assert.Equal(t, "userA@example.com", lists[0].StartedBy)
	})

	// Test case 5: List with ListByWorkloadName option
	t.Run("List with ListByWorkloadName option", func(t *testing.T) {
		lists, err := repo.List(
			ctx,
			options.NewListWorkloadTestOption().AddFilter(options.WorkloadName("completed-workload")))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Equal(t, workload2.ID, lists[0].ID)
		assert.Equal(t, "userB@example.com", lists[0].StartedBy)
	})

	// Test case 6: List with a combination of options (e.g., completed auto workloads)
	t.Run("List with combined options (completed auto workloads)", func(t *testing.T) {
		// No completed auto workloads in our test data
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.CompletedStatus()).AddFilter(options.IsAuto()))
		assert.NoError(t, err)
		assert.Len(t, lists, 0)
	})

	// Test case 7: List with a combination of options (e.g., active auto workloads)
	t.Run("List with combined options (active auto workloads)", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.ActiveWorkloads()).AddFilter(options.IsAuto()))
		assert.NoError(t, err)
		assert.Len(t, lists, 2)
		// Ensure stable order for assertion
		assert.ElementsMatch(t, []model.WorkloadTest{workload1, workload4}, lists)
	})

	// Test case 8: List with a combination of options (e.g., active workloads by name)
	t.Run("List with combined options (active workloads by name)", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.ActiveWorkloads()).AddFilter(options.WorkloadName("running-manual-workload")))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Equal(t, workload5.ID, lists[0].ID)
	})

	// Test case 9: List with a combination of options (e.g., completed workloads by workload ID)
	t.Run("List with combined options (completed workloads by workload ID)", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.CompletedStatus()).AddFilter(options.WorkloadID(102)))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Equal(t, workload2.ID, lists[0].ID)
	})

	// Test case 11: List with a combination of options (e.g., specific workload Name and Active)
	t.Run("List with combined options (specific workload Name and Active)", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadName("running-manual-workload")).AddFilter(options.ActiveWorkloads()))
		assert.NoError(t, err)
		assert.Len(t, lists, 1)
		assert.Equal(t, workload5.ID, lists[0].ID)
	})

	// Test case 12: List with multiple workload IDs
	t.Run("List with multiple workload IDs", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadID(101, 103)))
		assert.NoError(t, err)
		assert.Len(t, lists, 2)
		assert.ElementsMatch(t, []model.WorkloadTest{workload1, workload3}, lists)
	})

	// Test case 13: List with multiple workload Names
	t.Run("List with multiple workload Names", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadName("workload-a", "running-manual-workload")))
		assert.NoError(t, err)
		assert.ElementsMatch(t, []model.WorkloadTest{workload1, workload5}, lists)
	})

	// Test case 14: List with a non-existent Workload ID
	t.Run("List with non-existent Workload ID", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadID(999)))
		assert.NoError(t, err)
		assert.Len(t, lists, 0)
	})

	// Test case 15: List with a non-existent Workload Name
	t.Run("List with non-existent Workload Name", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadName("NonExistentWorkload")))
		assert.NoError(t, err)
		assert.Len(t, lists, 0)
	})

	// Test case 16: List with combined options resulting in no matches
	t.Run("List with combined options no matches", func(t *testing.T) {
		lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.WorkloadID(101)).AddFilter(options.WorkloadName("NonExistentWorkload")))
		assert.NoError(t, err)
		assert.Len(t, lists, 0)
	})
}

// Test that can run in parallel to verify isolation
func TestWorkloadTestRepo_ParallelIsolation(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	t.Run("Parallel1", func(t *testing.T) {
		t.Parallel()

		db, cleanup := setupTestDB(t)
		defer cleanup()

		repo := NewWorkloadTest(db)
		ctx := context.Background()

		workload := model.NewWorkloadTest("Parallel Test 1", 111, nil, false, "parallel1@example.com")
		_, err := repo.Create(ctx, workload)
		require.NoError(t, err)

		// This should only find our workload
		active, err := repo.ListActive(ctx)
		require.NoError(t, err)
		assert.Len(t, active, 1)
		assert.Equal(t, "Parallel Test 1", active[0].Name)
	})

	t.Run("Parallel2", func(t *testing.T) {
		t.Parallel()

		db, cleanup := setupTestDB(t)
		defer cleanup()

		repo := NewWorkloadTest(db)
		ctx := context.Background()

		workload := model.NewWorkloadTest("Parallel Test 2", 222, nil, false, "parallel2@example.com")
		_, err := repo.Create(ctx, workload)
		require.NoError(t, err)

		// This should only find our workload, not the one from Parallel1
		active, err := repo.ListActive(ctx)
		require.NoError(t, err)
		assert.Len(t, active, 1)
		assert.Equal(t, "Parallel Test 2", active[0].Name)
	})
}

func TestWorkloadTestRepo_DBStoreTagsPreservation(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workload with a cluster containing fields with dbstore tags
	workload := model.NewWorkloadTest("Test DBStore Tags", 123, model.WorkloadParams{
		{Name: "param1", Value: "value1"},
	}, false, "dbstore@example.com")
	workload.WorkloadName = "workload_name"

	// Set up cluster with all fields including those with json:"-" and dbstore tags
	cluster := &model.TiDBCluster{
		TestbedID:           456,
		TestbedName:         "test-testbed",
		TestbedPlanExecID:   789,
		TiWorkloadClusterID: "workload-cluster-123",
		TiDBCloudOrgID:      "101112",
		TiDBCloudClusterID:  "test-org",
	}
	workload.Cluster = cluster

	// Create a task with CreateClusterResult containing the cluster
	task := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypeCreateCluster,
			RelatedServiceID: "tcms-workflow-id",
			Phase:            model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				Status:  model.ExecStatusCompleted,
				Message: "cluster created",
			},
			Progress: 1.0,
		},
		Result: model.CreateClusterResult{
			Cluster: cluster,
		},
	}
	workload.Tasks = append(workload.Tasks, task)

	task2 := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypePrepareData,
			RelatedServiceID: "tcms-workflow-id",
			Phase:            model.WorkloadTaskPhasePending,
			ExecBase: model.ExecBase{
				Status:  model.ExecStatusCompleted,
				Message: "data prepared",
			},
			Progress: 1.0,
		},
		Result: model.PrepareDataResult{
			DataSize:               1000000000,
			TableCount:             10,
			DBHost:                 "127.0.0.1",
			DBPort:                 4000,
			PDHost:                 "127.0.0.1",
			PDPort:                 2379,
			TiWorkloadClusterID:    "workload-cluster-123",
			TiWorkloadWorkloadName: "workload-name",
		},
	}
	workload.Tasks = append(workload.Tasks, task2)

	task3 := model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Typ:              model.WorkloadTaskTypeRunTraffic,
			RelatedServiceID: "tcms-workflow-id",
			Phase:            model.WorkloadTaskPhaseRunning,
			ExecBase: model.ExecBase{
				Status:  model.ExecStatusPending,
				Message: "traffic running",
			},
			Progress: 0.0,
		},
		Result: model.RunTrafficResult{},
	}
	workload.Tasks = append(workload.Tasks, task3)

	// Test Create - should preserve cluster fields with dbstore tags
	created, err := repo.Create(ctx, workload)
	require.NoError(t, err)

	// Verify cluster fields are preserved in the main workload record
	require.NotNil(t, created.Cluster)
	assert.Equal(t, cluster.TestbedID, created.Cluster.TestbedID)
	assert.Equal(t, cluster.TestbedName, created.Cluster.TestbedName)
	assert.Equal(t, cluster.TestbedPlanExecID, created.Cluster.TestbedPlanExecID)
	assert.Equal(t, cluster.TiWorkloadClusterID, created.Cluster.TiWorkloadClusterID)
	assert.Equal(t, cluster.TiDBCloudOrgID, created.Cluster.TiDBCloudOrgID)
	assert.Equal(t, cluster.TiDBCloudClusterID, created.Cluster.TiDBCloudClusterID)

	// Test Get - should retrieve cluster fields with dbstore tags
	retrieved, err := repo.Get(ctx, workload.ID)
	require.NoError(t, err)

	// Verify cluster fields are preserved during retrieval
	require.NotNil(t, retrieved.Cluster)
	assert.Equal(t, cluster.TestbedID, retrieved.Cluster.TestbedID)
	assert.Equal(t, cluster.TestbedName, retrieved.Cluster.TestbedName)
	assert.Equal(t, cluster.TestbedPlanExecID, retrieved.Cluster.TestbedPlanExecID)
	assert.Equal(t, cluster.TiWorkloadClusterID, retrieved.Cluster.TiWorkloadClusterID)
	assert.Equal(t, cluster.TiDBCloudOrgID, retrieved.Cluster.TiDBCloudOrgID)
	assert.Equal(t, cluster.TiDBCloudClusterID, retrieved.Cluster.TiDBCloudClusterID)

	// Verify task result cluster fields are preserved
	require.Len(t, retrieved.Tasks, 3)
	taskResult, ok := retrieved.Tasks[0].Result.(model.CreateClusterResult)
	require.True(t, ok, "Task result should be CreateClusterResult")
	require.NotNil(t, taskResult.Cluster)

	// Check that fields with dbstore tags are preserved in task results
	assert.Equal(t, cluster.TestbedID, taskResult.Cluster.TestbedID)
	assert.Equal(t, cluster.TestbedName, taskResult.Cluster.TestbedName)
	assert.Equal(t, cluster.TestbedPlanExecID, taskResult.Cluster.TestbedPlanExecID)
	assert.Equal(t, cluster.TiWorkloadClusterID, taskResult.Cluster.TiWorkloadClusterID)
	assert.Equal(t, cluster.TiDBCloudOrgID, taskResult.Cluster.TiDBCloudOrgID)
	assert.Equal(t, cluster.TiDBCloudClusterID, taskResult.Cluster.TiDBCloudClusterID)

	// Test Update - should preserve cluster fields with dbstore tags during updates
	workload.Name = "Updated Test DBStore Tags"
	workload.Cluster.TestbedName = "updated-testbed"
	workload.Cluster.TiDBCloudOrgID = "updated-org"

	updated, err := repo.Update(ctx, workload)
	require.NoError(t, err)

	// Verify updated cluster fields are preserved
	require.NotNil(t, updated.Cluster)
	assert.Equal(t, cluster.TestbedID, updated.Cluster.TestbedID)                     // Unchanged
	assert.Equal(t, "updated-testbed", updated.Cluster.TestbedName)                   // Updated
	assert.Equal(t, cluster.TestbedPlanExecID, updated.Cluster.TestbedPlanExecID)     // Unchanged
	assert.Equal(t, cluster.TiWorkloadClusterID, updated.Cluster.TiWorkloadClusterID) // Unchanged
	assert.Equal(t, cluster.TiDBCloudOrgID, updated.Cluster.TiDBCloudOrgID)           // Unchanged
	assert.Equal(t, "updated-org", updated.Cluster.TiDBCloudOrgID)                    // Updated
	assert.Equal(t, cluster.TiDBCloudClusterID, updated.Cluster.TiDBCloudClusterID)   // Unchanged

	// Final verification with another Get operation
	final, err := repo.Get(ctx, workload.ID)
	require.NoError(t, err)

	require.NotNil(t, final.Cluster)
	assert.Equal(t, cluster.TestbedID, final.Cluster.TestbedID)
	assert.Equal(t, "updated-testbed", final.Cluster.TestbedName)
	assert.Equal(t, cluster.TestbedPlanExecID, final.Cluster.TestbedPlanExecID)
	assert.Equal(t, cluster.TiWorkloadClusterID, final.Cluster.TiWorkloadClusterID)
	assert.Equal(t, cluster.TiDBCloudOrgID, final.Cluster.TiDBCloudOrgID)
	assert.Equal(t, "updated-org", final.Cluster.TiDBCloudOrgID)
	assert.Equal(t, cluster.TiDBCloudClusterID, final.Cluster.TiDBCloudClusterID)
}

func TestWorkloadTestRepo_List(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	db, cleanup := setupTestDB(t)
	defer cleanup()

	repo := NewWorkloadTest(db)
	ctx := context.Background()

	// Create test workloads with different statuses
	workload1 := model.NewWorkloadTest("Active Workload 1", 123, nil, false, "active1@example.com")
	workload1.Status = model.ExecStatusRunning

	workload2 := model.NewWorkloadTest("Active Workload 2", 124, nil, false, "active2@example.com")
	now := time.Now().UTC()
	workload2.Tasks = append(workload2.Tasks, model.WorkloadTask[any]{
		WorkloadTaskBase: model.WorkloadTaskBase{
			Phase: model.WorkloadTaskPhaseRunning,
			Typ:   model.WorkloadTaskTypeCreateCluster,
			ExecBase: model.ExecBase{
				Status:     model.ExecStatusRunning,
				CreatedAt:  now,
				UpdatedAt:  &now,
				StartedAt:  &now,
				FinishedAt: &now,
				Message:    "failed to create cluster",
			},
		},
		Result: model.CreateClusterResult{
			Cluster: &model.TiDBCluster{
				TestbedID:           125,
				TestbedName:         "test-testbed",
				TestbedPlanExecID:   789,
				TiWorkloadClusterID: "workload-cluster-123",
				TiDBCloudOrgID:      "test-org",
				TiDBCloudClusterID:  "test-project",
			},
		},
	})
	workload2.Status = model.ExecStatusPending

	workload3 := model.NewWorkloadTest("Inactive Workload", 125, nil, false, "inactive@example.com")

	workload3.Status = model.ExecStatusCompleted

	// Create workloads
	_, err := repo.Create(ctx, workload1)
	require.NoError(t, err)
	workload2, err = repo.Create(ctx, workload2)
	require.NoError(t, err)
	workload3, err = repo.Create(ctx, workload3)
	require.NoError(t, err)

	// Test ListActive
	active, err := repo.ListActive(ctx)
	assert.NoError(t, err)
	assert.Len(t, active, 2) // Only pending and running workloads

	lists, err := repo.List(ctx, options.NewListWorkloadTestOption().AddFilter(options.ActiveWorkloads()))
	require.NoError(t, err)
	assert.Len(t, lists, 2)

	if !reflect.DeepEqual(active, lists) {
		t.Errorf("WorkloadTestRepo.List() got = %v, %v", lists, active)
	}
}

func TestRealDB_GetWorkload(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	// Base configuration to connect to MySQL server (without specific database)
	baseCfg := config.DatabaseConfig{
		Host:         "...",
		Port:         4000,
		User:         "...",
		Password:     "...",
		TLSMode:      "skip-verify",
		Database:     "pov_service", // Connect without database first
		MaxOpenConns: 5,
		MaxIdleConns: 2,
	}

	database, err := NewDB(baseCfg)
	assert.Nil(t, err)

	testRepo := NewWorkloadTest(database)

	start := time.Now()
	_, err = testRepo.Get(context.Background(), "83a96fc2-2b7e-4c41-a7b2-d42f3fbff135")

	assert.Nil(t, err)
	elapsed := time.Since(start).Seconds()
	fmt.Printf("GetWorkloadTest took %.2f \n", elapsed)

	start = time.Now()
	tx, err := testRepo.BeginTx(context.Background())
	assert.Nil(t, err)
	_, err = testRepo.GetAndLockForUpdateInTx(context.Background(), tx, "83a96fc2-2b7e-4c41-a7b2-d42f3fbff135")
	assert.Nil(t, err)
	elapsed = time.Since(start).Seconds()
	fmt.Printf("GetWorkloadTest took %.2f \n", elapsed)
}