Subversion Repositories SmartDukaan

Rev

Rev 37640 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

-- ============================================================================
--  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.
-- ============================================================================