package metadata

import (
	"context"
	"fmt"
	"log"
	"regexp"
	"strings"
	"time"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	domainSchema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	sharedEvents "github.com/nexuscrm/shared/pkg/events"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

// CreateField creates a new field for an object
func (ms *MetadataService) CreateField(ctx context.Context, objectAPIName string, field *models.FieldMetadata) error {
	ms.mu.Lock()
	defer ms.mu.Unlock()

	if field == nil {
		return apperrors.NewValidationError("field", "field metadata is required")
	}
	if field.TrackHistory {
		return apperrors.NewValidationError(constants.FieldSysField_TrackHistory, "new fields must be created with trackHistory disabled; enable it in a separate committed transition")
	}
	// Interactive field identity is an incarnation, not the API-name business
	// key. The persistence layer preserves this value only on first insert.
	mintInteractiveFieldIncarnation(field)

	normalizeCreatedFormulaField(field)

	// Validate API Name format
	// Allow alphanumeric and underscores, must start with letter/underscore
	validName := regexp.MustCompile(`^[a-zA-Z_][a-zA-Z0-9_]*$`)
	if !validName.MatchString(field.APIName) {
		return apperrors.NewValidationError("api_name", fmt.Sprintf("invalid field API name '%s': must start with letter or underscore and contain only alphanumeric characters", field.APIName))
	}

	// Master-Detail Validation/Enforcement
	if field.IsMasterDetail {
		if field.Type != constants.FieldTypeReference {
			return apperrors.NewValidationError(constants.FieldType, "Master-Detail relationship is only valid for Lookup fields")
		}
		// Enforce defaults for Master-Detail
		field.Required = true
		cascade := constants.DeleteRuleCascade
		field.DeleteRule = &cascade
	}

	// Validate System Field restrictions
	if constants.IsSystemField(field.APIName) {
		return apperrors.NewValidationError(field.APIName, "cannot create field with reserved system name")
	}

	// Resolve polymorphic ownership through the single SSoT before SQL type
	// resolution, LogicalType stamping, and persistence. Authored multi-target
	// lookups normalize to reference so the persisted row round-trips as
	// polymorphic after schema reload (LifecyclePersistedRead infers only for
	// Type=reference rows unless the bootstrap backfill has stamped the flag).
	if authoredSem := models.ResolveFieldRelationshipSemantics(field, models.LifecycleAuthoredWrite); authoredSem.IsPolymorphic {
		field.IsPolymorphic = true
		field.Type = authoredSem.CanonicalType
	}

	// Validate Picklist fields require options
	if field.Type == constants.FieldTypePicklist && len(field.Options) == 0 {
		return apperrors.NewValidationError("options", "Picklist fields require at least one option")
	}

	// Validate Lookup fields require reference_to
	if field.Type == constants.FieldTypeReference && len(field.ReferenceTo) == 0 {
		return apperrors.NewValidationError("reference_to", "Lookup fields require a referenced object")
	}

	// Get the object to ensure it exists
	obj, err := ms.repo.GetSchemaByAPIName(ctx, objectAPIName)
	if err != nil || obj == nil {
		return apperrors.NewNotFoundError("Object", objectAPIName)
	}
	objectAPIName = obj.APIName

	if err := ValidateFormulaAuthoringContract(objectAPIName, field.APIName, field.Type, field.Formula); err != nil {
		return err
	}

	// Validate Field Metadata
	if ms.validationSvc != nil {
		if err := ms.validationSvc.ValidateFieldMetadata(field); err != nil {
			return err
		}
	}
	if err := validateSiblingMaxFieldForObject(objectWithFieldCandidate(obj, field), field); err != nil {
		return err
	}
	if err := ms.validateWriteMaterializedFormulaField(ctx, obj, field); err != nil {
		return err
	}

	// Validate Max Master-Detail Usage (Limit 2)
	if field.IsMasterDetail {
		if err := ms.ValidateMaxMasterDetailFields(obj, ""); err != nil {
			return err
		}
	}

	// Check if field already exists
	for _, f := range obj.Fields {
		if strings.EqualFold(f.APIName, field.APIName) {
			return apperrors.NewConflictError("FieldMetadata", "api_name", field.APIName)
		}
	}

	// Validate Formula fields
	if field.ProjectionConfig != nil {
		return apperrors.NewValidationError("projection", "field projection config is internal and can only be declared by standard metadata")
	}
	if field.Type == constants.FieldTypeFormula {
		if field.Formula == nil || strings.TrimSpace(*field.Formula) == "" {
			return apperrors.NewValidationError("formula", "Formula fields require a formula expression")
		}
		if field.ReturnType == nil || strings.TrimSpace(string(*field.ReturnType)) == "" {
			return apperrors.NewValidationError("return_type", "Formula fields require a valid return_type")
		}
		// Formula syntax is validated by the /validate endpoint (DSL parser) before creation.
		// The old govaluate-based validator doesn't support cross-table refs or aggregates,
		// so we skip server-side re-validation here. The DSL ViewBuilder will catch
		// any remaining issues when rebuilding the View.
	}

	// Map to ColumnDefinition
	var relationshipName string
	if field.RelationshipName != nil {
		relationshipName = *field.RelationshipName
	}
	var childRelationshipName string
	if field.ChildRelationshipName != nil {
		childRelationshipName = *field.ChildRelationshipName
	}

	sqlColumnType, canonicalRollupConfig, err := ms.resolveFieldStorageType(ctx, objectAPIName, field)
	if err != nil {
		return apperrors.NewValidationError("rollupConfig", err.Error())
	}
	if canonicalRollupConfig != nil {
		field.RollupConfig = canonicalRollupConfig

		// Validate the rollup graph (cycle/depth) before any DDL or
		// metadata write. Temporarily attach the field to the object
		// so the graph validator sees the proposed state.
		tempField := *field
		objWithField := *obj
		objWithField.Fields = append(objWithField.Fields, tempField)
		if err := ms.validateRollupGraphForObject(ctx, &objWithField); err != nil {
			return err
		}
	}

	colDef := domainSchema.ColumnDefinition{
		MetadataID:            field.ID,
		Name:                  field.APIName,
		Label:                 field.Label,
		Type:                  sqlColumnType,
		LogicalType:           string(field.Type),
		Nullable:              fieldPhysicalNullable(field),
		Unique:                field.IsUnique,
		IsMasterDetail:        field.IsMasterDetail,
		WritableReferenceTo:   append([]string(nil), field.WritableReferenceTo...),
		RelationshipName:      relationshipName,
		ChildRelationshipName: childRelationshipName,
		RollupConfig:          domainSchema.CloneRollupConfig(field.RollupConfig),
		WriteProtectionPolicy: field.WriteProtectionPolicy,
		ReadProtectionPolicy:  field.ReadProtectionPolicy,
		SiblingMaxParentField: siblingMaxColumnParentField(field),
		// Provenance (Source) is derived at read time by
		// fieldprovenance.ClassifySource from the __c suffix rule; no need
		// to stamp a stored column. Issue #1582 RFC v1.6.
	}
	if field.DefaultValue != nil {
		colDef.Default = "'" + *field.DefaultValue + "'"
	}
	colDef.ReferenceTo = append([]string(nil), field.ReferenceTo...)
	if field.Type == constants.FieldTypeReference && field.IsPolymorphic {
		colDef.IsPolymorphic = true
	}
	if field.Formula != nil {
		colDef.Formula = *field.Formula
	}
	if field.ReturnType != nil {
		colDef.ReturnType = string(*field.ReturnType)
	}
	if field.FormulaMode != nil {
		colDef.FormulaMode = *field.FormulaMode
	}
	if field.FormulaStorageMode != nil {
		colDef.FormulaStorageMode = *field.FormulaStorageMode
	}
	if field.Validator != nil {
		colDef.Validator = *field.Validator
	}
	if field.ValidatorConfig != nil {
		colDef.ValidatorConfig = *field.ValidatorConfig
	}
	if field.DeleteRule != nil {
		switch constants.NormalizeDeleteRule(*field.DeleteRule) {
		case constants.DeleteRuleCascade:
			colDef.OnDelete = "CASCADE"
		case constants.DeleteRuleSetNull:
			colDef.OnDelete = "SET NULL"
		default:
			colDef.OnDelete = "RESTRICT"
		}
	}
	if len(field.Options) > 0 {
		colDef.Options = field.Options
	}

	// For active formula fields, skip physical column creation — the TiDB View computes them.
	// Only write metadata to _System_Field and trigger view rebuild via event.
	isActiveFormula := field != nil && field.Type == constants.FieldTypeFormula && formulastorage.IsActiveComputedField(field)

	// Admit the field into layouts before committing field metadata. If layout
	// admission fails, returning here keeps REST finalize/permission side
	// effects aligned with durable field state (no created field to finalize).
	log.Printf("📐 Adding %s to layout (isActiveFormula=%v)", field.APIName, isActiveFormula)
	var layoutAdmissionRollbacks []func() error
	rollbackLayoutAdmissions := func(cause error) error {
		var rollbackErrs []error
		for i := len(layoutAdmissionRollbacks) - 1; i >= 0; i-- {
			rollback := layoutAdmissionRollbacks[i]
			if rollback == nil {
				continue
			}
			if rollbackErr := rollback(); rollbackErr != nil {
				log.Printf("⚠️ Failed to roll back layout admission for %s.%s: %v", objectAPIName, field.APIName, rollbackErr)
				rollbackErrs = append(rollbackErrs, rollbackErr)
			}
		}
		if len(rollbackErrs) > 0 {
			return fieldCreateFailureWithLayoutRollbackFailures(cause, rollbackErrs...)
		}
		return cause
	}
	rollbackLayoutAdmission, err := ms.addFieldToLayoutWithRollback(ctx, objectAPIName, field)
	if err != nil {
		return err
	}
	layoutAdmissionRollbacks = append(layoutAdmissionRollbacks, rollbackLayoutAdmission)
	rollbackRelatedListAdmission, err := ms.addRelationshipFieldToParentLayoutsWithRollback(ctx, obj, field)
	if err != nil {
		return rollbackLayoutAdmissions(err)
	}
	layoutAdmissionRollbacks = append(layoutAdmissionRollbacks, rollbackRelatedListAdmission)
	log.Printf("✅ Layout done for %s", field.APIName)
	rollbackLayoutOnFieldCreateFailure := func(cause error) error {
		return rollbackLayoutAdmissions(cause)
	}

	if !isActiveFormula {
		log.Printf("📦 Adding physical column %s.%s (type=%s)", objectAPIName, field.APIName, field.Type)
		if err := ms.schemaMgr.AddColumn(objectAPIName, colDef); err != nil {
			return rollbackLayoutOnFieldCreateFailure(apperrors.NewInternalError("failed to add column to schema", err))
		}
		if field.Required != !colDef.Nullable {
			if err := ms.schemaMgr.SaveFieldMetadataWithIDs(ctx, field, obj.ID, obj.APIName, field.ID, nil); err != nil {
				return rollbackLayoutOnFieldCreateFailure(apperrors.NewInternalError("failed to save field metadata", err))
			}
		}
		log.Printf("✅ Physical column %s.%s added", objectAPIName, field.APIName)

		// A lookup / master-detail field's FK column backs related-list counts
		// and joins; index it so those do not full-scan. Best-effort; generic
		// via constants.IsReferenceType (no SObject-name branch).
		if !field.IsSystem && constants.IsReferenceType(constants.NormalizeFieldType(string(field.Type))) {
			// Index the exact physical column AddColumn just created — colDef.Name
			// (= field.APIName). PhysicalName is a read-path alias that AddColumn
			// does not honor at create time, so indexing it could target a column
			// that does not exist and silently skip the index.
			if err := ms.schemaMgr.EnsureColumnIndex(objectAPIName, colDef.Name); err != nil {
				log.Printf("⚠️ Failed to index relationship FK %s.%s: %v", objectAPIName, colDef.Name, err)
			}
		}
	} else {
		log.Printf("⚡ Active formula %s.%s — registering metadata only (no physical column)", objectAPIName, field.APIName)
		if err := ms.schemaMgr.RegisterFieldOnly(objectAPIName, colDef); err != nil {
			return rollbackLayoutOnFieldCreateFailure(apperrors.NewInternalError("failed to register formula field metadata", err))
		}
		if field.Required != !colDef.Nullable {
			if err := ms.schemaMgr.SaveFieldMetadataWithIDs(ctx, field, obj.ID, obj.APIName, field.ID, nil); err != nil {
				return rollbackLayoutOnFieldCreateFailure(apperrors.NewInternalError("failed to save formula field metadata", err))
			}
		}
		log.Printf("✅ Formula field %s.%s metadata registered", objectAPIName, field.APIName)
	}

	// For Polymorphic Lookups, create a secondary column for Object Type
	if field.IsPolymorphic {
		typeColDef := domainSchema.PolymorphicDiscriminatorColumn(colDef)
		typeColDef.MetadataID = GenerateID()
		if err := ms.schemaMgr.AddColumn(objectAPIName, typeColDef); err != nil {
			log.Printf("🔥 Failed to add polymorphic type column %s. Rolling back primary column...", typeColDef.Name)
			if dropErr := ms.schemaMgr.DropColumn(objectAPIName, field.APIName); dropErr != nil {
				log.Printf("⚠️ Rollback failed for column %s: %v", field.APIName, dropErr)
			}
			return rollbackLayoutOnFieldCreateFailure(apperrors.NewInternalError("failed to add type column for polymorphic field", err))
		}
	}

	// For AutoNumber fields, configure the v2 engine: write
	// `_System_Field.AutoNumberFormat` and seed the counter row.
	// Replaces the legacy UpsertAutoNumber call (PRD §7.2 step k).
	//
	// Round-5 Codex V1-P2 / V2-H3: after the row is written we MUST
	// invalidate the engine's MetadataSource snapshot — otherwise the
	// in-memory map continues to report "no autonumber on this
	// object" until the next process restart, silently skipping
	// assignment on every INSERT.
	if field.Type == constants.FieldTypeAutonumber {
		format := "{0}"
		if field.DefaultValue != nil && *field.DefaultValue != "" {
			format = *field.DefaultValue
		}
		// Codex round-8 V2 HIGH: previously a ConfigureAutoNumberField
		// failure was logged-and-swallowed. The field row was already
		// inserted by Create() above, so the admin saw "field created
		// OK" but the counter row was not seeded — the first INSERT
		// on the object would die with ErrCounterMissing. Fail the
		// operation instead so the caller sees the error and can
		// retry; the schema row alone is not enough to consider the
		// field created.
		if err := ms.repo.ConfigureAutoNumberField(ctx, objectAPIName, field.APIName, format, 1); err != nil {
			return apperrors.NewInternalError(
				fmt.Sprintf("configure auto-number field %s.%s", objectAPIName, field.APIName), err)
		}
		// Invalidator failure stays non-fatal: it's a cache nudge, not
		// a data-integrity step. A subsequent `/admin/autonumber/
		// refresh-metadata` (or process restart) picks up the new
		// field. The counter-seed above is the data-integrity gate and
		// IS fatal (Codex round-8 V2 HIGH).
		if ms.autoNumberInvalidator != nil {
			if err := ms.autoNumberInvalidator.InvalidateObject(ctx, objectAPIName); err != nil {
				log.Printf("⚠️ Failed to invalidate autonumber metadata for %s.%s: %v",
					objectAPIName, field.APIName, err)
			}
		}
	}

	log.Printf("🔄 Invalidating cache for %s", field.APIName)
	ms.invalidateCacheLocked()
	log.Printf("✅ Cache invalidated for %s", field.APIName)

	// Publish Event
	ms.publishMetadataEvent(ctx, sharedEvents.FieldCreated, sharedEvents.SchemaEventPayload{
		EventType:     sharedEvents.FieldCreated,
		ObjectAPIName: objectAPIName,
		FieldAPIName:  field.APIName,
		Timestamp:     time.Now().Format(time.RFC3339),
	})

	return nil
}

func mintInteractiveFieldIncarnation(field *models.FieldMetadata) {
	if field != nil {
		field.ID = GenerateID()
	}
}