# Email Tracking Technical Documentation

## Overview

This document explains the technical implementation of email tracking for auto-quotation emails in the PowerCozmo system.

## Architecture

### Components

1. **EmailTrigger Service** (`app/Services/AutoQuotation/Helpers/EmailTrigger.php`)
   - Sends auto-quotation emails
   - Embeds tracking pixel and trackable links
   - Logs email sending to database

2. **EmailTrackingService** (`app/Service/AutoQuote/EmailTrackingService.php`)
   - Handles tracking events (open, click, download)
   - Records events to database
   - Updates aggregate statistics

3. **Database Tables**
   - `auto_quote_emails` - Stores email records and aggregate statistics
   - `auto_quote_email_events` - Stores individual tracking events

4. **Routes** (`routes/web.php`)
   - `/track/open/{id}` - Email open tracking
   - `/track/click/{id}` - Link click tracking
   - `/track/download/{id}` - File download tracking

---

## How Tracking Works

### 1. Email Sending Process

```
┌─────────────────────────────────────────────────────────────┐
│ 1. Auto-quotation is created                                │
│ 2. Email template is loaded                                 │
│ 3. Template variables are replaced                          │
│ 4. Email is logged to database (gets ID)                    │
│ 5. Tracking pixel and links are added using the email ID    │
│ 6. Email is sent with tracking embedded                     │
└─────────────────────────────────────────────────────────────┘
```

### 2. Tracking Pixel (Email Opens)

**How it works:**
- A 1x1 transparent PNG image is embedded at the end of the email HTML
- Image URL: `https://yourdomain.com/track/open/{email_id}`
- When recipient opens email, their email client loads the pixel
- Server receives the request and logs it as an "open" event

**Implementation:**
```php
// Tracking pixel HTML embedded in email
<img src="https://yourdomain.com/track/open/123" 
     width="1" height="1" style="display:none;" />
```

**Route Handler:**
```php
Route::get('/track/open/{id}', function ($id) {
    app(\App\Service\AutoQuote\EmailTrackingService::class)->trackOpen($id);
    return response()->file(public_path('pixel.png'));
});
```

**Data Tracked:**
- Timestamp of open
- IP address
- User agent (browser/email client)
- Number of times opened
- First open time
- Last open time

### 3. Click Tracking (Links)

**How it works:**
- All quotation links in email are replaced with tracking URLs
- Tracking URL: `https://yourdomain.com/track/click/{email_id}`
- When clicked, server logs the click event
- User is immediately redirected to actual quotation page

**Implementation:**
```php
// Original link in template
View your quotation: {{quotation_url}}

// After processing
View your quotation: https://yourdomain.com/track/click/123
```

**Route Handler:**
```php
Route::get('/track/click/{id}', function ($id) {
    app(\App\Service\AutoQuote\EmailTrackingService::class)->trackClick($id);
    $email = \App\Models\Crm\Quote\AutoQuoteEmail::find($id);
    if ($email) {
        return redirect('/quotations/view/' . $email->quotation_number);
    }
    return redirect('/');
});
```

**Data Tracked:**
- Timestamp of click
- IP address
- User agent
- Referrer URL
- Number of times clicked
- First click time
- Last click time

### 4. Download Tracking

**How it works:**
- Download links route through tracking endpoint
- Tracking URL: `https://yourdomain.com/track/download/{email_id}`
- Server logs the download event
- User is redirected to actual file download

**Route Handler:**
```php
Route::get('/track/download/{id}', function ($id) {
    app(\App\Service\AutoQuote\EmailTrackingService::class)->trackDownload($id);
    $email = \App\Models\Crm\Quote\AutoQuoteEmail::find($id);
    if ($email) {
        return redirect('/quotations/download/' . $email->quotation_number);
    }
    return redirect('/');
});
```

**Data Tracked:**
- Timestamp of download
- IP address
- User agent
- Number of downloads
- First download time
- Last download time

---

## Database Schema

### Table: `auto_quote_emails`

```sql
id                      BIGINT (Primary Key)
auto_quote_workflow_id  BIGINT (Foreign Key)
auto_quote_template_id  BIGINT (Foreign Key, nullable)
quotation_number        VARCHAR
recipient_email         VARCHAR
email_body              TEXT
sent_at                 TIMESTAMP

-- Tracking statistics
open_count              INT (default: 0)
first_opened_at         TIMESTAMP (nullable)
last_opened_at          TIMESTAMP (nullable)

click_count             INT (default: 0)
first_clicked_at        TIMESTAMP (nullable)
last_clicked_at         TIMESTAMP (nullable)

download_count          INT (default: 0)
first_downloaded_at     TIMESTAMP (nullable)
last_downloaded_at      TIMESTAMP (nullable)

created_at              TIMESTAMP
updated_at              TIMESTAMP
```

### Table: `auto_quote_email_events`

```sql
id                      BIGINT (Primary Key)
auto_quote_email_id     BIGINT (Foreign Key)
event_type              VARCHAR ('open', 'click', 'download')
ip_address              VARCHAR
user_agent              TEXT
referrer                VARCHAR (nullable)
created_at              TIMESTAMP
```

---

## Event Flow Diagram

```
┌──────────────┐
│ Email Sent   │
└──────┬───────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ Buyer Opens Email                       │
│ → Email client loads tracking pixel     │
│ → GET /track/open/123                   │
└──────┬──────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ EmailTrackingService::trackOpen()       │
│ → Creates event record                  │
│ → Increments open_count                 │
│ → Sets first_opened_at (if first time)  │
│ → Updates last_opened_at                │
└──────┬──────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ Returns 1x1 pixel image                 │
└─────────────────────────────────────────┘

┌──────────────┐
│ Buyer Clicks │
│ Link         │
└──────┬───────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ GET /track/click/123                    │
└──────┬──────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ EmailTrackingService::trackClick()      │
│ → Creates event record                  │
│ → Increments click_count                │
│ → Updates timestamps                    │
└──────┬──────────────────────────────────┘
       │
       ▼
┌─────────────────────────────────────────┐
│ Redirects to quotation page             │
└─────────────────────────────────────────┘
```

---

## Code Implementation Details

### EmailTrigger::send() Method

```php
public function send(Quotation $quotation, AutoQuotingSetting $settings, AutoQuoteWorkflow $workflow): bool
{
    // 1. Get buyer and template
    $buyer = User::find($quotation->buyerId);
    $template = $this->getEmailTemplate($workflow, $settings);
    
    // 2. Prepare email data
    $emailData = $this->prepareEmailData($quotation, $buyer, $settings);
    
    // 3. Parse template
    $emailBody = $this->parseTemplate($template->dynamic_body, $emailData);
    $emailSubject = $this->parseTemplate($template->subject, $emailData);
    
    // 4. Log email to database (gets ID)
    $emailLog = $this->logEmailSent($quotation, $workflow, $template, $buyer->email, $emailBody);
    
    // 5. Add tracking to email body
    if ($emailLog) {
        $emailBody = $this->addEmailTracking($emailBody, $emailLog->id);
    }
    
    // 6. Send email
    $sent = $this->sendEmail($buyer->email, $emailSubject, $emailBody, $quotation, $settings);
    
    return $sent;
}
```

### EmailTrigger::addEmailTracking() Method

```php
protected function addEmailTracking(string $emailBody, int $emailId): string
{
    // Add tracking pixel (invisible 1x1 image)
    $trackingPixel = '<img src="' . url('/track/open/' . $emailId) . '" 
                           width="1" height="1" style="display:none;" />';
    
    // Replace quotation URL with tracked link
    $trackingUrl = url('/track/click/' . $emailId);
    $emailBody = str_replace('{{quotation_url}}', $trackingUrl, $emailBody);
    
    // Inject tracking pixel before </body> or at end
    if (stripos($emailBody, '</body>') !== false) {
        $emailBody = str_replace('</body>', $trackingPixel . '</body>', $emailBody);
    } else {
        $emailBody .= $trackingPixel;
    }
    
    return $emailBody;
}
```

### EmailTrackingService::trackEvent() Method

```php
protected function trackEvent(int $emailId, string $type, array $meta)
{
    // 1. Find email record
    $email = AutoQuoteEmail::findOrFail($emailId);
    
    // 2. Create event record
    AutoQuoteEmailEvent::create([
        'auto_quote_email_id' => $email->id,
        'event_type' => $type,
        'ip_address' => $meta['ip'] ?? request()->ip(),
        'user_agent' => $meta['ua'] ?? request()->userAgent(),
        'referrer' => $meta['referrer'] ?? null,
    ]);
    
    // 3. Update aggregate statistics
    $this->updateAggregates($email, $type);
}
```

---

## Security Considerations

### 1. Privacy
- IP addresses are logged but can be anonymized if needed
- User agents contain browser/device information
- Consider GDPR compliance for EU users

### 2. Email Client Blocking
- Some email clients block external images by default
- Tracking pixels won't work until user enables images
- Apple Mail Privacy Protection may preload images

### 3. Rate Limiting
- Consider rate limiting tracking endpoints to prevent abuse
- Implement CSRF protection if needed

### 4. Email ID Exposure
- Email IDs in URLs are sequential (predictable)
- Consider using UUIDs or encrypted tokens for better security

---

## Limitations

### 1. Email Open Tracking
- **Not 100% accurate** - Depends on image loading
- Blocked by privacy features (Apple Mail, ProtonMail)
- May count multiple opens (forwarding, previewing)
- Desktop clients may cache images

### 2. Click Tracking
- More reliable than open tracking
- Requires user action (clicking link)
- Only tracks configured links

### 3. Download Tracking
- Only works if download goes through tracking URL
- Direct file access bypasses tracking

---

## Analytics & Reporting

### Key Metrics Available

1. **Email Performance**
   - Total emails sent
   - Open rate (opens / sent)
   - Click-through rate (clicks / opens)
   - Download rate (downloads / clicks)

2. **Engagement Timeline**
   - First interaction time
   - Last interaction time
   - Time between send and first open
   - Time between open and click

3. **Buyer Behavior**
   - Multiple opens (high interest indicator)
   - Multiple downloads (possibly sharing with team)
   - No opens (email issue or low interest)

### Sample Queries

```sql
-- Get open rate by seller
SELECT 
    u.name as seller_name,
    COUNT(e.id) as total_emails,
    SUM(CASE WHEN e.open_count > 0 THEN 1 ELSE 0 END) as opened_emails,
    ROUND(SUM(CASE WHEN e.open_count > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(e.id), 2) as open_rate
FROM auto_quote_emails e
JOIN auto_quote_workflows w ON e.auto_quote_workflow_id = w.id
JOIN users u ON w.seller_id = u.id
GROUP BY u.id, u.name
ORDER BY open_rate DESC;

-- Get average time to first open
SELECT 
    AVG(TIMESTAMPDIFF(MINUTE, e.sent_at, e.first_opened_at)) as avg_minutes_to_open
FROM auto_quote_emails e
WHERE e.first_opened_at IS NOT NULL;

-- Get most engaged buyers
SELECT 
    e.recipient_email,
    COUNT(DISTINCT e.id) as emails_received,
    SUM(e.open_count) as total_opens,
    SUM(e.click_count) as total_clicks,
    SUM(e.download_count) as total_downloads
FROM auto_quote_emails e
GROUP BY e.recipient_email
ORDER BY (SUM(e.open_count) + SUM(e.click_count) * 2 + SUM(e.download_count) * 3) DESC
LIMIT 20;
```

---

## Testing & Verification

See the **Testing Guide** section for step-by-step instructions on how to test email tracking.

---

## Troubleshooting

### Issue: Tracking pixel not working

**Possible causes:**
1. Email client blocks external images
2. Pixel file doesn't exist (`public/pixel.png`)
3. Route not accessible
4. CORS issues

**Solutions:**
1. Ask user to enable images in email client
2. Verify pixel file exists: `ls -la public/pixel.png`
3. Test route directly: `curl https://yourdomain.com/track/open/1`
4. Check server logs for errors

### Issue: Click tracking not redirecting

**Possible causes:**
1. Email record not found
2. Quotation page doesn't exist
3. Route configuration issue

**Solutions:**
1. Verify email ID exists in database
2. Check quotation_number is valid
3. Test route manually in browser

### Issue: Multiple opens recorded immediately

**Possible causes:**
1. Email security scanners pre-loading links
2. Email forwarded to multiple recipients
3. Preview pane loading images repeatedly

**Solutions:**
1. Consider rate limiting or deduplication
2. Track unique IPs/sessions
3. Add time threshold between opens

---

## Future Enhancements

1. **Advanced Tracking**
   - Link click heatmap (which links clicked most)
   - Email reading time estimation
   - Device/browser analytics

2. **Real-time Notifications**
   - Notify seller when buyer opens email
   - Alert on multiple opens (hot lead)
   - Webhook integrations

3. **A/B Testing**
   - Test different email templates
   - Compare subject lines
   - Optimize send times

4. **Privacy Enhancements**
   - Anonymize IP addresses
   - Opt-out mechanism
   - GDPR compliance features

5. **Security Improvements**
   - Use encrypted/signed tracking tokens
   - Implement expiring tracking links
   - Add bot detection

---

## API Reference

### EmailTrackingService Methods

#### `trackOpen(int $emailId, array $meta = [])`
Records an email open event.

**Parameters:**
- `$emailId` - The auto_quote_emails.id
- `$meta` - Optional metadata array (ip, ua, referrer)

**Returns:** void

---

#### `trackClick(int $emailId, array $meta = [])`
Records a link click event.

**Parameters:**
- `$emailId` - The auto_quote_emails.id
- `$meta` - Optional metadata array (ip, ua, referrer)

**Returns:** void

---

#### `trackDownload(int $emailId, array $meta = [])`
Records a file download event.

**Parameters:**
- `$emailId` - The auto_quote_emails.id
- `$meta` - Optional metadata array (ip, ua, referrer)

**Returns:** void

---

## Maintenance

### Regular Tasks

1. **Cleanup Old Events**
   ```sql
   -- Delete events older than 1 year
   DELETE FROM auto_quote_email_events 
   WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
   ```

2. **Monitor Tracking Errors**
   ```bash
   # Check Laravel logs for tracking errors
   tail -f storage/logs/laravel.log | grep "track"
   ```

3. **Verify Pixel Accessibility**
   ```bash
   curl -I https://yourdomain.com/track/open/1
   # Should return 200 OK
   ```

---

## Support

For questions or issues related to email tracking:
1. Check Laravel logs: `storage/logs/laravel.log`
2. Review database records in `auto_quote_emails` and `auto_quote_email_events`
3. Test tracking routes manually
4. Contact development team

---

**Last Updated:** January 13, 2026
**Version:** 1.0