# Orphaned ECM Data Cleanup - Invoice Validation Fix

## Issue
**Error:** `Duplicate entry 'facture/IN2604-0001-IN2604-0001.pdf-1' for key 'llx_ecm_files.uk_ecm_files'`
**When:** Clicking "Validate" button on an invoice

## Root Cause
The database contained **orphaned ECM entries** - records in `llx_ecm_files` table that referenced invoices that no longer exist in the database. 

When attempting to validate a new invoice (or regenerate PDF), the system tried to create an ECM entry with the same filepath/filename as the orphaned entry, causing a duplicate key violation.

### What Are Orphaned ECM Entries?
- Invoices were created → PDF generated → ECM entry created in database
- Invoices were deleted → Invoice record removed from `llx_facture`
- **Problem:** ECM entries in `llx_ecm_files` were NOT deleted
- Result: Orphaned ECM entries blocking new invoices with same references

## Solution Applied

### 1. Database Cleanup
**Action:** Deleted all orphaned ECM entries for invoices

**SQL Executed:**
```sql
DELETE e FROM llx_ecm_files e
WHERE e.src_object_type = 'facture' 
AND e.src_object_id NOT IN (SELECT rowid FROM llx_facture);
```

**Results:**
- ✅ Deleted 9 orphaned ECM entries (rowid 1-9)
- ✅ 0 orphaned entries remaining
- ✅ Only 2 valid ECM entries remain (for existing invoices)

### 2. Enhanced Code Protection
**File Modified:** [commonobject.class.php](file:///var/www/html/aircrm/htdocs/core/class/commonobject.class.php#L6115-L6210)

Added automatic duplicate detection and cleanup in the `indexFile` method:
- Detects if ECM entry exists before creating new one
- Deletes orphaned entries automatically
- Handles duplicate key errors gracefully

## Files Created/Modified

### Created:
1. [cleanup_orphaned_invoice_ecm.sql](file:///var/www/html/aircrm/cleanup_orphaned_invoice_ecm.sql) - Script to find and delete orphaned invoice ECM entries
2. [cleanup_ecm_duplicates.sql](file:///var/www/html/aircrm/cleanup_ecm_duplicates.sql) - General ECM duplicate cleanup utility
3. [INVOICE_PDF_DUPLICATE_FIX.md](file:///var/www/html/aircrm/INVOICE_PDF_DUPLICATE_FIX.md) - Detailed fix documentation

### Modified:
1. [commonobject.class.php](file:///var/www/html/aircrm/htdocs/core/class/commonobject.class.php) - Enhanced ECM indexing with duplicate handling

## Verification Results

### Before Cleanup:
```
Orphaned ECM entries: 9
- Rowid 1: facture/IN2604-0001 (invoice ID 1 - DELETED)
- Rowid 2: facture/IN2604-0004 (invoice ID 2 - DELETED)
- Rowid 3: facture/IN2604-0002 (invoice ID 3 - DELETED)
- Rowid 4: facture/IN2604-0003 (invoice ID 4 - DELETED)
- Rowid 5: facture/IN2604-0005 (invoice ID 5 - DELETED)
- Rowid 6: facture/IN2604-0006 (invoice ID 6 - DELETED)
- Rowid 7: facture/IN2604-0007 (invoice ID 7 - DELETED)
- Rowid 8: facture/IN2604-0008 (invoice ID 8 - DELETED)
- Rowid 9: facture/IN2604-0009 (invoice ID 9 - DELETED)
```

### After Cleanup:
```
Total ECM entries: 2
Orphaned entries: 0
Valid entries: 2 (linked to existing invoices)
```

## How to Prevent This in the Future

### Option 1: Automatic Cleanup on Invoice Deletion
When deleting an invoice, also delete its ECM entries:

```php
// In facture.class.php delete() method
// Delete ECM files
$sql = "DELETE FROM ".MAIN_DB_PREFIX."ecm_files";
$sql .= " WHERE src_object_type = 'facture'";
$sql .= " AND src_object_id = ".((int) $this->id);
$this->db->query($sql);
```

### Option 2: Database Foreign Key with CASCADE
Add foreign key constraint:
```sql
ALTER TABLE llx_ecm_files 
ADD CONSTRAINT fk_ecm_files_facture 
FOREIGN KEY (src_object_type, src_object_id) 
REFERENCES llx_facture(element, rowid) 
ON DELETE CASCADE;
```

**Note:** This requires schema changes and careful testing.

### Option 3: Regular Cleanup Script (Recommended)
Run cleanup periodically:
```bash
mysql -u user -p database < cleanup_orphaned_invoice_ecm.sql
```

## Testing

You can now:
1. ✅ **Validate invoices** - No more duplicate entry errors
2. ✅ **Regenerate PDFs** - Updates existing ECM entries properly
3. ✅ **Delete invoices** - Invoice deletion works smoothly
4. ✅ **Create new invoices** - Even with reused references

## Maintenance Commands

### Check for orphaned ECM entries:
```sql
SELECT e.rowid, e.filepath, e.filename, e.src_object_id
FROM llx_ecm_files e
WHERE e.src_object_type = 'facture' 
AND e.src_object_id NOT IN (SELECT rowid FROM llx_facture);
```

### Clean up orphaned entries:
```sql
DELETE e FROM llx_ecm_files e
WHERE e.src_object_type = 'facture' 
AND e.src_object_id NOT IN (SELECT rowid FROM llx_facture);
```

### Check all ECM statistics:
```sql
SELECT src_object_type, COUNT(*) as total_entries
FROM llx_ecm_files
GROUP BY src_object_type;
```

## Summary

The duplicate entry error was caused by orphaned database records from deleted invoices. The fix involved:
1. **Immediate fix:** Deleted 9 orphaned ECM entries
2. **Long-term fix:** Enhanced code to handle duplicates automatically
3. **Prevention:** Created cleanup scripts for future maintenance

The system now handles ECM entries more robustly and won't fail on duplicate key violations.