package layoutownership

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"

	"github.com/nexuscrm/persistence/layoutrouting"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// nullableOwnerPack converts a pack name to a nullable SQL value.
// An empty string (admin/platform path) maps to nil (NULL); a non-empty
// string (source-backed pack path) maps to its value. This mirrors the
// contract from PR-A (#977): COALESCE(OwnerPack,”)=” matches admin rows.
func nullableOwnerPack(packName string) interface{} {
	if strings.TrimSpace(packName) == "" {
		return nil
	}
	return packName
}

// PageLayoutRow is the persisted ownership/config state for one live
// _System_PageLayout row.
type PageLayoutRow struct {
	ID           string
	ConfigJSON   string
	Source       string
	IsCustomized bool
	IsDefault    bool
	IsDeleted    bool
	OwnerPack    string
}

type WildcardLayoutTarget = layoutrouting.WildcardTarget

func isMalformedManagedPageLayoutOwnership(row PageLayoutRow) bool {
	return !IsAdminOwnedPageLayout(row.IsCustomized, row.Source) && strings.TrimSpace(row.OwnerPack) == ""
}

// IsAdminOwnedPageLayout is the single ownership predicate for page-layout
// writers. Admin saves, clones, and custom rows are all L2 authority and must
// be preserved by L1 pack/overlay reconciliation.
func IsAdminOwnedPageLayout(isCustomized bool, source string) bool {
	return isCustomized ||
		strings.EqualFold(source, "admin_created") ||
		strings.EqualFold(source, "cloned") ||
		strings.EqualFold(source, "custom")
}

// IsAdminOwnedLayoutAssignment is the single ownership predicate for layout
// assignment rows authored via admin UI surfaces. RouteSource carries intent;
// CreatedById remains audit only.
func IsAdminOwnedLayoutAssignment(routeSource *string) bool {
	return routeSource != nil &&
		strings.EqualFold(strings.TrimSpace(*routeSource), models.LayoutAssignmentRouteSourceAdminAuthored)
}

// LoadPageLayoutRow returns one live _System_PageLayout row by Id.
func LoadPageLayoutRow(ctx context.Context, db *sql.DB, layoutID string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.ColID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)

	var row PageLayoutRow
	if err := db.QueryRowContext(ctx, query, layoutID).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
		&row.OwnerPack,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

func loadPageLayoutRowByBusinessKeyExcludingIDs(ctx context.Context, db *sql.DB, objectAPIName, layoutName string, excludedIDs map[string]struct{}) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` WHERE LOWER(`%s`) = LOWER(?) AND LOWER(`%s`) = LOWER(?) AND (`%s` = 0 OR `%s` IS NULL) "+
			"ORDER BY `%s` ASC, `%s` ASC",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
		constants.FieldCreatedDate,
		constants.ColID,
	)

	rows, err := db.QueryContext(ctx, query, objectAPIName, layoutName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sourceBackedMatches []PageLayoutRow
	var adminOwnedMatches []PageLayoutRow
	var malformedManagedMatches []PageLayoutRow
	for rows.Next() {
		var row PageLayoutRow
		if err := rows.Scan(
			&row.ID,
			&row.ConfigJSON,
			&row.Source,
			&row.IsCustomized,
			&row.IsDefault,
			&row.OwnerPack,
		); err != nil {
			return nil, err
		}
		if _, excluded := excludedIDs[strings.TrimSpace(row.ID)]; excluded {
			continue
		}
		if IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
			adminOwnedMatches = append(adminOwnedMatches, row)
			continue
		}
		if isMalformedManagedPageLayoutOwnership(row) {
			malformedManagedMatches = append(malformedManagedMatches, row)
			continue
		}
		sourceBackedMatches = append(sourceBackedMatches, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if len(malformedManagedMatches) > 0 {
		ids := make([]string, 0, len(malformedManagedMatches))
		for _, row := range malformedManagedMatches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf(
			"page_layout business key %s/%s has malformed managed row(s) with empty OwnerPack (%s); refusing source-backed fallback",
			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); refusing source-backed fallback",
			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); refusing ambiguous same-name fallback",
			objectAPIName, layoutName, len(sourceBackedMatches), strings.Join(ids, ", "),
		)
	}
	return &sourceBackedMatches[0], nil
}

// LoadPageLayoutRowByBusinessKey returns one source-backed live page-layout row
// by the business key `(ObjectApiName, LayoutName)`. Admin-owned same-name rows
// are an ownership split and must fail closed instead of being reused as
// source-backed fallback targets.
func LoadPageLayoutRowByBusinessKey(ctx context.Context, db *sql.DB, objectAPIName, layoutName string) (*PageLayoutRow, error) {
	return loadPageLayoutRowByBusinessKeyExcludingIDs(ctx, db, objectAPIName, layoutName, nil)
}

// LoadWildcardLayoutTarget returns the oldest wildcard layout assignment that
// still points at a live target layout for an object.
func LoadWildcardLayoutTarget(ctx context.Context, db *sql.DB, objectAPIName string) (*WildcardLayoutTarget, error) {
	return layoutrouting.LoadLiveWildcardTarget(ctx, db, objectAPIName)
}

func sourceBackedDefaultPageLayoutID(objectAPIName string) string {
	return "curated_" + strings.TrimSpace(objectAPIName) + "_default"
}

// ValidateRequestedSourceBackedPageLayoutOwnership is the single authoritative
// preflight read path for source-backed page_layout ownership checks.
func ValidateRequestedSourceBackedPageLayoutOwnership(ctx context.Context, db *sql.DB, objectAPIName, layoutName string, isDefault bool, packName string) error {
	objectAPIName = strings.TrimSpace(objectAPIName)
	layoutName = strings.TrimSpace(layoutName)
	packName = strings.TrimSpace(packName)
	if objectAPIName == "" {
		return fmt.Errorf("page_layout ownership preflight requires object API name")
	}
	if packName == "" {
		return fmt.Errorf("page_layout ownership preflight requires non-empty owner pack")
	}
	if isDefault {
		layoutID := sourceBackedDefaultPageLayoutID(objectAPIName)
		row, err := LoadPageLayoutRow(ctx, db, layoutID)
		if err != nil {
			return err
		}
		excludedIDs := map[string]struct{}{}
		if row != nil {
			// Curated default rows may be admin-taken-over at the canonical
			// source-backed identity. That state is allowed and later curated
			// reconcile/verify logic decides whether routing is already steady
			// or needs canonicalization; preflight must not reject it early.
			if IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
				excludedIDs[strings.TrimSpace(row.ID)] = struct{}{}
			} else {
				if err := rejectManagedPageLayoutCollision(row, layoutID, packName); err != nil {
					return err
				}
			}
		}
		if layoutName == "" {
			return nil
		}
		row, err = loadPageLayoutRowByBusinessKeyExcludingIDs(ctx, db, objectAPIName, layoutName, excludedIDs)
		if err != nil {
			return err
		}
		if row == nil {
			return nil
		}
		return rejectManagedPageLayoutCollision(row, row.ID, packName)
	}
	if layoutName == "" {
		return fmt.Errorf("non-default page_layout ownership preflight requires layout name")
	}
	row, err := LoadPageLayoutRowByBusinessKey(ctx, db, objectAPIName, layoutName)
	if err != nil {
		return err
	}
	if row == nil {
		return nil
	}
	return rejectManagedPageLayoutCollision(row, row.ID, packName)
}

func CountLivePageLayoutsForObject(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_PageLayout
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND (IsDeleted = 0 OR IsDeleted IS NULL)`, objectAPIName).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func CountWildcardLayoutAssignments(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_LayoutAssignment
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND RoleId IS NULL
		  AND ProfileId IS NULL
		  AND RecordTypeId IS NULL`, objectAPIName).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func rejectManagedPageLayoutCollision(row *PageLayoutRow, layoutID string, packName string) error {
	if row == nil {
		return nil
	}
	if IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
		return fmt.Errorf("page_layout %s collides with admin-owned row %s; refusing managed upsert", layoutID, row.ID)
	}
	if isMalformedManagedPageLayoutOwnership(*row) {
		return fmt.Errorf("page_layout %s collides with malformed managed row %s (empty OwnerPack); refusing managed upsert", layoutID, row.ID)
	}
	existingOwnerPack := strings.TrimSpace(row.OwnerPack)
	incomingOwnerPack := strings.TrimSpace(packName)
	if !strings.EqualFold(existingOwnerPack, incomingOwnerPack) {
		return fmt.Errorf(
			"page_layout %s collides with owner pack %q on row %s; refusing managed upsert for %q",
			layoutID, existingOwnerPack, row.ID, incomingOwnerPack,
		)
	}
	return nil
}

func normalizeManagedPageLayoutBusinessKeyCollision(row *PageLayoutRow, layout models.PageLayout, packName string) (models.PageLayout, error) {
	if row == nil || strings.EqualFold(strings.TrimSpace(row.ID), strings.TrimSpace(layout.ID)) {
		return layout, nil
	}
	if layout.IsDefault {
		return layout, fmt.Errorf(
			"page_layout %s/%s collides with existing row %s under business key ownership; refusing managed upsert for %s",
			layout.ObjectAPIName, layout.LayoutName, row.ID, layout.ID,
		)
	}
	if !layout.IsDefault && strings.EqualFold(strings.TrimSpace(row.ID), "curated_"+strings.TrimSpace(layout.ObjectAPIName)+"_default") {
		return layout, fmt.Errorf(
			"page_layout %s/%s collides with existing row %s under business key ownership; refusing managed upsert for %s",
			layout.ObjectAPIName, layout.LayoutName, row.ID, layout.ID,
		)
	}
	existingOwnerPack := strings.TrimSpace(row.OwnerPack)
	incomingOwnerPack := strings.TrimSpace(packName)
	if strings.EqualFold(existingOwnerPack, incomingOwnerPack) {
		layout.ID = row.ID
		return layout, nil
	}
	return layout, fmt.Errorf(
		"page_layout %s/%s collides with existing row %s under business key ownership; refusing managed upsert for %s",
		layout.ObjectAPIName, layout.LayoutName, row.ID, layout.ID,
	)
}

func requireManagedOwnerPack(packName string) (string, error) {
	trimmed := strings.TrimSpace(packName)
	if trimmed == "" {
		return "", fmt.Errorf("managed page_layout upsert requires non-empty owner pack")
	}
	return trimmed, nil
}

func upsertManagedPageLayoutResolved(ctx context.Context, db *sql.DB, layout models.PageLayout, source string, packName string) (models.PageLayout, error) {
	normalizedPackName, err := requireManagedOwnerPack(packName)
	if err != nil {
		return layout, err
	}
	existingRow, err := LoadPageLayoutRow(ctx, db, layout.ID)
	if err != nil {
		return layout, err
	}
	if err := rejectManagedPageLayoutCollision(existingRow, layout.ID, normalizedPackName); err != nil {
		return layout, err
	}
	existingBusinessKeyRow, err := LoadPageLayoutRowByBusinessKey(ctx, db, layout.ObjectAPIName, layout.LayoutName)
	if err != nil {
		return layout, err
	}
	layout, err = normalizeManagedPageLayoutBusinessKeyCollision(existingBusinessKeyRow, layout, normalizedPackName)
	if err != nil {
		return layout, err
	}

	configJSON, err := json.Marshal(layout)
	if err != nil {
		return layout, err
	}

	ownerPack := nullableOwnerPack(normalizedPackName)
	_, err = db.ExecContext(ctx, `
		INSERT INTO _System_PageLayout (
			Id, ObjectApiName, LayoutName, Type, IsDefault, IsDeleted, Config,
			Source, GenerationVersion, IsCustomized, LayoutVersion, OwnerPack,
			CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?, 0, ?, ?, 0, 0, 0, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE
			ObjectApiName     = VALUES(ObjectApiName),
			LayoutName        = VALUES(LayoutName),
			Config           = VALUES(Config),
			Source           = VALUES(Source),
			Type             = VALUES(Type),
			GenerationVersion = VALUES(GenerationVersion),
			IsCustomized     = VALUES(IsCustomized),
			LayoutVersion    = VALUES(LayoutVersion),
			IsDefault        = VALUES(IsDefault),
			IsDeleted        = 0,
			OwnerPack        = VALUES(OwnerPack),
			LastModifiedDate = CURRENT_TIMESTAMP`,
		layout.ID, layout.ObjectAPIName, layout.LayoutName, layout.Type, layout.IsDefault, string(configJSON), source, ownerPack,
	)
	return layout, err
}

func UpsertManagedPageLayout(ctx context.Context, db *sql.DB, layout models.PageLayout, source string, packName string) error {
	_, err := upsertManagedPageLayoutResolved(ctx, db, layout, source, packName)
	return err
}

func UpsertManagedPageLayoutResolved(ctx context.Context, db *sql.DB, layout models.PageLayout, source string, packName string) (models.PageLayout, error) {
	return upsertManagedPageLayoutResolved(ctx, db, layout, source, packName)
}

// RecordPanelDedupKey is the shared identity used to union pack-authored
// recordPanels into a stored layout. Case-insensitive on the trimmed panel Id
// so the additive-merge applier and the verify missing-check agree and
// converge (never flag "missing" for a panel the merge considers present).
func RecordPanelDedupKey(id string) string {
	return strings.ToLower(strings.TrimSpace(id))
}

// mergeAdditiveRecordPanels appends every pack panel whose Id is not already
// present in live (matched by RecordPanelDedupKey) and returns the extended
// slice plus the number added. Pure: no DB, no mutation of existing entries,
// no reordering. Blank-Id pack panels are skipped.
func mergeAdditiveRecordPanels(live, pack []models.RecordPanelConfig) ([]models.RecordPanelConfig, int) {
	present := make(map[string]struct{}, len(live))
	for _, p := range live {
		present[RecordPanelDedupKey(p.ID)] = struct{}{}
	}
	added := 0
	for _, p := range pack {
		if strings.TrimSpace(p.ID) == "" {
			continue
		}
		key := RecordPanelDedupKey(p.ID)
		if _, ok := present[key]; ok {
			continue
		}
		live = append(live, p)
		present[key] = struct{}{}
		added++
	}
	return live, added
}

// MergePackRecordPanelsIntoAdminOwnedLayout additively unions pack-authored
// recordPanels (keyed by RecordPanelDedupKey) into an admin-owned curated
// layout's stored Config, WITHOUT overwriting admin-authored panels/sections
// or altering the row's ownership tuple (Source / IsCustomized / OwnerPack /
// IsDefault). It is the delivery path for pack-owned record panels onto layouts
// a tenant admin has customized: Salesforce-parity preserves admin edits, but
// pack composition additions (e.g. crm.recordActivityFeed) still land.
//
// Purely additive — never removes, reorders, or mutates existing panels, so it
// cannot regress an admin layout edit. Returns added=0 with no write when every
// pack panel Id is already present, when the row is not admin-owned (managed
// rows take the full-refresh path via UpsertManagedPageLayout), or when the row
// is missing.
func MergePackRecordPanelsIntoAdminOwnedLayout(
	ctx context.Context,
	db *sql.DB,
	layoutID string,
	packPanels []models.RecordPanelConfig,
) (int, error) {
	if len(packPanels) == 0 {
		return 0, nil
	}
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback() //nolint:errcheck

	// FOR UPDATE row lock: serialize the read-modify-write against concurrent
	// admin saves / re-adoption so neither a newer Config nor this panel
	// addition is lost.
	row, err := loadPageLayoutRowTxForUpdate(ctx, tx, layoutID)
	if err != nil {
		return 0, err
	}
	if row == nil {
		return 0, nil
	}
	// Only admin-owned rows use this path; managed rows are fully refreshed by
	// UpsertManagedPageLayout. Re-check under the lock so a concurrent re-adopt
	// to managed is not clobbered by a stale additive write.
	if !IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
		return 0, nil
	}

	live, err := DecodePageLayoutConfig([]byte(row.ConfigJSON))
	if err != nil {
		return 0, fmt.Errorf("parse admin-owned curated layout %s config: %w", layoutID, err)
	}

	merged, added := mergeAdditiveRecordPanels(live.RecordPanels, packPanels)
	if added == 0 {
		return 0, nil
	}
	live.RecordPanels = merged

	configJSON, err := json.Marshal(live)
	if err != nil {
		return 0, fmt.Errorf("marshal merged curated layout %s config: %w", layoutID, err)
	}
	// Update ONLY Config (+ LastModifiedDate), fenced on the exact ownership
	// tuple and Config we merged from, so even if the FOR UPDATE lock is not
	// honored the write cannot clobber a concurrent admin save or an
	// ownership flip (re-adopt to managed) — RowsAffected == 0 signals the
	// row moved under us. The ownership tuple (Source / IsCustomized /
	// OwnerPack / IsDefault) is deliberately left untouched so the row stays
	// admin-owned.
	res, err := tx.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET Config = ?,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?
		  AND COALESCE(Source, '') = ?
		  AND COALESCE(IsCustomized, 0) = ?
		  AND COALESCE(CAST(Config AS CHAR), '') = ?
		  AND (IsDeleted = 0 OR IsDeleted IS NULL)`,
		string(configJSON), layoutID, row.Source, row.IsCustomized, row.ConfigJSON,
	)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	if affected == 0 {
		// Row changed under us (concurrent admin save / re-adopt) or was
		// deleted; skip without committing. apply-packs re-reads and converges
		// on the next reconcile pass.
		return 0, nil
	}
	if err := tx.Commit(); err != nil {
		return 0, err
	}
	return added, nil
}

func ClearPageLayoutDefaultFlagByID(ctx context.Context, db *sql.DB, layoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?
		  AND IsDefault = 1`, layoutID)
	return err
}

func ClearOtherPageLayoutDefaultFlags(ctx context.Context, db *sql.DB, objectAPIName, keepLayoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND Id <> ?
		  AND IsDefault = 1`, objectAPIName, keepLayoutID)
	return err
}

func SetPageLayoutDefaultFlagByID(ctx context.Context, db *sql.DB, layoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 1,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?`, layoutID)
	return err
}

func InsertWildcardLayoutAssignment(ctx context.Context, db *sql.DB, assignmentID, objectAPIName, layoutID, routeSource string, packName string) error {
	// Assignment ownership derives from RouteSource, not from who created the row.
	// admin_authored routes are L2 authority; the assignment must have OwnerPack=NULL
	// regardless of which pack triggered the write.
	assignmentOwnerPack := nullableOwnerPack(assignmentOwnerPackFromRouteSource(routeSource, packName))
	_, err := db.ExecContext(ctx, `
		INSERT INTO _System_LayoutAssignment (
			Id, ObjectApiName, LayoutId, RoleId, ProfileId, RecordTypeId,
			Specificity, CreatedDate, LastModifiedDate, RouteSource, OwnerPack
		) VALUES (?, ?, ?, NULL, NULL, NULL, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)`,
		assignmentID, objectAPIName, layoutID, routeSource, assignmentOwnerPack,
	)
	return err
}

// LoadAdminOwnedDefaultPageLayoutRowForObject returns the oldest live default
// layout row for an object that is already owned by admin surfaces.
func LoadAdminOwnedDefaultPageLayoutRowForObject(ctx context.Context, db *sql.DB, objectAPIName string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` "+
			"WHERE LOWER(`%s`) = LOWER(?) AND COALESCE(`%s`, 0) = 1 AND (`%s` = 0 OR `%s` IS NULL) "+
			"  AND (COALESCE(`%s`, 0) = 1 OR LOWER(COALESCE(`%s`, '')) IN ('admin_created', 'cloned', 'custom')) "+
			"ORDER BY `%s` ASC LIMIT 1",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Source,
		constants.FieldCreatedDate,
	)

	var row PageLayoutRow
	if err := db.QueryRowContext(ctx, query, objectAPIName).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
		&row.OwnerPack,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

func CountAdminOwnedDefaultPageLayoutRowsForObject(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	query := fmt.Sprintf(
		"SELECT COUNT(*) FROM `%s` "+
			"WHERE LOWER(`%s`) = LOWER(?) AND COALESCE(`%s`, 0) = 1 AND (`%s` = 0 OR `%s` IS NULL) "+
			"  AND (COALESCE(`%s`, 0) = 1 OR LOWER(COALESCE(`%s`, '')) IN ('admin_created', 'cloned', 'custom'))",
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Source,
	)
	if err := db.QueryRowContext(ctx, query, objectAPIName).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func CountAdminOwnedWildcardLayoutAssignmentsForObject(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_LayoutAssignment
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND RoleId IS NULL
		  AND ProfileId IS NULL
		  AND RecordTypeId IS NULL
		  AND LOWER(COALESCE(RouteSource, '')) = LOWER(?)`,
		objectAPIName, models.LayoutAssignmentRouteSourceAdminAuthored).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

// loadPageLayoutRowTxForUpdate is loadPageLayoutRowTx with a FOR UPDATE row
// lock, so a read-modify-write of Config (see
// MergePackRecordPanelsIntoAdminOwnedLayout) serializes against concurrent
// admin saves / re-adoption instead of racing them.
func loadPageLayoutRowTxForUpdate(ctx context.Context, tx *sql.Tx, layoutID string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL) FOR UPDATE",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.ColID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)

	var row PageLayoutRow
	if err := tx.QueryRowContext(ctx, query, layoutID).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
		&row.OwnerPack,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

func loadPageLayoutRowTx(ctx context.Context, tx *sql.Tx, layoutID string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.ColID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)

	var row PageLayoutRow
	if err := tx.QueryRowContext(ctx, query, layoutID).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
		&row.OwnerPack,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

func loadPageLayoutRowByBusinessKeyTx(ctx context.Context, tx *sql.Tx, objectAPIName, layoutName string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0), COALESCE(`OwnerPack`, '') "+
			"FROM `%s` WHERE LOWER(`%s`) = LOWER(?) AND LOWER(`%s`) = LOWER(?) AND (`%s` = 0 OR `%s` IS NULL) "+
			"ORDER BY `%s` ASC, `%s` ASC",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
		constants.FieldCreatedDate,
		constants.ColID,
	)

	rows, err := tx.QueryContext(ctx, query, objectAPIName, layoutName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sourceBackedMatches []PageLayoutRow
	var adminOwnedMatches []PageLayoutRow
	var malformedManagedMatches []PageLayoutRow
	for rows.Next() {
		var row PageLayoutRow
		if err := rows.Scan(
			&row.ID,
			&row.ConfigJSON,
			&row.Source,
			&row.IsCustomized,
			&row.IsDefault,
			&row.OwnerPack,
		); err != nil {
			return nil, err
		}
		if IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
			adminOwnedMatches = append(adminOwnedMatches, row)
			continue
		}
		if isMalformedManagedPageLayoutOwnership(row) {
			malformedManagedMatches = append(malformedManagedMatches, row)
			continue
		}
		sourceBackedMatches = append(sourceBackedMatches, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if len(malformedManagedMatches) > 0 {
		ids := make([]string, 0, len(malformedManagedMatches))
		for _, row := range malformedManagedMatches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf(
			"page_layout business key %s/%s has malformed managed row(s) with empty OwnerPack (%s); refusing source-backed fallback",
			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); refusing source-backed fallback",
			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); refusing ambiguous same-name fallback",
			objectAPIName, layoutName, len(sourceBackedMatches), strings.Join(ids, ", "),
		)
	}
	return &sourceBackedMatches[0], nil
}

func upsertManagedPageLayoutTxResolved(ctx context.Context, tx *sql.Tx, layout models.PageLayout, source string, packName string) (models.PageLayout, error) {
	normalizedPackName, err := requireManagedOwnerPack(packName)
	if err != nil {
		return layout, err
	}
	existingRow, err := loadPageLayoutRowTx(ctx, tx, layout.ID)
	if err != nil {
		return layout, err
	}
	if err := rejectManagedPageLayoutCollision(existingRow, layout.ID, normalizedPackName); err != nil {
		return layout, err
	}
	existingBusinessKeyRow, err := loadPageLayoutRowByBusinessKeyTx(ctx, tx, layout.ObjectAPIName, layout.LayoutName)
	if err != nil {
		return layout, err
	}
	layout, err = normalizeManagedPageLayoutBusinessKeyCollision(existingBusinessKeyRow, layout, normalizedPackName)
	if err != nil {
		return layout, err
	}

	configJSON, err := json.Marshal(layout)
	if err != nil {
		return layout, err
	}

	ownerPack := nullableOwnerPack(normalizedPackName)
	_, err = tx.ExecContext(ctx, `
		INSERT INTO _System_PageLayout (
			Id, ObjectApiName, LayoutName, Type, IsDefault, IsDeleted, Config,
			Source, GenerationVersion, IsCustomized, LayoutVersion, OwnerPack,
			CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?, 0, ?, ?, 0, 0, 0, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE
			ObjectApiName     = VALUES(ObjectApiName),
			LayoutName        = VALUES(LayoutName),
			Config           = VALUES(Config),
			Source           = VALUES(Source),
			Type             = VALUES(Type),
			GenerationVersion = VALUES(GenerationVersion),
			IsCustomized     = VALUES(IsCustomized),
			LayoutVersion    = VALUES(LayoutVersion),
			IsDefault        = VALUES(IsDefault),
			IsDeleted        = 0,
			OwnerPack        = VALUES(OwnerPack),
			LastModifiedDate = CURRENT_TIMESTAMP`,
		layout.ID, layout.ObjectAPIName, layout.LayoutName, layout.Type, layout.IsDefault, string(configJSON), source, ownerPack,
	)
	return layout, err
}

func upsertManagedPageLayoutTx(ctx context.Context, tx *sql.Tx, layout models.PageLayout, source string, packName string) error {
	_, err := upsertManagedPageLayoutTxResolved(ctx, tx, layout, source, packName)
	return err
}

func clearOtherPageLayoutDefaultFlagsTx(ctx context.Context, tx *sql.Tx, objectAPIName, keepLayoutID string) error {
	_, err := tx.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND Id <> ?
		  AND IsDefault = 1`, objectAPIName, keepLayoutID)
	return err
}

func setPageLayoutDefaultFlagByIDTx(ctx context.Context, tx *sql.Tx, layoutID string) error {
	_, err := tx.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 1,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?`, layoutID)
	return err
}

// AdoptWildcardLayoutRouteAtomically replaces any existing wildcard
// assignments for the object with a single new assignment pointing at
// layoutID, in the same transaction that pins the matching IsDefault flag.
// Used when the curated row is already correct but the wildcard route is
// missing/wrong/orphaned.
// packName is the owning pack name; pass "" for admin/platform-authored assignments.
func AdoptWildcardLayoutRouteAtomically(
	ctx context.Context,
	db *sql.DB,
	assignmentID string,
	objectAPIName string,
	layoutID string,
	routeSource string,
	packName string,
) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint:errcheck

	if err := clearOtherPageLayoutDefaultFlagsTx(ctx, tx, objectAPIName, layoutID); err != nil {
		return err
	}
	if err := setPageLayoutDefaultFlagByIDTx(ctx, tx, layoutID); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `
		DELETE FROM _System_LayoutAssignment
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND RoleId IS NULL
		  AND ProfileId IS NULL
		  AND RecordTypeId IS NULL`,
		objectAPIName,
	); err != nil {
		return err
	}
	// Assignment ownership derives from RouteSource: admin_authored routes are
	// L2 authority and must not be attributed to the pack that triggered the repair.
	assignmentOwnerPack := nullableOwnerPack(assignmentOwnerPackFromRouteSource(routeSource, packName))
	if _, err := tx.ExecContext(ctx, `
		INSERT INTO _System_LayoutAssignment (
			Id, ObjectApiName, LayoutId, RoleId, ProfileId, RecordTypeId,
			Specificity, CreatedDate, LastModifiedDate, RouteSource, OwnerPack
		) VALUES (?, ?, ?, NULL, NULL, NULL, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)`,
		assignmentID, objectAPIName, layoutID, routeSource, assignmentOwnerPack,
	); err != nil {
		return err
	}
	return tx.Commit()
}

// EnsureManagedPageLayoutAndAdoptWildcardRouteAtomically upserts the curated
// row to the pack's expected config and then replaces any wildcard
// assignments with a single new one pointing at routeLayoutID, all in one
// transaction. Used when both row repair and route repair are needed.
// packName is the owning pack name; pass "" for admin/platform-authored rows.
func EnsureManagedPageLayoutAndAdoptWildcardRouteAtomically(
	ctx context.Context,
	db *sql.DB,
	layout models.PageLayout,
	source string,
	assignmentID string,
	objectAPIName string,
	routeLayoutID string,
	routeSource string,
	packName string,
) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint:errcheck

	resolvedLayout, err := upsertManagedPageLayoutTxResolved(ctx, tx, layout, source, packName)
	if err != nil {
		return err
	}
	if strings.EqualFold(strings.TrimSpace(routeLayoutID), strings.TrimSpace(layout.ID)) {
		routeLayoutID = resolvedLayout.ID
	}
	if err := clearOtherPageLayoutDefaultFlagsTx(ctx, tx, objectAPIName, routeLayoutID); err != nil {
		return err
	}
	if err := setPageLayoutDefaultFlagByIDTx(ctx, tx, routeLayoutID); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `
		DELETE FROM _System_LayoutAssignment
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND RoleId IS NULL
		  AND ProfileId IS NULL
		  AND RecordTypeId IS NULL`,
		objectAPIName,
	); err != nil {
		return err
	}
	// Layout row ownership = packName (the pack created/repairs this row).
	// Assignment row ownership derives from RouteSource: admin_authored => NULL.
	assignmentOwnerPack := nullableOwnerPack(assignmentOwnerPackFromRouteSource(routeSource, packName))
	if _, err := tx.ExecContext(ctx, `
		INSERT INTO _System_LayoutAssignment (
			Id, ObjectApiName, LayoutId, RoleId, ProfileId, RecordTypeId,
			Specificity, CreatedDate, LastModifiedDate, RouteSource, OwnerPack
		) VALUES (?, ?, ?, NULL, NULL, NULL, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)`,
		assignmentID, objectAPIName, routeLayoutID, routeSource, assignmentOwnerPack,
	); err != nil {
		return err
	}
	return tx.Commit()
}

// assignmentOwnerPackFromRouteSource derives the OwnerPack value for a
// _System_LayoutAssignment row from the route's authorship intent.
// admin_authored routes are L2 authority: the assignment must have OwnerPack=NULL
// regardless of which pack triggered the write.  All other route sources
// (managed_seed, runtime_fallback, …) are pack-owned.
func assignmentOwnerPackFromRouteSource(routeSource, packName string) string {
	if strings.EqualFold(strings.TrimSpace(routeSource), models.LayoutAssignmentRouteSourceAdminAuthored) {
		return ""
	}
	return packName
}

// EnsureManagedPageLayoutAndClearDefaultAtomically upserts the curated row
// while clearing its IsDefault flag, used when an admin route legitimately
// points elsewhere — we must keep the pack's curated config landed for
// runtime resolution fallback but must not let it appear as the default.
// packName is the owning pack name; pass "" for admin/platform-authored rows.
func EnsureManagedPageLayoutAndClearDefaultAtomically(
	ctx context.Context,
	db *sql.DB,
	layout models.PageLayout,
	source string,
	clearLayoutID string,
	packName string,
) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint:errcheck

	resolvedLayout, err := upsertManagedPageLayoutTxResolved(ctx, tx, layout, source, packName)
	if err != nil {
		return err
	}
	if strings.EqualFold(strings.TrimSpace(clearLayoutID), strings.TrimSpace(layout.ID)) {
		clearLayoutID = resolvedLayout.ID
	}
	if _, err := tx.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?
		  AND IsDefault = 1`, clearLayoutID); err != nil {
		return err
	}
	return tx.Commit()
}