package persistence

import (
	stderrors "errors"
	"regexp"
	"strings"

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

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

// IsTableExistsError checks if the error is a MySQL "table already exists" error
// (Error 1050). The strict object-create path issues a plain `CREATE TABLE`
// (no IF NOT EXISTS) precisely so a pre-existing physical table of the same name
// makes the CREATE fail with 1050 — the atomic "did not just create it" signal
// that lets creation fail closed instead of silently rebinding new metadata to a
// stale orphan table (#3484). Mirrors the 1146/1054/1062 helpers.
func IsTableExistsError(err error) bool {
	if err == nil {
		return false
	}

	var mysqlErr *mysql.MySQLError
	if stderrors.As(err, &mysqlErr) {
		return mysqlErr.Number == MySQLTableExistsCode
	}

	// Fallback for errors wrapped as opaque strings: match ONLY the errno-1050
	// marker, not a bare "already exists" substring — the latter also appears in
	// duplicate-key (1062) and other conflict messages and would misclassify them
	// as a table-exists block.
	return strings.Contains(err.Error(), "Error 1050")
}

// 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
	}

	var mysqlErr *mysql.MySQLError
	if stderrors.As(err, &mysqlErr) {
		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
	}

	var mysqlErr *mysql.MySQLError
	if stderrors.As(err, &mysqlErr) {
		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 ""
}

func isUnknownColumnInWhereClause(err error) bool {
	if err == nil {
		return false
	}
	return strings.Contains(strings.ToLower(err.Error()), "in 'where clause'")
}

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

	var mysqlErr *mysql.MySQLError
	if stderrors.As(err, &mysqlErr) {
		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) *apperrors.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 apperrors.NewConflictError(tableName, fieldName, "")
}