203 lines
10 KiB
TypeScript
203 lines
10 KiB
TypeScript
#!/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 <date> created_at cutoff (default 2026-10-18)
|
|
* --keep-mobile <n> 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 <date>] [--keep-mobile <mobile>] [--dry-run] [--show-sql]
|
|
|
|
Options:
|
|
--since <date> created_at cutoff (default ${DEFAULT_SINCE})
|
|
--keep-mobile <n> 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<string, number> =>
|
|
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('')
|