tt-booking/scripts/reset-db.ts

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from '../lib/db/schema';
import { sql } from 'drizzle-orm';

const dbPath = process.env.DATABASE_URL || './data/sqlite.db';
const sqlite = new Database(dbPath);
const db = drizzle(sqlite, { schema });

interface ResetOptions {
	confirm?: boolean;
	keepData?: boolean;
	verbose?: boolean;
}

async function resetDatabase(options: ResetOptions = {}) {
	const { confirm = false, keepData = false, verbose = false } = options;

	try {
		if (!confirm) {
			console.log(`
⚠️  WARNING: This will permanently delete ALL data in the database!

To confirm the reset, run:
  tsx scripts/reset-db.ts --confirm

Options:
  --confirm     Confirm the destructive operation
  --keep-data   Only reset schema, keep existing data if possible
  --verbose     Show detailed output
  --help        Show this help message

Current database: sqlite.db
			`);
			return;
		}

		console.log('🗑️  Resetting database...\n');

		if (keepData) {
			console.log('🔄 Schema reset mode - attempting to preserve data...');
		} else {
			console.log('💥 Full reset mode - all data will be lost!');
		}

		// List all tables to drop
		const tables = [
			'activity_logs',
			'metrics',
			'bookings',
			'announcements',
			'time_slots',
			'settings',
			'courts',
			'users',
			'__drizzle_migrations',
			'__old_push_courts',
			'__old_push_users',
		];

		// Drop all tables
		let droppedCount = 0;
		for (const table of tables) {
			try {
				await db.run(sql.raw(`DROP TABLE IF EXISTS ${table}`));
				droppedCount++;
				if (verbose) console.log(`  ✓ Dropped table: ${table}`);
			} catch (error) {
				if (verbose) console.log(`  - Table ${table} doesn't exist or error dropping`);
			}
		}

		console.log(`\n✅ Database reset complete! Dropped ${droppedCount} tables.`);

		if (!keepData) {
			console.log('\n📝 To set up the database with fresh data, run:');
			console.log('   tsx scripts/setup-database.ts');
			console.log('   or');
			console.log('   npm run db:setup');
		}

		console.log('\n💡 Database file location: ./sqlite.db');
	} catch (error) {
		console.error('❌ Database reset failed:', error);
		throw error;
	} finally {
		sqlite.close();
	}
}

// Get database statistics before reset
async function getDatabaseStats() {
	try {
		const stats: Record<string, number> = {};

		const tableQueries = [
			{ name: 'users', table: schema.users },
			{ name: 'courts', table: schema.courts },
			{ name: 'bookings', table: schema.bookings },
			{ name: 'announcements', table: schema.announcements },
			{ name: 'timeSlots', table: schema.timeSlots },
			{ name: 'settings', table: schema.settings },
		];

		for (const { name, table } of tableQueries) {
			try {
				const count = await db.select().from(table);
				stats[name] = count.length;
			} catch {
				stats[name] = 0;
			}
		}

		return stats;
	} catch {
		return {};
	}
}

// Parse command line arguments
function parseArgs() {
	const args = process.argv.slice(2);
	const options: ResetOptions = {};

	if (args.includes('--confirm')) {
		options.confirm = true;
	}

	if (args.includes('--keep-data')) {
		options.keepData = true;
	}

	if (args.includes('--verbose') || args.includes('-v')) {
		options.verbose = true;
	}

	if (args.includes('--help') || args.includes('-h')) {
		console.log(`
Table Tennis Booking System - Database Reset

Usage: tsx scripts/reset-db.ts [options]

⚠️  WARNING: This is a destructive operation!

Options:
  --confirm        Confirm the destructive operation (required)
  --keep-data      Preserve data where possible during schema reset
  --verbose, -v    Show detailed output
  --help, -h       Show this help message

Examples:
  tsx scripts/reset-db.ts --confirm              # Full reset
  tsx scripts/reset-db.ts --confirm --verbose    # Full reset with details
  tsx scripts/reset-db.ts --confirm --keep-data  # Schema reset only

After reset, set up the database:
  tsx scripts/setup-database.ts
		`);
		process.exit(0);
	}

	return options;
}

// Main execution
if (require.main === module) {
	const options = parseArgs();

	// Show current database stats if verbose and confirmed
	if (options.confirm && options.verbose) {
		getDatabaseStats().then((stats) => {
			console.log('📊 Current Database Contents:');
			Object.entries(stats).forEach(([table, count]) => {
				console.log(`   ${table}: ${count} records`);
			});
			console.log('');

			resetDatabase(options)
				.then(() => {
					console.log('🎯 Database reset completed successfully!');
					process.exit(0);
				})
				.catch((error) => {
					console.error('💥 Database reset failed:', error);
					process.exit(1);
				});
		});
	} else {
		resetDatabase(options)
			.then(() => {
				if (options.confirm) {
					console.log('🎯 Database reset completed successfully!');
				}
				process.exit(0);
			})
			.catch((error) => {
				console.error('💥 Database reset failed:', error);
				process.exit(1);
			});
	}
}

export { resetDatabase };