package persistence

import (
	"context"
	"database/sql"
	"encoding/json"
	"reflect"
	"strings"
	"testing"
	"time"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestCurrentListFieldsAndRoleDistanceOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db, constants.TableObject, constants.TableLayoutAssignment)
	seedRecordTypeLifecycleObject(t, db, "obj-account", "Account", false)
	seedRecordTypeLifecycleObject(t, db, "obj-contact", "Contact", false)
	metadataReadExec(t, db, "UPDATE `_System_Object` SET `ListFields` = ? WHERE `ApiName` = ?", `["Name","OwnerId"]`, "Account")
	metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, RoleId, Specificity, CreatedDate)
		VALUES
		('assignment-parent', 'Account', 'layout-parent', 'role-parent', 1, '2025-01-01 00:00:00'),
		('assignment-user', 'Account', 'layout-user', 'role-user', 1, '2025-02-01 00:00:00')`)

	repository := NewMetadataRepository(db)
	fields, err := repository.ReadCurrentObjectListFields(t.Context(), []string{"Account", "Contact"})
	if err != nil {
		t.Fatal(err)
	}
	if !reflect.DeepEqual(fields["account"], []string{"Name", "OwnerId"}) ||
		fields["contact"] == nil || len(fields["contact"]) != 0 {
		t.Fatalf("current ListFields = %#v", fields)
	}
	assignment, err := repository.FindBestAssignment(
		t.Context(), "Account", []string{"role-user", "role-parent"}, "", "",
	)
	if err != nil {
		t.Fatal(err)
	}
	if assignment == nil || assignment.ID != "assignment-user" {
		t.Fatalf("nearest role assignment = %#v", assignment)
	}
}

func TestPageLayoutWriteOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()

	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TableField,
		constants.TablePageLayout,
		constants.TableLayoutAssignment,
		constants.TablePageLayoutHistory,
	)
	seedRecordTypeLifecycleObject(t, db, "obj-account", "Account", false)
	repository := NewMetadataRepository(db)

	t.Run("admin save transfers managed ownership and rejects collisions", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-managed", objectAPIName: "Account", layoutName: "Main",
			source: "curated", ownerPack: "crm",
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})

		layout := &models.PageLayout{
			ID: "layout-managed", ObjectAPIName: "Account", LayoutName: "Main",
			Source: "curated", Sections: []models.PageSection{{ID: "details", Fields: []string{"Name", "Phone"}}},
		}
		if err := repository.SaveLayout(t.Context(), layout); err != nil {
			t.Fatalf("SaveLayout managed takeover: %v", err)
		}
		requirePageLayoutOwner(t, db, layout.ID, "admin_created", true, "")

		persisted, err := repository.GetLayout(t.Context(), layout.ID)
		if err != nil {
			t.Fatalf("GetLayout: %v", err)
		}
		if got := persisted.Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name", "Phone"}) {
			t.Fatalf("persisted fields = %#v", got)
		}

		err = repository.SaveLayout(t.Context(), &models.PageLayout{
			ID: "layout-collision", ObjectAPIName: "account", LayoutName: "main",
		})
		if err == nil || !apperrors.IsConflict(err) {
			t.Fatalf("case-insensitive business-key collision = %v, want conflict", err)
		}
	})

	t.Run("malformed managed ownership fails closed", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-malformed", objectAPIName: "Account", layoutName: "Malformed",
			source: "curated", customized: true, ownerPack: "crm",
		})
		err := repository.SaveLayout(t.Context(), &models.PageLayout{
			ID: "layout-malformed", ObjectAPIName: "Account", LayoutName: "Malformed",
		})
		if err == nil || !apperrors.IsValidation(err) {
			t.Fatalf("malformed owner tuple = %v, want validation error", err)
		}
		requirePageLayoutOwner(t, db, "layout-malformed", "curated", true, "crm")
	})

	t.Run("strict admin create rejects an existing identity", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-strict-create", objectAPIName: "Account", layoutName: "Strict Create",
			source: "admin_created", customized: true,
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		replacement := &models.PageLayout{
			ID: "layout-strict-create", ObjectAPIName: "Account", LayoutName: "Strict Create",
			Sections: []models.PageSection{{ID: "details", Fields: []string{"Phone"}}},
		}
		if err := repository.CreateLayoutForAdministration(t.Context(), replacement); err == nil || !apperrors.IsConflict(err) {
			t.Fatalf("strict create collision = %v, want conflict", err)
		}
		if got := requirePageLayout(t, repository, replacement.ID).Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name"}) {
			t.Fatalf("strict create collision changed fields = %#v", got)
		}
	})

	t.Run("strict authoring create owns lifecycle fields", func(t *testing.T) {
		layout := &models.PageLayout{
			ID: "layout-owned-lifecycle", ObjectAPIName: "Account", LayoutName: "Owned Lifecycle",
			IsDefault: true, IsDeleted: true, GenerationVersion: 99,
		}
		if err := repository.CreateLayoutForAdministration(t.Context(), layout); err != nil {
			t.Fatalf("strict create: %v", err)
		}
		var isDefault, isDeleted bool
		var generation int
		var configJSON string
		if err := db.QueryRow(
			"SELECT IsDefault, IsDeleted, GenerationVersion, Config FROM `_System_PageLayout` WHERE Id = ?", layout.ID,
		).Scan(&isDefault, &isDeleted, &generation, &configJSON); err != nil {
			t.Fatal(err)
		}
		if isDefault || isDeleted || generation != 0 {
			t.Fatalf("stored lifecycle = default:%v deleted:%v generation:%d, want false/false/0", isDefault, isDeleted, generation)
		}
		var configured models.PageLayout
		if err := decodeJSONUseNumber(configJSON, &configured); err != nil {
			t.Fatal(err)
		}
		if configured.IsDefault || configured.IsDeleted || configured.GenerationVersion != 0 {
			t.Fatalf("stored Config leaked caller lifecycle: %#v", configured)
		}
	})

	t.Run("strict admin storage update does not treat audit time as a version", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-audit-time", objectAPIName: "Account", layoutName: "Audit Time",
			source: "admin_created", customized: true,
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		update := requirePageLayout(t, repository, "layout-audit-time")
		metadataReadExec(t, db,
			"UPDATE `_System_PageLayout` SET `LastModifiedDate` = '2026-08-30 03:04:05' WHERE `Id` = ?",
			update.ID,
		)
		update.Sections[0].Fields = append(update.Sections[0].Fields, "Phone")
		if err := repository.SaveLayoutForAdministration(t.Context(), update); err != nil {
			t.Fatalf("storage update rejected an audit timestamp mismatch: %v", err)
		}
		if got := requirePageLayout(t, repository, update.ID).Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name", "Phone"}) {
			t.Fatalf("stored fields = %#v, want metadata-approved update", got)
		}
	})

	t.Run("strict admin update rejects missing identity", func(t *testing.T) {
		missing := &models.PageLayout{
			ID: "layout-missing-update", ObjectAPIName: "Account", LayoutName: "Missing Update",
		}
		if err := repository.SaveLayoutForAdministration(t.Context(), missing); err == nil || !apperrors.IsNotFound(err) {
			t.Fatalf("missing strict update = %v, want not found", err)
		}
	})

	t.Run("strict admin storage update accepts an unchanged document", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-noop-update", objectAPIName: "Account", layoutName: "No-op Update",
			source: "admin_created", customized: true,
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		current := requirePageLayout(t, repository, "layout-noop-update")
		if err := repository.SaveLayoutForAdministration(t.Context(), current); err != nil {
			t.Fatalf("first unchanged strict update: %v", err)
		}
		current = requirePageLayout(t, repository, "layout-noop-update")
		if err := repository.SaveLayoutForAdministration(t.Context(), current); err != nil {
			t.Fatalf("second unchanged strict update: %v", err)
		}
	})

	t.Run("strict admin update renames the authored document", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-rename", objectAPIName: "Account", layoutName: "Before Rename",
			source: "admin_created", customized: true,
		})
		current := requirePageLayout(t, repository, "layout-rename")
		current.LayoutName = "After Rename"
		if err := repository.SaveLayoutForAdministration(t.Context(), current); err != nil {
			t.Fatalf("strict authored rename: %v", err)
		}
		if got := requirePageLayout(t, repository, current.ID).LayoutName; got != "After Rename" {
			t.Fatalf("renamed LayoutName = %q, want After Rename", got)
		}
	})

	t.Run("strict admin update converges raw default from wildcard authority", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-default-drift", objectAPIName: "Account", layoutName: "Default Drift",
			source: "admin_created", customized: true,
		})
		metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET IsDefault = 1 WHERE Id = ?", "layout-default-drift")
		current := requirePageLayout(t, repository, "layout-default-drift")
		if current.IsDefault {
			t.Fatal("caller-effective read trusted raw default without a wildcard")
		}
		if err := repository.SaveLayoutForAdministration(t.Context(), current); err != nil {
			t.Fatalf("strict update: %v", err)
		}
		var rawDefault bool
		var configJSON string
		if err := db.QueryRow("SELECT IsDefault, Config FROM `_System_PageLayout` WHERE Id = ?", current.ID).Scan(&rawDefault, &configJSON); err != nil {
			t.Fatal(err)
		}
		var configured models.PageLayout
		if err := decodeJSONUseNumber(configJSON, &configured); err != nil {
			t.Fatal(err)
		}
		if rawDefault || configured.IsDefault {
			t.Fatalf("strict update retained raw default drift: row=%v config=%v", rawDefault, configured.IsDefault)
		}
	})

	t.Run("stored authoring JSON preserves exact numbers and action extensions", func(t *testing.T) {
		layout := &models.PageLayout{
			ID: "layout-exact-json", ObjectAPIName: "Account", LayoutName: "Exact JSON",
			Sections: []models.PageSection{{
				ID: "details", ComponentConfig: map[string]interface{}{
					"scaled":  json.Number("12.50"),
					"large":   json.Number("9007199254740993"),
					"precise": json.Number("0.123456789012345678901234567890"),
				},
			}},
			HeaderActions: []models.ActionConfig{{
				Name: "save", Label: "Save", Type: "Standard",
				Extensions: map[string]interface{}{"flowName": "Save_Account"},
			}},
		}
		if err := repository.CreateLayoutForAdministration(t.Context(), layout); err != nil {
			t.Fatalf("CreateLayoutForAdministration: %v", err)
		}
		stored := requirePageLayout(t, repository, layout.ID)
		config := stored.Sections[0].ComponentConfig
		for name, want := range map[string]string{
			"scaled": "12.50", "large": "9007199254740993", "precise": "0.123456789012345678901234567890",
		} {
			value, ok := config[name].(json.Number)
			if !ok || value.String() != want {
				t.Fatalf("stored exact number %s = %#v, want json.Number(%q)", name, config[name], want)
			}
		}
		if got := stored.HeaderActions[0].Extensions["flowName"]; got != "Save_Account" {
			t.Fatalf("stored action extension = %#v", got)
		}
		var raw string
		if err := db.QueryRow("SELECT `Config` FROM `_System_PageLayout` WHERE `Id` = ?", layout.ID).Scan(&raw); err != nil {
			t.Fatal(err)
		}
		for _, exact := range []string{"12.50", "9007199254740993", "0.123456789012345678901234567890"} {
			if !strings.Contains(raw, exact) {
				t.Fatalf("stored authoring JSON lost exact number %q: %s", exact, raw)
			}
		}
	})

	t.Run("layout document list fields and history join the caller transaction", func(t *testing.T) {
		tx, err := db.BeginTx(t.Context(), nil)
		if err != nil {
			t.Fatal(err)
		}
		txCtx := context.WithValue(t.Context(), txContextKey{}, tx)
		layout := &models.PageLayout{
			ID: "layout-atomic", ObjectAPIName: "Account", LayoutName: "Atomic",
			Sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		}
		if err := repository.CreateLayoutForAdministration(txCtx, layout); err != nil {
			_ = tx.Rollback()
			t.Fatal(err)
		}
		const writerID = "nexuscrm-persistence/repo_layout.go#MetadataRepository.SetLayoutAuthoringListFields"
		if err := repository.SetLayoutAuthoringListFields(withPermissionStateDirectWriter(txCtx, writerID), "Account", []string{}); err != nil {
			_ = tx.Rollback()
			t.Fatal(err)
		}
		if err := repository.WriteLayoutHistory(txCtx, &models.PageLayoutHistoryEntry{
			LayoutID: layout.ID, Config: `{"id":"layout-atomic"}`, ChangeReason: "admin_create",
		}); err != nil {
			_ = tx.Rollback()
			t.Fatal(err)
		}
		if err := tx.Rollback(); err != nil {
			t.Fatal(err)
		}
		var layoutCount, historyCount int
		if err := db.QueryRow("SELECT COUNT(*) FROM `_System_PageLayout` WHERE `Id` = ?", layout.ID).Scan(&layoutCount); err != nil {
			t.Fatal(err)
		}
		if err := db.QueryRow("SELECT COUNT(*) FROM `_System_PageLayoutHistory` WHERE `LayoutId` = ?", layout.ID).Scan(&historyCount); err != nil {
			t.Fatal(err)
		}
		var listFieldsCustomized bool
		if err := db.QueryRow("SELECT `ListFieldsCustomized` FROM `_System_Object` WHERE `ApiName` = 'Account'").Scan(&listFieldsCustomized); err != nil {
			t.Fatal(err)
		}
		if layoutCount != 0 || historyCount != 0 || listFieldsCustomized {
			t.Fatalf("rolled-back layout/history/list customization = %d/%d/%t, want 0/0/false",
				layoutCount, historyCount, listFieldsCustomized)
		}
	})

	t.Run("list fields take only column ownership", func(t *testing.T) {
		tx, err := db.BeginTx(t.Context(), nil)
		if err != nil {
			t.Fatal(err)
		}
		txCtx := context.WithValue(t.Context(), txContextKey{}, tx)
		const writerID = "nexuscrm-persistence/repo_layout.go#MetadataRepository.SetLayoutAuthoringListFields"
		if err := repository.SetLayoutAuthoringListFields(
			withPermissionStateDirectWriter(txCtx, writerID), "Account", []string{"Name", "OwnerId"},
		); err != nil {
			_ = tx.Rollback()
			t.Fatal(err)
		}
		if err := tx.Commit(); err != nil {
			t.Fatal(err)
		}
		var encoded string
		var listFieldsCustomized, objectCustomized bool
		if err := db.QueryRow(
			"SELECT `ListFields`, `ListFieldsCustomized`, `IsCustomized` FROM `_System_Object` WHERE `ApiName` = 'Account'",
		).Scan(&encoded, &listFieldsCustomized, &objectCustomized); err != nil {
			t.Fatal(err)
		}
		var fields []string
		if err := decodeJSONUseNumber(encoded, &fields); err != nil {
			t.Fatal(err)
		}
		if !reflect.DeepEqual(fields, []string{"Name", "OwnerId"}) || !listFieldsCustomized || objectCustomized {
			t.Fatalf(
				"list ownership = %s/%t/%t, want authored fields/true/false",
				encoded, listFieldsCustomized, objectCustomized,
			)
		}
	})

	t.Run("field admission exact rollback restores content and ownership", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-exact", objectAPIName: "Account", layoutName: "Exact",
			source: "curated", ownerPack: "crm",
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		original := requirePageLayout(t, repository, "layout-exact")
		admitted := clonePageLayoutWithFields(original, "Name", "Temporary__c")

		restore, err := repository.SaveLayoutForFieldAdmission(t.Context(), original, admitted)
		if err != nil {
			t.Fatalf("SaveLayoutForFieldAdmission: %v", err)
		}
		if err := restore(t.Context(), admitted); err != nil {
			t.Fatalf("restore field admission: %v", err)
		}
		requirePageLayoutOwner(t, db, original.ID, "curated", false, "crm")
		if got := requirePageLayout(t, repository, original.ID).Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name"}) {
			t.Fatalf("restored fields = %#v", got)
		}
	})

	t.Run("field admission rollback preserves concurrent admin edits", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-concurrent", objectAPIName: "Account", layoutName: "Concurrent",
			source: "curated", ownerPack: "crm",
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		original := requirePageLayout(t, repository, "layout-concurrent")
		admitted := clonePageLayoutWithFields(original, "Name", "Temporary__c")

		restore, err := repository.SaveLayoutForFieldAdmission(t.Context(), original, admitted)
		if err != nil {
			t.Fatalf("SaveLayoutForFieldAdmission: %v", err)
		}
		concurrent := clonePageLayoutWithFields(admitted, "Name", "Temporary__c", "AdminOnly__c")
		configJSON, err := repository.marshalJSON(concurrent)
		if err != nil {
			t.Fatalf("marshal concurrent layout: %v", err)
		}
		metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET `Config` = ? WHERE `Id` = ?", configJSON, concurrent.ID)

		if err := restore(t.Context(), admitted); err != nil {
			t.Fatalf("restore after concurrent edit: %v", err)
		}
		got := requirePageLayout(t, repository, original.ID).Sections[0].Fields
		if !reflect.DeepEqual(got, []string{"Name", "AdminOnly__c"}) {
			t.Fatalf("fields after residue repair = %#v", got)
		}
		requirePageLayoutOwner(t, db, original.ID, "admin_created", true, "")
	})

	t.Run("field admission rejects a stale original snapshot", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-stale", objectAPIName: "Account", layoutName: "Stale",
			source: "curated", ownerPack: "crm",
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		original := requirePageLayout(t, repository, "layout-stale")
		admitted := clonePageLayoutWithFields(original, "Name", "Temporary__c")
		concurrent := clonePageLayoutWithFields(original, "Name", "AdminOnly__c")
		configJSON, err := repository.marshalJSON(concurrent)
		if err != nil {
			t.Fatalf("marshal concurrent layout: %v", err)
		}
		metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET `Config` = ? WHERE `Id` = ?", configJSON, concurrent.ID)

		if _, err := repository.SaveLayoutForFieldAdmission(t.Context(), original, admitted); err == nil || !apperrors.IsConflict(err) {
			t.Fatalf("stale field admission = %v, want conflict", err)
		}
		if got := requirePageLayout(t, repository, original.ID).Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name", "AdminOnly__c"}) {
			t.Fatalf("fields after stale admission = %#v", got)
		}
		requirePageLayoutOwner(t, db, original.ID, "curated", false, "crm")
	})

	t.Run("field admission rollback keeps fields that became live", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-live-field", objectAPIName: "Account", layoutName: "Live Field",
			source: "curated", ownerPack: "crm",
			sections: []models.PageSection{{ID: "details", Fields: []string{"Name"}}},
		})
		original := requirePageLayout(t, repository, "layout-live-field")
		admitted := clonePageLayoutWithFields(original, "Name", "Live__c")
		restore, err := repository.SaveLayoutForFieldAdmission(t.Context(), original, admitted)
		if err != nil {
			t.Fatalf("SaveLayoutForFieldAdmission: %v", err)
		}
		concurrent := clonePageLayoutWithFields(admitted, "Name", "Live__c", "AdminOnly__c")
		configJSON, err := repository.marshalJSON(concurrent)
		if err != nil {
			t.Fatalf("marshal concurrent layout: %v", err)
		}
		metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET `Config` = ? WHERE `Id` = ?", configJSON, concurrent.ID)
		seedRecordTypeLifecycleField(t, db, "field-live", "obj-account", "Live__c", string(constants.FieldTypeString), nil, nil, "")

		if err := restore(t.Context(), admitted); err == nil || !apperrors.IsConflict(err) {
			t.Fatalf("restore with live admitted field = %v, want conflict", err)
		}
		if got := requirePageLayout(t, repository, original.ID).Sections[0].Fields; !reflect.DeepEqual(got, []string{"Name", "Live__c", "AdminOnly__c"}) {
			t.Fatalf("fields after live-field rollback = %#v", got)
		}
		requirePageLayoutOwner(t, db, original.ID, "admin_created", true, "")
	})

	t.Run("assignment takeover clears stale pack ownership", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-routing-a", objectAPIName: "Account", layoutName: "Routing A", source: "auto_generated",
		})
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "layout-routing-b", objectAPIName: "Account", layoutName: "Routing B", source: "auto_generated",
		})
		metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
			(Id, ObjectApiName, LayoutId, ProfileId, Specificity, RouteSource, OwnerPack)
			VALUES ('assignment-profile', 'Account', 'layout-routing-a', 'profile-1', 1, 'managed_seed', 'crm')`)
		profileID := "profile-1"
		routeSource := models.LayoutAssignmentRouteSourceAdminAuthored
		if err := repository.CreateAssignment(t.Context(), &models.LayoutAssignment{
			ObjectAPIName: "account", LayoutID: "layout-routing-b", ProfileID: &profileID, RouteSource: &routeSource,
		}); err != nil {
			t.Fatalf("CreateAssignment admin takeover: %v", err)
		}
		requireLayoutAssignmentOwner(t, db, "assignment-profile", "layout-routing-b", routeSource, "")

		metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
			(Id, ObjectApiName, LayoutId, Specificity, RouteSource, OwnerPack)
			VALUES ('assignment-fallback', 'Account', 'layout-routing-a', 0, 'runtime_fallback', 'crm')`)
		routeSource = models.LayoutAssignmentRouteSourceRuntimeFallback
		if err := repository.CreateAssignment(t.Context(), &models.LayoutAssignment{
			ObjectAPIName: "Account", LayoutID: "layout-routing-b", RouteSource: &routeSource,
		}); err != nil {
			t.Fatalf("CreateAssignment runtime fallback: %v", err)
		}
		requireLayoutAssignmentOwner(t, db, "assignment-fallback", "layout-routing-b", routeSource, "")
	})

	t.Run("set default stores the canonical Layout identity", func(t *testing.T) {
		seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
			id: "Layout-Canonical", objectAPIName: "Account", layoutName: "Canonical Default", source: "admin_created", customized: true,
		})
		if err := repository.SetDefaultLayout(t.Context(), "account", "layout-canonical", nil); err != nil {
			t.Fatalf("SetDefaultLayout case variant: %v", err)
		}
		var storedLayoutID string
		if err := db.QueryRow(
			"SELECT LayoutId FROM `_System_LayoutAssignment` WHERE ObjectApiName = ? AND RoleId IS NULL AND ProfileId IS NULL AND RecordTypeId IS NULL",
			"Account",
		).Scan(&storedLayoutID); err != nil {
			t.Fatal(err)
		}
		if storedLayoutID != "Layout-Canonical" {
			t.Fatalf("wildcard LayoutId = %q, want canonical identity", storedLayoutID)
		}
	})

	t.Run("platform upsert rejects admin-authored callers", func(t *testing.T) {
		err := repository.UpsertLayout(t.Context(), &models.PageLayout{Source: "admin_created", IsCustomized: true})
		if err == nil || !apperrors.IsValidation(err) {
			t.Fatalf("UpsertLayout admin route = %v, want validation error", err)
		}
	})
}

func TestLockLayoutAuthoringAggregateUsesCurrentReadsAfterWorkspaceWait(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TablePageLayout,
		constants.TableLayoutAssignment,
	)
	seedRecordTypeLifecycleObject(t, db, "obj-current-layout", "CurrentLayout", false)
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "layout-current-read", objectAPIName: "CurrentLayout", layoutName: "Before",
		source: "admin_created", customized: true,
	})

	tx1, cleanup1, err := BeginPessimisticTx(t.Context(), db)
	if err != nil {
		t.Fatal(err)
	}
	defer cleanup1()
	tx2, cleanup2, err := BeginPessimisticTx(t.Context(), db)
	if err != nil {
		_ = tx1.Rollback()
		t.Fatal(err)
	}
	defer cleanup2()

	var objectID string
	if err := tx1.QueryRowContext(t.Context(),
		"SELECT `Id` FROM `_System_Object` WHERE `ApiName` = ? FOR UPDATE", "CurrentLayout",
	).Scan(&objectID); err != nil {
		_ = tx1.Rollback()
		_ = tx2.Rollback()
		t.Fatal(err)
	}
	var staleName string
	if err := tx2.QueryRowContext(t.Context(),
		"SELECT `LayoutName` FROM `_System_PageLayout` WHERE `Id` = ?", "layout-current-read",
	).Scan(&staleName); err != nil {
		_ = tx1.Rollback()
		_ = tx2.Rollback()
		t.Fatal(err)
	}
	if staleName != "Before" {
		t.Fatalf("initial transaction snapshot = %q, want Before", staleName)
	}

	type aggregateResult struct {
		aggregate *models.LayoutAuthoringAggregate
		err       error
	}
	result := make(chan aggregateResult, 1)
	tx2Context := context.WithValue(t.Context(), txContextKey{}, tx2)
	go func() {
		aggregate, lockErr := NewMetadataRepository(db).LockLayoutAuthoringAggregate(tx2Context, "CurrentLayout")
		result <- aggregateResult{aggregate: aggregate, err: lockErr}
	}()
	select {
	case early := <-result:
		_ = tx1.Rollback()
		_ = tx2.Rollback()
		t.Fatalf("aggregate lock returned before workspace owner committed: %#v", early)
	case <-time.After(100 * time.Millisecond):
	}
	if _, err := tx1.ExecContext(t.Context(),
		"UPDATE `_System_PageLayout` SET `LayoutName` = ?, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE `Id` = ?",
		"After", "layout-current-read",
	); err != nil {
		_ = tx1.Rollback()
		_ = tx2.Rollback()
		t.Fatal(err)
	}
	if err := tx1.Commit(); err != nil {
		_ = tx2.Rollback()
		t.Fatal(err)
	}

	select {
	case locked := <-result:
		defer tx2.Rollback() //nolint:errcheck // read-only proof transaction
		if locked.err != nil {
			t.Fatal(locked.err)
		}
		if len(locked.aggregate.Layouts) != 1 || locked.aggregate.Layouts[0].LayoutName != "After" {
			t.Fatalf("locked aggregate reused stale child snapshot: %#v", locked.aggregate)
		}
	case <-time.After(5 * time.Second):
		_ = tx2.Rollback()
		t.Fatal("aggregate lock did not resume after workspace owner committed")
	}
}

func TestLayoutAuthoringAggregateReadAndLockShareStoredProjection(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TablePageLayout,
		constants.TableLayoutAssignment,
	)
	seedRecordTypeLifecycleObject(t, db, "obj-authoring-projection", "AuthoringProjection", false)
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "Layout-Create", objectAPIName: "AuthoringProjection", layoutName: "Create", source: "admin_created", customized: true,
	})
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "layout-shadow", objectAPIName: "AuthoringProjection", layoutName: "Shadow", source: "admin_created", customized: true,
	})
	metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET Type = 'Create' WHERE Id = 'Layout-Create'")
	metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET IsDefault = 1 WHERE Id = 'layout-shadow'")
	metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, Specificity, RouteSource)
		VALUES ('assignment-wildcard', 'AuthoringProjection', 'Layout-Create', 0, 'admin_authored')`)

	repository := NewMetadataRepository(db)
	plainTx, err := db.BeginTx(t.Context(), nil)
	if err != nil {
		t.Fatal(err)
	}
	plainContext := context.WithValue(t.Context(), txContextKey{}, plainTx)
	unlocked, err := repository.ReadLayoutAuthoringAggregate(plainContext, "authoringprojection")
	if err != nil {
		_ = plainTx.Rollback()
		t.Fatal(err)
	}
	if err := plainTx.Rollback(); err != nil {
		t.Fatal(err)
	}

	lockedTx, lockedCleanup, err := BeginPessimisticTx(t.Context(), db)
	if err != nil {
		t.Fatal(err)
	}
	defer lockedCleanup()
	lockedContext := context.WithValue(t.Context(), txContextKey{}, lockedTx)
	locked, err := repository.LockLayoutAuthoringAggregate(lockedContext, "AuthoringProjection")
	if err != nil {
		_ = lockedTx.Rollback()
		t.Fatal(err)
	}
	if err := lockedTx.Rollback(); err != nil {
		t.Fatal(err)
	}
	if !reflect.DeepEqual(unlocked, locked) {
		t.Fatalf("unlocked and locked authoring facts diverged\nunlocked: %#v\nlocked: %#v", unlocked, locked)
	}
	if len(locked.Layouts) != 2 || locked.Layouts[0].ID != "Layout-Create" || locked.Layouts[0].Type != "Create" ||
		!locked.Layouts[0].IsDefault || locked.Layouts[1].IsDefault {
		t.Fatalf("stored authoring projection filtered kind or trusted raw default: %#v", locked.Layouts)
	}

	metadataReadExec(t, db, "UPDATE `_System_PageLayout` SET Config = CONCAT(Config, '{}') WHERE Id = 'Layout-Create'")
	invalidTx, err := db.BeginTx(t.Context(), nil)
	if err != nil {
		t.Fatal(err)
	}
	defer invalidTx.Rollback() //nolint:errcheck
	invalidContext := context.WithValue(t.Context(), txContextKey{}, invalidTx)
	if _, err := repository.ReadLayoutAuthoringAggregate(invalidContext, "AuthoringProjection"); err == nil {
		t.Fatal("authoring read accepted a stored JSON prefix followed by another value")
	}
}

func TestLayoutAuthoringAggregateCanonicalizesHistoricalCaseAliases(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TablePageLayout,
		constants.TableLayoutAssignment,
	)
	seedRecordTypeLifecycleObject(t, db, "obj-system-file", "_System_File", false)
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "default__system_file", objectAPIName: "_system_file", layoutName: "Default", source: "admin_created", customized: true,
	})
	metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, Specificity, RouteSource)
		VALUES ('assignment-system-file', '_system_file', 'default__system_file', 0, 'admin_authored')`)

	repository := NewMetadataRepository(db)
	var snapshot *models.LayoutAuthoringAggregate
	for _, read := range []func(context.Context, string) (*models.LayoutAuthoringAggregate, error){
		repository.ReadLayoutAuthoringAggregate, repository.LockLayoutAuthoringAggregate,
	} {
		tx, release, err := BeginPessimisticTx(t.Context(), db)
		if err != nil {
			t.Fatal(err)
		}
		aggregate, err := read(context.WithValue(t.Context(), txContextKey{}, tx), "_System_File")
		_ = tx.Rollback()
		release()
		if err != nil {
			t.Fatal(err)
		}
		if aggregate.ObjectAPIName != "_System_File" || len(aggregate.Layouts) != 1 ||
			aggregate.Layouts[0].ObjectAPIName != "_System_File" ||
			len(aggregate.Assignments) != 1 || aggregate.Assignments[0].ObjectAPIName != "_System_File" {
			t.Fatalf("historical aliases were not projected through the canonical object: %#v", aggregate)
		}
		if snapshot != nil && !reflect.DeepEqual(snapshot, aggregate) {
			t.Fatal("historical aliases produced different snapshot and locked version inputs")
		}
		snapshot = aggregate
	}
	for _, table := range []string{"_System_PageLayout", "_System_LayoutAssignment"} {
		var stored string
		if err := db.QueryRow("SELECT ObjectApiName FROM `" + table + "`").Scan(&stored); err != nil {
			t.Fatal(err)
		}
		if stored != "_system_file" {
			t.Fatalf("%s read rewrote stored identity: %q", table, stored)
		}
	}
}

func TestLayoutAuthoringAggregateRejectsNonASCIIAndPaddedObjectIdentities(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db, constants.TableObject, constants.TablePageLayout, constants.TableLayoutAssignment)
	const canonical = "_System_Key"
	seedRecordTypeLifecycleObject(t, db, "obj-system-key", canonical, false)
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "layout-system-key", objectAPIName: canonical, layoutName: "Default", source: "admin_created", customized: true,
	})
	metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, Specificity, RouteSource)
		VALUES ('assignment-system-key', '_System_Key', 'layout-system-key', 0, 'admin_authored')`)
	repository := NewMetadataRepository(db)
	for _, source := range []struct{ table, column string }{
		{constants.TableObject, "ApiName"},
		{constants.TablePageLayout, "ObjectApiName"},
		{constants.TableLayoutAssignment, "ObjectApiName"},
	} {
		for _, invalid := range []struct{ name, value string }{
			{"kelvin_sign", "_system_Key"},
			{"long_s", "_ſystem_key"},
			{"accent", "_sýstem_key"},
			{"trailing_space", "_system_key "},
		} {
			t.Run(source.table+"/"+invalid.name, func(t *testing.T) {
				update := "UPDATE `" + source.table + "` SET `" + source.column + "` = ?"
				metadataReadExec(t, db, update, invalid.value)
				defer metadataReadExec(t, db, update, canonical)
				// Prove the malformed row reaches the real SQL lookup; a fixture
				// excluded by the database cannot exercise the identity guard.
				var selected int
				if err := db.QueryRowContext(t.Context(),
					"SELECT COUNT(*) FROM `"+source.table+"` WHERE LOWER(`"+source.column+"`) = LOWER(?)", canonical,
				).Scan(&selected); err != nil {
					t.Fatal(err)
				}
				if selected != 1 {
					t.Fatalf("malformed identity did not reach object lookup: selected %d rows", selected)
				}
				for _, reader := range []struct {
					name string
					read func(context.Context, string) (*models.LayoutAuthoringAggregate, error)
				}{
					{"snapshot", repository.ReadLayoutAuthoringAggregate},
					{"locked", repository.LockLayoutAuthoringAggregate},
				} {
					t.Run(reader.name, func(t *testing.T) {
						tx, release, err := BeginPessimisticTx(t.Context(), db)
						if err != nil {
							t.Fatal(err)
						}
						defer release()
						defer tx.Rollback() //nolint:errcheck // read-only proof transaction
						_, err = reader.read(context.WithValue(t.Context(), txContextKey{}, tx), canonical)
						if err == nil {
							t.Fatalf("invalid object identity %q was accepted as an ASCII case alias", invalid.value)
						}
						if !apperrors.IsInternal(err) || !strings.Contains(err.Error(), "object identity") {
							t.Fatalf("expected stored object identity rejection, got %v", err)
						}
					})
				}
			})
		}
	}
}

func TestLayoutAuthoringAggregateRejectsPhantomAssignmentFacts(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	createCanonicalMetadataTables(t, db,
		constants.TableObject,
		constants.TablePageLayout,
		constants.TableLayoutAssignment,
	)
	seedRecordTypeLifecycleObject(t, db, "obj-phantom-assignment", "PhantomAssignment", false)
	seedPageLayoutWriteOutcome(t, db, pageLayoutWriteSeed{
		id: "layout-phantom", objectAPIName: "PhantomAssignment", layoutName: "Phantom", source: "admin_created", customized: true,
	})
	metadataReadExec(t, db, `INSERT INTO _System_LayoutAssignment
		(Id, ObjectApiName, LayoutId, ProfileId, Specificity, RouteSource)
		VALUES ('assignment-phantom', 'PhantomAssignment', 'layout-phantom', '', 1, 'admin_authored')`)

	tx, err := db.BeginTx(t.Context(), nil)
	if err != nil {
		t.Fatal(err)
	}
	defer tx.Rollback() //nolint:errcheck
	ctx := context.WithValue(t.Context(), txContextKey{}, tx)
	if _, err := NewMetadataRepository(db).ReadLayoutAuthoringAggregate(ctx, "PhantomAssignment"); err == nil {
		t.Fatal("stored present-empty selector was silently normalized")
	}
}

type pageLayoutWriteSeed struct {
	id            string
	objectAPIName string
	layoutName    string
	source        string
	customized    bool
	ownerPack     any
	sections      []models.PageSection
}

func seedPageLayoutWriteOutcome(t *testing.T, db *sql.DB, seed pageLayoutWriteSeed) {
	t.Helper()
	layout := &models.PageLayout{
		ID: seed.id, ObjectAPIName: seed.objectAPIName, LayoutName: seed.layoutName,
		Source: seed.source, IsCustomized: seed.customized, Sections: seed.sections,
	}
	configJSON, err := NewMetadataRepository(db).marshalJSON(layout)
	if err != nil {
		t.Fatalf("marshal seeded layout: %v", err)
	}
	metadataReadExec(t, db, `INSERT INTO _System_PageLayout
		(Id, ObjectApiName, LayoutName, Config, IsCustomized, Source, OwnerPack)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		seed.id, seed.objectAPIName, seed.layoutName, configJSON, seed.customized, seed.source, seed.ownerPack)
}

func requirePageLayout(t *testing.T, repository *MetadataRepository, id string) *models.PageLayout {
	t.Helper()
	layout, err := repository.GetLayout(t.Context(), id)
	if err != nil {
		t.Fatalf("GetLayout(%s): %v", id, err)
	}
	if layout == nil {
		t.Fatalf("GetLayout(%s) = nil", id)
	}
	return layout
}

func clonePageLayoutWithFields(layout *models.PageLayout, fields ...string) *models.PageLayout {
	clone := *layout
	clone.Sections = []models.PageSection{{ID: "details", Fields: append([]string(nil), fields...)}}
	return &clone
}

func requirePageLayoutOwner(t *testing.T, db *sql.DB, id, wantSource string, wantCustomized bool, wantOwnerPack string) {
	t.Helper()
	var source string
	var customized bool
	var ownerPack sql.NullString
	if err := db.QueryRow("SELECT Source, IsCustomized, OwnerPack FROM `_System_PageLayout` WHERE Id = ?", id).
		Scan(&source, &customized, &ownerPack); err != nil {
		t.Fatalf("read page layout owner %s: %v", id, err)
	}
	if source != wantSource || customized != wantCustomized || ownerPack.String != wantOwnerPack || ownerPack.Valid != (wantOwnerPack != "") {
		t.Fatalf("owner %s = (%q, %v, %q/%v), want (%q, %v, %q)",
			id, source, customized, ownerPack.String, ownerPack.Valid, wantSource, wantCustomized, wantOwnerPack)
	}
}

func requireLayoutAssignmentOwner(t *testing.T, db *sql.DB, id, wantLayoutID, wantRouteSource, wantOwnerPack string) {
	t.Helper()
	var layoutID, routeSource string
	var ownerPack sql.NullString
	if err := db.QueryRow("SELECT LayoutId, RouteSource, OwnerPack FROM `_System_LayoutAssignment` WHERE Id = ?", id).
		Scan(&layoutID, &routeSource, &ownerPack); err != nil {
		t.Fatalf("read layout assignment %s: %v", id, err)
	}
	if layoutID != wantLayoutID || routeSource != wantRouteSource || ownerPack.String != wantOwnerPack || ownerPack.Valid != (wantOwnerPack != "") {
		t.Fatalf("assignment %s = (%q, %q, %q/%v), want (%q, %q, %q)",
			id, layoutID, routeSource, ownerPack.String, ownerPack.Valid, wantLayoutID, wantRouteSource, wantOwnerPack)
	}
}