Subversion Repositories SmartDukaan

-- ============================================================================
--  Delist logic v3 - rolling 24-month, house-wide                  2026-09-10
--
--  Supersedes samsung_delist_pre2025_no_stock_20260909.sql, which was one
--  brand with a frozen id list. This runs across EVERY brand and EVERY
--  category (mobile and non-mobile) on a rolling 24-month window, so it is
--  safe to schedule daily.
--
--  Changes over v1:
--    (NEW 0) rolling 24-month window instead of a hardcoded 2025-01-01, and
--            no brand / category restriction.
--    (NEW 1) an item with an OUTSTANDING VENDOR PO is never delisted. It is
--            skipped, not excluded -- the next run re-evaluates it, so it
--            delists by itself once the PO closes.
--    (NEW 2) a model that goes FULLY DARK (no active listing left on any of
--            its colours) has its movement categorisation moved to OTHER, so
--            it stops being offered as a live model.
--  Added in v3 (2026-09-10):
--    (NEW 3) NOT MOVED FOR A MONTH on either side. Zero stock alone was too
--            weak: it fired on SKUs partners were still transacting, where the
--            recent movement was often the very sale that emptied the stock.
--    (NEW 4) the internal pseudo-brands (Dummy, FOC, FOC HANDSET, Live Demo)
--            are no longer excluded; they are swept like anything else.
--    (NEW 5) vendor-PO guard narrowed to status = 1; status 2 is unreachable.
--
--  ⚠ The 2026-09-10 run of v2 predates NEW 3, so it delisted 24 listings /
--    21 models that had partner movement inside the month. Reverted by
--    rollback_delist_recent_movement_20260910.sql.
--
--  Written to be run repeatedly (daily). Every step is idempotent.
-- ============================================================================

-- ---------------------------------------------------------------------------
--  WHAT "OUTSTANDING VENDOR PO" MEANS  (NEW 1)
--
--    warehouse.purchaseorder.status is an ORDINAL enum (in.shop2020.purchase.POStatus):
--        0 INIT   1 READY   2 PARTIALLY_FULFILLED   3 PRECLOSED   4 CLOSED
--
--    Outstanding  = status = 1 AND supplier.internal = 0
--                   AND lineitem.unfulfilledQuantity > 0
--
--    status 1 (READY) is the house definition of an open PO -- the existing
--    named query warehouse.selectOpenPo uses exactly
--    "po.status = 1 and s.internal = false".
--
--    PARTIALLY_FULFILLED (2) is NOT included, and this is not an oversight.
--    It is UNREACHABLE, not merely rare -- verified 2026-09-10:
--      * all 8 PO-status writes in the codebase set INIT, READY, PRECLOSED or
--        CLOSED (PurchaseOrderServiceImpl 399/479/913, GrnController:495,
--        V2FofoGrnController:441, PurchaseOrderController:188,
--        V2FofoPurchaseOrderController:139, POScheduler:46). None sets it.
--      * no native SQL writes purchaseorder.status.
--      * SELECT COUNT(*) ... WHERE status = 2 -> 0, across 51,593+ POs to 2011.
--    Its only two code references (InvoiceServiceImpl:248, POScheduler:32) are
--    defensive READS. Partial receipt is modelled per LINE
--    (unfulfilledQuantity / fulfilled -- 9,153 lines are genuinely part
--    received), never rolled up to a header status, so a part-received PO sits
--    in READY and is already caught by "status = 1 AND unfulfilled > 0".
--    Do not "restore" status 2 here thinking it closes a gap.
--
--    NOTE the guard is effectively a 6-DAY GRACE, not an indefinite hold:
--    POScheduler.autoClosePurchaseOrders (ScheduledSkeleton:747, daily 01:00)
--    force-CLOSES vendor POs 6 days after creation (internal: 4) regardless of
--    whether goods arrived. That is why every READY PO on prod is <= 6 days
--    old, and it guarantees a deferral always resolves.
--
--    INIT (0) is DELIBERATELY EXCLUDED. Measured on prod 2026-09-09:
--        status 0 -> 376 unfulfilled lines spanning 2024-04-02 .. 2026-09-03
--        status 1 ->  97 unfulfilled lines spanning 2026-09-01 .. 2026-09-09
--    INIT is a drawer of abandoned drafts, 2.5 years deep. Nothing ever closes
--    them, so counting INIT as "outstanding" would defer those items forever
--    instead of for a day. On the 738 rows of the 2026-09-09 Samsung batch,
--    INIT would have blocked 13 listings / 10 models, every one a stale draft.
--
--    s.internal = 0 is what makes it a VENDOR po rather than an internal
--    stock transfer between our own warehouses.
--
--  COLUMN-NAME TRAP in warehouse.lineitem: the entity field `quantity` maps to
--  DB column `initial_qty`, and entity `amendedQty` maps to DB column
--  `quantity`. So in raw SQL `quantity` is the AMENDED qty, not the ordered
--  one. Use unfulfilledQuantity / fulfilled and do not reach for `quantity`.
-- ---------------------------------------------------------------------------

-- ===========================================================================
--  STEP 0 - persistent audit. Created once, appended to by every run.
--  Without this a run is IRREVERSIBLE: after step 3 the rows read active=0,
--  so the selection predicate (which requires active=1) can no longer
--  re-derive what it touched. Same pattern as the freeze table kept for the
--  2026-09-09 Samsung batch.
--
--  ROLLBACK for a given run_date:
--    UPDATE catalog.tag_listing tl
--      JOIN catalog._delist_audit a ON a.tag_listing_id = tl.id
--       SET tl.active = 1, tl.eol_date = NULL
--     WHERE a.run_date = '<date>';
--    -- and restore categorisation:
--    DELETE cc FROM catalog.catagoriesd_catalog cc
--      JOIN catalog._delist_cc_audit x ON x.catalog_id = cc.catalog_id
--     WHERE x.run_date = '<date>' AND cc.status = 'OTHER' AND cc.end_date IS NULL;
--    UPDATE catalog.catagoriesd_catalog cc
--      JOIN catalog._delist_cc_audit x ON x.cc_id = cc.id
--       SET cc.end_date = NULL
--     WHERE x.run_date = '<date>';
-- ===========================================================================
CREATE TABLE IF NOT EXISTS catalog._delist_audit (
    tag_listing_id INT NOT NULL,
    item_id        INT,
    run_date       DATE NOT NULL,
    PRIMARY KEY (tag_listing_id, run_date)
);

CREATE TABLE IF NOT EXISTS catalog._delist_cc_audit (
    cc_id       INT NOT NULL,
    catalog_id  INT NOT NULL,
    prev_status VARCHAR(299),
    run_date    DATE NOT NULL,
    PRIMARY KEY (cc_id, run_date)
);

-- ===========================================================================
--  STEP 1 - build the candidate set
-- ===========================================================================
DROP TEMPORARY TABLE IF EXISTS _delist_candidate;
CREATE TEMPORARY TABLE _delist_candidate (
    tag_listing_id  INT PRIMARY KEY,
    item_id         INT,
    catalog_item_id INT,
    KEY idx_item (item_id),
    KEY idx_cat  (catalog_item_id)
);

-- ROLLING 24-MONTH WINDOW, EVERY BRAND, EVERY CATEGORY (mobile and non-mobile).
-- No brand or category predicate: the cron evaluates any itemId.
INSERT INTO _delist_candidate (tag_listing_id, item_id, catalog_item_id)
SELECT tl.id, tl.item_id, i.catalog_item_id
FROM catalog.tag_listing tl
JOIN catalog.item i ON i.id = tl.item_id
WHERE tl.tag_id = 4                       -- default_fofo; tag 7 'test' has no rows
  AND tl.active = 1
  -- aged from the later of start and last re-activation (2026-09-28)
  AND GREATEST(tl.start_date, COALESCE(tl.last_activated, tl.start_date))
      < CURDATE() - INTERVAL 24 MONTH
  -- No brand exclusion. The internal pseudo-brands (Dummy, FOC, FOC HANDSET,
  -- Live Demo) are swept like anything else, by instruction 2026-09-10.
  -- no SmartDukaan stock
  AND NOT EXISTS (
        SELECT 1 FROM warehouse.view_availability va
        WHERE va.item_id = tl.item_id AND va.total > 0)
  -- no stock at any live partner
  AND NOT EXISTS (
        SELECT 1
        FROM fofo.inventory_item ii
        JOIN fofo.fofo_store fs ON fs.id = ii.fofo_id
        WHERE ii.item_id = tl.item_id AND ii.good_quantity > 0
          AND fs.active = 1 AND fs.closed = 0)
  -- nothing already on order from the partner side
  AND NOT EXISTS (
        SELECT 1 FROM warehouse.view_cis vc
        WHERE vc.item_id = tl.item_id AND vc.indent > 0)
  -- ---------------------------------------------------------------------
  -- NOT MOVED FOR A MONTH, EITHER SIDE.
  --
  -- The stock guards above are point-in-time: they say "zero right now", not
  -- "zero for a while". Nothing records when stock hit zero
  -- (view_availability.updated_at is the materialized-view refresh clock, it
  -- ticks continuously and cannot be used for this), so recent MOVEMENT is
  -- used as the proxy for "still trading".
  --
  -- Warehouse side: warehouse.scanNew (3.4M rows, live) via inventoryItem.
  --   warehouse.scan is a dead Saholic relic - 2,476 rows, last write 2012.
  -- Partner side: fofo.scan_record via fofo.inventory_item. Any type counts
  --   (PURCHASE / SALE / returns) - the question is whether the SKU is moving
  --   at all, not which direction.
  --
  -- Both are NOT EXISTS rather than MAX(...) comparisons so they short-circuit
  -- on the first recent row and use the (inventoryItemId, scannedAt) and
  -- (inventory_item_id, create_timestamp) indexes. An item that has NEVER
  -- moved qualifies for delisting, which is correct.
  AND NOT EXISTS (
        SELECT 1
        FROM warehouse.inventoryItem wii
        JOIN warehouse.scanNew sn ON sn.inventoryItemId = wii.id
        WHERE wii.itemId = tl.item_id
          AND sn.scannedAt >= CURDATE() - INTERVAL 1 MONTH)
  AND NOT EXISTS (
        SELECT 1
        FROM fofo.inventory_item fii
        JOIN fofo.scan_record sr ON sr.inventory_item_id = fii.id
        WHERE fii.item_id = tl.item_id
          AND sr.create_timestamp >= CURDATE() - INTERVAL 1 MONTH);

-- ===========================================================================
--  STEP 2 (NEW 1) - drop anything with an outstanding vendor PO.
--  These are SKIPPED, not excluded: tomorrow's run re-evaluates them.
-- ===========================================================================
DROP TEMPORARY TABLE IF EXISTS _deferred_vendor_po;
CREATE TEMPORARY TABLE _deferred_vendor_po (
    tag_listing_id INT PRIMARY KEY
);

INSERT INTO _deferred_vendor_po (tag_listing_id)
SELECT DISTINCT c.tag_listing_id
FROM _delist_candidate c
JOIN warehouse.lineitem      li ON li.itemId = c.item_id
JOIN warehouse.purchaseorder po ON po.id     = li.purchaseOrder_id
JOIN warehouse.supplier      s  ON s.id      = po.supplierId
WHERE s.internal = 0
  AND po.status = 1                       -- READY only; status 2 is unreachable (see header)
  AND li.unfulfilledQuantity > 0;

-- Report before writing anything.
-- NOTE: MySQL cannot reference the same TEMPORARY table twice in one
-- statement ("Can't reopen table"), so these counts are deliberately kept as
-- separate statements rather than one combined SELECT.
SELECT COUNT(*) AS deferred_open_vendor_po FROM _deferred_vendor_po;

DELETE c FROM _delist_candidate c
JOIN _deferred_vendor_po d ON d.tag_listing_id = c.tag_listing_id;

SELECT COUNT(*)                          AS listings_to_delist FROM _delist_candidate;
SELECT COUNT(DISTINCT catalog_item_id)   AS models_touched     FROM _delist_candidate;

-- ===========================================================================
--  STEP 3 - the delist itself
-- ===========================================================================
INSERT IGNORE INTO catalog._delist_audit (tag_listing_id, item_id, run_date)
SELECT c.tag_listing_id, c.item_id, CURDATE() FROM _delist_candidate c;

UPDATE catalog.tag_listing tl
JOIN _delist_candidate c ON c.tag_listing_id = tl.id
SET tl.active   = 0,
    tl.eol_date = CURDATE()
WHERE tl.active = 1;

-- ===========================================================================
--  STEP 4 (NEW 2) - models that just went FULLY DARK -> categorisation OTHER
--
--  "Fully dark" = zero active listings left across every colour of that
--  catalog_item_id. A model that keeps even one active colour is NOT touched.
--  (Real example from the 2026-09-09 batch: catalog 1024414, Samsung S24
--   8GB/256GB, had 3 colours delisted but 2 still active -- it correctly
--   stays RUNNING.)
--
--  WHY 'OTHER' AND NOT NULL: catagoriesd_catalog.status is
--  `varchar(299) NOT NULL`, so a literal NULL needs a schema change. It is
--  also unnecessary -- CatalogMovingEnum.OTHER is defined as "not found", and
--  every reader already treats OTHER as "not a live model":
--    * /indent/getOutOfStockDetails keeps only {HID, FASTMOVING, RUNNING}
--      -> OTHER drops out of the Suggested-PO out-of-stock list
--    * Catalog.findAllWithEOLWithOutStock / findAllWithNoGoodStock accept
--      (status IS NULL OR 'OTHER' OR 'SLOWMOVING') -> the model becomes
--      eligible for Solr eol_no_stock_b and hides from partner browse
--    * Catalog.selectAllStatusAndBrandWise matches only requested statuses
--
--  WHY WE INSERT A NEW ROW RATHER THAN JUST END-DATING: the table is a
--  slowly-changing dimension (open row = end_date IS NULL), but the native
--  query CatagorisedCatalog.getBrandWiseCatalogMovement picks the latest row
--  per catalog via MAX(COALESCE(end_date, CURDATE())) and has NO end_date
--  filter. An orphaned end-dated RUNNING row would still come back as
--  "latest" and still read RUNNING. Closing the row is not enough -- a new
--  open OTHER row must outrank it.
--
--  SCOPE: MOBILE ONLY (category 10006, ProfitMandiConstants.MOBILE_CATEGORY_ID).
--  Movement classification is a mobile-catalogue concept -- accessories, TVs
--  and the rest are never categorised, so writing OTHER rows for them would
--  invent data no reader consults. Delisting itself (steps 1-3) stays
--  house-wide; only this re-categorisation is narrowed.
--  NOTE: the EOL readers Catalog.findAllWithEOLWithOutStock /
--  findAllWithNoGoodStock actually span categories (10006, 10009, 10010). If
--  10009/10010 should be re-categorised too, widen the IN list below.
--
--  We only NORMALISE models that already have an open row -- i.e. models
--  whose categorisation is currently non-null. We do not manufacture rows for
--  models that never had one: every reader treats a missing row exactly like
--  OTHER, so inserting would add noise and change no behaviour. (2026-09-09 batch: 170 fully dark -> 139 have no open row
--  and are left alone, 31 SLOWMOVING are normalised.)
-- ===========================================================================
DROP TEMPORARY TABLE IF EXISTS _dark_model;
CREATE TEMPORARY TABLE _dark_model (catalog_item_id INT PRIMARY KEY);

-- RUN-SCOPED (changed 2026-09-24). Only models whose listing THIS run delisted.
-- The earlier house-wide version also caught models that were dark because
-- they were not listed yet (new launches categorised before listing) or were
-- paused by hand, and retired them the next morning -- e.g. the 8 Nothing
-- Phone 4 models (1026596-603) on 2026-09-15. The 2026-09-10 run already
-- cleared the pre-cron backlog, so house-wide scanning is no longer needed.
-- Repair of the wrongly retired models: fix_dark_model_retire_20260924.sql.
INSERT IGNORE INTO _dark_model (catalog_item_id)
SELECT cc.catalog_id
FROM catalog._delist_audit a
JOIN catalog.item i ON i.id = a.item_id
JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = i.catalog_item_id
WHERE a.run_date = CURDATE()              -- delisted by this run ...
  AND i.category = 10006                  -- ... MOBILE ONLY (see note above)
  AND cc.end_date IS NULL                 -- has an open categorisation row ...
  AND cc.status <> 'OTHER'                -- ... that is not already OTHER
  AND NOT EXISTS (                        -- fully dark: no active colour left
        SELECT 1
        FROM catalog.tag_listing t2
        JOIN catalog.item i2 ON i2.id = t2.item_id
        WHERE i2.catalog_item_id = cc.catalog_id
          AND t2.active = 1);

-- 4a. close the open row (only where it is not already OTHER)
INSERT IGNORE INTO catalog._delist_cc_audit (cc_id, catalog_id, prev_status, run_date)
SELECT cc.id, cc.catalog_id, cc.status, CURDATE()
FROM catalog.catagoriesd_catalog cc
JOIN _dark_model d ON d.catalog_item_id = cc.catalog_id
WHERE cc.end_date IS NULL AND cc.status <> 'OTHER';

UPDATE catalog.catagoriesd_catalog cc
JOIN _dark_model d ON d.catalog_item_id = cc.catalog_id
SET cc.end_date = CURDATE() - INTERVAL 1 DAY
WHERE cc.end_date IS NULL
  AND cc.status <> 'OTHER';

-- 4b. open a new OTHER row so it outranks the closed one in
--     getBrandWiseCatalogMovement's MAX(COALESCE(end_date, CURDATE()))
INSERT INTO catalog.catagoriesd_catalog (catalog_id, status, start_date, end_date)
SELECT d.catalog_item_id, 'OTHER', CURDATE(), NULL
FROM _dark_model d
WHERE EXISTS (                            -- had an open row we just closed
        SELECT 1 FROM catalog.catagoriesd_catalog x
        WHERE x.catalog_id = d.catalog_item_id
          AND x.end_date = CURDATE() - INTERVAL 1 DAY)
  AND NOT EXISTS (                        -- idempotency guard
        SELECT 1 FROM catalog.catagoriesd_catalog y
        WHERE y.catalog_id = d.catalog_item_id
          AND y.end_date IS NULL);

-- ===========================================================================
--  STEP 5 - Solr does not see any of this.
--  A raw UPDATE publishes no TagListingChangeListener event. The portal
--  catches up at the 06:00/18:00 full reindex (Listing.scheduledPushDataToSolr),
--  or run the cron jar with --pushDataToSolr to force it.
-- ===========================================================================