# Edge Cases Analysis - November 2025 Sync

## Summary of Issues

From processing 4,367 order items:
- ✅ **Successfully Processed**: 3,157 items (72.3%)
- ⚠️ **Skipped**: 1,090 items (25.0%)
- ❌ **Errors**: 120 items (2.7%)

---

## Edge Case Categories

### 1. 🔴 ERRORS (120 items - 2.7%)

#### Issue: Decimal Conversion Errors
**Count**: 120 errors (all 120 errors are this type)

**Root Cause**:
When `part_supplier` query returns no matching records, the `.get()` method returns `None`. The code then tries to convert `Decimal(str(None))`, which converts the string `"None"` instead of handling the null value.

**Affected Fields**:
- `supplier_shipping`: 76 occurrences
- `supplier_handling`: 43 occurrences
- `supplier_price`: 1 occurrence (likely)

**Code Location**:
```python
# Lines 533-535 in sync_margins_detailed.py
margin['supplier_price'] = Decimal(str(part_supplier['price']))
margin['supplier_shipping'] = Decimal(str(part_supplier.get('shipping', 0)))  # ❌ Returns None from dict
margin['supplier_handling'] = Decimal(str(part_supplier.get('handling', 0)))  # ❌ Returns None from dict
```

**Example Items**: 988499, 988484, 988455, 988448, 988446, 988443, 988441, etc.

**Why It Happens**:
- Item has a `supplier_id`
- Snapshot data (`independent_buy_price_recording`) returns no records
- Fallback to `parts_suppliers` finds a record BUT:
  - The `shipping` or `handling` columns contain `NULL` in the database
  - Python dict `.get('shipping', 0)` returns `None` (database NULL)
  - `str(None)` = `"None"` (string)
  - `Decimal("None")` throws `InvalidOperation: ConversionSyntax`

**Fix Needed**:
```python
# Correct way to handle None values:
margin['supplier_shipping'] = Decimal(str(part_supplier.get('shipping') or 0))
# OR
margin['supplier_shipping'] = Decimal('0') if part_supplier.get('shipping') is None else Decimal(str(part_supplier['shipping']))
```

---

### 2. ⚠️ SKIPPED - Sales Channel Not Found (630 items - 14.4%)

#### Issue: Cannot Determine Sales Channel
**Count**: 630 items

**Root Cause**:
Orders with `source = 200` (marketplace) but the note lookup fails to identify Amazon or eBay.

**Why It Happens**:

**Problem 1 - Multiple Notes**:
The query uses `LIMIT 1` to get just the first note:
```python
cursor.execute("SELECT note FROM notes WHERE order_id = %s LIMIT 1", (order['order_id'],))
```

But orders often have multiple notes, and the first one might not contain "Amazon" or "eBay":
```
Order 859311 notes:
  1. "Sent to SS [LX2593113]..." ← This one is returned (no keyword)
  2. "(Amazon.com) Order ID: 111-2148407..." ← This one has Amazon!
```

**Problem 2 - Case Sensitivity**:
The check is case-sensitive:
```python
if 'amazon' in note.lower():  # ✓ lowercase
elif 'ebay' in note.lower():  # ✓ lowercase
```
This part is actually correct!

**Problem 3 - Source Code Variations**:
Some orders might use `source` codes we don't recognize:
- `source = 500` → B2B ✓
- `source = 1` → Web ✓
- `source = 200` → Check notes ✓
- `source = ???` → Unknown ❌

**Example Orders**: 859455, 859448, 859445, 859443, 859313, 859311, 858936, 858935, etc.

**Fix Needed**:
```python
# Option 1: Check ALL notes
cursor.execute("SELECT note FROM notes WHERE order_id = %s", (order['order_id'],))
all_notes = cursor.fetchall()
for note_row in all_notes:
    note = note_row['note'].lower()
    if 'amazon' in note:
        return "Amazon"
    elif 'ebay' in note:
        return "eBay"

# Option 2: Use OR condition in SQL
cursor.execute("""
    SELECT note FROM notes
    WHERE order_id = %s
      AND (LOWER(note) LIKE '%amazon%' OR LOWER(note) LIKE '%ebay%')
    LIMIT 1
""", (order['order_id'],))
```

---

### 3. ⚠️ SKIPPED - No Supplier Mapping (250 items - 5.7%)

#### Issue: Items Without Supplier
**Count**: 250 items

**Root Cause**:
Items that have:
- `supplier_id = NULL`
- `wc_supplier = NULL` or not in mapping (GALTL02/03/04)

**Why It Happens**:
These are likely:
- Items not yet assigned to a supplier
- Cancelled items before supplier assignment
- Special order types (custom, local pickup, etc.)
- Data quality issues

**Example Items**: 988574, 988570, 988569, 988568, 988567, 988565, etc.

**Current Behavior**:
Script skips these items entirely - they're not saved to the database.

**Fix Options**:

**Option 1**: Save with remarks (recommended)
```python
if not item.get('supplier_id'):
    margin['remarks'] = "No supplier assigned"
    margin['supplier_id'] = None
    margin['supplier'] = 'Unknown'
    margin['our_buy_price'] = Decimal('0')
    # Save with partial data
    self.save_margin(margin)
```

**Option 2**: Create a catch-all supplier
```python
if not item.get('supplier_id'):
    margin['supplier_id'] = 999  # "Unknown" supplier
    margin['supplier'] = 'No Supplier'
```

**Option 3**: Keep skipping (current behavior)
- Pro: Clean data
- Con: Missing transactions in reporting

---

### 4. ⚠️ SKIPPED - PS (PartSelect) Orders (140 items - 3.2%)

#### Issue: PartSelect Orders Intentionally Skipped
**Count**: 140 items

**Detection Logic**:
```python
if order.get('ps_order_id') or \
   (item.get('custom_data') and 'sku' in item['custom_data']) or \
   (item.get('ordered_brand') and item['ordered_brand'].startswith('PS-')):
    logger.info(f"Skipping PS order item {item['order_item_id']}")
    return False
```

**Example Items**: 988571, 988566, 988546, 988359, 988278, 988277, etc.

**Why Skipped**:
PartSelect orders have a different business model/margins and should be calculated separately or not at all.

**Current Behavior**:
✓ Correct - these should be skipped

**Fix Needed**:
None - this is intentional behavior

**Potential Enhancement**:
Could save these to a separate table `orders_items_margin_ps` with PS-specific calculations if needed.

---

### 5. ⚠️ SKIPPED - Already Processed (70 items)

#### Issue: Duplicate Prevention
**Count**: 70 items (from test run)

**Root Cause**:
The test script (`test_sync.py`) processed 100 items first, saving 70 records. When the full sync ran, those 70 were skipped.

**Current Behavior**:
```python
def check_item_processed(self, order_item_id):
    cursor.execute(
        "SELECT 1 FROM orders_items_margin_detailed WHERE order_item_id = %s LIMIT 1",
        (order_item_id,)
    )
    return cursor.fetchone() is not None
```

**Why It Happens**:
✓ This is correct behavior - prevents duplicates

**Fix Needed**:
None - this is working as intended

---

## Additional Edge Cases (Not Errors, Just Observations)

### 6. Items with $0 Sales Price

**Current Behavior**: Saved with `remarks = "Sale Price is 0."`

**Count**: Unknown (included in processed count)

**Why It Happens**:
- Returns/refunds might zero out the price
- Promotional items
- Data entry errors

**Current Handling**: ✓ Correct - saves with remarks

---

### 7. Items with $0 Buy Price

**Current Behavior**: Saved with `remarks = "Our buy price is 0."`

**Count**: Unknown (included in processed count)

**Why It Happens**:
- No pricing data available
- Free samples
- Warranty replacements
- Data missing

**Current Handling**: ✓ Correct - saves with remarks

---

### 8. Items with No Category

**Current Behavior**: Saved with `remarks = "Item Category not found"`

**Count**: Unknown (included in processed count)

**Why It Happens**:
- Product not in catalog
- Category mapping missing
- Custom/special order items

**Current Handling**: ✓ Correct - saves with remarks

---

## Summary of Required Fixes

### 🔴 CRITICAL (Must Fix)

1. **Decimal Conversion Error** (120 items)
   - Priority: HIGH
   - Impact: 2.7% of items fail
   - Fix: Handle `None` values properly before Decimal conversion
   - Estimated effort: 5 minutes

### 🟡 IMPORTANT (Should Fix)

2. **Sales Channel Detection** (630 items)
   - Priority: MEDIUM
   - Impact: 14.4% of items skipped
   - Fix: Check ALL notes, not just first one
   - Estimated effort: 10 minutes

### 🟢 OPTIONAL (Nice to Have)

3. **No Supplier Mapping** (250 items)
   - Priority: LOW
   - Impact: 5.7% of items skipped (might be intentional)
   - Fix: Save with remarks instead of skipping
   - Estimated effort: 5 minutes
   - Decision needed: Do we want these in the database?

### ✅ NO ACTION NEEDED

4. **PS Orders** (140 items) - ✓ Intentional skip
5. **Already Processed** (70 items) - ✓ Duplicate prevention working
6. **$0 Prices** - ✓ Handled with remarks
7. **No Category** - ✓ Handled with remarks

---

## Recommended Fixes Priority

### Phase 1 - Critical Fix (Do Now)
1. Fix Decimal conversion for None values
   - Affects: 120 items
   - Time: 5 minutes
   - Risk: Low

### Phase 2 - Important Fix (Do Soon)
2. Fix sales channel detection with multiple notes
   - Affects: 630 items
   - Time: 10 minutes
   - Risk: Low

### Phase 3 - Optional Enhancements (Do Later)
3. Decide on no-supplier items handling
   - Affects: 250 items
   - Time: 5 minutes + decision time
   - Risk: Low

---

## Testing Recommendation

After fixes:
1. Clear `orders_items_margin_detailed` table
2. Re-run full November 2025 sync
3. Expected results:
   - **Processed**: ~3,907 items (3,157 + 120 + 630)
   - **Skipped**: ~460 items (250 + 140 + 70)
   - **Errors**: 0 items

This would bring success rate from **72.3%** to **89.5%**!

---

## Database Impact

Current state:
- 3,227 records in database (some duplicates from test)
- Missing 750 viable items (120 errors + 630 channel issues)

After fixes:
- ~3,977 records expected (91% of total items)
- Only intentional skips remain (PS orders, no supplier)