package mysql

import (
	"context"
	"testing"
	"time"

	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/pov-service/internal/model"
)

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

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

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

	// Test data
	var workloadID int64 = 111
	testDBs := []model.DataBase{
		{
			Name: "test_db",
			Tables: []model.Table{
				{
					Name: "users",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "name", DataType: "VARCHAR(255)"},
							{Col: "email", DataType: "VARCHAR(255)"},
						},
						Rows: [][]string{
							{"1", "Alice", "alice@example.com"},
							{"2", "Bob", "bob@example.com"},
						},
					},
				},
				{
					Name: "orders",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "user_id", DataType: "INT"},
							{Col: "amount", DataType: "DECIMAL(10,2)"},
						},
						Rows: [][]string{
							{"1", "1", "99.99"},
							{"2", "2", "149.99"},
						},
					},
				},
			},
		},
		{
			Name: "analytics",
			Tables: []model.Table{
				{
					Name: "events",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "BIGINT", Key: "PRIMARY"},
							{Col: "event_type", DataType: "VARCHAR(50)"},
							{Col: "timestamp", DataType: "DATETIME"},
						},
						Rows: [][]string{
							{"1", "click", "2024-01-01 10:00:00"},
							{"2", "view", "2024-01-01 10:01:00"},
						},
					},
				},
			},
		},
	}

	t.Run("Create valid workload samples", func(t *testing.T) {
		// Begin transaction
		tx, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx := &mysqlTx{tx: tx}

		// Create workload samples
		version, err := repo.CreateInTx(ctx, mysqlTx, workloadID, testDBs)
		assert.NoError(t, err)
		assert.Greater(t, version, int64(0))

		// Commit transaction
		err = tx.Commit()
		assert.NoError(t, err)

		// Verify data was inserted
		var count int
		err = db.GetContext(ctx, &count, "SELECT COUNT(*) FROM workload_sample WHERE workload_id = ?", workloadID)
		assert.NoError(t, err)
		assert.Equal(t, 3, count) // 2 tables in test_db + 1 table in analytics
	})

	t.Run("Create with empty database list", func(t *testing.T) {
		tx, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx := &mysqlTx{tx: tx}

		version, err := repo.CreateInTx(ctx, mysqlTx, 0001, []model.DataBase{})
		assert.NoError(t, err)
		assert.Equal(t, version, int64(0))

		err = tx.Commit()
		assert.NoError(t, err)

		// Should have no rows inserted
		var count int
		err = db.GetContext(ctx, &count, "SELECT COUNT(*) FROM workload_sample WHERE workload_id = ?", 0001)
		assert.NoError(t, err)
		assert.Equal(t, 0, count)
	})

	t.Run("Create with invalid transaction type", func(t *testing.T) {
		_, err := repo.CreateInTx(ctx, nil, workloadID, testDBs)
		assert.Error(t, err)
		assert.Contains(t, err.Error(), "invalid transaction type")
	})
}

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

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

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

	// Setup test data
	var workloadID int64 = 111

	testDBs := []model.DataBase{
		{
			Name: "test_db",
			Tables: []model.Table{
				{
					Name: "users",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "name", DataType: "VARCHAR(255)"},
						},
						Rows: [][]string{{"1", "Alice"}},
					},
				},
				{
					Name: "products",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "name", DataType: "VARCHAR(255)"},
							{Col: "price", DataType: "DECIMAL(10,2)"},
						},
						Rows: [][]string{{"1", "Laptop", "999.99"}},
					},
				},
			},
		},
		{
			Name: "inventory",
			Tables: []model.Table{
				{
					Name: "items",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "sku", DataType: "VARCHAR(50)"},
						},
						Rows: [][]string{{"1", "SKU-123"}},
					},
				},
			},
		},
	}

	// Insert test data
	tx, err := db.BeginTxx(ctx, nil)
	require.NoError(t, err)
	mysqlTx := &mysqlTx{tx: tx}

	_, err = repo.CreateInTx(ctx, mysqlTx, workloadID, testDBs)
	require.NoError(t, err)

	err = tx.Commit()
	require.NoError(t, err)

	t.Run("Select schema for existing workload", func(t *testing.T) {
		res, err := repo.SelectLatestSchema(ctx, workloadID)
		assert.NoError(t, err)

		dbs := res.Databases
		assert.Len(t, dbs, 2)

		// Verify database structure
		dbMap := make(map[string]model.DataBase)
		for _, db := range dbs {
			dbMap[db.Name] = db
		}

		assert.Contains(t, dbMap, "test_db")
		assert.Contains(t, dbMap, "inventory")

		// Verify test_db structure
		testDB := dbMap["test_db"]
		assert.Len(t, testDB.Tables, 2)
		tableMap := make(map[string]model.Table)
		for _, table := range testDB.Tables {
			tableMap[table.Name] = table
		}
		assert.Contains(t, tableMap, "users")
		assert.Contains(t, tableMap, "products")

		// Verify users table columns
		usersTable := tableMap["users"]
		assert.Len(t, usersTable.Columns, 2)
		assert.Equal(t, "id", usersTable.Columns[0].Col)
		assert.Equal(t, "name", usersTable.Columns[1].Col)

		// Verify no rows data in schema selection
		assert.Empty(t, usersTable.Rows)
	})

	t.Run("Select schema for non-existent workload", func(t *testing.T) {
		dbs, err := repo.SelectLatestSchema(ctx, -1)
		assert.Error(t, err)
		assert.Nil(t, dbs)
		assert.Contains(t, err.Error(), "not found")
	})
}

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

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

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

	// Setup test data
	var workloadID int64 = 111

	testDBs := []model.DataBase{
		{
			Name: "test_db",
			Tables: []model.Table{
				{
					Name: "users",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
							{Col: "name", DataType: "VARCHAR(255)"},
							{Col: "email", DataType: "VARCHAR(255)"},
						},
						Rows: [][]string{
							{"1", "Alice", "alice@example.com"},
							{"2", "Bob", "bob@example.com"},
							{"3", "Charlie", "charlie@example.com"},
						},
					},
				},
			},
		},
	}

	// Insert test data
	tx, err := db.BeginTxx(ctx, nil)
	require.NoError(t, err)
	mysqlTx := &mysqlTx{tx: tx}

	_, err = repo.CreateInTx(ctx, mysqlTx, workloadID, testDBs)
	require.NoError(t, err)

	err = tx.Commit()
	require.NoError(t, err)

	t.Run("Select existing table", func(t *testing.T) {
		table, err := repo.SelectTable(ctx, workloadID, "test_db", "users")
		assert.NoError(t, err)
		assert.Equal(t, "users", table.Name)
		assert.Len(t, table.Columns, 3)
		assert.Len(t, table.Rows, 3)

		// Verify columns
		expectedColumns := []model.Column{
			{Col: "id", DataType: "INT", Key: "PRIMARY"},
			{Col: "name", DataType: "VARCHAR(255)"},
			{Col: "email", DataType: "VARCHAR(255)"},
		}
		assert.Equal(t, expectedColumns, table.Columns)

		// Verify rows
		expectedRows := [][]string{
			{"1", "Alice", "alice@example.com"},
			{"2", "Bob", "bob@example.com"},
			{"3", "Charlie", "charlie@example.com"},
		}
		assert.Equal(t, expectedRows, table.Rows)
	})

	t.Run("Select non-existent table", func(t *testing.T) {
		table, err := repo.SelectTable(ctx, workloadID, "test_db", "nonexistent")
		assert.Error(t, err)
		assert.Contains(t, err.Error(), "not found")
		assert.Equal(t, model.Table{}, table)
	})

	t.Run("Select table from non-existent database", func(t *testing.T) {
		table, err := repo.SelectTable(ctx, workloadID, "nonexistent_db", "users")
		assert.Error(t, err)
		assert.Contains(t, err.Error(), "not found")
		assert.Equal(t, model.Table{}, table)
	})

	t.Run("Select table with empty rows", func(t *testing.T) {
		var emptyWorkloadID int64 = 11111
		emptyDBs := []model.DataBase{
			{
				Name: "empty_db",
				Tables: []model.Table{
					{
						Name: "empty_table",
						Data: model.Data{
							Columns: []model.Column{
								{Col: "id", DataType: "INT", Key: "PRIMARY"},
							},
							Rows: [][]string{}, // Empty rows
						},
					},
				},
			},
		}

		mysqlTx, err := repo.BeginTx(ctx)
		require.NoError(t, err)

		_, err = repo.CreateInTx(ctx, mysqlTx, emptyWorkloadID, emptyDBs)
		require.NoError(t, err)

		err = tx.Commit()
		require.NoError(t, err)

		table, err := repo.SelectTable(ctx, emptyWorkloadID, "empty_db", "empty_table")
		assert.NoError(t, err)
		assert.Equal(t, "empty_table", table.Name)
		assert.Len(t, table.Columns, 1)
		assert.Empty(t, table.Rows)
	})
}

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

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

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

	var workloadID int64 = 111

	testDBs := []model.DataBase{
		{
			Name: "test_db",
			Tables: []model.Table{
				{
					Name: "users",
					Data: model.Data{
						Columns: []model.Column{
							{Col: "id", DataType: "INT", Key: "PRIMARY"},
						},
						Rows: [][]string{{"1"}},
					},
				},
			},
		},
	}

	t.Run("Delete outdated versions", func(t *testing.T) {
		// Create version 1
		tx1, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx1 := &mysqlTx{tx: tx1}

		version1, err := repo.CreateInTx(ctx, mysqlTx1, workloadID, testDBs)
		require.NoError(t, err)

		err = tx1.Commit()
		require.NoError(t, err)

		// Create version 2
		tx2, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx2 := &mysqlTx{tx: tx2}

		version2, err := repo.CreateInTx(ctx, mysqlTx2, workloadID, testDBs)
		require.NoError(t, err)
		assert.Greater(t, version2, version1)

		err = tx2.Commit()
		require.NoError(t, err)

		// Create version 3
		tx3, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx3 := &mysqlTx{tx: tx3}

		version3, err := repo.CreateInTx(ctx, mysqlTx3, workloadID, testDBs)
		require.NoError(t, err)
		assert.Greater(t, version3, version2)

		err = tx3.Commit()
		require.NoError(t, err)

		// Verify we have 3 versions
		var count int
		err = db.GetContext(ctx, &count, "SELECT COUNT(*) FROM workload_sample WHERE workload_id = ?", workloadID)
		assert.NoError(t, err)
		assert.Equal(t, 3, count)

		// Delete outdated versions (keep only version 3)
		tx4, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx4 := &mysqlTx{tx: tx4}

		err = repo.DeleteOutdatedInTx(ctx, mysqlTx4, workloadID, version3)
		assert.NoError(t, err)

		err = tx4.Commit()
		require.NoError(t, err)

		// Verify only version 3 remains
		err = db.GetContext(ctx, &count, "SELECT COUNT(*) FROM workload_sample WHERE workload_id = ?", workloadID)
		assert.NoError(t, err)
		assert.Equal(t, 1, count)

		// Verify the remaining version is 3
		var remainingVersion int64
		err = db.GetContext(ctx, &remainingVersion, "SELECT version FROM workload_sample WHERE workload_id = ? LIMIT 1", workloadID)
		assert.NoError(t, err)
		assert.Equal(t, version3, remainingVersion)
	})

	t.Run("Delete with invalid transaction type", func(t *testing.T) {
		invalidTx := &mysqlTx{tx: nil} // Use valid type but nil tx
		err := repo.DeleteOutdatedInTx(ctx, invalidTx, workloadID, 123)
		assert.Error(t, err)
	})

	t.Run("Delete with no matching workload", func(t *testing.T) {
		tx, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx := &mysqlTx{tx: tx}

		// Should not error even if no workloads match
		err = repo.DeleteOutdatedInTx(ctx, mysqlTx, 0010101, 999)
		assert.NoError(t, err)

		// Verify no rows were deleted
		var count int
		err = db.GetContext(ctx, &count, "SELECT COUNT(*) FROM workload_sample WHERE workload_id = ?", "non-existent-workload")
		assert.NoError(t, err)
		assert.Equal(t, 0, count)

		err = tx.Commit()
		assert.NoError(t, err)
	})
}

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

	repo := &workloadSampleRepo{} // We can test dbToModel without DB connection

	testTime := time.Now().UTC()

	testCases := []struct {
		name        string
		dbSamples   []WorkloadSample
		expected    []model.DataBase
		expectError bool
	}{
		{
			name: "Single database with single table",
			dbSamples: []WorkloadSample{
				{
					ID:          1,
					WorkloadID:  123,
					Version:     1,
					CreatedAt:   testTime,
					DBName:      "test_db",
					TableName:   "users",
					ColumnsJSON: `[{"col":"id","data_type":"INT","key":"PRIMARY"},{"col":"name","data_type":"VARCHAR(255)"}]`,
					RowsJSON:    `[["1","Alice"],["2","Bob"]]`,
				},
			},
			expected: []model.DataBase{
				{
					Name: "test_db",
					Tables: []model.Table{
						{
							Name: "users",
							Data: model.Data{
								Columns: []model.Column{
									{Col: "id", DataType: "INT", Key: "PRIMARY"},
									{Col: "name", DataType: "VARCHAR(255)"},
								},
								Rows: [][]string{{"1", "Alice"}, {"2", "Bob"}},
							},
						},
					},
				},
			},
			expectError: false,
		},
		{
			name: "Multiple databases with multiple tables",
			dbSamples: []WorkloadSample{
				{
					WorkloadID:  123,
					DBName:      "db1",
					TableName:   "table1",
					ColumnsJSON: `[{"col":"col1","data_type":"INT"}]`,
					RowsJSON:    `[["1"]]`,
				},
				{
					WorkloadID:  123,
					DBName:      "db1",
					TableName:   "table2",
					ColumnsJSON: `[{"col":"col1","data_type":"VARCHAR"}]`,
					RowsJSON:    `[["test"]]`,
				},
				{
					WorkloadID:  123,
					DBName:      "db2",
					TableName:   "table3",
					ColumnsJSON: `[{"col":"col1","data_type":"DECIMAL"}]`,
					RowsJSON:    `[["99.99"]]`,
				},
			},
			expected: []model.DataBase{
				{
					Name: "db1",
					Tables: []model.Table{
						{Name: "table1", Data: model.Data{Columns: []model.Column{{Col: "col1", DataType: "INT"}}, Rows: [][]string{{"1"}}}},
						{Name: "table2", Data: model.Data{Columns: []model.Column{{Col: "col1", DataType: "VARCHAR"}}, Rows: [][]string{{"test"}}}},
					},
				},
				{
					Name: "db2",
					Tables: []model.Table{
						{Name: "table3", Data: model.Data{Columns: []model.Column{{Col: "col1", DataType: "DECIMAL"}}, Rows: [][]string{{"99.99"}}}},
					},
				},
			},
			expectError: false,
		},
		{
			name: "Empty columns and rows",
			dbSamples: []WorkloadSample{
				{
					WorkloadID:  123,
					DBName:      "empty_db",
					TableName:   "empty_table",
					ColumnsJSON: "",
					RowsJSON:    "",
				},
			},
			expected: []model.DataBase{
				{
					Name: "empty_db",
					Tables: []model.Table{
						{Name: "empty_table", Data: model.Data{Columns: []model.Column{}, Rows: [][]string{}}},
					},
				},
			},
			expectError: false,
		},
		{
			name: "Invalid JSON in columns",
			dbSamples: []WorkloadSample{
				{
					WorkloadID:  123,
					DBName:      "test_db",
					TableName:   "test_table",
					ColumnsJSON: "invalid json",
					RowsJSON:    `[["1"]]`,
				},
			},
			expected:    nil,
			expectError: true,
		},
		{
			name: "Invalid JSON in rows",
			dbSamples: []WorkloadSample{
				{
					WorkloadID:  123,
					DBName:      "test_db",
					TableName:   "test_table",
					ColumnsJSON: `[{"col":"id","data_type":"INT"}]`,
					RowsJSON:    "invalid json",
				},
			},
			expected:    nil,
			expectError: true,
		},
	}

	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			result, _, err := repo.dbToModel(tc.dbSamples)

			if tc.expectError {
				assert.Error(t, err)
				assert.Nil(t, result)
			} else {
				assert.NoError(t, err)
				assert.Equal(t, tc.expected, result)
			}
		})
	}
}

func TestWorkloadSampleRepo_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 := NewWorkloadSampleRepo(db)
		ctx := context.Background()

		var workloadID int64 = 111
		testDBs := []model.DataBase{
			{
				Name: "parallel_db",
				Tables: []model.Table{
					{
						Name: "test_table",
						Data: model.Data{
							Columns: []model.Column{
								{Col: "id", DataType: "INT", Key: "PRIMARY"},
							},
							Rows: [][]string{{"1"}},
						},
					},
				},
			},
		}

		tx, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx := &mysqlTx{tx: tx}

		_, err = repo.CreateInTx(ctx, mysqlTx, workloadID, testDBs)
		require.NoError(t, err)

		err = tx.Commit()
		require.NoError(t, err)

		// Verify this test only sees its own data
		res, err := repo.SelectLatestSchema(ctx, workloadID)
		assert.NoError(t, err)

		dbs := res.Databases

		assert.Len(t, dbs, 1)
		assert.Equal(t, "parallel_db", dbs[0].Name)
	})

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

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

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

		var workloadID int64 = 111

		testDBs := []model.DataBase{
			{
				Name: "another_parallel_db",
				Tables: []model.Table{
					{
						Name: "different_table",
						Data: model.Data{
							Columns: []model.Column{
								{Col: "name", DataType: "VARCHAR(255)"},
							},
							Rows: [][]string{{"test"}},
						},
					},
				},
			},
		}

		tx, err := db.BeginTxx(ctx, nil)
		require.NoError(t, err)
		mysqlTx := &mysqlTx{tx: tx}

		_, err = repo.CreateInTx(ctx, mysqlTx, workloadID, testDBs)
		require.NoError(t, err)

		err = tx.Commit()
		require.NoError(t, err)

		// Verify this test only sees its own data
		res, err := repo.SelectLatestSchema(ctx, workloadID)
		assert.NoError(t, err)

		dbs := res.Databases
		assert.Len(t, dbs, 1)
		assert.Equal(t, "another_parallel_db", dbs[0].Name)
	})
}