# Daily Profit Snapshot Schema Fix

## Why This Error Happens

**Error:** `SQLSTATE[HY000]: General error: 1364 Field 'margin' doesn't have a default value`

**Root Cause:**
- MySQL strict mode requires NOT NULL columns to have either:
  1. A value provided during INSERT
  2. A DEFAULT value defined in schema
- The daily_profit_snapshots table had NOT NULL columns without defaults
- Partial INSERT statements failed because required fields were missing

## Solution Applied

Updated migration to add `default(0)` to all financial numeric fields:

```php
$table->decimal('balance', 15, 2)->default(0);
$table->decimal('equity', 15, 2)->default(0);
$table->decimal('margin', 15, 2)->default(0);
$table->decimal('free_margin', 15, 2)->default(0);
$table->decimal('floating_profit', 15, 2)->default(0);
$table->decimal('daily_profit', 15, 2)->default(0);
$table->integer('open_trades_count')->default(0);
$table->integer('closed_trades_count')->default(0);
$table->timestamp('snapshot_at')->nullable();
```

## Why Financial Systems Should Avoid NULL Numeric Fields

### 1. Calculation Safety
- NULL + any_value = NULL (SQL behavior)
- SUM(), AVG(), COUNT() exclude NULL values
- Causes incorrect financial calculations
- Hard to debug calculation errors

### 2. Data Integrity
- Zero represents "no value" in finance
- NULL represents "unknown/missing"
- Financial systems need deterministic values
- Prevents ambiguous states

### 3. Performance
- Queries with NULL checks are slower
- Indexes work better with NOT NULL
- Optimizer can make better decisions
- Reduces storage overhead

### 4. Business Logic
- Zero balance is a valid state
- Zero profit is a valid state
- NULL creates three-state logic (true/false/unknown)
- Complicates conditional logic

### 5. Reporting
- Financial reports expect numbers
- NULL values break aggregations
- Excel/Power BI handle NULL poorly
- Regulatory compliance requires complete data

## Corrected DailyProfitSnapshot Schema

```sql
CREATE TABLE daily_profit_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mt5_account_id BIGINT UNSIGNED NOT NULL,
    snapshot_date DATE UNIQUE NOT NULL,
    balance DECIMAL(15, 2) NOT NULL DEFAULT 0,
    equity DECIMAL(15, 2) NOT NULL DEFAULT 0,
    margin DECIMAL(15, 2) NOT NULL DEFAULT 0,
    free_margin DECIMAL(15, 2) NOT NULL DEFAULT 0,
    floating_profit DECIMAL(15, 2) NOT NULL DEFAULT 0,
    daily_profit DECIMAL(15, 2) NOT NULL DEFAULT 0,
    open_trades_count INT NOT NULL DEFAULT 0,
    closed_trades_count INT NOT NULL DEFAULT 0,
    snapshot_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (mt5_account_id) REFERENCES mt5_accounts(id) ON DELETE CASCADE,
    INDEX idx_account_date (mt5_account_id, snapshot_date),
    INDEX idx_date (snapshot_date)
);
```

## Decimal Precision for Finance Systems

### DECIMAL(15, 2) Explanation
- **15**: Total digits precision
- **2**: Decimal places after the point
- **Range**: -999,999,999,999.99 to 999,999,999,999.99
- **Suitable for**: Most forex/hedge fund operations

### When to Use Different Precision
- **DECIMAL(10, 2)**: Small accounts (up to 99,999,999.99)
- **DECIMAL(15, 2)**: Standard hedge fund (up to 999 billion)
- **DECIMAL(19, 4)**: Cryptocurrency (more decimal places)
- **DECIMAL(19, 2)**: Institutional (up to 999 trillion)

## Recommended Snapshot Architecture

### 1. MT5 Sync Architecture
```
Python MT5 Bridge → Laravel API → ProcessMt5Sync Job → Mt5SyncService
    ↓
Sync Account Data
    ↓
Create/Update Daily Snapshot (if needed)
    ↓
Queue profit distribution (scheduled separately)
```

### 2. Daily Settlement Architecture
```
00:00 UTC - Recalculate Investor Units
    ↓
00:05 UTC - Create Daily Profit Snapshots
    ↓
00:10 UTC - Process Profit Distribution
    ↓
00:15 UTC - Update Investor Balances
```

### 3. Profit Distribution Architecture
```
Daily Snapshot Created
    ↓
Calculate Total Profit
    ↓
Split: 20% Safety Fund, 5% Referral, 75% Sharing
    ↓
Distribute Sharing Profit: 50% Investor, 45% ECM
    ↓
Update Investor Profit Logs
    ↓
Mark Distribution as COMPLETED
```

### 4. Historical Analytics Architecture
```
Daily Snapshots (Real-time)
    ↓
Aggregate to Weekly/Monthly (Scheduled Job)
    ↓
Calculate Performance Metrics
    ↓
Generate Reports
    ↓
Archive Old Data
```

## Best Practices for Snapshot Fault Tolerance

### 1. Duplicate Prevention
```php
$snapshot = DailyProfitSnapshot::firstOrCreate(
    [
        'mt5_account_id' => $accountId,
        'snapshot_date' => $today,
    ],
    $snapshotData
);
```

### 2. Transaction Safety
```php
DB::transaction(function () use ($account, $data) {
    $snapshot = DailyProfitSnapshot::create($data);
    $account->update(['last_profit_snapshot_at' => now()]);
});
```

### 3. Error Handling
```php
try {
    $snapshot = $this->createSnapshot($account);
    Log::info("Snapshot created", ['snapshot_id' => $snapshot->id]);
} catch (\Exception $e) {
    Log::error("Snapshot creation failed", [
        'account_id' => $account->id,
        'error' => $e->getMessage()
    ]);
    throw $e;
}
```

### 4. Validation
```php
$validator = Validator::make($data, [
    'balance' => 'required|numeric|min:0',
    'equity' => 'required|numeric|min:0',
    'margin' => 'required|numeric|min:0',
    'free_margin' => 'required|numeric|min:0',
    'floating_profit' => 'required|numeric',
    'daily_profit' => 'required|numeric',
]);
```

### 5. Retry Logic
```php
$attempts = 0;
$maxAttempts = 3;

while ($attempts < $maxAttempts) {
    try {
        $snapshot = DailyProfitSnapshot::create($data);
        break;
    catch (\Exception $e) {
        $attempts++;
        if ($attempts >= $maxAttempts) {
            throw $e;
        }
        sleep(2);
    }
}
```

### 6. Data Validation
```php
// Ensure financial consistency
if ($snapshot->equity < 0) {
    throw new \Exception("Equity cannot be negative");
}

if ($snapshot->balance < 0) {
    throw new \Exception("Balance cannot be negative");
}
```

## Checklist for Other Possible NOT NULL Financial Fields

### Tables to Check:

**mt5_accounts**
- [ ] balance → default(0)
- [ ] equity → default(0)
- [ ] margin → default(0)
- [ ] free_margin → default(0)
- [ ] floating_profit → default(0)

**trades**
- [ ] volume → default(0)
- [ ] open_price → default(0)
- [ ] current_price → default(0)
- [ ] profit → default(0)
- [ ] swap → default(0)
- [ ] commission → default(0)

**trade_histories**
- [ ] volume → default(0)
- [ ] open_price → default(0)
- [ ] close_price → default(0)
- [ ] profit → default(0)
- [ ] swap → default(0)
- [ ] commission → default(0)

**profit_distributions**
- [ ] total_equity → default(0)
- [ ] previous_equity → default(0)
- [ ] total_profit → default(0)
- [ ] safety_fund → default(0)
- [ ] sharing_profit → default(0)
- [ ] referral_share → default(0)
- [ ] investor_share → default(0)
- [ ] ecm_share → default(0)
- [ ] total_units → default(0)

**investor_profit_logs**
- [ ] allocated_amount → default(0)

**safety_funds**
- [ ] amount → default(0)

**referral_commissions**
- [ ] amount → default(0)

## Migration Commands

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

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

### Rollback Last Migration
```bash
php artisan migrate:rollback
```

### Reset All Migrations
```bash
php artisan migrate:reset
```

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

## Testing Snapshot Creation

### Manual Test (Tinker)
```php
$account = App\Models\Mt5Account::first();
$snapshot = app(App\Services\Mt5SyncService::class)->createDailySnapshot($account);
echo $snapshot->id;
```

### Via API
```bash
curl -X POST http://127.0.0.1:8000/api/v1/mt5/sync \
  -H "Content-Type: application/json" \
  -d '{
    "api_key": "mt5-bridge",
    "timestamp": 1234567890,
    "account": {
      "account_number": "198145899",
      "account_name": "Test Account",
      "server": "HFMarketsGlobal-Live16",
      "balance": 4510,
      "equity": 4510,
      "margin": 0,
      "free_margin": 4510,
      "floating_profit": 0,
      "currency": "USD",
      "leverage": 100
    },
    "trades": []
  }'
```

### Check Database
```bash
php artisan tinker
>>> App\Models\DailyProfitSnapshot::latest()->first()
```

## Next Steps

1. ✅ Migration updated with default values
2. ✅ Database migrated successfully
3. ⏳ Test snapshot creation via queue worker
4. ⏳ Test profit distribution calculation
5. ⏳ Verify complete MT5 sync pipeline