| Line 274... |
Line 274... |
| 274 |
-- and are left alone, 31 SLOWMOVING are normalised.)
|
274 |
-- and are left alone, 31 SLOWMOVING are normalised.)
|
| 275 |
-- ===========================================================================
|
275 |
-- ===========================================================================
|
| 276 |
DROP TEMPORARY TABLE IF EXISTS _dark_model;
|
276 |
DROP TEMPORARY TABLE IF EXISTS _dark_model;
|
| 277 |
CREATE TEMPORARY TABLE _dark_model (catalog_item_id INT PRIMARY KEY);
|
277 |
CREATE TEMPORARY TABLE _dark_model (catalog_item_id INT PRIMARY KEY);
|
| 278 |
|
278 |
|
| 279 |
-- SELF-HEALING, NOT RUN-SCOPED. This deliberately looks at EVERY fully-dark
|
279 |
-- RUN-SCOPED (changed 2026-09-24). Only models whose listing THIS run delisted.
|
| 280 |
-- mobile model, not just the ones this run delisted. Scoping it to the
|
280 |
-- The earlier house-wide version also caught models that were dark because
|
| 281 |
-- current candidate set would leave models that went dark before the cron
|
281 |
-- they were not listed yet (new launches categorised before listing) or were
|
| 282 |
-- existed (e.g. the 2026-09-09 Samsung batch) carrying a stale RUNNING /
|
282 |
-- paused by hand, and retired them the next morning -- e.g. the 8 Nothing
|
| 283 |
-- SLOWMOVING categorisation forever, because no later run would revisit them.
|
283 |
-- Phone 4 models (1026596-603) on 2026-09-15. The 2026-09-10 run already
|
| 284 |
-- Run house-wide it converges instead: once every dark model reads OTHER,
|
284 |
-- cleared the pre-cron backlog, so house-wide scanning is no longer needed.
|
| 285 |
-- subsequent runs are no-ops. It also removes the need for a separate
|
285 |
-- Repair of the wrongly retired models: fix_dark_model_retire_20260924.sql.
|
| 286 |
-- Samsung backfill.
|
- |
|
| 287 |
INSERT INTO _dark_model (catalog_item_id)
|
286 |
INSERT IGNORE INTO _dark_model (catalog_item_id)
|
| 288 |
SELECT cc.catalog_id
|
287 |
SELECT cc.catalog_id
|
| 289 |
FROM catalog.catagoriesd_catalog cc
|
288 |
FROM catalog._delist_audit a
|
| - |
|
289 |
JOIN catalog.item i ON i.id = a.item_id
|
| - |
|
290 |
JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = i.catalog_item_id
|
| - |
|
291 |
WHERE a.run_date = CURDATE() -- delisted by this run ...
|
| - |
|
292 |
AND i.category = 10006 -- ... MOBILE ONLY (see note above)
|
| 290 |
WHERE cc.end_date IS NULL -- has an open categorisation row ...
|
293 |
AND cc.end_date IS NULL -- has an open categorisation row ...
|
| 291 |
AND cc.status <> 'OTHER' -- ... that is not already OTHER
|
294 |
AND cc.status <> 'OTHER' -- ... that is not already OTHER
|
| 292 |
AND EXISTS ( -- MOBILE ONLY (see note above)
|
- |
|
| 293 |
SELECT 1 FROM catalog.item im
|
- |
|
| 294 |
WHERE im.catalog_item_id = cc.catalog_id
|
- |
|
| 295 |
AND im.category = 10006)
|
- |
|
| 296 |
AND NOT EXISTS ( -- fully dark: no active colour left
|
295 |
AND NOT EXISTS ( -- fully dark: no active colour left
|
| 297 |
SELECT 1
|
296 |
SELECT 1
|
| 298 |
FROM catalog.tag_listing t2
|
297 |
FROM catalog.tag_listing t2
|
| 299 |
JOIN catalog.item i2 ON i2.id = t2.item_id
|
298 |
JOIN catalog.item i2 ON i2.id = t2.item_id
|
| 300 |
WHERE i2.catalog_item_id = cc.catalog_id
|
299 |
WHERE i2.catalog_item_id = cc.catalog_id
|