package metadata

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

	domainSchema "github.com/nexuscrm/backend/internal/domain/schema"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	sharedEvents "github.com/nexuscrm/shared/pkg/events"
	"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()

	// Validate Field Metadata
	if ms.validationSvc != nil {
		if err := ms.validationSvc.ValidateFieldMetadata(field); err != nil {
			return err
		}
	}

	// 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")
	}

	// Detect polymorphic lookups early
	if field.Type == constants.FieldTypeReference && len(field.ReferenceTo) > 1 {
		field.IsPolymorphic = true
	}

	// 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

	// 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.Type == constants.FieldTypeFormula {
		if field.Formula == nil || *field.Formula == "" {
			return apperrors.NewValidationError("formula", "Formula fields require a formula expression")
		}
		if field.ReturnType == nil || 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
	}

	colDef := domainSchema.ColumnDefinition{
		Name:             field.APIName,
		Label:            field.Label,
		Type:             ms.schemaMgr.MapFieldTypeToSQL(string(field.Type)),
		LogicalType:      string(field.Type),
		Nullable:         !field.Required,
		Unique:           field.IsUnique,
		IsMasterDetail:   field.IsMasterDetail,
		RelationshipName: relationshipName,
		// 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 = field.ReferenceTo
	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.DeleteRule != nil {
		if *field.DeleteRule == constants.DeleteRuleCascade {
			colDef.OnDelete = "CASCADE"
		} else if *field.DeleteRule == constants.DeleteRuleSetNull {
			colDef.OnDelete = "SET NULL"
		} else {
			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.Type == constants.FieldTypeFormula &&
		field.FormulaMode != nil && *field.FormulaMode == "active" &&
		field.Formula != nil && *field.Formula != ""

	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 apperrors.NewInternalError("failed to add column to schema", err)
		}
		log.Printf("✅ Physical column %s.%s added", objectAPIName, field.APIName)
	} 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 apperrors.NewInternalError("failed to register 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 {
		typeColName := GetPolymorphicTypeColumnName(field.APIName)
		typeColDef := domainSchema.ColumnDefinition{
			Name:        typeColName,
			Type:        "VARCHAR(255)",
			LogicalType: string(constants.FieldTypeString),
			Nullable:    true,
		}
		if err := ms.schemaMgr.AddColumn(objectAPIName, typeColDef); err != nil {
			log.Printf("🔥 Failed to add polymorphic type column %s. Rolling back primary column...", typeColName)
			if dropErr := ms.schemaMgr.DropColumn(objectAPIName, field.APIName); dropErr != nil {
				log.Printf("⚠️ Rollback failed for column %s: %v", field.APIName, dropErr)
			}
			return apperrors.NewInternalError("failed to add type column for polymorphic field", err)
		}
	}

	// Add to default layout (skip for active formulas — they're view-computed, not physical)
	log.Printf("📐 Adding %s to layout (isActiveFormula=%v)", field.APIName, isActiveFormula)
	if !isActiveFormula {
		if err := ms.addFieldToLayout(ctx, objectAPIName, field.APIName); err != nil {
			log.Printf("Warning: Failed to add field %s to layout: %v", field.APIName, err)
		}
	}
	log.Printf("✅ Layout done for %s", field.APIName)

	// 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
}