# UPN Rate Card Import Simulation

This simulation documents the proposed conversion of `src/data/AHS 2026.pdf` into the Shippingcosts import shape. The PDF is a reference document, so it must first be transcribed or converted to XLSX. The importer should then preserve both service and transit-time information while using the production-proven delimited-postcode pattern.

---

## Stage 1: Converted XLSX Input

The converted sheet should use one row for each zone/service combination:

| Provider | Zone | Service | TransitTime | PostCode | Micro | Quarter | Qtr+ | Half | Euro | Full |
|---|---:|---|---|---|---:|---:|---:|---:|---:|---:|
| UPN | 1 | Next Day | 1-2 days | TN17, TN28-38 | 22.00 | 24.00 | 25.00 | 27.00 | 29.00 | 32.00 |
| UPN | 1 | Eco | 1-2 days | TN17, TN28-38 | 22.00 | 22.00 | 23.00 | 25.00 | 27.00 | 30.00 |
| UPN | 2 | Next Day | 1-2 days | B, BB, BD, BL, CV, CW, DE, DN, DY, HD, HG, HP, HX, L, LE, LS, LU, M, MK, NG, NN, OL, PR, S, SK | 34.22 | 38.34 | 39.41 | 41.04 | 44.48 | 47.89 |
| UPN | 2 | Eco | 1-2 days | B, BB, BD, BL, CV, CW, DE, DN, DY, HD, HG, HP, HX, L, LE, LS, LU, M, MK, NG, NN, OL, PR, S, SK | 35.58 | 36.68 | 38.34 | 39.70 | 41.04 | 41.04 |
| UPN | 7 | Next Day | 1-2 days | SW | 62.27 | 65.06 | 65.59 | 66.43 | 67.81 | 69.20 |
| UPN | 7 | Eco | 1-2 days | SW | 58.25 | 58.56 | 59.01 | 59.74 | 60.49 | 60.49 |

The values above are taken from the visible rows in the 2026 sample PDF; the full conversion must preserve every zone row.

---

## Stage 2: Header Detection and Mapping

The importer maps:

- `Provider` → provider/haulier lookup
- `Zone` → zone metadata
- `Service` → service/type
- `TransitTime` → service metadata or service label suffix
- `PostCode` → postcode group
- `Micro`, `Quarter`, `Qtr+`, `Half`, `Euro`, `Full` → price tiers

If the Shippingcosts schema has only one service field, use a deterministic label such as `Priority / Next Day - 1-2 days` and `Standard / Economy - 1-2 days`. Do not discard transit-time information because it affects delivery-service selection.

---

## Stage 3: Postcode Expansion and Consolidation

Postcode prefixes and ranges are expanded for validation, then rejoined into the database representation:

```php
$expanded = PostcodeExpansionService::expandPostcodes('TN17, TN28-38');

// Example result:
// TN17, TN28, TN29, ..., TN38

$record = [
    'zone' => '1',
    'service' => 'Next Day',
    'transit_time' => '1-2 days',
    'postcodes' => implode('||', $expanded),
    'prices' => [
        ['label' => 'Micro', 'size' => null, 'qty' => 1, 'price' => 22.00],
        ['label' => 'Quarter', 'size' => 0.25, 'qty' => 1, 'price' => 24.00],
        ['label' => 'Qtr+', 'size' => null, 'qty' => 1, 'price' => 25.00],
        ['label' => 'Half', 'size' => 0.5, 'qty' => 1, 'price' => 27.00],
        ['label' => 'Euro', 'size' => 1, 'qty' => 1, 'price' => 29.00],
        ['label' => 'Full', 'size' => 1, 'qty' => 1, 'price' => 32.00],
    ],
];
```

The same postcode group normally produces separate records for Priority and Standard because their services and prices differ. If adjacent source rows have identical service, transit time, and prices, they may be consolidated into one delimited postcode group.

---

## Stage 4: Preview

```text
UPN Rate Import - Preview

Source rows:                  6
Expanded postcodes:           [calculated]
Consolidated records:          6

| Zone | Service | Transit | Postcodes | Price Count | Action |
|------|---------|---------|-----------|-------------|--------|
| 1 | Priority / Next Day | 1-2 days | TN17||TN28||...||TN38 | 6 | create |
| 1 | Standard / Economy | 1-2 days | TN17||TN28||...||TN38 | 6 | create |
| 2 | Priority / Next Day | 1-2 days | B||BB||BD||...||SK | 6 | create |
| 2 | Standard / Economy | 1-2 days | B||BB||BD||...||SK | 6 | create |
| 7 | Priority / Next Day | 1-2 days | SW | 6 | create |
| 7 | Standard / Economy | 1-2 days | SW | 6 | create |
```

The preview should show the full postcode group in a readable truncated form, with the expanded count available as a statistic. It should not create one persisted record per postcode.

---

## Stage 5: Shippingcosts Upsert

```php
$shippingcost = new Shippingcosts();
$shippingcost->haulierid = $upnHaulierId;
$shippingcost->type = 'Next Day';
$shippingcost->postcodes = 'TN17||TN28||TN29||...||TN38';
$shippingcost->prices = json_encode($prices, JSON_THROW_ON_ERROR);
$shippingcost->no_of_prices = count($prices);
$shippingcost->status = 'active';
$shippingcost->save();
```

The upsert key must distinguish the provider, service/transit-time combination, and postcode group. Re-importing the same rate card must update the existing group rather than duplicate it.

---

## Stage 6: Expected Database Shape

| type | postcodes | prices | no_of_prices | status |
|---|---|---|---:|---|
| Priority / Next Day - 1-2 days | `TN17||TN28||...||TN38` | JSON price array | 6 | active |
| Standard / Economy - 1-2 days | `TN17||TN28||...||TN38` | JSON price array | 6 | active |
| Priority / Next Day - 1-2 days | `SW` | JSON price array | 6 | active |
| Standard / Economy - 1-2 days | `SW` | JSON price array | 6 | active |

This matches the validated GForce SQL pattern: one record stores the complete postcode group and its price array.

---

## Stage 7: Result Summary

```text
UPN Rate Import - Result

Source rows processed:       6
Records created/updated:     6
Errors:                      0

Total postcodes expanded:    [calculated]
```

## Implementation Notes

- PDF extraction/transcription must preserve zone, service, transit time, postcode text, and all six price bands.
- Prefixes such as `B`, `BB`, and `SW` are valid postcode groups; do not force them into numeric ranges.
- Preserve route-specific notes and exceptions in the preview and conversion audit.
- Reuse the shared postcode expansion and price-import services where their field model supports the UPN price bands.
