package database

import (
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
	"github.com/tidbcloud/AgentTeam/internal/store/content"
	"github.com/tidbcloud/AgentTeam/internal/store/kb"
	"github.com/tidbcloud/AgentTeam/internal/store/knowledgeqa"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/activity"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/memory"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/repos"
)

type knowledgeBaseSetupSessionBeforeOfficialEdit struct {
	ID                string    `gorm:"primaryKey;size:64"`
	WorkspaceID       string    `gorm:"size:64;not null;uniqueIndex:uk_kb_setup_session_kb"`
	KnowledgeBaseID   string    `gorm:"size:64;not null;uniqueIndex:uk_kb_setup_session_kb"`
	Mode              string    `gorm:"size:32;not null"`
	ActiveStep        string    `gorm:"size:32;not null;default:'basic'"`
	WorkingRevision   string    `gorm:"size:64;not null"`
	PublishedRevision string    `gorm:"size:64;not null;default:''"`
	Version           int64     `gorm:"not null;default:1"`
	CreatedBy         string    `gorm:"size:64;not null"`
	UpdatedBy         string    `gorm:"size:64;not null"`
	CreatedAt         time.Time `gorm:"not null"`
	UpdatedAt         time.Time `gorm:"not null"`
}

func (knowledgeBaseSetupSessionBeforeOfficialEdit) TableName() string {
	return "knowledge_base_setup_sessions"
}

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

	base := time.Date(2026, 7, 31, 10, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&[]activity.ResourceActivityEvent{
		{ID: "act_old", WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_1", EventType: activity.EventTypeCommit, OccurredAt: base},
		{ID: "act_latest_a", WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_1", EventType: activity.EventTypeCommit, OccurredAt: base.Add(time.Hour)},
		{ID: "act_latest_b", WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_1", EventType: activity.EventTypeCommit, OccurredAt: base.Add(time.Hour)},
		{ID: "act_issue", WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_1", EventType: "issue_opened", OccurredAt: base.Add(2 * time.Hour)},
	}).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, Migrate(db))
	var rows []activity.ResourceActivityLatestCommit
	require.NoError(t, db.Find(&rows).Error)
	require.Len(t, rows, 1)
	require.Equal(t, "act_latest_b", rows[0].EventID)
	require.Equal(t, base.Add(time.Hour), rows[0].OccurredAt)
}

func TestMigrateDoesNotRescanActivityHistoryAfterLatestCommitBackfill(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_latest_activity_commit_noop?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, Migrate(db))
	require.NoError(t, db.Exec("DROP TABLE resource_activity_events").Error)
	require.NoError(t, Migrate(db))
}

func TestMigrateBackfillsMissingLatestCommitsWhenProjectionAlreadyHasRows(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_latest_activity_commit_partial?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&activity.ResourceActivityEvent{}, &activity.ResourceActivityLatestCommit{}))

	base := time.Date(2026, 7, 31, 10, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&activity.ResourceActivityEvent{ID: "act_missing", WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_missing", EventType: activity.EventTypeCommit, OccurredAt: base}).Error)
	require.NoError(t, db.Create(&activity.ResourceActivityLatestCommit{WorkspaceID: "wsp_1", AssetType: activity.AssetTypeKB, AssetID: "src_existing", OccurredAt: base, EventID: "act_existing"}).Error)

	require.NoError(t, Migrate(db))
	var rows []activity.ResourceActivityLatestCommit
	require.NoError(t, db.Order("asset_id").Find(&rows).Error)
	require.Len(t, rows, 2)
	require.Equal(t, "src_missing", rows[1].AssetID)
}

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

	require.Error(t, backfillResourceActivityLatestCommits(db))
	if db.Migrator().HasTable("meshx_data_migrations") {
		var applied int64
		require.NoError(t, db.Table("meshx_data_migrations").Where("name = ?", "resource_activity_latest_commits_backfill").Count(&applied).Error)
		require.Zero(t, applied)
	}
}

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

	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasTable(&kb.SourceMirror{}))
	require.True(t, db.Migrator().HasColumn(&kb.SourceMirror{}, "credential_ref"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceMirror{}, "last_mirrored_commit"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceMirror{}, "default_ref_explicit"))
}

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

	require.NoError(t, db.AutoMigrate(&knowledgeBaseSetupSessionBeforeOfficialEdit{}))
	now := time.Date(2026, 8, 9, 1, 2, 3, 0, time.UTC)
	require.NoError(t, db.Create(&knowledgeBaseSetupSessionBeforeOfficialEdit{
		ID: "setup_existing", WorkspaceID: "wsp_1", KnowledgeBaseID: "kb_1",
		Mode: "create_draft", ActiveStep: "sources", WorkingRevision: "kbr_1", Version: 2,
		CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now,
	}).Error)

	require.NoError(t, Migrate(db))
	for _, column := range []string{
		"working_snapshot_json", "published_snapshot_json", "save_request_id", "completed_at",
	} {
		require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBaseSetupSession{}, column), column)
	}
	var migrated kb.KnowledgeBaseSetupSession
	require.NoError(t, db.Where("id = ?", "setup_existing").First(&migrated).Error)
	require.Empty(t, migrated.WorkingSnapshotJSON)
	require.Empty(t, migrated.PublishedSnapshotJSON)
	require.Empty(t, migrated.SaveRequestID)
	require.Nil(t, migrated.CompletedAt)
}

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

	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasTable(&kb.KnowledgeBaseGenerationRequest{}))
	for _, column := range []string{
		"source_revision_id", "source_commit_sha", "idempotency_key",
		"payload_digest", "approval_version", "decision", "decision_id",
		"decision_input_reason", "decision_reason", "decided_by", "decided_at", "job_id",
		"notification_state", "notification_version", "notified_at",
	} {
		require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBaseGenerationRequest{}, column), column)
	}
	require.True(t, db.Migrator().HasIndex(&kb.KnowledgeBaseGenerationRequest{}, "idx_kb_generation_terminal_projection"))
}

func TestMigrateBackfillsPendingWikiTerminalNotifications(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_kb_terminal_notification_backfill?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&kb.KnowledgeBaseGenerationRequest{}))
	now := time.Date(2026, 8, 9, 12, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&kb.KnowledgeBaseGenerationRequest{
		ID: "kbgr_terminal", WorkspaceID: "ws_1", KnowledgeBaseID: "kb_1", SourceID: "src_1",
		Kind: kb.GenerationKindWiki, SourceRevisionID: "rev_1", SourceCommitSHA: "source-sha",
		Scope: "full", Language: "auto", RequesterID: "usr_1",
		Status: kb.GenerationRequestStatusFailed, IdempotencyKey: "request-1",
		PayloadDigest: strings.Repeat("a", 64), DecisionReason: "generation_failed",
		JobID: "job_terminal", Version: 3, CreatedAt: now, UpdatedAt: now,
	}).Error)
	require.NoError(t, db.Create(&kb.KnowledgeBaseGenerationRequest{
		ID: "kbgr_approval_stale", WorkspaceID: "ws_1", KnowledgeBaseID: "kb_1", SourceID: "src_2",
		Kind: kb.GenerationKindWiki, SourceRevisionID: "rev_2", SourceCommitSHA: "source-sha-2",
		Scope: "full", Language: "auto", RequesterID: "usr_1",
		Status: kb.GenerationRequestStatusStale, IdempotencyKey: "request-2",
		PayloadDigest: strings.Repeat("b", 64), DecisionReason: "source_revision_changed",
		Version: 2, CreatedAt: now, UpdatedAt: now,
	}).Error)

	require.NoError(t, Migrate(db))
	var stored kb.KnowledgeBaseGenerationRequest
	require.NoError(t, db.First(&stored, "id = ?", "kbgr_terminal").Error)
	require.Equal(t, kb.GenerationNotificationStatePending, stored.NotificationState)
	require.Zero(t, stored.NotificationVersion)
	stored = kb.KnowledgeBaseGenerationRequest{}
	require.NoError(t, db.First(&stored, "id = ?", "kbgr_approval_stale").Error)
	require.Empty(t, stored.NotificationState, "approval-only stale requests already converge through the Approval projection")
}

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

	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasTable(&kb.KnowledgeBasePublicationRequest{}))
	for _, column := range []string{
		"working_revision", "idempotency_key", "payload_digest", "content_digest",
		"snapshot_json", "approval_version", "decision", "decision_id",
		"decision_input_reason", "decision_reason", "decided_by", "decided_at", "published_at",
	} {
		require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBasePublicationRequest{}, column), column)
	}
}

func TestMigrateBackfillsKnowledgeBaseDefaultGroups(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_kb_group_backfill?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&kb.KnowledgeBase{}))
	now := time.Date(2026, 8, 6, 1, 2, 3, 0, time.UTC)
	require.NoError(t, db.Create(&kb.KnowledgeBase{ID: "kb_legacy", WorkspaceID: "wsp_1", Name: "Legacy", IsActive: true, RetrievalPolicy: "{}", SourceCollectionState: "{}", CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now}).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, db.Create(&kb.KnowledgeBase{ID: "kb_rolling", WorkspaceID: "wsp_1", Name: "Rolling writer", IsActive: true, RetrievalPolicy: "{}", SourceCollectionState: "{}", CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now}).Error)
	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasTable(&kb.KnowledgeBaseGroup{}))
	require.True(t, db.Migrator().HasTable(&kb.KnowledgeBaseTag{}))
	require.True(t, db.Migrator().HasTable(&kb.KnowledgeBaseTagAssignment{}))

	var base kb.KnowledgeBase
	require.NoError(t, db.First(&base, "id = ?", "kb_legacy").Error)
	require.Equal(t, kb.DefaultKnowledgeBaseGroupID("wsp_1"), base.GroupID)
	var rolling kb.KnowledgeBase
	require.NoError(t, db.First(&rolling, "id = ?", "kb_rolling").Error)
	require.Equal(t, base.GroupID, rolling.GroupID)
	var group kb.KnowledgeBaseGroup
	require.NoError(t, db.First(&group, "id = ?", base.GroupID).Error)
	require.True(t, group.IsDefault)
}

func TestMigrateMakesLegacyKnowledgeBasesWorkspacePublic(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_kb_public_visibility?mode=memory&cache=shared"}, false)
	require.NoError(t, err)
	defer func() { require.NoError(t, Close(db)) }()
	require.NoError(t, db.AutoMigrate(&kb.KnowledgeBase{}))
	now := time.Date(2026, 8, 10, 1, 2, 3, 0, time.UTC)
	require.NoError(t, db.Create(&kb.KnowledgeBase{
		ID: "kb_legacy_private", WorkspaceID: "wsp_1", Name: "Legacy private",
		DefaultWorkspaceMemberRole: "none", IsActive: true,
		RetrievalPolicy: "{}", SourceCollectionState: "{}",
		CreatedBy: "owner", UpdatedBy: "owner", CreatedAt: now, UpdatedAt: now,
	}).Error)

	require.NoError(t, Migrate(db))
	require.NoError(t, Migrate(db))
	var base kb.KnowledgeBase
	require.NoError(t, db.First(&base, "id = ?", "kb_legacy_private").Error)
	require.Equal(t, "view", base.DefaultWorkspaceMemberRole)
}

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

	require.NoError(t, Migrate(db))
	require.True(t, db.Migrator().HasTable(&knowledgeqa.RootHandleRecord{}))
	for _, column := range []string{
		"organization_id", "workspace_id", "requester_id", "turn_id",
		"knowledge_base_id", "source_id", "source_name", "root_kind", "repo_role",
		"revision", "repository_ref", "default_ref", "resolved_ref", "resolved_ref_kind", "expires_at",
	} {
		require.True(t, db.Migrator().HasColumn(&knowledgeqa.RootHandleRecord{}, column), column)
	}
	require.True(t, db.Migrator().HasTable(&knowledgeqa.AccountingCheckpointRecord{}))
	for _, column := range []string{
		"attempt_id", "organization_id", "workspace_id", "requester_id", "turn_id",
		"generation", "state", "usage_json", "settlement_refs_json", "expires_at",
	} {
		require.True(t, db.Migrator().HasColumn(&knowledgeqa.AccountingCheckpointRecord{}, column), column)
	}
}

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

	require.NoError(t, db.Exec(`
CREATE TABLE memories (
  id VARCHAR(64) NOT NULL PRIMARY KEY,
  workspace_id VARCHAR(64) NOT NULL,
  slug VARCHAR(255) NOT NULL DEFAULT '',
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  storage_scope VARCHAR(32) NOT NULL DEFAULT 'personal',
  storage_owner_login VARCHAR(128) NOT NULL DEFAULT '',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by VARCHAR(64) NOT NULL,
  updated_by VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP NULL
)`).Error)
	require.NoError(t, db.Exec("CREATE INDEX idx_mem_workspace_updated ON memories (workspace_id, updated_at)").Error)
	require.NoError(t, db.Exec("CREATE INDEX idx_mem_workspace_creator_deleted ON memories (workspace_id, created_by, deleted_at)").Error)
	require.NoError(t, db.AutoMigrate(&memory.MemoryGrant{}, &memory.MemoryPin{}, &repos.ResourceRepository{}))
	require.NoError(t, db.Exec(`
CREATE TABLE memory_agent_bindings (
  id VARCHAR(64) NOT NULL PRIMARY KEY,
  workspace_id VARCHAR(64) NOT NULL,
  agent_id VARCHAR(64) NOT NULL,
  memory_id VARCHAR(64) NOT NULL,
  branch VARCHAR(255) NOT NULL DEFAULT 'main',
  import_existing_local_content BOOLEAN NOT NULL DEFAULT FALSE,
  replace_local_content_on_switch BOOLEAN NOT NULL DEFAULT FALSE,
  created_by VARCHAR(64) NOT NULL,
  updated_by VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
)`).Error)

	now := time.Date(2026, 7, 8, 2, 30, 0, 0, time.UTC)
	require.NoError(t, db.Exec(`
INSERT INTO memories (id, workspace_id, slug, name, description, storage_scope, storage_owner_login, is_active, created_by, updated_by, created_at, updated_at)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"mem_bound", "wsp_1", "bound", "Bound", "", "personal", "usr_1", true, "usr_1", "usr_1", now, now,
		"mem_unbound", "wsp_1", "unbound", "Unbound", "", "personal", "usr_1", true, "usr_1", "usr_1", now, now,
	).Error)
	require.NoError(t, db.Exec(`
INSERT INTO memory_agent_bindings (id, workspace_id, agent_id, memory_id, branch, import_existing_local_content, replace_local_content_on_switch, created_by, updated_by, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"mab_bound", "wsp_1", "agt_1", "mem_bound", "dev", true, false, "usr_1", "usr_1", now, now.Add(time.Minute),
	).Error)
	require.NoError(t, db.Create(&memory.MemoryGrant{ID: "grant_unbound", WorkspaceID: "wsp_1", MemoryID: "mem_unbound", SubjectType: "user", SubjectID: "usr_1", Role: "owner", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}).Error)
	require.NoError(t, db.Create(&memory.MemoryPin{WorkspaceID: "wsp_1", PrincipalID: "usr_1", MemoryID: "mem_unbound", CreatedAt: now, UpdatedAt: now}).Error)
	require.NoError(t, db.Create(&repos.ResourceRepository{ID: "rr_unbound", WorkspaceID: "wsp_1", ResourceType: repos.ResourceTypeMemory, ResourceID: "mem_unbound", RepoRole: repos.RepoRolePrimary, OwnerKind: "personal", RepoOwner: "usr_1", RepoName: "unbound.mem", RepoFullName: "usr_1/unbound.mem", CreatedBy: "usr_1", UpdatedBy: "usr_1", CreatedAt: now, UpdatedAt: now}).Error)

	require.NoError(t, Migrate(db))

	var rows []memory.Memory
	require.NoError(t, db.Find(&rows).Error)
	require.Len(t, rows, 1)
	require.Equal(t, "mem_bound", rows[0].ID)
	require.NotNil(t, rows[0].AgentID)
	require.Equal(t, "agt_1", *rows[0].AgentID)
	require.Equal(t, "dev", rows[0].Branch)
	require.True(t, rows[0].ImportExistingLocalContent)
	require.False(t, db.Migrator().HasColumn(&memory.Memory{}, "workspace_id"))
	require.False(t, db.Migrator().HasTable("memory_agent_bindings"))

	var orphanCount int64
	require.NoError(t, db.Model(&memory.MemoryGrant{}).Where("memory_id = ?", "mem_unbound").Count(&orphanCount).Error)
	require.Zero(t, orphanCount)
	require.NoError(t, db.Model(&memory.MemoryPin{}).Where("memory_id = ?", "mem_unbound").Count(&orphanCount).Error)
	require.Zero(t, orphanCount)
	require.NoError(t, db.Model(&repos.ResourceRepository{}).Where("resource_type = ? AND resource_id = ?", repos.ResourceTypeMemory, "mem_unbound").Count(&orphanCount).Error)
	require.Zero(t, orphanCount)
}

func TestMigrateCreatesCleanKnowledgeBaseRepoMappingSchema(t *testing.T) {
	db, err := open(Config{DSN: "file:meshx_kb_clean_mapping_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 []any{&kb.AnalysisRun{}, &kb.SourceRevision{}, &kb.KnowledgeBaseSourceRepositoryAccessOverride{}, &repos.ResourceRepository{}, &repos.ResourceRepositoryLifecycleEvent{}, &repos.ResourceRepositoryAccessGrant{}} {
		require.True(t, db.Migrator().HasTable(table))
	}
	require.True(t, db.Migrator().HasTable("resource_repositories"))
	require.True(t, db.Migrator().HasTable("resource_repository_access_grants"))
	require.False(t, db.Migrator().HasTable("meshx_resource_repositories"))
	require.False(t, db.Migrator().HasTable("meshx_resource_repository_access_grants"))
	require.False(t, db.Migrator().HasTable("knowledge_base_source_materializations"))
	require.False(t, db.Migrator().HasTable("knowledge_base_insights_repositories"))
	for _, column := range []string{"repo_owner", "repo_name", "repo_full_name", "repo_id", "repo_state"} {
		require.False(t, db.Migrator().HasColumn(&kb.KnowledgeBase{}, column), "knowledge_bases.%s should be removed", column)
		require.False(t, db.Migrator().HasColumn(&kb.Source{}, column), "knowledge_base_sources.%s should be removed", column)
		require.False(t, db.Migrator().HasColumn(&memory.Memory{}, column), "memories.%s should be removed", column)
	}
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBase{}, "retrieval_policy"))
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBase{}, "source_collection_state"))
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBase{}, "default_repository_access_profile"))
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBase{}, "allow_knowledge_qa_without_source_access"))
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBaseGrant{}, "repository_access_profile"))
	require.True(t, db.Migrator().HasColumn(&kb.KnowledgeBaseSourceRepositoryAccessOverride{}, "repository_access_profile"))
	require.True(t, db.Migrator().HasIndex(&kb.KnowledgeBaseSourceRepositoryAccessOverride{}, "uk_kb_src_repo_access_override"))
	require.True(t, db.Migrator().HasIndex(&kb.KnowledgeBaseSourceRepositoryAccessOverride{}, "idx_kb_src_repo_access_override_lookup"))
	require.True(t, db.Migrator().HasIndex(&kb.SourceContentMapping{}, "idx_kb_source_content_mapping_content"))
	require.True(t, db.Migrator().HasColumn(&repos.ResourceRepositoryAccessGrant{}, "materialized"))
	require.True(t, db.Migrator().HasIndex(&repos.ResourceRepositoryAccessGrant{}, "idx_resource_repo_access_intent_lookup"))
	require.True(t, db.Migrator().HasIndex(&repos.ResourceRepositoryAccessGrant{}, "idx_resource_repo_access_repo_subject"))
	require.True(t, db.Migrator().HasColumn(&kb.Source{}, "analysis_status"))
	require.True(t, db.Migrator().HasColumn(&kb.Source{}, "freshness_status"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceRevision{}, "insights_commit_sha"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceRevision{}, "ua_dashboard_commit_sha"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceRevision{}, "ua_dashboard_manifest_json"))
	require.True(t, db.Migrator().HasColumn(&kb.SourceRevision{}, "ua_dashboard_published_at"))
}

func TestMigrateCreatesNativeKBACLIndexesOnFreshAndUpgrade(t *testing.T) {
	for _, mode := range []string{"fresh", "upgrade"} {
		t.Run(mode, func(t *testing.T) {
			db, err := open(Config{DSN: "file:meshx_native_kb_acl_indexes_" + mode + "?mode=memory&cache=shared"}, false)
			require.NoError(t, err)
			defer func() { require.NoError(t, Close(db)) }()
			if mode == "upgrade" {
				require.NoError(t, db.AutoMigrate(&kb.KnowledgeBaseSourceRepositoryAccessOverride{}, &kb.SourceContentMapping{}))
				require.NoError(t, db.Migrator().DropIndex(&kb.KnowledgeBaseSourceRepositoryAccessOverride{}, "idx_kb_src_repo_access_override_lookup"))
				require.NoError(t, db.Migrator().DropIndex(&kb.SourceContentMapping{}, "idx_kb_source_content_mapping_content"))
			}
			require.NoError(t, Migrate(db))

			overrideColumns, err := automationIndexColumns(db, "knowledge_base_source_repository_access_overrides", "idx_kb_src_repo_access_override_lookup")
			require.NoError(t, err)
			require.Equal(t, []string{"workspace_id", "grant_id", "source_id", "repo_role"}, overrideColumns)
			mappingColumns, err := automationIndexColumns(db, "knowledge_base_source_content_mappings", "idx_kb_source_content_mapping_content")
			require.NoError(t, err)
			require.Equal(t, []string{"workspace_id", "knowledge_base_id", "source_id", "content_id", "repository_role"}, mappingColumns)
		})
	}
}

func TestMigrateCreatesContentVersionOperationReceiptSchemaOnFreshAndUpgrade(t *testing.T) {
	for _, mode := range []string{"fresh", "upgrade"} {
		t.Run(mode, func(t *testing.T) {
			db, err := open(Config{DSN: "file:meshx_content_operation_" + mode + "?mode=memory&cache=shared"}, false)
			require.NoError(t, err)
			defer func() { require.NoError(t, Close(db)) }()
			if mode == "upgrade" {
				require.NoError(t, db.AutoMigrate(&content.VersionedContent{}, &content.ContentVersion{}))
			}
			require.NoError(t, Migrate(db))
			require.True(t, db.Migrator().HasTable(&content.VersionOperation{}))
			require.True(t, db.Migrator().HasIndex(&content.VersionOperation{}, "uk_content_version_operation"))
			columns, err := automationIndexColumns(db, "content_version_operations", "uk_content_version_operation")
			require.NoError(t, err)
			require.Equal(t, []string{"workspace_id", "content_id", "operation_key"}, columns)
			for _, column := range []string{"request_digest", "operation_namespace", "bounds_policy_namespace", "bounds_policy_version", "max_files", "max_file_bytes", "max_total_bytes", "max_manifest_bytes", "expected_head_version_id", "version_id", "content_revision", "retained_version_count", "stored_payload_bytes"} {
				require.True(t, db.Migrator().HasColumn(&content.VersionOperation{}, column), column)
			}
		})
	}
}