mc-bot-town/nodejs/scripts/db-reset.js

'use strict';

const path = require('path');
const sqlite3 = require('sqlite3').verbose();
const { open } = require('sqlite');

const target = process.argv[2];
const dbPath = path.resolve(__dirname, '..', 'storage', 'storage.db');

const GROUPS = {
	all: {
		tables: ['shulker_items', 'chest_loose_items', 'shulkers', 'chests',
			'item_index', 'trades', 'invite_permissions', 'invite_sites',
			'maps', 'permissions'],
		label: 'ALL tables'
	},
	storage: {
		tables: ['shulker_items', 'chest_loose_items', 'shulkers', 'chests', 'item_index'],
		label: 'storage tables (chests, shulkers, items, index)'
	},
	permissions: {
		tables: ['permissions'],
		label: 'permissions table'
	},
	maps: {
		tables: ['maps'],
		label: 'maps table'
	},
	invites: {
		tables: ['invite_permissions', 'invite_sites'],
		label: 'invite tables'
	},
	trades: {
		tables: ['trades'],
		label: 'trades table'
	}
};

function usage() {
	console.log('Usage: node scripts/db-reset.js <target>');
	console.log('Targets:');
	for (const [name, group] of Object.entries(GROUPS)) {
		console.log(`  ${name.padEnd(14)} ${group.label}`);
	}
	process.exit(1);
}

if (!target || !GROUPS[target]) {
	usage();
}

const group = GROUPS[target];

async function main() {
	console.log(`Database: ${dbPath}`);
	console.log(`Resetting ${group.label}...`);

	const db = await open({
		filename: dbPath,
		driver: sqlite3.Database
	});

	try {
		await db.run('PRAGMA foreign_keys = OFF');

		for (const table of group.tables) {
			console.log(`  DROP TABLE IF EXISTS ${table}`);
			await db.run(`DROP TABLE IF EXISTS ${table}`);
		}

		await db.run('PRAGMA foreign_keys = ON');

		// Recreate tables and re-insert defaults
		await recreateTables(db, group);

		console.log('Done.');
	} finally {
		await db.close();
	}
}

async function recreateTables(db, group) {
	const tables = group.tables;
	const recreate = (t) => tables.includes(t);

	if (recreate('permissions')) {
		await db.exec(`CREATE TABLE permissions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			player_name TEXT UNIQUE NOT NULL,
			role TEXT DEFAULT 'team' NOT NULL CHECK(role IN ('owner', 'team', 'readonly')),
			joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`);
		console.log('  Recreated permissions');
		await insertDefaultPermissions(db);
	}

	if (recreate('chests')) {
		await db.exec(`CREATE TABLE chests (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			pos_x INTEGER NOT NULL,
			pos_y INTEGER NOT NULL,
			pos_z INTEGER NOT NULL,
			chest_type TEXT NOT NULL CHECK(chest_type IN ('single', 'double')),
			row INTEGER NOT NULL,
			column INTEGER NOT NULL,
			category TEXT,
			last_scan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			UNIQUE(pos_x, pos_y, pos_z)
		)`);
		console.log('  Recreated chests');
	}

	if (recreate('shulkers')) {
		await db.exec(`CREATE TABLE shulkers (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			chest_id INTEGER NOT NULL,
			slot INTEGER NOT NULL,
			shulker_type TEXT DEFAULT 'shulker_box',
			category TEXT,
			item_focus TEXT,
			slot_count INTEGER DEFAULT 0,
			total_items INTEGER DEFAULT 0,
			last_scan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (chest_id) REFERENCES chests(id) ON DELETE CASCADE,
			UNIQUE(chest_id, slot)
		)`);
		console.log('  Recreated shulkers');
	}

	if (recreate('shulker_items')) {
		await db.exec(`CREATE TABLE shulker_items (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			shulker_id INTEGER NOT NULL,
			item_name TEXT NOT NULL,
			item_id INTEGER NOT NULL,
			slot INTEGER NOT NULL,
			count INTEGER NOT NULL,
			nbt_data TEXT,
			FOREIGN KEY (shulker_id) REFERENCES shulkers(id) ON DELETE CASCADE,
			UNIQUE(shulker_id, slot),
			CHECK(slot >= 0 AND slot <= 26),
			CHECK(count > 0 AND count <= 64)
		)`);
		console.log('  Recreated shulker_items');
	}

	if (recreate('trades')) {
		await db.exec(`CREATE TABLE trades (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			player_name TEXT NOT NULL,
			action TEXT NOT NULL CHECK(action IN ('deposit', 'withdraw')),
			items TEXT NOT NULL,
			timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`);
		console.log('  Recreated trades');
	}

	if (recreate('chest_loose_items')) {
		await db.exec(`CREATE TABLE chest_loose_items (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			chest_id INTEGER NOT NULL,
			slot INTEGER NOT NULL,
			item_name TEXT NOT NULL,
			item_id INTEGER NOT NULL,
			count INTEGER NOT NULL,
			FOREIGN KEY (chest_id) REFERENCES chests(id) ON DELETE CASCADE,
			UNIQUE(chest_id, slot)
		)`);
		console.log('  Recreated chest_loose_items');
	}

	if (recreate('item_index')) {
		await db.exec(`CREATE TABLE item_index (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			item_id INTEGER UNIQUE NOT NULL,
			item_name TEXT NOT NULL,
			total_count INTEGER DEFAULT 0,
			shulker_ids TEXT,
			last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`);
		console.log('  Recreated item_index');
	}

	if (recreate('maps')) {
		await db.exec(`CREATE TABLE maps (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			map_id INTEGER UNIQUE NOT NULL,
			image_data TEXT,
			pixel_data TEXT,
			captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`);
		console.log('  Recreated maps');
	}

	if (recreate('invite_sites')) {
		await db.exec(`CREATE TABLE invite_sites (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT UNIQUE NOT NULL,
			label TEXT NOT NULL,
			bot_name TEXT NOT NULL,
			description TEXT,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`);
		console.log('  Recreated invite_sites');
	}

	if (recreate('invite_permissions')) {
		await db.exec(`CREATE TABLE invite_permissions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			site_id INTEGER NOT NULL,
			player_name TEXT NOT NULL,
			FOREIGN KEY (site_id) REFERENCES invite_sites(id) ON DELETE CASCADE,
			UNIQUE(site_id, player_name)
		)`);
		console.log('  Recreated invite_permissions');
	}
}

async function insertDefaultPermissions(db) {
	const conf = require('../conf/base');
	const defaultPlayers = conf.storage?.defaultPlayers || [];

	for (const player of defaultPlayers) {
		try {
			await db.run(
				'INSERT OR IGNORE INTO permissions (player_name, role) VALUES (?, ?)',
				[player.name, player.role]
			);
		} catch (e) {
			console.error(`  Error inserting ${player.name}:`, e.message);
		}
	}
	if (defaultPlayers.length > 0) {
		console.log(`  Inserted ${defaultPlayers.length} default players`);
	}
}

main().catch(err => {
	console.error('Fatal:', err);
	process.exit(1);
});