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.