# Shipment Handling Flow: Admin Order Management Design Document

## 1. OVERVIEW

Shipment handling in this system refers to the lifecycle management of vehicle orders from purchase through final delivery. It encompasses:

- **Orders**: Customer purchase records that aggregate one or more vehicles with payment information
- **Vehicles**: Individual car records with physical characteristics (chassis, make, model, mileage)
- **Auction Data**: Source information including ports, timelines, and shipping details linked via AuctionCar
- **Logistics Stages**: Discrete phases of shipment progress (reserved, confirmed, waiting for departure, shipped, in transit, arrived, completed)
- **Documents**: Bill of Lading (B/L), ownership papers, and inspection certificates

The shipment lifecycle is triggered after checkout and progresses through distinct stages, each of which updates the customer dashboard view and availability of actions (payments, document downloads, etc.).

---

## 2. CURRENT DATA STRUCTURE ANALYSIS

### 2.1 Orders Table
```
orders:
  - id (PK)
  - user_id (FK → users/customer)
  - seller_id (FK → users/seller)
  - status: enum('reserved', 'payment_pending', 'paid', 'shipment_pending', 'shipped', 'in_transit', 'arrived', 'delivered', 'completed', 'cancelled')
  - customer_status: similar tracking from customer perspective
  - total_price, vehicle_price, shipping_cost (logistics totals)
  - paid_amount (cumulative payments)
  - remaining_balance (total_price - paid_amount)
  - buy_date, departure_date, arrival_date, dhl_date (timeline)
  - from_port, arrival_port (logistics routing)
  - tracking_number (carrier tracking)
  - min_deposit_percent, min_deposit_amount (deposit rules)
```

### 2.2 Vehicles Table
```
vehicles:
  - id (PK)
  - seller_id (FK → users/seller)
  - auction_car_id (FK → auction_cars)
  - make, model, year, chassis (physical attributes)
  - mileage, condition
  - dhl_date (last-mile courier expected arrival)
  - stock_status: enum('available', 'sold', 'reserved')
```

### 2.3 AuctionCar Table
```
auction_cars:
  - id (PK)
  - make_id, model_id (FK → makes, models)
  - chassis_no, vin (identification)
  - buy_date (purchase date from auction house)
  - ship_ok_date (when approved for shipment)
  - departure_date (when vessel left origin port)
  - arrival_date (when vessel reached destination port)
  - from_port, arrival_port (route endpoints)
  - vessel (shipping line/vessel name, optional)
  - status (internal auction lifecycle)
  - documents (B/L file references, path-based)
```

### 2.4 VehicleReservation Table
```
vehicle_reservations:
  - id (PK)
  - auction_id (FK → auction_cars)
  - seller_id (FK → users/seller)
  - user_id (FK → users/customer)
  - vehicle_id (FK → vehicles, nullable — vehicle may not exist yet)
  - reserved_at, expires_at (time window)
  - status: enum('active', 'expired', 'converted')
  - order_id (FK → orders, nullable — populated after checkout)
```

### 2.5 Shipment Table (if using separate shipment tracking)
```
shipments:
  - id (PK)
  - order_id (FK → orders)
  - status: enum('confirmed', 'shipped', 'in_transit', 'arrived', 'delivered')
  - origin_port, destination_port (route details)
  - tracking_number (carrier reference)
  - departure_date, arrival_date, local_dispatch_date
  - vessel (optional shipping line)
```

**Current Issue**: Shipment data is split across orders, vehicles, and auction_cars tables. This creates complexity when querying a cohesive shipment timeline.

---

## 3. SHIPMENT LIFECYCLE (ADMIN SIDE)

### 3.1 Stage Definitions

| Stage | Description | Trigger | Table Updates | Customer Visibility |
|-------|-------------|---------|----------------|---------------------| 
| **Reserved** | Vehicle held pending customer checkout | VehicleReservation created | vr.status = 'active' | "Reserved Units" tab |
| **Confirmed** | Order placed; payment initiated | Checkout → Order created, Payment record added | orders.status = 'payment_pending' or 'paid' | "Shipment Updates" or status-based tab |
| **Waiting for Departure** | Payment cleared; shipment being prepared | Order fully paid; no departure_date set | orders.status = 'shipment_pending' | "Waiting for Departure" tab |
| **Shipped** | Vehicle has departed origin port | Admin updates departure_date | auction_cars.departure_date, orders.departure_date, orders.status = 'shipped' | "Shipped Units with Balance" tab (if balance > 0) |
| **In Transit** | Vehicle in transit to destination | Automated or admin update (optional stage) | shipment.status = 'in_transit' or inferred from dates | Dashboard "In Transit" data |
| **Arrived** | Vehicle reached destination port | Admin updates arrival_date OR automated via carrier tracking | auction_cars.arrival_date, orders.arrival_date, orders.status = 'arrived' | Dashboard "Arrived" data |
| **Delivered** | Vehicle in last-mile delivery | Admin updates dhl_date OR reserves for customer pickup | vehicles.dhl_date, orders.status = 'delivered' | "Fully Completed Units" tab (with documents) |
| **Completed** | Final paperwork and handover done | Admin marks order complete | orders.status = 'completed' | "Fully Completed Units" tab |

### 3.2 Trigger Logic

```
Reserved → Confirmed:
  Trigger: Customer clicks "Proceed to Checkout" on reservation
  Action: Create Order; convert VehicleReservation.status='active' to 'converted'
  Fields Updated: orders.created, orders.status='payment_pending'

Confirmed → Waiting for Departure:
  Trigger: First payment >= min_deposit_percent OR full payment received
  Action: Admin or system marks order ready for shipment
  Fields Updated: orders.status='shipment_pending' OR 'paid'

Waiting for Departure → Shipped:
  Trigger: Admin receives vessel schedule; updates departure date
  Action: Admin logs into order and enters departure_date
  Fields Updated: auction_cars.departure_date, orders.departure_date, orders.status='shipped'

Shipped → In Transit (optional):
  Trigger: Carrier confirms ship in motion (automated or manual)
  Fields Updated: shipments.status='in_transit' (if separate shipment table used)

In Transit/Shipped → Arrived:
  Trigger: Vessel reaches destination; admin updates or receives notification
  Action: Admin updates arrival_date or system auto-updates from carrier data
  Fields Updated: auction_cars.arrival_date, orders.arrival_date, orders.status='arrived'

Arrived → Delivered:
  Trigger: Vehicle cleared at destination, ready for local delivery/pickup
  Action: Admin updates dhl_date (last-mile courier date) or marks as available for local pickup
  Fields Updated: vehicles.dhl_date, orders.status='delivered'

Delivered → Completed:
  Trigger: Final documents released; handover confirmed
  Action: Admin releases documents and marks order complete
  Fields Updated: orders.status='completed', vehicle_documents.can_download=true
```

---

## 4. REQUIRED FIELDS FOR SHIPMENT

### 4.1 Core Shipment Fields

| Field | Type | Location | Purpose | Populated By |
|-------|------|----------|---------|---------------| 
| `from_port` | string | auction_cars | Origin port name | Auction house data / Admin |
| `arrival_port` | string | auction_cars | Destination port name | Auction house data / Admin |
| `buy_date` | date | auction_cars | Date vehicle was purchased at auction | Auction house data |
| `ship_ok_date` | date | auction_cars | Date cleared for shipment | Admin |
| `departure_date` | date | auction_cars, orders | Date vessel left origin | Shipping line / Admin |
| `arrival_date` | date | auction_cars, orders | Date vessel reached destination | Shipping line / Admin |
| `dhl_date` | date | vehicles, orders | Expected last-mile courier arrival | Logistics provider / Admin |
| `vessel` | string | auction_cars | Vessel name or shipping line | Shipping line / Admin |
| `tracking_number` | string | orders, shipments | Carrier tracking code | Shipping line / Admin |
| `status` | enum | orders, shipments | Lifecycle stage | System / Admin |

### 4.2 Field Location Recommendations

**auction_cars table** (preferred for source data):
- `from_port`, `arrival_port`: Origin and destination routing
- `buy_date`: Auction purchase date
- `ship_ok_date`: Shipment approval date
- `departure_date`, `arrival_date`: Vessel transit dates
- `vessel`: Shipping line

**orders table** (denormalized for customer query speed):
- `departure_date`, `arrival_date` (copy from auction_cars for faster filtering)
- `dhl_date`: Last-mile courier date
- `status`: Order lifecycle stage
- `tracking_number`: Carrier tracking

**vehicles table** (physical asset attributes):
- `dhl_date`: Last-mile expected arrival (can also live in orders)
- `stock_status`: Inventory state (available, sold, reserved)

**shipments table** (if normalized):
- Dedicated table tracking each shipment leg
- Reduces denormalization; used when order has multiple shipments (rare in this system)

---

## 5. ADMIN ACTIONS

### 5.1 Update Shipment Dates

**Use Case**: Shipping line confirms departure/arrival; admin updates system.

**Workflow**:
1. Admin navigates to Order → Shipment details
2. Admin enters date (departure_date, arrival_date, or dhl_date)
3. System updates auction_cars and orders tables
4. Customer dashboard updates to reflect new stage
5. If date triggers stage transition, order status updates automatically

**Implementation Hooks**:
```
PUT /admin/orders/{order}/shipment
Request:
  - departure_date (optional)
  - arrival_date (optional)
  - dhl_date (optional)
  - vessel (optional)
  - tracking_number (optional)

Action:
  - Validate dates (departure <= arrival <= dhl_date)
  - Update auction_cars and orders tables
  - Trigger status transitions if applicable
  - Log change (admin audit trail)
  - Notify customer (optional: email update)
```

### 5.2 Upload B/L Documents

**Use Case**: Admin receives Bill of Lading file; stores for customer download after delivery.

**Workflow**:
1. Admin navigates to Order → Documents
2. Admin selects B/L file (PDF)
3. System stores file in storage/vehicle_documents/
4. Creates VehicleDocument record linked to vehicle
5. Document becomes available for download only after order reaches 'delivered' stage

**Implementation Hooks**:
```
POST /admin/orders/{order}/documents
Request:
  - file (multipart/form-data)
  - document_type (e.g., 'bill_of_lading', 'certificate_of_origin')
  - document_name (human-readable label)

Action:
  - Store file
  - Create VehicleDocument record
  - Log upload (admin audit trail)
  - Mark document visibility based on order status
```

### 5.3 Mark Shipment Stages

**Use Case**: Admin manually advances order through stages if automation is not available.

**Workflow**:
1. Admin navigates to Order status selector
2. Admin selects new stage from dropdown
3. System validates stage transition (no backward moves; reserved → confirmed → paid → shipped → arrived → delivered → completed)
4. System updates order.status and related fields
5. Customer receives status update notification

**Implementation Hooks**:
```
PUT /admin/orders/{order}/status
Request:
  - status: enum('reserved', 'payment_pending', 'paid', 'shipment_pending', 'shipped', 'arrived', 'delivered', 'completed')
  - reason (optional, for audit trail)

Validation:
  - Ensure transition is forward-only
  - Ensure all required fields are set before advancing (e.g., departure_date before marking 'shipped')

Action:
  - Update orders.status
  - Update orders.customer_status (if different)
  - Trigger notifications
  - Log state change
```

### 5.4 Attach Vehicle After Reservation

**Use Case**: Vehicle created after order placed; need to link to order and update auction data.

**Workflow**:
1. Admin navigates to Order → Unlinked/Edit
2. Admin selects or creates Vehicle record
3. System creates OrderItem linking order to vehicle
4. System syncs auction_car data if vehicle references auction_car
5. Dashboard updates with vehicle details

**Implementation Hooks**:
```
POST /admin/orders/{order}/vehicles
Request:
  - vehicle_id (existing)
  OR
  - vehicle_data (create new)

Action:
  - Create OrderItem (order_id, vehicle_id)
  - Sync auction_car data to orders table (departure_date, arrival_date, ports)
  - Update order totals if needed
```

---

## 6. STATUS VS UI MAPPING

Customer Dashboard tabs are populated based on order.status and derived fields:

```
Tab: "Download Doc"
  Visibility: orders.status = 'delivered' OR 'completed'
  Rows: vehicles with documents.count > 0
  Actions: Download document links (only if can_download = true)

Tab: "All Cars"
  Visibility: Always visible
  Rows: All orders mapped to vehicles
  Columns: All shipment data (ports, dates, dhl_date, balance)

Tab: "Fully Completed Units"
  Visibility: Always visible
  Rows: orders where status in ('delivered', 'completed')
  Columns: All shipment data + document availability

Tab: "Shipped Units with Balance"
  Visibility: Always visible
  Rows: orders where status in ('shipped', 'in_transit', 'arrived', 'delivered', 'completed') AND remaining_balance > 0
  Columns: Shipment data + outstanding balance
  Actions: "Pay balance" link (if remaining_balance > 0)

Tab: "Waiting for Departure"
  Visibility: Always visible
  Rows: orders where status in ('paid', 'shipment_pending') AND departure_date IS NULL
  Columns: Shipment prep data (buy_date, no departure_date yet)
  Message: "Waiting on vessel schedule"

Tab: "Further Deposit Required"
  Visibility: Always visible
  Rows: orders where remaining_balance > (total_price * (100 - min_deposit_percent) / 100)
  Columns: Balance due + minimum deposit requirement
  Actions: "Pay balance" link

Tab: "Reserved Units"
  Visibility: Always visible
  Rows: VehicleReservation records where user_id = customer AND status = 'active' AND expires_at > NOW()
  Columns: Vehicle name, reservation expiry, seller name
  Actions: "Proceed to Checkout" link (if within reservation window)
```

---

## 7. EDGE CASES

### 7.1 Order Without Vehicle Yet

**Scenario**: Customer reserves vehicle; checks out; but Vehicle record hasn't been created by seller yet.

**Current Handling**:
- Order exists with status = 'reserved' or 'payment_pending'
- OrderItem.vehicle_id = NULL or references a placeholder
- Vehicle data shown as "Vehicle pending" in dashboard
- Shipment dates (departure_date, etc.) are NULL

**Admin Action**:
- Admin creates Vehicle record → links to Order via OrderItem
- Auction data syncs from AuctionCar to Orders table
- Dashboard updates with real vehicle details

### 7.2 Vehicle Created After Order

**Scenario**: Seller lists new vehicle in inventory after customer has already placed an order for it (pre-auction scenario).

**Current Handling**:
- Vehicle record is created independently
- Admin must manually link to Order → creates OrderItem
- Auction data from AuctionCar syncs to Orders table upon link

**Potential Improvement**: Use auction_car_id as the key; once Vehicle is created with that auction_car_id, auto-link to any pending orders.

### 7.3 Missing Shipment Data

**Scenario**: Departure date not yet known; order is in 'shipment_pending' stage.

**Current Handling**:
- Dashboard shows NULL for departure_date
- Row appears in "Waiting for Departure" tab
- Admin updates date once known

**UI Safeguard**: Dashboard displays "Pending" or "—" for missing dates; doesn't break table rendering.

### 7.4 Partial Payments

**Scenario**: Customer pays $5,000 of $10,000 total; status is 'payment_pending' but some amount has been received.

**Current Handling**:
- orders.paid_amount = 5,000
- orders.remaining_balance = 5,000
- orders.status could be 'payment_pending' or 'paid' depending on whether min_deposit_amount is met
- Dashboard shows balance due; "Further Deposit Required" tab lists this order
- Admin can mark order as 'shipment_pending' only after min_deposit_percent threshold is met

**UI Logic**:
```
if (remaining_balance > 0 && remaining_balance < total_price * min_deposit_percent / 100) {
  tab: "Further Deposit Required"
} else if (remaining_balance > 0 && order.status in ('shipped', 'arrived', ...)) {
  tab: "Shipped Units with Balance"
}
```

### 7.5 Duplicate Data Across Tables

**Scenario**: departure_date exists in both auction_cars and orders tables; may diverge if updates are inconsistent.

**Current Handling**:
- Denormalization for query speed (orders table indexed for fast dashboard queries)
- Risk of staleness if auction_cars is updated without updating orders

**Mitigation**:
- Always update both tables in single transaction
- Add database trigger or application-level hook to sync dates
- Consider audit trail to detect divergence

---

## 8. SUGGESTED IMPROVEMENTS

### 8.1 Normalized Shipment Status System

**Current Problem**: Shipment status mixed into orders.status; makes querying complex.

**Suggestion**:
```sql
CREATE TABLE shipments (
  id INT PRIMARY KEY,
  order_id INT FK,
  status ENUM('confirmed', 'shipped', 'in_transit', 'arrived', 'delivered', 'local_pickup'),
  from_port VARCHAR(255),
  arrival_port VARCHAR(255),
  departure_date DATE,
  arrival_date DATE,
  dhl_date DATE,
  vessel VARCHAR(255),
  tracking_number VARCHAR(255),
  created_at, updated_at
);
```

**Benefit**:
- Separate shipment lifecycle from order payment lifecycle
- One order can have multiple shipments (future: multi-leg routes)
- Cleaner queries: `Shipment::where('status', 'shipped')->get()`
- Audit trail: shipment history stays intact

**Migration Path**:
- Create shipments table
- Copy data from orders table
- Keep orders.status for backward compatibility (payment lifecycle)
- Update dashboard queries to use shipments table
- Deprecate auction_cars denormalization over time

### 8.2 Avoid Duplicate Data Across Tables

**Current Problem**: from_port, arrival_port, departure_date, arrival_date in both auction_cars and orders tables.

**Suggestion**:
- Keep auction_cars as source of truth
- Orders table references auction_cars_id instead of copying fields
- Dashboard queries JOIN to auction_cars for shipment details
- Reduces sync risk; single source of truth

**Trade-off**: Slightly slower queries (JOIN); cleaner data model (no duplication).

### 8.3 Introduce Shipment Events

**Suggestion**:
```php
Class ShipmentStatusChanged implements ShouldQueue {
  public $shipment;
  
  public function handle() {
    // Update order.status based on shipment.status
    // Send customer notification
    // Update dashboard cache
  }
}
```

**Benefit**:
- Decoupled status updates
- Easier to add new side effects (webhooks, analytics, etc.)
- Audit trail of all status changes

### 8.4 Introduce Shipment Timeline View

**Suggestion**: Admin dashboard shows visual timeline of shipment milestones:
```
Reserved ──→ Confirmed ──→ Paid ──→ Shipment Pending ──→ Shipped (date) ──→ In Transit ──→ Arrived (date) ──→ Delivered (date) ──→ Completed
```

**Benefit**:
- Clear visual feedback of progress
- Easy to identify bottlenecks (e.g., orders stuck in "Shipment Pending" for 30+ days)
- Admin can click on each milestone to update or view details

### 8.5 Bulk Update Shipments

**Suggestion**: Admin can select multiple orders and update shipment dates in batch.

```php
PUT /admin/shipments/bulk-update
Request:
  - order_ids: [1, 2, 3, 4, 5]
  - departure_date: 2025-03-15
  - vessel: "MV Example Ship"
```

**Benefit**:
- Faster batch updates for vessel manifests
- Reduce data entry errors
- Audit trail records bulk action

---

## 9. OPTIONAL PSEUDO IMPLEMENTATION

### 9.1 Admin Update Shipment Dates Flow

```php
// app/Http/Controllers/Admin/ShipmentController.php

public function updateShipmentDates(Request $request, Order $order)
{
  $validated = $request->validate([
    'departure_date' => 'nullable|date|after:buy_date',
    'arrival_date' => 'nullable|date|after:departure_date',
    'dhl_date' => 'nullable|date|after:arrival_date',
    'vessel' => 'nullable|string|max:255',
    'tracking_number' => 'nullable|string|max:255',
  ]);

  // Ensure order belongs to logged-in admin's jurisdiction (if multi-admin)
  $this->authorize('update', $order);

  // Update AuctionCar (source of truth)
  if ($order->vehicle?->auctionCar) {
    $order->vehicle->auctionCar->update([
      'departure_date' => $validated['departure_date'],
      'arrival_date' => $validated['arrival_date'],
      'vessel' => $validated['vessel'],
    ]);
  }

  // Denormalize to Orders for query speed
  $order->update([
    'departure_date' => $validated['departure_date'],
    'arrival_date' => $validated['arrival_date'],
    'dhl_date' => $validated['dhl_date'],
    'tracking_number' => $validated['tracking_number'],
  ]);

  // Auto-advance status if dates trigger transitions
  $this->autoAdvanceOrderStatus($order);

  // Dispatch shipment event for side effects (notifications, etc.)
  event(new ShipmentDatesUpdated($order));

  // Audit log
  activity()
    ->causedBy(auth()->user())
    ->performedOn($order)
    ->withProperties($validated)
    ->log('shipment_dates_updated');

  return redirect()->back()->with('success', 'Shipment dates updated.');
}

private function autoAdvanceOrderStatus(Order $order)
{
  $current_status = $order->status;

  if ($current_status === 'shipment_pending' && $order->departure_date) {
    $order->update(['status' => 'shipped']);
  } elseif ($current_status === 'shipped' && $order->arrival_date) {
    $order->update(['status' => 'arrived']);
  } elseif ($current_status === 'arrived' && $order->dhl_date) {
    $order->update(['status' => 'delivered']);
  }
}
```

### 9.2 Auto-Compute Dashboard Tab Eligibility

```php
// app/Models/Order.php

public function scopeWaitingForDeparture(Builder $query)
{
  return $query
    ->whereIn('status', ['paid', 'shipment_pending'])
    ->whereNull('departure_date');
}

public function scopeShippedWithBalance(Builder $query)
{
  return $query
    ->whereIn('status', ['shipped', 'in_transit', 'arrived', 'delivered', 'completed'])
    ->whereRaw('remaining_balance > 0');
}

public function scopeFurtherDepositRequired(Builder $query)
{
  return $query->whereRaw(
    'remaining_balance > (total_price * ((100 - min_deposit_percent) / 100))'
  );
}

// Usage in controller:
$waiting_for_departure = $customer->orders()->waitingForDeparture()->get();
$shipped_with_balance = $customer->orders()->shippedWithBalance()->get();
```

### 9.3 Customer Notification on Status Change

```php
// app/Events/ShipmentStatusChanged.php

class ShipmentStatusChanged implements ShouldQueue
{
  public function __construct(public Order $order, public string $old_status, public string $new_status) {}

  public function handle()
  {
    // Send email notification
    Mail::queue(new ShipmentStatusNotification($this->order, $this->new_status));

    // Optional: send SMS for critical transitions (shipped, arrived)
    if (in_array($this->new_status, ['shipped', 'arrived'])) {
      Notification::send(
        $this->order->customer,
        new ShipmentStatusSms($this->order, $this->new_status)
      );
    }
  }
}
```

---

## 10. CONCLUSION

Shipment handling in this system is currently split across three tables (orders, vehicles, auction_cars) with some denormalization for query speed. The admin workflow involves:

1. **Creating Orders** (from reservations)
2. **Confirming Payment** (triggering shipment readiness)
3. **Updating Shipment Dates** (departure, arrival, last-mile)
4. **Uploading Documents** (B/L, certificates)
5. **Marking Status Transitions** (automatic or manual)
6. **Linking Vehicles** (if created after order)

The customer dashboard reflects these updates via 7 standardized tabs, each querying orders based on status, balance, and document availability.

**Next Steps for Implementation**:
- Create normalized Shipment table
- Implement bulk update endpoint
- Add shipment timeline UI for admins
- Introduce event-driven status transitions
- Consolidate duplicate fields (single source of truth)
- Add audit trail for all shipment changes

---

**Document Version**: 1.0  
**Last Updated**: 2025  
**Author**: Design Review  
**Status**: Design Phase (not yet implemented)