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

-- ⚠️ NO DELETE. The old row-keyed badges are retired by the SYNC itself, which

-- deactivates any CIRCULAR row with a NULL circular_offer_key on its next run. That is

-- strictly better than deleting them here:

--

--   * 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 a DELETE here would have to

--     clear its rows separately or strand them as orphans

--   * there is no window where a circular has no badges at all - the old ones stay live

--     until the new ones exist

--

-- So this migration is purely additive and safe to run ahead of the deploy.

--

-- To retire them by hand instead (not needed, but this is the statement):

--   UPDATE dtr.web_offer SET active = 0

--    WHERE source = 'CIRCULAR' AND circular_offer_key IS NULL;