73 lines
3.2 KiB
SQL
73 lines
3.2 KiB
SQL
-- ============================================================
|
|
-- delete-orders-before.sql
|
|
--
|
|
-- Deletes every order created BEFORE the cutoff below, together
|
|
-- with its dependent rows (coupon usage, complaints, incidents,
|
|
-- payments, refunds, status, items).
|
|
--
|
|
-- HOW TO USE
|
|
-- 1. Edit the cutoff date in the helper step below (single place).
|
|
-- 2. Run (remote):
|
|
-- bunx wrangler d1 execute <db-name> --remote --config <wrangler.toml> \
|
|
-- --file scripts/delete-orders-before.sql
|
|
-- (omit --remote for the local dev DB.)
|
|
--
|
|
-- DATE FORMAT — orders.created_at is stored as an ISO-8601 string,
|
|
-- e.g. '2025-12-24T02:50:33.284Z'. Accepted cutoff values:
|
|
-- * Date only : 'YYYY-MM-DD' e.g. '2026-01-01' <-- recommended
|
|
-- * Full timestamp : 'YYYY-MM-DDTHH:MM:SS.sssZ' e.g. '2026-01-01T00:00:00.000Z'
|
|
-- * Space format : 'YYYY-MM-DD HH:MM:SS' e.g. '2026-01-01 00:00:00'
|
|
-- Where the placeholders mean, in order left to right:
|
|
-- YYYY = year (2026-01-01 -> 2026)
|
|
-- MM = month (01-12) e.g. Jan = 01, Feb = 02, Mar = 03, Apr = 04,
|
|
-- DD = day of month (01-31)
|
|
-- HH = hour 00-23, MM = minutes, SS = seconds
|
|
-- Example: 25 March 2024 is written '2024-03-25'.
|
|
-- Rows strictly EARLIER than the value are removed.
|
|
--
|
|
-- WARNING: destructive and NOT reversible. Back up first:
|
|
-- bunx wrangler d1 export <db-name> --remote --output dumps/backup.sql
|
|
-- ============================================================
|
|
|
|
-- Helper: capture the targeted orders once, so the cutoff appears in ONE place.
|
|
-- (Dropped at the end; dropped first too, so a re-run never reuses stale ids.)
|
|
DROP TABLE IF EXISTS `_old_orders`;
|
|
CREATE TABLE `_old_orders` AS
|
|
SELECT `id`, `payment_info_id` FROM `orders`
|
|
WHERE `created_at` < '2026-08-28'; -- <<< EDIT THE CUTOFF DATE HERE
|
|
|
|
-- 1) coupon_usage references both orders and order_items -> delete first.
|
|
DELETE FROM `coupon_usage`
|
|
WHERE `order_id` IN (SELECT `id` FROM `_old_orders`)
|
|
OR `order_item_id` IN (
|
|
SELECT `id` FROM `order_items`
|
|
WHERE `order_id` IN (SELECT `id` FROM `_old_orders`)
|
|
);
|
|
--> statement-breakpoint
|
|
|
|
-- 2) remaining direct children of orders.
|
|
DELETE FROM `complaints` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
DELETE FROM `user_incidents` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
DELETE FROM `payments` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
DELETE FROM `refunds` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
DELETE FROM `order_status` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
|
|
-- 3) order_items (no longer referenced by coupon_usage).
|
|
DELETE FROM `order_items` WHERE `order_id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
|
|
-- 4) the orders.
|
|
DELETE FROM `orders` WHERE `id` IN (SELECT `id` FROM `_old_orders`);
|
|
--> statement-breakpoint
|
|
|
|
-- 5) OPTIONAL: remove now-orphaned payment_info rows. Comment out to keep them.
|
|
DELETE FROM `payment_info`
|
|
WHERE `id` IN (SELECT `payment_info_id` FROM `_old_orders` WHERE `payment_info_id` IS NOT NULL);
|
|
--> statement-breakpoint
|
|
|
|
DROP TABLE IF EXISTS `_old_orders`;
|