#!/usr/bin/env bun /** * remove-test-data — delete the test-created rows from the LOCAL D1. * * The API suite creates hundreds of stores/products/regions/users/orders. This * removes them (and their dependents) while keeping real data: * * - everything created before `--since` (default 2026-10-18) is untouched, * which preserves the seeded regions 1/2/3, real stores/products and real users * - rows owned by non-test users (e.g. mobile 9676651496) are untouched * - the keep-user's notif_creds are untouched * * It is LOCAL-ONLY by design and refuses `--remote`. * * Run from apps/backend: * * bun run remove-test-data -- --dry-run * bun run remove-test-data * bun run remove-test-data -- --since "2026-10-18" --keep-mobile 9676651496 * * Flags: * --since created_at cutoff (default 2026-10-18) * --keep-mobile user whose data is always preserved (default 9676651496) * --dry-run print the SQL and the before-counts, change nothing * --show-sql print the SQL and exit * --help show this help * * Env overrides: D1_CONFIG (wrangler.dev.toml), D1_DB_NAME (freshyo-dev) */ import { existsSync } from 'node:fs' import { resolve } from 'node:path' const DEFAULT_SINCE = '2026-10-18' const DEFAULT_KEEP_MOBILE = '9676651496' const USAGE = `remove-test-data — delete test-created rows from the LOCAL D1 (keeps real data) Usage: bun run remove-test-data [--since ] [--keep-mobile ] [--dry-run] [--show-sql] Options: --since created_at cutoff (default ${DEFAULT_SINCE}) --keep-mobile user whose data is always preserved (default ${DEFAULT_KEEP_MOBILE}) --dry-run print the SQL and before-counts, change nothing --show-sql print the SQL and exit --help show this help Local-only: never targets the remote/production D1. ` const argv = process.argv.slice(2) const flag = (n: string) => argv.includes(`--${n}`) const opt = (n: string): string | undefined => { const eq = argv.find((a) => a.startsWith(`--${n}=`)) if (eq) return eq.slice(n.length + 3) const i = argv.indexOf(`--${n}`) if (i !== -1 && argv[i + 1] !== undefined && !argv[i + 1].startsWith('--')) return argv[i + 1] return undefined } function die(msg: string): never { console.error(`\n error: ${msg}\n`) console.error(USAGE) process.exit(1) } if (flag('help') || flag('h')) { console.log(USAGE) process.exit(0) } if (flag('remote')) die('this script is local-only: it refuses to modify the remote/production D1') const since = (opt('since') ?? DEFAULT_SINCE).trim() const keepMobile = (opt('keep-mobile') ?? DEFAULT_KEEP_MOBILE).trim() const dryRun = flag('dry-run') const showSql = flag('show-sql') const BACKEND_DIR = resolve(import.meta.dir, '..') const config = process.env.D1_CONFIG ?? 'wrangler.dev.toml' const dbName = process.env.D1_DB_NAME ?? 'freshyo-dev' const WRANGLER = resolve(BACKEND_DIR, 'node_modules/.bin/wrangler') function run(args: string[]): { out: string; err: string; code: number } { const p = Bun.spawnSync([existsSync(WRANGLER) ? WRANGLER : 'wrangler', ...args], { cwd: BACKEND_DIR, stdout: 'pipe', stderr: 'pipe', }) return { out: p.stdout.toString(), err: p.stderr.toString(), code: p.exitCode } } function parseJson(out: string): any[] | null { const s = out.indexOf('['); const e = out.lastIndexOf(']') if (s === -1 || e === -1) return null try { return JSON.parse(out.slice(s, e + 1)) } catch { return null } } const q = (s: string) => `'${s.replace(/'/g, "''")}'` const targetArgs = ['d1', 'execute', dbName, '--config', resolve(BACKEND_DIR, config), '--local'] const query = (sql: string): any[] => { const res = run([...targetArgs, '--json', '--command', sql]) if (res.code !== 0) die(`query failed:\n${res.err || res.out}`) return parseJson(res.out)?.[0]?.results ?? [] } const keepRows = query(`SELECT id FROM users WHERE mobile = ${q(keepMobile)} ORDER BY id LIMIT 1;`) const keepId = keepRows[0]?.id != null ? Number(keepRows[0].id) : -1 const S = q(since) const TEST_USERS = `SELECT id FROM users WHERE created_at >= ${S} AND id <> ${keepId}` const TEST_REGIONS = `SELECT id FROM regions WHERE created_at >= ${S}` const TEST_STAFF = `SELECT id FROM staff_users WHERE created_at >= ${S}` const TEST_STORES = `SELECT id FROM store_info WHERE created_at >= ${S} OR region_id IN (${TEST_REGIONS})` const TEST_PRODUCTS = `SELECT id FROM product_info WHERE created_at >= ${S} OR store_id IN (${TEST_STORES})` const TEST_SKUS = `SELECT id FROM product_skus WHERE product_id IN (${TEST_PRODUCTS})` const TEST_COUPONS = `SELECT id FROM coupons WHERE created_at >= ${S} OR region_id IN (${TEST_REGIONS})` const TEST_TAGS = `SELECT id FROM product_tag_info WHERE created_at >= ${S} OR region_id IN (${TEST_REGIONS})` const TEST_GROUPS = `SELECT id FROM product_group_info WHERE created_at >= ${S} OR region_id IN (${TEST_REGIONS})` const TEST_SLOTS = `SELECT id FROM delivery_slot_info WHERE region_id IN (${TEST_REGIONS}) OR EXISTS (SELECT 1 FROM json_each(delivery_slot_info.sku_ids) je WHERE CAST(je.value AS INTEGER) IN (${TEST_SKUS}))` const TEST_ORDERS = `SELECT id FROM orders WHERE user_id IN (${TEST_USERS}) OR region_id IN (${TEST_REGIONS}) OR slot_id IN (${TEST_SLOTS})` const statements: string[] = [ `DELETE FROM product_combos WHERE sku_id IN (${TEST_SKUS}) OR combo_sku_id IN (${TEST_SKUS});`, `DELETE FROM special_deals WHERE sku_id IN (${TEST_SKUS});`, `DELETE FROM sku_features WHERE sku_id IN (${TEST_SKUS});`, `DELETE FROM product_tags WHERE product_id IN (${TEST_PRODUCTS}) OR tag_id IN (${TEST_TAGS});`, `DELETE FROM product_group_membership WHERE product_id IN (${TEST_PRODUCTS}) OR group_id IN (${TEST_GROUPS});`, `DELETE FROM product_market_stats WHERE sku_id IN (${TEST_SKUS}) OR region_id IN (${TEST_REGIONS});`, `DELETE FROM product_reviews WHERE product_id IN (${TEST_PRODUCTS}) OR user_id IN (${TEST_USERS});`, `DELETE FROM coupon_usage WHERE user_id IN (${TEST_USERS}) OR coupon_id IN (${TEST_COUPONS}) OR order_id IN (${TEST_ORDERS});`, `DELETE FROM order_items WHERE order_id IN (${TEST_ORDERS}) OR sku_id IN (${TEST_SKUS});`, `DELETE FROM order_status WHERE order_id IN (${TEST_ORDERS}) OR user_id IN (${TEST_USERS});`, `DELETE FROM complaints WHERE user_id IN (${TEST_USERS}) OR order_id IN (${TEST_ORDERS});`, `DELETE FROM user_incidents WHERE user_id IN (${TEST_USERS}) OR order_id IN (${TEST_ORDERS}) OR added_by IN (${TEST_STAFF});`, `DELETE FROM coupon_applicable_users WHERE user_id IN (${TEST_USERS}) OR coupon_id IN (${TEST_COUPONS});`, `DELETE FROM cart_items WHERE user_id IN (${TEST_USERS}) OR sku_id IN (${TEST_SKUS});`, `DELETE FROM orders WHERE id IN (${TEST_ORDERS});`, `DELETE FROM addresses WHERE user_id IN (${TEST_USERS}) OR region_id IN (${TEST_REGIONS});`, `DELETE FROM user_regions WHERE user_id IN (${TEST_USERS}) OR region_id IN (${TEST_REGIONS});`, `DELETE FROM notif_creds WHERE user_id IN (${TEST_USERS}) OR region_id IN (${TEST_REGIONS});`, `DELETE FROM unlogged_user_tokens WHERE region_id IN (${TEST_REGIONS});`, `DELETE FROM staff_notif_creds WHERE staff_user_id IN (${TEST_STAFF});`, `DELETE FROM home_banners WHERE region_id IN (${TEST_REGIONS}) OR created_at >= ${S};`, `DELETE FROM product_tag_info WHERE region_id IN (${TEST_REGIONS}) OR created_at >= ${S};`, `DELETE FROM product_group_info WHERE region_id IN (${TEST_REGIONS}) OR created_at >= ${S};`, // slots must go before their SKUs (sku_ids is a JSON array of test SKUs) `DELETE FROM delivery_slot_info WHERE id IN (${TEST_SLOTS});`, `DELETE FROM product_skus WHERE product_id IN (${TEST_PRODUCTS});`, `DELETE FROM product_info WHERE id IN (${TEST_PRODUCTS});`, `DELETE FROM store_info WHERE id IN (${TEST_STORES});`, `DELETE FROM reserved_coupons WHERE region_id IN (${TEST_REGIONS}) OR created_by IN (${TEST_STAFF}) OR redeemed_by IN (${TEST_USERS}) OR created_at >= ${S};`, `DELETE FROM coupons WHERE id IN (${TEST_COUPONS});`, `DELETE FROM region_bound_consts WHERE region_id IN (${TEST_REGIONS}) OR bound_id IN (SELECT id FROM region_bounds WHERE region_id IN (${TEST_REGIONS}));`, `DELETE FROM region_consts WHERE region_id IN (${TEST_REGIONS});`, `DELETE FROM region_bounds WHERE region_id IN (${TEST_REGIONS});`, `DELETE FROM user_creds WHERE user_id IN (${TEST_USERS});`, `DELETE FROM user_details WHERE user_id IN (${TEST_USERS});`, `DELETE FROM staff_users WHERE id IN (${TEST_STAFF});`, `DELETE FROM superadmins WHERE username = 'e2e_super';`, `DELETE FROM users WHERE id IN (${TEST_USERS});`, `DELETE FROM upload_url_status WHERE created_at >= ${S};`, `DELETE FROM regions WHERE id IN (${TEST_REGIONS});`, ] const COUNT_TABLES = ['users','staff_users','superadmins','regions','store_info','product_info','product_skus','delivery_slot_info','orders','addresses','coupons','reserved_coupons','complaints','product_reviews','notif_creds'] const counts = (): Record => Object.fromEntries( COUNT_TABLES.map((t) => [t, Number(query(`SELECT COUNT(*) AS n FROM ${t};`)[0]?.n ?? 0)]) ) const before = counts() if (flag('step')) { for (let i = 0; i < statements.length; i += 1) { const r = run([...targetArgs, '--command', statements[i]]) if (r.code !== 0) { console.log(`FAILED at statement ${i}:\n${statements[i]}\n${r.err || r.out}`) process.exit(1) } console.log(`ok ${i}`) } console.log('all statements ok') process.exit(0) } if (showSql || dryRun) { console.log(`-- target: ${dbName} (local) via ${config}`) console.log(`-- since: ${since} keep-mobile: ${keepMobile} (id ${keepId})`) console.log(statements.join('\n')) console.log('\n-- before counts --') for (const [t, n] of Object.entries(before)) console.log(`-- ${t}: ${n}`) if (dryRun) console.log('\n dry-run: nothing was deleted.') process.exit(0) } const res = run([...targetArgs, '--command', statements.join('\n')]) if (res.code !== 0) die(`cleanup failed:\n${res.err || res.out}`) const after = counts() console.log(`\n Removed test data (local, since ${since}); kept mobile ${keepMobile} (id ${keepId})`) for (const [t, n] of Object.entries(after)) { const delta = before[t] - n console.log(` ${t}: ${before[t]} -> ${n}${delta ? ` (-${delta})` : ''}`) } console.log('')