package connectorreaders

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

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

// ResolveQuery carries the filter parameters for a direction-aware connector
// field mapping read. It is the contract DTO; re-exported here for the relocated
// reader.
type ResolveQuery = connector.ResolveQuery

// ConnectorFieldMappingInput is the input struct for pack-managed connector
// rows. It is the contract DTO; re-exported here for the relocated reader.
type ConnectorFieldMappingInput = connector.ConnectorFieldMappingInput

// Direction constants for connector field mapping rows, mirrored from the
// contract package for the resolver's WHERE/ORDER BY clauses and validation.
const (
	directionInbound       = connector.DirectionInbound
	directionOutbound      = connector.DirectionOutbound
	directionBidirectional = connector.DirectionBidirectional
)

var validDirections = map[string]struct{}{
	directionInbound:       {},
	directionOutbound:      {},
	directionBidirectional: {},
}

// connectorFieldMappingKeySegment folds a single business-key segment to the
// repo-default case-insensitive form. _System_ObjectFieldMapping uses the
// repo-default case-insensitive collation, so code-side duplicate checks must
// fold case the same way for API-name/action segments.
func connectorFieldMappingKeySegment(value string) string {
	return strings.ToLower(strings.TrimSpace(value))
}

// ResolveFieldMapping returns active connector field mapping rows that match
// the caller's direction perspective.
//
// Fail-closed rules:
//   - q.Source must be non-blank (a connector read must scope to its owner).
//   - q.Direction must be one of the allowed connector values (inbound,
//     outbound, bidirectional). 'legacy' and blank are rejected.
//
// Ordering mirrors the legacy GetFieldMappings reader so the two surfaces
// stay consistent: rows are ordered by the same precedence expression
// (IsCustomized first, then tenant overrides, then connector-owned, then
// standard/legacy) followed by SortOrder ASC, SourceField ASC.
func ResolveFieldMapping(ctx context.Context, db *sql.DB, q ResolveQuery) ([]ConnectorFieldMappingInput, error) {
	if strings.TrimSpace(q.Source) == "" {
		return nil, fmt.Errorf("ResolveFieldMapping: Source must not be blank (connector reads must scope to their owner)")
	}
	dir := strings.TrimSpace(q.Direction)
	if dir == "" {
		return nil, fmt.Errorf("ResolveFieldMapping: Direction must not be blank")
	}
	if dir == legacyObjectFieldMappingOwner {
		return nil, fmt.Errorf("ResolveFieldMapping: Direction 'legacy' is reserved for pre-connector rows; use GetFieldMappings for legacy reads")
	}
	if _, ok := validDirections[dir]; !ok {
		return nil, fmt.Errorf("ResolveFieldMapping: Direction %q is not valid; allowed values: inbound, outbound, bidirectional", dir)
	}

	// Bidirectional fan-out: match rows whose Direction = q.Direction OR
	// Direction = 'bidirectional'.  This is the WHERE clause for part of the
	// contract described in the exported doc comment.
	query := fmt.Sprintf(
		`SELECT Source, SourceObject, TargetObject, ActionType, SourceField,
		        TargetField, DefaultValue, SortOrder, Direction,
		        TransformExpression, ConflictPolicy, ValueMap, OnUnmappedValue
		 FROM %s
		 WHERE Source = ?
		   AND SourceObject = ?
		   AND TargetObject = ?
		   AND ActionType = ?
		   AND Direction IN (?, '%s')
		   AND IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		 ORDER BY (CASE WHEN Direction = '%s' THEN 1 ELSE 0 END) ASC, %s, SortOrder ASC, SourceField ASC`,
		tableObjectFieldMapping,
		directionBidirectional,
		directionBidirectional,
		fieldMappingSourcePrecedenceSQL(),
	)

	rows, err := db.QueryContext(ctx, query,
		strings.TrimSpace(q.Source),
		strings.TrimSpace(q.SourceObject),
		strings.TrimSpace(q.TargetObject),
		strings.TrimSpace(q.ActionType),
		dir,
	)
	if err != nil {
		return nil, fmt.Errorf("ResolveFieldMapping %s/%s→%s(%s)[%s]: %w",
			q.Source, q.SourceObject, q.TargetObject, q.ActionType, dir, err)
	}
	defer rows.Close()

	var result []ConnectorFieldMappingInput
	seen := map[string]struct{}{}

	for rows.Next() {
		var row ConnectorFieldMappingInput
		var defaultVal, transformExpr, conflictPolicy, valueMap, onUnmappedVal sql.NullString
		if err := rows.Scan(
			&row.Source,
			&row.SourceObject,
			&row.TargetObject,
			&row.ActionType,
			&row.SourceField,
			&row.TargetField,
			&defaultVal,
			&row.SortOrder,
			&row.Direction,
			&transformExpr,
			&conflictPolicy,
			&valueMap,
			&onUnmappedVal,
		); err != nil {
			return nil, fmt.Errorf("ResolveFieldMapping scan: %w", err)
		}

		// Deduplicate by SourceField: the precedence ORDER BY ensures the
		// highest-priority row arrives first, matching GetFieldMappings
		// dedup semantics.
		sourceFieldKey := connectorFieldMappingKeySegment(row.SourceField)
		if _, dup := seen[sourceFieldKey]; dup {
			continue
		}
		seen[sourceFieldKey] = struct{}{}

		if defaultVal.Valid {
			row.DefaultValue = defaultVal.String
		}
		if transformExpr.Valid {
			row.TransformExpression = transformExpr.String
		}
		if conflictPolicy.Valid {
			row.ConflictPolicy = conflictPolicy.String
		}
		if valueMap.Valid {
			row.ValueMap = valueMap.String
		}
		if onUnmappedVal.Valid {
			row.OnUnmappedValue = onUnmappedVal.String
		}
		result = append(result, row)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("ResolveFieldMapping rows: %w", err)
	}
	if err := validateConnectorFieldMappingNoDuplicateResolvedTargetFields(result); err != nil {
		return nil, fmt.Errorf("ResolveFieldMapping %s/%s→%s(%s)[%s]: %w",
			q.Source, q.SourceObject, q.TargetObject, q.ActionType, dir, err)
	}
	return result, nil
}

// ResolvePackConnectorFieldMapping returns the pack-authored connector mapping
// rows for trusted outbound authority. Unlike ResolveFieldMapping, this reader
// intentionally does not use effective/admin-customized precedence: trusted
// PushToConnector execution must consume the source-backed pack declaration
// (Source=<owner>, IsCustomized=0) or fail closed at the caller.
func ResolvePackConnectorFieldMapping(ctx context.Context, db *sql.DB, q ResolveQuery) ([]ConnectorFieldMappingInput, error) {
	if strings.TrimSpace(q.Source) == "" {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping: Source must not be blank (connector reads must scope to their owner)")
	}
	dir := strings.TrimSpace(q.Direction)
	if dir == "" {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping: Direction must not be blank")
	}
	if dir == legacyObjectFieldMappingOwner {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping: Direction 'legacy' is reserved for pre-connector rows; use GetFieldMappings for legacy reads")
	}
	if _, ok := validDirections[dir]; !ok {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping: Direction %q is not valid; allowed values: inbound, outbound, bidirectional", dir)
	}

	query := fmt.Sprintf(
		`SELECT Source, SourceObject, TargetObject, ActionType, SourceField,
		        TargetField, DefaultValue, SortOrder, Direction,
		        TransformExpression, ConflictPolicy, ValueMap, OnUnmappedValue
		 FROM %s
		 WHERE Source = ?
		   AND SourceObject = ?
		   AND TargetObject = ?
		   AND ActionType = ?
		   AND Direction IN (?, '%s')
		   AND IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND COALESCE(IsCustomized, 0) = 0
		 ORDER BY (CASE WHEN Direction = '%s' THEN 1 ELSE 0 END) ASC, SortOrder ASC, SourceField ASC`,
		tableObjectFieldMapping,
		directionBidirectional,
		directionBidirectional,
	)

	rows, err := db.QueryContext(ctx, query,
		strings.TrimSpace(q.Source),
		strings.TrimSpace(q.SourceObject),
		strings.TrimSpace(q.TargetObject),
		strings.TrimSpace(q.ActionType),
		dir,
	)
	if err != nil {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping %s/%s→%s(%s)[%s]: %w",
			q.Source, q.SourceObject, q.TargetObject, q.ActionType, dir, err)
	}
	defer rows.Close()

	var result []ConnectorFieldMappingInput
	seen := map[string]struct{}{}

	for rows.Next() {
		var row ConnectorFieldMappingInput
		var defaultVal, transformExpr, conflictPolicy, valueMap, onUnmappedVal sql.NullString
		if err := rows.Scan(
			&row.Source,
			&row.SourceObject,
			&row.TargetObject,
			&row.ActionType,
			&row.SourceField,
			&row.TargetField,
			&defaultVal,
			&row.SortOrder,
			&row.Direction,
			&transformExpr,
			&conflictPolicy,
			&valueMap,
			&onUnmappedVal,
		); err != nil {
			return nil, fmt.Errorf("ResolvePackConnectorFieldMapping scan: %w", err)
		}

		sourceFieldKey := connectorFieldMappingKeySegment(row.SourceField)
		if _, dup := seen[sourceFieldKey]; dup {
			continue
		}
		seen[sourceFieldKey] = struct{}{}

		if defaultVal.Valid {
			row.DefaultValue = defaultVal.String
		}
		if transformExpr.Valid {
			row.TransformExpression = transformExpr.String
		}
		if conflictPolicy.Valid {
			row.ConflictPolicy = conflictPolicy.String
		}
		if valueMap.Valid {
			row.ValueMap = valueMap.String
		}
		if onUnmappedVal.Valid {
			row.OnUnmappedValue = onUnmappedVal.String
		}
		result = append(result, row)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping rows: %w", err)
	}
	if err := validateConnectorFieldMappingNoDuplicateResolvedTargetFields(result); err != nil {
		return nil, fmt.Errorf("ResolvePackConnectorFieldMapping %s/%s→%s(%s)[%s]: %w",
			q.Source, q.SourceObject, q.TargetObject, q.ActionType, dir, err)
	}
	return result, nil
}

func validateConnectorFieldMappingNoDuplicateResolvedTargetFields(rows []ConnectorFieldMappingInput) error {
	type keyInfo struct {
		index int
		row   ConnectorFieldMappingInput
	}
	seen := make(map[string]keyInfo, len(rows))
	for i, row := range rows {
		key := connectorFieldMappingKeySegment(row.TargetField)
		if prev, dup := seen[key]; dup {
			return fmt.Errorf("duplicate effective target field %s from SourceField %s conflicts with row %d SourceField %s",
				strings.TrimSpace(row.TargetField),
				strings.TrimSpace(row.SourceField),
				prev.index,
				strings.TrimSpace(prev.row.SourceField),
			)
		}
		seen[key] = keyInfo{index: i, row: row}
	}
	return nil
}