# Email Tracking Testing Guide

## Quick Testing Routes

Special routes have been added to test email tracking without sending actual emails.

### Test Routes

1. **`/test-email-tracking`** - Complete email tracking simulation
2. **`/test-email-tracking/preview`** - Preview email with tracking embedded
3. **`/test-email-tracking/stats/{emailId}`** - View tracking statistics

---

## Step-by-Step Testing

### Method 1: Using Test Routes (Recommended)

#### Step 1: Generate Test Email

Visit: `http://yourdomain.com/test-email-tracking`

This will:
- Create a test email record in database
- Generate an email with tracking pixel and links
- Display the email preview
- Show tracking URLs

**Expected Output:**
```
Email Tracking Test Created Successfully!

Email ID: 123
Quotation Number: TEST-000123

Tracking URLs:
- Open Tracking: http://yourdomain.com/track/open/123
- Click Tracking: http://yourdomain.com/track/click/123
- Download Tracking: http://yourdomain.com/track/download/123

[Email Preview with HTML]

Test Instructions:
1. Click the tracking URLs above
2. Check stats at: /test-email-tracking/stats/123
```

#### Step 2: Test Email Open

Click or visit: `http://yourdomain.com/track/open/123`

**Expected Behavior:**
- Returns a 1x1 transparent pixel image
- Creates record in `auto_quote_email_events` table
- Increments `open_count` in `auto_quote_emails` table
- Sets `first_opened_at` and `last_opened_at` timestamps

**How to Verify:**
```sql
-- Check email record
SELECT id, open_count, first_opened_at, last_opened_at 
FROM auto_quote_emails 
WHERE id = 123;

-- Check event records
SELECT * FROM auto_quote_email_events 
WHERE auto_quote_email_id = 123 AND event_type = 'open';
```

#### Step 3: Test Link Click

Click or visit: `http://yourdomain.com/track/click/123`

**Expected Behavior:**
- Redirects to quotation page
- Creates click event record
- Increments `click_count`
- Updates click timestamps

**How to Verify:**
```sql
-- Check email record
SELECT id, click_count, first_clicked_at, last_clicked_at 
FROM auto_quote_emails 
WHERE id = 123;

-- Check event records
SELECT * FROM auto_quote_email_events 
WHERE auto_quote_email_id = 123 AND event_type = 'click';
```

#### Step 4: Test Download Tracking

Click or visit: `http://yourdomain.com/track/download/123`

**Expected Behavior:**
- Redirects to download page
- Creates download event record
- Increments `download_count`
- Updates download timestamps

**How to Verify:**
```sql
-- Check email record
SELECT id, download_count, first_downloaded_at, last_downloaded_at 
FROM auto_quote_emails 
WHERE id = 123;

-- Check event records
SELECT * FROM auto_quote_email_events 
WHERE auto_quote_email_id = 123 AND event_type = 'download';
```

#### Step 5: View Statistics

Visit: `http://yourdomain.com/test-email-tracking/stats/123`

**Expected Output:**
```
Email Tracking Statistics

Email ID: 123
Quotation: TEST-000123
Recipient: test@example.com
Sent At: 2026-01-13 15:30:00

📊 Summary:
- Opens: 5 (First: 2026-01-13 15:31:00, Last: 2026-01-13 15:45:00)
- Clicks: 3 (First: 2026-01-13 15:32:00, Last: 2026-01-13 15:40:00)
- Downloads: 2 (First: 2026-01-13 15:33:00, Last: 2026-01-13 15:38:00)

📋 Detailed Events:
1. [2026-01-13 15:31:00] OPEN from 192.168.1.1 (Chrome/Mac)
2. [2026-01-13 15:32:00] CLICK from 192.168.1.1 (Chrome/Mac)
3. [2026-01-13 15:33:00] DOWNLOAD from 192.168.1.1 (Chrome/Mac)
...
```

---

### Method 2: Manual Database Testing

#### Step 1: Create Test Email Record

```sql
INSERT INTO auto_quote_emails (
    auto_quote_workflow_id,
    quotation_number,
    recipient_email,
    email_body,
    sent_at,
    created_at,
    updated_at
) VALUES (
    1,
    'TEST-000001',
    'test@example.com',
    '<p>Test email</p>',
    NOW(),
    NOW(),
    NOW()
);

-- Get the inserted ID
SELECT LAST_INSERT_ID();
```

#### Step 2: Test Tracking URLs

Replace `{ID}` with the ID from Step 1:

```bash
# Test open tracking
curl -I http://yourdomain.com/track/open/{ID}

# Test click tracking (in browser)
# Visit: http://yourdomain.com/track/click/{ID}

# Test download tracking (in browser)
# Visit: http://yourdomain.com/track/download/{ID}
```

#### Step 3: Verify Database Changes

```sql
-- Check aggregate counts
SELECT 
    id,
    open_count,
    click_count,
    download_count,
    first_opened_at,
    last_opened_at
FROM auto_quote_emails
WHERE id = {ID};

-- Check individual events
SELECT 
    event_type,
    ip_address,
    user_agent,
    created_at
FROM auto_quote_email_events
WHERE auto_quote_email_id = {ID}
ORDER BY created_at DESC;
```

---

### Method 3: Integration Testing (Real Email)

#### Step 1: Trigger Auto-Quotation

```bash
# Run the auto-quotation command
php artisan auto-quote:process --seller=2091 --limit=1
```

#### Step 2: Check Email in Database

```sql
-- Get the latest email
SELECT * FROM auto_quote_emails 
ORDER BY id DESC 
LIMIT 1;
```

#### Step 3: Send Test Email to Yourself

Use a tool like Mailtrap or send to your own email:

```php
// In tinker: php artisan tinker
$email = \App\Models\Crm\Quote\AutoQuoteEmail::latest()->first();
$emailBody = $email->email_body;

// Add tracking manually if not already added
$trackingPixel = '<img src="' . url('/track/open/' . $email->id) . '" width="1" height="1" style="display:none;" />';
$emailBody .= $trackingPixel;

// Send to yourself
Mail::send([], [], function ($message) use ($emailBody) {
    $message->to('your-email@example.com')
        ->subject('Email Tracking Test')
        ->html($emailBody);
});
```

#### Step 4: Open Email and Click Links

1. Open the email in your email client
2. Enable images if blocked
3. Click any links in the email
4. Check tracking stats

---

## Verification Checklist

### ✅ Basic Functionality

- [ ] Tracking pixel route returns 200 OK
- [ ] Pixel file exists at `public/pixel.png`
- [ ] Click tracking redirects correctly
- [ ] Download tracking redirects correctly
- [ ] Database records are created
- [ ] Timestamps are updated correctly

### ✅ Data Accuracy

- [ ] `open_count` increments on each pixel load
- [ ] `click_count` increments on each link click
- [ ] `download_count` increments on each download
- [ ] `first_opened_at` is set only once
- [ ] `last_opened_at` updates on each open
- [ ] Event records contain correct IP and user agent

### ✅ Edge Cases

- [ ] Multiple opens from same IP
- [ ] Multiple clicks in quick succession
- [ ] Invalid email ID returns error
- [ ] Missing quotation redirects safely
- [ ] Email client with images blocked

---

## Common Issues and Solutions

### Issue 1: Tracking pixel returns 404

**Cause:** Pixel file doesn't exist

**Solution:**
```bash
cd public
echo "iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNkYPhfDwAChwGA60e6kgAAAABJRU5ErkJggg==" | base64 -d > pixel.png
```

### Issue 2: No events recorded

**Cause:** EmailTrackingService class not found

**Solution:**
```bash
# Check namespace
grep -r "namespace.*EmailTrackingService" app/

# Clear cache
php artisan cache:clear
php artisan config:clear
composer dump-autoload
```

### Issue 3: Redirect loop

**Cause:** Quotation page route not configured

**Solution:**
```php
// Check if route exists
php artisan route:list | grep quotations

// Add missing route if needed
Route::get('/quotations/view/{number}', 'QuotationController@view');
```

### Issue 4: Timestamps not updating

**Cause:** Database column doesn't exist

**Solution:**
```bash
# Run migration to add tracking columns
php artisan migrate --path=database/migrations/2026_01_13_154222_update_auto_quote_emails_table_make_template_id_nullable.php
```

---

## Testing Scenarios

### Scenario 1: Cold Lead (No Interaction)

**Expected Tracking:**
- Email sent: ✅
- Opens: 0
- Clicks: 0
- Downloads: 0

**What it means:** Email not opened (possibly spam, wrong address, or low interest)

---

### Scenario 2: Warm Lead (Opened, No Click)

**Expected Tracking:**
- Email sent: ✅
- Opens: 1-3
- Clicks: 0
- Downloads: 0

**What it means:** Buyer aware of quotation but not interested enough to view details

---

### Scenario 3: Hot Lead (Multiple Interactions)

**Expected Tracking:**
- Email sent: ✅
- Opens: 5+
- Clicks: 2+
- Downloads: 1+

**What it means:** High engagement, possibly comparing with competitors or sharing with team

---

### Scenario 4: Forwarded Email

**Expected Tracking:**
- Email sent: ✅
- Opens: 10+ (from different IPs)
- Clicks: Multiple from different IPs
- Downloads: 2+

**What it means:** Email shared with multiple people (good signal!)

---

## Performance Testing

### Load Test Tracking Endpoints

```bash
# Install Apache Bench (if needed)
# brew install apache-bench

# Test open tracking
ab -n 1000 -c 10 http://yourdomain.com/track/open/123

# Test click tracking
ab -n 100 -c 5 http://yourdomain.com/track/click/123
```

**Expected Performance:**
- Open tracking: < 50ms response time
- Click tracking: < 100ms response time (includes redirect)
- Database writes: < 20ms per event

---

## Monitoring & Alerts

### Set Up Monitoring

1. **Track Error Rates**
   ```bash
   # Count tracking errors in logs
   grep "Failed to track" storage/logs/laravel.log | wc -l
   ```

2. **Monitor Response Times**
   ```bash
   # Check slow queries
   tail -f storage/logs/laravel.log | grep "slow"
   ```

3. **Alert on High Volume**
   ```sql
   -- Emails with unusually high open counts (possible bot)
   SELECT * FROM auto_quote_emails 
   WHERE open_count > 50 
   AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR);
   ```

---

## Next Steps

After verifying tracking works:

1. ✅ Test with real emails
2. ✅ Monitor for 1 week
3. ✅ Analyze tracking data
4. ✅ Create dashboard for sellers
5. ✅ Implement alerts for hot leads
6. ✅ Set up A/B testing

---

## Support Commands

```bash
# Check tracking logs
tail -f storage/logs/laravel.log | grep track

# Count tracking events by type
mysql -u root -p -e "
  USE crm;
  SELECT event_type, COUNT(*) as count 
  FROM auto_quote_email_events 
  GROUP BY event_type;
"

# Find most tracked email
mysql -u root -p -e "
  USE crm;
  SELECT id, quotation_number, 
         open_count + click_count + download_count as total_events
  FROM auto_quote_emails 
  ORDER BY total_events DESC 
  LIMIT 10;
"

# Clear old test data
mysql -u root -p -e "
  USE crm;
  DELETE FROM auto_quote_emails 
  WHERE quotation_number LIKE 'TEST-%';
"
```

---

**Testing Checklist:**
- [ ] Test routes added
- [ ] Database connections verified
- [ ] Tracking pixel accessible
- [ ] Events recorded correctly
- [ ] Redirects working
- [ ] Stats page displays correctly
- [ ] Performance acceptable
- [ ] Ready for production

---

**Last Updated:** January 13, 2026