package packs

import (
	"bytes"
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"io"
	"log"
	"strings"
	"time"

	"github.com/nexuscrm/kernel/pkg/createsourceguard"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/utils"
	connector "github.com/pingcap-inc/nexuscrm-contract/pkg/connector"
)

// This file hosts the legacy (Direction='legacy') field-mapping
// WRITE/INIT/VERIFY surface, originally relocated from internal/bootstrap as
// part of the Wave-5 control-plane decoupling. The READ
// surface (GetFieldMappings / GetFieldMappingSourceForTarget /
// fieldMappingSourcePrecedenceSQL) is SHARED — consumed by the automation
// runtime field-mapping applier via the contract
// runtimeports.ConnectorFieldMappingReader port and by the standard-action
// reader port — and STAYS in internal/bootstrap.

// FieldMappingGroup / FieldMappingDef alias the contract DTOs (the canonical
// bootstrap definitions alias the same contract types).
type (
	FieldMappingGroup = connector.FieldMappingGroup
	FieldMappingDef   = connector.FieldMappingDef
)

// fieldMappingGroupV2 is the authored legacy-direction field_mapping payload.
// The published v1 contract types remain the compatibility surface for callers
// that construct groups in Go, while source-backed JSON may additionally carry
// the neutral value-translation policy represented by FieldMappingDefV2.
type fieldMappingGroupV2 struct {
	SourceObject        string              `json:"sourceObject"`
	TargetObject        string              `json:"targetObject"`
	ActionType          string              `json:"actionType"`
	IsCustomized        bool                `json:"isCustomized,omitempty"`
	RetiredSourceFields []string            `json:"retiredSourceFields,omitempty"`
	Mappings            []fieldMappingDefV2 `json:"mappings"`
}

type fieldMappingDefV2 struct {
	connector.FieldMappingDef
	ValueMap        json.RawMessage `json:"valueMap,omitempty"`
	OnUnmappedValue string          `json:"onUnmappedValue,omitempty"`
}

const (
	standardObjectFieldMappingOwner = "standard"
	maxObjectFieldMappingOwnerLen   = 64
)

// boolToInt mirrors the canonical bootstrap helper (relocated alongside the
// write surface; shared by the field-mapping and standard-action writers in
// this package).
func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

// InitializeFieldMappingsFromPayload seeds standard field mappings.
func InitializeFieldMappingsFromPayload(rawDB *sql.DB, raw []byte) error {
	return InitializeFieldMappingsFromPayloadForOwner(rawDB, raw, standardObjectFieldMappingOwner)
}

func InitializeFieldMappingsFromPayloadForOwner(rawDB *sql.DB, raw []byte, owner string) error {
	owner = strings.TrimSpace(owner)
	if owner == "" {
		owner = standardObjectFieldMappingOwner
	}
	log.Printf("🔧 Initializing field mappings for owner %s...", owner)
	groups, err := parseFieldMappingGroupsPayload(raw)
	if err != nil {
		return err
	}
	if owner != standardObjectFieldMappingOwner {
		if err := adoptLegacyStandardFieldMappingRows(context.Background(), rawDB, groups, owner); err != nil {
			return err
		}
	}
	return upsertFieldMappingGroupsForOwnerV2(context.Background(), rawDB, groups, owner)
}

// StandardFieldMappingsPackHashFromPayload returns the shipped field-mapping payload hash.
func StandardFieldMappingsPackHashFromPayload(raw []byte) string {
	sum := sha256.Sum256(raw)
	return hex.EncodeToString(sum[:])
}

func StandardFieldMappingsPackPayloadFromPayload(raw []byte) []byte {
	return append([]byte(nil), raw...)
}

// VerifyStandardFieldMappingsFromPayload checks the shipped standard
// field-mapping component against live rows.
func VerifyStandardFieldMappingsFromPayload(ctx context.Context, rawDB *sql.DB, raw []byte) error {
	return VerifyFieldMappingsFromPayloadForOwner(ctx, rawDB, raw, standardObjectFieldMappingOwner)
}

func VerifyFieldMappingsFromPayloadForOwner(ctx context.Context, rawDB *sql.DB, raw []byte, owner string) error {
	owner = strings.TrimSpace(owner)
	if owner == "" {
		owner = standardObjectFieldMappingOwner
	}
	groups, err := parseFieldMappingGroupsPayload(raw)
	if err != nil {
		return err
	}
	return verifyFieldMappingGroupsForOwnerV2(ctx, rawDB, groups, owner)
}

func parseFieldMappingGroupsPayload(raw []byte) ([]fieldMappingGroupV2, error) {
	dec := json.NewDecoder(bytes.NewReader(raw))
	dec.DisallowUnknownFields()

	var groups []fieldMappingGroupV2
	if err := dec.Decode(&groups); err != nil {
		return nil, apperrors.NewInternalError("failed to parse standard_field_mappings.json", err)
	}
	if tok, err := dec.Token(); err == nil {
		return nil, apperrors.NewInternalError(
			fmt.Sprintf("standard_field_mappings.json has unexpected trailing token %v", tok), nil,
		)
	} else if err != io.EOF {
		return nil, apperrors.NewInternalError("standard_field_mappings.json has invalid trailing JSON", err)
	}
	if err := normalizeFieldMappingPolicies(groups); err != nil {
		return nil, apperrors.NewInternalError("standard_field_mappings.json has invalid value policy", err)
	}
	return groups, nil
}

func normalizeFieldMappingPolicies(groups []fieldMappingGroupV2) error {
	for groupIndex := range groups {
		group := &groups[groupIndex]
		for mappingIndex := range group.Mappings {
			mapping := &group.Mappings[mappingIndex]
			rawValueMap := strings.TrimSpace(string(mapping.ValueMap))
			if rawValueMap != "" {
				parsed, err := connector.ParseConnectorValueMap(rawValueMap)
				if err != nil {
					return fmt.Errorf(
						"group %s→%s(%s) mapping %s ValueMap: %w",
						group.SourceObject, group.TargetObject, group.ActionType, mapping.SourceField, err,
					)
				}
				canonical, err := json.Marshal(parsed)
				if err != nil {
					return fmt.Errorf(
						"group %s→%s(%s) mapping %s canonicalize ValueMap: %w",
						group.SourceObject, group.TargetObject, group.ActionType, mapping.SourceField, err,
					)
				}
				mapping.ValueMap = canonical
			}

			mapping.OnUnmappedValue = strings.TrimSpace(mapping.OnUnmappedValue)
			switch mapping.OnUnmappedValue {
			case "", connector.OnUnmappedValueError, connector.OnUnmappedValueSkip, connector.OnUnmappedValuePassthrough:
			default:
				return fmt.Errorf(
					"group %s→%s(%s) mapping %s OnUnmappedValue %q is not valid",
					group.SourceObject, group.TargetObject, group.ActionType,
					mapping.SourceField, mapping.OnUnmappedValue,
				)
			}
		}
	}
	return nil
}

func fieldMappingValueMapString(raw json.RawMessage) string {
	return strings.TrimSpace(string(raw))
}

func upgradeFieldMappingGroups(groups []FieldMappingGroup) []fieldMappingGroupV2 {
	upgraded := make([]fieldMappingGroupV2, 0, len(groups))
	for _, group := range groups {
		value := fieldMappingGroupV2{
			SourceObject:        group.SourceObject,
			TargetObject:        group.TargetObject,
			ActionType:          group.ActionType,
			IsCustomized:        group.IsCustomized,
			RetiredSourceFields: append([]string(nil), group.RetiredSourceFields...),
			Mappings:            make([]fieldMappingDefV2, 0, len(group.Mappings)),
		}
		for _, mapping := range group.Mappings {
			value.Mappings = append(value.Mappings, fieldMappingDefV2{FieldMappingDef: mapping})
		}
		upgraded = append(upgraded, value)
	}
	return upgraded
}

func contractFieldMappingGroups(groups []fieldMappingGroupV2) []connector.FieldMappingGroup {
	result := make([]connector.FieldMappingGroup, 0, len(groups))
	for _, group := range groups {
		value := connector.FieldMappingGroup{
			SourceObject:        group.SourceObject,
			TargetObject:        group.TargetObject,
			ActionType:          group.ActionType,
			IsCustomized:        group.IsCustomized,
			RetiredSourceFields: append([]string(nil), group.RetiredSourceFields...),
			Mappings:            make([]connector.FieldMappingDef, 0, len(group.Mappings)),
		}
		for _, mapping := range group.Mappings {
			value.Mappings = append(value.Mappings, mapping.FieldMappingDef)
		}
		result = append(result, value)
	}
	return result
}

func VerifyFieldMappingGroupsForOwner(ctx context.Context, rawDB *sql.DB, groups []FieldMappingGroup, owner string) error {
	return verifyFieldMappingGroupsForOwnerV2(ctx, rawDB, upgradeFieldMappingGroups(groups), owner)
}

func verifyFieldMappingGroupsForOwnerV2(ctx context.Context, rawDB *sql.DB, groups []fieldMappingGroupV2, owner string) error {
	type expectedRow struct {
		SourceObject      string
		TargetObject      string
		ActionType        string
		SourceField       string
		TargetField       string
		DefaultValue      string
		SourceLookupField string
		ValueMap          string
		OnUnmappedValue   string
		SortOrder         int
	}

	var expected []expectedRow
	expectedKeysByTuple := map[string]map[string]struct{}{}
	sortOrder := 0
	for _, group := range groups {
		tuple := group.SourceObject + "\x00" + group.TargetObject + "\x00" + group.ActionType
		if _, ok := expectedKeysByTuple[tuple]; !ok {
			expectedKeysByTuple[tuple] = map[string]struct{}{}
		}
		for _, m := range group.Mappings {
			row := expectedRow{
				SourceObject:      group.SourceObject,
				TargetObject:      group.TargetObject,
				ActionType:        group.ActionType,
				SourceField:       strings.TrimSpace(m.SourceField),
				TargetField:       strings.TrimSpace(m.TargetField),
				DefaultValue:      strings.TrimSpace(m.DefaultValue),
				SourceLookupField: strings.TrimSpace(m.SourceLookupField),
				ValueMap:          fieldMappingValueMapString(m.ValueMap),
				OnUnmappedValue:   strings.TrimSpace(m.OnUnmappedValue),
				SortOrder:         sortOrder,
			}
			expected = append(expected, row)
			expectedKeysByTuple[tuple][row.SourceField] = struct{}{}
			sortOrder++
		}
	}

	var errs []string
	for _, want := range expected {
		var targetField string
		var defaultValue, sourceLookupField, valueMap, onUnmappedValue sql.NullString
		var gotSortOrder int
		var isCustomized bool
		var isActive, isDeleted bool
		err := rawDB.QueryRowContext(ctx, `
SELECT TargetField, DefaultValue, SourceLookupField, ValueMap, OnUnmappedValue, SortOrder, IsCustomized, IsActive, IsDeleted
FROM _System_ObjectFieldMapping
WHERE Source = ? AND SourceObject = ? AND TargetObject = ? AND ActionType = ? AND SourceField = ?
  AND Direction = 'legacy'
LIMIT 1`,
			owner, want.SourceObject, want.TargetObject, want.ActionType, want.SourceField,
		).Scan(
			&targetField, &defaultValue, &sourceLookupField, &valueMap, &onUnmappedValue,
			&gotSortOrder, &isCustomized, &isActive, &isDeleted,
		)
		if err == sql.ErrNoRows {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s -> %s/%s is missing", want.SourceObject, want.SourceField, want.TargetObject, want.ActionType))
			continue
		}
		if err != nil {
			return fmt.Errorf("query field mapping %s.%s: %w", want.SourceObject, want.SourceField, err)
		}
		if !isActive || isDeleted {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s is inactive/deleted", want.SourceObject, want.SourceField))
			continue
		}
		if isCustomized {
			continue
		}
		if targetField != want.TargetField {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s TargetField = %q, want %q", want.SourceObject, want.SourceField, targetField, want.TargetField))
		}
		if strings.TrimSpace(defaultValue.String) != want.DefaultValue {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s DefaultValue = %q, want %q", want.SourceObject, want.SourceField, defaultValue.String, want.DefaultValue))
		}
		if strings.TrimSpace(sourceLookupField.String) != want.SourceLookupField {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s SourceLookupField = %q, want %q", want.SourceObject, want.SourceField, sourceLookupField.String, want.SourceLookupField))
		}
		if strings.TrimSpace(valueMap.String) != want.ValueMap {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s ValueMap = %q, want %q", want.SourceObject, want.SourceField, valueMap.String, want.ValueMap))
		}
		if strings.TrimSpace(onUnmappedValue.String) != want.OnUnmappedValue {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s OnUnmappedValue = %q, want %q", want.SourceObject, want.SourceField, onUnmappedValue.String, want.OnUnmappedValue))
		}
		if gotSortOrder != want.SortOrder {
			errs = append(errs, fmt.Sprintf("field mapping %s.%s SortOrder = %d, want %d", want.SourceObject, want.SourceField, gotSortOrder, want.SortOrder))
		}
	}

	for tuple, keep := range expectedKeysByTuple {
		parts := strings.Split(tuple, "\x00")
		rows, err := rawDB.QueryContext(ctx, `
SELECT SourceField
FROM _System_ObjectFieldMapping
WHERE Source = ? AND SourceObject = ? AND TargetObject = ? AND ActionType = ?
  AND Direction = 'legacy'
  AND IsActive = 1 AND IsDeleted = 0 AND (IsCustomized = 0 OR IsCustomized IS NULL)`,
			owner, parts[0], parts[1], parts[2],
		)
		if err != nil {
			return fmt.Errorf("query field mapping tuple %s/%s/%s: %w", parts[0], parts[1], parts[2], err)
		}
		for rows.Next() {
			var sourceField string
			if err := rows.Scan(&sourceField); err != nil {
				rows.Close()
				return fmt.Errorf("scan field mapping tuple %s/%s/%s: %w", parts[0], parts[1], parts[2], err)
			}
			if _, ok := keep[sourceField]; !ok {
				errs = append(errs, fmt.Sprintf("field mapping %s/%s/%s.%s is active but not declared", parts[0], parts[1], parts[2], sourceField))
			}
		}
		if err := rows.Close(); err != nil {
			return err
		}
		if err := rows.Err(); err != nil {
			return err
		}
	}

	if len(errs) > 0 {
		return fmt.Errorf("standard field mappings drift:\n  - %s", strings.Join(errs, "\n  - "))
	}
	if err := createsourceguard.VerifyCreateFromSourceGuardCoverageForFieldMappingGroups(rawDB, contractFieldMappingGroups(groups), owner); err != nil {
		return fmt.Errorf("standard field mappings create-from-source guard coverage drift: %w", err)
	}
	return nil
}

// UpsertFieldMappingGroups writes the platform-standard field-mapping seed.
// Relocated verbatim from internal/bootstrap.
func UpsertFieldMappingGroups(ctx context.Context, rawDB *sql.DB, groups []FieldMappingGroup) error {
	return UpsertFieldMappingGroupsForOwner(ctx, rawDB, groups, standardObjectFieldMappingOwner)
}

// UpsertFieldMappingGroupsForOwner is the shared writer used by the platform
// standard-pack loader and tenant overlays to add rows to
// _System_ObjectFieldMapping.
func UpsertFieldMappingGroupsForOwner(ctx context.Context, rawDB *sql.DB, groups []FieldMappingGroup, owner string) error {
	return upsertFieldMappingGroupsForOwnerV2(ctx, rawDB, upgradeFieldMappingGroups(groups), owner)
}

func upsertFieldMappingGroupsForOwnerV2(ctx context.Context, rawDB *sql.DB, groups []fieldMappingGroupV2, owner string) error {
	owner = strings.TrimSpace(owner)
	if owner == "" {
		return apperrors.NewInternalError("field mapping owner must not be empty", nil)
	}
	if len(owner) > maxObjectFieldMappingOwnerLen {
		return apperrors.NewInternalError(
			fmt.Sprintf("field mapping owner %q exceeds %d characters", owner, maxObjectFieldMappingOwnerLen), nil)
	}

	start := time.Now()
	now := time.Now().Format("2006-01-02 15:04:05")
	if err := claimLegacyFieldMappingRowsForOwner(ctx, rawDB, groups, owner, now); err != nil {
		return err
	}
	if err := deactivateStaleTenantFieldMappingRowsForOwner(ctx, rawDB, groups, owner, now); err != nil {
		return err
	}

	type tupleKey struct {
		SourceObject, TargetObject, ActionType string
	}
	sourceFieldsByTuple := map[tupleKey][]string{}

	var valueParts []string
	var params []interface{}
	sortOrder := 0

	for _, group := range groups {
		k := tupleKey{group.SourceObject, group.TargetObject, group.ActionType}
		if _, ok := sourceFieldsByTuple[k]; !ok {
			sourceFieldsByTuple[k] = nil
		}
		for _, m := range group.Mappings {
			id := fieldMappingID(owner, group, m)

			var defaultVal interface{} = nil
			if m.DefaultValue != "" {
				defaultVal = m.DefaultValue
			}
			var sourceLookupVal interface{} = nil
			if m.SourceLookupField != "" {
				sourceLookupVal = m.SourceLookupField
			}
			var valueMapVal interface{} = nil
			if valueMap := fieldMappingValueMapString(m.ValueMap); valueMap != "" {
				valueMapVal = valueMap
			}
			var onUnmappedValueVal interface{} = nil
			if onUnmappedValue := strings.TrimSpace(m.OnUnmappedValue); onUnmappedValue != "" {
				onUnmappedValueVal = onUnmappedValue
			}

			valueParts = append(valueParts, "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 0, ?, ?, ?)")
			params = append(params,
				id,
				group.SourceObject,
				group.TargetObject,
				group.ActionType,
				m.SourceField,
				m.TargetField,
				sourceLookupVal,
				defaultVal,
				valueMapVal,
				sortOrder,
				boolToInt(fieldMappingIsCustomized(group, m)),
				owner,
				onUnmappedValueVal,
				now, now,
			)
			sourceFieldsByTuple[k] = append(sourceFieldsByTuple[k], m.SourceField)
			sortOrder++
		}
	}

	if len(valueParts) > 0 {
		insertSQL := fmt.Sprintf(
			`INSERT INTO %s
			(Id, SourceObject, TargetObject, ActionType, SourceField, TargetField,
			 SourceLookupField, DefaultValue, ValueMap, SortOrder, IsCustomized, Source, IsActive,
			 IsDeleted, OnUnmappedValue, CreatedDate, LastModifiedDate)
			VALUES %s
			ON DUPLICATE KEY UPDATE
			  TargetField       = IF(IsCustomized=1, TargetField,       VALUES(TargetField)),
			  DefaultValue      = IF(IsCustomized=1, DefaultValue,      VALUES(DefaultValue)),
			  SourceLookupField = IF(IsCustomized=1, SourceLookupField, VALUES(SourceLookupField)),
			  ValueMap          = IF(IsCustomized=1, ValueMap,          VALUES(ValueMap)),
			  SortOrder         = IF(IsCustomized=1, SortOrder,         VALUES(SortOrder)),
			  IsCustomized      = IF(IsCustomized=1, IsCustomized,      VALUES(IsCustomized)),
			  IsActive          = IF(IsCustomized=1, IsActive,          VALUES(IsActive)),
			  IsDeleted         = IF(IsCustomized=1, IsDeleted,         0),
			  OnUnmappedValue   = IF(IsCustomized=1, OnUnmappedValue,   VALUES(OnUnmappedValue)),
			  LastModifiedDate  = IF(IsCustomized=1, LastModifiedDate,  VALUES(LastModifiedDate))`,
			tableObjectFieldMapping,
			strings.Join(valueParts, ", "),
		)

		result, err := rawDB.ExecContext(ctx, insertSQL, params...)
		if err != nil {
			return apperrors.NewInternalError("failed to seed field mappings", err)
		}
		inserted, _ := result.RowsAffected()
		log.Printf("✅ Field mappings upserted (%d rows affected, %d total, %v)",
			inserted, len(valueParts), time.Since(start).Round(time.Millisecond))
	} else {
		log.Println("   ↩ No field mappings to seed (reconcile-only pass)")
	}

	for k, sourceFields := range sourceFieldsByTuple {
		if err := reconcileTupleSoftDelete(ctx, rawDB, k.SourceObject, k.TargetObject, k.ActionType, owner, sourceFields, now); err != nil {
			return err
		}
	}
	return nil
}

func claimLegacyFieldMappingRowsForOwner(ctx context.Context, rawDB *sql.DB, groups []fieldMappingGroupV2, owner string, now string) error {
	clauses, args := fieldMappingBusinessKeyClauses(groups)
	if len(clauses) == 0 || owner == legacyObjectFieldMappingOwner {
		return nil
	}

	query := fmt.Sprintf(
		`UPDATE %s
		 SET Source = ?, LastModifiedDate = ?
		 WHERE Source = ?
		   AND Direction = 'legacy'
		   AND IsCustomized = 0
		   AND (%s)`,
		tableObjectFieldMapping,
		strings.Join(clauses, " OR "),
	)
	execArgs := []interface{}{owner, now, legacyObjectFieldMappingOwner}
	execArgs = append(execArgs, args...)

	result, err := rawDB.ExecContext(ctx, query, execArgs...)
	if err != nil {
		return apperrors.NewInternalError("failed to claim legacy field mapping owner rows", err)
	}
	claimed, _ := result.RowsAffected()
	if claimed > 0 {
		log.Printf("   ↪ Claimed %d legacy field mapping row(s) for owner %s", claimed, owner)
	}
	return nil
}

func deactivateStaleTenantFieldMappingRowsForOwner(ctx context.Context, rawDB *sql.DB, groups []fieldMappingGroupV2, owner string, now string) error {
	if !strings.HasPrefix(owner, "tenant:") {
		return nil
	}
	clauses, args := fieldMappingTupleClauses(groups)
	if len(clauses) == 0 {
		return nil
	}

	query := fmt.Sprintf(
		`UPDATE %s
		 SET IsDeleted = 1, LastModifiedDate = ?
		 WHERE Source LIKE 'tenant:%%'
		   AND Source <> ?
		   AND Direction = 'legacy'
		   AND IsCustomized = 0
		   AND IsDeleted = 0
		   AND (%s)`,
		tableObjectFieldMapping,
		strings.Join(clauses, " OR "),
	)
	execArgs := []interface{}{now, owner}
	execArgs = append(execArgs, args...)

	result, err := rawDB.ExecContext(ctx, query, execArgs...)
	if err != nil {
		return apperrors.NewInternalError("failed to deactivate stale tenant field mapping owner rows", err)
	}
	deactivated, _ := result.RowsAffected()
	if deactivated > 0 {
		log.Printf("   🧹 Soft-deleted %d stale tenant field mapping row(s) for owner %s", deactivated, owner)
	}
	return nil
}

func fieldMappingTupleClauses(groups []fieldMappingGroupV2) ([]string, []interface{}) {
	var clauses []string
	var args []interface{}
	seen := map[string]struct{}{}
	for _, group := range groups {
		key := group.SourceObject + "\x00" + group.TargetObject + "\x00" + group.ActionType
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		clauses = append(clauses, "(SourceObject = ? AND TargetObject = ? AND ActionType = ?)")
		args = append(args, group.SourceObject, group.TargetObject, group.ActionType)
	}
	return clauses, args
}

func fieldMappingBusinessKeyClauses(groups []fieldMappingGroupV2) ([]string, []interface{}) {
	var clauses []string
	var args []interface{}
	for _, group := range groups {
		if len(group.Mappings) == 0 {
			clauses = append(clauses, "(SourceObject = ? AND TargetObject = ? AND ActionType = ?)")
			args = append(args, group.SourceObject, group.TargetObject, group.ActionType)
			continue
		}
		sourceFields := make([]string, 0, len(group.Mappings)+len(group.RetiredSourceFields))
		seen := map[string]bool{}
		for _, mapping := range group.Mappings {
			sourceField := strings.TrimSpace(mapping.SourceField)
			if seen[sourceField] {
				continue
			}
			seen[sourceField] = true
			sourceFields = append(sourceFields, sourceField)
		}
		for _, sourceField := range group.RetiredSourceFields {
			sourceField = strings.TrimSpace(sourceField)
			if sourceField == "" || seen[sourceField] {
				continue
			}
			seen[sourceField] = true
			sourceFields = append(sourceFields, sourceField)
		}
		if len(sourceFields) == 0 {
			continue
		}

		placeholders := strings.TrimRight(strings.Repeat("?,", len(sourceFields)), ",")
		clauses = append(clauses,
			fmt.Sprintf("(SourceObject = ? AND TargetObject = ? AND ActionType = ? AND SourceField IN (%s))", placeholders))
		args = append(args, group.SourceObject, group.TargetObject, group.ActionType)
		for _, sourceField := range sourceFields {
			args = append(args, sourceField)
		}
	}
	return clauses, args
}

func fieldMappingID(owner string, group fieldMappingGroupV2, mapping fieldMappingDefV2) string {
	id := fmt.Sprintf("ofm_%s_%s_%s_%s_%s_%s",
		strings.ToLower(owner),
		strings.ToLower(group.SourceObject),
		strings.ToLower(group.TargetObject),
		strings.ToLower(group.ActionType),
		strings.ToLower(mapping.SourceField),
		strings.ToLower(mapping.TargetField),
	)
	if len(id) > 255 {
		return utils.GenerateID()
	}
	return id
}

func fieldMappingIsCustomized(group fieldMappingGroupV2, mapping fieldMappingDefV2) bool {
	if mapping.IsCustomized != nil {
		return *mapping.IsCustomized
	}
	return group.IsCustomized
}

// reconcileTupleSoftDelete soft-deletes every non-customized, non-deleted row
// in _System_ObjectFieldMapping for the given tuple whose business-key
// SourceField is not in keepSourceFields. Relocated verbatim from
// internal/bootstrap.
func reconcileTupleSoftDelete(ctx context.Context, rawDB *sql.DB, sourceObject, targetObject, actionType, owner string, keepSourceFields []string, now string) error {
	var query string
	args := []interface{}{now, sourceObject, targetObject, actionType, owner}
	if len(keepSourceFields) == 0 {
		query = fmt.Sprintf(
			`UPDATE %s
			 SET IsDeleted = 1, LastModifiedDate = ?
			 WHERE SourceObject = ? AND TargetObject = ? AND ActionType = ?
			   AND Source = ?
			   AND Direction = 'legacy'
			   AND IsCustomized = 0 AND IsDeleted = 0`,
			tableObjectFieldMapping,
		)
	} else {
		placeholders := strings.Repeat("?,", len(keepSourceFields))
		placeholders = placeholders[:len(placeholders)-1]
		query = fmt.Sprintf(
			`UPDATE %s
			 SET IsDeleted = 1, LastModifiedDate = ?
			 WHERE SourceObject = ? AND TargetObject = ? AND ActionType = ?
			   AND Source = ?
			   AND Direction = 'legacy'
			   AND IsCustomized = 0 AND IsDeleted = 0
			   AND SourceField NOT IN (%s)`,
			tableObjectFieldMapping, placeholders,
		)
		for _, sourceField := range keepSourceFields {
			args = append(args, sourceField)
		}
	}
	res, err := rawDB.ExecContext(ctx, query, args...)
	if err != nil {
		return apperrors.NewInternalError(
			fmt.Sprintf("reconcile soft-delete for %s→%s(%s)", sourceObject, targetObject, actionType), err)
	}
	affected, _ := res.RowsAffected()
	if affected > 0 {
		log.Printf("   🧹 Soft-deleted %d stale row(s) for %s→%s(%s)",
			affected, sourceObject, targetObject, actionType)
	}
	return nil
}

func adoptLegacyStandardFieldMappingRows(ctx context.Context, rawDB *sql.DB, groups []fieldMappingGroupV2, owner string) error {
	owner = strings.TrimSpace(owner)
	if owner == "" || owner == standardObjectFieldMappingOwner {
		return nil
	}
	for _, group := range groups {
		for _, mapping := range group.Mappings {
			sourceField := strings.TrimSpace(mapping.SourceField)
			if sourceField == "" {
				continue
			}
			var ownedCount int
			if err := rawDB.QueryRowContext(ctx, fmt.Sprintf(`
SELECT COUNT(*)
FROM %s
WHERE Source = ? AND SourceObject = ? AND TargetObject = ? AND ActionType = ? AND SourceField = ?
  AND Direction = 'legacy'
LIMIT 1`, tableObjectFieldMapping),
				owner, group.SourceObject, group.TargetObject, group.ActionType, sourceField,
			).Scan(&ownedCount); err != nil {
				return apperrors.NewInternalError("check owner field mapping adoption target", err)
			}
			if ownedCount > 0 {
				continue
			}
			if _, err := rawDB.ExecContext(ctx, fmt.Sprintf(`
UPDATE %s
   SET Source = ?, LastModifiedDate = ?
 WHERE Source = ?
   AND SourceObject = ?
   AND TargetObject = ?
   AND ActionType = ?
   AND SourceField = ?
   AND Direction = 'legacy'
   AND IsCustomized = 0
   AND IsDeleted = 0`, tableObjectFieldMapping),
				owner, time.Now().Format("2006-01-02 15:04:05"),
				standardObjectFieldMappingOwner,
				group.SourceObject, group.TargetObject, group.ActionType, sourceField,
			); err != nil {
				return apperrors.NewInternalError("adopt legacy standard field mapping row", err)
			}
		}
	}
	return nil
}