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.

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