Subversion Repositories SmartDukaan

-- ============================================================================
--  Rollback: listings delisted on 2026-09-10 that had recent movement
--                                                                  2026-09-10
--  The 2026-09-10 run of delist_inactive_listings_rolling_24m.sql predates the
--  v3 "not moved for a month" constraint (NEW 3). It therefore delisted 24
--  listings across 21 models whose partners had transacted them within the
--  previous month (partner movement 2026-08-12 .. 2026-09-09, while SD
--  warehouse movement was 2023 .. 2026-06). Under v3 those would have been
--  spared, so they are restored here.
--
--  THE ID LIST IS FROZEN, not re-derived. The predicate is relative to
--  CURDATE(), so re-deriving it on a later date -- or in another environment
--  whose scan tables have drifted -- would pick a different set. Pinning the
--  ids is what makes the same 24 listings come back everywhere.
--
--  Idempotent: re-running reactivates nothing further and reopens nothing.
-- ============================================================================

DROP TEMPORARY TABLE IF EXISTS _rb_listing;
CREATE TEMPORARY TABLE _rb_listing (tag_listing_id INT PRIMARY KEY);
INSERT INTO _rb_listing (tag_listing_id) VALUES
  (5478), (5701), (5791), (5978), (5984), (6093), (6133), (6427),
  (6550), (6594), (6624), (6645), (6780), (6851), (7038), (7935),
  (7968), (7969), (8486), (8939), (8940), (8979), (8980), (9031);

-- ---------------------------------------------------------------------------
--  STEP 1 - relist
-- ---------------------------------------------------------------------------
UPDATE catalog.tag_listing tl
JOIN _rb_listing r ON r.tag_listing_id = tl.id
SET tl.active   = 1,
    tl.eol_date = NULL
WHERE tl.active = 0;

SELECT COUNT(*) AS relisted
FROM catalog.tag_listing tl
JOIN _rb_listing r ON r.tag_listing_id = tl.id
WHERE tl.active = 1;

-- ---------------------------------------------------------------------------
--  STEP 2 - revert the categorisation flips that depended on them.
--
--  Only models that are NO LONGER fully dark now that the listings above are
--  active again. A model that stays dark for other reasons keeps its OTHER
--  row, which is still correct.
-- ---------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS _rb_model;
CREATE TEMPORARY TABLE _rb_model (catalog_id INT PRIMARY KEY, cc_id INT, prev_status VARCHAR(299));

INSERT INTO _rb_model (catalog_id, cc_id, prev_status)
SELECT x.catalog_id, x.cc_id, x.prev_status
FROM catalog._delist_cc_audit x
JOIN catalog.item i2 ON i2.catalog_item_id = x.catalog_id
JOIN catalog.tag_listing t2 ON t2.item_id = i2.id
JOIN _rb_listing r ON r.tag_listing_id = t2.id
WHERE x.run_date = '2026-09-10'
GROUP BY x.catalog_id, x.cc_id, x.prev_status;

SELECT COUNT(*) AS models_to_revert FROM _rb_model;

-- 2a. drop the OTHER row the run opened
DELETE cc FROM catalog.catagoriesd_catalog cc
JOIN _rb_model m ON m.catalog_id = cc.catalog_id
WHERE cc.status = 'OTHER'
  AND cc.end_date IS NULL
  AND cc.start_date = '2026-09-10';

-- 2b. reopen the row the run closed, restoring its original status
UPDATE catalog.catagoriesd_catalog cc
JOIN _rb_model m ON m.cc_id = cc.id
SET cc.end_date = NULL,
    cc.status   = m.prev_status
WHERE cc.end_date = '2026-09-09';

-- 2c. drop the audit rows for what we just reverted, so _delist_cc_audit keeps
--     describing only categorisation the job still owns
DELETE x FROM catalog._delist_cc_audit x
JOIN _rb_model m ON m.cc_id = x.cc_id
WHERE x.run_date = '2026-09-10';

-- ---------------------------------------------------------------------------
--  STEP 3 - drop the delist audit rows for the relisted listings, so
--  _delist_audit continues to describe only what is actually delisted.
-- ---------------------------------------------------------------------------
DELETE a FROM catalog._delist_audit a
JOIN _rb_listing r ON r.tag_listing_id = a.tag_listing_id
WHERE a.run_date = '2026-09-10';

-- ---------------------------------------------------------------------------
--  STEP 4 - Solr. As with the delist itself, no event is published; the
--  06:00/18:00 reindex picks the relisting up, or force --pushDataToSolr.
-- ---------------------------------------------------------------------------