package metadata

import (
	"context"
	"fmt"
	"strings"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

const validationProjectionKindLookupValue = "lookupValue"

type ActiveProjectionResolver func(ctx context.Context, sourceSchema *models.ObjectMetadata, record models.SObject, field *models.FieldMetadata) (interface{}, error)

type validationProjectionQueryExecutor interface {
	ExecuteRawSQL(ctx context.Context, sql string, params []interface{}) ([]models.SObject, error)
}

func NewValidationActiveProjectionResolver(
	metadataSvc *MetadataService,
	queryExec validationProjectionQueryExecutor,
) ActiveProjectionResolver {
	if metadataSvc == nil || queryExec == nil {
		return nil
	}

	return func(ctx context.Context, sourceSchema *models.ObjectMetadata, record models.SObject, field *models.FieldMetadata) (interface{}, error) {
		if field == nil || field.ProjectionConfig == nil {
			return nil, nil
		}
		if sourceSchema == nil || sourceSchema.APIName == "" {
			return nil, fmt.Errorf("projection-backed field requires source schema context")
		}

		projection := field.ProjectionConfig
		if !strings.EqualFold(strings.TrimSpace(projection.Kind), validationProjectionKindLookupValue) {
			return nil, fmt.Errorf("projection kind %q is unsupported in validation runtime", projection.Kind)
		}
		if strings.TrimSpace(projection.TargetObject) == "" {
			return nil, fmt.Errorf("lookupValue projection missing targetObject")
		}
		if strings.TrimSpace(projection.SelectField) == "" {
			return nil, fmt.Errorf("lookupValue projection missing selectField")
		}
		if len(projection.Matches) == 0 {
			return nil, fmt.Errorf("lookupValue projection requires at least one match")
		}
		if !isValidIdentifier(projection.TargetObject) {
			return nil, fmt.Errorf("invalid lookupValue targetObject %q", projection.TargetObject)
		}
		if !isValidIdentifier(projection.SelectField) {
			return nil, fmt.Errorf("invalid lookupValue selectField %q", projection.SelectField)
		}

		targetSchema := metadataSvc.GetSchema(ctx, projection.TargetObject)
		if targetSchema == nil {
			return nil, fmt.Errorf("lookupValue projection target object %q not found", projection.TargetObject)
		}
		if validationFieldByKey(targetSchema, projection.SelectField) == nil {
			return nil, fmt.Errorf("lookupValue projection select field %q not found on %s", projection.SelectField, projection.TargetObject)
		}
		if projection.ExcludeDeleted && validationFieldByKey(targetSchema, constants.FieldIsDeleted) == nil {
			return nil, fmt.Errorf("lookupValue projection excludeDeleted requires %s.%s", projection.TargetObject, constants.FieldIsDeleted)
		}

		clauses := make([]string, 0, len(projection.Matches)+1)
		args := make([]interface{}, 0, len(projection.Matches)+1)
		for i, match := range projection.Matches {
			if strings.TrimSpace(match.TargetField) == "" || strings.TrimSpace(match.SourceField) == "" {
				return nil, fmt.Errorf("lookupValue projection match %d requires targetField and sourceField", i+1)
			}
			if !isValidIdentifier(match.TargetField) {
				return nil, fmt.Errorf("invalid lookupValue targetField %q", match.TargetField)
			}
			if !isValidIdentifier(match.SourceField) {
				return nil, fmt.Errorf("invalid lookupValue sourceField %q", match.SourceField)
			}
			if validationFieldByKey(targetSchema, match.TargetField) == nil {
				return nil, fmt.Errorf("lookupValue projection target field %q not found on %s", match.TargetField, projection.TargetObject)
			}
			if validationFieldByKey(sourceSchema, match.SourceField) == nil {
				return nil, fmt.Errorf("lookupValue projection source field %q not found on %s", match.SourceField, sourceSchema.APIName)
			}

			value, definitiveNull := validationProjectionSourceValue(record, match.SourceField)
			if definitiveNull {
				return nil, nil
			}
			clauses = append(clauses, fmt.Sprintf("`%s` = ?", match.TargetField))
			args = append(args, value)
		}

		if projection.ExcludeDeleted {
			clauses = append(clauses, fmt.Sprintf("(`%s` = ? OR `%s` IS NULL)", constants.FieldIsDeleted, constants.FieldIsDeleted))
			args = append(args, false)
		}

		sql := fmt.Sprintf(
			"SELECT `%s` FROM `%s` WHERE %s LIMIT 1",
			projection.SelectField,
			validationProjectionLookupTable(projection.TargetObject),
			strings.Join(clauses, " AND "),
		)
		rows, err := queryExec.ExecuteRawSQL(ctx, sql, args)
		if err != nil {
			return nil, fmt.Errorf("lookupValue projection query failed: %w", err)
		}
		if len(rows) == 0 {
			return nil, nil
		}
		return validationProjectionFieldValue(rows[0], projection.SelectField)
	}
}

func validationProjectionLookupTable(targetObject string) string {
	if strings.EqualFold(targetObject, constants.ObjectRecordType) {
		return constants.TableRecordType
	}
	return targetObject
}

func validationProjectionSourceValue(record models.SObject, fieldName string) (interface{}, bool) {
	value, ok := record[fieldName]
	if !ok || value == nil {
		return nil, true
	}
	return value, false
}

func validationProjectionFieldValue(record models.SObject, fieldName string) (interface{}, error) {
	if value, ok := record[fieldName]; ok {
		return value, nil
	}
	for key, value := range record {
		if strings.EqualFold(key, fieldName) {
			return value, nil
		}
	}
	return nil, fmt.Errorf("lookupValue projection query returned row without field %q", fieldName)
}

func validationProjectionInputRecord(
	schema *models.ObjectMetadata,
	rawRecord map[string]interface{},
	overlayRecord map[string]interface{},
) models.SObject {
	if rawRecord == nil && overlayRecord == nil {
		return nil
	}

	out := cloneValidationFormulaMap(rawRecord)
	if out == nil {
		out = make(map[string]interface{})
	}
	if schema == nil || overlayRecord == nil {
		return models.SObject(out)
	}

	for i := range schema.Fields {
		field := &schema.Fields[i]
		if !formulastorage.IsActiveComputedField(field) {
			continue
		}
		if value, ok := overlayRecord[field.APIName]; ok {
			out[field.APIName] = value
		}
	}
	return models.SObject(out)
}