## Getting Started

### Minimal Setup
1. **Install the package**:
   ```bash
   composer require ascetic-soft/rowcast

Create a connection (e.g., SQLite in-memory for testing):

use AsceticSoft\Rowcast\Connection;

$connection = Connection::create('sqlite::memory:');

Define a DTO (Data Transfer Object):

class UserDto {
    public int $id;
    public string $email;
    public bool $isActive;
}

Initialize DataMapper:

use AsceticSoft\Rowcast\DataMapper;

$mapper = new DataMapper($connection);

First CRUD operation (insert and fetch):

$user = new UserDto();
$user->email = 'alice@example.com';
$user->isActive = true;

$mapper->insert('users', $user); // Auto-maps to `users` table
$foundUser = $mapper->findOne(UserDto::class, ['email' => $user->email]);

Key First Use Cases

Auto-mapping: Use DTO class names to derive table/column names (e.g., UserDto → users table, created_at → createdAt).
Explicit mapping: Override defaults with Mapping for custom table/column names or ignored properties.
Type safety: Hydrate DTOs with PHP 8.4+ type casting (e.g., int, bool, DateTime).

Implementation Patterns

1. Auto-Mapping Workflow

When: Using standard table/column naming conventions. Pattern:

// Insert
$mapper->insert(UserDto::class, $dto);

// Find
$users = $mapper->findAll(UserDto::class, ['isActive' => true]);

Pros: Zero boilerplate for simple cases. Cons: Limited to default naming rules (e.g., snake_case → camelCase).

2. Explicit Mapping for Edge Cases

When: Custom table/column names, ignored properties, or partial mapping. Pattern:

use AsceticSoft\Rowcast\Mapping;

$mapping = Mapping::auto(UserDto::class, 'custom_users')
    ->column('usr_email', 'email') // Override column name
    ->ignore('internalNote');      // Skip property

$mapper->findOne($mapping, ['id' => 1]);

Best Practice:

Reuse Mapping instances (e.g., as class constants) to avoid recreation overhead.
Prefer Mapping::explicit() for strict column whitelisting.

3. Batch Operations

When: Bulk inserts/updates (e.g., importing data). Pattern:

$users = [$dto1, $dto2, $dto3];
$mapper->batchInsert('users', $users); // Chunks automatically for large datasets
$mapper->batchUpsert('users', $users, ['email'], 500); // 500 params per chunk

Tip: Use maxBindParameters to control chunk size (critical for SQLite’s 999-param limit).

4. Query Builder for Complex Queries

When: Custom SQL logic or advanced filtering. Pattern:

$qb = $connection->createQueryBuilder();
$qb->select('u.*')
   ->from('users', 'u')
   ->where(['isActive' => true, 'age >=' => 18])
   ->orWhere(['role' => 'admin'])
   ->orderBy('u.id', 'DESC');

$users = $qb->fetchAllAssociative();

Key Features:

Dialect-aware: Supports ILIKE (PostgreSQL), BETWEEN, and IN/NOT IN.
Parameter binding: Auto-generates unique params (e.g., :w_age, :w_age_1).

5. Type Conversion

When: Custom data types (e.g., UUIDs, JSON). Pattern:

use AsceticSoft\Rowcast\TypeConverter\TypeConverterRegistry;

$converters = TypeConverterRegistry::defaults()
    ->add(new UuidConverter());

$mapper = new DataMapper($connection, typeConverter: $converters);

Built-in Converters:

DateTimeInterface → ISO string.
BackedEnum → backing value.
array → JSON string.

6. Transactions and Savepoints

When: Atomic operations or nested transactions. Pattern:

$connection->transactional(function (Connection $conn) {
    $conn->executeStatement('INSERT INTO users (...) VALUES (...)');
    // Nested transactions use savepoints if `nestTransactions: true`.
});

Use Case: Rollback partial failures (e.g., multi-step imports).

7. UPSERT for Idempotency

When: Insert-or-update logic without duplicate checks. Pattern:

$mapper->upsert('users', $dto, 'email'); // Conflict on `email`

Dialect Note: Uses ON CONFLICT (PostgreSQL) or INSERT ... ON DUPLICATE KEY (MySQL).

Gotchas and Tips

1. Auto-Mapping Pitfalls

Table Name Derivation: UserDto → user_dtos (pluralized snake_case). Fix: Use Mapping::auto(UserDto::class, 'users') to override.
Column Naming: created_at → createdAt (default). Fix: Customize with NameConverterInterface or explicit mapping.

2. Batch Operation Quirks

SQLite Limit: Hard 999 bind parameters per query. Tip: Always specify maxBindParameters for large batches:
```
$mapper->batchInsert('users', $dtos, 500);
```
Transaction Scope: Batch operations run in a single transaction by default. Tip: Disable with $mapper->batchInsert(..., null, false).

3. Type Conversion Gotchas

Unsupported Types: Throws UnsupportedTypeException for unsupported PHP types. Fix: Register a custom TypeConverterInterface.
Enum Handling: BackedEnum values are normalized to their backing type (e.g., UserStatus::Active → 'active'). Tip: Use explicit converters for complex enums.

4. Query Builder Edge Cases

Empty IN Clauses: ['status' => []] → 1 = 0 (always false). Workaround: Skip the condition or use ['status !=' => []] (always true).
Dialect-Specific Operators: ILIKE fails on MySQL/SQLite. Fix: Use LIKE or implement a custom dialect.
Parameter Naming: Auto-generated params (e.g., :w_age_1) may collide. Tip: Use explicit params for clarity:
```
$qb->where('age > :min_age')->setParameter('min_age', 18);
```

5. Performance Tips

Reuse Mapping: Avoid recreating Mapping instances for repeated operations.

Lazy Hydration: Use iterateAll() for large datasets to avoid memory spikes:

foreach ($mapper->iterateAll(UserDto::class, []) as $user) {
    // Process one row at a time
}

Indexed Access: Prefer findOne() over findAll() + array search for lookups.

6. Debugging Tips

SQL Logging: Attach query event listeners:

$connection->onBeforeQuery(function (string $sql, array $params) {
    error_log("SQL: $sql | Params: " . json_encode($params));
});

Type Errors: Enable PHP 8.4’s strict types and check TypeConverterRegistry.

Transaction Debugging: Use nestTransactions: true to isolate failures:

$connection = Connection::create('mysql:...', nestTransactions: true);

7. Extension Points

Custom Dialects: Extend DialectInterface for unsupported databases (e.g., Oracle).
Name Conversion: Override NameConverterInterface for non-snake_case schemas.
Query Events: Hook into onBeforeQuery/onAfterQuery for logging/auditing.

8. Laravel Integration Tips

Service Provider: Bind Connection and DataMapper as singletons:

$this->app->singleton(Connection::class, function () {
    return Connection::create(env('DB_DSN'), env('DB_USER'), env('DB_PASS'));
});

Eloquent Alternative: Use DataMapper for:
- Complex DTOs (non-Eloquent models).
- Raw SQL queries without Eloquent overhead.
- Batch operations

Rowcast Laravel Package