package connectorreaders

import (
	"database/sql"
	"fmt"

	connector "github.com/pingcap-inc/nexuscrm-contract/pkg/connector"
)

// FieldMappingDef represents a single source→target field mapping. It is the
// contract DTO; re-exported here for the relocated reader.
type FieldMappingDef = connector.FieldMappingDef

// legacyObjectFieldMappingOwner is the reserved 'legacy' direction sentinel
// rejected by the connector resolver readers. Inlined to keep this package free
// of any up-DAG dependency; it mirrors the bootstrap local const of the same
// name.
const legacyObjectFieldMappingOwner = "legacy"

// GetFieldMappings retrieves active field mappings for a given conversion.
// Used by BootstrapFieldMappingApplier during flow-driven recordCreate to
// copy source-record field values into a newly created target record.
func GetFieldMappings(db *sql.DB, sourceObject, targetObject, actionType string) ([]FieldMappingDef, error) {
	query := fmt.Sprintf(
		`SELECT Source, SourceField, TargetField, DefaultValue, SourceLookupField
		 FROM %s
		 WHERE SourceObject = ? AND TargetObject = ? AND ActionType = ?
		   AND IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND Direction = 'legacy'
		 ORDER BY %s, SortOrder ASC, SourceField ASC, TargetField ASC`,
		tableObjectFieldMapping,
		fieldMappingSourcePrecedenceSQL(),
	)

	rows, err := db.Query(query, sourceObject, targetObject, actionType)
	if err != nil {
		return nil, fmt.Errorf("failed to query field mappings: %w", err)
	}
	defer rows.Close()

	var mappings []FieldMappingDef
	seen := map[string]bool{}
	for rows.Next() {
		var m FieldMappingDef
		var source string
		var defaultVal, sourceLookup sql.NullString
		if err := rows.Scan(&source, &m.SourceField, &m.TargetField, &defaultVal, &sourceLookup); err != nil {
			return nil, fmt.Errorf("failed to scan field mapping: %w", err)
		}
		if seen[m.SourceField] {
			continue
		}
		seen[m.SourceField] = true
		if defaultVal.Valid {
			m.DefaultValue = defaultVal.String
		}
		if sourceLookup.Valid {
			m.SourceLookupField = sourceLookup.String
		}
		mappings = append(mappings, m)
	}

	return mappings, nil
}

// GetFieldMappingSourceForTarget returns the active source field that maps to
// a target field for a conversion-style mapping tuple. It shares the same owner
// precedence as GetFieldMappings so standard-action discovery and Flow runtime
// describe the same metadata fact.
func GetFieldMappingSourceForTarget(db *sql.DB, sourceObject, targetObject, actionType, targetField string) (string, error) {
	query := fmt.Sprintf(
		`SELECT SourceField
		 FROM %s
		 WHERE SourceObject = ? AND TargetObject = ? AND ActionType = ?
		   AND TargetField = ?
		   AND IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND Direction = 'legacy'
		 ORDER BY %s, SortOrder ASC, SourceField ASC
		 LIMIT 1`,
		tableObjectFieldMapping,
		fieldMappingSourcePrecedenceSQL(),
	)
	var sourceField string
	err := db.QueryRow(query, sourceObject, targetObject, actionType, targetField).Scan(&sourceField)
	switch {
	case err == sql.ErrNoRows:
		return "", nil
	case err != nil:
		return "", fmt.Errorf("failed to query field mapping source for %s→%s.%s(%s): %w",
			sourceObject, targetObject, targetField, actionType, err)
	default:
		return sourceField, nil
	}
}