package bootstrap

import (
	"database/sql/driver"
	"encoding/json"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestStandardActionID_DeterministicLowercase(t *testing.T) {
	assert.Equal(t, "sa_convertlead", StandardActionID("convertLead"))
	assert.Equal(t, "sa_changeowner", StandardActionID("ChangeOwner"))
}

// expectPackHashFastPathMiss pushes sqlmock expectations for the Phase-1B
// pack-hash fast-path gate on _System_BootstrapState. Use when a test wants
// the full seed + reconcile path to run (empty stored hash → cache miss).
//
// Pair with expectPackHashStamp at the END of the expectation sequence.
func expectPackHashFastPathMiss(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}))
}

// expectPackHashStamp pushes the post-seed stamp INSERT. Use as the LAST
// expectation in tests that run the full seed path successfully.
func expectPackHashStamp(mock sqlmock.Sqlmock) {
	mock.ExpectExec(`INSERT INTO .*BootstrapState.*ON DUPLICATE KEY UPDATE`).
		WillReturnResult(sqlmock.NewResult(1, 1))
}

func expectPersistedStandardActionID(mock sqlmock.Sqlmock, name, id string) {
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name = \?`).
		WithArgs(name).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(id))
}

func expectPersistedStandardActionName(mock sqlmock.Sqlmock, id, name string) {
	mock.ExpectQuery(`SELECT Name FROM .*_System_StandardAction.* WHERE Id = \? AND IsDeleted = 0`).
		WithArgs(id).
		WillReturnRows(sqlmock.NewRows([]string{"Name"}).AddRow(name))
}

func expectPersistedStandardActionScopeOwner(mock sqlmock.Sqlmock, id, name, flowName string, isCustomized bool) {
	mock.ExpectQuery(`SELECT Name, FlowDeveloperName, IsCustomized FROM .*_System_StandardAction.* WHERE Id = \? AND IsDeleted = 0`).
		WithArgs(id).
		WillReturnRows(sqlmock.NewRows([]string{"Name", "FlowDeveloperName", "IsCustomized"}).AddRow(name, flowName, isCustomized))
}

func expectPersistedActionInputGuardIDs(mock sqlmock.Sqlmock, actionID string, rows ...[3]string) {
	result := sqlmock.NewRows([]string{"Id", "Condition", "GuardKey"})
	for _, row := range rows {
		result.AddRow(row[0], row[1], row[2])
	}
	mock.ExpectQuery(`SELECT Id, .*Condition.*, GuardKey FROM .*ActionInputGuard.* WHERE StandardActionId = .* AND \(IsDeleted = 0 OR IsCustomized = 1\)`).
		WithArgs(actionID).
		WillReturnRows(result)
}

func expectActiveStandardFlowPackOwned(mock sqlmock.Sqlmock, flowName string, source string, isCustomized bool) {
	mock.ExpectQuery(`SELECT fd\.IsCustomized, f\.Source\s+FROM FlowDefinition fd\s+LEFT JOIN Flow f ON f\.Id = fd\.ActiveVersionId AND f\.IsDeleted = 0\s+WHERE fd\.DeveloperName = \? AND fd\.IsDeleted = 0`).
		WithArgs(flowName).
		WillReturnRows(sqlmock.NewRows([]string{"IsCustomized", "Source"}).AddRow(isCustomized, source))
}

// expectParentReconcileNoOrphans pushes the two SELECTs that the
// _System_StandardAction parent reconcile emits in the happy "no orphan"
// case: the persisted-Id lookup (keyed by Name) and the live-row scan.
// Both return the same canonical set, so no UPDATE fires. Tests that
// want to exercise orphan-deactivation inline their own expectations
// (see TestInitializeStandardActions_ParentOrphanDeactivated).
//
// packNames is the pack's action Names in order; persistedIds is the
// set of Ids actually stored (one per Name — the test scope). Both
// slices must have the same length; the persisted-Id SELECT binds one
// `?` per Name and returns one row per Name.
func expectParentReconcileNoOrphans(mock sqlmock.Sqlmock, packNames []string, persistedIds []string) {
	byNameArgs := make([]driver.Value, len(packNames))
	for i, n := range packNames {
		byNameArgs[i] = n
	}
	byNameRows := sqlmock.NewRows([]string{"Id"})
	for _, id := range persistedIds {
		byNameRows.AddRow(id)
	}
	liveRows := sqlmock.NewRows([]string{"Id"})
	for _, id := range persistedIds {
		liveRows.AddRow(id)
	}

	placeholder := strings.TrimRight(strings.Repeat(`\?,`, len(packNames)), ",")
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name IN \(` + placeholder + `\) AND IsDeleted = 0`).
		WithArgs(byNameArgs...).
		WillReturnRows(byNameRows)
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE IsCustomized = 0 AND IsDeleted = 0`).
		WillReturnRows(liveRows)
}

func TestInitializeStandardActions_EmptyPackIsNoOp(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	// Swap shipped pack with an empty array just for this test.
	original := standardActionsJSON
	standardActionsJSON = []byte(`[]`)
	defer func() { standardActionsJSON = original }()

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet(), "no SQL should have run on an empty pack")
}

func TestInitializeStandardActions_ShippedPackIsValidJSON(t *testing.T) {
	// Guards against a bad-commit regression: the file shipped with the
	// binary must always unmarshal into the expected shape, even when
	// it's the empty-array default.
	var defs []StandardActionDef
	require.NoError(t, json.Unmarshal(standardActionsJSON, &defs))
}

func TestInitializeStandardActions_SeedsParentAndSatelliteRows(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[
        {
            "name": "pingAction",
            "label": "Ping",
            "flowDeveloperName": "Std_Ping",
            "requiredPermissions": ["ViewSetup"],
            "isAtomic": true,
            "inputs":  [{"apiName":"recordId","dataType":"id","isRequired":true}],
            "outputs": [{"apiName":"ok","dataType":"boolean"}],
            "fieldDefaults": [{"targetObject":"Opportunity","fieldAPIName":"StageName","valueFormula":"'Prospecting'"}],
            "inputGuards":   [{"condition":"$Input.recordId == null","requiredPermission":"ViewSetup","errorMessage":"missing recordId"}],
            "internalAccessScopes": [{"objectApiName":"Task","operation":"Edit","reason":"test scope"}]
        }
    ]`)
	defer func() { standardActionsJSON = original }()

	expectPackHashFastPathMiss(mock)

	guard := StandardActionGuardDef{Condition: "$Input.recordId == null", RequiredPermission: "ViewSetup"}
	guardKey := actionInputGuardKey(guard)
	guardID := actionInputGuardID("sa_pingaction", guard.Condition, guardKey)

	// Parent + two schema upserts + one field-default upsert + one guard upsert + one internal-access scope upsert.
	mock.ExpectExec(`INSERT INTO _System_StandardAction`).WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "pingAction", "sa_pingaction")
	mock.ExpectExec(`INSERT INTO _System_ActionSchema`).WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec(`INSERT INTO _System_ActionSchema`).WillReturnResult(sqlmock.NewResult(1, 1))
	// Reconcile: SELECT live schema rows for this action (may contain 0).
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId`).
		WithArgs("sa_pingaction").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec(`INSERT INTO _System_ActionFieldDefault`).WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
		WithArgs("sa_pingaction").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec(`INSERT INTO _System_ActionInputGuard`).WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedActionInputGuardIDs(mock, "sa_pingaction", [3]string{guardID, guard.Condition, guardKey})
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
		WithArgs("sa_pingaction").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(guardID))
	mock.ExpectExec(`INSERT INTO _System_StandardActionInternalAccessScope`).WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
		WithArgs("sa_pingaction").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	expectParentReconcileNoOrphans(mock, []string{"pingAction"}, []string{"sa_pingaction"})
	expectPackHashStamp(mock)

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestUpsertStandardAction_SatellitesUsePersistedParentID(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	persistedID := "custom_convertLead"
	now := "now"
	def := StandardActionDef{
		Name:              "convertLead",
		Label:             "Convert",
		FlowDeveloperName: "Std_ConvertLead",
		Inputs: []StandardActionSchemaDef{{
			APIName:    "leadId",
			DataType:   "id",
			IsRequired: true,
		}},
		FieldDefaults: []StandardActionDefaultDef{{
			TargetObject: "Opportunity",
			FieldAPIName: "CloseDate",
			ValueFormula: "TODAY() + 30",
		}},
		InputGuards: []StandardActionGuardDef{{
			Condition:         "true",
			RequiredObject:    "Account",
			RequiredOperation: "Read",
			ErrorMessage:      "Account.Read is required",
		}},
		InternalAccessScopes: []StandardActionInternalAccessScopeDef{{
			ObjectAPIName: "Task",
			Operation:     "Edit",
			Reason:        "pack scope",
		}},
	}

	schemaID := actionSchemaID(persistedID, "input", "leadId")
	defaultID := actionFieldDefaultID(persistedID, "Opportunity", "CloseDate", 0)
	guardKey := actionInputGuardKey(def.InputGuards[0])
	guardID := actionInputGuardID(persistedID, "true", guardKey)
	scopeID := actionInternalAccessScopeID(persistedID, "Task", "Edit")

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "convertLead", persistedID)
	mock.ExpectExec(`INSERT INTO _System_ActionSchema`).
		WithArgs(schemaID, persistedID, "input", "leadId", "id",
			nil, nil, nil, nil, nil, nil, nil, nil, nil,
			1, nil, 0, now, now).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId`).
		WithArgs(persistedID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(schemaID))
	mock.ExpectExec(`INSERT INTO _System_ActionFieldDefault`).
		WithArgs(defaultID, persistedID, "Opportunity", "CloseDate", "TODAY() + 30", nil, 0, now, now).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
		WithArgs(persistedID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(defaultID))
	mock.ExpectExec(`INSERT INTO _System_ActionInputGuard`).
		WithArgs(guardID, persistedID, "true", guardKey, nil, "Account", "Read", "Account.Read is required", nil, 0, now, now).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedActionInputGuardIDs(mock, persistedID, [3]string{guardID, "true", guardKey})
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
		WithArgs(persistedID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(guardID))
	mock.ExpectExec(`INSERT INTO _System_StandardActionInternalAccessScope`).
		WithArgs(scopeID, persistedID, "Task", "Edit", "pack scope", now, now).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
		WithArgs(persistedID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(scopeID))

	require.NoError(t, upsertStandardAction(db, def, now))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestUpsertStandardAction_RekeysPackOwnedActionInputGuardBeforeReconcile(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	now := "now"
	guard := StandardActionGuardDef{
		Condition:          "$Input.doNotCreateOpportunity == true",
		RequiredPermission: "SkipOpportunityOnConvert",
		ErrorMessage:       "Skipping opportunity requires permission",
		GatedInput:         "doNotCreateOpportunity",
	}
	def := StandardActionDef{
		Name:              "convertLead",
		Label:             "Convert",
		FlowDeveloperName: "Std_ConvertLead",
		InputGuards:       []StandardActionGuardDef{guard},
	}

	guardKey := actionInputGuardKey(guard)
	guardID := actionInputGuardID("sa_convertlead", guard.Condition, guardKey)
	legacyGuardID := "aig_convertlead_0"

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "convertLead", "sa_convertlead")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId`).
		WithArgs("sa_convertlead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
		WithArgs("sa_convertlead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec(`(?s)INSERT INTO _System_ActionInputGuard.*ON DUPLICATE KEY UPDATE\s+Id\s+=\s+IF\(IsCustomized = 0, VALUES\(Id\), Id\),`).
		WithArgs(
			guardID, "sa_convertlead", guard.Condition, guardKey,
			"SkipOpportunityOnConvert", nil, nil,
			"Skipping opportunity requires permission", "doNotCreateOpportunity",
			0, now, now,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	// Warm upgrades can still have the unique-key owner under a pre-GuardKey
	// positional Id. Reconcile must keep the row that actually persisted after
	// upsert instead of assuming the newly derived hash Id won the duplicate.
	expectPersistedActionInputGuardIDs(mock, "sa_convertlead", [3]string{legacyGuardID, guard.Condition, guardKey})
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
		WithArgs("sa_convertlead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(legacyGuardID))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
		WithArgs("sa_convertlead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	require.NoError(t, upsertStandardAction(db, def, now))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestInitializeStandardActions_RejectsMissingName(t *testing.T) {
	db, _, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"label":"x","flowDeveloperName":"y"}]`)
	defer func() { standardActionsJSON = original }()

	err = InitializeStandardActions(db)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "name")
}

func TestInitializeStandardActions_RejectsMissingFlowDeveloperName(t *testing.T) {
	db, _, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"convertLead","label":"Convert Lead"}]`)
	defer func() { standardActionsJSON = original }()

	err = InitializeStandardActions(db)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "flowDeveloperName")
}

func TestShippedPack_ConvertLeadActionPinsContract(t *testing.T) {
	// Guards the exact shape that /actions/standard/convertLead exposes
	// to callers. The frontend and tests/contract assume these fields;
	// a regression here is a user-visible regression.
	var defs []StandardActionDef
	require.NoError(t, json.Unmarshal(standardActionsJSON, &defs))

	var convert *StandardActionDef
	for i := range defs {
		if defs[i].Name == "convertLead" {
			convert = &defs[i]
			break
		}
	}
	require.NotNil(t, convert, "standard_actions.json must ship convertLead")

	assert.Equal(t, "Std_ConvertLead", convert.FlowDeveloperName)
	assert.Contains(t, convert.RequiredPermissions, "ConvertLeads")
	assert.True(t, convert.IsAtomic, "convertLead must run atomically")

	inputNames := map[string]bool{}
	inputsByName := map[string]StandardActionSchemaDef{}
	requiredInputs := map[string]bool{}
	for _, in := range convert.Inputs {
		inputNames[in.APIName] = true
		inputsByName[in.APIName] = in
		if in.IsRequired {
			requiredInputs[in.APIName] = true
		}
	}
	// Metadata-driven MVP required inputs. accountId stays optional so the
	// Flow-native create-vs-reuse path remains part of the public API contract.
	// convertedStatus is also required by public metadata because the platform
	// pack must not ship an org-specific converted Lead Status value; org-owned
	// ActionFieldDefault rows can still fill the required input before schema validation.
	assert.True(t, requiredInputs["leadId"])
	assert.True(t, requiredInputs["convertedStatus"])
	assert.False(t, requiredInputs["accountId"], "accountId must stay optional so API callers can create an Account")
	// Frontend-sent optional inputs.
	for _, name := range []string{
		"contactFirstName",
		"contactLastName",
		"contactTitle",
		"contactEmail",
		"contactId",
		"opportunityId",
		"opportunityName",
		"doNotCreateOpportunity",
		"ownerId",
		"overwriteLeadSource",
	} {
		assert.True(t, inputNames[name], "schema must accept %q from the frontend", name)
	}
	assert.True(t, inputNames["accountId"], "schema must accept accountId for Account reuse")
	assert.True(t, inputNames["convertedStatus"], "convertedStatus must be accepted and validated from action metadata")
	assert.Equal(t, "picklist", inputsByName["convertedStatus"].DataType)
	assert.Equal(t, "converted", inputsByName["convertedStatus"].RequiredPicklistTag,
		"convertedStatus must be constrained by Lead.Status picklist metadata instead of raw string input")
	for name, field := range map[string]string{
		"contactFirstName": "FirstName",
		"contactLastName":  "LastName",
		"contactTitle":     "Title",
		"contactEmail":     "Email",
	} {
		row := inputsByName[name]
		assert.Equal(t, "Contact", row.UISection)
		assert.Equal(t, "create", row.UIMode)
		assert.Equal(t, "Contact", row.TargetObject)
		assert.Equal(t, field, row.TargetFieldAPIName)
		assert.Equal(t, "Lead", row.MappingSourceObject)
		assert.Equal(t, "convert", row.MappingActionType)
	}
	assert.Equal(t, "Opportunity", inputsByName["opportunityName"].UISection)
	assert.Equal(t, "create", inputsByName["opportunityName"].UIMode)
	assert.Equal(t, "Opportunity", inputsByName["opportunityName"].TargetObject)
	assert.Equal(t, "Name", inputsByName["opportunityName"].TargetFieldAPIName)
	assert.Equal(t, "Lead", inputsByName["opportunityName"].MappingSourceObject)
	assert.Equal(t, "convert", inputsByName["opportunityName"].MappingActionType)

	outputsByName := map[string]StandardActionSchemaDef{}
	for _, out := range convert.Outputs {
		outputsByName[out.APIName] = out
	}
	previewOutput, ok := outputsByName["opportunityCampaignId"]
	require.True(t, ok, "Campaign attribution preview target must be declared as action output metadata")
	assert.Equal(t, "reference", previewOutput.DataType)
	assert.Equal(t, "Campaign", previewOutput.ReferenceTo)
	assert.Equal(t, "Campaign attribution", previewOutput.Label)
	assert.Equal(t, "Opportunity", previewOutput.UISection)
	assert.Equal(t, "preview", previewOutput.UIMode)
	assert.Equal(t, "Opportunity", previewOutput.TargetObject)
	assert.Equal(t, "CampaignId", previewOutput.TargetFieldAPIName)

	// Inputs declared but silently-ignored are a lie to callers. These three
	// SF-native fields are excluded until the flow can honor them under
	// RunAtomically semantics. sendNotificationEmail is email I/O and remains
	// out of scope for this metadata-driven slice.
	for _, name := range []string{"sendNotificationEmail"} {
		assert.False(t, inputNames[name],
			"%q must not be declared until the flow honors it (regression reopens the silent-drop hazard)", name)
	}

	// Guards are action metadata. Lead Convert follows the Flow operation
	// graph: create branches require Create, Account reuse requires Read
	// because existing Accounts are not mutated, and reuse/update branches
	// that still issue updates require Edit. Do not encode object CRUD as
	// fake permissions or let it drift into only the persistence layer.
	guardsByPermission := map[string]StandardActionGuardDef{}
	guardsByObjectOp := map[string]StandardActionGuardDef{}
	guardConditionsByObjectOp := map[string][]string{}
	for _, guard := range convert.InputGuards {
		if guard.RequiredPermission != "" {
			assert.Empty(t, guard.RequiredObject)
			assert.Empty(t, guard.RequiredOperation)
			guardsByPermission[guard.RequiredPermission] = guard
			continue
		}
		assert.NotEmpty(t, guard.RequiredObject)
		assert.NotEmpty(t, guard.RequiredOperation)
		objectOp := guard.RequiredObject + "." + guard.RequiredOperation
		guardsByObjectOp[objectOp] = guard
		guardConditionsByObjectOp[objectOp] = append(guardConditionsByObjectOp[objectOp], guard.Condition)
	}
	skipGuard, ok := guardsByPermission["SkipOpportunityOnConvert"]
	require.True(t, ok, "SkipOpportunityOnConvert must remain a metadata-owned custom-permission guard")
	assert.Contains(t, skipGuard.Condition, "doNotCreateOpportunity")
	for _, objectOp := range []string{
		"Lead.Edit",
		"Account.Create",
		"Account.Read",
		"Contact.Create",
		"Contact.Edit",
		"Opportunity.Create",
		"Opportunity.Edit",
	} {
		require.Contains(t, guardsByObjectOp, objectOp,
			"object CRUD guard %s must be represented as requiredObject+requiredOperation metadata", objectOp)
	}
	assert.NotContains(t, guardsByObjectOp, "Lead.Create",
		"Lead Convert updates an existing Lead; Lead.Create must not be preflighted")
	assert.Equal(t, "true", guardsByObjectOp["Lead.Edit"].Condition)
	assert.Equal(t, "ISBLANK(Input.accountId)", guardsByObjectOp["Account.Create"].Condition)
	assert.Equal(t, "accountId", guardsByObjectOp["Account.Create"].GatedInput)
	assert.Equal(t, "NOT(ISBLANK(Input.accountId))", guardsByObjectOp["Account.Read"].Condition)
	assert.Equal(t, "accountId", guardsByObjectOp["Account.Read"].GatedInput)
	assert.Equal(t, "ISBLANK(Input.contactId)", guardsByObjectOp["Contact.Create"].Condition)
	assert.Equal(t, "contactId", guardsByObjectOp["Contact.Create"].GatedInput)
	assert.Equal(t, "NOT(ISBLANK(Input.contactId))", guardsByObjectOp["Contact.Edit"].Condition)
	assert.Equal(t, "contactId", guardsByObjectOp["Contact.Edit"].GatedInput)
	assert.Contains(t, guardsByObjectOp["Opportunity.Create"].Condition, "ISBLANK(Input.opportunityId)")
	assert.Equal(t, "opportunityName", guardsByObjectOp["Opportunity.Create"].GatedInput)
	assert.NotContains(t, guardConditionsByObjectOp["Opportunity.Edit"],
		"AND(Input.doNotCreateOpportunity != true, ISBLANK(Input.opportunityId))",
		"new Opportunity path must not require Opportunity.Edit before Flow DML")
	assert.Contains(t, guardConditionsByObjectOp["Opportunity.Edit"],
		"AND(Input.doNotCreateOpportunity != true, NOT(ISBLANK(Input.opportunityId)))",
		"reused Opportunity path must require Opportunity.Edit before Flow DML")
	assert.Equal(t, "opportunityId", guardsByObjectOp["Opportunity.Edit"].GatedInput)
	for _, fakePerm := range []string{"CreateAccount", "EditAccount", "CreateContact", "EditContact", "CreateOpportunity", "EditOpportunity"} {
		assert.NotContains(t, guardsByPermission, fakePerm,
			"object CRUD must not be modeled as fake RequiredPermission %q", fakePerm)
	}

	scopesByObjectOp := map[string]StandardActionInternalAccessScopeDef{}
	for _, scope := range convert.InternalAccessScopes {
		scopesByObjectOp[scope.ObjectAPIName+"."+scope.Operation] = scope
	}
	assert.Equal(t, map[string]StandardActionInternalAccessScopeDef{
		"FlowDefinition.Read":           scopesByObjectOp["FlowDefinition.Read"],
		"Flow.Read":                     scopesByObjectOp["Flow.Read"],
		"OpportunityContactRole.Read":   scopesByObjectOp["OpportunityContactRole.Read"],
		"OpportunityContactRole.Create": scopesByObjectOp["OpportunityContactRole.Create"],
		"OpportunityContactRole.Edit":   scopesByObjectOp["OpportunityContactRole.Edit"],
		"CampaignMember.Read":           scopesByObjectOp["CampaignMember.Read"],
		"CampaignMember.Edit":           scopesByObjectOp["CampaignMember.Edit"],
		"Task.Read":                     scopesByObjectOp["Task.Read"],
		"Task.Edit":                     scopesByObjectOp["Task.Edit"],
	}, scopesByObjectOp, "standard action owned metadata and bookkeeping reads/writes must be declared as internal access scopes")
	for _, objectOp := range []string{
		"FlowDefinition.Read",
		"Flow.Read",
		"OpportunityContactRole.Read",
		"OpportunityContactRole.Create",
		"OpportunityContactRole.Edit",
		"CampaignMember.Read",
		"CampaignMember.Edit",
		"Task.Read",
		"Task.Edit",
	} {
		require.Contains(t, scopesByObjectOp, objectOp)
	}

	// SkipOpportunityOnConvert is a policy permission — ships as a
	// self-describing custom-permission row inline on the action, NOT
	// as a Go enum constant. This is the load-bearing metadata-driven
	// property: adding a new action-level policy is pure JSON, no Go
	// change. A regression that reintroduces a Go const for a policy
	// permission should fail this assertion.
	var skipOpportunityOnConvert *StandardActionCustomPermissionDef
	for i := range convert.CustomPermissions {
		if convert.CustomPermissions[i].APIName == "SkipOpportunityOnConvert" {
			skipOpportunityOnConvert = &convert.CustomPermissions[i]
		}
	}
	require.NotNil(t, skipOpportunityOnConvert, "SkipOpportunityOnConvert must ship as a custom-permission row declared on the action")

	// FieldDefaults ships only neutral or explicitly action-owned defaults:
	// Opportunity.CloseDate = TODAY()+30.
	//
	// What's allowed in L1 fieldDefaults: neutral calendar/arithmetic
	// formulas that do NOT encode a tenant-specific business decision
	// (e.g. TODAY()+30 makes no assumption about the tenant's sales cycle).
	//
	// What's NOT allowed (CLAUDE.md Red Line #4): picklist-value literals
	// that assume a tenant's picklist contains that value — StageName="Prospecting",
	// Role="Decision Maker", etc. Those are tenant business-enum decisions
	// and must be configured per-org via admin setup, not shipped in the
	// platform binary. If a picklist default is needed for the happy path,
	// the right home is schema-level _System_ObjectField.DefaultValue on the
	// field itself (e.g. StageName has `default:"Prospecting"` in
	// standard_objects_spec.json).
	//
	// Converted Lead Status is intentionally NOT defaulted by this public
	// pack; each org must either pass convertedStatus or provide org-owned
	// _System_ActionFieldDefault metadata. Shipping a status picklist literal
	// here would make the platform pack assume one org's Lead Status values.
	//
	// CloseDate is required at the schema level and has NO schema default,
	// so it needs a default somewhere for the fresh-org happy path to work.
	// TODAY()+30 is the Salesforce-compatible neutral choice; admins override
	// by inserting a higher-priority _System_ActionFieldDefault row (their
	// IsCustomized=1 row is preserved by the reconcile pass).
	require.Len(t, convert.FieldDefaults, 1,
		"shipped pack must seed only Opportunity.CloseDate default; adding more needs a CLAUDE.md Red Line review")
	defaultsByKey := map[string]StandardActionDefaultDef{}
	for _, fd := range convert.FieldDefaults {
		defaultsByKey[fd.TargetObject+"."+fd.FieldAPIName] = fd
	}
	closeDate, ok := defaultsByKey["Opportunity.CloseDate"]
	require.True(t, ok, "Opportunity.CloseDate default must be action metadata")
	assert.Equal(t, "Opportunity", closeDate.TargetObject)
	assert.Equal(t, "CloseDate", closeDate.FieldAPIName)
	assert.Equal(t, "TODAY() + 30", closeDate.ValueFormula)
	assert.Empty(t, closeDate.Condition, "CloseDate default must fire unconditionally")
	// Explicit regression gate: this test would FAIL if someone reintroduces
	// StageName="Prospecting" or any other picklist-literal ValueFormula.
	for _, fd := range convert.FieldDefaults {
		assert.NotContainsf(t, fd.ValueFormula, "'Prospecting'",
			"fieldDefaults must not bake picklist-value literals (Red Line #4): %+v", fd)
	}
}

func TestInitializeStandardActions_ReconcileUsesIsCustomizedGuard(t *testing.T) {
	// The upsert statement for the parent row must preserve IsCustomized=1
	// admin edits. We regex-match the ON DUPLICATE KEY UPDATE clause to
	// ensure the guard is present on every mutable column.
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"n","label":"l","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	expectPackHashFastPathMiss(mock)

	mock.ExpectExec(`ON DUPLICATE KEY UPDATE[\s\S]+IF\(IsCustomized = 0, VALUES\(Label\), Label\)`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "n", "sa_n")

	// Reconcile SELECTs run even when the pack has no satellite rows for
	// this action — a pack that dropped all inputs/defaults/guards for an
	// action must deactivate any orphan rows left by a prior release.
	// Return empty rowsets: no orphans → no UPDATE fires.
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId`).
		WithArgs("sa_n").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
		WithArgs("sa_n").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
		WithArgs("sa_n").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
		WithArgs("sa_n").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	expectParentReconcileNoOrphans(mock, []string{"n"}, []string{"sa_n"})
	expectPackHashStamp(mock)

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestAdminIsActiveZeroSurvivesPackUpdate pins Phase-1 Finding-3. An admin
// disables a pack-seeded row by setting IsActive=0 WITHOUT also setting
// IsCustomized=1. Before the fix, the ON DUPLICATE KEY UPDATE clause at
// every pack seed site used `IsActive = IF(IsCustomized = 0, VALUES(IsActive),
// IsActive)`, so the next bootstrap silently restored IsActive=1 from the
// pack literal. That made admin toggles NOT load-bearing: pack updates
// (or any byte-change to the pack JSON that misses the hash fast-path)
// would re-enable disabled actions/permissions.
//
// The fix narrows the gate to `IsCustomized = 0 AND IsDeleted = 1` — so
// IsActive/IsDeleted are only pack-reset when the row was previously
// reconcile-soft-deleted (the legitimate pack-reintroduction case).
// Admin's IsActive=0 alone is preserved.
//
// This test enforces the shape of that SQL across customizable pack-seeded
// tables (the parent, customizable satellites, and _System_CustomPermission)
// so a future upsert
// call site can never regress to the old gate without tripping CI.
func TestAdminIsActiveZeroSurvivesPackUpdate(t *testing.T) {
	// Every seed site that writes an IsActive column MUST narrow the
	// pack-reset clause to `IsCustomized = 0 AND IsDeleted = 1`. Any
	// occurrence of the loose `IsCustomized = 0, VALUES(IsActive)` pattern
	// (without the AND IsDeleted = 1 guard) is an admin-toggle footgun.
	// Run `go test` after any future edit to the six upsert SQL blocks
	// to keep them honest.
	tables := []string{
		"_System_StandardAction",
		"_System_ActionSchema",
		"_System_ActionFieldDefault",
		"_System_ActionInputGuard",
		"_System_CustomPermission",
	}
	for _, table := range tables {
		t.Run(table, func(t *testing.T) {
			db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
			require.NoError(t, err)
			defer db.Close()

			// Regex ensures the narrowed gate is present on BOTH the
			// IsActive and IsDeleted clauses. The old shape (without
			// `AND IsDeleted = 1`) would not match.
			mock.ExpectExec(
				`INSERT INTO ` + table +
					`[\s\S]+ON DUPLICATE KEY UPDATE` +
					`[\s\S]+IsActive\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, VALUES\(IsActive\), IsActive\)` +
					`[\s\S]+IsDeleted\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted\)`,
			).WillReturnResult(sqlmock.NewResult(1, 1))

			var helperErr error
			switch table {
			case "_System_StandardAction":
				// Parent upsert is inline in upsertStandardAction which
				// then resolves the persisted parent Id and runs 4
				// satellite reconcile SELECTs. Mock them as empty after
				// the parent INSERT so the helper can finish.
				expectPersistedStandardActionID(mock, "admin_toggle_probe", "sa_admin_toggle_probe")
				mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId`).
					WithArgs("sa_admin_toggle_probe").
					WillReturnRows(sqlmock.NewRows([]string{"Id"}))
				mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
					WithArgs("sa_admin_toggle_probe").
					WillReturnRows(sqlmock.NewRows([]string{"Id"}))
				mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
					WithArgs("sa_admin_toggle_probe").
					WillReturnRows(sqlmock.NewRows([]string{"Id"}))
				mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
					WithArgs("sa_admin_toggle_probe").
					WillReturnRows(sqlmock.NewRows([]string{"Id"}))
				helperErr = upsertStandardAction(db,
					StandardActionDef{Name: "admin_toggle_probe", Label: "p", FlowDeveloperName: "f"}, "now")
			case "_System_ActionSchema":
				_, helperErr = upsertActionSchemaRows(db, "sa_probe", "input",
					[]StandardActionSchemaDef{{APIName: "x", DataType: "string"}}, "now")
			case "_System_ActionFieldDefault":
				_, helperErr = upsertActionFieldDefaults(db, "sa_probe",
					[]StandardActionDefaultDef{{TargetObject: "X", FieldAPIName: "Y", ValueFormula: "1"}}, "now")
			case "_System_ActionInputGuard":
				guard := StandardActionGuardDef{Condition: "c", RequiredPermission: "p"}
				guardKey := actionInputGuardKey(guard)
				guardID := actionInputGuardID("sa_probe", guard.Condition, guardKey)
				expectPersistedActionInputGuardIDs(mock, "sa_probe", [3]string{guardID, guard.Condition, guardKey})
				_, helperErr = upsertActionInputGuards(db, "sa_probe",
					[]StandardActionGuardDef{guard}, "now")
			case "_System_CustomPermission":
				helperErr = upsertActionCustomPermissions(db,
					[]StandardActionCustomPermissionDef{{APIName: "AdminToggleProbe", Label: "p"}}, "now")
			}
			require.NoError(t, helperErr)
			require.NoError(t, mock.ExpectationsWereMet())
		})
	}
}

// TestCustomPermissionUpsertCarriesIsCustomizedGuard is the narrow
// regression gate for the _System_CustomPermission fix specifically. This
// table's upsert had NO IsCustomized guard at all before Finding-3. That
// meant admin-disabling a custom permission (e.g. `SkipOpportunityOnConvert`)
// was silently reset to IsActive=1 on every bootstrap — and
// perm_system.go:417's effective permission evaluation uses `AND cp.IsActive = 1`,
// so the auth boundary was drifting under admin's feet.
//
// The test pins the IsCustomized=0 guard on Label/Description AND the
// narrowed IsActive/IsDeleted gate. If a future refactor drops the
// guard, the regex fails before shipping.
func TestCustomPermissionUpsertCarriesIsCustomizedGuard(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec(
		`INSERT INTO _System_CustomPermission` +
			`[\s\S]+ON DUPLICATE KEY UPDATE` +
			`[\s\S]+Label\s*=\s*IF\(IsCustomized = 0, VALUES\(Label\), Label\)` +
			`[\s\S]+Description\s*=\s*IF\(IsCustomized = 0, VALUES\(Description\), Description\)` +
			`[\s\S]+IsActive\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, VALUES\(IsActive\), IsActive\)` +
			`[\s\S]+IsDeleted\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted\)`,
	).WillReturnResult(sqlmock.NewResult(1, 1))

	err = upsertActionCustomPermissions(db,
		[]StandardActionCustomPermissionDef{
			{APIName: "SkipOpportunityOnConvert", Label: "Skip", Description: "desc"},
		}, "now")
	require.NoError(t, err)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestInternalAccessScopeUpsertIsL1Only(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectExec(
		`INSERT INTO _System_StandardActionInternalAccessScope` +
			`[\s\S]+ON DUPLICATE KEY UPDATE` +
			`[\s\S]+Id\s*=\s*VALUES\(Id\)` +
			`[\s\S]+ObjectApiName\s*=\s*VALUES\(ObjectApiName\)` +
			`[\s\S]+Operation\s*=\s*VALUES\(Operation\)` +
			`[\s\S]+Reason\s*=\s*VALUES\(Reason\)` +
			`[\s\S]+IsActive\s*=\s*VALUES\(IsActive\)` +
			`[\s\S]+IsDeleted\s*=\s*VALUES\(IsDeleted\)`,
	).WillReturnResult(sqlmock.NewResult(1, 1))

	_, err = upsertActionInternalAccessScopes(db, "sa_probe",
		[]StandardActionInternalAccessScopeDef{{ObjectAPIName: "Task", Operation: "Edit", Reason: "test"}}, "now")
	require.NoError(t, err)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestInternalAccessScopeRuntimeLoadsOnlyPackDeclaredScopes(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"probe","label":"Probe","flowDeveloperName":"Std_Probe",
		"internalAccessScopes":[{"objectApiName":"Task","operation":"Edit","reason":"pack scope"}]}]`)
	defer func() { standardActionsJSON = original }()

	expectPersistedStandardActionScopeOwner(mock, "custom_probe", "probe", "Std_Probe", false)
	expectActiveStandardFlowPackOwned(mock, "Std_Probe", FlowSourceStandard, false)
	mock.ExpectQuery(`SELECT StandardActionId, ObjectApiName, Operation, Reason\s+FROM _System_StandardActionInternalAccessScope\s+WHERE StandardActionId = \? AND IsActive = 1 AND IsDeleted = 0\s+AND \(\(Id = \? AND ObjectApiName = \? AND Operation = \?\)\)`).
		WithArgs("custom_probe", actionInternalAccessScopeID("custom_probe", "Task", "Edit"), "Task", "Edit").
		WillReturnRows(sqlmock.NewRows([]string{"StandardActionId", "ObjectApiName", "Operation", "Reason"}).
			AddRow("custom_probe", "Task", "Edit", "pack scope"))

	rows, err := GetStandardActionInternalAccessScopes(db, "custom_probe")
	require.NoError(t, err)
	require.Len(t, rows, 1)
	assert.Equal(t, "Task", rows[0].ObjectAPIName)
	assert.Equal(t, "Edit", rows[0].Operation)
	require.NoError(t, mock.ExpectationsWereMet())

	expectPersistedStandardActionScopeOwner(mock, "sa_not_in_pack", "notInPack", "Std_NotInPack", false)
	noRows, err := GetStandardActionInternalAccessScopes(db, "sa_not_in_pack")
	require.NoError(t, err)
	assert.Empty(t, noRows, "runtime must not query or load admin-authored internal scopes absent from the shipped pack")
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestInternalAccessScopeRuntimeSkipsCustomizedActionAndFlow(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"probe","label":"Probe","flowDeveloperName":"Std_Probe",
		"internalAccessScopes":[{"objectApiName":"Task","operation":"Edit","reason":"pack scope"}]}]`)
	defer func() { standardActionsJSON = original }()

	expectPersistedStandardActionScopeOwner(mock, "custom_probe", "probe", "Std_Probe", true)
	customizedActionRows, err := GetStandardActionInternalAccessScopes(db, "custom_probe")
	require.NoError(t, err)
	assert.Empty(t, customizedActionRows, "customized standard action rows must not inherit L1 internal access scopes")
	require.NoError(t, mock.ExpectationsWereMet())

	expectPersistedStandardActionScopeOwner(mock, "custom_probe", "probe", "Std_Probe", false)
	expectActiveStandardFlowPackOwned(mock, "Std_Probe", FlowSourceCustom, false)
	customFlowRows, err := GetStandardActionInternalAccessScopes(db, "custom_probe")
	require.NoError(t, err)
	assert.Empty(t, customFlowRows, "custom active Flow versions must not inherit scopes reviewed for the L1 standard graph")
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestActionInputGuardID_DerivesFromConditionAndGuardKey pins the Phase-1A
// rekeying of _System_ActionInputGuard rows. The prior positional-index
// shape broke idempotency when guards were reordered in the pack; the
// key-derived shape is stable across reorders and matches the true unique
// key (StandardActionId, Condition, GuardKey) from
// system_tables.json's uq_aig_rule index.
func TestActionInputGuardID_DerivesFromConditionAndGuardKey(t *testing.T) {
	permissionKey := actionInputGuardKey(StandardActionGuardDef{RequiredPermission: "SkipOpportunityOnConvert"})
	a := actionInputGuardID("sa_convertlead",
		"Input.doNotCreateOpportunity == true", permissionKey)
	b := actionInputGuardID("sa_convertlead",
		"Input.doNotCreateOpportunity == true", permissionKey)
	assert.Equal(t, a, b, "same (condition, guard key) must produce the same Id")

	c := actionInputGuardID("sa_convertlead",
		"Input.doNotCreateOpportunity == true", actionInputGuardKey(StandardActionGuardDef{RequiredPermission: "DifferentPermission"}))
	assert.NotEqual(t, a, c, "different RequiredPermission guard key must produce a different Id")

	objectGuardKey := actionInputGuardKey(StandardActionGuardDef{RequiredObject: "Account", RequiredOperation: "Create"})
	objectGuard := actionInputGuardID("sa_convertlead", "ISBLANK(Input.accountId)", objectGuardKey)
	assert.NotEqual(t, a, objectGuard, "object CRUD guard key must not collide with permission guard key")

	d := actionInputGuardID("sa_convertlead",
		"Input.otherFlag == true", permissionKey)
	assert.NotEqual(t, a, d, "different Condition must produce a different Id")

	// Id shape: prefix + action stem + stable hex. No bounded length issue,
	// no invalid characters from free-form formula input.
	assert.Contains(t, a, "aig_convertlead_",
		"Id must begin with aig_<action-stem>_ for backwards-grep compatibility")
}

func TestLoadPersistedActionInputGuardIDs_IncludesCustomizedTombstones(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	const (
		actionID = "sa_convertlead"
		guardID  = "aig_custom_deleted"
		cond     = "$Input.accountId == null"
		guardKey = "permission:ConvertLeads"
	)
	mock.ExpectQuery(`SELECT Id, .*Condition.*, GuardKey FROM .*ActionInputGuard.* WHERE StandardActionId = .* AND \(IsDeleted = 0 OR IsCustomized = 1\)`).
		WithArgs(actionID).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Condition", "GuardKey"}).
			AddRow(guardID, cond, guardKey))

	ids, err := loadPersistedActionInputGuardIDs(db, actionID, []string{actionInputGuardIdentityKey(cond, guardKey)})
	require.NoError(t, err)
	assert.Equal(t, []string{guardID}, ids)
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcile_PackRemove_DeactivatesStandardOnly pins the upgrade-safety
// fix: when the pack drops an input that used to exist as an IsCustomized=0
// row, the reconcile pass soft-deletes it. Customized admin rows are never
// candidates (the SELECT's `AND IsCustomized=0` guard filters them out at
// the DB level — see CustomizedThenRemoved_Preserved which pins that SQL
// shape).
func TestReconcile_PackRemove_DeactivatesStandardOnly(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"act","label":"l","flowDeveloperName":"f",
		"inputs":[{"apiName":"stillShipped","dataType":"string"}]}]`)
	defer func() { standardActionsJSON = original }()

	expectPackHashFastPathMiss(mock)

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "act", "sa_act")
	// The pack's single input upserts successfully.
	mock.ExpectExec(`INSERT INTO _System_ActionSchema`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	// Reconcile SELECT returns TWO Ids: the one we just upserted + an
	// orphan from a prior release. Only the orphan is IsCustomized=0 and
	// absent from the pack key set, so only the orphan goes into the
	// batch UPDATE.
	stillShippedID := actionSchemaID("sa_act", "input", "stillShipped")
	removedOrphanID := actionSchemaID("sa_act", "input", "removedByPack")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema.* WHERE StandardActionId = .* AND IsCustomized = 0 AND IsDeleted = 0`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow(stillShippedID).
			AddRow(removedOrphanID))
	// The UPDATE IN (...) MUST contain only the orphan Id, not the still-shipped Id.
	mock.ExpectExec(`UPDATE .*ActionSchema.* SET IsActive=0, IsDeleted=1.* WHERE Id IN \(\?\)`).
		WithArgs(sqlmock.AnyArg(), removedOrphanID).
		WillReturnResult(sqlmock.NewResult(0, 1))

	// Remaining satellite reconciles have nothing to do (no pack rows, no orphans).
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault.* WHERE StandardActionId`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard.* WHERE StandardActionId`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope.* WHERE StandardActionId`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	expectParentReconcileNoOrphans(mock, []string{"act"}, []string{"sa_act"})
	expectPackHashStamp(mock)

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcile_CustomizedThenRemoved_Preserved pins the SQL shape: the
// reconcile pass filters out IsCustomized=1 rows at the query level so
// admin edits can never be a deactivation candidate. If a future refactor
// drops the `AND IsCustomized=0` guard, this test catches it.
func TestReconcile_CustomizedThenRemoved_Preserved(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"act","label":"l","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	expectPackHashFastPathMiss(mock)
	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "act", "sa_act")

	// The reconcile SELECT pattern must contain `IsCustomized = 0` for the
	// customizable satellites. The regex below fails if that guard is ever
	// dropped.
	for _, tableRe := range []string{
		`ActionSchema.* WHERE StandardActionId = .* AND IsCustomized = 0`,
		`ActionFieldDefault.* WHERE StandardActionId = .* AND IsCustomized = 0`,
		`ActionInputGuard.* WHERE StandardActionId = .* AND IsCustomized = 0`,
	} {
		mock.ExpectQuery(tableRe).
			WithArgs("sa_act").
			WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	}
	mock.ExpectQuery(`StandardActionInternalAccessScope.* WHERE StandardActionId = .* AND IsDeleted = 0`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	expectParentReconcileNoOrphans(mock, []string{"act"}, []string{"sa_act"})
	expectPackHashStamp(mock)

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcile_ReintroducedStandard_Reactivates pins the reactivation
// branch: ON DUPLICATE KEY UPDATE must include `IsDeleted = IF(IsCustomized
// = 0, 0, IsDeleted)` so a pack row that reappears after a prior
// deactivation cleanly flips IsDeleted back to 0 on non-customized rows.
// Without this branch, a re-introduced pack row would remain soft-deleted
// — upsert would hit the unique key, preserve IsDeleted=1 from the ON
// DUPLICATE UPDATE list (because we wouldn't touch it), and the reconcile
// pass's IsDeleted=0 filter would no longer see the row.
func TestReconcile_ReintroducedStandard_Reactivates(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"act","label":"l","flowDeveloperName":"f",
		"inputs":[{"apiName":"resurrected","dataType":"string"}]}]`)
	defer func() { standardActionsJSON = original }()

	expectPackHashFastPathMiss(mock)
	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "act", "sa_act")

	// The schema-row INSERT must carry the IsDeleted reactivation branch
	// in its ON DUPLICATE KEY UPDATE. Regex asserts the literal SQL shape.
	mock.ExpectExec(`INSERT INTO _System_ActionSchema[\s\S]+ON DUPLICATE KEY UPDATE[\s\S]+IsDeleted\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted\)`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	// Also pin the branch on the other satellite INSERTs while we're
	// here — centralizes the reactivation regression gate.
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope`).
		WithArgs("sa_act").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	expectParentReconcileNoOrphans(mock, []string{"act"}, []string{"sa_act"})
	expectPackHashStamp(mock)

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())

	// Bonus regex check on the other customizable satellite tables' insert
	// SQL — cheap, and prevents a future "drop IsDeleted branch from only
	// N-of-3 tables" footgun.
	for _, table := range []string{tableActionFieldDefault, tableActionInputGuard} {
		t.Run("IsDeleted_reactivation_branch_on_"+table, func(t *testing.T) {
			// Re-derive the SQL shape used by the seeder by running a
			// no-row upsert with sqlmock that asserts the regex. Cheaper
			// than duplicating the SELECT/INSERT harness.
			db2, mock2, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
			require.NoError(t, err)
			defer db2.Close()
			// No need for parent upsert — we call the helper directly.
			mock2.ExpectExec(
				`INSERT INTO ` + table + `[\s\S]+ON DUPLICATE KEY UPDATE[\s\S]+IsDeleted\s*=\s*IF\(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted\)`,
			).WillReturnResult(sqlmock.NewResult(1, 1))
			var helperErr error
			switch table {
			case tableActionFieldDefault:
				_, helperErr = upsertActionFieldDefaults(db2, "sa_probe",
					[]StandardActionDefaultDef{{TargetObject: "X", FieldAPIName: "Y", ValueFormula: "1"}}, "now")
			case tableActionInputGuard:
				guard := StandardActionGuardDef{Condition: "c", RequiredPermission: "p"}
				guardKey := actionInputGuardKey(guard)
				guardID := actionInputGuardID("sa_probe", guard.Condition, guardKey)
				expectPersistedActionInputGuardIDs(mock2, "sa_probe", [3]string{guardID, guard.Condition, guardKey})
				_, helperErr = upsertActionInputGuards(db2, "sa_probe",
					[]StandardActionGuardDef{guard}, "now")
			}
			require.NoError(t, helperErr)
			require.NoError(t, mock2.ExpectationsWereMet())
		})
	}
}

// TestSatelliteKeyColumns_PinPackContract is the Phase-1A version of the
// schema-drift guard: the reconcile pass inside standard_actions.go must
// key each satellite by the same composite used in the uq_* index declared
// in system_tables.json. Phase 1B extracts this into a shared utility +
// descriptor; Phase 1A pins it with a direct test so the later extraction
// is behavior-preserving.
//
// Today we assert by exercising the Id-derivation functions against
// distinct (input tuple) values and verifying that only the intended
// fields produce collisions. If a future refactor changes the composition
// (e.g. drops Priority from actionFieldDefaultID), this test catches the
// regression before it ships.
func TestSatelliteKeyColumns_PinPackContract(t *testing.T) {
	// _System_ActionSchema keyed by (StandardActionId, Direction, APIName).
	assert.Equal(t,
		actionSchemaID("sa_convertlead", "input", "leadId"),
		actionSchemaID("sa_convertlead", "input", "leadId"),
		"schema Id must be deterministic for same (action, direction, APIName)")
	assert.NotEqual(t,
		actionSchemaID("sa_convertlead", "input", "leadId"),
		actionSchemaID("sa_convertlead", "output", "leadId"),
		"schema Id must change with Direction")
	assert.NotEqual(t,
		actionSchemaID("sa_convertlead", "input", "leadId"),
		actionSchemaID("sa_convertlead", "input", "accountId"),
		"schema Id must change with APIName")

	// _System_ActionFieldDefault keyed by (StandardActionId, TargetObject, FieldAPIName, Priority).
	assert.Equal(t,
		actionFieldDefaultID("sa_convertlead", "Opportunity", "CloseDate", 0),
		actionFieldDefaultID("sa_convertlead", "Opportunity", "CloseDate", 0))
	assert.NotEqual(t,
		actionFieldDefaultID("sa_convertlead", "Opportunity", "CloseDate", 0),
		actionFieldDefaultID("sa_convertlead", "Opportunity", "CloseDate", 10),
		"field-default Id must change with Priority (lets admin-overlay rows coexist with pack rows)")
	assert.NotEqual(t,
		actionFieldDefaultID("sa_convertlead", "Opportunity", "CloseDate", 0),
		actionFieldDefaultID("sa_convertlead", "Contact", "CloseDate", 0),
		"field-default Id must change with TargetObject")

	// _System_ActionInputGuard keyed by (StandardActionId, Condition, GuardKey)
	// — exercised in detail by TestActionInputGuardID_DerivesFromConditionAndGuardKey above.
	assert.Equal(t,
		actionInternalAccessScopeID("sa_convertlead", "Task", "Edit"),
		actionInternalAccessScopeID("sa_convertlead", "Task", "Edit"),
		"internal-access scope Id must be deterministic for same (action, object, operation)")
	assert.NotEqual(t,
		actionInternalAccessScopeID("sa_convertlead", "Task", "Edit"),
		actionInternalAccessScopeID("sa_convertlead", "Task", "Create"),
		"internal-access scope Id must change with Operation")
	assert.NotEqual(t,
		actionInternalAccessScopeID("sa_convertlead", "Task", "Edit"),
		actionInternalAccessScopeID("sa_convertlead", "CampaignMember", "Edit"),
		"internal-access scope Id must change with ObjectApiName")
}