83 lines
3.5 KiB
SQL
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
|