SmartDukaan – /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/

Rev 37585 – Fix web-offer sync: selectModelOffers join order (Unknown column 'o.id' in ...

amit — Thu, 10 Sep 2026 17:19:06 +0530

amit – 1 file(s) modified
Fix web-offer sync: selectModelOffers join order (Unknown column 'o.id' in 'on clause')

selectModelOffers led its FROM with offers.offer_product p and then joined
offers.offer_raw_row r ON r.offer_id = o.id, referencing alias o one join
before o was introduced. An ON clause resolves only against tables already
in the join order, so MySQL rejected it with SQLSyntaxErrorException:
Unknown column 'o.id' in 'on clause'. Every per-model sync since r37581 has
therefore failed - webOfferSync=FAILED on the 15:04 and 17:14 re-ingests of
document 4 - leaving the 166 legacy NULL-key row-badges active and zero
consolidated badges published.

Reordered to lead with offers.offer o, matching the proven shape of the
sibling selectRowOffers query. All three are inner joins so the result set
is unchanged; BEST_OFFER_WINS and ORDER BY p.catalog_id still resolve.

Verified by executing the corrected statement read-only against prod for
document 4: 733 rows over 314 distinct catalog ids, the expected model
count. Not caught by tests because all 53 offercircular tests are
stub-based (no Spring, no DB) and never execute this SQL.

Failure was visible only because r37568 records webOfferSync= in
ingest_summary; the runner still swallows the exception by design.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java

Rev 37581 – web offer sync: retire the pre-consolidation badges instead of deleting ...

amit — Thu, 10 Sep 2026 14:58:44 +0530

amit – 2 file(s) modified
web offer sync: retire the pre-consolidation badges instead of deleting them

The sync now deactivates any CIRCULAR row with a NULL circular_offer_key on its next
run. A row-keyed badge is a fragment of what a consolidated badge says, so leaving it
active would show a partner the same offer twice - once whole and once in pieces.

That removes the DELETE from the migration entirely, which is strictly better:

* nothing is destroyed, so a bad sync is one UPDATE away from being undone
* web_offer_product has NO foreign key to web_offer, so the DELETE had to clear its
532 rows separately or strand them as orphans
* no window where a circular has no badges at all - the old ones stay live until the
new ones exist

The migration is now purely additive: one ALTER, safe to run ahead of the deploy, which
is how it was applied to prod.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/resources/sql/add_web_offer_per_model.sql

Rev 37580 – web offer: state an EMI scheme the circular gave without ...

amit — Thu, 10 Sep 2026 14:55:17 +0530

amit – 3 file(s) modified
web offer: state an EMI scheme the circular gave without months

Rs.2,500 on Credit Card EMI (CIB) with IDFC First Bank, Kotak Mahindra Bank

52 Sep'26 offers have a bare 'CIB' tenure cell - the circular names the EMI type and
gives no months. TenureParser correctly yields no tenure row for a cell with no digits,
so the type vanished and the badge said only 'on Credit Card EMI'. Every one of those
52 carries a cashback, so it was 52 offers' worth of SKUs silent about the EMI they
apply to.

The type is stated because the circular stated it. The MONTHS are not invented,
because it did not give them - the same line the CIB expansion was left on.

Read from offer_raw_row.col_emi_tenure rather than stored: 'scheme, no month' is not a
tenure, and offer_tenure.tenure_months is NOT NULL, so persisting it would mean a fake
0 row standing for a fact that is not a tenure.

Only ever attached to an EMI mode, and real tenures always win over the bare code.

ModelOfferTest 8 -> 11.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
~ /trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/offercircular/WebOfferSyncService.java
~ /trunk/profitmandi-fofo/src/test/java/com/spice/profitmandi/web/offercircular/ModelOfferTest.java

Rev 37578 – web offer sync: read what the circular grants each MODEL, ...

amit — Thu, 10 Sep 2026 13:46:45 +0530

amit – 3 file(s) modified
web offer sync: read what the circular grants each MODEL, not each row

selectModelOffers returns one row per (catalog_id, benefit), carrying the context that
qualifies it - the banks it applies to, its tenures and their scheme, the timing and
the window. Consolidation happens in the service; this is the read it needs.

LEFT JOIN on the benefit, because a scheme-only offer carries no cashback and dropping
it here would lose the NCE/LCE badge entirely.

Content-keyed writes alongside: selectSyncedByKey, insertKeyedWebOffer,
replaceProducts, deactivateMissingKeys. A badge is now identified by a sha256 of its
terms, so models with identical terms share one and a model appears in exactly one.

Migration add_web_offer_per_model.sql adds circular_offer_key + its unique key, and
drops the row-keyed CIRCULAR badges - one of those is a fragment of what a
content-keyed badge says and there is no mapping from five fragments to one whole, so
they are regenerated by the next ingest.

⚠️ web_offer_product has NO foreign key to web_offer, so its rows do not cascade and
are deleted explicitly or they are orphaned. web_offer_sync_shadow does cascade.
source='CIRCULAR' only - the 3,850 hand-authored rows are never touched.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_web_offer_per_model.sql

Rev 37555 – web offer sync: one value per SKU per payment mode ...

amit — Wed, 09 Sep 2026 14:42:05 +0530

amit – 1 file(s) modified
web offer sync: one value per SKU per payment mode

The circular says two things about the same phone. Motorola Sep'26 page 9 names
'G37 Power' generically at Rs.1,000 CC Full Swipe (row 12) and 'G37 Power (8+128)'
at Rs.2,000 (row 13), so G37 Power 8+128 carried both badges and a partner could
quote either.

Precedence, per (catalog_id, txn_mode):
1. a specifically named VARIANT beats a generic MODEL - the named line is the OEM
being precise about that SKU
2. at the same level, the higher value wins
3. tie-break on lowest offer id, so the outcome is deterministic

The generic line still covers every variant nobody named: G37 Power 4+64 keeps its
Rs.1,000 Full Swipe while 8+128 takes Rs.2,000. Verified on the live data - 1026311
now resolves to exactly CC_EMI 2500 and CC_FULL_SWIPE 2000, down from four competing
values.

An offer keeps a SKU if it wins on at least one of its own modes, since one badge
carries every mode of its offer. Scheme-only offers are never filtered - no value to
compare.

Deliberately NOT reusing v_offer_applicable, which encodes the same VARIANT-beats-
MODEL rule but has no document or status filter: once August is re-ingested its rows
enter that view and an EXPIRED August variant would silently suppress a live
September model. Scoping to one document avoids that; the view is left alone for
other consumers.

Measured on Sep'26: 557 product links -> 458, 191 badges -> 166.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java

Rev 37553 – web offer sync: all_banks is TINYINT(1), so never cast it ...

amit — Wed, 09 Sep 2026 13:11:25 +0530

amit – 1 file(s) modified
web offer sync: all_banks is TINYINT(1), so never cast it to Number

ClassCastException: java.lang.Boolean cannot be cast to java.lang.Number, thrown from
selectPublishableOffers on every ingest since 2026-09-08. Confirmed against the live
driver: SELECT all_banks returns java.lang.Boolean.

MySQL Connector/J maps TINYINT(1) to Boolean while Hibernate may hand back a Number,
so the value must be read through isTrue() - the same shape as ScopeConfig.isTrue and
OfferScopeRepositoryImpl.isTrue, both of which exist for exactly this reason and both
of which I had in front of me when writing the cast.

The failure was silent by design: CircularIngestRunner swallows sync exceptions so a
web-offer problem can never fail a good ingest. The circular kept publishing, the
document's processed_at kept moving, and the only symptom was that web_offer.synced_at
stopped advancing - which is what should have been checked before reporting the sync
as working on 8 September.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java

Rev 37549 – web offer sync: read tenures for scheme-only offers selectTenures, ...

amit — Wed, 09 Sep 2026 12:36:26 +0530

amit – 2 file(s) modified
web offer sync: read tenures for scheme-only offers

selectTenures, keyed by offer id like the benefit and bank reads.

Needed because an offer with no cashback is still a real offer - a no-cost or
low-cost EMI scheme - and its tenures are the only thing it has to say. Without them
those rows can only be described as 'NA'.

Rev 37545 – web offer sync: read benefits and banks for the composed ...

amit — Tue, 08 Sep 2026 19:39:16 +0530

amit – 2 file(s) modified
web offer sync: read benefits and banks for the composed detail text

selectBenefits and selectBanks, keyed by offer id and fetched once per circular
rather than per offer - 259 offers would otherwise be 518 extra round trips.

The main query now also carries offer_id, benefit_timing and all_banks.

Bank read takes INCLUDE rows only: an EXCLUDE is meaningful only when all_banks=1,
and such an offer is described as 'All banks' rather than by listing an exclusion a
partner cannot act on.

Rev 37527 – offer circular: a new month supersedes every older one ...

amit — Thu, 03 Sep 2026 18:16:40 +0530

amit – 4 file(s) modified
offer circular: a new month supersedes every older one

Publishing a circular expires still-running offers from earlier circulars, at the
offers level and again for the web offers they produced.

Why: the new circular restates whatever is still live, so leaving the old month
ACTIVE means the same cashback is current twice. Verified Aug'26 -> Sep'26: all 24
SKUs covered by August's four still-running Apple offers were carried into
September, six of them twice.

- expireOlderDocumentOffers: status ACTIVE -> EXPIRED for earlier documents.
end_date is deliberately NOT touched - it is what the OEM said, and older offers
stay readable as reference. They simply stop being current.
- expireIfSuperseded: the converse, so re-ingesting an OLD circular while a newer
one is live cannot resurrect it. With both, 'newest circular wins' holds whatever
order documents are ingested in.
- deactivateOlderPeriods / hasNewerPublishedPeriod do the same for dtr.web_offer.

Verified on real data in a rolled-back transaction: publishing Sep expired 165
still-ACTIVE Aug offers (many stale since the 18 Aug ingest), Sep did not expire
itself, and re-ingesting Aug while Sep is live self-expired its 4 revived offers.

Rev 37525 – offer circular: division aliases, portal-editable scope, web offer sync (dao) ...

amit — Thu, 03 Sep 2026 17:43:36 +0530

amit – 11 file(s) modified
offer circular: division aliases, portal-editable scope, web offer sync (dao)

- division_alias support: CircularIngestRepository.selectDivisionAliases so an
alternate OEM label resolves onto an existing division instead of dropping the
rows as UNKNOWN. Both pdf_label columns are ALIASED - Hibernate discovers
native-query results by name and throws NonUniqueDiscoveredSqlAlias on a dup.

- OfferScopeRepository{,Impl}: write side for scope config (add/edit a division,
in_scope toggle, label aliases). Kept apart from OfferCurationRepository, whose
contract is limited to product_alias - a bad alias mismaps one product, a bad
scope change silently drops a whole brand.

- OfferCircularRepositoryImpl: label-side division join now resolves through
division_alias (dva), otherwise an UNPARSED row under an aliased label is hidden
by the inScopeOnly filter - exactly the rows the review screen exists to show.
Also isoDate(): period_month left as java.sql.Date serialised to epoch millis,
which an <input type=date> silently rejects, so the circular filter cleared
itself and every circular showed every row.

- WebOfferSyncRepository{,Impl} + migration: publish a parsed circular into
dtr.web_offer / web_offer_product. Anchored on (document, page, row) stored as
VALUES, never offers.offer.id, which is recreated on every re-ingest. source
defaults to MANUAL so the 3850 hand-typed rows are untouched; web_offer_sync_shadow
is what lets a human edit win over a later sync.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/CircularIngestRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/CircularIngestRepositoryImpl.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCircularRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferScopeRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferScopeRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/WebOfferSyncRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_web_offer_circular_sync.sql
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/dao/repository
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/dao/repository/offers
+ /trunk/profitmandi-dao/src/test/java/com/spice/profitmandi/dao/repository/offers/OfferCircularDateSerialisationTest.java

Rev 37363 – Offer circular ingest: return affected rows so INSERT IGNORE cannot ...

amit — Wed, 19 Aug 2026 20:32:53 +0530

amit – 2 file(s) modified
Offer circular ingest: return affected rows so INSERT IGNORE cannot hide an FK failure

insertBenefit/insertTenure/insertBank use INSERT IGNORE for idempotency, which also
makes MySQL downgrade a foreign key violation to a warning. Production was bootstrapped
without the bank / txn_mode / emi_scheme masters, so all 872 benefit and tenure inserts
were silently discarded: the ingest reported PUBLISHED with 239 offers carrying no
amounts, no tenures and no bank eligibility, and nothing anywhere said so.

The three methods now return the affected row count (1 written, 0 discarded) so the
caller can count what landed rather than what it attempted.

Rev 37342 – Allow re-ingest to start a circular already sitting in DRAFT ...

amit — Tue, 18 Aug 2026 14:59:37 +0530

amit – 2 file(s) modified
Allow re-ingest to start a circular already sitting in DRAFT

Moving the parse into the portal turned DRAFT into a dead end. Under the cron
scheduler DRAFT meant "something will pick me up", so re-ingest only ever had to push
a document back into it, and PUBLISHED/FAILED were the only sensible sources. Nothing
polls DRAFT any more - the runner is triggered by the upload that created the
document - so a circular whose trigger never fired had no way to be started at all.

That is not hypothetical: the Aug'26 circular uploaded to production before the runner
was deployed sits in DRAFT, and pressing re-ingest matched zero rows and reported
"not started".

DRAFT now joins PUBLISHED and FAILED as a valid source state. PROCESSING stays
excluded - that one is genuinely in flight - and claimForProcessing remains the single
arbiter of who actually parses, so allowing DRAFT cannot cause a double parse.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCurationRepository.java
~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCurationRepositoryImpl.java

Rev 37333 – Reclaim offer circulars stranded in PROCESSING, and surface the ingest ...

amit — Tue, 18 Aug 2026 12:11:38 +0530

amit – 3 file(s) modified
Reclaim offer circulars stranded in PROCESSING, and surface the ingest reason

A claim moves a circular DRAFT -> PROCESSING, and selectDraftDocumentIds only ever
picks up DRAFT. So a worker that died, was redeployed, or hung mid-parse left that
document stranded in PROCESSING forever, invisible to every later sweep. The batch
tier looked like it provided retry semantics and did not.

- reclaimStalledProcessing(minutes) fails anything left in PROCESSING past the
threshold. Deliberately FAILED and not back to DRAFT: a circular that reliably
kills the parser would otherwise be re-claimed on every sweep forever. FAILED is
visible, carries the reason, and the existing re-ingest action already allows
FAILED -> DRAFT, so re-queueing is one click.
- claimForProcessing now stamps processed_at, which is what the stall is measured
against. No DDL: that column is written only by this repository and read by
nothing else, so it can carry the last-transition time.
- selectDocuments returns ingest_error and ingest_summary. Without the reason the
screen would show a bare FAILED, which the reaper would now produce routinely.

Verified against the local DB both ways: a claim aged 45 minutes is reclaimed with
its reason recorded, a fresh claim is untouched.

Rev 37329 – Add offers-schema repositories for the Pine Labs affordability circular ...

amit — Mon, 17 Aug 2026 21:07:00 +0530

amit – 7 file(s) modified
Add offers-schema repositories for the Pine Labs affordability circular

Three repositories over the offers schema, split by who is allowed to write what:

- OfferCircularRepository - read side for the review screen (verbatim rows,
documents, per-offer parsed detail)
- CircularIngestRepository - write side for ingest; claims a DRAFT document with
a guarded UPDATE and deletes only rows derived from that one document, so
re-ingesting one month cannot disturb another
- OfferCurationRepository - write side for manual curation

The split is deliberate: a caller that only displays circulars should not hold a
handle that can rewrite the scope config or the offer tables.

OfferCurationRepository writes offers.product_alias and nothing else. An alias is
config, keyed on (division_id, raw_text), so re-ingest reproduces it and one
decision carries into every later circular. Writing offer_product instead would be
derived data that re-ingest deletes, which is why doing so has to set
manually_curated and freeze the circular permanently - not a trade worth making for
a naming decision, so this repository cannot make it.

A PIN is validated against the division's own brand and category before it is
stored. Nothing downstream re-checks a pinned id - the matcher takes it at face
value - so an unverified exception would silently mismap money.

+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/CircularIngestRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/CircularIngestRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCircularRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCircularRepositoryImpl.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCurationRepository.java
+ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/repository/offers/OfferCurationRepositoryImpl.java