package controlplane

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

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// expectStampNotPresent 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 expectStampNotPresent(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ?")).
		WithArgs(migrationIdNormalizeProcessActionLiteralFieldValue).
		WillReturnRows(sqlmock.NewRows([]string{"_"}))
}

// expectStampPresent 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 expectStampPresent(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta("SELECT 1 FROM _System_Migration WHERE Id = ?")).
		WithArgs(migrationIdNormalizeProcessActionLiteralFieldValue).
		WillReturnRows(sqlmock.NewRows([]string{"_"}).AddRow(1))
}

// expectStampInsert sets up the post-UPDATE INSERT into _System_Migration.
func expectStampInsert(mock sqlmock.Sqlmock) *sqlmock.ExpectedExec {
	return mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WithArgs(
			migrationIdNormalizeProcessActionLiteralFieldValue,
			1,
			sqlmock.AnyArg(),
		)
}

// TestNormalizeProcessActionLiteralFieldValue_StripsPrefixOnExistingRows
// pins the IANTHEREAL P1 Round-3 fix (PR #271): existing ProcessAction rows
// authored under the pre-PR runtime contract ("Literal: Approved" stored
// verbatim, runtime trimmed at execution time) MUST be normalized at apply
// time so the new opaque-FieldValue runtime writes "Approved" — not
// "Literal: Approved" — into the linked record on the next approval
// transition.
//
// Regression: without this migration, warm-upgraded orgs would corrupt
// L3 record data the moment a Salesforce-authored FieldUpdate fires.
func TestNormalizeProcessActionLiteralFieldValue_StripsPrefixOnExistingRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE ProcessAction")).
		WillReturnResult(sqlmock.NewResult(0, 2))
	expectStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestNormalizeProcessActionLiteralFieldValue_Idempotent verifies that a
// first-run invocation against an already-clean (but unstamped) database
// touches zero rows — the WHERE-guard on the literal prefix means clean
// rows do not match. Stamp is still recorded so subsequent runs short-
// circuit at the SELECT.
func TestNormalizeProcessActionLiteralFieldValue_Idempotent(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE ProcessAction")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestNormalizeProcessActionLiteralFieldValue_SQLShape locks the WHERE-guard
// and SUBSTRING shape so a future edit cannot quietly rewrite the predicate
// (e.g. dropping the prefix-anchor LEFT() check, which would corrupt rows
// whose FieldValue legitimately contains "Literal: " as a non-prefix
// substring).
func TestNormalizeProcessActionLiteralFieldValue_SQLShape(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectStampNotPresent(mock)

	// Anchored WHERE clause — must filter on IsDeleted=0, FieldValue NOT NULL,
	// length >= prefix length, and prefix-anchored LEFT() / LTRIM() match.
	expected := regexp.MustCompile(
		`(?s)UPDATE ProcessAction.*` +
			`SET FieldValue = SUBSTRING\(LTRIM\(FieldValue\),.*` +
			`WHERE IsDeleted = 0.*` +
			`AND FieldValue IS NOT NULL.*` +
			`AND CHAR_LENGTH\(LTRIM\(FieldValue\)\) >=.*` +
			`AND LEFT\(LTRIM\(FieldValue\),.*BINARY 'Literal: '`)
	mock.ExpectExec(expected.String()).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestNormalizeProcessActionLiteralFieldValue_StampGuardSkipsWhenApplied
// pins the Round-9 Codex BLOCK fix (PR #271): once `_System_Migration`
// records the migration, subsequent calls MUST short-circuit at the SELECT
// and never issue the UPDATE. Otherwise post-upgrade rows whose FieldValue
// legitimately starts with "Literal: " (a valid plain value under the new
// opaque-FieldValue contract) would be silently corrupted on every
// `setup repair` run.
//
// 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.
func TestNormalizeProcessActionLiteralFieldValue_StampGuardSkipsWhenApplied(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectStampPresent(mock)
	// Intentionally NO ExpectExec for the UPDATE: any UPDATE issued at this
	// point would silently corrupt post-upgrade authored values, so the
	// function must early-return without touching ProcessAction.

	touched, err := NormalizeProcessActionLiteralFieldValue(context.Background(), db)
	if err != nil {
		t.Fatalf("NormalizeProcessActionLiteralFieldValue = error %v, want nil", err)
	}
	if touched != 0 {
		t.Fatalf("NormalizeProcessActionLiteralFieldValue 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)
	}
}

// TestNormalizeProcessActionLiteralFieldValue_StampInsertFailureReturnsError
// pins fail-closed semantics for the stamp epilogue: if the post-UPDATE
// INSERT into _System_Migration fails, the function MUST return the error
// so RunRepair (wired via trackStepFatal in operations.go) aborts ApplyAll
// before the new runtime can run on a half-migrated DB without a stamp.
//
// Without this guard, a transient stamp-INSERT failure would leave the DB
// stripped (UPDATE succeeded) but unstamped, and the next repair would
// re-strip post-upgrade authored "Literal: …" values — the same data-
// corruption mode Codex Round 8 flagged.
func TestNormalizeProcessActionLiteralFieldValue_StampInsertFailureReturnsError(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE ProcessAction")).
		WillReturnResult(sqlmock.NewResult(0, 1))
	expectStampInsert(mock).WillReturnError(sql.ErrConnDone)

	_, err = NormalizeProcessActionLiteralFieldValue(context.Background(), db)
	if err == nil {
		t.Fatal("expected error from stamp-insert failure (so RunRepair aborts), got nil")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

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

// expectBackfillClosure sets up the post-UPDATE closure SELECT — counts
// remaining stale Template values + a sample of failing values.
func expectBackfillClosure(mock sqlmock.Sqlmock, remaining int, sample string) {
	rows := sqlmock.NewRows([]string{"count", "sample"})
	if sample == "" {
		rows.AddRow(remaining, nil)
	} else {
		rows.AddRow(remaining, sample)
	}
	mock.ExpectQuery(regexp.QuoteMeta("FROM _System_WorkflowAlert")).
		WillReturnRows(rows)
}

type backfillProcessActionAlertRef struct {
	apiName               string
	processName           string
	actionName            string
	parentActive          int
	runnableInstanceCount int
}

type backfillFlowRef struct {
	developerName                    string
	metadata                         string
	versionID                        string
	versionNumber                    int
	activeVersion                    int
	pausedInterviewCount             int
	recoverableRunningInterviewCount int
}

func expectBackfillActiveDependencyScan(
	mock sqlmock.Sqlmock,
	unresolved []workflowAlertTemplateDependencyRow,
	processActions []backfillProcessActionAlertRef,
	flows []backfillFlowRef,
) {
	alertRows := sqlmock.NewRows([]string{"Id", "ObjectApiName", "Name", "Template"})
	for _, row := range unresolved {
		alertRows.AddRow(row.id, row.object, row.name, row.template)
	}
	mock.ExpectQuery(regexp.QuoteMeta("FROM _System_WorkflowAlert")).
		WillReturnRows(alertRows)

	processRows := sqlmock.NewRows([]string{"ApiName", "ProcessName", "ActionName", "ParentActive", "RunnableInstanceCount"})
	for _, row := range processActions {
		parentActive := row.parentActive
		if parentActive == 0 && row.runnableInstanceCount == 0 {
			parentActive = 1
		}
		processRows.AddRow(row.apiName, row.processName, row.actionName, parentActive, row.runnableInstanceCount)
	}
	mock.ExpectQuery(regexp.QuoteMeta("FROM ProcessAction")).
		WithArgs(constants.ApprovalStatusApproved, constants.ApprovalStatusRejected, constants.ApprovalStatusRemoved, constants.ProcessActionTypeAlert).
		WillReturnRows(processRows)

	flowRows := sqlmock.NewRows([]string{"DeveloperName", "Metadata", "VersionId", "VersionNumber", "ActiveVersion", "PausedInterviewCount", "RecoverableRunningInterviewCount"})
	for _, row := range flows {
		versionID := row.versionID
		if versionID == "" {
			versionID = "flow-v1"
		}
		versionNumber := row.versionNumber
		if versionNumber == 0 {
			versionNumber = 1
		}
		activeVersion := row.activeVersion
		if activeVersion == 0 && row.pausedInterviewCount == 0 && row.recoverableRunningInterviewCount == 0 {
			activeVersion = 1
		}
		flowRows.AddRow(row.developerName, row.metadata, versionID, versionNumber, activeVersion, row.pausedInterviewCount, row.recoverableRunningInterviewCount)
	}
	mock.ExpectQuery(regexp.QuoteMeta("JOIN FlowDefinition")).
		WithArgs(models.FlowInterviewStatusPaused, models.FlowInterviewStatusRunning).
		WillReturnRows(flowRows)
}

// expectBackfillStampInsert sets up the post-closure INSERT into
// _System_Migration that records successful completion.
func expectBackfillStampInsert(mock sqlmock.Sqlmock) *sqlmock.ExpectedExec {
	return mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_Migration")).
		WithArgs(
			migrationIdBackfillWorkflowAlertTemplateIds,
			1,
			sqlmock.AnyArg(),
		)
}

// TestBackfillWorkflowAlertTemplateIds_ResolvesFolderPathAndDeveloperName
// pins the IANTHEREAL P1 Round-3 fix (PR #271): WorkflowAlert.Template
// rows authored under the pre-PR contract that allowed Salesforce-shaped
// folder paths ("unfiled$public/Quote_Approved") and bare DeveloperNames
// MUST be resolved to _System_EmailTemplate.Id so the new Id-only
// LoadAndRenderTemplate path can render them. Without this backfill, the
// runtime silently stops sending email for warm-upgraded orgs.
func TestBackfillWorkflowAlertTemplateIds_ResolvesFolderPathAndDeveloperName(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 3))
	expectBackfillClosure(mock, 0, "")
	expectBackfillStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestBackfillWorkflowAlertTemplateIds_Idempotent verifies that re-running
// against a database whose Template rows already point at valid
// EmailTemplate.Id values produces zero updates — the NOT EXISTS guard on
// existing EmailTemplate.Id means already-resolved rows are skipped, the
// closure check finds nothing remaining, and the stamp is recorded so
// subsequent runs short-circuit at the SELECT.
func TestBackfillWorkflowAlertTemplateIds_Idempotent(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 0, "")
	expectBackfillStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestBackfillWorkflowAlertTemplateIds_SQLShape locks the JOIN/COUNT
// safety so a future edit cannot quietly drop the COUNT(et.Id) = 1
// guard. Without that guard, ambiguous DeveloperName matches (multiple
// active EmailTemplate rows with the same DeveloperName) would
// non-deterministically pick one Id, silently corrupting alert
// configuration.
func TestBackfillWorkflowAlertTemplateIds_SQLShape(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)

	// Token order matches the actual SQL: UPDATE → SELECT clause (with COUNT
	// AS match_count) → JOIN ON (DeveloperName = SUBSTRING_INDEX) → outer
	// WHERE (match_count = 1) → NOT EXISTS guard against existing
	// EmailTemplate.Id.
	mustContain := []string{
		`UPDATE _System_WorkflowAlert`,
		`SUBSTRING_INDEX\(.*Template,.*'/',.*-1\)`,
		`COUNT\(DISTINCT\s+et\.Id\)\s+AS\s+match_count`,
		`DeveloperName\s*=\s*SUBSTRING_INDEX`,
		`match_count\s*=\s*1`,
		`NOT EXISTS.*_System_EmailTemplate`,
	}
	expected := regexp.MustCompile(`(?s)` + mustContain[0])
	for _, tok := range mustContain[1:] {
		expected = regexp.MustCompile(expected.String() + `.*` + tok)
	}
	mock.ExpectExec(expected.String()).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 0, "")
	expectBackfillStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestBackfillWorkflowAlertTemplateIds_CountDistinctGuardsCrossProduct
// pins the Codex HIGH fix (PR #271 Round 5/6): the inner SELECT JOINs
// _System_WorkflowAlert × _System_EmailTemplate, so a non-DISTINCT
// COUNT(et.Id) measures post-JOIN cross-product cardinality rather than
// the true ambiguity surface (how many _System_EmailTemplate rows share
// a DeveloperName).
//
// Concrete failure mode without DISTINCT: two _System_WorkflowAlert rows
// reference the same Salesforce-shaped folder path
// "unfiled$public/Foo", and exactly one _System_EmailTemplate row has
// DeveloperName='Foo'. The JOIN produces 2×1=2 rows; GROUP BY collapses
// them with match_count=2; the outer WHERE match_count = 1 filters the
// group out; both alerts stay unresolved; runtime LoadAndRenderTemplate
// silently fails on Id-only resolution. Regression: warm-upgraded orgs
// with multiple alerts pointing at the same template name silently stop
// sending email — the exact failure class the Round-3 backfill was
// supposed to repair.
//
// COUNT(DISTINCT et.Id) measures the true ambiguity surface (et.Id is
// the EmailTemplate primary key, so DISTINCT collapses cross-product
// duplicates back to the count of distinct candidate templates) and is
// invariant in the number of alerts referencing the DeveloperName.
//
// We pin the SQL token here rather than seed a real database because
// the bootstrap suite uses go-sqlmock throughout — token-shape pinning
// matches the established convention (see _SQLShape tests above).
func TestBackfillWorkflowAlertTemplateIds_CountDistinctGuardsCrossProduct(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	// Must be DISTINCT — without DISTINCT, two alerts referencing the
	// same DeveloperName double-count and falsely fail the
	// match_count = 1 ambiguity guard.
	expected := regexp.MustCompile(`(?s)COUNT\(DISTINCT\s+et\.Id\)\s+AS\s+match_count`)
	mock.ExpectExec(expected.String()).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 0, "")
	expectBackfillStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

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

// TestBackfillWorkflowAlertTemplateIds_StampGuardSkipsWhenApplied pins the
// Round-10 closure-proof fix (PR #271): once `_System_Migration` records
// the migration as applied, subsequent calls MUST short-circuit at the
// SELECT and never re-run the UPDATE / closure check / stamp INSERT.
//
// Mock asserts NO further expectations after the stamp-present SELECT —
// sqlmock fails the test if the SUT issues an unexpected query.
func TestBackfillWorkflowAlertTemplateIds_StampGuardSkipsWhenApplied(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampPresent(mock)
	// Intentionally NO further expectations: the function must early-return.

	touched, err := BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillWorkflowAlertTemplateIds = error %v, want nil", err)
	}
	if touched != 0 {
		t.Fatalf("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)
	}
}

// TestBackfillWorkflowAlertTemplateIds_ClosureFailsForActiveDependency pins
// the post-cutover closure rule: unresolved WorkflowAlert.Template values are
// fatal only when an active legacy dependency can still read the row. That
// prevents setup apply from running with live legacy email behavior while
// allowing dead historical rows to remain as audit inventory.
func TestBackfillWorkflowAlertTemplateIds_ClosureFailsForActiveDependency(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 1))
	// Two unresolved rows remain after the UPDATE — e.g. one ambiguous
	// DeveloperName ("unfiled$public/Quote_Approved" with 2 active
	// EmailTemplate matches) and one missing reference. The function
	// must return a fatal error only because an active ProcessAction.Alert
	// still references one of those rows.
	expectBackfillClosure(mock, 2, "unfiled$public/Quote_Approved, unfiled$public/Missing_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{
			{id: "wa-quote", object: "Quote", name: "Quote_Approved", template: "unfiled$public/Quote_Approved"},
			{id: "wa-missing", object: "Quote", name: "Missing_Template", template: "unfiled$public/Missing_Template"},
		},
		[]backfillProcessActionAlertRef{{apiName: "Quote_Approved", processName: "Quote_Approval", actionName: "Notify_Approved"}},
		nil,
	)
	// Intentionally NO ExpectExec for INSERT INTO _System_Migration —
	// the stamp must NOT be recorded when closure fails. sqlmock would
	// fail the test if any INSERT leaks through.

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillWorkflowAlertTemplateIds returned nil err for active unresolved legacy dependency")
	}
	msg := err.Error()
	if !strings.Contains(msg, "active legacy WorkflowAlert dependency") {
		t.Errorf("err = %q; want it to mention active legacy dependency so operators recognize the cutover blocker", msg)
	}
	if !strings.Contains(msg, "Quote_Approved") {
		t.Errorf("err = %q; want it to include the failing-row sample for operator forensics", msg)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestBackfillWorkflowAlertTemplateIds_ClosureFailsForPendingApprovalOnInactiveDefinition(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 1, "unfiled$public/Pending_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{{id: "wa-pending", object: "Quote", name: "Pending_Alert", template: "unfiled$public/Pending_Template"}},
		[]backfillProcessActionAlertRef{{
			apiName:               "Pending_Alert",
			processName:           "Inactive_Quote_Approval",
			actionName:            "Notify_Pending",
			parentActive:          0,
			runnableInstanceCount: 1,
		}},
		nil,
	)

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillWorkflowAlertTemplateIds returned nil err for pending approval on inactive definition")
	}
	for _, want := range []string{"active legacy WorkflowAlert dependency", "pendingInstances=1", "Inactive_Quote_Approval"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want mention %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestBackfillWorkflowAlertTemplateIds_AllowsHistoricalUnresolvedRowsAfterCutover(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 1, "unfiled$public/Historical_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{{id: "wa-historical", object: "Quote", name: "Historical_Alert", template: "unfiled$public/Historical_Template"}},
		nil,
		nil,
	)
	expectBackfillStampInsert(mock).WillReturnResult(sqlmock.NewResult(1, 1))

	touched, err := BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillWorkflowAlertTemplateIds historical-only = %v, want nil", err)
	}
	if touched != 0 {
		t.Fatalf("BackfillWorkflowAlertTemplateIds touched = %d, want 0", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestBackfillWorkflowAlertTemplateIds_ClosureFailsForActiveFlowEmailAlertDependency(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 1, "unfiled$public/Flow_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{{id: "wa-flow", object: "Quote", name: "Flow_Alert", template: "unfiled$public/Flow_Template"}},
		nil,
		[]backfillFlowRef{{
			developerName: "Quote_Notification_Flow",
			metadata:      `{"start":{"object":"Quote"},"actionCalls":[{"name":"Legacy_Email","actionName":"Quote.Flow_Alert","actionType":"emailAlert"}]}`,
		}},
	)

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillWorkflowAlertTemplateIds returned nil err for active Flow emailAlert unresolved dependency")
	}
	for _, want := range []string{"active legacy WorkflowAlert dependency", "Flow.emailAlert", "Quote_Notification_Flow"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want mention %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestBackfillWorkflowAlertTemplateIds_ClosureFailsForPausedInterviewEmailAlertDependency(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 1, "unfiled$public/Paused_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{{id: "wa-paused", object: "Quote", name: "Paused_Alert", template: "unfiled$public/Paused_Template"}},
		nil,
		[]backfillFlowRef{{
			developerName:        "Quote_Notification_Flow",
			metadata:             `{"start":{"object":"Quote"},"actionCalls":[{"name":"Legacy_Email","actionName":"Quote.Paused_Alert","actionType":"emailAlert"}]}`,
			versionID:            "flow-old",
			versionNumber:        7,
			activeVersion:        0,
			pausedInterviewCount: 2,
		}},
	)

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillWorkflowAlertTemplateIds returned nil err for paused Flow emailAlert unresolved dependency")
	}
	for _, want := range []string{"active legacy WorkflowAlert dependency", "Flow.emailAlert", "v7", "pausedInterviews=2"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want mention %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestBackfillWorkflowAlertTemplateIds_ClosureFailsForRecoverableRunningInterviewEmailAlertDependency(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectBackfillClosure(mock, 1, "unfiled$public/Recoverable_Template")
	expectBackfillActiveDependencyScan(
		mock,
		[]workflowAlertTemplateDependencyRow{{id: "wa-recoverable", object: "Quote", name: "Recoverable_Alert", template: "unfiled$public/Recoverable_Template"}},
		nil,
		[]backfillFlowRef{{
			developerName:                    "Quote_Notification_Flow",
			metadata:                         `{"start":{"object":"Quote"},"actionCalls":[{"name":"Legacy_Email","actionName":"Quote.Recoverable_Alert","actionType":"emailAlert"}]}`,
			versionID:                        "flow-old",
			versionNumber:                    7,
			activeVersion:                    0,
			recoverableRunningInterviewCount: 1,
		}},
	)

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillWorkflowAlertTemplateIds returned nil err for recoverable running Flow emailAlert unresolved dependency")
	}
	for _, want := range []string{"active legacy WorkflowAlert dependency", "Flow.emailAlert", "v7", "recoverableRunningInterviews=1"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want mention %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestBackfillWorkflowAlertTemplateIds_ClosureSampleTruncates pins the
// operator-readability cap on the closure-failure sample: when many
// rows fail, the GROUP_CONCAT'd sample is truncated to a fixed number
// of distinct values plus a "(N total)" suffix.
func TestBackfillWorkflowAlertTemplateIds_ClosureSampleTruncates(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectBackfillStampNotPresent(mock)
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_WorkflowAlert")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	// 12 distinct unresolved values — the message must truncate to the
	// configured sample size + total count.
	bigSample := strings.Join([]string{"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}, ", ")
	expectBackfillClosure(mock, 12, bigSample)
	var unresolved []workflowAlertTemplateDependencyRow
	var processActions []backfillProcessActionAlertRef
	for _, name := range []string{"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l"} {
		unresolved = append(unresolved, workflowAlertTemplateDependencyRow{id: "wa-" + name, object: "Quote", name: name, template: name})
		processActions = append(processActions, backfillProcessActionAlertRef{apiName: name, processName: "Quote_Approval", actionName: "Notify_" + name})
	}
	expectBackfillActiveDependencyScan(mock, unresolved, processActions, nil)

	_, err = BackfillWorkflowAlertTemplateIds(context.Background(), db)
	if err == nil {
		t.Fatal("expected closure-failure error")
	}
	if !strings.Contains(err.Error(), "12 total") {
		t.Errorf("err = %q; want it to include 'N total' on truncated samples", err.Error())
	}
}