package persistence

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

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

type pageLayoutFieldAdmissionSnapshot struct {
	ID                string
	ObjectAPIName     string
	LayoutName        string
	Type              sql.NullString
	IsDefault         bool
	IsDeleted         bool
	ConfigJSON        string
	LayoutVersion     int
	IsCustomized      bool
	Source            sql.NullString
	GenerationVersion int
	OwnerPack         sql.NullString
}

type layoutAdmissionRestorePlan struct {
	snapshot           pageLayoutFieldAdmissionSnapshot
	current            pageLayoutOwnerState
	admitted           *models.PageLayout
	admittedConfigJSON string
	currentConfigJSON  string
	typeValue          interface{}
	sourceValue        interface{}
	ownerPackValue     interface{}
}

func newLayoutAdmissionRestorePlan(
	snapshot pageLayoutFieldAdmissionSnapshot,
	current pageLayoutOwnerState,
	admitted *models.PageLayout,
	admittedConfigJSON string,
) layoutAdmissionRestorePlan {
	plan := layoutAdmissionRestorePlan{
		snapshot:           snapshot,
		current:            current,
		admitted:           admitted,
		admittedConfigJSON: admittedConfigJSON,
	}
	if snapshot.Type.Valid {
		plan.typeValue = snapshot.Type.String
	}
	if snapshot.Source.Valid {
		plan.sourceValue = snapshot.Source.String
	}
	if snapshot.OwnerPack.Valid && strings.TrimSpace(snapshot.OwnerPack.String) != "" {
		plan.ownerPackValue = snapshot.OwnerPack.String
	}
	return plan
}

type pageLayoutAdmissionConfigGuard struct {
	CompactLayout         []string                   `json:"compactLayout"`
	Tabs                  []string                   `json:"tabs"`
	Sections              []models.PageSection       `json:"sections"`
	RelatedLists          []models.RelatedListConfig `json:"relatedLists"`
	RecordPanels          []models.RecordPanelConfig `json:"recordPanels"`
	HeaderActions         []models.ActionConfig      `json:"headerActions"`
	QuickActions          []models.ActionConfig      `json:"quickActions"`
	HeaderActionsAuthored bool                       `json:"headerActionsAuthored,omitempty"`
	QuickActionsAuthored  bool                       `json:"quickActionsAuthored,omitempty"`
	RecordStates          []models.RecordStateRule   `json:"recordStates"`
	PrimaryHeaderActions  []string                   `json:"primaryHeaderActions,omitempty"`
}

func pageLayoutAdmissionConfigGuardFromLayout(layout *models.PageLayout) (string, error) {
	if layout == nil {
		return "", nil
	}
	guard := pageLayoutAdmissionConfigGuard{
		CompactLayout:         layout.CompactLayout,
		Tabs:                  layout.Tabs,
		Sections:              layout.Sections,
		RelatedLists:          layout.RelatedLists,
		RecordPanels:          layout.RecordPanels,
		HeaderActions:         layout.HeaderActions,
		QuickActions:          layout.QuickActions,
		HeaderActionsAuthored: layout.HeaderActionsAuthored,
		QuickActionsAuthored:  layout.QuickActionsAuthored,
		RecordStates:          layout.RecordStates,
		PrimaryHeaderActions:  layout.PrimaryHeaderActions,
	}
	guardJSON, err := json.Marshal(guard)
	if err != nil {
		return "", err
	}
	return string(guardJSON), nil
}

func pageLayoutAdmissionConfigGuardFromJSON(configJSON string) (string, error) {
	var layout models.PageLayout
	if strings.TrimSpace(configJSON) != "" {
		if err := decodeJSONUseNumber(configJSON, &layout); err != nil {
			return "", err
		}
	}
	return pageLayoutAdmissionConfigGuardFromLayout(&layout)
}

// SnapshotLayoutForFieldAdmission captures the current durable page-layout row
// before add-field admission transfers it through SaveLayout. The returned
// rollback restores the captured owner tuple as well as the layout content; it
// fences on the admitted config and does not trust caller-supplied owner bits.
func (r *MetadataRepository) SnapshotLayoutForFieldAdmission(ctx context.Context, layoutID string) (func(context.Context, *models.PageLayout) error, error) {
	if strings.TrimSpace(layoutID) == "" {
		return nil, apperrors.NewValidationError("id", "page layout id is required")
	}
	sqlStr := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `LayoutVersion`, `%s`, `%s`, `%s`, `OwnerPack` "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL) LIMIT 1",
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Type,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_GenerationVersion,
		constants.TablePageLayout,
		constants.FieldID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	var snapshot pageLayoutFieldAdmissionSnapshot
	if err := r.db.QueryRowContext(ctx, sqlStr, layoutID).Scan(
		&snapshot.ID,
		&snapshot.ObjectAPIName,
		&snapshot.LayoutName,
		&snapshot.Type,
		&snapshot.IsDefault,
		&snapshot.IsDeleted,
		&snapshot.ConfigJSON,
		&snapshot.LayoutVersion,
		&snapshot.IsCustomized,
		&snapshot.Source,
		&snapshot.GenerationVersion,
		&snapshot.OwnerPack,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, apperrors.NewInvalidReferenceError(
				fmt.Sprintf("layoutId %q does not reference a live page layout", layoutID),
				"layoutId",
			)
		}
		return nil, err
	}
	return func(restoreCtx context.Context, admitted *models.PageLayout) error {
		return r.restoreLayoutForFieldAdmission(restoreCtx, snapshot, admitted)
	}, nil
}

// SaveLayoutForFieldAdmission saves an add-field layout admission using the
// original layout Config observed by GetLayouts. It captures the rollback
// snapshot and applies the admitted layout under one pessimistic row lock, so a
// stale layout clone cannot overwrite newer admin layout content.
func (r *MetadataRepository) SaveLayoutForFieldAdmission(ctx context.Context, original, updated *models.PageLayout) (func(context.Context, *models.PageLayout) error, error) {
	if original == nil {
		return nil, apperrors.NewValidationError("layout", "original page layout is required for field admission")
	}
	if updated == nil {
		return nil, apperrors.NewValidationError("layout", "updated page layout is required for field admission")
	}
	if strings.TrimSpace(original.ID) == "" || strings.TrimSpace(updated.ID) == "" {
		return nil, apperrors.NewValidationError("id", "page layout id is required")
	}
	if original.ID != updated.ID {
		return nil, apperrors.NewValidationError("id", "field admission layout id mismatch")
	}
	normalizeAdminPageLayoutForPersistence(updated)
	if updated.ObjectAPIName == "" {
		return nil, apperrors.NewValidationError("objectApiName", "objectApiName is required")
	}
	if updated.LayoutName == "" {
		return nil, apperrors.NewValidationError("layoutName", "layoutName is required")
	}
	expectedConfigGuard, err := pageLayoutAdmissionConfigGuardFromLayout(original)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to marshal original layout config guard", err)
	}

	configJSON, err := r.marshalJSON(updated)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to marshal layout", err)
	}

	tx, cleanup, err := BeginPessimisticTx(ctx, r.db)
	if err != nil {
		return nil, err
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
		_ = cleanup()
	}()

	sqlStr := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `LayoutVersion`, `%s`, `%s`, `%s`, `OwnerPack`, `%s` "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL) LIMIT 1 FOR UPDATE",
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Type,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_GenerationVersion,
		constants.FieldLastModifiedDate,
		constants.TablePageLayout,
		constants.FieldID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	var snapshot pageLayoutFieldAdmissionSnapshot
	var ignoredLastModifiedDate interface{}
	if err := tx.QueryRowContext(ctx, sqlStr, updated.ID).Scan(
		&snapshot.ID,
		&snapshot.ObjectAPIName,
		&snapshot.LayoutName,
		&snapshot.Type,
		&snapshot.IsDefault,
		&snapshot.IsDeleted,
		&snapshot.ConfigJSON,
		&snapshot.LayoutVersion,
		&snapshot.IsCustomized,
		&snapshot.Source,
		&snapshot.GenerationVersion,
		&snapshot.OwnerPack,
		&ignoredLastModifiedDate,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, apperrors.NewInvalidReferenceError(
				fmt.Sprintf("layoutId %q does not reference a live page layout", updated.ID),
				"layoutId",
			)
		}
		return nil, err
	}

	current := pageLayoutOwnerState{
		ID:            snapshot.ID,
		ObjectAPIName: snapshot.ObjectAPIName,
		LayoutName:    snapshot.LayoutName,
		IsCustomized:  snapshot.IsCustomized,
	}
	if snapshot.Source.Valid {
		current.Source = snapshot.Source.String
	}
	if snapshot.OwnerPack.Valid {
		current.OwnerPack = snapshot.OwnerPack.String
	}
	if !isTrustedAdminPageLayoutOwnerState(current) &&
		!isDefaultsOnlyManagedPageLayoutOwnerState(current) &&
		!isPlatformGeneratedPageLayoutOwnerState(current) {
		return nil, pageLayoutOwnerTupleError(updated.ID, current)
	}
	if !strings.EqualFold(strings.TrimSpace(updated.ObjectAPIName), strings.TrimSpace(current.ObjectAPIName)) ||
		!strings.EqualFold(strings.TrimSpace(updated.LayoutName), strings.TrimSpace(current.LayoutName)) {
		return nil, pageLayoutBusinessKeyMismatch(updated, current)
	}
	currentConfigGuard, err := pageLayoutAdmissionConfigGuardFromJSON(snapshot.ConfigJSON)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to parse current layout config guard", err)
	}
	if currentConfigGuard != expectedConfigGuard {
		return nil, apperrors.NewConflictError("PageLayout", "version", updated.ID)
	}
	collision, err := loadPageLayoutBusinessKeyCollisionForUpdate(ctx, tx, updated.ObjectAPIName, updated.LayoutName, updated.ID)
	if err != nil {
		return nil, err
	}
	if collision != nil {
		return nil, pageLayoutBusinessKeyConflict(updated, collision.ID)
	}

	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `LayoutVersion` = ?, `IsCustomized` = ?, `%s` = ?, `%s` = ?, `OwnerPack` = NULL, `%s` = CURRENT_TIMESTAMP "+
			"WHERE `%s` = ? AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND COALESCE(`%s`, '') = ? AND COALESCE(`%s`, 0) = ? AND COALESCE(`OwnerPack`, '') = ? AND COALESCE(CAST(`%s` AS CHAR), '') = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Type,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_GenerationVersion,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Config,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	result, err := tx.ExecContext(
		ctx,
		updateSQL,
		updated.ObjectAPIName,
		updated.LayoutName,
		updated.Type,
		updated.IsDefault,
		updated.IsDeleted,
		configJSON,
		updated.LayoutVersion,
		true,
		"admin_created",
		updated.GenerationVersion,
		updated.ID,
		strings.TrimSpace(current.ObjectAPIName),
		strings.TrimSpace(current.LayoutName),
		strings.TrimSpace(current.Source),
		current.IsCustomized,
		strings.TrimSpace(current.OwnerPack),
		snapshot.ConfigJSON,
	)
	if err != nil {
		return nil, err
	}
	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 0 {
		return nil, apperrors.NewConflictError("PageLayout", "version", updated.ID)
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	committed = true
	return func(restoreCtx context.Context, admitted *models.PageLayout) error {
		return r.restoreLayoutForFieldAdmission(restoreCtx, snapshot, admitted)
	}, nil
}

func (r *MetadataRepository) restoreLayoutForFieldAdmission(ctx context.Context, snapshot pageLayoutFieldAdmissionSnapshot, admitted *models.PageLayout) error {
	if admitted == nil {
		return apperrors.NewValidationError("layout", "admitted page layout is required for rollback fencing")
	}
	admittedConfigJSON, err := r.marshalJSON(admitted)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal admitted layout", err)
	}

	tx, cleanup, err := BeginPessimisticTx(ctx, r.db)
	if err != nil {
		return err
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
		_ = cleanup()
	}()

	current, err := loadPageLayoutOwnerStateForUpdate(ctx, tx, snapshot.ID)
	if err != nil {
		return err
	}
	if current == nil {
		return apperrors.NewConflictError("PageLayout", "owner", snapshot.ID)
	}
	if !strings.EqualFold(strings.TrimSpace(current.ObjectAPIName), strings.TrimSpace(snapshot.ObjectAPIName)) ||
		!strings.EqualFold(strings.TrimSpace(current.LayoutName), strings.TrimSpace(snapshot.LayoutName)) {
		return pageLayoutBusinessKeyMismatch(&models.PageLayout{
			ID:            snapshot.ID,
			ObjectAPIName: snapshot.ObjectAPIName,
			LayoutName:    snapshot.LayoutName,
		}, *current)
	}
	if !strings.EqualFold(strings.TrimSpace(current.Source), "admin_created") ||
		!current.IsCustomized ||
		strings.TrimSpace(current.OwnerPack) != "" {
		return pageLayoutOwnerTupleError(snapshot.ID, *current)
	}

	plan := newLayoutAdmissionRestorePlan(snapshot, *current, admitted, admittedConfigJSON)
	restored, err := r.tryExactAdmissionRestore(ctx, tx, plan)
	if err != nil {
		return err
	}
	if !restored {
		plan.currentConfigJSON, err = r.loadCurrentLayoutConfigForFieldAdmissionRollback(ctx, tx, plan.snapshot.ID)
		if err != nil {
			return err
		}
		restored, err = r.trySemanticTokenRetry(ctx, tx, plan)
		if err != nil {
			return err
		}
	}
	if !restored {
		restored, err = r.tryResidueOnlyRepair(ctx, tx, plan)
		if err != nil {
			return err
		}
		if !restored {
			return apperrors.NewConflictError("PageLayout", "owner", snapshot.ID)
		}
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	committed = true
	return nil
}

func (r *MetadataRepository) tryExactAdmissionRestore(ctx context.Context, tx *sql.Tx, plan layoutAdmissionRestorePlan) (bool, error) {
	return r.restoreLayoutAdmissionSnapshotWithFence(ctx, tx, plan, plan.admittedConfigJSON)
}

func (r *MetadataRepository) trySemanticTokenRetry(ctx context.Context, tx *sql.Tx, plan layoutAdmissionRestorePlan) (bool, error) {
	if !jsonSemanticallyEqual(plan.currentConfigJSON, plan.admittedConfigJSON) {
		return false, nil
	}
	return r.restoreLayoutAdmissionSnapshotWithFence(ctx, tx, plan, plan.currentConfigJSON)
}

func (r *MetadataRepository) restoreLayoutAdmissionSnapshotWithFence(
	ctx context.Context,
	tx *sql.Tx,
	plan layoutAdmissionRestorePlan,
	configFence string,
) (bool, error) {
	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `LayoutVersion` = ?, `IsCustomized` = ?, `%s` = ?, `%s` = ?, `OwnerPack` = ?, `%s` = CURRENT_TIMESTAMP "+
			"WHERE `%s` = ? AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND COALESCE(`%s`, '') = ? AND COALESCE(`%s`, 0) = ? AND COALESCE(`OwnerPack`, '') = ? AND COALESCE(CAST(`%s` AS CHAR), '') = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Type,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldIsDeleted,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_GenerationVersion,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Config,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	result, err := tx.ExecContext(
		ctx,
		updateSQL,
		plan.snapshot.ObjectAPIName,
		plan.snapshot.LayoutName,
		plan.typeValue,
		plan.snapshot.IsDefault,
		plan.snapshot.IsDeleted,
		plan.snapshot.ConfigJSON,
		plan.snapshot.LayoutVersion,
		plan.snapshot.IsCustomized,
		plan.sourceValue,
		plan.snapshot.GenerationVersion,
		plan.ownerPackValue,
		plan.snapshot.ID,
		strings.TrimSpace(plan.current.ObjectAPIName),
		strings.TrimSpace(plan.current.LayoutName),
		strings.TrimSpace(plan.current.Source),
		plan.current.IsCustomized,
		strings.TrimSpace(plan.current.OwnerPack),
		configFence,
	)
	if err != nil {
		return false, err
	}
	rowsAffected, _ := result.RowsAffected()
	return rowsAffected > 0, nil
}

func (r *MetadataRepository) tryResidueOnlyRepair(ctx context.Context, tx *sql.Tx, plan layoutAdmissionRestorePlan) (bool, error) {
	residueFields, err := r.safeAdmissionResidueFields(ctx, tx, plan)
	if err != nil {
		return false, err
	}
	if len(residueFields) == 0 {
		return false, nil
	}

	var currentLayout models.PageLayout
	if strings.TrimSpace(plan.currentConfigJSON) != "" {
		if err := decodeJSONUseNumber(plan.currentConfigJSON, &currentLayout); err != nil {
			return false, apperrors.NewInternalError("failed to parse current layout config for rollback repair", err)
		}
	}
	if !removeSectionFields(&currentLayout, residueFields) {
		return false, nil
	}
	repairedConfigJSON, err := r.marshalJSON(&currentLayout)
	if err != nil {
		return false, apperrors.NewInternalError("failed to marshal repaired layout config", err)
	}
	return r.updateLayoutConfigForFieldAdmissionRepair(ctx, tx, plan, repairedConfigJSON)
}

func (r *MetadataRepository) safeAdmissionResidueFields(ctx context.Context, tx *sql.Tx, plan layoutAdmissionRestorePlan) ([]string, error) {
	residueFields, err := sectionFieldsAddedByAdmission(plan.snapshot.ConfigJSON, plan.admitted)
	if err != nil {
		return nil, err
	}
	if len(residueFields) == 0 {
		return nil, nil
	}
	for _, field := range residueFields {
		live, err := r.liveFieldExists(ctx, tx, plan.snapshot.ObjectAPIName, field)
		if err != nil {
			return nil, err
		}
		if live {
			return nil, nil
		}
	}
	return residueFields, nil
}

func (r *MetadataRepository) loadCurrentLayoutConfigForFieldAdmissionRollback(ctx context.Context, tx *sql.Tx, layoutID string) (string, error) {
	sqlStr := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL) LIMIT 1",
		constants.FieldSysPageLayout_Config,
		constants.TablePageLayout,
		constants.FieldID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	var configJSON sql.NullString
	if err := tx.QueryRowContext(ctx, sqlStr, layoutID).Scan(&configJSON); err != nil {
		return "", err
	}
	if !configJSON.Valid {
		return "", nil
	}
	return configJSON.String, nil
}

func (r *MetadataRepository) liveFieldExists(ctx context.Context, tx *sql.Tx, objectAPIName, fieldAPIName string) (bool, error) {
	sqlStr := fmt.Sprintf(
		"SELECT COUNT(*) FROM `%s` f JOIN `%s` o ON o.`%s` = f.`%s` "+
			"WHERE LOWER(o.`%s`) = LOWER(?) AND LOWER(f.`%s`) = LOWER(?) "+
			"AND COALESCE(o.`%s`, 0) = 0 AND COALESCE(f.`%s`, 0) = 0",
		constants.TableField,
		constants.TableObject,
		constants.FieldID,
		constants.FieldObjectID,
		constants.FieldAPIName,
		constants.FieldAPIName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	var count int
	if err := tx.QueryRowContext(ctx, sqlStr, objectAPIName, fieldAPIName).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}

func (r *MetadataRepository) updateLayoutConfigForFieldAdmissionRepair(
	ctx context.Context,
	tx *sql.Tx,
	plan layoutAdmissionRestorePlan,
	repairedConfigJSON string,
) (bool, error) {
	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = CURRENT_TIMESTAMP "+
			"WHERE `%s` = ? AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND LOWER(COALESCE(`%s`, '')) = LOWER(?) AND COALESCE(`%s`, '') = ? AND COALESCE(`%s`, 0) = ? AND COALESCE(`OwnerPack`, '') = ? AND COALESCE(CAST(`%s` AS CHAR), '') = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.TablePageLayout,
		constants.FieldSysPageLayout_Config,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysPageLayout_LayoutName,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_Config,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	result, err := tx.ExecContext(
		ctx,
		updateSQL,
		repairedConfigJSON,
		plan.snapshot.ID,
		strings.TrimSpace(plan.current.ObjectAPIName),
		strings.TrimSpace(plan.current.LayoutName),
		strings.TrimSpace(plan.current.Source),
		plan.current.IsCustomized,
		strings.TrimSpace(plan.current.OwnerPack),
		plan.currentConfigJSON,
	)
	if err != nil {
		return false, err
	}
	rowsAffected, _ := result.RowsAffected()
	return rowsAffected > 0, nil
}

func jsonSemanticallyEqual(left string, right string) bool {
	var leftValue interface{}
	var rightValue interface{}
	if strings.TrimSpace(left) != "" {
		if err := decodeJSONUseNumber(left, &leftValue); err != nil {
			return false
		}
	}
	if strings.TrimSpace(right) != "" {
		if err := decodeJSONUseNumber(right, &rightValue); err != nil {
			return false
		}
	}
	return reflect.DeepEqual(leftValue, rightValue)
}

func sectionFieldsAddedByAdmission(originalConfigJSON string, admitted *models.PageLayout) ([]string, error) {
	var original models.PageLayout
	if strings.TrimSpace(originalConfigJSON) != "" {
		if err := decodeJSONUseNumber(originalConfigJSON, &original); err != nil {
			return nil, apperrors.NewInternalError("failed to parse original layout config for rollback repair", err)
		}
	}
	originalFields := make(map[string]struct{})
	for _, section := range original.Sections {
		for _, field := range section.Fields {
			originalFields[strings.ToLower(strings.TrimSpace(field))] = struct{}{}
		}
	}
	seen := make(map[string]struct{})
	var added []string
	for _, section := range admitted.Sections {
		for _, field := range section.Fields {
			key := strings.ToLower(strings.TrimSpace(field))
			if key == "" {
				continue
			}
			if _, ok := originalFields[key]; ok {
				continue
			}
			if _, ok := seen[key]; ok {
				continue
			}
			seen[key] = struct{}{}
			added = append(added, field)
		}
	}
	return added, nil
}

func removeSectionFields(layout *models.PageLayout, fields []string) bool {
	if layout == nil || len(fields) == 0 {
		return false
	}
	remove := make(map[string]struct{}, len(fields))
	for _, field := range fields {
		key := strings.ToLower(strings.TrimSpace(field))
		if key != "" {
			remove[key] = struct{}{}
		}
	}
	if len(remove) == 0 {
		return false
	}
	changed := false
	for i := range layout.Sections {
		kept := layout.Sections[i].Fields[:0]
		for _, field := range layout.Sections[i].Fields {
			if _, ok := remove[strings.ToLower(strings.TrimSpace(field))]; ok {
				changed = true
				continue
			}
			kept = append(kept, field)
		}
		layout.Sections[i].Fields = kept
	}
	return changed
}