package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/nexuscrm/backend/internal/domain/schema"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/fieldcapabilities"
	"github.com/nexuscrm/shared/pkg/models"
)

// FieldWithContext removed (moved to domain/schema)

// SaveObjectMetadata upserts object metadata into _System_Object
func (r *SchemaRepository) SaveObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error {
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	// Determine Object ID if not set
	if obj.ID == "" {
		obj.ID = schema.GenerateObjectID(obj.APIName)
	}

	values, err := r.prepareObjectDBMap(obj)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to prepare object values for %s", obj.APIName), err)
	}

	// Prepare Update Map (excluding CreatedDate and ID)
	updates := make(map[string]interface{})
	for k, v := range values {
		if k != constants.FieldID && k != constants.FieldCreatedDate {
			updates[k] = v
		}
	}
	updates[constants.FieldLastModifiedDate] = time.Now()

	qr := query.Insert(constants.TableObject, values).
		OnDuplicateKeyUpdate(updates).
		Build()

	_, err = exec.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// InsertObjectMetadata inserts object metadata into _System_Object (Strict - Fails on Unique Constraint)
func (r *SchemaRepository) InsertObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error {
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	// Determine Object ID if not set
	if obj.ID == "" {
		obj.ID = schema.GenerateObjectID(obj.APIName)
	}

	values, err := r.prepareObjectDBMap(obj)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to prepare object values for %s", obj.APIName), err)
	}

	qr := query.Insert(constants.TableObject, values).Build()

	_, err = exec.ExecContext(context.Background(), qr.SQL, qr.Params...)
	return err
}

// SaveFieldMetadataWithIDs upserts field metadata with explicit IDs.
// objectAPIName is needed to compute object-specific intrinsic capabilities.
func (r *SchemaRepository) SaveFieldMetadataWithIDs(ctx context.Context, field *models.FieldMetadata, objectID string, objectAPIName string, fieldID string, tx interface{}) error {
	// Compute intrinsic capabilities before persisting
	field.Createable, field.Updateable = fieldcapabilities.ComputeIntrinsic(objectAPIName, field)
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	values, err := r.prepareFieldDBMap(field, objectID, fieldID)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to prepare field values for %s", field.APIName), err)
	}

	// Prepare Update Map — only include fields that should change on upsert.
	// Skip Label if it is empty to avoid overwriting existing labels with blanks.
	updates := make(map[string]interface{})
	for k, v := range values {
		if k == constants.FieldID || k == constants.FieldCreatedDate || k == constants.FieldObjectID {
			continue
		}
		if k == constants.FieldSysField_Label {
			if s, ok := v.(string); ok && s == "" {
				continue
			}
		}
		updates[k] = v
	}
	updates[constants.FieldLastModifiedDate] = time.Now()
	// Mark as user-customized so bootstrap reconcileFieldLabels() won't overwrite.
	// This path is called from admin API / field editor, not from bootstrap.
	updates["IsCustomized"] = 1

	qr := query.Insert(constants.TableField, values).
		OnDuplicateKeyUpdate(updates).
		Build()

	_, err = exec.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// BatchSaveObjectMetadata inserts multiple objects in a single statement
func (r *SchemaRepository) BatchSaveObjectMetadata(ctx context.Context, objs []*models.ObjectMetadata, tx interface{}) error {
	if len(objs) == 0 {
		return nil
	}
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	// Build map list for Batch Insert
	var batchValues []map[string]interface{}

	for _, obj := range objs {
		// Ensure ID is set
		if obj.ID == "" {
			obj.ID = schema.GenerateObjectID(obj.APIName)
		}

		values, err := r.prepareObjectDBMap(obj)
		if err != nil {
			return err
		}
		batchValues = append(batchValues, values)
	}

	// Prepare Update Map (Generic fields to update on duplicate)
	// For Batch Upsert, the builder needs to know which columns to update.
	// We map the Logical Fields we want to update.
	// The values here are dummy values or raw SQL "VALUES(col)".
	// Use query.Raw to specify VALUES(physical_name) for ON DUPLICATE KEY UPDATE.

	updates := make(map[string]interface{})
	updatableFields := []string{
		constants.FieldSysObject_Label, constants.FieldSysObject_PluralLabel,
		constants.FieldSysObject_Icon, constants.FieldSysObject_Description,
		constants.FieldSysObject_IsCustom, constants.FieldSysObject_Searchable,
		constants.FieldSysObject_AppID,
		constants.FieldSysObject_ListFields, constants.FieldSysObject_PathField,
		constants.FieldSysObject_ThemeColor, constants.FieldSysObject_TableType,
		constants.FieldSysObject_SharingModel,
		constants.FieldSysObject_EnableHierarchySharing,
		constants.FieldSysObject_RequiredWritePermission,
		constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_DeleteMode,
		constants.FieldLastModifiedDate,
	}

	r.configureObjectBatchUpdates(updates, updatableFields)

	qr := query.Insert(constants.TableObject, nil).
		ValuesBatch(batchValues).
		OnDuplicateKeyUpdate(updates).
		Build()

	_, err := exec.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// Helper to configure batch updates
func (r *SchemaRepository) configureBatchUpdates(updates map[string]interface{}, fields []string) {
	for _, field := range fields {
		// Target physical name is identical to API name under Salesforce Native model
		physical := field

		// Special case: LastModifiedDate -> NOW()
		if field == constants.FieldLastModifiedDate {
			updates[field] = query.Raw("NOW()")
			continue
		}

		updates[field] = query.Raw(fmt.Sprintf("VALUES(`%s`)", physical))
	}
}

func (r *SchemaRepository) configureObjectBatchUpdates(updates map[string]interface{}, fields []string) {
	guarded := objectBatchCustomizedGuardFields()
	for _, field := range fields {
		physical := field
		if field == constants.FieldLastModifiedDate {
			updates[field] = query.Raw(fmt.Sprintf(
				"IF(`%s` = 0 OR `%s` IS NULL, NOW(), `%s`)",
				constants.FieldSysObject_IsCustomized,
				constants.FieldSysObject_IsCustomized,
				physical,
			))
			continue
		}
		if _, ok := guarded[field]; ok {
			updates[field] = query.Raw(fmt.Sprintf(
				"IF(`%s` = 0 OR `%s` IS NULL, VALUES(`%s`), `%s`)",
				constants.FieldSysObject_IsCustomized,
				constants.FieldSysObject_IsCustomized,
				physical,
				physical,
			))
			continue
		}
		updates[field] = query.Raw(fmt.Sprintf("VALUES(`%s`)", physical))
	}
}

func objectBatchCustomizedGuardFields() map[string]struct{} {
	// Object customization is currently row-scoped. Once an admin update marks
	// the row customized, batch seed re-apply must not become a second author
	// for mutable object metadata. Shape/classification columns that heal
	// physical storage, such as IsCustom and TableType, stay unguarded.
	return map[string]struct{}{
		constants.FieldSysObject_Label:                   {},
		constants.FieldSysObject_PluralLabel:             {},
		constants.FieldSysObject_Icon:                    {},
		constants.FieldSysObject_Description:             {},
		constants.FieldSysObject_Searchable:              {},
		constants.FieldSysObject_AppID:                   {},
		constants.FieldSysObject_ListFields:              {},
		constants.FieldSysObject_PathField:               {},
		constants.FieldSysObject_ThemeColor:              {},
		constants.FieldSysObject_SharingModel:            {},
		constants.FieldSysObject_EnableHierarchySharing:  {},
		constants.FieldSysObject_RequiredWritePermission: {},
		constants.FieldSysObject_RequiredReadPermission:  {},
		constants.FieldSysObject_AccessPolicy:            {},
		constants.FieldSysObject_DeleteMode:              {},
	}
}

// configureBatchUpdatesGuarded behaves like configureBatchUpdates but wraps each
// column listed in `guarded` so the ON DUPLICATE KEY UPDATE only writes the new
// value when the existing row is not admin-customized (IsCustomized=0 or NULL).
// Customized rows keep their current value, and VerifyStandardFieldSpecDrift
// surfaces the resulting spec-vs-live divergence for operator review.
func (r *SchemaRepository) configureBatchUpdatesGuarded(updates map[string]interface{}, fields, guarded []string) {
	guardedSet := make(map[string]struct{}, len(guarded))
	for _, g := range guarded {
		guardedSet[g] = struct{}{}
	}
	for _, field := range fields {
		physical := field
		if field == constants.FieldLastModifiedDate {
			updates[field] = query.Raw("NOW()")
			continue
		}
		if _, ok := guardedSet[field]; ok {
			updates[field] = query.Raw(fmt.Sprintf(
				"IF(`%s` = 0 OR `%s` IS NULL, VALUES(`%s`), `%s`)",
				constants.ColSysField_IsCustomized,
				constants.ColSysField_IsCustomized,
				physical, physical,
			))
			continue
		}
		updates[field] = query.Raw(fmt.Sprintf("VALUES(`%s`)", physical))
	}
}

// BatchSaveFieldMetadata inserts multiple fields in a single statement
func (r *SchemaRepository) BatchSaveFieldMetadata(ctx context.Context, fields []schema.FieldWithContext, tx interface{}) error {
	if len(fields) == 0 {
		return nil
	}
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	// Process in batches of 50
	batchSize := 50
	for i := 0; i < len(fields); i += batchSize {
		end := i + batchSize
		if end > len(fields) {
			end = len(fields)
		}
		batch := fields[i:end]

		var batchValues []map[string]interface{}
		for _, fc := range batch {
			// Ensure IDs are set
			objectID := fc.ObjectID
			if objectID == "" {
				return apperrors.NewValidationError("ObjectID", fmt.Sprintf("ObjectID is required for field %s.%s — caller must resolve from _System_Object", fc.TableName, fc.ColumnDefinition.Name))
			}
			fieldID := fc.FieldID
			if fieldID == "" {
				fieldID = schema.GenerateFieldID(fc.TableName, fc.ColumnDefinition.Name)
			}

			var fieldMetadata *models.FieldMetadata

			if fc.Field != nil {
				// Use provided field metadata (preserves Logical APIName even if PhysicalName is hashed)
				fieldMetadata = fc.Field
			} else {
				fieldMetadata = r.buildFieldMetadataFromColumn(fc.ColumnDefinition)
			}

			// Compute intrinsic capabilities before persisting
			fieldMetadata.Createable, fieldMetadata.Updateable = fieldcapabilities.ComputeIntrinsic(fc.TableName, fieldMetadata)

			values, err := r.prepareFieldDBMap(fieldMetadata, objectID, fieldID)
			if err != nil {
				return err
			}
			batchValues = append(batchValues, values)
		}

		updates := make(map[string]interface{})
		// NOTE: Label is intentionally excluded from updatableFields to prevent
		// overwriting user-customized labels during schema reconciliation (#355).
		// Labels are only set on INSERT (new fields), never overwritten on UPDATE.
		updatableFields := []string{
			constants.FieldSysField_Type,
			constants.FieldSysField_Required, constants.FieldSysField_IsUnique,
			constants.FieldSysField_DefaultValue, constants.FieldSysField_HelpText,
			constants.FieldSysField_IsSystem, constants.FieldSysField_IsNameField,
			constants.FieldSysField_Createable, constants.FieldSysField_Updateable,
			constants.FieldSysField_Options, constants.FieldSysField_MinLength,
			constants.FieldSysField_MaxLength, constants.FieldReferenceTo,
			constants.FieldSysField_Formula, constants.FieldSysField_ReturnType,
			constants.FieldSysField_FormulaMode,
			constants.FieldSysField_RollupConfig, constants.FieldSysField_IsMasterDetail,
			constants.FieldSysField_IsPolymorphic, constants.FieldSysField_DeleteRule,
			constants.FieldSysField_RelationshipName, constants.ColSysField_PhysicalName,
			// Issue #1582 RFC v1.6 — Source/IsCompound/ComposedFrom removed.
			// Provenance is derived at read time by fieldprovenance.ClassifySource;
			// there is no stored column to overwrite on pack re-apply.
			constants.FieldLastModifiedDate,
		}
		// Spec-controlled columns that admins can override via the field-edit UI
		// (which writes through SaveFieldMetadataWithIDs and sets IsCustomized=1).
		// Intrinsic/server-managed columns (IsSystem, Createable, Updateable,
		// PhysicalName) still auto-heal from schema shape; admin-owned metadata
		// columns stay untouched on pack re-apply.
		guardedFields := []string{
			constants.FieldSysField_Type,
			constants.FieldSysField_Required,
			constants.FieldSysField_IsUnique,
			constants.FieldSysField_DefaultValue,
			constants.FieldSysField_HelpText,
			constants.FieldSysField_IsNameField,
			constants.FieldSysField_Options,
			constants.FieldSysField_MinLength,
			constants.FieldSysField_MaxLength,
			constants.FieldReferenceTo,
			constants.FieldSysField_Formula,
			constants.FieldSysField_ReturnType,
			constants.FieldSysField_FormulaMode,
			constants.FieldSysField_RollupConfig,
			constants.FieldSysField_IsMasterDetail,
			constants.FieldSysField_IsPolymorphic,
			constants.FieldSysField_DeleteRule,
			constants.FieldSysField_RelationshipName,
		}
		r.configureBatchUpdatesGuarded(updates, updatableFields, guardedFields)

		qr := query.Insert(constants.TableField, nil).
			ValuesBatch(batchValues).
			OnDuplicateKeyUpdate(updates).
			Build()

		if _, err := exec.ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
			return apperrors.NewInternalError("batch field insert failed", err)
		}
	}

	return nil
}

// PrepareFieldForBatch converts a column definition to FieldWithContext
func (r *SchemaRepository) PrepareFieldForBatch(tableName string, col schema.ColumnDefinition) schema.FieldWithContext {
	return schema.FieldWithContext{
		ColumnDefinition: col,
		TableName:        tableName,
		// IDs will be generated if missing inside BatchSaveFieldMetadata or caller must provide
	}
}

// GetTableRegistry retrieves all registered tables
func (r *SchemaRepository) GetTableRegistry(ctx context.Context) ([]*schema.TableRegistryItem, error) {
	qr := query.From(constants.TableTable).
		Select([]string{
			constants.ColID, constants.FieldSysTable_TableName, constants.FieldSysTable_TableType,
			constants.FieldSysTable_SchemaVersion, constants.ColCreatedDate,
			constants.FieldSysTable_Category, constants.FieldSysTable_Description,
			constants.FieldSysTable_IsManaged, constants.FieldSysTable_CreatedBy,
			constants.ColLastModifiedDate,
		}).
		Where(constants.FieldIsDeleted, false).
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var items []*schema.TableRegistryItem
	for rows.Next() {
		var t schema.TableRegistryItem
		var description sql.NullString
		var category sql.NullString
		var schemaVersion sql.NullString

		err := rows.Scan(
			&t.ID, &t.TableName, &t.TableType, &category, &description,
			&t.IsManaged, &schemaVersion, &t.CreatedBy, &t.CreatedDate, &t.LastModifiedDate,
		)
		if err != nil {
			return nil, err
		}

		if description.Valid {
			t.Description = description.String
		}
		if category.Valid {
			t.Category = category.String
		}
		if schemaVersion.Valid {
			t.SchemaVersion = schemaVersion.String
		}

		items = append(items, &t)
	}

	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating table registry", err)
	}

	return items, nil
}