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