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

  AND 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);

-- SELF-HEALING, NOT RUN-SCOPED. This deliberately looks at EVERY fully-dark

-- mobile model, not just the ones this run delisted. Scoping it to the

-- current candidate set would leave models that went dark before the cron

-- existed (e.g. the 2026-09-09 Samsung batch) carrying a stale RUNNING /

-- SLOWMOVING categorisation forever, because no later run would revisit them.

-- Run house-wide it converges instead: once every dark model reads OTHER,

-- subsequent runs are no-ops. It also removes the need for a separate

-- Samsung backfill.

INSERT INTO _dark_model (catalog_item_id)

SELECT cc.catalog_id

FROM catalog.catagoriesd_catalog cc

WHERE cc.end_date IS NULL                 -- has an open categorisation row ...

  AND cc.status <> 'OTHER'                -- ... that is not already OTHER

  AND EXISTS (                            -- MOBILE ONLY (see note above)

        SELECT 1 FROM catalog.item im

        WHERE im.catalog_item_id = cc.catalog_id

          AND im.category = 10006)

  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.

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