# Bizmart Feature Specification — v3.0

> **Price History · Weighted Average Cost (WAC/Moyenne Pondérée) · Advanced Expense Analytics**
>
> Status: DRAFT — Ready for review  
> Author: Copilot  
> Date: January 2025  
> Builds on: Existing `bizmart_price_history`, `bizmart_invoice_items`, `bizmart_expenses` tables

---

## Table of Contents

1. [Executive Summary](#1-executive-summary)
2. [Existing Infrastructure Audit](#2-existing-infrastructure-audit)
3. [Feature 1 — Price History & Trends](#3-feature-1--price-history--trends)
4. [Feature 2 — Weighted Average Cost (WAC)](#4-feature-2--weighted-average-cost-wac)
5. [Feature 3 — Advanced Expense Analytics](#5-feature-3--advanced-expense-analytics)
6. [Feature 4 — Margin Timeline & Alerts](#6-feature-4--margin-timeline--alerts)
7. [Feature 5 — Supplier Cost Comparison](#7-feature-5--supplier-cost-comparison)
8. [Schema Changes](#8-schema-changes)
9. [New PHP Functions](#9-new-php-functions)
10. [New AJAX Endpoints](#10-new-ajax-endpoints)
11. [UI/UX Wireframes](#11-uiux-wireframes)
12. [Migration Plan](#12-migration-plan)
13. [Performance Considerations](#13-performance-considerations)
14. [Implementation Roadmap](#14-implementation-roadmap)

---

## 1. Executive Summary

Bizmart already records price changes in `bizmart_price_history` every time an invoice is saved (~8 call sites). This spec expands that foundation into **five production-ready features**:

| # | Feature | Value |
|---|---------|-------|
| 1 | Price History & Trends | See every purchase/selling price change per product over time, with charts |
| 2 | Weighted Average Cost | Replace fixed `_purchase_price` with WAC calculated from real invoice data |
| 3 | Advanced Expense Analytics | Category breakdown, monthly trends, budget vs actual, recurring projections |
| 4 | Margin Timeline & Alerts | Track margin % over time, alert when margin drops below threshold |
| 5 | Supplier Cost Comparison | Compare same product across suppliers to find cheapest source |

**Design principles:**
- Build on existing tables — no breaking changes
- Algerian locale aware (comma decimal separator, EUR/DZD, RTL-ready)
- Bilingual (EN/FR) via existing `bizmart_text()` helper
- 2-decimal enforcement everywhere (`round2()`)
- Chart.js for all visualizations (already loaded in dashboard)

---

## 2. Existing Infrastructure Audit

### 2.1 Tables Already in Place

| Table | Key Columns | Records |
|-------|-------------|---------|
| `bizmart_price_history` | product_id, purchase_price(12,2), selling_price(12,2), invoice_id, created_at | Per invoice line |
| `bizmart_invoice_items` | invoice_id, product_id, quantity(12,2), purchase_price(12,2), selling_price_used(14,4), subtotal(14,4) | Per invoice line |
| `bizmart_invoices` | supplier_id, supplier_name, total, created_at, status, payment_status | Per invoice |
| `bizmart_expenses` | expense_date, amount(14,4), category, description, supplier_id, payment_method, is_recurring | Per expense |
| `bizmart_suppliers` | name, global_balance, total_invoices, total_paid, total_unpaid | Per supplier |
| `bizmart_supplier_history` | supplier_id, type, amount(14,4), previous_balance, new_balance | Ledger entries |
| `bizmart_payments` | invoice_id, supplier_id, amount(14,4), method, payment_date | Per payment |

### 2.2 Existing Functions

| Function | Location | Purpose |
|----------|----------|---------|
| `bizmart_store_price_history()` | addon-core.php:1212 | INSERT into price_history |
| `bizmart_get_last_prices()` | addon-core.php:1225 | Latest price (fallback to post_meta) |
| `bizmart_metrics_resolve_purchase_price()` | metrics.php:33 | Resolve PP from meta chain |
| `bizmart_metrics_get_inventory_totals()` | metrics.php:97 | Cached stock valuation |
| `bizmart_metrics_clean_price()` | metrics.php:76 | Parse locale-aware price strings |
| `bizmart_add_supplier_history()` | addon-core.php:1173 | Insert supplier ledger entry |

### 2.3 What's Missing

| Gap | Impact |
|-----|--------|
| `price_history` has no `supplier_id` | Cannot compare same product across suppliers |
| `price_history` has no `quantity` | Cannot compute WAC from price_history alone (must JOIN invoice_items) |
| `price_history` has no `source` column | Cannot distinguish invoice-based vs manual edit vs import |
| No WAC calculation function | Inventory uses static `_purchase_price` post_meta, not dynamic WAC |
| No price history UI | Data is recorded but users cannot see it |
| No expense category analytics | Only total sum is shown on dashboard |
| No margin tracking over time | No alerting when margin erodes |
| No supplier price comparison | Must manually check each invoice |

---

## 3. Feature 1 — Price History & Trends

### 3.1 Goal

Let the user see **every price change** for any product: when it changed, who supplied it, at what quantity, and the impact on margin.

### 3.2 Data Model Enhancement

**ALTER `bizmart_price_history`** — add columns:

```sql
ALTER TABLE {prefix}bizmart_price_history
  ADD COLUMN supplier_id BIGINT(20) UNSIGNED DEFAULT NULL AFTER invoice_id,
  ADD COLUMN quantity    DECIMAL(12,2) DEFAULT 1.00 AFTER supplier_id,
  ADD COLUMN source      VARCHAR(30) DEFAULT 'invoice' AFTER quantity,
  ADD COLUMN user_id     BIGINT(20) UNSIGNED DEFAULT NULL AFTER source,
  ADD INDEX idx_product_date (product_id, created_at),
  ADD INDEX idx_supplier (supplier_id);
```

**`source` values:** `invoice` | `manual` | `import` | `wc_edit`

### 3.3 Recording Points

| Trigger | Source Value | Data |
|---------|-------------|------|
| Invoice save (existing ~8 call sites) | `invoice` | product_id, PP, SP, invoice_id, supplier_id (from invoice), quantity (from line) |
| WooCommerce product save (new hook) | `wc_edit` | product_id, new PP/SP, NULL invoice, NULL supplier |
| CSV import (existing importer) | `import` | product_id, PP, SP from import row |
| Manual price edit via Bizmart UI | `manual` | product_id, PP, SP, current_user_id |

**Update `bizmart_store_price_history` signature:**

```php
function bizmart_store_price_history(
    $product_id,
    $purchase_price,
    $selling_price,
    $invoice_id   = null,
    $supplier_id  = null,
    $quantity      = 1,
    $source        = 'invoice',
    $user_id       = null
)
```

Backward-compatible: existing calls with 3-4 args still work.

### 3.4 Query Functions

```php
/**
 * Get full price history for a product.
 * @return array [{id, purchase_price, selling_price, margin_pct, supplier_name, quantity, source, created_at}, ...]
 */
function bizmart_get_price_history($product_id, $limit = 50, $offset = 0) { ... }

/**
 * Get price at a specific date (for historical valuation).
 * Returns the most recent price_history entry on or before $date.
 */
function bizmart_get_price_at_date($product_id, $date) { ... }

/**
 * Get min/max/avg/last purchase price for a product.
 */
function bizmart_get_price_stats($product_id, $since = null) { ... }
```

### 3.5 UI: Product Price History Panel

Accessible from: **Product row click** (on dashboard product table) or **dedicated tab in product edit**.

```
┌─────────────────────────────────────────────────────────┐
│  📊 Price History — Product: "Laptop HP 250"            │
│  Current PP: 45,000.00 DA  |  Current SP: 52,000.00 DA │
│  Margin: 15.6%  |  WAC: 44,250.00 DA                   │
├─────────────────────────────────────────────────────────┤
│  [Chart.js line chart]                                  │
│    ── Purchase Price (blue line)                        │
│    ── Selling Price (green line)                        │
│    ── Margin % (orange dashed, right Y axis)            │
│    X-axis: dates    Y-axis: price (left), % (right)    │
├─────────────────────────────────────────────────────────┤
│  Date        │ PP       │ SP       │ Margin │ Supplier  │ Qty │ Source  │
│  2025-01-15  │ 45,000   │ 52,000   │ 15.6%  │ TechCo    │  10 │ Invoice │
│  2025-01-02  │ 43,500   │ 52,000   │ 19.5%  │ MegaIT    │  5  │ Invoice │
│  2024-12-20  │ 44,000   │ 50,000   │ 13.6%  │ TechCo    │  20 │ Invoice │
│  2024-12-01  │ 44,000   │ 50,000   │ 13.6%  │ —         │  1  │ Manual  │
└─────────────────────────────────────────────────────────┘
```

### 3.6 Price Change Notification

On invoice save, if the new purchase price differs by > 5% from the last recorded price for that product, show a non-blocking admin notice:

```
⚠️ Purchase price for "Laptop HP 250" changed by +12.3% (was 40,000 → now 44,920).
```

Threshold configurable in Bizmart Settings: `biz_price_change_alert_pct` (default: 5).

---

## 4. Feature 2 — Weighted Average Cost (WAC)

### 4.1 What is WAC (Moyenne Pondérée / CUMP)

WAC = **Σ (quantity × purchase_price) / Σ quantity** across all invoice receipts.

This is the standard inventory valuation method used in Algeria (SCF — Système Comptable Financier) and France (PCG — Plan Comptable Général). It is also called **CUMP** (Coût Unitaire Moyen Pondéré) or **PMP** (Prix Moyen Pondéré).

### 4.2 Calculation Method

**Perpetual WAC** (recalculated on each receipt):

```
new_wac = (existing_stock_qty × old_wac + new_qty × new_purchase_price)
          ÷ (existing_stock_qty + new_qty)
```

**Batch WAC** (period-based, simpler):

```
period_wac = Σ (invoice_line.quantity × invoice_line.purchase_price)
             ÷ Σ (invoice_line.quantity)
WHERE invoice.status IN ('received', 'completed')
AND invoice_items.product_id = ?
```

**Recommendation:** Implement **Batch WAC** first (simpler, uses existing data), with Perpetual WAC as a future option.

### 4.3 Data Source

The data already exists in `bizmart_invoice_items`:

```sql
SELECT
    SUM(ii.quantity * ii.purchase_price) / NULLIF(SUM(ii.quantity), 0) AS wac
FROM {prefix}bizmart_invoice_items ii
JOIN {prefix}bizmart_invoices i ON ii.invoice_id = i.id
WHERE ii.product_id = %d
  AND i.status IN ('received', 'completed')
  AND i.deleted_at IS NULL
```

No new table needed for Batch WAC.

### 4.4 New Functions

```php
/**
 * Calculate Weighted Average Cost for a product.
 *
 * @param int         $product_id  WC product or variation ID
 * @param string|null $before_date Optional date cutoff (for historical WAC)
 * @return float WAC rounded to 2 decimals, or 0.0 if no invoice data
 */
function bizmart_get_wac($product_id, $before_date = null): float { ... }

/**
 * Calculate WAC for ALL products at once (for inventory valuation page).
 * Returns: [product_id => wac, ...]
 * Cached via transient (10 min), invalidated when any invoice is saved/deleted.
 */
function bizmart_get_all_wac(): array { ... }

/**
 * Get the valuation method setting.
 * @return string 'wac' | 'last_price' | 'manual'
 */
function bizmart_get_valuation_method(): string {
    return bizmart_get_option('biz_valuation_method', 'last_price');
}
```

### 4.5 Integration with Inventory Metrics

`bizmart_metrics_resolve_purchase_price()` currently reads static `_purchase_price` meta. With WAC enabled:

```php
function bizmart_metrics_resolve_purchase_price(int $id, int $parent_id = 0): float {
    $method = bizmart_get_valuation_method();

    if ($method === 'wac') {
        $wac = bizmart_get_wac($id);
        if ($wac > 0) return $wac;
        // Fallback to parent WAC for variations
        if ($parent_id > 0) {
            $wac = bizmart_get_wac($parent_id);
            if ($wac > 0) return $wac;
        }
    }

    // Existing chain: _purchase_price → parent → _alg_wc_cog_cost → 0
    // ... (keep current logic as fallback)
}
```

### 4.6 Settings

New option in Bizmart Settings page:

```
┌──────────────────────────────────────────────────┐
│  💰 Inventory Valuation Method                   │
│                                                  │
│  ○ Last Invoice Price (current behavior)         │
│  ● Weighted Average Cost (WAC / CUMP)            │
│  ○ Manual (use _purchase_price meta only)        │
│                                                  │
│  ℹ️ WAC calculates the weighted average of all   │
│     purchase prices from your invoices. This is  │
│     the standard method under SCF/PCG accounting │
│     rules.                                       │
└──────────────────────────────────────────────────┘
```

### 4.7 Dashboard WAC Display

On the dashboard inventory card, show:

```
Stock Value (WAC):  1,250,000.00 DA
Stock Value (Last): 1,280,000.00 DA   ← only if different from WAC
Unrealized Margin:    320,000.00 DA   (selling value − WAC value)
```

### 4.8 WAC on Invoice Save

After each invoice save, auto-update `_purchase_price` meta if valuation method = WAC:

```php
// In invoice save handler, after storing items:
if (bizmart_get_valuation_method() === 'wac') {
    foreach ($items as $item) {
        $new_wac = bizmart_get_wac($item['product_id']);
        if ($new_wac > 0) {
            update_post_meta($item['product_id'], '_purchase_price', $new_wac);
        }
    }
    bizmart_invalidate_product_metrics_cache();
}
```

---

## 5. Feature 3 — Advanced Expense Analytics

### 5.1 Current State

- Expenses are stored in `bizmart_expenses` with: date, amount, category, description, supplier_id, payment_method, is_recurring
- Dashboard shows: total expenses as a single number + line on the chart
- Expense page: CRUD table + single "Total expenses (period)" stat card
- Categories are user-defined free text (settings option `biz_expenses_categories`)
- No breakdown, no trends, no budgets

### 5.2 New: Category Breakdown (Pie/Donut Chart)

**Query:**
```sql
SELECT category, SUM(amount) AS total, COUNT(*) AS count
FROM {prefix}bizmart_expenses
WHERE expense_date BETWEEN %s AND %s
GROUP BY category
ORDER BY total DESC
```

**UI:** Donut chart (Chart.js `doughnut`) with legend showing:
```
🏢 Rent:           150,000 DA  (35%)
👥 Salaries:       120,000 DA  (28%)
💡 Utilities:       45,000 DA  (11%)
🚚 Delivery:        38,000 DA   (9%)
📢 Marketing:       30,000 DA   (7%)
🛠 Tools:           25,000 DA   (6%)
📦 Other:           17,000 DA   (4%)
```

### 5.3 New: Monthly Trend (Bar Chart)

**Query:**
```sql
SELECT
    DATE_FORMAT(expense_date, '%Y-%m') AS month,
    category,
    SUM(amount) AS total
FROM {prefix}bizmart_expenses
WHERE expense_date BETWEEN %s AND %s
GROUP BY month, category
ORDER BY month ASC
```

**UI:** Stacked bar chart — each bar = month, stacked by category colors.

### 5.4 New: Budget vs Actual

**New DB table or option:**

```sql
CREATE TABLE {prefix}bizmart_expense_budgets (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category      VARCHAR(255) NOT NULL,
    month         VARCHAR(7) NOT NULL,   -- '2025-01'
    budget_amount DECIMAL(14,4) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_cat_month (category(100), month)
) $charset_collate;
```

Alternatively, store as a serialized option `biz_expense_budgets` if keeping it simple:
```php
[
    '🏢 Rent'     => 150000,   // monthly budget
    '👥 Salaries' => 120000,
    ...
]
```

**UI:** Horizontal bar chart — budget (gray) vs actual (colored):
```
🏢 Rent:     ████████████████░░░░  150,000 / 150,000 (100%) ✅
👥 Salaries: ████████████████████░  130,000 / 120,000 (108%) ⚠️ Over!
💡 Utilities: ████████░░░░░░░░░░░  28,000 / 50,000  (56%)  ✅
```

### 5.5 New: Recurring Expense Projection

With `is_recurring = 1`, project future expenses:

```php
/**
 * Calculate projected expenses for the next N months.
 * Uses actual recurring expenses + their frequency.
 */
function bizmart_project_recurring_expenses($months = 3): array { ... }
```

**UI card:**
```
📅 Projected Monthly Recurring: 285,000 DA
   Next 3 months: 855,000 DA
```

### 5.6 New: Expense KPI Cards

Replace single "Total expenses" card with:

```
┌────────────────┐ ┌────────────────┐ ┌────────────────┐ ┌────────────────┐
│ Total Expenses │ │ Monthly Avg    │ │ vs Last Month  │ │ Top Category   │
│   425,000 DA   │ │   141,667 DA   │ │   ▲ +12.3%     │ │ 🏢 Rent (35%) │
└────────────────┘ └────────────────┘ └────────────────┘ └────────────────┘
```

### 5.7 New: Profit & Loss Summary

Combine revenue, COGS, and expenses into a simple P&L:

```
┌────────────────────────────────────────┐
│  📊 Profit & Loss (January 2025)      │
├────────────────────────────────────────┤
│  Revenue (sales):       2,500,000 DA  │
│  - COGS (WAC):         -1,800,000 DA  │
│  = Gross Profit:          700,000 DA  │
│  - Operating Expenses:   -425,000 DA  │
│  = Net Profit:            275,000 DA  │
│                                       │
│  Gross Margin:  28.0%                 │
│  Net Margin:    11.0%                 │
└────────────────────────────────────────┘
```

---

## 6. Feature 4 — Margin Timeline & Alerts

### 6.1 Margin Tracking

For each product, compute margin at each price history point:

```
margin_pct = ((selling_price - purchase_price) / selling_price) × 100
```

This is already calculable from `bizmart_price_history` — no new table needed.

### 6.2 Dashboard Margin Summary

```
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Avg Margin   │ │ Min Margin   │ │ Products ⚠️  │
│   22.5%      │ │   3.2%       │ │   4 below    │
│              │ │  "USB Cable" │ │   threshold  │
└──────────────┘ └──────────────┘ └──────────────┘
```

### 6.3 Alerts System

**Settings:**
```
Margin Alert Threshold: [  10  ] %
Price Change Alert:     [   5  ] %
Alert Method:           ☑ Admin dashboard banner  ☐ Email
```

**Storage:** WordPress options:
- `biz_margin_alert_threshold` (default: 10)
- `biz_price_change_alert_pct` (default: 5)

**Alert Logic (on invoice save):**

```php
function bizmart_check_margin_alerts($product_id, $purchase_price, $selling_price) {
    $threshold = (float) bizmart_get_option('biz_margin_alert_threshold', 10);
    if ($selling_price <= 0) return;

    $margin = (($selling_price - $purchase_price) / $selling_price) * 100;

    if ($margin < $threshold) {
        $alerts = get_transient('bizmart_margin_alerts') ?: [];
        $alerts[$product_id] = [
            'name'   => get_the_title($product_id),
            'margin' => round($margin, 2),
            'pp'     => $purchase_price,
            'sp'     => $selling_price,
            'date'   => current_time('Y-m-d H:i'),
        ];
        set_transient('bizmart_margin_alerts', $alerts, DAY_IN_SECONDS);
    }
}
```

### 6.4 Low-Margin Products Table

On dashboard, a collapsible panel showing products below threshold:

```
⚠️ Low Margin Products (below 10%)
┌──────────────────┬──────────┬──────────┬────────┬─────────────┐
│ Product          │ PP       │ SP       │ Margin │ Last Change │
│ USB Cable 3m     │ 480 DA   │ 500 DA   │  4.0%  │ 2025-01-14  │
│ Phone Case X     │ 950 DA   │ 1000 DA  │  5.0%  │ 2025-01-12  │
│ Screen Protector │ 280 DA   │ 300 DA   │  6.7%  │ 2025-01-10  │
│ HDMI Cable       │ 850 DA   │ 900 DA   │  5.6%  │ 2025-01-08  │
└──────────────────┴──────────┴──────────┴────────┴─────────────┘
```

---

## 7. Feature 5 — Supplier Cost Comparison

### 7.1 Goal

For any product, show which supplier offers the best price — based on actual invoice history.

### 7.2 Data Source

Requires `supplier_id` on `price_history` (see Schema Changes §8). Query:

```sql
SELECT
    ph.supplier_id,
    s.name AS supplier_name,
    COUNT(*)            AS order_count,
    AVG(ph.purchase_price)  AS avg_price,
    MIN(ph.purchase_price)  AS min_price,
    MAX(ph.purchase_price)  AS max_price,
    MAX(ph.created_at)      AS last_order
FROM {prefix}bizmart_price_history ph
JOIN {prefix}bizmart_suppliers s ON ph.supplier_id = s.id
WHERE ph.product_id = %d
  AND ph.supplier_id IS NOT NULL
GROUP BY ph.supplier_id
ORDER BY avg_price ASC
```

### 7.3 UI: Supplier Comparison Table

```
📊 Supplier Comparison — "Laptop HP 250"

Best Price: MegaIT (avg 43,500 DA)

┌───────────┬───────────┬──────────┬──────────┬───────┬────────────┐
│ Supplier  │ Avg Price │ Min      │ Max      │ Orders│ Last Order │
│ MegaIT    │ 43,500    │ 42,000   │ 45,000   │   5   │ 2025-01-15 │
│ TechCo    │ 44,800    │ 44,000   │ 46,000   │   8   │ 2025-01-20 │
│ InfoPlus  │ 46,200    │ 45,000   │ 48,000   │   3   │ 2024-12-01 │
└───────────┴───────────┴──────────┴──────────┴───────┴────────────┘

💡 Switching from TechCo to MegaIT would save 1,300 DA per unit (2.9%).
```

### 7.4 Savings Estimator

```php
function bizmart_estimate_supplier_savings($product_id): array {
    // For each product: (current avg price - cheapest avg price) × recent monthly qty
    // Returns: ['savings_per_unit' => ..., 'monthly_savings' => ..., 'best_supplier' => ...]
}
```

---

## 8. Schema Changes

### 8.1 ALTER Existing Tables

```sql
-- 1. Add supplier context to price history
ALTER TABLE {prefix}bizmart_price_history
  ADD COLUMN supplier_id BIGINT(20) UNSIGNED DEFAULT NULL AFTER invoice_id,
  ADD COLUMN quantity    DECIMAL(12,2) NOT NULL DEFAULT 1.00 AFTER supplier_id,
  ADD COLUMN source      VARCHAR(30) NOT NULL DEFAULT 'invoice' AFTER quantity,
  ADD COLUMN user_id     BIGINT(20) UNSIGNED DEFAULT NULL AFTER source;

-- 2. Add indexes for performance
ALTER TABLE {prefix}bizmart_price_history
  ADD INDEX idx_ph_product_date (product_id, created_at),
  ADD INDEX idx_ph_supplier (supplier_id);

-- 3. Add index on invoice_items for WAC queries
ALTER TABLE {prefix}bizmart_invoice_items
  ADD INDEX idx_ii_product (product_id);

-- 4. Add index on expenses for analytics
ALTER TABLE {prefix}bizmart_expenses
  ADD INDEX idx_exp_date_cat (expense_date, category(50));
```

### 8.2 New Tables

```sql
-- Expense budgets (optional — can use wp_options instead for MVP)
CREATE TABLE {prefix}bizmart_expense_budgets (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category      VARCHAR(255) NOT NULL,
    month         VARCHAR(7)   NOT NULL,
    budget_amount DECIMAL(14,4) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_cat_month (category(100), month)
) $charset_collate;
```

### 8.3 Backfill Migration

Populate `supplier_id` and `quantity` on existing `price_history` rows from invoice data:

```sql
UPDATE {prefix}bizmart_price_history ph
JOIN {prefix}bizmart_invoices i ON ph.invoice_id = i.id
SET ph.supplier_id = i.supplier_id
WHERE ph.invoice_id IS NOT NULL
  AND ph.supplier_id IS NULL;

UPDATE {prefix}bizmart_price_history ph
JOIN {prefix}bizmart_invoice_items ii
  ON ph.invoice_id = ii.invoice_id
  AND ph.product_id = ii.product_id
SET ph.quantity = ii.quantity
WHERE ph.invoice_id IS NOT NULL
  AND ph.quantity = 1.00;
```

### 8.4 Version Check

```php
$current_db_version = get_option('bizmart_db_version', '1.0');
if (version_compare($current_db_version, '2.0', '<')) {
    bizmart_run_migration_v2();
    update_option('bizmart_db_version', '2.0');
}
```

---

## 9. New PHP Functions

### 9.1 WAC Functions (in `includes/wac.php`)

```php
/**
 * Calculate Batch WAC for a single product.
 */
function bizmart_get_wac(int $product_id, ?string $before_date = null): float

/**
 * Calculate WAC for all products at once (single query).
 * Returns associative array [product_id => wac].
 * Cached: transient bizmart_all_wac_v1 (10 min).
 */
function bizmart_get_all_wac(): array

/**
 * Invalidate WAC cache. Called on invoice save/delete.
 */
function bizmart_invalidate_wac_cache(): void

/**
 * Get the configured valuation method.
 * @return string 'wac' | 'last_price' | 'manual'
 */
function bizmart_get_valuation_method(): string
```

### 9.2 Price History Functions (in `includes/price-history.php`)

```php
/**
 * Extended price history store (backward-compatible).
 */
function bizmart_store_price_history(
    $product_id, $purchase_price, $selling_price,
    $invoice_id = null, $supplier_id = null,
    $quantity = 1, $source = 'invoice', $user_id = null
): void

/**
 * Get paginated price history for a product.
 */
function bizmart_get_price_history(int $product_id, int $limit = 50, int $offset = 0): array

/**
 * Get price statistics (min, max, avg, count, first_date, last_date).
 */
function bizmart_get_price_stats(int $product_id, ?string $since = null): array

/**
 * Get the purchase price as of a specific date.
 */
function bizmart_get_price_at_date(int $product_id, string $date): ?array

/**
 * Check and fire margin/price-change alerts after invoice save.
 */
function bizmart_check_price_alerts(int $product_id, float $pp, float $sp): void
```

### 9.3 Expense Analytics Functions (in `includes/expense-analytics.php`)

```php
/**
 * Get expense totals grouped by category for a date range.
 */
function bizmart_get_expenses_by_category(string $start, string $end): array

/**
 * Get monthly expense trend (optionally per category).
 */
function bizmart_get_expense_trend(string $start, string $end, ?string $category = null): array

/**
 * Get budget vs actual for a month.
 */
function bizmart_get_budget_vs_actual(string $month): array

/**
 * Project recurring expenses for next N months.
 */
function bizmart_project_recurring(int $months = 3): array

/**
 * Simple P&L for a date range.
 */
function bizmart_get_pnl(string $start, string $end): array
```

### 9.4 Supplier Comparison Functions (in `includes/supplier-compare.php`)

```php
/**
 * Compare suppliers for a product based on invoice history.
 */
function bizmart_compare_suppliers(int $product_id): array

/**
 * Estimate savings from switching supplier.
 */
function bizmart_estimate_supplier_savings(int $product_id): array
```

---

## 10. New AJAX Endpoints

| Action | Handler | Input | Output |
|--------|---------|-------|--------|
| `bizmart_get_product_price_history` | `bizmart_ajax_price_history` | product_id, limit, offset | Paginated price history rows |
| `bizmart_get_product_price_chart` | `bizmart_ajax_price_chart` | product_id, range | Chart.js-ready labels + datasets |
| `bizmart_get_product_wac` | `bizmart_ajax_wac` | product_id | `{wac, last_price, method, total_qty}` |
| `bizmart_get_expense_category_breakdown` | `bizmart_ajax_expense_cats` | start, end | Categories with totals |
| `bizmart_get_expense_trend` | `bizmart_ajax_expense_trend` | start, end | Monthly breakdown |
| `bizmart_get_budget_vs_actual` | `bizmart_ajax_budget` | month | Budget/actual pairs |
| `bizmart_save_expense_budget` | `bizmart_ajax_save_budget` | category, month, amount | Success/error |
| `bizmart_get_supplier_compare` | `bizmart_ajax_supplier_compare` | product_id | Supplier cost comparison |
| `bizmart_get_margin_alerts` | `bizmart_ajax_margin_alerts` | — | Active low-margin alerts |
| `bizmart_get_pnl` | `bizmart_ajax_pnl` | start, end | P&L summary |

All endpoints: nonce-verified, capability-checked (`manage_woocommerce`), JSON response with `wp_send_json_success()`.

---

## 11. UI/UX Wireframes

### 11.1 Dashboard Integration

The existing dashboard gets new cards:

```
┌─── Row 1: KPI Cards (existing + new) ───────────────────────────────┐
│ [Revenue]  [Orders]  [Expenses]  [Stock Value]  [Margin Avg]  [WAC] │
└─────────────────────────────────────────────────────────────────────┘

┌─── Row 2: Charts ───────────────────────────────────────────────────┐
│ [Revenue + Expenses Line Chart (existing)]  │  [Expense Donut NEW] │
└─────────────────────────────────────────────────────────────────────┘

┌─── Row 3: Tables (existing + alerts) ──────────────────────────────┐
│ [Products Table (existing)]  │  [⚠️ Low Margin Alerts NEW]         │
└─────────────────────────────────────────────────────────────────────┘

┌─── Row 4: P&L Summary (NEW) ──────────────────────────────────────┐
│ [Revenue | COGS | Gross Margin | Expenses | Net Profit]            │
└─────────────────────────────────────────────────────────────────────┘
```

### 11.2 Product Price History Modal

Triggered by clicking a product row → opens a modal (reuse existing modal pattern from invoices.php):

```
┌────────────────────────────────────────────────────────────────┐
│  ✕                                                             │
│  📊 Price History — Laptop HP 250                             │
│                                                                │
│  WAC: 44,250.00 DA  │  Last PP: 45,000.00  │  Margin: 15.6%  │
│                                                                │
│  [=== Chart (300px height, 2 Y-axes) ===]                     │
│                                                                │
│  Supplier Comparison:                                          │
│    🥇 MegaIT — 43,500 DA avg (5 orders)                       │
│    🥈 TechCo — 44,800 DA avg (8 orders)                       │
│                                                                │
│  History:                                                      │
│  ┌────────┬────────┬────────┬───────┬──────────┬─────┬───────┐│
│  │ Date   │ PP     │ SP     │ Mrg%  │ Supplier │ Qty │ Src   ││
│  │ Jan 15 │ 45,000 │ 52,000 │ 13.5% │ TechCo   │ 10  │ Inv   ││
│  │ Jan 02 │ 43,500 │ 52,000 │ 16.3% │ MegaIT   │  5  │ Inv   ││
│  │ ...    │        │        │       │          │     │       ││
│  └────────┴────────┴────────┴───────┴──────────┴─────┴───────┘│
│  [Load More]                                                   │
└────────────────────────────────────────────────────────────────┘
```

### 11.3 Expense Analytics Page Enhancement

Add analytics section above the existing expenses table:

```
┌─── Analytics Row ──────────────────────────────────────────────┐
│ [Total] [Monthly Avg] [vs Last Month] [Top Category]          │
└────────────────────────────────────────────────────────────────┘

┌─── Charts Row ────────────────────────────────────────────────┐
│ [Category Donut (50%)]  │  [Monthly Stacked Bars (50%)]      │
└────────────────────────────────────────────────────────────────┘

┌─── Budget Row (collapsible) ──────────────────────────────────┐
│ [Category budget bars — actual vs planned]                    │
│ [Set Budget] button opens inline editable fields              │
└────────────────────────────────────────────────────────────────┘

┌─── P&L Card ──────────────────────────────────────────────────┐
│ Revenue - COGS = Gross - Expenses = Net                       │
└────────────────────────────────────────────────────────────────┘

┌─── Existing CRUD Table ───────────────────────────────────────┐
│ (unchanged)                                                    │
└────────────────────────────────────────────────────────────────┘
```

### 11.4 Settings Page Additions

```
┌─── Valuation Section ─────────────────────────────────────────┐
│ Valuation Method:  ○ Last Price  ● WAC  ○ Manual              │
│ Margin Alert:      [ 10 ] %                                   │
│ Price Alert:       [  5 ] %                                   │
│ Alert Display:     ☑ Dashboard  ☐ Email                       │
└────────────────────────────────────────────────────────────────┘

┌─── Expense Budget Section ────────────────────────────────────┐
│ Monthly Budgets:                                              │
│   🏢 Rent:       [ 150,000 ]                                 │
│   👥 Salaries:   [ 120,000 ]                                 │
│   💡 Utilities:  [  50,000 ]                                 │
│   ...                                                         │
│ [Save Budgets]                                                │
└────────────────────────────────────────────────────────────────┘
```

---

## 12. Migration Plan

### 12.1 Steps

1. **Bump DB version** → `2.0`
2. **Run ALTER TABLE** on `bizmart_price_history` (add 4 columns + 2 indexes)
3. **Add index** on `bizmart_invoice_items.product_id`
4. **Add index** on `bizmart_expenses (expense_date, category)`
5. **Create** `bizmart_expense_budgets` table
6. **Backfill** supplier_id + quantity from invoices (one-time migration)
7. **Add new options** with defaults:
   - `biz_valuation_method` → `'last_price'` (non-breaking default)
   - `biz_margin_alert_threshold` → `10`
   - `biz_price_change_alert_pct` → `5`
   - `biz_expense_budgets` → `[]`
8. **Update DB version** to `2.0`

### 12.2 Safety

- All ALTERs use `ADD COLUMN ... DEFAULT` — no data loss risk
- Backfill uses UPDATE with JOINs — idempotent (can run multiple times)
- Default valuation method = `last_price` — existing behavior preserved
- New columns are nullable — existing INSERT calls don't break

### 12.3 Rollback

- Columns can be dropped without data loss
- No existing columns are modified
- Options are independent (deleting them reverts to defaults)

---

## 13. Performance Considerations

### 13.1 Caching Strategy

| Data | Cache Key | TTL | Invalidation |
|------|-----------|-----|--------------|
| All WAC values | `bizmart_all_wac_v1_{blog}` | 10 min | Invoice save/delete |
| Inventory totals | `bizmart_inventory_totals_v9_{blog}` (existing) | 10 min | Product stock/price change |
| Expense category breakdown | `bizmart_expense_cats_{hash}` | 5 min | Expense save/delete |
| P&L data | `bizmart_pnl_{hash}` | 10 min | Order/expense/invoice change |
| Margin alerts | `bizmart_margin_alerts` | 24 hours | Invoice save |

### 13.2 Query Optimization

- **WAC all-products query** — single `GROUP BY product_id` query, not N+1
- **Price history** — indexed on `(product_id, created_at)` → covered index for lookups
- **Expense trend** — indexed on `(expense_date, category)` → range scan
- **Supplier comparison** — indexed on `supplier_id` in price_history → fast GROUP BY

### 13.3 Limits

- Price history modal: paginated (50 rows per page)
- Expense trend: max 24 months in one chart
- WAC calculation: only counts invoices with status `received`/`completed` and non-deleted
- Dashboard alert cards: max 10 low-margin products shown (with "View all" link)

---

## 14. Implementation Roadmap

### Phase 1 — Foundation (Est. 2–3 hours)

- [ ] Schema migration: ALTER price_history, add indexes, create budgets table
- [ ] Update `bizmart_store_price_history()` with new params (backward-compatible)
- [ ] Backfill migration for existing price_history rows
- [ ] DB version check in plugin activation

### Phase 2 — WAC Engine (Est. 2–3 hours)

- [ ] Implement `bizmart_get_wac()` and `bizmart_get_all_wac()`
- [ ] Add valuation method setting to Settings page
- [ ] Integrate WAC into `bizmart_metrics_resolve_purchase_price()`
- [ ] Auto-update `_purchase_price` meta on invoice save when WAC mode enabled
- [ ] Cache invalidation hooks

### Phase 3 — Price History UI (Est. 3–4 hours)

- [ ] AJAX endpoints: price history, price chart, WAC lookup
- [ ] Product price history modal (table + Chart.js dual-axis chart)
- [ ] Wire click handler on dashboard product rows
- [ ] Price change alert on invoice save (admin notice)

### Phase 4 — Expense Analytics (Est. 3–4 hours)

- [ ] AJAX endpoints: category breakdown, trend, budget, P&L
- [ ] Expense analytics cards (4 KPI cards above table)
- [ ] Category donut chart
- [ ] Monthly stacked bar chart
- [ ] Budget editor (inline or settings page)
- [ ] Budget vs actual bars
- [ ] Recurring expense projection card

### Phase 5 — Margin & Supplier (Est. 2–3 hours)

- [ ] Margin alert system (check on invoice save, transient storage)
- [ ] Low-margin products panel on dashboard
- [ ] Supplier comparison AJAX + UI in price history modal
- [ ] Savings estimator

### Phase 6 — P&L & Polish (Est. 1–2 hours)

- [ ] P&L summary card on dashboard
- [ ] P&L section on expense analytics page
- [ ] Bilingual labels (EN/FR via `bizmart_text()`)
- [ ] RTL adjustments for Arabic users
- [ ] Final testing with Algerian locale (comma decimals, EUR/DZD)

---

## Appendix A — Formulas Reference

| Formula | Expression |
|---------|-----------|
| **Batch WAC** | `Σ(qty × PP) / Σ(qty)` across all received invoices |
| **Perpetual WAC** | `(old_stock × old_WAC + new_qty × new_PP) / (old_stock + new_qty)` |
| **Gross Margin %** | `(SP - PP) / SP × 100` |
| **Net Margin %** | `(Revenue - COGS - Expenses) / Revenue × 100` |
| **Stock Value (WAC)** | `Σ(stock_qty × WAC)` for all products |
| **Stock Value (Last)** | `Σ(stock_qty × last_PP)` for all products |
| **Unrealized Profit** | `Stock Value (Sell) - Stock Value (WAC)` |
| **Budget Variance** | `(Actual - Budget) / Budget × 100` |

## Appendix B — Bilingual Labels

| Key | English | French |
|-----|---------|--------|
| wac_label | Weighted Average Cost | Coût Unitaire Moyen Pondéré (CUMP) |
| last_price | Last Invoice Price | Dernier Prix Facture |
| margin_pct | Margin % | Marge % |
| price_history | Price History | Historique des Prix |
| supplier_compare | Supplier Comparison | Comparaison Fournisseurs |
| budget_vs_actual | Budget vs Actual | Budget vs Réel |
| gross_profit | Gross Profit | Bénéfice Brut |
| net_profit | Net Profit | Bénéfice Net |
| expense_trend | Expense Trend | Tendance des Dépenses |
| category_breakdown | Category Breakdown | Répartition par Catégorie |
| low_margin_alert | Low Margin Alert | Alerte Marge Faible |
| price_change_alert | Price Change Alert | Alerte Changement de Prix |
| recurring_projection | Recurring Projection | Projection Récurrentes |
| valuation_method | Valuation Method | Méthode de Valorisation |
| savings_estimate | Potential Savings | Économies Potentielles |

## Appendix C — Settings Keys

| Option Key | Type | Default | Description |
|------------|------|---------|-------------|
| `biz_valuation_method` | string | `'last_price'` | WAC / last_price / manual |
| `biz_margin_alert_threshold` | float | `10` | Min acceptable margin % |
| `biz_price_change_alert_pct` | float | `5` | Alert when price changes by more than X% |
| `biz_expense_budgets` | array | `[]` | `[category => monthly_amount]` |
| `biz_show_pnl_dashboard` | bool | `true` | Show P&L on main dashboard |
| `biz_wac_include_shipping` | bool | `false` | Include invoice shipping in WAC calc |
| `biz_db_version` | string | `'2.0'` | Schema migration tracker |
