ferrero-opentext/Python-Version/MARKDOWN_DOCS/A2_A3_CREATIVEX_INTEGRATION.md

# A2→A3 CreativeX Integration Guide

## Overview

The A2→A3 upload workflow now automatically looks up CreativeX scores from the database instead of reading from Box metadata templates.

## How It Works

### Old Method (Box Metadata Template)
❌ **Removed:** `box.get_file_metadata(file_id, template_name='Ferrero-DAM-Metadata')`
- Required manual metadata entry in Box
- Error-prone, easy to miss
- No history tracking

### New Method (Database Lookup)
✅ **Implemented:** `db.get_creativex_score_by_filename(clean_filename)`
- Automatic lookup by filename
- Always gets latest `active` score
- History preserved for audit
- Fallback to defaults if not found

## Workflow Sequence

### Step-by-Step:

1. **Agency uploads file to Box** (folder 348526703108)
   - Filename: `Brand_Country_Language_123456_OMG7654321_AbCdEf.mp4`

2. **A2→A3 script polls Box folder**
   - Parses filename to get tracking ID: `AbCdEf`
   - Strips upload components to get clean filename: `Brand_Country_Language_123456.mp4`

3. **Database lookup for CreativeX score**
   ```python
   creativex_data = db.get_creativex_score_by_filename("Brand_Country_Language_123456.mp4")
   ```

4. **Two scenarios:**

   **✅ Score Found in Database:**
   ```python
   {
       'filename': 'Brand_Country_Language_123456.mp4',
       'creativex_id': '6864255',
       'creativex_url': 'https://app.creativex.com/audit/scorecards/32456797',
       'quality_score': '85',
       'box_file_id': '2043306479381',
       'full_extraction_data': {...},  # Complete JSON
       'extracted_at': '2025-11-11 21:29:45'
   }
   ```
   - Uses score `85` and actual URL
   - Email shows: "✅ CreativeX Score Added: 85 (from database)"

   **⚠️ Score NOT Found:**
   ```python
   None  # No matching filename in database
   ```
   - Uses default score: `0`
   - Uses placeholder URL: `https://app.creativex.com/preflight/pretests`
   - Email shows orange warning box with instructions
   - Upload still succeeds (defaults allow it to pass)

5. **Metadata applied to DAM upload**
   - Field: `FERRERO.TAB.FIELD.CREATIVEX` = score value
   - Field: `FERRERO.FIELD.CREATIVEX LINK` = URL
   - Plus all other MVP fields

6. **File uploaded to DAM and deleted from Box**

## Code Changes

### A2→A3 Script Changes

**File:** `scripts/a2_to_a3_upload_polling.py`

**Lines 101-124:** Replaced Box metadata lookup with database lookup
```python
# Old code (removed):
# box_metadata = box.get_file_metadata(file_id, template_name='Ferrero-DAM-Metadata')

# New code:
clean_filename_for_lookup = parser.strip_upload_components(filename)
creativex_data = db.get_creativex_score_by_filename(clean_filename_for_lookup)

if creativex_data:
    box_metadata = {
        'score': creativex_data['quality_score'],
        'url': creativex_data['creativex_url']
    }
    creativex_found = True
else:
    box_metadata = {
        'score': '0',
        'url': 'https://app.creativex.com/preflight/pretests'
    }
    creativex_found = False
```

**Lines 193-202:** Pass CreativeX status to email
```python
return {
    'success': True,
    'creativex_found': creativex_found,
    'creativex_score': box_metadata.get('score', '0'),
    'creativex_url': box_metadata.get('url', '...')
}
```

### Email Template Changes

**File:** `scripts/shared/notifier.py`

**Template:** `a2_to_a3_file_uploaded`

**Added to checklist:**
```html
{% if creativex_found %}
<li>✅ <strong>CreativeX Score Added:</strong> {{ creativex_score }} (from database)</li>
{% else %}
<li>⚠️ <strong>CreativeX Score:</strong> Not found - used default (0)</li>
{% endif %}
```

**Added warning section:**
```html
{% if not creativex_found %}
<div style="background-color: #fff3e0; border-left: 4px solid #ff9800; padding: 15px;">
    <p><strong>⚠️ CreativeX Score Missing</strong></p>
    <p>No score found for: <code>{{ clean_filename }}</code></p>
    <p><strong>Default Values Used:</strong> Score: 0, URL: placeholder</p>
    <p><em>To add: Upload PDF to Box folder 350605024645 and run creativex_scoring_storing.py</em></p>
</div>
{% endif %}
```

## Default Values

When no CreativeX score is found:

| Field | Default Value | Purpose |
|-------|---------------|---------|
| Score | `0` | Indicates "not scored" but allows upload to proceed |
| URL | `https://app.creativex.com/preflight/pretests` | Valid CreativeX URL (preflight section) |

These defaults ensure uploads never fail due to missing CreativeX data.

## Database Query Details

### Query Used:
```sql
SELECT filename, creativex_id, creativex_url, quality_score,
       box_file_id, full_extraction_data, extracted_at
FROM creativex_scores
WHERE filename = %s AND status = 'active'
ORDER BY extracted_at DESC
LIMIT 1
```

### Key Points:
- ✅ Filters for `status = 'active'` → Only latest version
- ✅ Orders by `extracted_at DESC` → Most recent first
- ✅ `LIMIT 1` → Only one result
- ✅ Returns `None` if no match found

## Email Notification Examples

### When CreativeX Score Found:

```
What Was Done:
✅ Downloaded from Box processing folder (348526703108)
✅ Loaded master metadata from database (AbCdEf)
✅ Built asset representation with 27 MVP fields
✅ Updated Description from filename
✅ Updated Language from filename
✅ Set State to "Local"
✅ CreativeX Score Added: 85 (from database)
✅ Stripped OMG Job Number and Tracking ID from filename
✅ Uploaded to DAM Final Assets folder
✅ Deleted file from Box
```

### When CreativeX Score Missing:

```
What Was Done:
✅ Downloaded from Box processing folder (348526703108)
✅ Loaded master metadata from database (AbCdEf)
✅ Built asset representation with 27 MVP fields
✅ Updated Description from filename
✅ Updated Language from filename
✅ Set State to "Local"
⚠️ CreativeX Score: Not found - used default (0)
✅ Stripped OMG Job Number and Tracking ID from filename
✅ Uploaded to DAM Final Assets folder
✅ Deleted file from Box

[Orange Warning Box]
⚠️ CreativeX Score Missing
No CreativeX score found in database for: Brand_Country_Language_123456.mp4

Default Values Used:
• Score: 0
• URL: https://app.creativex.com/preflight/pretests

To add CreativeX score: Upload PDF report to Box folder 350605024645
and run creativex_scoring_storing.py
```

## Filename Matching Logic

### Important: Clean Filename Used for Lookup

The A2→A3 script receives files with upload components:
- **Box filename:** `Brand_Country_Language_123_OMG7654321_AbCdEf.mp4`
- **Clean filename:** `Brand_Country_Language_123.mp4` (stripped)

The database lookup uses the **clean filename** because:
1. CreativeX PDFs are named with clean filenames
2. Upload components (OMG Job, Tracking ID) are added later in workflow
3. Database stores what's in the PDF report (original clean name)

```python
clean_filename_for_lookup = parser.strip_upload_components(filename)
# "Brand_Country_Language_123_OMG7654321_AbCdEf.mp4"
# → "Brand_Country_Language_123.mp4"

creativex_data = db.get_creativex_score_by_filename(clean_filename_for_lookup)
```

## Testing Scenarios

### Scenario 1: File with CreativeX Score

1. Upload PDF to Box folder 350605024645 with filename: `Nutella_France_French_456.pdf`
2. Run: `python scripts/creativex_scoring_storing.py`
3. Verify in database: Score stored for `Nutella_France_French_456.mp4`
4. Agency uploads: `Nutella_France_French_456_OMG999_XyZ123.mp4` to A2→A3 folder
5. A2→A3 script:
   - Strips to: `Nutella_France_French_456.mp4`
   - Finds score in database
   - Uploads with actual CreativeX data
   - Email shows: "✅ CreativeX Score Added: 85"

### Scenario 2: File WITHOUT CreativeX Score

1. Agency uploads: `NewBrand_Italy_Italian_789_OMG888_Abc987.mp4` to A2→A3 folder
2. No PDF was ever uploaded to 350605024645 for this file
3. A2→A3 script:
   - Strips to: `NewBrand_Italy_Italian_789.mp4`
   - Database lookup returns `None`
   - Uses defaults: Score=0, URL=placeholder
   - Upload succeeds anyway
   - Email shows orange warning: "⚠️ CreativeX Score Missing"

## Advantages Over Box Metadata

| Aspect | Old (Box Metadata) | New (Database) |
|--------|-------------------|----------------|
| Source | Manual entry in Box | Automatic from LlamaExtract |
| Accuracy | Human error prone | AI extraction |
| History | No history | Full version history |
| Lookup Speed | Box API call | Fast database query |
| Fallback | Would fail if missing | Graceful defaults |
| Audit Trail | None | Complete with timestamps |
| Scalability | Manual per file | Batch processing |

## Production Deployment Notes

### Prerequisites:
1. CreativeX scoring system deployed (see CREATIVEX_DEPLOYMENT.md)
2. Database table `creativex_scores` created
3. At least some files scored (or expect warnings for missing scores)

### No Breaking Changes:
- A2→A3 workflow continues to work
- Missing scores use defaults (Score: 0)
- Uploads never fail due to missing CreativeX data
- Email clearly indicates when defaults are used

### Recommended Workflow:
1. Download master assets (A1→A2)
2. **Upload CreativeX PDFs to folder 350605024645**
3. **Run creativex_scoring_storing.py to populate database**
4. Agency creates localized versions
5. Agency uploads to A2→A3 folder
6. A2→A3 script automatically attaches CreativeX scores

## Future Enhancements

Potential additions:
- Add CreativeX brand/market validation (warn if mismatch with filename)
- Display version number in A2→A3 email ("Score: 85 - Version 3")
- Track which files are missing scores in daily report
- Auto-populate score field in Box for agency reference

## Support

- **Database queries:** See CREATIVEX_DEPLOYMENT.md section "Checking Results"
- **Scoring new files:** Run `python scripts/creativex_scoring_storing.py`
- **Logs:** Check `logs/a2_to_a3.log` for CreativeX lookup results
- **Email:** Orange warning box appears when scores are missing