# Unified Email Tracking System

## Overview

The system now supports **two types** of email tracking in a **single unified route**:

1. **Auto-Quotation Emails** - New system using `AutoQuoteEmail` model
2. **CRM Emails** - Existing system using `CrmEmail` model with scoring rules

## 🎯 How It Works

When a tracking URL is hit, the system automatically detects which type of email it is and applies the appropriate tracking logic.

### Flow Diagram

```
User clicks/opens email
    ↓
Tracking route hit: /track/logo/{id} or /track/click/{id}
    ↓
Check: Is this AutoQuoteEmail? (ID lookup)
    ↓
YES → Use EmailTrackingService
    ├─ Log to auto_quote_email_events
    ├─ Update aggregate counts
    └─ Log activity for seller dashboard
    ↓
NO → Check: Is this CrmEmail?
    ↓
YES → Use CRM Scoring System
    ├─ Update email status to 'opened'
    ├─ Fetch active CrmScoringRules for user
    ├─ Calculate points from lead_json_fields
    ├─ Create EmailScoreTracking records
    └─ Log for CRM analytics
    ↓
Return appropriate logo image
```

## 📍 Unified Routes

### 1. Logo Tracking Route

**Route:** `/track/logo/{id}`  
**Name:** `email.track.logo`  
**Method:** GET

#### Supports:
- **Auto-Quotation Emails**: Tracks opens via `EmailTrackingService`
- **CRM Emails**: Updates status + processes scoring rules

#### Usage in Email Templates:

```blade
{{-- Auto-Quotation Emails --}}
<img src="{{ url('/track/logo/' . $trackingId) }}" alt="Logo" />

{{-- CRM Emails (existing) --}}
<img src="{{ url('/track-logo?query_id=' . $emailId) }}" alt="Logo" />
```

#### Image Priority:
1. `public/assets/powercozmo-logo-track.png`
2. `public/frontend/images/logo.png`
3. `public/pixel.png`
4. Base64 encoded 1x1 transparent pixel (fallback)

### 2. Click Tracking Route

**Route:** `/track/click/{id}`  
**Name:** `email.track.click`  
**Method:** GET

#### Supports:
- **Auto-Quotation**: Redirects to `/quotations/view/{quotation_number}`
- **CRM Emails**: Redirects to `/crm/leads/{lead_id}` or `/crm/deals/{deal_id}`

#### CRM Click Scoring:
- Points extracted from `lead_json_fields[1]['point']` (index 1 for clicks)
- Uses `updateOrCreate` to avoid duplicate records
- Updates `is_email_click` and `email_click_point`

### 3. Backward Compatible Route

**Route:** `/track-logo` (with query parameter)  
**Name:** `track.logo`  
**Method:** GET  
**Query Param:** `query_id`

This route is kept for **backward compatibility** with old CRM emails that use the query parameter format.

```
Example: /track-logo?query_id=12345
```

## 🗄️ Database Models

### Auto-Quotation Email
```php
Model: App\Models\Crm\Quote\AutoQuoteEmail
Connection: 'crm'
Table: auto_quote_emails

Fields:
- id (tracking ID)
- quotation_number
- recipient_email
- open_count, click_count, download_count
- first_opened_at, last_opened_at
- first_clicked_at, last_clicked_at
```

### CRM Email
```php
Model: App\Models\Crm\CrmEmail
Table: crm_emails (or default connection)

Fields:
- id (tracking ID)
- user_id (for fetching scoring rules)
- lead_id, deal_id
- email_status ('opened', etc.)
- status ('opened', etc.)
```

### CRM Scoring Rule
```php
Model: App\Models\Crm\CrmScoringRule

Fields:
- id
- user_id
- status ('activate')
- lead_json_fields (JSON with points)
  Example: [
    {"action": "email_open", "point": 5},
    {"action": "email_click", "point": 10}
  ]
```

### Email Score Tracking
```php
Model: App\Models\Crm\EmailScoreTracking

Fields:
- score_rule_id
- lead_id, deal_id
- email_id
- email_open_point
- is_email_open (1 or 0)
- email_click_point
- is_email_click (1 or 0)
- is_condition_match
- condition_match
- positive_touch_point
```

## 🔍 Scoring System Logic

### Email Open Points

When a CRM email is opened via logo:

1. Fetch all active `CrmScoringRule` for the email's user
2. Parse `lead_json_fields` array
3. Extract points from index `[0]['point']` (email open)
4. Create `EmailScoreTracking` record with:
   - `is_email_open = 1`
   - `email_open_point = {calculated_points}`

### Email Click Points

When a CRM email link is clicked:

1. Fetch all active `CrmScoringRule` for the email's user
2. Parse `lead_json_fields` array
3. Extract points from index `[1]['point']` (email click)
4. Update or create `EmailScoreTracking` record with:
   - `is_email_click = 1`
   - `email_click_point = {calculated_points}`

## 📊 Tracking Comparison

| Feature | Auto-Quotation | CRM Email |
|---------|----------------|-----------|
| **Model** | AutoQuoteEmail | CrmEmail |
| **Service** | EmailTrackingService | Direct model update |
| **Events Log** | ✅ Detailed events table | ❌ No separate events |
| **Scoring** | ❌ No scoring | ✅ CRM scoring rules |
| **Aggregate Stats** | ✅ Counts + timestamps | ❌ Status only |
| **Activity Log** | ✅ Seller dashboard | ❌ Not applicable |
| **URL Format** | `/track/logo/{id}` | `/track-logo?query_id={id}` |

## 🚀 Migration Path

### For New Auto-Quotation Emails
```blade
{{-- Use the new format --}}
<img src="{{ url('/track/logo/' . $trackingId) }}" alt="Logo" />
<a href="{{ url('/track/click/' . $trackingId) }}">View Quotation</a>
```

### For Existing CRM Emails
```blade
{{-- Old format still works --}}
<img src="{{ url('/track-logo?query_id=' . $emailId) }}" alt="Logo" />

{{-- Or migrate to new format --}}
<img src="{{ url('/track/logo/' . $emailId) }}" alt="Logo" />
```

## 💡 Best Practices

### 1. Email Template Usage

**Auto-Quotation:**
```blade
@if (isset($trackingId))
    <img src="{{ url('/track/logo/' . $trackingId) }}" width="300" />
@endif
```

**CRM Email:**
```blade
@if (isset($emailId))
    <img src="{{ url('/track/logo/' . $emailId) }}" width="300" />
@endif
```

### 2. Scoring Rule Configuration

Ensure `lead_json_fields` in `CrmScoringRule` follows this structure:

```json
[
  {
    "action": "email_open",
    "point": 5,
    "description": "Email opened"
  },
  {
    "action": "email_click", 
    "point": 10,
    "description": "Link clicked"
  }
]
```

**Index 0** = Email open points  
**Index 1** = Email click points

### 3. Error Handling

All routes include try-catch blocks that:
- Log errors without breaking email display
- Return fallback images on failure
- Redirect to safe pages on tracking failures

### 4. Testing

```bash
# Test auto-quotation tracking
curl http://yourdomain.com/track/logo/123

# Test CRM tracking (old format)
curl http://yourdomain.com/track-logo?query_id=456

# Test CRM tracking (new format)
curl http://yourdomain.com/track/logo/456
```

## 🔧 Configuration

### Logo Image Locations (Priority Order)

1. `public/assets/powercozmo-logo-track.png` - Primary for CRM
2. `public/frontend/images/logo.png` - Primary for auto-quotation
3. `public/pixel.png` - Fallback pixel
4. Base64 embedded pixel - Final fallback

Ensure at least one of these files exists.

## 📈 Analytics Queries

### CRM Email Scoring Stats
```sql
SELECT 
    sr.id as rule_id,
    COUNT(DISTINCT est.email_id) as emails_opened,
    SUM(est.email_open_point) as total_open_points,
    COUNT(CASE WHEN est.is_email_click = 1 THEN 1 END) as emails_clicked,
    SUM(est.email_click_point) as total_click_points
FROM crm_scoring_rules sr
LEFT JOIN email_score_tracking est ON sr.id = est.score_rule_id
WHERE sr.status = 'activate'
GROUP BY sr.id;
```

### Auto-Quotation Stats
```sql
SELECT 
    COUNT(*) as total_emails,
    SUM(open_count) as total_opens,
    SUM(click_count) as total_clicks,
    ROUND(AVG(open_count), 2) as avg_opens_per_email
FROM auto_quote_emails
WHERE sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
```

## 🐛 Troubleshooting

### Issue: Logo not tracking for CRM emails
**Solution:** Check if `CrmEmail` model exists and has `user_id` field

### Issue: Scoring not working
**Solution:** 
1. Verify `CrmScoringRule` status is 'activate'
2. Check `lead_json_fields` JSON format
3. Ensure `EmailScoreTracking` model exists

### Issue: Both routes return 404
**Solution:** Run `php artisan route:cache` to refresh routes

### Issue: Image not loading
**Solution:** Verify at least one logo file exists in the priority list

## 🔄 Future Enhancements

- [ ] Unify both systems under single tracking service
- [ ] Add download tracking for CRM attachments
- [ ] Dashboard showing both tracking types
- [ ] Webhooks for scoring events
- [ ] A/B testing for email templates
- [ ] Geolocation based on IP address

## 📝 Related Files

- `routes/web.php` (lines 43-400) - All tracking routes
- `app/Service/AutoQuote/EmailTrackingService.php` - Auto-quote tracking service
- `app/Services/AutoQuotation/Helpers/EmailTrigger.php` - Email sending with tracking
- `app/Models/Crm/CrmEmail.php` - CRM email model
- `app/Models/Crm/CrmScoringRule.php` - Scoring rules model
- `app/Models/Crm/EmailScoreTracking.php` - Score tracking model
- `resources/views/emails/layouts/auto_quotation.blade.php` - Email template

---

**Last Updated:** January 15, 2026  
**Version:** 3.0 - Unified System  
**Status:** ✅ Production Ready