package persistence

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

	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/constants"
)

// SchemaRepository handles direct database schema operations (DDL) and system registry updates
type SchemaRepository struct {
	db     *sql.DB
	logger *logging.Logger
}

// NewSchemaRepository creates a new SchemaRepository.
// The logger parameter is optional — passing nil is safe because
// logging.Logger methods are nil-receiver safe.
func NewSchemaRepository(db *sql.DB, logger *logging.Logger) *SchemaRepository {
	return &SchemaRepository{
		db:     db,
		logger: logger,
	}
}

// Executor interface for db/tx flexibility
type Executor interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

// IsSystemColumn returns true for columns that are automatically populated
// by the server or database (e.g., timestamps, IDs, ownership fields)
// Handles logical names (Id, CreatedDate, etc.)
func (r *SchemaRepository) IsSystemColumn(name string) bool {
	// Check logical names first
	switch name {
	case constants.FieldID, constants.FieldCreatedDate, constants.FieldLastModifiedDate, constants.FieldCreatedByID, constants.FieldLastModifiedByID, constants.FieldOwnerID, constants.FieldIsDeleted, constants.FieldSystemModstamp:
		return true
	}
	// Physical fallback removed; native Salesforce naming in effect.
	return false
}

// mapSQLTypeToLogical converts SQL types to system logical types.
// Used during schema introspection and field registration to determine the UI type.
func (r *SchemaRepository) mapSQLTypeToLogical(sqlType string) string {
	// Use extractBaseType for strict matching (e.g., "DECIMAL(18,6)" -> "DECIMAL")
	baseType := extractBaseType(strings.ToUpper(sqlType))

	switch baseType {
	// Text Family
	case SQLTypeVarchar, SQLTypeChar, SQLTypeText, "MEDIUMTEXT", "LONGTEXT":
		return string(constants.FieldTypeString)

	// Boolean: TINYINT(1) is MySQL boolean, also explicit BOOLEAN/BOOL
	case SQLTypeBoolean, SQLTypeBool:
		return string(constants.FieldTypeBoolean)

	// Integer Family -> Number
	case SQLTypeInt, SQLTypeInteger, SQLTypeBigInt, SQLTypeSmallInt:
		return string(constants.FieldTypeDouble)

	// TINYINT: Special handling - TINYINT(1) is boolean, others are numbers
	case SQLTypeTinyInt:
		// Check for exact TINYINT(1) match
		if strings.ToUpper(sqlType) == SQLTypeTinyInt1 {
			return string(constants.FieldTypeBoolean)
		}
		return string(constants.FieldTypeDouble)

	// Decimal/Float Family -> Number
	case SQLTypeDecimal, SQLTypeFloat, SQLTypeDouble, "NUMERIC":
		return string(constants.FieldTypeDouble)

	// Date/Time Family
	case SQLTypeDateTime, SQLTypeTimestamp:
		return string(constants.FieldTypeDatetime)
	case SQLTypeDate:
		return string(constants.FieldTypeDate)

	// JSON
	case SQLTypeJSON:
		return string(constants.FieldTypeJson)
	}

	// Default fallback with logging for unknown types
	r.logger.Warn(fmt.Sprintf("mapSQLTypeToLogical: unknown SQL type '%s' (base: %s), defaulting to Text", sqlType, baseType))
	return string(constants.FieldTypeString)
}

// MapFieldTypeToSQL converts logical field types to SQL column types
// This is used by MetadataService to prepare TableDefinition
// It handles both Logical Types (from constants) and Physical SQL Types (for system tables)
func (r *SchemaRepository) MapFieldTypeToSQL(fieldType string) string {
	// Delegate to strict resolver
	sqlType, err := ResolveSQLType(fieldType)
	if err != nil {
		// STRICT MODE: Do not fallback. Return invalid SQL to force failure.
		r.logger.Error(fmt.Sprintf("CRITICAL: failed to resolve SQL type for '%s': returning INVALID_TYPE to halt execution", fieldType), err)
		return "INVALID_TYPE_ERROR"
	}
	return sqlType
}