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;