Subversion Repositories SmartDukaan

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

--  Delist logic v2 - rolling 24-month, house-wide                 2026-09-09

--

--  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.

--

--  Three 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.

--

--  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 IN (1,2) AND supplier.internal = 0

--                   AND lineitem.unfulfilledQuantity > 0

--

--    status 1 is the house definition of an open PO -- the existing named query

--    warehouse.selectOpenPo uses exactly "po.status = 1 and s.internal = false".

--    status 2 is included because PARTIALLY_FULFILLED is a legitimate open

--    state (it happens to hold no unfulfilled lines today).

--

--    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

  -- Internal pseudo-brands. These are NOT sellable catalogue: the app already

  -- hard-excludes them from every partner listing

  -- (StoreController/FofoSolr excludeBrands = Dummy, FOC HANDSET, FOC, Live Demo).

  -- Delisting them would change nothing a partner sees while disturbing demo /

  -- FOC operations. Remove this line if you want them swept too.

  AND i.brand NOT IN ('Dummy', 'FOC', 'FOC HANDSET', 'Live Demo')

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

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

--  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 IN (1, 2)

  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.

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