Subversion Repositories SmartDukaan

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

--  Undo wrongly retired model categorisations                     2026-09-24

--

--  The delist cron's STEP 4 used to scan house-wide for "fully dark" mobile

--  models and move them to OTHER. That also caught models that were dark only

--  because they were not listed yet (new launches) or were paused by hand,

--  and retired them the morning after they were categorised. Fixed in

--  CatalogDelistServiceImpl / delist_inactive_listings_rolling_24m.sql: STEP 4

--  now only retires a model whose listing that same run delisted.

--

--  This restores the models the fixed logic would NOT have retired:

--    * latest retirement run had no _delist_audit row for any of the model's

--      items on that run_date (the cron did not delist it), AND

--    * run after the 2026-09-10 backfill, or the model was added within 30

--      days before that run (older never-listed models in the backfill were

--      meant to be retired and stay OTHER), AND

--    * the model is still on the OTHER row that run opened (models already

--      restored by hand, e.g. Nothing 1026596-603, are left alone).

--

--  Restore = delete the OTHER row the cron opened and reopen the row it

--  closed (end_date -> NULL), so history reads as if it never happened.

--  2026-09-24 preview: 16 models.

--

--  Rollback: catalog._bak_cc_restore_20260924 holds every row of the affected

--  catalogs as they were before this ran.

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

DROP TEMPORARY TABLE IF EXISTS _cc_restore;

CREATE TEMPORARY TABLE _cc_restore (

    catalog_id INT PRIMARY KEY,

    other_id   INT NOT NULL,

    reopen_id  INT NOT NULL

);

INSERT INTO _cc_restore (catalog_id, other_id, reopen_id)

SELECT a.catalog_id, o.id, a.cc_id

FROM catalog._delist_cc_audit a

JOIN catalog.catagoriesd_catalog o

  ON o.catalog_id = a.catalog_id AND o.end_date IS NULL

 AND o.status = 'OTHER' AND o.start_date = a.run_date

JOIN catalog.catagoriesd_catalog p

  ON p.id = a.cc_id AND p.end_date = a.run_date - INTERVAL 1 DAY

WHERE a.run_date = (SELECT MAX(a2.run_date) FROM catalog._delist_cc_audit a2

                    WHERE a2.catalog_id = a.catalog_id)

  AND NOT EXISTS (SELECT 1 FROM catalog._delist_audit d

                  JOIN catalog.item i ON i.id = d.item_id

                  WHERE i.catalog_item_id = a.catalog_id AND d.run_date = a.run_date)

  AND (a.run_date > '2026-09-10'

       OR (SELECT MIN(i.addedOn) FROM catalog.item i

           WHERE i.catalog_item_id = a.catalog_id) >= a.run_date - INTERVAL 30 DAY);

CREATE TABLE catalog._bak_cc_restore_20260924 AS

SELECT cc.* FROM catalog.catagoriesd_catalog cc

WHERE cc.catalog_id IN (SELECT catalog_id FROM _cc_restore);

START TRANSACTION;

DELETE cc FROM catalog.catagoriesd_catalog cc

JOIN _cc_restore r ON r.other_id = cc.id;

UPDATE catalog.catagoriesd_catalog cc

JOIN _cc_restore r ON r.reopen_id = cc.id

SET cc.end_date = NULL;

COMMIT;

-- Solr picks this up at the next 06:00 / 18:00 full reindex.