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';