# ECStores CLV Tracking — Design Spec

## Goal

Give Pro-tier merchants a unified customer view — aggregating both registered users and guests by `contact_email` — showing lifetime value metrics (total spent, net spent, order count, average order value) and a full per-customer order history.

## Context

- **Platform:** Laravel 13, Livewire 4.3, Filament 5.6, stancl/tenancy 3.x
- **Plan gate:** `has_clv_tracking` on the `Plan` model; `PlanService::hasClvTracking()` already wired
- **Tenancy:** New view created via tenant migration under `database/migrations/tenant/`
- **Existing pattern:** Follows same nav-badge + persistent-notification gate pattern as Coupons, Expenses, and Refunds
- **Customer identity:** `contact_email` is the universal key. Orders with `user_id` (registered users) and orders without (guests) are treated equally — same email = same customer.
- **Known limitation:** If the same email has placed orders under different names, `MAX(contact_name)` picks one name arbitrarily. Acceptable trade-off for email-based identity.

---

## Data Layer

### New: `customer_summary` MySQL View

Created via tenant migration (`DB::statement`). Aggregates all orders by `contact_email`:

```sql
CREATE OR REPLACE VIEW customer_summary AS
SELECT
    contact_email,
    MAX(contact_name)            AS contact_name,
    COUNT(*)                     AS order_count,
    SUM(total)                   AS total_spent,
    SUM(amount_refunded)         AS total_refunded,
    SUM(total - amount_refunded) AS net_spent,
    AVG(total)                   AS avg_order_value,
    MIN(ordered_at)              AS first_order_at,
    MAX(ordered_at)              AS last_order_at
FROM orders
GROUP BY contact_email
```

The `down()` migration drops the view with `DROP VIEW IF EXISTS customer_summary`.

### `Customer` Model

Reads from the view — no writes, no timestamps:

```
$table     = 'customer_summary'
$primaryKey = 'contact_email'    (string, non-incrementing)
$incrementing = false
$keyType   = 'string'
$timestamps = false
$fillable  = []                  (read-only)
```

**Relationship:**
```
orders() → hasMany(Order::class, 'contact_email', 'contact_email')
```

This works for both registered and guest orders since `contact_email` is always populated on orders.

---

## Filament Resource

### `CustomerResource`

**Location:** `app/Filament/Resources/Customers/CustomerResource.php`

**Navigation:**
- Group: `'Store'`
- Sort: `4` (after Refunds at sort 3)
- Icon: `heroicon-o-users`
- Label: `'Customers'`

**Plan gate:**
- `getNavigationBadge()` returns `'Pro'` when `!hasClvTracking()`, null otherwise
- `getNavigationBadgeColor()` returns `'warning'` when `!hasClvTracking()`, null otherwise
- `canCreate()` always returns false (view is read-only by design)
- `ListCustomers::mount()` sends persistent warning notification when locked:
  > "Customer lifetime value tracking is available on the Pro plan. Contact us to upgrade."

**List table columns:**

| Column | Label | Notes |
|---|---|---|
| `contact_name` | Name | Searchable, sortable |
| `contact_email` | Email | Searchable, sortable |
| `order_count` | Orders | Sortable |
| `total_spent` | Total Spent | Prefix `$`, numeric 2dp, sortable |
| `net_spent` | Net Spent | Prefix `$`, numeric 2dp, sortable |
| `avg_order_value` | Avg Order | Prefix `$`, numeric 2dp |
| `last_order_at` | Last Order | Date `M j, Y`, sortable, default sort desc |

**No filters** — searchable columns provide sufficient lookup.

**Pages:**
```
'index' → ListCustomers::route('/')
'view'  → ViewCustomer::route('/{record}/view')
```

No `create` or `edit` routes.

---

### `ListCustomers` Page

```
extends ListRecords
mount(): parent::mount() + persistent warning notification if !hasClvTracking()
getHeaderActions(): []
```

---

### `ViewCustomer` Page

```
extends ViewRecord
```

**Infolist (stats at top):**

| Entry | Label | Format |
|---|---|---|
| `order_count` | Orders | Plain integer |
| `total_spent` | Total Spent | Prefix `$`, 2dp |
| `total_refunded` | Total Refunded | Prefix `$`, 2dp |
| `net_spent` | Net Spent | Prefix `$`, 2dp |
| `avg_order_value` | Avg Order Value | Prefix `$`, 2dp |
| `first_order_at` | First Order | Date `M j, Y` |
| `last_order_at` | Last Order | Date `M j, Y` |

**Relation managers:**
```
CustomerOrdersRelationManager
```

---

### `CustomerOrdersRelationManager`

**Location:** `app/Filament/Resources/Customers/RelationManagers/CustomerOrdersRelationManager.php`

```
protected static string $relationship = 'orders';
```

**Table columns:**

| Column | Label | Notes |
|---|---|---|
| `ordered_at` | Ordered | DateTime `M j, Y g:i a`, sortable, default desc |
| `id` | Order # | Sortable |
| `status` | Status | Badge with colors (pending=warning, processing=info, shipped=success, cancelled=danger, partially_refunded=warning, refunded=gray) |
| `total` | Total | Prefix `$`, numeric 2dp |
| `amount_refunded` | Refunded | Prefix `$`, numeric 2dp, placeholder `—` |

**Actions:** none — read-only. `recordActions([])`, `toolbarActions([])`, `headerActions([])`.

---

## Deployment

1. Run tenant migration (creates `customer_summary` view)
2. Clear caches
3. Verify: Customers nav item appears in Store group for Pro tenants; locked with badge for lower tiers

---

## Future Considerations (out of scope)

- Export customer list to CSV
- Customer tags or segments
- Integration with abandoned cart (identify high-value customers with abandoned carts)
- Net revenue integration with Financial Reports