# GForce Ratecard Import

This document describes how the GForce ratecard importer works in `ngauge-delivery-gforce` and which parts are reusable for other delivery providers.

## Overview

The import flow is spreadsheet-driven. A user uploads a GForce rate sheet, reviews a preview, then confirms the import.

The importer reads the sheet directly with PhpSpreadsheet, detects the header row automatically, expands postcode ranges into individual postcode records, and then upserts each record into `Shippingcosts`.

The implementation is split across three layers:

1. `RateImportController` handles upload, preview, confirmation, and temporary file cleanup.
2. `RateSheetImporter` parses the spreadsheet and performs preview/import logic.
3. Shared helpers from `ngauge-shippingcosts` handle price header parsing and postcode expansion.

Reference files that ship with provider modules may be spreadsheets, PDFs, or text docs. In this module, the importer still expects a spreadsheet input; PDF files are reference material unless they are converted into a tabular format first.

## User Flow

1. The user opens `/gforce/rate-import/index`.
2. The upload form accepts a single `.xlsx` or `.xls` file.
3. On upload, the file is saved to a temporary location and passed to the preview parser.
4. A preview screen shows counts and sample rows that will be created or updated.
5. The preview is stored in session with a random token.
6. When the user clicks **Confirm & Import**, the controller reuses the temporary file and runs the real import.
7. The temporary file is deleted when the process finishes, or when stale preview entries are cleaned up.

## Files Involved

- [src/controllers/RateImportController.php](src/controllers/RateImportController.php)
- [src/components/RateSheetImporter.php](src/components/RateSheetImporter.php)
- [src/models/RateUploadForm.php](src/models/RateUploadForm.php)
- [src/views/rate-import/upload.php](src/views/rate-import/upload.php)
- [src/views/rate-import/preview.php](src/views/rate-import/preview.php)
- [src/views/rate-import/result.php](src/views/rate-import/result.php)

Shared dependencies:

- [ngauge-shippingcosts/src/models/ColumnMapForm.php](../ngauge-shippingcosts/src/models/ColumnMapForm.php)
- [ngauge-shippingcosts/src/services/PostcodeExpansionService.php](../ngauge-shippingcosts/src/services/PostcodeExpansionService.php)
- [ngauge-shippingcosts/src/models/Shippingcosts.php](../ngauge-shippingcosts/src/models/Shippingcosts.php)

## Upload Rules

The upload form only accepts spreadsheet files:

- `.xlsx`
- `.xls`

The file size limit is 20 MB.

The controller saves the upload to a temporary file under the system temp directory before previewing or importing it.

If a provider module only has a PDF rate document, that document is not directly importable by this flow. It needs to be converted or transcribed into a spreadsheet that follows the same header contract.

## Spreadsheet Contract

The importer detects columns by header text in the first row.

Recognised role columns:

- `Provider`, `Haulier`, `Carrier` -> used to resolve `Accounts.intid`
- `Service`, `Type`, `Service Type` -> stored as the `type` field
- `PostCode`, `Post Code`, `Post_Code`, `Postal Code` -> the postcode field to expand and import

Any other columns that look like rate bands are treated as price columns.

The importer expects the sheet to contain at least a postcode column. If it cannot find one, it throws a runtime error and stops.

## Price Column Detection

Price columns are detected heuristically rather than by an explicit mapping UI.

The importer treats a header as a price column when one of these is true:

- the header contains a known size keyword such as `Micro`, `Mini`, `Euro`, `Qtr`, `Quarter`, `Half`, or `Full`
- the header contains a quantity pattern such as `1 X`, `2x`, `3 X`

For every price column, the header is parsed through `ColumnMapForm::parsePriceHeader()` to derive:

- `label` - the original header text
- `size` - the size multiplier, if a known size keyword is present
- `qty` - the quantity number if present in the header

The cell value becomes the imported price.

Cells containing `N/A`, blank, or `null` are ignored.

## Postcode Expansion

The importer does not only store the literal postcode string from the sheet. It expands postcode values using `PostcodeExpansionService::expandPostcodes()` first.

Supported inputs include:

- individual postcodes such as `AB12`
- comma-separated lists such as `AB12, CD34`
- ranges such as `AB1-5`
- short prefixes that can be resolved from the shared postcode lookup data

If expansion returns nothing, the importer falls back to the original postcode string.

Each expanded postcode becomes its own `Shippingcosts` record.

## Import Behaviour

The importer processes each source row as follows:

1. Read the postcode value.
2. Skip the row if postcode is empty or `N/A`.
3. Resolve the provider name to a haulier ID using `backend\models\Accounts`.
4. Read the service value.
5. Parse all price-band columns into an array of price entries.
6. Expand the postcode into one or more individual postcodes.
7. For each expanded postcode, upsert one `Shippingcosts` record.

The importer tracks these counters during preview and import:

- `sourceRows` - rows with a usable postcode
- `expandedRows` - postcode records after expansion
- `toCreate` - records that do not currently exist
- `toUpdate` - records that already exist
- `saved` - newly created records during import
- `updated` - matched records updated during import
- `skipped` - rows ignored or failed during import

## Upsert Rules

The importer looks for an existing `Shippingcosts` row using these fields:

- `type`
- `haulierid`
- `postcodes`

It checks in this order:

1. exact postcode match
2. a partial match inside a `||`-separated postcode list

If a match exists, the record is updated in place.
If no match exists, a new record is created with status set to `active`.

The importer stores:

- `haulierid`
- `type`
- `postcodes`
- `prices`
- `no_of_prices`

## Preview And Confirmation

The preview is not just a visual summary. It is a real parse of the uploaded file without persistence.

The preview screen shows:

- source row count
- expanded postcode record count
- create/update counts
- a sample of rows that will be written

The controller keeps the temporary file path in session under a random token. When the user confirms the import, the same file is reused so the preview and import are guaranteed to refer to the same upload.

Preview entries expire after 30 minutes. Expired files are cleaned up when a new preview is stored or consumed.

## Error Handling

The importer is intentionally tolerant of row-level failures.

If a row cannot be saved after the retry loop, the importer collects:

- spreadsheet row number
- postcode
- model validation or exception details

These failures are shown in the result screen after import.

If the entire file cannot be loaded, or if the postcode column is missing, the import aborts with a runtime error.

## What Makes This Reusable For Other Providers

The GForce importer is a good template for other delivery providers because the workflow is already provider-agnostic in a few places:

- upload, preview, confirm, and cleanup are generic controller concerns
- price parsing is driven by header heuristics and shared parsing helpers
- postcode expansion is already centralised in `ngauge-shippingcosts`
- `Shippingcosts` upsert logic is generic enough to reuse if the provider exports the same row shape

For a new provider, the main work is usually:

1. adjusting the accepted column headers
2. adjusting the way service names are normalised
3. handling provider-specific postcode formats
4. deciding whether one sheet row becomes one `Shippingcosts` record or many
5. deciding whether create/update matching should be exact or fuzzy

## Reuse Checklist For Another Provider

If you want the same import style in another delivery module, copy this pattern:

- a dedicated `RateImportController`
- a simple upload form model
- a parser/importer component that can run in preview mode and persist mode
- a preview screen that shows create/update counts before confirmation
- a tokenised temporary-file handoff between preview and import

Then swap only the provider-specific parts:

- header aliases
- price-band parsing rules
- service code normalisation
- postcode expansion rules
- record matching rules for updates

## Notes For Future Modules

The current GForce importer does not try to consolidate or summarise price bands across postcode groups. It writes one shipping-cost record per expanded postcode.

That means other providers can reuse the same mechanics if their spreadsheet layout is similar, but if their export groups postcodes differently you may need a provider-specific parser while keeping the same preview/confirm pipeline.

When reviewing the sample files in other provider modules, treat PDFs as source documentation for the rate structure, not as direct upload inputs. The reusable target format is still a spreadsheet with a detected header row and price-band columns.