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