Subversion Repositories SmartDukaan

-- One web offer per model: the badge is keyed on its consolidated CONTENT, not on a

-- circular row.

--

-- Before: one web_offer per circular row, so a model appeared in as many badges as the

-- circular had rows naming it - Edge 60 Pro 12+256 carried five. A partner's product card

-- listed all five and had to reconcile them.

--

-- After: every model's offers are consolidated into one description, and models whose

-- consolidated terms are IDENTICAL share a single web_offer. Sep'26: 191 badges -> 107,

-- covering the same 314 models, each appearing exactly once.

--

-- circular_page_no / circular_row_no stay for reference but no longer identify a badge -

-- one badge now spans many rows. They are left NULL for content-keyed rows, and MySQL

-- permits repeated NULLs in uk_circular_row, so the old key does not fight the new one.

ALTER TABLE dtr.web_offer

  -- sha256 over the normalised content: modes, values, banks, timing, schemes, tenures,

  -- windows. Two models with the same terms hash the same and share the badge.

  ADD COLUMN circular_offer_key CHAR(64) NULL,

  ADD UNIQUE KEY uk_circular_offer_key (circular_document_id, circular_offer_key);

-- The old row-keyed badges cannot be migrated: one of them is a fragment of what a

-- content-keyed badge now says, and there is no mapping from five fragments to one whole.

-- They are regenerated in full by the next ingest.

--

-- ⚠️ web_offer_product has NO foreign key to web_offer, so its rows do NOT cascade and

-- must be cleared first or they are orphaned. web_offer_sync_shadow DOES cascade.

--

-- source='CIRCULAR' only. The 3,850 hand-authored MANUAL rows are never touched.

DELETE FROM dtr.web_offer_product

 WHERE webOfferId IN (SELECT id FROM dtr.web_offer WHERE source = 'CIRCULAR');

DELETE FROM dtr.web_offer WHERE source = 'CIRCULAR';