package persistence

import (
	"fmt"
	"regexp"
	"strconv"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
)

var (
	// strictDecimalRegex enforces DECIMAL(p,s) syntax
	// p (precision): 1-65 digits
	// s (scale): 0-30 digits
	// MySQL Limit: Precision 65, Scale 30.
	strictDecimalRegex = regexp.MustCompile(`^DECIMAL\(\s*(\d+)\s*,\s*(\d+)\s*\)$`)

	// varcharRegex enforces VARCHAR(N) syntax. Size bounds are checked
	// separately so an out-of-range N produces a descriptive error instead
	// of a generic "unknown type" miss.
	varcharRegex = regexp.MustCompile(`^VARCHAR\(\s*(\d+)\s*\)$`)

	// charRegex enforces CHAR(N) syntax. Same size-bound handling as VARCHAR.
	charRegex = regexp.MustCompile(`^CHAR\(\s*(\d+)\s*\)$`)

	// binaryRegex and varbinaryRegex admit binary-safe coordination keys in
	// system-table definitions without opening arbitrary raw SQL pass-through.
	binaryRegex    = regexp.MustCompile(`^BINARY\(\s*(\d+)\s*\)$`)
	varbinaryRegex = regexp.MustCompile(`^VARBINARY\(\s*(\d+)\s*\)$`)

	// fractionalTimeRegex enforces DATETIME(N) / TIMESTAMP(N) where N is
	// the fractional-second precision. MySQL/TiDB accepts N in [0, 6]. Required
	// by audit design §2.4 which specifies DATETIME(6) (microsecond) on
	// _System_AuditOutbox for deterministic FIFO ordering and sub-second
	// lease-expiry accuracy.
	fractionalTimeRegex = regexp.MustCompile(`^(DATETIME|TIMESTAMP)\(\s*(\d+)\s*\)$`)
)

// MySQL column-length limits for parameterized character types.
const (
	maxVarcharLength    = 65535 // MySQL VARCHAR per-column byte ceiling
	maxCharLength       = 255   // MySQL CHAR per-column char ceiling
	maxVarbinaryLength  = 65535 // MySQL VARBINARY per-column byte ceiling
	maxBinaryLength     = 255   // MySQL BINARY byte ceiling
	maxFractionalTimeFS = 6     // MySQL/TiDB fractional-second precision upper bound
)

// LogicalTextStorageMaxBytes reports the physical TiDB capacity selected by
// ResolveSQLType for a logical text field. Engines that must persist a value
// after an external effect use this to fail before that effect; the physical
// storage fact remains owned by persistence.
func LogicalTextStorageMaxBytes(fieldType constants.SchemaFieldType) (int64, bool) {
	switch constants.NormalizeFieldType(string(fieldType)) {
	case constants.FieldTypeString, constants.FieldTypeEmail:
		return 255, true
	case constants.FieldTypeTextarea:
		return 65535, true
	case constants.FieldTypeRichtextarea:
		return 1<<32 - 1, true
	default:
		return 0, false
	}
}

// ResolveSQLType resolves the SQL column type from a field type string.
// It strictly separates Logical Types (Metadata) from Physical Types (Raw SQL).
func ResolveSQLType(fieldType string) (string, error) {
	upper := strings.ToUpper(fieldType)

	// 1. First check if it's a valid Physical SQL Type (System Tables pass-through)
	// This must come BEFORE normalization to prevent LONGTEXT→textarea→TEXT
	if isValidPhysicalType(upper) {
		return upper, nil
	}

	// 2. Normalize the input type to Salesforce-compatible naming for logical types
	normalizedType := constants.NormalizeFieldType(fieldType)

	// 3. Try to map from a known Logical Type
	if sqlType, ok := mapLogicalToSQL(normalizedType); ok {
		return sqlType, nil
	}

	// 4. Fail-safe passes for parameterized physical types. These produce
	// a descriptive error when the syntax is well-formed but the parameter
	// is out of range (easier to diagnose than a generic "unknown type").
	// Well-formed in-range values are already accepted by isValidPhysicalType
	// at step 1 above; these branches only run for rejects.
	if strings.HasPrefix(upper, "DECIMAL") {
		if strictDecimalRegex.MatchString(upper) {
			return fieldType, nil // Allow DECIMAL(x,y)
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid DECIMAL syntax: %s (expected DECIMAL(p,s))", fieldType))
	}
	if strings.HasPrefix(upper, "VARCHAR") {
		if m := varcharRegex.FindStringSubmatch(upper); m != nil {
			return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("VARCHAR length out of range in %s: must be 1..%d", fieldType, maxVarcharLength))
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid VARCHAR syntax: %s (expected VARCHAR(N))", fieldType))
	}
	if strings.HasPrefix(upper, "CHAR") {
		if m := charRegex.FindStringSubmatch(upper); m != nil {
			return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("CHAR length out of range in %s: must be 1..%d", fieldType, maxCharLength))
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid CHAR syntax: %s (expected CHAR(N))", fieldType))
	}
	if strings.HasPrefix(upper, "VARBINARY") {
		if m := varbinaryRegex.FindStringSubmatch(upper); m != nil {
			return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("VARBINARY length out of range in %s: must be 1..%d", fieldType, maxVarbinaryLength))
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid VARBINARY syntax: %s (expected VARBINARY(N))", fieldType))
	}
	if strings.HasPrefix(upper, "BINARY") {
		if m := binaryRegex.FindStringSubmatch(upper); m != nil {
			return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("BINARY length out of range in %s: must be 1..%d", fieldType, maxBinaryLength))
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid BINARY syntax: %s (expected BINARY(N))", fieldType))
	}
	if strings.HasPrefix(upper, "DATETIME(") || strings.HasPrefix(upper, "TIMESTAMP(") {
		if m := fractionalTimeRegex.FindStringSubmatch(upper); m != nil {
			return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("%s fractional-seconds precision out of range in %s: must be 0..%d", m[1], fieldType, maxFractionalTimeFS))
		}
		return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("invalid fractional-time syntax: %s (expected DATETIME(N) or TIMESTAMP(N))", fieldType))
	}

	return "", apperrors.NewValidationError("fieldType", fmt.Sprintf("unknown or unsupported field type: %s", fieldType))
}

// mapLogicalToSQL converts a Salesforce-compatible Logical Type to its backing SQL storage type.
// Input should already be normalized via constants.NormalizeFieldType
func mapLogicalToSQL(fieldType constants.SchemaFieldType) (string, bool) {
	switch fieldType {
	case constants.FieldTypeString:
		return SQLTypeVarchar255, true
	case constants.FieldTypeTextarea:
		return SQLTypeText, true
	case constants.FieldTypeRichtextarea:
		return SQLTypeLongText, true
	case constants.FieldTypeDouble:
		return "DECIMAL(65,30)", true
	case constants.FieldTypeCurrency:
		return "DECIMAL(65,30)", true
	case constants.FieldTypePercent:
		return "DECIMAL(65,30)", true
	case constants.FieldTypeBoolean:
		return SQLTypeBoolean, true
	case constants.FieldTypeDate:
		return SQLTypeDate, true
	case constants.FieldTypeDatetime:
		return SQLTypeDateTime, true
	case constants.FieldTypeEmail:
		return SQLTypeVarchar255, true
	case constants.FieldTypePhone:
		return SQLTypeVarchar50, true
	case constants.FieldTypeUrl:
		return SQLTypeVarchar255, true
	case constants.FieldTypePicklist:
		return SQLTypeVarchar255, true
	case constants.FieldTypeMultiPicklist:
		return SQLTypeJSON, true // Stored as JSON array
	case constants.FieldTypeReference, constants.FieldTypeMasterDetail:
		return SQLTypeVarchar255, true // UUID
	case constants.FieldTypeAutonumber:
		return SQLTypeVarchar255, true
	case constants.FieldTypeFormula:
		return SQLTypeVarchar255, true // Default
	case constants.FieldTypePassword, constants.FieldTypeEncryptedString:
		return SQLTypeText, true
	case constants.FieldTypeJson:
		return SQLTypeJSON, true
	}

	// Handle unnormalized string values
	switch strings.ToLower(string(fieldType)) {
	case "checkbox":
		return SQLTypeBoolean, true
	case "multiselectpicklist":
		return SQLTypeJSON, true
	}

	return "", false
}

// isValidPhysicalType allows strict pass-through for specific SQL types used in System Tables.
// VARCHAR(N) and CHAR(N) accept any N within MySQL's declared range; out-of-range
// parameters are caught in ResolveSQLType with a descriptive error.
func isValidPhysicalType(upper string) bool {
	// Assumes upper is already Uppercase from caller
	switch upper {
	case SQLTypeInt, SQLTypeInteger, SQLTypeTinyInt, SQLTypeTinyInt1, SQLTypeBigInt, SQLTypeSmallInt:
		return true
	case SQLTypeDateTime, SQLTypeTimestamp, SQLTypeDate:
		return true
	case SQLTypeText, "MEDIUMTEXT", "LONGTEXT", SQLTypeMediumBlob, SQLTypeJSON:
		return true
	case SQLTypeBoolean, SQLTypeBool:
		return true
	}
	return isValidVarchar(upper) || isValidChar(upper) || isValidBinary(upper) || isValidVarbinary(upper) || isValidFractionalTime(upper)
}

// isValidVarchar returns true when upper is VARCHAR(N) with 1 <= N <= 65535.
func isValidVarchar(upper string) bool {
	m := varcharRegex.FindStringSubmatch(upper)
	if m == nil {
		return false
	}
	n, err := strconv.Atoi(m[1])
	return err == nil && n >= 1 && n <= maxVarcharLength
}

// isValidChar returns true when upper is CHAR(N) with 1 <= N <= 255.
func isValidChar(upper string) bool {
	m := charRegex.FindStringSubmatch(upper)
	if m == nil {
		return false
	}
	n, err := strconv.Atoi(m[1])
	return err == nil && n >= 1 && n <= maxCharLength
}

func isValidBinary(upper string) bool {
	m := binaryRegex.FindStringSubmatch(upper)
	if m == nil {
		return false
	}
	n, err := strconv.Atoi(m[1])
	return err == nil && n >= 1 && n <= maxBinaryLength
}

func isValidVarbinary(upper string) bool {
	m := varbinaryRegex.FindStringSubmatch(upper)
	if m == nil {
		return false
	}
	n, err := strconv.Atoi(m[1])
	return err == nil && n >= 1 && n <= maxVarbinaryLength
}

// isValidFractionalTime returns true when upper is DATETIME(N) or TIMESTAMP(N)
// with 0 <= N <= 6. Required by audit design §2.4 (_System_AuditOutbox uses
// DATETIME(6) for deterministic FIFO ordering and sub-second lease-expiry).
func isValidFractionalTime(upper string) bool {
	m := fractionalTimeRegex.FindStringSubmatch(upper)
	if m == nil {
		return false
	}
	n, err := strconv.Atoi(m[2])
	return err == nil && n >= 0 && n <= maxFractionalTimeFS
}

// extractBaseType extracts the base type from a full column type.
// e.g., "DECIMAL(18,6)" -> "DECIMAL", "VARCHAR(255)" -> "VARCHAR"
// This is a package-level function for use across the package.
func extractBaseType(colType string) string {
	colType = strings.ToUpper(colType)
	if idx := strings.Index(colType, "("); idx != -1 {
		return colType[:idx]
	}
	return colType
}