# COGS Update Scripts Analysis (Scripts 1-8)

## Overview

Location: `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/`

These 8 scripts work together to update the `orders_items_margin` table with accurate Cost of Goods Sold (COGS) data. They are designed to run sequentially or via cron jobs to keep cost data up-to-date.

**Target Table**: `orders_items_margin`
**Date Range**: Primarily September 2025, but most scripts use rolling windows (60-120 days)

---

## Script Sequence & Purpose

### Script 1: Import All External Data (`1_import_all_data.py`)

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

**What it does**:
1. Imports **Bolt payment processor fees** from CSV → `payment_processor_charges-Bolt` table
2. Imports **UPS ShipHaven shipping** from CSV → `shipping_charges-ups-shiphaven` table
3. Imports **USPS Stamps.com shipping** from CSV → `shipping_charges-usps_stampscom` table

**Data Sources**:
- `bolt/` directory - Bolt payment processor CSV exports
- `shiphaven/` directory - UPS ShipHaven CSV exports
- `usps-stamps/` directory - USPS Stamps.com CSV exports

**Key Logic**:
- Checks for duplicates before importing (by reference/tracking number)
- Filters completed/delivered transactions only
- Parses currency amounts (`$24.18` format)
- Parses dates in multiple formats
- Handles quoted CSV fields (`=""value""`)

**Database Tables Created/Updated**:
- `payment_processor_charges-Bolt`
- `shipping_charges-ups-shiphaven`
- `shipping_charges-usps_stampscom`

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

---

### Script 2: Update Supplier Costs (`2_update_supplier_costs.py`)

**Purpose**: Update `our_buy_price` from supplier charge tables for all suppliers

**What it does**:
Updates `orders_items_margin` with supplier costs from 9 different supplier tables:

**Dropshippers** (sets `buy_price_source` = "[Supplier] Included"):
1. **USAuto** (supplier_ids: 11, 16, 17, 45, 46, 48, 49, 54)
2. **Meyer** (supplier_ids: 61-69)
3. **DEPO MaxZone** (supplier_ids: 3, 51-53, 72)
4. **LKQ/Keystone** (supplier_ids: 1, 2, 59, 60)
5. **TYC** (supplier_ids: 18-21, 25-31, 33-42)

**Regular Suppliers** (sets `buy_price_source` = "Supplier e-mail"):
6. **ExpressParts** (supplier_ids: 4, 5, 8)
7. **JCAuto** (supplier_ids: 10, 47)
8. **PBI** (supplier_ids: 9, 23)
9. **RegionMax/ELT** (supplier_ids: 6, 24, 43)

**Key Logic**:
- Matches orders by order_id (stored in supplier table's order_no or cross_reference_no)
- For DEPO: Matches by part_no through `parts_suppliers` lookup (their order_no is empty)
- Calculates: `our_buy_price = (unit_price - discount) × quantity + shipping + handling`
- Updates `supplier_price`, `supplier_shipping`, `supplier_handling`
- Uses 7-day tolerance for order date matching

**Database Tables Read**:
- `supplier_charges-usauto`
- `supplier_charges-meyer`
- `supplier_charges-depo`
- `supplier_charges-lkq`
- `supplier_charges-tyc`
- `supplier_charges-ep_miami`
- `supplier_charges-jc_auto`
- `supplier_charges-pbi`
- `supplier_charges-regionmax`

**Database Table Updated**: `orders_items_margin`

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

---

### Script 3: Update Payment Processor Fees (`3_update_payment_processor_fees.py`)

**Purpose**: Apply payment processor fees for Bolt, Credit Card, and eBay transactions

**What it does**:
Updates `payment_processor_fee` field with transaction costs from payment processors:

1. **Bolt Payments**:
   - Matches by order_reference to `payment_processor_charges-Bolt`
   - Uses actual `processing_fee_amount + bolt_fee_amount`
   - Most accurate (actual fees from Bolt)

2. **Credit Card Payments**:
   - Applies to orders with payment_method in specific CC types
   - Calculates: `fee = sales_price × 0.0269` (2.69%)
   - Sets `payment_processor_fee_source = 'CC calculation'`

3. **eBay Managed Payments**:
   - Applies to eBay orders (sales_channel = 'Amazon')
   - Calculates: `fee = sales_price × 0.029 + 0.30` (2.9% + $0.30)
   - Sets `payment_processor_fee_source = 'eBay calculation'`

**Payment Method Types for CC**:
- `authorize.net`, `bolt`, `usaepay`, `paypal`, `payflow`, `bambora`, `moneris`

**Key Logic**:
- **Does NOT charge B2B orders** payment processor fees (they pay separately)
- Prioritizes Bolt actual fees over calculated fees
- Only updates NULL or 0.00 fees (doesn't overwrite)

**Database Table Updated**: `orders_items_margin.payment_processor_fee`

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

---

### Script 4: Update UPS ShipHaven Shipping (`4_update_ups_shipping.py`)

**Purpose**: Match UPS shipping charges to orders by tracking number

**What it does**:
- Matches tracking numbers from `shipping_charges-ups-shiphaven` to `orders_items.tracking`
- Updates `customer_shipping_cost` with UPS charges
- Sets `our_shipping_source = 'UPS ShipHaven'`

**Key Logic**:
- Joins tracking numbers between shipping charges and orders_items
- Parses SHIPMENT_TOTAL from ShipHaven data (format: `=""28.50""`)
- Only processes orders with tracking numbers
- Excludes international tracking numbers (starting with letters)

**Database Tables**:
- Read: `shipping_charges-ups-shiphaven`
- Update: `orders_items_margin.customer_shipping_cost`, `our_shipping_source`

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

---

### Script 5: Update Customer Shipping Costs (`5_update_customer_shipping.py`)

**Purpose**: Match customer shipping costs from both UPS and USPS to orders

**IMPORTANT**: Only updates orders where WE paid shipping directly
- **EXCLUDES dropshipper orders** (`buy_price_source LIKE '%Included%'`)
- Dropshipper shipping already captured in Script 2
- Only processes: `buy_price_source IS NULL OR = 'Supplier e-mail'`

**What it does**:
1. **UPS ShipHaven**: Matches by tracking number
2. **USPS Stamps.com**: Matches by tracking number

**Updates both fields**:
- `supplier_shipping` = shipping cost (for reporting)
- `our_buy_price` += shipping cost (for COGS calculation)
- `our_shipping_source` = 'UPS ShipHaven' or 'USPS Stamps'

**Key Logic**:
- Prevents overwriting dropshipper shipping costs
- Adds shipping to our_buy_price (part of total cost we paid)
- Uses tracking number matching
- Parses amounts from both data sources

**Database Tables**:
- Read: `shipping_charges-ups-shiphaven`, `shipping_charges-usps_stampscom`
- Update: `orders_items_margin.supplier_shipping`, `our_buy_price`, `our_shipping_source`

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

---

### Script 6: Update Commission and Fees (`6_update_commission_and_fees.py`)

**Purpose**: Update commission and fees for all sales channels using monthly .env files

**Monthly .env Files**:
- `.env.08` = August 2025 values
- `.env.09` = September 2025 values
- etc.

**Sales Channels**:
1. **Web** (source = 1)
2. **B2B** (source = 500)
3. **Amazon** (source = 200, notes contain 'amazon')
4. **eBay** (source = 200, notes contain 'ebay')

**What it does**:
For each month with a .env file:
1. Loads fee values from .env file:
   - `WEB_TOTAL_FEES`, `WEB_TOTAL_SALES`
   - `B2B_TOTAL_FEES`, `B2B_TOTAL_SALES`
   - `AMAZON_TOTAL_FEES`, `AMAZON_TOTAL_SALES`
   - `EBAY_TOTAL_FEES`, `EBAY_TOTAL_SALES`

2. Calculates per-order fees proportionally:
   ```
   fee_rate = TOTAL_FEES / TOTAL_SALES
   order_fee = sales_price × fee_rate
   ```

3. Updates `commission_and_fee` field

**For months without .env files**:
- Estimates fees based on previous month's rates
- Logs warning about estimated values

**Key Logic**:
- Processes orders month by month
- Determines channel from order source and notes
- Uses proportional allocation (fair distribution)
- Tracks which months have actual vs estimated fees

**Database Table Updated**: `orders_items_margin.commission_and_fee`

**Cron-Ready**: Partial (requires manual .env file creation for new months)

---

### Script 7: Calculate Final COGS (`7_calculate_final_cogs.py`)

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

**COGS Formula**:
```
COGS = our_buy_price
       + commission_and_fee
       + payment_processor_fee
       - supplier_credits
       + loss_amount

where: loss_amount = sales_price × (loss_rate / 100)
```

**Gross Profit Formula**:
```
gross_profit = sales_price - COGS
```

**Gross Margin %**:
```
gross_margin_pct = (gross_profit / sales_price) × 100
```

**What it does**:
1. Reads all cost components from `orders_items_margin`
2. Calculates `loss_amount` using `loss_rate` from category
3. Applies COGS formula
4. Calculates gross profit and margin percentage
5. Updates three fields:
   - `good_sold_cost` = COGS
   - `gross_profit` = sales_price - COGS
   - `gross_margin` = (gross_profit / sales_price) × 100

**Key Logic**:
- Handles NULL values (treats as 0)
- Uses Decimal for precision
- Joins with products table for category loss_rate
- Only updates if sales_price > 0

**Database Table Updated**: `orders_items_margin.good_sold_cost`, `gross_profit`, `gross_margin`

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

---

### Script 8: Fix Multiplier Bugs (`8_fix_multiplier_bugs.py`)

**Purpose**: Validate and correct `our_buy_price` to match the correct formula

**Correct Formula**:
```
our_buy_price = supplier_price + supplier_shipping + supplier_handling
```

**What it does**:
Identifies and fixes orders where `our_buy_price` doesn't match the formula:

1. **Detection**: Finds mismatches using:
   ```sql
   WHERE ABS(our_buy_price - (supplier_price + supplier_shipping + supplier_handling)) > 0.01
   ```

2. **Correction**: Updates `our_buy_price` to correct value

3. **Common Bugs Fixed**:
   - 216x multiplier bug (buy price multiplied by 216)
   - 27x multiplier bug (buy price multiplied by 27)
   - Other calculation errors

**Key Logic**:
- Validates formula for all orders
- Fixes regardless of specific error pattern
- Uses 0.01 tolerance for floating point comparison
- Logs all corrections made

**Database Table Updated**: `orders_items_margin.our_buy_price`

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

---

## Execution Order

The scripts must run in this specific order:

```
1. Import All Data (Script 1)
   ↓
2. Update Supplier Costs (Script 2)
   ↓
3. Update Payment Processor Fees (Script 3)
   ↓
4. (OBSOLETE - Script 4 superseded by Script 5)
   ↓
5. Update Customer Shipping (Script 5)
   ↓
6. Update Commission and Fees (Script 6)
   ↓
7. Calculate Final COGS (Script 7)
   ↓
8. Fix Multiplier Bugs (Script 8)
```

**Master Script**: `run_all_cogs_updates.py` orchestrates the execution

---

## Fields Updated in orders_items_margin

| Field | Updated By | Description |
|-------|-----------|-------------|
| `supplier_price` | Script 2 | Base price from supplier |
| `supplier_shipping` | Scripts 2, 5 | Shipping cost (dropshipper or our cost) |
| `supplier_handling` | Script 2 | Handling cost from supplier |
| `our_buy_price` | Scripts 2, 5, 8 | Total cost = price + shipping + handling |
| `buy_price_source` | Script 2 | "[Supplier] Included" or "Supplier e-mail" |
| `payment_processor_fee` | Script 3 | Bolt, CC, or eBay payment processing fee |
| `payment_processor_fee_source` | Script 3 | Source of fee (Bolt actual/CC/eBay calculation) |
| `customer_shipping_cost` | Script 4 | UPS shipping charge |
| `our_shipping_source` | Scripts 4, 5 | 'UPS ShipHaven' or 'USPS Stamps' |
| `commission_and_fee` | Script 6 | Channel commission/fees (Web/B2B/Amazon/eBay) |
| `good_sold_cost` | Script 7 | Final COGS value |
| `gross_profit` | Script 7 | sales_price - COGS |
| `gross_margin` | Script 7 | Gross profit percentage |

---

## Key Database Tables

**Source Tables** (read from):
- `payment_processor_charges-Bolt`
- `shipping_charges-ups-shiphaven`
- `shipping_charges-usps_stampscom`
- `supplier_charges-usauto`
- `supplier_charges-meyer`
- `supplier_charges-depo`
- `supplier_charges-lkq`
- `supplier_charges-tyc`
- `supplier_charges-ep_miami`
- `supplier_charges-jc_auto`
- `supplier_charges-pbi`
- `supplier_charges-regionmax`
- `orders`
- `orders_items`
- `notes`
- `products`
- `parts_suppliers`

**Target Table** (updated):
- `orders_items_margin`

---

## Cron Job Readiness

Most scripts are cron-ready with rolling windows:

| Script | Cron-Ready | Window | Frequency |
|--------|-----------|---------|-----------|
| 1 | Manual | N/A | As needed (when CSV data arrives) |
| 2 | ✅ Yes | 120 days | Daily |
| 3 | ✅ Yes | 60 days | Daily |
| 4 | ⚠️ Obsolete | - | Use Script 5 instead |
| 5 | ✅ Yes | 120 days | Daily |
| 6 | ⚠️ Partial | All months | Monthly (requires .env file) |
| 7 | ✅ Yes | 120 days | Daily |
| 8 | ✅ Yes | 120 days | Daily |

**Recommended Cron Schedule**:
```bash
# Daily at 3 AM - Update all costs
0 3 * * * /path/to/run_all_cogs_updates.py

# Or individual scripts:
0 3 * * * /path/to/2_update_supplier_costs.py
0 4 * * * /path/to/3_update_payment_processor_fees.py
0 5 * * * /path/to/5_update_customer_shipping.py
0 6 * * * /path/to/7_calculate_final_cogs.py
0 7 * * * /path/to/8_fix_multiplier_bugs.py
```

---

## Special Handling

### Dropshipper vs Regular Supplier Distinction

**Dropshippers**:
- Supplier handles shipping directly to customer
- Shipping cost included in supplier charges
- `buy_price_source` = "[Supplier] Included"
- Shipping updated by Script 2 ONLY

**Regular Suppliers**:
- We receive goods and ship to customer ourselves
- We pay shipping separately (UPS/USPS)
- `buy_price_source` = "Supplier e-mail" or NULL
- Shipping updated by Script 5

**Critical**: Script 5 explicitly excludes dropshipper orders to prevent overwriting their shipping costs!

### Sales Channel Detection

Channels determined by order source + notes:
- **Web**: source = 1
- **B2B**: source = 500
- **Amazon**: source = 200 AND notes contain 'amazon'
- **eBay**: source = 200 AND notes contain 'ebay'

### DEPO Special Handling

DEPO supplier data doesn't include our order_id in their export:
- Cannot match by order_id
- Instead matches by part_num through `parts_suppliers` lookup
- More complex matching logic required

---

## Data Flow Diagram

```
CSV Files (Bolt, UPS, USPS)
  ↓
[Script 1] Import to staging tables
  ↓
Supplier Charge Tables → [Script 2] → our_buy_price
                                     → supplier_price/shipping/handling
  ↓
Bolt Charges → [Script 3] → payment_processor_fee
  ↓
UPS/USPS Charges → [Script 5] → supplier_shipping (for non-dropship)
                               → our_buy_price += shipping
  ↓
.env Files → [Script 6] → commission_and_fee
  ↓
[Script 7] Calculate → good_sold_cost, gross_profit, gross_margin
  ↓
[Script 8] Validate & Fix → Correct our_buy_price errors
  ↓
Final COGS Data in orders_items_margin
```

---

## Summary

These 8 scripts work together to build accurate COGS data:

1. **Import external data** from payment processors and shipping carriers
2. **Match supplier costs** from 9 different supplier systems
3. **Apply payment fees** based on payment method
4. **Add shipping costs** for orders we ship ourselves
5. **Allocate channel fees** proportionally based on monthly totals
6. **Calculate final COGS** using the formula
7. **Validate and fix** any calculation errors

The result is a complete cost breakdown for every order item in the `orders_items_margin` table, enabling accurate profitability analysis.