# Batch Upsert Service Quick Reference

## TL;DR Checklist for New Services

### 🚀 Quick Start (30 min setup)

1. **Analyze Schema**
   ```bash
   mysql -h localhost -u root -e "USE {tenant_db}; DESCRIBE {table};" > schema.txt
   ```

2. **Count Complexity**
   ```bash
   grep -c "tinyint(1)" schema.txt  # Boolean fields
   grep -c "decimal\|double" schema.txt  # Decimal fields
   ```

3. **Choose Template**
   - **Simple** (≤5 boolean, ≤2 decimal): 8-step process
   - **Medium** (6-15 boolean, 3-7 decimal): 10-step process
   - **Complex** (15+ boolean, 8+ decimal): 11-step process

4. **Apply Pattern** (see templates below)

---

## Essential Code Templates

### Validation Rules Template

```php
$this->cachedValidationRules = [
    // Core fields (ALL services)
    'refid' => 'required|integer',
    'external_id' => 'nullable|string|max:255',
    'synced' => 'required|boolean',

    // Boolean fields (check schema: TINYINT(1) NOT NULL = required|boolean)
    'boolean_field' => 'required|boolean',  // NOT NULL
    'optional_bool' => 'nullable|boolean',  // NULL allowed

    // Decimal fields (for pre-normalization)
    'money_field' => 'required|string|max:20',  // NOT NULL
    'optional_decimal' => 'nullable|string|max:20',  // NULL allowed

    // Text fields
    'title' => 'nullable|string|max:255',
    'description' => 'nullable|string',

    // JSON field
    'fields' => 'nullable|string',
];
```

### Boolean Schema Defaults Template

```php
$schemaDefaults = [
    'most_fields' => false,  // DEFAULT 0 (majority)
    'enforceminqtyinternally' => true,  // DEFAULT 1 (items table exception)
    // CHECK SCHEMA: grep "DEFAULT 1" schema.txt
];
```

### Validation Process Template (Complex)

```php
foreach ($mappedRecords as $index => $recordData) {
    // 1. Required fields
    $recordData['refid'] = $netsuiteRecords[$index]['id'];
    $recordData['synced'] = true;

    // 2. ItemType (items table only)
    $recordData['itemtype'] = '{TypeName}';

    // 3. Boolean normalization with defaults
    foreach ($this->booleanFields as $field => $netsuiteField) {
        $value = $netsuiteRecords[$index][$netsuiteField] ?? null;
        $recordData[$field] = $value !== null
            ? $this->dataTransformationService->normalizeBoolean($value, $field, 'boolean')
            : ($schemaDefaults[$field] ?? false);
    }

    // 4. Decimal normalization
    foreach (['averagecost', 'price'] as $field) {
        if (isset($recordData[$field])) {
            $recordData[$field] = $this->dataTransformationService
                ->normalizeDecimalWithPrecision($recordData[$field], 2);
        }
    }

    // 5. Text normalization
    foreach (['title', 'description'] as $field) {
        if (isset($recordData[$field])) {
            $recordData[$field] = $this->dataTransformationService->normalizeText($recordData[$field]);
        }
    }

    // 6. Empty to null
    foreach (['external_id', 'title'] as $field) {
        if (isset($recordData[$field]) && $recordData[$field] === '') {
            $recordData[$field] = null;
        }
    }

    // 7. JSON handling
    if (isset($recordData['fields']) && is_array($recordData['fields'])) {
        $recordData['fields'] = json_encode($recordData['fields']);
    }

    // 8. Normalization method
    $recordData = $this->normalize{Type}Record($recordData);

    // 9. Validation with custfield filtering
    $dataForValidation = $this->dataTransformationService->filterCustomFieldsFromValidation($recordData);
    $validator = Validator::make($dataForValidation, $this->cachedValidationRules);

    if (!$validator->fails()) {
        $validRecords[] = $recordData;
    }
}
```

---

## Critical Schema Patterns

### Boolean Field Rules

| **Schema** | **Validation Rule** | **Default Handling** |
|------------|--------------------|--------------------|
| `TINYINT(1) NOT NULL DEFAULT 0` | `'required\|boolean'` | `false` |
| `TINYINT(1) NOT NULL DEFAULT 1` | `'required\|boolean'` | `true` |
| `TINYINT(1) NULL` | `'nullable\|boolean'` | `null` |

### Decimal Field Rules

| **Schema** | **Validation Rule** | **Normalization** |
|------------|--------------------|--------------------|
| `DECIMAL(10,2) NOT NULL` | `'required\|string\|max:20'` | `precision: 2` |
| `DECIMAL(15,4) NULL` | `'nullable\|string\|max:20'` | `precision: 4` |
| `DOUBLE NULL` | `'nullable\|string\|max:20'` | `precision: 6` |

### Common Special Cases

```php
// Items table exception
'enforceminqtyinternally' => true,  // Only field with DEFAULT 1

// Required decimal defaults
if (!isset($recordData['averagecost'])) {
    $recordData['averagecost'] = '0.00';  // DECIMAL NOT NULL DEFAULT 0.00
}

// ItemType setting (items table services)
$recordData['itemtype'] = 'NonInventoryItem';  // or 'OthCharge', 'DescriptionItem'
```

---

## Instant Validation Checklist

### ✅ Must-Have Rules
- [ ] `refid` → `'required|integer'`
- [ ] `synced` → `'required|boolean'`
- [ ] All NOT NULL boolean fields → `'required|boolean'`
- [ ] All decimal fields → `'nullable|string|max:20'` (not numeric!)
- [ ] Custfield filtering → `filterCustomFieldsFromValidation()`

### ❌ Common Mistakes
- [ ] Boolean fields as `'boolean'` instead of `'required|boolean'` for NOT NULL
- [ ] Decimal fields as `'numeric'` instead of `'string'` for pre-normalization
- [ ] Missing schema defaults for boolean fields
- [ ] Filtering custfields from upsert instead of just validation
- [ ] Not using DataTransformationService methods

---

## Service Types Quick Guide

### Simple Services (2-3 hours)
- **Examples**: InventoryItem, ServiceItem, Subsidiary
- **Pattern**: Dedicated simple tables
- **Process**: 8-step validation
- **Key**: Basic field alignment

### Medium Services (3-4 hours)
- **Examples**: Customer, Project, Vendor, Employee
- **Pattern**: Dedicated complex tables
- **Process**: 10-step validation
- **Key**: Business logic + field alignment

### Complex Services (4-5 hours)
- **Examples**: Item, NonInventoryItem, DescriptionItem, OtherCharge, SalesOrder, Invoice
- **Pattern**: Shared complex tables (especially `items`)
- **Process**: 11-step validation
- **Key**: Extensive boolean handling + itemtype logic

---

## Emergency Debug Commands

```bash
# Quick schema check
mysql -h localhost -u root -e "USE {db}; DESCRIBE {table};" | grep -E "tinyint|decimal|varchar"

# Validation rule count check
grep -c "=>" src/Domain/{Type}/Services/{Type}BatchUpsertService.php

# Boolean field verification
grep "required|boolean" src/Domain/{Type}/Services/{Type}BatchUpsertService.php | wc -l

# Custfield filtering check
grep -n "filterCustomFieldsFromValidation" src/Domain/{Type}/Services/{Type}BatchUpsertService.php

# DataTransformationService usage
grep -c "dataTransformationService->" src/Domain/{Type}/Services/{Type}BatchUpsertService.php

# 🚀 NEW: Chunking debug commands
grep -i "chunked.*batch" storage/logs/laravel.log | tail -10
grep "chunks_processed\|chunks_failed" storage/logs/laravel.log | tail -5
grep "MySQL.*placeholder" storage/logs/laravel.log | tail -5
```

### 🚀 Chunking Troubleshooting

```bash
# Check for chunking activity in logs
grep "🚀 UPSERT: Starting chunked batch upsert" storage/logs/laravel.log

# Check chunk failure patterns
grep "❌ UPSERT: Chunk.*failed" storage/logs/laravel.log

# Check individual fallback processing
grep "🔄 UPSERT:.*falling back to individual processing" storage/logs/laravel.log

# Monitor chunk performance
grep "🎯 UPSERT: Chunked processing completed" storage/logs/laravel.log | tail -5
```

---

## 🚀 Enhanced Batch Size Handling (MySQL Placeholder Protection)

### Automatic Chunking System

The `RecordUpsertService` now includes **automatic chunking** to prevent MySQL placeholder limit errors while maximizing throughput:

```php
// Default behavior (backward compatible)
$result = $this->baseUpsertService->upsertBatch(
    Model::class,
    $validatedRecords,
    ['refid'],
    true  // Automatic 250-record chunks applied
);

// Custom chunk size for wide tables (many columns)
$result = $this->baseUpsertService->upsertBatch(
    WideTableModel::class,
    $validatedRecords,
    ['refid'],
    true,
    100  // Custom chunk size for tables with 50+ columns
);
```

### Enhanced Result Structure

```php
$result = [
    'success' => 950,                      // Total successful records
    'errors' => [...],                     // Detailed error information
    'strategy_used' => 'chunked_batch_success',
    'chunks_processed' => 4,               // NEW: Number of chunks processed
    'chunks_failed' => 1,                  // NEW: Number of chunks that failed
    'total_records' => 1000                // NEW: Total records attempted
];
```

### Chunking Strategy

| **Scenario** | **Chunk Size** | **Strategy** | **Fallback** |
|--------------|----------------|--------------|--------------|
| **≤250 records** | Single batch | Direct processing | Individual records |
| **>250 records** | 250 per chunk | Sequential chunks | Individual per chunk |
| **Wide tables** | 50-100 per chunk | Custom sizing | Individual per chunk |
| **Chunk failure** | N/A | Individual processing | Error logging |

### Chunking Benefits

- ✅ **Eliminates MySQL placeholder errors** for wide NetSuite tables (50+ columns)
- ✅ **Maximizes success rate** through granular chunk-level fallback
- ✅ **Maintains performance** with intelligent chunk sizing
- ✅ **Provides detailed logging** for chunk and individual record failures
- ✅ **Backward compatible** - no changes required to existing services

---

## Performance Expectations

| **Metric** | **Before** | **After** | **Target** |
|------------|------------|-----------|------------|
| **Records/sec** | 50-100 | 500-1000+ | 10x improvement |
| **DB calls/batch** | 1000 | 1 | 1000x reduction |
| **Validation success** | Variable | 99%+ | Near perfect |
| **Error rate** | High | <1% | Minimal |
| **🚀 MySQL Placeholder Errors** | **Frequent** | **Eliminated** | **Zero errors** |
| **🚀 Wide Table Support** | **Limited** | **Unlimited** | **Any column count** |
| **🚀 Chunk Processing** | **N/A** | **250 records/chunk** | **Optimal sizing** |

---

## Support & Escalation

### When to Follow Full Guide
- **New record types** (no existing service)
- **Major schema changes** (new columns, type changes)
- **Performance issues** (not meeting 10x target)
- **Complex business logic** (conditional validation)

### When Quick Reference Suffices
- **Minor field additions** (1-2 new columns)
- **Validation rule updates** (constraint changes)
- **Bug fixes** (small logic issues)
- **Copy-paste patterns** (similar to existing service)

---

## Success Criteria Checklist

### Immediate (Post-Implementation)
- [ ] Linting passes (`./vendor/bin/phpstan analyse --level=5`)
- [ ] No obvious validation errors in logs
- [ ] Service can process test data without exceptions
- [ ] Boolean field count matches schema
- [ ] **🚀 NEW**: Chunking activates for datasets >250 records
- [ ] **🚀 NEW**: No MySQL placeholder limit errors in logs

### Performance (Within 1 week)
- [ ] 10x processing speed improvement observed
- [ ] 99%+ validation success rate achieved
- [ ] Database query count reduced to 1 per batch
- [ ] Error logs significantly reduced
- [ ] **🚀 NEW**: Wide tables (50+ columns) process without errors
- [ ] **🚀 NEW**: Chunk fallback maintains >95% success rate

### Long-term (Within 1 month)
- [ ] No regressions in data quality
- [ ] Maintenance overhead reduced
- [ ] New field additions are straightforward
- [ ] Team can replicate pattern for new services
- [ ] **🚀 NEW**: Large imports (1000+ records) complete reliably
- [ ] **🚀 NEW**: Chunking logs provide clear debugging information

---

*This quick reference should be used alongside the full [Batch Upsert Service Optimization Guide](./BATCH_UPSERT_SERVICE_OPTIMIZATION_GUIDE.md) for comprehensive implementations.*