Blame | Last modification | View Log | RSS feed
-- ============================================================================-- Samsung: delist pre-2025 listings with zero stock anywhere 2026-09-09---- Marks 738 Samsung tag_listing rows inactive and end-of-life. These are-- listings first published before 2025-01-01 that hold no stock at-- SmartDukaan and no stock at any active partner, i.e. dead catalogue that-- was still orderable in the FOFO portal.---- SELECTION (evaluated against prod hadb1 on 2026-09-09 16:39 IST):-- - catalog.item.brand = 'Samsung'-- - catalog.tag_listing.tag_id = 4 (default_fofo; tag 7 'test' has no rows)-- - tag_listing.active = 1-- - tag_listing.start_date < 2025-01-01-- (start_date, create_timestamp and item.addedOn agree row-for-row)-- - SUM(warehouse.view_availability.total) = 0 or no row -> no SD stock-- - no fofo.inventory_item.good_quantity > 0 at any store with-- fofo_store.active = 1 AND closed = 0 -> no partner stock---- The id list below is FROZEN from that prod evaluation and is deliberately-- NOT recomputed here. Re-deriving the predicate in another environment would-- pick a different set, because stock tables in a copy drift from prod. The-- point of this script is that the same 738 listings die everywhere.---- SCOPE: catalog.tag_listing.active and .eol_date ONLY.-- - NO price columns touched (mop, mrp, selling_price, support_price).-- - NO catalog.item rows touched; item.status is left alone.-- - NO stock, order or wallet data touched.---- BREAKDOWN: 738 listings / 274 catalog variants / 258 models-- 612 mobiles, 92 tablets, 14 smart watches, 20 accessories-- 170 variants go fully dark; 104 keep an active post-2025 sibling-- colour, so the variant stays listed.---- CHECKED BEFORE WRITING:-- - 0 of the 738 have an open indent (warehouse.view_cis.indent), so none-- has stock already on order.-- - 21 sold at partner counters within the last 90 days. Those are sold-- out rather than dead; included deliberately, per instruction.-- - The 214 pre-2025 Samsung listings NOT in this set all still hold stock-- somewhere (21 warehouse, 201 partner, 8 both) and stay active.-- - eol_date was near-unused before this: 15 rows house-wide, all-- 2020-06-01. None of the 738.---- SOLR: this is a bulk SQL path, so it does NOT fire-- TagListingChangeListener (@TransactionalEventListener AFTER_COMMIT ->-- fofoSolr.updateSingleCatalog). The partner listing reads Solr, where a-- catalog's active_b is the OR of its items' tag_listing.active. It catches-- up on the next full reindex: Listing.scheduledPushDataToSolr,-- cron "0 0 6,18 * * *" in profitmandi-cron, or the cron jar run manually-- with --pushDataToSolr.-- Note eol_date does NOT feed Solr at all. Solr's eol_no_stock_b comes from-- Catalog.findAllWithEOLWithOutStock / findAllWithNoGoodStock, which are pure-- warehouse-stock queries. eol_date is read only by the price-circular and-- scheme-summary named queries on TagListing, which already gate on-- tl.active = true.---- APPLIED: prod hadb1 2026-09-09 (both steps, 738 rows each).---- EXPECTED IMPACT: 738 rows -> active = 0, eol_date = '2026-09-08'-- ============================================================================-- ----------------------------------------------------------------------------- STEP 0: freeze the target set. Every statement below joins this table, so-- the script is deterministic and safe to re-run.-- ---------------------------------------------------------------------------DROP TABLE IF EXISTS catalog._samsung_delist_20260909;CREATE TABLE catalog._samsung_delist_20260909 (tag_listing_id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO catalog._samsung_delist_20260909 (tag_listing_id) VALUES(9455), (9335), (9336), (9337), (9338), (9331), (9332), (9333), (9334), (9327), (9328), (9329),(9330), (9324), (9325), (9326), (9321), (9322), (9323), (9317), (9318), (9315), (9316), (9311),(9297), (9299), (9292), (9291), (9250), (9251), (9252), (9253), (9246), (9247), (9248), (9249),(9204), (9205), (9202), (9198), (9200), (9201), (9194), (9195), (9196), (9197), (9190), (9191),(9192), (9193), (9189), (9188), (9186), (9096), (9093), (9094), (9090), (9009), (9002), (9003),(8983), (8968), (8956), (8957), (8958), (8959), (8882), (8875), (8874), (8873), (8872), (8856),(8855), (8833), (8834), (8835), (8828), (8829), (8830), (8831), (8827), (8823), (8826), (8820),(8821), (8611), (8612), (8565), (8566), (8567), (8568), (8521), (8522), (8516), (8503), (8504),(8499), (8500), (8501), (8496), (8497), (8498), (8492), (8493), (8494), (8495), (8426), (8427),(8425), (8424), (8422), (8419), (8418), (8391), (8392), (8393), (8394), (8389), (8390), (8384),(8385), (8386), (8381), (8382), (8383), (8378), (8379), (8380), (8371), (8327), (8326), (8325),(8324), (8323), (8315), (8316), (8317), (8318), (8311), (8312), (8313), (8314), (8307), (8308),(8309), (8310), (8303), (8304), (8305), (8306), (8300), (8301), (8302), (8254), (8255), (8256),(8251), (8252), (8253), (8249), (8250), (8246), (8247), (8202), (8187), (8188), (8154), (8133),(8134), (8054), (8055), (8056), (8047), (8048), (8049), (8050), (8043), (8044), (8045), (8046),(8039), (8040), (8041), (8042), (8036), (8037), (8038), (8032), (8033), (8034), (8035), (8028),(8029), (8030), (7960), (7961), (7962), (7963), (7957), (7939), (7906), (7901), (7903), (7898),(7900), (7897), (7813), (7793), (7754), (7742), (7738), (7734), (7727), (7712), (7713), (7711),(7659), (7634), (7619), (7613), (7566), (7567), (7556), (7557), (7554), (7555), (7552), (7553),(7550), (7551), (7548), (7549), (7546), (7547), (7544), (7545), (7542), (7543), (7540), (7541),(7538), (7539), (7536), (7537), (7534), (7535), (7532), (7533), (7530), (7527), (7528), (7529),(7524), (7525), (7526), (7077), (7042), (7043), (7040), (7006), (7005), (6979), (6980), (6981),(6976), (6977), (6978), (6957), (6958), (6955), (6956), (6951), (6952), (6953), (6954), (6947),(6948), (6949), (6950), (6943), (6946), (6939), (6941), (6942), (6925), (6852), (6853), (6854),(6855), (6856), (6841), (6840), (6824), (6825), (6768), (6769), (6770), (6765), (6767), (6756),(6755), (6744), (6691), (6690), (6689), (6688), (6687), (6667), (6669), (6666), (6600), (6601),(6596), (6598), (6591), (6588), (6582), (6584), (6577), (6580), (6574), (6576), (6571), (6572),(6566), (6567), (6568), (6565), (6510), (6511), (6512), (6509), (6495), (6496), (6497), (6498),(6492), (6494), (6487), (6488), (6489), (6490), (6484), (6485), (6486), (6481), (6482), (6483),(6478), (6479), (6480), (6476), (6471), (6472), (6473), (6474), (6475), (6423), (6424), (6425),(6420), (6421), (6422), (6404), (6405), (6387), (6384), (6381), (6378), (6373), (6357), (6358),(6359), (6354), (6356), (6350), (6347), (6342), (6343), (6344), (6345), (6284), (6281), (6278),(6274), (6275), (6270), (6272), (6268), (6224), (6212), (6208), (6156), (6145), (6146), (6129),(6098), (6078), (6009), (6010), (6011), (6008), (6003), (6004), (5999), (6000), (6001), (6002),(5995), (5996), (5997), (5998), (5991), (5992), (5993), (5994), (5987), (5988), (5989), (5990),(5962), (5925), (5923), (5910), (5906), (5907), (5902), (5903), (5904), (5899), (5901), (5867),(5868), (5865), (5866), (5838), (5828), (5829), (5826), (5827), (5823), (5824), (5825), (5820),(5821), (5822), (5817), (5818), (5819), (5814), (5815), (5816), (5809), (5811), (5812), (5807),(5808), (5551), (5552), (5553), (5548), (5549), (5550), (5547), (5545), (5542), (5541), (5532),(5533), (5534), (5531), (5493), (5495), (5490), (5491), (5489), (5488), (5485), (5487), (5484),(5443), (5441), (5442), (5437), (5438), (5439), (5434), (5435), (5436), (5431), (5432), (5433),(5428), (5430), (5427), (5422), (5395), (5396), (5397), (5392), (5393), (5394), (5389), (5390),(5391), (5386), (5387), (5388), (5383), (5384), (5385), (5379), (5380), (5381), (5382), (5375),(5376), (5377), (5378), (5371), (5372), (5373), (5374), (5328), (5329), (5330), (5331), (5324),(5325), (5326), (5327), (5317), (5318), (5319), (5316), (5267), (5235), (5234), (5223), (5224),(5225), (5220), (5221), (5222), (5198), (5199), (5200), (5155), (5140), (5088), (5083), (5076),(5078), (5052), (5000), (5001), (4996), (4997), (4998), (4972), (4938), (4939), (4940), (4935),(4936), (4937), (4929), (4930), (4931), (4926), (4927), (4928), (4886), (4887), (4883), (4884),(4885), (4880), (4881), (4882), (4877), (4878), (4879), (4870), (4867), (4868), (4869), (4864),(4865), (4866), (4861), (4862), (4863), (4858), (4859), (4860), (4830), (4800), (4801), (4798),(4795), (4726), (4727), (4724), (4725), (4714), (4715), (4712), (4713), (4710), (4709), (4664),(4665), (4666), (4662), (4649), (4640), (4642), (4643), (4627), (4628), (4630), (4622), (4624),(4619), (4620), (4621), (4618), (4613), (4614), (4615), (4611), (4612), (4549), (4453), (4454),(4455), (4456), (4449), (4450), (4451), (4452), (4394), (4395), (4396), (4391), (4392), (4386),(4387), (4388), (4389), (4370), (4364), (4362), (4359), (4360), (4361), (4264), (4265), (4266),(4267), (4261), (4262), (4263), (4248), (4236), (4220), (4215), (4216), (4212), (4197), (4198),(4199), (4192), (4194), (4195), (4154), (4155), (4151), (4152), (4153), (4150), (4146), (4141),(4135), (4136), (4137), (4138), (4072), (4073), (4074), (4075), (4068), (4069), (4070), (3995),(3996), (3997), (3992), (3993), (3994), (3929), (3877), (3878), (3868), (3869), (3870), (3871),(3864), (3865), (3866), (3867), (3847), (3846), (3845), (3844), (3775), (3764), (3761), (3759),(3758), (3757), (3756), (3561), (3426), (3427), (3428), (3283), (1965), (1967), (1968), (1970),(1971), (1973), (1974), (1975), (1976), (1989);-- Guard: must be exactly 738 rows, and every id must resolve to a Samsung-- tag_listing row in this environment. If either check surprises you, STOP.SELECT COUNT(*) AS frozen_ids FROM catalog._samsung_delist_20260909; -- expect 738SELECT COUNT(*) AS resolved,SUM(i.brand = 'Samsung') AS samsung,SUM(tl.tag_id = 4) AS default_fofo,SUM(tl.start_date < '2025-01-01') AS pre_2025FROM catalog._samsung_delist_20260909 dJOIN catalog.tag_listing tl ON tl.id = d.tag_listing_idJOIN catalog.item i ON i.id = tl.item_id; -- expect 738 / 738 / 738 / 738-- Pre-state, for the record.SELECT tl.active, tl.eol_date, COUNT(*) AS nFROM catalog._samsung_delist_20260909 dJOIN catalog.tag_listing tl ON tl.id = d.tag_listing_idGROUP BY 1, 2;-- ----------------------------------------------------------------------------- STEP 1: delist. Idempotent - re-running matches 0 rows.-- ---------------------------------------------------------------------------UPDATE catalog.tag_listing tlJOIN catalog._samsung_delist_20260909 d ON d.tag_listing_id = tl.idSET tl.active = 0WHERE tl.active = 1;-- ----------------------------------------------------------------------------- STEP 2: mark end-of-life as of yesterday. Idempotent.-- ---------------------------------------------------------------------------UPDATE catalog.tag_listing tlJOIN catalog._samsung_delist_20260909 d ON d.tag_listing_id = tl.idSET tl.eol_date = '2026-09-08 00:00:00'WHERE tl.eol_date IS NULL OR tl.eol_date <> '2026-09-08 00:00:00';-- ----------------------------------------------------------------------------- STEP 3: verify. Expect a single row: 0 / 2026-09-08 00:00:00 / 738-- ---------------------------------------------------------------------------SELECT tl.active, tl.eol_date, COUNT(*) AS nFROM catalog._samsung_delist_20260909 dJOIN catalog.tag_listing tl ON tl.id = d.tag_listing_idGROUP BY 1, 2;-- Samsung listings left active, by listing year. Everything surviving from-- before 2025 should still hold stock somewhere.SELECT YEAR(tl.start_date) AS yr, SUM(tl.active = 1) AS still_active, SUM(tl.active = 0) AS inactiveFROM catalog.tag_listing tlJOIN catalog.item i ON i.id = tl.item_idWHERE tl.tag_id = 4 AND i.brand = 'Samsung'GROUP BY 1 ORDER BY 1;-- ============================================================================-- ROLLBACK (run only the block below)-- ============================================================================-- UPDATE catalog.tag_listing tl-- JOIN catalog._samsung_delist_20260909 d ON d.tag_listing_id = tl.id-- SET tl.active = 1, tl.eol_date = NULL;---- Then push Solr again so the portal picks the listings back up:-- cron jar with --pushDataToSolr, or wait for the 06:00 / 18:00 reindex.---- The freeze table is kept as the audit record of exactly which listings were-- touched. Drop it only once you are sure no rollback is coming:-- DROP TABLE catalog._samsung_delist_20260909;