bibdle/scripts/deduplicate-completions.ts

import Database from 'bun:sqlite';

// Database path - adjust if your database is located elsewhere
const dbPath = Bun.env.DATABASE_URL || './local.db';
console.log(`Connecting to database: ${dbPath}`);
const db = new Database(dbPath);

interface DuplicateGroup {
	anonymous_id: string;
	date: string;
	count: number;
}

interface Completion {
	id: string;
	anonymous_id: string;
	date: string;
	guess_count: number;
	completed_at: number;
}

console.log('Finding duplicates...\n');

// Find all (anonymous_id, date) pairs with duplicates
const duplicatesQuery = db.query<DuplicateGroup, []>(`
	SELECT anonymous_id, date, COUNT(*) as count
	FROM daily_completions
	GROUP BY anonymous_id, date
	HAVING count > 1
`);

const duplicates = duplicatesQuery.all();
console.log(`Found ${duplicates.length} duplicate groups\n`);

if (duplicates.length === 0) {
	console.log('No duplicates to clean up!');
	db.close();
	process.exit(0);
}

let totalDeleted = 0;

// Process each duplicate group
for (const dup of duplicates) {
	// Get all completions for this (anonymous_id, date) pair
	const completionsQuery = db.query<Completion, [string, string]>(`
		SELECT id, anonymous_id, date, guess_count, completed_at
		FROM daily_completions
		WHERE anonymous_id = ? AND date = ?
		ORDER BY completed_at ASC
	`);

	const completions = completionsQuery.all(dup.anonymous_id, dup.date);
	console.log(`  ${dup.anonymous_id} on ${dup.date}: ${completions.length} entries`);

	// Keep the first (earliest completion), delete the rest
	const toKeep = completions[0];
	const toDelete = completions.slice(1);

	console.log(`    Keeping: ${toKeep.id} (completed at ${new Date(toKeep.completed_at * 1000).toISOString()})`);

	const deleteQuery = db.query('DELETE FROM daily_completions WHERE id = ?');

	for (const comp of toDelete) {
		console.log(`    Deleting: ${comp.id} (completed at ${new Date(comp.completed_at * 1000).toISOString()})`);
		deleteQuery.run(comp.id);
		totalDeleted++;
	}
}

console.log(`\n✅ Deduplication complete!`);
console.log(`Total records deleted: ${totalDeleted}`);
console.log(`Unique completions preserved: ${duplicates.length}`);

db.close();