backend/omr-service/src/db/backfillHash.ts

import { starry } from 'starry-omr';
import { pool } from './client.js';

const BATCH_SIZE = 500;

async function backfillHash() {
	console.log('Backfilling hash for issue_measures...');

	let updated = 0;
	let failed = 0;
	let duplicateConflicts = 0;

	// Phase 1: Compute hashes in memory (without writing) to detect duplicates first
	console.log('Phase 1: Computing hashes...');
	const hashMap = new Map<string, { id: string; hash: string; score_id: string; measure_index: number; updated_at: Date }[]>();
	let lastId = '00000000-0000-0000-0000-000000000000';

	while (true) {
		const { rows } = await pool.query(
			'SELECT id, score_id, measure_index, measure, status, updated_at FROM issue_measures WHERE hash IS NULL AND id > $1 ORDER BY id LIMIT $2',
			[lastId, BATCH_SIZE]
		);

		if (rows.length === 0) break;

		console.log(`  Computing batch of ${rows.length} rows (after id ${lastId.slice(0, 8)}...)...`);

		for (const row of rows) {
			try {
				const recovered = starry.recoverJSON(row.measure, starry);
				const hash = recovered.regulationHash0;
				if (!hash) {
					console.warn(`  Row ${row.id}: no regulationHash0, skipping`);
					failed++;
					continue;
				}

				const key = `${row.score_id}::${hash}`;
				if (!hashMap.has(key)) hashMap.set(key, []);
				hashMap.get(key)!.push({ id: row.id, hash, score_id: row.score_id, measure_index: row.measure_index, updated_at: row.updated_at });
			} catch (err: any) {
				console.error(`  Row ${row.id}: ${err.message}`);
				failed++;
			}
		}

		lastId = rows[rows.length - 1].id;
	}

	console.log(`  Computed ${hashMap.size} unique (score_id, hash) groups from NULL-hash rows.`);

	// Phase 2: Check which hashes already exist (from previously backfilled rows)
	const allHashes = [...new Set([...hashMap.values()].flat().map((r) => r.hash))];
	const { rows: existingRows } = await pool.query('SELECT id, score_id, hash, updated_at FROM issue_measures WHERE hash = ANY($1) AND status > 0', [
		allHashes,
	]);
	const existingSet = new Set(existingRows.map((r) => `${r.score_id}::${r.hash}`));
	console.log(`  ${existingSet.size} hashes already exist in DB from previous backfill.`);

	// Phase 3: For each group, decide what to keep and write
	console.log('\nPhase 2: Writing hashes and deduplicating...');

	for (const [key, rows] of hashMap) {
		const alreadyExists = existingSet.has(key);

		if (rows.length === 1 && !alreadyExists) {
			// Simple case: single row, no conflict
			try {
				await pool.query('UPDATE issue_measures SET hash = $1 WHERE id = $2', [rows[0].hash, rows[0].id]);
				updated++;
			} catch (err: any) {
				console.error(`  Row ${rows[0].id}: ${err.message}`);
				failed++;
			}
		} else {
			// Duplicate case: multiple NULL-hash rows map to same (score_id, hash),
			// or a row with this hash already exists.
			// Sort by updated_at DESC to find the best candidate
			rows.sort((a, b) => new Date(b.updated_at).getTime() - new Date(a.updated_at).getTime());

			if (alreadyExists) {
				// All NULL-hash rows are duplicates of an already-backfilled row; delete them all
				const idsToDelete = rows.map((r) => r.id);
				console.log(`  ${key}: already exists, deleting ${idsToDelete.length} duplicate NULL-hash rows`);
				await pool.query('DELETE FROM issue_measures WHERE id = ANY($1)', [idsToDelete]);
				duplicateConflicts += idsToDelete.length;
			} else {
				// Keep the most recent, update its hash, delete the rest
				const keeper = rows[0];
				const idsToDelete = rows.slice(1).map((r) => r.id);
				try {
					await pool.query('UPDATE issue_measures SET hash = $1 WHERE id = $2', [keeper.hash, keeper.id]);
					updated++;
				} catch (err: any) {
					console.error(`  Row ${keeper.id}: ${err.message}`);
					failed++;
				}
				if (idsToDelete.length > 0) {
					console.log(`  ${key}: keeping ${keeper.id.slice(0, 8)}, deleting ${idsToDelete.length} duplicates`);
					await pool.query('DELETE FROM issue_measures WHERE id = ANY($1)', [idsToDelete]);
					duplicateConflicts += idsToDelete.length;
				}
			}
		}
	}

	console.log(`\nUpdated: ${updated}, Failed: ${failed}, Duplicates removed: ${duplicateConflicts}`);

	console.log('\nBackfill complete.');
}

backfillHash()
	.then(() => process.exit(0))
	.catch((err) => {
		console.error('Backfill failed:', err);
		process.exit(1);
	});