# MargincalculatorTask.php - Complete Logic Analysis

## Overview
This task processes order items and populates the `orders_items_margin` table with profitability calculations. It runs as a Phalcon CLI task and is the **primary data source** for the Profitability pages.

---

## Main Action: `calculateMarginAction()`

### Phase 1: Initialization & Data Loading (Lines 180-221)

**Reference Data Loaded:**
1. **USAuto Suppliers Array** `[16, 17, 45, 48, 49, 54]` - Suppliers with 4% rebate
2. **Loss Rates** - Category-specific loss percentages (B2B vs B2C)
3. **Category Return Rates** - Return rate by category from latest date
4. **Channel Commissions** - Commission & advertisement fees per channel (Amazon, eBay, B2B, Web)
5. **Item Status Mapping** - Status ID to status name lookup
6. **Suppliers List** - Supplier ID to supplier name lookup

**Date Range:**
- Hardcoded: `from = "2025-11-10 00:00:00"` to `to = current date 23:59:59`
- Processes items with `last_updated` in this range

**Items Query Criteria:**
```sql
current_status IN [1, 3, 4, 6, 12, 24, 32, 39, 43, 48, 50, 52]
AND last_updated >= from AND last_updated <= to
AND supplier_id IS NOT NULL
ORDER BY order_item_id DESC
```

---

## Phase 2: Item Processing Loop (Lines 223-629)

### 2.1 Skip Conditions (Lines 246-282)

**Item is SKIPPED and logged if:**
1. ✅ Already processed (exists in `orders_items_margin` or `orders_items_margin_skipped`)
2. ✅ Sales channel cannot be determined (order source check fails)
3. ✅ Is a PS (PartSelect) order:
   - `order.ps_order_id` is set, OR
   - `item.custom_data` contains "sku", OR
   - `item.ordered_brand` starts with "PS-"
4. ❌ COMMENTED OUT: Supplier not available
5. ❌ COMMENTED OUT: Part is KIT or SET

**Skipped items are recorded in `orders_items_margin_skipped` table with remarks**

---

### 2.2 Sales Channel Determination (Lines 238-244, 1040-1065)

**Logic:**
- `source = 500` → **B2B**
- `source = 1` → **Web**
- `source = 200` → Check notes table:
  - Note contains "Amazon" → **Amazon**
  - Note contains "eBay" → **eBay**

---

### 2.3 Base Margin Record Creation (Lines 292-304)

**Always Populated Fields:**
```php
order_id = order.order_id
external_order_id = order.external_order_id
date_placed = order.date_placed
billing_name = order.billing_first_name + ' ' + order.billing_last_name
billing_address = full concatenated address
order_item_id = item.order_item_id
item_sale_price = item.sale_price
sales_channel = determined channel (Amazon/eBay/B2B/Web)
category = product category name
return_rate = 0.00 (initial)
item_name = product.name or 'Unknown'
```

---

## Phase 3: TWO PROCESSING PATHS

The script branches into **TWO COMPLETELY DIFFERENT PATHS** based on whether `item.supplier_id` is set:

---

### PATH A: WITH SUPPLIER_ID (Lines 305-495) - STANDARD PATH

#### 3.1 Initial Setup
```php
supplier_id = item.supplier_id
channel_commission = from channelCommissionArray[lowercase(sales_channel)]
channel_advertisement_fee = from channelCommissionArray
channel_advertisement_fee_amount = from channelCommissionArray
sku = product.sku
item_shipping = order.shipping / order_item_count (evenly distributed)
sales_price = item_sale_price + item_shipping
our_shipping_source = "Snapshot"
buy_price_source = "Snapshot"
supplier = suppliers[supplier_id] or 'Unknown'
item_status = itemStatusArr[current_status]
category_discount_applied = 0 (default)
is_active = 1 if current_status == 6, else 0
```

#### 3.2 Discount Application (Lines 349-367)

**For B2B Orders:**
- Calls `GetB2BDiscount(item, date_placed)`
- Searches `b2b_products` table by partslink
- Looks up `b2b_price_discount_history` for matching product_id and date
- Sets: `category_discount_applied = 1`, `discount_type`, `discount_percentage`

**For Non-B2B Orders:**
- Calls `GetDiscount(product_id, date_placed)`
- Searches `price_discount_history` for matching product_id and date
- Sets: `category_discount_applied = 1`, `discount_type`, `discount_percentage`

#### 3.3 Credits Lookup (Lines 370-378)

Queries `orders_additional_cost` table:
- **Supplier Credits**: `cost_type = "Supplier Credit"` for this order_item_id
- **Carrier Credits**: `cost_type = "Carrier Credit"` for this order_item_id

#### 3.4 Category & Return Rate (Lines 380-391)

- If category not found → Save with `remarks = "Item Category not found"` and SKIP
- Otherwise: `return_rate = GetReturnRate(category, categoryRates)` (from profitability_return_rates)

#### 3.5 Buy Price Determination - PRIORITY ORDER (Lines 392-456)

**Step 1: Try Snapshot Data (Lines 395-414)**
```php
snapshotdata = IndependentBuyPriceRecording WHERE item_id = order_item_id AND supplier_id = supplier_id
                                            ORDER BY buy_price ASC (cheapest first)
```

**If Found:**
- **USAuto Suppliers (16,17,45,48,49,54):**
  ```php
  usauto_rebate = buy_price * 4%
  supplier_price = buy_price
  supplier_shipping = shipping
  supplier_handling = handling
  our_buy_price = price + shipping + handling - rebate
  ```
- **Other Suppliers:**
  ```php
  usauto_rebate = 0
  supplier_price = buy_price
  supplier_shipping = shipping
  supplier_handling = handling
  our_buy_price = price + shipping + handling
  ```

**Step 2: Fallback to Part Supplier Data (Lines 419-438)**

If `our_buy_price == 0`:
```php
partSupplier = PartsSuppliers WHERE supplier_id = supplier_id
                                 AND partslink = partslink
                                 AND qty > 0
                                 AND in_stock = 1
                             ORDER BY price ASC
```

Apply same USAuto vs Standard logic as above

**Step 3: Exit if Still Zero (Lines 449-456)**
- If `our_buy_price == 0.00` → Save with `remarks = "Our buy price is 0."` and SKIP

#### 3.6 Additional Costs (Lines 458-472)

**Label Cost:**
```sql
SUM(cost) FROM amazon_fees_index
WHERE gp_order_id = order_id AND type = 'Shipping Services'
```

**Credits (re-fetched):**
- Supplier Credits (from `orders_additional_cost`)
- Carrier Credits (from `orders_additional_cost`)

#### 3.7 Loss Rate Calculation (Lines 472-482)

From `profitability_total_loss_rate` by category_id:
- B2B orders → `loss_rate_b2b`
- B2C orders → `loss_rate_b2c`
- Default: `0.00` if not found

#### 3.8 Margin Calculations - FORMULA (Lines 484-494)

**Gross Profit/Margin (OLD):**
```php
grossProfit = (sales_price - our_buy_price - (sales_price * (channel_commission% + channel_advertisement_fee% + loss_rate%))) / sales_price

gross_profit = grossProfit * 100  (percentage)
gross_margin = sales_price * grossProfit  (dollar amount)
```

**Updated Gross Profit/Margin (NEW - uses fixed fee amount):**
```php
channel_advertisement_fee_amount = sales_price * (channel_advertisement_fee / 100)

updatedGrossProfit = (sales_price - our_buy_price - (sales_price * (channel_commission% + loss_rate%) + channel_advertisement_fee_amount)) / sales_price

updated_gross_profit = updatedGrossProfit * 100
updated_gross_margin = sales_price * updatedGrossProfit
```

**Partslink:**
```php
partslink = GetItemPartslink(product_id, part_num)
```

---

### PATH B: WITHOUT SUPPLIER_ID (Lines 496-610) - WAREHOUSE ORDERS

This handles special warehouse orders where supplier isn't directly linked to item.

#### 3.1 Supplier Mapping (Lines 498-510)
```php
IF wc_supplier == 'GALTL02' → supplier_id = 73
ELSE IF wc_supplier == 'GALTL03' → supplier_id = 1
ELSE IF wc_supplier == 'GALTL04' → supplier_id = 3
ELSE → SKIP with "Item Supplier is not available"
```

#### 3.2 Setup (Lines 512-544)
```php
channel_commission = 0
channel_advertisement_fee = 0
channel_advertisement_fee_amount = 0
sku = product.sku
item_shipping = 0  (NO SHIPPING!)
sales_price = item_sale_price (only)
our_shipping_source = "Snapshot"
buy_price_source = "Snapshot"
supplier = suppliers[supplier_id]
item_status = itemStatusArr[current_status]
category_discount_applied = 0
is_active = 1 if current_status == 6, else 0
category_discount_percentage = 0
supplier_credits = 0
carrier_credits = 0
return_rate = 0
```

#### 3.3 Buy Price (Lines 552-576)

**Same Priority as Path A:**
1. Try `IndependentBuyPriceRecording` (snapshot)
   - NO rebates (usauto_rebate = 0)
   - NO shipping/handling
   - `our_buy_price = supplier_price ONLY`

2. Fallback to `PartsSuppliers` data
   - Same as above

**Exit if Zero:**
- Save with `remarks = "Our buy price is 0."` and SKIP

#### 3.4 Costs & Margin Calculation (Lines 596-609)

```php
label_cost = 0
supplier_credits = 0
carrier_credits = 0
loss_rate = 0.00

// INCORRECT FORMULA (sales_price subtracted twice!)
grossProfit = (sales_price - our_buy_price - sales_price) / sales_price
gross_profit = grossProfit * 100
gross_margin = sales_price * grossProfit

channel_advertisement_fee_amount = 0
updatedGrossProfit = (sales_price - our_buy_price - sales_price) / sales_price
updated_gross_profit = updatedGrossProfit * 100
updated_gross_margin = sales_price * updatedGrossProfit

partslink = GetItemPartslink(product_id, part_num)
```

**⚠️ BUG ALERT:** Lines 601, 607 - Formula subtracts `sales_price` twice, resulting in always negative margins!

---

## Phase 4: Save Record (Lines 612-623)

```php
created_at = current timestamp
IF margin.save() succeeds:
    processedCount++
ELSE:
    errorCount++
    Log all validation messages
```

---

## Supporting Methods Summary

### Data Retrieval Methods

| Method | Purpose | Source |
|--------|---------|--------|
| `GetLossRates()` | Category loss rates (B2B/B2C) | `profitability_total_loss_rate` |
| `GetCategoryReturnRates()` | Return rates by category | `profitability_return_rates` (latest date) |
| `GetChannelCommissions()` | Commission & fees per channel | `profitability_channel_commissions` (latest month) |
| `GetSuppliers()` | Supplier ID → name mapping | `suppliers` |
| `GetItemsStatus()` | Status ID → name mapping | `items_status` |
| `GetItemCredit(item_id, type)` | Credits for item | `orders_additional_cost` |
| `GetSnapshotPrices(item_id, supplier_id)` | Recorded buy prices | `independent_buy_price_recording` |
| `GetPartSupplierData(supplier_id, product_id, partslink)` | Part supplier pricing | `parts_suppliers` (in stock, qty > 0) |
| `GetProduct(product_id, part_num)` | Product details | `products` |
| `GetItemCategory(product)` | Category name | `categories` |
| `GetItemPartslink(product_id, part_num)` | Partslink number | `products` |
| `GetOrderDetails(order_id)` | Order information | `orders` |
| `GetOrderSalesChannel(order)` | Determine sales channel | `orders` + `notes` |
| `GetOrderItemCount(order_id)` | Items in order | `orders_items` |
| `GetItemLabelCost(order_id)` | Shipping label cost | `amazon_fees_index` (data warehouse) |
| `GetDiscount(product_id, date)` | B2C discounts | `price_discount_history` |
| `GetB2BDiscount(item, date)` | B2B discounts | `b2b_price_discount_history` |
| `CheckItemProcessed(item_id)` | Avoid duplicates | `orders_items_margin` + `orders_items_margin_skipped` |

### Item Fetch Query (Lines 1135-1155)

```sql
SELECT * FROM orders_items
WHERE current_status IN (1,3,4,6,12,24,32,39,43,48,50,52)
  AND last_updated >= from_date
  AND last_updated <= to_date
  AND supplier_id IS NOT NULL
ORDER BY order_item_id DESC
```

---

## Key Business Rules

### 1. **USAuto Rebate Logic**
- Supplier IDs: 16, 17, 45, 48, 49, 54
- Automatic 4% rebate on buy price
- Reduces `our_buy_price`

### 2. **Shipping Distribution**
- Order shipping cost divided EVENLY by item count
- Each item gets: `order.shipping / GetOrderItemCount(order_id)`

### 3. **Channel Commission Lookup**
- Lowercase channel name: `amazon`, `ebay`, `b2b`, `web`
- Latest month's data used
- Returns: `commission` (%) and `fee` (%)

### 4. **Two Gross Profit Calculations**
- **Old (`gross_profit`/`gross_margin`)**: Treats ad fee as percentage
- **New (`updated_gross_profit`/`updated_gross_margin`)**: Uses fixed dollar amount

### 5. **Data Source Priority**
For buy price:
1. `IndependentBuyPriceRecording` (snapshot) - ORDER BY price ASC
2. `PartsSuppliers` (live pricing) - WHERE in_stock=1 AND qty>0 ORDER BY price ASC
3. Skip if still zero

### 6. **Discount Application**
- B2B orders: Check `b2b_price_discount_history` by product + date
- Other orders: Check `price_discount_history` by product + date
- Must match exact date (date_added LIKE 'YYYY-MM-DD%')

---

## Critical Issues & Observations

### 🐛 **Bugs:**
1. **Path B Formula Error (Lines 601, 607)**: Subtracts `sales_price` twice instead of costs
   ```php
   // Current (WRONG):
   (sales_price - our_buy_price - sales_price) / sales_price

   // Should be:
   (sales_price - our_buy_price) / sales_price
   ```

### ⚠️ **Concerns:**
1. **Hardcoded Date**: Line 215 - `from = "2025-11-10 00:00:00"` should be dynamic
2. **Duplicate Credit Fetches**: Lines 370-378 and 461-471 fetch credits twice
3. **PS Order Detection**: Multiple checks (ps_order_id, custom_data, ordered_brand)
4. **No Validation**: Sales price, buy price can be negative (no bounds checking)
5. **Path B Limitations**: No shipping, commissions, loss rates, credits considered

### 💡 **Design Patterns:**
1. **Skip vs Error**: Items that can't be processed are SKIPPED (saved with remarks) not errored
2. **Defensive Defaults**: Missing data defaults to 0 rather than null
3. **Price Priority**: Always prefers recorded snapshot over live pricing
4. **Category-based Rates**: Loss and return rates are category-specific

---

## Summary Flow Chart

```
┌─────────────────────┐
│  Load Reference     │
│  Data (Rates,       │
│  Commissions, etc)  │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│  Fetch Items        │
│  (Date Range,       │
│   Status Filter)    │
└──────────┬──────────┘
           │
           ▼
      ┌────────┐
      │  LOOP  │◄──────────────┐
      └────┬───┘               │
           │                   │
           ▼                   │
   ┌──────────────┐            │
   │ Already      │ YES        │
   │ Processed?   ├────────────┤
   └──────┬───────┘     SKIP   │
          │ NO                 │
          ▼                    │
   ┌──────────────┐            │
   │ PS Order?    │ YES        │
   │              ├────────────┤
   └──────┬───────┘     SKIP   │
          │ NO                 │
          ▼                    │
   ┌──────────────┐            │
   │ Get Sales    │            │
   │ Channel      │            │
   └──────┬───────┘            │
          │                    │
          ▼                    │
   ┌──────────────┐            │
   │ Has          │ NO         │
   │ supplier_id? ├────────────┤
   └──────┬───────┘     PATH B │
          │ YES                │
          │                    │
   ┌──────▼────────┐           │
   │               │           │
   │   PATH A      │           │
   │               │           │
   │ • Commissions │           │
   │ • Shipping    │           │
   │ • Discounts   │           │
   │ • Credits     │           │
   │ • Loss Rates  │           │
   │ • Snapshot→   │           │
   │   PartSupp    │           │
   │               │           │
   └───────┬───────┘           │
           │                   │
           ▼                   │
   ┌──────────────┐            │
   │ Calculate    │            │
   │ Margins      │            │
   └──────┬───────┘            │
           │                   │
           ▼                   │
   ┌──────────────┐            │
   │ Save to      │            │
   │ orders_items │            │
   │ _margin      │            │
   └──────┬───────┘            │
           │                   │
           └───────────────────┘
```

---

## Database Tables Accessed

**READ:**
- `orders`
- `orders_items`
- `notes`
- `products`
- `categories`
- `suppliers`
- `items_status`
- `parts_suppliers`
- `independent_buy_price_recording`
- `orders_additional_cost`
- `price_discount_history`
- `b2b_price_discount_history`
- `profitability_channel_commissions`
- `profitability_return_rates`
- `profitability_total_loss_rate`
- `amazon_fees_index` (data warehouse)
- `b2b_products` (B2B database)

**WRITE:**
- `orders_items_margin` (primary target)
- `orders_items_margin_skipped` (failed/skipped items)

---

## Output Metrics

At completion, logs:
- `processedCount` - Successfully saved records
- `skippedCount` - Items skipped (with reasons)
- `errorCount` - Database errors during save
- `totalItems` - Total items attempted
- `executionTime` - Seconds elapsed

---

## Recommended Improvements for "Detailed" Version

1. ✅ Fix Path B formula bug (lines 601, 607)
2. ✅ Make date range dynamic (remove hardcoded date)
3. ✅ Add payment processor fees (Stripe, PayPal - not currently included)
4. ✅ Add repayment processor fees (chargebacks)
5. ✅ Consolidate duplicate credit fetches
6. ✅ Add validation for negative prices
7. ✅ Consider removing "consolidated" fields (unused)
8. ✅ Add proper logging for Path B issues
9. ✅ Document why Path B exists and when it's used
10. ✅ Add confidence scoring based on data source quality