# Supporting Files for COGS Scripts

## Overview

This guide documents all supporting files needed by the COGS update scripts and how they are configured.

---

## 📁 Files Copied from Original Location

All supporting files were copied from:
```
/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/
```

To our profitability directory:
```
/home/centralgoparts/public_html/profitability/
```

---

## 📄 .env Configuration Files

### Purpose
Monthly configuration files containing commission and ad spend data for Script 6 (6_update_commission_and_fees.py).

### Files Copied

1. **`.env.example`** - Template showing all required variables
2. **`.env.08`** - August 2025 commission and ad spend data
3. **`.env.09`** - September 2025 commission and ad spend data

### Usage Pattern

Script 6 loads `.env.{month}` files based on the order date:
- Orders from August 2025 → Uses `.env.08`
- Orders from September 2025 → Uses `.env.09`
- etc.

### Required Variables in .env Files

```bash
# B2B Commission Fees (DOLLAR amounts from Income Statement)
CCC_FEES_DOLLAR=2736.33
OPSTRAX_FEES_DOLLAR=897.18
PARTS_TRADER_FEES_DOLLAR=4951.32

# Amazon (PERCENTAGE + DOLLAR amount)
AMAZON_COMMISSION_PERCENTAGE=12.0
AMAZON_AD_SPEND_DOLLAR=5432.10

# eBay (PERCENTAGE + DOLLAR amount)
EBAY_COMMISSION_PERCENTAGE=16.35
EBAY_AD_SPEND_DOLLAR=1234.56

# Website (DOLLAR amount only - no commission)
WEBSITE_AD_SPEND_DOLLAR=3456.78
```

### Monthly Workflow

When processing a new month (e.g., October 2025):

1. Copy the template:
   ```bash
   cp .env.example .env.10
   ```

2. Edit `.env.10` and fill in actual values from Income Statement:
   ```bash
   nano .env.10
   ```

3. Run Script 6 and Script 7:
   ```bash
   python3 6_update_commission_and_fees.py
   python3 7_calculate_final_cogs.py
   ```

---

## 📦 Python Dependencies

### requirements.txt

All required Python packages are listed in `requirements.txt`:

```
PyMySQL==1.1.0
python-dotenv==1.0.0
mysql-connector-python==8.0.33
```

### Dependency Breakdown

| Package | Version | Used By | Purpose |
|---------|---------|---------|---------|
| `PyMySQL` | 1.1.0 | Script 1 (sync_margins_detailed.py) | Main margin calculator |
| `python-dotenv` | 1.0.0 | Script 6 (commission_and_fees) | Load .env files |
| `mysql-connector-python` | 8.0.33 | Scripts 2-8 (COGS scripts) | Database connectivity |

### Installation

To install all dependencies:

```bash
cd /home/centralgoparts/public_html/profitability
pip3 install -r requirements.txt
```

Or install individually:

```bash
pip3 install PyMySQL==1.1.0
pip3 install python-dotenv==1.0.0
pip3 install mysql-connector-python==8.0.33
```

---

## 📊 CSV Data Directories

### Data Source Location

Script 2 (2_import_all_data.py) 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')
```

### Why Original Location?

The CSV upload workflow hasn't changed - files are still uploaded to:
- `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/bolt/`
- `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/shiphaven/`
- `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/usps-stamps/`

Only the **target database table** has changed from `orders_items_margin` to `orders_items_margin_detailed`.

### CSV File Types

1. **Bolt Payment Processor Fees**
   - Directory: `bolt/`
   - Contains: Transaction fees charged by Bolt
   - Import Target: `payment_processor_charges-Bolt` table

2. **UPS ShipHaven Shipping Costs**
   - Directory: `shiphaven/`
   - Contains: Actual UPS shipping charges
   - Import Target: `shipping_charges-ups-shiphaven` table

3. **USPS Stamps.com Shipping Costs**
   - Directory: `usps-stamps/`
   - Contains: Actual USPS shipping charges
   - Import Target: `shipping_charges-usps_stampscom` table

---

## 🎯 Master Orchestrator Script

### File: `run_all_cogs_updates.py`

Master script that runs all 7 COGS scripts in correct sequence.

**Copied and Modified**:
- ✅ BASE_DIR updated to `/home/centralgoparts/public_html/profitability`
- ✅ Script names updated (1→2, 2→3, 3→4, 5→5, 6→6, 7→7, 8→8)
- ✅ Target table reference added in header

**Usage**:

```bash
cd /home/centralgoparts/public_html/profitability
python3 run_all_cogs_updates.py
```

**Execution Order**:
1. Script 2: Import all data (Bolt, ShipHaven, USPS)
2. Script 3: Update supplier costs (9 suppliers)
3. Script 4: Update payment processor fees
4. Script 5: Update customer shipping costs
5. Script 6: Update commission and fees (requires .env)
6. Script 8: Fix multiplier bugs
7. Script 7: Calculate final COGS

**Output**:
- Creates timestamped log: `logs/orchestrator_YYYYMMDD_HHMMSS.log`
- Shows success/failure for each script
- Displays total execution time
- Returns exit code 0 (success) or 1 (failure)

---

## 🗂️ Directory Structure

```
/home/centralgoparts/public_html/profitability/
├── .env.example                       # Template for monthly config
├── .env.08                            # August 2025 commission data
├── .env.09                            # September 2025 commission data
├── requirements.txt                   # Python dependencies
├── run_all_cogs_updates.py           # Master orchestrator
│
├── 1_sync_margins_detailed.py        # Main margin calculator
├── 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   # Commission & ads (uses .env)
├── 7_calculate_final_cogs.py         # Final COGS
├── 8_fix_multiplier_bugs.py          # Validation & fixes
│
├── logs/                              # Timestamped execution logs
│
└── [Documentation files]
    ├── COGS_SCRIPTS_README.md
    ├── SUPPORTING_FILES_GUIDE.md (this file)
    └── COGS_SCRIPTS_ANALYSIS.md
```

---

## ✅ Verification Checklist

To verify all supporting files are in place:

### 1. Check .env Files
```bash
ls -la /home/centralgoparts/public_html/profitability/.env*
```

Expected output:
```
.env.08
.env.09
.env.example
```

### 2. Check Python Dependencies
```bash
pip3 list | grep -E "PyMySQL|python-dotenv|mysql-connector"
```

Expected output:
```
PyMySQL                    1.1.0
python-dotenv              1.0.0
mysql-connector-python     8.0.33
```

### 3. Check Master Orchestrator
```bash
ls -la /home/centralgoparts/public_html/profitability/run_all_cogs_updates.py
```

Should be executable (`-rwxr-xr-x`)

### 4. Verify CSV Data Source Paths
```bash
grep "DATA_SOURCE_BASE" /home/centralgoparts/public_html/profitability/2_import_all_data.py
```

Should show:
```python
DATA_SOURCE_BASE = '/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts'
```

### 5. Verify Target Table
```bash
grep -h "orders_items_margin_detailed" /home/centralgoparts/public_html/profitability/[2-8]_*.py | head -5
```

Should return multiple results (all scripts use correct table)

---

## 🔄 Monthly Maintenance

### Required Actions Each Month

1. **Create new .env file** for the month:
   ```bash
   cd /home/centralgoparts/public_html/profitability
   cp .env.example .env.10  # For October
   nano .env.10
   ```

2. **Fill in Income Statement data**:
   - B2B commission totals
   - Amazon/eBay commission percentages and ad spend
   - Website ad spend

3. **Run commission update**:
   ```bash
   python3 6_update_commission_and_fees.py
   ```

4. **Calculate final COGS**:
   ```bash
   python3 7_calculate_final_cogs.py
   ```

### Optional: Run All Scripts

If you want to recalculate everything from scratch:

```bash
python3 run_all_cogs_updates.py
```

---

## 📝 Important Notes

### ⚠️ Critical Points

1. **CSV Data Location**
   - CSV files are NOT copied to profitability directory
   - Scripts read from original location: `/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/`
   - Only the target table has changed

2. **Monthly .env Files**
   - Must be created BEFORE running Script 6
   - Naming format: `.env.{month}` where month is 01-12
   - Values come from Income Statement, NOT automated

3. **Target Table**
   - ALL scripts work exclusively with `orders_items_margin_detailed`
   - Scripts do NOT touch `orders_items_margin` table

4. **Execution Order Matters**
   - Scripts must run in sequence (2 → 3 → 4 → 5 → 6 → 8 → 7)
   - Use `run_all_cogs_updates.py` to ensure correct order

---

## 🆘 Troubleshooting

### Missing .env File Error

**Error**: `FileNotFoundError: .env.10`

**Solution**: Create the .env file for that month:
```bash
cp .env.example .env.10
nano .env.10  # Fill in values
```

### Python Module Not Found

**Error**: `ModuleNotFoundError: No module named 'dotenv'`

**Solution**: Install dependencies:
```bash
pip3 install -r requirements.txt
```

### CSV Import Fails

**Error**: Script 2 can't find CSV files

**Solution**: Verify original CSV directories exist:
```bash
ls -la /home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/bolt/
ls -la /home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/shiphaven/
ls -la /home/oldgoparts/www/scripts/cogs_investigation/combined_scripts/usps-stamps/
```

---

## 📚 Related Documentation

- **COGS_SCRIPTS_README.md** - Detailed script descriptions and execution order
- **COGS_SCRIPTS_ANALYSIS.md** - Original script analysis and logic breakdown
- **SYNC_STATUS_FEATURE.md** - Documentation for status/supplier sync feature

---

## ✨ Summary

All supporting files are now in place:

✅ `.env.example`, `.env.08`, `.env.09` - Monthly configuration files
✅ `requirements.txt` - Python dependencies (PyMySQL, python-dotenv, mysql-connector-python)
✅ `run_all_cogs_updates.py` - Master orchestrator script
✅ CSV data directories - Continue using original location

The COGS scripts are now ready to run against the `orders_items_margin_detailed` table!