package persistence

import (
	"regexp"
	"strings"

	"github.com/go-sql-driver/mysql"
	"github.com/nexuscrm/backend/pkg/errors"
)

// MySQL error codes
const (
	MySQLUnknownColumnCode  = 1054
	MySQLDuplicateEntryCode = 1062
	MySQLTableNotExistsCode = 1146
)

// IsTableNotExistsError checks if the error is a MySQL "table doesn't exist" error (Error 1146)
func IsTableNotExistsError(err error) bool {
	if err == nil {
		return false
	}

	// Check for MySQL driver error
	if mysqlErr, ok := err.(*mysql.MySQLError); ok {
		return mysqlErr.Number == MySQLTableNotExistsCode
	}

	// Fallback: check error message for table not exists pattern
	return strings.Contains(err.Error(), "doesn't exist") ||
		strings.Contains(err.Error(), "Error 1146")
}

// IsSchemaDrift reports whether err represents a metadata-vs-physical
// divergence (table missing or column unknown) rather than a genuine
// infra failure. Probe consumers that iterate over objects described
// by metadata should treat drift as "this probe cannot match" and
// silently continue, while still promoting genuine infra errors to
// InternalError. Keeping the classification here avoids each caller
// having to repeat the 1146/1054 check — a class of bug the
// persistence layer has hit twice already.
func IsSchemaDrift(err error) bool {
	return IsTableNotExistsError(err) || IsUnknownColumnError(err)
}

// IsUnknownColumnError checks if the error is a MySQL "unknown column" error (Error 1054)
func IsUnknownColumnError(err error) bool {
	if err == nil {
		return false
	}

	if mysqlErr, ok := err.(*mysql.MySQLError); ok {
		return mysqlErr.Number == MySQLUnknownColumnCode
	}

	return strings.Contains(err.Error(), "Unknown column") ||
		strings.Contains(err.Error(), "Error 1054")
}

// extractUnknownColumnName extracts the column name from a MySQL 1054 error message.
// Example: "Unknown column 'BadField' in 'where clause'"
func extractUnknownColumnName(err error) string {
	re := regexp.MustCompile(`Unknown column '([^']+)'`)
	matches := re.FindStringSubmatch(err.Error())
	if len(matches) >= 2 {
		return matches[1]
	}
	return ""
}

// IsDuplicateKeyError checks if the error is a MySQL duplicate key error (Error 1062)
func IsDuplicateKeyError(err error) bool {
	if err == nil {
		return false
	}

	// Check for MySQL driver error
	if mysqlErr, ok := err.(*mysql.MySQLError); ok {
		return mysqlErr.Number == MySQLDuplicateEntryCode
	}

	// Fallback: check error message for "Duplicate entry" pattern
	// This handles wrapped errors
	return strings.Contains(err.Error(), "Duplicate entry") ||
		strings.Contains(err.Error(), "Error 1062")
}

// extractDuplicateKeyName extracts the key name from a MySQL duplicate entry error message
// Example: "Duplicate entry 'value' for key 'table.unique_key'"
func extractDuplicateKeyName(errMsg string) string {
	// Pattern: "for key 'table.key_name'" or "for key 'key_name'"
	re := regexp.MustCompile(`for key '([^']+)'`)
	matches := re.FindStringSubmatch(errMsg)
	if len(matches) >= 2 {
		// Extract just the field name from "table.index_name" pattern
		keyName := matches[1]
		if idx := strings.LastIndex(keyName, "."); idx >= 0 {
			keyName = keyName[idx+1:]
		}
		return keyName
	}
	return ""
}

// NewDuplicateKeyError creates a ConflictError from a duplicate key violation
func NewDuplicateKeyError(tableName, keyName string) *errors.ConflictError {
	// Extract field name from index name patterns like "idx_table_field"
	fieldName := keyName
	if strings.HasPrefix(keyName, "idx_") || strings.HasPrefix(keyName, "uk_") {
		// Try to extract a meaningful field name
		parts := strings.Split(keyName, "_")
		if len(parts) > 2 {
			fieldName = strings.Join(parts[2:], "_")
		}
	}

	return errors.NewConflictError(tableName, fieldName, "")
}