package records

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

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

const runtimeScopedLookupValidatorName = "runtime_scoped_lookup"

func (ps *PersistenceService) validateRuntimeScopedLookups(
	ctx context.Context,
	schema *models.ObjectMetadata,
	finalRecord models.SObject,
	changedFields models.SObject,
) error {
	if ps == nil || schema == nil {
		return nil
	}

	for i := range schema.Fields {
		field := &schema.Fields[i]
		if field.Validator == nil || !strings.EqualFold(strings.TrimSpace(*field.Validator), runtimeScopedLookupValidatorName) {
			continue
		}

		if changedFields != nil && !runtimeScopedLookupNeedsValidation(*field, changedFields) {
			continue
		}

		lookupID, hasLookupValue, err := runtimeScopedLookupSelectedValue(finalRecord[field.APIName])
		if err != nil {
			return fmt.Errorf("%s: %w", field.APIName, err)
		}
		if !hasLookupValue {
			continue
		}

		if len(field.ReferenceTo) != 1 || strings.TrimSpace(field.ReferenceTo[0]) == "" {
			return fmt.Errorf("%s: runtime_scoped_lookup metadata is invalid", field.APIName)
		}

		cfg, err := models.ParseLookupScopeConfig(field.ValidatorConfig)
		if err != nil {
			return fmt.Errorf("%s: runtime_scoped_lookup metadata is invalid", field.APIName)
		}

		targetObject := strings.TrimSpace(field.ReferenceTo[0])
		targetSchema := ps.metadata.GetSchema(ctx, targetObject)
		if targetSchema == nil {
			return fmt.Errorf("%s: runtime_scoped_lookup target object %q not found", field.APIName, targetObject)
		}

		filterValues := make(map[string]string, len(cfg.FilterMappings))
		for _, mapping := range cfg.FilterMappings {
			if lookupFieldByAPIName(targetSchema, mapping.TargetField) == nil {
				return fmt.Errorf("%s: runtime_scoped_lookup target field %q not found on %s", field.APIName, mapping.TargetField, targetObject)
			}
			value, resolved, resolveErr := ps.resolveRuntimeScopedLookupValue(ctx, schema, finalRecord, mapping)
			if resolveErr != nil {
				return fmt.Errorf("%s: %w", field.APIName, resolveErr)
			}
			if !resolved {
				return fmt.Errorf("%s: scoped lookup requires resolvable scope before save", field.APIName)
			}
			filterValues[mapping.TargetField] = value
		}

		matches, err := ps.runtimeScopedLookupValueMatches(ctx, targetSchema, lookupID, filterValues)
		if err != nil {
			return fmt.Errorf("%s: failed to validate scoped lookup: %w", field.APIName, err)
		}
		if !matches {
			return fmt.Errorf("%s: value %q is not valid for the current scoped lookup context", field.APIName, lookupID)
		}
	}

	return nil
}

func runtimeScopedLookupNeedsValidation(field models.FieldMetadata, changedFields models.SObject) bool {
	if changedFields == nil {
		return true
	}
	if _, ok := changedFields[field.APIName]; ok {
		return true
	}
	cfg, err := models.ParseLookupScopeConfig(field.ValidatorConfig)
	if err != nil {
		return true
	}
	for _, mapping := range cfg.FilterMappings {
		if _, ok := changedFields[mapping.RecordField]; ok {
			return true
		}
	}
	return false
}

func runtimeScopedLookupSelectedValue(value interface{}) (string, bool, error) {
	if value == nil {
		return "", false, nil
	}
	str, ok := value.(string)
	if !ok {
		return "", false, fmt.Errorf("expected string value for runtime_scoped_lookup")
	}
	trimmed := strings.TrimSpace(str)
	if trimmed == "" {
		return "", false, nil
	}
	return trimmed, true, nil
}

func (ps *PersistenceService) resolveRuntimeScopedLookupValue(
	ctx context.Context,
	sourceSchema *models.ObjectMetadata,
	finalRecord models.SObject,
	mapping models.LookupScopeFilterMapping,
) (string, bool, error) {
	recordField := lookupFieldByAPIName(sourceSchema, mapping.RecordField)
	if recordField == nil {
		return "", false, fmt.Errorf("lookup scope record field %q not found on %s", mapping.RecordField, sourceSchema.APIName)
	}

	recordFieldValue, _, err := runtimeScopedLookupSelectedValue(finalRecord[mapping.RecordField])
	if err != nil {
		return "", false, fmt.Errorf("invalid record field %q value: %w", mapping.RecordField, err)
	}
	if recordFieldValue == "" {
		return "", false, nil
	}

	normalizedType := constants.NormalizeFieldType(string(recordField.Type))
	if (normalizedType != constants.FieldTypeReference && normalizedType != constants.FieldTypeMasterDetail) ||
		len(recordField.ReferenceTo) != 1 ||
		strings.EqualFold(strings.TrimSpace(mapping.RecordField), strings.TrimSpace(mapping.SourceField)) {
		return "", false, fmt.Errorf("runtime_scoped_lookup currently requires parent-derived mappings")
	}

	parentObject := strings.TrimSpace(recordField.ReferenceTo[0])
	parentSchema := ps.metadata.GetSchema(ctx, parentObject)
	if parentSchema == nil {
		return "", false, fmt.Errorf("lookup scope parent object %q not found", parentObject)
	}
	if lookupFieldByAPIName(parentSchema, mapping.SourceField) == nil {
		return "", false, fmt.Errorf("lookup scope source field %q not found on %s", mapping.SourceField, parentObject)
	}

	return ps.runtimeScopedLookupSourceValue(ctx, parentSchema, recordFieldValue, mapping.SourceField)
}

func (ps *PersistenceService) runtimeScopedLookupSourceValue(
	ctx context.Context,
	parentSchema *models.ObjectMetadata,
	recordID string,
	sourceField string,
) (string, bool, error) {
	// Preview-provenance short-circuit: under a no-DML insert preview
	// preparation, the parent-derived record ID can be a synthetic
	// preview-* value minted by an earlier same-run RecordCreate. The
	// durable SQL probe would 404, which makes the validator hard-fail
	// the create-preview with "scoped lookup requires resolvable scope
	// before save". Resolve from the registry first; stray preview-*
	// literals without a matching registry entry fall through to the
	// durable read so the preview is still gated by the durable
	// existence/access check.
	if constants.HasInsertPreviewPreparation(ctx) {
		if synthetic, ok := constants.PreviewSyntheticRecordForObject(ctx, parentSchema.APIName, recordID); ok {
			raw, present := synthetic.Fields[sourceField]
			if !present || raw == nil {
				return "", false, nil
			}
			str, ok := raw.(string)
			if !ok {
				return "", false, nil
			}
			str = strings.TrimSpace(str)
			if str == "" {
				return "", false, nil
			}
			return str, true, nil
		}
	}
	queryBuilt := query.From(parentSchema.APIName).
		WithMetadata(parentSchema).
		SkipID().
		Select([]string{sourceField}).
		ExcludeDeleted().
		Where(constants.FieldID, recordID).
		Limit(1).
		Build()
	rows, err := ps.runtimeScopedLookupQueryRows(ctx, queryBuilt.SQL, queryBuilt.Params...)
	if err != nil {
		return "", false, err
	}
	defer rows.Close()

	if !rows.Next() {
		return "", false, fmt.Errorf("lookup scope could not resolve %s.%s from record %s", parentSchema.APIName, sourceField, recordID)
	}

	var value sql.NullString
	if err := rows.Scan(&value); err != nil {
		return "", false, err
	}
	if !value.Valid || strings.TrimSpace(value.String) == "" {
		return "", false, nil
	}
	return strings.TrimSpace(value.String), true, nil
}

func (ps *PersistenceService) runtimeScopedLookupValueMatches(
	ctx context.Context,
	targetSchema *models.ObjectMetadata,
	lookupID string,
	filterValues map[string]string,
) (bool, error) {
	// Preview-provenance short-circuit: under a no-DML insert preview
	// preparation, the looked-up child ID can itself be a synthetic
	// preview-* value (chain create→lookup→create where the child's
	// scoped-lookup field points at another preview-minted record).
	// Resolve from the registry and match filterValues against the
	// synthetic record's fields by stringified equality, mirroring the
	// durable Where-clause shape (Field=value). Stray preview-* literals
	// without a matching registry entry fall through to the durable SQL
	// probe so the validator still fails closed.
	if constants.HasInsertPreviewPreparation(ctx) {
		if synthetic, ok := constants.PreviewSyntheticRecordForObject(ctx, targetSchema.APIName, lookupID); ok {
			for field, want := range filterValues {
				got, present := synthetic.Fields[field]
				if !present || got == nil {
					return false, nil
				}
				if fmt.Sprintf("%v", got) != want {
					return false, nil
				}
			}
			return true, nil
		}
	}
	built := query.From(targetSchema.APIName).
		WithMetadata(targetSchema).
		Select([]string{constants.FieldID}).
		ExcludeDeleted().
		Where(constants.FieldID, lookupID).
		Limit(1)

	for fieldName, value := range filterValues {
		built = built.Where(fieldName, value)
	}
	queryBuilt := built.Build()

	rows, err := ps.runtimeScopedLookupQueryRows(ctx, queryBuilt.SQL, queryBuilt.Params...)
	if err != nil {
		return false, err
	}
	defer rows.Close()
	return rows.Next(), nil
}

func (ps *PersistenceService) runtimeScopedLookupQueryRows(ctx context.Context, sqlText string, args ...interface{}) (*sql.Rows, error) {
	if ps == nil || ps.metadata == nil || ps.rawDB == nil {
		return nil, fmt.Errorf("runtime scoped lookup validation is not configured")
	}

	exec := interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	}(ps.rawDB)
	if ps.txManager != nil {
		if rawTx := ps.txManager.ExtractTx(ctx); rawTx != nil {
			if tx, ok := rawTx.(*sql.Tx); ok {
				exec = tx
			}
		}
	}

	return exec.QueryContext(ctx, sqlText, args...)
}

func lookupFieldByAPIName(schema *models.ObjectMetadata, apiName string) *models.FieldMetadata {
	if schema == nil || strings.TrimSpace(apiName) == "" {
		return nil
	}
	if schema.FieldsMap != nil {
		if field, ok := schema.FieldsMap[strings.ToLower(apiName)]; ok {
			return field
		}
	}
	for i := range schema.Fields {
		if strings.EqualFold(schema.Fields[i].APIName, apiName) {
			return &schema.Fields[i]
		}
	}
	return nil
}