Subversion Repositories SmartDukaan

-- ============================================================================

--  Hot Deal brand - DATA MIGRATION                                2026-09-15

--

--  Spec : docs/superpowers/specs/2026-09-10-hot-deal-brand-design.md

--  Plan : docs/superpowers/plans/2026-09-11-hot-deal-brand.md

--

--  Run AFTER hot_deal_brand_schema.sql PASS 1, BEFORE PASS 2.

--  Idempotent - every mutating step guards on brand <> 'Hot Deal', so a second

--  run matches nothing and cannot double-prefix a model name.

--

--  WHAT IT DOES

--    1. freezes the current brand / model_name of every in-scope row

--    2. folds the OEM brand into model_name, then sets brand = 'Hot Deal'

--    3. ensures every in-scope catalog has a model_hot_deal attributes row

--       carrying oem_brand

--

--  WHY model_name CHANGES

--    Description is DERIVED, never stored: both Catalog.getDescription() and

--    Item.getItemDescriptionNoColor() build  brand + model_name + model_number

--    at read time. Overwriting brand alone would erase the OEM from every

--    label, so it is folded into model_name:

--

--      brand        'Oneplus'  ->  'Hot Deal'

--      model_name   ''         ->  'Oneplus'

--      model_number unchanged

--      => "Hot Deal Oneplus Y1S Edge 32inch 32HD2A01"

--

--    model_name is empty on most in-scope rows (the marketing text lives in

--    model_number), so the prefix usually lands in a free slot.

--

--  ALREADY-BILLED DOCUMENTS ARE NOT TOUCHED

--    transaction.lineitem and fofo.fofo_order_item snapshot brand/model_name

--    at order time, so invoices keep printing the OEM brand. 2,507 in-scope

--    lines sit on invoices filed at NIC with an IRN; rewriting the snapshot

--    would make a reprint disagree with the PrdDesc held against that IRN.

--    That is deliberate - see spec section 8.

-- ============================================================================

-- ---------------------------------------------------------------------------

--  SCOPE - FROZEN ID LIST, derived once on prod (hadb1) 2026-09-15

--

--  Selection rule that produced this list:

--      a known hot deal  AND  an active listing

--        known hot deal = a model_hot_deal row (expiry irrelevant - brand

--                         drives membership now, there are no windows)

--                       UNION tag_listing.hot_deals = 1 AND tag_id = 4

--                         (the 31 legacy rows from the old

--                          /v2/fofo/indent/confirm-hotdeals-pause screen)

--        active listing = tag_listing.tag_id = 4 AND active = 1

--

--  Result: 69 items across 31 catalogs - 27 of the 28 model_hot_deal catalogs

--  (one has no active listing) plus 4 legacy-only Ai+ catalogs.

--

--  ⚠ The ids are FROZEN ON PURPOSE. Do not re-derive the predicate per

--    environment. The localhost dev copy holds 26 model_hot_deal rows against

--    prod's 29, and the set moved from 69/31 to 68/30 over four days in

--    September as listings were reactivated. Pinning the ids is what makes the

--    same SKUs migrate in every environment.

-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS catalog._hot_deal_brand_scope (

  item_id    INT NOT NULL PRIMARY KEY,

  catalog_id INT NOT NULL,

  KEY idx_hd_scope_catalog (catalog_id)

) ENGINE=InnoDB;

INSERT IGNORE INTO catalog._hot_deal_brand_scope (item_id, catalog_id) VALUES

  (37876,1025252),(37877,1025252),(37878,1025252),(37879,1025253),(37880,1025253),

  (37881,1025253),(37882,1025253),(37919,1025253),(37920,1025252),(39579,1026010),

  (39759,1026085),(39941,1026085),(39942,1026085),(39943,1026085),(39944,1026085),

  (40199,1026268),(40251,1026302),(40412,1026360),(40413,1026360),(40483,1026403),

  (40484,1026403),(40485,1026403),(40486,1026403),(40487,1026403),(40488,1026403),

  (40497,1026408),(40498,1026408),(40499,1026408),(40500,1026408),(40581,1026443),

  (40582,1026444),(40583,1026445),(40584,1026446),(40585,1026447),(40649,1026470),

  (40650,1026470),(40651,1026470),(40654,1026473),(40655,1026473),(40656,1026473),

  (40657,1026473),(40658,1026473),(40659,1026474),(40660,1026474),(40661,1026474),

  (40662,1026474),(40663,1026474),(40664,1026475),(40665,1026475),(40666,1026475),

  (40667,1026475),(40668,1026475),(40698,1026488),(40699,1026488),(40700,1026488),

  (40701,1026488),(40702,1026488),(40733,1026506),(40753,1026526),(40754,1026527),

  (40755,1026528),(40756,1026529),(40826,1026562),(40828,1026564),(40829,1026565),

  (40830,1026566),(40860,1026583),(40861,1026584),(40863,1026586);

-- Guard: the list must load whole. 69 items / 31 catalogs.

-- MySQL cannot reference the same TEMPORARY table twice in one statement, so

-- these are two plain statements against a normal table.

SELECT COUNT(*) AS scope_items, COUNT(DISTINCT catalog_id) AS scope_catalogs

FROM catalog._hot_deal_brand_scope;

-- ---------------------------------------------------------------------------

--  FREEZE - must run before any UPDATE.

--

--  This table is the ONLY record of the pre-move state: after the UPDATE the

--  old brand cannot be re-derived, because brand now reads 'Hot Deal' and the

--  OEM survives only as a prefix inside model_name, which is not safely

--  parseable back out.

--

--  INSERT IGNORE + the brand guard means a re-run will not overwrite the

--  original values with already-migrated ones.

-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS catalog._hot_deal_brand_freeze (

  level          VARCHAR(8)   NOT NULL COMMENT 'catalog | item',

  row_id         INT          NOT NULL,

  old_brand_id   INT          NULL     COMMENT 'catalog level only; item has no brand_id',

  old_brand      VARCHAR(64)  NULL,

  old_model_name VARCHAR(255) NULL,

  run_date       DATE         NOT NULL,

  PRIMARY KEY (level, row_id)

) ENGINE=InnoDB;

INSERT IGNORE INTO catalog._hot_deal_brand_freeze

      (level, row_id, old_brand_id, old_brand, old_model_name, run_date)

SELECT 'catalog', c.id, c.brand_id, c.brand, c.model_name, CURDATE()

FROM catalog.catalog c

JOIN (SELECT DISTINCT catalog_id FROM catalog._hot_deal_brand_scope) s

     ON s.catalog_id = c.id

WHERE c.brand <> 'Hot Deal';

INSERT IGNORE INTO catalog._hot_deal_brand_freeze

      (level, row_id, old_brand_id, old_brand, old_model_name, run_date)

SELECT 'item', i.id, NULL, i.brand, i.model_name, CURDATE()

FROM catalog.item i

JOIN catalog._hot_deal_brand_scope s ON s.item_id = i.id

WHERE i.brand <> 'Hot Deal';

-- ---------------------------------------------------------------------------

--  MOVE - prefix the OEM brand into model_name, then overwrite brand.

--

--  The  brand <> 'Hot Deal'  predicate is what makes this idempotent: on a

--  second run nothing matches, so no row can become "Samsung Samsung ...".

--  COALESCE guards the NULL model_name rows; TRIM collapses the stray space

--  those leave behind.

-- ---------------------------------------------------------------------------

UPDATE catalog.catalog c

JOIN (SELECT DISTINCT catalog_id FROM catalog._hot_deal_brand_scope) s

     ON s.catalog_id = c.id

JOIN catalog.brand b ON b.name = 'Hot Deal'

SET c.model_name = TRIM(CONCAT(c.brand, ' ', COALESCE(c.model_name, ''))),

    c.brand      = 'Hot Deal',

    c.brand_id   = b.id

WHERE c.brand <> 'Hot Deal';

UPDATE catalog.item i

JOIN catalog._hot_deal_brand_scope s ON s.item_id = i.id

SET i.model_name = TRIM(CONCAT(i.brand, ' ', COALESCE(i.model_name, ''))),

    i.brand      = 'Hot Deal'

WHERE i.brand <> 'Hot Deal';

-- ---------------------------------------------------------------------------

--  ATTRIBUTES - every in-scope catalog needs exactly one model_hot_deal row.

--

--  Catalogs that arrived via the legacy-flag source have no row at all. They

--  get one with oem_brand set and the five pill attributes left at defaults,

--  so the fofo admin lists them as incomplete and ops can fill them in. A SKU

--  with the Hot Deal brand but no pills is valid, not an error - the badge

--  comes from the brand, the pills from this row.

--

--  start_date / end_date are still NOT NULL at this point (they are dropped in

--  schema PASS 3), so sentinel values are supplied.

-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------

--  DEDUPE - one attributes row per catalog.

--

--  The old model allowed several date-windowed deals for the same catalog, so a

--  model re-promoted in a later window has more than one row. That is legal

--  history under the old design and a duplicate under the new one, where the

--  row IS the attributes. Keep the newest and drop the rest.

--

--  Found on prod 2026-09-15: catalog 1026529 (Realme) had two rows, created

--  2026-08-19 and 2026-09-02 by the same user, with IDENTICAL attributes - so

--  keeping the newest lost nothing. Verify that before running elsewhere; if the

--  attributes differ, decide which is current instead of taking MAX(id) blindly.

--

--  Must run before the unique key in schema PASS 2, which this is what makes

--  possible.

-- ---------------------------------------------------------------------------

DELETE m FROM catalog.model_hot_deal m

JOIN (SELECT catalog_item_id, MAX(id) AS keep_id

      FROM catalog.model_hot_deal

      GROUP BY catalog_item_id HAVING COUNT(*) > 1) d

  ON d.catalog_item_id = m.catalog_item_id AND m.id < d.keep_id;

-- existing rows: backfill oem_brand from the freeze table

UPDATE catalog.model_hot_deal mhd

JOIN catalog._hot_deal_brand_freeze f

     ON f.level = 'catalog' AND f.row_id = mhd.catalog_item_id

SET mhd.oem_brand = f.old_brand

WHERE mhd.oem_brand IS NULL;

-- missing rows: create them

INSERT INTO catalog.model_hot_deal

      (brand_id, catalog_item_id, start_date, end_date, oem_brand,

       warranty_months, item_condition, finance_mapping, fresh, affordability,

       created_on, created_by)

SELECT (SELECT id FROM catalog.brand WHERE name = 'Hot Deal'),

       f.row_id, '2000-01-01', '2099-12-31', f.old_brand,

       0, 'NEW', 0, 0, 0, NOW(), 'hot_deal_brand_migration'

FROM catalog._hot_deal_brand_freeze f

WHERE f.level = 'catalog'

  AND NOT EXISTS (SELECT 1 FROM catalog.model_hot_deal m

                   WHERE m.catalog_item_id = f.row_id);

-- ---------------------------------------------------------------------------

--  VERIFY - all three must come back clean before running schema PASS 2.

-- ---------------------------------------------------------------------------

-- 1. every scope row now carries the brand: expect 69 and 31

SELECT COUNT(*) AS items_on_brand

FROM catalog.item i JOIN catalog._hot_deal_brand_scope s ON s.item_id = i.id

WHERE i.brand = 'Hot Deal';

SELECT COUNT(DISTINCT c.id) AS catalogs_on_brand

FROM catalog.catalog c

JOIN (SELECT DISTINCT catalog_id FROM catalog._hot_deal_brand_scope) s ON s.catalog_id = c.id

WHERE c.brand = 'Hot Deal';

-- 2. no catalog is missing its attributes row or its oem_brand: expect 0

SELECT COUNT(*) AS catalogs_missing_attributes

FROM (SELECT DISTINCT catalog_id FROM catalog._hot_deal_brand_scope) s

LEFT JOIN catalog.model_hot_deal m ON m.catalog_item_id = s.catalog_id

WHERE m.id IS NULL OR m.oem_brand IS NULL;

-- 3. no duplicate attributes rows, which would break schema PASS 2: expect 0

SELECT COUNT(*) AS duplicate_attribute_rows FROM (

  SELECT catalog_item_id FROM catalog.model_hot_deal

  GROUP BY catalog_item_id HAVING COUNT(*) > 1

) d;

-- 4. eyeball the derived descriptions

SELECT c.id, c.brand, c.model_name, c.model_number,

       TRIM(CONCAT(c.brand, ' ', c.model_name, ' ', c.model_number)) AS description

FROM catalog.catalog c

JOIN (SELECT DISTINCT catalog_id FROM catalog._hot_deal_brand_scope) s ON s.catalog_id = c.id

ORDER BY c.id LIMIT 10;

-- ============================  ROLLBACK  ====================================

--  Restores brand, brand_id and model_name from the freeze table, then removes

--  the rows this migration created. Run a FULL Solr reindex afterwards.

--

--    UPDATE catalog.catalog c

--      JOIN catalog._hot_deal_brand_freeze f

--        ON f.level = 'catalog' AND f.row_id = c.id

--    SET c.brand      = f.old_brand,

--        c.brand_id   = f.old_brand_id,

--        c.model_name = f.old_model_name;

--

--    UPDATE catalog.item i

--      JOIN catalog._hot_deal_brand_freeze f

--        ON f.level = 'item' AND f.row_id = i.id

--    SET i.brand      = f.old_brand,

--        i.model_name = f.old_model_name;

--

--    DELETE FROM catalog.model_hot_deal WHERE created_by = 'hot_deal_brand_migration';

--    UPDATE catalog.model_hot_deal SET oem_brand = NULL;

--

--  Keep catalog._hot_deal_brand_freeze and catalog._hot_deal_brand_scope as

--  the audit record of the run; they are what make it reversible at all.

-- ============================================================================