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

import { pool } from './client.js';

const migrations = [
	{
		name: '001_create_scores_table',
		sql: `
			CREATE TABLE IF NOT EXISTS scores (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				title VARCHAR(255),
				source_url TEXT,
				page_count INTEGER DEFAULT 0,
				data JSONB DEFAULT '{}',
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
			);

			CREATE INDEX IF NOT EXISTS idx_scores_created_at ON scores(created_at DESC);
		`,
	},
	{
		name: '002_create_tasks_table',
		sql: `
			CREATE TYPE task_status AS ENUM ('pending', 'running', 'completed', 'failed');
			CREATE TYPE task_type AS ENUM ('predict_page', 'predict_all', 'predict_custom');

			CREATE TABLE IF NOT EXISTS tasks (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				score_id UUID REFERENCES scores(id) ON DELETE CASCADE,
				type task_type NOT NULL,
				status task_status DEFAULT 'pending',
				progress INTEGER DEFAULT 0,
				current_step VARCHAR(100),
				result JSONB,
				error TEXT,
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				started_at TIMESTAMP WITH TIME ZONE,
				completed_at TIMESTAMP WITH TIME ZONE
			);

			CREATE INDEX IF NOT EXISTS idx_tasks_score_id ON tasks(score_id);
			CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
		`,
	},
	{
		name: '003_create_pages_table',
		sql: `
			CREATE TABLE IF NOT EXISTS pages (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				score_id UUID REFERENCES scores(id) ON DELETE CASCADE,
				page_index INTEGER NOT NULL,
				image_path TEXT,
				width INTEGER,
				height INTEGER,
				data JSONB DEFAULT '{}',
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				UNIQUE(score_id, page_index)
			);

			CREATE INDEX IF NOT EXISTS idx_pages_score_id ON pages(score_id);
		`,
	},
	{
		name: '004_create_music_sets_table',
		sql: `
			CREATE TABLE IF NOT EXISTS music_sets (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				name VARCHAR(255),
				type VARCHAR(20) DEFAULT 'stave',
				content JSONB DEFAULT '{}',
				status VARCHAR(50) DEFAULT 'draft',
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
			);

			CREATE INDEX IF NOT EXISTS idx_music_sets_updated_at ON music_sets(updated_at DESC);
			CREATE INDEX IF NOT EXISTS idx_music_sets_name ON music_sets(name);
		`,
	},
	{
		name: '005_create_tags_table',
		sql: `
			CREATE TABLE IF NOT EXISTS tags (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				name VARCHAR(100) NOT NULL UNIQUE,
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
			);

			CREATE TABLE IF NOT EXISTS music_set_tags (
				music_set_id UUID REFERENCES music_sets(id) ON DELETE CASCADE,
				tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				PRIMARY KEY (music_set_id, tag_id)
			);

			CREATE INDEX IF NOT EXISTS idx_music_set_tags_music_set_id ON music_set_tags(music_set_id);
			CREATE INDEX IF NOT EXISTS idx_music_set_tags_tag_id ON music_set_tags(tag_id);
		`,
	},
	{
		name: '006_create_solution_cache_table',
		sql: `
			CREATE TABLE IF NOT EXISTS solution_cache (
				name VARCHAR(64) PRIMARY KEY,
				value JSONB NOT NULL,
				priority DOUBLE PRECISION,
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
			);
		`,
	},
	{
		name: '007_create_issue_measures_table',
		sql: `
			CREATE TABLE IF NOT EXISTS issue_measures (
				id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
				score_id VARCHAR(255) NOT NULL,
				measure_index INTEGER NOT NULL,
				measure JSONB NOT NULL,
				status INTEGER NOT NULL DEFAULT 1,
				by_user BOOLEAN DEFAULT false,
				created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
				updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
			);

			CREATE INDEX IF NOT EXISTS idx_issue_measures_score_id ON issue_measures(score_id);
			CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_measures_score_measure
				ON issue_measures(score_id, measure_index) WHERE status > 0;
		`,
	},
	{
		name: '008_add_annotator_to_issue_measures',
		sql: `
			ALTER TABLE issue_measures ADD COLUMN IF NOT EXISTS annotator VARCHAR(100);
		`,
	},
];

async function runMigrations() {
	console.log('Running migrations...');

	// Create migrations tracking table
	await pool.query(`
		CREATE TABLE IF NOT EXISTS migrations (
			name VARCHAR(255) PRIMARY KEY,
			executed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
		)
	`);

	for (const migration of migrations) {
		const { rows } = await pool.query('SELECT name FROM migrations WHERE name = $1', [migration.name]);

		if (rows.length === 0) {
			console.log(`Running migration: ${migration.name}`);
			await pool.query(migration.sql);
			await pool.query('INSERT INTO migrations (name) VALUES ($1)', [migration.name]);
			console.log(`Completed: ${migration.name}`);
		} else {
			console.log(`Skipping: ${migration.name} (already executed)`);
		}
	}

	console.log('All migrations completed.');
}

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