# Trade History Architecture

## Why Trade History is Critical in Hedge Fund/Trading Backend Systems

### 1. Performance Analysis
- Track win/loss ratio per symbol, strategy, or time period
- Calculate average profit/loss per trade
- Analyze trade duration and holding periods
- Identify profitable vs unprofitable trading patterns

### 2. Regulatory Compliance
- Maintain complete audit trail of all closed trades
- Required for tax reporting and financial statements
- Proof of trading activities for investors
- Compliance with financial regulations (FCA, SEC, etc.)

### 3. Investor Reporting
- Show detailed trade history to investors
- Generate monthly/yearly trade statements
- Calculate returns based on historical trades
- Provide transparency on trading activities

### 4. Risk Management
- Track maximum drawdown from trade history
- Analyze losing streaks and recoveries
- Calculate risk metrics (Sharpe ratio, Sortino ratio)
- Identify risky trading patterns

### 5. Strategy Optimization
- Backtest trading strategies using historical data
- Compare performance across different time periods
- Identify which symbols/pairs are most profitable
- Optimize entry/exit points

## Architecture Overview

### Trade Flow
```
MT5 Terminal → Python Bridge → Laravel API → Queue Worker → ProcessMt5Sync
    ↓
Open Trades (trades table) → Close Position → Move to History (trade_histories table)
```

### Data Persistence Strategy
- **Active Trades**: Stored in `trades` table (real-time updates)
- **Closed Trades**: Moved to `trade_histories` table (archival)
- **Soft Deletes**: `deleted_at` column for recovery capability
- **Indexes**: Optimized for queries by account, symbol, date

## Database Schema

### trade_histories Table
```sql
CREATE TABLE trade_histories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mt5_account_id BIGINT UNSIGNED NOT NULL,
    ticket VARCHAR(255) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    type ENUM('BUY', 'SELL') NOT NULL,
    volume DECIMAL(10, 2) NOT NULL,
    open_price DECIMAL(15, 5) NOT NULL,
    close_price DECIMAL(15, 5) NOT NULL,
    profit DECIMAL(15, 2) NOT NULL,
    swap DECIMAL(15, 2) DEFAULT 0,
    commission DECIMAL(15, 2) DEFAULT 0,
    open_time TIMESTAMP NULL,
    close_time TIMESTAMP NULL,
    comment VARCHAR(255) NULL,
    synced_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (mt5_account_id) REFERENCES mt5_accounts(id) ON DELETE CASCADE,
    INDEX idx_account_close_time (mt5_account_id, close_time),
    INDEX idx_ticket (ticket)
);
```

### Key Design Decisions

**Decimal Precision**
- Volume: DECIMAL(10, 2) - Supports up to 99,999,999.99 lots
- Prices: DECIMAL(15, 5) - Supports 5 decimal places (common in forex)
- Profit: DECIMAL(15, 2) - Standard financial precision

**Nullable Timestamps**
- `open_time` nullable: Historical data may be incomplete
- `close_time` nullable: For data migration scenarios

**Soft Deletes**
- Allows recovery of accidentally deleted trades
- Maintains audit trail
- Supports data retention policies

**Indexes**
- Composite index on (mt5_account_id, close_time) for date range queries
- Index on ticket for fast lookups by MT5 ticket number

## Model Architecture

### TradeHistory Model
```php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\SoftDeletes;

class TradeHistory extends Model
{
    use SoftDeletes;

    protected $fillable = [
        'mt5_account_id',
        'ticket',
        'symbol',
        'type',
        'volume',
        'open_price',
        'close_price',
        'profit',
        'swap',
        'commission',
        'open_time',
        'close_time',
        'comment',
        'synced_at',
    ];

    protected $casts = [
        'volume' => 'decimal:2',
        'open_price' => 'decimal:5',
        'close_price' => 'decimal:5',
        'profit' => 'decimal:2',
        'swap' => 'decimal:2',
        'commission' => 'decimal:2',
        'open_time' => 'datetime',
        'close_time' => 'datetime',
        'synced_at' => 'datetime',
    ];

    public function mt5Account(): BelongsTo
    {
        return $this->belongsTo(Mt5Account::class);
    }
}
```

### Mt5Account Model (Updated)
```php
namespace App\Models;

use App\Models\TradeHistory;

class Mt5Account extends Model
{
    public function tradeHistories(): HasMany
    {
        return $this->hasMany(TradeHistory::class);
    }
}
```

## Trade Lifecycle Management

### 1. Open Trade
```python
# Python MT5 Bridge
trade = {
    'ticket': '12345',
    'symbol': 'EURUSD',
    'type': 'BUY',
    'volume': 0.1,
    'open_price': 1.0850,
    'status': 'OPEN'
}
```

### 2. Sync to Laravel
```php
// Mt5SyncService
$trade = Trade::updateOrCreate(
    ['ticket' => $tradeData['ticket']],
    $tradeData
);
```

### 3. Close Trade
```python
# Python detects closed trade
closed_trade = {
    'ticket': '12345',
    'close_price': 1.0860,
    'profit': 10.00,
    'status': 'CLOSED'
}
```

### 4. Move to History
```php
// Mt5SyncService
public function moveTradeToHistory(Trade $trade): TradeHistory
{
    $history = TradeHistory::create([
        'mt5_account_id' => $trade->mt5_account_id,
        'ticket' => $trade->ticket,
        'symbol' => $trade->symbol,
        'type' => $trade->type,
        'volume' => $trade->volume,
        'open_price' => $trade->open_price,
        'close_price' => $trade->close_price,
        'profit' => $trade->profit,
        'swap' => $trade->swap,
        'commission' => $trade->commission,
        'open_time' => $trade->open_time,
        'close_time' => $trade->close_time,
        'comment' => $trade->comment,
        'synced_at' => now(),
    ]);

    $trade->delete(); // Remove from active trades

    return $history;
}
```

## Query Scopes

### Common Queries
```php
// Get trade history for account
$history = TradeHistory::forAccount($accountId)->get();

// Get trades for specific symbol
$eurusdTrades = TradeHistory::forSymbol('EURUSD')->get();

// Get only BUY trades
$buyTrades = TradeHistory::forType('BUY')->get();

// Get trade by ticket
$trade = TradeHistory::byTicket('12345')->first();

// Get trades in date range
$trades = TradeHistory::whereBetween('close_time', [$from, $to])->get();

// Get profitable trades
$profitable = TradeHistory::where('profit', '>', 0)->get();

// Get losing trades
$losing = TradeHistory::where('profit', '<', 0)->get();
```

## Performance Metrics

### Win Rate
```php
$totalTrades = TradeHistory::forAccount($accountId)->count();
$winningTrades = TradeHistory::forAccount($accountId)->where('profit', '>', 0)->count();
$winRate = ($winningTrades / $totalTrades) * 100;
```

### Average Profit
```php
$avgProfit = TradeHistory::forAccount($accountId)->avg('profit');
```

### Total Profit by Symbol
```php
$symbolProfits = TradeHistory::forAccount($accountId)
    ->selectRaw('symbol, SUM(profit) as total_profit')
    ->groupBy('symbol')
    ->orderBy('total_profit', 'desc')
    ->get();
```

### Monthly Performance
```php
$monthlyPerformance = TradeHistory::forAccount($accountId)
    ->selectRaw('DATE_FORMAT(close_time, "%Y-%m") as month, SUM(profit) as profit')
    ->where('close_time', '>=', $startDate)
    ->groupBy('month')
    ->get();
```

## Best Practices

### 1. Atomic Operations
```php
DB::transaction(function () use ($trade) {
    $history = $this->moveTradeToHistory($trade);
    $this->updateAccountMetrics($trade->mt5_account_id);
});
```

### 2. Duplicate Prevention
```python
# Python: Check if trade already exists in history
if ticket in existing_history_tickets:
    skip_trade()
```

### 3. Error Handling
```php
try {
    $history = TradeHistory::create($data);
    Log::info("Trade history created", ['history_id' => $history->id]);
} catch (\Exception $e) {
    Log::error("Trade history creation failed", [
        'ticket' => $data['ticket'],
        'error' => $e->getMessage()
    ]);
    throw $e;
}
```

### 4. Data Validation
```php
$validator = Validator::make($data, [
    'ticket' => 'required|string|unique:trade_histories,ticket,NULL,id,mt5_account_id,' . $accountId,
    'symbol' => 'required|string|max:50',
    'type' => 'required|in:BUY,SELL',
    'volume' => 'required|numeric|min:0.01',
    'open_price' => 'required|numeric|min:0',
    'close_price' => 'required|numeric|min:0',
]);
```

## Data Retention Strategy

### Active Period (0-90 days)
- Keep in `trade_histories` table
- Full query capabilities
- Used for reporting and analysis

### Archive Period (90 days - 7 years)
- Keep in `trade_histories` table
- May move to archive database
- Used for compliance and historical analysis

### Long-term Archive (7+ years)
- Move to cold storage (S3, Glacier)
- Compressed format
- Retained for regulatory compliance

## Troubleshooting

### Issue: Duplicate ticket numbers
**Solution**: Use unique constraint on (mt5_account_id, ticket) or check before insert

### Issue: Missing close_time for closed trades
**Solution**: Validate that close_time is set for trades with status 'CLOSED'

### Issue: Profit calculation mismatch
**Solution**: Ensure profit includes swap and commission

### Issue: Large query performance
**Solution**: Add appropriate indexes, consider pagination, use date range filters

## Queue Restart and Retry Commands

### Restart Queue Worker
```bash
# Stop current worker (Ctrl+C)
# Start new worker
php artisan queue:work
```

### Retry Failed Jobs
```bash
# List failed jobs
php artisan queue:failed

# Retry all failed jobs
php artisan queue:retry all

# Retry specific job
php artisan queue:retry <job-id>

# Flush failed jobs
php artisan queue:flush
```

### Monitor Queue
```bash
# Check queue status
php artisan queue:work --status

# Clear queue
php artisan queue:clear
```

## Testing Checklist

- [ ] TradeHistory model exists with correct namespace
- [ ] Migration runs successfully with softDeletes
- [ ] Relationships are defined in Mt5Account model
- [ ] Mt5SyncService imports TradeHistory correctly
- [ ] Trade history is created when trades close
- [ ] Open trades are deleted after moving to history
- [ ] Query scopes work correctly
- [ ] Indexes improve query performance
- [ ] Soft deletes work correctly
- [ ] Error handling logs failures
- [ ] Queue worker processes trade history jobs successfully

## Production Considerations

### 1. Index Optimization
- Monitor slow queries
- Add composite indexes for common query patterns
- Consider partitioning by date for large datasets

### 2. Data Archival
- Implement automated archival process
- Archive trades older than 90 days
- Keep recent trades in hot storage

### 3. Backup Strategy
- Daily backup of trade_histories table
- Point-in-time recovery capability
- Test restore procedures regularly

### 4. Monitoring
- Monitor trade history creation rate
- Alert on failed trade history syncs
- Track storage usage
- Monitor query performance