package database

import (
	"reflect"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"

	"github.com/tidbcloud/AgentTeam/internal/store/content/migration"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/roles"
	"github.com/tidbcloud/AgentTeam/internal/store/resource/skills"
)

func TestMigrateCreatesSkillSchema(t *testing.T) {
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, Migrate(db))

	migrator := db.Migrator()
	for _, model := range []any{&skills.Skill{}, &skills.SkillVersion{}, &skills.SkillRepositoryBranch{}, &skills.SkillAgentBinding{}, &skills.SkillGrant{}} {
		require.True(t, migrator.HasTable(model), "%T should be migrated", model)
	}
	require.True(t, migrator.HasIndex(&skills.SkillGrant{}, "uk_skill_grants_subject"))
	require.True(t, migrator.HasIndex(&skills.SkillRepositoryBranch{}, "uk_skill_repo_branch_repo"))
	require.False(t, migrator.HasIndex(&skills.Skill{}, "uk_skill_workspace_slug"))
	require.True(t, migrator.HasIndex(&skills.Skill{}, "uk_skill_workspace_visibility_slug"))
	require.True(t, migrator.HasIndex(&skills.Skill{}, "idx_skills_workspace_creator_deleted"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "skill_count"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "official_source_fingerprint"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "visibility_scope"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "visibility_owner_id"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "content_id"))
	require.True(t, migrator.HasColumn(&skills.Skill{}, "active_content_version_id"))
	require.True(t, migrator.HasIndex(&skills.Skill{}, "idx_skills_exact_content"))
	require.True(t, migrator.HasIndex(&skills.SkillVersion{}, "idx_skill_versions_history"))
	require.True(t, migrator.HasIndex(&skills.SkillVersion{}, "uk_skill_content_version"))
	require.True(t, migrator.HasIndex(&skills.SkillVersion{}, "idx_skill_versions_content"))
	requireIndexColumns(t, db, "skills", "idx_skills_exact_content", "workspace_id", "id", "content_id", "active_content_version_id")
	requireIndexColumns(t, db, "skill_versions", "idx_skill_versions_history", "workspace_id", "skill_id", "created_at", "id")
	requireIndexColumns(t, db, "skill_versions", "uk_skill_content_version", "workspace_id", "skill_id", "content_id", "content_version_id")
	requireIndexColumns(t, db, "skill_versions", "idx_skill_versions_content", "workspace_id", "content_id", "content_version_id")
	requireIndexColumns(t, db, "role_versions", "idx_role_versions_exact_content", "workspace_id", "role_id", "content_id", "content_version_id")
	requireIndexColumns(t, db, "role_versions", "idx_role_versions_history", "workspace_id", "role_id", "content_id", "created_at", "id")
	requireIndexColumns(t, db, "role_package_manifest_items", "idx_role_pkg_manifest_scope_content", "workspace_id", "role_id", "role_version_id", "content_id", "content_version_id")
	require.True(t, migrator.HasTable(&migration.Mapping{}))
	require.True(t, migrator.HasTable(&migration.Job{}))
	require.True(t, migrator.HasIndex(&migration.Job{}, "uk_content_migration_job_target"))
	requireIndexColumns(t, db, "content_ags_migration_jobs", "uk_content_migration_job_target", "workspace_id", "domain", "binding_key", "source_cursor")
	require.True(t, migrator.HasIndex(&skills.SkillAgentBinding{}, "uk_skill_agent_binding"))
	require.True(t, migrator.HasIndex(&skills.SkillAgentBinding{}, "idx_skill_agent_bindings_agent"))
	require.False(t, migrator.HasIndex(&skills.SkillAgentBinding{}, "idx_skill_agent_bindings_repo"))
	require.False(t, migrator.HasColumn(&skills.SkillAgentBinding{}, "repo_owner"))
	require.False(t, migrator.HasColumn(&skills.SkillAgentBinding{}, "repo_name"))
}

func TestMigrateUpgradesRoleSkillExactContentProjectionSchema(t *testing.T) {
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, db.AutoMigrate(
		&oldSkillWithWorkspaceSlugIndex{},
		&oldRoleVersionBeforeContentProjection{},
		&oldRoleManifestBeforeContentProjection{},
	))
	now := time.Date(2026, 8, 20, 21, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&oldRoleVersionBeforeContentProjection{
		ID: "rlv_legacy", RoleID: "rol_legacy", WorkspaceID: "wsp_1", Version: 1,
		Status: "published", Definition: "legacy", CreatedBy: "usr_1", CreatedAt: now,
	}).Error)
	require.NoError(t, db.Create(&oldRoleManifestBeforeContentProjection{
		ID: "rpm_legacy", RoleVersionID: "rlv_legacy", RoleID: "rol_legacy", WorkspaceID: "wsp_1",
		ContentRef: "legacy://role", SortOrder: 0,
	}).Error)

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

	migrator := db.Migrator()
	for _, column := range []string{"content_id", "active_content_version_id"} {
		require.True(t, migrator.HasColumn(&skills.Skill{}, column))
	}
	require.True(t, migrator.HasTable(&skills.SkillVersion{}))
	for _, column := range []string{"content_id", "content_version_id"} {
		require.True(t, migrator.HasColumn(&roles.RoleVersion{}, column))
	}
	for _, column := range []string{"content_id", "content_version_id", "content_path"} {
		require.True(t, migrator.HasColumn(&roles.RolePackageManifestItem{}, column))
	}
	requireIndexColumns(t, db, "skill_versions", "idx_skill_versions_history", "workspace_id", "skill_id", "created_at", "id")
	requireIndexColumns(t, db, "skill_versions", "uk_skill_content_version", "workspace_id", "skill_id", "content_id", "content_version_id")
	requireIndexColumns(t, db, "role_versions", "idx_role_versions_exact_content", "workspace_id", "role_id", "content_id", "content_version_id")
	requireIndexColumns(t, db, "role_versions", "idx_role_versions_history", "workspace_id", "role_id", "content_id", "created_at", "id")
	requireIndexColumns(t, db, "role_package_manifest_items", "idx_role_pkg_manifest_scope_content", "workspace_id", "role_id", "role_version_id", "content_id", "content_version_id")

	var version roles.RoleVersion
	require.NoError(t, db.First(&version, "id = ?", "rlv_legacy").Error)
	require.Empty(t, version.ContentID)
	require.Empty(t, version.ContentVersionID)
	var manifest roles.RolePackageManifestItem
	require.NoError(t, db.First(&manifest, "id = ?", "rpm_legacy").Error)
	require.Equal(t, "legacy://role", manifest.ContentRef)
	require.Empty(t, manifest.ContentPath)
}

type oldRoleVersionBeforeContentProjection struct {
	ID                     string    `gorm:"primaryKey;size:64"`
	RoleID                 string    `gorm:"size:64;not null;index:idx_role_versions_role_version;uniqueIndex:uk_role_versions_role_version"`
	WorkspaceID            string    `gorm:"size:64;not null;index"`
	Version                int       `gorm:"not null;uniqueIndex:uk_role_versions_role_version"`
	Status                 string    `gorm:"size:32;not null;default:'draft';index"`
	Definition             string    `gorm:"type:text;not null"`
	RuntimeHintsJSON       string    `gorm:"column:runtime_hints_json;type:text;not null"`
	ProvenanceJSON         string    `gorm:"column:provenance_json;type:text;not null"`
	NonPortableSummaryJSON string    `gorm:"column:non_portable_summary_json;type:text;not null"`
	CreatedBy              string    `gorm:"size:64;not null"`
	CreatedAt              time.Time `gorm:"not null"`
}

func (oldRoleVersionBeforeContentProjection) TableName() string { return "role_versions" }

type oldRoleManifestBeforeContentProjection struct {
	ID                string `gorm:"primaryKey;size:64"`
	RoleVersionID     string `gorm:"size:64;not null;index:idx_role_pkg_manifest_role_version_sort"`
	RoleID            string `gorm:"size:64;not null;index"`
	WorkspaceID       string `gorm:"size:64;not null;index"`
	ItemType          string `gorm:"column:item_type;size:64;not null;index"`
	Name              string `gorm:"size:255;not null;default:''"`
	Description       string `gorm:"type:text;not null"`
	Required          bool   `gorm:"not null;default:false"`
	RealizationAction string `gorm:"column:realization_action;size:32;not null;default:''"`
	SourceKind        string `gorm:"column:source_kind;size:32;not null;default:''"`
	SourceResourceID  string `gorm:"column:source_resource_id;size:128;not null;default:''"`
	SourceRef         string `gorm:"column:source_ref;type:text;not null"`
	ContentRef        string `gorm:"column:content_ref;type:text;not null"`
	ContentHash       string `gorm:"column:content_hash;size:128;not null;default:''"`
	AccessLevel       string `gorm:"column:access_level;size:32;not null;default:''"`
	Branch            string `gorm:"size:255;not null;default:''"`
	Content           string `gorm:"type:text;not null"`
	SortOrder         int    `gorm:"column:sort_order;not null;default:0;index:idx_role_pkg_manifest_role_version_sort"`
	MetadataJSON      string `gorm:"column:metadata_json;type:text;not null"`
}

func (oldRoleManifestBeforeContentProjection) TableName() string {
	return "role_package_manifest_items"
}

func TestRoleSkillContentProjectionModelsDoNotCarryJSONTags(t *testing.T) {
	for _, model := range []reflect.Type{
		reflect.TypeOf(migration.Mapping{}),
		reflect.TypeOf(migration.Job{}),
		reflect.TypeOf(skills.Skill{}),
		reflect.TypeOf(skills.SkillVersion{}),
		reflect.TypeOf(roles.RoleVersion{}),
		reflect.TypeOf(roles.RolePackageManifestItem{}),
	} {
		for fieldIndex := 0; fieldIndex < model.NumField(); fieldIndex++ {
			require.Empty(t, model.Field(fieldIndex).Tag.Get("json"), "%s.%s must not carry API JSON tags", model.Name(), model.Field(fieldIndex).Name)
		}
	}
}

func TestVerifySchemaRejectsSkillHistoryIndexWithoutStableTieBreaker(t *testing.T) {
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, Migrate(db))
	require.NoError(t, db.Migrator().DropIndex(&skills.SkillVersion{}, "idx_skill_versions_history"))
	require.NoError(t, db.Exec("CREATE INDEX idx_skill_versions_history ON skill_versions (workspace_id, skill_id, created_at)").Error)

	err = VerifySchema(db)
	require.ErrorContains(t, err, "skill_versions.idx_skill_versions_history columns workspace_id,skill_id,created_at want workspace_id,skill_id,created_at,id")
}

func requireIndexColumns(t *testing.T, db *gorm.DB, table, index string, want ...string) {
	t.Helper()
	columns, err := automationIndexColumns(db, table, index)
	require.NoError(t, err)
	require.Equal(t, want, columns)
}

type oldSkillWithWorkspaceSlugIndex struct {
	ID                string     `gorm:"primaryKey;size:64"`
	WorkspaceID       string     `gorm:"size:64;not null;uniqueIndex:uk_skill_workspace_slug"`
	Slug              string     `gorm:"size:255;not null;default:'';uniqueIndex:uk_skill_workspace_slug"`
	Name              string     `gorm:"size:255;not null"`
	Description       string     `gorm:"type:text;not null"`
	StorageScope      string     `gorm:"size:32;not null;default:'personal'"`
	StorageOwnerLogin string     `gorm:"size:128;not null;default:''"`
	SkillCount        int        `gorm:"not null;default:0"`
	IsActive          bool       `gorm:"not null;index"`
	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"`
	DeletedAt         *time.Time `gorm:"index"`
}

func (oldSkillWithWorkspaceSlugIndex) TableName() string {
	return "skills"
}

func TestMigrateSkillVisibilityScopeReplacesWorkspaceSlugUniqueness(t *testing.T) {
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, db.AutoMigrate(&oldSkillWithWorkspaceSlugIndex{}))
	now := time.Date(2026, 7, 8, 1, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&oldSkillWithWorkspaceSlugIndex{
		ID:                "skill_legacy",
		WorkspaceID:       "ws_1",
		Slug:              "abc",
		Name:              "Legacy abc",
		Description:       "legacy",
		StorageScope:      "workspace",
		StorageOwnerLogin: "ws_1",
		IsActive:          true,
		CreatedBy:         "usr_legacy",
		UpdatedBy:         "usr_legacy",
		CreatedAt:         now,
		UpdatedAt:         now,
	}).Error)

	require.NoError(t, Migrate(db))

	migrator := db.Migrator()
	require.False(t, migrator.HasIndex(&skills.Skill{}, "uk_skill_workspace_slug"))
	require.True(t, migrator.HasIndex(&skills.Skill{}, "uk_skill_workspace_visibility_slug"))

	var migrated skills.Skill
	require.NoError(t, db.First(&migrated, "id = ?", "skill_legacy").Error)
	require.Equal(t, "private", migrated.VisibilityScope)
	require.Equal(t, "usr_legacy", migrated.VisibilityOwnerID)

	for _, skill := range []skills.Skill{
		{ID: "skill_bob", WorkspaceID: "ws_1", Slug: "abc", Name: "Bob abc", VisibilityScope: "private", VisibilityOwnerID: "usr_bob", IsActive: true, CreatedBy: "usr_bob", UpdatedBy: "usr_bob", CreatedAt: now, UpdatedAt: now},
		{ID: "skill_workspace", WorkspaceID: "ws_1", Slug: "abc", Name: "Workspace abc", VisibilityScope: "workspace", VisibilityOwnerID: "ws_1", IsActive: true, CreatedBy: "usr_legacy", UpdatedBy: "usr_legacy", CreatedAt: now, UpdatedAt: now},
	} {
		require.NoError(t, db.Create(&skill).Error)
	}

	require.Error(t, db.Create(&skills.Skill{ID: "skill_duplicate", WorkspaceID: "ws_1", Slug: "abc", Name: "Duplicate abc", VisibilityScope: "private", VisibilityOwnerID: "usr_legacy", IsActive: true, CreatedBy: "usr_legacy", UpdatedBy: "usr_legacy", CreatedAt: now, UpdatedAt: now}).Error)
}

type oldSkillAgentBindingWithRepoSnapshot struct {
	ID          string `gorm:"primaryKey;size:64"`
	WorkspaceID string `gorm:"size:64;not null;index:idx_skill_agent_bindings_agent;uniqueIndex:uk_skill_agent_binding"`
	AgentID     string `gorm:"size:64;not null;index:idx_skill_agent_bindings_agent;uniqueIndex:uk_skill_agent_binding"`
	SkillID     string `gorm:"size:64;not null;index:idx_skill_agent_bindings_skill;uniqueIndex:uk_skill_agent_binding"`
	RepoOwner   string `gorm:"size:128;not null;index:idx_skill_agent_bindings_repo"`
	RepoName    string `gorm:"size:128;not null;index:idx_skill_agent_bindings_repo"`
	Branch      string `gorm:"size:255;not null;default:'main'"`
	CreatedBy   string `gorm:"size:64;not null"`
	UpdatedBy   string `gorm:"size:64;not null"`
	CreatedAt   time.Time
	UpdatedAt   time.Time `gorm:"not null;index"`
}

func (oldSkillAgentBindingWithRepoSnapshot) TableName() string {
	return "skill_agent_bindings"
}

func TestMigrateDropsSkillAgentBindingRepoSnapshotColumns(t *testing.T) {
	db, err := gorm.Open(sqlite.Open("file:"+t.Name()+"?mode=memory&cache=shared"), &gorm.Config{})
	require.NoError(t, err)
	require.NoError(t, db.AutoMigrate(&oldSkillAgentBindingWithRepoSnapshot{}))
	now := time.Date(2026, 6, 18, 1, 0, 0, 0, time.UTC)
	require.NoError(t, db.Create(&oldSkillAgentBindingWithRepoSnapshot{
		ID:          "sab_legacy",
		WorkspaceID: "ws_1",
		AgentID:     "agt_1",
		SkillID:     "skill_1",
		RepoOwner:   "usr_1",
		RepoName:    "reviewer.sk",
		Branch:      "main",
		CreatedBy:   "usr_1",
		UpdatedBy:   "usr_1",
		CreatedAt:   now,
		UpdatedAt:   now,
	}).Error)

	require.NoError(t, Migrate(db))

	migrator := db.Migrator()
	require.False(t, migrator.HasColumn(&skills.SkillAgentBinding{}, "repo_owner"))
	require.False(t, migrator.HasColumn(&skills.SkillAgentBinding{}, "repo_name"))
	require.False(t, migrator.HasIndex(&skills.SkillAgentBinding{}, "idx_skill_agent_bindings_repo"))

	var binding skills.SkillAgentBinding
	require.NoError(t, db.First(&binding, "id = ?", "sab_legacy").Error)
	require.Equal(t, "agt_1", binding.AgentID)
	require.Equal(t, "skill_1", binding.SkillID)
	require.Equal(t, "main", binding.Branch)
}