-- ============================================================ -- delete-outside-bound-addresses.sql -- -- Targets 23 addresses: -- * 22 whose computed bound is NULL in computed-bounds-466.sql -- (addresses outside both region polygons), plus -- * address 285 (user 388, mobile 9676651496, "Mbnr, Mbnr"). -- -- DESTRUCTIVE. For each address it: -- 1. detaches coupon_usage / complaints / user_incidents from its orders -- 2. deletes its order_items and order_status rows -- 3. deletes its orders -- 4. deletes the address -- -- Local impact: 46 orders, 2 order_items, 46 order_status, -- 3 coupon_usage rows (detached, kept), 0 complaints, 0 incidents. -- 17 of the 23 addresses are users' default addresses. -- User 388 keeps their account but will have no address after this. -- If an address is already gone, every statement is a no-op. -- -- Apply: -- wrangler d1 execute --remote --config wrangler.prod.toml \ -- --file address_util/delete-outside-bound-addresses.sql -- ============================================================ -- Preserve coupon usage history; drop its reference to the deleted orders/items. UPDATE coupon_usage SET order_id = NULL, order_item_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881)) OR order_item_id IN ( SELECT oi.id FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881) ); -- Preserve complaints / incidents; drop their order reference. UPDATE complaints SET order_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881)); UPDATE user_incidents SET order_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881)); -- Delete order dependents. DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881)); DELETE FROM order_status WHERE order_id IN (SELECT id FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881)); -- Delete the orders that belong to these addresses. DELETE FROM orders WHERE address_id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881); -- Delete the addresses. DELETE FROM addresses WHERE id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881); -- Verification: -- SELECT COUNT(*) FROM addresses WHERE id IN (250, 280, 285, 310, 394, 448, 586, 595, 599, 603, 606, 621, 629, 637, 647, 660, 678, 694, 696, 699, 765, 778, 881); -- expect 0 -- SELECT COUNT(*) FROM orders WHERE id IN (649, 776, 815, 859, 860, 867, 910, 1038, 1050, 1060, 1209, 1394, 1397, 1471, 1570, 1597, 1654, 1796, 1950, 1952, 1953, 1982, 2009, 2023, 2037, 2040, 2042, 2043, 2085, 2102, 2128, 2153, 2171, 2284, 2317, 2352, 2429, 2455, 2466, 2467, 2522, 2557, 2562, 2590, 2693, 2702); -- expect 0 -- SELECT COUNT(*) FROM order_status WHERE order_id IN (649, 776, 815, 859, 860, 867, 910, 1038, 1050, 1060, 1209, 1394, 1397, 1471, 1570, 1597, 1654, 1796, 1950, 1952, 1953, 1982, 2009, 2023, 2037, 2040, 2042, 2043, 2085, 2102, 2128, 2153, 2171, 2284, 2317, 2352, 2429, 2455, 2466, 2467, 2522, 2557, 2562, 2590, 2693, 2702); -- expect 0 -- SELECT id, order_id, order_item_id FROM coupon_usage WHERE id IN (116, 147, 169); -- -- expect all rows kept, order_id/order_item_id NULL -- SELECT COUNT(*) FROM users WHERE id = 388; -- expect 1 (account kept) -- PRAGMA foreign_key_check; -- expect no rows