# Status & Supplier Sync Feature

## Overview

The sync feature keeps the `orders_items_margin_detailed` table in sync with the `orders_items` table for supplier assignments and order status changes over the last 180 days.

## Why This Feature?

When orders are initially created, they may not have a `supplier_id` assigned (status = "Needs Order"). As orders are processed:
- `supplier_id` gets assigned when the order is placed with a supplier
- `current_status` changes as the order moves through fulfillment stages

This sync feature ensures the margin table always reflects the current state from `orders_items`.

---

## Usage

### Run Status Sync Only

```bash
python3 sync_margins_detailed.py --sync-status
```

This will:
- Update `supplier_id` and `item_status` for all records from the last 180 days
- Skip the full margin calculation
- Complete in under 1 second

### Run Full Margin Calculation (Default)

```bash
python3 sync_margins_detailed.py
```

or explicitly:

```bash
python3 sync_margins_detailed.py --full-sync
```

### Run Both Operations

```bash
python3 sync_margins_detailed.py --full-sync --sync-status
```

This will:
1. First run the full margin calculation
2. Then sync supplier_id and item_status

---

## What Gets Updated

The sync updates **only** these two fields:

| Field in Margin Table | Source Field | Description |
|----------------------|--------------|-------------|
| `supplier_id` | `orders_items.supplier_id` | Supplier who fulfilled the order |
| `item_status` | `orders_items.current_status` | Current order item status |

**No other fields are modified** - all margin calculations, prices, and fees remain unchanged.

---

## Date Range

- **Last 180 days**: Syncs orders placed in the last 6 months
- **Calculated from**: Current date at runtime
- **Example**: If run on 2025-11-22, syncs orders from 2025-05-26 onwards

**Why 180 days?**
- Covers typical order lifecycle (order → fulfill → ship → deliver)
- Captures any delayed supplier assignments
- Keeps recent data fresh without processing entire history

---

## Performance

**Test Results** (4,353 records):
- Execution Time: 0.08 seconds
- Records per second: ~54,000
- Database Load: Minimal (single UPDATE with JOINs)

**Optimized Query**:
```sql
UPDATE orders_items_margin_detailed oimd
JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id
JOIN orders o ON oi.order_id = o.order_id
SET
    oimd.supplier_id = oi.supplier_id,
    oimd.item_status = oi.current_status
WHERE o.date_placed >= [180 days ago]
```

---

## Use Cases

### 1. Daily Sync (Recommended)

Set up a cron job to run the status sync daily:

```bash
# /etc/cron.d/margin-sync
# Run status sync daily at 2 AM
0 2 * * * goparts cd /home/centralgoparts/public_html/profitability && python3 sync_margins_detailed.py --sync-status >> /var/log/margin-sync.log 2>&1
```

**Benefits**:
- Keeps supplier assignments up-to-date
- Tracks status changes as orders progress
- Fast execution (< 1 second)

### 2. After Full Sync

After running a full margin calculation, run the status sync to ensure everything is current:

```bash
python3 sync_margins_detailed.py --full-sync --sync-status
```

### 3. On-Demand

Run manually whenever you need to refresh supplier/status data:

```bash
python3 sync_margins_detailed.py --sync-status
```

---

## Example Scenarios

### Scenario 1: Order Initially Without Supplier

**Initial State** (when order first created):
- orders_items.supplier_id = NULL
- orders_items.current_status = 1 (Needs Order)
- orders_items_margin_detailed.supplier_id = NULL
- orders_items_margin_detailed.item_status = 1

**After Supplier Assignment**:
- orders_items.supplier_id = 48 (assigned to USAuto)
- orders_items.current_status = 3 (Ordered)

**After Running Sync**:
- orders_items_margin_detailed.supplier_id = 48 ✅
- orders_items_margin_detailed.item_status = 3 ✅

### Scenario 2: Status Changes Through Fulfillment

**Order Lifecycle**:
1. Status 1 (Needs Order) → Sync updates margin table
2. Status 3 (Ordered) → Sync updates margin table
3. Status 4 (Shipped) → Sync updates margin table
4. Status 5 (Delivered) → Sync updates margin table

The margin table always reflects the current status after each sync run.

---

## Verification

### Check Sync Status

Verify all records are in sync:

```sql
SELECT
    COUNT(*) as total_records,
    SUM(CASE
        WHEN oimd.supplier_id <=> oi.supplier_id
         AND oimd.item_status = oi.current_status
        THEN 1 ELSE 0
    END) as synced,
    SUM(CASE
        WHEN oimd.supplier_id <=> oi.supplier_id
         AND oimd.item_status = oi.current_status
        THEN 0 ELSE 1
    END) as mismatched
FROM orders_items_margin_detailed oimd
JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id;
```

**Expected Result**: `mismatched = 0`

### Sample Records

View a few records to verify sync:

```sql
SELECT
    oimd.order_item_id,
    oimd.supplier_id as margin_supplier,
    oi.supplier_id as item_supplier,
    oimd.item_status as margin_status,
    oi.current_status as item_status,
    CASE
        WHEN oimd.supplier_id <=> oi.supplier_id
         AND oimd.item_status = oi.current_status
        THEN 'SYNCED'
        ELSE 'MISMATCH'
    END as sync_status
FROM orders_items_margin_detailed oimd
JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id
LIMIT 20;
```

---

## Logging

The sync function logs to:
- **File**: `/home/centralgoparts/public_html/profitability/sync_margins.log`
- **Console**: stdout

**Log Output Example**:
```
2025-11-22 01:19:49,947 - INFO - ============================================================
2025-11-22 01:19:49,947 - INFO - Starting Status & Supplier Sync
2025-11-22 01:19:49,947 - INFO - Syncing last 180 days (from 2025-05-26)
2025-11-22 01:19:49,947 - INFO - ============================================================
2025-11-22 01:19:50,022 - INFO - ============================================================
2025-11-22 01:19:50,022 - INFO - Completed Status & Supplier Sync
2025-11-22 01:19:50,022 - INFO - Summary:
2025-11-22 01:19:50,022 - INFO -   Records Updated: 4353
2025-11-22 01:19:50,022 - INFO -   Execution Time: 0.08s
2025-11-22 01:19:50,022 - INFO - ============================================================
```

---

## Error Handling

The sync function includes:
- **Transaction safety**: Uses commit/rollback
- **Error logging**: Full stack traces on failure
- **Connection cleanup**: Closes connections properly

**If sync fails**:
1. Changes are rolled back (no partial updates)
2. Error is logged with full details
3. Exception is raised (can be caught by calling code)

---

## Testing

### Test Results (2025-11-22)

**Test**: Sync 4,353 records from November 2025

**Results**:
- ✅ All 4,353 records updated successfully
- ✅ 100% sync accuracy (0 mismatches)
- ✅ Execution time: 0.08 seconds
- ✅ No errors

**Verification Query**:
```sql
-- Result: 4353 synced, 0 mismatched
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN oimd.supplier_id <=> oi.supplier_id
             AND oimd.item_status = oi.current_status
        THEN 1 ELSE 0 END) as synced
FROM orders_items_margin_detailed oimd
JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id;
```

---

## Best Practices

### 1. Regular Syncs
Run sync daily to keep data fresh:
```bash
# Cron: Daily at 2 AM
0 2 * * * python3 sync_margins_detailed.py --sync-status
```

### 2. After Bulk Updates
If you manually update orders_items.supplier_id or current_status in bulk, run a sync:
```bash
python3 sync_margins_detailed.py --sync-status
```

### 3. Monitoring
Check logs regularly to ensure syncs are completing successfully:
```bash
tail -f /home/centralgoparts/public_html/profitability/sync_margins.log
```

### 4. Verification
Periodically verify sync status with the verification query above.

---

## Technical Details

### Database Impact

**Tables Modified**:
- `orders_items_margin_detailed` (UPDATE only)

**Tables Read**:
- `orders_items`
- `orders`

**Indexes Used**:
- `order_item_id` (PRIMARY KEY join)
- `order_id` (FK join)
- `date_placed` (WHERE filter)

### SQL Query Breakdown

```sql
UPDATE orders_items_margin_detailed oimd
-- Join to get current supplier_id and status from source
JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id
-- Join orders to filter by date
JOIN orders o ON oi.order_id = o.order_id
-- Update both fields
SET
    oimd.supplier_id = oi.supplier_id,     -- Sync supplier
    oimd.item_status = oi.current_status   -- Sync status
-- Only sync last 180 days
WHERE o.date_placed >= [cutoff_date]
```

**Why this is efficient**:
1. Uses indexed joins (order_item_id, order_id)
2. Single UPDATE query (not row-by-row)
3. Date filter limits scope
4. Only updates 2 fields (minimal write)

---

## Command-Line Reference

```bash
# Show help
python3 sync_margins_detailed.py --help

# Run status sync only
python3 sync_margins_detailed.py --sync-status

# Run full margin calculation (default)
python3 sync_margins_detailed.py
python3 sync_margins_detailed.py --full-sync

# Run both operations
python3 sync_margins_detailed.py --full-sync --sync-status
```

---

## Summary

The Status & Supplier Sync feature:
- ✅ Keeps margin table synchronized with orders table
- ✅ Updates supplier_id and item_status for last 180 days
- ✅ Completes in < 1 second for thousands of records
- ✅ Safe (uses transactions, error handling)
- ✅ Flexible (can run standalone or with full sync)
- ✅ Production-ready (tested and verified)

**Recommended Usage**: Run daily via cron to keep data fresh.