# Safety Fund SoftDelete Fix

## Why This Error Happens

**Error:** `SQLSTATE[42S22]: Unknown column 'safety_funds.deleted_at'`

**Root Cause:**
- SafetyFund model uses `use SoftDeletes;` trait
- The trait automatically adds a global scope: `WHERE deleted_at IS NULL`
- The safety_funds table was created before softDeletes was added to migration
- Table schema doesn't match model expectations

**Why It Occurred:**
- Migration initially created table without `softDeletes()`
- Model was updated to use SoftDeletes trait
- Database table was never updated to include `deleted_at` column
- Eloquent tries to apply the global scope but column doesn't exist

## Solution Applied

1. Deleted duplicate migration that tried to add deleted_at (failed due to table recreation)
2. Confirmed original migration includes `softDeletes()`
3. Ran `migrate:fresh` to recreate all tables with correct schema

**Original Migration (Correct):**
```php
Schema::create('safety_funds', function (Blueprint $table) {
    $table->id();
    $table->foreignId('profit_distribution_id')->constrained()->onDelete('cascade');
    $table->date('date')->unique();
    $table->decimal('amount', 15, 2);
    $table->decimal('cumulative_balance', 15, 2)->default(0);
    $table->enum('transaction_type', ['CREDIT', 'DEBIT'])->default('CREDIT');
    $table->text('description')->nullable();
    $table->timestamps();
    $table->softDeletes(); // ← Included in original migration

    $table->index('date');
});
```

## Relationship Between Components

```
Model (SoftDeletes Trait)
    ↓
Global Query Scope (WHERE deleted_at IS NULL)
    ↓
Database Column (deleted_at TIMESTAMP NULL)
    ↓
Automatic Timestamp Management
```

## Eloquent SoftDeletes Mechanics

### 1. Trait Usage
```php
class SafetyFund extends Model
{
    use SoftDeletes;
}
```

### 2. Global Query Scope
When you query SafetyFund, Eloquent automatically adds:
```sql
SELECT * FROM safety_funds WHERE deleted_at IS NULL
```

### 3. Soft Delete Operation
```php
$fund->delete(); // Sets deleted_at = NOW(), doesn't actually delete row
```

### 4. Restore Operation
```php
$fund->restore(); // Sets deleted_at = NULL
```

### 5. Force Delete
```php
$fund->forceDelete(); // Actually deletes the row from database
```

### 6. Include Deleted Records
```php
$funds = SafetyFund::withTrashed()->get(); // Includes soft deleted
$funds = SafetyFund::onlyTrashed()->get(); // Only soft deleted
```

## Why Soft Delete is Useful for Reserve Funds

### 1. Reserve Fund History
- Complete audit trail of all fund movements
- Track all allocations and withdrawals
- Historical analysis of fund growth
- Compliance with financial regulations

### 2. Audit Trail
- Every deletion is recorded with timestamp
- Prevents accidental permanent data loss
- Maintains complete transaction history
- Required for regulatory compliance

### 3. Rollback Recovery
- Restore accidentally deleted fund records
- Recover from sync errors
- Rollback incorrect allocations
- Debug data integrity issues

### 4. Financial Compliance
- Financial regulations require complete records
- Audit trails must show all data modifications
- Soft deletes provide reversible actions
- Supports data retention policies

## Safety Fund SoftDelete Best Practices

### 1. Transaction Safety
```php
DB::transaction(function () use ($fund) {
    $fund->delete();
    Log::info("Safety fund soft deleted", ['fund_id' => $fund->id]);
});
```

### 2. Recovery Flow
```php
$deletedFund = SafetyFund::onlyTrashed()
    ->where('date', $date)
    ->first();

if ($deletedFund) {
    $deletedFund->restore();
    Log::info("Safety fund restored", ['fund_id' => $deletedFund->id]);
}
```

### 3. Audit Logging
```php
SafetyFund::deleting(function ($fund) {
    Log::info("Safety fund soft deleted", [
        'fund_id' => $fund->id,
        'amount' => $fund->amount,
        'date' => $fund->date,
        'reason' => 'User request'
    ]);
});
```

### 4. Data Retention
```php
// Archive soft deleted funds after 7 years
SafetyFund::onlyTrashed()
    ->where('deleted_at', '<', now()->subYears(7))
    ->chunk(1000, function ($funds) {
        foreach ($funds as $fund) {
            ArchiveSafetyFund::create($fund->toArray());
            $fund->forceDelete(); // Permanent delete after archival
        }
    });
```

## Migration Commands

### Fresh Migration (Development)
```bash
php artisan migrate:fresh
```

### Apply Migration
```bash
php artisan migrate
```

### Check Migration Status
```bash
php artisan migrate:status
```

## Testing Checklist

- [ ] safety_funds table contains deleted_at column
- [ ] SafetyFund model uses SoftDeletes trait
- [ ] Migration runs successfully
- [ ] Laravel cache cleared
- [ ] SafetyFund queries work correctly
- [ ] Soft delete operations work
- [ ] Restore operations work
- [ ] withTrashed() scope works
- [ ] onlyTrashed() scope works
- [ ] Force delete works

## Troubleshooting

### Issue: Column still doesn't exist after migration
**Solution:** Use `migrate:fresh` to recreate all tables with correct schema

### Issue: Query still fails
**Solution:** Clear config cache: `php artisan config:clear`

### Issue: Global scope not applied
**Solution:** Verify model uses trait: `use SoftDeletes;`

### Issue: Can't restore deleted record
**Solution:** Use `withTrashed()` to find deleted record first

## Production Considerations

### 1. Data Retention
- Keep soft deleted records for 7 years (regulatory)
- Archive to cold storage after retention period
- Implement cleanup job for old records

### 2. Performance
- Add index on deleted_at column for faster queries
- Monitor query performance with soft deletes
- Consider partitioning by deleted_at status

### 3. Monitoring
- Monitor soft delete rate
- Alert on unusual deletion patterns
- Track restore operations
- Monitor disk space usage

### 4. Backup Strategy
- Include soft deleted records in backups
- Test restore procedures with soft deletes
- Maintain point-in-time recovery capability

## Next Steps

1. ✅ Removed duplicate migration
2. ✅ Confirmed original migration has softDeletes
3. ✅ Ran migrate:fresh to recreate tables
4. ✅ Cleared Laravel cache
5. ⏳ Test SafetyFund queries
6. ⏳ Test profit distribution with safety fund