package controlplane

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

	"github.com/DATA-DOG/go-sqlmock"
)

// expectPluralStampNotPresent sets up a mock for the stamp-guard SELECT
// returning no rows — i.e. the migration has not been applied yet, so the
// function should proceed to UPDATE.
func expectPluralStampNotPresent(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ?")).
		WithArgs(migrationIdMigrateLegacyNaivePlurals).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
}

// expectPluralStampPresent sets up a mock for the stamp-guard SELECT
// returning one row — i.e. the migration is already applied, so the
// function should skip the UPDATE entirely.
func expectPluralStampPresent(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ?")).
		WithArgs(migrationIdMigrateLegacyNaivePlurals).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
}

// expectPluralStampInsert sets up a mock for the post-UPDATE INSERT into
// _System_Migration. Returned ExpectedExec lets the caller chain
// .WillReturnResult(...) or .WillReturnError(...).
func expectPluralStampInsert(mock sqlmock.Sqlmock) *sqlmock.ExpectedExec {
	return mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WithArgs(
			migrationIdMigrateLegacyNaivePlurals,
			1,
			sqlmock.AnyArg(),
		)
}

// TestMigrateLegacyNaivePlurals_StampGuardSkipsWhenApplied pins the
// stamp-guard short-circuit: once `_System_Migration` records the
// migration, subsequent calls MUST early-return without touching
// `_System_Object`. The mock asserts NO UPDATE expectation — sqlmock
// fails the test if the SUT issues an unexpected query, which is the
// property we want to pin (re-running the UPDATE on already-corrected
// rows is safe due to the WHERE guard, but bumping LastModifiedDate on
// every boot would still be a no-op write that the stamp guard
// rightfully prevents).
func TestMigrateLegacyNaivePlurals_StampGuardSkipsWhenApplied(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectPluralStampPresent(mock)
	// Intentionally NO ExpectExec for UPDATE: the stamp-guard short-circuit
	// must fire before any _System_Object touch.

	touched, err := MigrateLegacyNaivePlurals(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateLegacyNaivePlurals = error %v, want nil", err)
	}
	if touched != 0 {
		t.Fatalf("MigrateLegacyNaivePlurals post-stamp touched = %d, want 0 (must skip on already-applied migration)", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestMigrateLegacyNaivePlurals_SQLShape pins the WHERE-clause anchors
// that protect:
//
//   - admin-customized plurals shaped non-naively (the
//     `PluralLabel = CONCAT(Label, 's')` guard); without it, the
//     migration would overwrite an admin intentionally setting
//     Opportunity's plural to "Pipelines"
//   - admin-customized rows shaped naively as `Label+'s'` (the
//     `COALESCE(IsCustomized, 0) = 0` guard); without it, an admin
//     deliberately keeping the naive form via IsCustomized=1 would
//     be silently rewritten — added in response to Codex review on
//     PR #494 commit `9f91a3b` ("PluralLabel is admin-mutable; pack
//     apply preserves IsCustomized=1; one-shot repair must too")
//   - no-op writes for vowel-y / non-sibilant 's' Labels (the
//     `PluralLabel <> CASE ... END` guard); without it, rows like
//     "Boy"/"Boys" would round-trip the same value through UPDATE,
//     bumping LastModifiedDate without a real change
//
// A future refactor that drops any of these guards would silently
// break one of these properties. This test keeps the WHERE shape
// stable.
func TestMigrateLegacyNaivePlurals_SQLShape(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectPluralStampNotPresent(mock)

	// Anchored UPDATE shape — must touch _System_Object, must filter on
	// IsDeleted=0, must include the IsCustomized=0 guard (closes the
	// pack-managed-column gap Codex flagged), the naive-+s WHERE guard,
	// and the "derived plural differs from stored" suppression for
	// no-op writes.
	expected := regexp.MustCompile(
		`(?s)UPDATE _System_Object.*` +
			`SET PluralLabel = CASE.*` +
			`WHERE IsDeleted = 0.*` +
			`AND Label IS NOT NULL.*` +
			`AND COALESCE\(IsCustomized, 0\) = 0.*` +
			`AND PluralLabel = CONCAT\(Label, 's'\).*` +
			`AND PluralLabel <> CASE.*END`)
	mock.ExpectExec(expected.String()).
		WillReturnResult(sqlmock.NewResult(0, 5))
	expectPluralStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

	touched, err := MigrateLegacyNaivePlurals(context.Background(), db)
	if err != nil {
		t.Fatalf("MigrateLegacyNaivePlurals = error %v, want nil", err)
	}
	if touched != 5 {
		t.Fatalf("MigrateLegacyNaivePlurals touched = %d, want 5", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestMigrateLegacyNaivePlurals_StampInsertFailureReturnsError pins
// observable error semantics for the stamp epilogue: if the post-UPDATE
// INSERT into _System_Migration fails, the function MUST return the
// error so the operator sees the failure in RunRepair logs.
//
// Because the migration is wired as `trackStep` (not Fatal), the
// returned error does not abort ApplyAll. Re-running on the next boot
// is safe: the WHERE guard `PluralLabel = CONCAT(Label, 's')` no
// longer matches the rows that were corrected in the first run, so the
// next attempt processes only what remains and tries to write the
// stamp again. The contract this test pins is: error-return, not
// silent-success — the caller can observe the failure even though it
// chooses not to abort.
func TestMigrateLegacyNaivePlurals_StampInsertFailureReturnsError(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectPluralStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_Object")).
		WillReturnResult(sqlmock.NewResult(0, 3))
	expectPluralStampInsert(mock).WillReturnError(sql.ErrConnDone)

	touched, err := MigrateLegacyNaivePlurals(context.Background(), db)
	if err == nil {
		t.Fatal("expected error from stamp-insert failure (so operator sees the failure), got nil")
	}
	if touched != 3 {
		t.Fatalf("MigrateLegacyNaivePlurals touched = %d, want 3 (UPDATE succeeded before stamp insert failed)", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}