package database

import (
	"context"
	"database/sql"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/AgentTeam/internal/store/automation"
	"github.com/tidbcloud/AgentTeam/internal/store/kb"
	"gorm.io/gorm"
)

// This integration test intentionally creates only the source-scoped tables
// under test. It validates TiDB's production FOR UPDATE behavior independently
// from the application migration suite.
func TestTiDBKBSourceRowSerializesMirrorAndCredentialMutation(t *testing.T) {
	db := openTiDBMinimalTestDB(t)
	require.NoError(t, db.AutoMigrate(&kb.Source{}, &kb.SourceMirror{}, &automation.AutomationSecret{}))
	ctx := context.Background()
	now := time.Date(2026, 7, 24, 12, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&kb.Source{
		ID: "src_1", WorkspaceID: "wsp_1", KnowledgeBaseID: "kb_1", SourceType: "codebase", Status: "ready", Name: "AgentTeam", Config: "{}", CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now,
	}).Error)
	require.NoError(t, db.Create(&kb.SourceMirror{
		SourceID: "src_1", WorkspaceID: "wsp_1", KnowledgeBaseID: "kb_1", UpstreamType: kb.SourceMirrorUpstreamTypeGenericGit, RemoteURL: "https://example.com/old.git", DefaultRef: "main", Enabled: true, SyncStatus: kb.SourceMirrorSyncStatusIdle, CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now,
	}).Error)

	firstLocked := make(chan struct{})
	releaseFirst := make(chan struct{})
	firstDone := make(chan error, 1)
	go func() {
		firstDone <- db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
			store := kb.New(tx)
			if _, err := store.LockSource(ctx, "wsp_1", "kb_1", "src_1"); err != nil {
				return err
			}
			close(firstLocked)
			<-releaseFirst
			return tx.Model(&kb.SourceMirror{}).Where("source_id = ?", "src_1").Updates(map[string]any{
				"remote_url": "https://example.com/winner.git", "updated_by": "winner", "updated_at": now.Add(time.Second),
			}).Error
		})
	}()
	<-firstLocked

	secondAcquired := make(chan struct{})
	secondDone := make(chan error, 1)
	go func() {
		secondDone <- db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
			store := kb.New(tx)
			if _, err := store.LockSource(ctx, "wsp_1", "kb_1", "src_1"); err != nil {
				return err
			}
			close(secondAcquired)
			var mirror kb.SourceMirror
			return tx.Where("source_id = ?", "src_1").First(&mirror).Error
		})
	}()

	select {
	case <-secondAcquired:
		t.Fatal("second source mutation acquired FOR UPDATE before the first transaction committed")
	case <-time.After(200 * time.Millisecond):
	}
	close(releaseFirst)
	require.NoError(t, <-firstDone)
	select {
	case <-secondAcquired:
	case <-time.After(5 * time.Second):
		t.Fatal("second source mutation did not acquire FOR UPDATE after the first transaction committed")
	}
	require.NoError(t, <-secondDone)
}

// A fresh production migration must remain valid on TiDB, whose TEXT columns
// reject DEFAULT clauses even though SQLite accepts them.
func TestTiDBFreshMigrationCreatesUADashboardManifestColumn(t *testing.T) {
	db := openTiDBMinimalTestDB(t)
	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasColumn(&kb.SourceRevision{}, "ua_dashboard_manifest_json"))
	requireUADashboardManifestColumn(t, db, "varbinary(16384)")
}

func requireUADashboardManifestColumn(t *testing.T, db *gorm.DB, wantType string) {
	t.Helper()
	var field, columnType, null, key, extra, privileges, comment string
	var collation, defaultValue sql.NullString
	err := db.Raw("SHOW FULL COLUMNS FROM knowledge_base_source_revisions WHERE Field = ?", "ua_dashboard_manifest_json").Row().Scan(
		&field, &columnType, &collation, &null, &key, &defaultValue, &extra, &privileges, &comment,
	)
	require.NoError(t, err)
	require.Equal(t, wantType, strings.ToLower(columnType))
	require.False(t, collation.Valid)
	require.Equal(t, "NO", null)
	require.True(t, defaultValue.Valid)
	require.Empty(t, defaultValue.String)
}

func openTiDBMinimalTestDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := tidbTestDSN(t)
	isolatedDSN, cleanup := createIsolatedTiDBDatabase(t, dsn)
	t.Cleanup(func() {
		ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
		defer cancel()
		require.NoError(t, cleanup(ctx))
	})
	db, err := open(Config{DSN: isolatedDSN}, false)
	require.NoError(t, err)
	t.Cleanup(func() { require.NoError(t, Close(db)) })
	return db
}