freshyo/address_util/delete-orders-before-2026-02-10.sql
2026-10-25 19:38:18 +05:30

83 lines
3.5 KiB
SQL

-- ============================================================
-- delete-orders-before-2026-02-10.sql
--
-- DESTRUCTIVE. Deletes every order created before 2026-02-10 (midnight,
-- exclusive -- anything on/after 2026-02-10 00:00 is kept) and the rows that
-- hang off those orders.
--
-- Cutoff: `created_at < '2026-02-10'` compares correctly for the timestamp
-- shapes the app writes ('2026-02-09T23:00:00.000Z' and '2026-02-09 23:00:00').
--
-- Schema: this targets the post-0006 database (applied to the remote now).
-- 0006 removed online payments, so `payments`, `refunds` and `payment_info` no
-- longer exist and `orders.payment_info_id` is gone -- none are referenced here.
-- (The same statements also run on a pre-0006 copy; they just leave the dropped
-- payment tables alone.)
--
-- Per order it:
-- 1. detaches coupon_usage / complaints / user_incidents (rows kept, order
-- references set to NULL, so coupon limits and complaint history survive)
-- 2. deletes its order_items and order_status rows
-- 3. deletes the order
--
-- Post-0006 orders whose items were archived into `orders.items_json` have no
-- `order_items` rows, so only the order/status row is removed for them.
--
-- Counts from the local dump at time of writing (pre-0006 copy):
-- 110 of 2548 orders (oldest 2025-12-24), 194 order_items, 110 order_status,
-- 23 coupon_usage detached, 1 complaint detached, 0 user_incidents.
-- Counts will differ on the target database.
--
-- Apply from apps/backend:
-- wrangler d1 execute freshyo-backend-dev --file=../../address_util/delete-orders-before-2026-02-10.sql --config wrangler.dev.toml --remote
--
-- Dry run (counts only):
-- SELECT COUNT(*) FROM orders WHERE created_at < '2026-02-10';
-- ============================================================
-- 1. Preserve cross-cutting history; drop only the order references.
UPDATE coupon_usage
SET order_id = NULL,
order_item_id = NULL
WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10')
OR order_item_id IN (
SELECT oi.id FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.created_at < '2026-02-10'
);
UPDATE complaints
SET order_id = NULL
WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10');
UPDATE user_incidents
SET order_id = NULL
WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10');
-- 2. Delete the order-owned item/status rows.
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10');
DELETE FROM order_status
WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10');
-- 3. Delete the orders themselves.
DELETE FROM orders
WHERE created_at < '2026-02-10';
-- Verification (run after applying):
-- SELECT COUNT(*) FROM orders WHERE created_at < '2026-02-10'; -- expect 0
-- SELECT COUNT(*) FROM order_items
-- WHERE order_id NOT IN (SELECT id FROM orders); -- expect 0
-- SELECT COUNT(*) FROM order_status
-- WHERE order_id NOT IN (SELECT id FROM orders); -- expect 0
-- SELECT COUNT(*) FROM coupon_usage
-- WHERE order_id IS NOT NULL
-- AND order_id NOT IN (SELECT id FROM orders); -- expect 0
-- SELECT COUNT(*) FROM complaints
-- WHERE order_id IS NOT NULL
-- AND order_id NOT IN (SELECT id FROM orders); -- expect 0
-- SELECT COUNT(*) FROM user_incidents
-- WHERE order_id IS NOT NULL
-- AND order_id NOT IN (SELECT id FROM orders); -- expect 0
-- PRAGMA foreign_key_check; -- expect no rows