package packs

import (
	"context"
	"crypto/sha1"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"reflect"
	"sort"
	"strings"

	"github.com/pingcap-inc/nexuscrm-controlplane/layoutownership"
	"github.com/nexuscrm/shared/pkg/models"
)

type sourceBackedLayoutAssignmentPayload struct {
	Assignments []sourceBackedLayoutAssignmentEntry `json:"assignments"`
}

type sourceBackedLayoutAssignmentEntry struct {
	ObjectAPIName string `json:"objectApiName"`
	RecordType    string `json:"recordType"`
}

type sourceBackedLayoutAssignmentRow struct {
	ComponentBusinessKey string `json:"componentBusinessKey"`
	AssignmentID         string `json:"assignmentId"`
	AssignmentIDPrefix   string `json:"assignmentIdPrefix"`
	ObjectAPIName        string `json:"objectApiName"`
	RecordTypeName       string `json:"recordTypeName"`
	RecordTypeID         string `json:"recordTypeId,omitempty"`
	LayoutBusinessKey    string `json:"layoutBusinessKey"`
	LayoutID             string `json:"layoutId"`
	RouteSource          string `json:"routeSource"`
	OwnerPack            string `json:"ownerPack"`
}

type sourceBackedLayoutAssignmentPackBatch struct {
	componentByRoute map[string]string
	declaredByRoute  map[string]sourceBackedLayoutAssignmentRow
}

func newSourceBackedLayoutAssignmentPackBatch() *sourceBackedLayoutAssignmentPackBatch {
	return &sourceBackedLayoutAssignmentPackBatch{
		componentByRoute: map[string]string{},
		declaredByRoute:  map[string]sourceBackedLayoutAssignmentRow{},
	}
}

func (b *sourceBackedLayoutAssignmentPackBatch) add(rows []sourceBackedLayoutAssignmentRow) error {
	if b == nil {
		return nil
	}
	for _, row := range rows {
		routeKey := recordTypePicklistValueRecordTypeKey(row.ObjectAPIName, row.RecordTypeName)
		if owner, ok := b.componentByRoute[routeKey]; ok {
			return fmt.Errorf("layout_assignment route %s/%s declared by multiple components (%s, %s)", row.ObjectAPIName, row.RecordTypeName, owner, row.ComponentBusinessKey)
		}
		b.componentByRoute[routeKey] = row.ComponentBusinessKey
		b.declaredByRoute[routeKey] = row
	}
	return nil
}

func (b *sourceBackedLayoutAssignmentPackBatch) allRows() []sourceBackedLayoutAssignmentRow {
	if b == nil {
		return nil
	}
	rows := make([]sourceBackedLayoutAssignmentRow, 0, len(b.declaredByRoute))
	for _, row := range b.declaredByRoute {
		rows = append(rows, row)
	}
	sortSourceBackedLayoutAssignmentRows(rows)
	return rows
}

func newSourceBackedLayoutAssignmentComponent(
	packName string,
	component ManifestComponent,
	payload []byte,
	recordTypeIndex map[string]sourceBackedRecordTypeTarget,
	packBatch *sourceBackedLayoutAssignmentPackBatch,
) (PackComponent, error) {
	layoutBusinessKey, layoutID, objectAPIName, err := sourceBackedLayoutAssignmentTarget(component)
	if err != nil {
		return nil, err
	}
	expected, err := sourceBackedLayoutAssignmentProjection(
		payload,
		componentPayloadLabel(component),
		strings.TrimSpace(component.BusinessKey),
		layoutBusinessKey,
		layoutID,
		packName,
	)
	if err != nil {
		return nil, err
	}
	expected, err = validateSourceBackedLayoutAssignmentComponent(component, expected, objectAPIName, recordTypeIndex)
	if err != nil {
		return nil, err
	}
	if err := packBatch.add(expected); err != nil {
		return nil, err
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: component,
		payload:           payload,
		expectedProjection: func() (any, error) {
			return expected, nil
		},
		liveProjection: func(ctx context.Context, deps VerifyDeps) (any, error) {
			return liveSourceBackedLayoutAssignmentProjection(ctx, deps, expected, packName)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifySourceBackedLayoutAssignmentComponent(ctx, deps, expected, packName)
		},
		// Real manifest components verify declared routes, but only the
		// synthetic sweeper executes the pack-wide batch apply after all
		// page_layout components in the UI-surfaces phase have run.
		apply: nil,
	}, nil
}

func sourceBackedLayoutAssignmentProjection(
	payload []byte,
	source string,
	componentBusinessKey string,
	layoutBusinessKey string,
	layoutID string,
	ownerPack string,
) ([]sourceBackedLayoutAssignmentRow, error) {
	parsed, err := parseSourceBackedLayoutAssignmentPayload(payload, source)
	if err != nil {
		return nil, err
	}
	rows := make([]sourceBackedLayoutAssignmentRow, 0, len(parsed.Assignments))
	for _, assignment := range parsed.Assignments {
		prefix := sourceBackedLayoutAssignmentComponentIDPrefix(ownerPack, componentBusinessKey, assignment.ObjectAPIName)
		rows = append(rows, sourceBackedLayoutAssignmentRow{
			ComponentBusinessKey: componentBusinessKey,
			AssignmentID:         generatedSourceBackedLayoutAssignmentID(ownerPack, componentBusinessKey, assignment.ObjectAPIName, assignment.RecordType),
			AssignmentIDPrefix:   prefix,
			ObjectAPIName:        assignment.ObjectAPIName,
			RecordTypeName:       assignment.RecordType,
			LayoutBusinessKey:    layoutBusinessKey,
			LayoutID:             layoutID,
			RouteSource:          models.LayoutAssignmentRouteSourceManagedSeed,
			OwnerPack:            ownerPack,
		})
	}
	sortSourceBackedLayoutAssignmentRows(rows)
	return rows, nil
}

func parseSourceBackedLayoutAssignmentPayload(payload []byte, source string) (sourceBackedLayoutAssignmentPayload, error) {
	var parsed sourceBackedLayoutAssignmentPayload
	if err := json.Unmarshal(payload, &parsed); err != nil {
		return sourceBackedLayoutAssignmentPayload{}, fmt.Errorf("parse %s: %w", source, err)
	}
	if len(parsed.Assignments) == 0 {
		return sourceBackedLayoutAssignmentPayload{}, fmt.Errorf("%s must declare at least one assignment", source)
	}
	seen := map[string]struct{}{}
	objectAPIName := ""
	for i := range parsed.Assignments {
		parsed.Assignments[i].ObjectAPIName = strings.TrimSpace(parsed.Assignments[i].ObjectAPIName)
		parsed.Assignments[i].RecordType = strings.TrimSpace(parsed.Assignments[i].RecordType)
		if parsed.Assignments[i].ObjectAPIName == "" || parsed.Assignments[i].RecordType == "" {
			return sourceBackedLayoutAssignmentPayload{}, fmt.Errorf("%s assignments[%d] requires objectApiName and recordType", source, i)
		}
		if objectAPIName == "" {
			objectAPIName = parsed.Assignments[i].ObjectAPIName
		} else if !strings.EqualFold(objectAPIName, parsed.Assignments[i].ObjectAPIName) {
			return sourceBackedLayoutAssignmentPayload{}, fmt.Errorf("%s must target exactly one objectApiName, got %s and %s", source, objectAPIName, parsed.Assignments[i].ObjectAPIName)
		}
		key := recordTypePicklistValueRecordTypeKey(parsed.Assignments[i].ObjectAPIName, parsed.Assignments[i].RecordType)
		if _, ok := seen[key]; ok {
			return sourceBackedLayoutAssignmentPayload{}, fmt.Errorf("%s declares duplicate recordType route %s/%s", source, parsed.Assignments[i].ObjectAPIName, parsed.Assignments[i].RecordType)
		}
		seen[key] = struct{}{}
	}
	return parsed, nil
}

func sourceBackedLayoutAssignmentTarget(component ManifestComponent) (layoutBusinessKey string, layoutID string, objectAPIName string, err error) {
	var layoutDeps []ComponentReference
	for _, dep := range component.DependsOn {
		if dep.Kind == ComponentKindPageLayout {
			layoutDeps = append(layoutDeps, dep)
		}
	}
	if len(layoutDeps) != 1 {
		return "", "", "", fmt.Errorf("layout_assignment %s requires exactly one dependsOn page_layout target", strings.TrimSpace(component.BusinessKey))
	}
	objectAPIName, layoutKey, ok := splitSourceBackedPageLayoutBusinessKey(layoutDeps[0].BusinessKey)
	if !ok {
		return "", "", "", fmt.Errorf("layout_assignment %s dependsOn invalid page_layout business key %q", strings.TrimSpace(component.BusinessKey), strings.TrimSpace(layoutDeps[0].BusinessKey))
	}
	isDefault := strings.EqualFold(layoutKey, "default")
	layoutName := ""
	if !isDefault {
		layoutName = layoutKey
	}
	return strings.TrimSpace(layoutDeps[0].BusinessKey), sourceBackedLayoutID(objectAPIName, layoutName, isDefault), objectAPIName, nil
}

func validateSourceBackedLayoutAssignmentComponent(
	component ManifestComponent,
	expected []sourceBackedLayoutAssignmentRow,
	targetObjectAPIName string,
	recordTypeIndex map[string]sourceBackedRecordTypeTarget,
) ([]sourceBackedLayoutAssignmentRow, error) {
	if len(expected) == 0 {
		return nil, fmt.Errorf("layout_assignment %s has no routes", strings.TrimSpace(component.BusinessKey))
	}
	for i, row := range expected {
		if !strings.EqualFold(strings.TrimSpace(row.ObjectAPIName), strings.TrimSpace(targetObjectAPIName)) {
			return nil, fmt.Errorf("layout_assignment %s targets page_layout object %s but payload declares %s", strings.TrimSpace(component.BusinessKey), targetObjectAPIName, row.ObjectAPIName)
		}
		target, ok := recordTypeIndex[recordTypePicklistValueRecordTypeKey(row.ObjectAPIName, row.RecordTypeName)]
		if !ok {
			return nil, fmt.Errorf("layout_assignment %s requires declaring record type %s/%s in the same pack", strings.TrimSpace(component.BusinessKey), row.ObjectAPIName, row.RecordTypeName)
		}
		if !hasComponentDependency(component.DependsOn, ComponentKindRecordType, target.businessKey) {
			return nil, fmt.Errorf("layout_assignment %s requires dependsOn on record_type %q for %s/%s", strings.TrimSpace(component.BusinessKey), target.businessKey, row.ObjectAPIName, row.RecordTypeName)
		}
		if strings.TrimSpace(target.expectedID) == "" {
			return nil, fmt.Errorf("layout_assignment %s requires authoritative record type id for %s/%s", strings.TrimSpace(component.BusinessKey), row.ObjectAPIName, row.RecordTypeName)
		}
		row.RecordTypeID = strings.TrimSpace(target.expectedID)
		expected[i] = row
	}
	return expected, nil
}

func applySourceBackedLayoutAssignmentPackBatch(ctx context.Context, db *sql.DB, batch *sourceBackedLayoutAssignmentPackBatch, ownerPack string) error {
	if batch == nil {
		return nil
	}
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint:errcheck

	declaredByRecordTypeID := map[string]sourceBackedLayoutAssignmentRow{}
	resolvedRows := make([]sourceBackedLayoutAssignmentRow, 0, len(batch.allRows()))
	for _, row := range batch.allRows() {
		recordTypeID, err := resolveLiveRecordTypeIDForLayoutAssignment(ctx, tx, ownerPack, row.ObjectAPIName, row.RecordTypeName, row.RecordTypeID)
		if err != nil {
			return err
		}
		layoutID, err := resolveLiveLayoutIDForLayoutAssignment(ctx, tx, ownerPack, row.LayoutBusinessKey, row.LayoutID)
		if err != nil {
			return err
		}
		row.RecordTypeID = recordTypeID
		row.LayoutID = layoutID
		if existing, ok := declaredByRecordTypeID[recordTypeID]; ok {
			return fmt.Errorf("layout_assignment routes %s/%s and %s/%s both resolve to live record type id %s", existing.ObjectAPIName, existing.RecordTypeName, row.ObjectAPIName, row.RecordTypeName, recordTypeID)
		}
		declaredByRecordTypeID[recordTypeID] = row
		resolvedRows = append(resolvedRows, row)
	}
	liveRows, err := queryManagedRecordTypeLayoutAssignments(ctx, tx, ownerPack)
	if err != nil {
		return err
	}
	for _, live := range liveRows {
		declared, ok := declaredByRecordTypeID[strings.TrimSpace(live.RecordTypeID)]
		if ok && strings.EqualFold(strings.TrimSpace(live.AssignmentID), strings.TrimSpace(declared.AssignmentID)) {
			continue
		}
		if _, err := tx.ExecContext(ctx, `
DELETE FROM _System_LayoutAssignment
WHERE Id = ?`, live.AssignmentID); err != nil {
			return fmt.Errorf("delete stale layout_assignment route %s/%s id %s: %w", live.ObjectAPIName, live.RecordTypeName, live.AssignmentID, err)
		}
	}
	for _, row := range resolvedRows {
		recordTypeID := strings.TrimSpace(row.RecordTypeID)
		if err := upsertManagedRecordTypeLayoutAssignmentTx(ctx, tx, row.AssignmentID, row.ObjectAPIName, row.LayoutID, recordTypeID, row.RouteSource, ownerPack); err != nil {
			return err
		}
	}
	return tx.Commit()
}

type queryableRecordTypeResolver interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type liveSourceBackedPageLayoutRow struct {
	ID            string
	ObjectAPIName string
	LayoutName    string
	Source        string
	IsCustomized  bool
	OwnerPack     string
}

func resolveLiveRecordTypeIDForLayoutAssignment(ctx context.Context, tx queryableRecordTypeResolver, ownerPack, objectAPIName, recordTypeName, expectedID string) (string, error) {
	expectedID = strings.TrimSpace(expectedID)
	if expectedID == "" {
		return "", fmt.Errorf("layout_assignment route %s/%s is missing authoritative record type id", objectAPIName, recordTypeName)
	}
	var recordTypeID string
	err := tx.QueryRowContext(ctx, `
SELECT rt.Id
FROM _System_RecordType rt
JOIN _System_Object o ON o.Id = rt.ObjectId
WHERE rt.Id = ?
  AND LOWER(o.ApiName) = LOWER(?)
  AND rt.Name = ?
  AND COALESCE(rt.OwnerPack, '') = ?
  AND (rt.IsDeleted = 0 OR rt.IsDeleted IS NULL)
LIMIT 1`, expectedID, objectAPIName, recordTypeName, ownerPack).Scan(&recordTypeID)
	if err == nil {
		return recordTypeID, nil
	}
	if err != sql.ErrNoRows {
		return "", fmt.Errorf("query layout_assignment route %s/%s by record type id %s: %w", objectAPIName, recordTypeName, expectedID, err)
	}

	rows, err := tx.QueryContext(ctx, `
SELECT rt.Id
FROM _System_RecordType rt
JOIN _System_Object o ON o.Id = rt.ObjectId
WHERE LOWER(o.ApiName) = LOWER(?)
  AND rt.Name = ?
  AND COALESCE(rt.OwnerPack, '') = ?
  AND (rt.IsDeleted = 0 OR rt.IsDeleted IS NULL)
ORDER BY rt.Id`, objectAPIName, recordTypeName, ownerPack)
	if err != nil {
		return "", fmt.Errorf("query layout_assignment route %s/%s by current record type authority: %w", objectAPIName, recordTypeName, err)
	}
	defer rows.Close()

	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return "", fmt.Errorf("scan layout_assignment route %s/%s by current record type authority: %w", objectAPIName, recordTypeName, err)
		}
		ids = append(ids, strings.TrimSpace(id))
	}
	if err := rows.Err(); err != nil {
		return "", fmt.Errorf("iterate layout_assignment route %s/%s by current record type authority: %w", objectAPIName, recordTypeName, err)
	}
	if len(ids) == 0 {
		return "", fmt.Errorf("layout_assignment route %s/%s is missing live record type row %s and has no current-authority name match", objectAPIName, recordTypeName, expectedID)
	}
	if len(ids) > 1 {
		return "", fmt.Errorf("layout_assignment route %s/%s expected record type id %s but current-authority name resolution matches multiple live rows (%s)", objectAPIName, recordTypeName, expectedID, strings.Join(ids, ", "))
	}
	return ids[0], nil
}

func resolveLiveLayoutIDForLayoutAssignment(ctx context.Context, tx queryableRecordTypeResolver, ownerPack, layoutBusinessKey, expectedID string) (string, error) {
	objectAPIName, layoutKey, ok := splitSourceBackedPageLayoutBusinessKey(layoutBusinessKey)
	if !ok {
		return "", fmt.Errorf("layout_assignment dependsOn invalid page_layout business key %q", strings.TrimSpace(layoutBusinessKey))
	}
	layoutName := ""
	if !strings.EqualFold(layoutKey, "default") {
		layoutName = layoutKey
	}

	row, err := loadLiveSourceBackedPageLayoutRowByID(ctx, tx, expectedID)
	if err != nil {
		return "", fmt.Errorf("query layout_assignment target layout %s: %w", expectedID, err)
	}
	if row != nil {
		if !strings.EqualFold(strings.TrimSpace(row.ObjectAPIName), strings.TrimSpace(objectAPIName)) {
			return "", fmt.Errorf("layout_assignment target layout %s belongs to %s, not %s", expectedID, row.ObjectAPIName, objectAPIName)
		}
		if layoutownership.IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
			return row.ID, nil
		}
		liveOwnerPack := strings.TrimSpace(row.OwnerPack)
		if liveOwnerPack == "" || !strings.EqualFold(liveOwnerPack, strings.TrimSpace(ownerPack)) {
			return "", fmt.Errorf("layout_assignment target layout %s OwnerPack = %q, want %q", expectedID, row.OwnerPack, ownerPack)
		}
		return row.ID, nil
	}

	row, err = loadLiveSourceBackedPageLayoutRowByBusinessKey(ctx, tx, objectAPIName, layoutName)
	if err != nil {
		return "", fmt.Errorf("query layout_assignment target layout %s by current page_layout authority: %w", expectedID, err)
	}
	if row == nil {
		return "", fmt.Errorf("layout_assignment target layout %s is missing", expectedID)
	}
	if layoutownership.IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
		return "", fmt.Errorf("layout_assignment target layout %s has admin-owned same-name row %s; refusing source-backed fallback", expectedID, row.ID)
	}
	liveOwnerPack := strings.TrimSpace(row.OwnerPack)
	if liveOwnerPack == "" || !strings.EqualFold(liveOwnerPack, strings.TrimSpace(ownerPack)) {
		return "", fmt.Errorf("layout_assignment target layout %s fallback row %s OwnerPack = %q, want %q", expectedID, row.ID, row.OwnerPack, ownerPack)
	}
	return row.ID, nil
}

func loadLiveSourceBackedPageLayoutRowByID(ctx context.Context, tx queryableRecordTypeResolver, layoutID string) (*liveSourceBackedPageLayoutRow, error) {
	var row liveSourceBackedPageLayoutRow
	err := tx.QueryRowContext(ctx, `
SELECT Id, ObjectApiName, COALESCE(LayoutName, ''), COALESCE(Source, ''), COALESCE(IsCustomized, 0), COALESCE(OwnerPack, '')
FROM _System_PageLayout
WHERE Id = ?
  AND (IsDeleted = 0 OR IsDeleted IS NULL)
LIMIT 1`, layoutID).Scan(&row.ID, &row.ObjectAPIName, &row.LayoutName, &row.Source, &row.IsCustomized, &row.OwnerPack)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &row, nil
}

func loadLiveSourceBackedPageLayoutRowByBusinessKey(ctx context.Context, tx queryableRecordTypeResolver, objectAPIName, layoutName string) (*liveSourceBackedPageLayoutRow, error) {
	rows, err := tx.QueryContext(ctx, `
SELECT Id, ObjectApiName, COALESCE(LayoutName, ''), COALESCE(Source, ''), COALESCE(IsCustomized, 0), COALESCE(OwnerPack, '')
FROM _System_PageLayout
WHERE LOWER(ObjectApiName) = LOWER(?)
  AND LOWER(COALESCE(LayoutName, '')) = LOWER(?)
  AND (IsDeleted = 0 OR IsDeleted IS NULL)
ORDER BY CreatedDate ASC, Id ASC`, objectAPIName, layoutName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sourceBackedMatches []liveSourceBackedPageLayoutRow
	var adminOwnedMatches []liveSourceBackedPageLayoutRow
	var malformedMatches []liveSourceBackedPageLayoutRow
	for rows.Next() {
		var row liveSourceBackedPageLayoutRow
		if err := rows.Scan(&row.ID, &row.ObjectAPIName, &row.LayoutName, &row.Source, &row.IsCustomized, &row.OwnerPack); err != nil {
			return nil, err
		}
		if layoutownership.IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
			adminOwnedMatches = append(adminOwnedMatches, row)
			continue
		}
		if strings.TrimSpace(row.OwnerPack) == "" {
			malformedMatches = append(malformedMatches, row)
			continue
		}
		sourceBackedMatches = append(sourceBackedMatches, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if len(malformedMatches) > 0 {
		ids := make([]string, 0, len(malformedMatches))
		for _, row := range malformedMatches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf("page_layout business key %s/%s has malformed managed row(s) with empty OwnerPack (%s)", objectAPIName, layoutName, strings.Join(ids, ", "))
	}
	if len(adminOwnedMatches) > 0 {
		ids := make([]string, 0, len(adminOwnedMatches))
		for _, row := range adminOwnedMatches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf("page_layout business key %s/%s has admin-owned same-name row(s) (%s)", objectAPIName, layoutName, strings.Join(ids, ", "))
	}
	if len(sourceBackedMatches) == 0 {
		return nil, nil
	}
	if len(sourceBackedMatches) > 1 {
		ids := make([]string, 0, len(sourceBackedMatches))
		for _, row := range sourceBackedMatches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf("page_layout business key %s/%s has %d live rows (%s)", objectAPIName, layoutName, len(sourceBackedMatches), strings.Join(ids, ", "))
	}
	return &sourceBackedMatches[0], nil
}

func upsertManagedRecordTypeLayoutAssignmentTx(ctx context.Context, tx *sql.Tx, assignmentID, objectAPIName, layoutID, recordTypeID, routeSource, ownerPack string) error {
	existing, err := loadEffectiveManagedRecordTypeLayoutAssignmentTx(ctx, tx, objectAPIName, recordTypeID)
	if err != nil {
		return err
	}
	if existing != nil &&
		strings.EqualFold(strings.TrimSpace(existing.RouteSource), models.LayoutAssignmentRouteSourceAdminAuthored) &&
		strings.TrimSpace(existing.OwnerPack) == "" {
		return fmt.Errorf(
			"layout_assignment route %s/%s is shadowed by ownerless admin-authored assignment %s; locked source-backed layout_assignment fails closed on same-dimension admin override",
			objectAPIName,
			recordTypeID,
			existing.AssignmentID,
		)
	}
	if existing != nil && !strings.EqualFold(strings.TrimSpace(existing.AssignmentID), strings.TrimSpace(assignmentID)) {
		return fmt.Errorf(
			"layout_assignment route %s/%s already has live assignment %s with routeSource %q ownerPack %q; retained source-backed host path fails closed on existing same-dimension routes",
			objectAPIName,
			recordTypeID,
			existing.AssignmentID,
			existing.RouteSource,
			existing.OwnerPack,
		)
	}
	result, err := tx.ExecContext(ctx, `
UPDATE _System_LayoutAssignment
SET LayoutId = ?, Specificity = 1, RouteSource = ?, OwnerPack = ?, LastModifiedDate = CURRENT_TIMESTAMP
WHERE Id = ?`,
		layoutID, routeSource, ownerPack, assignmentID,
	)
	if err != nil {
		return fmt.Errorf("update layout_assignment route %s/%s: %w", objectAPIName, recordTypeID, err)
	}
	rowsAffected, _ := result.RowsAffected()
	if rowsAffected > 0 {
		return nil
	}
	_, err = tx.ExecContext(ctx, `
INSERT INTO _System_LayoutAssignment (
	Id, ObjectApiName, LayoutId, RoleId, ProfileId, RecordTypeId,
	Specificity, CreatedDate, LastModifiedDate, RouteSource, OwnerPack
) VALUES (?, ?, ?, NULL, NULL, ?, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)`,
		assignmentID, objectAPIName, layoutID, recordTypeID, routeSource, ownerPack,
	)
	if err != nil {
		return fmt.Errorf("insert layout_assignment route %s/%s: %w", objectAPIName, recordTypeID, err)
	}
	return nil
}

type sourceBackedEffectiveLayoutAssignment struct {
	AssignmentID  string
	ObjectAPIName string
	LayoutID      string
	RouteSource   string
	OwnerPack     string
}

func loadEffectiveManagedRecordTypeLayoutAssignmentTx(ctx context.Context, tx queryableRecordTypeResolver, objectAPIName, recordTypeID string) (*sourceBackedEffectiveLayoutAssignment, error) {
	rows, err := tx.QueryContext(ctx, `
SELECT Id, ObjectApiName, LayoutId, COALESCE(RouteSource, ''), COALESCE(OwnerPack, '')
FROM _System_LayoutAssignment
WHERE LOWER(ObjectApiName) = LOWER(?)
  AND RecordTypeId = ?
  AND RoleId IS NULL
  AND ProfileId IS NULL
ORDER BY CreatedDate ASC, Id ASC`, objectAPIName, recordTypeID)
	if err != nil {
		return nil, fmt.Errorf("query effective layout_assignment route %s/%s: %w", objectAPIName, recordTypeID, err)
	}
	defer rows.Close()

	var matches []sourceBackedEffectiveLayoutAssignment
	for rows.Next() {
		var got sourceBackedEffectiveLayoutAssignment
		if err := rows.Scan(&got.AssignmentID, &got.ObjectAPIName, &got.LayoutID, &got.RouteSource, &got.OwnerPack); err != nil {
			return nil, fmt.Errorf("scan effective layout_assignment route %s/%s: %w", objectAPIName, recordTypeID, err)
		}
		matches = append(matches, got)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate effective layout_assignment route %s/%s: %w", objectAPIName, recordTypeID, err)
	}
	if len(matches) == 0 {
		return nil, nil
	}
	return &matches[0], nil
}

func liveSourceBackedLayoutAssignmentProjection(
	ctx context.Context,
	deps VerifyDeps,
	expected []sourceBackedLayoutAssignmentRow,
	ownerPack string,
) (any, error) {
	if len(expected) == 0 {
		return []sourceBackedLayoutAssignmentRow{}, nil
	}
	live := make([]sourceBackedLayoutAssignmentRow, 0, len(expected))
	for _, want := range expected {
		recordTypeID, err := resolveLiveRecordTypeIDForLayoutAssignment(ctx, deps.DB, ownerPack, want.ObjectAPIName, want.RecordTypeName, want.RecordTypeID)
		if err != nil {
			return nil, err
		}
		layoutID, err := resolveLiveLayoutIDForLayoutAssignment(ctx, deps.DB, ownerPack, want.LayoutBusinessKey, want.LayoutID)
		if err != nil {
			return nil, err
		}
		resolvedWant := want
		resolvedWant.RecordTypeID = recordTypeID
		resolvedWant.LayoutID = layoutID
		rows, err := deps.DB.QueryContext(ctx, `
SELECT j.Id, j.ObjectApiName, rt.Name, j.LayoutId, COALESCE(j.RouteSource, ''), COALESCE(j.OwnerPack, '')
FROM _System_LayoutAssignment j
JOIN _System_RecordType rt ON rt.Id = j.RecordTypeId
WHERE j.RecordTypeId = ?
  AND LOWER(j.ObjectApiName) = LOWER(?)
  AND RoleId IS NULL
  AND ProfileId IS NULL
  AND j.RecordTypeId IS NOT NULL
ORDER BY j.CreatedDate ASC, j.Id ASC`, recordTypeID, want.ObjectAPIName)
		if err != nil {
			return nil, fmt.Errorf("query layout_assignment route %s/%s: %w", want.ObjectAPIName, want.RecordTypeName, err)
		}
		var matches []sourceBackedLayoutAssignmentRow
		for rows.Next() {
			var got sourceBackedLayoutAssignmentRow
			if err := rows.Scan(&got.AssignmentID, &got.ObjectAPIName, &got.RecordTypeName, &got.LayoutID, &got.RouteSource, &got.OwnerPack); err != nil {
				rows.Close()
				return nil, fmt.Errorf("scan layout_assignment route %s/%s: %w", want.ObjectAPIName, want.RecordTypeName, err)
			}
			got.ComponentBusinessKey = resolvedWant.ComponentBusinessKey
			got.AssignmentIDPrefix = resolvedWant.AssignmentIDPrefix
			got.RecordTypeID = recordTypeID
			got.LayoutBusinessKey = resolvedWant.LayoutBusinessKey
			matches = append(matches, got)
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return nil, fmt.Errorf("iterate layout_assignment route %s/%s: %w", want.ObjectAPIName, want.RecordTypeName, err)
		}
		rows.Close()
		if len(matches) == 0 {
			return nil, fmt.Errorf("layout_assignment route %s/%s is missing", want.ObjectAPIName, want.RecordTypeName)
		}
		if len(matches) > 1 {
			return nil, fmt.Errorf("layout_assignment route %s/%s has duplicate live assignment rows", want.ObjectAPIName, want.RecordTypeName)
		}
		got := matches[0]
		if strings.EqualFold(strings.TrimSpace(got.RouteSource), models.LayoutAssignmentRouteSourceAdminAuthored) &&
			strings.TrimSpace(got.OwnerPack) == "" {
			return nil, fmt.Errorf(
				"layout_assignment route %s/%s is shadowed by ownerless admin-authored assignment %s; locked source-backed layout_assignment fails closed on same-dimension admin override",
				want.ObjectAPIName,
				want.RecordTypeName,
				got.AssignmentID,
			)
		}
		if got.AssignmentID != resolvedWant.AssignmentID {
			return nil, fmt.Errorf("layout_assignment route %s/%s effective assignment id = %q, want %q", want.ObjectAPIName, want.RecordTypeName, got.AssignmentID, resolvedWant.AssignmentID)
		}
		if got.LayoutID != resolvedWant.LayoutID {
			return nil, fmt.Errorf("layout_assignment route %s/%s layoutId = %q, want %q", want.ObjectAPIName, want.RecordTypeName, got.LayoutID, resolvedWant.LayoutID)
		}
		if !strings.EqualFold(strings.TrimSpace(got.RouteSource), strings.TrimSpace(resolvedWant.RouteSource)) {
			return nil, fmt.Errorf("layout_assignment route %s/%s routeSource = %q, want %q", want.ObjectAPIName, want.RecordTypeName, got.RouteSource, resolvedWant.RouteSource)
		}
		if !strings.EqualFold(strings.TrimSpace(got.OwnerPack), strings.TrimSpace(ownerPack)) {
			return nil, fmt.Errorf("layout_assignment route %s/%s OwnerPack = %q, want %q", want.ObjectAPIName, want.RecordTypeName, got.OwnerPack, ownerPack)
		}
		live = append(live, got)
	}
	if err := verifyNoStaleSourceBackedLayoutAssignmentRows(ctx, deps.DB, expected, ownerPack); err != nil {
		return nil, err
	}
	sortSourceBackedLayoutAssignmentRows(live)
	return live, nil
}

func verifySourceBackedLayoutAssignmentComponent(
	ctx context.Context,
	deps VerifyDeps,
	expected []sourceBackedLayoutAssignmentRow,
	ownerPack string,
) error {
	resolvedExpected, err := resolveExpectedSourceBackedLayoutAssignmentRows(ctx, deps, expected, ownerPack)
	if err != nil {
		return err
	}
	live, err := liveSourceBackedLayoutAssignmentProjection(ctx, deps, expected, ownerPack)
	if err != nil {
		return err
	}
	liveRows, ok := live.([]sourceBackedLayoutAssignmentRow)
	if !ok {
		return fmt.Errorf("layout_assignment verify produced unexpected projection type %T", live)
	}
	if reflect.DeepEqual(resolvedExpected, liveRows) {
		return nil
	}
	return fmt.Errorf("layout_assignment projection mismatch: expected %#v live %#v", resolvedExpected, liveRows)
}

func resolveExpectedSourceBackedLayoutAssignmentRows(
	ctx context.Context,
	deps VerifyDeps,
	expected []sourceBackedLayoutAssignmentRow,
	ownerPack string,
) ([]sourceBackedLayoutAssignmentRow, error) {
	resolved := make([]sourceBackedLayoutAssignmentRow, 0, len(expected))
	for _, want := range expected {
		recordTypeID, err := resolveLiveRecordTypeIDForLayoutAssignment(ctx, deps.DB, ownerPack, want.ObjectAPIName, want.RecordTypeName, want.RecordTypeID)
		if err != nil {
			return nil, err
		}
		layoutID, err := resolveLiveLayoutIDForLayoutAssignment(ctx, deps.DB, ownerPack, want.LayoutBusinessKey, want.LayoutID)
		if err != nil {
			return nil, err
		}
		normalized := want
		normalized.RecordTypeID = recordTypeID
		normalized.LayoutID = layoutID
		resolved = append(resolved, normalized)
	}
	sortSourceBackedLayoutAssignmentRows(resolved)
	return resolved, nil
}

func newSourceBackedLayoutAssignmentPackSweeperComponent(packName string, batch *sourceBackedLayoutAssignmentPackBatch) PackComponent {
	return &sourceBackedManagedComponent{
		packName: packName,
		manifestComponent: ManifestComponent{
			Kind:          ComponentKindLayoutAssignment,
			BusinessKey:   "__layout_assignment_batch__",
			Manageability: ManageabilityLocked,
		},
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applySourceBackedLayoutAssignmentPackBatch(ctx, deps.DB, batch, packName)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifySourceBackedLayoutAssignmentPackBatch(ctx, deps.DB, batch, packName)
		},
		applyGroupKey: string(ComponentKindLayoutAssignment),
	}
}

func verifySourceBackedLayoutAssignmentPackBatch(ctx context.Context, db *sql.DB, batch *sourceBackedLayoutAssignmentPackBatch, ownerPack string) error {
	if batch == nil {
		return nil
	}
	var resolved []sourceBackedLayoutAssignmentRow
	if rows := batch.allRows(); len(rows) > 0 {
		live, err := liveSourceBackedLayoutAssignmentProjection(ctx, VerifyDeps{DB: db}, rows, ownerPack)
		if err != nil {
			return err
		}
		var ok bool
		resolved, ok = live.([]sourceBackedLayoutAssignmentRow)
		if !ok {
			return fmt.Errorf("layout_assignment verify produced unexpected projection type %T", live)
		}
	}
	liveRows, err := queryManagedRecordTypeLayoutAssignments(ctx, db, ownerPack)
	if err != nil {
		return err
	}
	for _, live := range liveRows {
		var declared *sourceBackedLayoutAssignmentRow
		for _, row := range resolved {
			if strings.EqualFold(strings.TrimSpace(row.RecordTypeID), strings.TrimSpace(live.RecordTypeID)) {
				copied := row
				declared = &copied
				break
			}
		}
		ok := declared != nil
		if !ok {
			return fmt.Errorf("layout_assignment route %s/%s owned by %s is active but not declared by the current manifest", live.ObjectAPIName, live.RecordTypeName, ownerPack)
		}
		if !strings.EqualFold(strings.TrimSpace(live.AssignmentID), strings.TrimSpace(declared.AssignmentID)) {
			return fmt.Errorf("layout_assignment route %s/%s owned by %s has stale assignment id %s; current manifest expects %s", live.ObjectAPIName, live.RecordTypeName, ownerPack, live.AssignmentID, declared.AssignmentID)
		}
	}
	return nil
}

type sourceBackedLayoutAssignmentLiveRoute struct {
	AssignmentID   string
	ObjectAPIName  string
	RecordTypeID   string
	RecordTypeName string
}

func queryManagedRecordTypeLayoutAssignments(ctx context.Context, q queryableRecordTypeResolver, ownerPack string) ([]sourceBackedLayoutAssignmentLiveRoute, error) {
	rows, err := q.QueryContext(ctx, `
SELECT j.Id, j.ObjectApiName, rt.Id, rt.Name
FROM _System_LayoutAssignment j
JOIN _System_RecordType rt ON rt.Id = j.RecordTypeId
WHERE j.OwnerPack = ?
  AND j.RoleId IS NULL
  AND j.ProfileId IS NULL
  AND j.RecordTypeId IS NOT NULL
  AND LOWER(COALESCE(j.RouteSource, '')) = LOWER(?)
ORDER BY j.Id`, ownerPack, models.LayoutAssignmentRouteSourceManagedSeed)
	if err != nil {
		return nil, fmt.Errorf("query %s-owned layout_assignment routes: %w", ownerPack, err)
	}
	defer rows.Close()

	var live []sourceBackedLayoutAssignmentLiveRoute
	for rows.Next() {
		var row sourceBackedLayoutAssignmentLiveRoute
		if err := rows.Scan(&row.AssignmentID, &row.ObjectAPIName, &row.RecordTypeID, &row.RecordTypeName); err != nil {
			return nil, fmt.Errorf("scan %s-owned layout_assignment route: %w", ownerPack, err)
		}
		live = append(live, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate %s-owned layout_assignment routes: %w", ownerPack, err)
	}
	sort.Slice(live, func(i, j int) bool {
		if live[i].ObjectAPIName != live[j].ObjectAPIName {
			return live[i].ObjectAPIName < live[j].ObjectAPIName
		}
		if live[i].RecordTypeID != live[j].RecordTypeID {
			return live[i].RecordTypeID < live[j].RecordTypeID
		}
		return live[i].AssignmentID < live[j].AssignmentID
	})
	return live, nil
}

func verifyNoStaleSourceBackedLayoutAssignmentRows(ctx context.Context, db *sql.DB, expected []sourceBackedLayoutAssignmentRow, ownerPack string) error {
	prefix := strings.TrimSpace(expected[0].AssignmentIDPrefix)
	objectAPIName := strings.TrimSpace(expected[0].ObjectAPIName)
	if prefix == "" || objectAPIName == "" {
		return nil
	}
	rows, err := db.QueryContext(ctx, `
SELECT Id
FROM _System_LayoutAssignment
WHERE OwnerPack = ?
  AND LOWER(ObjectApiName) = LOWER(?)
  AND LOWER(COALESCE(RouteSource, '')) = LOWER(?)
  AND RoleId IS NULL
  AND ProfileId IS NULL
  AND RecordTypeId IS NOT NULL
  AND Id LIKE ?
ORDER BY Id`, ownerPack, objectAPIName, models.LayoutAssignmentRouteSourceManagedSeed, prefix+`_%`)
	if err != nil {
		return fmt.Errorf("query layout_assignment stale-check rows %s/%s: %w", ownerPack, objectAPIName, err)
	}
	defer rows.Close()

	expectedIDs := make(map[string]struct{}, len(expected))
	for _, row := range expected {
		expectedIDs[strings.TrimSpace(row.AssignmentID)] = struct{}{}
	}
	for rows.Next() {
		var assignmentID string
		if err := rows.Scan(&assignmentID); err != nil {
			return fmt.Errorf("scan layout_assignment stale-check row %s/%s: %w", ownerPack, objectAPIName, err)
		}
		if _, ok := expectedIDs[strings.TrimSpace(assignmentID)]; ok {
			continue
		}
		return fmt.Errorf("layout_assignment component for %s/%s still has undeclared managed row %s", ownerPack, objectAPIName, assignmentID)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate layout_assignment stale-check rows %s/%s: %w", ownerPack, objectAPIName, err)
	}
	return nil
}

func sourceBackedLayoutAssignmentComponentIDPrefix(ownerPack, componentBusinessKey, objectAPIName string) string {
	sum := sha1.Sum([]byte(strings.ToLower(strings.TrimSpace(ownerPack)) + "\x00" + strings.TrimSpace(componentBusinessKey) + "\x00" + strings.TrimSpace(objectAPIName)))
	return "layout_assignment_" + hex.EncodeToString(sum[:8])
}

func generatedSourceBackedLayoutAssignmentID(ownerPack, componentBusinessKey, objectAPIName, recordTypeName string) string {
	prefix := sourceBackedLayoutAssignmentComponentIDPrefix(ownerPack, componentBusinessKey, objectAPIName)
	sum := sha1.Sum([]byte(strings.TrimSpace(recordTypeName)))
	return prefix + "_" + hex.EncodeToString(sum[:6])
}

func sortSourceBackedLayoutAssignmentRows(rows []sourceBackedLayoutAssignmentRow) {
	sort.Slice(rows, func(i, j int) bool {
		if rows[i].ComponentBusinessKey != rows[j].ComponentBusinessKey {
			return rows[i].ComponentBusinessKey < rows[j].ComponentBusinessKey
		}
		if rows[i].ObjectAPIName != rows[j].ObjectAPIName {
			return rows[i].ObjectAPIName < rows[j].ObjectAPIName
		}
		return rows[i].RecordTypeName < rows[j].RecordTypeName
	})
}