const fs = require('fs');
const path = require('path');

// filepath: c:\Users\Chris\git\Roblox-Analytics\migrations.js

module.exports = function(db) {
	const migrationsDir = path.join(__dirname, 'migrations');
	if (!fs.existsSync(migrationsDir)) {
		console.log('No migrations directory found:', migrationsDir);
		return;
	}

	const sqlFiles = fs.readdirSync(migrationsDir)
		.filter(f => f.toLowerCase().endsWith('.sql'))
		.sort();

	if (sqlFiles.length === 0) {
		console.log('No .sql migration files found in', migrationsDir);
		return;
	}

	db.serialize(() => {
		// Ensure migrations table exists
		db.run(
			`CREATE TABLE IF NOT EXISTS migrations (
				name TEXT PRIMARY KEY,
				applied_at TEXT NOT NULL
			)`,
			(err) => {
				if (err) {
					console.error('Could not create migrations table', err);
					process.exit(1);
				}

				// Apply each migration in order if not already applied
				sqlFiles.forEach(file => {
					const name = file;
					const fullPath = path.join(migrationsDir, file);

					db.get('SELECT name FROM migrations WHERE name = ?', [name], (err, row) => {
						if (err) {
							console.error('Failed checking migration', name, err);
							process.exit(1);
						}
						if (row) {
							console.log('Skipping applied migration:', name);
							return;
						}

						let sql;
						try {
							sql = fs.readFileSync(fullPath, 'utf8');
						} catch (readErr) {
							console.error('Failed reading migration file', fullPath, readErr);
							process.exit(1);
						}

						db.exec(sql, function(execErr) {
							if (execErr) {
								console.error('Failed applying migration', name, execErr);
								process.exit(1);
							}

							db.run(
								"INSERT INTO migrations(name, applied_at) VALUES(?, datetime('now'))",
								[name],
								function(insertErr) {
									if (insertErr) {
										console.error('Failed recording migration', name, insertErr);
										process.exit(1);
									}
									console.log('Applied migration:', name);
								}
							);
						});
					});
				});
			}
		);
	});
};