package database

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

	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/AgentTeam/internal/store/automation"
	"github.com/tidbcloud/AgentTeam/internal/store/kb"
	"github.com/tidbcloud/AgentTeam/internal/store/providertokens"
	"github.com/tidbcloud/AgentTeam/internal/store/repocache"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/activity"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/memory"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/repos"
	"gorm.io/gorm"
)

func TestMeshXTextColumnsDoNotDeclareDefaults(t *testing.T) {
	// All TEXT columns in MeshX models must not have DEFAULT values
	// because MySQL/TiDB does not allow DEFAULT on TEXT/BLOB/JSON columns.
	type textFieldSpec struct {
		field    string
		wantType string
	}
	models := []struct {
		typ    reflect.Type
		fields []textFieldSpec
	}{
		{reflect.TypeOf(kb.KnowledgeBaseGroup{}), []textFieldSpec{{"Description", "type:text"}}},
		{reflect.TypeOf(kb.KnowledgeBaseGenerationRequest{}), []textFieldSpec{
			{"Reason", "type:text"}, {"DecisionInputReason", "type:text"}, {"DecisionReason", "type:text"},
		}},
		{reflect.TypeOf(kb.KnowledgeBasePublicationRequest{}), []textFieldSpec{
			{"SnapshotJSON", "type:text"}, {"DecisionInputReason", "type:text"}, {"DecisionReason", "type:text"},
		}},
		{reflect.TypeOf(automation.AutomationRun{}), []textFieldSpec{
			{"InputJSON", "type:text"}, {"OutputJSON", "type:text"},
			{"ErrorJSON", "type:text"}, {"Summary", "type:text"},
			{"Metadata", "type:text"},
		}},
		{reflect.TypeOf(automation.AutomationRepoStatusOutbox{}), []textFieldSpec{{"LastError", "type:text"}}},
		{reflect.TypeOf(automation.AutomationJobRun{}), []textFieldSpec{
			{"InputJSON", "type:text"}, {"OutputJSON", "type:mediumtext"},
			{"ErrorJSON", "type:text"}, {"DependsOnJSON", "type:text"},
		}},
		{reflect.TypeOf(automation.AutomationEvent{}), []textFieldSpec{
			{"Summary", "type:text"}, {"MetadataJSON", "type:text"},
		}},
		{reflect.TypeOf(automation.AutomationJob{}), []textFieldSpec{{"LabelsJSON", "type:text"}}},
		{reflect.TypeOf(automation.AutomationWait{}), []textFieldSpec{
			{"InputJSON", "type:text"}, {"ResultJSON", "type:text"},
		}},
		{reflect.TypeOf(automation.AutomationRunner{}), []textFieldSpec{{"LabelsJSON", "type:text"}}},
		{reflect.TypeOf(automation.AutomationRunnerSession{}), []textFieldSpec{{"LabelsJSON", "type:text"}}},
		{reflect.TypeOf(automation.AutomationActionsRunnerCredential{}), []textFieldSpec{{"PublicKey", "type:text"}}},
		{reflect.TypeOf(automation.AutomationArtifact{}), []textFieldSpec{{"ObjectURL", "type:text"}}},
		{reflect.TypeOf(automation.AutomationSecret{}), []textFieldSpec{{"ValueCiphertext", "type:text"}}},
		{reflect.TypeOf(providertokens.PersonalAccessToken{}), []textFieldSpec{
			{"AllowedUsagesJSON", "type:text"}, {"ValueCiphertext", "type:text"},
		}},
		{reflect.TypeOf(memory.AgentAssetAccessChangeOutbox{}), []textFieldSpec{{"LastError", "type:text"}}},
		{reflect.TypeOf(activity.ResourceActivityEvent{}), []textFieldSpec{{"DetailsJSON", "type:text"}, {"TargetURL", "type:text"}}},
		{reflect.TypeOf(activity.ResourceActivityProjectorCheckpoint{}), []textFieldSpec{{"StateJSON", "type:text"}, {"LastError", "type:text"}}},
		{reflect.TypeOf(repocache.Cache{}), []textFieldSpec{{"Info", "type:json"}}},
	}

	for _, m := range models {
		for _, spec := range m.fields {
			field, ok := m.typ.FieldByName(spec.field)
			require.True(t, ok, "%s.%s should exist", m.typ.Name(), spec.field)
			gormTag := string(field.Tag.Get("gorm"))
			require.Contains(t, gormTag, spec.wantType, "%s.%s should be %s", m.typ.Name(), spec.field, spec.wantType)
			require.NotContains(t, strings.ToLower(gormTag), "default:", "%s.%s must not have default: clause (TEXT columns can't have DEFAULT in MySQL/TiDB)", m.typ.Name(), spec.field)
		}
	}
}

func TestUADashboardManifestSchemaMatchesStoreByteLimit(t *testing.T) {
	field, ok := reflect.TypeOf(kb.SourceRevision{}).FieldByName("UADashboardManifestJSON")
	require.True(t, ok)
	require.Contains(t, strings.ToLower(field.Tag.Get("gorm")), fmt.Sprintf("type:varbinary(%d)", kb.UADashboardManifestMaxBytes))
	require.Contains(t, strings.ToLower(field.Tag.Get("gorm")), "default:''", "binary manifest needs an empty default for old-writer compatibility")
}

func TestAutomationMigrationTextColumnsDoNotDeclareDefaults(t *testing.T) {
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	for _, migration := range migrations {
		for lineNumber, line := range strings.Split(migration.SQL, "\n") {
			lineWithoutComment := strings.SplitN(line, "--", 2)[0]
			normalized := strings.ToUpper(strings.Join(strings.Fields(strings.TrimRight(strings.TrimSpace(lineWithoutComment), ",")), " "))
			if normalized == "" || !strings.Contains(normalized, " DEFAULT") {
				continue
			}
			for _, disallowedType := range []string{" BLOB", " TEXT", " MEDIUMTEXT", " LONGTEXT", " JSON"} {
				require.NotContains(t, normalized, disallowedType, "%s:%d must not declare DEFAULT for BLOB/TEXT/JSON columns", migration.Path, lineNumber+1)
			}
		}
	}
}

func TestIDFieldsUseSize64(t *testing.T) {
	// All ID, principal, and user-reference fields must use size:64.
	// This keeps unique indexes within TiDB's 3072-byte key limit.
	type model struct {
		typ    reflect.Type
		fields []string
	}

	models := []model{
		// kb
		{reflect.TypeOf(kb.KnowledgeBase{}), []string{"WorkspaceID", "GroupID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(kb.KnowledgeBaseGroup{}), []string{"ID", "WorkspaceID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(kb.KnowledgeBaseTag{}), []string{"ID", "WorkspaceID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(kb.KnowledgeBaseTagAssignment{}), []string{"WorkspaceID", "KnowledgeBaseID", "TagID", "CreatedBy"}},
		{reflect.TypeOf(kb.Source{}), []string{"WorkspaceID", "KnowledgeBaseID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(kb.AuditEvent{}), []string{"WorkspaceID", "KnowledgeBaseID", "ActorID"}},
		{reflect.TypeOf(kb.KnowledgeBaseSourceRepositoryAccessOverride{}), []string{"ID", "WorkspaceID", "KnowledgeBaseID", "GrantID", "SourceID", "RepoRole", "SubjectID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(kb.KnowledgeBaseGenerationRequest{}), []string{"ID", "WorkspaceID", "KnowledgeBaseID", "SourceID", "SourceRevisionID", "RequesterID", "ApprovalID", "JobID", "DecidedBy"}},
		{reflect.TypeOf(kb.KnowledgeBasePublicationRequest{}), []string{"ID", "WorkspaceID", "KnowledgeBaseID", "RequesterID", "ApprovalID", "DecidedBy"}},
		{reflect.TypeOf(repos.ResourceRepositoryAccessGrant{}), []string{"ID", "WorkspaceID", "ResourceID", "AGSRepoID", "SubjectID", "IntentID", "CreatedBy", "UpdatedBy", "ReconciledBy", "RevokedBy"}},
		// automations
		{reflect.TypeOf(automation.AutomationRun{}), []string{"WorkspaceID", "OwnerPrincipalID", "CreatedByPrincipalID", "CurrentJobID"}},
		{reflect.TypeOf(automation.AutomationRepoStatusOutbox{}), []string{"ID", "WorkspaceID", "RunID"}},
		{reflect.TypeOf(automation.AutomationRunSlot{}), []string{"WorkspaceID", "RunID"}},
		{reflect.TypeOf(automation.AutomationJobRun{}), []string{"WorkspaceID", "RunID", "JobID"}},
		{reflect.TypeOf(automation.AutomationEvent{}), []string{"WorkspaceID", "RunID", "JobRunID", "ActorPrincipalID"}},
		{reflect.TypeOf(automation.AutomationJob{}), []string{"WorkspaceID", "RunID", "JobRunID"}},
		{reflect.TypeOf(automation.AutomationWait{}), []string{"WorkspaceID", "RunID", "JobRunID", "CreatedBy", "ResolvedBy"}},
		{reflect.TypeOf(automation.AutomationRunner{}), []string{"WorkspaceID", "RunnerID"}},
		{reflect.TypeOf(automation.AutomationRunnerToken{}), []string{"WorkspaceID", "RunnerID", "SessionID"}},
		{reflect.TypeOf(automation.AutomationRunnerSession{}), []string{"WorkspaceID", "RunnerID", "SessionID"}},
		{reflect.TypeOf(automation.AutomationActionsRunnerCredential{}), []string{"WorkspaceID", "RunnerID"}},
		{reflect.TypeOf(automation.AutomationLogChunk{}), []string{"ID", "WorkspaceID", "RunID", "JobRunID"}},
		{reflect.TypeOf(automation.AutomationArtifact{}), []string{"ID", "WorkspaceID", "RunID", "JobRunID"}},
		{reflect.TypeOf(automation.AutomationSecret{}), []string{"WorkspaceID", "CreatedBy", "UpdatedBy"}},
		{reflect.TypeOf(automation.AutomationPinnedRepo{}), []string{"WorkspaceID", "PrincipalID"}},
		{reflect.TypeOf(providertokens.PersonalAccessToken{}), []string{"ID", "WorkspaceID", "OwnerPrincipalID", "CreatedBy", "UpdatedBy"}},
	}

	for _, m := range models {
		for _, fieldName := range m.fields {
			field, ok := m.typ.FieldByName(fieldName)
			require.True(t, ok, "%s.%s should exist", m.typ.Name(), fieldName)
			gormTag := string(field.Tag.Get("gorm"))
			require.Contains(t, gormTag, "size:64", "%s.%s should use size:64", m.typ.Name(), fieldName)
		}
	}
}

func TestPublicRepositoryCacheUniqueURLIndexFitsTiDBKeyLimit(t *testing.T) {
	field, ok := reflect.TypeOf(repocache.Cache{}).FieldByName("NormalizedRemoteURL")
	require.True(t, ok)
	require.Contains(t, field.Tag.Get("gorm"), "size:768")
	require.Contains(t, field.Tag.Get("gorm"), "uniqueIndex")

	generationField, ok := reflect.TypeOf(repocache.Cache{}).FieldByName("ActiveGeneration")
	require.True(t, ok)
	require.Contains(t, generationField.Tag.Get("gorm"), "size:64")
	require.Contains(t, generationField.Tag.Get("gorm"), "default:''")
}

func TestNameAndPathFieldsUse255Or512(t *testing.T) {
	// Name/slug/title/path/ref fields that hold user-visible or external values
	// should keep size:255 or size:512 as appropriate.
	type fieldSpec struct {
		name     string
		wantSize string
	}

	tests := []struct {
		typ    reflect.Type
		fields []fieldSpec
	}{
		{reflect.TypeOf(automation.AutomationRun{}), []fieldSpec{
			{"RepositoryOwner", "size:64"}, {"RepositoryName", "size:128"},
			{"RepositoryFullName", "size:255"}, {"DefinitionPath", "size:512"},
			{"GitRef", "size:255"}, {"CommitSHA", "size:128"},
			{"DefinitionID", "size:255"}, {"Name", "size:255"}, {"RunName", "size:255"},
			{"TriggerRef", "size:512"}, {"TargetRef", "size:512"},
		}},
		{reflect.TypeOf(automation.AutomationRepoStatusOutbox{}), []fieldSpec{
			{"RepositoryOwner", "size:128"}, {"RepositoryName", "size:128"},
			{"CommitSHA", "size:128"}, {"Context", "size:255"},
			{"State", "size:16"}, {"Description", "size:255"},
			{"Status", "size:32"}, {"LeaseOwner", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationWait{}), []fieldSpec{
			{"ActiveKey", "size:512"}, {"ResourceID", "size:512"},
		}},
		{reflect.TypeOf(automation.AutomationJob{}), []fieldSpec{
			{"QueueName", "size:64"}, {"RunnerGroup", "size:128"},
			{"RunnerGroupKey", "size:128"}, {"LabelKey", "size:512"},
			{"IdempotencyKey", "size:255"},
		}},
		{reflect.TypeOf(automation.AutomationRunner{}), []fieldSpec{
			{"Name", "size:255"}, {"RunnerGroup", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationRunnerToken{}), []fieldSpec{
			{"TokenHash", "size:128"}, {"TokenKind", "size:32"},
			{"TokenScope", "size:32"}, {"SessionTokenHash", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationRunnerSession{}), []fieldSpec{
			{"LeaseOwner", "size:128"}, {"RunnerGroup", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationActionsRunnerCredential{}), []fieldSpec{
			{"ClientID", "size:64"},
		}},
		{reflect.TypeOf(automation.AutomationLogChunk{}), []fieldSpec{
			{"Stream", "size:32"}, {"ObjectKey", "size:512"}, {"SHA256", "size:64"},
			{"ContentType", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationArtifact{}), []fieldSpec{
			{"Name", "size:255"}, {"ObjectKey", "size:512"}, {"SHA256", "size:64"},
			{"ContentType", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationSecret{}), []fieldSpec{
			{"RepositoryOwner", "size:128"}, {"RepositoryName", "size:128"},
			{"Name", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationPinnedRepo{}), []fieldSpec{
			{"Owner", "size:128"}, {"Repo", "size:128"},
		}},
		{reflect.TypeOf(automation.AutomationEvent{}), []fieldSpec{
			{"ResourceID", "size:512"},
		}},
		{reflect.TypeOf(providertokens.PersonalAccessToken{}), []fieldSpec{
			{"DisplayName", "size:255"}, {"ProviderType", "size:64"},
			{"ProviderBaseURL", "size:512"}, {"Username", "size:255"},
			{"Status", "size:32"},
		}},
		{reflect.TypeOf(kb.AuditEvent{}), []fieldSpec{
			{"ResourceID", "size:512"},
		}},
		{reflect.TypeOf(repos.ResourceRepositoryAccessGrant{}), []fieldSpec{
			{"RepoFullName", "size:255"},
		}},
	}

	for _, tt := range tests {
		for _, fs := range tt.fields {
			field, ok := tt.typ.FieldByName(fs.name)
			require.True(t, ok, "%s.%s should exist", tt.typ.Name(), fs.name)
			gormTag := string(field.Tag.Get("gorm"))
			require.Contains(t, gormTag, fs.wantSize, "%s.%s should have %s", tt.typ.Name(), fs.name, fs.wantSize)
		}
	}
}

func TestAutomationRunIndexesFollowQueryPaths(t *testing.T) {
	automationRunType := reflect.TypeOf(automation.AutomationRun{})

	forbiddenIndexes := map[string][]string{
		"SourceKind":           {"index"},
		"RepositoryOwner":      {"idx_automation_runs_repo_source", "index"},
		"RepositoryName":       {"idx_automation_runs_repo_source", "index"},
		"RepositoryFullName":   {"index"},
		"DefinitionPath":       {"idx_automation_runs_repo_source", "index"},
		"GitRef":               {"index"},
		"CommitSHA":            {"index"},
		"DefinitionID":         {"index"},
		"TriggerKind":          {"index"},
		"TriggerRef":           {"index"},
		"TargetKind":           {"index"},
		"TargetRef":            {"index"},
		"OwnerPrincipalID":     {"index"},
		"CreatedByPrincipalID": {"index"},
		"CurrentJobID":         {"index"},
		"StartedAt":            {"index"},
		"CompletedAt":          {"index"},
	}

	for fieldName, forbidden := range forbiddenIndexes {
		field, ok := automationRunType.FieldByName(fieldName)
		require.True(t, ok, "AutomationRun.%s should exist", fieldName)
		gormTag := string(field.Tag.Get("gorm"))
		for _, fragment := range forbidden {
			require.NotContains(t, gormTag, fragment, "AutomationRun.%s should not declare unused index %s", fieldName, fragment)
		}
	}

	workspaceID, ok := automationRunType.FieldByName("WorkspaceID")
	require.True(t, ok)
	workspaceTag := string(workspaceID.Tag.Get("gorm"))
	require.Contains(t, workspaceTag, "idx_automation_runs_workspace_updated")
	require.Contains(t, workspaceTag, "idx_automation_runs_workspace_status_updated")

	status, ok := automationRunType.FieldByName("Status")
	require.True(t, ok)
	require.Contains(t, string(status.Tag.Get("gorm")), "idx_automation_runs_workspace_status_updated")
	require.Contains(t, string(status.Tag.Get("gorm")), "idx_automation_runs_status_updated")

	updatedAt, ok := automationRunType.FieldByName("UpdatedAt")
	require.True(t, ok)
	updatedTag := string(updatedAt.Tag.Get("gorm"))
	require.Contains(t, updatedTag, "idx_automation_runs_workspace_updated")
	require.Contains(t, updatedTag, "idx_automation_runs_workspace_status_updated")
	require.Contains(t, updatedTag, "idx_automation_runs_status_updated")
}

func TestAutomationIndexByteBudget(t *testing.T) {
	// TiDB/MySQL limits an index key to 3072 bytes. Assume utf8mb4 worst case
	// (4 bytes per character) for string columns. This guards against adding
	// wide external strings such as definition_path/trigger_ref into composite indexes.
	models := []reflect.Type{
		reflect.TypeOf(automation.AutomationRun{}),
		reflect.TypeOf(automation.AutomationRepoStatusOutbox{}),
		reflect.TypeOf(automation.AutomationRunSlot{}),
		reflect.TypeOf(automation.AutomationJobRun{}),
		reflect.TypeOf(automation.AutomationEvent{}),
		reflect.TypeOf(automation.AutomationJob{}),
		reflect.TypeOf(automation.AutomationWait{}),
		reflect.TypeOf(automation.AutomationRunner{}),
		reflect.TypeOf(automation.AutomationRunnerToken{}),
		reflect.TypeOf(automation.AutomationRunnerSession{}),
		reflect.TypeOf(automation.AutomationActionsRunnerCredential{}),
		reflect.TypeOf(automation.AutomationLogChunk{}),
		reflect.TypeOf(automation.AutomationArtifact{}),
		reflect.TypeOf(automation.AutomationSecret{}),
		reflect.TypeOf(automation.AutomationPinnedRepo{}),
		reflect.TypeOf(providertokens.PersonalAccessToken{}),
	}

	for _, typ := range models {
		indexBytes := map[string]int{}
		for i := 0; i < typ.NumField(); i++ {
			field := typ.Field(i)
			gormTag := string(field.Tag.Get("gorm"))
			for _, indexName := range gormIndexNames(gormTag) {
				indexBytes[indexName] += gormFieldIndexBytes(gormTag)
			}
		}
		for indexName, bytes := range indexBytes {
			require.LessOrEqual(t, bytes, 3072, "%s.%s index key should fit TiDB/MySQL 3072-byte limit", typ.Name(), indexName)
		}
	}
}

func TestResourceRepositoryAccessGrantIndexByteBudget(t *testing.T) {
	model := reflect.TypeOf(repos.ResourceRepositoryAccessGrant{})
	indexBytes := map[string]int{}
	for i := 0; i < model.NumField(); i++ {
		field := model.Field(i)
		gormTag := string(field.Tag.Get("gorm"))
		for _, indexName := range gormIndexNames(gormTag) {
			indexBytes[indexName] += gormFieldIndexBytes(gormTag)
		}
	}
	for indexName, bytes := range indexBytes {
		require.LessOrEqual(t, bytes, 3072, "ResourceRepositoryAccessGrant.%s index key should fit TiDB/MySQL 3072-byte limit", indexName)
	}
}

func TestKnowledgeBaseSourceRepositoryAccessOverrideIndexByteBudget(t *testing.T) {
	model := reflect.TypeOf(kb.KnowledgeBaseSourceRepositoryAccessOverride{})
	indexBytes := map[string]int{}
	for i := 0; i < model.NumField(); i++ {
		field := model.Field(i)
		gormTag := string(field.Tag.Get("gorm"))
		for _, indexName := range gormIndexNames(gormTag) {
			indexBytes[indexName] += gormFieldIndexBytes(gormTag)
		}
	}
	for indexName, bytes := range indexBytes {
		require.LessOrEqual(t, bytes, 3072, "KnowledgeBaseSourceRepositoryAccessOverride.%s index key should fit TiDB/MySQL 3072-byte limit", indexName)
	}
}

func TestAutomationPersistenceModelsDoNotCarryJSONTags(t *testing.T) {
	models := []reflect.Type{
		reflect.TypeOf(automation.AutomationRun{}),
		reflect.TypeOf(automation.AutomationRepoStatusOutbox{}),
		reflect.TypeOf(automation.AutomationRunSlot{}),
		reflect.TypeOf(automation.AutomationJobRun{}),
		reflect.TypeOf(automation.AutomationEvent{}),
		reflect.TypeOf(automation.AutomationJob{}),
		reflect.TypeOf(automation.AutomationWait{}),
		reflect.TypeOf(automation.AutomationRunner{}),
		reflect.TypeOf(automation.AutomationRunnerToken{}),
		reflect.TypeOf(automation.AutomationRunnerSession{}),
		reflect.TypeOf(automation.AutomationActionsRunnerCredential{}),
		reflect.TypeOf(automation.AutomationLogChunk{}),
		reflect.TypeOf(automation.AutomationArtifact{}),
		reflect.TypeOf(automation.AutomationSecret{}),
		reflect.TypeOf(automation.AutomationPinnedRepo{}),
	}

	for _, model := range models {
		for i := 0; i < model.NumField(); i++ {
			field := model.Field(i)
			require.Empty(t, field.Tag.Get("json"), "%s.%s must not carry API JSON tags", model.Name(), field.Name)
		}
	}
}

func TestPersonalAccessTokenPersistenceModelDoesNotCarryJSONTags(t *testing.T) {
	model := reflect.TypeOf(providertokens.PersonalAccessToken{})
	for i := 0; i < model.NumField(); i++ {
		field := model.Field(i)
		require.Empty(t, field.Tag.Get("json"), "%s.%s must not carry API JSON tags", model.Name(), field.Name)
	}
}

func TestRepositoryPersistenceModelsDoNotCarryJSONTags(t *testing.T) {
	models := []reflect.Type{
		reflect.TypeOf(repos.ResourceRepository{}),
		reflect.TypeOf(repos.ResourceRepositoryAccessGrant{}),
	}

	for _, model := range models {
		for i := 0; i < model.NumField(); i++ {
			field := model.Field(i)
			require.Empty(t, field.Tag.Get("json"), "%s.%s must not carry API JSON tags", model.Name(), field.Name)
		}
	}
}

func TestMeshXAutomationMigrationFilesStaySortedAndUnique(t *testing.T) {
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	require.NotEmpty(t, migrations)
	for i := 1; i < len(migrations); i++ {
		require.Less(t, migrations[i-1].Version, migrations[i].Version)
	}
}

func TestMigrateCreatesAndVerifiesAutomationRuntimeSchema(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_automation_runtime_schema?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))

	requireAutomationMigrationsApplied(t, db, "0001", "0002", "0003", "0004", "0005", "0006", "0007", "0008", "0009", "0010", "0011", "0012", "0013", "0014", "0015", "0016", "0017", "0018")
	require.True(t, db.Migrator().HasTable(&automation.AutomationRepoStatusOutbox{}))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationRepoStatusOutbox{}, "uk_automation_repo_status_outbox_commit_context"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationRepoStatusOutbox{}, "idx_automation_repo_status_outbox_status_next"))
	requireAutomationRepoStatusOutboxIndexColumns(t, db)
	require.True(t, db.Migrator().HasColumn(&automation.AutomationRun{}, "idempotency_key"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationRun{}, "uk_automation_runs_workspace_idem"))
	require.True(t, db.Migrator().HasTable(&automation.AutomationRunSlot{}))
	require.True(t, db.Migrator().HasTable(&automation.AutomationRunner{}))
	require.True(t, db.Migrator().HasTable(&automation.AutomationRunnerToken{}))
	require.True(t, db.Migrator().HasTable(&automation.AutomationRunnerSession{}))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationRunnerToken{}, "token_scope"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationRunnerToken{}, "session_id"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationRunnerToken{}, "session_token_hash"))
	require.True(t, db.Migrator().HasTable(&automation.AutomationLogChunk{}))
	require.True(t, db.Migrator().HasTable(&automation.AutomationArtifact{}))
	require.True(t, db.Migrator().HasTable(&automation.AutomationPinnedRepo{}))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "runner_group_key"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key_hash"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_count"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_claim_lookup"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_workspace_claim_lookup"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_ready_workspace_lookup"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationRunnerToken{}, "idx_automation_runner_tokens_session_lookup"))
	requireAutomationWorkspaceClaimIndexColumns(t, db)
	requireAutomationReadyWorkspaceIndexColumns(t, db)
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_lease_recover"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationRun{}, "idx_automation_runs_status_updated"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJobRun{}, "idx_automation_job_runs_status_updated"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationLogChunk{}, "uk_automation_log_chunks_sequence"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationArtifact{}, "idx_automation_artifacts_run_job_name"))
	require.True(t, db.Migrator().HasTable(&automation.AutomationSecret{}))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationSecret{}, "value_ciphertext"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationWait{}, "signal_key"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationWait{}, "idx_automation_waits_signal_status"))
	requireAutomationWaitSignalIndexColumns(t, db)

	require.NoError(t, Migrate(db))
	requireAutomationMigrationsApplied(t, db, "0001", "0002", "0003", "0004", "0005", "0006", "0007", "0008", "0009", "0010", "0011", "0012", "0013", "0014", "0015", "0016", "0017", "0018")
}

func TestMigrateDoesNotCreateProjectOrKBRefTables(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_no_project_schema?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()

	require.NoError(t, Migrate(db))

	for _, table := range []string{
		"projects",
		"project_status_updates",
		"project_audit_events",
		"project_members",
		"project_resource_links",
		"project_memories",
		"project_pins",
		"project_saved_views",
		"project_repositories",
		"knowledge_base_refs",
	} {
		require.False(t, db.Migrator().HasTable(table), "%s should not be created by MeshX migrations", table)
	}
}

func requireAutomationMigrationsApplied(t *testing.T, db *gorm.DB, versions ...string) {
	t.Helper()
	for _, version := range versions {
		var applied int64
		require.NoError(t, db.Table("schema_migrations").Where("version = ?", version).Count(&applied).Error)
		require.EqualValues(t, 1, applied, "migration %s should be applied once", version)
	}
}

func TestMigrateBackfillsAutomationWaitSignalKeys(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_automation_wait_signal_backfill?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, ensureSchemaMigrationsTable(db))
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	for _, migration := range migrations {
		if migration.Version > "0012" {
			continue
		}
		require.NoError(t, executeAutomationMigration(db, migration))
		require.NoError(t, recordAutomationMigration(db, migration))
	}
	now := time.Date(2026, 6, 7, 12, 0, 0, 0, time.UTC)
	require.False(t, db.Migrator().HasColumn(&automation.AutomationWait{}, "signal_key"))
	require.NoError(t, db.Exec(`INSERT INTO automation_waits (
  id, workspace_id, run_id, job_run_id, wait_kind, status, active_key,
  resource_type, resource_id, resume_event_type, input_json, result_json,
  created_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"wait-signal-backfill", "ws-1", "run-1", "job-1", automation.AutomationWaitKindWaitEvent, automation.AutomationWaitStatusActive, "ws-1:run-1:job-1",
		"provider", "installation-1", "provider.ready", "{}", "", now, now).Error)
	require.NoError(t, db.Exec(`INSERT INTO automation_waits (
  id, workspace_id, run_id, job_run_id, wait_kind, status, active_key,
  resource_type, resource_id, resume_event_type, input_json, result_json,
  created_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"wait-agent-task-backfill", "ws-1", "run-2", "job-2", automation.AutomationWaitKindAgentTask, automation.AutomationWaitStatusActive, "ws-1:run-2:job-2",
		"agent_task", "run-2:task", "agent_task_completed", `{"steps":[{"uses":"agentteam/agent-task","with":{"task_id":"tsk_legacy"}}]}`, "", now, now).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))

	var signalKey string
	require.NoError(t, db.Model(&automation.AutomationWait{}).Select("signal_key").Where("id = ?", "wait-signal-backfill").Scan(&signalKey).Error)
	require.Equal(t, automation.AutomationWaitSignalKey("ws-1", automation.AutomationWaitSignal{WaitKind: automation.AutomationWaitKindWaitEvent, EventType: "provider.ready", ResourceType: "provider", ResourceID: "installation-1"}), signalKey)
	var agentTaskWait automation.AutomationWait
	require.NoError(t, db.Model(&automation.AutomationWait{}).Select("signal_key", "resource_id").Where("id = ?", "wait-agent-task-backfill").First(&agentTaskWait).Error)
	require.Equal(t, "tsk_legacy", agentTaskWait.ResourceID)
	require.Equal(t, automation.AutomationWaitSignalKey("ws-1", automation.AutomationWaitSignal{WaitKind: automation.AutomationWaitKindAgentTask, EventType: "agent_task_completed", ResourceType: "agent_task", ResourceID: "tsk_legacy"}), agentTaskWait.SignalKey)
}

func TestMigrateUpgradesAutoMigratedAutomationJobsWithClaimFields(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_automation_runtime_upgrade?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&oldAutomationJob{}))
	require.False(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "runner_group_key"))
	require.False(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key"))
	require.False(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key_hash"))
	require.False(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_count"))

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "runner_group_key"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_key_hash"))
	require.True(t, db.Migrator().HasColumn(&automation.AutomationJob{}, "label_count"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_claim_lookup"))
	require.True(t, db.Migrator().HasIndex(&automation.AutomationJob{}, "idx_automation_jobs_workspace_claim_lookup"))
	requireAutomationWorkspaceClaimIndexColumns(t, db)
}

func TestMigrateRebuildsWorkspaceClaimIndexOrder(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_automation_runtime_claim_index_order?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, ensureSchemaMigrationsTable(db))
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	for _, migration := range migrations {
		if migration.Version != "0001" && migration.Version != "0002" && migration.Version != "0003" {
			continue
		}
		require.NoError(t, executeAutomationMigration(db, migration))
		require.NoError(t, recordAutomationMigration(db, migration))
	}
	require.NoError(t, db.Exec("ALTER TABLE automation_jobs ADD COLUMN label_key_hash VARCHAR(64) NOT NULL DEFAULT ''").Error)
	require.NoError(t, db.Exec("CREATE INDEX idx_automation_jobs_workspace_claim_lookup ON automation_jobs (workspace_id, queue_name, shard_bucket, status, runner_group_key, label_key_hash, priority, scheduled_at, id)").Error)
	require.NoError(t, recordAutomationMigration(db, automationMigration{Version: "0004", Name: "automation_queue_workspace_claim", Path: "migrations/0004_automation_queue_workspace_claim.sql"}))

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))
	requireAutomationWorkspaceClaimIndexColumns(t, db)
}

func requireAutomationWorkspaceClaimIndexColumns(t *testing.T, db *gorm.DB) {
	t.Helper()
	columns, err := automationIndexColumns(db, "automation_jobs", "idx_automation_jobs_workspace_claim_lookup")
	require.NoError(t, err)
	require.Equal(t, []string{
		"workspace_id",
		"queue_name",
		"shard_bucket",
		"status",
		"runner_group_key",
		"label_key_hash",
		"scheduled_at",
		"priority",
		"id",
	}, columns)
}

func requireAutomationReadyWorkspaceIndexColumns(t *testing.T, db *gorm.DB) {
	t.Helper()
	columns, err := automationIndexColumns(db, "automation_jobs", "idx_automation_jobs_ready_workspace_lookup")
	require.NoError(t, err)
	require.Equal(t, []string{"status", "queue_name", "scheduled_at", "workspace_id"}, columns)
}

func requireAutomationWaitSignalIndexColumns(t *testing.T, db *gorm.DB) {
	t.Helper()
	columns, err := automationIndexColumns(db, "automation_waits", "idx_automation_waits_signal_status")
	require.NoError(t, err)
	require.Equal(t, []string{"signal_key", "status", "updated_at", "id"}, columns)
}

func requireAutomationRepoStatusOutboxIndexColumns(t *testing.T, db *gorm.DB) {
	t.Helper()
	columns, err := automationIndexColumns(db, "automation_repo_status_outbox", "uk_automation_repo_status_outbox_commit_context")
	require.NoError(t, err)
	require.Equal(t, []string{"workspace_id", "repository_owner", "repository_name", "commit_sha", "context"}, columns)
	columns, err = automationIndexColumns(db, "automation_repo_status_outbox", "idx_automation_repo_status_outbox_status_next")
	require.NoError(t, err)
	require.Equal(t, []string{"status", "next_attempt_at", "id"}, columns)
}

func TestMigrateBackfillsAutoMigratedAutomationJobClaimFields(t *testing.T) {
	ctx := context.Background()
	now := time.Date(2026, 6, 5, 15, 0, 0, 0, time.UTC)
	db, err := open(Config{DSN: "file:meshx_automation_runtime_claim_backfill?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&oldAutomationJob{}))
	require.NoError(t, db.Create(&oldAutomationJob{
		WorkspaceID:    "ws-1",
		RunID:          "run-1",
		JobRunID:       "job-1",
		QueueName:      "default",
		Status:         automation.AutomationJobStatusReady,
		Priority:       100,
		ScheduledAt:    now.Add(-time.Minute),
		MaxAttempts:    3,
		IdempotencyKey: "run-1:job-1",
		RunnerGroup:    "Trusted",
		LabelsJSON:     `["self-hosted","macOS"]`,
		ShardBucket:    0,
		CreatedAt:      now.Add(-time.Minute),
		UpdatedAt:      now.Add(-time.Minute),
	}).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))

	var job automation.AutomationJob
	require.NoError(t, db.First(&job).Error)
	require.Equal(t, "trusted", job.RunnerGroupKey)
	require.Equal(t, "macos,self-hosted", job.LabelKey)
	require.NotEmpty(t, job.LabelKeyHash)
	require.Equal(t, 2, job.LabelCount)

	store := automation.New(db)
	wrongGroup, err := store.ClaimJobs(ctx, automation.ClaimJobsRequest{
		WorkspaceID:   "ws-1",
		QueueName:     "default",
		ShardBucket:   0,
		LeaseOwner:    "runner-wrong",
		LeaseDuration: time.Minute,
		Limit:         1,
		Labels:        []string{"self-hosted", "macos", "x64"},
		Now:           now,
	})
	require.NoError(t, err)
	require.Empty(t, wrongGroup)

	matched, err := store.ClaimJobs(ctx, automation.ClaimJobsRequest{
		WorkspaceID:   "ws-1",
		QueueName:     "default",
		ShardBucket:   0,
		LeaseOwner:    "runner-trusted",
		LeaseDuration: time.Minute,
		Limit:         1,
		RunnerGroup:   "trusted",
		Labels:        []string{"self-hosted", "macos", "x64"},
		Now:           now,
	})
	require.NoError(t, err)
	require.Len(t, matched, 1)
	require.Equal(t, job.ID, matched[0].ID)
}

func TestMigrateBackfillsActiveAutomationRunSlots(t *testing.T) {
	now := time.Date(2026, 6, 5, 15, 30, 0, 0, time.UTC)
	db, err := open(Config{DSN: "file:meshx_automation_runtime_run_slot_backfill?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, ensureSchemaMigrationsTable(db))
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	for _, migration := range migrations {
		if migration.Version > "0006" {
			continue
		}
		require.NoError(t, executeAutomationMigration(db, migration))
		require.NoError(t, recordAutomationMigration(db, migration))
	}
	for i := 0; i < automationRunSlotBackfillBatchSize+2; i++ {
		status := automation.AutomationRunStatusRunning
		if i%2 == 1 {
			status = automation.AutomationRunStatusWaiting
		}
		runTime := now.Add(time.Duration(i) * time.Second)
		require.NoError(t, db.Omit("ContentID", "ContentVersionID").Create(&automation.AutomationRun{
			ID:          fmt.Sprintf("run-active-%03d", i),
			WorkspaceID: "ws-1",
			Name:        fmt.Sprintf("Active %03d", i),
			Status:      status,
			CreatedAt:   runTime,
			UpdatedAt:   runTime,
		}).Error)
	}
	require.NoError(t, db.Omit("ContentID", "ContentVersionID").Create(&automation.AutomationRun{ID: "run-done", WorkspaceID: "ws-1", Name: "Done", Status: automation.AutomationRunStatusSucceeded, CreatedAt: now, UpdatedAt: now}).Error)
	require.NoError(t, db.Exec(`CREATE TABLE IF NOT EXISTS automation_run_slots (
  workspace_id VARCHAR(64) NOT NULL,
  slot_index INTEGER NOT NULL,
  run_id VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  PRIMARY KEY (workspace_id, slot_index)
)`).Error)
	require.NoError(t, db.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS uk_automation_run_slots_run ON automation_run_slots (run_id)`).Error)
	require.NoError(t, db.Create(&automation.AutomationRunSlot{WorkspaceID: "ws-1", SlotIndex: 0, RunID: "run-active-000", CreatedAt: now, UpdatedAt: now}).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))

	var slots []automation.AutomationRunSlot
	require.NoError(t, db.Order("slot_index ASC").Find(&slots).Error)
	require.Len(t, slots, automationRunSlotBackfillBatchSize+2)
	require.Equal(t, "run-active-000", slots[0].RunID)
	require.Equal(t, 0, slots[0].SlotIndex)
	require.Equal(t, "run-active-001", slots[1].RunID)
	require.Equal(t, 1, slots[1].SlotIndex)
	require.Equal(t, fmt.Sprintf("run-active-%03d", automationRunSlotBackfillBatchSize+1), slots[len(slots)-1].RunID)
	require.Equal(t, automationRunSlotBackfillBatchSize+1, slots[len(slots)-1].SlotIndex)

	var doneSlots int64
	require.NoError(t, db.Model(&automation.AutomationRunSlot{}).Where("run_id = ?", "run-done").Count(&doneSlots).Error)
	require.Zero(t, doneSlots)
}

func TestMigrateReplaysBlobMetadataColumnMigrationAfterPartialDDL(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_automation_runtime_blob_replay?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, ensureSchemaMigrationsTable(db))
	migrations, err := readAutomationMigrations()
	require.NoError(t, err)
	for _, migration := range migrations {
		if migration.Version != "0001" && migration.Version != "0002" {
			continue
		}
		require.NoError(t, executeAutomationMigration(db, migration))
		require.NoError(t, recordAutomationMigration(db, migration))
	}
	require.NoError(t, db.Exec("ALTER TABLE automation_log_chunks ADD COLUMN content_type VARCHAR(128) NOT NULL DEFAULT ''").Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, VerifySchema(db))
	var applied int64
	require.NoError(t, db.Table("schema_migrations").Where("version = ?", "0003").Count(&applied).Error)
	require.EqualValues(t, 1, applied)
}

func gormIndexNames(tag string) []string {
	if tag == "" {
		return nil
	}
	parts := strings.Split(tag, ";")
	var names []string
	for _, part := range parts {
		if part == "index" {
			names = append(names, "index")
			continue
		}
		if strings.HasPrefix(part, "index:") || strings.HasPrefix(part, "uniqueIndex:") {
			value := strings.TrimPrefix(strings.TrimPrefix(part, "index:"), "uniqueIndex:")
			name := strings.Split(value, ",")[0]
			if name == "" {
				name = "index"
			}
			names = append(names, name)
		}
	}
	return names
}

func gormFieldIndexBytes(tag string) int {
	if strings.Contains(tag, "type:text") {
		return 0
	}
	for _, part := range strings.Split(tag, ";") {
		if strings.HasPrefix(part, "size:") {
			var size int
			_, _ = fmt.Sscanf(part, "size:%d", &size)
			return size * 4
		}
	}
	return 8
}

type oldAutomationJob struct {
	ID             int64      `gorm:"primaryKey;autoIncrement"`
	WorkspaceID    string     `gorm:"size:64;not null;index:idx_automation_jobs_run_status,priority:1"`
	RunID          string     `gorm:"size:64;not null;index:idx_automation_jobs_run_status,priority:2"`
	JobRunID       string     `gorm:"size:64;not null"`
	QueueName      string     `gorm:"size:64;not null;index:idx_automation_jobs_claim,priority:1"`
	Status         string     `gorm:"size:32;not null;default:'ready';index:idx_automation_jobs_claim,priority:3;index:idx_automation_jobs_lease_expiry,priority:1;index:idx_automation_jobs_run_status,priority:3"`
	Priority       int        `gorm:"not null;default:100;index:idx_automation_jobs_claim,priority:4"`
	ScheduledAt    time.Time  `gorm:"not null;index:idx_automation_jobs_claim,priority:5"`
	LeaseOwner     string     `gorm:"size:128;not null;default:''"`
	LeaseUntil     *time.Time `gorm:"index:idx_automation_jobs_lease_expiry,priority:2"`
	Attempt        int        `gorm:"not null;default:0"`
	MaxAttempts    int        `gorm:"not null;default:3"`
	IdempotencyKey string     `gorm:"size:255;not null;uniqueIndex:uk_automation_jobs_idem"`
	RunnerGroup    string     `gorm:"size:128;not null;default:''"`
	LabelsJSON     string     `gorm:"type:text;not null"`
	ShardBucket    int        `gorm:"not null;index:idx_automation_jobs_claim,priority:2"`
	CreatedAt      time.Time  `gorm:"not null"`
	UpdatedAt      time.Time  `gorm:"not null"`
}

func (oldAutomationJob) TableName() string { return "automation_jobs" }