package tidb

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

	"github.com/stretchr/testify/require"
)

// The business-data Apps land after the 00350 IM bot migration, so 00351
// must seed extension_definition_versions alongside the legacy rows under the
// same contract: production-catalog visibility for the registry JOIN, an
// executed Up -> Down -> Up cycle (selections disabled and restored by this
// migration's rollback marker), and the explicit SIGNAL drift guard covering
// identity, version, manifest, and callback drift.

const businessDataMigrationVersion int64 = 351

// businessDataRollbackMarker is the disabled_reason 00351's Down stamps and
// its Up restores — and only that.
const businessDataRollbackMarker = "migration_00351_business_data_rollback"

var businessDataAppIDs = []string{"app_tianyancha", "app_qichacha"}

// firstPartyCatalogCount mirrors the first-party branch of the registry's
// ListApps catalog JOIN: latest-version JOIN, public visibility, and a
// first_party appKind. Counting on status='active' alone stays green while
// the production catalog hides the App, so every visibility assertion in
// this suite goes through this predicate.
func firstPartyCatalogCount(t *testing.T, ctx context.Context, db *sql.DB, appIDs []string) int {
	t.Helper()
	placeholders := ""
	arguments := make([]any, 0, len(appIDs))
	for index, appID := range appIDs {
		if index > 0 {
			placeholders += ","
		}
		placeholders += "?"
		arguments = append(arguments, appID)
	}
	var visible int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM service_definitions app
JOIN extension_definition_versions version
  ON version.service_definition_id = app.id
 AND version.version = app.latest_version
WHERE app.id IN (`+placeholders+`)
  AND app.visibility = 'public'
  AND JSON_UNQUOTE(JSON_EXTRACT(COALESCE(app.config, JSON_OBJECT()), '$.appKind')) = 'first_party'`,
		arguments...).Scan(&visible))
	return visible
}

func TestBusinessDataMigrationSeedsExtensionDefinitionVersions(t *testing.T) {
	ctx := context.Background()
	db := openIsolatedTiDBForMigrationTest(t, ctx)
	now := time.Date(2026, 8, 19, 0, 0, 0, 0, time.UTC)

	provider := applyVersionedMigrationsUpToForTest(t, ctx, db, businessDataMigrationVersion)
	assertBusinessDataSeedState(t, ctx, db)
	assertFirstPartyCatalogPredicatesFailClosed(t, ctx, db)

	// Plant live workspace state the Down must disable and the next Up must
	// restore: an active selection pinned to this migration's definition,
	// plus a user-disabled selection that neither direction may touch.
	selections := plantBusinessDataSelectionFixtures(t, ctx, db, now)

	// Down is a real rollback, not a no-op: the Apps leave the production
	// catalog, their capability defaults drop, and exactly the active
	// selections of these two definitions flip to the rollback marker.
	_, err := provider.DownTo(ctx, businessDataMigrationVersion-1)
	require.NoError(t, err)
	assertBusinessDataDownState(t, ctx, db, selections)

	// Up restores only the rows this migration's Down marked: the marked
	// selection returns to active and the user-disabled row stays disabled.
	// The drift guard sees byte-identical rows, so the seed inserts degrade
	// to no-ops.
	_, err = provider.UpTo(ctx, businessDataMigrationVersion)
	require.NoError(t, err)
	assertBusinessDataSelectionRestored(t, ctx, db, selections)
	assertBusinessDataSeedState(t, ctx, db)
}

// assertFirstPartyCatalogPredicatesFailClosed mutates one predicate at a time
// after a valid seed. Removing either appKind or the latest-version JOIN from
// firstPartyCatalogCount must make this test fail.
func assertFirstPartyCatalogPredicatesFailClosed(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()

	_, err := db.ExecContext(ctx, `
UPDATE service_definitions
SET config = JSON_SET(COALESCE(config, JSON_OBJECT()), '$.appKind', 'third_party')
WHERE id = 'app_tianyancha'`)
	require.NoError(t, err)
	require.Equal(t, len(businessDataAppIDs)-1, firstPartyCatalogCount(t, ctx, db, businessDataAppIDs),
		"a non-first-party App must disappear from the production catalog")
	_, err = db.ExecContext(ctx, `
UPDATE service_definitions
SET config = JSON_SET(COALESCE(config, JSON_OBJECT()), '$.appKind', 'first_party')
WHERE id = 'app_tianyancha'`)
	require.NoError(t, err)
	require.Equal(t, len(businessDataAppIDs), firstPartyCatalogCount(t, ctx, db, businessDataAppIDs))

	_, err = db.ExecContext(ctx, `
UPDATE service_definitions
SET latest_version = '9.9.9'
WHERE id = 'app_qichacha'`)
	require.NoError(t, err)
	require.Equal(t, len(businessDataAppIDs)-1, firstPartyCatalogCount(t, ctx, db, businessDataAppIDs),
		"an App without its pinned latest version must disappear from the production catalog")
	_, err = db.ExecContext(ctx, `
UPDATE service_definitions
SET latest_version = '1.0.0'
WHERE id = 'app_qichacha'`)
	require.NoError(t, err)
	require.Equal(t, len(businessDataAppIDs), firstPartyCatalogCount(t, ctx, db, businessDataAppIDs))
}

// TestBusinessDataMigrationUpRestoreSkipsTakenNames proves the restore's
// active-owner guard: when another definition already holds the workspace
// extension name the marked selection used, the replayed Up leaves the
// marked row disabled instead of colliding with the live owner.
func TestBusinessDataMigrationUpRestoreSkipsTakenNames(t *testing.T) {
	ctx := context.Background()
	db := openIsolatedTiDBForMigrationTest(t, ctx)
	now := time.Date(2026, 8, 19, 0, 0, 0, 0, time.UTC)

	provider := applyVersionedMigrationsUpToForTest(t, ctx, db, businessDataMigrationVersion)
	selections := plantBusinessDataSelectionFixtures(t, ctx, db, now)

	_, err := provider.DownTo(ctx, businessDataMigrationVersion-1)
	require.NoError(t, err)

	// After the Down frees the workspace extension name, a different
	// definition takes it over with its own active selection.
	decoyID := plantBusinessDataNameOwnerFixture(t, ctx, db, now)

	_, err = provider.UpTo(ctx, businessDataMigrationVersion)
	require.NoError(t, err)

	var status, disabledReason string
	require.NoError(t, db.QueryRowContext(ctx,
		`SELECT status, disabled_reason FROM service_installation_extensions WHERE id = ?`,
		selections.markedID).Scan(&status, &disabledReason))
	require.Equal(t, "disabled", status)
	require.Equal(t, businessDataRollbackMarker, disabledReason)
	require.NoError(t, db.QueryRowContext(ctx,
		`SELECT status FROM service_installation_extensions WHERE id = ?`,
		decoyID).Scan(&status))
	require.Equal(t, "active", status)
	assertBusinessDataSeedState(t, ctx, db)
}

func TestBusinessDataMigrationAbortsOnExtensionVersionDrift(t *testing.T) {
	ctx := context.Background()
	db := openIsolatedTiDBForMigrationTest(t, ctx)

	provider := applyVersionedMigrationsUpToForTest(t, ctx, db, businessDataMigrationVersion-1)
	now := time.Date(2026, 8, 19, 0, 0, 0, 0, time.UTC)
	// The slug and kind must match what 00351 itself inserts: the legacy
	// tables carry drift-bomb ON DUPLICATE guards, so a mismatched fixture
	// row would abort there instead of reaching the version drift guard.
	plantDefinitionForFK(t, ctx, db, "app_tianyancha", "tianyancha", now)
	_, err := db.ExecContext(ctx, `
INSERT INTO extension_definition_versions (id, service_definition_id, version, manifest, callback_url,
  created_at, updated_at)
VALUES ('appver_tianyancha_1_0_0', 'app_tianyancha', '1.0.0',
  JSON_OBJECT('version','0.9.9','kind','historical'), '', ?, ?)
`, now, now)
	require.NoError(t, err)

	_, err = provider.UpTo(ctx, businessDataMigrationVersion)
	require.Error(t, err)
	require.Contains(t, err.Error(), "00351 business data apps")
	require.Contains(t, err.Error(), "canonical extension version drift")

	// The historical row is not mutated and the migration leaves no partial
	// version seed for the other App.
	var manifestVersion string
	require.NoError(t, db.QueryRowContext(ctx,
		`SELECT JSON_UNQUOTE(JSON_EXTRACT(manifest, '$.version'))
		 FROM extension_definition_versions WHERE id = 'appver_tianyancha_1_0_0'`).Scan(&manifestVersion))
	require.Equal(t, "0.9.9", manifestVersion)
	var seeded int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM extension_definition_versions
WHERE service_definition_id = 'app_qichacha'
`).Scan(&seeded))
	require.Equal(t, 0, seeded)
}

func TestBusinessDataMigrationAbortsOnExtensionVersionIdentityDrift(t *testing.T) {
	ctx := context.Background()
	db := openIsolatedTiDBForMigrationTest(t, ctx)

	// Identity-only drift: seed everything normally, then reassign one
	// version row to a decoy definition while its version, manifest, and
	// callback stay byte-identical. The soft-rollback Down keeps the row, so
	// replaying the Up must abort on the identity comparison alone — the id
	// guard would otherwise skip the insert and silently starve the App's
	// catalog JOIN.
	provider := applyVersionedMigrationsUpToForTest(t, ctx, db, businessDataMigrationVersion)
	now := time.Date(2026, 8, 19, 0, 0, 0, 0, time.UTC)
	plantDefinitionForFK(t, ctx, db, "app_identity_decoy_biz", "identity-decoy-biz", now)
	_, err := db.ExecContext(ctx, `
UPDATE extension_definition_versions
SET service_definition_id = 'app_identity_decoy_biz'
WHERE id = 'appver_qichacha_1_0_0'
`)
	require.NoError(t, err)

	_, err = provider.DownTo(ctx, businessDataMigrationVersion-1)
	require.NoError(t, err)
	_, err = provider.UpTo(ctx, businessDataMigrationVersion)
	require.Error(t, err)
	require.Contains(t, err.Error(), "00351 business data apps")
	require.Contains(t, err.Error(), "canonical extension version drift")

	// Restoring the identity and replaying proves the guard is precise, not
	// a blanket rejection of reruns.
	_, err = db.ExecContext(ctx, `
UPDATE extension_definition_versions
SET service_definition_id = 'app_qichacha'
WHERE id = 'appver_qichacha_1_0_0'
`)
	require.NoError(t, err)
	_, err = provider.UpTo(ctx, businessDataMigrationVersion)
	require.NoError(t, err)
}

// assertBusinessDataSeedState proves every business-data App has exactly one
// extension_definition_versions row mirroring its legacy app_versions row
// byte for byte, and that the production catalog JOIN the registry uses
// surfaces both Apps with an installable latest version.
func assertBusinessDataSeedState(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	requireEquivalentVersionRows(t, ctx, db)
	require.Equal(t, len(businessDataAppIDs), firstPartyCatalogCount(t, ctx, db, businessDataAppIDs),
		"every business-data App must remain visible through the production catalog JOIN")
}

// requireEquivalentVersionRows proves each App still owns exactly one legacy
// app_versions row and exactly one byte-equivalent
// extension_definition_versions row, independent of any catalog visibility
// condition. The Down-state check reuses it so a rollback that wrongly
// deleted rows (and let the next Up silently rebuild them) fails here, not
// only at the converged end state.
func requireEquivalentVersionRows(t *testing.T, ctx context.Context, db *sql.DB) {
	t.Helper()
	for _, appID := range businessDataAppIDs {
		var legacyRows int
		require.NoError(t, db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM app_versions WHERE app_id = ?`, appID).Scan(&legacyRows))
		require.Equal(t, 1, legacyRows, "exactly one legacy row for %s survives", appID)
		var versionRows int
		require.NoError(t, db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM extension_definition_versions WHERE service_definition_id = ?`,
			appID).Scan(&versionRows))
		require.Equal(t, 1, versionRows, "exactly one version row for %s survives", appID)

		var pairs int
		require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM app_versions legacy
JOIN extension_definition_versions pinned ON pinned.id = legacy.id
WHERE legacy.app_id = ?
  AND pinned.service_definition_id = legacy.app_id
  AND pinned.version = legacy.version
  AND pinned.callback_url = legacy.callback_url
  AND CAST(pinned.manifest AS CHAR) = CAST(legacy.manifest AS CHAR)
`, appID).Scan(&pairs))
		require.Equal(t, 1, pairs, "exactly one equivalent version row for %s", appID)
	}
}

// assertBusinessDataDownState locks the executed Down: hidden Apps, kept
// rows, dropped capability defaults, and the rollback marker on exactly the
// active selections of the two definitions.
func assertBusinessDataDownState(
	t *testing.T, ctx context.Context, db *sql.DB, selections businessDataSelectionFixtures,
) {
	t.Helper()
	require.Equal(t, 0, firstPartyCatalogCount(t, ctx, db, businessDataAppIDs),
		"Down must hide both Apps from the production catalog JOIN")

	// The soft rollback deletes nothing: both capability rows still exist
	// and are disabled, and the legacy/version row pairs stay equivalent.
	var capabilityRows int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM service_capabilities
WHERE service_definition_id IN ('app_tianyancha', 'app_qichacha')
`).Scan(&capabilityRows))
	require.Equal(t, len(businessDataAppIDs), capabilityRows,
		"Down must keep both capability rows")
	var enabledDefaults int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM service_capabilities
WHERE service_definition_id IN ('app_tianyancha', 'app_qichacha')
  AND default_enabled
`).Scan(&enabledDefaults))
	require.Equal(t, 0, enabledDefaults, "Down must drop the capability defaults")
	requireEquivalentVersionRows(t, ctx, db)

	var hiddenDefinitions int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM service_definitions
WHERE id IN ('app_tianyancha', 'app_qichacha')
  AND status = 'disabled' AND visibility = 'unlisted'
`).Scan(&hiddenDefinitions))
	require.Equal(t, len(businessDataAppIDs), hiddenDefinitions)

	var unlistedApps int
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT COUNT(*) FROM apps
WHERE id IN ('app_tianyancha', 'app_qichacha') AND visibility = 'unlisted'
`).Scan(&unlistedApps))
	require.Equal(t, len(businessDataAppIDs), unlistedApps)

	var status, disabledReason string
	var disabledAtSet bool
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT status, disabled_reason, disabled_at IS NOT NULL
FROM service_installation_extensions WHERE id = ?
`, selections.markedID).Scan(&status, &disabledReason, &disabledAtSet))
	require.Equal(t, "disabled", status)
	require.Equal(t, businessDataRollbackMarker, disabledReason)
	require.True(t, disabledAtSet)

	require.NoError(t, db.QueryRowContext(ctx, `
SELECT status, disabled_reason FROM service_installation_extensions WHERE id = ?
`, selections.userDisabledID).Scan(&status, &disabledReason))
	require.Equal(t, "disabled", status)
	require.Equal(t, "user_test_disabled", disabledReason,
		"Down must not touch selections the user already disabled")
}

// assertBusinessDataSelectionRestored locks the replayed Up's restore: only
// this migration's marker row returns to active.
func assertBusinessDataSelectionRestored(
	t *testing.T, ctx context.Context, db *sql.DB, selections businessDataSelectionFixtures,
) {
	t.Helper()
	var status, disabledReason string
	var disabledAtSet bool
	require.NoError(t, db.QueryRowContext(ctx, `
SELECT status, disabled_reason, disabled_at IS NOT NULL
FROM service_installation_extensions WHERE id = ?
`, selections.markedID).Scan(&status, &disabledReason, &disabledAtSet))
	require.Equal(t, "active", status)
	require.Equal(t, "", disabledReason)
	require.False(t, disabledAtSet)

	require.NoError(t, db.QueryRowContext(ctx, `
SELECT status, disabled_reason FROM service_installation_extensions WHERE id = ?
`, selections.userDisabledID).Scan(&status, &disabledReason))
	require.Equal(t, "disabled", status)
	require.Equal(t, "user_test_disabled", disabledReason,
		"the restore must not re-enable user-disabled selections")
}

// businessDataSelectionFixtures pins one workspace's selection state around
// the Down/Up boundary.
type businessDataSelectionFixtures struct {
	workspaceID    string
	memberID       string
	markedID       string // active selection on app_tianyancha the Down must mark
	userDisabledID string // user-disabled selection on app_qichacha both directions must leave alone
}

func plantBusinessDataSelectionFixtures(
	t *testing.T, ctx context.Context, db *sql.DB, now time.Time,
) businessDataSelectionFixtures {
	t.Helper()
	fixtures := businessDataSelectionFixtures{
		workspaceID:    "wsp_bizdata_sel",
		memberID:       "mbr_bizdata_sel",
		markedID:       "sel_bizdata_tianyancha",
		userDisabledID: "sel_bizdata_qichacha_user",
	}

	_, err := db.ExecContext(ctx, `
INSERT INTO users (id, email, name, display_name, password_hash, created_at, updated_at)
VALUES ('usr_bizdata_sel', 'bizdata-sel@example.com', 'bizdata_sel', 'Bizdata Sel', 'hash', ?, ?)
`, now, now)
	require.NoError(t, err)
	_, err = db.ExecContext(ctx, `
INSERT INTO organizations (id, slug, name, created_by_user_id, created_at, updated_at)
VALUES ('org_bizdata_sel', 'bizdata-sel', 'Bizdata Sel', 'usr_bizdata_sel', ?, ?)
`, now, now)
	require.NoError(t, err)
	_, err = db.ExecContext(ctx, `
INSERT INTO workspaces (id, organization_id, slug, name, created_at, updated_at)
VALUES (?, 'org_bizdata_sel', 'bizdata-sel', 'Bizdata Sel', ?, ?)
`, fixtures.workspaceID, now, now)
	require.NoError(t, err)
	_, err = db.ExecContext(ctx, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, status, created_at, updated_at)
VALUES (?, ?, 'human', ?, 'usr_bizdata_sel', 'admin', 'active', ?, ?)
`, fixtures.memberID, fixtures.workspaceID, fixtures.memberID, now, now)
	require.NoError(t, err)

	_, err = db.ExecContext(ctx, `
INSERT INTO service_installations (id, workspace_id, service_definition_id, service_kind,
  status, installed_version, created_by_member_id, created_at, updated_at)
VALUES
  ('inst_bizdata_tianyancha', ?, 'app_tianyancha', 'app', 'active', '1.0.0', ?, ?, ?),
  ('inst_bizdata_qichacha', ?, 'app_qichacha', 'app', 'active', '1.0.0', ?, ?, ?)
`, fixtures.workspaceID, fixtures.memberID, now, now,
		fixtures.workspaceID, fixtures.memberID, now, now)
	require.NoError(t, err)

	_, err = db.ExecContext(ctx, `
INSERT INTO service_installation_extensions (
  id, workspace_id, service_definition_id, service_installation_id,
  extension_name, definition_domain, definition_version, definition_digest,
  status, disabled_reason, selection_source, created_by_member_id,
  created_at, updated_at, disabled_at
) VALUES
  (?, ?, 'app_tianyancha', 'inst_bizdata_tianyancha',
   'tianyancha', 'tianyancha', 1,
   'sha256:abf4eae8bcdfebfdd96aac193e2f7a49a333848ddb9b26d916b3bca920a87704',
   'active', '', 'app_default', ?, ?, ?, NULL),
  (?, ?, 'app_qichacha', 'inst_bizdata_qichacha',
   'qichacha', 'qichacha', 1,
   'sha256:f8d79a259315548f161d2ce0b0f4117aa7bc47742c43bf5d56f45231bda550f9',
   'disabled', 'user_test_disabled', 'user', ?, ?, ?, ?)
`,
		fixtures.markedID, fixtures.workspaceID, fixtures.memberID, now, now,
		fixtures.userDisabledID, fixtures.workspaceID, fixtures.memberID, now, now, now)
	require.NoError(t, err)
	return fixtures
}

// plantBusinessDataNameOwnerFixture plants a different definition holding
// the workspace extension name the marked selection used, with its own
// active selection, and returns the selection id.
func plantBusinessDataNameOwnerFixture(
	t *testing.T, ctx context.Context, db *sql.DB, now time.Time,
) string {
	t.Helper()
	plantDefinitionForFK(t, ctx, db, "app_bizdata_name_decoy", "bizdata-name-decoy", now)
	_, err := db.ExecContext(ctx, `
INSERT INTO service_installations (id, workspace_id, service_definition_id, service_kind,
  status, installed_version, created_by_member_id, created_at, updated_at)
VALUES ('inst_bizdata_name_decoy', 'wsp_bizdata_sel', 'app_bizdata_name_decoy', 'app',
  'active', '1.0.0', 'mbr_bizdata_sel', ?, ?)
`, now, now)
	require.NoError(t, err)
	_, err = db.ExecContext(ctx, `
INSERT INTO service_installation_extensions (
  id, workspace_id, service_definition_id, service_installation_id,
  extension_name, definition_domain, definition_version, definition_digest,
  status, disabled_reason, selection_source, created_by_member_id,
  created_at, updated_at, disabled_at
) VALUES ('sel_bizdata_name_decoy', 'wsp_bizdata_sel', 'app_bizdata_name_decoy', 'inst_bizdata_name_decoy',
  'tianyancha', 'tianyancha', 1,
  'sha256:abf4eae8bcdfebfdd96aac193e2f7a49a333848ddb9b26d916b3bca920a87704',
  'active', '', 'app_default', 'mbr_bizdata_sel', ?, ?, NULL)
`, now, now)
	require.NoError(t, err)
	return "sel_bizdata_name_decoy"
}