# Database Columns Domain

This domain provides services for managing dynamic database columns in tenant databases. It focuses **exclusively** on database column operations and is completely independent of form field management.

## Architecture

The domain follows a clean architecture pattern with the following components:

### Actions
- `CreateDatabaseColumn` - Handles column creation with transaction support
- `UpdateDatabaseColumn` - Handles column updates with transaction support (uses `ColumnUpdateStrategyService`)
- `DeleteDatabaseColumn` - Handles column deletion with transaction support
- `ListDatabaseColumns` - Lists columns for a given table

### Services
- `DatabaseColumnService` - Main service that orchestrates all column operations
- `ColumnTypeMappingService` - Centralizes logic for mapping between database types and Laravel schema methods
- `ColumnValidationService` - Centralizes validation logic for default values and column constraints
- `ColumnUpdateStrategyService` - Handles different update strategies based on database driver and type changes

### Configuration
- `DatabaseColumnConfig` - Centralizes all configuration constants, mappings, and validation patterns

### Data Transfer Objects
- `ColumnDefinition` - Encapsulates column properties and type mapping

### Events
- `DatabaseColumnCreated` - Dispatched when a new column is created
- `DatabaseColumnUpdated` - Dispatched when a column is modified
- `DatabaseColumnDeleted` - Dispatched when a column is removed

### Rules
- `ColumnNameRule` - Validates column names against database constraints

### Exceptions
- `ColumnValidationException` - Specific exception types for different validation errors

### Models
- `DatabaseColumnChange` - Tracks column modifications for auditing

### Service Provider
- `DatabaseColumnServiceProvider` - Registers all services with proper dependency injection

## Features

- **Pure Database Column Management**: No dependencies on form fields or other domains
- Dynamic column creation based on column definitions
- Multi-tenancy support
- Constraint management (nullable, unique, foreign keys)
- Audit trail of all column changes
- Error handling and validation with specific exception types
- Event-driven architecture
- Data Transfer Objects for type safety
- Custom validation rules
- **Centralized type mapping logic**
- **Centralized validation logic**
- **Strategy-based update operations**
- **Configuration-driven constants and mappings**

## Recent Improvements

### Code Quality Enhancements
1. **Eliminated Code Duplication**: Extracted common validation logic into `ColumnValidationService`
2. **Reduced Method Complexity**: Broke down the large `UpdateDatabaseColumn` action using `ColumnUpdateStrategyService`
3. **Centralized Configuration**: Moved all constants and mappings to `DatabaseColumnConfig`
4. **Improved Error Handling**: Created specific exception types for better error management
5. **Enhanced Dependency Injection**: Created service provider for proper service registration

### Architecture Improvements
1. **Strategy Pattern**: Implemented different update strategies based on database driver and type changes
2. **Single Responsibility**: Each service now has a focused responsibility
3. **Configuration Management**: All magic numbers and constants are now centralized
4. **Validation Pipeline**: Centralized validation logic with consistent error messages

## Usage Examples

### Using the DatabaseColumnService (Recommended)

```php
use Domain\DatabaseColumns\Services\DatabaseColumnService;
use Domain\DatabaseColumns\DataTransferObjects\ColumnDefinition;
use Domain\RecordTypes\Models\RecordType;

class DatabaseColumnController extends Controller
{
    protected $databaseColumnService;

    public function __construct(DatabaseColumnService $databaseColumnService)
    {
        $this->databaseColumnService = $databaseColumnService;
    }

    public function createColumn(Request $request)
    {
        $recordType = RecordType::findOrFail($request->record_type_id);

        $columnDefinition = ColumnDefinition::create(
            name: 'custom_field',
            type: 'text',
            method: 'text',
            constraints: [
                'nullable' => true,
                'default' => null
            ]
        );

        $result = $this->databaseColumnService->createColumn($columnDefinition, $recordType);

        if (!$result['success']) {
            return response()->json(['error' => $result['message']], 422);
        }

        return response()->json([
            'message' => 'Column created successfully',
            'column' => $result['column']
        ]);
    }

    public function updateColumn(Request $request)
    {
        $recordType = RecordType::findOrFail($request->record_type_id);

        $columnDefinition = ColumnDefinition::create(
            name: 'updated_field',
            type: 'datetime',
            method: 'datetime',
            constraints: ['nullable' => true]
        );

        $result = $this->databaseColumnService->updateColumn(
            $columnDefinition,
            $recordType,
            $request->old_column_name
        );

        return response()->json($result);
    }

    public function deleteColumn(Request $request)
    {
        $recordType = RecordType::findOrFail($request->record_type_id);

        $result = $this->databaseColumnService->deleteColumn(
            $request->column_name,
            $recordType
        );

        return response()->json($result);
    }

    public function listColumns(Request $request)
    {
        $recordType = RecordType::findOrFail($request->record_type_id);

        $result = $this->databaseColumnService->listColumns($recordType);

        return response()->json($result);
    }
}
```

### Using Individual Services

```php
use Domain\DatabaseColumns\Services\ColumnTypeMappingService;
use Domain\DatabaseColumns\Services\ColumnValidationService;
use Domain\DatabaseColumns\Config\DatabaseColumnConfig;

// Type mapping
$mappingService = new ColumnTypeMappingService();
$laravelMethod = $mappingService->mapColumnTypeToMethod('int'); // returns 'integer'
$dbType = $mappingService->mapMethodToColumnType('datetime'); // returns 'datetime'
$isIncompatible = $mappingService->isIncompatibleTypeConversion('int', 'datetime'); // returns true

// Validation
$validationService = new ColumnValidationService();
$result = $validationService->validateDefaultValue('datetime', '2024-01-01 12:00:00');
// Returns: ['valid' => true, 'message' => '', 'normalized_value' => '2024-01-01 12:00:00']

// Configuration
$mapping = DatabaseColumnConfig::getContentTypeMapping('text_area'); // returns ['method' => 'text', 'type' => 'text']
$isReserved = DatabaseColumnConfig::isReservedWord('select'); // returns true
```

### Using Actions Directly (Advanced Usage)

```php
use Domain\DatabaseColumns\Actions\CreateDatabaseColumn;
use Domain\DatabaseColumns\Actions\UpdateDatabaseColumn;
use Domain\DatabaseColumns\Actions\DeleteDatabaseColumn;
use Domain\DatabaseColumns\Services\ColumnTypeMappingService;
use Domain\DatabaseColumns\Services\ColumnUpdateStrategyService;
use Domain\DatabaseColumns\Services\ColumnValidationService;
use Domain\DatabaseColumns\DataTransferObjects\ColumnDefinition;
use Domain\RecordTypes\Models\RecordType;

class AdvancedDatabaseColumnController extends Controller
{
    protected $createColumn;
    protected $updateColumn;
    protected $deleteColumn;

    public function __construct()
    {
        $mappingService = new ColumnTypeMappingService();
        $validationService = new ColumnValidationService();
        $strategyService = new ColumnUpdateStrategyService($mappingService, $validationService);

        $this->createColumn = new CreateDatabaseColumn();
        $this->updateColumn = new UpdateDatabaseColumn($mappingService, $strategyService, $validationService);
        $this->deleteColumn = new DeleteDatabaseColumn($mappingService);
    }

    public function createColumn(Request $request)
    {
        $recordType = RecordType::findOrFail($request->record_type_id);

        $columnDefinition = ColumnDefinition::create(
            name: 'custom_field',
            type: 'text',
            method: 'text',
            constraints: [
                'nullable' => true,
                'default' => null
            ]
        );

        $result = $this->createColumn->execute($columnDefinition, $recordType);

        if (!$result['success']) {
            return response()->json(['error' => $result['message']], 422);
        }

        return response()->json([
            'message' => 'Column created successfully',
            'column' => $result['column']
        ]);
    }
}
```

### Event Handling

```php
use Domain\DatabaseColumns\Events\DatabaseColumnCreated;

class DatabaseColumnEventSubscriber
{
    public function handleColumnCreated(DatabaseColumnCreated $event)
    {
        // Handle column creation event
        // e.g., update cache, notify other services
    }

    public function subscribe($events)
    {
        $events->listen(
            DatabaseColumnCreated::class,
            [DatabaseColumnEventSubscriber::class, 'handleColumnCreated']
        );
    }
}
```

### Using the ColumnDefinition DTO

```php
use Domain\DatabaseColumns\DataTransferObjects\ColumnDefinition;

// Create from scratch
$definition = ColumnDefinition::create(
    name: 'custom_field',
    type: 'text',
    method: 'text',
    constraints: ['nullable' => true]
);

// Create from content type (for backward compatibility)
$definition = ColumnDefinition::fromFormField(
    name: 'custom_field',
    contentType: 'text_area',
    constraints: ['nullable' => true],
    formFieldId: 123
);

// Access properties
$columnName = $definition->name;
$columnType = $definition->type;
$columnMethod = $definition->method;
```

### Validation

```php
use Domain\DatabaseColumns\Rules\ColumnNameRule;

$validator = Validator::make($request->all(), [
    'column_name' => ['required', new ColumnNameRule($tableName)],
]);
```

## Configuration

The domain uses a centralized configuration system through `DatabaseColumnConfig`:

### Content Type Mappings
```php
// Automatically maps content types to database types
$mapping = DatabaseColumnConfig::getContentTypeMapping('text_area');
// Returns: ['method' => 'text', 'type' => 'text']
```

### Database Type Mappings
```php
// Map database types to Laravel methods
$method = DatabaseColumnConfig::getColumnTypeToMethod('bigint');
// Returns: 'bigInteger'

// Map Laravel methods to database types
$type = DatabaseColumnConfig::getMethodToColumnType('datetime');
// Returns: 'datetime'
```

### Validation Patterns
```php
// Date format validation
DatabaseColumnConfig::DATE_FORMAT_PATTERN; // '/^\\d{4}-\\d{2}-\\d{2}$/'
DatabaseColumnConfig::DATETIME_FORMAT_PATTERN; // '/^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$/'

// Column name validation
DatabaseColumnConfig::MAX_COLUMN_NAME_LENGTH; // 64
DatabaseColumnConfig::COLUMN_NAME_PATTERN; // '/^[a-z_][a-z0-9_]*$/'
```

## Error Handling

The domain provides specific exception types for different error scenarios:

```php
use Domain\DatabaseColumns\Exceptions\ColumnValidationException;

// Invalid column name
throw ColumnValidationException::invalidColumnName('select', 'Reserved SQL keyword');

// Column already exists
throw ColumnValidationException::columnAlreadyExists('custom_field', 'users');

// Column not found
throw ColumnValidationException::columnNotFound('missing_field', 'users');

// Invalid default value
throw ColumnValidationException::invalidDefaultValue('created_at', 'datetime', 'invalid-date', 'Invalid format');

// Incompatible type conversion
throw ColumnValidationException::incompatibleTypeConversion('int', 'datetime');
```

## Audit Trail

All column changes are tracked in the `database_column_changes` table, including:
- Action performed (create/update/delete)
- Table and column names
- Column type and constraints
- Associated form field ID (optional)
- User who made the change
- Timestamp

## Best Practices

1. **Always use the DatabaseColumnService** for database operations to ensure proper transaction handling
2. **Listen for events** to handle side effects
3. **Use DTOs** for type safety and data consistency
4. **Use the centralized services** for all type mapping and validation logic
5. **Validate column names** using the provided rule
6. **Check operation results** before proceeding
7. **Monitor the audit trail** for debugging
8. **Use proper error handling** with specific exception types
9. **Keep this domain focused** on database column management only
10. **Use the service provider** for proper dependency injection

## Integration with FormField Domain

This domain is designed to work with the FormField domain through the `FormFieldAssociationService`. The FormField domain handles:
- Form field lifecycle management
- Record type associations
- Coordination with database column operations

## Testing

The domain includes tests for:
- Column creation/update/deletion
- Validation rules
- Event dispatching
- Audit trail creation
- Error handling
- Multi-tenancy support
- Strategy-based updates
- Configuration validation

## Service Registration

To use this domain, register the service provider in your `config/app.php`:

```php
'providers' => [
    // ...
    Domain\DatabaseColumns\Providers\DatabaseColumnServiceProvider::class,
],
```

## Contributing

When adding new features:
1. Create appropriate DTOs for data handling
2. Add relevant events
3. Update validation rules if needed
4. Add tests for new functionality
5. Update this documentation
6. **Maintain separation of concerns** - keep this domain focused on database columns only
7. **Use the configuration system** for constants and mappings
8. **Follow the established patterns** for error handling and validation