# COGS Update Scripts for orders_items_margin_detailed

## Overview

These scripts have been copied from `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/` and adapted to work with the `orders_items_margin_detailed` table.

**Target Table**: `orders_items_margin_detailed` (NOT orders_items_margin)

---

## Script Numbering

Our profitability directory already has Script #1 (`sync_margins_detailed.py`), so the COGS scripts are numbered 2-8:

| Our Script | Original Script | Purpose |
|-----------|----------------|---------|
| **Script 1** | - | `sync_margins_detailed.py` - Main margin calculator |
| **Script 2** | Script 1 | `2_import_all_data.py` - Import external data |
| **Script 3** | Script 2 | `3_update_supplier_costs.py` - Update supplier costs |
| **Script 4** | Script 3 | `4_update_payment_processor_fees.py` - Payment processor fees |
| ~~Script 4~~ | ~~Script 4~~ | ~~OBSOLETE - Superseded by Script 5~~ |
| **Script 5** | Script 5 | `5_update_customer_shipping.py` - Customer shipping costs |
| **Script 6** | Script 6 | `6_update_commission_and_fees.py` - Commission and fees |
| **Script 7** | Script 7 | `7_calculate_final_cogs.py` - Calculate final COGS |
| **Script 8** | Script 8 | `8_fix_multiplier_bugs.py` - Fix calculation bugs |

---

## What Was Changed

All scripts have been updated with the following changes:

### 1. Table Name Updated
```python
# OLD:
FROM orders_items_margin
UPDATE orders_items_margin

# NEW:
FROM orders_items_margin_detailed
UPDATE orders_items_margin_detailed
```

**This ensures all COGS updates go to the detailed table, not the original table.**

### 2. Base Directory Updated
```python
# OLD:
BASE_DIR = '/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts'

# NEW:
BASE_DIR = '/home/centralgoparts/public_html/profitability'
```

### 3. Data Source Directories (Script 2 only)
Script 2 (import script) still reads CSV files from the original location:
```python
DATA_SOURCE_BASE = '/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts'
BOLT_DIR = os.path.join(DATA_SOURCE_BASE, 'bolt')
SHIPHAVEN_DIR = os.path.join(DATA_SOURCE_BASE, 'shiphaven')
USPS_DIR = os.path.join(DATA_SOURCE_BASE, 'usps-stamps')
```

This is intentional - CSV files will continue to be uploaded to the original location.

---

## Script Details

### Script 2: Import All External Data
**File**: `2_import_all_data.py`

**Purpose**: Import payment processor and shipping data from CSV files

**Imports**:
- Bolt payment fees → `payment_processor_charges-Bolt`
- UPS ShipHaven shipping → `shipping_charges-ups-shiphaven`
- USPS Stamps shipping → `shipping_charges-usps_stampscom`

**Data Source**: `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/[bolt|shiphaven|usps-stamps]/`

**Table Updated**: Import tables (not orders_items_margin_detailed directly)

**Execution**: One-time per month when new CSV data arrives

---

### Script 3: Update Supplier Costs
**File**: `3_update_supplier_costs.py`

**Purpose**: Update buy prices from 9 supplier charge tables

**Suppliers**:
- Dropshippers: USAuto, Meyer, DEPO, LKQ/Keystone, TYC
- Regular: ExpressParts, JCAuto, PBI, RegionMax/ELT

**Table Updated**: `orders_items_margin_detailed`

**Fields Updated**:
- `supplier_price`
- `supplier_shipping`
- `supplier_handling`
- `our_buy_price`
- `buy_price_source`

**Cron-Ready**: Yes (120-day window)

---

### Script 4: Update Payment Processor Fees
**File**: `4_update_payment_processor_fees.py`

**Purpose**: Apply payment processor fees for Bolt, CC, and eBay

**Fee Types**:
- Bolt: Actual fees from `payment_processor_charges-Bolt`
- Credit Card: 2.69% calculation
- eBay: 2.9% + $0.30 calculation

**Table Updated**: `orders_items_margin_detailed.payment_processor_fee`

**Cron-Ready**: Yes (60-day window)

---

### Script 5: Update Customer Shipping Costs
**File**: `5_update_customer_shipping.py`

**Purpose**: Match UPS and USPS shipping costs to orders WE ship

**IMPORTANT**: Only updates non-dropshipper orders
- Excludes: `buy_price_source LIKE '%Included%'`
- Processes: `buy_price_source IS NULL OR = 'Supplier e-mail'`

**Table Updated**: `orders_items_margin_detailed`

**Fields Updated**:
- `supplier_shipping`
- `our_buy_price` (adds shipping cost)
- `our_shipping_source`

**Cron-Ready**: Yes (120-day window)

---

### Script 6: Update Commission and Fees
**File**: `6_update_commission_and_fees.py`

**Purpose**: Update commission/fees by channel using monthly .env files

**Channels**: Web, B2B, Amazon, eBay

**Requires**: `.env.{month}` files with fee totals
- `.env.08` = August 2025
- `.env.09` = September 2025
- etc.

**Table Updated**: `orders_items_margin_detailed.commission_and_fee`

**Cron-Ready**: Partial (requires manual .env creation)

---

### Script 7: Calculate Final COGS
**File**: `7_calculate_final_cogs.py`

**Purpose**: Calculate final COGS, gross profit, and gross margin

**Formula**:
```
COGS = our_buy_price + commission_and_fee + payment_processor_fee - supplier_credits + loss_amount
Gross Profit = sales_price - COGS
Gross Margin % = (Gross Profit / sales_price) × 100
```

**Table Updated**: `orders_items_margin_detailed`

**Fields Updated**:
- `good_sold_cost` (COGS)
- `gross_profit`
- `gross_margin`

**Cron-Ready**: Yes (120-day window)

---

### Script 8: Fix Multiplier Bugs
**File**: `8_fix_multiplier_bugs.py`

**Purpose**: Validate and fix `our_buy_price` calculation errors

**Validates**:
```
our_buy_price = supplier_price + supplier_shipping + supplier_handling
```

**Fixes**: Common bugs like 216x or 27x multiplier errors

**Table Updated**: `orders_items_margin_detailed.our_buy_price`

**Cron-Ready**: Yes (120-day window)

---

## Execution Order

These scripts must run in sequence:

```
1. sync_margins_detailed.py (Initial margin calculation)
   ↓
2. import_all_data.py (Import external data)
   ↓
3. update_supplier_costs.py (Supplier costs)
   ↓
4. update_payment_processor_fees.py (Payment fees)
   ↓
5. update_customer_shipping.py (Shipping costs)
   ↓
6. update_commission_and_fees.py (Channel fees)
   ↓
7. calculate_final_cogs.py (Final COGS calculation)
   ↓
8. fix_multiplier_bugs.py (Validation & fixes)
```

---

## Verification

To verify all scripts use the correct table:

```bash
grep -h "FROM orders_items_margin\|UPDATE orders_items_margin\|INSERT INTO orders_items_margin" *.py | grep -v "_detailed"
# Should return NO results
```

To verify correct table is used:

```bash
grep -h "orders_items_margin_detailed" [2-8]_*.py | wc -l
# Should return many results
```

---

## Important Notes

### ✅ All Scripts Updated
- ✅ Table name changed to `orders_items_margin_detailed`
- ✅ Base directory updated to profitability folder
- ✅ Log files will go to `/home/centralgoparts/public_html/profitability/logs/`
- ✅ All SQL queries use correct table name

### ⚠️ Data Source Location
- Script 2 still reads CSV files from original location
- This is intentional - CSV upload location hasn't changed
- Only the target database table has changed

### 🔍 Strict Table Usage
**CRITICAL**: These scripts work EXCLUSIVELY with `orders_items_margin_detailed`

They do NOT touch `orders_items_margin` table at all.

---

## Next Steps

1. **Test Script 2** (Import): Verify it can import data from CSV files
2. **Test Script 3** (Supplier Costs): Run on a small date range first
3. **Test remaining scripts**: One by one in sequence
4. **Set up cron jobs**: Once tested, schedule for regular execution

---

## Files Summary

```
/home/centralgoparts/public_html/profitability/
├── 1_sync_margins_detailed.py (our main script)
├── 2_import_all_data.py (import Bolt/UPS/USPS)
├── 3_update_supplier_costs.py (9 suppliers)
├── 4_update_payment_processor_fees.py (Bolt/CC/eBay)
├── 5_update_customer_shipping.py (UPS/USPS for non-dropship)
├── 6_update_commission_and_fees.py (channel fees)
├── 7_calculate_final_cogs.py (final COGS)
└── 8_fix_multiplier_bugs.py (validation)
```

All scripts target: **`orders_items_margin_detailed`** ✅