# Pricing Enquiry to Quotation
Answer a mail asking "what does this cost?" without opening a spreadsheet.
The whole feature, once finished:
```
A mail arrives
↓
Classification prompt (the one Gemini call that already returns
sentiment, category, next action and lead)
↓
Category = PRICING_ENQUIRY already shipped — email_categories
↓
Extract product + quantity PHASE 3
↓
Product Master search PHASE 1 ← this document
↓
Price applicable on the date PHASE 1 ← this document
↓
Missing information? PHASE 2 (Question Master)
↓
Quotation + Terms & Conditions PHASE 4
↓
Quotation PDF PHASE 5
↓
Email draft, PDF attached PHASE 5
↓
User reviews, edits, sends PHASE 5 (never sent automatically)
```
**Built: the Product Master and its price history (phase 1), and the whole
path from a pricing enquiry to a reply with the quotation attached as a PDF
(phases 3, 4 and 5).** What is not built is the Question Master (phase 2) and
the discount approval workflow (phase 6) — the last section says what each
needs.
## From an enquiry to a drafted reply
Open a pricing enquiry, press **Reply**, press **Prepare quotation**:
```
The mail "Please share pricing for 12 ABC controllers
with installation, one mounting set, and two
industrial widget presses."
↓
EnquiryExtractor one Gemini call, given the catalogue, returns
products + quantities + requirements as JSON
↓
ProductMatcher verifies every code the model returned against
the real catalogue, and scores the match
↓
Product::priceOn(date, qty) 12 units crosses into the 10-and-up band, so
₹9,000, not the headline ₹10,000
↓
QuotationBuilder quotations + quotation_items, every figure
snapshotted, terms assembled
↓
QuotationPdf renders the customer's copy with dompdf
↓
QuotationDraftWriter a covering mail that points at the attachment
↓
The CKEditor box the user reads it, edits it, presses Send Reply
↓
The send path attaches Quotation_QT-2026-0001.pdf
```
Nothing is sent automatically. The draft lands in the editor and the user's own
Send Reply button is still the only thing that puts a mail on the wire — the
same rule the AI reply draft follows, and the spec's default.
The widget presses are not in the catalogue, so that line is kept with the
customer's own words, priced at nothing, and flagged. The draft says *"we are
checking on the following and will come back to you separately"* rather than
quietly dropping what was asked for.
### What the user sees when a line is doubtful
```
QT-2026-0001 — 3 lines, total ₹156,940.00 open / print
Check these before sending:
• No product in the catalogue matches "two industrial widget presses".
```
Three things earn that flag, and they are the three ways an automated quotation
goes wrong: nothing matched, something matched but has no price on the day, or
the match itself is a guess (below 70% — see `QuotationItem::CONFIDENCE_FLOOR`,
which is set at 70 rather than 50 because the two mistakes do not cost the
same: a line needlessly flagged costs a glance, a confidently wrong product
goes to a customer as a commitment).
### Confidence is corroborated, not taken on trust
The number on a line is not simply the model's own. `ProductMatcher` adjusts it
by how much independent evidence agrees:
| Situation | Confidence |
| --- | --- |
| Code returned **and** the sender's words name that product | at least 95 |
| Code returned, nothing else corroborates it | the model's own, or 90 |
| No code, but this app finds a name or alias in the phrase | 60 — the model and the text **disagree**, so a human decides |
| Nothing matches | 0, no product, line flagged |
A code the model invents, or one belonging to a product retired since, is
caught here too: every code is looked up for real before it is used.
### Why extraction is not in the classification prompt
It would be the obvious place and it is the wrong one. That prompt runs over
every mail in the mailbox and only a fraction are pricing enquiries, so
extracting products from all of them is paid for on every message and used on
few. It is also assembled inline in five separate blocks across
`ProcessEmailQueue` and `MailController`, so a field added there becomes five
copies that drift. Extraction happens when somebody actually asks for a
quotation instead.
### Requirements are acknowledged, not turned into terms
A pricing enquiry is rarely only about price. The extractor returns everything
the sender wants that is not a product line, and on the first real enquiry this
ran against that was five *questions* — key features, integration
capabilities, implementation timeline, demo availability. They were being
appended to the quotation's Terms & Conditions, where they read as though the
customer were agreeing to them.
They now go in the covering mail instead:
```
You also asked about the following, which we will cover in our reply:
• Key features and functionalities
• Integration capabilities with our existing systems
• Demo or trial availability
```
Acknowledged, not answered. Nothing in this module knows what the integration
capabilities are, and inventing them is exactly the failure the quotation
figures are built to avoid. They stay on the quotation in `extraction_json`
either way.
### A quotation is built once
Re-opening the reply screen re-renders the draft from the stored quotation —
no second AI call, no second quotation number. **Start again** cancels the
existing one and issues a new number; it is cancelled rather than deleted,
because the old number may already have been quoted to the customer.
When the reply is finally sent, the quotation is marked `sent`. That is hooked
into `MailController::clearReplyDraft()` — the one place the Gmail and Outlook
send paths already agree on, so neither can send a quotation and leave it
sitting in the list as a draft for ever.
The rule the module is built around: **a quotation is priced at the date it was
raised.** If today's price is ₹10,000 and next month's is ₹12,000, a quotation
raised today has to go on showing ₹10,000 forever — when it is reprinted, when
it is queried, when it is disputed. Everything below follows from that one
sentence.
## What the user sees
**Products** in the Configuration menu. The catalogue, one row per product:
| Product | Code | Price today | Tax | Unit | Active |
| --- | --- | --- | --- | --- | --- |
| ABC Product | P001 | 10,000.00 INR
3 price bands | 18% | Nos | ● |
| XYZ Product | P002 | 25,000.00 INR | 18% | Set | ● |
| Legacy Controller | P003 | **No price today** | 18% | Nos | ● |
*Price today* is what would be quoted right now at quantity 1. **No price
today** is a real state, not a zero: every price on that product has expired,
so nothing can be quoted from it until someone adds one. A quotation engine
that read `0` there would send a customer a free product.
The clock button on each row opens that product's **price book** — every band
and every window, current, future, expired and withdrawn:
```
Price book — P001
Price Quantity Applies Source State
10,000.00 INR 1-9 From 01 Aug 2026 Entered Quoted today
9,000.00 INR 10 and up From 01 Aug 2026 Import #14 In force
11,500.00 INR 1-9 From 01 Oct 2026 Entered Future
8,000.00 INR 1-9 01 Jan 2026 - 31 Jul 2026 Import #9 Expired
```
Nothing is ever removed from that list. A withdrawn price stays visible and
marked *Withdrawn*, because it is the evidence behind whatever quotations used
it.
## Never quote from `products.price`
`products.price` exists and is a **cache** — today's figure, denormalised so a
list screen can show a number without a subquery. The authority is
`product_pricing`, and the only sanctioned read is:
```php
$applicable = $product->priceOn($quotationDate, $quantity); // ?ProductPricing
if (!$applicable) {
// Cannot quote. Not zero, not the cached price — cannot quote.
}
```
`priceOn()` returns `null` for an unpriced product, an expired price list, or a
quantity below every band's minimum. Phase 4 turns that null into the spec's
"product found but not priceable → create a task for the sales team" branch.
`syncPriceCache()` re-points the cache after any pricing change, and is allowed
to write `null`: a product whose prices have all expired shows no figure rather
than a stale one.
## Which price applies
`priceOn($date, $quantity)` filters, then ranks. It filters on:
- the validity window containing the date — a null bound is open-ended;
- the quantity sitting inside the band — a null maximum is unbounded;
- the row being active.
Then, of the survivors, it takes the **highest minimum quantity**, then the
**narrowest band**, then the **latest `valid_from`**, then the newest row.
That order was settled by a case that the obvious order gets wrong. A price
list of "1 and up at 10,000, 10 and up at 9,000" is two rows with no upper
bound, so they are *equally wide* — ranking by width first cannot separate
them, falls through to the older row, and quotes 10,000 for a bulk order of 20.
Ranking by the band's floor first picks 9,000, because a tier is identified by
where it starts, which is how a price list is both written and read. Width
still separates bands that share a floor: "1-9" beats an open "1 and up".
`tools/pricing-smoke.php` holds that case and five more like it. It is worth
running after any change to the lookup — it is how the bug above was found, and
also how a second one was: `Collection::sortBy([fn, fn, ...])` looks like a
list of key extractors and is not one. Laravel calls each callable as
`$callback($a, $b)` and treats the return value as the *comparison result*, so
single-argument closures produce a silently nonsensical order. The lookup uses
an explicit comparator over rank tuples for that reason.
## Adding a price never overwrites one
There is no "edit price" anywhere in the module — not on the screen, not in the
routes, not in the importer. `ProductPricing::supersede()` is the only way a
price is written, and it:
1. finds the active rows for the same product **and the same quantity band**
that are still open, or that run past the new start date;
2. closes each of them the day before the new price begins;
3. inserts the new row.
A row that would be left with a zero-length window — the same band re-priced
from the same date — is deactivated instead of being given a nonsensical
`valid_to`.
Different bands coexist untouched: re-pricing "1-9" leaves "10 and up" exactly
where it was. MySQL cannot express "no two windows for one product and band may
overlap", so this method *is* the constraint, and everything goes through it.
Editing the price on the product form is therefore not an edit either: it adds
a price from today and closes the current one. The form says so.
## Importing a price list
Upload an `.xlsx`, `.xls`, `.ods` or `.csv`, or paste a Google Drive / Sheets
link. Both routes run the same importer and write the same audit record.
**Column names are matched loosely.** "Product Code", "SKU", "Item Code" and
"Part No" all mean the same column; so do "Tax %", "GST" and "tax_percentage".
The header row is *searched for* in the first 15 rows rather than assumed to be
row 1, because exported price lists routinely carry a title and a blank line
above the real header. Only a product-code column is mandatory.
`₹10,000.00`, `10,000/-` and `10000` all parse. `18%` and `18` both mean 18%.
`31/12/2026` is read as a day-first date, not the American month-first reading
Carbon would otherwise take.
Four rules govern what an import does:
**Rows are matched on product code, never on name.** A name is prose and gets
retyped; the code is the identity.
**A repeated code is a quantity band, not a duplicate.** This is how a tiered
price list is written, and the sample workbook shows it:
| Product Code | Product Name | Price | Min Qty | Max Qty |
| --- | --- | --- | --- | --- |
| P001 | ABC Product | 10000 | 1 | 9 |
| P001 | | 9000 | 10 | |
Only the *first* row for a code may set the product's own fields; later rows
add prices and nothing else, so a blank cell on a band row cannot quietly wipe
the name or the tax rate. What must be unique is the code **and** its band —
two rows claiming the same product at the same band are a genuine mistake,
because only one of them could win.
**A price change is a new price.** When the file's figure differs from what
applies, a `product_pricing` row is written through `supersede()`. When it is
unchanged, *nothing at all* is written — re-importing the same sheet twice must
not fill the price book with identical rows, and the smoke check asserts it.
**A bad row is rejected, not guessed at.** Missing code, missing name on a
create, unparseable price, tax outside 0-100, a maximum quantity below the
minimum, a `valid_to` before its `valid_from`, or two rows claiming the same
product at the same quantity band — each rejects that row with a line number
and a reason, and the rest of the file still imports. The spec's "5 failed" is the feature: a price list
is usually mostly right.
**Nothing is ever deleted.** A product absent from the file is left alone, not
retired. A spreadsheet is very often a *partial* price list, and treating an
omission as a deletion would empty a catalogue on the first upload of a
one-product update. Retiring a product stays a deliberate click.
A blank cell means "leave it alone", not "clear it". Only the status column can
set a value to false, and only when it is actually filled in.
### The audit trail
Every run writes a `pricing_imports` row: the file, the link it came from, who
started it, the counts, and the rejected rows with their reasons. The row is
opened **before** the file is parsed, so an import that dies mid-file leaves a
visible `running` record rather than a catalogue that changed with nothing to
say why.
```
100 processed, 85 updated, 10 created, 5 failed
```
Each entry links to the run, and each imported price links back to the run that
wrote it — so a price that looks wrong can be traced to the file that set it.
A run's status is derived from **how many rows got through**, not how many
changed something. Re-importing an unchanged price list creates nothing and
updates nothing — that is the importer being correctly idempotent — and
deriving the status from created + updated reported it to the user as *"Import
failed"*. No rows read, or every row rejected, is `failed`; some through and
some rejected is `partial`; everything through is `completed`.
### The pricing log
`storage/logs/pricing.log` records **every change to the catalogue**, not only
imports — a product created, renamed, retired or deleted; a price added, closed
by supersession, withdrawn or reinstated; a Drive link that could not be
fetched; every rejected spreadsheet row. A price that is wrong is worth tracing
whether it arrived in a file of 500 rows or was typed into one field.
The writes are logged from **model events** on `Product` and `ProductPricing`,
not from calls in the controller, so the log cannot be bypassed: the screens,
the importer, a console command and any later phase of this module all write
through Eloquent and all end up in the log without having to remember to.
```
product created product=37 code=P001 name="ABC Product" owner=27 tax=18.00 unit=Nos
price added price_row=86 product=37 code=P001 owner=27 price=10000.00 band="Any quantity" window="From 01 Sep 2026" source=manual
price added price_row=87 product=37 code=P001 owner=27 price=9000.00 band="10 and up" window="From 01 Sep 2026" source=manual
price closed (superseded) price_row=86 code=P001 price=10000.00 window="01 Sep 2026 - 30 Sep 2026" fields=valid_to
product deactivated product=38 code=P004 name="Installation Visit" owner=27 fields=is_active
price withdrawn price_row=91 code=P004 price=3500.00 band=10-49 source=manual
```
Every line carries the product **code**, not just the id — an id means nothing
when reading a log months later, and the product may since have been deleted.
`actor` is filled in from the session automatically, so a line says who made
the change, or says nothing when it was cron.
Two details worth knowing:
- **`products.price` cache refreshes are not logged.** `syncPriceCache()` writes
that one column after every pricing change, and the price itself was already
logged by `ProductPricing` — logging both would double every price change.
- **`CatalogueLog::mute()`** exists for one caller: `tools/pricing-shot.php`
seeds a sample catalogue inside a transaction it always rolls back, and
logging "product created P001" for a product that never existed is worse than
logging nothing.
### When something goes wrong
Two destinations, for two different readers.
**`pricing_imports.error_log`** is for the user: the rejected rows with line
numbers and reasons written to be acted on, shown on the import screen. It is
capped at 500 rows so one nonsense file cannot put megabytes in a longText
column or 5,000 rows on a page; past the cap the count stands in for the
detail.
**`storage/logs/pricing.log`** is for whoever has to work out why — the same
file the section above describes, which is why it is a file of its own rather
than lines in `laravel.log`: 160MB of other things cannot be read after a
failed import. It holds every rejection uncapped, plus what the database has
nowhere to put — exception classes, file and line, stack traces, the Drive
link, the owner and the actor:
```
[2026-09-09 09:51:08] import started run=15 owner=27 actor=27 source=upload file=not-a-price-list.csv bytes=42
[2026-09-09 09:51:08] the file could not be read run=15 exception=RuntimeException error="No header row found..." at=...CatalogueImporter.php:460
#0 ...CatalogueImporter.php(155): App\Services\Pricing\CatalogueImporter->readRows(...)
[2026-09-09 09:51:08] row rejected run=15 reason="The file could not be read: No header row found..."
[2026-09-09 09:51:08] import finished run=15 status=failed processed=0 created=0 updated=0 priced=0 failed=1
```
`key=value` throughout, so `grep 'run=15'` gives one run and `grep 'row
rejected'` gives every rejection ever. `ImportLog` swallows its own failures on
purpose: an unwritable log directory must never be the reason an import fails.
Three guarantees hold however an import ends:
- **Nothing reaches the user as a 500.** An unreadable file becomes a recorded
run with a reason. An unexpected throwable is caught in the controller,
logged with its trace, and returned as a message on the screen.
- **A run never stays on `running`.** `CatalogueImporter::close()` is called on
every path out, including the controller's own catch — otherwise the history
keeps a row claiming an import is still in progress days later.
- **A row that throws does not stop the file.** It is logged with its trace,
rejected with a short reason, and the import carries on.
### Google Drive, without the Google API client
A Sheet or Drive file shared as *anyone with the link* exposes a plain
unauthenticated export URL, so the fetch is one HTTP GET:
```
docs.google.com/spreadsheets/d//edit → .../export?format=xlsx
drive.google.com/file/d//view → drive.google.com/uc?export=download&id=
```
Adding `google/apiclient` would mean a service account, a JSON key on the
server, OAuth consent and some 200 packages — to read a file the business has
already chosen to share. The honest time to add it is when a genuinely private
file has to be read, and `DriveSheetFetcher` is where it plugs in.
What that trade costs, and why the class is mostly error handling: a link that
is **not** shared publicly does not fail cleanly. Google answers `200` with a
sign-in page, so a naive fetch hands the importer an HTML file and reports "no
header row found". The fetcher detects HTML in the leading bytes and says *"That
link is not shared publicly"* instead. It also retries once through Drive's
virus-scan interstitial, refuses anything over 25MB, and picks the file
extension from `Content-Disposition` before falling back to the content type —
PhpSpreadsheet chooses its reader by extension, so getting that wrong reads as
a corrupt file.
## Where the data lives
The catalogue is scoped to an **account**, like `email_categories` and unlike
`sentiments`, `next_actions` and `lead_types`. Those four are vocabularies for
describing mail and mean the same thing for every installation; a catalogue and
its prices are one business's commercial data. Employees see their admin's
catalogue through `ResolvesAutoReplyOwner`; only the admin can change it.
`Product::activeFor($ownerId, $connection)` takes the same optional connection
argument the vocabularies do, so the cron pipeline can read a tenant database
the same way `EmailCategory::activeFor()` already does. Phase 3 needs that.
Unlike the vocabularies there is deliberately **no** `DEFAULTS` fallback. A
built-in sentiment list is a reasonable guess; a built-in product list would be
an invented price. Callers must handle an empty catalogue — which is exactly
the spec's "product not found" branch.
## Two things the spec asked for that are not in the schema
**`products.valid_from` / `valid_to`.** Price validity belongs to a price. A
second pair of dates on the product would give "may this price be quoted
today?" two answers that can disagree. Whether a product may be quoted at all
is `is_active`; when a given price applies is `product_pricing.valid_from`.
**`pricing_enquiry` and `pricing_enquiry_items`.** These duplicate what
`messages` plus `gemini_return_json` plus a draft quotation already hold, and
add tables that must be kept in sync with a quotation the user may edit. The
draft quotation is the extraction record. If an audit trail of *what the AI
extracted before the user corrected it* is wanted, that is one nullable JSON
column on the quotation, not two tables.
## Tools
```
php tools/pricing-smoke.php # 56 checks: lookup, importer, failures, logging, links
php tools/quotation-smoke.php # 55 checks: matching, pricing, snapshot, draft, PDF
php tools/quotation-shot.php # renders the quotation screen; --pdf renders the attachment
php tools/pricing-shot.php # renders the screen to public/_shot.html
php tools/pricing-shot.php --clean
```
The smoke check writes rows under the product code prefix `ZZSMOKE-` and
deletes them at the end. The shot script seeds its sample catalogue inside a
transaction that is always rolled back — the screenshot shows four products and
the database keeps none of them. `_shot.html` lives under `public/`, so the
`--clean` step matters.
## What comes next
| Phase | State | |
| --- | --- | --- |
| 1 Product Master, price history, imports | **built** | |
| 3 Extraction, matching, confidence | **built** | `EnquiryExtractor`, `ProductMatcher` |
| 4 Quotations, items, terms, price-as-of-date | **built** | `QuotationBuilder` |
| 5 Email draft with the quotation | **built** | `QuotationDraftWriter` |
| 5b Quotation PDF, attached to the reply | **built** | `dompdf/dompdf`, `QuotationPdf`, both send paths |
| 2 Question Master, product questions | not built | asks the customer for what is missing before quoting |
| 6 Discount approval workflow | not built | policy on top of a working engine |
## The PDF, and how it gets attached
The quotation goes out as an **attached PDF**, not as a table in the body. The
covering mail says what it is, what it comes to and how long it stands; every
figure, every line and the terms are in the attachment.
That is deliberate beyond following the request. A table in the mail body
gives the customer two versions of one document to reconcile, and the body
copy is the one that loses its formatting in transit — email clients rewrite
table markup freely, and CKEditor strips inline styles before it is even sent.
### Rendering
`dompdf/dompdf` renders `resources/views/quotations/pdf.blade.php`. That
template is written for dompdf and shares nothing with the on-screen
`quotations.show`: dompdf supports a subset of CSS 2.1 with no flexbox and no
grid, so the layout is tables and floats.
Two settings are not optional:
- **`defaultFont` is DejaVu Sans.** dompdf's default face has no ₹ (U+20B9),
and a missing glyph does not fail — it renders as a blank, on a document
quoting money. DejaVu ships with dompdf and has the glyph, and the smoke
check asserts the face is actually embedded in the output.
- **`isRemoteEnabled` is false.** A quotation is built from the app's own
data; a template able to fetch a remote URL would turn PDF generation into
a request-forgery surface for no benefit.
The customer's copy carries none of the internal apparatus — no match
confidence, no "asked as", no review warnings. Those are for the salesperson
on `quotations.show`. An unpriced line prints **"To be advised"**, never
₹0.00, because a zero in a price column on a customer's copy reads as *free*.
Files live in `storage/app/quotations`, named by quotation id and number, and
are rebuilt when the quotation is newer than the file. The PDF is rendered at
**prepare** time, not at send time, so a template or font failure surfaces
while the user is still looking at the screen.
### Attaching
The reply screen posts `quotation_id` with the form. That id is not trusted:
`MailController::quotationAttachment()` re-checks that the quotation belongs
to this account **and** to this very mail, so a tampered form cannot attach
another customer's prices to an outgoing message.
Both send paths carry it, and neither goes through `OutgoingReply` — that DTO
belongs to the auto-reply senders, not to a human pressing Send Reply:
| Path | How |
| --- | --- |
| Gmail (`sendGemilReply`) | the raw RFC 822 message becomes `multipart/mixed` — body part, then the PDF base64'd and `chunk_split` at 76 characters |
| Outlook (`sendOutlookReply`) | Graph cannot attach while creating a reply, so the file is POSTed to `/messages/{draftId}/attachments` between createReply and send |
A failure to produce or attach the PDF is logged and **the reply still goes**.
The user has already pressed Send; a mail that arrives without its attachment
is recoverable, one that never leaves is not. When the render fails at prepare
time the screen says so in red rather than promising an attachment that will
not be there.
### Still missing on the document
There is no company profile in the app, so the PDF header shows the account's
name and email. A logo, a registered address and a GSTIN belong on a settings
screen that does not exist yet, and every real quotation wants them. Note that
a logo will also need `ext-gd`, which is commented out at php.ini line 931 on
this machine — dompdf only needs it for images, which is why it is not needed
today.
### Two things a later phase still has to face
**The classification prompt is still not in one place.** Extraction sidesteps
it by running on demand, but if anything ever does need to go into that prompt,
it is assembled inline in five blocks — three in `ProcessEmailQueue.php` and
two in `MailController.php`. The fix follows the pattern the vocabularies
already established: one `promptSection()` referenced from all five sites, as
in `EmailCategory::promptSection()`.
**There is no company profile.** The quotation header shows the account's name
and email because that is all the app holds. A logo, a registered address and a
GSTIN belong on a settings screen that does not exist yet, and every real
quotation wants them.